In MySQL, Is there a way to display all tables contained in a view

Update: You can use sql parsers for this here is a demo page for the one I tested http://107.170.101.241:8080/getTableColumn/

Here are other useful links: [1]
[2]

I personally found this interesting so here is what I have tried so far:

Basic idea, table names are being preceded by Keywords like Left join , From ,etc (thus same logic can be repeated for other types of joins as well) so what I am trying to do here is locate the keyword and find there position using locate function then using that position (which I got as output) in a substring command after adding some extra sum to the location which is equal to no of digits in keyword eg: 4 for FROM which is in turn is skipping the keyword itself from occurring in results and resulting in the table name followed by some extra followup characters,which i think can be replaced.

Thoughts:The catch here will be to know the length of each table name and then passing it as the variable to the last parameter of the substring function and then iterating the below query for each view may be using a batch script which will yield only the table name in turn as a result.

Note : This is just an work in progress and still needs further improvements.

SELECT TABLE_NAME as View_Name ,VIEW_DEFINITION, SUBSTR(VIEW_DEFINITION,LOCATE('from',VIEW_DEFINITION)+4,15) as Table_In_View
FROM INFORMATION_SCHEMA.VIEWS
WHERE locate('from',VIEW_DEFINITION)>0
UNION
SELECT TABLE_NAME as View_Name , VIEW_DEFINITION, SUBSTR(VIEW_DEFINITION,LOCATE('left join',VIEW_DEFINITION)+9,15)as Table_In_View
FROM INFORMATION_SCHEMA.VIEWS
WHERE locate('left join',VIEW_DEFINITION)>0;

Another updated format:

    SELECT TABLE_NAME as View_Name ,VIEW_DEFINITION, REPLACE(REPLACE(REPLACE(SUBSTR(VIEW_DEFINITION,LOCATE('from',VIEW_DEFINITION)+4,15), ',', "\n|"),
 ".", "\n"), "(", "") as Table_In_View
FROM INFORMATION_SCHEMA.VIEWS
WHERE locate('from',VIEW_DEFINITION)>0
UNION
SELECT TABLE_NAME as View_Name , VIEW_DEFINITION, REPLACE(REPLACE(REPLACE(SUBSTR(VIEW_DEFINITION,LOCATE('left join',VIEW_DEFINITION)+9,15), ',', "\n|"),
 ".", "\n"), "(", "")as Table_In_View
FROM INFORMATION_SCHEMA.VIEWS
WHERE locate('left join',VIEW_DEFINITION)>0 ;

To also show where it came from for Eg: join,from etc

SELECT TABLE_NAME as View_Name ,VIEW_DEFINITION, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTR(VIEW_DEFINITION,LOCATE('from',VIEW_DEFINITION),20), ',', "\n|"),
"from", "\n|from"), ".", "\n") , "left join", "\n|left join"), "(", "") ,"`","") as Table_In_View
FROM INFORMATION_SCHEMA.VIEWS
WHERE locate('from',VIEW_DEFINITION)>0
UNION
SELECT TABLE_NAME as View_Name , VIEW_DEFINITION, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTR(VIEW_DEFINITION,LOCATE('left join',VIEW_DEFINITION),20), ',', "\n|"),
"from", "\n|from"), ".", "\n") , "left join", "\n|left join"), "(", "") ,"`","")as Table_In_View
FROM INFORMATION_SCHEMA.VIEWS
WHERE locate('left join',VIEW_DEFINITION)>0 ;

Please check it here to see what I mean for now the main purpose is finding the table names used by the view,grouping can be done as we move forward using a temporary table may be http://rextester.com/NXFM21955

Please use this link for live cooperation,testing and further improvement http://rextester.com/live/UFEV3010


Restore exceeds licensed limit of 10240 MB per database. All I really need is the DB structure

The only way would be to restore to an edition of SQL Server besides the express edition, then delete the data that you don't need.  Then shrink the database below the 10 Gig limit then backup and restore to your express instance.

If this is for a development machine look at getting a copy of SQL Server Developer Edition. It's usually about $50.00 and doesn't have any limitations like database size.  It's only limitation is that you can't use the instance for production use.


In Talend MysqlOutput -> Component window -> Advanced settings. Add the following:

"useUnicode=true&characterEncoding=latin2"


Best approach to apply a long list of LIKE patterns?

I would use a translation table in a LATERAL subquery. Demo (Postgres 10+):
CREATE TABLE ac_translate (
   ord_nr int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
 , like_pattern text NOT NULL
 , target text NOT NULL
);

INSERT INTO ac_translate(like_pattern, target) VALUES
   ('AIR NEW Z%'       , 'AIR NEW ZEALAND')  -- rows in order of precedence!
 , ('AIR BP%'          , 'AIR BP')
 , ('ADDICTION ADVICE%', 'ADDICTION ADVICE')
 , ('AIA%'             , 'AIA')
;

See:

Does Postgres preserve insertion order of records?

Manipulate ord_nr to adjust priorities.
Query:
SELECT COALESCE(act.target, ac.accountName) AS accountname
     , SUM(ac.charge_amount) AS gstexcl
FROM   account_code ac
LEFT   JOIN LATERAL (
   SELECT a1.target
   FROM   ac_translate a1
   WHERE  ac.accountname LIKE a1.like_pattern
   ORDER  BY a1.ord_nr
   LIMIT  1
   ) act ON true
GROUP BY 1;
Or with a correlated subquery:
SELECT COALESCE(
        (SELECT a1.target
         FROM   ac_translate a1
         WHERE  ac.accountname LIKE a1.like_pattern
         ORDER  BY a1.ord_nr
         LIMIT  1), ac.accountName) AS accountname
     , SUM(ac.charge_amount) AS sum_amount
FROM   account_code ac
GROUP BY 1;

This is easy to handle, keeps the long list of options out of the code and puts it into a table where it can be maintained properly. And is moderately fast.
We can't easily use a plain LEFT JOIN ac_translate since CASE traverses patterns in order to return the single, first match. We can't just join to a set, that might return multiple matches if one pattern is the prefix of the other, like 'AIR%' and 'AIR N%'. So we use an ordering number in the translation table to prioritize matches in the subquery.
The ELSE clause in the referenced question resolves to the original value. That's implemented with COALESCE here. Basically, this combines the virtues of the top two answers over there.
To top it off, I slipped in GROUP BY 1 as another way to avoid repeating lengthy expressions (which is not actually needed here any more). See:

Concatenate multiple result rows of one column into one, group by another column [duplicate]

Speed
Performance deteriorates with the number of rows in the translation table since Postgres is forced to walk through all of them sequentially and evaluate the LIKE expression. If that's not fast enough any more, we need index support, but the expression is not "sargable" - the expression we need to index is to the right of the operator and there is no COMMUTATOR for LIKE. Details:

Can PostgreSQL index array columns?

There is a workaround, though. My example requires that patterns have at least 3 leading characters (3 is my arbitrary pick). Add a CHECK constraint in the translation table to enforce this rule, and an expression index on the leading trigram:
CREATE INDEX ac_translate_left_idx ON ac_translate (left(like_pattern, 3));

Adapt the query:
SELECT COALESCE(act.target, ac.accountName) AS accountname
     , SUM(ac.charge_amount) AS gstexcl
FROM   account_code ac
LEFT   JOIN LATERAL (
   SELECT a1.target
   FROM   ac_translate a1
   WHERE  left(ac.accountname, 3) = left(a1.like_pattern, 3)
   AND    ac.accountname LIKE a1.like_pattern
   ORDER  BY a1.ord_nr
   LIMIT  1
   ) act ON true
GROUP BY 1;
With enough rows in the translation table (and favorable estimates and cost settings) Postgres will use a very fast index scan to narrow it down to the few candidates (if any) and only filter the rest with the LIKE expression. Should scale just fine. I added the EXPLAIN output to the fiddle as proof of concept:
db<>fiddle here


SQL Server - linked Oracle server: EXECUTE permission denied on 'xp_prop_oledb_provider'

On the SQL Server where the linked server is defined, execute:

Use MASTER
GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO public

Does anyone have any comments on the security implications of this?


As I understand your question (and let me know if I have misunderstood your requirements):

Using the TOP 10 rows from Table1 where they fall into some date criteria and are ordered by COL2
Get the MAX(COL2) per COL1 group
Then, select all columns from Table2 where IDCOL = MAX(COL2) from the previous grouping.

I think this solution addresses all of those items.  Basically, CTE2 is encapsulated into CTE1.

--Demo setup
Declare @Table1 TABLE (COL1 INT, COL2 INT)
INSERT INTO @Table1(COL1, COL2) VALUES

(10005,500),
(10005,501),
(10005,521),
(10005,521),

(42344,502),
(42344,550),

(12345,503),
(12345,575),
(12345,575)

Declare @Table2 TABLE (IDCOL INT,OtherStuff varchar(10))
INSERT INTO @Table2(IDCOL,OtherStuff) VALUES
(575,'Other1'),(575,'Other2'),(580,'Other3')

--The solution
;WITH CTE1 (COL1,COL2,Rn)
AS (
    SELECT TOP 10 COL1
        ,COL2
        ,ROW_NUMBER() OVER (
            PARTITION BY COL1 ORDER BY COL2 DESC
            ) AS Rn
    FROM @Table1
    --   WHERE CREATEDATE < DATEADD(HOUR, -1, GETDATE())
    ORDER BY COL2
    )
--select * from CTE1 order by col1, rn
SELECT T2.*
FROM CTE1 c1
JOIN @Table2 T2
    ON t2.IDCOL = c1.COL2
        AND c1.Rn = 1

Rows coming out of CTE1 have an additional rn column.

| COL1  | COL2 | Rn |
|-------|------|----|
| 10005 | 521  | 1  |
| 10005 | 521  | 2  |
| 10005 | 501  | 3  |
| 10005 | 500  | 4  |
| 12345 | 575  | 1  |
| 12345 | 575  | 2  |
| 12345 | 503  | 3  |
| 42344 | 550  | 1  |
| 42344 | 502  | 2  |

The last part of the query joins Table2 with the CTE1 where only the rows from CTE1 have rn = 1 and Table2 (IDCOL) = COL2 value for rn = 1.

| IDCOL | OtherStuff |
|-------|------------|
| 575   | Other1     |
| 575   | Other2     |

Ultimately, you will have to judge whether it is a Better approach to optimize query.


Which of these two MYSQL queries is better (and what is the default join type)?

The default join type is inner join.

The second query may be better as it avoids an extra join to the partner table.  The required partner id value is available from the promotion table.  The impact will likely be minimal in this case.

I would recommend prefixing the primary key id columns with the table name (singular form); play_id, promotion_id and partner_id.  Most design tools allow this, but may default to the form you have.

Learn to use explain plans to understand how the query will be executed.  This is not foolproof as the execution plan may be influenced by table sizes and other factors.  Different databases may generate different execution plans.


Why does CONTAINS not find matches at the end of the string?

This has to do with how stoplist (or noise words) is/are treated when adding the asterix (*) in combination with CONTAINS().

A longer explanation on that here.

In short: Default <> Prefix searching

By default, when using the SYSTEM stoplist some words are not indexed and classified as stoplist words.

