Statistics update with automatic sampling messes up density vector and histogram

Why this weird behaviour?

Suppose someone gave you a book of a million integers and told you to make a guess about the overall distribution based on ten sampled numbers. The ten sampled numbers that you receive are 1, 1, 125000, 125000, 250000, 250000, 375000, 375000, 500000, and 500000. One perfectly reasonable guess is that the book contains 500000 unique integers in pairs ranging from 1 to 500000. Another equally valid guess is the book contains five unique integers with each integer present 200000 times. For your data distribution, SQL Server is making an inference about your data based on the sample that's closer to the latter interpretation. The algorithms to build histograms don't work well for all possible sampled data distributions, including one with each unique integer showing up exactly twice. I was able to easily reproduce your described issue in SQL Server 2017.

  Is there some rule of thumb to determine a correct sampling rate or is the fullscan necessary?

The only rule of thumb that I'm aware of is that the correct sampling rate is at least equal to the sampling rate that gives you acceptable query or system performance. I've found DBAs to use a 100% sampling rate to avoid that analysis, and it's a perfectly reasonable shortcut to take for tables that aren't huge.

You could consider replacing your table with an updatable view built on two underlying tables. Each underlying table could contain one half of the data. SQL Server will build separate statistics objects for each table and you may get better performance without having to tweak statistics. I cannot say if this is a good approach for your specific scenario.

  Should I create a statistics update job with fullscan for some statistics?

That is a common solution to the problem that you're describing. For another option, SQL Server 2016 SP1 CU4 introduces the PERSIST_SAMPLE_PERCENT keyword for STATISTICS commands. You can use it to instruct SQL Server to always sample statistics at a certain rate for this column whenever an auto-stats update is triggered. If async stats updates are disabled then keep in mind that query compilation times may occasionally run long for queries that use your column.

  And if that's the case, how do I know which are the statistics that need this kind of treatment?

The usual answer is to monitor your workload for unacceptable query performance, determine which queries have statistics issues, and to fix those issues with statistics changes. If you wish to be proactive you could consider analyzing statistics on a copy of production. First get sampled statistics for all relevant columns on the database and save all of the density vectors to a table. You can use DBCC SHOW_STATISTICS WITH DENSITY_VECTOR to do that. Then update all of the same statistics with FULLSCAN and save those density vectors as well. You can then compare the density vectors to find the statistics objects with the largest differences. This won't find all statistics issues, but it will find those similar to what you observed in this question.


Filling not applicable fields with N/A rather than leaving them empty or Null

Let me change the direction of the question...

If you are making an 'array' of Ec's as columns, that is poor schema design.  Instead, you should have another table something like this:

CREATE TABLE Ec (
    id ... -- id in the main table
    ec ... -- one of several Ec values
    PRIMARY KEY (id, ec)
) ENGINE=InnoDB;

There would be any number of rows in this table, depending on the number of Ec values you need.  There is no need for NULL or N/A.


Sql Max value from multiple columns in Computed Column

First thing... I'm not advocating THAT you do this... I'm simply showing you HOW to do this. If your table experiences high volume inserts and/or updates, you could see a noticeable performance hit. Using scalar UDFs in computed columns will force all queries against the table to run serially.

Start by creating a scalar function similar to the following...

CREATE FUNCTION dbo.GreatestOfThreeInts
/* ================================================================================================
Scalar function created for the sole purpose of calculating the MaxVal computed column on dbo.Test.
================================================================================================ */
(
    @C1 INT,
    @C2 INT,
    @C3 INT
)
RETURNS INT WITH SCHEMABINDING --, RETURNS NULL ON NULL INPUT --<< use this if NULLs are a possibility..
AS
BEGIN
    DECLARE @MaxVal INT = ( SELECT MAX(x.Val) FROM ( VALUES (@C1), (@C2), (@C3) ) x (Val) );
    RETURN ISNULL(@MaxVal, 0);
END;
GO

Either create your table with a PERSISTED computed column or, if the table already exists, use the ALTER / ADD syntax to add the PERSISTED computed column...

CREATE TABLE dbo.Test (
    C1 INT NOT NULL,
    C2 INT NOT NULL,
    c3 INT NOT NULL,
    MaxVal AS dbo.GreatestOfThreeInts(C1, C2, C3) PERSISTED NOT NULL    -- persist the value so that it doesn't need to be constantly recomputed
    );
GO

CREATE NONCLUSTERED INDEX ix_Test_MaxVal ON dbo.Test (MaxVal) INCLUDE (C1, C2, c3);
GO

Why do I keep saying PERSISTED?... Buy once, cry once... Unless you have a very write heavy usage pattern, you'll be better off computing the values on inserts & updates, than every time you reference the column in a select... Especially if that column is going to be used in a predicate or sorting operation.

Sooo... Let's see it in action...

INSERT dbo.Test (C1, C2, c3) VALUES
    (123,456,789),
    (345,478,123),
    (523,321,852),
    (111,471,951),
    (874,320,357),
    (965,102,478);
GO

SELECT * FROM dbo.Test t ORDER BY t.MaxVal OPTION(QUERYTRACEON 176);
GO

SELECT * FROM dbo.Test t WHERE t.MaxVal >= 800 AND t.MaxVal < 900 OPTION(QUERYTRACEON 176);
GO

Results...

C1          C2          c3          MaxVal
----------- ----------- ----------- -----------
345         478         123         478
123         456         789         789
523         321         852         852
874         320         357         874
111         471         951         951
965         102         478         965

C1          C2          c3          MaxVal
----------- ----------- ----------- -----------
523         321         852         852
874         320         357         874

Hope this helps,
Jason

Edit #1: A BIG THANK YOU to Erik for adding the link, pointing out the fact that using a scalar UDF to compute a column will prevent the optimizer from considering a parallel execution plan... Even when the computed column is persisted. A fact that I actually knew but completely omitted from my initial answer. What I didn't know is the OPTION(QUERYTRACEON 176) thing... Picking up that little nugget, more than covered the cost of admission for me!

Edit #2: Without inviting the religious debate of "NULL vs NOT NULL" column constraints, I'll simply state that my personal "default" is make all columns NOT NULL unless there is a compelling reason to do otherwise... That said, @MartinSmith makes some good points... Including the fact that the OP, by not specifying NULLability, made all columns NULLable. Plus, after the back & forth, I was just curious to see if the RETURN ISNULL(@MaxVal, 0); was doing anything other than irritating people reading the T_SQL...
Short answer... It does not.

The following includes the introduction of a "control" table (no computed column) and NULLable versions of dbo.GreatestOfThreeInts & dbo.Test (dbo.GreatestOfThreeInts_2 & dbo.Test_2)

CREATE FUNCTION dbo.GreatestOfThreeInts_2
/* ==================================================================================================
Scalar function created for the sole purpose of calculating the MaxVal computed column on dbo.Test_2.
================================================================================================== */
(
    @C1 INT,
    @C2 INT,
    @C3 INT
)
RETURNS INT WITH SCHEMABINDING
AS
BEGIN
    DECLARE @MaxVal INT = ( SELECT MAX(x.Val) FROM ( VALUES (@C1), (@C2), (@C3) ) x (Val) );
    RETURN @MaxVal;
END;
GO

CREATE TABLE dbo.Test_2 (
    C1 INT NULL,
    C2 INT NULL,
    c3 INT NULL,
    MaxVal AS dbo.GreatestOfThreeInts_2(C1, C2, C3) PERSISTED   -- persist the value so that it doesn't need to be constantly recomputed
    );
GO

CREATE NONCLUSTERED INDEX ix_Test2_MaxVal ON dbo.Test_2 (MaxVal) INCLUDE (C1, C2, c3);
GO

CREATE TABLE dbo.Control (
    C1 int NOT NULL,
    C2 int NOT NULL,
    c3 int NOT NULL,
    MaxVal INT NOT NULL
    );
GO

CREATE NONCLUSTERED INDEX ix_Control_MaxVal ON dbo.Control (MaxVal) INCLUDE (C1, C2, c3);
GO

And because the 6 rows in my original answer isn't much of a test, the following will load all 3 tables with 1 million rows of test data...

-- clear out any existing data...
TRUNCATE TABLE dbo.Test;
GO
TRUNCATE TABLE dbo.Test_2;
GO
TRUNCATE TABLE dbo.Control;
GO

-- add 1M rows of test data...
WITH
    cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)),
    cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
    cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),
    cte_Tally (c1, c2, c3) AS (
        SELECT TOP (1000000)
            ABS(CHECKSUM(NEWID())) % 9000 + 1000,   -- randomly generate INTs between 1000 and 9999
            ABS(CHECKSUM(NEWID())) % 9000 + 1000,   -- no, I don't have an actual reason for using that specific range...
            ABS(CHECKSUM(NEWID())) % 9000 + 1000    --
        FROM
            cte_n3 a CROSS JOIN cte_n3 b
        )
INSERT dbo.Test (C1, C2, c3)
SELECT
    t.c1,
    t.c2,
    t.c3
FROM
    cte_Tally t;
GO

-- use dbo.Test to insert dbo.Test_2 & dbo.Control so all 3 tables will have the exact same data values...
-- (to compare actual insert performance, use the cte_Tally to load all tables)

INSERT dbo.Test_2 (C1, C2, c3)
SELECT
    t.C1, t.C2, t.c3
FROM
    dbo.Test t;
GO

INSERT dbo.Control (C1, C2, c3, MaxVal)
SELECT
    t.C1, t.C2, t.c3, t.MaxVal
FROM
    dbo.Test t;
GO


Is it possible to get SQL Server 2008 to download a file from a URL

Use an external process that does the HTTP work and the inserts into the database. I explicitly advise against using SQLCLR for this. Hijacking precious SQL Server workers for the boring job of waiting for HTTP results will one day impact your server severely.

  but the size of the XML can be quite variable (esp given that a binary
  would pass it to the stored proc using L2S) and Iv'e already
  overflowed the input to the SP a couple of times because there's been
  too much data

Use the techniques from DOWNLOAD AND UPLOAD IMAGES FROM SQL SERVER VIA ASP.NET MVC to stream the HTTP response into the database.


How to set foreign key in table in one database that references a table in another database

A FOREIGN KEY constraint cannot reference a table in a different database. It's by design.

Here's an example:

CREATE DATABASE test1
GO

USE test1;
GO

CREATE TABLE tableA (
     PK int PRIMARY KEY CLUSTERED
    ,someColumn varchar(50)
)
GO

CREATE DATABASE test2
GO

USE test2;
GO

CREATE TABLE tableB (
     PK int PRIMARY KEY CLUSTERED
    ,aForeignKey int NULL FOREIGN KEY REFERENCES test1.dbo.tableA(PK)
)
GO

This will throw an error:

Msg 1763, Level 16, State 0, Line 19
Cross-database foreign key references are not supported. Foreign key 'test1.dbo.tableA'.
Msg 1750, Level 16, State 0, Line 19
Could not create constraint. See previous errors.

An (ugly) workaround is a trigger that checks whether the data is present in the referenced table:

CREATE TRIGGER TR_Fake_FK ON dbo.tableB
    FOR INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (
        SELECT *
        FROM INSERTED AS I
        WHERE NOT EXISTS (
            SELECT *
            FROM test1.dbo.tableA AS A
            WHERE I.aForeignKey = A.PK
        )
    )
    BEGIN
        RAISERROR('Violation of fake constraint',16,1);
        ROLLBACK;
    END

END

The trigger won't handle cascading behavior of foreign keys and you'll also need another trigger on the referenced table to handle UPDATEs and DELETEs. In other words: don't do it.

EDIT: In case you're wondering if a VIEW or a SYNONYM to "copy" the referenced table to the second database will work (which seems to be what you're trying to do), it won't.


Select Multiple Values from Same Column; one sql statement and split into separate columns

The way you are describing the expected result; I am concerned that what you are looking for won't get you what you want. Taking the example from the referenced post, you may actually want:

create view yourview1 as (
  select
    user_id,
    case when Item_Type = "first_name" then meta_value end as first_name,
    case when Item_Type = "last_name" then meta_value end as last_name,
    case when Item_Type = "street_add" then meta_value end as street_add,
    case when Item_Type = "city" then meta_value end as city,
    case when Item_Type = "state" then meta_value end as state
  from User_Items
);

create view yourview1_Pivot as (
  select
    user_id,
    MAX(first_name) as first_name,
    MAX(last_name) as last_name,
    MAX(street_add) as street_add,
    MAX(city) as city,
    MAX(state) as state
  from yourview1
  group by user_id
);

source


Why mongo is choosing the wrong index / execution plan?

these indexes are existing in collection :

db.products.createIndex({"origins_tags": 1,"sortkey": -1}, { background: true })
db.products.createIndex({"last_modified_t": -1}, { background: true })

The MongoDB query optimizer chooses the most efficient query plan based on the query shape (combination of query predicate, sort, and projection) and candidate indexes. If there are multiple candidate plans for a given query shape, a trial evaluation will run to determine which index returns the initial batch of results (101 documents) with the least amount of "work" measured. The winning plan will be cached and periodically reevaluated if query performance or other circumstances change (for example, adding or dropping indexes).

Since neither index is ideal for your query, index selection may vary depending on which index returns the initial batch of results faster during plan evaluation:

The first index is more selective given origins_tags as filter criteria, but it requires a document fetch and in-memory sort (a blocking query stage with a 32MB limit) in order to return results ordered by last_modified. The work required to use this index for the given query will depend on the total number of matching documents: every matching document has to be fetched for the sort. The worst case scenario would be a query that needs to perform an in-memory sort of more than 32MB of data and results in an exception.
The second index returns results in the desired sort order but requires a collection scan to filter by origins_tags. The work required to use this index depends on how quickly the matches are found: this index can stream matches as they are found and stop as soon as there are enough matches. The worst case scenario would be a full collection scan to confirm there are no further matching documents.

If evaluating the two plans result in a tie (both appear to perform the same work to return initial results for a given query shape), the plan that doesn't require an in-memory sort will win.

You can see details of plan evaluation by explaining a query with allPlansExecution mode: db.products.find({...}).explain('allPlansExecution').

  keysExamined:721542 docsExamined:721542 (basically, the whole collection is examined)

This is the worst case scenario for the second index: there are 323 matches out of 721,542 documents and you've asked for results 320-340 (via skip: 20, limit: 20).

  I don't understand how the second index can give faster results during plan evaluation

Plan evaluation only considers which candidate plan returns the initial batch of results (101 documents) with less overall effort. The query planner does not run the plans to completion during evaluation, or keep any metrics on the distribution of key values. The cached query plan would have been based on a comparison where the second index did not not have to perform the collection scan.

  When mongo choose a bad plan, it should be able to see that the execution time is not good and it should not choose same plan for next queries.

Cached plans are re-evaluated if the performance declines, as per the Query Plans flow chart in the MongoDB documentation. However, if you have multiple candidate plans without a deterministic winner, it is possible for a query plan to be chosen that is faster for initial results (or the same query shape with different values), but suboptimal for later results.

How can I fix this?

To fix this issue you should add a compound index supporting your filter & sort criteria:

db.products.createIndex({"origins_tags": 1,"last_modified_t": -1}, { background: true })

Alternatively (and less ideally) you could provide an index hint to force the query to use the first index. Note that hinting will ignore any future indexes added that might be more ideal, and will also fail with an exception if the in-memory sort needs to work with more than 32MB of data.

Adding the suggested compound index would result in the most efficient ratio of keys and docs examined to the number of results returned.


put the result of a query into a variable and WITH statement

Assign the variable in the last query.
DECLARE @count INT;

WITH person_address (id)
as (
    SELECT AddressID
    FROM [AdventureWorks].[Person].[Address]
   )
   SELECT @count = count (*)
   FROM person_address;

select @count;

create table t (id int);

insert into t values
(1),(1),(2),(1),(2);

declare @cnt int;

with ct as
(
    select id from t
)
select
  @cnt = count(*)
from
  ct
where id = 1;

select @cnt;

| (No column name) |
| ---------------: |
|                3 |

db<>fiddle here


To use the Linked Server, have you set up the user account on the AX server as well as the other?  You need it to be in place on both in order to use account impersonation.

Personally, I would go with option (b), if it's not too complicated.  Depending on what you need, you could consider providing an SSRS report to expose the data they need and no more.


Implications of using a Unique NonClustered Index with Covering Columns instead of a Primary Key

Performance Optimisation. By removing either the PK or an Index, there
  will be less pages needed for my indexes = faster writes, plus also
  maintenance/operational benefits, i.e. one less index to keep
  defragged etc.

You need to be able to prove that what's proposed will actually help (cost vs. benefit). For what reason is this change being considered? Are there actually performance issues with this table, or did it just "look wrong"?

Here are some other questions that will help you come to the best decision for your environment:

How much time would be saved in the maintenance window? In the backup window?
How much storage space would this save (data files, log files, backups, etc.)?
Is INSERT performance on this table really a bottleneck right now? How much would it improve? Is removing an index the best strategy to fix that problem?
Will this cause problems with database tools and frameworks (ORMs particularly) that expect each table to have a primary key and not just a unique index? Transactional Replication requires a primary key on published tables.
Is a self-documenting database schema important?
Despite its limited use, is the narrowness of the primary key index still allowing the optimizer to produce more efficient plans for certain queries? (Use sys.dm_db_index_usage_stats to find out.)

Personally speaking, from what you've told us, I would leave it alone until it can be proven that both (a) the extra index is a problem, and (b) removing it is the solution.


SQL Server 2016 - sp_execute_external_script

After turning on external scripts, you'll need to restart SQL Server so that when you run

sp_configure 'external scripts enabled';

it returns a run_value of 1:

name                                minimum     maximum     config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
external scripts enabled            0           1           1            1

Documentation: External Scripts Enabled server configuration option


Doing a insert across two databases with same collations and getting an error

Ok, so when you ran

SELECT * FROM sys.columns WHERE object_id = object_id('tb1')

for each of the tables you found that the collation was not the same as the database default.  At this point you have two options.

Change the collation in the query

All you have to do here is use the COLLATE  phrase after the column that needs it.

select distinct Number, Name, 'Unknown', 'Unknown', 'Unknown', NULL, 'No', 'GL', 'N/A'
from DB2.dbo.tbl1 T2
where T1.Number not in (select distinct AccountNumber COLLATE SQL_Latin1_General_CP850_CI_AI from DB1.dbo.tbl1)

The above would force the AccountNumber column to use SQL_Latin1_General_CP850_CI_AI in this particular spot.

Change the collation of the columns

This could have ramifications elsewhere so you want to be careful before doing it but to change collation of a column it's usually just a simple ALTER TABLE ALTER COLUMN statement.

ALTER TABLE tbl1 ALTER COLUMN AccountNumber char(10)
COLLATE SQL_Latin1_General_CP850_CI_AI NOT NULL


Implement roles in you DB.
Manage grants on the schemas using roles definition.(single point)
Grant these roles to your users.

in regards users development, create a schema for each one of them with full rights (ownership) where they can play as much as they want.


Yes, mongod <3.4 have slow balancing speed because of "internal throttling", but if you do your balancing work with script (using MoveChunk-command) it's much faster.

However, if you upgrade your system to 3.4 series, balancing will be about 10x faster and even faster if you have multiple shards (>=4)


any documentation on tpcc-mysql benchmark tool

Here's a recent blog post (2013-07-01) that shows how to use tpcc-mysql and graph results:

http://www.mysqlperformanceblog.com/2013/07/01/tpcc-mysql-simple-usage-steps-and-how-to-build-graphs-with-gnuplot/

This is written by Michael Rikmas, who is a super nice guy, and a long time Percona support engineer.


Create index for WHERE COALESCE() condition

Strip the incorrect pair of parentheses:

CREATE INDEX idx_test_c ON test_c(COALESCE(updt_dttm, load_dttm));

db<>fiddle here

The way you had it was effectively indexing the composite value (updt_dttm, load_dttm), COALESCE not doing anything.

Your second problem was revealed by the added table definition:

CREATE TABLE test_c (
  insrt_prcs_id bigint NOT NULL
, updt_prcs_id  bigint
, src_sys_id    integer NOT NULL
, load_dttm     timestamp(6) with time zone NOT NULL
, updt_dttm     timestamp(6) without time zone   -- !!!
);

Why would you use different data types for load_dttm and updt_dttm? Fix that and the second problem goes away. I suggest:

CREATE TABLE test_c (
  -- ...
, load_dttm     timestamp with time zone NOT NULL
, updt_dttm     timestamp with time zone  -- !!!
);

Why?

You got this error:

  ERROR: functions in index expression must be marked IMMUTABLE

.. because COALESCE must return one data type. timestamp with time zone (timestamtz) is the "preferred type", so updt_dttm timestamp is coerced to timestamptz, which uses a function that depends on the timezone setting of the current session and hence is not IMMUTABLE. And index expressions cannot involve non-IMMUTABLE functions.

Related:

Is timestamptz preferred when timezone logic is performed by the client?
Ignoring time zones altogether in Rails and PostgreSQL

You could make the index work with your original (broken) table design by hard-coding the time zone - 'Europe/Vienna' in my example:

CREATE INDEX ON test_c(COALESCE(updt_dttm AT TIME ZONE 'Europe/Vienna', load_dttm));

Just a proof of concept. Queries hoping to use that index would have to use the same expression. Don't go there if you don't have to. Fix your table definition instead.


Oracle: Interval partitioning and dependant tables

You can't use interval partitioning with reference partitioning, however, It's possible to mix range partitioning and reference partitioning, as shown in the example of reference partitioning in the 11g doc:

  The benefit of this extension is that tables with a parent-child relationship can be logically equipartitioned by inheriting the partitioning key from the parent table without duplicating the key columns. The logical dependency also automatically cascades partition maintenance operations, thus making application development easier and less error-prone.

The child table doesn't need to contain the partition key (here the log date). It inherits the partition key from the parent table.

