SQL Express installs by default with a named instance of SQLEXPRESS.  See https://technet.microsoft.com/en-us/library/ms143744(v=sql.90).aspx as it applies to SQL Express 2005.  The settings you give in your question imply your instance uses dynamic ports.  Running SQL Browser and connecting using the named instance as previously suggested should in theory help.  If not, read on...

To restate: you're using a Mac to host a virtual machine where your SQL Express instance lives.  You're connecting to this virtual machine from the Mac host.  HTTP connections are okay, but not SQL Server connections.

There is a possibility that the Mac's network security allows connections over port 80 but not other ports.  That may be worth investigating, especially if you're trying to connect to the VM from an entirely different computer; the Mac's firewall may be preventing the connection in that case. Depending on what you're using to run the VM (virtualbox? vmware?), it's possible that network settings there are interfering with what you want to do as well.

Determining your SQL Server's port

If you're fortunate enough to have SQL Server Configuration Manager on your SQL Server box, you can run that, choose your instance, expand SQL Server Network Configuration, then click Protocols for instancename.  Double-click TCP/IP (it IS enabled, right?) Choose the IP Addresses tab and scroll all the way to the bottom where you'll see IPAll. On your way there, notice if TCP Dynamic ports is zero for the various IPn entries and TCP Port is blank.  Once you get to IPAll, notice the value for TCP Dynamic ports - this is what port SQL Express listens on if you're using dynamic ports.

For each IPn item where TCP Dynamic ports is zero, your instance will listen on the value specified in IPAll.TCP Dynamic Ports.  Otherwise, it listens on the TCP Port value for that particular item (or IPAll if blank).

Assuming you have all the defaults and SQL Browser is running, connecting to 192.168.2.1\sqlexpress should get you to where you need to go.  If not, try 192.168.2.1,dynamicportnumber instead.  Yes, that's a comma separating the IP address and port number.  So if your SQL instance uses port 49390 like the screen capture above, it would be 192.168.2.1,49390.

No SQL Server Configuration Manager?  Open a command prompt as administrator and use netstat -b -a -p TCP to get a list of open ports and attached processes:

The -b switch tells netstat to give you the associated process, -a says list listening ports, and -p TCP limits the list to just TCP otherwise you'd get a lot of extra items to sift through.

Note this particular box has an instance listening on two different ports: 1433 and 49390.

If none of this works (which would be sad because this is a lot of typing) I recommend you run a good port scan against the IP address where your SQL instance lives.  NMAP works on Mac, Linux and Windows:  https://nmap.org/