When adding the * + CONTAINS()they are not seen as stoplist words, and the exact phrase has to match.
But when using the default SYSTEM stoplist these 'stoplist words' are not added to the index, resulting in no matches found.

Question

  Are you saying that the intended behaviour, when an asterisk is
  present, is to return zero matches for a user who includes a noise
  word? This seems surprising, when you consider what most humans are
  likely to type!

Prefix matching in combination with contains from microsoft docs

  Specifies a match of words or phrases beginning with the specified
  text. Enclose a prefix term in double quotation marks ("") and add an
  asterisk (*) before the ending quotation mark, so that all text
  starting with the simple term specified before the asterisk is matched

Source

Keywords here are:

  so that all text
  starting with the simple term specified before the asterisk is matched

Meaning that we have to have precise matches when using prefix matching and CONTAINS().

Workaround #1 Disabling the stoplist

One workaround for this would be disabling the 'stoplist'

ALTER FULLTEXT INDEX ON News
SET STOPLIST OFF;

You do have to know that this will increase the words stored in the fulltext index, as the words 'and', 'or', 'still', ... are added to the index.

Performance might suffer from disabling the stoplist.

To reenable the system stoplist

ALTER FULLTEXT INDEX ON News
SET STOPLIST SYSTEM;

Alternatively, you could create your own stoplist

Testing this on your query:

ALTER FULLTEXT INDEX ON News
SET STOPLIST OFF;
INSERT INTO News(Headline)
VALUES('1120 days at sea and still sailing')

INSERT INTO News(Headline)
VALUES('zzz 1120 days at sea and still sailing')

SELECT * FROM News
WHERE CONTAINS(Headline, '"days at sea and still*"')

Result

id  Headline
1   1120 days at sea and still sailing
2   zzz 1120 days at sea and still sailing

For other workarounds you could look into using CONTAINS() or FREETEXT() without prefix matching, or prefix matching + CONTAINS() with one (non noise) word to get more accurate results

In your case, enbling transform noise words does not seem to work with prefix-matching

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'transform noise words', 1;
RECONFIGURE;
GO

Again due to how prefix matching + contains works.

SELECT * FROM News
WHERE CONTAINS(Headline, '"1120 days at sea and still sailing*"')

-- No results

Inside the fulltext index (with stoplist = system).

SELECT display_term,column_id,document_count
FROM sys.dm_fts_index_keywords(DB_ID('MNGDB'), OBJECT_ID('dbo.News'));

result

display_term    column_id   document_count
1120                 2           2
days                 2           2
nn1120               2           2
sailing              2           2
sea                  2           2
zzz                  2           1
END OF FILE          2           2

'Still','at','and' are filtered out, when disabling the stoplist they will be added to the index

Testing without stopwords

ALTER FULLTEXT INDEX ON News
SET STOPLIST SYSTEM;

INSERT INTO dbo.News
VALUES('days sea sailing')
SELECT * FROM dbo.News
WHERE CONTAINS(Headline, '"days sea sailing*"')

id  Headline
4   days sea sailing

More on the asterix

  The presence of the asterisk enables the prefix-matching mode...
  If a phrase is provided, matches are detected if the column contains
  all the specified words with zero or more other characters following
  the final word

Source


select `packages`.`id` as `id`,
       `external_id`,
       `project_name`,
       `credits_available`,
       `credits_aq`,
       `price`,
       `currency_code`,
       `packages`.`status`,
       `packages`.`updated_at`,
       SUM(order_items.credits_number) as credits_deducted_total,
       SUM(order_items_reserved.credits_number) as credits_reserved_total
from `packages`
left join `order_items`
     on `order_items`.`package_id` = `packages`.`id`
left join `order_items` as `order_items_reserved`
     on `order_items_reserved`.`package_id` = `packages`.`id`
left join `orders`
     on `orders`.`id` = `order_items`.`order_id`
    AND `orders`.`status` = 1
left join `orders` as `orders_reserved`
     on `orders_reserved`.`id` = `order_items_reserved`.`order_id`
    AND `orders_reserved`.`status` = 0
group by `packages`.`id`;

Alternatively:

select `packages`.`id` as `id`,
       `external_id`,
       `project_name`,
       `credits_available`,
       `credits_aq`,
       `price`,
       `currency_code`,
       `packages`.`status`,
       `packages`.`updated_at`,
       SUM(CASE WHEN `orders`.`status` = 1
                THEN order_items.credits_number
                ELSE 0
           END) as credits_deducted_total,
       SUM(CASE WHEN `orders`.`status` = 0
                THEN order_items.credits_number
                ELSE 0
           END) as credits_reserved_total
from `packages`
left join `order_items`
     on `order_items`.`package_id` = `packages`.`id`
left join `orders`
     on `orders`.`id` = `order_items`.`order_id`
group by `packages`.`id`;


How to correct SQL-Server installation with wrong language?

This can be done from the SSMS GUI.  Right click on the instance in object explorer and go to properties.

From there navigate to the Advanced page.

There is a drop down item there for Default Language.

Alternatively, if you know the language code you can do it via tsql

EXEC sp_configure 'default language', 0 ;
GO
RECONFIGURE ;
GO

where 0  is English in this case.

See https://msdn.microsoft.com/en-GB/library/ms190682.aspx

You may also need to update or change the default language of logins via their properties page.


How to get COUNT of a range between 2 columns

This is a way of doing it. However, if you need it broken out by day, you need to add an extra column in your table for indicating the day, and you should add that column to the table variable I created as well.

    DECLARE @Hours TABLE
    (
      hour varchar(2),
      total int
    )
    DECLARE @I INT
    SET @I = 0
    WHILE @I < 24
    begin
        INSERT INTO @Hours VALUES (RIGHT('0' + CAST(@I AS VARCHAR(2)),2), 0)
        SET @I = @I + 1
    end

    DECLARE @starthour VARCHAR(2)
    DECLARE @endhour VARCHAR(2)
    DECLARE cur CURSOR
    FOR SELECT starthour, endhour FROM WorkTime
    OPEN cur
    FETCH NEXT FROM cur
    INTO @starthour, @endhour
    WHILE @@FETCH_STATUS = 0
    begin
        UPDATE @hours
        SET total = total + 1
        WHERE hour BETWEEN @starthour AND @endhour
        FETCH NEXT FROM cur INTO @starthour, @endhour
    end
    CLOSE cur
    DEALLOCATE cur

SELECT * FROM @hours
WHERE total <> 0

OUTPUT

hour    total
08      1
09      2
10      2
11      3
12      3
13      3
14      3
15      4
16      4
17      3
18      2
19      2
20      2
21      2
22      1


Can the replicated database be the publisher with SQL Server

No.

The publisher is local to the data being published and can't be remote.

You can have a separate distributor of course

You'll have to consider database mirroring perhaps as an alternative or enable replication at the client


Few years ago we migrated our DB2 from 9.7 to 10.5. We also moved the database to new hardware and implemented data compression. Because of this we decided to create the database from scratch and export and import the data.

We used db2look and db2move to do the job. However, there was no way to get the DDL created by db2look in the correct order. We had to split the generated script into different parts for creating tables, creating triggers, creating indexes, etc.

Finally we end up with the following steps:

export the existing data
create the new database
create bufferpools and tablespaces
create tables
create primary keys
create indexes
create views
load data
run reorgs and runstats
create stored procedures, user defined functions and triggers

I hope it helps, even it is not the answer you may have expected.


"ORA-01017: invalid username/password" : Why is that?

SYSDBA isn't a user but a way to connect to the DB.

Connecting as sysdba is based on the OS authentication which means that as long as you connect to your machine with a user which is in the oradba group you will be connected with this user AS SYSDBA no metter what user/password you'll write (as long as you try to connect as sysdba of course)

Read more in this Tom Kyte's thread

So, this-

SQLPLUS / AS SYSDBA

and this

SQLPLUS a/b AS SYSDBA

and even this

SQLPLUS
Enter user-name: as SYSDBA
Enter user-name: a
Enter password: b

Are pretty much the same


How to forcibly create/modify MySQL user grants, without restarting the database service

You could copy just the the "mysql" database away to another location and start another daemon on it.  Get the SHA1 or DES hash stored in the user table for a user with SUPER privs (usually root, but sometimes renamed for security through obscurity).

Then connect to the mysql using a modified version of the client library that makes mysql_real_connect() support using a pre-hashed password instead of having it take the password plaintext.  This should be trivial.

You won't ever know the actual password, but with the hash and a modified client you'll be able to log in anyway.

You can then make any modifications to permissions, create necessary schema and tables and flush privileges.

I'll leave the security implications of such practices up to you.


DROP command denied to user 'root'@'localhost'

OK, then the problem is simpler than I expected.

It's time to hack the mysql.user table.

CREATE TABLE mysql.user_new LIKE mysql.user;
INSERT INTO mysql.user_new SELECT * FROM mysql.user;
UPDATE mysql.user_new SET drop_priv='Y',grant_priv='Y'
WHERE user='root' AND host='localhost';
UPDATE mysql.user A INNER JOIN mysql.user_new B USING (user,host)
SET A.drop_priv = B.drop_priv;
FLUSH PRIVILEGES;

Then, run this to verify:

SHOW GRANTS FOR 'root'@'localhost';

The reason you could not just run the GRANT command to fix this is the fact that you cannot grant a privilege that you currently do not own. Similarly, you can't use RENAME TABLE mysql.user TO mysql.user_old,mysql.user_new TO mysql.user; because RENAME requires the DROP privilege.


SQL Server DB becomes unusable overnight

It could happen that a small amount of data reaches a certain limit in the SQL Server to force another plan or something like that. This is not unlikely. But the fact that your disc seems to be heavily under duty takes me to another conclusion.
There are 2 possible base reasons for your slow down.

You upgraded your system and rebooted it
You load a bunch of data in it

Let's take a look at part No. 1
It might be that your SQL Server configuration might be broken. This can cause serious problems regarding your Server speed and the disc usage.
Please check in the first instance your basic server settings. Those basic settings are max server memory, affinity I/O mask, affinity mask and max degree of parallelism. You may need to enable the advanced options using show advanced options.
Here is a complete script:
-- enable advanced options
EXEC sp_configure 'show advanced options',1
-- apply configuration
RECONFIGURE
-- how much memory can the sql server allocate?
EXEC sp_configure 'max server memory'
-- which cpu is used to run I/O operations
EXEC sp_configure 'affinity I/O mask'
-- which cpus can run processes?
EXEC sp_configure 'affinity mask'
-- how many threads can work on one query part?
EXEC sp_configure 'max degree of parallelism'

