How group by affects aggregate functions

There are several aspects of your query. The first is that mysql by default will give you a random answer if there are several possible answers to a group by question. SQL92 demanded that all non aggregated columns in the select clause must be part of the group by clause. SQL99 loosened this restriction and requires that all non aggregated columns are functionally dependent of the group by clause. By default mysql ignores both of these and returns a random row if there are several possible ones. You can force the SQL92 behaviour by adding ONLY_FULL_GROUP_BY to @@sql_mode, most DBMS's use such implementation. A small example:

create table t (x int not null, y int not null);
insert into t (x,y) values (1,1),(1,2);

For a query like:

select x,y, count(*) from t group by x;

There are two possible answers:

1 | 1 |        2
1 | 2 |        2

Mysql will randomly return one of them. In some sense the result is undefined.

To see what happens when there is no group by clause at all involved, let's add another row to the equation:

insert into t (x,y) values (2,3);

select x,y, count(*) from t

Since we have no group by count(*) will be applied to the whole result, for x and y there are 3 possible combinations:

1 |    1 |        3
1 |    2 |        3
2 |    3 |        3

Once again mysql will shoot you in the foot by randomly returning one of these rows, say:

1 |    1 |        3

For an empty set the aggregate function count(*) should be 0 (WHERE is evaluated before GROUP BY), but there are no x and y:

select x,y, count(*) from t where x=4;
+------+------+----------+
| x    | y    | count(*) |
+------+------+----------+
| NULL | NULL |        0 |
+------+------+----------+

so they are shown as non-existing (NULL).

The short answer is that you get a non-sense answer because you asked a non-sense question ;-)

The scenario you describe is is probably the one thing that causes the most confusion in the mysql community. I wish that ONLY_FULL_GROUP_BY where the default behaviour and that there where a setting ALLOW_PARTIAL_GROUPING that could be used for backward compability.


'OraOLEDB.Oracle' provider is not registered on the local machine - Oracle Database Client 12C

From comment by Wernfried Domscheit:

  Have a look at this instruction to install both 32 bit and 64 bit on one machine: stackoverflow.com/questions/24104210/…. Note Oracle OLEDB provider is not included in Oracle Instant client, you have to install it separately or enable the corresponding option in Oracle Universal Installer.


Powershell Script Error Connecting To SQL Server

This particular issue I was able to resolve. I went and installed Active Directory Module Feature on the server thus removing the Import-Module- Active Directory error.

Even though I already had Microsoft.SQLServer.BatchParser assembly installed on the client desktop, I had read a few posts on StackOverflow how the reinstalled SSMS and it worked. So I re-installed SSMS 2014 with SP 2 and it still gave me the same error. My Processor Architecture was x86. So I also installed SQL Server 2014 SMO for 64-bit on my client issued desktop. I have been able to connect to the server and got the scripts running.


What you need is a design like this:

Keep you list of people in one table.  Then keep another table with the list of things that various people might need.  Normalizing people and needed things is important for being able to write simple, reliable queries about your data from the perspective of either an individual person or a particular needed thing.

Use a third table to keep track of who needs what, and how badly they need it.


Denormalized table designs in normalized database?

Some issues to bring up:

For OLTP, inserts will be really slow in a table that wide
You will be wasting a lot of space by repeating redundant information
Columnstore is a non-modifiable index type so you can't use it in an OLTP environment
You greatly complicate referential integrity controls this way.  You can't just make foreign keys to make sure you are getting valid values for fields.
Indexing will be a nightmare

The real issue here is the developers not understanding design.

Keeping the client data in it's native format is FINE.  I do  this kind of thing for a living, and I get tables with 500+ fields all the time.  The way to handle it is to separate your RAW data from your BUILT data.

If the client gives you a massively wide table, you need to normalize it yourself to make a usable data set.  There's nothing stopping you from creating a process that breaks out that data into appropriate tables.


The key to performance is a matching multicolumn index of the form:

CREATE UNIQUE INDEX ON people (firstname, id);

UNIQUE, since sort order can be ambiguous without it, and you can get arbitrary results from peers.

A UNIQUE or PRIMARY KEY constraint serves as well.

While the first column is checked for equality like in your example (or sorted in the same direction as the query), this index is good for paging up and down, though it's a bit better for paging up.

With the index in place (and after running ANALYZE on the table), you won't see sequential scans any more (unless your table is small). The database is not "going through almost all your rows" any more.

Read the fine presentation by Markus Winand you are linking to.

If you want paging across more than one firstname, use ROW values. Example for paging down:

SELECT *
FROM  (
   SELECT id, firstname, lastname
   FROM   people
   WHERE  (firstname, id) < ('John', 6)  -- ROW values
   ORDER  BY firstname DESC, id DESC
   LIMIT  3
   ) d
ORDER BY firstname, id;

Related:

Optimize query with OFFSET on large table

If the SELECT list only adds lastname like in your example, you might try to add that column to the index to get index-only scans out of it:

CREATE UNIQUE INDEX ON people (firstname, id, lastname);

Index expressions in this order.

The upcoming Postgres 11 allows indexes to INCLUDE columns, which results in smaller index size, better performance and is applicable in more situations. Like:

CREATE UNIQUE INDEX ON people (firstname, id) INCLUDE (lastname);


Drop the last character of string, if the last character is s

It seems a bit odd issue but here's how to do it.
DECLARE @string NVARCHAR(10)
SET @string = 'Tests'

SELECT
  CASE
    WHEN RIGHT(@string,1) IN ('s','S') THEN SUBSTRING(@string,1,LEN(@string)-1)
    ELSE @string
  END AS string


Within a RHEL 6.3 + High Reliability Add-on, how do I add DB2 (v9.7) into a service group to have the service controlled by the cluster?

As I mention in my comments above, setting up HADR (High Availability Disaster Recovery) with DB2 is pretty simple, especially if you are going Active/Passive with your nodes. You do not gain automatic failover per se. You would use the db2 takeover hadr on database <database-name> command for a normal takeover for maintenance and a db2 takeover hadr on database <database-name> by force in the case of a hard fail of the active node. This article is a good one for how to set that up. (I know it is about Rational Jazz, but it has the steps for how to configure simple HADR without db2haicu).

It sounds like you want automated failover. This means moving up the levels of HADR to start using db2haicu and TSA. Ember Crooks' blog has a part 1, part 2, and a part 3 that shows to set this up. This gets a little trickier because it involves another layer between the OS and the database that needs to be configured and it must know about IP addresses, ports, a virtual IP address, etc. But the benefit is that this extra layer checks to make sure your active nodes stay active, and if not, it fails over to a passive node.