Also, install SQL Server Management Studio Express (https://technet.microsoft.com/en-us/library/ms365247(v=sql.105).aspx) on the SQL VM and try connecting to the database engine using that.  If you're on the box and it can't connect to itself, the problem is on the virtual machine itself - that will help narrow things down considerably.


MongoDB cloneCollection between CentOS servers times out at 2 hours

The connection being killed after 2 hours, even when active (keepalive would only come into play for an idle connection, which is why it had not impact on your issue), suggests that this is a firewall or similar in the path between the two servers that is killing the connection.  You either need an exception to that rule from your network admin or you need to figure out a different way to copy the data, preferably one that can resume following an interruption.

The easiest alternative I can think of is to use mongodump to dump out the collection locally on the first MongoDB instance, SCP/FTP the files (with a client that supports resuming transfers) to the second machine, then use mongorestore to load the collection locally into the second MongoDB instance.


MySQL Update/Insert trigger auto-increment over group by

One option is not to have a distinct set of unique receipt numbers per category, per year. Instead, simply use the BuchungID as the receipt number (I'm assuming that value is unique in your table, and may very well be an actual autoincrement column in the actual database). This should guarantee a unique ID is already available, whether the user wants a receipt number or not.

However, I'll assume that you've got some business rule that prevents this simple solution from working, and you really need to generate distinct receipt numbers within each category/year pairing.

Simply looking at the maximum current receiptno and adding 1 doesn't guarantee uniqueness - it would be possible for two users to request a receipt number at the same time, both to check the current max for the category and year before either receiptno is updated, and thus to get the same receipt number.

My next thought was to use sequences, but there are two problems with that. First, it looks like MySQL doesn't have a sequence object. Second, since you would have to have a sequence for each category and year, you'd either have to use dynamic SQL to set which sequence to use (not allowed in triggers), or you'd have to hard-code all the sequence names into your query (complicating the query significantly, and forcing you to update it any time a new category or year is added).

However, we can get around that by using a sequence table. This is a table that has a row for each "sequence" you want, with column(s) that identify the sequences (category and year, in your case), and others that define how the sequence works (starting value, ending value, increment, etc.).

I found an article online that provides the code to set this all up, including some options I hadn't thought of.

To make things best fit your situation, I'd modify their code as follows:

Instead of naming the sequences, identify them by category and year.
When using the nextval function, have it automatically create a new sequence if there isn't one for the category and year you pass in.

(Note: the following code is untested, and it's just my tweaks of the code in the article)

CREATE TABLE `sequence_data` (
    `sequence_category` int NOT NULL,
    `sequence_year` int NOT NULL,
    `sequence_increment` int(11) unsigned NOT NULL DEFAULT 1,
    `sequence_min_value` int(11) unsigned NOT NULL DEFAULT 1,
    `sequence_max_value` bigint(20) unsigned NOT NULL DEFAULT 18446744073709551615,
    `sequence_cur_value` bigint(20) unsigned DEFAULT 1,
    `sequence_cycle` boolean NOT NULL DEFAULT FALSE,
    PRIMARY KEY (`seq_category`, `seq_year`)
)

DELIMITER $$

CREATE FUNCTION `nextval` (`seq_category` int, `seq_year` int)
RETURNS bigint(20) NOT DETERMINISTIC
BEGIN
    DECLARE cur_val bigint(20);

    SELECT
        sequence_cur_value INTO cur_val
    FROM
        sequence_data
    WHERE
        sequence_category = seq_category
    AND
        sequence_year = seq_year
    ;

    IF cur_val IS NOT NULL THEN
        UPDATE
            sequence_data
        SET
            sequence_cur_value = IF (
                (sequence_cur_value + sequence_increment) > sequence_max_value,
                IF (
                    sequence_cycle = TRUE,
                    sequence_min_value,
                    NULL
                ),
                sequence_cur_value + sequence_increment
            )
        WHERE
            sequence_category = seq_category
        AND
            sequence_year = seq_year
        ;
    ELSE
    BEGIN
        IF NOT EXISTS (SELECT 1 FROM sequence_data
                       WHERE sequence_cateogry = seq_category
                       AND   sequence_year = seq_year
                      )
        BEGIN
            INSERT INTO sequence_data (sequence_category, sequence_year, sequence_cur_val)
            VALUES (seq_category, seq_year, 2);
            SET cur_val = 1;
        END
    END
    END IF;

    RETURN cur_val;
END$$

In your trigger, you'd use this:

SET New.receiptno = nextval(`category`, `year`);

It's important to note that, as written, sequence_cur_val is actually the next value that the sequence will assign, not the last value it did assign. So, when you query sequence_data and see a current value of 71, then the highest assigned value for that category and year is 70. Also, if the current value goes over the maximum value, then it is set to NULL (or, if you change the default setting of sequence_cycle, to the minimum value for the sequence - in your case, you definitely don't want to do that). So, you should probably add a check to see if the function returns NULL, and error out appropriately; otherwise, no receipt number will be set.

Please read the article (and the comments below it) for more details on how this is all meant to work.

Also note that the function must be run inside a transaction, to ensure that you don't get duplicate sequence numbers. If it's only going to be run inside a trigger, then you may not need to explicitly start a transaction inside the function itself.

Actually, I'd be inclined to rearrange the nextval code a little, to make sure we've done our update or insert (which should lock down the value to our session) before I try to set the value. I believe that version would look like this:

DELIMITER $$

CREATE FUNCTION `nextval` (`seq_category` int, `seq_year` int)
RETURNS bigint(20) NOT DETERMINISTIC
BEGIN
    DECLARE cur_val bigint(20);

    IF NOT EXISTS (SELECT 1 FROM sequence_data
                   WHERE sequence_cateogry = seq_category
                   AND   sequence_year = seq_year
                  )
        INSERT INTO sequence_data (sequence_category, sequence_year, sequence_cur_val)
        VALUES (seq_category, seq_year, 2);
    ELSE
        UPDATE
            sequence_data
        SET
            sequence_cur_value = IF (
                (sequence_cur_value + sequence_increment) > sequence_max_value,
                IF (
                    sequence_cycle = TRUE,
                    sequence_min_value,
                    NULL
                ),
                sequence_cur_value + sequence_increment
            )
        WHERE
            sequence_category = seq_category
        AND
            sequence_year = seq_year
        ;
    END IF;

    SELECT
        IF(sequence_cur_val IS NULL, NULL, sequence_cur_value - sequence_increment) INTO cur_val
    FROM
        sequence_data
    WHERE
        sequence_category = seq_category
    AND
        sequence_year = seq_year
    ;

    RETURN cur_val;
END$$

(Again, untested code).

Hope this provides help, or at least a direction to consider.


SQL Server Trace Stopped Messages Displaying Random Login Name

Found an answer here: http://www.360sqldba.com/2015/08/sql-error-log-shows-random-user-and.html

Conclusion: SQL error log message about which user and SPID stopped a timed trace is misleading. It appears that SQL uses a random active session to stop timed trace. It may be something that MS SQL team overlooked.


How to restore a database with SMK and Cerificate to another computer

I think you might be encountering the same problem as How to restore symmetric key on the new database?.  Excerpted from the accepted answer on that post:

  A symmetric key that was created without specifying the KEY_SOURCE and
  IDENTITY_VALUE can never be scripted or copied.

If you can recreate the symmetric key on the source database and specify the KEY_SOURCE and IDENTITY_VALUE, I think you will be able to open it on the target server.

Referencing Create Identical Symmetric Keys on Two Servers:

  In order to decrypt ciphertext, you need the key that was used to
  encrypt it. When both encryption and decryption occur in a single
  database, the key is stored in the database and it is available,
  depending on permissions, for both encryption and decryption. But when
  encryption and decryption occur in separate databases or on separate
  servers, the key stored in one database is not available for use on
  the second database

From that post, here is an example of creating a symmetric key using  KEY_SOURCE and IDENTITY_VALUE:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'My p@55w0Rd';
GO
CREATE CERTIFICATE [cert_keyProtection] WITH SUBJECT = 'Key Protection';
GO
CREATE SYMMETRIC KEY [key_DataShare] WITH
    KEY_SOURCE = 'My key generation bits. This is a shared secret!',
    ALGORITHM = AES_256,
    IDENTITY_VALUE = 'Key Identity generation bits. Also a shared secret'
    ENCRYPTION BY CERTIFICATE [cert_keyProtection];
GO

On the target server, after your restore, you might need to run the following:

-------------------------------------------------------------------------------------
-- When a backup is restored to a different server, the following script must be run
-- so the encrypted data will be accessible with the standard stored procedures.
-- Run this in the database containing the encrypted data.
-------------------------------------------------------------------------------------
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<original password>';
GO
-------------------------------------------------------------------------------------
-- This will alter the master key to drop service master key encryption
-- The service master key is server-specific so it's tied
-- to the server the backup came from.
-------------------------------------------------------------------------------------
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;
GO
-------------------------------------------------------------------------------------
-- This will alter the master key to add new service master key
-- encryption based on the current server.
-- This is done so you don't have to open the master key
-- with a password each time you use the certificate and symmetric key.
-------------------------------------------------------------------------------------
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO
CLOSE MASTER KEY;


how to create custom id in sql server with sequence

You can use a mix between a SEQUENCE and formatting a DEFAULT CONSTRAINT:

CREATE SEQUENCE MySequence
AS int
START WITH 1
INCREMENT BY 1;
GO

CREATE TABLE MyTable
(
    MySeq varchar(20) CONSTRAINT [DF_MyTable_MySeq]
                      DEFAULT FORMAT((NEXT VALUE FOR MySequence), 'BK-000000#'),
    Foo   int,
    Bar   int,

    CONSTRAINT [PK_MyTable] PRIMARY KEY (MySeq)
);
GO

INSERT INTO MyTable (Foo, Bar)
VALUES (1,2),(2,3),(3,4),(5,6);
GO

SELECT * FROM MyTable;
GO

MySeq      | Foo | Bar
:--------- | --: | --:
BK-0000001 |   1 |   2
BK-0000002 |   2 |   3
BK-0000003 |   3 |   4
BK-0000004 |   5 |   6

db<>fiddle here


Sql Server 2012 Failover Replica in Synchronous Mode between Primary-Secondary on VPN is very slow

Because you're using Synchronous replication, all transactions have to be signed off by both servers before your query can complete. You say you have a 'high speed' VPN, but, you don't make any mention of latency or bandwidth.

I suspect that you'll never be happy with the performance of synchronous replication, due to the additional latency. You should strongly consider setting the HA replication to Asynchronous.

Source: https://msdn.microsoft.com/en-us/library/ff877931.aspx


MySQL: how to use audit_log plugin to audit user modification

I found the reason for it, you just need to relogin to database... so audit_log will be able to capture the action.
SELECT audit_log_filter_set_filter('user_modification',
'{
  "filter": {
    "class": {
    "name": "general",
      "event": {
        "name": "status",
        "log": {
          "field": { "name": "general_sql_command.str", "value": "create_user" }
        }
      }
    }
  }
}') as RESULT;

SELECT audit_log_filter_set_user('%', 'user_modification');


Back when mysql was not transactionally sound by default (when people regularly used myISAM tables instead of InnoDB because that was the default or, going further back in time, because it didn't exist yet) "SELECT * FROM some_table" without any filtering clauses was one of the query types that peopel banged on about mySQL being much faster at that other database engines.

In a transactionally safe environment generally speaking the database engine will need to check every row and make sure that it should be visible to the current session (i.e. it isn't part of a transaction that is not yet committed (or wasn't committed at the start of this sessions active transaction) or is currently being rolled back) - checking every row implies needing to perform a table scan or (where one is present ) a clustered index scan.

It would be possible for the engine to keep track of the number of rows visible in each object for every active session/transaction, but presumably the designers have not judged this to be worth the extra processing involved so I assume it is not generally considered practical - I can imagine there would be some fairly complex locking requirements to deal with concurrency that would harm performance of other operations too much. You could implement this yourself by keeping a table in which is recorded the count of the rows in the table of interest, and have all your code meticulously maintain that value, but this would be quite some hassle and may be overly prone to errors due to bugs meaning that the count would drift from true over time (and you are probably adding a potential deadlock source and/or locking bottleneck at the application layer).

Situations where row-level security is in use complicate this even more - as well as needing to check the status of a row/page with respect to the current transaction, then engine needs to check again the current user too and as the security rules are dynamic it would be impractical to cache this information further necessitating the scan every time just-in-case. Row-level security is being added to MS SQL Server in the next release (https://msdn.microsoft.com/en-us/library/dn765131.aspx) and is already present in postgres (http://www.postgresql.org/docs/9.5/static/ddl-rowsecurity.html), I don't know about its status in other RDBMSs.


Why can't I see my table (PostgreSQL) when I use \dt(+) inside psql?

The documentation on psql explains:

  Whenever the pattern parameter is omitted completely, the \d commands
  display all objects that are visible in the current schema search path
  — this is equivalent to using * as the pattern. (An object is said to
  be visible if its containing schema is in the search path and no
  object of the same kind and name appears earlier in the search path.
  This is equivalent to the statement that the object can be referenced
  by name without explicit schema qualification.) To see all objects in
  the database regardless of visibility, use *.* as the pattern.

Bold emphasis mine.
Obviously, you have oecd_cl before reference in your search path. Use this for your purpose:

\dt *.donor*

And you'll get:

                          List of relations
  Schema   |      Name      | Type  |  Owner   | Size  | Description
-----------+----------------+-------+----------+-------+-------------
 oecd_cl   | donor          | table | postgres | 16 kB |
 reference | donor          | table | postgres | 16 kB |
 reference | donor_template | table | postgres | 16 kB |
 reference | donor_type     | table | postgres | 16 kB |
(4 rows)


Will changing a MySQL user's password break existing connections?

According to MySQL 5.0 Certification Study Guide

Page 489 under the subheading says 'Statement Privilege Checking' says:

Each time the server receives a statement from a client, it check's the client's privileges to see whether it is allowed to execute the statement.

Since MySQL grants are checked in memory, then one of two things must happen
SCENARIO #1
If you ran standard GRANT commands to change the password, the password change is recording on disk via FLUSH PRIVILEGES; automatically. (See Section 34.1.2 "The Grant Tables")
SCENARIO #2
If you hacked the password in, as follows (shown in Section 35.5.1 Page 498):
SET @newpass = PASSWORD('whateverpasswordiwant');
UPDATE mysql.user SET PASSWORD = @newpass WHERE user='replication_user';

This stores the password on disk but does not push it into the in-memory copy of the MySQL Grants. As long as you don't run FLUSH PRIVILEGES; or restart mysqld, you have a chance to change it back. Newer versions of MySQL 5.5 may not be as forgiving.
SHORT ANSWER : For SCENARIO #1, Yes, for SCENARIO #2, more than likely.


SQL Agent Job step using Powershell can not find Network Share

I cannot explain why this is the answer but it is:

$sel = SELECT-STRING "Nothing to do" -path "D:\Results\Production.20120117_02.$(ESCAPE_NONE(STRTDT)).$(ESCAPE_NONE(STRTTM)).txt"

Changed to:

New-PSDrive -Name "P" -PSProvider FileSystem -Root "\\<network share\Results"
$sel = SELECT-STRING "Nothing to do" -path "P:\Ad-hoc\Results\20120117_02\Production.20120117_02.$(ESCAPE_NONE(STRTDT)).$(ESCAPE_NONE(STRTTM)).txt"

Runs successful! There were NO permission changes but I guess powershell needs a [temporary with PS-Drive] local path to run. Again ZERO permission changes and if I were to replace the "P:\" with the "\\network share\Results" it would fail.

This is what I needed. The network share name will not be changing but a drive letter on the server can change so this is maintainable for me and reusable in my other Jobs.


Improve performance of sys.dm_db_index_physical_stats

'DETAILED' implies a full scan of every single page in the index (or heap). Do this for every table and every secondary index, the result means you are doing a full database scan, end to end, and not a very efficient one (ie. not nearly as fast as backup would read it, for instance). The time is driven by:

how big your database is
how fast your IO subsytem is to read the entire database
aditional concurent load competing for the IO throughput

Basically, if all you have is a straw (your IO throughput) it takes 30 minutes to drink a bucket (your database size). Buy faster IO, reduce the size of your data, or use SAMPLED scans.

That being said... 20Gb is quite small. 30 minutes to read 20Gb is a lot of time. Is you IO subsystem that slow? Did you deploy on 7200 RPM consumer 1TB drives?


SSMS closes after installing visual studio

This sounds like a problem with your Visual C++ Redistributable not being the version required by SSMS. Check which version of the VC++ Redistributable is required by SSMS and if you have a later version already installed, then uninstall it. If you have an older version, then download the version required by SSMS and install it manually. Normally I find this problem when developers install Visual Studio before installing SSMS (as Visual Studio comes packaged with a newer version than SSMS does). You can upgrade the VC++ Redistributable after installing SSMS if you need it.


Auditing sysadmin logons with Extended Events

This may work for you, but by no means do I consider it elegant (or even good for that matter).

First, configure the server to log all successful and failed logins.

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 3
GO

Now, to audit those logins that are in the SYSADMIN fixed server role, you can run through the following process:

CREATE TABLE #ErrorLog
(
    LogDate DATETIME NULL,
    ProcessInfo VARCHAR(20) NULL,
    [Text] VARCHAR(MAX) NULL
)

INSERT INTO #ErrorLog
exec xp_readerrorlog 0, 1, N'Login succeeded', NULL, NULL, NULL, N'DESC'

SELECT *
FROM (
    SELECT IS_SRVROLEMEMBER('sysadmin', SUBSTRING([Text], CHARINDEX(CHAR(39), [Text]) + 1, CHARINDEX(CHAR(39), [Text], CHARINDEX(CHAR(39), [Text]) + 1) - CHARINDEX(CHAR(39), [Text]) - 1)) AS isSysAdmin
    , SUBSTRING([Text], CHARINDEX(CHAR(39), [Text]) + 1, CHARINDEX(CHAR(39), [Text], CHARINDEX(CHAR(39), [Text]) + 1) - CHARINDEX(CHAR(39), [Text]) - 1) AS loginName
    , *
    FROM #ErrorLog
) InstanceLogins
WHERE isSysAdmin = 1

DROP TABLE #ErrorLog

What this does is pull all successful logins from the current error log, identify the login name, and pass it into the IS_SRVROLEMEMBER function you were already tying into with your Login Trigger approach.

This will potentially balloon your ERRORLOG, but it won't block/slow logins like a failing trigger may cause.  It will also provide login times and if you have some SQL Auditing in place, you may be able to correlate these with activity, etc.  At any rate, I think this will give you what you're looking for.  Final note: you don't have to audit the failed logins, but I find that information more useful than not.


Optimizing query performance with aggregation tables and partitioning

I would recommend trying a single-table design.  There would be groups of columns which are only relevant to a single type, meaning they would be NULL for rows of any of the other types.  For the physical layout of the table, you would probably want the common columns first, then the frequently filtered-upon type-specific columns, then the infrequently/never filtered-upon type-specific columns last.

This will get rid of the problem of needing to do left joins.  It will also make it easy to partition on timestamps, as you no longer have the side tables which lack timestamps.  Hopefully the large text fields will get TOASTed aggressively, keeping the size of the main table down (but this is not something easy to control).

If the partitions with the most recent timestamps are the hottest ones, this will help keep their data and their indexes small enough to fit RAM cache, and hot enough to be naturally kept there.


Since each dependency implies itself (given the fact that in logic trivially every assertion implies itself), F is included in F+. But note that in your example F+ contains quite a large number of dependencies, in addition to the dependencies that you have listed, like for instance A→A, AB→C, B→B, AC→A, etc.


MySQL FOREIGN KEY constraint is incorrectly formed

Both the referencing and referenced columns must be of the same type (and same in this case includes the unsigned attribute).

You didn't define notification.async_task_id as unsigned, so it was created with the (default) signed. Fix that and the foreign key will raise no errors.


If you can't add any extra storage for the data (as noted in your comment) then you simply can't do this.  SQL Server doesn't track the old and modified rows for you.

There's a duct-tape way of doing it temporarily: you can read the transaction log.  When combined with the full chain of backups, you can build the old & new data together.  Third party data recovery products like Quest LiteSpeed do this - for any transaction, they'll show you the old and new data by reading through the full backup and transaction log backups.


Using the name of a table contained in a column of another table?

The only way to do this is to query SourceID in to a variable and then query the source table. At that point, you're better off pulling both into variablesm like this:

DECLARE @SourceTable NVARCHAR(255) --Note the length is arbitrary
,@SourceID NVARCHAR(255) --Assuming it's an INT, it doesn't matter
,@sql NVARCHAR(MAX)

SELECT @SourceTable = SourceID
,@SourceID = ReferenceID
FROM MyTable
WHERE SourceID = 'some_table'
AND ReferenceID = 1

SET @SQL = 'SELECT * FROM ' + @SourceTable + ' WHERE InstanceID = ' + @SourceID

EXEC(@sql)

You want the SourceID to be a string because otherwise you'd need to convert it to one anyway.


MS Analysis Services (Multidimensional)'s [almost] whole reason for being is to abstract away aggregations so you can simply query tour OLAP data without having to worry about how the aggregate value is being retrieved.  In fact, in Analysis Services Multidimensional, you point and click to create your aggregation combinations.

I'm not sure what database server you're running, but if you're running SQL Server 2014 you also have the ability to build a clustered columnstore index on your fact table.  Doing this will, in a most peoples' cases, essentially eliminate the need for you to perform aggregations entirely.  I'd definitely look into that as an option if you're on SQL Server and your data and resources are a good fit.

In that similar vein, if you're not running SQL Server, there are a lot of columnar databases out there which service OLAP workloads quite well, such as Tableau ($$$).  I'm not sure what the open-source space has to offer, but that's worth checking out as well.


There is no "mass revoke" statement so you best option to change this is to update the users table:

UPDATE mysql.user SET Grant_priv = 'N' WHERE user != 'root';

Not that occasionally, there are users that should have grant privileges (and should have that) which are not named root. On my system, for example, I have debian-sys-maint (which I actually wonder if they should have grant privileges, but that's how it's installed).

Do not forget to reload the privileges after changing the user table:

FLUSH PRIVILEGES;

You can find more information on http://dev.mysql.com/doc/refman/5.5/en/privilege-changes.html.


Generally you would not install Pgpool on the backend servers. What you see in your picture is the most common configuration. Pgpool is a standalone server which essentially sits in front of the databases. The two Postgres servers are often configured with streaming replication; with one being the master and the other the slave.

This allows Pgpool to load balance all read queries between the two (or more) databases. Any queries which involve any writes, will be routed to the master server which in turn replicates to the slave.

As @Neil McGuigan said, you can also have multiple Pgpool servers to achieve better high availability. Technically you could install Pgpool on the database servers in this configuration, but this would be bad practice. Running multiple Pgpool servers is a much more complex configuration. If this is your first time with Pgpool, I would start with one Pgpool server before getting two to work.

In either configuration, your application server thinks it is just connecting to a single Postgres database.

About pgpool_regclass, which should really be a separate question, this is from the Pgpool FAQ:

  If you are using PostgreSQL 8.0 or later, installing pgpool_regclass function on all PostgreSQL to be accessed by pgpool-II is strongly recommended, as it is used internally by pgpool-II. Without this, handling of duplicate table names in different schema might cause trouble (temporary tables aren't a problem).

  If you are using PostgreSQL 9.4.0 or later and pgpool-II 3.3.4 or later, 3.4.0 or later, you don't need to install pgpool_regclass since PostgreSQL 9.4 has built-in pgpool_regclass like function "to_regclass".

If you need this, it is just some SQL code run on your Postgres master server to add a function Pgpool uses.

With regclass, there is an additional step you have to do (I was thinking of insert_lock). If you are compiling from source (generally most distributions have really outdated versions of Pgpool), you will have to compile a Postgres library as well.

If you compiled from source, you will have to go into the .../pgpool-II-3.X.X/src/sql/pgpool-regclass folder and do a ./configure; make.

Copy the pgpool-regclass.so file into the Postgres extension directory. On my Ubuntu 14.04 server (just using the Postgres 9.3 package install), it is located at: /usr/lib/postgresql/9.3/lib. Remember to do this for all Postgres servers.

Once that is complete, then you can run pgpool-regclass.sql on the master. This just maps the pgpool_regclass function to the library you copied over.


Audit trigger 91plus: setting up with proper role?

If I own the table (the owner is the user that created the table) or am a superuser, I can modify and drop triggers on a table.

This makes it trivial to tamper with trigger based auditing.

For good security, it is a good idea to use an application user different from the object owner, and of course not a superuser.


Do I need to worry about RESTORE FILELISTONLY types other than L and D?

In SQL Server, the full-text catalog is a logical concept and does not reside in a filegroup. Therefore, to back up a full-text catalog in SQL Server, you must identify every filegroup that contains a full-text index that belongs to the catalog. Then you must back up those filegroups, one by one.
When a full-text index is created, it is placed in one of the following locations:
A user-specified filegroup.
The same filegroup as base table or view, for a nonpartitioned table.
The primary filegroup, for a partitioned table.

Source
If the database has full text catalog,  you'll need to backup the filegroups pertaining to it. It is possible that there is a filegroup only containing the index for the full text catalog and nothing else, as seen by "A user-specified filegroup".

If a full-text index resides in a separate filegroup from all of the associated table data, the behavior of piecemeal restore depends on which of the filegroups is restored and brought online first:

Source
Now for filestream, you can see an example of backups and restores here:
MSSQL
You can see that the filestream backup data is classified as type S as you talked about. It is also seen in SQL as FILESTREAM type and is it's own file to be restored.
To recap, you may run into F and S type in the filelist. S type seems more likely as it's possible the full text indices reside in a filegroup with tables or other data and would be seen as D. You can specify your own filegroup for full text indices that contain just the indices however.


How can I call a remote User Defined Function with Parameters?

You can use OPENQUERY. You just have to use dynamic SQL to build the OPENQUERY call

DECLARE @sql nvarchar(4000);

SET @sql = 'SELECT * FROM OPENQUERY(linkedServerName,
                          ''SELECT @someValue = dbo.[SomeUDF](' + CAST(@id etc) + ')'')';
EXEC (@sql)

However, you can't build this into a larger query on the local server.
UDFs + linked servers + parameters just don't play nice together...


Is there a way to detect that a function is in an invalid state,
  preferably from SQL

For plpgsql, that's the purpose of the plpgsql_check extension.

Otherwise, functions (and now procedures too) can be written in all kinds of languages, so the job of parsing the body of functions is offloaded to these languages. PostgreSQL itself can't know when a function refers to any particular database object: it can't even parse the function's body to start with.

Also, it's arguable that code referencing a not-yet-existing table is necessarily invalid. It may be a temporary table that is meant to always be created before calling the function.

  Can a table in Postgres become invalid after a certain number of
  "dangerous" operations, if so, how would one detect that a table is
  about to enter an invalid state?

When a column is dropped, it's marked as dropped but still uses a slot in pg_attribute and in the heap. If columns are constantly dropped and new columns are constantly added to a table, at some point a row would try to grow bigger than a page, which is forbidden, even though its number of live columns would be reasonable and would cause no problem in a new table. So in a way, you could say that drop column / add column are operations that can make a table unusable, when used recklessly.

There are certainly other corner cases like that, and every case would need its own detection mechanism.

  I guess what I'm trying to find out how I can detect if there are any
  invalid objects in the database before discovering it in run-time.

Framed like that, the question looks too general. In general, objects don't have  a valid flag, with some exceptions such as indexes ith pg_index.indisvalid


I suppose, that schema owner has implicit privileges to do anything with schema.

No, that is not the case. The manual on GRANT:

  PostgreSQL allows an object owner to revoke their own ordinary
  privileges: for example, a table owner can make the table read-only to
  themselves by revoking their own INSERT, UPDATE, DELETE, and TRUNCATE
  privileges. This is not possible according to the SQL standard. The
  reason is that PostgreSQL treats the owner's privileges as having been
  granted by the owner to themselves; therefore they can revoke them
  too. In the SQL standard, the owner's privileges are granted by an
  assumed entity "_SYSTEM". Not being "_SYSTEM", the owner cannot revoke
  these rights.

Bold emphasis mine.

There are also no default privileges for public:

  PostgreSQL grants default privileges on some types of objects to PUBLIC. No privileges are granted to PUBLIC by default on tables, columns, schemas or tablespaces. (...)

So even the owner needs privileges for a schema - which are given by default! If all you do is:

CREATE SCHEMA test AUTHORIZATION dummy;

Or:

CREATE SCHEMA test;

Then default privileges are NULL, i.e. default to system defaults (which is USAGE and CREATE for the owner of a schema). The manual on GRANT:

  If the "Access privileges" column is empty for a given object, it
  means the object has default privileges (that is, its privileges
  column is null). Default privileges always include all privileges for
  the owner, and can include some privileges for PUBLIC depending on the
  object type, as explained above. The first GRANT or REVOKE on an
  object will instantiate the default privileges (producing, for
  example, {miriam=arwdDxt/miriam}) and then modify them per the
  specified request. Similarly, entries are shown in "Column access
  privileges" only for columns with nondefault privileges. (Note: for
  this purpose, "default privileges" always means the built-in default
  privileges for the object's type. An object whose privileges have been
  affected by an ALTER DEFAULT PRIVILEGES command will always be shown
  with an explicit privilege entry that includes the effects of the ALTER.)

If you revoke privileges from the owner (can be done by owner himself or superuser), then the role does not have these privileges. (But the owner can always grant these privileges to himself as well.) And if you assign a new owner, the status is inherited:

-- as user postgres:
CREATE SCHEMA test1;
REVOKE ALL ON SCHEMA test1 FROM postgres;
ALTER schema test1 owner TO dummy;

You'll now see an empty array ({}) instead of NULL:

SELECT nspname AS schema, nspacl AS privileges
FROM   pg_namespace
WHERE  nspname LIKE 'ow%';

(pgAdmin4 reads from the same system table.)

If you did not explicitly revoke USAGE and CREATE, then the new owner has these privileges, too. That's what's confusing in your description. Did you revoke privileges from the old or new owner?


Display equal ranking if count is the same

Here is one way to achieve equal ranking with variables:

SELECT
  @lastrank := (subq1.ArticleCount = @lastcount) * @lastrank
            + (subq1.ArticleCount <> @lastcount) * (@rank := @rank + 1) AS Rank,
  subq1.University_Name,
  @lastcount := subq1.ArticleCount AS ArticleCount
FROM
  (
    SELECT
      Uni.University_Name,
      COUNT(*) AS ArticleCount
    FROM
      Author_University AS AU,
      University AS Uni,
      Articles AS Art
    WHERE
      Art.Year_Published >= '2010'
      AND Uni.University_ID = AU.University_ID
      AND Art.Article_ID = AU.Article_ID
    GROUP BY
      University_Name
    ORDER BY
      ArticleCount DESC
    LIMIT
      0, 25
  ) AS subq1,
  (
    SELECT
      @lastcount := 0,
      @lastrank := (@rank := 0)
  ) AS r
;

The purpose of the three variables in this method is:

@rank – to calculate and store sequential, unique ranking;
@lastrank – to store the rank of the previous row;
@lastcount – to store the count value of the previous row.

Here is how the Rank expression works. If the current row's ArticleCount is equal to @lastcount, the ArticleCount = @lastcount condition evaluates to true and the other, ArticleCount <> @lastcount, of course, to false. In the context of an arithmetical operation (*), true is implicitly converted to 1 and false to 0. So, the expression becomes equivalent to this:

1 * @lastrank + 0 * (@rank := @rank + 1)

which, in turn, simplifies to just @lastrank. So, when ArticleCount is equal to @lastcount, @lastrank retains its value from the previous row. The current count is then stored in @lastcount to be compared against on the next row.

That way @lastrank stays the same until ArticleCount is no longer equal to @lastcount. At that point true and false – and, consequently, 1 and 0 – trade places and the expression becomes equivalent to this:

0 * @lastrank + 1 * (@rank := @rank + 1)

and so in the end @lastrank gets the result of evaluation of (@rank := @rank + 1), which will simply be a new ranking. Note, however, that (@rank := @rank + 1) actually evaluates on every row but assigned to @lastrank only when ArticleCount changes to a new value.

And it is the fact @rank increases on every row that allows us to get this kind of ranking, with gaps. For comparison, consider this CASE expression:

CASE WHEN ArticleCount = @lastcount THEN @lastrank ELSE (@rank := @rank + 1) END

or its equivalent using the IF() function:

IF(ArticleCount = @lastcount, @lastrank, (@rank := @rank + 1))

Each of these variations would give us equal ranking as well, but the ranking would be without gaps – that is, instead of e.g. 1, 1, 3, 4, 4, 4, 7 we would be getting 1, 1, 2, 3, 3, 3, 4. The reason is, the (@rank := @rank + 1) assignment in each case would take place only if/when Article differed from @lastcount – in other words, @rank would both increase and be assigned to @lastrank only when ArticleCount changed, unlike the first method, where @rank increases constantly.


Why does SQL Server require the datatype length to be the same when using UNPIVOT?

What is the logic behind requiring the same length when using UNPIVOT?

This question may only be truly answerable by the people who worked on the implementation of UNPIVOT. You might be able to obtain this by contacting them for support. The following is my understanding of the reasoning, which may not be 100% accurate:

T-SQL contains any number of instances of weird semantics and other counter-intuitive behaviours. Some of these will eventually go away as part of deprecation cycles, but others may never be 'improved' or 'fixed'. Quite aside from anything else, applications exist that depend on these behaviours, so backward compatibility has to be preserved.

The rules for implicit conversions, and expression type derivation account for a significant proportion of the weirdness mentioned above. I do not envy the testers who have to ensure that the weird (and often undocumented) behaviours are preserved (under all combinations of SET session values and so on) for new versions.

That said, there is no good reason not to make improvements, and avoid past mistakes, when introducing new language features (with obviously no backward compatibility baggage). New features like recursive common table expressions (as mentioned by Andriy M in a comment) and UNPIVOT were free to have relatively sane semantics and clearly-defined rules.

There will be a range of views as to whether including the length in the type is taking explicit typing too far, but personally I welcome it. In my view, the types varchar(25) and varchar(50) are not the same, any more than decimal(8) and decimal(10) are. Special casing string type conversion complicates things unnecessarily and adds no real value, in my opinion.

One could argue that only implicit conversions that might lose data should be required to be explicitly stated, but there are edge-cases there too. Ultimately, a conversion is going to be needed, so we might as well make it explicit.

If the implicit conversion from varchar(25) to varchar(50) were allowed, it would just be another (most likely hidden) implicit conversion, with all the usual weird edge cases and SET setting sensitivities. Why not make the implementation the simplest and most explicit possible? (Nothing is perfect, however, and it is a shame that hiding varchar(25) and varchar(50) inside a sql_variant is allowed.)

Rewriting the UNPIVOT with APPLY and UNION ALL avoids the (better) type behaviour because the rules for UNION are subject to backward compatibility, and are documented in Books Online as allowing different types so long as they are comparable using implicit conversion (for which the arcane rules of data type precedence are used, and so on).

The workaround involves being explicit about the data types and adding explicit conversions where necessary. This looks like progress to me :)

One way to write the explicitly-typed workaround:

SELECT
    U.PersonId,
    U.ColumnName,
    U.Value
FROM dbo.People AS P
CROSS APPLY
(
    VALUES (CONVERT(varchar(50), Lastname))
) AS CA (Lastname)
UNPIVOT
(
    Value FOR
    ColumnName IN (P.Firstname, CA.Lastname)
) AS U;

Recursive CTE example:

-- Fails
WITH R AS
(
    SELECT Dummy = 'A row'
    UNION ALL
    SELECT 'Another row'
    FROM R
    WHERE Dummy = 'A row'
)
SELECT Dummy
FROM R;

-- Succeeds
WITH R AS
(
    SELECT Dummy = CONVERT(varchar(11), 'A row')
    UNION ALL
    SELECT CONVERT(varchar(11), 'Another row')
    FROM R
    WHERE Dummy = 'A row'
)
SELECT Dummy
FROM R;

Finally, note that the rewrite using CROSS APPLY in the question is not quite the same as the UNPIVOT, because it does not reject NULL attributes.


Unable to SELECT data that is definitely there

Global temporary table was just a guess, because you had not posted the actual statement for table creation, and already said you used explicit commit. Using the information you gave us, we can only guess.
But here it is, works as expected:
[root@r5 ~]# docker pull epiclabs/docker-oracle-xe-11g
Using default tag: latest
latest: Pulling from epiclabs/docker-oracle-xe-11g
e92ed755c008: Pull complete
b9fd7cb1ff8f: Pull complete
ee690f2d57a1: Pull complete
53e3366ec435: Pull complete
51f7c9099c0d: Pull complete
1b7f09633195: Pull complete
4a8ff9746209: Pull complete
6bae0d820e30: Pull complete
Digest: sha256:

404173b90d09d0619c854dd469c76c3e7256c88a7aac5d47e05f934ec7afae06
Status: Downloaded newer image for epiclabs/docker-oracle-xe-11g:latest
docker.io/epiclabs/docker-oracle-xe-11g:latest
[root@r5 ~]# mkdir /storage/wdred_3tb_raid/mydata
[root@r5 ~]# docker run -d -v /storage/wdred_3tb_raid/mydata:/u01/app/oracle -p 1521:1521 epiclabs/docker-oracle-xe-11g
29f5bcbd1ccbf88d59111d653830098e6a965c602d07adfad5b331c7be8a4b95
[root@r5 ~]#

Test from other machine:
[oracle@o81 ~]$ sqlplus system/oracle@\"r5/xe\"

SQL*Plus: Release 19.0.0.0.0 - Production on Mon May 24 20:31:08 2021
Version 19.11.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

ERROR:
ORA-28002: the password will expire within 7 days

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> create user foo identified by foo default tablespace users quota unlimited on users;

User created.

SQL> grant create table to foo;

Grant succeeded.

SQL> create table foo.bar(c1 number);

Table created.

SQL> insert into foo.bar select rownum from dual connect by level <= 5;

5 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from foo.bar;

  COUNT(*)
----------
         5

SQL> connect system/oracle@r5/xe
ERROR:
ORA-28002: the password will expire within 7 days

Connected.
SQL> select count(*) from foo.bar;

  COUNT(*)
----------
         5

SQL>


Replicate some tables without transferring the entire log

The only way to filter on one DB server is to run multiple instances of MySQL on one DB Server.

SERVER DB1

Port 3306 would be your normal database instance for your application

Port 3307 would be a slave of Port 3306

There are a few things you need to do with the MySQL instance running on DB1

Enable the following in my.cnf for

log-bin=mysql-bin
log-slave-updates
replicate-do-table=db1.public_tb1
replicate-do-table=db2.public_tb2
...
replicate-do-table=dbn.public_tbn

As an option, convert all tables in DB1 3307 to the BLACKHOLE storage engine.

That way, DB1 3307 only has binlogs with information. No actual data.

SERVER DB2

Setup MySQL instance and make it Slave from the 3307 instance of DB1. Why is that good ?

Because the binlogs in the DB1 3307 instance should only contain the public info. Thus, all slaves from DB1 3307 will only see public info.

CAVEAT

Please see my other posts on how to use BLACKHOLE tables in replication

Apr 18, 2013 : Single slave - multiple master MySQL replication
Feb 03, 2012 : One slave, multiple masters MySql
Jun 01, 2011 : What can we do in MySQL 5.0 Replication to address bandwidth concerns?
May 16, 2011 : Is Multi Master Single Slave possible in mySQL DB?
Mar 11, 2011 : MySQL in star topology


Difference in results between case syntax

For the same reason Col3 I've added below returns "Oh". In your Col2 you are testing if NULL equals @Value1. NULL is undefined, it doesn't equal anything. As Codd would say

  Rule 3: Systematic treatment of null values:

  The DBMS must allow each field to remain null (or empty). Specifically, it must support a representation of "missing information and inapplicable information" that is systematic, distinct from all regular values (for example, "distinct from zero or any other number", in the case of numeric values), and independent of data type. It is also implied that such representations must be manipulated by the DBMS in a systematic way.

DECLARE @Value1 INT

SELECT
    'Col1' = CASE WHEN @Value1 IS NULL THEN 'Yay'
    ELSE 'Oh'
    END
    ,'Col2' = CASE @Value1 WHEN NULL THEN 'Yay'
    ELSE 'Oh'
    END
    ,'Col3' = CASE WHEN NULL = NULL  THEN 'Yay'
    ELSE 'Oh'
    END
    ,'Val' = @Value1


Database not going into single_user mode

From Comments

As this error is related to I/O and most likely this has to do with server hardware where your database is hosted.

Please try putting database into emergency mode before setting into single user mode with below command:

ALTER DATABASE testDb SET EMERGENCY

Once database is set into emergency mode, try to put database into single user mode with below command:

ALTER DATABASE testDb  SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

try to run dbcc checkdb now and see if it works. In case, it doesn't work and throws error. Take the Full backup of database if single user mode is successful otherwise take it when emergency mode is turned on. Copy this backup to some other server with same or higher SQL server version and restore the same.

Once successfully restored, perform dbcc checkdb and see if it can be repaired. Repair depends on type of issue with your database. Since you don't have any backup, I guess you will have to go with minimal loss of data/log based on error type.

Please refer this link from Paul Randal for more details on repair of database.

Hope above helps.


Database not able to startup showing control file is inconsistent with another

You should be able to resolve this by copying CONTROL01.CTL over CONTROL02.CTL since the first one has a higher version number. Make a copy of CONTROL02.CTL in case it does not work


The number of files is irrelevant from a performance perspective.  The number of spindles those files are distributed over is, on the other hand, critically important to performance.  If you are using a reasonably modern SAN and the additional files would be created on the same mountpoint, there will be no meaningful performance difference since the data is being spread over the same number of physical disks.  If additional data files would be placed on different physical disks, there would likely be performance benefits to that.

There may be management benefits to having multiple smaller, consistently sized data files if that makes it easier to move files from one physical location to another.  If you have a bunch of 5 GB data files, for example, and you generally allocate 30 GB mountpoints on the SAN, it may be easier to have up to 6 data files per mountpoint so that if you need to move data files from one mountpoint to another, you can do so relatively easily.  If you've got 4 mountpoints, each with 4 data files, you can move all the data files off of one mountpoint and distribute them over the other 3 mountpoints if that ever became necessary (for example, if you started having performance problems on one mountpoint).  If you had one data file per mountpoint each of which was 20 GB, you couldn't easily move those data files around.  Additionally, if a single data file became corrupt, it would generally be quicker to restore a 5 GB file from backup rather than a 20 GB file.

Of course, the number of data files needs to be reasonable.  If you've got a 20 TB database, 5 GB data files would mean that you needed to manage 4000 separate files which would probably be excessive and would probably cause checkpoints to start to take longer than you'd like.


How to select only first column dynamically on mysql

It's not clear why you need this but technically you can do it with dynamic SQL like this

SET @sql = NULL;
SELECT column_name
  INTO @sql
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE table_schema = SCHEMA()
   AND table_name = 'users'
   AND ordinal_position = 1;

SET @sql = CONCAT('SELECT `', @sql, '` FROM users');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Here is SQLFiddle demo

You can wrap it up into a stored procedure

DELIMITER $$
CREATE PROCEDURE select_first_column(IN _tname VARCHAR(64))
BEGIN
  SET @sql = NULL;
  SELECT column_name
    INTO @sql
    FROM INFORMATION_SCHEMA.COLUMNS
   WHERE table_schema = SCHEMA()
     AND table_name = _tname
     AND ordinal_position = 1;

  SET @sql = CONCAT('SELECT `', @sql, '` FROM `', _tname, '`');

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

Sample usage:

CALL select_first_column('users');
CALL select_first_column('orders');

Sample output:

| USER_ID |
|---------|
|       1 |
|       2 |

| ORDER_ID |
|----------|
|      101 |
|      202 |

Here is SQLFiddle demo


I am struggling to decide on the best way to design my database tables
  around my resources.

Don't.

Design your API according to RESTful principes, design your database according to normalisation principles. One does not need to impact upon the other.

Your database should not contain a SaleResource table, it should contain a Sale (or purchase/order) table. That table will include a primary key that uniquely identifies a Sale and foreign keys to related User and Customer tables.

You REST api will translate a request for the resource identified by GET /users/{userID}/clients/{clientID}/sales/{salesID} to the appropriate database query, retrieve the row, construct the resource that represents a Sale and return it to the client.

Be mindful that you are currently exposing what appears to be internal database identifiers (UserID/ClientId/SalesID) to the outside world. It may be appropriate in your case but generally <entity>ID feels off in a RESTful API.


Why does the secondary server not failover?

Wouldn't the server in site2 think that it was the last remaining server and failover to become the primary?

No, because it is part of a group with only two votes, which is not a majority.  For all the DR side knows there are three healthy nodes on the primary side which have quorum.
When you gracefully shut down the primary side, Dynamic Quorum reconfigured the votes.
Quorum is explained here: https://docs.microsoft.com/en-us/windows-server/failover-clustering/manage-cluster-quorum


Under what conditions are polymorphic associations used? What are the alternatives?

In general, I have never found polymorphic associations to work very well.  The problem is that once you open up something like this, you have to have unique identifiers across the tables and these need to be enforced.  Otherwise you end up with a case where "customer has the same 'id' as an invoice and therefore ship-to information is mixed up."

The alternative is to define polymorphic association groups and use foreign keys.  For example we might have a table entitled "objects" and in it we might have two columns:  table_name and object_id.  We can then use triggers to maintain that table and have all tables in the polymorphic set define foreign keys against this association catalog (by table name and id) and we can therefore guarantee uniqueness that way.  We can then set foreign keys on the other side, and reference the catalog table.  This isn't perfect  If there is a trigger malfunction it is possible for a reference to be dangling, but it avoids the worst of the join projection and insert anomilies.  Also depending on the complexity of the catalog, mutual foreign keys may be useful.

Finally some databases (like PostgreSQL) would allow table inheritance to be used to create something like this, but this is a rather advanced topic.....


MongoDB document found on replica set but not in mongos

Have you followed this guide from MongoDB to restore a sharded cluster ? Apparently it is different from restoring a non-sharded cluster.

The key here is the backup and restore done on Shards for a sharded collection. The reason for the current state of your database is because, config server or cached information on Mongos are not aware of the restoring happened on Shards. So the meta information stored on config servers doesn't have latest information about the distribution of the data in shards.

You can try getting into proper state,

By restoring the cluster by following the guide.
by tying to remove shard and re-adding the shard again. So that MongoDB would drain all the the data from one replica-set to the primary replica set and then re-balance the data, after re-adding the shard. You may use, removeShard and addShard operations.


There is not. Unless (until) one develops it (MySQL is open-source, anyone can contribute.)

The ANSI/ISO SQL WITH keyword is used to define Common Table Expressions (CTEs) and it simplifies complex queries with one or several nested references. It's available in Oracle, Postgres, SQL-Server, DB2 but not in MySQL.

The final query may have references (usually in the FROM clause but they could be in any other part) to anyone of the common table expressions, one or more times. The query can be written (without CTEs) in MySQL using derived tables but the references have to be made repeatedly.

Example of a silly query showing all persons born in the 50s and in the month of July and the number of all persons born in the same year:

WITH a AS
    ( SELECT name, birthdate, YEAR(birthdate) AS birthyear
      FROM persons
      WHERE birthdate >= '1950-01-01' AND birthdate < '1960-01-01'
    )
, b AS
    ( SELECT birthyear, COUNT(*) AS cnt
      FROM a
      GROUP BY birthyear
    )
SELECT a.name, a.birthdate, b.cnt AS number_of_births
FROM a JOIN b
  ON a.birthyear = b.birthyear
WHERE MONTH(a.birthdate) = 7 ;

In MySQL, it could be written as:

SELECT a.name, a.birthdate, b.cnt AS number_of_births
FROM
    ( SELECT name, birthdate, YEAR(birthdate) AS birthyear
      FROM persons
      WHERE birthdate >= '1950-01-01' AND birthdate < '1960-01-01'
    ) AS a
  JOIN
    ( SELECT birthyear, COUNT(*) AS cnt
      FROM
        ( SELECT name, birthdate, YEAR(birthdate) AS birthyear
          FROM persons
          WHERE birthdate >= '1950-01-01' AND birthdate < '1960-01-01'
        ) AS aa
      GROUP BY birthyear
    ) AS b
  ON a.birthyear = b.birthyear
WHERE MONTH(a.birthdate) = 7 ;

Notice the duplication of code for the derived table a. In more complex queries, code would have to be written multiple times.


Do you connect an application to the load balancer name or the cluster name?

Let's assume this is just a Windows Server Failover Cluster and that there is no Always On availability group involved.

The terms are often used somewhat interchangeably, so I don't know what your DBA or server admin calls them. But in the Failover Cluster management console, there are the following terms and concepts.

Cluster Name. This is the name for the Windows Server Failover Cluster, and is independent of any resources (SQL in your case, but other resources (DHCP, file server, generic services) can be added to a cluster). This is not what you want to point any SQL-using application to.
Node Name. This is the name of each server in the cluster. This would be used if you want to connect to a specific member of the cluster, without regard to whether the clustered service (i.e., SQL) is running on that node or on another one. Generally used for OS management tasks, this is also not what you want to point your SQL-using application to.
Server Name. This is where it really gets confusing. Within each role of a cluster (SQL Server in your case), there is a resource line item called "Server Name". That's the name (and associated IP address) that the role listens to. That name/address is the one that swings back and forth between the nodes whenever there is a failover. This is the one that you want to point your SQL-using applications to. (The IP address associated with this Server Name, and sometimes the name itself, is often called a "Virtual IP Address" or "VIP", referring to the idea that the IP address doesn't belong to a specific server.)