Compare the result with your documented values in your installation steps. Are they still the same?
It may have many reasons why your server behaves so strange. I would normally bet, that your max server memory is just wrong. This will cause your SQL Server permanently swapping data pages. He can't hold everything in his memory. This means he need to read the pages from the disc, update it, write it instantly back. If another update comes along and uses the same page for an update, it can't be read from the memory. Instead the server needs to read it again from the disc. Just swapping...
Another problem can be a to high affinity on disc or processes. If you used a shared Server (SQL Server + other services) with a dedicated disc for SQL Server (which may be a rare case, but it could be), this could be your problem. Your server normally used to have for example 3 cpus for processes and one for I/O. The other 12 cpus are used for other services. In this case your affinity mask is wrong and uses for example an automatic configuration. This means your Server uses all the 16 cores for processes and I/O dynamically. If you have huge processes running, they can put a huge load on the disc, which it may not handle. But in fact, I don't believe that this is your case. It would be faster (even if just a bit) if this would apply, but your case is a slow down.
Another problem may be a too high degree of parallelism. Which means you have too many threads idling on one partial of a query. This could also cause a huge slow down if the parallelism don't work as expected. But this won't describe your high I/O in total.
Now let's take a look at part No. 2 too
You load a bunch of rows into your system. Even if this is a regular job, it could raised a limit in which your query plans escalate. It could be even the case that your insert in combination with SQL Server produces this behavior.
Yοu mentioned that you already tried to migrate your indices to another disc, which seems to help. This can be the happened just to the fact that you split the load on two different discs.
It may be that your indices were fractured, that your plans were fractured or that your statistics are just outdated.
1. lets check the statistics last update
You can do this manually over the interface for each single statistic element. Which would be a pain. Or you can try this code:
SELECT name AS indexname,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes

This will give you a complete information over each index (and heap) and the statistics behind them. Even if you run sp_updatestats it doesn't mean that the statistics were updated. The part when an update is quite tricky, even if you run sp_updatestats or even if auto update statistics is enabled, the statistics won't be updated just in time. Here are some edge points, when an update is needed/generated:

An empty table gets one or more rows
A table with more than 500 rows updates 20% + 500 additional rows and an insert happened afterwards
When 500 rows were changed in a table that holds less than 500 rows

This means, your statistics may be outdated even if you run the update.
You can take a look at the query above. If you find some pretty old statistics in some tables, you may want to run an manual statistic update for this table:
UPDATE STATISTICS dbo.YourBadTable WITH FULLSCAN

After that, you may want to give your server a kick in the ass to throw away all old plans.
DBCC FREEPROCCACHE

If you just want to clean all caches, you might want to run this instead:
DBCC FREESYSTEMCACHE ('ALL')

This will clean up all caches, not just the plan cache. I would normally warn, to use this on a production server in production phase. But as your server don't work currently, you can't harm them too much. It might slow down for some seconds maybe 1-2 minutes as he needs to rebuild all caches, but after that he should run with the correct plans.
Another reason can be totally fragmented indices. This can be checked on the whole server using this statement:
SELECT *
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL)

If the fragmentation is very high, you might need to reorganize (fragmentation < 20%) or totally rebuild (>20%) it. This may take more pressure on your disc and cause trouble. On the other hand, if the indices are that bad, it probably would help in the end more than it harms.
Beside those two reasons, there still may be a third problem
It maybe that your server is configured probably, you haven't changed any code in this time, just added a few rows. All statistics are updated and all caches are rebuild. All your indices are reorganized in the way you need them, but still - nothing works. It just might be that you reached the limit of available memory in your processes. Maybe you need more. You can simply check if there is any process which tries to get more memory than you have.
You can check this using this command:
SELECT * FROM sys.dm_exec_query_memory_grants

It will provide you a list of all sessions which consuming memory. There might be some query which is still waiting to get memory. Those queries can be easily filtered. All sessions where granted_memory_kb IS NULL. These are sessions which requested memory but don't get it. Another thing can be an granted memory which may be to low. You can compare the columns requested_memory_kb with granted_memory_kb. Requested shows how much memory the process needs to run optimal while granted shows up the memory which is enable for the process. If a process needs 2GB to run but only gets 2MB... you might get it on your own. ;-)
Another way is to check the RESSOURCE_SEMAPHORE:
SELECT * FROM sys.dm_exec_query_resource_semaphore

You can take a look at the waiter_count and the grantee_count. If the waiter is above 0, you have pressure on your memory, which may cause swapping and may cause the disc pressure seen by you in the perfmon.


How to SELECT with two CSV files/... on PostgreSQL?

Error is obvious since you're intending to add characters to integer defined column. Just change datatype for name column in log table from integer to varchar(10)


Database Schema with a shared identity Field

Until SQL Server supports sequences (next version "Denali") then you'll have to have a common table.

However, if I understand you, I think you're looking at the subtype/supertype pattern. A sequence would be nice but if you designed using, say, Object Role Modelling then you'd generate this pattern/schema

Basically, you have a common "Contract" table:

ContractID, PK, UQ1 (Identity)
ContractType, FK, UQ1
InsertedDateTime
UpdatedDateTime
CounterParty

Notes:

the PK is ContractID to give a unique value
ContractType defines your "various types". Let's go for Tom, Dick and Harry as types
The unique key UQ1 is on both ContractID, ContractType (this is a "super key")
InsertedDateTime, CounterParty etc are common attributes

Then you have sub-tables with specific attributes for each contract type

ContractTom: ContractID, ContractType, TomAttrib1, TomAttrib2, ..., TomAttribn
ContractDick: ContractID, ContractType, DickAttrib1, DickAttrib2
ContractHarry: ContractID, ContractType, HarryAttrib1, HarryAttrib2, HarryAttrib3

Notes:

ContractID, ContractType are both the PK and FK.
ContractType has CHECK constraint to restrict to Tom or Dick or Harry


No, absolutely impossible. Each sqlcmd invocation is a different session and transaction cannot span sessions.

Your use of a transaction on the script that invokes the sqlcmd is completely useless, it has nothing to do with the scripts being run.


Raise error if BCP command fails to dump data into a file

Powershell is your friend here. When working with cmd commands in Powershell you can use the $LASTEXITCODE variable to read the result of the command you executed.

The code below passes a BCP command into the Invoke-Expression cmdlet and captures it's output.

$OutputPath = "C:\temp\Numbers-20151230.dat"

try
{
    $Command = "bcp dbo.Numbers out $OutputPath -T -n -S Localhost\JamesA_Test -d UtilityDB"
    $Output = Invoke-Expression -command $Command

    if ($LASTEXITCODE)
    {
        throw $Output
    }
}
catch
{
    Write-Host "BCP command failed: $Output"
}

I'm not sure how you want to handle the error so I just used Write-Host to display the error for this example. You could log the error to the event log, a file, a table in SQL, etc.


how to prevent dropping sysadmin user by another user in sql server

CREATE TRIGGER [DDL_PreventLoginDrop]ON ALL SERVER
FOR DROP_LOGIN
AS
DECLARE @LoginName NVARCHAR(128),@eventData XML

SET @eventData = EVENTDATA()
SELECT @LoginName=@eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')
IF @LoginName IN ('Login name that you want to protect')
  BEGIN
    PRINT 'Gotha, you nasty application!'
    ROLLBACK;
  END
GO
ENABLE TRIGGER [DDL_PreventLoginDrop] ON ALL SERVER

disclaimer:
Hopefully you'll agree that this answers your question "How to prevent dropping a login" but in no way is this a proper root cause fix. However, I know that there are some pretty nasty legacy applications where it's not possible to change the code... I must admit, this one just made me smile..


Can I use Azure Data Lake in place of Data Warehouse to separate Compute and Storage of RDBMS analytics

Your approach sounds reasonable. Many of the analytics-oriented DBMS allow access to data lake files in situ. You're just reproducing a similar feature in user-space.
IIRC ADF's data flow uses a DataBricks cluster under the covers. This may be a simpler implementation than writing dataframes in a notebook and getting it to run in 'Bricks.
I think most opt for a load then process approach because SQL makes such manipulation easy and Synapse is optimised to perform it.
If your source database is SQL Server you may want to consider Change Data Capture for watermarks / delta tracking.


Join or Replace colum value based on query

You can use an scalar subquery in this way:

SELECT    h.name,
          i.name,
          i.delay,
          i.delay_flex,
          i.status,
          (SELECT name
           FROM hosts
           WHERE hostid = (SELECT hostid
                           FROM items
                           WHERE itemid = i.templateid)),
          i.params,
          i.description
FROM      items as i
LEFT JOIN hosts as h
ON        i.hostid = h.hostid
AND       i.type = 11
AND       i.status = 0
WHERE     h.name LIKE "ABC%"
OR        h.name LIKE "123%"
ORDER BY  templateid DESC
LIMIT 1;

Or you can add both tables hosts and items to the JOIN section:

SELECT    h.name,
          i.name,
          i.delay,
          i.delay_flex,
          i.status,
          h2.name,
          i.params,
          i.description
FROM      items as i
LEFT JOIN hosts as h
ON        i.hostid = h.hostid
AND       i.type = 11
AND       i.status = 0
LEFT JOIN items as it
ON        it.itemid = i.templateid
LEFT JOIN hosts as h2
ON        h2.hostid = it.hostid
WHERE     h.name LIKE "ABC%"
OR        h.name LIKE "123%"
ORDER BY  templateid DESC
LIMIT 1;


Why does the optimizer choose nested loops over merge joins here?

Why does it work like that and what should I do to get two Merge Joins?

With three table references (the minimum required), the query qualifies for the Transaction Processing (aka search 0) stage of cost-based optimization.

This stage is aimed at OLTP queries, which normally benefit from a navigational (index-based) strategy. Nested Loops Join is the main physical join type available (hash and merge are only considered if no valid nested loops plan can be found in this stage).

If this stage finds a low cost (good enough) plan, cost-based optimization stops there. This prevents spending more time in optimization that we can expect to save over the best solution found so far. If the cost exceeds a threshold, the optimizer moves on to the Quick Plan (search 1), parallel Quick Plan, and Full Optimization (search 2) phases.

The query with two table references did not qualify for Transaction Processing, and went straight into Quick Plan, where Merge and Hash joins are available.

See my Query Optimizer Deep Dive series for more information.

  Without inner merge join hint.

If you absolutely must hint a physical join type, strongly prefer OPTION (MERGE JOIN). This allows the optimizer to still consider changing the join order.

Join hints like INNER MERGE JOIN come with an implied OPTION (FORCE ORDER), which severely limits the optimizer's freedom, with consequences most people (including experts) do not appreciate.


Why does MySQL server 8+ create .ini config files in a MySQL server 5.5 folder?

After alot of digging I found the answer.

Inside %AppData%..\MySQL I found "server_instances.xml". For some reason it listed MySQL server version as 5.5 even though workbench AND command line both showed it is 8.0, and 5.5 was never installed in my machine.

I searched for all mentions of 5.5 and swapped them for 8.0. Saved, rebooted machine (just to be sure) and opened workbench. Created a new config file via the Options File under the Administration in workbench. Everything works :)


Having regex_matches exclude cases where both capturing groups match a 0-length string

I can get this by making the whole preface digit optional and ignoring the trailing pipe you put in the last group, I think you can remove the NULLs after by another process:
select array(
  select regexp_matches(
    '9a.-16.y -y. -7hello world',
    '(-\d+|\d+)?([a-zA-Z][a-zA-Z.]*)',
    'g'
));
                        array
-----------------------------------------------------
 {{9,a.},{NULL,y},{NULL,y.},{-7,hello},{NULL,world}}


Restore a database with compression on a non-enterprise edition SQL Server

No !

But, you can :

1 script our your database schema and use BCP out and bcp in method  or

2 restore the database as a copy on EE or Dev edition, remove all enterprise features and backup the database and restore it on standard edition.


Single MySQL query with row averages based on conditions

