No One Can Login SQL Server Instance Even Though Its Services Are Running

That seems to be a firewall issue.
Open the Windows Defender Firewall with Advanced Security applet from a client machine on the same network, connect to the server, and see if you can enable the SQL Server or or TCP port. By default the port is 1433, but it might be some other port. You can use SQL Server Configuration Manager to determine which port the server is running on.
You might need to enable Remote Management of the Server Core machine.  Connect to it via RDP, open a command-prompt, and run sconfig.  There is an option to enable remote management from there.


Parallel Execution Causing Duplicates [SQL Server]

Fix the application. There is no magic setting in the database that will prevent a badly written application from inserting wrong data in the database.

Fixing the application means using the appropriate isolation levels and keeping transactions as short as possible.

Here is an example of how you check and decrease quantity in the same query:

IF OBJECT_ID('tempdb..#stock') IS NOT NULL DROP TABLE #stock;
IF OBJECT_ID('tempdb..#basket') IS NOT NULL DROP TABLE #basket;
GO

SET NOCOUNT ON;

CREATE TABLE #stock (
    product varchar(50) PRIMARY KEY,
    quantity int
);

INSERT INTO #stock
VALUES
     ('pants', 30),
     ('shirt', 18),
     ('shoes', 4);

CREATE TABLE #basket (
    product varchar(50),
    quantity int
);

-- At this point the stock covers the basket
INSERT INTO #basket
VALUES
    ('pants', 2),
    ('shoes', 4);

-- Uncomment this statement to make it fail
INSERT INTO #basket
VALUES
    ('shoes', 1);

DECLARE @invalid_stock TABLE (
    product varchar(50) PRIMARY KEY,
    quantity int
);

BEGIN TRAN

-- Here you decrease the stock of the quantity required by your basket
-- If you have stock that was turned to negative by the operation, it
-- will be inserted in the @invalid_stock table.
INSERT INTO @invalid_stock
SELECT *
FROM (
    UPDATE s
    SET quantity = s.quantity - b.quantity
    OUTPUT inserted.*
    FROM #stock AS s
    INNER JOIN (
        SELECT product, SUM(quantity) AS quantity
        FROM #basket
        GROUP BY product
    ) AS b
        ON s.product = b.product
) AS newstock
WHERE quantity < 0;

IF EXISTS (SELECT * FROM @invalid_stock)
BEGIN
    ROLLBACK;
    THROW 50001, 'Not enough stock to complete this order!', 1;
END
ELSE
BEGIN
    COMMIT;
    PRINT 'Transaction complete!'
END


How to find object name given in a problematic query

The AWR report will contain the SQL_ID.  You can query V$SQL with this to locate additional metadata such as the MODULE, ACTION, PARSING_USER_ID.  Also, if the SQL came from a stored procedure, the PROGRAM_ID, PROGAM_LINE# columns point to the (stored procedure) object concerned.

I've got an example of the latter on my blog

https://connor-mcdonald.com/2016/01/20/problematic-sql-plsql-is-your-friend/


Instead of Trigger Continue with operation after custom logic?

Posting this as an answer to address the core question:

  Is there a way to perform my history insert and then tell SQL Server to continue with the original INSERT, UPDATE or DELETE without writing those myself?

No, it is not possible to have an INSTEAD OF trigger just perform the original DML for you. This is why they are called INSTEAD OF and not BEFORE - they replace the original action with whatever logic you want to perform, which may or may not include DML against the original target table, depending on the scenario (you may want to prevent the insert instead of rolling it back, you may be migrating to a new table and using this as an interim solution, you may have distributed or otherwise not-directly-updateable views, etc).

If you feel you have a valid use case for INSTEAD OF, then you will have to manually code the DML they're replacing. I don't think you do, so, as the others have suggested, use an AFTER trigger.


What's the best setup for a MySQL multi master replication for a POS in a restaurant?

I would go with Galera.  It is self-healing after a single node dies and comes back online.  If, however, the entire network dies (no machine can talk to any other machine), NDB Cluster may be the only solution since it repairs outages by user-defined "conflict resolution" algorithms.

Plain Replication has a lot of potential failings, and needs more hand-holding.  Semi-sync requires that part of the network stay up.  When semi-sync can't do its "sync" it quitely reverts to "async", thereby losing what you were expecting.

100 nodes in any replication technology will be a challenge.  Going past 5-10 nodes needs special casing for larger networks.  For plain Replication and semi-sync, "relay" servers are desirable; they take extra setup and require extra handling in failure situations.  I have not heard of NDB or Galera being shaken down for larger networks that maybe a dozen nodes.  Some people have multiple Galera clusters with async between the clusters.

You might be better off spending time and money to hardening the network first.

A large department store might have 10 checkout counters, each with 4 POS.  That hints at hard-wired (very reliable) 4-node cluster of some kind, plus something more fragile between the clusters.  If you use wifi, be sure to encrypt all traffic.

With plain Replication, a dual-Master, single-writer (or 3-node Galera cluster) is possibly the best.  It does require that all POS be able to write to the Master, else they are offline.  It also requires extra coding to deal with "critical reads" -- that is where you post something, then need to immediately read it back, and must be sure any replication lag is accounted for.  (Note: I am not having any writes occur on the POS, only indirectly through the Master and Replication.)

Chew on all that.  Sorry, I don't have a "Do this" answer.


SQL Server 2005 Cluster Service pack 4 upgrade failure at remote user account

I faced the same problem with installing sql server 2005 service pack 3 on windows 2008 R2 cluster. On node1, i brought the sql server group online and pause the Node2 in cluster Manager and then ran the set up on node1. Once setup completed, i resume the node2 and failed over the SQL Server group on node2. Now on Node2, i pause Node1 in cluster manager and run the setup on node2. Once setup was completed, i resume Node1 and rebooted both nodes. I verified the version by failing over the sql server on both nodes.


User permissions for Microsoft SQL Server

GRANT CONTROL ON SCHEMA :: BobsSchema To Bob;

Where Bob is the users name. See MSDN.


Oracle: Tables and Views missing from the all_objects dictionary

I tracked it down to an SCHEMA.ALL_OBJECTS view attached to the schema that was restricting the tables rather than using the SYS.ALL_OBJECTS view.


According to the MySQL Documentation, this feature was deprecated: https://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html#mysql-nutshell-deprecations

Changes to account-management statements make the following features obsolete. They are now deprecated:

Using GRANT to create users. Instead, use CREATE USER. Following this practice makes the NO_AUTO_CREATE_USER SQL mode immaterial for GRANT statements, so it too is deprecated.

Using GRANT to modify account properties other than privilege assignments. This includes authentication, SSL, and resource-limit properties. Instead, establish such properties at account-creation time with CREATE USER or modify them afterward with ALTER USER.

The solution is to use two separate statements. One to ALTER USER, then a second to GRANT privs.
ALTER USER 'br' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON simple_cms_development.* TO 'br'@'localhost';


Replicate to a secondary database that also allows edits