I suspect that the person providing this information to you is using "load balancer" to refer to the Server Name that you should point your application to. The term "load balancer" is a bit mis-used in this case, because there isn't really any balancing happening -- either by round-robin or by resource capacity.


Why would "Drop Database" fail on local SQL Server 2005 Express instance?

Depending on whether or not these are subscriptions or the published database you are going to need to drop the replication data.

MS Published a handy doc with example scripts here http://support.microsoft.com/kb/324401

This one is a general tool to clear out what you need

sp_removedbreplication '<Database name>'


Can I rollback/abort a transaction if no rows were inserted?

Sure.  In MySQL, you can only use if in stored procedures, so this is typically done client side.  Pseudo-code:

int rows = db.ExecuteScalar(
    "start transaction; " +
    "insert into table1 (col1) select col1 from table2; " +
    "select count(*) from table1");
if (rows == 0) {
    db.ExecuteNonQuery("abort transaction");
} else {
    db.ExecuteNonQuery("commit transaction");
}

Rolling back a transaction that did nothing (inserted no rows) doesn't make a whole lot of sense, but I'm assuming you simplified reality for this question.


Join and Count based on Specific User ID in SQL

When you join to tables together you need to think of it comparing each row from the first table to each row of the second table and then returning the ones that match based on the join condition. The join condition is specified in the ON clause.