Keep the raw data in MySQL.  But plan on coding the logic in PHP (or whatever).  You have only begun to touch on how complex the math can get.  What about the blood pressure readings, plus whether the patient is/was taking a BP lowering drug?


The information in your question leads me to believe that some kind of log truncation is occurring between your first invocation of DBCC SQLPERF('LOGSPACE') and your second invocation.  According to The Transaction Log, (specifically the section Factors that can delay log truncation),  there are numerous things that can prevent log truncation.  You mentioned the fact that the database is in a High Availability Group.  I'd suggest periodically querying the sys.databases catalog view (log_reuse_wait and log_reuse_wait_desc columns).  Perhaps "a secondary replica of an availability group is applying transaction log records of this database to a corresponding secondary database. and when that completes, the VLF's are marked for reuse."


Figured it out. I saw documentation about the using keyword which apparently wasn't what I needed. Instead I needed to use string concatenation ||.

Here's the working version:

SET SERVEROUTPUT ON
declare

begin
    for rec in (select distinct Profile from dba_users) loop
     EXECUTE IMMEDIATE 'alter profile ' || rec.PROFILE || ' LIMIT PASSWORD_LIFE_TIME UNLIMITED';
     EXECUTE IMMEDIATE 'alter profile ' || rec.PROFILE || ' LIMIT PASSWORD_REUSE_MAX UNLIMITED';
    end loop;
end;
/


Four db servers all identical, one is much slower than the others

I'd be inclined to point a finger at the storage of the poorly performing server.

Sounds like you're in the unfortunate position of having a storage admin telling you everything is ok when it quite possibly isn't. Only way to be sure is to prove it.

If you have the luxury of a maintenance window, run a batch of SQLIO tests against each server and compare the results. @BrentOzar has a good intro article on SQLIO which also covers some alternative tools such as Crystal Diskmark, which might be enough to prove the point in your case. Run the tests in collaboration with the storage admin, so they can witness the issues first hand. Perhaps arm yourself with How to Prove It’s a SAN Problem before hand.

If IO testing proves fruitless or impossible, throw sp_whoisactive or a similar monitoring solution at the problem. Kendra Little's Collecting Data from sp_WhoIsActive in a Table would be the best approach for a long running ETL process.

Before doing any of the above, take 1 minute to check something very very basic that crops up time and time again... the NTFS allocation unit size on each of the arrays.


Your best bet is going to be to split the list into a temp table.

I'm using Jeff Moden's DelimitedSplit8k.  You can find the code here.  Note it includes an ItemNumber column that is the values in order.

DECLARE @DelimString varchar(50) = '4,5,3'

SELECT * INTO #DelimList FROM dbo.DelimitedSplit8k(@DelimString,',')

SELECT Table.*
FROM Table
JOIN #DelimList
    ON Table.Id = #DelimList.Item
ORDER BY #DelimList.ItemNumber

This has the added benefit of being faster for large numbers of values in the delimited list.


Combine various temporal criteria to filter rows

A basic solution to retrieve only the first row per interval would be to

Generate time series using from_date, to_dateand interval with generate_series().
Join to (pre-selected) main table thereby forming 1 group per interval.
Use DISTINCT ON to get the first per group.

Select first row in each GROUP BY group?

For simpler parameter handling I wrapped the query in a plpgsql function:

CREATE OR REPLACE FUNCTION f_snapshots(_camera_id int
                                     , _from_date timestamp
                                     , _to_date   timestamp
                                     , _interval  interval = '0 min')
  RETURNS SETOF snapshots AS
$func$
BEGIN
IF _interval > '0 min' THEN
   RETURN QUERY
   SELECT DISTINCT ON (i) s.*
   FROM  (
      SELECT *
      FROM   snapshots
      WHERE  created_at >= _from_date
      AND    created_at <  _to_date    -- exclude upper bound?
      ) s
   JOIN   generate_series(_from_date
                        , _to_date - _interval  -- adjust upper bound
                        , _interval) i ON s.created_at >= i
                                      AND s.created_at <  i + _interval
   ORDER  BY i, created_at;

ELSE  -- no interval, return all rows
   RETURN QUERY
   SELECT *
   FROM   snapshots
   WHERE  created_at >= _from_date
   AND    created_at <  _to_date
   ORDER  BY created_at;
END IF;
END
$func$  LANGUAGE plpgsql STABLE;

Call:

SELECT * FROM f_snapshots(
        _camera_id := '9879'
      , _from_date := '2015-05-15 00:00'
      , _to_date   := '2016-08-15 00:00'
      , _interval  := '120 min');

I ignored the additional filter schedule. Add that yourself. I would extract all intervals from the json document and add OR'ed WHERE conditions. Either use the SQL OVERLAPS operator or the overlap operator && for range types. More:

2 B-tree indices OR 1 GiST index on tsrange -- which will perform better?
Find overlapping date ranges in PostgreSQL

Also consider my answer to your previous question:

Filter created_at and count distinct minutes


Update row where associated row does not exist

Add the predicate:

 AND NOT EXISTS (
 SELECT 1 FROM role AS r_existing
 WHERE r_existing.role_name = 'prefix-' || role.role_name)


postgres - SUM Time of this Interval and Group by 30 and 30 minutes

I've got a ton of questions here,

Are these all the same day?
What should happen if there is no overlap in time?

Let's also focus on this table from the times of 6:30-7:00

     date_start      |      date_end       | overlap
---------------------+---------------------+----------
 2017-01-06 06:52:00 | 2017-01-06 06:52:00 |
 2017-01-06 06:56:00 | 2017-01-06 07:25:00 | 00:04:00
 2017-01-06 06:56:00 | 2017-01-06 07:05:00 | 00:04:00
 2017-01-06 06:56:00 | 2017-01-06 10:29:00 | 00:04:00
 2017-01-06 06:57:00 | 2017-01-06 07:23:00 | 00:03:00

So how do you get this in your ideal result set?

06:30:00 - 07:00:00 |   00:12:36

Please, value our time... When you create sample data, it's important for you to be sure you're not misleading us. I'm sure it's an innocent mistake, but double check next time. Moreover, this should set you down the right path to get this job done..

SELECT
  tsrange,
  sum(
    UPPER(tsrange*tsrange(date_start,date_end)) -
    LOWER(tsrange*tsrange(date_start,date_end))
  )
FROM foo
JOIN (
  SELECT tsrange(ts::timestamp, ts+'30 minutes'::interval) AS tsrange
  FROM generate_series(
    '2017-01-06'::timestamp,
    '2017-01-17'::timestamp,
    '30 minutes'::interval
  ) AS t(ts)
) AS t(tsrange)
  ON tsrange && tsrange(date_start,date_end)
GROUP BY tsrange
ORDER BY tsrange
;

Let's break it down, here we generate the times in 30 minute intervals as a timestamp range..

JOIN (
  SELECT tsrange(ts::timestamp, ts+'30 minutes'::interval) AS tsrange
  FROM generate_series(
    '2017-01-06'::timestamp,
    '2017-01-17'::timestamp,
    '30 minutes'::interval
  ) AS t(ts)
) AS t(tsrange)
  ON tsrange && tsrange(date_start,date_end)

Then we join this set on an overlap test. The only other clever unintuitive thing is this,

sum(
  UPPER(tsrange*tsrange(date_start,date_end)) -
  LOWER(tsrange*tsrange(date_start,date_end))
)

In my opinion tsrange - tsrange should return an interval (or a cast should be provided). One isn't. So we have to do what's done above. This provides us the duration of the overlap that we previously detected with &&. We can sum(interval) and produce the result set you want..

                    tsrange                    |   sum
-----------------------------------------------+----------
 ["2017-01-06 06:30:00","2017-01-06 07:00:00") | 00:15:00
 ["2017-01-06 07:00:00","2017-01-06 07:30:00") | 01:23:00
 ["2017-01-06 07:30:00","2017-01-06 08:00:00") | 00:30:00
 ["2017-01-06 08:00:00","2017-01-06 08:30:00") | 00:30:00
 ["2017-01-06 08:30:00","2017-01-06 09:00:00") | 00:30:00
 ["2017-01-06 09:00:00","2017-01-06 09:30:00") | 00:30:00
 ["2017-01-06 09:30:00","2017-01-06 10:00:00") | 00:30:00
 ["2017-01-06 10:00:00","2017-01-06 10:30:00") | 00:29:00
(8 rows)


What are SHOWPLAN permission security risks? (MS SQL Server)

You can read more on the security risk in: SHOWPLAN Permission and Transact-SQL Batches:

  Security Note
  Users who have SHOWPLAN, ALTER TRACE, or VIEW SERVER STATE permission can view queries that are captured in Showplan output. These queries may contain sensitive information such as passwords. Therefore, we recommend that you only grant these permissions to users who are authorized to view sensitive information, such as members of the db_owner fixed database role, or members of the sysadmin fixed server role. We also recommend that you only save Showplan files or trace files that contain Showplan-related events to a location that uses the NTFS file system, and that you restrict access to users who are authorized to view sensitive information.


What are changes made to a database schema that is already in 4NF that would break applications?

Most changes are "breaking" if you aren't careful.  Any change to code for example (you mentioned triggers).  Any new or modified constraints, calculated columns etc.

You could even add a new index and effectively "break" your application because a query that used to run sub second now runs in seconds, minutes, even hours because the query plan has changed.

Assuming your code is written to always specifically name columns will probably make adding new columns reasonably safe and adding a table almost always is but you still probably want to test how each change to your database affects your application and it's performance.


Find player's latest match whether player was the winner or loser

Are you looking for this,

select top(1)  isWinner,  win_end_hcp
from
(
select 1 isWinner, end_time,  win_end_hcp from table1
where winner_id=X
union ALL
select 0 isWinner, end_time,  win_end_hcp from table1
where loser_id=X
)t4
order by t4.end_time desc


Why am i getting a truncation error when running a SSIS package on the sql server agent, but not when i manually run it

This is one of those annoying inconsistencies due to the fact that the SQL Agent may run under different settings than when you run it yourself (e.g. it might have ARITHABORT off when you have it on, so forth).

Rather than twiddling with the settings, I find it more robust to use solution that are not sensitive to different settings, which means avoid any possible precision loss. Because your target is an Access database, the date/time has a precision of one second. If you're using old datetime data type, it has a weird precision of 1/3 milliseconds, which can potentially cause the precision loss that SSIS loves to tell everyone and their dog about.

To work around that, match the precision exactly by using datetime2(0) which has the same precision (one second) which should then avoid the error, regardless of what settings you/the agent are running under.


TYPE_SYM syntax error when running the mysql command to create or replace type

There is a bit of confusion that needs to be cleared up.
MySQL
In MySQL, the SQL Clause CREATE OR REPLACE works with Views only
You cannot create CREATE OR REPLACE any other types in MySQL (someone correct me if this is not so).
That's why ERROR: Line 1: syntax error, unexpected TYPE_SYM, expecting VIEW_SYM. Statement skipped. says VIEW_SYM is expected.
You also cannot create types with the above syntax in MySQL.
PostgreSQL
You can create types in PostgreSQL, but there is no CREATE OR REPLACE syntax for creating types. You can use CREATE OR REPLACE for creating functions and views, but not types.


Why not create a VIEW? The upside is that it is client independent.


Make queries and BCP faster on a very large table

