Why is the value of NUMERIC_SCALE column in INFORMATION_SCHEMA.COLUMNS table for DATA_TYPE='double' NULL?

Floating point numbers are exactly that -- there's no fixed position for the decimal separator (point), it can be anywhere, and specifying scale (i.e. the number of digits after the decimal separator) does not make sense.

Scale is used for the DECIMAL data type, a type with the fixed decimal separator, for which scale is significant and meaningful.

MySQL also allows a non-standard syntax for floating point numbers, where you can specify precision and scale, e.g. DOUBLE(10, 5). MySQL will round floating point values to that specification when storing them, and format them appropriately when returning query results.

Documentation link.

dbfiddle example.


Why to put unstructured data in NoSQL DBs?

Check the comparison where NoSQL has better results.

NoSQL databases can store structured, semi-structured and unstructured data. Their main advantages focus on semi-structured (JSON, XML, not all fields are known) and unstructured.

But, you can safely store BLOB in a RDBMS, e. g., Oracle Database and many others relational databases.

  The people talking about NoSQL be a better sollution to store
  unstructured data, not always had built an IT infrastructure, and
  they're replaying what they heard.

  Also the trend to think that newer is always better push to this.

But some scenarios NoSQL+unstructured data could be better: to scale out, hundreds of db instances, big data and cloud.


You are only ordering by one column. If there is a tie, you haven't told SQL Server how you want those rows with the same value of that column to be sorted, so they can be returned in any order SQL Server likes (why would it sort by FSONUM and not some other column, when you haven't indicated that that's a column whose order you care about?). And yes this can change between executions.

If you order by manifestDate and expect all of the values for FSONUM to be sorted when the date column has the same value, then just hard-code FSONUM as the second order by:

SET @sort = N', FSONUM';

This is okay to do even when FSONUM is also the first choice.

For a lot more on dynamic sorting, see Conditional Order By.


Select MaxDate where hh:mm:ss makes the difference

In the OPENQUERY, you could get the max timestamp for all the dogs at once, and then join to the original PETS table to get the other columns, like so:

SELECT *
FROM [MyDatabase].[dbo].[DogTags] t1
JOIN OPENQUERY(ROPSA,
    'SELECT [TimeStamp], [Name], [Owner], [Address]
     FROM [CustomerDB].[PETS] t3
     JOIN (SELECT [Name], MAX(TimeStamp) AS TimeStamp
           FROM [CustomerDB].[PETS]
           GROUP BY [Name]) t4
     ON t3.[Name]=t4.[Name] AND t3.TimeStamp=t4.TimeStamp') t2
ON t1.TAGS collate DATABASE_DEFAULT = t2.Name collate DATABASE_DEFAULT

EDIT: Needed to add AS TimeStamp in the subquery.

EDIT: Needed to specify the database everytime in OPENQUERY.


how to using group concat distinct count in MySQL 5.7

There's no direct way to do this with one operator, but you can use the following trick to accomplish it:
select group_concat(DISTINCT user_id SEPARATOR ','),
    LENGTH(group_concat(DISTINCT user_id SEPARATOR ',')) - LENGTH(REPLACE(group_concat(DISTINCT user_id SEPARATOR ','), ',', '')) + 1 AS IdCount
from report_summary
where statistic_time >= 1615824000000

Basically it counts the number of commas (+ 1) in the list by subtracting the length of the string after removing the commas from the original string's length. I thought it was pretty nifty and found it in this StackOverflow answer.


How can I disable all unique index and constraints in Oracle?

Community Wiki answer generated from question comments by Justin Cave

You can use user_indexes and user_constraints (or all_indexes and all_constraints or dba_indexes and dba_constraints) to iterate through all the indexes and constraints that you own/ have access to/ exist in the database.

I can't imagine that you'd really want to try to disable every constraint in the database.  Maybe all those in your schema.  More likely, you want to disable those that are on a particular set of tables.  As you iterate through them, you could construct the appropriate DDL statement to do whatever you want.

You can't disable an index (well, other than a function-based index).  You could make the index unusable but that's unlikely to be what you want.  You can drop the index but that presumably implies that you're going to save off the definition of the index so you can re-create it.


Why can't I use NEWSEQUENTIALID() as the default value for my column?

Use CREATE TABLE, not the GUI.

This is a bug in SSMS, already reported here as well as here on Connect.


I have used the MVC Mini Profiler as part of an application to profile SQL Azure but depending on your application it may or may not work. What kind of tasks are you doing that need profiling?


Let's say you want to update id 27
Run this first
SELECT REPLACE(content,'http://www.abc.com/data/img','http://www.def.com/data/img') newurl
FROM Content_T1 WHERE id = 27;

If the result is correct, you can run
UPDATE Content_T1
SET content = REPLACE(content,'http://www.abc.com/data/img','http://www.def.com/data/img')
WHERE id = 27;
SELECT content FROM Content_T1 WHERE id = 27;

If the result is correct and you are ready to fix all of them, run this query without the WHERE clause:
UPDATE Content_T1
SET content = REPLACE(content,'http://www.abc.com/data/img','http://www.def.com/data/img');

Please, go to a test server and run this. When the results are correct ...
GIVE IT A TRY !!!


Trun a query result to argument of EXEC sp_executesql

It wasn't problem with NVARCHAR(MAX) limit for sure. 120 tables isn't a lot and NVARCHAR(MAX) limit is a way above what is needed here (up to 2 GB data), more information link.

I've just checked this solution and it works for me (399 tables).

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql += 'SELECT Top 1 [table] = '''
            + REPLACE(name, '''','')
            + ''', 1 FROM '
            + QUOTENAME(SCHEMA_NAME([schema_id]))
            + '.'
            + QUOTENAME(name) + ';'
FROM     sys.tables AS t
ORDER BY name;

EXEC sp_executesql @sql

If you want to see the whole content of NVARCHAR(MAX) value in SSMS (SELECT and PRINT have limits), you could do something like that:

SELECT @sql AS [processing-instruction(x)]
FOR XML PATH('')

As suggested by @Dan Guzman, more reliable approach would be:

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = STUFF((SELECT 'SELECT Top 1 [table] = '''
                            + REPLACE(name, '''','')
                            + ''', 1 FROM '
                            + QUOTENAME(SCHEMA_NAME([schema_id]))
                            + '.'
                            + QUOTENAME(name) + ';'
                     FROM sys.tables AS t
                     FOR XML PATH(''), TYPE).value(N'.[1]', N'varchar(max)'), 1, 0, '');

EXEC sp_executesql @sql


In order to get advantage of the SPATIAL index you have to filter results using a bounding box (or any other geometry type) with the functions st_within() or st_contains(). You have some examples here:

Query Optimization with MySQL 5.7 and MariaDB 10 - GIS Improvements
Using the new spatial functions in MySQL 5.6 for geo-enabled applications
MySQL 5.7 and GIS, an Example


query slows down exponentially as table grows

The compound INDEX(field2, field1) would be a "covering" index.  This would avoid a huge number of lookups, thereby making it much faster.  Note:  This will not work if you also SELECT field3.  (InnoDB and MyISAM would both benefit in this case.)

Keep your UNIQUE(field2) because of the uniqueness constraint.


More efficient to do SELECT and compare in Java or DELETE and INSERT

Neither of your proposals can be considered efficient. MERGE was introduced a long time ago (SQL99 or SQL2003) and will outperform both of your alternatives:

MERGE INTO T as x
USING (
    VALUES (?, ...., ?)
) as y (c1, c2, ...,cn)
    ON x.<key> = y.<key>
WHEN MATCHED AND (x.c1, x.c2, x....) <> (y.c1,y.c2, y...) THEN
    UPDATE (...)
    VALUES (y..)
WHEN NOT MATCHED THEN
    INSERT (...)
    VALUES (...)


CTE part returns stores and dates that have not sold this products '001,002,003', the second part returns only those that have not sold any of them.

NOTE: COUNT(*) = 3 because you're looking for 3 products.

WITH ct AS
(
  SELECT
    store_id,
    dt,
    product_code
  FROM
    DAILY_SALES_RECORDS
  WHERE
    product_code IN ('001', '002', '003')
  GROUP BY
    store_id,
    dt,
    product_code
  HAVING
    SUM(sales) = 0
)
SELECT
  store_id,
  dt
FROM
  ct
GROUP BY
  store_id,
  dt
HAVING
  COUNT(*) = 3;

store_id | dt
-------: | :------------------
       1 | 01/01/2019 00:00:00
       3 | 01/01/2019 00:00:00
       1 | 03/01/2019 00:00:00

db<>fiddle here

As ypercube has pointed out in comment the easiest, grouping by store_id and date returning those that has sold nothing:

SELECT
    store_id,
    dt
  FROM
    DAILY_SALES_RECORDS
  WHERE
    product_code IN ('001', '002', '003')
  GROUP BY
    store_id,
    dt
  HAVING
    SUM(sales) = 0 ;


Performing deltas / diffs over VPN from Oracle to SQL Server database

In terms of getting the data into the SQL Server database, you can use that ODBC connection directly via the "linked servers" feature (see http://msdn.microsoft.com/en-us/library/ms188279.aspx) - this would allow you to transfer data directly from the remove Oracle instance down to your local SQL Server one with statements as simple as INSERT <localtable> SELECT <stuff> FROM <server>.<database>.<schema>.<table> WHERE <condition>. I've used this to pull data from both local and remote databases (over VPNs or other tunnels) and found it to be reliable as long as the connectivity between the sites is reliable. It is worth making sure that either your VPN or ODBC setup are performing stream compression: this will increase latency a little but could greatly improve transfer times as your data is most likely to be quite compressible.

Pulling down changes only is going to be something that will depend heavily on the database design.

The best case scenario is if everything is audited so you can easily know which updates have happened since you last picked up changes: check current "last audited action", pull down rows that have been added/changed between that and the last recorded action last time, merge these rows into your local tables, then record the ID/timestamp of the last audited action for next time. The reason to check the audit id/stamp only once at the beginning rather than for each table is to help consistency: you don't have more uptodate data in the tables you transfer last than you do in those you check first. Any other audit/history recording format can be used of course (records holding a "last modified date" and so forth).

If the data has no obvious audit structure like this then things are going to be more convoluted - comparing data in large tables between the two sites is not going to be very efficient. You can reduce the amount of data being transferred by pulling down from each table the primary key column(s) and a hash of all the others, compare the hashes with those generated locally, then request the rows that don't watch locally. You are still going to be transferring something for every row here though (for an UUID PK and a SHA1 hash that is 36 bytes/row plus any padding if transferred in binary form, and it is unlikely to be easily compressible so the VPN can speed it up that way) and if the table contains thinner rows on average than the length of the hash you should transfer the lot as you'll end up doubling (or worse) the transfer with the initial hashes.

You might find you have to use a mix of techniques (updates from audit, checksums for wide tables with no audit, just plain complete transfer for thin tables with no audit) if some of your data is audited and some not.

Another option you might be able to consider (though it requires cooperation from the other end to implement, and may have cost implications as you'll need a local Oracle license) is to use Oracle's equivalent of SQL Server's log shipping method to keep a copy up-to-date on an Oracle instance locally (and move the data from there to SQL Server or Access if that is an explicit requirement rather than just a convenience because that is what you already have installed locally). It is unlikely it that this is possible as an extra for you as it may interfere with the database's primary backup strategy, but if a log shipping equivalent (or differential backups) is being used already for the DB's backup plan then perhaps you could "piggy back" onto the arrangement by having the logs or differential backups sent to you as well as the backup infrastructure. This way the DBMS handles only pushing changes to you (though may push more than by other methods if the same rows are updated many times in the time covered by a given log segment, as you'll get the log of each change to replay not just the final result).

What you are essentially doing with any of the above is emulating replication, so if you have any say on infrastructure or replication is already in use then using that may be an option too.


online Oracle database migration to different Oracle database release

1) impdp with network link doest not need the production database to be stopped. This is the slowest method, as this needs to build everything from scratch, I am not sure this would finish in 4 hours.

2) RMAN duplicate fails between different versions because it tries to automatically open the database, and that will fail. But you can manually restore the database on the new host from the RMAN backups, then open it with alter database open resetlogs upgrade; and run the upgrade scripts immediately. With this method, you need downtime only to upgrade the database on the new host, should not take more than 30-60 minutes.

3) Streams does not require any license. You can just simply replicate the database, and start using the new database whenever you want to. This requires the least downtime, but the configuration and administration is cumbersome compared to other methods.


There is no elegant way to proceed. If the hierarchy has an unknown amount of levels, you can't serialize it into fixed relations. But partitioning it is probably a bad idea. Perhaps you want a Graph Database?


To answer this, I did the following.

mysql> CREATE TABLE myid(id INT, myname VARCHAR(10));
Query OK, 0 rows affected (0.35 sec)

mysql> INSERT INTO myid VALUES (1, 'Paul');
Query OK, 1 row affected (0.06 sec)
mysql> INSERT INTO myid VALUES (2, 'Peter');
Query OK, 1 row affected (0.07 sec)

mysql> CREATE TABLE myrating(ratename VARCHAR(10), therating INT);
Query OK, 0 rows affected (0.43 sec)

mysql> INSERT INTO myrating VALUES('Paul', 3);
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO myrating VALUES('Paul', 4);
Query OK, 1 row affected (0.04 sec)
mysql> INSERT INTO myrating VALUES('Peter', 6);
Query OK, 1 row affected (0.06 sec)
mysql> INSERT INTO myrating VALUES('Peter', 456);
Query OK, 1 row affected (0.06 sec)

And then performed the query

SELECT myid.myname, SUM(myrating.therating) AS The_Rating
FROM myid inner
JOIN myrating
ON myrating.ratename = myid.myname
GROUP BY myid.myname;

Obtaining the result:

+--------+------------+
| myname | The_Rating |
+--------+------------+
| Paul   |          7 |
| Peter  |        462 |
+--------+------------+
2 rows in set (0.04 sec)

Does this satisfy your requirements?


Get last modified date of table in postgresql

There is a way with Postgres 9.5 or later. Turn on track_commit_timestamp in postgresql.conf and restart the DB cluster. Starts logging commit timestamps.
Then you can get the timestamp of the latest modification (latest commit) for a given table using the function pg_xact_commit_timestamp():
SELECT pg_xact_commit_timestamp(t.xmin) AS modified_ts
FROM   my_table t
ORDER  BY modified_ts DESC NULLS LAST
LIMIT  1;

NULLS LAST is necessary while there still may be rows without recorded commit timestamp.
Related:

PostgreSQL sort by datetime asc, null first?
Row with latest timestamp
Is there way to get transaction commit timestamp in Postgres?

For Postgres 9.4 or older, see:

How to find out when data was inserted to Postgres?


How to structure Tables with changing status without duplicating data

Assuming that your Inquiry table has a ID column, you can add a column in the Appointment table which accepts NULL and is a foreign key to the Inquiry table. Like so:

CREATE TABLE Appointment
(
    Inquiry INT NULL REFERENCES Inquiry(Id)
);

This way you can lookup the additional details when you need to without duplicating data which is a key component of obtaining third-normal-form.

EDIT

Because an Appointment can be created without an Inquiry I would recommend abstracting the Services, Commodities & Invoices to a separate table which has its own key, which you can then reference from both the Inquiry and Appointment like so:

CREATE TABLE OrderDetails
(
    ID INT NOT NULL PRIMARY KEY,
    -- other columns
);
CREATE TABLE Inquiry
(
   ID INT NOT NULL PRIMARY KEY,
   OrderDetail INT NULL
       REFERENCES OrderDetails(Id)
);
CREATE TABLE Appointment
(
    ID INT NOT NULL PRIMARY KEY,
    Inquiry INT NULL REFERENCES Inquiry(Id),
    OrderDetail INT NOT NULL REFERENCES OrderDetails(Id)
);

EDIT based on Comment

As you want to ensure that the Appointment will always have the same OrderDetail as the Inquiry you could change your model to this:

CREATE TABLE Inquiry
(
    Id INT NOT NULL PRIMARY KEY,
    -- other columns
);
CREATE TABLE OrderDetails
(
    Id INT NOT NULL PRIMARY KEY,
    Inquiry INT NULL REFERENCES Inquiry(Id)
);
CREATE TABLE Appointment
(
    Id INT NOT NULL PRIMARY KEY,
    OrderDetail INT NOT NULL,
    FOREIGN KEY (OrderDetail)
      REFERENCES OrderDetails(Id)
);

Your other alternative is to implement the check within the logic layer that creates the Appointment and ensure that it only ever creates it using the same Order Detail that the Inquiry has, if the Appointment was based off an Inquiry.


This must be a dump from an old PostgreSQL database where it was still possible to generate unknown columns.

The best thing would be to fix the source database, but if you cannot do that, generate an SQL script from the dump with pg_restore and fix the column definitions there.


Should I put OLAP and realtime web DBs on the same server?

You can install both on the same instance, If the performance is an issue you can use the resource govenor to make sure that the web database gets the lion's share of the resouces.

Security wise I would reccomend to separate the two by using different instances but if you are careful in setting up security this should be no issue.


How can I override the default values for a parameter in a report?

It appears that SQL Reports actually does what I want automatically - but only if the parameter with the dependent query immediately follows the first query.

We were modifying the report for other reasons and moved the offending drop down higher up the parameter list so it was second (after the query that filled in the location drop down). Having done that, the drop down was populated automatically.

The blog post I linked to in the question mentions that parameter order is important and there's a separate post about that by the same author.


To see if the index is in use you can use sys.dm_db_index_usage_stats. Here is an example query with some description.

T-SQL Script to Correct SQL Server Full Text Indexes Not Updating by Pablo Echeverria

SELECT [t].[name] [table_name],
       [i].[name] [index_name],
       [fi].[change_tracking_state_desc],
       [fi].[has_crawl_completed],
       [fi].[crawl_type_desc],
       [fi].[crawl_end_date],
       [ius].[last_user_update],
       [ius].[last_user_seek],
       (SELECT [name]+',' FROM [sys].[fulltext_index_columns] [fc] INNER JOIN [sys].[columns] [c] ON [c].[object_id] = [fc].[object_id] AND [c].[column_id] = [fc].[column_id] WHERE [fc].[object_id] = [fi].[object_id] FOR XML PATH('')) [columns],
       (CASE WHEN [fi].[crawl_end_date] < ISNULL([ius].[last_user_update], [ius].[last_user_seek]) THEN 'ALTER FULLTEXT INDEX ON ['+[t].[name]+'] SET CHANGE_TRACKING MANUAL; ALTER FULLTEXT INDEX ON ['+[t].[name]+'] SET CHANGE_TRACKING AUTO' ELSE '' END) [Command]
FROM [sys].[fulltext_indexes] [fi]
INNER JOIN [sys].[indexes] [i] ON [i].[index_id] = [fi].[unique_index_id] AND [i].[object_id] = [fi].[object_id]
INNER JOIN [sys].[tables] [t] ON [t].[object_id] = [fi].[object_id]
LEFT JOIN [sys].[dm_db_index_usage_stats] [ius] ON [ius].[index_id] = [fi].[unique_index_id] AND [ius].[object_id] = [fi].[object_id] AND [ius].[database_id] = DB_ID()
ORDER BY [table_name], [index_name]

This article refers to more system objects regarding Full-Text Search. You can use this to find more details especially about population status.


What file formats can be restored using pg_restore on PostgreSQL?

Exactly one file format is supported by pg_restore: the custom PostgreSQL dump formats from pg_dump -F c (custom), -F d (directory) and -F t (tar). Notably it cannot restore -F s (SQL) format dumps, which must be passed to psql -f instead.

If you want to load data, you probably want the \copy command from psql or the server-backend COPY command. It can load a variety of CSV-like data.

There are also external loader and ETL tools that can do more, e.g. pgloader, Talend Studio, Pentaho Kettle, CloverETL, etc.


How do I enable both PowerShell Remoting and SPN for SQL Server Reporting?

I believe we have found the solution. To avoid Reporting Services and WinRM fighting over the HTTP SPN, you can set a port-specific SPN for the WinRM like this:

setspn -S HTTP/<Machine>:<port> <Machine>

It's a good idea to create SPNs for both short machine name and the FQDN.
The default port is 5985 for HTTP and 5986 for HTTPS, but I believe it can be set up to use different ports.

When using WinRM, I just set up a session like this:

$CimSessionOption = New-CimSessionOption -EncodePortInServicePrincipalName
$CimSession = New-CimSession -Name ServiceSession -SessionOption $CimSessionOption -ComputerName <Machine>
Get-CimInstance Win32_Service -CimSession $CimSession


Why does my table keep getting corrupted after removing large amounts of data?

Did you really mean varbinary(max)? Seems strange to have non-text data in a text-type column.
Anyhow, corruptions shouldn't occur. Your first step would be to make sure that you are up to date within the version of SQL Server you are using. I.e., (service pack and) CU.
If it persists, then I'd suspect a bug in SQL Server or that a corruption already existed before your updates. I.e., do a checkdb first and see if it is clean. If it is clean and gets corrupted by an update, then you either have some storage/machine issue (less likely if it is repeatable) or a bug in SQL server.


Unless you grant SELECT to 'public' role, you must explicitly grant access to each user, group or database role.

For simplicity, you can create a new database role, (i.e. my_users), then grant select to this role and finally add new users to the role.


TSQL to get the list of associated users from all databases for SQl server Login

You can use the following Dynamic SQL to build the query you need to hit all databases and get a list of Users:
DECLARE @LoginName AS NVARCHAR(100) = N'Your Login Name Here'
DECLARE @DynamicSQL AS NVARCHAR(MAX) = N'';

SELECT @DynamicSQL = @DynamicSQL +
    'SELECT ''' + [name] + ''' AS DatabaseName, [name] AS UserName FROM [' + [name] + '].sys.database_principals WHERE [type] IN (''S'', ''U'', ''E'', ''G'') AND [name] = ''' + @LoginName + ''' UNION ALL '
FROM sys.databases
WHERE [state] = 0; -- Online databases only

SET @DynamicSQL = LEFT(@DynamicSQL, LEN(@DynamicSQL) - 10); -- Cleans up unneeded trailing "UNION ALL"

-- PRINT @DynamicSQL -- Prints the dynamic SQL string (For Testing)
EXEC master.sys.sp_ExecuteSQL @DynamicSQL -- Executes the dynamic SQL string

I based it off the query Aaron Bertrand is using in the answer that @Learning_DBAdmin linked, but I figured re-writing my own Dynamic SQL could make it a little more readable. (And as he mentions sp_msforeachdb is undocumented and unreliable, so better to query dynamically.)
This can be used to get SQL Users, Windows Users, Azure Active Directory External Users, and Windows Groups based on the Type column in sys.database_principals (you can adjust this as needed).


Your 2 table design approach can be implemented using a view. This would not compromise your underlying database design based on 3 tables.

Views are under used but often give the best of all worlds.

Expanded:

From your "normalised" 3 table design, which looks OK, you will CREATE VIEW (SELECT Employee ID, Language Name, Proficiency FROM Employee, Languages, Employee_Languages WHERE etc. ). Your application then selects from the view.


How to convert a Postgresql ltree structure to a nested set by using VIEW?

I think there is a way around this without using a recursive query:

WITH cte AS
  ( SELECT
        m.*,
        ( SELECT COUNT(*)
          FROM KeywordLtree AS d
          WHERE m.path @> d.path
        ) AS descendants,
        ( SELECT COUNT(*)
          FROM KeywordLtree AS a
          WHERE a.path @> m.path
        ) AS ancestors,
        ROW_NUMBER() OVER (ORDER BY m.path) AS rn
    FROM KeywordLtree AS m
  )
SELECT cte.*,
       2 * rn - ancestors AS lft,
       2 * rn - ancestors + 2 * descendants - 1 AS rgt
FROM cte
ORDER BY path ;


Cannot Restore Database in SQL Server 2014

Sql server stores file locations with the restores with the general idea you'd be restoring on a similarly setup machine. But you can override this -- look at the restore UI for the database files and switch those to appropriate locations based on your setup.


RavenDB - Disaster Recovery on a Replicated Instance

Configure replication as 'Changed and Replicated'.


Controlled Transaction Blocking - Resume Behavior

Example 1: You shouldn't care. The process is much more involved than it seems, and I don't think you have any guarantee. The 3 commands go into the queue in order. The first one is pulled and executed, when the second is attempted, the engine may choose to put it back in queue if it waits for more than some threshold, and the third may be pulled and attempted. if it happens that the resources are now free, the 3rd will be executed first. if not, it may go back in queue again.

Example 2: I think the above explains it, and the answer to both your questions is no AFAIK, unless you develop an execution buffer of your own. Don't.

Rationale: Now you got to the core challenge, which should really have been the title - "How to deal with deadlocks". You assumed that your 'global lock table' solution is the only way to make things right, but I think the price of losing all concurrency is not worth it. It would be easier just to have the procedures executed serially using a scheduler of some sort. You will open a Pandora box with this approach that will be hard to close. Queuing and syncing is a very complicated challenge.

If I were in your place, I would first investigate the root cause of the deadlocks. Sometimes, something as simple as adding or modifying an index can solve it.
If you post your deadlock graphs here, along with the involved queries with their execution plans, people will help you pin point the root issue.

HTH


SQL agent job, why is the job history showing that a step is still running even though the job has completed

On the Hey Scripting Guy! blog at Technet, Chad Miller reports that if you run a Windows Powershell command as a SQL Agent job, and there are no syntax errors yet, the command produces an error (for example, attempting to get operating system information from an unavailable server). The SQL Server Agent job will report success.
Check tip #10 in the post for instructions on how to implement error handling with different options so that you can, for instance, have SQL Agent continue on error or halt the Agent job. The reported "succeeded" may not in fact have succeeded.

10-tips-for-the-sql-server-powershell-scripter


Why is ORDER BY LIMIT 1 so much faster than MAX?

It makes a lot of sense to me.
The MySQL Query Optimizer looks over the WHERE, GROUP BY and ORDER BY clauses.
Look at the first query
select sts_in
from sta_session
where sts_user_id=2006
AND sts_sessid!='0jitkt80gg3avere03tqk4lhi6'
order by sts_in desc limit 1;

Which index in sta_session has the most columns mentioned in the WHERE, GROUP BY, and ORDER BY clauses ? idx_last_login
How does MySQL locate the MAX value ?

Traverse sts_user_id in idx_last_login index for 2006
Go to the Last Index Entry for sts_in for sts_user_id 2006
Scroll backwards until sts_sessid!='0jitkt80gg3avere03tqk4lhi6'
Limit result to 1 row

Look at the second query
select max(sts_in)
from sta_session
where sts_user_id=2006
AND sts_sessid!='0jitkt80gg3avere03tqk4lhi6';

Which index in sta_session has the most columns mentioned in the WHERE, GROUP BY, and ORDER BY clauses ? Not idx_last_login, but sts_user_id.
How does MySQL locate the MAX value ?

Full Index Range Scan sts_user_id for 2006
Aggregate sts_in from the table, comparing all values of sts_sessid <> '0jitkt80gg3avere03tqk4lhi6'

POSSIBLE WORKAROUND
Since sts_user_id and idx_last_login are duplicate indexes (because they have the same first column), you should run
ALTER TABLE sta_session DROP INDEX sts_user_id;

There is a possibility for idx_last_login to be chosen and aggregate sts_in. However, there is still some traversal across the table.
If you really want to get aggressive with indexing, create this one
ALTER TABLE sta_session ADD INDEX everything_and_the_kitchen_sink_index
(sts_user_id,sts_in,sts_sessid);

This index might get selected and never have to touch the table because all columns (WHERE, ORDER BY, MAX()) are in the index.
GIVE IT A TRY !!!


What could cause a record to appear in the MySQL binary log but not the original table?

It sounds like you lack the SUPER privilege (global permission).

When you connect to MySQL, run SHOW GRANTS;

If you see more than one line, you do not have SUPER.

What does having SUPER give you ?

  The SUPER privilege enables an account to use CHANGE MASTER TO, KILL or mysqladmin kill to kill threads belonging to other accounts (you can always kill your own threads), PURGE BINARY LOGS, configuration changes using SET GLOBAL to modify global system variables, the mysqladmin debug command, enabling or disabling logging, performing updates even if the read_only system variable is enabled, starting and stopping replication on slave servers, specification of any account in the DEFINER attribute of stored programs and views, and enables you to connect (once) even if the connection limit controlled by the max_connections system variable is reached.

  To create or alter stored functions if binary logging is enabled, you may also need the SUPER privilege, as described in Section 20.7, “Binary Logging of Stored Programs”.

This also means you may only have database level grants for all users.

How can you tell ?

Login as root@localhost and run this

SELECT
    A.column_name GlobalPrivilege,
    IFNULL(B.column_name,'Not at DB Level') DBPrivilege
FROM
(SELECT column_name FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='user'
AND column_name LIKE '%priv') A
LEFT JOIN
(SELECT column_name FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='db'
AND column_name LIKE '%priv') B
USING (column_name);

You should see something like this

+------------------------+-----------------------+
| GlobalPrivilege        | DBPrivilege           |
+------------------------+-----------------------+
| Select_priv            | Select_priv           |
| Insert_priv            | Insert_priv           |
| Update_priv            | Update_priv           |
| Delete_priv            | Delete_priv           |
| Create_priv            | Create_priv           |
| Drop_priv              | Drop_priv             |
| Reload_priv            | Not at DB Level       |
| Shutdown_priv          | Not at DB Level       |
| Process_priv           | Not at DB Level       |
| File_priv              | Not at DB Level       |
| Grant_priv             | Grant_priv            |
| References_priv        | References_priv       |
| Index_priv             | Index_priv            |
| Alter_priv             | Alter_priv            |
| Show_db_priv           | Not at DB Level       |
| Super_priv             | Not at DB Level       |
| Create_tmp_table_priv  | Create_tmp_table_priv |
| Lock_tables_priv       | Lock_tables_priv      |
| Execute_priv           | Execute_priv          |
| Repl_slave_priv        | Not at DB Level       |
| Repl_client_priv       | Not at DB Level       |
| Create_view_priv       | Create_view_priv      |
| Show_view_priv         | Show_view_priv        |
| Create_routine_priv    | Create_routine_priv   |
| Alter_routine_priv     | Alter_routine_priv    |
| Create_user_priv       | Not at DB Level       |
| Event_priv             | Event_priv            |
| Trigger_priv           | Trigger_priv          |
| Create_tablespace_priv | Not at DB Level       |
+------------------------+-----------------------+

As shown, SUPER does not exist for a DB Level user.

You can run this

SELECT user,host FROM mysql.db;

to see your DB Level users.

Please also look into your DB level user having the TRIGGER privilege, which can allow a database level user to create, drop, or execute triggers.

Your real problem comes from Drupal's required settings for CiviCRM

Drupal.org : CiviCRM 4.2 and higher require more SQL privileges than past versions or binary logging disabled
CiviCRM Community Forums : MySQL Triggers and binary logging suggests giving out the TRIGGER privilege.

You can look into doing on of the following:

make all users global users and give them SUPER (not secure)
give away the TRIGGER privilege to all DB users

UPDATE mysql.db SET trigger_priv = 'Y';
FLUSH PRIVILEGES;

GIVE IT A TRY !!!


Is it possible to run Oracle 12c and AD-Server on Windows Server 2012 R2?

Once you run dcpromo to create an AD domain controller, this server differs significant from a common Windows Server as you are used to work with. In your case the most critical point is that a domain controller does not know local users and groups because it does not have a local context. But what Oracle requires, when you create a database via the Oracle Installer or oradim on Windows, is to create several local groups like ORA_DBA, ORA_OPER, etc. and also adding users to these groups. Otherwise you can not do stuff like e.g. "sqlplus / as sysdba". Knowing about what is going on in the background during Oracle database installation makes it easy to understand why you can not run Oracle on an AD domain controller because the installer can not create the local groups resulting from the fact that an AD server does not have a local context. Please find a list of the required local groups here under section 4.1.4: https://docs.oracle.com/database/121/NTDBI/user_grps.htm#NTDBI260

In case your oradim command created and started a database successfully, you resulted in a database using the LocalSystem Account (NT AUTHORITY\System) to start the database which Oracle does not recommend since Oracle 12c. It was the default until Oracle 11g but Oracle introduced the new Oracle Homer User for security reasons as requested from Oracle customers. You can still use the LocalSystem Account and in your case this has been done. In addition you have no local Oracle groups as listed under section 4.1.4 and in case you request support I have serious doubt that Oracle Support will accept your installation.

The above topics are the basic reasons why all customers who asked me in the past how to install Oracle on a domain controller finally have decided not to do so. I must add that I can not predict what kind of errors and failures might result from an installation which actually misses the required local Oracle groups.

Hope that helps.


Potential corruption in sys.sysdercv, latch errors from ghost cleanup process

UPDATE: The problem has been resolved. Took a lot of screwing around, but we were able to take the instance down, bring it back up, put the database in emergency mode, and ran a DBCC CHECKTABLE with REPAIR_ALLOW_DATA_LOSS (we were able to fix it in a test restore with REPAIR_REBUILD, but emergency mode won't let you use that option, apparently). That fixed the corruption in sys.sysdercv, and everything is back to normal.  Still not sure what ended up causing it, but backups are working again, DBCC CHECKDB has been coming back clean.  Chalking it up to "stuff happens", I guess.


Counting unique (distinct) users per day

Because your form is off, what you want is

SELECT count(DISTINCT x)
FROM generate_series(1,10) AS gs(x);

Or, in your case,

SELECT start_time::date, count(DISTINCT user_id)
FROM mytable
GROUP BY start_time::date;


MongoDB Sharded Cluster on AWS reliability

The issue was actually in using the T2 instance, we upgraded to M4 instances and it is running well


Specify each database name explicitly as a string:

SELECT
  Custnumber,
  'X' AS DatabaseName
FROM
  X.dbo.tablecust

UNION

SELECT
  Custnumber,
  'Y' AS DatabaseName
FROM
  Y.dbo.tablecust

UNION

...

There is no other way. After all, you have to explicitly specify the database name in the table references too (X.dbo.tablecust, Y.dbo.tablecust etc.).


mysqldump from the database on the source server.

Then still on the source server mysql -h[dest server hostname/IP] -u[user] -p[pass] < [location of the file you just made].


Central Management Server - can it manage itself?

I had also previously assumed that this wasn't possible at all, until I started playing around with it.

From what I can tell, the matching is only done by the server network name that's entered.

If you attempt to add a server to the tree with the same network name, it won't allow it; however, if you use a different network name, even though it may point to the same instance, it does work.

For example, I successfully registered my local instance .\SQL2008R2DEV as a CMS, and then successfully registered (local)\SQL2008R2DEV in the tree. (Tested in 2012 Management Studio.)

From a management perspective, this may or may not be a good idea. Furthermore, if Microsoft decides to tighten down the policy, any reliance on this ability will force you to change your process.

Certainly if SQL1 would be a production instance in your case, this is a bad idea because you want to separate management duties from serving user data duties. I strongly recommend using a separate instance of SQL Server just for CMS purposes, particularly in a production environment.


Rename Field And Add Field With Same Name In One

Couple of things...

The sp_rename procedure is in the sys schema, not dbo.

Second, if you execute a stored procedure in a batch, then you must use the EXEC keyword.

Try changing your code to this:

CREATE Table TMI (empName varchar(500), userID varchar(100), TV float)

CREATE NONCLUSTERED INDEX eNIndex
ON TMI (empName ASC)

EXEC sys.sp_rename '[dbo].TMI.TV' , 'OrigTV', 'COLUMN'

ALTER TABLE [dbo].TMI
ADD TV float;


SQL Server replication error "profile name is not valid"

It seems that you initialized your transactional replication using database backup. With database backup, there is no schema difference between the publisher and subscriber.

There might be trigger exists in your publisher and restored in your subscriber, trying to send email using invalid profile name from your subscriber.


How can I insert records 'into null' in Oracle?

One way you could do something like this on Oracle is to use a temporary table with ON COMMIT DELETE ROWS. The rows will "disappear" at the end of the transaction. If you're using a host environment such as JDBC with autocommit, this will make it look like what you describe. If performance is important, this approach will be much faster than the "discarding trigger" approach.

Syntax:

CREATE GLOBAL TEMPORARY TABLE my_table (
  column1 INTEGER,
  column2 VARCHAR2(100),
  ...
)
ON COMMIT DELETE ROWS;

Documentation: http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables003.htm#ADMIN11633


Does large VARCHAR take up more room in the transaction log

In the case of a small insert, there is no difference in size of transaction log generated.

Here's a demo script to prove it (SQL Server 2016):

--setup
USE master
GO
CREATE DATABASE LogTest
GO
ALTER DATABASE LogTest
SET RECOVERY SIMPLE
GO
USE LogTest
GO

CREATE TABLE dbo.v100 (junk VARCHAR(100) )

CREATE TABLE dbo.v8000 (junk VARCHAR(8000) )
GO

--initial inserts have extra log activity from initializing the table
INSERT dbo.v100
VALUES ('z')

INSERT dbo.v8000
VALUES ('z')

-----------------------------------------------------------------
--ok, let's test this
CHECKPOINT --checkpoint clears log in SIMPLE recovery, which helps limit it to what we want to see
GO

BEGIN TRAN
INSERT dbo.v100
VALUES ('a')
ROLLBACK

SELECT Operation, Context, [Log Record Length], [Log Reserve]
FROM sys.fn_dblog(NULL,NULL)
WHERE Operation NOT LIKE '%CKPT'

CHECKPOINT
GO

BEGIN TRAN
INSERT dbo.v8000
VALUES ('a')
ROLLBACK

SELECT Operation, Context, [Log Record Length], [Log Reserve]
FROM sys.fn_dblog(NULL,NULL)
WHERE Operation NOT LIKE '%CKPT'


Being able to create a view is not [quite] enough.
The Owner [account] of the finished View must have read privileges on the Tables used by that view.  These privileges cannot be conferred through any Role.
grant read on friends to owner_of_friends_v4 ;
grant read on clients to owner_of_friends_v4 ;

Also, why are you working with Common users here (starting "C##")?
I would only expect to see these in the Container database, not any of the Pluggable ones that live within it (which is where I would expect to see all Application Tables and Data).


Prepare statements plan invalidation in postgresql

Plans are invalidated if any object that they use suffers “cache invalidation”. Compare this source comment:

 * Currently, we track exactly the dependencies of plans on relations and
 * user-defined functions.  On relcache invalidation events or pg_proc
 * syscache invalidation events, we invalidate just those plans that depend
 * on the particular object being modified.  (Note: this scheme assumes
 * that any table modification that requires replanning will generate a
 * relcache inval event.)  We also watch for inval events on certain other
 * system catalogs, such as pg_namespace; but for them, our response is
 * just to invalidate all plans.  We expect updates on those catalogs to
 * be infrequent enough that more-detailed tracking is not worth the effort.

In the case of tables:

git grep CacheInvalidateRelcache

on the PostgreSQL source, you can find which changes involving tables cause plans to be invalidated:

contraints are added, removed, modified or renamed
attaching or detaching a partition
an index is added or dropped
REINDEX
a table is added to or removed from a publication
CLUSTER and VACUUM (FULL)
a row level security policy is added, modified or dropped
an inheritance child is added
ALTER TABLE
a trigger is added, dropped, renamed, disabled or enabled
a query rewrite rule is added or removed
TRUNCATE

Other actions that lead to any such action will of course also invalidate plans.


This is a tricky one. crosstab() expects one (or no) value per category for each row_name.

We can work around this restriction like this:

SELECT id
     , COALESCE(cat01, max(cat01) OVER w)
     , COALESCE(cat02, max(cat02) OVER w)
     , COALESCE(cat03, max(cat03) OVER w)
FROM   crosstab(
   'SELECT id::text || row_number() OVER (PARTITION BY id, category ORDER BY value) * -1 AS ext_id
         , id, category, value
    FROM   tbl
    ORDER  BY ext_id, category, value'

   ,$$VALUES ('Cat01'::text), ('Cat02'), ('Cat03')$$
   ) AS ct (xid text, id int, cat01 text, cat02 text, cat03 text)
WINDOW w AS (PARTITION BY id);

Returns your desired result.

How?

Add an extended id: ext_id from the existing id and a row number for each value of the category for the same id. This way we ensure as many rows per id in as there are values for the most common category. We get a derived table like this to build our crosstab() on:

ext_id   | id   | category | value
---------+------+----------+-------
'1234-1' | 1234 | 'Cat01'  | 'V001'
'1234-1' | 1234 | 'Cat02'  | 'V002'
'1234-1' | 1234 | 'Cat03'  | 'V003'
'1234-2' | 1234 | 'Cat03'  | 'V004'
'1234-3' | 1234 | 'Cat03'  | 'V005'

Now we can feed it to crosstab() using the safe 2-parameter form for missing attributes. Read the basics first if you are not familiar with this:

PostgreSQL Crosstab Query

The original id is carried over as "extra column". See:

Pivot on Multiple Columns using Tablefunc

Your question leaves room for interpretation. My solution pairs the lowest values per category first and keeps filling the following rows until there are no values left. (We could combine multiple values per category any other way, it has not been defined.) If a category is short of values for a given id, the rest is filled in with NULL values.
In the final step I replace those NULL values with the maximum value of each category per id:

COALESCE(cat01, max(cat01) OVER (PARTITION BY id, category))

which is effectively the same as:

max(cat01) OVER (PARTITION BY id, category)

I am hoping to make it slightly faster if we only default to the window function if the value is NULL.


Performance improvement on SQL query joining table on itself

You are doing the entire delete in one transaction that will possibly bloat your transaction log and it will crawl like a sloth.

There is a much better way of doing a delete - break it into chunks or you can do deletes using Careful Batching.

Also, its always a good habit to refer objects with their schema.


What's the best way to update several (200k)rows in a database?

I would suggest that the schema is poorly designed if you have 205K rows with the identical uuid.

Furthermore, if you need all of them to have the same ended_at value, then there should be some other table that has, for example, one row with uuid and ended_at.  The JOIN to get the data during a SELECT will be more than compensated for by having removed ended_at from the big table.

Perhaps I am being overly critical.  If so, please provide some more insight into what the columns mean, and where the uuids come from, etc.

The best way to update "many" rows in a "huge" table is to walk through the table by the PRIMARY KEY, and update a thousand rows at a time.  More details on chunking:  http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks


How to skip to next iteration if no results are returned in SELECT statement in Stored Procedure

You shoud use: LEAVE loop_name

IF NOT EXIST (SELECT 1 FROM table3 WHERE <some condition>)
    LEAVE read_loop;


How do I remove duplicate entries in a given timeframe?

The usual solution for this problem is first looking for the rows you want to keep, and then delete all others.  The first part can be accomplished with an aggregate and grouping:

SELECT min(id)
FROM my_table
GROUP BY row2, DATE_FORMAT(`time`, '%Y-%m-%d %H')

Then you can write your DELETE:

DELETE FROM my_table
WHERE id NOT IN (
    SELECT min(id)
    FROM my_table
    GROUP BY row2, DATE_FORMAT(`time`, '%Y-%m-%d %H')
);

There are variations to this theme: you can use a NOT EXISTS construct instead of NOT IN (with appropriate changes), or a join using the USING keyword.


Which MS Access version are you using?

Depending on MS Access version it's not possible to order by memo fields. You will need a workaround like:

ORDER BY Left(Description, 127) DESC


Create a file with variable name in power shell Power shell

This code should do what you want. To make it more readable and understandable, I expanded out much of the logic. I also added comments to describe what each piece of code is doing.

## Import the SMO assembly from the GAC
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")

## Create a Server object for connection to the SQL instance
$server = New-Object Microsoft.SqlServer.Management.Smo.Server "SRVBLRDBATST98\MSSQLSERVER1"

## Loop over the databases on the instance
$server.Databases | ForEach-Object {
    ## Store the name of the database
    $databaseName = $_.Name

    ## Generate a script for the database
    $databaseScript = $_.Script()

    ## Save the script to a file, overwriting what may have already been there
    $databaseScript | Out-File -FilePath "E:\pruthvi\$($databaseName).sql" -Force
}

The key to this change are the parameters passed to Out-File. First off -FilePath "E:\pruthvi\$($databaseName).sql" is what saves the script to a file named after the database. Secondly, -Force ensures that if the target file already exists, it will be replaced.


Rollback is not working if the storedproce call another storedproc

Beginning a transaction causes any pending transaction to be committed. See Section 13.3.3, “Statements That Cause an Implicit Commit”, for more information.

-- https://dev.mysql.com/doc/refman/8.0/en/commit.html

and

  Transactions cannot be nested. This is a consequence of the implicit commit performed for any current transaction when you issue a START TRANSACTION statement or one of its synonyms.

-- https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html

That is, proc1's START closed proc2's START.

The first ROLLBACK closes the only open transaction, hence the second ROLLBACK has nothing to "roll back".


My database got in to suspect mode, I repaired it using dbcc, but I want to know cause

The database has corruption which is reported by DBCC CHECKDB and it is the only safe way it could repair it which did not happen in this case. Using repair_allow_data_loss will guarantee you some loss of data and you should not be going for that unless you can afford to lose data.

As any DBA, you will have point in recover backup set up so use it to recover the database which will be more consistent. For any further troubleshooting use :

https://support.microsoft.com/en-gb/help/2015748/how-to-troubleshoot-database-consistency-errors-reported-by-dbcc-check

https://sqlpowershell.wordpress.com/2016/07/11/fix-database-consistency-errors-reported-by-dbcc-checkdb/


FLUSH TABLES WITH READ LOCK not working?

Found the reason my script failed.

The lock is released when the connection is finished.

Appear be quite obvious, but I don't remember read at any place about this behave and since few commands are able to execute from prompt with mysqladmin (not this case) induce to wrong interpretation of FLUSH behave.

My first script, where fails to lock look like this :

    # Trava banco de dados e faz flush dos dados em disco
    echo
    echo "Travando banco e sincronizando dados..."
    echo "FLUSH TABLES WITH READ LOCK" | mysql -vvv 2>&1 | awk '{print " | "$0}'

    echo "Forcando novo binlog"
    echo "FLUSH LOGS" | mysql -vvv 2>&1 | awk '{print " | "$0}'

    # sincroniza FS
    sync
    sleep 1

    # cria snap , com 10G de dados
    echo
    echo "Criando snapshot..."
    echo "  lvcreate --snapshot $VG/lvmysql -n $vN -L 10G"
    lvcreate --snapshot $VG/lvmysql -n $vN -L 10G

    # libera banco de dados
    echo
    echo "Liberando banco de dados..."
    echo "UNLOCK TABLES " | mysql -vvv  2>&1 | awk '{print " | "$0}'

    echo
    echo "Snapshot criado : "
    lvs -o +lv_time | awk '{print "  "$0}'

The problem is the FLUSH running in standalone session, when this session close it's released.

To solve I run all at unique session.
This is how I rewrite it and work properly

    VG=system
    vN=lv_bkp1
    # Trava banco de dados e faz flush dos dados em disco
    echo
    {
    echo "
    select '-- Travando banco e sincronizando dados...' ;
    FLUSH TABLES WITH READ LOCK  ;

    select '-- Forcando novo binlog' ;
    FLUSH LOGS ;

    select '-- Logando posicao do bin-log, caso necessario...' ;
    SHOW MASTER STATUS ;

    select '-- ! sync' ;
    system sync ;

    select '-- ! sleep 1' ;
    system sleep 1 ;

    select '-- Criando snapshot...' ;
    system lvcreate --verbose --snapshot $VG/lvmysql -n $vN -L 10G ;

    select '-- Liberando banco de dados...' ;
    UNLOCK TABLES ;
    "
    } 2>&1 | mysql --unbuffered -vvv 2>&1 | awk '{print " | "$0}'

    echo
    lvs -o +lv_time | awk '{print "  "$0}'


SequelizeDatabaseError: cannot execute UPDATE in a read-only transaction

It can be caused by these reasons

1) you connected to a follower. Check it, using select pg_is_in_recovery(); – on master, it will return false:

test=# select pg_is_in_recovery();
 pg_is_in_recovery
-------------------
 f
(1 row)

Of course, you can run UPDATEs only on the master DB.

2) (I suppose this is not your case, but just for completeness I add it) You're working with master DB, but transaction or session was set to be "read only":

set session characteristics as transaction read only;

or, inside transaction where UPDATE is being executed:

set transaction read only;


In principle this won't work, as you can't check if table exists and then join it in a query.
The error is unrelated. You are selecting a value in a subselect and it returns multiple rows, as the table exists.
What you can do is:

Create temp table
Check if table1 exists using V_Catalog.table and if specific column existence is important, v_catalog.column
If it exists, select data from table1 into temp table
Use the temp table in the union instead of table1


The thing you need to do to change, what I will call, the reference point for replication.
Since Servers B,C, and D have binlogs enabled, this will be handy.
You gave
slaveB> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
      Relay_Master_Log_File: master-bin.000002
        Exec_Master_Log_Pos: 1476884

slaveC> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
      Relay_Master_Log_File: master-bin.000002
        Exec_Master_Log_Pos: 78684

Let's sync up ServerC to be a Slave to ServerB
STEP 01 : Stop all writes to ServerC
STOP SLAVE;

STEP 02 : On ServerC, run SHOW MASTER STATUS;
Let's say the output was master-bin.000003 position 12345678
STEP 03 : On ServerC, run
mysqlbinlog master-bin.000003 > latest-binlog.sql

STEP 04 : Locate position from STEP 03 and show 3 lines before and after
grep -C 3 12345678 latest-binlog.sql

Let's say the timestamp was "2013-09-03 01:23:45"
STEP 05 : Find the timestamp of that position in the binary logs of ServerB
Let's say the binary log on ServerB is master-bin.000004
STEP 06 : Dump position matching timestamp inside ServerB's binary log master-bin.000004
SQLFILE=dumpstuff.sql
BINLOG="master-bin.000004"
DT2="2013-09-03 01:23:45"
DT1="2013-09-03 01:00:00"
mysqlbinlog --start-datetime="${DT1}" --stop-datetime="${DT2}" > ${SQLFILE}

STEP 07 : Locate the timestamp inside SQLFILE and find the position
Let's say the position is 87654321
STEP 08 : Connect SlaveC to SlaveB
On SlaveC, run this
STOP SLAVE;
CHANGE MASTER TO master_log_file='master-bin.000004',master_log_pos=87654321;
START SLAVE;

That's it !!!
CAVEAT #1
Repeats all these same steps to sync ServerD to ServerB
CAVEAT #2
If you have done these steps and still don't trust the data, please download Percona Tools and use pt-table-checksum to verify each Slave against the new Master. If there are differences, you can run pt-table-sync to generate the SQL to repair the differences.
UPDATE 2013-09-05 12:28 EDT
You just made this comment

On Step 2,3 and 4 , I don't have access to master binlog, I guess the way to get the latest applied transaction on slaveC is to extract it (using the Relay_Master_Log_File and Exec_Master_Log_Pos from show slave status) from slaveC relay log. Am I right? Steps 5 to 8 seems to be correct.

You can use mysqlbinlog remotely using --read-from-remote-server. Besides, you do not go back to the old Master, ServerA. You are doing these things among the Slaves, making ServerB the new Master. If you are using some manged hosting company, those admins need to be involved in this process. At the very least, they could provide some SSH tunnel access to permit you to access the Linux box and all necessary folders permissions. No MySQL authentication is needed.


Postgres extremely slow after upgrade from Version 10 to 11

The output of pg_upgrade includes this:

...

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade so,
once you start the new server, consider running:
    ./analyze_new_cluster.sh

...

We found horrible performance straight after the upgrade until we ran this, lesson learned to read the output :)


Can we minimise the risk of using xp_cmdshell?

Well, you could not grant execute on it explicitly to end users (deny it even), and only enable it in stored procedures that use EXECUTE AS with some login that does have execute permissions. Then grant execute on only that stored procedure to the user that needs to run the command.

First, make sure xp_cmdshell is enabled for the instance:

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'show advanced options', 0;
GO
RECONFIGURE WITH OVERRIDE;

Now depending on operating system and SQL Server service account, you may need to set up a proxy account (and to do this, thanks to UAC, you may need elevation and launch SSMS as an administrator):

EXEC master..sp_xp_cmdshell_proxy_account 'Domain\User', 'Password';

Then create a wrapper in your database that does whatever you need it to do (this is just a sample of a VERY generic wrapper that doesn't really protect you from anything; only using it to demonstrate):

USE yourdb;
GO
CREATE PROCEDURE dbo.uxp_cmdshell
  @cmd VARCHAR(2048)
WITH EXECUTE AS OWNER
AS
BEGIN
  EXEC master.dbo.xp_cmdshell @cmd;
END
GO

Now give your user or role permissions to execute the procedure:

GRANT EXECUTE ON dbo.uxp_cmdshell TO [your_user_or_role!];

Now when your_user logs in, and tries to execute willy-nilly calls to xp_cmdshell:

EXEC master.dbo.xp_cmdshell 'dir c:\';

They will get:

  Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1
  The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

However if they pass the same command to your new procedure:

EXEC dbo.uxp_cmdshell 'dir c:\';

It will work just fine (assuming your proxy account is set up correctly and/or the SQL Server service account has adequate permissions).

This can take some work, but effectively this allows you to control exactly what your users can do with xp_cmdshell.


Improve performance of converting a large xml file (~300 MB) to relational table in SQL Server

I had another look at this and can reproduce your issue.  Try adding OPTION ( MAXDOP 1 ) to your query.  In my test rig with a 300MB file this ran in 1 min 42 seconds.  The unhinted version ran for 30 minutes at 100% CPU before I killed it.

You could also have a look at OPENXML.  People often say it's faster with large XML files and it appears to be in this case.  However you should be aware of the known issues with OPENXML ( eg can take 1/8th of buffer pool, is an old-fashioned COM .dll, you must call sp_xml_removedocument etc ).  Once you've researched the pros and cons of OPENXML, you could try something like this:

DECLARE @FileData XML

SELECT @FileData = BulkColumn
FROM OPENROWSET(BULK 'd:\temp\temp.xml', SINGLE_BLOB) AS x

DECLARE @hDoc int

EXEC sp_xml_preparedocument @hDoc OUTPUT, @FileData

SELECT *
INTO #tmp
FROM OPENXML( @hDoc, '/Data/Entities/Entity/Attributes/Attribute/Values/Value', 1 )
WITH
    (
    Id VARCHAR(50) '../../../../@Id',
    Name VARCHAR(100) '../../../../@Name',
    AttributeName VARCHAR(100)  '../../@AttributeName',
    AttributeValue VARCHAR(MAX) '.'
    )

EXEC sp_xml_removedocument @hDoc

Honestly I try and avoid it these days due to those issues; what's the point of one query going faster when you've just lopped 1/8th of your buffer pool?

Finally, the quickest and most scalable method (IMHO) is SSIS.  This version ran in about 10 seconds for the same file as the above methods in my rig.

SSIS XML Import Method

Create a package, add a Data Flow task, add an XML Source and then each table. I created a 300MB file with the same structure as yours and it loaded in about 10 seconds, eg

Obviously you'll need extra time to query the tables you have imported but I think this is a safer method.  You could even scale it out with multiple packages.  Post back if you need more help with the package.


In a transaction (or in an SQL function) only the results from the last command are returned to the user. You could write the results to a (temporary) table to inspect them later.

Or you run through your transaction interactively, executing one command after the other.


What is the best way to store SSH fingerprints and public keys?

If I'm not mistaken, the fingerprint is a hexadecimal number.  That means you could store it as a binary number, since two hexadecimal characters can be represented by a single byte, making a 32-character hexadecimal string = 16 bytes.

Here's the documentation on binary and varbinary data types for MySQL 5.7 (I don't have a test environment handy).

I do something similar for IP addresses based on this question and answer from StackExchange.  Store the data as binary, convert it back to text when it needs to be displayed or compared against another string.  Create a view that calls the functions as a part of it so you can easily get and store the full string when you want it.

Also, public keys are indeed public, and are often stored in searchable databases like this one.


I'd go with option 3, which will require less overall work:

Make a lookup table that contains a list of purged records and dates, like in your first option
Rename your current table to something like dbo.Tablename_master
Create a VIEW named dbo.Tablename which consists of:

.

SELECT <fields>
FROM dbo.Tablename_Master
WHERE PrimaryKeyField NOT IN (SELECT PrimaryKeyField FROM PurgeTable)

Now you don't need to modify any existing code, since all references to your original table go through the view instead.  This will be complicated if you have any existing constraints but they should be easy to work around.


What could explain a 30 second MySQL SELECT query latency when profiling records an execution time of <1 second?

Consider creating this composite index,
CREATE INDEX some_table_indx_col3_col2 ON some_table(col3,col2);

You should remove the col3 index to avoid index redundancy and conserve storage space.
Considering these existing details,
Query_cache_size        2,556,593,152
less Qcache_free_memory 2,555,753,592
                       ----------------
Query_cache bytes in use      839,560
Qcache_queries_in_cache     /     288
You have avg bytes per query    2,915 in use

Consider query_cache_limit of 192K  for ~ 64 * avg bytes per query
query_cache_min_res_unit          512  the minimum to store more queries in your cache
query_cache_size   50M   for ~ 64 * Query_cache bytes in use - from ~2.5G size.

To conserve 98% of CPU cycles used each time qcache_lowmem_prunes are required.
Stalls can be observed if innodb tables are deleted that have involvement with flushing from innodb_buffer_pool change activity before the requested table delete can be completed.
Please let us know of your progress/frustration and consider awarding the bounty.
Happy New Year


SQL Server Table Reports Disk Usage 0 Rows but reserved 46 GBs

The solution is to TRUNCATE TABLE table_name instead of  DELETE FROM table_name.

P.S.
This tables are stage table and ETL uses them quite often, every 20 minutes data is inserted and shortly after data was processed - deleted.


Something like this?

SELECT
  to_timestamp(end_time, 'YYYYMMDDHH24MISS') -
  to_timestamp(start_time, 'YYYYMMDDHH24MISS') as "time_interval"
FROM
  measurement
WHERE
  end_time IS NOT NULL;

There's an sqlfiddle if you want to adjust your question: http://sqlfiddle.com/#!1/576d4/6


SSMS - Can not bulk load because the file could not be opened

winerror.h lists Operating System Error Code 3 as ERROR_PATH_NOT_FOUND.  This likely indicates that \\servername\sharename\filename.txt either doesn't exist or the SQL Server Service Account does not have access to the file via the UNC.
Use SQL Server Configuration Manager to determine the name of the SQL Server Service Account:

Check the Fileshare Security for the UNC to ensure that account has access to the share.  Check the Filesystem Security for the folder underlying the UNC to ensure the account has access to the file.
T-SQL BULK INSERT requires an understanding of the security architecture.  Taken from that page:

If a user uses a SQL Server login, the security profile of the SQL Server process account is used. A login using SQL Server authentication cannot be authenticated outside of the Database Engine. Therefore, when a BULK INSERT command is initiated by a login using SQL Server authentication, the connection to the data is made using the security context of the SQL Server process account (the account used by the SQL Server Database Engine service). To successfully read the source data you must grant the account used by the SQL Server Database Engine, access to the source data.In contrast, if a SQL Server user logs on by using Windows Authentication, the user can read only those files that can be accessed by the user account, regardless of the security profile of the SQL Server process.
When executing the BULK INSERT statement by using sqlcmd or osql, from one computer, inserting data into SQL Server on a second computer, and specifying a data_file on third computer by using a UNC path, you may receive a 4861 error.
To resolve this error, use SQL Server Authentication and specify a SQL Server login that uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about how to enable a user account to be trusted for delegation, see Windows Help.


Allow user to create databases on server and to see only the databases that are created by him, without creating login first

Instead of making Logins for each user, you should use an Active Directory Group that each user is a part of (e.g. DOMAIN USERS), and assign permissions directly on that AD Group or on a Role you can create for that AD Group. Then you only need to DENY VIEW ANY DATABASE once.
Then when your user creates a database via your application is when you can run the CREATE LOGIN (if one doesn't exist) and ALTER AUTHORIZATION ON DATABASE scripts for that User and Database.
The benefit of doing it this way is all your Users will be unable to access any databases that aren't their own automatically, even via SSMS. You also can set the default database of the new Login to their first database they create too, and set appropriate permissions like giving them the db_owner role.


Optimal way of loading, registering, indexing and querying a huge XMLDB

Wouter got, hopefully, useful answers here:

https://community.oracle.com/message/12608939

That said, being the Oracle ACE Director for this technology. Oracle XMLDB functionality is nowadays so interwoven with the Oracle database that this is an MANDATORY (install) functionality for Oracle 12c and onwards. It is the basis for much in the database like ACL security, database security, Database Vault, Oracle APEX, Multitenent functionality, import and export, DBMS_packages like DBMS_METADATA, Oracle performance implementations (AWR XMLtype columns etc etc), DBA/Developer tools like DB Express...

XML is (alas?) everywhere nowadays. To give one example, you using it now (that is if you use a mobile phone...)


Have you considered Robocopy? It is able to copy NTFS ACLs. I guess you might need to have postgres running under the same Domain Account on source and target if possible but I'm probably out of my depth talking about such things.


UUID array declared in stored procedure, on access throws explicit type casts needed


How to store and query products the price of which depends on a complex set of conditions?

Check out chapter 3 of

The Data Model Resource Book Volume 1 by Silverston

It has a pricing model that is very flexible

A very brief summary:

It's kinda complicated...

(*) means this column references another table

PriceComponent

id
fromDate
thruDate null
price null
percent null
geographicBoundary* [pizza costs more outside of city]
partyType* [surcharge to seniors]
productCategory* [discount for CRT monitors]
quantityBreak* [discount for high volume]
orderValue* [discount for big order]
saleType* [surcharge for retail]
currency* [discount for Canadians]
productFeature*
product*

BasePrice : PriceComponent

DiscountComponent : PriceComponent

SurchargeComponent : PriceComponnent

ManufacturersSuggestedRetailPrice : PriceComponent

Charge : PriceComponent

OneTimeCharge : Charge

RecurringCharge : Charge

UtilizationCharge : Charge

So, the price of something is a set of PriceComponents, which can be either an actual price amount or a percentage. You can have a component of the final price based on geographical location, the type of sale (retail vs wholesale), the value of the order and so on.


How can I replace constants 5,6 & 8 in sql instead of in condition

It depends whether the values you want to go in the IN list are from an existing table or from a SQL parameter. If they are from an existing table or query you can do something like this:

SELECT DISTINCT party_id
INTO #tmp_new_part
FROM #party_approval
WHERE party_approval_type_id in (select party_approval_type_id from some_table)

If you want it to be based on parameters to a stored procedure, you can pass a varchar containing a delimited list of values, and use one of the many existing UDFs to split it into a table variable, similar to below:

SELECT DISTINCT party_id
INTO #tmp_new_part
FROM #party_approval
WHERE party_approval_type_id in (select * from dbo.CsvToInt(@values))


Why does MySQL increment the auto_increment value when there's a constraint violation?

Because the value is incremented outside the scope of the transaction. This is the way you want it to work, so that separate transactions can happily insert rows or roll back without waiting for each other.

If you care about gaps or want different behavior, stop using the built in and roll your own...


In order to find out the users and the profile assigned you can use the commands below.

DESC DBA_USERS;

This will show you all the fields name for which you want to query

SELECT USERNAME, PROFILE, ACCOUNT_STATUS FROM DBA_USERS;

And this command will show you the user name, profile and account status i.e. which profile is assigned to which user


FK constraint fails while inserting into child on Disjoint tables

Equal both type ENUM values.

CREATE TABLE `item` (
  id int NOT NULL AUTO_INCREMENT,
  type ENUM('label', 'checkbox') NOT NULL DEFAULT 'label',
  PRIMARY KEY (id, type)
  );

✓

CREATE TABLE checkbox (
  item_id INT NOT NULL,
  item_type ENUM('label', 'checkbox') NOT NULL DEFAULT 'checkbox',
  reviewer INT NOT NULL,
  PRIMARY KEY(item_id, item_type, reviewer),
  CONSTRAINT `fk_2` FOREIGN KEY (item_id, item_type)
                    REFERENCES `item` (id, type) ON DELETE NO ACTION ON UPDATE CASCADE
  );

✓

INSERT INTO item VALUES(1,'checkbox');

✓

INSERT INTO checkbox VALUES(1,'checkbox', 1);

✓

dbfiddle here


Why does MariaDB 10.1.2 blow away MySQL 5.6.22 in sysbench?

The MariaDB has a lot of optimizer features not found in mySQL (especially 5.5, but still compared to 5.6)

See this link for a comparison of optimiser features.

There is also a benchmark results page which shows MariaDB faster in a lot of tests.

So in the end MariaDB has more options when choosing an execution plan.


String index for a list of values using IN($1,$2): will a btree index work?

An IN statement can be considered a chained OR, e.g. WHERE name IN ('Banana','Apple') is equivalent to WHERE (name = 'Banana' OR name = 'Apple').
The data is normalized once you define Name as your primary key as that would uniquely identify a fruit for the purposes of this example.  So if that was your primary key, there would be a B-tree present and the engine would (generally) do two seeks for your query: one for 'Banana' and one for 'Apple'.
If there was a secondary index on Name, the engine would (again, generally) do two seeks on that index and two seeks against the table.


Possible to upgrate SQL Server 2012 express to 2012 Standard?

Yes it's as simple as running the setup and execute the edition upgrade.

Just in case it's better to backup your database before but everything should be ok.

Details here :
https://msdn.microsoft.com/en-us/library/cc707783.aspx

Of course, you should buy the standard licence.


Update Statement takes forever to complete

It's difficult to say exactly what's causing it without seeing the code for the trigger, but I'd bet some non-zero amount that misuse of the INSERTED and DELETED virtual tables is to blame.

The thing about INSERTED and DELETED is that they have NO INDEXES. If you join them directly back to the base table (or to each other), you can end up with some very awful performance.

You can generally mitigate this by loading the two into temp tables and creating indexes for however you'll be joining them (usually on the primary key).

SELECT *
INTO #i
FROM INSERTED

CREATE CLUSTERED INDEX CX_i ON #i (primary_key)

SELECT *
INTO #d
FROM DELETED

CREATE CLUSTERED INDEX CX_d ON #d (primary_key)

Something like that, anyway. Then only use your temp tables, and don't touch INSERTED and DELETED.

Be aware that the temp table names have to be unique per session. If you have multiple triggers cascading, they need to use unique table names. You may want to use table variables instead if this becomes a problem, as they are scoped to the trigger/procedure they are running in, with no risk of name collisions. (However, the only way to index a table variable is to declare a clustered primary key in your DECLARE statement.)


Server and Database role memberships required to add columns and constraints

First, the rollback transaction will undo what you are doing. Use commit transaction instead.

Yes
DB_owner in a database named XXX
Do you have a "USE XXX" in your script? I normally remove that stuff to protect DB names and server names when posting.  What you are saying here does not make sense to me.


NTFS filesystem fragmentation & other influence vs database files

is there any information about fragmentation of filesystem influence on database/MS SQL Server performance?

AFAIK the only thing we have is about error 665 (NTFS metadata fragmentation) which is more of an error than a performance issue.

  Or there are hidden proprietary layers of optimisation of NTFS for MS SQL Server database storage?

There are not. SQL Server is installed on any system just like it is. It also runs on ReFS.

  Are SQL server databases really ordinary files on NTFS or OS treated it as special objects?

Yep, treated as normal files. Nothing special going on. For real.

  Is there any know safety mechanism preventing such accident for MS SQL to occur?

Nothing stopping the underlying filesystem from doing something bad because of corruption, write caching, etc. We do have some built in mechanisms such as write ahead logging (WAL) but nothing specific for NTFS. That's below our purview as an application on Windows. We tell Windows we want to do something, using normal Windows API... what happens after that is anyone's guess.

Related documentation:

Database Files and Filegroups
SQL Server Transaction Log Architecture and Management Guide


No.
Instead, I suggest that the most important information comes from summarizing the log.  This can be done via either of these

pt-query-digest
mysqdumpslow -s t

Then focus on the first few entries.


Get the Replication/Lag time of Redshift

In this context, I usually create a specific table to see the last replication status.

On your PostgreSql master server, you create a simple table, example:

CREATE TABLE check_replication ( now_date timestamp );

Then you create a batch or job, that performs every minute, or more often:

INSERT INTO check_replication ( now_date ) VALUES (now());

Then, you add this table in the replicated tables from PostgreSql to RedShift

And you can check the replication delay.

HTH,
Thomas


Adding a database with Service Broker to an existing Availability Group on a new replica

Both SET TRUSTWORTHY ON and ENABLE_BROKER are database settings. When you backup and restore a database, these settings will be included in the bak-file and can be restored on the new SQL instance.
Probably, you'll need to make some server level configurations though, such as a service broker endpoint as described here

Edit: Just to clarify, when restoring the backup you don't need to specify the ENABLE_BROKER option. The broker settings will be synced from the primary replica as soon as you join the database to the AG. You only need to restore the database using NORECOVERY.

On a side note, when you add your SQL Server 2017 instance to the AG and failover to it you won't be able to fail back. This leaves your AG with a single point of failure (the new primary replica).
Consider upgrading all your secondary replicas before failing over to your new instance. After the failover, upgrade your (previous) primary replica. More information about rolling upgrades can be found here


How to implement business logic permissions in PostgreSQL (or SQL in general)?

I know that you are not asking about database security per se, but you can do what you want using database security. You can even use this in a web app. If you don't want to use database security, then the schemas still apply.

You want column-level security, row-level security, and probably hierarchical role management. Role-Based security is much easier to manage than User-Based security.

This example code is for PostgreSQL 9.4, which comes out soon. You can do it with 9.3, but there is more manual labour required.

You want everything to be indexable if you are concerned with performance †, which you should be. This means that bit-mask and array fields probably won't be a good idea.

In this example, we keep the main data tables in the data schema, and corresponding views in public.

create schema data; --main data tables
create schema security; --acls, security triggers, default privileges

create table data.thing (
  thing_id int primary key,
  subject text not null, --or whatever
  owner name not null
);

Put a trigger on data.thing for inserts and updates enforcing that the owner column is the current_user. Perhaps allow only the owner to delete his own records (another trigger).

Create a WITH CHECK OPTION view, which is what users will actually use. Try really hard to make it updatable, otherwise you'll need triggers/rules, which is more work.

create view public.thing with(security_barrier) as
select
thing_id,
subject,
owner,
from data.thing
where
pg_has_role(owner, 'member') --only owner or roles "above" him can view his rows.
WITH CHECK OPTION;

Next, create an access-control list table:

--privileges r=read, w=write

create table security.thing_acl (
  thing_id int,
  grantee name, --the role to whom your are granting the privilege
  privilege char(1) check (privilege in ('r','w') ),

  primary key (thing_id, grantee, privilege),

  foreign key (thing_id) references data.thing(thing_id) on delete cascade
);

Change your view to account for ACLs:

drop view public.thing;

create view public.thing with(security_barrier) as
select
thing_id,
subject,
owner
from data.thing a
where
pg_has_role(owner, 'member')
or exists (select 1 from security.thing_acl b where b.thing_id = a.thing_id and pg_has_role(grantee, 'member') and privilege='r')
with check option;

Create a default row privileges table:

create table security.default_row_privileges (
  table_name name,
  role_name name,
  privilege char(1),

  primary key (table_name, role_name, privilege)
);

Put a trigger on insert on data.thing so that it copies default row privileges to security.thing_acl .

Adjust table-level security appropriately (prevent inserts from unwanted users). No one should be able to read the data or security schemas.
Adjust column-level security appropriately (prevent some users from seeing/editing some columns). You can use has_column_privilege() to check that a user can see a column.
Probably want security definer tag on your view.
Consider adding grantor and admin_option columns to acl tables to track who granted the privilege, and whether the grantee can manage privileges on that row.
Test lots

† In this case pg_has_role is probably not indexable. You would have to get a list of all superior roles to current_user and compare to the owner/grantee value instead.


Not at all! Aerospike is a great in-memory key-value store, typically as fast as Redis, but that also scales super easy.

You can check the different configurations possible on Aerospike here. So if you can fit all your data in memory, Aerospike will work as well or better then any other in-memory database. The SSD optimization is useful for larger data sets that would be too expensive to fully store in memory and Aerospike is also optimized for that use case.


Gathering information to tune oracle row prefetching

This is an interesting question and differs significantly from the approach I have used.  The kind of information you are looking for would not be of much interest to me.  I would be more concerned with the number of rows displayed (or processed) for each query.  The place to capture that is likely in your application(s).

You may be able to gather most of what you are looking by enabling auditing for your queries and analyzing the audit data.

For batch retrieval which will use the full result set, I generally set a relatively large pre-fetch value (100 or so).  Tuning might be of value in some cases, but I would limit it to frequently run queries with large result sets where database fetch is a significant portion of the overhead.

For interactive applications I usually tune prefetch limits towards the number of items displayed on a page.  There isn't much value in prefetching data for the next page unless the users actually page to it.  It has been my experience, that users don't page down that often.  Anything past the second page would be overkill for the initial prefetch.

EDIT: Based on your comment, setting prefetch size to one or two screens of data may decrease latency on the link.  Fetching unused data will increase the load on the link.  Setting the link to prefetch data should also reduce the load on the link.


Execute T-SQL Only If AlwaysOn Database Is Primary

The client connection role for secondary replicas determines whether your database is always available, or only available to read-only connections, or not available at all. If you set the database to read-only connections, then your connection string has to include the parameter ApplicationIntent=ReadOnly as defined in that Books Online page.

When you try to connect to that database without specifying ApplicationIntent=ReadOnly, your query will fail.


Is there an automatic way for a secondary member to promote to primary member in a replica set?

The main factor here is the Replica Set Elections.
Replica sets use elections to determine which set member will become primary. Replica sets can trigger an election in response to a variety of events, such as:

Adding a new node to the replica set,
Initiating a replica set,
Performing replica set maintenance using methods such as
rs.stepDown() or rs.reconfig(), and
The secondary members losing connectivity to the primary for more
than the configured timeout (10 seconds by default).

I think this is what you are looking for:
rs.stepDown()
instructs the primary of the replica set to become a secondary. After the primary steps down, eligible secondaries will hold an election for a new primary.
Another option would be that you can force a replica set member to become primary by giving it a higher members[n].priority value than any other member in the set. Note that a majority of the configured members of a replica set must be available for a replica set to reconfigure or elect a primary.


Best way to associate multiple records with one another

Are the linked businesses mutually exclusive groups or an extended network?

To extend your example, say we have businesses A, B and C and also X, Y and Z. If {A, B, C} form a closed group where the user can get from any one to each of the others but not to X, Y or Z then you have a closed group. If, however, the user can navigate from any of A, B or C to, let's say, X and then onto Y or Z you have an extended network.

For the closed group scenario adding a Group_Identifier to the Businesses table would suffice. The data then becomes

Business_Name  | Group_Identifier
---------------------------------
Business A     | One
Business B     | One
Business C     | One
Business X     | Two
Business Y     | Two
Business Z     | Two

To retrieve related entities

select
  businesses_name
from businesses
where group_identifier = <the current row's identifier>
and businesses_name != <the current row's name>

If a single business can participate in several groups you have what is known as a graph. Your own and Shooter's solutions implement this scenario. However, these model a directed graph - there is a "from" and a "to" end to each combination. To find all possible links you must read the table twice - once using the Business_1 column and once using the Businesses_2 column - or double up each row - "A to B" and "B to A". (There are specialised graph database products which can handle this, but that's beyond the scope of this question.)

An alternative implementation is to have a new Business_Links table. It has two columns Business_Name and Group_Identifier. Note that this in not the same as the sample data above. That is an extension of the Businesses table. It will have one row per business. This is a new table. There may be many rows per business.

Let's say {A, B, C} form a group and so do {C, D, E}. "C" is in both groups. The data is:

Business_Name  | Group_Identifier
---------------------------------
Business A     | One
Business B     | One
Business C     | One
Business C     | Two
Business D     | Two
Business E     | Two

To find the linked businesses, read the groups of the current business, then read the other business that are also in those groups. I'm not familiar with MySQL syntax but it will be something like

select
  business_name
from business_links
where group_identifier in (
  select
    group_identifier
  from business_links
  where business_name = <current name>
)
and business_name != <current name>

Reading this table will be efficient with an index on business_name and one on group_identifier. Trying to maintain a minimal set of groupings may be tricky if the bussiness often change groupings.


SSMS table properties show an exception for Index space on every table

Like Haris commented, it's likely an SSMS bug that Microsoft will have to resolve. But you should be able to run a trace or an Extended Event session to capture the TSQL that SSMS is using to get the data that you are after. Capture the TSQL and then run that in a query window. Or use the query from marc_s.


ORA-39083: Object type INDEX_STATISTICS failed to create with error

Excluding statistics is safe in the sense that it doesn't affect the actual data you're importing. You can always recalculate the statistics in the restored database.

If you need that restore to do performance debugging on specific queries, that's going to be a problem - you'd want to have exactly the same statistics as for the source database for your tests to be realistic. (But you can always re-export and import the statistics specifically if you need that.)

That being said, nothing's really "safe" with a corrupt database, and that type of error "shouldn't happen". It's possible I guess that the export you're running on failed partially. Check the export log file for errors too.


I've solved with a Preview Solution and connected from the inside virtual network to Sql Server on port 1433.

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-private-endpoint-overview

Next step: Certification Management of the VPN'S client.

Other Solution in Preview also....

https://docs.microsoft.com/en-us/azure/vpn-gateway/openvpn-azure-ad-client


Privilege to set default database in MySQL

Well, thanks Rick James, it is a bit more complicated. The problem is with MariaDB.

Update: https://jira.mariadb.org/browse/MDEV-9614


Placing a uniqueness constraint on a date range

Use an exclusion constraint and be aware how bounds for the type daterange work. date is a discrete type, so ranges have default [) bounds. Per documentation:

  The built-in range types int4range, int8range, and daterange all use a
  canonical form that includes the lower bound and excludes the upper
  bound; that is, [).

So, if you want to book a room from 2016-01-01 to 2016-01-05, including first and last day, you have to specify bounds explicitly:

daterange(start_date, end_date, '[]')

Or you enter dates with exclusive upper bounds; to include 2016-01-05, enter 2016-01-06. Then you can use the simpler expression:

daterange(start_date, end_date)

Either make it a functional index with the above expression in the exclusion constraint, like demonstrated by @ypercube:

Uniqueness constraint with date range

This way you can keep columns like you have right now.

Or you can replace startdate and enddate with a daterange column and use the plain column in the exclusion constraint. Like in this related answer:

Updating table of versioned rows with historical records in PostgreSQL

Either way, assuming roomno is an integer column, you need to install the additional module btree_gist first, once per database.

Related:

Combining separate ranges into largest possible contiguous ranges
Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL


How long will a truncate table take on 252M Rows (170GB data)?

A millisecond or so.

Truncate is O(1) - a pure metadata operation.

This is assuming there is no concurrent activity on the table.

Truncate will potentially need to wait until existing reads are done to acquire a Sch-M (schema modification) lock.

For large tables, the actual deallocation is done asynchronously on a background thread (SQL Server 2000 SP3 onward).

For some other myths surrounding truncate, see A SQL Server DBA myth a day: (19/30) TRUNCATE TABLE is non-logged by Paul S. Randal.


Seems like the following would be sufficient:

CREATE TABLE dbo.tblCustomText
(
  ParentID int NOT NULL,
  CustomFieldID int NOT NULL,
  Value char(50) NOT NULL,
  Log_CreateDate datetime NOT NULL,
  PRIMARY KEY CLUSTERED (ParentID, CustomFieldID)
);

You may also find later that you are querying by date, so you may want a non-clustered index on the Log_CreatedDate column.


I think the trick to this is that it doesn't have to be real time, just eventually consistent, in which case it's straightforward enough (using SQL Server, but this applies in any DB). First a trivial table and some sample data:

create table messages
(message_id integer, sender varchar(20), recipient varchar (20))
go

insert into messages values (1, 'Gaius', 'Octavian')
insert into messages values (2, 'Gaius', 'Octavian')
insert into messages values (3, 'Gaius', 'Octavian')
insert into messages values (4, 'Aurelius', 'Octavian')
insert into messages values (5, 'Aurelius', 'Octavian')
insert into messages values (6, 'Aurelius', 'Gaius')
insert into messages values (7, 'Aurelius', 'Gaius')
insert into messages values (8, 'Octavian', 'Gaius')
go

This is logging for every message, who sent it and who to (assuming for simplicity that the message body is stored in another table). So we can see that the top sender to Octavian is Gaius (3 messages of 5), and the top sender to Gaius is Aurelius (2 messages of 3). To query that using a CTE:

with q1 as (
  select recipient, sender, count(sender) as num_messages_from_sender,
  rank() over (partition by recipient order by count(sender) desc) as priority
  from messages group by recipient, sender)
select recipient, sender as top_sender, num_messages_from_sender
from q1 where priority=1
go

In practice you would have a job that ran every minute (or whatever interval is best) refreshing a lookup table mapping a user to their top sender (or top n senders using where priority <= n) (or in your case, you would be tracking the senders to which they reply with another column and filtering by that).

For the sake of simplicity I have left off indexes and partitioning - they would be the key to performance of this solution. You could certainly scale this to many billions of messages on any modern DB/hardware. GMail most likely has a custom solution tho', but with 20,000 engineers Google can do that!


Convert list of dates in a date range in SQL Server

It's classic "islands" problem.

Here is "classic" solution:

declare @t table (dt date);

insert into @t(dt) values
  ('20180202'),
  ('20180203'),
  ('20180204'),
  ('20180205'),
  ('20180209'),
  ('20180212'),
  ('20180213');

with c as
(
  select dt, dateadd(day, -1 * dense_rank() over(order by dt), dt) as grp
  from @t
)
select min(dt) as start_range, max(dt) as end_range
from c
group by grp;


MYSQL: Select rows with identical values in one column, and specific values in another

SELECT b.*
    FROM (
        SELECT customer_id
            FROM tbl
            WHERE COUNT(*) = COUNT(DISTINCT STORE_id) -- no dup store_ids
            GROUP BY customer_id      -- per customer
            HAVING MIN(status_id) > 4 -- all status_id > 4
         ) AS a
    JOIN tbl AS b            -- go back to get other columns
        USING(customer_id);


Which columns to index with LEFT JOIN and GROUP_CONCAT

You have to create indices for all the fields you have used to bind tables or to restrict results:

wpp.ID
object_id
term_taxonomy_id
wp_terms.term_id
wpp.post_author
s.ID
post_type

Some of fields are already indexed as primary keys so do not duplicate indices, that can impact RAM consumption as well as performance.

First you have to do now - is to launch your query with EXPLAIN prefix and show the result.

N.B.

Always use aliases for joined tables, like third one in your code. Always specify explicitly table you refer to, like wp_terms.term_id or s.id.


If a Parallelism Exchange Event deadlock is victim-less, is it a problem?

I wouldn't be surprised if this is the way the deadlock graph looks when an intra-query parallel deadlock is resolved by an exchange spill (so there is no victim, except performance).
You could confirm this theory by capturing exchange spills and matching them up (or not) to the deadlock.
Writing exchange buffers to tempdb to resolve a deadlock is not ideal. Look to eliminate sequences of order-preserving operations in the execution plan (e.g. order-preserving exchanges feeding a parallel merge join). Unless it's not causing a noticeable performance problem, and you have other things to worry about.

Out of interest, is this problem likely to be exacerbated by high fragmentation/outdated statistics?

Fragmentation, no. Outdated Statistics: not in any specific sense I can think of, no. Of course unrepresentative stats are rarely a good thing generally.
The fundamental issue here is that parallelism works best when there are as few dependencies between threads as possible; preserved ordering introduces rather nasty dependencies. Things can easily get gummed up, and the only way to clear the logjam is to spill a bunch of rows held at exchanges to tempdb.

Note
Intra-query parallel deadlocks no longer generate xml graphs when the deadlock can be resolved with an exchange spill, following a fix released for Cumulative Update 10 for SQL Server 2017 and Cumulative Update 2 for SQL Server 2016 SP2.


MySql shell command not replicated to slave

Have I got a stunning revelation for you on this problem. I saw this type problem before. I have a MySQL client at my employer's web hosting company with this situation :

The client, running MySQL 5.1.37, has two DB servers in Circular Replication (call it M1 and M2)

He performed a LOAD DATA LOCAL INFILE with a 50GB CSV file on M1

Running SHOW SLAVE STATUS\G several times on M2, Log Files and Positions were not moving.

Then, I noticed something very disturbing. When I ran SHOW SLAVE STATUS\G several more times on M2, I noticed this pattern in the output:

Relay_Log_Space was growing
Seconds_Behind_Master was still 0
Still, none of the Log Files or positions were changing

I was stunned because I did not know what was happening. A few hours later, I ran SHOW PROCESSLIST;. To my surprise, there it was : LOAD DATA LOCAL INFILE in the SQL Thread, but the file name was a temp file in the /tmp folder. All of a sudden, replication was more than 6 hours behind. Then, is dawned on me what was actually happening.

I learned that day that for mysqld to replicate LOAD DATA LOCAL INFILE, this is happens under the hood:

When a Master server executes LOAD DATA LOCAL INFILE

mysqld on the Master will...

complete the LOAD DATA LOCAL INFILE command
record the LOAD DATA LOCAL INFILE command its binary logs
dump the enitre data file it just imported into its binary logs

mysqld on the Slave will do the following:

I/O Thread sees LOAD DATA LOCAL INFILE in the relay logs
I/O Thread create the import file need for LOAD DATA LOCAL INFILE
I/O Thread copies the contents of all relay logs making up the import file, storing it in /tmp
SQL Thread updates Seconds_Behind_Master based on when the Master completed LOAD DATA LOCAL INFILE command
SQL Thread executes LOAD DATA LOCAL INFILE using the mainifested temp file

In order to verfiy this is indeed happening:

On the Master

create a MyISAM table in the test database called mycsv
Run LOAD DATA LOCAL INFILE to import a 5 line file CSV file into test.mycsv

On the Slave

See if test.mycsv exists with the imported data in it
See if any file exists in /tmp that has the exact same size as the original import file on the Master

If that is what happened, test that again against a 10 GB file.

While it is replicating, goto into the OS in /var/lib/mysql and do ls -l and look for the relay logs. You should see 10 consecutive relay logs 1GB. That will show it for sure because under normal circumstances, every relay log context switch skips by 3 files not 1. Seeing multiple 1GB relay logs reveal that the data for the LOAD DATA LOCAL INFILE recently executed on the Master is currently being shipped over.

I hope this solves the mystery you have been experiencing.


Procedures, functions are stored in mysql database, triggers in  respective database directories. XtraBackup makes a copy of all of that.

It however doesn't make a copy of MySQL config file (/etc/my.cnf) you should take care of it.