In almost all cases you want to reference both tables in the ON clause. Let's look at your example in SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE Question (
  QID INT,
  QTxt VARCHAR(150),
  QOrder INT);

CREATE TABLE User (
  UID INT,
  UserName VARCHAR(150));

CREATE TABLE ANSWER (
  AID INT,
  ATxt VARCHAR(150),
  QID INT,
  USERID INT);

INSERT INTO User VALUES
 (1, 'Alex'),
 (2, 'Sarah'),
 (3, 'Tom');

INSERT INTO Question VALUES
 (1, 'Age', 1),
 (2, 'Gender', 2),
 (3, 'Weight',3),
 (4, 'Height',4);

INSERT INTO Answer VALUES
 (1, '30', 1 , 1),
 (2, '27', 1,2),
 (3, '30', 1,3),
 (4, 'M', 2,1),
 (5, 'F', 2,2),
 (6, 'M', 2,3),
 (7, '61', 4,1),
 (8, '165', 3,1);

The ON condition in your case is Answer.QID = Question.QID:

Query 1:

SELECT *
  FROM Answer
  JOIN Question
    ON Answer.QID = Question.QID
 WHERE Answer.UserId = 3

Results:

| AID | ATXT | QID | USERID |   QTXT | QORDER |
|-----|------|-----|--------|--------|--------|
|   3 |   30 |   1 |      3 |    Age |      1 |
|   6 |    M |   2 |      3 | Gender |      2 |