This is a long shot but seen as you are running SQL 2014 Enterprise I would have a test with using a Clustered Columnstore Index on this table and see if this improves performance for you.

Especially considering that you are using the table for selects and bulk inserts only - no updates and no deletes.

You will have the added advantage of taking a bit of load off the spinning disks and moving it more towards the CPU / Memory which you seem to have a decent amount of.


Moving SQL server 2008 to a new Server with all DBs, replication and Jobs

To do this without recreating the publications and subscriptions - backup the publication, msdb, distribution, and the master databases.  Then restore them on the new Publisher that has the same name as the old Publisher.  Use the keep_replication switch when doing the restore.  A DNS flush may be required but afterwards the Subscribers should begin synchronizing.

This may or may not work for you.  I highly suggest testing the migration out in your test environment prior to production deployment.

I would also like to add that you may have to back up and restore the Service Master Key to the new Publisher as well.  The is tied to the distributor_admin login that is created for the linked server when you Configure Distribution.

If this doesn't work for you - you can you can configure Distribution on the new Publisher/Distributor, and recreate the publication and subscriptions from backups.  The subscriptions could be configured as replication support only if done during a maintenance window with no changes occurring.  This would avoid sending down a new snapshot.  If not done during a maintenance window, run a data validation to see how out of sync you are with the Subscriber and use tablediff utility or SQL Data Compare to bring the Publisher and Subscriber back into convergence.  Reinitialization is an option as well.

Also, if you are upgrading the Publisher SQL Server version then there are considerations to make which are covered in Using Multiple Versions of SQL Server in a Replication Topology.  It depends on your replication type.

  For all types of replication, the Distributor version must be no
  earlier than the Publisher version. (Frequently, the Distributor is
  the same instance as the Publisher.)

  For transactional replication, a Subscriber to a transactional
  publication can be any version within two versions of the Publisher
  version. For example, a SQL Server 2000 Publisher can have SQL Server
  2008 Subscribers, and a SQL Server 2008 Publisher can have SQL Server
  2000 Subscribers.

  For merge replication, a Subscriber to a merge publication can be any
  version no later than the Publisher version. For more information
  about compatibility for earlier versions, see "Compatibility Level for
  Merge Publications" later in this topic. For more information about
  replication features that are supported in the various editions of SQL
  Server, see Features Supported by the Editions of SQL Server 2012.


Q) RMAN is the online backup tool for oracle. MySQL backup for MySQL, is it capable to do point in time recovery?

A) MySQL use mysqldump as online backup tool.

Q) In Oracle, an instance starts with memory structure SGA. How does it work in MySQL?

A) In MySQL, tuning of the caches similar to Oracle’s buffer cache is done on a per storage engine basis. For the MyISAM engine, the key_buffer can be used to cache index data only, and relies on the underlying operating system to cache data files unlike Oracle’s buffer cache. Because of this difference, while a dedicated Oracle database might allocate 80% of the system’s main memory to the buffer cache, often MySQL’s key buffer does not get set to much over 50% of the database system’s main memory for optimal tuning.

MySQL’s InnoDB storage engine has a more similar cache to Oracle’s buffer cache called innodb_buffer_pool_size and caches both index and data blocks. Like Oracle’s buffer pool, a dedicated MySQL system using only InnoDB tables would do well to set to up 80% of the systems main memory for use in this cache.

Q) I don't understand logging and database files layout in MySQL In Oracle if i create tablespace we see a file on the file system with name. How does this work in MySQL?

A) Each database managed by the MySQL server has its own database directory. This exists as a subdirectory of the data directory, with the same name as the database it represents. For example, a database mydb corresponds to the database directory DATADIR/mydb. This representation allows several database-level statements to be almost trivial in their implementation.

Q) In Oracle we have to put the database in archive log mode, do we have to do this with MySQL to perform point in time recovery?

A)  I found something similar to this question in https://stackoverflow.com/questions/65093/best-way-to-archive-live-mysql-database


Business Continuity Plan -- How can I connect to DR Server if PROD site is completely Down

If you have:

2 Datacenters
1 Server in Each DC
Using SQL Server AlwaysOn Availability Groups

Then the following happens:

The Primary Datacenter Goes Down, Completely

How do you continue business continuity?

Assuming that the Availability Replicas are both set to Synchronous...

Quorum will have most likely been lost - you'll need to force quorum on the server in the DC that is still up. This can be completed a variety of ways. Powershell: Start-ClusterNode -FixQuorum , services: net start clussvc /fq , etc.
Connect to SQL Server now that the cluster was forced quorum. Failover the Availability Group. This will bring the listener with it. Whether Synchronous or Asynchronous is set, this can be accomplished by executing ALTER VAILABILITY GROUP [Name] FORCE_FAILOVER_ALLOW_DATA_LOSS
Test connectivity.

Depending on the settings of the listener in the windows cluster, your clients may need to flush their dns cache (to pickup the new ip if they are legacy clients).

Once the first DC comes back online we'll want to make sure to reconfigure and re-evaluate the situation.

Resources that will help you:

https://msdn.microsoft.com/en-us/library/hh781257.aspx (Solutions guide for HA/DR)
https://msdn.microsoft.com/en-us/library/dn425173.aspx (MSIT case study for HA/DR)


What's the fastest way to replicate data?

First, let me say that I would not recommend doing this, ever.  I would recommend running your database on EC2 and having it live there, only.  Have a test database on your local machine for convenience, but keeping the two in sync is going to be painful, as you will see from the answers below.

  One way is to zip the file and then upload it to the server. But what
  about if after that I want to synchronize the server data with current
  data?

You either need to add that EC2 based server as a secondary, so that it syncs from your home machine (likely to be unreliable) and would require that you have an appropriate set up to route traffic), or you need to replay the oplog manually from your home machine on the EC2 machine (basically how replication functions).  You can write your own program to do so, or in 2.2 you have the option of the mongooplog tool

  So how do the amazon server knows the IP of my computer?
  So how do I turn a server in my computer to become master? How do I
  set the router to pass on connection?

It may not be able to route to your computer, you may have only an IPV6 address, but I doubt  it.  Go here:

http://www.whatsmyip.org/

That will tell you what your publicly viewable IP address is, and then you can try connecting to it from EC2 to see if it works.  It may not - ISPs will often block listening ports and have other firewalls in effect to prevent this.  As for how to pass on connections through a rotuer etc. that topic is large and beyond the scope of the DBA site realistically.  You should try SuperUser for a detailed answer on that front, and be sure to include your router model when asking.

  Then there is mongodump. Is it fast?

Mongodump is as fast as your database basically - it has to walk an index (usually _id), write out each document it finds as BSON.  So it will be limited by how quickly your machine can do that.  It is not likely to be as fast as a simple file based copy.

  Which one do you usually use?

I doubt anyone runs in this manner for very long - it will be too prone to failure.  I would imagine that most will do one of the above methods once (or at least very infrequently), to move their DB into EC2 or elsewhere on the cloud, then just use it there, rather than trying to keep the two in sync as part of a replica set.


If your SQL Server has not been restarted there may a chance that your query text might have have been cached along with its execution plan. You may want to run the following to see if query text is still in the plan.

SELECT
    cp.objtype AS ObjectType,
    OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
    cp.usecounts AS ExecutionCount,
    st.TEXT AS QueryText,
    qp.query_plan AS QueryPlan
FROM
    sys.dm_exec_cached_plans AS cp
CROSS APPLY
    sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY
    sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE
    st.text LIKE  '%query search criteria%'


MySQL hangs when I query specific tables after truncate

See answer on StackExchange and the bug is known and related to the autoincrement.
And if you don't only want to understand the issue, but fix it, see https://dba.stackexchange.com/a/146181/220459.
mysqladmin -u root proc
mysqladmin -u root kill xxx

where xxx is the first process that is hanging.


Upgrade Advisor Or DMA (Database Migration Assistant)

The list on the page you reference is the versions of SSRS that are supported to upgrade FROM.  So, basically any version of SSRS from 2008 onwards is supported to upgrade to SQL 2017.
https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/migrate-a-reporting-services-installation-native-mode?view=sql-server-ver15

...one of the following supported versions of a Reporting Services native
mode deployment to a new SQL Server Reporting Services instance:
SQL Server 2016 (13.x)
SQL Server 2014 (12.x)
SQL Server 2012 (11.x)
SQL Server 2008 R2
SQL Server 2008

Yes, the migration assistant is useful, as it mentions it will check for non-standard configurations of the source SSRS installation to make sure that nothing weird will happen.

By running Upgrade Advisor, you can find out about custom settings
that might not be supported in a new SQL Server installation.

My preference though is not to migrate/update SSRS but to do a fresh deployment from source control (upgrade the project to the target version) and go from there.  This means that you will need to recreate the schedule for any reports, etc, etc.  But this is a good time to go over your SSRS installation and cull the cruft that has built up.  Look at the execution history, find out which reports are actually used!  Make sure that they are sharing data sources and you don't have any wonky connections that are over privileged.  It's more work but you are making your environment better by taking the time.


Don't know how to transform variable entity into relational table

The proper structure for this scenario is a SubClass / Inheritance model, and is nearly identical to the concept I proposed in this answer: Heterogeneous ordered list of value.

The model proposed in this question is actually quite close in that the Animal entity contains the type (i.e. race) and the properties that are common across all types. However, there are two minor changes that are needed:

Remove the Cat_ID and Dog_ID fields from their respective entities:

The key concept here is that everything is an Animal, regardless of race: Cat, Dog, Elephant, and so on. Given that starting point, any particular race of Animal doesn't truly need a separate identifier since:

the Animal_ID is unique
the Cat, Dog, and any additional race entities added in the future do not, by themselves, fully represent any particular Animal; they only have meaning when used in combination with the information contained in the parent entity, Animal.

Hence, the Animal_ID property in the Cat, Dog, etc entities is both the PK and the FK back to the Animal entity.
Differentiate between types of breed:

Just because two properties share the same name does not necessarily mean that those properties are the same, even if the name being the same implies such a relationship. In this case, what you really have is actually CatBreed and DogBreed as seperate "types"

Initial Notes

The SQL is specific to Microsoft SQL Server (i.e. is T-SQL). Meaning, be careful about datatypes as they are not the same across all RDBMS's. For example, I am using VARCHAR but if you need to store anything outside of the standard ASCII set, you should really use NVARCHAR.
The ID fields of the "type" tables (Race, CatBreed, and DogBreed) are not auto-incrementing (i.e. IDENTITY in terms of T-SQL) because they are application constants (i.e. they are part of the application) that are static lookup values in the database and are represented as enums in C# (or other languages). If values are added, they are added in controlled situations. I reserve the use of auto-increment fields for user data that comes in via the application.
The naming convention I use is to name each subclass table starting with the main class name followed by the subclass name. This helps organize the tables as well as indicates clearly (without looking at the FKs) the relationship of the subclass table to the main entity table.
Please see "Final Edit" section at the end for a note regarding Views.

"Breed" as "Race"-Specific Approach

This first set of tables are the lookup / types tables:

CREATE TABLE Race
(
  RaceID INT NOT NULL PRIMARY KEY
  RaceName VARCHAR(50) NOT NULL
);