Range partitioning involves a bit more maintenance because you have to create the partitions  yourself. However, once the partitions are created, range and interval work similarly.


Where are sql profiler trace files stored?

If you create a new trace and check the 'Save to file' box, you will see a save as dialogue box which defaults to Libraries\Documents.

If your goal is to locate the trace file that you already saved, simply try opening the file in profiler. If you still cannot find it:

Download WinDirStat (https://windirstat.info/)
Scan your C: drive.
Look for the trace file extension on the top right of the UI (.trc) and select it. Doing this will highlight the location of the file(s). Right click and "explorer here" will do the trick.

Retracing your steps should suffice though.


Why is Oracle's number datatype so HUGE? and why do I seem to have no alternatives?

See here ixora.

Number has variable length, like string.
each digit occupies one nibble (4bits, half byte)
NUMBER implementation is platform independent
you can validate size of number value in a column by calling dump() function

PS: Oracle also supports native intergers like PLS_INTEGER, but these can only be used in PL/SQL context only.


If a Master has relay logs, then the Master must also be a Slave in the midst of some Replication topology (i.e., Master/Master, Daisy-Chained Replication)

What could cause relay logs to grow like this?

BROKEN REPLICATION

MySQL Replication is broken when the IO Thread or SQL thread dies under these SCENARIOS:

SCENARIO #1 : When the IO Thread and SQL thread are off, one of two things happended

MySQL was started with skip-slave-start enabled
Someone/something ran STOP SLAVE;

SCENARIO #2 : When the IO thread dies

nothing can pile up the relay logs
The SQL thread processes all SQL commands in the relay logs or until a SQL error occurs

SCENARIO #3 : When the SQL thread dies

SQL error occurred processing a SQL command
Running SHOW SLAVE STATUS\G shows you the Last_Errno and Last Error
IO Thread continued collecting SQL commands from the Master, making relay logs grow

It is SITUATION #3 that's the problem. When the SQL thread dies due to a SQL error, there is no built-in mechanism in MySQL Replication that triggers the IO thread to disconnect.

RECOMMENDATION

The only decent way to control the growth of relay logs is to set the limit on it

[mysqld]
relay_log_space_limit=4G

Setting relay_log_space_limit places a cap of 4G.

When a relay log is completely processed

it is rotated out
the SQL thread starts working on the next relay log
the I/O thread starts loading SQL from the Master from the last place it left of from, as long as there is enough freespace on the disk

EPILOGUE

If the Master used to be a Slave and it does not need to be anymore, simple disable it.

mysql -e"STOP SLAVE; CHANGE MASTER TO MASTER_HOST='';"
rm -f /var/lib/mysql/master.info

If the Master is a Slave, go correct the SQL error.

I would suggest this if the SQL error is in the way:

STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE SQL_THREAD;

then run SHOW SLAVE STATUS\G every minute to see if the relay logs get processed and rotated.


You didn't restore the dump. Your pg_restore invocation output the contents of the dump to standard output. To restore the dump into a database, you have to add the -d option:
pg_restore -U postgres -h db -d databasename ambari.dump

The second attempt seems to have succeeded. You probably don't see the tables because they are not on your search_path. Try \dt *.*.


MySQL Stored Procedure Compiles Without Error (I want the error to occur)

When you right click on stored procedures, you hit create stroed procedures.

this should show spelling errors like INsRT and systax erors

But logic errors it will not find.

So start using error handling and user defined variables to debug it properly.

But you can test all queries in a normal Query tab this would also check for syntax errors


If you need to do that very often and you can connect to all of those servers directly, I would setup one Postgres instance that acts as a "unified" view on all other servers.

To do that create a new Postgres database (or server) and install the postgres_fdw extension.

Then create one foreign server for each of the database you want to look at.

Then create an (empty) table in your new database, that looks exactly like the corresponding table on the other servers, e.g.

create table auth_user
(
  id integer,
  user_name varchar(100)
);

Then for each server create a foreign table, e.g.:

create foreign table auth_user_server1 ()
inherits auth_user
server customer_1_server options (table_name 'user_auth');

It's probably a good idea to include the option updatable 'false' when defining the foreign table to prevent accidental updates from the integration server to the remote servers.

The above steps could all be scripted if necessary.

Once all tables are defined, you can do a simple:

select count(*)
from auth_users;

on your "integration" server and Postgres will automatically query all remote tables.


Restore of copy_only backup of encrypted database made without FORMAT but with INIT fails

This is indeed a bug in SQL Server. We (@sepupic and I) had some discussions offline and managed to narrow down the circumstances: The error occurs when:

You use TDE
The backup is compressed
You specify COPY_ONLY
You specify INIT
The backup file exists

Repro:
--The database has TDE
--Backupfile doesn't exist at this stage

BACKUP DATABASE db1Encrypted
TO DISK = 'R:\db1Encrypted.bak'
WITH INIT, COPY_ONLY, COMPRESSION, CHECKSUM

--backup file 660 kb

RESTORE VERIFYONLY FROM DISK = 'R:\db1Encrypted.bak' WITH CHECKSUM
--OK

--Backupfile now exists, below causes unreadable backup

BACKUP DATABASE db1Encrypted
TO DISK = 'R:\db1Encrypted.bak'
WITH INIT, COPY_ONLY, COMPRESSION, CHECKSUM

--backup file 3890 kb

RESTORE VERIFYONLY FROM DISK = 'R:\db1Encrypted.bak' WITH CHECKSUM
--Fails "The media family on device 'R:\db1Encrypted.bak' is incorrectly formed"


How can I capture queries that fail on the SQL Server due to connection failures?

I believe you have to do this on application level and not on SQL Server.

Here is .NET way of inserting in to the event viewer: http://forums.asp.net/t/1503109.aspx/1


Bidirectional Replication Push and Pull with SQL Server

Yes you can run all the replication agents from the machine inside the firewall.  After everything is setup normally, just disable the SQL Agent jobs that run the agents on the DMZ machine, then create the same jobs on the machine inside the network.  Enable the jobs inside the network and start them.

The push and pull terminology with SQL replication just refers to the machine that actually runs the job.  There's no other differences besides the job location.


Changing your table from a heap to having a clustered index should significantly improve your performance on both queries and perhaps even on inserts. Generally speaking, your clustered index should be narrow, unique, and ever increasing. Using a datetime that you can't guarantee to be unique is not ideal because it's 8 bytes and, since it isn't unique, sql will add a four byte uniquifier to non unique rows. You may be better off using an identity column with an int as your clustered index and, since that's what the "row_number" queries are really after anyway (an ever increasing unique number) that might be a great way to go especially if you have a number of nonclustered indexes already (because the clustered is used as a row pointer by the nonclustered so it adds size to them). The 4 billion available int values leave you decades of growth.

I suggest you make a testing copy of your database and then test it with a clustered index on your datetime value and contrast that with a test using a clustered index on a new identity column (and a nonclustered index on your datetime). See which one does better with your query load. Both scenarios will outperform a heap.


Users by default do not have the privilege to use those objects:

[oracle@o7ca1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Mar 4 20:42:48 2019
Version 19.2.0.0.0

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

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.2.0.0.0

SQL> show user
USER is "SYS"
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     564558

SQL> create user u1 identified by u1;

User created.

SQL> grant create session to u1;

Grant succeeded.

SQL> conn u1/u1
Connected.
SQL> select current_scn from v$database;
select current_scn from v$database
                        *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select dbms_flashback.get_system_change_number from dual;
select dbms_flashback.get_system_change_number from dual
       *
ERROR at line 1:
ORA-00904: "DBMS_FLASHBACK"."GET_SYSTEM_CHANGE_NUMBER": invalid identifier

DBMS_FLASHBACK

  DBMS_FLASHBACK Security Model

  To use the DBMS_FLASHBACK package, you must have the EXECUTE privilege
  on it.

About Dynamic Performance Views

  After installation, only user SYS or anyone with SYSDBA privilege has
  access to the dynamic performance tables.

We do not know what privileges you had before, that you do not have now.

Ask your DBA to grant you the required privileges.

grant read on sys.v_$database to petar;

Or:

grant execute on dbms_flashback to petar;


Can I safely kill OPTIMIZE TABLE on an InnoDB table?

According to MySQL Certification Guide :

  The OPTIMIZE TABLE statement cleans up a MyISAM table by defragmenting
  it. This involves reclaiming unused space resulting from deletes and
  updates, and coalescing records that have become split and stored
  non-contiguously. OPTIMIZE TABLE also sorts the index pages if they
  are out of order and updates the index statistics

  Also works for InnoDB tables, but maps to ALTER TABLE, which rebuilds
  the table. This updates index statistics and frees space in the
  clustered index.

So In Case of InnoDB :

Innodb is ACID compliant, the optimize table simply copies all records into a new table

If the index pages are not sorted, sort them,
If the table's statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.
If the table has deleted or split rows, repair the table.

And if you try to kill it when it's running you will not loose any records.

Additionally, case of InnoDB with innodb_defragment=1, there will be no ROLLBACK required, as in this case OPTIMIZE TABLE is incremental.


Should master and slave have same mysql version?

Your replication configuration 5.5.11 -> 5.5.30 is a supported configuration and there is no real justification for deploying a new slave using old software.

In fact, MySQL replication is supported across major releases as long as the two releases are consecutive, so 5.1.x -> 5.5.x is supported and 5.5.x -> 5.6.x is supported.

The only caveats are that the slave should be the same version as -- or newer than -- the master, and if you're doing circular replication, you should not have more than two versions of MySQL in the chain (and best practice suggests that they be identical in this case).

http://dev.mysql.com/doc/refman/5.5/en/replication-compatibility.html

If you are copying the files from another machine of a different version, don't forget to run mysql_upgrade on the new slave.


MobiLink: How to propagade schema change for a synch profile to remote databases

Mobilink can also pass schemachanges to it's replicated databases.
For this, there exists the start synchronization schema change statement

This looks like this:

START SYNCHRONIZATION SCHEMA CHANGE
  FOR TABLES DBA.Sales, DBA.Products
  SET SCRIPT VERSION = 'version_2';
ALTER TABLE DBA.Sales ADD SUBTOTAL NUMERIC (10,2);
ALTER TABLE DBA.Products ALTER QUANTITY BIGINT;
STOP SYNCHRONIZATION SCHEMA CHANGE;


Persistent inserts in a UDF even if the function aborts

When an exception occurs everything is rolled back. You want to execute selected code "outside" the current transaction context, which is commonly referred to as autonomous transactions. This is not currently implemented (as of pg 9.4). There is an item in the Postgres TODO wiki, but it's a tricky matter, don't hold your breath.

For now you can use the additional module dblink. It provides functions to connect to another DB in a separate connection (and thus also separate transaction) to execute SQL there. What's done is done and cannot be rolled back. By connecting to the same DB another time you effectively achieve autonomous transactions (with some extra overhead). It's decently fast.

Proof of concept

Preparations

You need to install dblink once per database (where you run the function):

CREATE EXTENSION dblink;

For convenience I encapsulate the connection info in a FOREIGN SERVER plus USER MAPPING:

CREATE SERVER myserver FOREIGN DATA WRAPPER dblink_fdw
OPTIONS (hostaddr '127.0.0.1', dbname 'test');

CREATE USER MAPPING FOR role_source SERVER myserver
OPTIONS (user 'role_target', password 'secret');

There are various options for the connection string. The example is for a user named role_source to connect as role_target in a DB test on localhost 127.0.0.1. Could be the same role name for source and target. I use a password here (simple example) but I much prefer password-less access for the purpose, so we don't have to save a PW (and it does not end up in backups etc.):

Run batch file with psql command without password

However, per documentation:

  Only superusers may use dblink_connect to create
  non-password-authenticated connections. If non-superusers need this
  capability, use dblink_connect_u instead.

Or create a SECURITY DEFINER function to encapsulate relevant parts. See below.

Main function

CREATE OR REPLACE FUNCTION f_work()
  RETURNS void AS
$func$
DECLARE
   sql1 text := 'SELECT 1';  -- dummy code
   sql2 text := 'SELECT 2';

BEGIN
   PERFORM dblink_connect('myserver');  -- name of foreign server
   PERFORM dblink_exec($f$INSERT INTO backup_logs(sql)
                          VALUES ('f_work() start')$f$);   -- log start

   PERFORM dblink_exec('INSERT INTO backup_logs(sql)
                        SELECT ' || quote_literal(sql1));  -- log sql1
   EXECUTE sql1;

   -- RAISE EXCEPTION 'foo'; -- unquote to test error case

   PERFORM dblink_exec('INSERT INTO backup_logs(sql)
                        SELECT ' || quote_literal(sql2));  -- log sql2
   EXECUTE sql2;

   PERFORM dblink_exec($f$INSERT INTO backup_logs(sql)
                          VALUES ('f_work() end')$f$);     -- log end
   PERFORM dblink_disconnect();
END
$func$  LANGUAGE plpgsql VOLATILE;

Be sure to have a USER MAPPING for the role you are running this function with, or make it a SECURITY DEFINER function owned by the role you want to work with:

Permission for sequence in another schema
Creating UUID extension as the non super privileged user


You could UNPIVOT, apply a ROW_NUMBER and then PIVOT it back.

select
    CustID,
    CustName,
    [1] as [VL1],
    [2] as [VL2],
    [3] as [VL3],
    [4] as [VL4],
    [5] as [VL5],
    [6] as [VL6],
    NUMBER
from(
    select
        CustID,
        CustName,
        NUMBER,
        V,
        Seq = row_number() over (partition by CustID order by case when V = '' then 7 else null end)
    from
        (
        select
            CustID,
            CustName,
            VL1,
            VL2,
            VL3,
            VL4,
            VL5,
            VL6,
            NUMBER
        from #CustData
        ) as cp
    unpivot
        (
        V for Vs in (VL1, VL2, VL3, VL4, VL5, VL6)
        ) up
    ) s
pivot
    (
    Max(V) for Seq in ([1],[2],[3],[4],[5],[6])
    ) p


Log Reader Agent is not running - The job failed

The recommended approach is to have replication agents run under Windows accounts, not the SQL Server Agent service account, and the accounts should be granted only the required permissions.

Create a dedicated Windows account for the Log Reader Agent, grant it the appropriate permissions covered in Replication Agent Security Model, and use this account for your Log Reader Agent Security.


Dynamic quorum and Dynamic witness in Always On AG 2016

Will it automatically take away 1 vote from either one of the nodes? If yes, remaining vote will be 33% not majority and single point of failure.

Yes correct. Dynamic witness works in such a way that it tires to keep total number of votes odd. In your case when the FS witness is down the Dynamic quorum will toggle one vote from one of the node such that its vote becomes zero and total votes now voting would be one.

The dynamic quorum and witness are very much interrelated. In above case the process of adapting quorum after FS witness failed is actually dynamic quorum. It adjusted itself after the failure.

I would strongly suggest you to read Understanding cluster and pool quorum


What are points to consider when replacing the SQL Server service account?

If you have Kerberos in the mix you may run into SPN issues. – Jeff A

Depending on your domain functional level (need to be 2012 I believe), you could use Group managed service accounts – Bob Klimes

Any manually created SPNs will need to be deleted and recreated. Any SPNs automatically created by the service on startup will be removed on shutdown, so that shouldn't be a concern. I'll second Bob Klimes' suggestion that you should go to Group Managed Service accounts, if possible. They're absolutely fantastic. Finally, you may need to configure service account permissions within each instance depending on your current configuration. Generally these accounts can be minimally provisioned, but it'll depend on your current config. – John Eisbrener

PowerShell can be used to automate – Bob Klimes

Community Wiki answer compiled from question comments. Community wiki posts have been donated to the community in hopes that others will edit them to keep them up to date, to add useful information, and generally improve their quality.


Unique constraint on multiple columns and one of them already has index

If the purpose of the unique constraint (with its associated unique index) is simply to enforce uniqueness then you should change the column order for your unique constraint to col3, col4, col1, col2 and then your index will be redundant and can be dropped.

Otherwise there may be some benefit from having another index on your table with different leading columns, you should consider your workload, if the table is often accessed with a predicate on col1 or on both col1 and col2 and maybe that is a good case to have those columns first in the key.

Also @srutzky comment about the clustered index has merit and is worth considering.


Custom unique constraint, only enforced if one column has a specific value

In other words, you want values in the column subset to be unique among rows where the column type is 'true'.
A partial unique index will do that:
CREATE UNIQUE INDEX tbl_some_name_idx ON tbl (subset) WHERE type = 'true';

Data type does matter. If the column type is boolean (likely should be), you can simplify:
CREATE UNIQUE INDEX tbl_some_name_idx ON tbl (subset) WHERE type;

This way you can even make combinations with NULL unique, which is not possible otherwise. See:

PostgreSQL multi-column unique constraint and NULL values


At what point does a database update its indexes in a transaction?

I work with SQL Server and Oracle. There are probably some exceptions, but for those platforms the general answer is that data and indexes will be updated at the same time.

I think that it would be helpful to draw a distinction between when the indexes are updated for the session that owns the transaction and for other sessions. By default, other sessions will not see the updated indexes until the transaction is committed. However, the session that owns the transaction will immediately see the updated indexes.

For one way to think about it, consider at a table with a primary key. In SQL Server and Oracle this is implemented as an index. Most of the time we want there to immediately be an error if an INSERT is done which would violate the primary key. For that to happen the index must be updated at the same time as the data. Note that other platforms, such as Postgres, allow deferred constraints which are checked only when the transaction is committed.

Here's a quick Oracle demo showing a common case:

CREATE TABLE X_TABLE (PK INT NULL, PRIMARY KEY (PK));

INSERT INTO X_TABLE VALUES (1);
INSERT INTO X_TABLE VALUES (1); -- no commit

The second INSERT statement throws an error:

  SQL Error: ORA-00001: unique constraint (XXXXXX.SYS_C00384850) violated

  00001. 00000 -  "unique constraint (%s.%s) violated"

  *Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
             For Trusted Oracle configured in DBMS MAC mode, you may see
             this message if a duplicate entry exists at a different level.

  *Action:   Either remove the unique restriction or do not insert the key.

If you prefer to see an index update action below is a simple demo in SQL Server. First create a two column table with one million rows and a nonclustered index on the VAL column:

DROP TABLE IF EXISTS X_TABLE_IX;

CREATE TABLE X_TABLE_IX (
ID INT NOT NULL,
VAL VARCHAR(10) NOT NULL
PRIMARY KEY (ID)
);

CREATE INDEX X_INDEX ON X_TABLE_IX (VAL);

-- insert one million rows with N from 1 to 1000000
INSERT INTO X_TABLE_IX
SELECT N, N FROM dbo.Getnums(1000000);

The following query can use the nonclustered index because the index is a covering index for that query. It contains all of the data needed to execute it. As expected no returns are returned.

SELECT *
FROM X_TABLE_IX
WHERE VAL = 'A';

Now let's start a transaction and update VAL for almost all of the rows in the table:

BEGIN TRANSACTION

UPDATE X_TABLE_IX
SET VAL = 'A'
WHERE ID <> 1;

Here is part of the query plan for that:

Circled in red is the update to the nonclustered index. Circled in blue is the update to the clustered index, which is essentially the table's data. Even though the transaction has not been committed we see that the data and the index is updated in part of the query's execution. Note that you will not always see this in a plan depending on the size of data involved along with possibly other factors.

With the transaction still not committed, let's revisit the SELECT query from above.

SELECT *
FROM X_TABLE_IX
WHERE VAL = 'A';

The query optimizer is still able to use the index and this time it estimates that 999999 rows will be returned. Executing the query returns the expected result.

That was a simple demo but hopefully it cleared things up a bit.

As an aside, I am aware of a few cases in which it could be argued that an index is not immediately updated. This is done for performance reasons and the end user should not be able to see inconsistent data. For example, sometimes deletes will not be fully applied to an index in SQL Server. A background process runs and eventually cleans up the data. You can read about ghost records if you're curious.


TL:DR, there is a good reason, you are not finding any good solutions for what you want.  It does not seem possible, within the constraints you have.

At first glance getting details from the existing stored procedures in Programmatically Monitor Replication putting them together in a table (temp or permanent) to use for tracking and reporting seems like a great idea.  But when you start googling around you don't find anything as simple as you imagined it would be.

The two top solutions google finds involve INSERT-EXEC or OPENQUERY & OPENROWSET, but they have issues and ultimately most googling leads to How to Share Data between Stored Procedures, by Erland Sommarskog

  INSERT-EXEC

  It's a method that is seemingly very appealing, because it's very simple to use and understand... It Can't Nest

  Msg 8164, Level 16, State 1, Procedure BigSalesByStore, Line 8

  An INSERT EXEC statement cannot be nested.

and

  OPENQUERY and its cousin OPENROWSET

  Moreover, it is not aimed at improving performance. It may save you from rewriting your stored procedure, but most likely you will have to put in more work overall – and in the end you get a poorer solution. While I'm not enthusiastic over INSERT-EXEC, it is still a far better choice than OPENQUERY.

The first thing I tried that should have been a really good start.

IF OBJECT_ID('tempdb..#SP_RMHP') IS NOT NULL DROP TABLE #SP_RMHP
GO

Create Table #SP_RMHP(
    publisher SYSNAME
    , distribution_db SYSNAME
    , status INT
    , warning INT
    , publicationcount INT
    , returnstamp CHAR (16)
    )
INSERT #SP_RMHP
  EXEC distribution.dbo.sp_replmonitorhelppublisher

Select * from #SP_RMHP

drop table #SP_RMHP

The problem is that, sometimes you get the error An INSERT EXEC statement cannot be nested. and sometimes you don't.  In SQL 2017 I almost always get it.  In SQL 2014 sometimes I don't, sometimes I do.  Very frustrating and not a path to solution.

So now what?

Focus on pulling data directly from the distribution database tables.

Data supporting the replication process is kept in tables on 4 different database. MSN details are here

msdb Database
Distribution Database
Publication Database
Subscription Database

In one of my replication instances I do not have access to the Subscription servers, so I focused on just the first 3.

The table msdb.sysreplicationalerts has some errors, but everyone I saw was also displayed in the SQL logs, I am looking for evidence of broken replication when there are no errors reported in the SQL logs. So not what I need, but might be helpful to others.

select top (100) * from msdb..sysreplicationalerts

I looked through all of the tables, examined the column descriptions and looked at the data durring uptime.  I found a few potential source, so I set the subscription database offline, in theory this would duplicate an issue with the subscriber that did not directly impact replication or the publication.

With the subscriber database offline (for hours during this testing)

The replication monitor showed green, no Latency, and the Last Synchronization time was updated every couple of minutes to be current.

Clearly Replication is not "working" as the subscriber is offline :(

A possible candidate is 'MSreplication_monitordata' (distribution database), but essentially shows the same thing as the replication monitor, I did not dig deeper, but it is probably the source for Last Synchronization time in the replication monitor

.

Select last_distsync
, DATEDIFF ( MINUTE , last_distsync , GETDATE() )  as 'MinDifflast_distsync'
, agentstoptime
, time_stamp
, DATEDIFF ( MINUTE , time_stamp, GETDATE() )  as 'MinDifftime_stamp'
, GETDATE() as 'GETDATE'
, agent_id --may want this for reporting
, agent_name --may want this for reporting
--, *
from MSreplication_monitordata
where agent_type = 3 --3=Distribution Agent

.

The only other thing that looked promising is the table MSsubscriptions on the publication database.  There are some sequence numbers there that differ when the subscriber is offline, unfortunately they still differ when you bring the subscriber database back online.  Looking at one of live publication replications, there are 100's that differ at any given time.

.

select subscription_seqno
, publisher_seqno
, *
from MSsubscriptions
where subscription_seqno <> publisher_seqno

Now I am going to look into Canary tables as discussed by Kendra Little in this article This is problematic, because it requires modification of databases (add the table) with can be problematic if you are responsible for monitoring, but prohibited from altering the user database.


Advantages/Disadvantages of using a multicolumn clustering index

A narrow, static, unique clustering key is better in situations like these:

The table is referenced by another table (because the referencing table needs to include the primary key which is usually the same as the clustering key)
The table has many non-clustered indexes (because non-clustered indexes include the clustering key)
No other field or combination of fields is guaranteed to be unique
Queries against the table usually retrieve one row and use a surrogate key to do so (e.g. because the query comes from an application that has learned the surrogate key, not a user who is searching by natural key)

A multi-column clustering key is better in situations like these:

No other table references this one (e.g. a fact table in a data warehouse)
Queries against this table usually refer to the natural key, or part of it, and not the surrogate key (e.g. queries that scan for a date range)
There are zero, or few, non-clustered indexes on this table (including a wide clustering key in these indexes would make them very large)
A unique, natural key exists

https://www.toadworld.com/platforms/sql-server/w/wiki/9547.data-warehousing-indexing-considerations


Compatibility of queries written in SQL Server 2012 with SQL Server 2016

SQL Server is always backwards compatible, with the exception of items listed on the deprecated features list.

The query optimizer has changes that may result in performance increases, and in some circumstances, performance decreases.

Microsoft's Data Migration Assistant, provides a way to find deprecated functionality in use by your database.

  The Data Migration Assistant helps discover issues that can affect an upgrade to an on-premises SQL Server. These are described as compatibility issues and are organized in the following categories:

  Breaking changes
  Behavior changes
  Deprecated features

Microsoft also provides this list of breaking changes to database engine features.

Ensure you test the upgrade first in a non-production environment.

The following script can be used to create two Extended Events sessions that log the use of deprecated features and final-support features:

IF EXISTS (
    SELECT 1
    FROM sys.dm_xe_sessions xes
    WHERE xes.name = 'DeprecationFinalSupportEvents'
    )
BEGIN
    ALTER EVENT SESSION DeprecationFinalSupportEvents ON SERVER STATE = STOP;
    DROP EVENT SESSION DeprecationFinalSupportEvents ON SERVER;
END
IF EXISTS (
    SELECT 1
    FROM sys.dm_xe_sessions xes
    WHERE xes.name = 'DeprecationAnnouncmentEvents'
    )
BEGIN
    ALTER EVENT SESSION DeprecationAnnouncmentEvents ON SERVER STATE = STOP;
    DROP EVENT SESSION DeprecationAnnouncmentEvents ON SERVER;
END
GO

DECLARE @LogPath VARCHAR(260);
DECLARE @FinalSupportPathETL VARCHAR(260);
DECLARE @FinalSupportPathMTA VARCHAR(260);
DECLARE @DepPathETL VARCHAR(260);
DECLARE @DepPathMTA VARCHAR(260);

SELECT @LogPath = REVERSE(SUBSTRING(REVERSE(path)
    , CHARINDEX('\', REVERSE(path)) + 1, LEN(path) - CHARINDEX('\', REVERSE(path)) + 1))
FROM sys.traces;

SET @FinalSupportPathETL = @LogPath + '\DeprecationFinalSupportEvents.etl';
SET @FinalSupportPathMTA = @LogPath + '\DeprecationFinalSupportEvents.mta';
SET @DepPathETL = @LogPath + 'DeprecationAnnouncmentEvents.etl';
SET @DepPathMTA = @LogPath + 'DeprecationAnnouncmentEvents.mta';

DECLARE @cmd NVARCHAR(MAX);

SET @cmd = '
CREATE EVENT SESSION DeprecationFinalSupportEvents ON SERVER
ADD EVENT sqlserver.deprecation_final_support (
    ACTION  (
        sqlserver.database_id
        , sqlserver.sql_text
        , sqlserver.session_id
        , sqlserver.tsql_stack
    )
)
ADD TARGET package0.asynchronous_file_target (
    SET FILENAME = ''' + @FinalSupportPathETL + '''
        , METADATAFILE = ''' + @FinalSupportPathMTA + '''
);

ALTER EVENT SESSION DeprecationFinalSupportEvents ON SERVER
STATE = START;
';
PRINT @cmd;
EXEC (@cmd);

SET @cmd = '
CREATE EVENT SESSION DeprecationAnnouncmentEvents ON SERVER
ADD EVENT sqlserver.deprecation_announcement
(
    ACTION (
        sqlserver.database_id
        , sqlserver.sql_text
        , sqlserver.session_id
        , sqlserver.tsql_stack
        , sqlserver.username
        , sqlserver.client_hostname
        , sqlserver.database_name
        )
)
ADD TARGET package0.asynchronous_file_target (
    SET FILENAME = ''' + @DepPathETL + '''
        , METADATAFILE = ''' + @DepPathMTA + '''
);

ALTER EVENT SESSION DeprecationAnnouncmentEvents ON SERVER
STATE = START;
';
PRINT @cmd;
EXEC (@cmd);
GO


SSIS Blocking itself with IsolationLevel read Uncommited and TransactionOption supported

to check how many transactions you have open

select @@opentran

if all you want to do is not to be affected by blocking - and you are aware of the possible side effects - dirty reads -

you can either
before the select add this line:

set transaction isolation level read uncommitted

or within your select
you can use the hint WITH (NOLOCK), example:

select a,b,c, from mytable with (nolock)

instead of sp_who
please run the following code, and post the results here:

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT es.session_id AS session_id
,COALESCE(es.original_login_name, 'No Info') AS login_name
,COALESCE(es.host_name,'No Info') AS hostname
,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch
,es.status
,COALESCE(er.blocking_session_id,0) AS blocked_by
,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype
,COALESCE(er.wait_time,0) AS waittime
,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype
,COALESCE(er.wait_resource,'') AS waitresource
,coalesce(db_name(er.database_id),'No Info') as dbid
,COALESCE(er.command,'AWAITING COMMAND') AS cmd
,sql_text=st.text
,transaction_isolation =
    CASE es.transaction_isolation_level
        WHEN 0 THEN 'Unspecified'
        WHEN 1 THEN 'Read Uncommitted'
        WHEN 2 THEN 'Read Committed'
        WHEN 3 THEN 'Repeatable'
        WHEN 4 THEN 'Serializable'
        WHEN 5 THEN 'Snapshot'
    END
,COALESCE(es.cpu_time,0)
    + COALESCE(er.cpu_time,0) AS cpu
,COALESCE(es.reads,0)
    + COALESCE(es.writes,0)
    + COALESCE(er.reads,0)
    + COALESCE(er.writes,0) AS physical_io
,COALESCE(er.open_transaction_count,-1) AS open_tran
,COALESCE(es.program_name,'') AS program_name
,es.login_time
FROM sys.dm_exec_sessions es
    LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
    LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id
    LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid
    LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id
    LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address
    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st

where es.is_user_process = 1
  and es.session_id <> @@spid

UNION

SELECT es.session_id AS session_id
,COALESCE(es.original_login_name, 'No Info') AS login_name
,COALESCE(es.host_name,'No Info') AS hostname
,COALESCE(es.last_request_end_time,es.last_request_start_time) AS last_batch
,es.status
,COALESCE(er.blocking_session_id,0) AS blocked_by
,COALESCE(er.wait_type,'MISCELLANEOUS') AS waittype
,COALESCE(er.wait_time,0) AS waittime
,COALESCE(er.last_wait_type,'MISCELLANEOUS') AS lastwaittype
,COALESCE(er.wait_resource,'') AS waitresource
,coalesce(db_name(er.database_id),'No Info') as dbid
,COALESCE(er.command,'AWAITING COMMAND') AS cmd
,sql_text=st.text
,transaction_isolation =
    CASE es.transaction_isolation_level
        WHEN 0 THEN 'Unspecified'
        WHEN 1 THEN 'Read Uncommitted'
        WHEN 2 THEN 'Read Committed'
        WHEN 3 THEN 'Repeatable'
        WHEN 4 THEN 'Serializable'
        WHEN 5 THEN 'Snapshot'
    END
,COALESCE(es.cpu_time,0)
    + COALESCE(er.cpu_time,0) AS cpu
,COALESCE(es.reads,0)
    + COALESCE(es.writes,0)
    + COALESCE(er.reads,0)
    + COALESCE(er.writes,0) AS physical_io
,COALESCE(er.open_transaction_count,-1) AS open_tran
,COALESCE(es.program_name,'') AS program_name
,es.login_time
FROM sys.dm_exec_sessions es
    INNER JOIN sys.dm_exec_requests ec2 ON es.session_id = ec2.blocking_session_id
    LEFT OUTER JOIN sys.dm_exec_connections ec ON es.session_id = ec.session_id
    LEFT OUTER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id
    LEFT OUTER JOIN sys.server_principals sp ON es.security_id = sp.sid
    LEFT OUTER JOIN sys.dm_os_tasks ota ON es.session_id = ota.session_id
    LEFT OUTER JOIN sys.dm_os_threads oth ON ota.worker_address = oth.worker_address
    CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) AS st
where es.is_user_process = 1
  and es.session_id <> @@spid

this will show you what is actually causing the blocking.
this code comes from here:

how to find the T-SQL of a sleeping session that is blocking some other process?


Was the database in FULL recovery but no backups being taken previously? Paul Randall's article Running Out of Transaction Log Space covers the other likely scenarios.

The DMV query below will tell you why the log cannot be truncated. If you're unsure how to interpret the output, add the response to your question.

SELECT
    db.[name] AS [Database Name]
  , db.recovery_model_desc AS [Recovery Model]
  , db.log_reuse_wait_desc AS [Log Reuse Wait Description]
  , ls.cntr_value AS [Log Size (KB)]
  , lu.cntr_value AS [Log Used (KB)]
  , CAST(CAST(lu.cntr_value AS FLOAT)
    / CAST(ls.cntr_value AS FLOAT) AS DECIMAL(18,2)) * 100 AS [Log Used %]
FROM
    sys.databases AS db
INNER JOIN
    sys.dm_os_performance_counters AS lu
ON  db.name = lu.instance_name
INNER JOIN
    sys.dm_os_performance_counters AS ls
ON  db.name = ls.instance_name
WHERE
    lu.counter_name LIKE N'Log File(s) Used Size (KB)%'
AND ls.counter_name LIKE N'Log File(s) Size (KB)%'
AND ls.cntr_value > 0
OPTION (RECOMPILE);


Query to find users with passwords same as their username in Oracle

I don't think this is possible for current versions of Oracle. What I understand happens is that Oracle generates an instance-specific salt and adds this to the passwords being hashed using the SHA1 algorithm, the result of which is stored as the hashed password. See for example http://marcel.vandewaters.nl/oracle/security/password-hashes for information about this algorithm.

This means that password hashes are installation specific, and I don't think it's possible to generate the expected hash value for a given username/password combination.

What you need to do is as @elfcheg suggests to add a password_verify_function on all profiles, or to a single profile, and assign this profile to all users.


Increased disk consumption after upgrading to MySQL 8

This is most likely because of a bug in the version of mysql you are using. The issue is that mysql does not release its file handle on temporary files until you restart mysqld. This is a confirmed bug and you can read others experience with it here.

Upgrading past 8.0.15 should have resolved the issue.


Running pt-table-checksum with replicate-do-db option on

But do I need to write the resulting checksum table into one of these dbs with --replicate=db1.checksum?

Yes, you need. Otherwise it would not be replicated.

  And after using the tool, should I be running USE db1 before dropping the checksum table on master so that this is logged and sent to slave?

Yes.


As far as I know you cannot disable SHOW TABLES, but if you have only assigned permissions to tables that the user should be able to access, I don't see how there is a security issue.  A user cannot list tables to which he has no permissions.

root@beren [~]# mysql -u root -p
Enter password:

<-- SNIP -->

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

Database changed
mysql> show tables;
+---------------+
| Tables_in_foo |
+---------------+
| bar           |
| baz           |
+---------------+
2 rows in set (0.00 sec)

mysql> create user 'quux'@'localhost' identified by '*******';
Query OK, 0 rows affected (0.00 sec)

mysql> grant select on table foo.bar to 'quux'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

mysql> exit
Bye
root@beren [~]# mysql -u quux -p foo
Enter password:

<-- SNIP -->

mysql> show tables;
+---------------+
| Tables_in_foo |
+---------------+
| bar           |
+---------------+
1 row in set (0.00 sec)

mysql>


SQL Server - TRY/CATCH does not work in certain cases

How can I write guarded code that invokes the CATCH code block in every expected* case (without doing funky junk like wrapping everything in dynamic SQL)?

You can't, unfortunately.

SQL Server MVP Erland Sommarskog has a series of brief* articles on the subject of error handling in SQL Server that starts here: Error and Transaction Handling in SQL Server

The issue you're running into with the Agent job is that it calls an extended stored procedure, which is what raises the error.  In particular, that error comes from master.dbo.xp_sqlagent_notify.

Erland covers that issue here:

  SQL Server still ships with quite a few extended stored procedures, whereof some are documented and others are only intended for the tools that ship with SQL Server. With regards to error handling, all bets are off with these guys. There is no consistent behaviour, and about every XP has its own twist.

I know you mentioned you're not interested bespoke solutions that aren't broadly applicable, but I think it's worth mentioning that, when dealing with Microsoft-shipped stored procedures, one solution to this TRY / CATCH issue is to always capture the return code of the procedure.  If it's non-zero, you can manually raise an error to trigger the CATCH block (thanks Dan Guzman).

* brief, ha!


I'm seeing inconsisntecies in table data after exporting from one server and importing to another, why is this?

From the symptoms, it seems that you are using InnoDB engine for your tables. If so, it is normal not to have accurate number of rows shown by clients (Like HeidiSQL or PHPMyAdmin).

It is also worth it to mention that SHOW TABLE STATUS LIKE 'table_name' does not provide accurate row count for InnoDB tables.

InnoDB estimates the number of rows in the table. If you want the exact count, you should run SELECT COUNT(*), as you just did.

MyISAM, on the other hand, has the information of its number of rows saved and 'always' up to date.

In regards to physical file size, there are many factors that leads to tables that have same date to have different file size on disks. Some are related to the operating system itself, and the file system used; some are related to the configs of MySQL; and some are related to the fragmentation of the data when doing deletes and updates. In short, this difference is very normal.


Select columns dynamically from sql server table

Use dynamic SQL. But join to sys.columns to avoid SQL Injection attacks.

DECLARE @qry NVARCHAR(MAX) =
'SELECT ' + STUFF((
    SELECT ', ' + QUOTENAME(c.name)
    FROM Report.InfoColumnOrder r
    JOIN sys.columns c ON c.name = r.ColumnName AND c.object_id = object_id('dbo.Info')
    WHERE u.UserId = @user
    ORDER BY r.SN
    FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,2,'') +
 ' FROM dbo.Info'
;
EXEC sp_executesql @qry, N'@user int', @user = @userid;

Or something like that - I'm writing this on my phone...


How to efficiently extract data from a csv file and save it in mysql

If there are several rows with a consistent number of columns in the CSV, then

CREATE TABLE with lots of columns
LOAD DATE into it.
Do "unpivot" code to rearrange it into the 'real' tables you want.

If there is only one row in your CSV, then write code in your favorite programming language to break it apart.

Not sure what to do if you have an inconsistent number of columns.

You can use INSERT IGNORE and/or IODKU` to deal with duplicates.


Incrementally Updated Image File Backup Multiple Locations

No, it is not possible to have this single backup command do the level 0 to a different location than the level 1.  It is possible to change the script to determine which files don't have level zero backups and do a separate backup just for those datafiles before running the existing command to the incremental location.


Recommendation for storage of series of time series

I can suggest Akumuli.
It's a time-series database that supports compression and high-throughput data ingestion. With 25KHz measurement frequency and 20 engines, you will need to write 500K data points per second in the worst case. Akumuli can handle an order of magnitude larger throughput (largest throughput ever recorded is around 16M data points per second).

Also, because of compression, the database needs only around 3-9 bytes per data point. Each data point is a timestamp with nanosecond precision + 64-bit floating point value. There is an automatic data retention that deletes old data only if there is not enough disk space to store the new data.

You can store data from each engine in the same time-series or you can create new time-series per burst.

The real time-series database can be a big win because you won't need to use all these fancy tricks. There is a downsides of cause. E.g. there is no clustering and backfill.

Disclaimer: I'm the author so I'm a bit biased.


Information about youngest child with inner subquery

You should use minimum age in the select clause so that group by is meaningful. Also, why do you need the motherId and fatherId to be null in nested subquery? If you do not add these conditions then you do not need a nested subquery:

select parents.name as name, min(kids.age) as age
from
people kids
inner JOIN
people  parents
ON (parents.id = kids.motherId OR parents.id = kids.fatherId)
group by parents.id

By the way, does the query you posted really works? In PostgreSQL 9.3.23 I am getting an error:

  column "parents.name" must appear in the GROUP BY clause or be used in
  an aggregate function


How to set a trigger on delete multiple parent and child rows?

I don't see the need to use a trigger. You could use two DELETEs:

DELETE C
FROM Table_C C
INNER JOIN Table_B B
    ON C.B_ID = B.B_ID
WHERE B.A_ID = 1;

DELETE FROM Table_B
WHERE A_Id = 1;

If you want to use a trigger, you can use this code inside:

DELETE C
FROM Table_C C
INNER JOIN DELETED B
    ON C.B_ID = B.B_ID;

Justa as a note, remember that if you have a foreign key in that table, you could configure it ON DELETE CASCADE (but be careful with your deletes)


Advantages of "middle" tables in one-to-many relationships

There are very few scenarios in which you would probably benefit from your second design:

One is if there is a sub-typing scenario where only certain a certain sub-type had the relationship (and the super-type doesn't) and the sub-type has other additional fields, besides the foreign key.

Another might be where the existence and value of the foreign key needs to be controlled for security reasons at a different level of granularity than the rest of the entity containing the foreign key.  Most DBMSs can handle security per table.  Most don't handle security per column.


Using a foreign key on multiple fields in one table

You can't implement this as a single constraint; you'll need to create two:

ALTER TABLE dbo.B ADD CONSTRAINT FK_HomeTeam
  FOREIGN KEY (homeTeam) REFERENCES dbo.A(teamName);

ALTER TABLE dbo.B ADD CONSTRAINT FK_AwayTeam
  FOREIGN KEY (awayTeam) REFERENCES dbo.A(teamName);

As I alluded to in my comment, it would be much more efficient to store TeamID in the two columns in dbo.B - this way you are repeating two INTs in every row, instead of bulky strings like "Billy Bob's Bait and Tackle Mudhens" or "South Cincinnati Rebel Rockers."

It also makes it much easier to change a team's name (unless the point is to record their historical name at the time the game was played, but even then I would store names over time in some kind of history table, not with every game).


SQL Server: Apply Custom Server Role afterwards for already existing databases

I suspect that you are not actually creating a SERVER ROLE but in fact are creating a DATABASE ROLE in the model database.  I looked under server roles for a bit and could find nothing that would grant read access to a database (old or new). I can see ALTER ANY DATABASE, CREATE ANY DATABASE and VIEW ANY DATABASE but nothing about read/write of the tables.

If what I suspect is correct and you are creating a role in the model database then it would have exactly the effect you are discussing.  You would see the role show up in all new databases but not the old ones.  Unfortunately the only way around this is to create the role in each of the existing databases.  Fortunately this is fairly easy.

First thing would be to generate a script that creates the role either by using a scripting tool, creating it in the GUI and hitting the script button, or using the generate scripts option (right click on the database).

Once you have the script for the role you can either manually run it on each of your existing databases (probably ok if you only have <50 or so) or you can generate some dynamic SQL to run the script for you.


Figure out the problem. Had to get elevated rights and then install it. Thanks to everyone that helped.


How to model an entity type that can have different sets of attributes?

According to your description of the business environment under consideration, there exists a supertype-subtype structure that encompasses Item —the supertype— and each of its Categories, i.e., Car, Boat and Plane (along with two more that were not made known) —the subtypes—.
I will detail below the method I would employ to manage said scenario.
Business rules
In order to start delineating the relevant conceptual schema, some of the most important business rules determined so far (restricting the analysis to the three disclosed Categories only, to keep things as brief as possible) can be formulated as follows:

A User owns zero-one-or-many Items.
An Item is owned by exactly-one User at a specific instant.
An Item may be owned by one-to-many Users at distinct points in time.
An Item is classified by exactly-one Category.
An Item is, at all times,

either a Car
or a Boat
or a Plane.

Illustrative IDEF1X diagram
Figure 1 displays an IDEF1X1 diagram that I created to group the previous formulations along with other business rules that appear pertinent:

Supertype
On the one hand, Item, the supertype, presents the properties† or attributes that are common to all the Categories, i.e.,

CategoryCode —specified as a FOREIGN KEY (FK) that references Category.CategoryCode and functions as a subtype discriminator, i.e., it indicates the exact Category of subtype with which a given Item must be connected—,
OwnerId —distinguished as a FK that points to User.UserId, but I assigned it a role name2 in order to reflect its special implications more accurately—,
Foo,
Bar,
Baz and
CreatedDateTime.

Subtypes
On the other hand, the properties‡ that pertain to every particular Category, i.e.,

Qux and Corge;
Grault, Garply and Plugh;
Xyzzy, Thud, Wibble and Flob;

are shown in the corresponding subtype box.
Identifiers
Then, the Item.ItemId PRIMARY KEY (PK) has migrated3 to the subtypes with different role names, i.e.,

CarId,
BoatId and
PlaneId.

Mutually exclusive associations
As depicted, there is an association or relationship with a one-to-one (1:1) cardinality ratio between (a) each supertype occurrence and (b) its complementary subtype instance.
The exclusive subtype symbol portrays the fact that the subtypes are mutually exclusive, i.e., a concrete Item occurrence can be supplemented by a single subtype instance only: either one Car, or one Plane, or one Boat (never by zero or less, nor by two or more).
†, ‡ I employed classic placeholder names to entitle some of the entity type properties, as their actual denominations were not supplied in the question.
Expository logical-level layout
Consequently, in order to discuss an expository logical design, I derived the following SQL-DDL statements based on the IDEF1X diagram displayed and described above:
-- You should determine which are the most fitting
-- data types and sizes for all your table columns
-- depending on your business context characteristics.

-- Also, you should make accurate tests to define the
-- most convenient INDEX strategies based on the exact
-- data manipulation tendencies of your business context.

-- As one would expect, you are free to utilize
-- your preferred (or required) naming conventions.

CREATE TABLE UserProfile (
    UserId          INT      NOT NULL,
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    BirthDate       DATE     NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    Username        CHAR(20) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT UserProfile_PK  PRIMARY KEY (UserId),
    CONSTRAINT UserProfile_AK1 UNIQUE ( -- Composite ALTERNATE KEY.
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    ),
    CONSTRAINT UserProfile_AK2 UNIQUE (Username) -- ALTERNATE KEY.
);

CREATE TABLE Category (
    CategoryCode     CHAR(1)  NOT NULL, -- Meant to contain meaningful, short and stable values, e.g.; 'C' for 'Car'; 'B' for 'Boat'; 'P' for 'Plane'.
    Name             CHAR(30) NOT NULL,
    --
    CONSTRAINT Category_PK PRIMARY KEY (CategoryCode),
    CONSTRAINT Category_AK UNIQUE      (Name) -- ALTERNATE KEY.
);

CREATE TABLE Item ( -- Stands for the supertype.
    ItemId           INT      NOT NULL,
    OwnerId          INT      NOT NULL,
    CategoryCode     CHAR(1)  NOT NULL, -- Denotes the subtype discriminator.
    Foo              CHAR(30) NOT NULL,
    Bar              CHAR(40) NOT NULL,
    Baz              CHAR(55) NOT NULL,
    CreatedDateTime  DATETIME NOT NULL,
    --
    CONSTRAINT Item_PK             PRIMARY KEY (ItemId),
    CONSTRAINT Item_to_Category_FK FOREIGN KEY (CategoryCode)
        REFERENCES Category    (CategoryCode),
    CONSTRAINT Item_to_User_FK     FOREIGN KEY (OwnerId)
        REFERENCES UserProfile (UserId)
);

CREATE TABLE Car ( -- Represents one of the subtypes.
    CarId INT          NOT NULL, -- Must be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
    Qux   DATE         NOT NULL,
    Corge DECIMAL(5,2) NOT NULL,
    --
    CONSTRAINT Car_PK         PRIMARY KEY (CarId),
    CONSTRAINT Car_to_Item_FK FOREIGN KEY (CarId)
        REFERENCES Item (ItemId),
    CONSTRAINT ValidQux_CK    CHECK       (Qux >= '1990-01-01')
);

CREATE TABLE Boat ( -- Stands for one of the subtypes.
    BoatId INT      NOT NULL, -- Must be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
    Grault SMALLINT NOT NULL,
    Garply DATETIME NOT NULL,
    Plugh  CHAR(63) NOT NULL,
    --
    CONSTRAINT Boat_PK         PRIMARY KEY (BoatId),
    CONSTRAINT Boat_to_Item_FK FOREIGN KEY (BoatId)
        REFERENCES Item (ItemId),
    CONSTRAINT ValidGrault_CK  CHECK       (Grault <= 10000)
);

CREATE TABLE Plane ( -- Denotes one of the subtypes.
    PlaneId INT      NOT NULL, -- Must be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
    Xyzzy   BIGINT   NOT NULL,
    Thud    TEXT     NOT NULL,
    Wibble  CHAR(20) NOT NULL,
    Flob    BIT(1)   NOT NULL,
    --
    CONSTRAINT Plane_PK         PRIMARY KEY (PlaneId),
    CONSTRAINT Plane_to_Item_PK FOREIGN KEY (PlaneId)
        REFERENCES Item (ItemId),
    CONSTRAINT ValidXyzzy_CK    CHECK       (Xyzzy <= 3258594758)
);

This has been tested in this db<>fiddle running on MySQL 8.0.
As demonstrated, the superentity type and each of the subentity types are represented by the corresponding base table.
The columns CarId, BoatId and PlaneId, constrained as the PKs of the appropriate tables, help in representing the conceptual-level one-to-one association by way of FK constraints§ that point to the ItemId column, which is constrained as the PK of the Item table. This signifies that, in an actual “pair”, both the supertype and the subtype rows are identified by the same PK value; thus, it is more than opportune to mention that

(a) attaching an extra column to hold system-controlled surrogate values‖ to (b) the tables standing for the subtypes is (c) entirely superfluous.

§ In order to prevent problems and errors concerning (particularly FOREIGN) KEY constraint definitions —situation you referred to in comments—, it is very important to take into account the existence-dependency that takes place among the different tables at hand, as exemplified in the declaration order of the tables in the expository DDL structure, which I supplied in this db<>fiddle too.
‖ E.g., appending an additional column with the AUTO_INCREMENT property to a table of a database built on MySQL.
Integrity and consistency considerations
It is critical to point out that, in your business environment, you have to (1) ensure that each “supertype” row is at all times complemented by its corresponding “subtype” counterpart, and, in turn, (2) guarantee that said “subtype” row is compatible with the value contained in the “discriminator” column of the “supertype” row.
It would be very elegant to enforce such circumstances in a declarative manner but, unfortunately, none of the major SQL platforms has provided the proper mechanisms to do so, as far as I know. Therefore, resorting to procedural code within ACID TRANSACTIONS it is quite convenient so that these conditions are always met in your database. Other option would be employing TRIGGERS, but they tend to make things untidy, so to speak.
Declaring useful views
Having a logical design like the one explained above, it would be very practical to create one or more views, i.e., derived tables that comprise columns that belong to two or more of the relevant base tables. In this way, you can, e.g., SELECT directly FROM those views without having to write all the JOINs every time you have to retrieve “combined” information.
Sample data
In this respect, let us say that the base tables are “populated” with the sample data shown below:
--
INSERT INTO UserProfile
    (UserId, FirstName, LastName, BirthDate, GenderCode, Username, CreatedDateTime)
VALUES
    (1, 'Edgar', 'Codd', '1923-08-19', 'M', 'ted.codd', CURDATE()),
    (2, 'Michelangelo', 'Buonarroti', '1475-03-06', 'M', 'michelangelo', CURDATE()),
    (3, 'Diego', 'Velázquez', '1599-06-06', 'M', 'd.velazquez', CURDATE());

INSERT INTO Category
    (CategoryCode, Name)
VALUES
    ('C', 'Car'), ('B', 'Boat'), ('P', 'Plane');

-- 1. ‘Full’ Car INSERTion

-- 1.1
INSERT INTO Item
    (ItemId, OwnerId, CategoryCode, Foo, Bar, Baz, CreatedDateTime)
VALUES
    (1, 1, 'C', 'Motorway', 'Tire', 'Chauffeur', CURDATE());

 -- 1.2
INSERT INTO Car
    (CarId, Qux, Corge)
VALUES
    (1, '1999-06-11',  999.99);

-- 2. ‘Full’ Boat INSERTion

-- 2.1
INSERT INTO Item
    (ItemId, OwnerId, CategoryCode, Foo, Bar, Baz, CreatedDateTime)
VALUES
    (2, 2, 'B', 'Ocean', 'Anchor', 'Sailor', CURDATE());

-- 2.2
INSERT INTO Boat
    (BoatId, Grault, Garply, Plugh)
VALUES
    (2, 10000, '2016-03-09 07:32:04.000', 'So far so good.');

-- 3 ‘Full’ Plane INSERTion

-- 3.1
INSERT INTO Item
    (ItemId, OwnerId, CategoryCode, Foo, Bar, Baz, CreatedDateTime)
VALUES
    (3, 3, 'P', 'Sky', 'Wing', 'Aviator', CURDATE());

-- 3.2

INSERT INTO Plane
    (PlaneId, Xyzzy, Thud, Wibble, Flob)
VALUES
    (3, 3258594758, 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut sollicitudin pharetra sem id elementum. Sed tempor hendrerit orci. Ut scelerisque pretium diam, eu sodales ante sagittis ut. Phasellus id nunc commodo, sagittis urna vitae, auctor ex. Duis elit tellus, pharetra sed ipsum sit amet, bibendum dapibus mauris. Morbi condimentum laoreet justo, quis auctor leo rutrum eu. Sed id nibh non leo sodales pulvinar. Nam ornare ipsum nunc, eget molestie nulla ultrices vel. Curabitur fermentum nisl quis lorem aliquam pretium aliquam at mauris. In vestibulum, tellus et pharetra sollicitudin, mi lacus consectetur dolor, id volutpat nulla eros a mauris. ', 'Here we go!', TRUE);

--

Then, an advantageous view is one that gathers columns from Item, Car and UserProfile:
--

CREATE VIEW CarAndOwner AS
    SELECT C.CarId,
           I.Foo,
           I.Bar,
           I.Baz,
           C.Qux,
           C.Corge,
           U.FirstName AS OwnerFirstName,
           U.LastName  AS OwnerLastName
        FROM Item I
        JOIN Car C
          ON C.CarId = I.ItemId
        JOIN UserProfile U
          ON U.UserId = I.OwnerId;

--

Naturally, a similar approach can be followed so that you can as well SELECT the “full” Boat and Plane information straight FROM one single table (a derived one, in these cases).
After that —if you do not mind about the presence of NULL marks in result sets— with the following VIEW definition, you can, e.g., “collect” columns from the tables Item, Car, Boat, Plane and UserProfile:
--

CREATE VIEW FullItemAndOwner AS
    SELECT I.ItemId,
           I.Foo, -- Common to all Categories.
           I.Bar, -- Common to all Categories.
           I.Baz, -- Common to all Categories.
          IC.Name      AS Category,
           C.Qux,    -- Applies to Cars only.
           C.Corge,  -- Applies to Cars only.
           --
           B.Grault, -- Applies to Boats only.
           B.Garply, -- Applies to Boats only.
           B.Plugh,  -- Applies to Boats only.
           --
           P.Xyzzy,  -- Applies to Planes only.
           P.Thud,   -- Applies to Planes only.
           P.Wibble, -- Applies to Planes only.
           P.Flob,   -- Applies to Planes only.
           U.FirstName AS OwnerFirstName,
           U.LastName  AS OwnerLastName
        FROM Item I
        JOIN Category IC
          ON I.CategoryCode = IC.CategoryCode
   LEFT JOIN Car C
          ON C.CarId = I.ItemId
   LEFT JOIN Boat B
          ON B.BoatId = I.ItemId
   LEFT JOIN Plane P
          ON P.PlaneId = I.ItemId
        JOIN UserProfile U
          ON U.UserId = I.OwnerId;

--

The code of the views here shown is only illustrative. Of course, doing some testing exercises and modifications might help to accelerate the (physical) execution of the queries at hand. In addition, you might need to remove or add columns to said views as the business needs dictate.
The sample data and all the view definitions are incorporated into this db<>fiddle so that they can be observed “in action”.
Data manipulation: Application program(s) code and column aliases
The usage of application program(s) code (if that is what you mean by “server-side specific code”) and column aliases are other significant points that you brought up in the next comments:

I did manage to workaround [a JOIN] issue with server-side specific code, but I really don't want to do that -AND- adding aliases to all columns might be "stressing".

Very well explained, thank you very much. However, as I suspected, I'll have to manipulate the result set when listing all data because of the similarities with some columns, since I don't want to use several aliases to keep the statement cleaner.

It is opportune to indicate that while using application program code is a very fitting resource to handle the presentation or graphical features —i.e., the external level of representation of a computerized information system— of data sets, it is paramount that you avoid carrying out data retrieval on a row-by-row basis to prevent execution speed issues. The objective should be to “fetch” the pertinent data sets in toto by means of the robust data manipulation instruments provided by the (precisely) set engine of the SQL platform so that you can optimize the behaviour of your system.
Furthermore, utilizing aliases to rename one or more columns within a certain scope may appear stressing but, personally, I see such resource as a very powerful tool that helps to (i) contextualize and (ii) disambiguate the meaning and intention ascribed to the concerning columns; hence, this is an aspect that should be thoroughly pondered with respect to the manipulation of the data of interest.
Similar scenarios
You might as well find of help this series of posts and this group of posts which contain my take on two other cases that include supertype-subtype associations with mutually exclusive subtypes.
I have also proposed a solution for a business environment involving a supertype-subtype cluster where the subtypes are not mutually exclusive in this (newer) answer.

Endnotes
1 Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in December 1993 by the U.S. National Institute of Standards and Technology (NIST). It is solidly based on (a) some of the theoretical works authored by the sole originator of the relational model, i.e., Dr. E. F. Codd; on (b) the entity-relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.
2 In IDEF1X, a role name is a distinctive label assigned to a FK property (or attribute) in order to express the meaning that it holds within the scope of its respective entity type.
3 The IDEF1X standard defines key migration as “The modeling process of placing the primary key of a parent or generic entity in its child or category entity as a foreign key”.


How does MySQL/InnoDB caching work internally when you perform two similar, but not identical, select requests?

Your question, "How MySQL select request works internally" is a very broad topic. However, I can more or less give a not-completely-accurate, but simple overview to "what happens when you select records 1 to 10 and then, sequentially, 1-15" regarding caching.

First thing you must understand is that, functionally, MySQL can be divided into 2 separate layer, the "frontend" or SQL layer, and the ENGINE layer, which communicate just by a low-level (remember I am simplifying things) row-based api. You can have InnoDB or other pluggable engines to store rows (let's focus on only that one). Caching can happen at SQL or Engine level:

SQL Layer

At SQL layer there are several caching mechanisms, let's focus on query results caching, known as Query Cache. It allows to allocate certain amount of space in memory to return query results without going to the engine. It is a very high level method of caching, has to be consistent with the actual data stored, and its implementation is very simplistic, which has lead to many practical problems:

It requires a global lock for writes, which kills all parallelism and creates high contention under medium to high load, even for low number of writes
It can slow down misses
Evictions can also be harmful, and all results are invalidated as soon as a table is written, even if results would not be affected
If the same results are returned, but the query is slightly different, those are considered as 2 different queries, storing the same queries twice
It does not support partitioned tables, and clustering solutions such as Galera do not support it
Not all queries are cachable (e.g. undeterministic queries), and there will be a finite number of space for them

In your case, having executed 2 different queries, the query cache would be useless, and you would have 2 misses, following by 2 separate inserts of the results (assuming they are cachable, etc.)

Many people recommend disabling the query cache and using the extra memory for other buffers. The exact impact would depend on your particular workload (you should monitor hits/misses/evictions and query latency). If you still need query caching, you can opt to have something like memcache, closer to the application layer. And even MySQL integrates a plugin to run memcache as a frontend for InnoDB, as part of the server.

Separating the caching, you can control yourself the TTL of the cached results. There are many other mechanisms that can handle that, such as ProxySQL or implementing your own on the application.

InnoDB

InnoDB implements it's own complex caching system for faster row retrieval inside the buffer pool (its page cache), a much lower-level cache. In general, InnoDB tries to avoid (overpass) the filesystem cache and implement its own row cache system. The default page size is 16KB (although that can be changed) and it has the same structure on disk than in memory. A page should be large enough to store 2 data rows or more (a row can be larger than 8K, for variable-size fields, but that is another story). When a page is accessed on disk, it gets copied to the buffer pool. In fact, due to some optimizations, in many cases, not only a single page gets read, but multiple contiguous ones to reduce IO operations.

So when you read the first 10 rows on an empty buffer pool, maybe only those pages are are loaded into memory with the rows you want and probably some more; and in many cases, the full extend and the next one. All of this is subject to tuning and depends on the kind of access patterns you have.

When you read again, you will probably find the same rows already in memory, and thus you have reduced the disk I/O. If you are missing some pages, it will require a (or several) disk IO operation. This is normally seen when you run the same query twice and get a faster response. Although row caching is only a part of the speed up- you also have thread caching, table caching, dictionary caching, index caching ...(at both SQL and engine level).

However, it can happen that by the time you have done the second query, the rows have been evicted- the eviction follow a relatively complex algorithm and it is also highly tunable, but it is -simplifying it- a LRU (Least recently used) implementation, which means that if the other, most recently accessed data gets into memory, it can evict rarely-used data.

Here you have a step-by-step summary of how InnoDB buffer pool works:

https://www.slideshare.net/ossez/percona-server-with-xtra-db/23

Other engines can do things differently, for example, MyISAM and TokuDB have both index caching, but they both relay more heavily on the filesystem cache for data.


Should pivot tables have more columns (many to many relationship)

There is no restrictions on data stored in the pivot table. The simplest case of two IDs establishes only qualitative relations between fridges and foods: this food is stored in this fridge. If you add the counter you just turn the qualitative relation into the quantitative one: this many units of this food is stored in this fridge.

On the opposite side you have to control the counters and remove any row having counter dropped to zero to avoid silly logic yes, we store zero units of this food in this fridge.


MySQL replication working fine, but still shows "Waiting for master to send event" at 280000 seconds

Don't you dare kill that process. Why ?

Run this query

SELECT COUNT(1) FROM information_schema.processlist WHERE user='system user';

The answer has to be two. Why ?

Standard MySQL Replication functions on 2 threads: 1) IO Thread, 2) SQL Thread. In my old post Does MySQL support replicating all databases?, I explain the symbiosis between the two threads.

When you run SHOW SLAVE STATUS\G, you will see three lines:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

This is the sign of a healthy replication process.If either Slave_IO_Running or Slave_IO_Running is No, then Replication is not running for one of six(6)reasons:

Somebody ran STOP SLAVE;, which turns off both the IO Thread and SQL Thread
Somebody ran STOP SLAVE IO_THREAD;, which turns off the IO Thread
Somebody ran STOP SLAVE SQL_THREAD;, which turns off the SQL Thread
There was a replication user authentication error, which turns off the IO Thread
There was a network error, which turns off the IO Thread
There was an SQL Error, which turns off the SQL Thread

When any of these cases show up, Seconds_Behind_Master is NULL.
What about the number 280000 ? That is simply the length of time (over 82 hours) that the IO Thread has been running. That's OK. It is not a ghost process. Replication requires an IO Thread to read binlog events from a Master's binary logs.

In conclusion, your IO Thread has been up for over 82 hours. Do not mess with replication.


Include fields from a table function in a select

The syntax you have is correct from SQL Server 2005 and up. You are using SQL Server 2008 so it should work just fine unless you have compatibility level for your database set to "SQL Server 2000 (80)".

Make sure you have compatibility level set to at least "SQL Server 2005 (90)".

Right-click on your database - Properties - Options


MySQL uses intersection instead compound index

The selectivity has changed with the added selections.So now the optimizer will think that an intersection of indexes is more efficient than just one index.Compound indexes have a cardinality that matches all their columns,it`s not separate for column so MySQL will try to reduce this selection with an index intersection.Find out which column is the most selective(has the least values returned) and rewrite it with this in mind.

You could turn off index merge with a SESSION option but this the bulldozer approach.


Exclude certain table data for all non public schemas (tenants)

Thanks to @a_horse_with_no_name's comment I figured out the correct wildcard syntax

--exclude-table-data='*.foo_bar'


Add full text index facility to SQL Server instance

When you install a clustered instance, the fulltext components are installed by default and it is not possible to de-select them. If you don't have fulltext installed, you're in big trouble. Are you sure the components are not there? What does SELECT SERVERPROPERTY('IsFulltextInstalled') return? If it returns false, I'm afraid you will have to reinstall this instance. I had the same thing happening to me once and there was no way other than reinstalling from scratch.