You can use aliases to shorten that query a little:

Query 2:

SELECT *
  FROM Answer AS A
  JOIN Question AS Q
    ON A.QID = Q.QID
 WHERE A.UserId = 3

Results:

| AID | ATXT | QID | USERID |   QTXT | QORDER |
|-----|------|-----|--------|--------|--------|
|   3 |   30 |   1 |      3 |    Age |      1 |
|   6 |    M |   2 |      3 | Gender |      2 |

The same way you can now also join in the User table:

Query 3:

SELECT *
  FROM Answer AS A
  JOIN Question AS Q
    ON A.QID = Q.QID
  JOIN User AS U
    ON A.UserId = U.UID
 WHERE U.UserName = 'Tom'

Results:

| AID | ATXT | QID | USERID |   QTXT | QORDER | UID | USERNAME |
|-----|------|-----|--------|--------|--------|-----|----------|
|   3 |   30 |   1 |      3 |    Age |      1 |   3 |      Tom |
|   6 |    M |   2 |      3 | Gender |      2 |   3 |      Tom |

For more information on JOINs check out my JOIN series here: A Join a Day

It is written about Microsoft SQL Server, but most of it works in other database management system too.


In this context, multi-page allocations means SQL Server needs more than 8K of memory, and it will allocate multiple 8K pages to hold that data.