CREATE TABLE CatBreed
(
  CatBreedID INT NOT NULL PRIMARY KEY,
  BreedName VARCHAR(50),
  CatBreedAttribute1 INT,
  CatBreedAttribute2 VARCHAR(10)
  -- other "CatBreed"-specific properties as needed
);

CREATE TABLE DogBreed
(
  DogBreedID INT NOT NULL PRIMARY KEY,
  BreedName VARCHAR(50),
  DogBreedAttribute1 TINYINT
  -- other "DogBreed"-specific properties as needed
);

This second listing is the main "Animal" entity:

CREATE TABLE Animal
(
  AnimalID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  RaceID INT NOT NULL, -- FK to Race
  Name VARCHAR(50)
  -- other "Animal" properties that are shared across "Race" types
);

ALTER TABLE Animal
  ADD CONSTRAINT [FK_Animal_Race]
  FOREIGN KEY (RaceID)
  REFERENCES Race (RaceID);

This third set of tables are the complimentary sub-class entities that complete the definition of each Race of Animal:

CREATE TABLE AnimalCat
(
  AnimalID INT NOT NULL PRIMARY KEY, -- FK to Animal
  CatBreedID INT NOT NULL, -- FK to CatBreed
  HairColor VARCHAR(50) NOT NULL
  -- other "Cat"-specific properties as needed
);

ALTER TABLE AnimalCat
  ADD CONSTRAINT [FK_AnimalCat_CatBreed]
  FOREIGN KEY (CatBreedID)
  REFERENCES CatBreed (CatBreedID);

ALTER TABLE AnimalCat
  ADD CONSTRAINT [FK_AnimalCat_Animal]
  FOREIGN KEY (AnimalID)
  REFERENCES Animal (AnimalID);

CREATE TABLE AnimalDog
(
  AnimalID INT NOT NULL PRIMARY KEY, -- FK to Animal
  DogBreedID INT NOT NULL, -- FK to DogBreed
  HairColor VARCHAR(50) NOT NULL
  -- other "Dog"-specific properties as needed
);

ALTER TABLE AnimalDog
  ADD CONSTRAINT [FK_AnimalDog_DogBreed]
  FOREIGN KEY (DogBreedID)
  REFERENCES DogBreed (DogBreedID);

ALTER TABLE AnimalDog
  ADD CONSTRAINT [FK_AnimalDog_Animal]
  FOREIGN KEY (AnimalID)
  REFERENCES Animal (AnimalID);

The model using a shared breed type is shown after the "Additional Notes" section.

Additional Notes

The concept of breed seems to be a focal point for confusion. It was suggested by jcolebrand (in a comment on the question) that breed is a property shared across the different races, and the other two answers have it integrated as such in their models. This is a mistake, however, because the values for breed are not shared across the different values of race. Yes, I am aware that the two other proposed models attempt to solve this issue by making race a parent of breed. While that technically solves the relationship issue, it doesn't help solve the overall modeling question of what to do about non-common properties, nor how to handle a race that does not have a breed. But, in the case that such a property were guaranteed to exist across all Animals, I will include an option for that as well (below).
The models proposed by vijayp and DavidN (which seem to be identical) do not work because:

They either

do not allow for non-common properties to be stored (at least not for individual instances of any Animal), or
require that all properties for all races be stored in the Animal entity which is a very flat (and nearly non-relational) way of representing this data. Yes, people do this all of the time, but it means having many NULL fields per row for the properties that are not meant for that particular race AND knowing which fields per row are associated with the particular race of that record.

They do not allow for adding a race of Animal in the future that does not have breed as a property. And even if ALL Animals have a breed, that wouldn't change the structure due to what has been previously noted about breed: that breed is dependent on the race (i.e. breed for Cat is not the same thing as breed for Dog).

"Breed" as Common- / Shared- Property Approach

Please note:

The SQL below can be run in the same database as the model presented above:

The Race table is the same
The Breed table is new
The three Animal tables have been appended with a 2

Even with Breed being a now common property, it does not seem right not to have Race noted in the main/parent entity (even if it is technically relationally correct). So, both RaceID and BreedID are represented in Animal2. In order to prevent a mismatch between the RaceID noted in Animal2 and a BreedID that is for a different RaceID, I have added a FK on both RaceID, BreedID that references a UNIQUE CONSTRAINT of those fields in the Breed table. I usually despise pointing a FK to a UNIQUE CONSTRAINT, but here is one of the few valid reasons to do so. A UNIQUE CONSTRAINT is logically an "Alternate Key", which makes it valid for this use. Please also note that the Breed table still has a PK on just BreedID.

The reason for not going with just a PK on the combined fields and no UNIQUE CONSTRAINT is that it would allow for the same BreedID to be repeated across different values of RaceID.
The reason for not switching which the PK and UNIQUE CONSTRAINT around is that this might not be the only usage of BreedID, so it should still be possible to reference a specific value of Breed without having the RaceID available.

While the following model does work, it has two potential flaws regarding the concept of a shared Breed (and are why I prefer the Race-specific Breed tables).

There is an implicit assumption that ALL values of Breed have the same properties. There is no easy way in this model to have disparate properties between Dog "breeds" and Elephant "breeds". However, there still is a way to do this, which is noted in the "Final Edit" section.
There is no way to share a Breed across more than one race. I am not sure if that is desirable to do (or maybe not in the concept of animals but possibly in other situations that would be using this type of model), but it is not possible here.

CREATE TABLE Race
(
  RaceID INT NOT NULL PRIMARY KEY,
  RaceName VARCHAR(50) NOT NULL
);

CREATE TABLE Breed
(
  BreedID INT NOT NULL PRIMARY KEY,
  RaceID INT NOT NULL, -- FK to Race
  BreedName VARCHAR(50)
);

ALTER TABLE Breed
  ADD CONSTRAINT [UQ_Breed]
  UNIQUE (RaceID, BreedID);

ALTER TABLE Breed
  ADD CONSTRAINT [FK_Breed_Race]
  FOREIGN KEY (RaceID)
  REFERENCES Race (RaceID);

CREATE TABLE Animal2
(
  AnimalID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  RaceID INT NOT NULL, -- FK to Race, FK to Breed
  BreedID INT NOT NULL, -- FK to Breed
  Name VARCHAR(50)
  -- other properties common to all "Animal" types
);

ALTER TABLE Animal2
  ADD CONSTRAINT [FK_Animal2_Race]
  FOREIGN KEY (RaceID)
  REFERENCES Race (RaceID);

-- This FK points to the UNIQUE CONSTRAINT on Breed, _not_ to the PK!
ALTER TABLE Animal2
  ADD CONSTRAINT [FK_Animal2_Breed]
  FOREIGN KEY (RaceID, BreedID)
  REFERENCES Breed (RaceID, BreedID);

CREATE TABLE AnimalCat2
(
  AnimalID INT NOT NULL PRIMARY KEY, -- FK to Animal
  HairColor VARCHAR(50) NOT NULL
);

ALTER TABLE AnimalCat2
  ADD CONSTRAINT [FK_AnimalCat2_Animal2]
  FOREIGN KEY (AnimalID)
  REFERENCES Animal2 (AnimalID);

CREATE TABLE AnimalDog2
(
  AnimalID INT NOT NULL PRIMARY KEY,
  HairColor VARCHAR(50) NOT NULL
);

ALTER TABLE AnimalDog2
  ADD CONSTRAINT [FK_AnimalDog2_Animal2]
  FOREIGN KEY (AnimalID)
  REFERENCES Animal2 (AnimalID);

Final Edit (hopefully ;-)

Regarding the possibility (and then difficulty) of handling disparate properties between types of Breed, it is possible to employ the same subclass / inheritance concept but with Breed as the main entity. In this setup the Breed table would have the properties common to all types of Breed (just like the Animal table) and RaceID would represent the type of Breed (same as it does in the Animal table). Then you would have subclass tables such as BreedCat, BreedDog, and so on. For smaller projects this might be considered "over-engineering", but it is being mentioned as an option for situations that would benefit from it.
For both approaches, it sometimes helps to create Views as short-cuts to the full entities. For example, consider:

CREATE VIEW Cats AS
   SELECT  an.AnimalID,
           an.RaceID,
           an.Name,
           -- other "Animal" properties that are shared across "Race" types
           cat.CatBreedID,
           cat.HairColor
           -- other "Cat"-specific properties as needed
   FROM    Animal an
   INNER JOIN  AnimalCat cat
           ON  cat.AnimalID = an.AnimalID
   -- maybe add in JOIN(s) and field(s) for "Race" and/or "Breed"

While not part of the logical entities, it is fairly common to have audit fields in the tables to at least get a sense of when the records are being inserted and updated. So in practical terms:

A CreatedDate field would be added to the Animal table. This field is not needed in any of the subclass tables (e.g. AnimalCat) as the rows being inserted for both tables should be done at the same time within a transaction.
A LastModifiedDate field would be added to the Animal table and all subclass tables. This field gets updated only if that particular table is updated: if an update occurs in AnimalCat but not in Animal for a particular AnimalID, then only the LastModifiedDate field in AnimalCat would be set.


What I am about to show you comes from lines 89-122 of the mysqltuner.pl code:

You will still have to connect like a mysql client:

Connection and Authentication
    --host <hostname>    Connect to a remote host to perform tests (default: localhost)
    --socket <socket>    Use a different socket for a local connection
    --port <port>        Port to use for connection (default: 3306)
    --user <username>    Username to use for authentication
    --pass <password>    Password to use for authentication

Since you are using a remote host, use parameters to supply values from the OS

--forcemem <size>    Amount of RAM installed in megabytes
--forceswap <size>   Amount of swap memory configured in megabytes

You may have to contact your remote SysAdmin to ask how much RAM and swap you have

If the database has too many tables, or very large table, use this:

--skipsize           Don't enumerate tables and their types/sizes (default: on)
                     (Recommended for servers with many tables)


Return table with separate columns from function

To get decomposed, separate columns, call the table function with:

SELECT * FROM test_function();

But you also have sneaky naming conflicts. The column names listed in the RETURNS TABLE clause are implicit OUT parameters, visible in every SQL statement in the function body. Avoid using names that conflict with column names and/or table-qualify all possibly conflicting table column names in the function body. Like:

CREATE OR REPLACE FUNCTION test_function()
  RETURNS TABLE (id int, organization_id int, email_address varchar) AS
$func$
SELECT u.id, u.organization_id, u.email_address FROM users u;
$func$ LANGUAGE SQL;

See: Variable Substitution in the manual.


There is at least two ways to do this. One with a derived table, and one with a CTE. These two queries assume there aren't two accounts with the same score. If that's a possibility, we can use another method to get the last ID, or pick a random one.

;select
    t.ID
    ,t2.Account
    ,t2.Score
from
    YourTable t
inner join
    (select Account, Score = max(Score)
    from YourTable
    group by Account) t2 on t2.Account = t.Account and t2.Score = t.Score;

;with cte as(
    select RN = row_number() over (partition by Account order by Score desc, id)
            *,
    from YourTable)

select
    ID,
    Account,
    Score
from cte
where RN = 1;


Why does auto increment jumps by more than the number of rows inserted?

This is not unusual and there are a couple of causes. Sometimes it is due to optimisations the query runner makes to reduce contention issues with the counter resource, improving efficiency when there are concurrent updates to the affected table. Sometimes it is due to transactions that got explicitly rolled back (or implicitly rolled back due to encountering an error).