I would also recommend (you and the DBA's) watching this replay from the DB2NightShow. Vikram Khatri from IBM goes through a set of slides that take HADR from its simplest to its most complex, based on what you are trying to do (ie, from active/passive on the same LPAR all the way up to multiple active/active phyiscal nodes with load balancing, etc.)

I'm guessing between Ember's blog and the DB2NightShow link you'll find what you are looking for. However, don't discount the first link. You could set up simple HADR without db2haicu and always "automate" failover with some scripts or something. It might be simpler than db2haicu until you can figure db2haicu out.


Postgres - how much space is required to perform a VACUUM

how much space is required to perform a VACUUM

At worst pg_total_relation_size(table) * 2. Usually lots less because the new table and indexes are significantly more compact.

  How do I free OS space in a POSTGRES database when I know there is unused space available, but there's not enough space to run a VACUUM?

Clean up unrelated files on the volume. Drop some tables or indexes you don't need. Move tables or indexes to other tablespaces on other storage. Expand the underlying volume.

There isn't really a good way to compact in-place and free space to the OS without first needing to allocate more space. I think most people agree this is a flaw, but nobody seems to care enough to develop any improvement for it.


Postgres 9.4, replication slots, doesn't work failover master

You cannot just jump back to the old master, as it is said here:

  Once failover to the standby occurs, there is only a single server in
  operation. This is known as a degenerate state. The former standby is
  now the primary, but the former primary is down and might stay down.
  To return to normal operation, a standby server must be recreated,
  either on the former primary system when it comes up, or on a third,
  possibly new, system.

For this, you need a recovery.conf on the old master.  This should be nearly the same as the present one, only the connection has to point elsewhere.


Inside a trigger create database is not working

As the documentation says (under Trigger Limitations):

  The following Transact-SQL statements are not allowed in a DML trigger:
  (...) CREATE DATABASE (...)

There are further limitations, consulting the documentation is highly recommended.

This all means, to my understanding, that you should solve this problem at application level*: check whether your user is successfully created, and then issue a CREATE DATABASE statement.  Preferably these two are enclosed in a transaction, so that either both succeed or both fail - this way you can be sure that your data are in a consistent state.  You may have to issue a DROP DATABASE if the transaction fails.  (Thanks to Jon Seigel for pointing this out.)

*: From this point of view, the 'application' may be a T-SQL block of code (or a stored procedure) as well.


How to securely store the password for an Oracle DB link?

Your real problem is not about storing the password in a table securely, but the need for repeatedly changing and recreating the dblink.

Yes, you can store a password in a wallet, and no, you will not be able to retrieve the actual password from the wallet.

To store a password securely in a table, you can encrypt it (e.g. with DBMS_CRYPTO) and store the encrypted value. The encryption key should be stored outside the database.

FYI: Oracle stores dblink passwords in a table, in an encrypted form. The encryption key is a constant (kind of) and decryption takes no time.

12c: https://mahmoudhatem.wordpress.com/2016/12/08/reverse-engineering-db-link-password-decryption-in-plsql/

11g: https://www.krenger.ch/blog/find-password-for-database-link/


The tutorial on that link is not complete.
You should type

apt-get install bdr-9.4

instead of

apt-get install postgresql-bdr-9.4

After that everything is working fine..

I also lose some time to figure that out..


simpler way to write if today or tomorrow is between ColA or ColB

The issue is how to check for overlapping ranges:

WHERE SPRFRDTE <= dateadd(day, 1, cast(getdate() as date))
  AND cast(getdate() as date) <= SPRTODTE
  AND SPRFRDTE <= SPRTODTE ;                 -- this might not be needed.

The very last condition is just a technical detail and may not be needed if you are sure (i.e. there is a constraint) that enforces that CHECK (SPRFRDTE <= SPRTODTE).

If however such abnormal values are allowed, you need to include that condition as well for the query to be equivalent to the version with the two BETWEEN.


user can insert record only two files with the same major

SELECT t1.major, t1.files file1, t2.files file2
FROM db_school t1
LEFT JOIN db_school t2 ON t1.major = t2.major
                      AND t1.id < t2.id


Find all tables in a DB with column names that contain that substring?

Login using the MySQL command-line client.

mysql> USE Information_Schema;

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW Tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENGINES                               |
<rest of tables snipped>

mysql> DESCRIBE Columns;
+--------------------------+---------------------+------+-----+---------+-------+
| Field                    | Type                | Null | Key | Default | Extra |
+--------------------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG            | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA             | varchar(64)         | NO   |     |         |       |
| TABLE_NAME               | varchar(64)         | NO   |     |         |       |
| COLUMN_NAME              | varchar(64)         | NO   |     |         |       |
| ORDINAL_POSITION         | bigint(21) unsigned | NO   |     | 0       |       |
| COLUMN_DEFAULT           | longtext            | YES  |     | NULL    |       |
| IS_NULLABLE              | varchar(3)          | NO   |     |         |       |
| DATA_TYPE                | varchar(64)         | NO   |     |         |       |
| CHARACTER_MAXIMUM_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| CHARACTER_OCTET_LENGTH   | bigint(21) unsigned | YES  |     | NULL    |       |
| NUMERIC_PRECISION        | bigint(21) unsigned | YES  |     | NULL    |       |
| NUMERIC_SCALE            | bigint(21) unsigned | YES  |     | NULL    |       |
| DATETIME_PRECISION       | bigint(21) unsigned | YES  |     | NULL    |       |
| CHARACTER_SET_NAME       | varchar(32)         | YES  |     | NULL    |       |
| COLLATION_NAME           | varchar(32)         | YES  |     | NULL    |       |
| COLUMN_TYPE              | longtext            | NO   |     | NULL    |       |
| COLUMN_KEY               | varchar(3)          | NO   |     |         |       |
| EXTRA                    | varchar(30)         | NO   |     |         |       |
| PRIVILEGES               | varchar(80)         | NO   |     |         |       |
| COLUMN_COMMENT           | varchar(1024)       | NO   |     |         |       |
+--------------------------+---------------------+------+-----+---------+-------+
20 rows in set (0.01 sec)

Your desired SQL is something like this:

mysql> SELECT Table_Name, Column_Name FROM Columns WHERE Column_Name LIKE '%My_Search_String%';

My_Search_String could be 'student%' for student_id.
My_Search_String is case sensitive - and be careful, the underscore is a wild-card in SQL for a single character.

[EDIT]

I double checked about My_Search_String being case sensitive. It appears that metadata is searched in a case insensitive fashion.

mysql> CREATE TABLE xyz (x int, `xx` int, XX int);
ERROR 1060 (42S21): Duplicate column name 'XX'
mysql>

Now, if column_names were case sensitive, there should be no duplicate XX column. Just one to watch - it's the data in the fields (assuming VARCHARs) that is case sensitive.


How to preseve Execute permissions for MySQL procedure with alter procedure (Drop/Create)

Best is to grant execute at the DB level:

grant execute on db.* to test1@'10.%';  (don't use %, too risk, use ip subnet)
This way you don't need to grant permission in SP.


How to chop off X number of characters across multiple records?

DECLARE @tab TABLE(statename VARCHAR(20))
INSERT INTO @tab
(
      statename
)
select 'Boston, MA' UNION ALL
select 'Los Angeles, CA' UNION ALL
select 'Helena, MT'

SELECT SUBSTRING(statename,0,LEN(statename)-3) FROM @tab t

SELECT LEFT(statename,LEN(statename)-4)FROM @tab t


A couple of possibilities, not sure if any of them will help you

Force recovery

[mysqld]
innodb_force_recovery = 4

If you run Force Recovery, your database will still be in an incoherent state. This will however allow you to make a backup of the data that you do have access to.

  Usually, most of the data obtained in this way is intact. Serious
  corruption might cause SELECT * FROM tbl_name statements or InnoDB
  background operations to crash or assert, or even cause InnoDB
  roll-forward recovery to crash. In such cases, use the
  innodb_force_recovery option to force the InnoDB storage engine to
  start up while preventing background operations from running, so that
  you can dump your tables

Force InnoDB recovery

Permissions

Make sure the MySQL service user has full permissions on C:\wamp\bin\mysql\mysql5.6.12\data

The service that runs MySQL needs full control for the directory where you are storing your data files

Update

FS error

Problems reading from the C:\wamp\bin\mysql\mysql5.6.12\data directory ?

If you go to Control Panels-> Administrative Tools-> Events Viewer, then Windows Logs -> System, you might find more information about disk issues

One possible solution, would be to copy your data directory to another drive and/or directory.

Stop MySQL

Copy the data directory contents to the new drive and/or directory.

Open the C:\wamp\bin\mysql\mysql5.6.12\my.cnf file and change the datadir option under mysqld

[mysqld]

datadir=D:\some\other\directory

Start MySQL


oracle failsafe - how to start instance in nomount

Shutdown instance in Cluster Manager (Right click, "Take Offline" or however you choose to do it)
Bring up Windows Services (Start->Run->Services.MSC) on the clustered server
Start the Oracle service for the instance you want
Now you can do whatever you like in SQL*Plus (Cluster Manager will still show it as down no matter what you do at this point)

So, if step 3 brings the Oracle instance up to "Open" you can do a shutdown immediate etc and then open it nomount.

HTH


Progress' collation tables are to control how indexes are sorted and assigned and some character handling if you are operating across several locales. Open Edge comes with a variety of collation tables for popular languages. The purpose is to help with sorting and comparisons.  You can specify collation tables upon startup.  If you do not but name a database in startup Progress will use the collation table for that database.  If you do not specify either then Progress will use a collation table called BASIC.

As for MS SQL: both databases have data in the same language, correct? And your ODBC test was from SQLServer -> Open Edge which was successful.  Some things to watch out for:

If you are using any "only in SQLServer" datatypes the data could get
corrupted.  Open Edge has a match for all types they say, but I have
not tried them all.  If you are sticking with the standard ones
VARCHAR, INT, NUMERIC, DATE, TEXT you should be fine.  If you have
any binary columns Open Edge will convert those to character, so they
will not operate the same.  The Open Edge documentation lists the
datatype equivalents (https://www.progress.com/documentation).

In case you are interested, Open Edge does have a SQLServer specific DataDirect branded ODBC driver you can use. It is intended to make connections to SQLServer easier.

Make sure you have the Open Edge driver on the MS ODBC Data Source    Administrator box you will use.
I've heard of issues with the 64 bit versions of various drivers and    the ODBC Data Source Administrator.  I do not know if they are now    fixed in late 2017, but if you have the 32-bit versions they may be    more compatible all around.

How To options:

You can use SSIS. Build your package and run it to import the data into SQL Server.  If you want to try any automation make sure that the SQL Server Agent job runs on the same server as your ODBC Data Source Administrator and the user has the privileges to all of those areas.
You can use the SQL Server Import/Export wizard to move your data. This might be the easiest way.  Setup your data source and target and import data from Open Edge.  They do note that any binary data will be converted to "datatype 23", and you have to manually go to each column definition and set the datatype in the dropdown to binary.  To play it safe, check the datatypes after you import.
Another option, if you wanted to go this way, is to use free ETL tools to move your data.  You don't say how much, how complex or how often; but ETL is another choice. This would remove the connection concerns since these packages typically come with a variety of connectors for many types of data sources.  If that interests you, you can check out this ETL wiki for options.


Okay to merge included columns on otherwise identical indexes?

Yes, you should merge these indexes and only keep one,  as long as you verify that:

Neither of them are marked as UNIQUE (in which case, preserve that one)
Neither of them have a filter condition
Neither of them are expressly referenced in an index hint


From the code repository I see that "Windows 7" was removed from the supported OS list on Oct 3rd, 2018.

   static std::vector<std::string> supportedOsList{
-    "Ubuntu 18.04", "Ubuntu 16.04", "Debian 8",
+    "Ubuntu 18.04", "Ubuntu 18.10", "Debian 9",

     "Red Hat Enterprise Linux Server release 7", // Oracle 7.1 looks like this: "Red Hat Enterprise Linux Server release
                                                  // 7.1 (Maipo)"
-    "Fedora release 28", "Fedora release 27", "CentOS release 7",
+    "Fedora release 28", "Fedora release 29", "CentOS release 7",

-    "Windows 7", "Windows 8", "Windows 10", "Windows 2012 Server", "Windows Server 2012 R2", "Windows Server 2016",
+    "Windows 10", "Windows Server 2016",

-    "OS X 10.11", "macOS 10.12", "macOS 10.13"
+    "macOS 10.13", "macOS 10.14"
   };

The release after that (8.0.13) was the first one without official Win 7 support.


Including rows from base table with left joins when these joins are 'empty'

As a rule of thumb -
when having ... LEFT JOIN t ON ..., conditions on t should be in the ON clause and not in the WHERE clause.

SELECT
   t.*, count(b.id) as nof_reservations
FROM (
   SELECT time_start, time_end FROM `interval`
   WHERE time_start < '18:00:00'
   AND time_end > '10:00:00'
) t -- holds a row per 15m interval

-- reservations of the assets
LEFT JOIN reservations r
ON   start_utc < concat('2017-04-02 ', t.time_end)
AND  end_utc > concat('2017-04-02 ', t.time_start)
AND  r.deleted_at is null

-- we need to filter the assets on their type, which is
-- stored in the assets table
LEFT JOIN assets b
ON r.asset_id = b.id
AND b.asset_type_id = 6 -- asset type we need

GROUP BY t.time_start, t.time_end;

It's possible you want a slightly different join instead (the rest of the query, SELECT ... FROM ... t and GROUP BY stays the same as above):

-- reservations of the assets
-- and filter the assets on their type
LEFT JOIN reservations r
    JOIN assets b
    ON r.asset_id = b.id
    AND b.asset_type_id = 6 -- asset type we need
ON   start_utc < concat('2017-04-02 ', t.time_end)
AND  end_utc > concat('2017-04-02 ', t.time_start)
AND  r.deleted_at is null


When trying to configure data guard broker I'm getting Error: ORA - 12154 : TNS: could not resolve the connect identifier specified

The part after IDENTIFIER IS must be a connection to the database. You should replace the orcl_standby with something like '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)
(HOST=<server of the Standby>)(PORT=1521)))(CONNECT_DATA=(SID=<the SID of the Standby>)(SERVER=DEDICATED)))'.


My off the cuff thought would be that you need to turn on logging in your SSIS package and I'm assuming you have it pointed at SQL Server. I prefer to capture OnPre/PostExecute, TaskFailed and OnError events at a minimum.

Write a query that looks for packages that have begun execution but have not terminated. This will go against dbo.sysdtslog90 for 2005, dbo.sysssislog for 2008+ instances.