MySQL Select previous row that matches condition

With MySQL 8 it would be much simpler using window functions.
The problem is with your data that you have no sequential order, so we have to get one by using row number.
This way we can compare the row numbers from the coplete table and those with has was_updated = 0.
This will only work in MySQL because it holds the ORDER BY from the inner select in MariaDB you have to use LIMIT to keep the order.

CREATE TABLE articles
    (`id` int, `category` int, `was_updated` int, `date_added` varchar(10))
;

INSERT INTO articles
    (`id`, `category`, `was_updated`, `date_added`)
VALUES
    (1, 2, 1, '10-01-2020'),
    (2, 2, 1, '11-01-2020'),
    (3, 2, 0, '13-01-2020'),
    (4, 2, 0, '20-01-2020'),
    (5, 1, 1, '22-01-2020'),
    (6, 2, 1, '23-01-2020'),
    (7, 2, 1, '02-02-2020'),
    (8, 2, 0, '29-01-2020')
;

SELECT
    a3.id
FROM
    (SELECT
        a.*, @rown2:=@rown2 + 1 rn
    FROM
        articles a, (SELECT @rown2:=0) t1
    ORDER BY id) a2
    INNER JOIN
    (SELECT
           id, rn,`category`
        FROM
            (SELECT
                a.*, @rown:=@rown + 1 rn
            FROM
                articles a, (SELECT @rown:=0) t1
            ORDER BY id) a1
        WHERE
            `was_updated` = 0) a3
ON

a2.rn  + 1 = a3.rn
AND
a2.`category` = a3.`category`
WHERE a2.`was_updated` = 1

| id |
| -: |
|  3 |
|  8 |

db<>fiddle here


How to design a database for storing phone attribute?

Depending on how you will access the database, there are different options:
Local with single or multiple users:
Think first, create later and you will have a good design.
STEP 1
 1. who will consume this data? that will give you an idea where you should store your data
 2. how much storage capacity do you need? you need plan for backups and recovery
 3. how many users will access that database simultaneously? server capacity
 4. which RDBMS are you planning to use? which one is your preference

STEP 2
 1. create list of attributes you planning to capture, always think of expansion for the future. maybe you will need to add more attributes.
 2. group them in a way that is meaningful to you.
Ex:
Dimension(Width, Height, Depth, Size, Weight),
Configuration(Processor, Ram, Storage, Screen),
Features(Finger Print, Camera Specs Front and Back, etc.)

STEP3
 1. Create Database, create table(s) and define indexes, create diagrams, create relationships if need it.
 2. Configure security settings.
 3. Load testing data for each attribute.
 4. Start using it and make appropriates changes if need it.

Good luck,


How to maintain a global index on a large table undergoing regular partition purges?

version <= 11.2? No.*

But in 12c, there is a new feature called Asynchronous Global Index Maintenance.

The DROP PARTITION operation completes immediately, leaving the global index in usable state. The trick is, that index entries are not maintained, they are orphaned, and these orphaned entries will be cleaned up later. The cleanup process happens automatically in the maintenance window, or it can be started manually.

More details about this: https://richardfoote.wordpress.com/category/asynchronous-global-index-maintenance/

*: There is, but I don't think it is applicable in your case. Deleting (and NOT truncating) all rows from the partition before dropping it leaves the global index in usable state. But this is enormous extra work for the 25% of a 3 TB table.


Changing IP Addresses of an Always On Availability Group in SQL Server 2014

Use the following steps to change IP address of the listener

ALTER ENDPOINT [MyEndpoint]
      STATE = STARTED
      AS TCP (LISTENER_PORT = 5023, LISTENER_IP = (10.x.x.x))
      FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = Windows Negotiate,
                          ENCRYPTION = REQUIRED ALGORITHM AES)
GO

http://www.ryanjadams.com/2016/01/change-availability-group-endpoint-ip/#axzz4yzaoZP8L

https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/change-the-ip-address-of-a-failover-cluster-instance


SQL Server expert Paul Randal addresses this very topic in How does daylight savings time affect disaster recovery?.  It's a relatively short post, so I'm quoting it in its entirety.  Since you were concerned about your transaction log backups, I also highlighed a bit of the post to draw that to your attention.

  It’s common knowledge that SQL Server copes with daylight savings time
  (DST) correctly so why should you care?

  Well, it’s not so common knowledge that at the end of DST when the
  clocks go back an hour (always at 02:00 in the U.S.), SQL Agent
  essentially pauses for an hour (in at least SS2000 onwards). This
  means that if you have a job that’s doing something every 15 minutes,
  there will be a gap of 75 minutes between the job execution at 01:45
  and the job execution at 02:00. This happens because at 02:00, the
  time is set back to 01:00 but the next run time of all the jobs
  remains the same – so your job cannot execute until it’s next
  scheduled time of 02:00. So, in the northern hemisphere every Fall,
  and in the southern hemisphere every Spring, you lose an hour’s worth
  of SQL Agent jobs. Still, why should you care?

  Well, it depends what the jobs are that get delayed by an hour. If you
  have a job that takes a log backup every 15 mins then on the day DST
  ends, there’s actually a gap of 75 minutes between log backups. If you
  have a Service Level Agreement (SLA) that limits the maximum amount of
  lost work to 15 minutes in the event of a disaster, then for those 75
  minutes you’re exposed to potentially not being able to meet that SLA!

  That could be a pretty big deal, especially if something goes wrong
  during that hour (no more or less likely than something going wrong at
  any other time, but still possible). In that case, you need to come up
  with an alternative solution. A couple of ways to get around the
  problem I can think of:

Have someone stay up late during that hour and take manual log
backups.
Switch over to database mirroring, which continually streams the log
to the redundant server and so isn’t affected the DST issue.

  Both of these are viable solutions but I think the best one is to
  create a SQL Agent job that runs at 01:59 and creates extra backup
  jobs to run at 01:00, 01:15, 01:30, and 01:45. I don’t see why this
  shouldn’t be possible. At 10:36 this morning I created a simple agent
  job to print the date to a file and set it to execute at 09:40 – in
  the past. I then set my system time back one hour and the job executed
  perfectly. The only downside of this solution is that you need to
  create and schedule the extra jobs using the T-SQL Agent SPs embedded
  in job steps for your 01:59 job – tedious but not hard. Maybe someone
  could send me a script and I’ll blog it as a follow-on?

  So with DST coming to an end on November 4th this is definitely
  something for you to be aware of even if you don’t want to go to the
  trouble of coping with the extra hour’s exposure. As an aside – the
  dates when DST starts and ends changed this year. KB article 931975
  discusses which parts of SQL Server aren’t aware of the changed dates
  and what you can do about it.


You have to configure the following two files

pg_hba.conf

host all all 0.0.0.0/0 md5

postgresql.conf

listen_addresses='*'

Make sure to restart postgres service after changing the files.

You have to check if the port 5432 is open: http://www.yougetsignal.com/tools/open-ports/

If it's not then add a rule to your iptables:

iptables -A INPUT -s 0/0 -p tcp --dport 5432 -j ACCEPT

0/0: If you want anybody to access it.
You can change it to a specific IP address or range of IP addresses.


Daily database maintenance using SQL Server 2008 and a stored procedure

Instead of using maintenance plans, check out Ola Hallengren's free maintenance stored procedures.  They're way more powerful than maintenance plans and they don't require SSMS (per your requirements) to implement.  You can set them up with any T-SQL connection.


Return List of Database Users and Server Logins

For such purpose i use this login audit script which gives me detailed information of logins and users attached to them along with database and server level permissions. Overall can be helpful to you :

if exists (select * from tempdb.sys.all_objects where name like '%#Login_Audit%')
drop table #Login_Audit
create table #Login_Audit
(A nvarchar (500),B nvarchar (500)default (''),C nvarchar (200)default (''), D nvarchar (200)default (''))
go

insert into #Login_Audit  (A,B,C,D)
SELECT
[Security Report] = '-----SQL SERVER SECURITY AUDIT Report-----','-----','-----','-----'
go
insert into #Login_Audit  (A,B,C,D)
SELECT
[Login count] = 'Total Count of Login','Windows User','SQL server User','Windows Group'
go

insert into #Login_Audit
select a,b,c,d from
(select count(name)a from sys.syslogins where name not like '%#%') a, -- total count
(select count (name)b from sys.syslogins where name not like '%#%'and isntuser=1) b, -      -for login is windows user
(select count (name)c from sys.syslogins where name not like '%#%'and isntname=0) c, -- for login is sql server login
(select count (name)d from sys.syslogins where name not like '%#%'and isntgroup=1 )d;
go

insert into #Login_Audit (A,B,C,D)
SELECT
[sysadmin_server role] = '-- SYSADMIN SERVER ROLE ASSIGN TO---',' ----- ',' ----- ',' ----- '
go
insert into #Login_Audit  (A,B,C,D)
SELECT
[Sys Admin role] = 'Login name',' Type ',' Login Status ',''
go
insert into #Login_Audit (A,B,C)
SELECT a.name as Logins, a.type_desc, case a.is_disabled
when 1 then 'Disable'
when 0 then 'Enable'
End
FROM sys.server_principals a
INNER JOIN sys.server_role_members b ON a.principal_id = b.member_principal_id
WHERE b.role_principal_id = 3
ORDER BY a.name
go

insert into #Login_Audit  (A,B,C,D)
SELECT
[Fixed_server role] = '-- FIXED SERVER ROLE DETAILS --',' ----- ',' ----- ',' ----- '
 go