The only guarantees from an auto_increment column (or IDENTITY in MSSQL, and the other names the concept goes by) is that each value will be unique and never smaller than a previous one: so you can rely on the values for ordering but you can not rely on them not to have gaps.

If you need the column's values to have no gaps at all you will need to manage the values yourself, either in another layer of business logic or in the DB via a trigger (be careful of potential performance issues with triggers though), of course if you do roll your own you will have to contend with all the concurrency/rollback/cleanup-after-delete/other issues that the DB engines work around by allowing gaps).


Using EXCEPT could help you write this query.

In short, if any rows of the first select do not have matches with the inverse + the correct RelType  & RelStatus  , return them.

SELECT PartyId , OtherPartyId, RelType , RelStatus
FROM dbo.Table
EXCEPT
SELECT OtherPartyId , PartyId, RelType , RelStatus
FROM dbo.Table;

DB<>Fiddle

Remember that if there are duplicate rows (E.G. 3 rows with an inverse relationship) these will not be returned.

Example

DDL

CREATE TABLE dbo.bla(ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
                     PartyId int,
                     OtherPartyId int,
                     RelType int,
                     RelStatus int);

DML

INSERT INTO dbo.Bla(PartyId , OtherPartyId , RelType , RelStatus)
VALUES
(1111,2211           ,1       ,1),
(2211,1111           ,1       ,1),
(3344,4444           ,1       ,2),
(5555,2224           ,1       ,2),
(4444,3344           ,2      ,2),
(1111,2211           ,2      ,2);-- different type or status

Query

SELECT PartyId , OtherPartyId, RelType , RelStatus
FROM dbo.Bla
EXCEPT
SELECT OtherPartyId , PartyId, RelType , RelStatus
FROM dbo.Bla;

Result

PartyId OtherPartyId    RelType RelStatus
1111    2211            2         2
3344    4444            1         2
4444    3344            2         2
5555    2224            1         2

Another solution could be using NOT EXISTS, E.G. When you also need the Id field.

SELECT Id,PartyId , OtherPartyId, RelType , RelStatus
FROM dbo.Bla b
WHERE NOT EXISTS
(
SELECT *
FROM dbo.Bla b2
where b.OtherPartyId = b2.PartyId
AND   b.PartyId = b2.OtherPartyId
AND b.RelType = b2.RelType
AND b.RelStatus = b2.RelStatus
);

DB<>Fiddle


Some view to find out which jobs are in the queue to be executed?

For Oracle versions prior to 19c you can check the DBA_JOBS view for job created with the DBMS_JOBS package. For jobs created using DBMS_JOBS in Oracle 19c and later, or for jobs created with the DBMS_SCHEDULER in any version, check the DBA_SCHEDULER_JOBS view.


Answers to your questions are as below:

1) Will it mess up the sync in any way

Answer: Yes it will mess up your replication process as slave reads from relay log and updates on Server.
For example: If a row has been added to slave and which is not there in Master then "INSERT INTO ..." query will fail which can cause replication process being stopped.

2) Will the changes get overwritten from the master during the next replication event, Can I make above (2) option happen ?

Answer: NO. but there is a alternative you can use pt-table-sync tool

This tool changes data, so for maximum safety, you should back up your data before using it. When synchronizing a server that is a replication slave with the --replicate or --sync-to-master methods, it always makes the changes on the replication master, never the replication slave directly. This is in general the only safe way to bring a replica back in sync with its master;

Note: It is a good idea to set slaves as read_only
This variable may help prevent some unsafe writes from occurring on slaves, but only if users are not needlessly granted the SUPER privilege.


If you have a consistent connection to the external source then setting up replication would be the way to go.  This way you ensure that all changes are automatically sent to the 'backup' (aka slave) server.  You could still run a regular backup script to dump the data to an external HD as a failsafe in case someone 'drops' all the tables int the database.  The book High Performance MySQL is a good source of information on replication setups.


Query to polybase external table giving error "Access to the remote server is denied because no login-mapping exists."

I don't know if this is directly related to your issue but the known limitations on PolyBase state that:

In order to use PolyBase, you must have sysadmin or CONTROL SERVER
level permissions on the database

As such you might try to:
GRANT CONTROL SERVER TO [LoginName]

However know that CONTROL SERVER gives you many rights on the instance such as the right to initiate a shut down.


How should I Optimize storage for this table?

Since it is built already, let's look at what you have. There is an interesting way to plan columns definitions for data currently present. If the table name is mydata, try running this query:

SELECT data FROM mydata PROCEDURE ANALYSE();

This will not display any of your data. This will examine the column data itself, calculate statistics based on the first 256 distinct values (by default, you can specify the distinct value count) and recommend the proper column type the table should have.

As long as the table is MyISAM, don't worry too much about the rows size because the default row format is DYNAMIC. If the table is InnoDB (and I sure hope it is not), please make sure the data is not part of the PRIMARY KEY. Your clustered index will grow like pouring undiluted MiracleGro in your backyard.

You may need to break up the data column into chunks of 128 and run either MD5 (32 character output) or SHA1 (40 character output) and concatenate those MD5 or SHA1 outputs and store them. That would save at 75% of storage. Have fun trying to code that breakup. This was just a rambling suggestion off the top of my head.

You may want to consider using Sphinx Indexing as well.


Mysql federated engine table creation error

OBSERVATION #1
According to the Book

Page 434 shows an example using COMMENT to establish a federated connection. Unfortunately, the book was written when MySQL 5.0.13 was released. Now, you use CONNECTION instead of COMMENT,
Here are my past answers (over a year ago) where I use CONNECTION instead of COMMENT:

Jun 28, 2012 : Connecting remote MySQL database to local MySQL database?
Jan 04, 2012 : fetching externally hosted db's table from within the local mysql server

OBSERVATION #2
You have this:
CREATE TABLE fedTransfer( `fedID` INT NOT NULL AUTO_INCREMENT, `transferID` INT, `outletFromID` INT, `employeeToID` INT, `transferStatus` ENUM('y'), `transferToDate` DATE, `transferToTime` TIME, PRIMARY KEY (`fedID`) )
ENGINE=FEDERATED;
COMMENT='mysql://root:passw1@192.168.2.103/mptest/fedTransfer';

Remove the semicolon from the back of the word FEDERATED
EPILOGUE
Your code should look like this:
CREATE TABLE fedTransfer( `fedID` INT NOT NULL AUTO_INCREMENT, `transferID` INT, `outletFromID` INT, `employeeToID` INT, `transferStatus` ENUM('y'), `transferToDate` DATE, `transferToTime` TIME, PRIMARY KEY (`fedID`) )
ENGINE=FEDERATED
CONNECTION='mysql://root:passw1@192.168.2.103/mptest/fedTransfer';

Give it a Try !!!


Optimize finding events with overbooked attendees

The query itself looks pretty much the way to do it. As for indexing strategies I suggest that you drop indexes:

KEY `start_datetime_idx` (`start_datetime`) -- covered by start_end_datetime_idx

KEY `IDX_E8625CBD71F7E88B` (`event_id`) -- covered by PRIMARY

and replace:

KEY `IDX_E8625CBDA76ED395` (`user_id`),

with:

KEY `IDX_E8625CBDA76ED395` (`user_id`, event_id)


Duplicate error when update column in the same table

It appears that WorkingShiftID is the primary key of the DutyRoster table, and that you're trying to set this to the same value in all rows:

UPDATE DutyRoster SET WorkingShiftID = workshfid;

Presumably, the problem is you forgot to include a WHERE clause in that statement, so the update could be restricted to a single row.


Can We Perform an "Up-To-The-Minute Restore" After Taking Transaction Log Backups?

Shrinking your transaction log should not be part of your routine. A transaction log backup clears the log file automatically; shrinking it afterward just causes it to have to grow again afterward. Read this for more information.

Yes, you should be able to perform point-in-time restores using your latest full backup and any subsequent transaction log backups. Anything more recent than the latest transaction log backup will require that the server still be operational enough to take a tail-log backup

The critical point is to test it. Backups are not reliable until you know from experience that you can restore the data successfully. This article and the links at the bottom of that article have some good information about SQL backup strategies.


I am trying to figure out how to get the proper results from a MySQL database

This is rather simple:

SELECT i.pn1 AS pn,
       CASE WHEN i.pn1 = i.pn2 THEN 'master' ELSE 'item' END AS type
FROM items AS i
  JOIN items AS s
    ON s.pn2 = i.pn2
WHERE s.pn1 = @searhed_PN ;


How to compare across rows in large table?

You could get the last word by first using reverse() to reverse it. So the last space becomes the first, so that the right one is taken even if there is more than one space in the name. Find the position of that space with locate(). With a substring() from the first character to the found position of the space minus one you'll get the last word reversed. Reverse this and have your last word.

reverse(substring(reverse(manager.name), 1, locate(' ', reverse(manager.name)) - 1))

For comparison we it actually doesn't matter if the word is reversed or not and if the space is still included. So we can simplify it to:

substring(reverse(manager.name), 1, locate(' ', reverse(manager.name)))

But we have to take care that at least one space is included as otherwise we'd do a substring(..., 1, 0) (locate() returns 0 if no match was found) which will result in the empty string. So two names without a space would match even if they were different, as the empty string is equal to the empty string. So we additionally need to check if:

locate(' ', manager.name) > 0

The order of the characters doesn't matter here, so we don't need to use reverse().

So you could use something like:

SELECT manager.name,
       store.zip,
       txn.date
       FROM transactions_table txn
            INNER JOIN stores_table store
                       ON txno.zip = store.zip
            INNER JOIN (SELECT *
                               FROM manager_table managero
                               WHERE NOT EXISTS (SELECT *
                                                        FROM manager_table manageri
                                                        WHERE AND locate(' ', managero.name) > 0
                                                              AND locate(' ', manageri.name) > 0
                                                              AND manageri.name <> managero.name
                                                              AND manageri.brand_id = managero.brand_id
                                                              AND substring(reverse(manageri.name), 1, locate(' ', reverse(manageri.name)) - 1) = substring(reverse(managero.name), 1, locate(' ', reverse(managero.name)) - 1)) manager
                       ON storeo.brand_id = managero.brand_id;

It will only join the set of managers, where no other manager with the same last word in name and the same brand_id exists. However note, that this fails to exclude managers without a space in their names, even if the names are equal. That is due to the fact, that you didn't reveal if there is any id column in manager_table. Only name seems to be unique. So there is no way to tell if the inner row is the same as the outer row without checking for the whole name. If there is and id column (e.g. named id) you can rewrite it to:

SELECT manager.name,
       store.zip,
       txn.date
       FROM transactions_table txn
            INNER JOIN stores_table store
                       ON txno.zip = store.zip
            INNER JOIN (SELECT *
                               FROM manager_table managero
                               WHERE NOT EXISTS (SELECT *
                                                        FROM manager_table manageri
                                                        WHERE manageri.id <> managero.id
                                                              AND (locate(' ', managero.name) > 0
                                                                   AND locate(' ', manageri.name) > 0
                                                                   AND manageri.brand_id = managero.brand_id
                                                                   AND substring(reverse(manageri.name), 1, locate(' ', reverse(manageri.name)) - 1) = substring(reverse(managero.name), 1, locate(' ', reverse(managero.name)) - 1)) manager
                                                                    OR manageri.name = managero.name)
                       ON storeo.brand_id = managero.brand_id;

That will also find duplicate managers with the same name, without a space in it.

Note: I assumed you're not already using MySQL 8.0. Version 8.0 introduced regexp_replace and regexp_substring providing possible easier alternatives to the string reversing.


SQL Server: Effect of Setting These Index Options

The article linked to in the comments makes it clear, but let's prove it with an example.

CREATE TABLE t1(a int);
INSERT INTO t1(a) VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9);

CREATE CLUSTERED INDEX IX_t1
    ON t1(a)
        WITH(ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON); /* Default */

BEGIN TRANSACTION;

    UPDATE t1
        SET a = 10
        WHERE a = 1;

    EXEC sp_lock;

ROLLBACK;

DROP TABLE t1;

Results (filtered to SPID and table of interest):

spid   dbid    ObjId       IndId   Type    Resource        Mode    Status
52      6       213575799   1       PAG     1:118           IX      GRANT
52      6       213575799   1       KEY     (de42f79bc795)   X      GRANT
52      6       213575799   1       KEY     (241332e1ddb0)   X      GRANT
52      6       213575799   0       TAB                     IX      GRANT <--
52      6       0           0       DB                       S      GRANT

(There are two KEY locks because SQL Server needs to protect both the "before" and "after" index values. If you want to see this, run SELECT a, %%lockres%% FROM t1 before and after the UPDATE statement.)

If I run the same script again with the clustered index definition having ALLOW_ROW_LOCKS = OFF and ALLOW_PAGE_LOCKS = OFF, I get this:

spid   dbid         ObjId   IndId  Type    Resource    Mode    Status
52      6               0       0   DB                    S     GRANT
52      6       229575856       0   TAB                   X     GRANT <--

So it should be pretty obvious now why you suddenly started having deadlocks!


I can't point to the cause but I believe the default rows-per-batch for a BULK INSERT operation is "all".  Setting a limit in rows could make the operation more digestible: that's why it's an option.  (Here and going on, I'm looking at the Transact-SQL "BULK INSERT" documentation, so it could be way off for SSIS.)

It'll have the effect of splitting the operation into multiple batches of X rows, each operating as a separate transaction.  If there's an error, the batches that finished will remain committed into the destination table, and the batch that was stopped will rollback.  If that's tolerable in what you're doing, i.e. you can re-run it later and catch up, then, try that.

It's not wrong to have a partition function that puts all current inserts into one table partition, but I don't see how it's useful to partition at all with partitions in the same filegroup.  And using datetime is poor, and actually kind of broken for datetime and 'YYYY-MM-DD' without explicit CONVERT formula since SQL Server 2008 (SQL may cheerfully treat this as YYYY-DD-MM: not kidding: don't panic, just change it to 'YYYYMMDD', fixed: or CONVERT(datetime, 'YYYY-MM-DDT00:00:00', 126), I think is it).  But I think using a proxy for date value (year as int, or year + quarter) to partition on will work better.

Maybe it's a design copied from elsewhere, or duplicated across several datamarts.  If this -is- a true datamart, a dump from the data warehouse to give department managers some data to play with, that isn't (by you) being sent on elsewhere, and probably read-only as far as data users are concerned, then, it seems to me that you could remove the partition function -or- change it to explicitly put all new data into the fourth partition no matter what, and no one would care.  (Perhaps you should check that no one cares.)

It feels like a design where the plan is to drop the contents of partition 1 some time in the future and create another new partition for more new data, but it doesn't sound like that's happening here.  At least it hasn't happened since 2013.


All recent changes in MySQL have been automatically removed. Recently INSERTed or UPDATEd data is not available

set autocommit=OFF
Do lots of INSERTs/UPDATEs/DELETEs
disconnect

Step 3 will do an implicit ROLLBACK since there was no COMMIT.  And this is why I advise against ever using autocommit=OFF.
And, yes, the AUTO_INCREMENT would not be reset.  That's just one of the quirks of auto_inc.  (Actually, it is an efficiency measure.)
So... either
SET autocommit=ON;

and leave it that way.  Check the configuration, too.  Or use transactions:
BEGIN;
inserts, etc
COMMIT;


It's not recommended to use analyze to gather statistics (from 8i onward) because CBO(cost base optimizer) is build to recognize statistics that have been gathered using dbms_stats. So dbms_stats is a preferable way to gather statistics.


Oracle uses a case-sensitive catalog, and hides that ugly fact from you by silently converting non-quoted identifiers to all caps, both in DDL and DML.

The name of your table is actually RS.RS_MYAPP, and SQL Server may be sending the identifier to Oracle as "rs"."rs_myapp", which would fail at Oracle.

So try

select * from [rstest-Link]..RS.RS_MYAPP


How to resolve error ORA-19802 when starting up an Oracle database instance?

Answer here: https://community.oracle.com/thread/2203255

reference answer:

You can always export, edit and import a pfile, even when the instance won't start like this:

SQL> create pfile = '/tmp/pfile' from spfile='+DATA/rac/spfilerac.ora';

edit the pfile with the text editor of your choice and import back:

SQL> create spfile='+DATA/rac/spfilerac.ora' from pfile='/tmp/pfile';**


MySQL - How to optimize multiple OR conditions in a query on a large table

OR is hard to optimize, especially 1000 of them.
See if you can through the values in a table, then JOIN to that table.
Then provide EXPLAIN SELECT ....


Assuming your table is ENGINE=InnoDB, here are some tips:

Set innodb_ft_min_token_size = 2; I don't think it is 'bad' idea.  Note:  If you are changing this value, do so in the config file, restart MySQL, then rebuild any FT indexes.  (Otherwise, the "2" won't take effect.)
Programmatically add a + in front of each word, and add IN BOOLEAN MODE.
Programmatically:  Either remove 1-character 'words' from the search string, or at least don't add a + in front of them.

Here's a trick that is useful sometimes:

WHERE MATCH(col) AGAINST('+css 3' IN BOOLEAN MODE)
  AND col LIKE '%css 3%'

The MATCH will happen first (even if it is not first in the WHERE).  It will find rows with the word "css", but ignore the "3".  Then the LIKE will double check that there is exactly "css 3" somewhere in the string.  Even this is imperfect, since "css 31" will be found.


If I want to move record 0 to the start, I have to reorder every record

No, there's a simpler way.
update your_table
set order = -1
where id = 0;

If I want to insert a new record in the middle, I have to reorder every record after it

That's true, unless you use a data type that supports "between" values. Float and numeric types allow you to update a value to, say, 2.5. But varchar(n) works, too.  (Think 'a', 'b', 'c'; then think 'ba', 'bb', 'bc'.)

If I want to remove a record, I have to reorder every record after it

No, there's a simpler way. Just delete the row. The remaining rows will still sort correctly.

It's easy to imagine a situations like:
Two records have the same order

A unique constraint can prevent that.

There are gaps in the order between records

Gaps have no effect on how a dbms sorts values in a column.

Some people have proposed using a decimal to store order, so that you can use "2.5" to insert a record in between the records at order 2 and 3. And while that helps a little, it's arguably even messier because you can end up with weird decimals (where do you stop? 2.75? 2.875? 2.8125?)

You don't stop until you have to. The dbms has no problem sorting values that have 2, 7, or 15 places after the decimal point.
I think your real problem is that you'd like to see values in sorted order as integers. You can do that.
create table your_table (
  id int primary key,
  title varchar(13),
  sort_order float
);

insert into your_table values
(0, 'Lorem ipsum', 2.0),
(1, 'Dolor sit', 1.5),
(2, 'Amet, consect', 0.0),
(3, 'Elit fusce', 1.0);

-- This windowing function will "transform" the floats into sorted integers.
select id, title,
       row_number() over (order by sort_order)
from your_table


Update postgres row where condition is from another table

I believe this can be solved relatively quickly, by adding a FROM clause to your UPDATE, and adding an additional equality condition, namely, adding user_info.user_login = user_contacts.user_login.

UPDATE user_info SET password = '$2a$11$fwea...IEI'
FROM user_contacts
WHERE user_info.user_login = user_contacts.user_login
AND user_contacts.email = 'ad@...com';

This will almost certainly do what you are asking.

Refer to the PostgreSQL Documentation on UPDATE for more info.


If you want to get the names of the friends for a user perhaps try something like so:

SELECT u.firstname, u.surname
FROM friend f, user u
WHERE (f.Person1 = 1 OR f.Person2 = 1)

This should get a back a list of names in your user table (providing that is what you have named your friends in the user table) that are friends with userID 1

I'm guessing your friend table contains the foreign key IDs of the friends making up the partnership?


Can I rely on the MSDTC to guarantee a distributed transaction was completed?

Microsoft's DTC developer documentation says

  The application programmer's transaction model is simple—programs either succeed or fail. .. When the program reaches a consistent state, it calls the Commit method. If the commit succeeds, the transaction is durably committed. If the commit fails, the transaction aborts.

The Administration Guide offers some more under-the covers information

  While a component remains prepared but not committed or aborted, it is in doubt about whether the transaction committed or aborted. If a component or transaction manager fails, it reconciles in-doubt transactions when it reconnects.

So, in the event of a failure a resource (e.g. a database) may be "in doubt". When that database can talk to the cluster again DTC works things out on behalf of the application. If the resource never re-joins the cluster then who cares - that resource is gone and whether it committed or rolled back is neither here nor there.

If someone, somehow manages to talk to that disconnected resource independently of the application or cluster then it may report an incongruous state. It has, after all, been severed from a cluster it expects to be part of.


Single collection with a key vs multiple collections

Answer is single collection at this case.


Query logical processing phases while mixing old and new join syntax

It is to do with the precedence of the comma operator. As stated in the MySQL docs

  the precedence of the comma operator is less than of INNER JOIN, CROSS
  JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join
  types when there is a join condition, an error of the form Unknown
  column 'col_name' in 'on clause' may occur

You get the same results with the rewrite below (where the position of the ON clause makes clear the logical order of join operations)

Fails

SELECT *
FROM   table1 a
       INNER JOIN (table2 b
                   INNER JOIN table3 c
                     ON c.a_id = a.id ) -- error - a.id - invalid identifier
         ON a.id = b.a_id

Succeeds

SELECT *
FROM   table2 b
       INNER JOIN (table1 a
                   INNER JOIN table3 c
                     ON c.a_id = a.id )
         ON a.id = b.a_id

In the one that succeeds a is first joined to c on c.a_id = a.id then the virtual table resulting from that join is joined to b on a.id = b.a_id which all works fine.

For the one that fails b is first attempted to be joined to c on c.a_id = a.id which is invalid as that references table a. The ON clause can only refer to the table immediately preceding it or one already brought in by a previous ON clause.

From the conditions c.a_id = a.id and a.id = b.a_id then c.a_id=b.a_id so you could change the join condition to that and it would work or you could of course also use a derived table or view to alter the precedence and ensure a,b were treated as a logically joined unit before joining onto c

SELECT *
FROM   (SELECT *
        FROM   table2 b,
               table1 a) ba
       INNER JOIN table3 c
         ON ( c.a_id = ba.id )
WHERE  ( ba.id = ba.a_id )