Depending on how you like to solve your problems, you can use that query in pure TSQL to detect and send mail via sp_send_dbmail (assuming you have it configured) or create an SSIS package that contains the logic and uses a Send Mail Task.

Now you have a tool that when it runs detects the overlong situation and sends notification.

The final step is how do you make it go. The cheap and lazy route would be to schedule a job every N intervals that fires off the solution above and checks. If you wanted more elegance, you could add a step in your existing SQL Agent job that schedules a one off execution of your job check logic, perhaps even a self-deleting job scheduled for a single execution, that runs 2:30 from now. If the package completes, then it will do nothing and delete. Otherwise, you get your page and have to jiggle the database. Harder to do it this way and uglier to test but there's a certain elegance to it.


Empty table being returned with plpgsql and EXECUTE USING

I think you want RETURN QUERY EXECUTE ... USING context doesn't matter: EXECUTE .. USING won't work from psql, it will not append the result to the functions return value.

This returns an empty table, and should probably trigger a warning when you create such a function.

CREATE FUNCTION foo ()
RETURNS TABLE (a int) AS $$
BEGIN
  EXECUTE 'SELECT $1' USING 42;
END
$$ LANGUAGE plpgsql;

This returns a table with one row, with one int column a with a value of 42,

CREATE FUNCTION foo ()
RETURNS TABLE (a int) AS $$
BEGIN
  RETURN QUERY EXECUTE 'SELECT $1;' USING 42;
END
$$ LANGUAGE plpgsql;

See also,

PL/pgSQL - SQL Procedural Language - Control Structures


Way to ignore pages in memory for 1 query

Using the OPTION (RECOMPILE) query hint will be your friend with regards to forcing a new plan each time you execute the query.

Preventing data being read from the cache is impossible.  However, you can clear the cache, assuming you're not using the production machine for these tests.  Clearing the cache will force data to be read from disk; do this prior to running the query each time.  DO NOT DO THIS IN PRODUCTION since you will cause a very quick and noticeable drop in performance for all queries running against the server.

Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server.  You may want to run the CHECKPOINT command first to ensure all dirty pages are written to disk first.

Clearly, you're going to want to look at the actual execution plans; do that by clicking "Query -> Include Actual Execution Plan" from the menu in SSMS (or press CTRL - M prior to running the query)

You may also want to turn STATISTICS IO, TIME ON at the start of your session to get better diagnostic info from the server.  Turning on "Client Statistics" is also a great tool to see the impact of SSMS on your results.  Hit the "Query -> Include Client Statistics" menu, or press Shift - Alt - s


what is the fastest way to restore MySQL database without using dump files

Ofcourse a physical files restore will be quicker than loading a dump. So, if you have full physical backup / snapshot of mysql data-directory - you can go fast!
(Below answer assumes that you have physical backup available.)
Tables are MyISAM:

Stop mysql
Copy database folder to mysql datadir
Correct permissions
Start mysql

Tables are InnoDB:
If we have InnoDB tables in the picture then things are not as simple. Considering you have Xtrabackup.
You will have  to import tables one after another.
Refer Importing-Exporting-Tables-using-Xtrabackup OR how-to-recover-a-single-innodb-table-from-a-full-backup
(For this to work you should have used xtrabackup as backup option.)
Splitting mysqldump
If you want to extract only one database from full mysqldump then you might want to extract single database from mysqldump and load it (saves you from loading full dump).


EventLog: Server TCP provider failed to listen on [ 'any'  1433]. Tcp port is already in use

Because netstat doesn't indicate that anything is listening on port 1433, that leaves the possibility that a client is already using port 1433 when SQL tries to start. From the SQL Server Premier Field Engineer blog post about "TCP Port is already In Use":

  The second situation involves a client running on the server using the same dynamic port as SQL Server. Every TCP session requires two endpoints – a client endpoint and a server endpoint. Each endpoint is an IP Address/Port combination - also known as a TCP socket. Normally, the client socket code will ask the OS to provide a port from a range of dynamic ports. The server socket is the IP/TCP port of the service the client is connecting to (SQL Server for example).

The default dynamic port range might include port 1433. You can check it with netsh int ipv4 show dynamicportrange tcp.

  ...if you happen to have a client (think Windows OS system services) that starts BEFORE SQL Server does, it could use TCP/1433 for its client socket.
  ...
  So, how do you fix and prevent a situation like this? There's another feature within the OS that allows you to tell the OS the TCP ports in the Dynamic Port Range that should be EXCLUDED. All that is needed is to run the following with an Administrative Command Prompt:

netsh int ipv4 add excludedportrange tcp startport=1433 numberofports=1 store=persistent

  After that setting is made, the OS will not give any clients that port. Services can listen on it still, but you prevent clients from getting it when asking the OS for a dynamic port.

IPv6 version

The original poster indicated that even though the logged error mentioned IPv6, his problem turned out to be with IPv4. But if it had been an IPv6 port conflict, the same process can be followed for IPv6.

netsh int ipv6 show dynamicportrange tcp

netsh int ipv6 add excludedportrange tcp startport=1433 numberofports=1 store=persistent


How do I create an Oracle 11g user with read access to all tables/schemas?

You can grant SELECT ANY TABLE, however, that is seen by some as "excessive" privileges, and should be questioned.

On the plus side, Oracle 11 keeps key system views hidden even from that grant .. so it's not nearly as bad as it once was ;)

It would be best to build ROLEs and grant the SELECTs to the required tables to the ROLEs ... then grant the ROLE to your users as needed.

If you haven't yet, you should probably read over this guy:

https://docs.oracle.com/cd/B28359_01/network.111/b28531.pdf


What is the timeout of a query in SQL Server 2008

To answer the specific question: by default there is no timeout while running a query against the database engine. It will happily wait or days for an operation to complete whether the delay is due to the actual statement taking a long time to process or due it being sat waiting for other connections to release relevant locks.

Standard connection libraries like ADO implement timeouts, after which they will tell SQL Server to cancel the current operation and drop the connection.

There are timeout settings governing various processes (login processes, or situations where the database engine itself is asking a remote resource to perform some action, and such) but these do not affect queries given direct to the database engine.

For more specific help you'll need to add further details (the code of your query, the exact error message, perhaps some notes on your structure (is "#tablename# the name of an actual table in your DB?) so we can see if there is something there that would cause the error you have seen.


Maintenance Plan produces BAK files I cannot use

Maintenance plans are certainly one way to perform database backups and a multitude of customers use them with success each day. My guess here, looking at the inclusion of the shrink job and the reorganize followed by rebuild that the maintenance plan was just quickly created without understanding the tasks.

When you say you have a "backup database" task - what type of backup is it? My assumption in this answer is that you are taking Full Backups and then Taking Differentials and possibly Log backups.

The problem likely isn't your backup, per se (though I suggest you spend some time understanding recovery models and the backup command at books online as you are likely not creating your backups in the most optimal order for your recovery intent).

I believe you are likely having an issue because you are restoring in this order:

1.) Restore the full database backup (.bak) first keeping all the defaults
2.) Attempt to restore the differential or the log backup.

Or you are just attempting to restore the log or differential on top of an existing database.

If this is the case - the issue is understanding how recovery models work and the proper restore sequence. (One more link to read - about transaction log restores) but in a nutshell:

If you plan on restoring any differentials or log backups, you have to first restore the full backup AND you must specify "WITH NORECOVERY" when restoring that full bakup. If you keep the default (WITH RECOVERY) then SQL Server performs the necessary recovery processes to make your database operational (basically rolls forward transactions that were done and consistent at the time of the backup, rolls back ones that weren't). If you want to restore additional backups (only the latest differential or each log file between the last full or differential backup and the time you want to restore to) then you must specify WITH NORECOVERY for each restore until the last one where you specify WITH RECOVERY finally.

I'd definitely spend some significant time with the Microsoft Books Online links I sent here and click on the links they refer to as well to best understand backup and recovery.


Index creation will be show in the mongo error logs. You can monitor the progress of the index creation. 52 hours is very slow. Can you post the masked error log here?


Correct Selection of SQL server instance db owner login name

Not sure about your specific case, but I would check to see if the login you're using is a member of the dbo role in the database you're trying to connect to.  You can query role membership using this query:

use YourDatabase;

select
    r.name role_name,
    u.name db_login,
    u.type_desc
from (select * from sys.database_principals where type = 'R') r
    join sys.database_role_members rm on (r.principal_id = rm.role_principal_id)
    join (select * from sys.database_principals where type != 'R') u on rm.member_principal_id = u.principal_id
where r.name = 'db_owner'
order by r.principal_id,u.type_desc,u.name;

If you need to add the login to the DBO role:

Use YourDatabase;
exec sp_addrolemember 'db_owner','FOO\bar';


SQL Server 2005: Full-text search on a multi-lingual table

When creating the index, it is possible to specify a "LANGUAGE" argument.  Within the description of the "LANGUAGE" argument is the following:

  For non-BLOB and non-XML columns containing text data in multiple languages, or for cases when the language of the text stored in the column is unknown, use the neutral (0x0) language resource.

Then, when doing a search using FREETEXT or FREETEXTTABLE, once again use the "LANGUAGE" argument.  Microsoft defines the "LANGUAGE" argument as follows:

  Is the language whose resources will be used for wordbreaking, stemming, and thesaurus and noise-word removal as part of the FREETEXT query. This parameter is optional and can be specified as a string, integer, or hexadecimal value corresponding to the locale identifier (LCID) of a language. If language_term is specified, the language it represents will be applied to all elements of the search condition. If no value is specified, the column full-text language is used.

So, I will use one catalog and one index on the multi-lingual table using a neutral language resource.  Then I'll use the appropriate language setting when searching so everything is parsed correctly.


format() is a nondeterministic built-in string function... right?

The FORMAT documentation has now been updated (in response to your Connect item) to say:

  The FORMAT function is nondeterministic.

Likewise, String Functions (Transact-SQL) now includes:

  All built-in string functions except FORMAT are deterministic.


Most of the time is spent aggregating and sorting the data.
I don't know how the tables are partitioned, but if the partitioning key is part of the join condition or the aggregate can be calculated partition by partition, you might benefit by setting enable_partitionwise_aggregate and enable_partitionwise_join to on.
There is not much more I can say with that little information.


How to select the latest record of each hour in a day?

That's a groupwise maximum.
You may plump for a null-self-join:

SELECT  ir1.*
FROM inverter_readings IR1
LEFT JOIN inverter_readings IR2 ON
DATE(IR1.READING_ON) = DATE(IR2.READING_ON) AND
HOUR(IR1.READING_ON) = HOUR(IR2.READING_ON) AND
IR1.READING_ON  < IR2.READING_ON
WHERE IR2.id IS NULL

sqlfiddle demo.


SQL Server 2008R2 minimum RAID/Disk config

For a 4 bay configuration with a high insert/update workload I would go with one of these:

1) Spindle only