insert into #Login_Audit  (A,B,C,D)
SELECT
[Fixed_server role] = 'ROLE name',' Members ',' Type ',''
 go

 insert into #Login_Audit (A,B,C)
  SELECT c.name as Fixed_roleName, a.name as logins ,a.type_desc
 FROM sys.server_principals a
 INNER JOIN sys.server_role_members b ON a.principal_id = b.member_principal_id
 INNER JOIN sys.server_principals c ON c.principal_id = b.role_principal_id
--WHERE a.principal_id > 250
 ORDER BY c.name
 go

 insert into #Login_Audit  (A,B,C,D)
 SELECT
[Fixed_database_Roles] = '-- FIXED DATABASE ROLES DETAILS --',' ----- ',' ----- ',' ----- '
go
insert into #Login_Audit  (A,B,C,D)
SELECT
[Fixed_database_Role] = 'Database Name','Role Name','Member','Type'
go
insert into #Login_Audit exec master.dbo.sp_MSforeachdb 'use [?]
SELECT db_name()as DBNAME, c.name as DB_ROLE ,a.name as Role_Member, a.type_desc
FROM sys.database_principals a
INNER JOIN sys.database_role_members b ON a.principal_id = b.member_principal_id
INNER JOIN sys.database_principals c ON c.principal_id = b.role_principal_id
WHERE a.name <> ''dbo''and c.is_fixed_role=1 '
go
------------ used is_fixed = 0 for non fixed database roles(need to run on each database)
 insert into #Login_Audit  (A,B,C,D)
 SELECT
 [NON_Fixed_database_Roles] = '-- NON FIXED DATABASE ROLES DETAILS --',' ----- ',' ----- ',' ----- '
 go
 insert into #Login_Audit  (A,B,C,D)
 SELECT
 [Non Fixed_database role] = 'Database Name','Role Name','Member ','Type'
  go
  insert into #Login_Audit exec master.dbo.sp_MSforeachdb 'use [?]
  SELECT db_name()as DBNAME, c.name as DB_ROLE ,a.name as Role_Member, a.type_desc
  FROM sys.database_principals a
  INNER JOIN sys.database_role_members b ON a.principal_id = b.member_principal_id
  INNER JOIN sys.database_principals c ON c.principal_id = b.role_principal_id
  WHERE a.name <> ''dbo''and c.is_fixed_role=0 '
  go

   insert into #Login_Audit  (A,B,C,D)
   SELECT
    [Server_Level_Permission] = '-- SERVER LEVEL PERMISSION DETAILS --',' ----- ',' ----- ',' ----- '
   go
   insert into #Login_Audit  (A,B,C,D)
   SELECT
   [Server permission] = 'Logins','Permission Type',' Permission_desc ','Status'
   go
   insert into #Login_Audit
   SELECT b.name,a.type,a.permission_name,a.state_desc
   FROM sys.server_permissions a
   INNER JOIN sys.server_principals b ON a.grantee_principal_id = b.principal_id
 --INNER JOIN sys.server_principals b ON b.principal_id = b.role_principal_id
   WHERE b.name not like '%#%'
   ORDER BY b.name
   go

   insert into #Login_Audit  (A,B,C,D)
  SELECT
  [DATABASE_Level_Permission] = '-- DATABASE LEVEL PERMISSION DETAILS ----',' ----- ',' ----- ',' ----- '
  go

  insert into #Login_Audit  (A,B,C,D)
  SELECT
  [DB permission] = 'Database Name','Login Name',' Permission ','Status'
  go

  insert into #Login_Audit
  exec master.dbo.sp_MSforeachdb 'use [?]
  SELECT db_name () as DBNAME,b.name as users,a.permission_name,a.state_desc
  FROM sys.database_permissions a
  INNER JOIN sys.database_principals b ON a.grantee_principal_id = b.principal_id
  where a.class =0 and b.name <> ''dbo'' and b.name <> ''guest''and   b.name not like   ''%#%'''
  go

   insert into #Login_Audit  (A,B,C,D)
  SELECT
  [Password_ Policy_Details] = '--- PASSWORD POLICY DETAILS ----',' ----- ',' ----- ',' ----- '
   go

   insert into #Login_Audit  (A,B,C,D)
   SELECT
   [Policy] = 'Users','type',' Policy status','Password policy status'
   go

   insert into #Login_Audit
   SELECT a.name AS SQL_Server_Login,a.type_desc,
   CASE b.is_policy_checked
   WHEN 1 THEN 'Password Policy Applied'
   ELSE
   'Password Policy Not Applied'
   END AS Password_Policy_Status,
   CASE b.is_expiration_checked
   WHEN 1 THEN 'Password Expiration Check Applied'
  ELSE
 'Password Expiration Check Not Applied'
 END AS Password_Expiration_Check_Status
 FROM sys.server_principals a INNER JOIN sys.sql_logins b
 ON a.principal_id = b.principal_id
 where a.name not like '%#%'
 order by a.name
  go

  insert into #Login_Audit  (A,B,C,D)
  SELECT
  [Orphan_Login_Details] = '--- ORPHAN LOGINS ----',' ----- ',' ----- ',' ----- '
  go

  insert into #Login_Audit  (A,B,C,D)
  SELECT
   [orphan logine] = 'Logins Name','ID','',''
   go

   insert into #Login_Audit (A,B) exec sp_validatelogins
   go

   insert into #Login_Audit  (A,B,C,D)
   SELECT
   [Orphan_USERS_Details] = '--- ORPHAN USERS----',' ----- ',' ----- ',' ----- '
   go
   insert into #Login_Audit  (A,B,C,D)
    SELECT
    [orphan users] = 'User Name','','  ',''
    go
    insert into #Login_Audit (A)
    select u.name from master..syslogins l right join
    sysusers u on l.sid = u.sid
    where l.sid is null and issqlrole <> 1 and isapprole <> 1
    and (u.name <> 'INFORMATION_SCHEMA' and u.name <> 'guest'
    and u.name <> 'system_function_schema'and u.name <> 'sys')

    insert into #Login_Audit  (A,B,C,D)
    SELECT
    [Database_Owner_details] = '--- DATABASE OWENER DETAILS----',' ----- ',' ----- ','   ----- '
    go
    insert into #Login_Audit  (A,B,C,D)
    SELECT
    [DB owner] = 'Database Name','Owener name','  ',''
    go
    insert into #Login_Audit (A,B)
    select name, SUSER_sNAME (owner_sid) from sys.databases order by name asc
     go

    select * from #Login_Audit


How to "UPDATE table SET col = (CASE WHEN ... THEN (SELECT col FROM table ORDER BY col DESC LIMIT 1) END)"?

I'm not sure of your reasoning for wanting to do it within a single query, but the one thing I would ask though is that you think about the maintenance of such a query - you may not be the only person who has to support this.
It took me a while to understand what your query was doing, and in the end I ignored it completely and continued based upon the question you were asking. It would have been much simpler, easier to understand and support if you used a single statement for each step and then wrap it in a transaction for consistency. Depending on the number of rows in the table, it might even be more performant as you won't need to join the table to itself a few times.
However the below will achieve what you want:
CREATE TABLE list (
    id int unsigned NOT NULL AUTO_INCREMENT,
    parent_id int unsigned,
    position int unsigned,
    PRIMARY KEY(id)
);

-- parents
INSERT INTO list (position) VALUES (0),(1);
-- parent 1 children
INSERT INTO list (parent_id, position) VALUES
  (1,0), (1,1), (1,2), (1,3), (1,4), (1,5);
-- parent 2 children
INSERT INTO list (parent_id, position) VALUES
  (2,0), (2,1), (2,2), (2,3), (2,4), (2,5);

WITH a AS
(
    SELECT parent_id, MAX(position) as max_position
      FROM list
  GROUP BY parent_id
)
   UPDATE list AS b
     JOIN a
       ON a.parent_id = b.parent_id
LEFT JOIN list AS c
       ON c.parent_id = b.parent_id
      AND c.position > b.position
      SET b.parent_id = 1
        , b.position = a.max_position + 1
        , c.position = c.position - 1
    WHERE b.id = 11;

SELECT *
  FROM list
 ORDER BY parent_id, position;

Below are the results when moving a item that is not the last child of a parent.
Before

After

Below are the results when moving a item that is the last child of a parent.
Before

After


Is the optimisation fence behaviour of a CTE (WITH query) specified in the SQL:2008 standard? If so, where?

I think it's an implementation detail.

  A conforming implementation is not required to perform the exact
  sequence of actions defined in the General Rules, provided its effect
  on SQL-data and schemas, on host parameters and host variable, and on
  SQL parameters and SQL variables is identical to the effect of that
  sequence. The term effectively is used to emphasize actions whose
  effect might be achieved in other ways by an implementation.1

I think an implementer could evaluate a common table expression 20 times, even in 20 different ways, and still have a conforming implementation. The only relevant issue is whether "its effect . . . is identical to the effect" of the sequence of actions defined in the General rules.

[1]. Section 6.3.3.3, "Rule evaluation order", in a draft of the SQL 2008 standard, having the local filename 5CD2-01-Framework-2006-01.pdf, p. 41 I have no idea where I got it.  Google might know.


Dividing DBCC CHECKDB over multiple days

DBCC CHECKDB is vital for SQL Server databases to be 100% sure that there is no corruption. However, due to databases growing massive in size, its very hard to find a maintenance window when you claim to be 24x7 up. Over the years, SQL Server team has implemented various mechanisms that will detect most common forms of corruptions especially related to Physical corruption caused by hardware.

SQL Server 2005 and up has PAGE_VERIFY = CHECKSUM which can help you proactively detect physical corruption in database pages thereby adding a checksum to each page as it is written to the I/O system and validates the checksum as it is read from the disk.

Also, backup (full or differential) with CHECKSUM will guarantee to detect any I/O corruption caused by hardware.

Hence, from the hardware side of corruption, SQL Server does a good job of detecting it and reporting it. (Make sure to set important corruption related Alerts as well).

That being said, still logical corruption, scribbler induced errors - where in-memory pages are corrupted either by third-party code running inside the SQL Server process or by drivers or other software with sufficient privileges executing in Windows kernel mode and/or SQL Server Bugs, etc are undetectable using above methods and hence CHECKDB comes into picture.

DBCC CHECKDB performs a more thorough checks that includes checking page headers for possible corruption that are not detectable by any other means.

  Any existing scripts out there?

Instead of reinventing the wheel, I would highly recommend you to take a look at Ola's SQL Server Integrity Check solution

Efficiently running DBCC CHECKDB :

You just need to be creative when you are tight in maintenance window having huge databases or high number of databases to run CHECKDB on.

After attending SQLSkills training, what I have implemented in my environment is :