If you need bidirectional synchronization, have a look at merge replication. (Good introduction: http://www.sqlservercentral.com/stairway/72401/)

However, you question sounds like you don't want those changes to be applied to the master sales database. In that case I would setup shadow tables that take the changes and use replication to load the master tables. The logic to write to the shadow tables and read from the shadow tables if there is a row, form the master table otherwise could be placed in a view with an instead-of trigger. The following is a conceptual example of that model:

create table dbo.product-master(Id int, ...);
create table dbo.product-shadow(Id int,....);
GO
CREATE VIEW dbo.product
AS
SELECT m.Id,
       CASE WHEN s.ID is NULL THEN m.col1 ELSE s.col1 END AS col1, --show shadow row if it exists, master row otherwise
       CASE WHEN s.ID is NULL THEN m.col2 ELSE s.col2 END AS col2,
       CASE WHEN s.ID is NULL THEN m.col2 ELSE s.col3 END AS col3
FROM dbo.product-master m
LEFT JOIN dbo.product-shadow s
ON m.Id = s.Id;
GO
CREATE TRIGGER dbo.product-IUD INSTEADOF INSERT,UPDATE,DELETE
AS
BEGIN
  --use INSERTED and DELETED virtual tables and the MERGE statement to apply changes to the shadow table
END;
GO

To load the master tables you could use any kind of replication (see link above for an introduction).


MySQL master-slave replication over an unstable internet link

Increase some of these:  net_retry_count, slave_transaction_retries, slave_net_timeout.
Monitor the slaves to catch them in the case of a really long network break.  Then do SLAVE STOP; SLAVE START;
Make sure expire_logs_days is long enough so that you won't lose binlogs.  There is no "too big", except that you could eventually run out of disk space.

Everything should work find after the Slave gets going again.  (There will be a delay as it "catches up".)


Fetch records of postgres and store it in different table , if one column is null

Well, normally id is used to implement primary key which is not nullable... But I guess your problem is that you're getting dirty data from another source and try to sort it out ?

You could either design correctly (with all due constraints) your table and use a tool like pg_loader to load your data. It will create a error file with all the lines that don't fit.

Or you can import all your data into a junk table (which is the approach you seem to have chosen) and perform SQL on top of that to sort out your data.

You can try something like that:

/*Processing "nulls" that can be a problem*/
with nullable_rows as
(
  delete
  from input_table
  where id is null
    or  emp is null
    /*Add any other condition here*/
  returning *
)
insert into null_table
(id, emp, salary, year)
select * from nullable_rows;

/*Processing correct rows*/
with correct_rows as
(
  delete
  from input_table
  where id is not null
    and emp is not null
    /*Add any other condition here*/
  returning *
)
insert into sucess_table
(id, emp, salary, year)
select * from correct_rows;

That's if you load your data out of another source.

If another source is inserting dirty data in your database, use constraints to prevent it...


Storing historical records and extracting data from a given period

The problem is that when an area is merged and its code is reused, you have that information, but when an area is merged and its code is deprecated, you don't have it. In other words, you don't have the information that an area is deprecated.

Now, I see two solutions. First, keep the schemas as is and whenever an area is deprecated, add a new row as it was a new area and just states it is deprecated, e.g.

INSERT INTO myTable(code, area, change_date)
VALUES(86001, 'deprecated', '2015-01-01')

So in your query it will show as 'deprecated' or you filter it out.

A second, and possible best option, is to have a new column that states when (and if) an area was deprecated:

ALTER TABLE myTable ADD deprecated DATE;
UPDATE myTable SET deprecated = '2015-01-01' WHERE gid = 1;

So, on your query you can simple add the filter:

(deprecated IS NULL OR deprecated >= '2016-01-01')

The full code:

SELECT DISTINCT ON (code) code, area, change_date
FROM myTable
WHERE change_date < '2016-01-01'
    AND (deprecated IS NULL OR deprecated >= '2016-01-01')
ORDER BY code, change_date DESC;

As another option for non-deprecated areas, you can set them as 'infinity' instead of NULL, so the query does not need the deprecated IS NULL condition, as 'infinity' will always be higher or equal than any other non-null value.


How to profile trigger database performance impact

There's no separate "trigger performance impact"; trigger code is essentially compiled into the execution plan of the statement that causes the trigger to fire, so measuring average DML statement response time before and after introduction of triggers should give you a general idea of the impact.

Of course, triggered actions will certainly affect other workloads, not only the triggering actions. I'm sure your DBAs have records of baseline database performance that they can compare to what happens after the triggers are added (presumably on a test system).

As to monitoring tools, DB2 is fully instrumented to provide detailed views of its performance from various aspects, using event monitors and monitor views/stored procedures, so you don't need to set up anything in addition to what's always there.


Checking for BCNF in a relational database through FDs?

First of all, you say:

  (TFN → ID is redundant right?)

This dependency is not redundant, since otherwise you do not know that TFN is a key, and you lose an essential information. So you must include it in the set of functional dependencies.

For phone, if each phone is uniquely associated to a person, and each person can have only one phone, then you have these functional dependencies:

ID → TFN
ID → Name
ID → Phone
TFN → ID
Phone → ID

There are three keys, ID, Phone and TFN, and the relation is in BCNF.

However, if each person can have more than one phone, than the situation is completely different. You have this set of functional dependencies:

ID → TFN
ID → Name
TFN → ID
Phone → ID

the key now is only Phone, and the relation has redundancies (since for each phone related to the same person, you have to repeat the ID, TFN, and Name of that person). The relation is not in BCNF, and to bring it in such form you have to decompose it in the two relations:

R1(ID, TFN, Name)
R2(Phone, ID)

the first with the dependencies:

ID → TFN
ID → Name
TFN → ID

and the second one with the dependency:

Phone → ID

so that no dependency is lost with the decomposition.


Would refreshing a materialized view in a transaction allow me to have test-only specific data on it?

Not sure what you are after here the questions has some contradictions in it

Materialized views work like tables so all the clients will being looking at the same data defined by the Query that created it.
You want the clients to look at different data sets for the test.

The problem is wanting the clients to look at different data sets when there looking at same data  as Materialized views are not private to the connection

Rollback is not going to help as it going to cause locking issues and conflicts from multiple clients trying to create the same Materialized View.

If the desire is to have the clients not share the data for the test,  use temporary tables instead...

Select * into temporary table students from students

this creates a identical temporary table of the students table in the private schema for this connection and will populate data into table based on the where clause

also can use

Create temporary table students like students.

Nice thing about private_name/schema_space is this is search first prior to searching public or any other schemas defined in search path.

So all the commands sent by the client will use this table.

INSERT INTO students (name,class, test5) values ('MIZUKI MINOHARA','A1'),
 ('DIMITRIOS DESYLLAS', 'C3'),('YAMAGUCHI SUZUKI','B7');

-- RUN TESTS

There is a gotcha here and that is if  queries specifies the schema this will not work

select * from public.student

will always match to public.student and not match the temporary table that was created.

Another big benefit using temporary tables is postgresql cleans up and deletes all the temp tables once the connection is closed or transaction ends depends on the command that created the temp table.


There is not, as of 10.3.7, a native JSON type on mariadb server. JSON is only an alias for LONGBLOB for compatibility reasons with MySQL: https://mariadb.com/kb/en/library/json-data-type/

While there is a library of function to work with JSON https://mariadb.com/kb/en/library/json-functions/ (select and transform it); there isn't really a native type, so all input and output of those functions is really of type string. While you could technically implement a recursive json parser with a stored procedure, I would recommend to just read it and compare it with client libraries or other client utilities (for example, https://stackoverflow.com/questions/25851183/how-to-compare-two-json-objects-with-the-same-elements-in-a-different-order-equa ).

Despite the claims by MariaDB that it is a drop-in replacement of MySQL, because MariaDB is no longer a 100% compatible fork, functionalities that are available on one software are no longer available on the other and viceversa.


How can I obtain the full text for a query that appears in sys.dm_pdw_exec_requests?

This came up the other day and the reply was "switch on Auditing":

https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-auditing-overview

I have not tried this myself yet so I'm not sure if you get the full text of the query.  I plan to test this feature in the next few weeks so will update the post, or you can : )

I have been having similar issues with Power BI queries which seem overly complicated, but I suspect this is a consequence of an overly complicated Power BI model.  One alternative I have thought of (but not yet done) is to create a schema-only model of the database in SQL Server 2016.  You could accurately reflect the columnstore, heap or clustered index and partitioning index choices, but not the distribution and data movement aspects.  This would at least give you access to tools like Extended Events (or Profiler if you're old-school), visual Execution Plans (although you can you EXPLAIN plans), bearing in mind the database engines are different.  I am yet to determine if the effort of creating and maintaining the copy is worth it, but I will update if I progress my thinking on this.

This was also discussed here:

https://stackoverflow.com/questions/41292662/identifying-needed-statistics-azure-sql-data-warehouse


Using SUBSTR and CONCAT to get the required Result

if the 'DEP2' does not exist it has to use 'DEP'

Typical NVL/COALESCE situation, not something that should be handled with string functions.

SELECT DEP, DEP2, COALESCE(DEP2, DEP) CENTRE
FROM DEPARTMENT


SELECTs in Database Change Log and Source Control

Well, in the article you reference, he specifically states "The first category of scripts fall out of the realm of database changes."  That said, I've worked with plenty of legacy codebases that had select statements in source control; they were just in the application code.  (So, subject to all the problems you mention and hidden from the DBA.)

Before answering your question about selects specifically, It seems important to mention that the whole point of having a source control system is to be able to trace back where things went wrong.  That's why people might choose one over, say, an archive of daily backups.  In your second bullet point, you mention that your select statement might become invalid, but if you have a version control system you would be able to check out a previous version where the select statement still matched the schema.  Once you figured out what it was intended to do, you could check in a corrected version of that statement at a later version.

I guess I've now answered your first through third points.  It's unlikely (though entirely possible) that a select statement sitting in it's own sql file would be used by some other piece of software, but consider that a select statement sitting inside a stored procedure is equally able to become out-of-synch with schema.  Expanding on point 3, it's precisely because we recognize that this is a problem that we have source control, it does not defeat the purpose of SC, dealing with that problem is the purpose.

I can't really think of a case where storing the results would be helpful, but if you were worried you'd be unable to interpret it in the future, perhaps a description in a comment would be useful?

If you have select statements that are consumed by application code, powershell scripts, etl, reports, or possibly even by the ops team for routine tasks, these all seem like good candidates for being considered a part of your codebase.  Remember, the purpose of the source control is not as a backup strategy or as logging, it is to be able to bring up an internally consistent version of your code at a particular point in time.  That is why the answer to your last point is no.  this is about the code and the schema, not so much about your data.  The article that prompted this does a good job of explaining why it's challenging to put anything but purely reference data in your source control system.


Problem with enabling System Audit (Firebird's Trace and Audit Services)

There are a number of things wrong with your config file:

enable true should be enabled true
log_filename C:\DB_trace\audit_trace_database.log should be log_filename C:\\DB_trace\\audit_trace_database.log
log_connection true should be log_connections true
log_transaction true should be log_transactions true

Most of these errors could have been detected by inspecting the firebird.log file.
And although the documentation in fbtrace.conf says:

Pattern is either database name without path or "SIMILAR TO"-based
regular expression which is matched against fully qualified database
path name.

It looks like you always need to actually specify a SIMILAR TO pattern matching the path of the database, for example %[\\/]WAPTEKA.FDB.
The corrected entry, which works on my machine, is:
<database %[\\/]WAPTEKA.FDB>
  enabled true
  log_filename C:\\DB_trace\\audit_trace_database.log
  max_log_size 0
  include_filter %(INSERT|UPDATE|DELETE)%
  log_connections true
  connection_id 0
  log_transactions true
  log_statement_prepare true
  log_statement_free true
  log_statement_start true
  log_statement_finish true
  log_procedure_start true
  log_procedure_finish true
  log_trigger_start true
  log_trigger_finish true
  log_errors true
  log_warnings true
  log_initfini true
  log_sweep true
  time_threshold 0
  max_sql_length 65536
</database>

The syntax in Firebird 3 is slightly bit different, check the Firebird 3 release notes for details


Could running ANALYZE on all databases on large InnoDB tables show some performance increase?

There are a few situations where running ANALYZE TABLE is beneficial

When you have innodb_stats_auto_update=0 set
When your statistics are already "messed up" and need a fixin'

If your table statistics frequently get into a "messed up" state (which would cause bad or strange query plans), as can be the case when a table has nightly purge or bulk insert jobs, you should consider setting innodb_stats_auto_update=0 and running a periodic manual ANALYZE TABLE.  Otherwise, let sleeping dogs lay and don't run it.

One caveat about ANALYZE TABLE is that it can cause problems if you have long running queries against the table because it flushes the table.  This means that, if you run ANALYZE TABLE while a query is running, no other tables will be able to access the table until the first query finishes.  I wrote ab-analyze-innodb to help address this problem by searching the process list to identify whether or not the table is actively being accessed.


Validate three digits before decimal and after decimal point six digits

You're better off using a DECIMAL(9,6) data type in the database.

And then you've validate with a simple numeric BETWEEN

However, if you insist on a character type, use CHAR instead of NCHAR - you're only accepting periods and numbers, so there's never any "Unicode"; cut your space use in half.

And then you could validate with something like

a.latitude LIKE '[0-3][0-9][0-9].[0-9][0-9][0-9][0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-3][0-9][0-9].[0-9][0-9][0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-3][0-9][0-9].[0-9][0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-3][0-9][0-9].[0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-3][0-9][0-9].[0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-3][0-9][0-9].[0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-3][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9][0-9].[0-9][0-9][0-9][0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9][0-9].[0-9][0-9][0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9][0-9].[0-9][0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9][0-9].[0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9][0-9].[0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9][0-9].[0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9].[0-9][0-9][0-9][0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9].[0-9][0-9][0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9].[0-9][0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9].[0-9][0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9].[0-9][0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9].[0-9]' ESCAPE '~'
OR a.latitude LIKE '[0-9]' ESCAPE '~'

Note that doing this with character types is both annoying and not nearly as performant.

Note that the leftmost digit is limited to 0-3, since you'd never have a latitude or longitude starting with a 4 or higher (I'm assuming degrees).

Since you're on SQL Server 2008 R2, you may also want to read the Stackoverflow question Why use the SQL Server 2008 geography data type?.  A very brief summary is: DECIMAL(9,6) is significantly smaller, and for a simple SELECT Lat, Long is significantly faster.  However, to do accurate proximity searches (which is often why coordinates are stored), distance calculations (also often why you need them), or areas and the like, the geography type is much simpler and almost certainly more accurate.


Fixing a MySQL query that miscounts cases where count is 0

The problem is that outer joins produce NULL values in columns of the (right) table for rows of the left table that don't have a match. So you should use COUNT(entries.id_widget) which counts not null values in that column and not COUNT(*) which counts rows.

Some more notes:

you can use aliases for tables to make the query more readable.
It's good to prefix all columns with the table name (i.e. widget.id_user) or alias (w.id_user). Otherwise the query will break if a column with the same name is added later in table entries.
Unless the column campaign_name is the primary key of widgets or has a unique constraint, you should not use GROUP BY this way. All columns in the SELECT list should be either used in aggregate functions or appear in the GROUP BY list. MySQL does no such check and indeterminate results can be returned by queries that do not follow this rule.
If the columns (in SELECT) depend on the columns in GROUP BY (and that would be true in this case if campaign_name had a unique constraint), that's one exception that the results will be determinate and you can use this "feature" of MySQL. You should also check that ONLY_FULL_GROUP_BY setting is disabled if you want to use this.
For more details, read the MySQL Documentation: MySQL Extensions to GROUP BY and the discussions here: Why does MySQL add a feature that conflicts with SQL standards?.

The query becomes:

    SELECT w.campaign_name, w.enddate, w.active, w.id_widget,
           COUNT(e.id_widget) AS entries
    FROM widgets AS w
      LEFT JOIN entries AS e  ON w.id_widget = e.id_widget
    WHERE w.active IN ( 0 )
      AND w.id_user = 8
    GROUP BY w.id_widget,
             w.campaign_name,
             w.enddate, w.active ;


Mysql query to find last entry for each of the last 7 days

SELECT a1.*
FROM WaterDepth a1
INNER JOIN ( SELECT MAX(`date`) as `max`
             FROM WaterDepth
             WHERE `date` >= CURRENT_DATE - INTERVAL 7 DAY
                 -- or 6 day? 7 day gives 8 different dates...
                 -- or today record not needed? if so add
                 -- AND `date` < CURRENT_DATE
             GROUP BY DATE(`date`) ) a2 ON a1.`date` = a2.`max`


Length of LOB data (2135980) to be replicated exceeds configured maximum 65536

This configuration can be changed using Management Studio by right-clicking the server -> Properties -> Advanced tab -> Miscellaneous -> Max Text Replication Size.
Or you may want to try :

EXEC sys.sp_configure N'max text repl size (B)', N'-1'
GO
RECONFIGURE WITH OVERRIDE
GO


How to import the data of postgres 8.x to postgres 9.x

What you need is a dump / restore. However, for this to work, you need the old PostgreSQL 8.4 server running. The new version 9.1 is not binary compatible and cannot use the files in postgres/8.4/data directly.

If you have no backup and cannot run PostgreSQL 8.4 on your machine any more, you are out of luck. Why is it that you cannot install PostgreSQL 8.4 any more? You'll have to, if you want your data.

On a side note:
It is recommended that you use the pg_dump and pg_dumpall programs from the newer version of PostgreSQL, to take advantage of any enhancements that may have been made in these programs.


Fetch Only new data from auto-updating online sql database

Assuming id is an incrementing number or something else which grows every time you insert a new row (such as a timestamp), this should be quite straightforward:

First read the last id value in the csv file from the previous run, and then use this in your query line:

sql = 'SELECT * FROM schema.table WHERE id > %d ORDER BY id ASC' % id

UPDATE:

If id is not an incrementing key, e.g. it's a UUID, then this becomes much harder. Other DBMSes (e.g. Oracle) have rowid which could probably have been used here, though that is not a MySQL feature. MariaDB has rowid in its CONNECT storage engine, so you could set up a local MariaDB instance, install the CONNECT engine, create a CONNECT table connected to your remote MySQL table schema.table with the special rowid column using a statement such as:

CREATE TABLE connect_table (
 col1 float,
 col2 datetime,
 col3 varchar(20),
 row_id int special=rowid
)
ENGINE=CONNECT TABLE_TYPE=MYSQL DBNAME=mytest TABNAME=table
CONNECTION='mysql://root:password@localhost';


I fear the where clause with the in is causing problems.  Try joining to the inserted table instead.  Here are two examples - one with the in clause and one with a join to the inserted table.  The results are different using my sample data.
drop table if exists shipment;
drop table if exists alphauniversal;
go
create table SHIPMENT (cm_controllerID int,au varchar(100))
create table alphauniversal (controllerid int, au varchar(100))
insert into alphauniversal values(1,'abc')
insert into alphauniversal values(2,'xyz')
go
Create Trigger [dbo].[trig_OnInsert] ON [dbo].[SHIPMENT]
For Insert
    AS
        BEGIN
            UPDATE si
            SET si.au = au.au + ' - ' + si.au
            FROM SHIPMENT si
            JOIN alphauniversal au
            ON si.cm_controllerID = au.controllerid
            WHERE si.cm_controllerID IN (Select si.cm_controllerID FROM Inserted)
            --JOIN inserted i on i.cm_controllerID=si.cm_controllerID
        End
go
insert into SHIPMENT values(1,'test1')
insert into SHIPMENT values(2,'test2')
select * from shipment

cm_controllerID     au
1                  abc - abc - test1
2                  xyz - test2

drop table if exists shipment;
drop table if exists alphauniversal;
go
create table SHIPMENT (cm_controllerID int,au varchar(100))
create table alphauniversal (controllerid int, au varchar(100))
insert into alphauniversal values(1,'abc')
insert into alphauniversal values(2,'xyz')
go
Create Trigger [dbo].[trig_OnInsert] ON [dbo].[SHIPMENT]
For Insert
    AS
        BEGIN
            UPDATE si
            SET si.au = au.au + ' - ' + si.au
            FROM SHIPMENT si
            JOIN alphauniversal au
            ON si.cm_controllerID = au.controllerid
            --WHERE si.cm_controllerID IN (Select si.cm_controllerID FROM Inserted)
            JOIN inserted i on i.cm_controllerID=si.cm_controllerID
        End
go
insert into SHIPMENT values(1,'test1')
insert into SHIPMENT values(2,'test2')
select * from shipment

cm_controllerID au
1               abc - test1
2               xyz - test2

The trigger code runs as part of the transaction that actually inserted the rows in the base table.  If the trigger encounters a fatal error, the transaction should roll back everything that was done during that transaction.  It's an all-or-nothing process.
You cannot swallow an exception inside a trigger even with try/catch logic.  Any exception inside a trigger WILL abort the transaction.


Which PgBackRest policy for differential backups in PostgreSQL

Depending on your archive (WAL) retention policy, maybe.

Nightly diffs: Relying solely on your nightly differential, your PITR recovery window would only go as far as last Sunday's full backup. From their docs, "Differential Backup: pgBackRest copies only those database cluster files that have changed since the last full backup".

Let's say today it's Thursday Sept 19th, and that you want to recover up to last week's Tuesday (the 10th). You can't use your only differential, the one taken tonight (the 19th) at 1am, which only contains changes since the last full backup take on Sunday the 15th. If you restore your oldest full backup, the one taken on Sunday 8th, you have no differentials taken from Monday 9th until Sat 14th to apply to it.
WAL archives: So, can you use archived WAL files then, if you apply them after restoring the full backup taken on Sunday Sept 8th? Maybe - this is the part where I think the documentation is a bit unclear: "pgBackRest automatically removes archived WAL segments when expiring backups (the default expires WAL for full backups based on the repo1-retention-full option)".

I am not sure if they mean that last week's WALS are kept around or not; sounds like maybe yes, since the oldest full is not expired yet.. but the statement in brackets gives me some pause.
Regardless of #2, my recommendation would be to find a solution that doesn't rely solely on WAL archive retention. You may either configure a higher retention for your nightly diffs so you don't need to rely solely on WAL archives (ideal but heavy on disk usage), or only do a full backup every 2 weeks (I don't like this option myself, since I prefer the peace of mind of a weekly full)

Or you can do what we do, nightly incrementals: "Incremental backups cannot be expired independently — they are always expired with their related full or differential backup."

To achieve the same PITR window of 14 days we are doing:

Sat 1am full backups, retention 2
Nightly incrementals from Sun-Fri

We find the above to be a good balance for us: the nightly incrementals are relatively small and are quicker than a differential, especially as we get farther from the Saturday full.

Source: https://pgbackrest.org/user-guide.html (visited in 2019-09-19)


Download the replication snapshot file using FTPS

Usually, SFTP is a specific port that you specify and the protocol sees the port and negotiates the encryption and shares the public key. It looks like you can use the @ftp_port argument in your publication to specify the appropriate sftp port (usually 22).

ftp technet article: https://technet.microsoft.com/en-us/library/ms146956(v=sql.105).aspx


It appears you're using Dialog Level Security. This means you have deployed remote service bindings, users and certificates, successfully exchanged certificates and and granted SEND permission on the target service. For example, if you have a DB1 and a DB2, and a service S1 in DB1 and a service S2 in DB2, then, to begin a secure dialog from S1 to S2, you must have also:

create a certificate C1 in DB1 owned by user that owns S1, lets say U1 (usually dbo)
create a certificate C2 in DB2 owned by the user that owns S2, say U2 (again usually dbo, but this time dbo of DB2)
export public key of C1 and C2
create in DB1 an user, say U2' that will represent the S2 identity in DB1
import C2 public key as a certificate C2' in DB1, owned by U2'
create, in DB1, a remote service binding object that binds S2 to U2'. This way BEGIN DIALOG will know to use C2 public key to encrypt messages for S2
create in DB2 an user U1' that represents U1's identity in DB2
import C1 public key in DB2 as a certificate C1', owned by U1'. Because messages from S1 are signed with C1 private key (owned by U1), they will be identified in DB2 as being sent by U1' (the owner of C1', the corresponding public key).
grant SEND on service S2 to U1'

Again, if you say that messages between S1 and S2 are flowing fine, then all the above steps are already correctly done (not a small feat!).

If I understand correctly now you want to add a new service, say S3, also located in DB2, if I understand correctly, that needs to also accept messages from S1. I will make a simplifying assumption that S3 is owned by the same user as S2 (since usually the owner is dbo, this assumption is usually true). If so then you already have the necessary users/certificates exchanged, all you need to do tell SSB to use them:

in DB1 create a new remote service binding, RSB3, for service S3, using the same user U2'. BEGIN DIALOG will use the same certificate C2' to encrypt messages for S3 as for S2, but this is OK since both S3 and S2 are owned by the same user
in DB2 grant SEND permission on service S3 to user U1'.

This should be it, now S1 can talk with S2 and also with S3.

This certificate exchange and user ownership logic is used to 'map' a user from DB1 to DB2, without relying on Windows domains. This mapping is done via exchanging public key certificates and then following the certificate ownership: In DB1, U1 (ie. the owner of S1) owns a certificate with a private key, C1. The same certificate's public key exists in DB2 and is owned by U1' (a user in DB2). Therefore U1 'maps' to U1'. Whenever ssbdiagnose complains that a user cannot be 'mapped using certificates' it means that this chain is broken somewhere.


database connection and sessions in an example of social media

Authentication and authorization for public site users is typically done in the application layer. Database access is then done by application services using service credentials for the database connection rather than end-user credentials. The application service serves requests for many end-user sessions (not to be confused with database connections/sessions). This sort of n-tier architecture greatly improves scalability.

In cases where MARS (multiple active result sets) is used, a single database connection may be used to interleave query execution. The same connection credentials are used for each session. Note that MARS is used for specialized data access needs. Typically, there is a one-to-one relationship of client application database connections and sessions.

Connection pooling also is very important for scalability. Pooling avoids the overhead of establishing physical network connection and authorization after the initial connection is made. When a query is executed on a reused pooled connection, the session is reset automatically without an additional round trip. This is why polling is used by default with many SQL Server Client APIs.


PostgreSQL - Generate unique name on insert based on other columns

You can achieve this with triggers and trigger procedures (trigger functions). Read Tutorial on triggers and from PostgreSQL documentation 40.9. Trigger Procedures. The following is the code for both the procedure and the trigger (there might be even more elegant ways to form the procedure):

CREATE OR REPLACE FUNCTION create_people() RETURNS trigger AS $$
    DECLARE
    u_num integer;
    BEGIN
    u_num := (SELECT count(*) FROM people WHERE NEW.name = people.name AND NEW.surname = people.surname);
    NEW.uname := NEW.name || NEW.surname || CASE u_num WHEN 0 THEN '' ELSE u_num::text END;
    RETURN NEW;
        END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER create_people BEFORE INSERT ON people
    FOR EACH ROW EXECUTE PROCEDURE create_people();

DISCLAIMER: As mentioned in the comments you should carefully plan on how to handle the possible deletes and updates on uname before using a solution based on this trigger on any production environment.


Why isn't this PosgreSQL LIKE query working?

The error message clearly describes the problem: "operator does not exist: inet ~~ unknown"

So you should use the native operators for inet type.

In your case it should be

... where ip_address << '192.168.43/24'::inet;

where the value after the slash specifies the number of significant bits in the value (24 bits = 3 bytes = x.y.z.any). For example 192.168.128/17 means any address from 192.168.128.0 to 192.168.255.255.

You can convert an inet value into varchar and deal with it like with any other varchar but it will break any benefits from the original type and disallow indexes if any. However, here is example:

... where ip_address::varchar like '192.168.43.%/32';

Additionally it may provide the wrong result if ip_address value is partially qualified in the way mentioned above (for example if ip_address = '192.168/16' it is not like '192.168.1.%' which is generally wrong because '192.168/16' value holds anything from '192.168.0.0' to '192.168.255.255')


Which is the most performant way to copy a number of SQL Server tables to a readonly SQLite database?

I have successfully used SQL Server Integration Services (SSIS) to setup a job to "archive" data to SQLite databases. A quick way to build a SSIS job, is to do a Task->Export Data task. At the very end you will see a checkbox on the top left asking you if you want to save this as a SSIS job.

Make sure that you have installed the SQLite provider from http://sqlite.phxsoftware.com/

You can schedule this SSIS package once it is saved.


Mysqltuner is a tool aimed at providing advice, not replacinf a DBA or editing the config file.

Question: Will MySQLTuner fix my slow MySQL server?
No. MySQLTuner is a read only script. It won't write to any configuration files, change the status of any daemons, or call your mother to wish her a happy birthday. It will give you an overview of your server's performance and make some basic recommendations about improvements that you can make after it completes. Make sure you read the warning above prior to following any recommendations.

You can find a default configuration file by installing the mysql-common package (on a different machine).
You can download the package with
apt-get download mysql-common

Then you will have the package in your current directory. For me, it is
mysql-common_5.5.46-0+deb8u1_all.deb

Check the contents with
dpkg --contents mysql-common_5.5.46-0+deb8u1_all.deb

and extract it to (let's say it exists) /tmp/mysql_wd/
dpkg --extract mysql-common_5.5.46-0+deb8u1_all.deb /tmp/mysql_wd

It will be in /tmp/mysql_wd/etc/mysql/my.cnf
Then, there are simple basic advices given by Peter from Percona, which can help you get started at https://www.percona.com/blog/2014/01/28/10-mysql-settings-to-tune-after-installation/
Sorry if I only provide a link here, this is not the matter of the topic (there is no specific question about that).
The subject is too vast to process here and you will have to take a good look at it yourself but I think I have answered your question.
Best regards!


complex mysql constraints over foreign keys

ALTER TABLE orders
  ADD FOREIGN KEY (user_id, address_id)
      REFERENCES address (user_id, id);

A single row then cannot have a user_id and address_id that do not appear on a row together in the address table.

Yes, I would say you want such a constraint.


Query Transactional Replication Article Filters

To research this, I configured transactional replication on my test server with 1 article having row filter. I did this for Adventureworks.Sales.Store table having [CustomerID] <10. Initially, I tried querying distribution..sysarticles to check the filter details. But, surprisingly it did not show any data in it.

Then I checked Adventureworks..sysarticles(i.e. in publication database) and that did the trick. See the below result:


How to grant execution rights on DBMS packages to a PDB user?

By default, noone is granted the EXECUTE privilege on DBMS_LOCK with grant option. Even the DBA role does not have the grant option. test1admin has access to DBMS_LOCK, but test1admin is not authorized to grant privileges on DBMS_LOCK to additional users. You need to connect as SYS to grant privileges on DBMS_LOCK to additional users.

Connect to the CDB root as SYS and switch to your pluggable database:

sqlplus / as sysdba
alter session set container = test1;

Or connect to the PDB directly:

sqlplus sys@test1 as sysdba

And issue the grant:

grant execute on dbms_lock to test1;

If you want test1admin to be able to grant privileges to DBMS_LOCK to others, then as SYS, issue the below:

grant execute on dbms_lock to test1admin with grant option;


How to replay xlogs from the beginning of time? (postgresql)

I was able to perform a complete recovery from T0 to T1 using these steps. We do it with a completely newly initialized database. We need the original database and all its xlogs. The original database is in $OLD_PGDATA and the new one is in $PGDATA. Make sure you get these straight before you proceed.

Steps

Set $PGDATA and create its directory and new database therein

[postgres]$ export PGDATA=/var/lib/pgsql.$$
[postgres]$ mkdir -p $PGDATA
[postgres]$ initdb

Modify the control file using a little trick I gleaned from stackexchange and other sites:

[postgres]$ cut -c 1-8 $OLD_PGDATA/global/pg_control | tr -d '\n' |
 dd of=$PGDATA/global/pg_control count=1 skip=0 conv=notrunc
[postgres]$ pg_resetxlog -f $PGDATA

You should see something like the following warning and output, which can be ignored:

     pg_resetxlog: pg_control exists but has invalid CRC; proceed with caution
     Transaction log reset

Create recovery.conf file and start the database.

 [postgres]$ cat >recovery.conf << END
 restore_command='cp "$OLD_PGDATA/pg_xlog/%f" "%p"'
 END
 [postgres]$ pg_ctl start

Note: the space between << and END is very important.

According to pg_resetxlog manual, afterwards, do a full dump, initdb, restore to be sure the DB is in a consistent state.

Note: if you are running the OLD_PGDATA instance on the same host, you'll just add -o "-p 5433" to use a port #5433 instead of the default port.

It took less than 15 minutes to recover 1700+ or 28 GB of xlog files.


Find records that do not have matching closing value

If the process with some PID was started, but was not finished, there is only one record for it.

SELECT pid
FROM table
GROUP BY pid
HAVING COUNT(pid) = 1

If process with the same PIDcan start/finish more than once, working process have odd records count.

HAVING 1 = (COUNT(pid) MOD 2)

Having PIDs list you can easily obtain the whole records.


Column definition for a SQL Server equivalent to Access BOOLEAN type

To address your concerns about BIT:

You can set your BIT column to NOT NULL.
You can use -1 when setting a BIT column to "true".
You can create a view that translates to -1, but +1 should be fine anyway unless your application explicitly checks for the numeric -1 (anything but zero should yield true in your client language).

CREATE TABLE dbo.foo(bar BIT NOT NULL, blat BIT NOT NULL);

INSERT dbo.foo SELECT -1, 0;

SELECT bar, blat, -CONVERT(SMALLINT, bar), -CONVERT(SMALLINT, blat) FROM dbo.foo;

Results:

bar   blat
----  ----  ----  ----
1     0     -1    0

The nice thing about BIT over TINYINT/SMALLINT is that if you have between 1-8 BIT columns, they can fit into a single byte.

In all of these cases, you still aren't going to be able to say

WHERE NOT BooleanColumn
-- or
WHERE !BooleanColumn

You will still have to say

WHERE BooleanColumn = 0


Oracle SQL Developer: Copy paste tables, with 2 different instances, with different table structure

"However, the tables in the DEV keeps adding/changing, which is why when I run the query, it hit the error saying the table/view does not exist."

Short of putting a lock on every object in the remote database during your copy, I'm not sure this is avoidable. In truth your approach this is unneccessarily complicated. If you want to clone your DB, use the existing cloning tools. There's no need to reinvent the wheel for this application. I'd suggest using datapump to export/import your schema if you want a consistent snapshot.


MySQL slave replicates changes that are in neither binlog_do_db nor replicate_do_db

Replication filtering isn't bulletproof. Due to how the filtering is implemented the events responsible for your errors are being generated because the default database at query runtime is the my-database schema as expected and the query being executed is fully qualified INSERT INTO phpmyadmin.pma_column_info...

Peter Zaitsev explains the scenario well in this post:

Filtered MySQL Replication


There are a couple of ways you could do this. You could use a FULL OUTER JOIN which will pull data:

SELECT ISNULL(#Test1.empName, #Test2.empName)
     , TotalSW  = COUNT(#Test1.empName)
     , TotalSNT = COUNT(#Test2.empName)
FROM #Test1
FULL OUTER JOIN #Test2 ON #Test2.empName = #Test1.EmpName
GROUP BY ISNULL(#Test1.empName, #Test2.empName)

(NOTE: the previous example that used SUM over a CASE statement isn't needed as COUNT does not count NULL values which is what the extra jiggery-pokery was trying to do)

or to keep the UNION method (which may be more or less efficient in more complex examples depending on the rest of the query):

SELECT empName, TotalSW = SUM(TotalSW), SUM(TotalSNT)
FROM   (
        SELECT empName, TotalSW = 1, TotalSNT = 0
        FROM #Test1
        UNION ALL
        SELECT empName, TotalSW = 0, TotalSNT = 1
        FROM #Test2
        ) AS sq
GROUP BY empName

Note the use of UNION ALL not just UNION - otherwise it will filter out duplicates if an employee exists more than once in one table. UNION ALL is also often more efficient because it possibly avoids extra sort operations so unless you need duplicate removal you should use it instead of plain UNION.

Or if you find the CTE syntax more readable than sub-queries (which it often is in more complex examples, though it makes no difference here IMO):

WITH Emps AS (
        SELECT empName, TotalSW = 1, TotalSNT = 0
        FROM #Test1
        UNION ALL
        SELECT empName, TotalSW = 0, TotalSNT = 1
        FROM #Test2
        )
SELECT empName, TotalSW = SUM(TotalSW), SUM(TotalSNT)
FROM   Emps
GROUP BY empName


How to link customers and suppliers in a database

In the customers table I think you can add an extra column which would flag whether that customer is also a supplier.  So you could have a generic customer table (or you can name it contacts table), with a column used as a supplier flag/indicator (could be numeric: 1 or 0 or char: y or n or even boolean: true or false)


default permissible datatype conversion matrix

You can get a list using the psql client with

\dCS

This will show all casts defined between system data types.

In addition, all types can be cast to and from text using the type input and output functions.


Does MySQL Enterprise Backup work across Operating Systems?

MySQL Enterprise Backup (MEB) is designed to create a point-in-time snapshot of data but only backs up data locally. If the dataset is relatively small, try setting up an NFS share and run MySQL Enterprise Backup with the NFS share as the Target. Of course, there may be some risk involved.

Please check out these links on this subject for further research on MEB

http://dev.mysql.com/doc/mysql-enterprise-backup/3.6/en/meb-backup-streaming.html
http://dev.mysql.com/doc/mysql-enterprise-backup/3.6/en/meb-backup-single-file.html
http://forums.mysql.com/read.php?28,409106,410496#msg-410496

Using MEB with data on server with MacOS and Linux OS on the Target Server? One can only guess. Personally, if I had to port mysql data from one OS to another I would recommend this:

Run mysqldump from MacOS to sqldump.sql
Install mysql on Linux
Load sqldump.sql into Linux
Setup replication between servers

MacOS running mysql as Master
Linux running mysql as Slave

From here, just install MEB on the Linux Box (For free version, use XtraBackup) and run your MEB backups locally on the Linux.


utl_raw.bit_and attaches extra 0 after and operation in oracle sql

That is not handled as binary, but hexadecimal.

select
          length( '0100000000000000000000000000000') as l_a_in_chars,
  utl_raw.length( '0100000000000000000000000000000') as l_a_in_bytes,
          length( '1111111111111100000000000000011') as l_b_in_chars,
  utl_raw.length( '1111111111111100000000000000011') as l_b_n_bytes,
          length('00100000000000000000000000000000') as l_ab_in_chars,
  utl_raw.length('00100000000000000000000000000000') as l_ab_in_bytes
from dual;

L_A_IN_CHARS L_A_IN_BYTES L_B_IN_CHARS L_B_N_BYTES L_AB_IN_CHARS L_AB_IN_BYTES
------------ ------------ ------------ ----------- ------------- -------------
          31           16           31          16            32            16

It is not the length in characters that matters, but the length in bytes, and that was the same the whole time. You can not have hexadecimal with odd number of characters. It is not the output that has an extra 0 attached, but your input, on the automatic conversion from hex to raw:

select hextoraw('0100000000000000000000000000000') from dual;

HEXTORAW('0100000000000000000000
--------------------------------
00100000000000000000000000000000

Your input does not fit into RAW(15), requires RAW(16) where the required 0-s are added as required, and there is no such thing as RAW(15.5):

SQL> select cast ('0100000000000000000000000000000' as raw(16)) from dual;

CAST('01000000000000000000000000
--------------------------------
00100000000000000000000000000000

SQL> select cast ('0100000000000000000000000000000' as raw(15)) from dual;
select cast ('0100000000000000000000000000000' as raw(15)) from dual
             *
ERROR at line 1:
ORA-25137: Data value out of range

Since you seem to store your numbers as strings, you can quite easily check the position of 1 "bit":

SQL> select substr('1111111111111100000000000000011', -30, 1) from dual;

S
-
1


The problem I see is the error message that says

  Table 'mysql.plugin' doesn't exist
  **120105  6:02:49 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it

MySQL 5.1 introduced plugins. MySQL 5.0 and prior do not have plugins. My guess is that you had MySQL 5.0 running, you uninstalled MySQL 5.0, and tried to install either MySQL 5.1 or 5.5 and it complained about mysql.plugin being missing.

Try cleaning up the 5.0 install as follows

uninstall mysql 5.0
Move /usr/local/var/mysql to /usr/local/var/mysql_old
Install mysql 5.5

Give it a Try !!!


Cardinality , Parallel Hint alternative for MS-SQL

Just remove the hint for SQL Server.
The cardinality and parallel hints are designed to help control performance when you "know better" than the DBMS--they don't affect the results in any way. You can't know if you'll get acceptable performance in SQL Server until you run the query.
The different platforms will almost certainly need tuning in different places, using different tools. For example, parallelism works differently in SQL Server, so the related query tuning is approached differently. If you DO need to control parallelism, using a hint has different semantics and happens in different scenarios.
So just remove those hints (or leave them. SQL Server will just interpret them as comments), and watch performance.
Tune the queries that need it: First by making sure there are indexes and statistics so that the optimizer has the data it needs to get a good plan, then by exploring alternate ways to write your query, and only last by exploring hints.


I have a table with a column named price typed as varchar.

And there is the problem. You have a column that obviously has numeric values (assuming the name price is relevant) and you have chosen a char type.

  I want the maximum value of this column. I am using this query:

SELECT MAX(price) FROM product_management.`item_list` ;

  but the result is wrong.

The result is 100% correct but not what you expected. The database did exactly what you asked it to - but not what you though you were asking.

You asked for the MAX() of a varchar column and that's what you got. Problem is that for char columns, the ordering is done lexicographically - as in dictionaries - and not with numeric order. Because char type columns have char values (strings) and not numbers.

So, for the database, they are just strings of characters. You could very have a value of peters-sellers in there. The ordering of values of a char column would be - even if all the values are visually "numbers".

1
11
165
17
2
20
200
21
21789
3
35
350
9
99
997

And of course your query would return the lexicographic maximum (997) and not the numeric maximum (21789).

The best solution would be to convert the column to a numeric type - integer, decimal, depending on the values you expect to have - and then MAX() and MIN() and ORDER BY would work as you expect.

There are workarounds, if you can't change the type (by casting the value to a numeric type before applying MAX/MIN functions) but I don't recommend it. This would still allow to store nonsense data in the column and possibly get conversion errors (which number should 'peter-sellers' be converted to?)


Is it possible for unique key to break master-master replication in mysql?

Though I've not tried this myself, I've done a quick documentation hunt and found that this part of the MySQL documentation is helpful.

The relevant part is:

  If a statement produces different errors on the master and the slave, the slave SQL thread terminates, and the slave writes a message to its error log and waits for the database administrator to decide what to do about the error. This includes the case that a statement produces an error on the master or the slave, but not both.

The next question would then be: "How do I know there has been an error?", to which I've found a useful script here, which could be run frequently as a cron task.


MySQL - How can I improve this query? EXPLAIN attached

First, please remove the parentheses from DISTINCT(reservations.id).
DISTINCT is not a function.
About performance, why do you join reservation_nights? You only seem to use that table in the ORDER BY. Assuming that a reservation can span several nights, you probably mean to use the last date (from the many that a reservation has) for the ordering and nothing else. This however, requires a join on reservation_nights and thus many rows for each reservation are created in the result set. So, you had to use DISTINCT to get rid of the duplicate rows. DISTINCT is often inefficient, especially when performed for big results sets and not in small/indexed tables.

Try this rewriting, that gets rid of the DISTINCT and pushes the ORDER BY / LIMIT 100 in a derived table where only base table is used. This technique which I call "LIMIT first, JOIN later" can be more efficient as the order by is done on less data (single table vs a large join) and then the joins are done on less data as well (max 100 rows from each of the joined tables):

SELECT
    r.id,
    r.referenceId, r.referenceStatus, r.status,
    r.firstname, r.lastname,
    r.dateArrival, r.daysToCheckIn,
    r.rateCode, r.roomCode,
    r.totalNights, r.rateRevenue,
    r.dailyRate, r.totalRevenue,
    r.foodBeverageRevenue, r.additionalRevenue,
    c.name AS channelName,
    cg.name AS channelGroupName,
    m.name AS marketName,
    p.totalReservations
FROM
    ( SELECT reservationId, MAX(dateStayed) AS lastDateStayed
      FROM reservation_nights
      GROUP BY reservationId
      ORDER BY MAX(dateStayed) DESC
      LIMIT 100
    ) AS rn
  INNER JOIN reservations AS r ON r.id = rn.reservationId
  INNER JOIN channels AS c ON c.id = r.channelId
  INNER JOIN channelgroups AS cg ON cg.id = c.channelgroupId
  INNER JOIN markets AS m ON m.id = r.marketId
  LEFT JOIN profiles AS p ON p.id = r.profileId
ORDER BY
    rn.lastDateStayed DESC ;

If you don't already have one, add an index on reservation_nights (reservationId, dateStayed) to improve the efficiency of the subquery.


Entity whose possible types differ in the FK

EventGroup would be one table, InstitutionGroup would be another, each with their own primary key and correlating fields. The foreign key in Group on eventID would reference the primary key in EventGroup, and the foreign key institutionID would reference the primary key in InstitutionGroup.


Postgres: Unsafe use of new value of enum type

You have forgotten to show us the hint you got:
ERROR:  unsafe use of new value "DONE_BY_PASSED" of enum type activity_state
HINT:  New enum values must be committed before they can be used.

You also forgot to mention that you ran the ALTER TYPE and the ALTER TABLE statements in the same transaction.
Like the hint says, you have to commit the ALTER TYPE before you can use the new enum value.


Breaking out of a recursive query in Postgres 11

WITH RECURSIVE cte AS (
  SELECT id, domain_name, valid
  FROM domains
  WHERE parent_id IS NULL
UNION ALL
  SELECT domains.id, domains.domain_name, domains.valid
  FROM domains
  JOIN cte ON domains.parent_id = cte.id
  WHERE NOT cte.valid -- stop recursion when valid node reached
)

SELECT id, domain_name
FROM cte
WHERE valid

fiddle


Complex dto based result sets with EF and MS SQL server

Using a view to produce the complex result set seems to be the best option in my case of the options presented in the question however ...

I found that with a slight architectural change in the business process I wouldn't end up in this situation at all, resulting 2 or 3 simpler requests rather than 1 complex one requiring a "special case" like this to be handled in the call handling logic.


know that I should migrate my database to utf8 to solve this problem, but for some reasons, I can not do that for the moment.

  In my case, I'd rather PostgreSQL saves my string removing characters it can not convert or for example replacing them with some symbol like "?" rather than throwing an error...

PostgreSQL does not support this. It's requested periodically, but nobody who requests it does the work to actually implement it in the system and convince the dev team it's an appropriate option to offer.

You will need to do your text-mangling client-side. In PHP, before you send the text to PostgreSQL, you will need to filter out characters that doesn't match the database encoding. How to do that is entirely PHP-specific (start with iconv support, probably). You have described one way to do this, using utf8_decode, already.

Using utf8_decode is actually incorrect, because the function (per the docs) actually assumes the input is ISO-8859-1, i.e. Latin-1. You're using latin-9, i.e. ISO-8859-15. So it'll mangle some of your input characters, in particular the Euro sign. See changes from ISO-8859-1. Instead, use the iconv function. See the surprisingly useful comments on the utf8_decode function documentation.

If in the process of filtering the text you convert it to LATIN9 inside PHP, remember that you must set your client_encoding to latin9, since that's the encoding of the text you'll be sending to PostgreSQL. That means the results will be in latin-9 too, so you must convert all results from PostgreSQL from latin-9 back to PHP's native utf-8.

If you use utf8_encode to convert your latin-9 output from PostgreSQL for consumption in PHP, you'll have the same problem with latin-1 vs latin-9 as you do on utf8_decode.

For that reason, if possible, try to use a filter that replaces characters not supported in latin-9 without actually converting the string to latin-9. It'll save you a bunch of hassle if you can keep client_encoding set to utf-8 and just mangle your strings instead of converting them.

All this said, I strongly recommend upgrading the database to utf-8 instead. The only reason to keep it in latin-9 would be if you have other client applications that can't cope with chars outside the latin-9 range (i.e. they rely on a latin-9 client_encoding)


Query the last records by date, where condition is true

There are various ways to do this kind of queries (greatest-n-per-group) which also differ per DBMS (in syntax and performance) but the standard SQL you have will work in almost all products.

Without changing the structure of your query, you just need to add the condition in both the external and the internal subquery:

SELECT t.DocID, t.RevID, t.EffDate
FROM tblRev AS t
INNER JOIN (
    SELECT DocID, max(EffDate) AS MAXEffDate
    FROM tblRev
    WHERE OK = TRUE
    GROUP BY DocID
) AS tm ON (t.EffDate = tm.MAxEffDate) AND (t.DocID = tm.DocID)
WHERE t.OK = TRUE ;

If there is a UNIQUE constraint on (DocID, RevDate), the external check (WHERE t.OK = TRUE) is not needed.

Minor (improvement or obfuscation?): the WHERE OK = TRUE can be shortened to WHERE = OK, since OK holds boolean values.


Queries for greatest-n-per-group can be solved using window functions:

select note_id, size, id
from (
   select note_id, size, id,
          row_number() over (partition by note_id order by size desc) as rn
   from the_table
) t
where rn = 1;

The above is standard SQL, but Postgres as another (proprietary) feature that can be used for this:

select distinct on (note_id) note_id, size, id
from the_table
order by node_id, size desc;

The distinct on () solution is usually faster then the solution using window functions.


Entity Relationship Model - double one to many

Your LEAD table is an intersection between MANUFACTURER and CUSTOMER.  That is a perfectly legitimate design.  Intersection tables don't have to be pure intersections.  They can have other attributes in their own right (such as your date or type).


How to make stored procedure with outputs OUT sys_refcursor, OUT varchar2, and OUT number, then use in c#.net app

Your procedure is missing two OUT parameters:

PROCEDURE myprocedure (myinput      IN Varchar2,
                       myretcursor  OUT sys_refcursor,
                       p_count      OUT NUMBER,
                       p_message    OUT VARCHAR2) IS
BEGIN
   OPEN myretcursor FOR
      SELECT * FROM MYTABLE;
   p_count := 123;
   p_message := 'message';
END myprocedure

You would also need to modify your C# code to register the new OUT parameters accordingly.


Code creating clustered columnstore index while maintaining row order

A clustered columnstore index is fundamentally different from a clustered rowstore index. You may have noticed there is no key column specification for a clustered columnstore index. That's right: a clustered columnstore index is an index with no keys - all columns are 'included'.

The most intuitive description I have heard for a clustered columnstore index is to think of it as a column-oriented heap table (where the 'RID' is rowgroup_id, row_number).

If you need indexes to support direct ordering and/or point/small range selections, you can create updateable rowstore b-tree indexes on top of clustered columnstore in SQL Server 2016.

In many cases this is simply not necessary, since columnstore access and batch mode sorting is so fast. Many of the things people 'know' about rowstore performance need to be relearned for columnstore. Scans and hashes are good :)

That said, of course columnstore has a structure to its row groups (and metadata about min/max values in each segment), which can be useful in queries that can benefit from row group/segment elimination.

One important technique in this area is to first create a clustered rowstore index with the desired ordering, then create the clustered columnstore index using the WITH (DROP_EXISTING = ON, MAXDOP = 1) option. In your example:

CREATE [UNIQUE] CLUSTERED INDEX idx
ON dbo.tab1_cstore (id, time)
WITH (MAXDOP = 1);

CREATE CLUSTERED COLUMNSTORE INDEX idx
ON dbo.tab1_cstore
WITH (DROP_EXISTING = ON, MAXDOP = 1);

Care is needed to maintain the benefits of row group/segment elimination over time. Also, while columnstore is already implicitly partitioned by row group, but you can explicitly partition it as well.

I'm not 100% sure what you're looking to test, but it is true that the 'order' of values within a segment is determined by the compression algorithm. My point about creating the columnstore index with DROP_EXISTING is about the ordering of data flowing into the segment creation process, so that segments overall will be ordered in a particular way. Within the segment, all bets are off.


What is the best practice for selecting from two tables with multiple relations?

Create a covering index on Id column and Include Name on the table Management_EnumerableItem.

CREATE INDEX IDX_Name on Management_EnumerableItem (Id) Include (Name) with (fillfactor=90);

and write it like this.

SELECT A.NAME,
B.NAME,
C.NAME,
D.NAME,
E.NAME,
F.NAME,
G.NAME,

FROM Product_Coupling_Serial P
LEFT OUTER JOIN Management_EnumerableItem A
ON A.ID = P.DN
LEFT OUTER JOIN Management_EnumerableItem B
ON B.ID = P.PN
LEFT OUTER JOIN Management_EnumerableItem C
ON C.ID = P.Winder
LEFT OUTER JOIN Management_EnumerableItem D
ON D.ID = P.CouplingType
LEFT OUTER JOIN Management_EnumerableItem E
ON E.ID = P.[Type]
LEFT OUTER JOIN Management_EnumerableItem F
ON F.ID = P.[ILayer]
LEFT OUTER JOIN Management_EnumerableItem G
ON G.ID = P.[OLayer]

Thanks


PostgreSQL on Ubuntu - Using conf files for recovery

The recovery.conf file needs to go into the data directory (/var/lib/postgresql/...).  The other configuration files can stay where they are (/etc/postgresql/...).  Then a normal restart should do what you want.


You wrote that "I have given NT SERVICE\MSSQLSERVER full control", so it looks like your SQL Server is running under "local system accounts".
If you granted full control to NT SERVICE\MSSQLSERVER on a second server, it will not work, because NT SERVICE\MSSQLSERVER is a different account on each machine.
You need to grant all network privileges to account [DomainName]\[ServerName]$ for a local service accounts to have access to network resources.
In your case it will be [DomainName]\ServerA$ account and this account will need to have access to the network share.


Windows could not start the SQL Server (mssqlserver) service on local computer error 17058

CHeck for another error in the event log that is time-correlated to this one. Basically - this one is from the service manager. The SQL Server service should have written another error (either into th event log or the errorlog) that contains the internal error that happened. THis error is generic from the serivce manager and as such totally irrelevant to debugging. It also is well known - google for "Sql server 17058" finds tons of references.

It seems to be a permission error:

http://www.it-sideways.com/2012/11/mssql-startup-error-code-17058.html

points to

"Could not open error log file ''. Operating system error = 3 (The system cannot find the path specified.)."

which would be an operating system level configuration or insallation issue.


Extended Events Collation resolution error

This appears to be a conflict between the instance (i.e. server) -level collation and the collation being used in the [master] database. Both sys.server_event_sessions and sys.dm_xe_sessions are system catalog views, and the collation of their [name] fields should be the same.

sepupic had the O.P. execute the following:

select top 1 sql_variant_property(name, 'collation') from sys.server_event_sessions;
select top 1 sql_variant_property(name, 'collation') from sys.dm_xe_sessions;

The results were:

sys.server_event_sessions = Latin1_General_CI_AS
sys.dm_xe_sessions        = SQL_Latin1_General_CP1_CI_AS

That difference should not be possible, but at least it explains the error message.

I requested that the O.P. execute the following to determine the instance-level collation:

SELECT SERVERPROPERTY('collation');

O.P. replied with:

  ... the server collation is "SQL_Latin1_General_CP1_CI_AS". I was under the impression that the Server collation was whatever the master DB collation was. This server was installed from a Microsoft Azure image so the collation was changed from SQL_Latin1_General_CP1_CI_AS to Latin1_General_CI_AS.

Clearly something went wrong with the collation change process. I was able to reproduce the error by creating a database containing one table with one string column. I used a case-sensitive collation and that one column was also the Primary Key (meaning: a unique index). I added two rows: N'A' and N'a'. I then used the sqlservr.exe -q method (details linked below) of globally updating the collations to change the instance's collation to a different one, one that was case-insensitive. When the process tried to rebuild the PK, it failed as the case-insensitive collation rendered the second row not-unique from the first. But, the system DBs has already been updated. However, the instance level collation was not changed to the new collation as the process never completed.

To fix this, I believe the following would work:

Detach all User DBs (to minimize impact of the operation: the user DB's are not a problem, and some columns might have different collations set that shouldn't be changed)
Update the instance's collation using the sqlservr.exe -q method
Re-attach the User DB's


Can SQL Server use multiple indexes for the same query?

Yes, but not with the indexes you've chosen.

If I create these indexes:

CREATE INDEX ix_top ON #ab (col1a) INCLUDE (colb);

CREATE INDEX ix_bottom ON #ab (colb, col1a);

I get back this plan:

And that's without having to use multiple index hints.

With a slightly different index:

CREATE INDEX ix_mindyourbusinessypercube ON #ab (col1a, colb);

The plan changes to use just that one index, though performance ramifications are difficult to surmise given the limited test data. I leave that as an exercise to ypercube the reader.


Combined column data display only unique value

There is no easy and supported built-in method for string aggregation in 10g. (Do not use WM_CONCAT, as it is not supported, and it is not available anymore in 12c.)

You need to write your own string aggregation function, for example:

CREATE OR REPLACE TYPE varchar2_ntt AS TABLE OF VARCHAR2(4000);
/

CREATE FUNCTION to_string (
                nt_in        IN varchar2_ntt,
                delimiter_in IN VARCHAR2 DEFAULT ','
                ) RETURN VARCHAR2 IS

   v_idx PLS_INTEGER;
   v_str VARCHAR2(32767);
   v_dlm VARCHAR2(10);

BEGIN

   v_idx := nt_in.FIRST;
   WHILE v_idx IS NOT NULL LOOP
      v_str := v_str || v_dlm || nt_in(v_idx);
      v_dlm := delimiter_in;
      v_idx := nt_in.NEXT(v_idx);
   END LOOP;

   RETURN v_str;

END to_string;
/

Source is: http://www.oracle-developer.net/display.php?id=306

Your sample data:

create table dummy
(
  plan_cd varchar2(6 char),
  plan_nm varchar2(6 char),
  class varchar2(2 char)
);

insert into dummy values ('D01501', 'DENTAL', 'AA');
insert into dummy values ('D01501', 'DENTAL', 'AB');
insert into dummy values ('D01501', 'DENTAL', 'AC');
insert into dummy values ('V01501', 'VISION', 'AA');
insert into dummy values ('V01501', 'VISION', 'AB');
commit;

And finally the query:

select
  plan_cd,
  plan_nm || ','
  || to_string(cast(collect(class order by class) as varchar2_ntt)) as plan_nm
from
  dummy
group by
  plan_cd, plan_nm
;

PLAN_CD PLAN_NM
------- ----------------------------------------
D01501  DENTAL,AA,AB,AC
V01501  VISION,AA,AB


Passing current server name from SQL Server Agent job to PowerShell

Replace A-SVR with SRVR.

A-SVR is for alerts.

SRVR will contain server name and instance name.

Refer to document on MSDN.


foreign key would have a different name from the primary key it is referring to, but necessarily , their data type and other attributes should be the same.
for example , if one is INT(10) unsigned the other should be defined the same.

but sometimes it is a good practice to name them similarly in complex queries. for example when you want to JOIN the relating tables, you can use the syntax :

userGroupType INNER JOIN userGroup USING (GroupType_id)

instead of :

userGroupType INNER JOIN userGroup ON userGroupType.GroupType_id = userGroup.GroupType_id


Trigger Performance : Is Using Trigger is a good way?

Personally, I would use triggers for auditing.

If they fail, then the main DML gets rolled back
(same) The main update requires the DML to succeed
A table will typically have several DML paths
You may not be able to use code
You will have direct table update that bypass code at some point

Ok, so we'll use triggers. Some notes

Ensure they work for multiple rows: no CURSORs or loops
Use them only for auditing: nothing else (no, say, sending emails)
Ensure you use TRY/CATCH in the triggers: this massively improves the behaviour on rollbacks in triggers
CASCADE foreign keys will cause child table triggers to fire: Beware nesting and recursion and don't use CASCADEs if possible

This last list also addresses sonme points in @NevilleK's answer. Triggers are one tool to use: use wisely and when appropriate and correctly.

600 updates per hour is trivial, not a problem at all for triggers (subject to cursors, loops, sending email and other stupid stuff)


help in correcting the syntax error of the below create table with as with multiple select

You can get the result by using UNION ALL .
Take help of following example for understanding:

create table empnew as select * from employees union all select * from employees;

As @a_horse_with_no_name perfectly mentioned !


Will transaction log grow event trigger time out event?

Growing the logfile (as well as database files) freezes the database operation for a short time. It is especially bad if it is needed repeatingly.

A good initial size and a big grow factor can reduce the number of resizes. What you also should avoid is shrinking the log because it will just grow again. The initial size of the log should cover all changes likely to happen within the backup period.

Timeout per se is not expected to happen by this, but of course if an application sets very low timeouts it will experience them. However if you work with small timeouts you should also implement proper retries since you are dealing with a enterprise software stack which has certainly no RT guarantees (and therefore timeouts will happen even without a log resize)

For database files (but not for logfiles) IFI (instant data file initialisation) can reduce the time to extend a data file. It requires SQL Server to be started with special volume management privileges.


Calculate Count Distinct without grouped columns in Oracle

You use COUNT(DISTINCT ..) expression without GROUP BY clause. If you need this value for each group (if there are duplicates in your output while removing COUNT() expression) you must add GROUP BY for all other fields in output list. Or you can use the window variant of COUNT() function which is scalar. - Akina


In postgres is there any real drawback on using a json field over multiple tables and one to many relationships?

The first normal form requires columns to be single, atomic values. I'd argue that if you really treat these JSON objects as something to store and retrieve, they are atomic from a database point of view, and what you plan to do is fine.
If you want to use parts of the JSON in a WHERE condition, that could still be alright. If you want database constraints based on parts of the JSON, or you want to join based on JSON attribute, I'd advise against using such a data model.


Shrinking the database partition if autogrow is turned off?

You can shrink a database regardless of autogrow setting. These are unrelated except for the fact that if autogrow is off, and you shrink the database, you're likely headed for a failure as soon as you start adding more data. This obsession with shrinking files is unhealthy - why are you going to shrink the file? What are you going to do with the space you freed up, but only until the file has to grow again? If you are never going to update the data that's there or add more data, maybe this strategy makes sense, but for an active database, it just doesn't accomplish anything except cause you more pain (maybe not today, but it will catch up to you).

No, seek times are not increased because a file is large. The file is just a container.


Select one column with same value and check another column's value

I only use T-SQL, but I don't think anything used below is specific to SSMS:

SELECT DISTINCT Cust_ID
FROM Tbl t
WHERE
    Status = 'D'
AND NOT EXISTS (SELECT * FROM Tbl WHERE Status <> 'D' AND Cust_ID = t.Cust_ID)


When Azure recommends an index (part of natural key) with all other columns included, should I switch to a clustered index for this natural key?

Unfortunately selecting a clustered index isn't just a matter of ever increasing. And insert performance and fragmentation aren't your only concerns.  Here are a few things to think about:

Your identity column is ever increasing, your natural key isn't. This will increase your insert time, but inserts are generally pretty quick and in a OLTP system they aren't so much less frequent as less visible.  For example doubling the time of a 4ms insert to 8ms and no one will notice.  Increase the time of a select statement from 30 seconds to a minute and people will see it.

It's a similar problem with fragmentation. It exists, it can cause problems, but it's a manageable problem at worst.

That said, are these your only indexes?  Because the clustered index keys are included in any non-clustered index.  So a wider set of keys will actually increase the size of your non-clustered indexes and slow them down. And your identity column is only 4 bytes wide.  Your natural key is 58 (assuming it's a unique key otherwise add 4 bytes for the uniquifier).

Ordered range scans can be very helpful, and are one of the big bonuses for the right clustered index.  That said, I don't see either of the indexes you mention above as candidates for range scans.  Those would be more for

SELECT * FROM table1 WHERE name BETWEEN 'Bob' and 'Mary'

You see them most with date data types, although queries similar to the above example happen too.

Also if you end up using your natural key as an NCI (non clustered index) then you might make sure it's a covering index for your main query(s).  In other words it contains (generally by using the INCLUDE statement) any extra columns needed by the query.  This means that SQL doesn't have to go back and check the CI (clustered index) and avoids an expensive lookup.

Last but certainly not least, do you have to worry about GDPR?  Do you have any data for people who are in the European Union?  If you do then you need to worry about natural keys in your scripts.  If someone's information ends up in a script then you will need to include that in your plans when you have to remove someone's data.

Here is a summary I wrote a while back.  It's more generic but might be helpful to look at:

https://sqlstudies.com/2016/08/29/natural-vs-artificial-primary-keys/

And a few other related posts:

https://sqlstudies.com/2014/12/01/using-a-date-or-int-column-as-the-clustered-index/
https://sqlstudies.com/2014/10/20/update-to-the-clustered-index-columns-are-in-all-of-the-non-clustered-indexes/

In the end though, you are right. You should test it out. That said, I would personally guess that keeping the identity as your CI is going to be your best bet with the following 2 non clustered indexes (based on the queries you mentioned above):

UserId, Key1, Key2
UserId, Key2

I didn't include Key3 because you didn't mention it and it adds unnecessary width to your index.  Beyond that, given the size of the table, you can include all of the non-key columns. Assuming that you have sufficient space for the indexes (which will be as big as the table is currently) it might improve your queries pretty significantly.  Of course any data you don't have to send to your application shouldn't be sent and will improve your performance (and reduce the size of your index if you don't have to add it to the include).


What is your workflow for planning a data migration?

Every time I've done it, we've gone for two passes ...

take a snapshot, and working on a different server, use that to determine what has to be done for the migration, and script it.
once they have the script in hand, the snapshop is restored on the test system, and it's timed to see if it'll run within the required time, or it's tuned and modified until it can.
have the stakeholders sign off that nothing looks wrong with the data on the test system.

Then, over a weekend, you have a scheduled outage :

Friday night, the systems that use the database are brought down, a full cold backup is made, and the scripts are run to migrate/modify/whatever to the data
Systems are brought back up under some private address or somehow set up so it's not open to anyone but the stakeholders for acceptance testing
If the stakeholders approve, the system's put online and made public; if not, the database is restored from the backup made on Friday night, and you start the process over again.

With our schedule, the database folks generally had from 6pm on Friday to 10am on Saturday to run the backup & migration scripts, so our goal was that they'd run in under 8 hrs (~6 of that was backups), so we'd have some time for our testing and corrections before it got released to the stakeholders.

Stakeholders were given their time windows in advance, so they knew to leave their weekend open for testing at the beginning of the window.  They'd also be told the end of their window, typically Sunday afternoon, where if everyone hadn't signed off, we'd have to start rolling back.

Oh, and of course ... if someone had a change during either of the acceptance tests, and we made a change, it meant that all of the stakeholder's sign-offs were voided, and they had to re-test ... so we'd try to give them all a while to look for issues and run any corrections as a batch, rather than apply them one at a time.

Luckily, the only times I've had one of those situations where we couldn't have significant downtime, the sytems I was migrating were fed from scripts, not user input, so I could just have two parallel systems going, and swap them out when things got signed off.  (only once was there a problem, when my boss insisted that we take a full backup, not understanding that the whole thing was going to still be online at a different IP ... so what should've been a 5 min outage on a bad day became a 5 hr outage.)


As of 5.5.3, FLUSH LOGS will close and reopen the slowlog.  (In old versions, FLUSH had no effect on the slowlog.)

So, on *nix OS, this should work without restarting the server:

rm (to delete) or mv the slowlog to another name.  (Note: mysqld will continue to write to the file, even though you changed the name.)
FLUSH LOGS;.  As of 5.5.3, you can limit the effect via FLUSH SLOW LOGS;

See http://bugs.mysql.com/bug.php?id=14104 and http://bugs.mysql.com/bug.php?id=60878

I would not turn off the slowlog -- next month someone will add a naughty query and you will want to know about it.


Having a group that is not a group may lead to reporting issues down the road.  For instance, if you want to know the average number of Materials per Group, the results could easily be skewed by the overly large number of unassigned group members.

I'd lean towards having a cross reference table that links Groups to Materials.  So a table named MaterialsGroupsXRef that contains GroupID and MaterialID; any Material that is a member of a Group has a row in that table.

Any Materials that are not a member of a Group would not show up in the MaterialsGroupsXRef table.  This allows you to easily identify Materials that do not have a group.  Likewise, you could easily identify Groups with no Materials.


changes on multimaster replication and activate GTID

OK, for anybody reading this in future.

I did the first step changing all server to use GTID based replication and it worked perfect.

I did it the way as I wrote in the original post.

Right now I have same landscape as before, just using GTID.

This night I'll go to activate circular replication between S4 and S5
and if all goes well shutdown S2

I keep posting for the world after.


Usefulness of Foreign Key WITHOUT reference option

Having built databases since before there were efficient database engines, I would recommend you create FOREIGN KEY constraints where required.

Presence or absence of ON DELETE and ON UPDATE does not avoid the INSERT requirement on the parent table.  They will influence what happens when the parent key is changed or removed.  The parent record will be required if the child table's key is not nullable.
The foreign key will enforce referential integrity and this will ensure that joins will work.  Without the foreign key it will be possible to enter values in the child table which do not exist in the parent table.  Outer joins to the parent table will fail for those records.
Query speed will not slow down due to the FOREIGN KEY.  There will be a minor impact on INSERT as the parent table's index will need to be check for presence of the parent record.  This is not a reason to sacrifice referential integrity.  (The cost to continually verify it by other means will usually outweigh the costs of the constraint.)  The cost of cleaning up the referential integrity after the fact will greatly exceed the savings.

I avoid the ON DELETE and ON UPDATE actions in FOREIGN KEY definitions.  I haven't yet designed a database where they are necessary.  These two actions control what happens to the child records when the parent changes.  ON DELETE can be extremely destructive if a frequently referenced parent is unintentionally deleted.  ON UPDATE is only useful where the parent key can change.  Use of surrogate keys eliminates the need for ON UPDATE actions.

If the reverence to the parent table is always from the child it might be appropriate not to index the foreign key column in the child table.


Why do row-versioning isolations use a linked list of versions of record?

Why is there a need in a linked list, i.e. to link the changes of the same record?

The "current row" in the index or data page provides a pointer to only the most recent row version in the version store. A transaction that needs older versions needs a way to navigate to the exact version it should use. The linked list provides this mechanism.


MySQL Help with Desktop Based Administration

If you have a very small database, I recommend using XEROUND free database service.

REASON #1 : Database is stored in the Cloud for Free

REASON #2 : Automatic Backups of the Database

REASON #3 : You can load data over the Internet and retrieve it via mysqldump

REASON #4 : It comes with phpmyadmin (no installation required) to administer and query the database

REASON #5 : You can set up your own access to the data via external SQL tools

MySQL Workbench
HeidiSQL
MS Access
etc.

Restrictions

There are only three storage engines

MyISAM
MEMORY
XEROUND (Proprietary Transactional Cloud Storage Engine)
Sorry, no InnoDB

Maximum Database Size for Free Service is 10MB


I don't have your data source, so I created a cte with some records in it

;with cte
AS
(
       Select 'CALYPSO' AS 'system', 'Mon' as cptname
       union all Select 'CMS-GDP' , 'Mon'
       union all Select 'MANUAL' , 'Mon'
)
SELECT system, cptname as extCode
from
   cte
FOR XML PATH('externalCode'), TYPE, ELEMENTS ,ROOT('externalCodes')

output for it:

<externalCodes>
   <externalCode>
      <system>CALYPSO</system>
      <extCode>Mon</extCode>
   </externalCode>
   <externalCode>
      <system>CMS-GDP</system>
      <extCode>Mon</extCode>
   </externalCode>
   <externalCode>
      <system>MANUAL</system>
      <extCode>Mon</extCode>
   </externalCode>
</externalCodes>


How To return null value if no records present in select query

Add 72 records with all fields NULL, get first 72 records (you sort in DESC, so NULLs will be last).

( SELECT .....
  ........
  order by date DESC limit 72 )
UNION ALL
( SELECT x.*
  FROM ( SELECT null, null, ... null ) x,
       ( SELECT 1 UNION SELECT 2 ... UNION SELECT 72 ) y
)
order by date DESC
/* , COALESCE('all another resultset fields list') IS NULL */
limit 72

COALESCE needed if date field can be NULL.

PS. Pseudotable with numbers from 1 to 72 can be generated using 2-3-4 small tables. Or in recursive CTE if DBMS allows...

PPS. In recursive CTE you can generate the required number of records up to 72 total records at once.


What is the function of rebuilding indexes?

Indexes are rebuilt to remove fragmentation. There a thousand and one articles and blog posts on the nature of index fragmentation but @BrentOzar recently posted a particularly concise explanation in Stop Worrying About SQL Server Fragmentation.

  Let’s take a step back for a second and pretend that your database is
  a phone book organized by last name, first name.

  As people move into your city, we have to add them to the phone book.
  Ideally, each page has some empty space, and we govern that with the
  fill factor.  When SQL Server rebuilds indexes, it uses the fill
  factor to decide how much free space to leave on each page.  If
  there’s not enough free space, SQL Server has to do some rearranging –
  but it can’t exactly shove a brand new page in the middle of the phone
  book.  The book’s already bound.  We’ll have to tack more blank pages
  onto the end.

  Problem #1 – Internal Fragmentation: We’ve got a newly added page with
  hardly any stuff on it.
  Problem #2 – External Fragmentation: The phone book pages are out of
  order.

Brent's recent article offers an updated perspective on one of his previous series, Index Fragmentation Findings. The older article highlights statistics from much older studies on the damaging effects of fragmentation, the more recent makes a case for mitigating a large percentage of the performance downsides by ensuring your database is fully cached.

Ram is now so comically cheap that it is probably the cheapest, easiest, lowest risk solution to a heavily fragmented database. Especially if the nature of the database design is such that it will naturally become fragmented despite maintenance efforts.


How to do a SQL Server 2016 continuous upgrade

The continuous upgrade referenced in that video was specific to the Upgrade Advisor tool. The tool has since been renamed Data Migration Assistant.

More content on upgrades here: https://docs.microsoft.com/en-us/sql/database-engine/install-windows/upgrade-sql-server?redirectedfrom=MSDN&view=sql-server-ver15


mysql fine tuning: open_tables, opened_tables

Three points:

I don't see the immediate connection between your "too many connections" and the table cache. There might be such connection, but you have not provided info which suggests the two relate.
I disagree with the advice about "If Open_tables climbs above (Opened_tables / Uptime), I have a cause for concern.."
You haven't listed the table_open_cache & table_definition_cache settings. The table_cache variable is an old one.

Some suggestions:

With regard "too many connections", try the following:
Set max_connections to some reasonable value, like 300, throwing around a number.
Set open_files_limit to a very high values, like 8192. By default you are only allowed 1024 open files on linux. Sockets, threads, table descriptors -- all map to files.
Do you have some connection pool which holds many open (idle) connections, with some keepalive?
How many processes do you get on a typical SHOW PROCESSLIST?
With regard table cache: I suggest set a high value. With 3,000 queries per hour you're not remotely near the problem of having a problem with that. Why don't you set:
table_open_cache=2048
table_definition_cache=512 (even though you may only have a couple tables)
With regard the advice about open tables, you should be more interested in the number of tables opened per second (or minute, whatever) in any given time period, rather than looking at the overall number of tables opened since the server started to run. So you should monitor your opened_tables status variable every few minutes, and do the math. You would typically want to see less than 1 table opened in a second. Again, I'm throwing numbers around, but just to give you proportion.
The open_tables is nice to know, as well. On good usage it can be very high, and that means your server is actively reading from tables. Fine! As long as the number of opened_tables/sec does not rise too high, this is no worry.
There could be other reasons for "too many connections". Are the queries very long? Such that they just accumulate? Are some background tasks failing to complete? (You do have a wait_timeout=300 which is good; however some PHP job might be issuing new queries every few seconds, failing to close properly?
Noting again: watch over SHOW PROCESSLIST and see what comes up.


What's the relationship between Documents, XSDs and Collections?

Yes, you are allowed to create multiple schema namespaces within a collection (in the Examples section of CREATE XML SCHEMA COLLECTION, see B. Specifying multiple schema namespaces in a schema collection).

But you can only associate a single XML Schema Collection with an XML field or variable. So whatever schema definitions you might put into a collection should all be relevant to the XML documents that you are going to store in the XML field or variable that will be assigned that XML SCHEMA COLLECTION.

If you will have different XML fields for different types of entities, then I would think those should have their own XML SCHEMA COLLECTIONs.

For more information, please see:

XML Schema Collections
Requirements and Limitations for XML Schema Collections on the Server


Illegal XML character out of the extended event

Since there are no takers yet, I offer a guesswork answer: Yes, I think it's a bug.

Workaround: replace the illegal character once you know which one it is before converting the data to XML like this:

select xml_event_data = CONVERT(xml, replace(event_data,CHAR(3),'&#3;')), *
from sys.fn_xe_file_target_read_file
            (
                '...\Log\error_*.xet',
                '...\Log\error_*.xem',
                null,
                null
            )