One RAID1 for the log. Back this up with a a few hundred MB of write cache
One RAID1 for the data files and tempdb

Rationale: you will mostly be writing to the log. Latency of log is king for OLTP and it doesn't get much better than a RAID1 with a write cache. You can use SATA - which should give you at least 80MB/sec sequential writes at latency less than 1ms.

2) SSD plus spindle

One RAID1 for log with same configuration as the above.
One RAID1 for data files and tempdb

Rationale: This config will favour higher tempdb load and would be good if you are skimping on DRAM (as this will cause checkpoint activity to go up and drive read I/O)

3) Pure SSD

Put them in a RAID5 if you are not worried about wearing them out.
If you are using consumer grade SSD - put them in a RAID10.

Rationale: RAID5 with 4 x SSD is fast enough for nearly all purposes.

Everything goes on same LUN

4) 8 bay, spindle only config

Put 2 disks in RAID1 for the log file.
Rest goes into a RAID10 or RAID5 (RAID10 if you are worried about tempdb)

Overall: always prefer a 2 spindle RAID1 with a write cache for logs. It's plenty for 99% of all cases (80MB/sec for SATA and up to 120MB/sec for SAS).


You don't seem to need the ticket table. The output does contain the ticket.id column, but it is always same as ticket_history.ticket_id, which is also included in the output.

In view of the above, the following query reads only ticket_history. It uses conditional aggregation to build the output you are after:

SELECT
  ticket_id,
  MIN(id) AS id,
  MAX(CASE name WHEN '%%Close' THEN NULL ELSE create_time END) AS open_time,
  MAX(CASE name WHEN '%%Close' THEN create_time ELSE NULL END) AS close_time
FROM ticket_history
GROUP BY
  ticket_id
HAVING COUNT(name LIKE '%\%Close'            OR NULL) = 1
   AND COUNT(name LIKE '%\%Raw\%% %\%new\%%' OR NULL) = 1
;

The HAVING clause makes sure a ticket has both open and close rows. The OR NULL trick is explained in this Stack Overflow answer:

Why do I need “OR NULL” in MySQL when counting rows with a condition

The id column is derived as MIN(id) on the assumption that a Close row will always appear after the corresponding Open one, and therefore the latter's id will always be lower than the former's.

The open_time and close_time values are obtained using conditional aggregation. Depending on whether the name column matches the '%\%Close' pattern or not, the create_time column is aggregated as either close_time or open_time.

You can use this SQL Fiddle demo to try the solution.


MySQL InnoDB Deadlock For 2 simple insert queries

HERE ARE THE FACTS

Here are the two INSERTs

insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition, nextClubId, account_id) values (0, '2014-12-23 15:47:11.596', 180, 4, 181, 561)
insert into PlayerClub (modifiedBy, timeCreated, currentClubId, endingLevelPosition, nextClubId, account_id) values (0, '2014-12-23 15:47:11.611', 180, 4, 181, 563)

Here are the two lines from your SHOW ENGINE INNODB STATUS\G

RECORD LOCKS space id 49735 page no 4 n bits 72 index `UK_cagoa3q409gsukj51ltiokjoh` of   table `db`.`playerclub` trx id 19896526 lock_mode X insert intention waiting
RECORD LOCKS space id 49735 page no 4 n bits 72 index `UK_cagoa3q409gsukj51ltiokjoh` of   table `db`.`playerclub` trx id 19896542 lock_mode X

OBSERVATIONS

You doing an INSERT with two different account_ids: 561 and 563.

They are unique and should not have issues, right ? WRONG !!!

Due to InnoDB's Clustered Index, there can still be a deadlock. Why ?

Look back at your two INSERTs. The PRIMARY KEY on id in not specified. It must be auto generated. Any key other than the PRIMARY KEY (unique or non-unique) will have the PRIMARY KEY attached.

Please note the MySQL Documentation on how a Secondary Index and a a Primary Key are intertwined:

  All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.

  If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key.

Although you are inserting account_id 561 and 563, under the hood you are insert 561-(id) and 563-(id) into the UK_cagoa3q409gsukj51ltiokjoh index. The PRIMARY KEY becomes the bottleneck because the Secondary Index has to wait until the id column is auto_generated.

RECOMMENDATION

You have a table with two candidate keys

PRIMARY KEY on id
UNIQUE KEY on UK_cagoa3q409gsukj51ltiokjoh

Since both are BIGINT, you could increase performance and have a smaller PlayerClub table by getting rid of id and still maintain uniqueness because of UK_cagoa3q409gsukj51ltiokjoh as well as avoiding this deadlock situation.


Slow search by LIKE and other values (1 million rows+)

Not sure about mysql but in mssql like '%val' is a full scan

Multiple keywords in a varchar(200) column is probably not the best design.  Spit the keyswords into separate words into a separate table with a FK back to company.ID.  So each word would be a row. Then you can search on keyword.value = 'xxx' and use an index.


Explicitly granting permissions to update the sequence for a serial column necessary?

You most probably need:

GRANT USAGE ON SEQUENCE my_table_id_seq TO writer;

Per documentation:

  USAGE
  ...
  For sequences, this privilege allows the use of the currval and nextval functions.

nextval() is the reason you need the USAGE privilege on the sequence for a table with serial column.
Details in this related answer on SO.

Since a sequence is a special kind of table (and for historical reasons) GRANT ... ON TABLE works on sequences, too. But you do not normally need that at all.


SQL Server 2008 R2 Standard with SSIS Enterprise

No. The only way to license SSIS enterprise features is to get SQL Server Enterprise Edition.  Your other option is to implement the functionality in some other way such as a stored procedure, TVF, or .Net code embedded in the SSIS package (e.g. a script task).


Trying to work out how to escape a hyphen in an XML Tag in Azure SQL Analytics

...but KQL chokes on the hyphen and I don't know how to escape it to make it work

Please see: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/schema-entities/entity-names#identifier-quoting
for example: DeadlockXML.deadlock['process-list'].waitresource


Best practices for transferring data across long, wide tables

Have you considered adding a ''rowversion'' column to your source tables?
The simplest way to think about rowversion (formerly timestamp in SQL Server) is as a database-wide autonumber.  Every INSERT and UPDATE increments the database-wide rowversion.  Any table that has a rowversion column defined has that column's value set to the database rowversion when the associated row is INSERTed or UPDATEd.
Here's a brief algorithm for how you could use this:

Save the database-wide rowversion value (available via @@DBTS; i.e., DataBase TimeStamp)
Process your data into your Data Warehouse
Next time through, only process rows where the rowversion value is greater than the previously saved @@DBTS

Note that while you should not miss any rows that should be processed, you may re-process rows with no actual change in data.  This is because the rowversion is incremented with every UPDATE statement, even if the data is "UPDATEd" to its current value and not actually changed.
Also, it won't help identify which columns might have been changed.  As the name implies, it works strictly at the row level.


Filter and then sort products by availability

This is a "top n per group" problem but within the limits of MySQL.
You need to GROUP first then join back

SELECT
   F.*
FROM
  (
  SELECT
    MIN(availability) AS MINavailability, id_product
  FROM
    foo
  GROUP BY
    id_product
  ) X
  JOIN
  foo F ON X.id_product = F.id_product AND X.MINavailability = F.availability
ORDER BY
    F.availability, F.id_product


Mysql Sharding. Application Layer vs Mysql Layer

Sharding does not speed up searching.
Sharding is useful when you hit a write-scaling limit.  Most social media apps end up with some form of sharding to handle many thousands of writes per second.
MySQL has no sharding capability.  However, add-ons, such as Spider and various Proxies can help.
Or you can build it into the application layer.  In this case, each client must first figure out which database shard to talk to, then talk only to that one.  This assumes all the activity can be handled in a single shard.
Sure, the application layer may make use of database tables to keep track of what shards exist, and which item is in which shard.


t-sql or powershell to return currently-configured snapshot folder for transactional replication SQL 2012

The snapshot folder is configured per Distributor.  The stored procedure sp_helpdistributor returns a column directory which is the snapshot folder for a specified distributor.


Going backwards just feels wrong to me.

With only two data centers you could also implement identity ranges. Unless you cycle through identity values at an alarming rate, there is no reason you can't have:

-- Data center 1
CREATE TABLE dbo.Table
(
  ID INT IDENTITY(1,1) PRIMARY KEY
  -- , ...
);

-- Data center 2
CREATE TABLE dbo.Table
(
  ID INT IDENTITY(1000000000,1) PRIMARY KEY
  -- , ...
);

This would allow to generate 1 billion (well, 999,999,999) values in data center 1 before there is any danger in collision with data center 2. And you could add a CHECK constraint in data center 1 to prevent overlapping values, depending on how you prioritize errors vs. duplicates. You could also implement a recurring job that periodically checks how close you are to the lower bound of the other data center, if you're concerned that you'll really generate over a billion values in either data center in the app's lifetime (never mind yours).

If ~1 billion is not enough, there are two other alternatives to give more room to breathe:

-- Data center 1
  ID INT IDENTITY(-2147483648,1)

-- Data center 2
  ID INT IDENTITY(1,1)

This would give each data center over 2 billion non-overlapping values, with no danger of collision until data center 1 approached 0. And if that's still not enough:

-- Data center 1
  ID BIGINT IDENTITY(-9223372036854775808,1)

-- Data center 2
  ID BIGINT IDENTITY(1,1)