prioritize on what tables are critical to check.
separate the tables into groups with different priorities and then run DBCC CHECKTABLE along with running DBCC CHECKALLOC and DBCC CHECKCATALOG
Create a worker table that will store the table names with priorities. Just make sure that all high prioritie tables (which are massively big) are not in one group else your CHECKDB will not complete at all.
You can even have a timeout column in your worker table that will orchestrate when your CHECKDB will get killed once it has passed the maintenance window
Add how long it took per table to run DBCC CHECKTABLE,  DBCC CHECKALLOC and DBCC CHECKCATALOG. So that you can get a feel on how long it is usually taking for your checks to run.
You can even run with NOINDEX option as it will speed up the operation as it does not check the Non-Clustered Indexes on user tables. This has some advantage as it is not that critical as Data corruption since no data is lost and you can drop and recreate the Index if necessary.

Obviously, Enterprise edition can take advantage of Parallel execution of DBCC statements, but look out for MAXDOP setting as it might end up taking all your CPU. This can be hard limited by Resource Governor.

Note: If you are having SPARSE column, then your CHECKDB will be dead slow as described here.

Finally, its how to prevent database corruption by utilizing all available tool set + your faith in your database server hardware system and most importantly the value of your data.

Some excellent references :

Is CHECKDB A Necessity?
DBCC Checks and Terabyte-Scale Databases
SQLU VLDB Week – Integrity Checks
Improvements for the DBCC CHECKDB command may result in faster performance when you use the PHYSICAL_ONLY option
SQL Server 2008 Database Checking


New Database vs New Table for User Registrations

Having a separate database for every user is an extremely bad idea. It will be a nightmare to keep up.

Your database sounds teeny tiny, I would not worry about it causing problems to have one database. Databases can easily be designed to perform well with one database and can handle terrabytes of information speedily if properly designed.


Same query, same workstation, different execution plans

Differences noticed:

The slow plan uses version 120 cardinality estimator
The fast plan uses version 70 cardinality estimator

You could try using query trace flag 9481 to force use of version 70 - just append OPTION (QUERYTRACEON 9481).

Next, the ItemProperties table is handled differently by both cases. The fast plan assumes that there will be at most one property in each join, while the slow plan assumes that there will be two. Assuming that there is in fact only one property value for all cases in question, you could replace the left joins with inner queries. This should result in identical plans on both servers.

EDIT Another optimization opportunity involves using PIVOT operator to fetch all properties at once, like so:

SELECT
vw_Items.ID
,vw_Items.ResolvedEntityID
,vw_Items.ResolvedDescription
,vw_Items.ItemProductNumber
,vw_Items.ItemDescription
,vw_Items.FilteringType
,vw_Items.ItemCategory
,vw_Items.IsDefault
,vw_Items.IsInventoryType
,vw_Items.Active
,vw_Items.PlantFloorVisible
,[dbo].f_ParseDistanceValue([Length]) AS 'Length'
,[dbo].f_ParseDistanceValue(Width) AS Width
,ISNULL(MaterialDescription.[Description], '(none)') AS 'Material'
,ISNULL(GradeDescription.[Description], '(none)') AS 'Grade'
,ISNULL(MillingDescription.[Description], '(none)') AS 'Milling'
,ISNULL(StateDescription.[Description], '(none)') AS 'State'
FROM
vw_Items
-- Join to get properties
left join (
    select [ItemEntityID],[Property],[Value] from [dbo].[ItemProperties]) p
    PIVOT (MAX(VALUE) for [Property] in (Material,Grade,Milling,State,Length,Width))properties on vw_Items.[EntityID] = properties.[ItemEntityID]
LEFT JOIN DescriptionEntity  AS MaterialDescription ON properties.Material = MaterialDescription.EntityID
LEFT JOIN DescriptionEntity  AS GradeDescription ON properties.Grade = GradeDescription.EntityID
LEFT JOIN DescriptionEntity  AS MillingDescription ON properties.Milling = MillingDescription.EntityID
LEFT JOIN #DescriptionEntity  AS StateDescription ON properties.[State] = StateDescription.EntityID

With PIVOT the server will access the ItemProperties table only once, which will likely result in reduced IO.


Reducing execution time for this gnarly SQL

At the moment, I am using an instance of MySQL 5.6.19 which has the performance_schema enabled on a 2GB x64 Linux laptop (nothing stellar by any means!). My normal response time is approx. 0.5 s - not too bad. What's your setup (RAM, disk config, OS &c.)?

I must say that I'm surprised at 3 seconds on a server. Are you sure that it's not some sort of network issue?

I checked out I/O for the query and noticed something strange - every time the query executes, the order_items table is accessed 27,321 times - which is exactly the same as the number of records. This smells of a correlated subquery - but the SQL is horribly complex and I haven't been able to get to the bottom of it (not enough time). Obviously, you should only want to access the table once (or a few times maybe) at most. Order_status is only read once for example.

It's not really a surprise that your query doesn't use indexes - see this post here which gives some explanation as to why - basically if the optimiser thinks that a FTS (full table scan) is cheaper than using the index, then that's what it will do. This is a function of the size of the table(s) (yours are v. small ===> FTS) and/or percentage of records to be retrieved (yours 100%, again ==> FTS).

I notice that you are using MyISAM tables, for full text indexes? InnoDB now has full text indexes - see here and check the docco. Also (and more importantly), InnoDB allows you to use DRI (declarative referential integrity) which is very important - IMHO, there is no good reason to use MyISAM tables anymore.

Could you explain the business logic behind this query? It strikes me (and this is only an opinion) that it is overly complex for a relatively simple system with 8 tables - has it grown over time and been added to by different people? I am respectfully suggesting that the time may have come for a complete rewrite from scratch rather than tinkering at the edges.

Another problem is that for orders, you have ordered_date as an INT(12) - when I first saw the data, I thought that it was a timestamp. You are creating extra work for yourself if you don't use the correct data type for your columns. Error checking is more difficult.

[EDIT] in response to OP's comments (easy to hard)

1)

INT v TIMESTAMP v DATE/DATETIME

Take a look at these (1, 2, 3, 4) posts. I suppose that INT v TIMESTAMP is relatively trivial (however, see point 4 in the answer here), but INT/TIMESTAMP v DATE/DATETIME isn't. Check out the answer to post 4 - you should be using DATEs/DATETIMEs when you are using INT/TIMESTAMP.

2)

  MySQL 5.6.19, but the server is using 5.5.37. Would upgrading improve
  the performance?

Maybe, maybe not - it's probably trivial in respect to the issues that I'll be going through in point 3 - see InnoDB/Full Text Indexing. If I had to guess, upgrading would decrease performance by no more than a couple of percent (some think that the performance schema adds overhead) and quite possibly be a significant improvement. These things can vary according to many parameters (disk, CPU, RAM, network, server load, phase of the moon...). Overall though, performance should improve release by release although YMMV.

3)

General remarks.

I changed the storage engine for your system from MyISAM to InnoDB. This resulted in the query time being consistently reduced from ~ .45s to ~ .37s (~20%) - a noticeable improvement - maybe you should try it? I can only speak for the testing (not v. thorough) I did on my 2GB x64 Linux laptop. You didn't answer my question about your own server specs. If I'm getting a response time of ~ .4s, I can't for the life of me understand why you're getting 3s? Unless it's (as I suggested) a network issue, or is the server under load when you run the query?

InnoDB now has full text indexing in 5.6 (see my point 2 above) - AFAIK, that will not work in 5.5 (i.e. your table declarations will fail for InnoDB) - another reason to upgrade.

I tried to put in FOREIGN KEY relationships in the tables, but there appears to be problems with your data - I'm getting different counts after reloading the schemas (without FK and with FK). This can mean you have orphaned records, which may or may not affect the result. Anomalies occurred with orders/order_items and customers/customer_addresses. e.g.

mysql> select i.item_id, i.order_id from order_items i where i.order_id not in (SELECT ORDER_ID from orders);
+---------+----------+
| item_id | order_id |
+---------+----------+
|   26892 |     2433 |
+---------+----------+

Worse, none of the order payments appear valid

mysql> select p.order_id from order_payments p where p.order_id not in (select order_id from orders);
+----------+
| order_id |
+----------+
|     2143 |
|     2214 |
|     2216 |
+----------+
3 rows in set (0.00 sec)

mysql>
mysql> select * from order_payments;
+------------+----------+--------+------------+------------+
| payment_id | order_id | amount | date       | is_deleted |
+------------+----------+--------+------------+------------+
|          1 |     2143 |  99.34 | 1405900800 |          0 |
|          2 |     2216 |   5.09 | 1406505600 |          0 |
|          3 |     2214 |  55.10 | 1406505600 |          0 |
+------------+----------+--------+------------+------------+

I couldn't figure out the other relationships in the time available - maybe we can go over them. It may well be that a solid DB structure with overtly declared FOREIGN KEYs may perform better. As a general rule, the more information you give the optimiser, the better.

The point about the optimiser not using indexes when tables are too small and/or too high a percentage of records is accessed still stands - however, there is no harm in doing things correctly - and as your data volumes change/grow, the optimiser may choose different plans - and the more info, the better.

The orphaned record issue (forgetting about performance for the moment) is another GOOD reason to switch to InnoDB. With proper DRI (declarative referential integrity), you cannot have orphaned records, which can be the source of tricky to find bugs.

Finally, just a few words on the system generally. It seems to me that this query is like a runaway train - it has built up and built up momentum and is now overly complex for purpose. Maybe a step back is required? I got to thinking about triggers and using them to populate other tables (similar to a materialised view) and/or even the use of views themselves. You need to have a system that you are comfortable with and that you can change to suit your needs. Some behemoth sitting in the corner spewing out answers that nobody understands is not very conducive to flexibility, upgrading or peace of mind :-).

I would like to do three things (using InnoDB)

1) get all the data types in order (DATE/DATETIMEs when appropriate)

2) sort out the foreign key relationships (need input on that - please feel free to contact me off list for this - my email is in my profile).

3) break this juggernaut down into its constituent components and explore methods of making it better, faster, easier to understand and modify.

I am unable to spend 09:00 - 18:00 at this, but would like to have a look in the evening if that suits and you are interested.


There is no option like that.  A trigger belongs to a table and that is that.  However, if all your triggers use the same procedure, you can easily generate a nice text output with all the CREATE TRIGGER statements.  The only thing to do is to collect the desired table names and then prepend and appent the necessary parts to those.

You can get all table names from a given schema with a query like this:

SELECT p.tablename
FROM pg_tables p
WHERE p.schemaname = 'public'
;

If now you select

'CREATE TRIGGER tuweke
AFTER INSERT ON ' || p.tablename || '
FOR EACH ROW EXECUTE PROCEDURE tuwekeAdjustextract();
'

instead of p.tablename then you are ready.  Copy the output and run it.


Create index on (start_ip_number,end_ip_number) and then check the Explain Plan.The query should user this newly created index.

You can also drop index  ind_ipscan (end_ip_number,start_ip_number) if you are not using it some where else.


If you have "too many" threads doing the restore, they will stumble over each other and might run slower than if you used a fewer number of threads.

Add on innodb_buffer_pool_instances=16

I assume you are using SSDs?  Otherwise, some of the settings are too high.

If you are I/O-bound, then probably there is nothing you can do to speed it up.  Except...

In the future, consider setting up LVM so that both dump and restore can be on the order of one minute, even for multi-TB dumps.  This is because of the "copy of write" technology at the block level of the OS and disk.