Or if you want to keep all values > 0, you could split the positive range roughly in half (you can be a little more precise than this if you're more pedantic than me).

-- Data center 1
  ID BIGINT IDENTITY(1,1)

-- Data center 2
  ID BIGINT IDENTITY(4611600000000000000,1)

I don't even know how to say that number, but it's ginormous. And in reality you'd have to work extremely hard on a very fast computer to use up all those values by the time your great great great great great grandchild graduates from college. And if you are on Enterprise Edition you can use data compression so that you don't pay the hit for all 8 bytes, at least in data center 1, until you exceed 2 billion there.

In a system I managed I did this a slightly different way - we had multiple web servers with Express instances that were responsible for generating ID numbers that needed to be unique in the enterprise. So we just set up a sequence generator on each machine (they didn't need to actually store the value) using a BIGINT IDENTITY column. We had < 9 servers, so they were all seeded like this:

-- WEBAPP0001
ID BIGINT IDENTITY(10000000000,1)

-- WEBAPP0002
ID BIGINT IDENTITY(20000000000,1)

-- WEBAPP0003
ID BIGINT IDENTITY(30000000000,1)

...

When the values were used and then merged in the central system, not only were we guaranteed to not have any duplicates, but it was also easy to instantly recognize which web server they came from (which was sometimes useful in debugging) without introducing any composite key requirements. And we had no concerns that any one web server would ever generate more than 10 billion values.

Many people would go with a GUID in this scenario, but I think there are several strong arguments against that approach.


how can I install Microsoft SQL Server 2005 Express Edition on Windows 8.1

As per Microsoft Official document

SQL Server 2005 (the release version and service packs) and earlier versions of SQL Server are not supported on Windows Server 2012 R2, Windows Server 2012, Windows 8.1, or Windows 8. You will receive a warning in the Action Center if Windows 8.1 or Windows 8 detects an instance of SQL Server 2005.

To resolve this issue, upgrade or remove the existing instance of SQL Server 2005.

Please read below link

http://support.microsoft.com/kb/2681562/en-gb

EDIT:

Click on run the program without getting help. It will proceed. Although installation is not supported it would not block you from installing but you are doing it at your own risk. If you want a supported scenario. You can create a VM on windows 8 machine and can install windows server 2003 SP2 on it and then install SQL Server 2005 with SP4 to run supported scenario.


mysql ERROR 1248: Every derived table must have its own alias

As far as I know the part:

{...}
SELECT * FROM (SELECT vBooksDetails.* FROM
{...}
);

could have caused the error - the topmost select wasn't aliased.

Please try this version of your last big insert:

INSERT INTO
    temp_MatchList
SELECT
    vBooksDetails.*
FROM
(
    SELECT DISTINCT *
    FROM
    (
        SELECT
            classmaster.ClassID AS ClassID,
            CDisplayName,
            subjectmaster.id,
            SubjectID,
            subjectmaster.DispName,
            BookName
        FROM
            classmaster
            JOIN subjectclasslinkage
                ON classmaster.ClassID = subjectclasslinkage.ClassNumber
            JOIN subjectmaster
                ON subjectclasslinkage.SubjectId = subjectmaster.id
            LEFT JOIN
            (
                SELECT *
                FROM vBooksDetails
                WHERE
                (
                    ((p_LinkedBoard = 0 )
                      OR (BoardID IN (SELECT boardname FROM userboards WHERE userboards.usrid = p_UsrID )))
                    AND ((p_LinkedBoards = 0 ) OR (BoardID IN (SELECT BoardID FROM schoolboards )))
                    AND EXISTS (SELECT * FROM vBoardInfo WHERE ID = BoardID )
                    AND vBooksDetails.Status = 'A')
            ) lessonsyllabus
            ON subjectmaster.id = lessonsyllabus.SubjectID
               AND lessonsyllabus.ClassID = classmaster.ClassID
    ) TAB
    WHERE
        TAB.BookName IS NULL
) TAB_MISSING
    JOIN vBooksDetails
        ON TAB_MISSING.ClassID = vBooksDetails.ClassID
           AND TAB_MISSING.SubjectID = vBooksDetails.SubjectID
           AND vBooksDetails.BoardID = 3
WHERE
    vBooksDetails.Status = 'A'
    AND ((p_LinkedClasses = 0 )
         OR ( vBooksDetails.ClassID IN (SELECT classid FROM userclasses WHERE UsrID = p_UsrID )))
    AND (( p_LinkedSubjects = 0 )
         OR (vBooksDetails.SubjectID IN (SELECT subjectid FROM usersubjects WHERE usrid = p_UsrID )))
-- AND ((@LinkedBoard = 0) OR (BoardID IN (SELECT boardname FROM userboards WHERE usrid=@UsrID)) )
    AND ((p_SchoolBooks = 0 )
         OR ( vBooksDetails.Id NOT IN ( SELECT BookID FROM SchoolBooks)));


Entries in suspect_pages but checkdb shows no error

So I wrote to Brent Ozar and this is the answer I got from him. It's the best answer I've got so far so I'll put it here:

  here's exactly what I do when SQL Server reports corruption:
  https://brentozar.com/go/corruption

  Suspect_pages doesn't do false positives, but the corruption may have been repaired since (or may have only been corrupt temporarily, like if storage had a transient error when you went to go read it the first time.) Definitely follow that checklist and star tthe support call with MS.

  Hope that helps!
  Brent


MariaDB - Changing explicit_defaults_to_timestamp

open you my.conf/ini
find the section [mysqld]
and add explicit_defaults_for_timestamp = 1
sometimes you need instead
explicit_defaults_for_timestamp = ON
You can check after restarting mysql with
SHOW GLOBAL VARIABLES LIKE '%timestamp%'; if it is enabled
But this is a not standard option and could be removed at some point


How I can impersonate a login with securityadmin server role

The bad news is that the EXECUTE AS clause (which you are using), as opposed to the statement, only works for Users (Database-level), and not Logins (Server-level).

BUT, the good news is that you don't need to use impersonation to solve this issue. Instead you need to use module signing, and you do that via the following steps (all done in [master]):

Create a Certificate (be sure to specify ENCRYPTION BY PASSWORD = 'password')
Create a Login from that Certificate
Grant that Login whatever permission you want, or add it to whatever Role is appropriate
Sign that Stored Procedure (i.e. SPU_CAMBIO_PASSWORD), after removing the EXECUTE AS clause
Backup that Certificate to files (a .cer file for the Certificate and a .pvk file for the Private Key). This is done so that it can be re-created later.
Remove the Private Key from that Certificate. This prevents anything else from being signed with it (so you better do that backup first!).

If that Stored Procedure is ever changed after signing it, the signature will be dropped automatically, thereby losing the association with those special permissions set up for that Certificate-based Login. This is a good thing as it allows you to do a code review (assuming you didn't make the change) before re-enabling the permissions. In this case, you would:

Restore the Private Key (using the same ALTER CERTIFICATE command that was used to remove the Private Key).
Sign the Stored Procedure
Remove the Private Key again


Your trigger runs after the updates have occurred on the table. If you update the table again in the trigger a new update is performed. See also the discussion about RECURSIVE_TRIGGERS.

If you want to run code before the update you will have to create an INSTEAD OF trigger.

Whether having to do two updates instead of one will become the bottleneck is entirely subject to the efficiency of the update in your trigger, and of course whether this UPDATE is already on the critical path or not. You probably have a primary key on job id, the pages are hot in buffer pool and you cannot have lock conflicts. So basically is only subject to a) how fast can your log accept writes and b) whether the version store (which serves the deleted and inserted pseudo-tables) can keep up.

If you ask me: such business logic status housekeeping belongs in the application, not in a trigger...


TEMPDB Round Robin is not working!! It is filled up

You likely need to implement Trace Flag 1117 to enable proportional fill (round-robin) growth on your server.  Be aware that TF 1117 affects all databases, not just tempdb.
From technet.com:

Trace flag 1117 (-T1117) can help keep  data files the same size in case DB needs to autogrow. Say you have eight DB data files with an initial size of 1000MB and autogrow of 200MB each. If the t database needs to autogrow, the default behavior is for one of these eight files to grow by the specified increment (200MB in this case). This will happen when all of the files are full, and after one file grows, it will be about 83% full (1000MB allocated from 1200MB total). The SQL Server proportional fill algorithm will direct new extent allocations to the file with the most available space, so new extents will be written to the one file that just grew. This defeats the benefit obtained by striping tempdb IO activity (especially extent allocations and de-allocations) across all of the available files.
When -T1117 is enabled, it will cause all the files in a filegroup to autogrow together by their specified autogrow increment. In the DB autogrow scenario described above, instead of one file growing by 200MB, all eight files will grow by 200MB when an autogrow event occurs. This means that all the files will have about the same percent of free space after autogrow, and the benefits of evenly distributing IO will be preserved.
It’s important to understand that –T1117 applies to the entire SQL Server instance, not just to one DB, and it affects all files in the same filegroup in a database. This means that if there are other databases that have multiple files defined for the same filegroup, and an autogrow event occurs in that filegroup, all of the files in the filegroup will autogrow at the same time (by the autogrow increment specified for each file). If there are multiple filegroups in the same database, an autogrow event that occurs in one filegroup will not affect the other filegroups. For a filegroup allocated in a single file, -T1117 has no effect.
When deciding whether to enable –T1117, consider the instance-wide implications and how it may affect other databases that have multiple files for the same filegroup. To avoid blasting your harddrives it is recommended to configure the maximum size of your database files. Always the best option is to presize your files to the assumed maximum.

You may also need to implement trace flag 1118 if you are experiencing tempdb contention.  See this question for more info on that.


MongoDB disk operations with journaling disabled

There are two primary methods for WiredTiger to ensure the durability of your data: journaling and checkpointing.

Checkpoints store the valid and consistent state of the whole database. Should the server crash for any reason, it can restart from the last checkpoint in a consistent state. WiredTiger by default checkpoints the database every 60 seconds.

Journals store the writes that happened in-between checkpoints. If the database crashed between checkpoints, the journal data will be replayed on top of the last known good checkpoint. The journal is persisted to disk every 50 ms. The journaling process is optimized to be a lot more lightweight compared to checkpoints (and it also does a lot less work), so it's typically much faster than a checkpoint.

If journaling is disabled, the behaviour is slightly different between a mongod running in standalone mode or in replica set mode:

MongoDB 3.6.4 standalone with journaling disabled: only the complete state of the whole database is written to disk on every checkpoint. That is, if your database crashes between checkpoints, you can potentially lose the last 60 seconds of your writes (if using default checkpoint timing values).
MongoDB 3.6.4 replica set with journaling disabled: by design, a replica set requires more durability since it must record every operation in the oplog and ensure that the oplog is safely persisted. Thus, it will perform a checkpoint on every write instead. This will slow down the replica set tremendously.

The tradeoff you make by disabling journaling:

In a standalone deployment, since the main goal of a database is to persist your data, limiting disk usage (space or IOPS) will result in more data being lost between allowed writes. However, it's not recommended to run a standalone MongoDB for production purposes.
In a replica set, the write process is considerably slowed down due to the need to persist the oplog. This will also make the deployment use more disk IOPS. In the future versions of MongoDB, running a replica set with no journal will not be allowed (see SERVER-30347).

For more information regarding journaling, please see the Journaling Process page.


Replicating a remote MySQL database to MS SQL Server 2008

Personally I'd pull into MS SQL method vs. the push from MySQL method.
Why? Well Windows has 32bit and 64bit MySQL ODBC drivers ready to go and setting up a linked server is trivial. I have plenty of MySQL servers linked to from MS SQL. Also, connecting to MS SQL from linux/unix is not always great and you're ususally not able to use all features. FreeTDS has limitations; you may hit them sooner rather than later so why not just skip it. This all assumes you're running MySQL on *nix. If not, it gets a little closer to 50/50 but I'd still choose pulling from MS SQL as it sounds like it's not the "live" database thus putting the load on it for any ETL or processing is more ideal.
The GoldenGate solution sounds interesting, but I'm sure it's not free.

Considering I've setup this sort of scenario with both MySQL and Oracle databases replicating to MS SQL I'll provide some tips that have worked best for me:

If you can, do your best to determine how you can ensure you're only bringing across delta changes. Merge commands can help with this. Truncating a table and then inserting it all in again bloats your log, uses network bandwidth, and just generally wastes time.
If dealing with lots of data be sure to break the transactions up so as not to require a massive log file. Use explicit commits or checkpoints when you've reached a step you know you won't need to roll back from.
If the MSSQL db will be for reporting only, do ETL work there so as not to impact the MySQL server. Use a staging database or schema + filegroup to make things easy.
Break the data importing into steps. This makes it easy to re-start the import where it failed and/or troubleshoot. An all or nothing approach becomes annoying quickly.
Use variables wherever possible to assist the remote db with query plans & index use. Also pay attention to what sort of transaction isolation you're in on the host box and what impact the "replication" queries will have. You don't want to block writers on a live db if you're just pulling data over for reporting or sandbox use.

Hope the tips help!


Moving Reporting Processes from Access to MySQL

Once I discovered MySQL, Excel was relegated to being just a fancy calculator.  It will take you some time to understand SQL, but in the long run, it will probably be well worth it.  If you end up with performance problems, you might need to avoid Views.


What intersection do multiple inner joins return?

No, the two queries are the same.

Since A.ID = B.ID and A.ID = C.ID, then by definition B.ID = C.ID.

For any given ID x, if there's a row in A with the ID and a row in B with the ID, but no row in C with the ID, then the row(s) from A and B will be ignored, because both joins are INNER JOINs. Same holds true for A-C-B and B-C-A, of course.

So, when all the JOINs are INNER JOINs, the only way to get NULL for a given column is if that column is NULL in an existing row - you can't fill in a row of NULLs in one table because there's no matching row, as the lack of a matching row in one INNER JOINed table means the rows in the other tables aren't valid.

In other words - For a given row in B, if there's no matching row in C, then it's also true that there's no match for the rows in A JOIN B that use that B row. That B row (and any A rows that match it) won't be included because a row has to exist for all INNER JOINed tables.

You asked if anything changed if the query was A INNER JOIN B RIGHT OUTER JOIN C or A LEFT OUTER JOIN B INNER JOIN C.

It can change.

Under normal circumstance, joins will be evaluated from left to right (that is, from the first listed join to the last).

If for some reason you must modify that behavior, you do so by moving the position of the ON clause. Depending on your DBMS, you may also have to add parentheses to indicate the order in which the clauses should be evaluated (I tried MS SQL and MySQL; MySQL required the parentheses to work, MS SQL allowed them but did not require them). I've included the parentheses in my examples below that don't evaluate left to right, to help make that clear.

Examples:

A INNER JOIN B ON <condition1> RIGHT OUTER JOIN C ON <condition2>

This would first join A and B (requiring a match on <condition1> to associate rows from the two tables), then perform the right outer join to C (where all rows from C that meet the WHERE condition will be returned, and will be associated with any rows from the A-B join where <condition2> is met.
A INNER JOIN (B RIGHT OUTER JOIN C ON <condition1>) ON <condition2>

This, on the other hand, would join B and C (including all rows from C, that meet the WHERE clause, associated with any rows from B that meet <condition1>. Then, the results of the B-C join are inner joined with A, associated where there is a row from A and a row from B-C that match based on <condition2>. Note that here, you will get different results for A.id - B.id and A.id = C.id, as all rows will have a non-NULL C.id, but B.id will be NULL where there was no matching row from B.
A LEFT JOIN B ON <condition1> INNER JOIN C ON <condition2>

With the ON clauses in the usual places, this is evaluated left to right. So, first we taken all rows from A, associating them with any matching rows from B based on <condition1>; then, we do an inner join of the results of the A-B join with C, returning only the rows where a row from A-B matches a row from C based on <condition2>.
A LEFT JOIN (B INNER JOIN C ON <condition1>) ON <condition2>

Here, we're again forcing the B-C inner join to happen first, so we get rows when a row from B matches a row from C based on <condition1>; then, we take the results of the B-C inner join and match that to A (returning all rows from A, with any matching rows from B-C based on <condition2>

Here's a couple of SQLFiddle links showing the results of these joins as you move around the ON clauses: one for MySQL and one for MS SQL Server.

NOTE: I've said nothing about how this works with non-ANSI join syntax. If you don't know what I'm talking about - thank your lucky stars and move on. I don't know precisely how the precedence works with that syntax. I'd expect it to go through the list of tables, left to right (first mentioned to last). Obviously, since there are no ON clauses, those have no impact. There may be ways to force the order there to (perhaps parentheses again, presumably in the FROM clause?), but I don't know the answer. As most people (myself included) would strongly recommend against trying to use that syntax for your JOINs, I'll simply use that to avoid the question :-).


Check, if you did not run out of disk-space (this is the first step, check the tmp file)

From the comments, You seem to have a too low maximum size for your innodb_data_file_path, You cannot host more than 512MB of data in all innodb tables combined.

I think you should switch to an innodb-per-table scheme using innodb_file_per_table.


If the multiple use of NVARCHAR(20) bothers you, change the type to INT and adapt the Code table to have two columns: ID (INT) and Name (NVARCHAR(20)).


How to set statistics level of a table in postgres?

There is nothing ready-made for a table or schema, because that is seldom useful.
But you can set it for a whole database with
ALTER DATABASE mydb SET default_statistics_target = 1000;

You can also use psql's \gexec to set the statistics target for all columns of a table:
SELECT format(
          'ALTER TABLE %s ALTER %I SET STATISTICS 1000',
          attrelid::regclass,
          attname
       )
FROM pg_attribute
WHERE attrelid = 'pgbench_tellers'::regclass
  AND attnum > 0 \gexec


How do I save functions to individual files in PostgreSQL?

I wrote a simple PHP script using pg_get_functiondef to get the source code and then save it to a file.

$connection = pg_connect("connection string details");
$result = pg_query($connection, "select proname, pg_get_functiondef(oid) from pg_proc where pronamespace=<namespace_oid>");

while ($row = pg_fetch_row($result)) {
    $fp = fopen("{$row[0]}.sql", 'w');
    fwrite($fp, $row[1]);
}


As pointed out by Mr Brownstone (this would just be a comment but it turned out too long) the behaviour you are expecting can be achieved if you define the key as ON DELETE CASCADE.

I would be very very careful with ON DELETE CASCADE, in fact I almost never use it and generally recommend against it. This counts for triggers that take action based upon rows being deleted too. If a naive programmer later implements UPSERT of a parent row by instead performing a DELETE followed by an INSERT (this is a very common pattern in my experience) then the child data is all lost because the delete is cascaded and the rows removed by the cascade are obviously not replaced by the subsequent insert.

Also if your UI allows the user, due to a bug, to try delete something they really shouldn't because rows elsewhere depend upon it, you (or the user) get an error instead of the database silently deleting the wrong thing and its children.

Not having the cascade option turned on sometimes means you need extra work in your data access or business logic layer to manually delete child objects before parents, but I consider this a small price to pay usually.

Caveats: Some consider my position to be one of unnecessary paranoia. Your mileage may vary.


DELETE and INSERT using OPENQUERY to Oracle require long delay between them

This turned out to be an issue with OPENQUERY and the related "inconsistent metadata" issue requiring OPTION (RECOMPILE). Switching to EXEC AT for write queries (UPDATE, INSERT and DELETE) resolved the throttling effect.


Quickest Method To Invalidate Statistics

The most reliable sequence I can find to cause an automatic statistics update is:

Update statistics sampling zero rows
This results in an empty statistic object.

-- Example
UPDATE STATISTICS
    Person.[Address]
    IX_Address_StateProvinceID
    WITH SAMPLE 0 ROWS;

Update the target column(s) in a single row of the table
This increments the column modification counter. The combination of an empty statistics object and an incremented modification counter enables a special case statistics update (it simulates creating statistics on an empty table, then adding a row).

-- Example
BEGIN TRANSACTION;
    UPDATE TOP (1)
        Person.[Address]
    SET StateProvinceID = StateProvinceID;
ROLLBACK TRANSACTION;

Run the query with OPTION (RECOMPILE)
This causes an automatic update of detected stale statistics, even if a matching plan for the query is already present in cache. The resulting statistics update will subsequently cause an optimality-based recompilation for the original cached plan, if it is matched again.

-- Example
SELECT
    A.City,
    A.AddressLine1,
    A.AddressLine2
FROM Person.[Address] AS A
WHERE
    1 = 1
    AND A.StateProvinceID = 54
OPTION (RECOMPILE);

Demo

Using a similar AdventureWorks query as used in jyao's answer, the following script puts everything above together:

DBCC FREEPROCCACHE;
GO
-- Cache a plan for the query
GO
SELECT
    A.City,
    A.AddressLine1,
    A.AddressLine2
FROM Person.[Address] AS A
WHERE
    1 = 1
    AND A.StateProvinceID = 54;
GO
DBCC SHOW_STATISTICS
(
    'Person.Address',
    'IX_Address_StateProvinceID'
) WITH STAT_HEADER;
GO
-- Empty stats object
UPDATE STATISTICS
    Person.[Address]
    IX_Address_StateProvinceID
    WITH SAMPLE 0 ROWS;
GO
-- Perform and rollback a single row update
BEGIN TRANSACTION;
    UPDATE TOP (1)
        Person.[Address]
    SET StateProvinceID = StateProvinceID;
ROLLBACK TRANSACTION;
GO
DBCC SHOW_STATISTICS
(
    'Person.Address',
    'IX_Address_StateProvinceID'
) WITH STAT_HEADER;
GO
-- Run the query again to trigger stats update
GO
SELECT
    A.City,
    A.AddressLine1,
    A.AddressLine2
FROM Person.[Address] AS A
WHERE
    1 = 1
    AND A.StateProvinceID = 54
OPTION (RECOMPILE);
GO
DBCC SHOW_STATISTICS
(
    'Person.Address',
    'IX_Address_StateProvinceID'
) WITH STAT_HEADER;
GO

Output

The DBCC SHOW_STATISTICS results show the original statistics header, the empty header, and the desired updated header at the end of the process:


Postgres 9.5 import to MSSQL Server 2012 r2 verification issue

This is more readable and may perform better:

SELECT date_part('year', month) as year,
       date_part('month', month) as month,
       count(*) as "trandate sum"
FROM (
  SELECT date_trunc('month', to_date(trandate, CASE
             WHEN left(trandate, 4) = '2014' THEN 'YYYY-MM-DD' ELSE 'MM/DD/YYYY' END
                    ))::date as month
  FROM SomeSchema.transaction_detail
  ) td
GROUP BY month
ORDER BY 1, 2

Note that you could install PgAdminIII in your windows desktop, and use its pg_dump (in C:\Program Files\PostgreSQL\9.3\bin), or start the console from PgAdmin (menu Plugins/PSQL Console). Depending on the type of data you're trying to import, a CSV format export file could be a good alternative. Otherwise, an SQL file via pg_dump with individual INSERT statements and processed via MSSQL's osql.exe or sqlcmd.exe tools to run it.


Ordering by this might be slowing it down quite a bit:

DateAdd(second, DateDiff(second, GetDate(), GetUtcDate()), [T3].[EventTime]) AS C18

I would change ORDER BY [C18] DESC to order by [T3].[EventTime] desc to see if that changes the cost.

Your join for dbo.Sites references [T1].[C23] which is hard-coded 0 as [C23] in your [T3] query. So for each row you are returning from [T3] you are returning every row from dbo.Sites.SiteId = 0 without any other criteria. Is that intended?

On another note... your column and table alias choices are frightening.


sp_BlitzIndex will list Unknown for the index name when the table is a heap. It's not saying you have an index named Unknown. The primary key that you added was added as non-clustered, and you don't have a clustered index on the table. A heap is a table without a clustered index.

If you don't want your table to be a heap, you could drop the PK and recreate it as a clustered PK.


Why is the database size larger than sum of table sizes

why database size is a way greater than the sum of table size in a database?

Databases contain lots of other things besides "just" your data.
Indexes are the largest and most obvious.  Every Index contains its key values, possibly some other stuff as well, depending on the Index type, and a pointer to the corresponding  Data record.
Every Table, and every Row within every Table, has some overhead associated with it, over and above the actual Data that you or I care about.
Then there's all the stuff that the DBMS creates for itself, like statistics tables, entire schemas for metadata, "code" objects like procedures and functions, and many, many more.


By the addition join on payments, of which there are two, the productAmount got doubled:

select i.id, sum(distinct pr.amount * pr.quantity) as productAmount,
sum(distinct pm.amount) as paymentAmount
from invoices as i
join products as pr on pr.invoice_id=i.id
join payments as pm on pm.invoice_id=i.id
group by i.id;

Also replaced LEFT JOIN with JOIN as you don't care about non-matching entries.


How to add additional hard drive(s) to existing database

Do I need to add a new entry to Filegroups and then add to Files?

You can do one of two things here:

Add a new filegroup and at least one new data file
Add a new data file to an existing filegroup (PRIMARY in this case)

  Is the PRIMARY type only supposed to be used for the first file?

No, the PRIMARY filegroup can contain multiple data files.

  Once I add a file, does the DB engine automatically decide where to put data or do I have to go into my tables and specify this in some way?

Within the filegroup, SQL Server will manage the data dispersion across the data files through a proportional fill algorithm.  Therefore, you will not need to manually administer where the data goes within a filegroup.  But, if you create an additional filegroup with one or more data files, and you want data to live on that filegroup then you will need to explicitly state that (unless you set the new filegroup as the default filegroup).

For instance:

create table dbo.YourNewTable
(
    col1 int not null,
    col2 int not null
    -- so on and so forth
) on YourNewFilegroup;
go

To find out what your default filegroup currently is:

select
    name as default_filegroup_name
from sys.filegroups
where is_default = 1;

To change the default filegroup:

alter database YourDatabase
modify filegroup YourNewFilegroup
default;
go


Why does performing a delete on my partitioned view result in a Clustered Index Insert?

INSTEAD OF triggers completely replace the triggering action.

The inserted and deleted pseudo-tables represent changes that would have been made, had the triggering statement actually executed. Row-versioning cannot be used for these triggers because no modifications have yet occurred, by definition.

SQL Server modifies the execution plan for the triggering DML statement when an INSTEAD OF trigger exists. Rather than modifying the affected tables directly, the execution plan writes information about the changes to a hidden worktable.

This worktable contains all the data needed to perform the original changes, the type of modification to perform on each row (delete or insert), as well as any information needed in the trigger for an OUTPUT clause.

The Insert in your execution plan represents writing to this hidden worktable. When you capture a post-execution plan for the statement, you will see this hidden worktable being used as the deleted and inserted pseudo-tables.

See my SQLPerformance.com article, Interesting Things About INSTEAD OF Triggers.


How does MySQL resolve schema information?

I found a cause.
All folders under the data directory were hidden attribute.
I changed them then I can see tables and dump data.


Database design: Is there a much better way on this current relationships?

True story: I was a student at my school, then I taught there. Then one of the teachers took my class. You need to account for silliness like this.

And a faculty member or student isn't necessarily a user either.

Use table inheritance.

Faculty : Individual : Party

Student : Individual : Party

Where : means inherits.

Party hasOne User

A contact is a relationship between two parties:

Party hasMany ContactRelationships belongsTo Party

See Ready-to-Use Database models example for reference models.


Deleted config.ini in MySQL cluster by mistake

The file on the FS is only marked as deleted, with the actual deletion happening once all open FDs to it are closed.

This means that if you haven't restarted the management server (or any process that had the config.ini file open), then you can copy the config.ini file contents from that FD which the process still has open.

You can see the open file descriptor(s) with:

lsof | grep config.ini

And here's a one-liner to copy the file contents:

lsof | awk '/config.ini/{print $2 ":" $4}' | tr -d [:alpha:] | sed s/:/\\/fd\\// | xargs -Ifile cat /proc/file > /tmp/config.ini

Alternatively you can try and use ndb_config to dump the in-memory configuration from the management server.

And in the future, you probably should backup the file. :)


Automatic change of "initial size" of a database?

There is nothing that allows SQL Server to modify the initial size of a file automatically, by itself.

The file must have been altered by a sysadmin or someone with ownership of the database.

I expect they were setting the max size of the file and set the wrong option.


MySQL cluster DataMemory and DiskPageBufferMemory parameters

DISCLAIMER : Not SCMCDBA Certified
If you plan to hold data in memory, you need to

increase RAM on all data nodes to 16GB
increase the DataMemory to 6GB
increase the number of SQL nodes to 3 (Makes SQLNodes to DataNodes 1:1)

If you cannot make these changes, then you should offload data to local storage

MySQL Cluster -- Storing table data on data nodes

Here is one of the best answers from Frazer Clement : MySQL Cluster RAM requirement
He states in his answer

When configuring DataMemory and IndexMemory, it is best to limit yourself to somewhat less than the physical memory in each system. Some physical memory should be reserved for the OS and other processes.

There are three MySQL Cluster Gurus in this forum

Frazer Clement
Mat Keep
Andrew Morgan

Checks out their answers !!!


Currently Azure SQL Database only allows to query other Azure SQL Databases or Azure SQL Data Warehouse databases by using elastic queries. Linked servers are not supported on Azure SQL Database. Currently is not possible to query SQL Server instances or other RDBMS from Azure SQL Database.

The new PaaS product named Azure Managed Instance allows to create linked servers but for a limited number of targets. Supported targets: SQL Server and SQL Database. Not supported targets: files, Analysis Services, and other RDBMS. For more information, please read this blog.


T-SQL for Query that can be done in SQL. What do they want?

You don't need the WHERE clause because you are doing an inner join, which ensures you will only get vendors that have an invoice:

SELECT DISTINCT VendorName FROM Vendors V join Invoices I on V.VendorId=I.VendorID

OR

SELECT DISTINCT VendorName
FROM Vendors V
WHERE EXISTS (SELECT 1 FROM Invoices I WHERE V.VendorId=I.VendorID)


Is a value that does not change calculated for each row?

As long as it is run as a single transaction, the value will be re-used on each line, not recalculated.  I'm not 100% sure about Postgres or SQL Lite but I'm fairly confident (98%) they will work the same.

In the past when I was more confused about this question, I would pre-fetch the date value to a variable and then reuse the variable in the where clause.  Now I realize that was pointless unless I have more than one place in a longer set of statements where I would like to use the value.

As a test, move the calculated date value to the select clause and execute against a large table that takes more than a few seconds to return.  You will notice that the first line will have the same date/time value as the last record.


Your use of this term

(Coalesce(xmd.Message_Status, 'Sent') = @Status OR @Status = '')

in your inner WHERE clauses is non SARG-able, so no index can be used. Change both occurrences as shown below, to make this term SARG-able:

(xmd.Message_Status  = @Status
    OR (xmd.Message_Status is NULL AND @Status = 'Sent')
    OR @Status = '')


How to calculate exact month (in days) difference b/w two dates

The problem with your problem (sorry, couldn't resist) is that you haven't properly defined what you expect a month to be. How many months are there between Dec 27 and Feb 28? How about Dec 28 and Feb 28? Dec 29? What about Dec 15 to Jan 13? Dec 15 to Jan 14?

As others have mentioned, DATEDIFF just measures the number of boundaries that have been passed. The following both return 1:

SELECT
  DATEDIFF(YEAR,'20120101','20131231'),
  DATEDIFF(YEAR,'20121231','20130101');

Even though you and I both know that only one day has passed in the first example, and almost two years have passed in the second.

If you want everything from before September 4th (since today is February 3rd), then:

DECLARE @d DATE = SYSDATETIME;

SELECT <cols> FROM dbo.tablename
  WHERE VisitDate < DATEADD(DAY, 1, DATEADD(MONTH, -5, @d));

-- the above evaluates to < '2013-09-04'


Logging and Replaying database updates/inserts SQL Server 2008

Just to close this off, I found that Red gate Data Compare seems to be a great tool that will allow me to simply compare the two databases and in essence replicate the differences.


Choosing between NoSQL and Relational databases while having both flexibility and consistency requirements

To answer your questions directly:

"Since we'll have different sheet structures, is NoSQL a better option than a relational database?"
A. Probably not, unless each of those sheet structures are very tightly related as one application that they'd all be stored in the same few tables. Otherwise it sounds like you just have a series of structured datasets (multiple applications) that can be represented each with their own different set of tables. This is quite normal and can easily be implemented in either a Relational Database Management System (RDBMS) or a NoSQL database, so no differentiation here.

"The users will go on using spreadsheets and may change their sheet structure on the fly. Will this be a lot of trouble for NoSQL or relational databases such as SQLite?"
A. Are the spreadsheets consumers of the data that was saved down to the database, i.e. besides writing to the database do the spreadsheets also need to read back from the database? If so, then changing structures is something you'll need to handle regardless of which type of database you use, the problem just occurs at different layers of the application. With a RDBMS you need a process to handle managing schema changes in the database that have occurred at the client, otherwise your database won't be storing any new data that is entered. With a NoSQL database, your problem exists in the other direction, when pulling data back into the client's new schema but the instance of data from NoSQL is still out of date and doesn't match the client. With Excel, it may actually be more forgiving though and just leave those columns blank. But keep in mind Consistency is also different in NoSQL should you choose to distribute the data across multiple nodes. It is eventually consistent, which means one user of a spreadsheet may receive a different version of the schema vs another user of that same spreadsheet, if the data from the newer structure hasn't been replicated to the other node yet. More of this on the answer to your next question.

"NoSQL does not support the ACID of relational databases, does it mean that data may get corrupted when multiple users work on the same record? We normally would like consistency."
A. NoSQL follows the BASE principles (and is bound by the CAP theorem), which means unlike a RDBMS that follows the ACID principals, it is eventually consistent. This means a change to the database, including the change of the data itself, is not guaranteed to instantly be replicated across all nodes of which that database is distributed across. But it will be eventually replicated and consistent across all nodes. In regards to your question, that does not mean data is any more likely to be corrupted (from the system's perspective) if you use a NoSQL database vs a RDBMS and multiple users are changing the same record. Rather, it just means that the same record could be in multiple different states at the same time between different nodes, until the last in (generally) change becomes synchronized across all nodes of that database so that it becomes eventually consistent. A RDBMS generally employs an algorithm with a locking mechanism to ensure that consistency is immediate and that the same record is never in more than one state at a time.

"If we maintain an optimized database but still want to be able to export selected portions or entire as CSV or JSON files? Will NoSQL or relational work better?"
A. At a high level, I don't believe you'll find any differences here between either. It's an equally achievable goal for both types of database systems, and the differences will be very low level detail specific as you're getting into it. Some RDBMS natively support storing and/or exporting the data as JSON and CSV formats, and typically NoSQL databases already store (or retrieve) data as JSON and there should be ways to convert the results to CSV with modern implementations of those databases.

"How to handle access control from scratch if we do it online? Will this affect the choice of database tech?"
A. By "doing it online" do you mean a cloud-based solution? If so, this shouldn't change anything from a security standpoint. Either way, all modern database systems have a way to setup accounts either directly in the database system itself or mapped to another security system. For example, the Microsoft SQL Server database system offers the ability to either create dedicated Logins to the database or leverage Active Directory such that you can define access control within the database on top of Windows Users and Groups, and this still functions the same even when using Azure (Microsoft's cloud solution) to host your database. Similar ideas apply to other modern database systems too.

"For this type of system, should we hire a database expert dev and/or maintainer? Can we build on some free/open-source infrastructure that requires the least amount of backend/frontend, security tech R&D?"
A. You sound like you have a lot of questions and thinking to do around the problems you're planning to solve, so if it's possible, hiring someone with strong database experience (especially one who has worked with both RDBMS and NoSQL) would be only of a benefit to you. Currently with the information you provided and based on the questions you've asked, I don't objectively see any distinguishing factor to choose one type of database system over another. My only subjective opinion would be to go with a RDBMS since it sounds like your data is structured at least (even if it does change - that's fine in a RDBMS) and if the eventual consistency of NoSQL can be an issue for your use-cases. If you're looking for an open source and free RDBMS then I'd recommend looking into PostgreSQL.


Can I run a Report Server and a Mirror server from a working Log Shipping secondary instance?

The question is confusing but I think what you're trying to say is that you want to access the data at City-B, using your existing log-shipping setup.

With regards to the log shipping, at City-B, there is an option [RESTORE ... WITH STANDBY] which will leave the database in restoring mode, however in a READONLY state so it's accessible for SELECT queries. The main restriction with this approach, is that you can only restore a log when all users are out of the database. Depending on your log shipping restore schedule (very frequent/this may not work then), you'd have to kill all the
connections prior to restoring the next log. Test this and re-do your log shipping config.
Depending on your version of SQL, perhaps explore using DB Snapshots at city-B - need to research how this would interact log shipping.
You could install/configure Reporting Services at City-B (which would create the two system report databases). Reports could then query the databases (at city-b) which are involved in log-shipping (but read-only) as per my first point.
If you going to create another instance on City-B, then how about creating a job that performs a backup of databases on City-B (instance1/logshipping) and restoring on City-B (instance2/Reporting Instance) - which you can then access as required.
Mirroring does not let you read from the mirrored instance.

I hope this helps.


AWS Aurora MySQL Serverless CPU utilisation is above 10 even it is not processing any requests

Run show processlist or an analogous command. 10% doesn't seem unreasonable but the database is obviously doing something at this time.

It seems as though this product puts a large emphasis on replication and durability. From the product description, "Amazon Aurora is designed to offer greater than 99.99% availability, replicating 6 copies of your data across 3 Availability Zones and backing up your data continuously to Amazon S3" https://aws.amazon.com/rds/aurora/

What I'm getting at is that 10% cpu usage could likely be the result of synchronization/replication thread(s).


How do I GRANT for all tables across all schemas

If you need to do this only once, the quickest way is probably the following.

Find all the schemas you want to touch by querying, for example, the pg_namespace system catalog:

SELECT nspname
FROM pg_namespace
WHERE nspname LIKE 'shard%'; -- tweak this to your needs

Then we can expand the query output into GRANT statements:

SELECT 'GRANT SELECT ON ALL TABLES IN SCHEMA ' || nspname || ' TO foo;'
FROM pg_namespace
WHERE nspname LIKE 'shard%';

This will give you an enormous amount of statements, which you can just copy over and run.  As you have a big number of schemas, the easiest is possibly the following (running it in psql):

\copy (SELECT 'GRANT ...' ...) TO some_local_file.sql

\i some_local_file.sql

Of course, if you prefer, you could do this using dynamic SQL, too - I find the above solution slightly better, as it leaves a file behind with the actions taken, that can then be checked in under version control.

If you happen to use a psql version 9.6 (or later), there is just another possibility using \gexec:

SELECT 'GRANT SELECT ...'
... -- you can even omit the semicolon here
\gexec

Notes:

I've written an answer about a similar issue a while ago - you may find useful details there.
there is no possibility of doing this from GRANT, unfortunately.  As it is not uncommon what you need here, I'd expect this functionality appearing sooner or later.
just to emphasize, the above commands will do the trick for the already existing tables.  Future tables need the default privileges (which you've set, for a given role).


create materialized view foo as
  select distinct machine_code, date(datetime) from thing;

create unique index on foo(machine_code,date);

Beware that the conversion of datetime to date will occur in the timezone of whoever last created or refreshed the MV.  You might want to use this instead:

create materialized view foo as
  select distinct machine_code, date(datetime at time zone 'GMT+1') from thing;

But that might not do what you want with daylight savings time.


Show the result of a set difference between tables Q and R

Here is what you looking at (Set Difference)

Q - R
Set Q Minus Set R

In MySQL

SELECT Q.*
FROM Q LEFT JOIN R USING (D, E)
WHERE R.E IS NULL;

Here is what the classmates are saying (Symmetric Difference)

(Q - R) U (R - Q) or (Set Q Minus Set R) Union (Set R Minus Set Q)
Also (Set Q Union Set R) Minus (Set Q Intersection Set R)

In MySQL

SELECT A.* FROM
(SELECT Q.* FROM Q LEFT JOIN R
USING (D, E) WHERE R.E IS NULL) A
UNION
SELECT B.* FROM
(SELECT R.* FROM R LEFT JOIN Q
USING (D, E) WHERE Q.E IS NULL) B;

Please go ask Your Teacher !!!


MariaDB allowing select only user to create databases

After reading this (thanks @ypercube)

MySQL : Why are there "test" entries in mysql.db?

I removed the test entries from mysql.db and it fixed it. I then realized that all the test I'd ran were using database named like test_xxx. Creating database with a name not beginning in 'test_' was not working as expected.


Postgres function to merge two json objects with overlapping keys into one object

If you're using PostgreSQL 9.5 (at least), you can use the concatenation operator (||) on jsonb types (and you can convert json to jsonb if necessary first).

For example:

WITH test(data) AS (
    VALUES ('{
        "a" : {
            "0" : 2,
            "1" : 4,
            "3" : 6
        },
        "b" : {
            "2" : 8,
            "1" : 10,
            "4" : 12
        }
    }'::jsonb)
)
SELECT (data->'a') || (data->'b') FROM test

will produce:

{"0": 2, "1": 10, "2": 8, "3": 6, "4": 12}

(Please note that, in this particular example, the parentheses around (data->'a') matter.)

You can modify the example above to get a specific value, as you requested, for example:

SELECT (((data->'a') || (data->'b'))->'1')::text::integer FROM test


Error on SQL Server 2000 backup (.bak file) restored on SQL Server 2012

The database is a SQL Server 2000 database (version 8.00.2055) - and you cannot directly update a SQL Server 2000 database to SQL Server 2012.

You'll need to take an interim step first (restore it in SQL Server 2008, back it up from that server again).

See the relevant MSDN documentation:

  In SQL Server 2012, you can restore a user database from a database backup that was created by using SQL Server 2005 or a later version.

but SQL Server 2000 is NOT supported.


How to store several lists and their items

It sounds like you want three tables

PROJECT( project_id, <<other attributes>>)
LIST( list_id, project_id, <<other attributes (e.g. the name of the list)>> )
LIST_ITEMS( list_item_id, list_id, <<other attributes>>)

Combining the LIST and LIST_ITEM tables wouldn't be appropriate because then you'd be storing attributes of the list (i.e. the list's name and description) in multiple rows of the LIST_ITEM table rather than just once in a single row of the LIST table.


Never changing the password for distributor_admin for local instance only?

I posted a general question at security.stackexchange If no one knows a password, is there a reason to change it?  as of this writing it has 3 answers.

The consensus is, two part

If you know that the password can not have been compromised there is no reason to change it.
But what if it was compromised and you don't know it.  By not changing you leave your system open forever.

Examples:

someone who was able to access the machine from an unknown vulnerability will have unlimited access to the system, forever.
If there is evidence, or at least a reasonable assumpion, that the secret has been compromised. In that case, change it immediately
If an admin who could recover (or change) the password either moves to another role or leaves the company, the password should be changed immediately thereafter.

  Is there a good reason to change the password for distributor_admin for local instance only?

Yes, if you can change it, someone else can change it and have eternal access. If you have a password changing policy 'distributor_admin' should be included in that policy.