AWS DMS Task getting failed after completion with error as AlwaysOn BACKUP-ed data is not available I have created DMS task for migrating data from one RDS MS SQL Server instance to another RDS MS SQL Server instance with "Full load + ongoing replication", and data copied to target instance but status showing as failed with below error -

Last Error AlwaysOn BACKUP-ed data is not available Task error
notification received from subtask 0, thread 0
[reptask/replicationtask.c:2822] [1020465] Error executing source
loop; Stream component failed at subtask 0, component
st_0_NJZW5VSVPASASA4E4N2SJGTVEZ3UIDLJHX3NDY; Stream component
'st_0_NJZW5VSVASASAZ4E4N2SJGTVEZ3UIDLJHX3NDY' terminated
[reptask/replicationtask.c:2829] [1020465] Stop Reason FATAL_ERROR
Error Level FATAL

Cloud watch logs:

2021-01-04T15:24:02 [SOURCE_CAPTURE  ]E:  Failed to access LSN '0000009a:00000087:0010' in the backup log sets since BACKUP/LOG-s are not available. [1020465]  (sqlserver_endpoint_capture.c:717)
2021-01-04T15:24:02 [TASK_MANAGER    ]I:  Task - PHTLF2WXLFVWESX4GLZ2CKAW3MY76HLRXHIYYYY is in ERROR state, updating starting status to AR_NOT_APPLICABLE  (repository.c:5103)
2021-01-04T15:24:02 [SOURCE_CAPTURE  ]E:  Error executing source loop [1020465]  (streamcomponent.c:1867)
2021-01-04T15:24:02 [TASK_MANAGER    ]E:  Stream component failed at subtask 0, component st_0_NJZW5VSVPJVW47Z4E4N2SJGTVEZ3UIDLJHX3YYY [1020465]  (subtask.c:1409)
2021-01-04T15:24:02 [SOURCE_CAPTURE  ]E:  Stream component 'st_0_NJZW5VSVPJVW47Z4E4N2SJGTVEZ3UIDLJHX3YYY' terminated [1020465]  (subtask.c:1578)
2021-01-04T15:24:02 [TASK_MANAGER    ]E:  Task error notification received from subtask 0, thread 0 [1020465]  (replicationtask.c:2822)
2021-01-04T15:24:02 [TASK_MANAGER    ]E:  Error executing source loop; Stream component failed at subtask 0, component st_0_NJZW5VSVPJVW47Z4E4N2SJGTVEZ3UIDLJHX3YYY; Stream component 'st_0_NJZW5VSVPJVW47Z4E4N2SJGTVEZ3UIDLJHX3YYY' terminated [1020465]  (replicationtask.c:2829)
2021-01-04T15:24:02 [TASK_MANAGER    ]E:  Task 'PHTLF2WXLFVWESX4GLZ2CKAW3MY76HLRXHIYYYY' encountered a fatal error  (repository.c:5196)

What is the issue? any suggestions!

I don't know if you're still working through this, but I wanted to post the problem & solution for anyone else.
There is a bug in the AWS DMS replication engine version 3.4.3 when using RDS SQL Server as a source endpoint, where the 5 minute log backup used for point-in-time recovery with RDS disrupts the CDC process. The solution/workaround is to use a different replication engine version set on the replication instance, such as v3.4.2.
From AWS support:

Root Cause:
Researching internally, I observed this error to be a know issue with
DMS replication instance running the version 3.4.3,
where RDS SQL Server has truncated active transaction logs (TLOG) or
there are no activities in the database. This is a known situation
when using RDS SQL Server as a source.
Why task failed:
DMS 3.4.3 tasks are failing due to missing LSNs in source RDS SQL
Sever databases, when source SQL Server MS-CDC enabled tables remains
idle for a long period of time, the DMS task still fails even when you
increase the MS-CDC polling interval on source database.
This is because MS-CDC polling interval prevents TLOGs truncation only
when there are changes on source tables, if source tables remains
idle, the LSNs does not increment, then the RDS t-log backup job
removes the LSNs from the online t-log that are required by DMS,
causing this issue.
Additionally, DMS task is failing with misleading errors such as:
00016458: 2020-12-28T09:30:08 [SOURCE CAPTURE ]E: Failed to access LSN
'00001002:0001c389:0003' in the backup log sets since BACKUP/LOG-s are
not available. [1020465] (sqlserverendpoint_capture.c:717
This error can still occur even when MS-CDC Polling interval is set to
sufficient value, it happens when the RDS t-log backup job removes an
LSN from the online t-log. This issue is especially seen in databases
with less traffic since the LSNs are not incrementing and DMS is
looking at the last committed LSN.
Workaround:
I would recommend trying to migrate the same tables by creating a
similar task using DMS RI version 3.4.2 or 3.3.4


MongoDB Migrating away from RocksDB to WiredTiger We have been working for some time with RocksDB as our engine and we are now trying to migrate to WiredTiger. We have some pretty big databases around 4~12 TB of data and according to the process described in the docs, we added a new node with WiredTiger and tried letting it replicate from scratch.

With the amount of data, the replication times are VERY long and quite a lot of times we were in the situation were the WiredTiger node decided to change the node it was replicating from, only to drop ALL data and start from scratch again. Only once we succeeded in completing the replication, but the node ended up way behind in comparison to the oplog.

Again with this amount of data it becomes prohibitive to have a big enough oplog to hold weeks of transactions and the process is also very flimsy, single threaded, slow and prone to fail.

So my questions are the following:

Is there a better way to proceed with this migration?
Is there a way to speed up replication (i.e. multithreaded replication)?
Is there a way to tell the new WiredTiger node to stop dropping all data in case of mishaps?

We are working with 3 and 5 nodes replica sets of Percona MongoDB version 3.4.13 and trying to move to Open Source MongoDB 3.4.13 (with the idea of upgrading to 4.x once we are in WiredTiger and dropping RocksDB and Percona entirely).

If replication doesn't keep up with primary and it's not possible to extend the oplog, I'd try to shard the deployment and...

let smaller shards replicate faster

or
drain the original shard and then remove it from the deployment - this solves you storage engine problem, but leaves the replication as laggy as before


Postgresql ignoring a substring in the middle of a string I may have phrased the title of this question badly. I have a documents table that has a filename, path, and file_content_type columns. I want to search against the filename column to determine if there are records with a similar name.

The problem is that a revision suffix (R00, R01, ...) is always appended to the filename column. What our business rules consider to be the same file will have a different names because the version suffix will be different.

We must search for the entire filename, including the extension, but ignore the revision suffix. And I'm not sure how to go about this.

I thought about negating the version suffix, but its dynamic. Using fuzzy search or trigram doesn't seem like a sound choice either, nor using ILIKE or SIMILAR TO.

Here are some examples of what we consider to be the same file, and a different file.

Same file; only difference is the version suffix: R00 vs R01:

LLE-MET-AP-0000-PLA-COB-R00.pdf
LLE-MET-AP-0000-PLA-COB-R01.pdf

Different files; paper number is different 0000 vs 0001

MET-AP-0000-PLA-COB-R00.plt
MET-AP-0001-PLA-COB-R00.plt

Different files; extensions are different:

LLE-0000-PLA-COB-R00.dwg
LLE-0000-PLA-COB-R00.plt

There's one more complication. There may be cases where the revision suffix can take different forms:

With a leading zero: R01, R02 ...
Without a leading zero: R1, R2 ...
Different separators, and lettercase: XXX-R01, XXX_R01, XXX_r1, etc...

A possible solution is to use a regular expression:

SELECT *
FROM documents
WHERE (filename ~* 'LLE-0000-PLA-COB([.-_ ]{0,}[0-9]{1,2})')

But this has a major disadvantage. I must preprocess the search string to remove any version suffix and then use it in the search. If a record has the filename value of LLE-0000-PLA-COB-R01 I have to remove the -R01 before doing a search, and this is not ideal.

I would simple like to say:

  Find me any files that have this name LLE-0000-PLA-COB-R01.jpg, but ignore the R01 in your search.

What other choices do I have to approach this problem?

One solution is to use regexps to remove that part of the filename. Assuming the separators are known

regexp_replace(filename, '^(.*?)[-_][rR][0-9]+(\.[^.]+)$', E'\\1\\2')

This would assume separators - or _, then r or R followed by at least one number, a dot and then something but never more dots. The part with r/R+numers would be removed.

The efficiency of this depends on the amount of data and how the searches are done. It could be used in an index to speed up searching. For example

CREATE INDEX filename_name_idx ON documents (regexp_replace(filename, '^(.*?)[-_][rR][0-9]+(\.[^,]+)', E'\\1\\2'));

Then doing a search

SELECT filename FROM documents
  WHERE regexp_replace(filename,
    '^(.*?)[-_][rR][0-9]+(\.[^,]+)', E'\\1\\2') = 'LLE-MET-AP-0000-PLA-COB.pdf';

would use index (if the query optimizer deems it faster).

Do note that the regexp part has to be exactly the same in the index and the search, otherwise the index cannot be used. You can also combine it with LOWER() if case insensitive searching is required.

The same regexp can be used to remove the revision identifier from the search string also, if there is a need to search for matching files for, e.g., LLE-MET-AP-0000-PLA-COB-R00.pdf

Making a view with this would make it a lot nicer to use and the actual regexp would be in the database and not in the application layer.


Designing a user authenication (Roles & Rights) module I am trying to model a User Authentication module for a MS SQL Server database that will be the back end to a Delphi UI Application. Basically, I want to have user accounts where the user belongs to only one group. A group can have "n" number of rights.

I also want to add password history to the database, as the user will be required to change their password based on a application setting (example, every 90 days).

I also want to log an event for each time a user logs in and out. I may extend this to additional events in the future.

Below you will find my first crack at it. Please let me know any suggestions to improve upon it, as this is my first time doing this.

Do you see any need for additional attributes for role-based security and constraints for the password rules/expiration periods?

Based on your stated requirements, your model is in pretty good shape.

Here are some suggestions for improvement:

You don't say so explicitly, so it's hard to say - but it looks like you might be storing the user password directly.  This would be very bad!  If you look at common authentication databases, passwords are stored in encrypted form.  You often see both a password column and a password_salt column.
Your USER_LOGS table has an Event column.  You aren't clear about how this is to be populated.  Should there be an EVENT_TYPE table which USER_LOGS references?  This might make for friendlier reporting.  Typical events would include log-in, log-out, password fail, password change, password reset, lock-out, unlock, ...
Your GROUP_RIGHTS table doesn't indicate who granted the rights.  For audit trail purposes, people often keep a log of who changed what record and when.  That may not be an issue for you.

Here's a couple of questions around your stated business requirements, which differ from the "text book" role-based security pattern in a couple of ways:

Are you sure you want users to be in only one group?  The advantage of role-based security is that the roles tend to be pretty static, whereas the people fulfilling roles come and go pretty often.  Included in this is that some people often "wear two hats".
Your design is grant-only.  Some systems include grant and revoke.  This allows you to say that a widely available right is not available to a particular group.
You have users and accounts comingled as USERS in your design.  There is often a distinction between people and user IDs.  Some user IDs are for teams or machines and some people have multiple user IDs for different purposes.  Is this a distinction that would be helpful to you?


Can anything reveal that 2 independant entries were created or updated at the same time? Imaginary example, simplified to better explain the question.
Let's say I have a form with the following fields :

user email
who did you vote for in 2012
who did you vote for in 2016

When submitted, I fill the following PostgreSQL (v.11) DB tables :

"UserList" table

Serial (auto-increment INT)
Email (text)

"UserData" table

Unique_Random (INT)
Vote_2012 (text)
Vote_2016 (text)

The entries are (as far as I can tell) unlinkable across tables because there is no relation between the Serial and the Unique Random ints.

Threat model : attacker gains full control of the postgreSQL DB server (both hardware and software)

If I am correct, UserData entries are not inserted in any specific position when created, so it should not be possible to tell which was added last.

Is there anything else (logs, data position on disk, memory, ...) that can reveal which entries were created or updated at the same time (and thus are linked) ?

If so, what can be done to prevent this ?

Rows inserted at the same time, particularly in the insert-only use case such as yours, will very likely reside next to each other on disk and as a result will have tuple IDs indicating that. They will remain so until you CLUSTER the table using  your unique_random1 column as the index key. You will need to run CLUSTER periodically to shuffle newly inserted rows.

Another bit of information that potentially correlates inserted rows is their transaction ID (XID), which might remain unchanged after the original commit, unless these rows at some later time are updated by separate transactions. I don't believe CLUSTER changes row XIDs.

Basically the same information -- the transaction ID, along with the row data, -- will also be stored in the write-ahead log (WAL). WAL segments will get overwritten at some point, depending on your database's checkpoint frequency.

TL;DR: You probably shouldn't rely on Postgres internals for reliable deidentification of your data.

1 -- Although, what's truly random, cannot have guaranteed uniqueness. You probably meant some sort of a hash.


Using parameters in stored procedure with plpgsql I am trying to return the count of overlapping polygons. The problem is that it complains about my "$1" signs:

Error is "SQL state: 42601", syntax error at "$1".

Why is this? I'm a total beginner here and I can't find the solution online, other than that parameters is referenced to with $ sign.
CREATE OR REPLACE FUNCTION any_overlap (x text)
RETURNS integer AS $$
DECLARE amount INTEGER;
BEGIN
    SELECT COUNT(*) INTO amount FROM $1 a
    INNER JOIN $1 b ON
    (a.polygon && b.polygon AND ST_Relate(a.polygon, b.polygon, '2********'))
    WHERE a.ctid != b.ctid;
    RETURN amount AS id;
END; $$ LANGUAGE plpgsql;

Problems:

You cannot parameterize identifiers (like table names) in plain SQL. You need dynamic SQL using EXECUTE for this.
amount is integer, but count() returns bigint.
RETURN amount AS id; is invalid syntax. To force a particular column name for a returned scalar (not a set-returning function), use an OUT parameter. Example:

Get latest timestamp from column in given table dynamically

We are talking about a function, not a stored procedure. See:

In PostgreSQL, what is the difference between a "Stored Procedure" and other types of functions?

This function would work:
CREATE OR REPLACE FUNCTION any_overlap (_tbl regclass, OUT id bigint)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format(
   $q$
   SELECT count(*)
   FROM   %1$s a
   JOIN   %1$s b ON a.polygon && b.polygon AND st_relate(a.polygon, b.polygon, '2********')
   WHERE  a.ctid <> b.ctid
   $q$, _tbl)
   INTO id;
END
$func$;

Call:
SELECT any_overlap ('public.mytable');

Be wary of SQL injection when using dynamic SQL! Note the type regclass for the passed table name. See:

Table name as a PostgreSQL function parameter


How to Set Up a Shadow Production Database for PostgreSQL? I'm working on a Django project and I'd like to avoid the common pitfall of using SQLite for development and PostgreSQL for production referenced in Two Scoops of Django.

I'd like to take this a step further and use a "shadow production database" (shadow prod DB) for PostgreSQL. How can I get this set up? I think that once the shadow prod DB is set up and configured, swapping out the database settings from "production" to "development" (shadow Prod) will be trivial in the Django application's settings.py file.

However, when it comes to setting up the framework where the "shadow prod DB" is actually recording all of the same transactions that are occurring in the production database I have no idea where to start. Any help is greatly appreciated.

Further, what do I do if I want to get the shadow prod DB onto a laptop. What happens when the database size grows prohibitively large?

Thanks, Scott.

For posterity, I originally heard about the concept of a "shadow prod DB" on the podcast Scaling Postgres which linked to this post from Heap.

One option is to use a "hot standby" server, either with log shipping or with streaming (or both).  This will automatically apply changes from the master into the replica whenever there is connectivity. The problem is that the hot standby server is read only.  If your Django never needs to write into the database, this is fine, but if it needs to write this will not work.  So this will likely not work, as most applications will write into the database when someone logs in, even if the application is only used for reporting purposes.  Another potential problem is that unlogged tables are not available on the hot standby.

Another possibility to set up a standby server, and then promote it when you need to use it.  When you promote it, it will become writable, but it will sever the connection to the master and so will slowly diverge.  You will have to recreate it every now and then in order to bring them back into sync.  If you need to minimize downtime, you can be using the old promoted copy while you are re-establishing a new standby copy in the background.  This is a bit more work, and requires you to have enough storage for two full copies of the database (although it could be less if your master changes slowly and your file system supports fancy deduplication)

You could use logical replication to replicate changes from the master into a "live" writable test database.  This is a lot more work as you need to go through all the tables and decide which ones should have changes replicated from the master and which should stand alone on the replica after the initial sync, and which ones need to do both with some kind of conflict resolution.  And if the purpose of this dev database is so that you can test work that is likely to have bugs and cause bad data to get into your database and you want to protect the production database from getting corrupted with this bad data, this won't work very well.  Locally caused corruption will remain in the local database, replicating new changes from the master is unlikely to fix the bad local data, so you will need to do a full refresh to get rid of the bad data.  So overall, this method will probably be more work but no more effective than the previous solution.  This also has the problem that it will not replicate schema changes.  So if your master schema has not been stabilized but is still getting new tables, new columns, etc. this will be a nightmare.

  Further, what do I do if I want to get the shadow prod DB onto a laptop. What happens when the database size grows prohibitively large?

Either create a demo database which has enough data to serve its purpose, and forget about trying to keep it up to date; or get a bigger laptop.  If really really need to have all the latest data from the master, but don't need the old stuff, then you can use logical replication to do that.  You can create your subscription with "copy_data false" to avoid copying the historical data.  Presumably you need at least some history, but it would then be on you to figure out how much and how to get it.


How to point tablespace in Oracle's pre-built Developer Day VM to another mount point I have downloaded and started up Oracle's pre-built OTN Developer Day VM. The VM is running on an SSD. I want to load some data for development purposes. I am planning to create a new user and load the data under this new user's schema.

I would like to keep this data outside the SSD. I already have a mount point to an HDD inside the VM (as a network share). This mount point is a Samba share from another VM in the same machine.

My question is, how do I create a tablespace for the datafiles on this HDD mount point?

It would be nice if I can point undo and temporary tablespaces also to the HDD mount point.

My objective is to keep the VM size as low as possible.

I will appreciate if someone can point me in the right direction.

NFS
I highly recommend you change your design to use a supported file system : NFS.
The database can use its own NFS client driver (Direct NFS) to talk to the NFS Server.  It will use the OS's if it can't use DNFS.
UNDO/TEMP
I'd keep these on the SSD and set AUTO EXTEND to NO.


Query to insert records from one table into another table based on the column of the first table I have a table with the structure below :

Table(customer_Num  int , Channel_Id  Varchar(50) ,  Branch_Code  int)

and some example data for this table is :

                  Table
__________________________________________
customer_Num  |  Channel_Id  |  Branch_Code
    1         |    'I'       |     1000
    1         |    'Y'       |     1001
    1         |    'L'       |     1002

and the final result I want in the output is :

                          Result_table
__________________________________________________________
customer_Num  | I_branch_Code |  Y_branch_Code  | L_Branch_Code
    1               1000             1001             1002

is there a better solution rather than using continuous Insert  queries? Cause with this query
I will have many nulls in my final table!And I also won't have the result in one record.

 insert into Result_table (customer_Num  ,I_branch_Code )
 select customer_Num   , Branch_Code
 from Table
 where Channel_Id   = 'I';

 insert into Result_table (customer_Num  ,Y_branch_Code  )
 select customer_Num   , Branch_Code
 from Table
 where Channel_Id   = 'Y';

insert into Result_table (customer_Num  ,L_Branch_Code)
select customer_Num   , Branch_Code
from Table
where Channel_Id  = 'L';

You're looking for the PIVOT clause. You can see a working example in this db<>fiddle.

The query is:

SELECT customer_num
  ,p.I AS I_Branch_Code
  ,p.Y AS Y_Branch_Code
  ,p.L AS L_Branch_Code
FROM
(
  SELECT customer_num
    ,Channel_Id
    ,Branch_code
  FROM [table]
)s
PIVOT
(
  MAX(Branch_Code) FOR Channel_Id IN ([I], [Y], [L])
) p


How can I output text until it is ascii in SQL? How can I output text until it is ascii in SQL?
Here is what I am trying to do:
DECLARE @input VARCHAR(20)
SET @input = 'text'

DECLARE @index INT
SET @index = 1

DECLARE @output VARCHAR(32)
SET @output = ''

WHILE CHAR(ASCII(SUBSTRING(@input, @index, 1))) LIKE '[ -~]'
BEGIN
    SET @output = @output + CHAR(ASCII(SUBSTRING(@input, @index, 1)))
    SET @index = @index + 1
END

SELECT @output

But in the end I am getting an empty string. Why? What am I missing here?
I am expecting the value of the @output to be 'text' in the end of the script.
UPDATE
If I update the script to the
DECLARE @input VARCHAR(20)
SET @input = 'text'

DECLARE @index INT
SET @index = 1

DECLARE @output VARCHAR(32)
SET @output = ''

WHILE CHAR(ASCII(SUBSTRING(@input, @index, 1))) LIKE '[a-z]'
BEGIN
    SET @output = @output + CHAR(ASCII(SUBSTRING(@input, @index, 1)))
    SET @index = @index + 1
END

SELECT @output

It will work as expected. But here I just shrinked the set from all printable ascii characters to only small letters. Why does the shrinked set [a-z] include the text characters and the extended set [ -~] does not?

A space is not a valid range delimiter so LIKE [ -~] will not work. That becomes a test for those three characters only.
You could check the ASCII code directly, rather than using LIKE:
DECLARE @input VARCHAR(20)
SET @input = 'text'

DECLARE @index INT
SET @index = 1

DECLARE @output VARCHAR(32)
SET @output = ''

WHILE ASCII(SUBSTRING(@input, @index, 1)) BETWEEN 32 AND 126
BEGIN
    SET @output = @output + CHAR(ASCII(SUBSTRING(@input, @index, 1)))
    SET @index = @index + 1
END

SELECT @output

demo
Side note: It's possible to get unexpected results with LIKE ranges because the comparison is collation-aware.


How to Deny permissions for newly created user in DDL Trigger? I'm having trouble with a SQL DDL trigger for Create_User and I'm wondering if there is an issue with my syntax or something else I'm not seeing?

I have some tables in my database which are only available to a few users. The rest of the users in the database are not allowed to query these tables in any way. I've had no problems limiting current user permissions with

DENY SELECT, INSERT, UPDATE, DELETE ON dbo.SecretTable TO UserName

I'm trying to set up a DDL trigger to automatically run the Deny command when any new users are created. I am not the only one creating users in the database, so I won't be able to manually set permissions myself when new users are added.

With my trigger, I constantly receive the error

Cannot find the user 'UserName', because it does not exist or you do not have permission.

If I change the UserName placeholder to an already existing user in the trigger, I do not receive any errors - so it is not a permissions issue for my account. Here is my trigger:

CREATE TRIGGER [NewUserCreation]
ON DATABASE
After Create_User
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @x xml = EVENTDATA();
    SELECT UserName = @x.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(max)');
    DENY SELECT, INSERT, UPDATE, DELETE, ON dbo.SecretTable TO [UserName]

END
GO

ENABLE TRIGGER [NewUserCreation] ON DATABASE
GO

Is there something I'm missing? The test results I've experienced so far seem to imply that the trigger is firing before the user account is actually created.

I think you'll have to use dynamic SQL for this. Something like

create TRIGGER [NewUserCreation] ON DATABASE
After Create_User
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @x xml = EVENTDATA();
    declare @UserName Varchar(30)
    SELECT @UserName = @x.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(max)');
    declare @Cmd nvarchar(100)
    set @cmd = 'DENY SELECT, INSERT, UPDATE, DELETE ON dbo.SecretTable TO ' + QUOTENAME(@UserName)
    exec sp_executesql @cmd
END
GO

ENABLE TRIGGER [NewUserCreation] ON DATABASE
GO

The reason your original code did not work is because your select username merely gave an alias UserName to the value returned from the select.  You are not able to use that column alias in the DENY.  Note that my example puts the value into a variable @UserName.  Then, I build a dynamic DENY statement and externalize the value of @UserName which is concatenated to the DENY command.

You can see this in action with this code snippet.  Assume I'm adding NewUser as a login and that is the value from the xml.

Declare @UserName varchar(100)
SELECT @UserName = (select 'NewUser')
declare @Cmd nvarchar(100)
set @cmd = 'DENY SELECT, INSERT, UPDATE, DELETE ON dbo.SecretTable TO ' + QUOTENAME(@UserName)
print @cmd

DENY SELECT, INSERT, UPDATE, DELETE ON dbo.SecretTable TO [NewUser]


Check 2 tables for differences, use programming language or pure SQL I'm trying to check 2 tables (10k plus rows) for inconsistent data. e.g. both table have a "id" and "price" column and I want to know when id are equal are the price also equal. I would like to create a output that tells me exactly which id have different/inconsistent prices.  I have to do this check 3 times where 1a,1b,1c is all on different servers.
table1 - table1a
table1 - table1b
table1 - table1c

Is this more efficiently done with pure SQL or with a programming language + SQL (python with pyodbc or java with jdbc etc). Since I'm new to SQL I don't know how trivial this would be with pure SQL.

Assuming you have access to all three servers from one of the servers, you can do this easily in SQL. I'm not sure if you meant Microsoft SQL Server when you tagged your question as "sql-server" (which is the tag for specifically Microsoft SQL Server), since you mention Python and Java so I won't go into the specifics of setting up linked servers or remote querying.
But in general this is an ANSI SQL way of comparing two tables and getting only the differences (so this will work in Microsoft SQL Server, Oracle SQL, MySQL, PostgreSQL, etc):
SELECT * -- Replace * with the actual columns you want, as SELECT * is bad practice
FROM table1 -- Source table
INNER JOIN table1a -- Server 1's Table
    ON table1.id = table1a.id -- Same id
    AND table1.price <> table1a.price -- Different price

If you want one dataset combined of the differences from all three servers this is how you can do it:
SELECT * -- Replace * with the actual columns you want, as SELECT * is bad practice
FROM table1 -- Source table
INNER JOIN table1a -- Server 1's Table
    ON table1.id = table1a.id -- Same id
    AND table1.price <> table1a.price -- Different price

UNION ALL

SELECT * -- Replace * with the actual columns you want, as SELECT * is bad practice
FROM table1 -- Source table
INNER JOIN table1b -- Server 2's Table
    ON table1.id = table1b.id -- Same id
    AND table1.price <> table1b.price -- Different price

UNION ALL

SELECT * -- Replace * with the actual columns you want, as SELECT * is bad practice
FROM table1 -- Source table
INNER JOIN table1c -- Server 3's Table
    ON table1.id = table1c.id -- Same id
    AND table1.price <> table1c.price -- Different price

The above uses the UNION ALL operator to bring back all 3 datasets as a single dataset.
Per your comments that this is Microsoft SQL Server, then you can look into using Linked Servers and OPENQUERY. Additionally these resources are particularly helpful for setting up access to remote servers:

MS Docs on Remote Servers
Querying remote data sources in SQL Server

To answer your main question, in short, yes this is a perfect data problem that can be done purely and simply in SQL.


Reset MySQL table data after each session is completed in php I am creating a PHP application demo. The users who login can insert, modify and delete data but when they logout the data must be reset to the default data I set before.

The data must reset depending on the session so that multiple users can access the default data at the same time.

Is this possible using MySQL?

UPDATE 1:
I have created a stored procedure to create a new db and dump the default data, but I am getting an error while using the USE db_name in a procedure.

NOTE: I am using phpMyAdmin.

Error: #1314 USE is not allowed in stored procudures

Here is the stored procedure i have created with sample data.

BEGIN

CREATE DATABASE IF NOT EXISTS `dp_demo2` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `dp_demo2`;

DROP TABLE IF EXISTS `customers`;
CREATE TABLE IF NOT EXISTS `customers` (
  `cid` int(10) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `company` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  `phone` varchar(15) NOT NULL,
  `skype_id` varchar(40) NOT NULL,
  `address` varchar(200) NOT NULL,
  `city` varchar(50) NOT NULL,
  `country` varchar(50) NOT NULL,
  `zip_code` int(10) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `customers`
--

INSERT INTO `customers` (`cid`, `first_name`, `last_name`, `company`, `email`, `phone`, `skype_id`, `address`, `city`, `country`, `zip_code`, `created`) VALUES
(1, 'Julie', 'Penning', 'Apple', 'juli@example.com', '0402011', '@julie', '20-120, Street', 'Calfornia', 'USA', 40192, '2016-07-16 04:05:22'),
(2, 'Karl', 'Max', 'Ham. co.', 'karl@example.com', '9828101', '@max.karl', '20-820, Street', 'New York', 'USA', 82918, '2016-07-16 04:06:57'),
(3, 'Jhon', 'Doe', 'Microsoft', 'jhon@example.com', '892123', '@jhone.doe', '9-21-23, ham street', 'Troy', 'USA', 83082, '2016-07-16 04:08:48');

END

UPDATE 2:

I am able to create new db and use them, but i got another problem, how can I get dynamic db_name into the stored procedure.

Parameters for the stored procedure is not working for names, but only for the data.

Setup:

Take a mysqldump of the 'default' data.
Put the output into a Stored Procedure.
Modify the stored procedure to also CREATE DATABASE and USE that database.  The database name would be dynamic -- probably the user id, with something prepended to avoid naming conflicts.  Also surround with backtics.

User comes in:

perform that stored procedure.

User leaves.  This could be tricky -- what if he stays logged in?  what if some server dies without getting to this code?  Etc.  There may need to be something that runs around periodically to clean up orphaned databases.

Drop his database.

Note:  Your question implied that everyone is using the same database.  I switched to having everyone use a different database.


Oracle GROUP BY question I am using Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production. Yes an ancient version but the license is embedded in an application that we use and this is the Oracle version that they use.

Here is an SQL query that I am using:

SELECT     EntryDate, COUNT(OrderNo)
FROM       Orders
GROUP BY   EntryDate;

I get results like:

EntryDate COUNT(OrderNo)
--------- --------------
01-NOV-12           543
02-NOV-12           555
03-NOV-12            91

There are currently 472 rows with dates up to today, so the above is only a small snapshot of results.

Is it possible to group the results by month instead of the current date i.e. get results like:

EntryDate COUNT(OrderNo)
--------- --------------
Nov12     X
Dec12     X
Jan14     X

Round to the beginning of the month:

SELECT     trunc(EntryDate,'MM'), COUNT(OrderNo)
FROM       Orders
GROUP BY   trunc(EntryDate,'MM');

In oracle 8i the results are sorted because of the internal algorithm used by the database to do the 'GROUP BY'. In higher Oracle version beginning with 10g a different algorithm is used and the results will not be sorted. So if you want a sorted result you should tell it the database by querying

SELECT     trunc(EntryDate,'MM'), COUNT(OrderNo)
FROM       Orders
GROUP BY   trunc(EntryDate,'MM')
ORDER by   trunc(EntryDate,'MM');

even if the result will be  sorted by accident.

To get exactly the output of your query execute the following statement:

SELECT     to_char(trunc(EntryDate,'MM'),'MonYY'), COUNT(OrderNo)
FROM       Orders
GROUP BY   trunc(EntryDate,'MM')
ORDER by   trunc(EntryDate,'MM');


Use an Alias in Where Clause Subquery I need to show some fields from another table in Oracle. Here is my query:

SELECT
ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI AS KODE,
ANGGARAN.SIMPEG_PEGAWAI.NAMA,
ANGGARAN.SIMPEG_PEGAWAI.NIP,
ANGGARAN.SIMPEG_ESELON_JABATAN.JABATAN,
ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.GOLONGAN,
ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.PANGKAT,
    (SELECT * FROM (SELECT CONCAT(TO_CHAR(abs(sysdate
    - TO_DATE(TMT_JABATAN))/360,'9,999,999.9'),' TAHUN')
       FROM SIMPEG_JABATAN where ID_PEGAWAI=KODE ORDER BY TMT_JABATAN desc)
    WHERE ROWNUM = 1) AS MASA_KERJA
FROM
ANGGARAN.SIMPEG_PEGAWAI
    INNER JOIN ANGGARAN.SIMPEG_ESELON_JABATAN
        ON ANGGARAN.SIMPEG_PEGAWAI.ESELON_JABATAN =
        ANGGARAN.SIMPEG_ESELON_JABATAN.ID_ESELON_JABATAN
    INNER JOIN ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT
        ON ANGGARAN.SIMPEG_PEGAWAI.PANGKAT =
        ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.ID_GOLONGAN_PANGKAT
WHERE
    ANGGARAN.SIMPEG_PEGAWAI.ST_AKTIF = 1 AND
    ANGGARAN.SIMPEG_PEGAWAI.ESELON2 <> 1 AND
    ANGGARAN.SIMPEG_PEGAWAI.PANGKAT >= 12 AND
    ANGGARAN.SIMPEG_ESELON_JABATAN.STATUS = 1 AND
    ANGGARAN.SIMPEG_ESELON_JABATAN.ID_ESELON2=2
ORDER BY
ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.SORT DESC

The result I got:

[Err] ORA-00904: "KODE": invalid identifier

KODE come from query ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI AS KODE, and used for this query :

(SELECT *
        FROM (SELECT CONCAT(TO_CHAR(abs(sysdate -
    TO_DATE(TMT_JABATAN))/360,'9,999,999.9'),' TAHUN')
            FROM SIMPEG_JABATAN where ID_PEGAWAI=KODE ORDER BY TMT_JABATAN desc)
WHERE ROWNUM = 1) AS MASA_KERJA

Do I miss something? Or could be using an alias in subquery wrong in the where clause in Oracle?

edited:
I have solved this problem, thanks for every one, here it is:

select
  e.nama as nama,
  e.nip  as nip,
    f.golongan as golongan,
    f.pangkat as pangkat_golongan,
    g.jabatan as jabatan_akhir,
    sub.tmt_jabatan as lama_menjabat,
    sub.kualifikasi as kualifikasi,
    sub.hari as hari_kerja
from
  simpeg_pegawai e,
  (
    select
      j.id_pegawai as id_pegawai,
            extract(year from (sysdate - j.tmt_jabatan) year to month) || ' Tahun ' ||
            extract(month from (sysdate - j.tmt_jabatan) year to month) || ' Bulan '
            tmt_jabatan,
            extract(year from (sysdate - j.tmt_jabatan) year to month) kualifikasi,
            trunc((((86400*(sysdate-j.tmt_jabatan))/60)/60)/24) as hari,
      row_number()
        over (
          partition by j.id_pegawai
          order by     j.tmt_jabatan desc
        ) as job_order
    from
      simpeg_jabatan j
  ) sub,simpeg_kode_golongan_pangkat f, simpeg_eselon_jabatan g

where
        sub.id_pegawai = e.id_pegawai
        and e.pangkat = f.id_golongan_pangkat
        and e.eselon_jabatan = g.id_eselon_jabatan
        and g.id_eselon2 = 2
        and g.status = 1
        and e.st_aktif=1
        and e.eselon2=2
        and e.pangkat >= 12
        and sub.job_order = 1
        and sub.kualifikasi > = 2
order by sub.hari desc

you cant use alias from the wraping query in the a sub query
change it this way

SELECT
ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI AS KODE,
ANGGARAN.SIMPEG_PEGAWAI.NAMA,
ANGGARAN.SIMPEG_PEGAWAI.NIP,
ANGGARAN.SIMPEG_ESELON_JABATAN.JABATAN,
ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.GOLONGAN,
ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.PANGKAT,
    (SELECT *
            FROM (SELECT CONCAT(TO_CHAR(abs(sysdate - TO_DATE(TMT_JABATAN))/360,'9,999,999.9'),' TAHUN')
                FROM SIMPEG_JABATAN where ID_PEGAWAI=ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI  ORDER BY TMT_JABATAN desc)
    WHERE ROWNUM = 1) AS MASA_KERJA
FROM
ANGGARAN.SIMPEG_PEGAWAI
    INNER JOIN ANGGARAN.SIMPEG_ESELON_JABATAN
        ON ANGGARAN.SIMPEG_PEGAWAI.ESELON_JABATAN = ANGGARAN.SIMPEG_ESELON_JABATAN.ID_ESELON_JABATAN
    INNER JOIN ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT
        ON ANGGARAN.SIMPEG_PEGAWAI.PANGKAT = ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.ID_GOLONGAN_PANGKAT
WHERE
    ANGGARAN.SIMPEG_PEGAWAI.ST_AKTIF = 1 AND
    ANGGARAN.SIMPEG_PEGAWAI.ESELON2 <> 1 AND
    ANGGARAN.SIMPEG_PEGAWAI.PANGKAT >= 12 AND
    ANGGARAN.SIMPEG_ESELON_JABATAN.STATUS = 1 AND
    ANGGARAN.SIMPEG_ESELON_JABATAN.ID_ESELON2=2
ORDER BY
ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.SORT DESC

remember, sql engine process a query in the following order

from
where
group by
having
select

so, when you execute the inner query the sql engine does no know the aliad KODE yet.


SQL Server transactional replication process and error I need to setup a replication.

Which replication is better if I want to keep my data updated in the second server as soon as new data is pushed into the primary server.
I tried setting up by clicking on New Publication and got error message:-

So I found I need to use sp_addserver. How does it work, In which table it will add the server name. How will it affect my current setup if I change the server name. Will my APIs or other services connected to this server will start failing if I chnage the server name.

A step by step guide for whole replication setup with details would be helpful.

Possible cause might be :

This error has been observed on a server that had been renamed after the original installation of SQL Server, and where the SQL Server configuration function ‘@@SERVERNAME’ still returned the original name of the server. This can be confirmed by:

select @@SERVERNAME
 go

This should return the name of the server. If it does not then follow the procedure below to correct it.

Please check these links below :

https://stackoverflow.com/questions/8212032/sql-server-replication-requires-the-actual-server-name-to-make-a-connection-to-t

http://www.cryer.co.uk/brian/sqlserver/replication_requires_actual_server_name.htm

http://blog.sqlauthority.com/2015/09/18/sql-server-fix-replication-error-sql-server-replication-requires-the-actual-server-name-to-make-a-connection-to-the-server/

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a4d1d008-05f5-46f2-a145-347cbf4d9e5c/sql-server-replication-requires-the-actual-server-name-to-make-a-connection-to-the-server-specify?forum=sqlreplication


How to automatically Reset Query Cache for improve table cache hit in MySQL? I have a VPS running CentOS 6.4 and 2.5GB RAM, Intel(R) Xeon(R) CPU. Its MySQL version is  Server version: 5.5.35-cell. I have two active wordpress sites and two inactive sites (inactive sites use for some test and one for personal use) in this server.

This VPS MySQL configuration file has following query cache.

thread_cache_size = 16

query_cache_type = 1
query_cache_size = 30M
query_cache_limit = 1M

After 30 minutes left, my table cache hit rate goes to the lower level. After 1h 49m 0s MySQL up-time, it's 4% (334 open / 7K opened).

I think to overcome this issue, I have to reset query cache, rather than the flush query cache. Therefore, how do I automatically Reset Query Cache for optimize MySQL table caching?

The query cache is only helpful for low write, high read workloads and should be pretty handy on a blog with this access pattern. If you issue any DML then you invalidate the QC for that table and repopulation would be necessary. Have you calculated the QC efficiency based on your local status?

http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/

The table cache is a different animal. It caches tables in memory so that MySQL can avoid the overhead of opening them. There's a recipe for tuning this, which is something along the lines of depth of longest join * max_connections but I doubt this us causing you trouble. It's likely that you would be better off tuning the key_buffer (MyISAM's cache for indexes) or the Innodb_buffer_pool_size (InnoDB cache for data + indexes) to reduce load on the server.

http://www.mysqlperformanceblog.com/2009/11/16/table_cache-negative-scalability/


Importing huge dumps into a mysql server 5.5 I want to import a couple of huge .sql dumps (~600G each) into a mysql server 5.5 running on an Ubuntu 14.04.

The machine has about 8G of RAM allocated to it;

The previous machine had 128G;

Storage space considerations aside, what else should I take into account before performing the mysql import command?

If your database tables have them, it's always best to drop indices and recreate them after the import. Also, disable foreign keys temporarily by executing SET FOREIGN_KEY_CHECKS=0; (and SET FOREIGN_KEY_CHECKS=1; afterwards). This will speed up your import (though by how much is hard to tell).


Single DB shared across multiple servers I've planned to host many servers (50+) each will have more than 20K users stay connected. There is lot of shared data amongst users (e.g. a user needs to refer data prepared by another user).

If I host single database server (preferably Microsoft SQL server), all the (50*20000) queries will be queued to that single server which could be major performance bottleneck. Is it possible to have multiple servers sharing single database. So that it will enable me to distribute my queries across multiple database servers. If this is not possible, what are the options available?

Update: This is completely new game application being developed and I want the design to be highly scalable. As each user's score changes (increases or decreases by certain number) it will be updated to the server. In turn Server will reply with "list of nearest 50 users" to that score. This is planned to happen in not less than 1 minute per user.

Thus if DB table is indexed on score, we can say single write and 50 reads will happen per minute by each user. Now if 20K users are connected to each "connection server" and all those "connection servers" in turn querying single DB Server, it is effectively queuing all the million requests to single DB Server. Which obviously cannot complete all those requests in a minute.

Hence, I was looking if we can distribute those queries across multiple SQL servers which share single database.

No, you can't have the same data from a single database shared across multiple servers, at least not in read/write mode. At the risk of ignoring that you might be prematurely optimizing here, there are some pretty common ways to scale SQL Server, but they aren't all as simple as you might expect, and they also might not meet all of your requirements (or budget, since for many of the options Enterprise Edition is required).

READ/WRITE

For read-write scaling you can "shard" data using federated servers and distributed partitioned views. But note that is not easy to set up. Another options is merge replication but it can also be a bear to set up and maintain.

READ ONLY

You can scale out reads only to multiple other servers by using Log Shipping (or simple backup / restore), transactional replication, Database Mirroring (though this feature is deprecated, so be careful about long-term projects) and Database Snapshots of the mirror, or in SQL Server 2012, AlwaysOn Availability Groups with readable secondaries. (Some good but admittedly biased comparisons with competing vendor solutions here.)

As an aside, I really don't like the term "active secondaries" as that implies to me that you can read/write to them, which you can't.

EDIT

Given the new requirements in the updated question, it seems the part that needs to scale is almost entirely read. One suggestion might be to have a small instance on each application server (could be Express so long as its resource limitations - most importantly 10GB data file - are sufficient). This instance would serve two main purposes: (1) queue updates to the primary server so that the application doesn't have to wait for them (and so that multiple updates from different users can be combined) and (2) serve as a data store for the read-only information so that this doesn't always have to ve retrieved from the primary server. With this idea there isn't really a simple "turn it on" feature that will help accomplish this, but the setup is pretty trivial in the grand scheme of things.


Why use WHERE 1 or WHERE 1=1? Usually, if conditions are not required in our query statements, we don't use a WHERE clause. But I've seen a WHERE 1 clause being used in many places, even where other conditions are not present.

Why is this done?
Are there specific benefits to the execution time?
Does it enable other functionalities?
Is using WHERE 1=1 similar to this?

Basically it is just for programmer convenience since you can just add additional conditions with AND... after that and it has no impact on execution time.

Check out these links to Stackoverflow:

Why would someone use WHERE 1=1 AND  in a SQL
clause?
“where 1=1”
statement

Note that WHERE 1 is identical to WHERE 1=1; both mean WHERE TRUE but the former is rejected by many database management systems as not really being boolean.


Plan changes to include Eager Spool causes the query to run slower We have a reporting query which is erratic in terms of execution plan and run duration.
It is either 5 seconds or as slow as 5 minutes.
The query is a Select statement with no DML involved.
One thing I noticed is the costly Eager Spool operator after an index scan in slow executions.
However, index seek is used when executing fast.
I updated the stats but it didn't help to speed up.
I was just wondering why sometimes optimiser chooses to use Index Scan (and Eager Spool) rather than Index Seek and what causes it to do so?
Attaching SS of both slow and fast execution plans. SS is saved as a file from Plan Explorer so may upload some other tabs if need be.
Thank you
Edit: The query runs fast in the mornings, slow in the afternoons, always.

Your queries are using totally different indexes, which means the optimizer is choosing different plans based on different parameters.

You're able to see the difference with the recompile hint (which means parameter sniffing is involved), which is good. How you fix it is up to you.

In the fast plan, these indexes get used:

Object 13: Index 2, Object 12: Index 3, Object 7: Index 4.

In the slow plan, these get used.

Object 13: Index 3, Object 12: Index 4, Object 7: Index 5.

Since the plan is anonymized, and you've only posted pictures of it, what you need to do is look at which parameters were used in each execution, and try out using the OPTIMIZE FOR (please for the love of Joe Sack don't use UNKNOWN) hint to see if the 'fast' plan is still fast for the set of parameters used to get the slow plan.

It may not be, which is totally possible. From there, you'd need to look at the query and indexes to tune things so you have a better general plan. It'll be really hard to help if you keep posting anonymized stuff, though.


How to Deal with Performace counters Recently we got this alert from monitoring team.

  SQLServer:General Statistics Logins/sec has exceeded the threshold of
  2

I tried to know a bit about it and if the value is less than

  2 per second indicates that the application is not correctly using connection pooling.

What does Application is not correctly using the connection pooling --  What does it mean ?

And few DMVs might help to find out the Logins/sec like the session_id and application

sys.dm_exec_sessions
sys.dm_exec_connections
sys.dm_exec_requests

But I am not able to connect the link between the Logins/sec and session ids.
Can anyone help me to understand how to deal with performace counters and does number of connections = number of session_ids (SPIDs) ,is this true ...Please help.

Thanks.

From the documentation Connection Pooling is:

  Connecting to a data source can be time consuming. To minimize the
  cost of opening connections, ADO.NET uses an optimization technique
  called connection pooling, which minimizes the cost of repeatedly
  opening and closing connections

Basically the provider keeps connections open, even if the code that opened them closes them. The next time the provider needs a connection the already open one is used.

These open connections are grouped into pools. A new pool is created for each combination of connection string and credential used to access SQL Server.

Having two logins per second could mean that connection pooling isn't working correctly or it could mean that there are multiple connections using a different combination of connection strings and credentials.


Is Database Mail worth the risk / a standard practice I enabled Database Mail XPs and was testing it on my dev machine.  I was just wondering if the DB Mail is a bad security choice, and if it more secure (or standard practice) to send this kind of message via an application server (ColdFusion or PHP)?  I would be using the Database Mail for DB backup/restore messages, errors or error checking messages (that I can think of).  I am aware that enabling a feature always increases risk.

Using:

behind firewalls, etc..
Windows 2012 r2
MS SQL Server 2012
Exchange is on separate server
Using Windows Authentication
Enabled require secure connection

Thanks in advance.

While your question would probably be borderline duplicate of this one, I'm not real fond of the answer to the other question as it does not go into to much detail of the security risk.

Database mail is configurable to include who can use the profiles/accounts created. You have the following security measures when you enable Database Mail:

(Reference this answer by Thomas Stringer)

A public profile is only available to users in msdb database
Users of msdb database must also be member of DatabaseMailUserRole in order to execute `sp_send_dbmail

The one thing to consider outside of the above is with your application and the data you are allowing to be sent via email. For example, if you are making a generic function that will allow users to send a report either via Database Mail or your application; you need to ensure there is no sensitive data being sent (especially user data). It all depends if there are any security standards for your company or the industry you are in, if any extra measures need to be put in place. I know for some if a report is being sent it simply has to have disclosure notification on the report that maybe the user acknowledges whether there is sensitive data included.

You may also add in security measures on your email system or Exchange that has policy where any email sent from the SQL Server instance or your application, can only go to internal email. It would not be allowed to send email to an outside domain name.

Is it worth the risk?

I would not consider it a risk at all based on the above information. It is a common tool used for any DBA (e.g. job failure notification, alerting, etc.) and can be a vital part of monitoring. I enable on an instance based on the need for support. Some environments it may not be preferred to setup on EVERY instance in your network (if large) and in that you may put it on an instance utilized for central monitoring.


Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" We recently started getting the following error while executing a stored procedure:

System.Data.SqlClient.SqlException (0x80131904): **Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation**.
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal

When checking online and observing stored procedure we found that in the stored procedure developer was creating #Temp tables and that the issue could be resolved by creating the #Temp table with correct database default collation.

We would like to know how this collation got changed as the stored procedure was working properly earlier and we received this sort of error for the first time. We continuously keep getting windows/security patch updates for the servers; could these updates modify the collation property of the database?

CREATE table  #temp( Code varchar(5) COLLATE DATABASE_DEFAULT null, Amount decimal(11,2) null )
INSERT into #temp select PT.Code,(PT.Percentage/100*(@PAmount+@ICharge+@FCharges+(select CC from @temp where [IN] = @MyVar))) from PTax PT inner join tax T on PT.Code=T.Code where Code = @strCode and PId=@PId AND T.Flag =@CFlag
INSERT INTO #temp SELECT T.Code,0 from Tax T where Flag =@CFlag AND T.Code not in (select Code from #temp)
drop table #tempTaxes

@Solomon Rutzky Output to 1st Query:

0   <Instance>      SQL_Latin1_General_CP1_CI_AS
1   master          SQL_Latin1_General_CP1_CI_AS
2   tempdb          SQL_Latin1_General_CP1_CI_AS
3   model           SQL_Latin1_General_CP1_CI_AS
4   msdb            SQL_Latin1_General_CP1_CI_AS
6   CustomerDB      Latin1_General_CI_AI

2nd Query output:

Latin1_General_CI_AI

3rd Query Output:

Tax 2017-09-15 01:46:39.217 2017-09-15 01:46:39.217

Any suggestions are welcome.

Let's see about the possibilities. The first one is that server's collation has changed. This is very unlikely, as it requires rebuilding system databases. Unless you have extremely dynamic environment with multiple admins doing stuff in chaotic manner, I wouldn't consider this further.

First and one half: is it possible that the database has been moved to another instance? If you are using client-side aliases, the change can be transparent.

The second one is that database's collation has changed. This is quite straight-forward, all one needs is an alter database command. So, check the database's current collation and expected collation. Do they match?

The third one is that the stored procedure itself has been modified or renamed. Maybe there used to be explicit collation specification, but that was dropped. Do you have sprocs in version control?

To see the last modify dates for stored procedures, query sys.procedures

SELECT create_date, modify_date, name
FROM sys.procedures;

As far as I know, without auditing it's hard to catch database changes after the fact. If you are using some kind of CI system, double-check if the database - or sprocs - were re-deployed recently.

It would be worth a shot to chat with other server admins and developers too.


What I can do to speed up Key Lookup in my Query I am using SQL2005 and against them I calling query simila to this

    declare @art table (id int primary key, naziv varchar(35) null, sifra varchar(15) null, jm int null);

            insert into @art
            select id, left(naziv,35), left(sifra,15), jm from art a
            where   (a.id = @art_id@ or @art_id@ = 0 )

Select
      s.art_id
     ,sum(kol) as kol
     ,sum(kol*mpc) as MPCI
     /*--and meny others --/
    from doc d
                inner join dokumenti dk on (d.tip = dk.tip)
                inner join sdo s on (d.id=s.doc_id)
        where
                        d.datum between  @do_datuma@ and @do_datuma@
                        and dk.prodaja = 1
                        and (dk.Mal_Vel in (@Mal_Vel@))
                        and (d.skl_id = @skl_id@ or (@skl_id@ = 0))
                        and ((d.skl_id in (select skl_id from skladiste_gruperj  where grp_id = @gr_rj_id@)) or ( @gr_rj_id@ = 0))
                        and (d.par_id = @par_id@ or @par_id@ = 0)
                        and s.art_id in (select id from @art)
            group by s.art_id

This is query is slow when  I take look at Execution plan I see that is Cost of 71 % is taken by
Key Lookup.

What I can do to seed up this query.

Remove it by making the index covering

That is, add an INCLUDE clause to the index so all columns needed are in the index.


Configure MariaDB client with host-specific credentials? How can I configure my MariaDB client (mysql) so that I don't have to enter a password to connect, regardless of which host I am connecting to?

I can't just put a password in the [client] section of ~/.my.cnf because that would apply all hosts.

For example, I'd like to be able to connection with mysql -h myhost mydatabase, ie. without specifying username or password. Even better would be the ability to specify a "host alias" instead of the actual (awkwardly long) hostname.

It sounds like this is possible in MySQL with the mysql-config-editor but MariaDB does not have this feature.

In ~/.my.cnf make sections like:

[clientmy_host_name_1]

[clientmy_host_name_2]

Then make an alias (assuming Linux-like OS - depending on shell, you can perhaps put the alias in ~/.bashrc_local):

alias mysql="mysql --defaults-group-suffix=\`hostname --short\`"

Now you can connect without specifying the password to any of the hosts for which you have stored the credentials in ~/.my.cnf. (These would be my_host_name_1 and my_host_name_2 in my example above.)


Migrate a schema structure from one server to another MySQL We have two Database environment server (Development and Production).
Production DB is already being used in Production(means it already has production data etc.).

My question is, is it possible to migrate databases(structure only) from Development to Production without affecting the data that is already in the Production server?

Note that, there are already changes made in the Development databases, means some of the tables/columns in a table exist in Development but doesn't exist yet in our Production database.

I'm using Workbench Community 6.2 as client.

Our Development database is using MySQL Community and our Production database is using MySQL Enterprise.

If You already use MySQL Workbench, You can use menu:
Database -> Synchronise with any Source

Before apply changes it show You script, which You can copy and if need edit
You can test it on copy of databases

Also there are many other tools:

Navicat
SQLyog
dbSchema
and etc

example of script:

ALTER TABLE `test_db`.`company1_name`
ADD FULLTEXT INDEX `Company1_name` (`Company1_name` ASC)


SQL db query using UNION and Left Join I have 6 tables (t1, t2, t3, ...,t6 in the same SQL Server 2008 R2 database. I wanted to group them into two. Group 1 (TG1) includes tables 1, 2 and 3. Group 1 (TG1) includes tables 4, 5 and six.
I have created two queries each with union

SELECT TG1.*
  FROM (SELECT t1.PID AS ID, t1.FVALUE AS FVALUE, t1.TVALUE AS TVALUE
         FROM mydb.myuser.t1
         UNION
         SELECT t2.PID AS ID, t2.FVALUE AS FVALUE, t2.TVALUE AS TVALUE
         FROM mydb.myuser.t2
         UNION
         SELECT t3.PID AS ID, t3.FVALUE AS FVALUE, t3.TVALUE AS TVALUE
         FROM mydb.myuser.t3) AS TG1
SELECT TG2.*
  FROM (SELECT t4.PID AS PID, t4.VALUE AS VALUE, t4.VALUEID AS VALUEID, t4.VALUEDESC AS VALUEDESC
        FROM mydb.myuser.T4
        SELECT t4.PID AS PID, t4.VALUE AS VALUE, t4.VALUEID AS VALUEID, t4.VALUEDESC AS VALUEDESC
        FROM mydb.myuser.T4
        SELECT t4.PID AS PID, t4.VALUE AS VALUE, t4.VALUEID AS VALUEID, t4.VALUEDESC AS VALUEDESC
        FROM mydb.myuser.T4) AS TG2

I am getting the following two tables

The final query that I want to create is as shown in the screenshot below by taking all the values of TG1 and populate additional fields from TG2 that match the values of TG1.

The values in above screenshot were populated in Excel using vlookup and I need help to do the same in SQL query. I appreciate your help.

Check:

WITH
TG1 AS ( SELECT PID AS ID, FVALUE, TVALUE
         FROM mydb.myuser.t1
         UNION
         SELECT PID, FVALUE, TVALUE
         FROM mydb.myuser.t2
         UNION
         SELECT PID, FVALUE, TVALUE
         FROM mydb.myuser.t3 ),
TG2 AS ( SELECT PID, VALUE, VALUEID, VALUEDESC
         FROM mydb.myuser.T4
         UNION
         SELECT PID, VALUE, VALUEID, VALUEDESC
         FROM mydb.myuser.T5
         UNION
         SELECT PID, VALUE, VALUEID, VALUEDESC
         FROM mydb.myuser.T6 )
SELECT TG1.*,
       TG21.PID FPID,
       TG22.PID TPID,
       TG21.VALUEID FVALUEID,
       TG22.VALUEID TVALUEID,
       TG21.VALUEDESC FVALUEDESC,
       TG22.VALUEDESC TVALUEDESC
FROM TG1
JOIN TG2 TG21 ON TG1.FVALUE = TG21.VALUE
JOIN TG2 TG22 ON TG1.TVALUE = TG22.VALUE


SQL Server 2000 - How to find unused indexes In SQL Server 2000 - how can we find unused indexes, so that we can drop them.]

I have scripts for 2008 but don't know how to do it in SQL Server 2000.

Please help.

Regards

The only thing I can recommend is to put your 2000 database on a server with SQL Server 2005 or greater (a development or test server) installed, find the unused indexes with the help of the new DMVs and once you have a list of changes apply them back to the production SQL Server 2000 instance.

Please be aware that SQL Server 2000 is no longer supported and you should move your production processes, if possible, to a later version of SQL Server.

An example query in SQL Server 2012 using the sys.dm_db_index_usage_stats DMV:

SELECT u.*
FROM [sys].[indexes] i
JOIN [sys].[objects] o
ON (i.OBJECT_ID = o.OBJECT_ID)
LEFT JOIN [sys].[dm_db_index_usage_stats] u
ON (i.OBJECT_ID = u.OBJECT_ID)
AND i.[index_id] = u.[index_id]
AND u.[database_id] = DB_ID() --returning the database ID of the current database
WHERE o.[type] <> 'S' --shouldn't be a system base table
AND i.[type_desc] <> 'HEAP'
AND i.[name] NOT LIKE 'PK_%'
AND u.[user_seeks] + u.[user_scans] + u.[user_lookups] = 0
AND u.[last_system_scan] IS NOT NULL
ORDER BY 1 ASC


SSRS report expression FormatDateTime changes Nulls to '1/1/0001' I have a report where I need to remove the time from a datetime column.
I've found this expression to work but it changes all the null values to '1/1/0001'

=FormatDateTime(Fields!Temperature_Control_Inspection_Date.Value,DateFormat.ShortDate)

I have tried the below expression to look for nulls but it doesn't appear to work.

=IIF(Fields!Temperature_Control_Inspection_Date is nothing, nothing, FormatDateTime(Fields!Temperature_Control_Inspection_Date.Value,DateFormat.ShortDate))

How can I show just the Date from a DateTime column without also having all the nulls replaced by '1/1/0001'?

Below will work :

=IIf(IsNothing(Fields!Temperature_Control_Inspection_Date.Value), "", FormatDateTime(Fields!Temperature_Control_Inspection_Date.Value, DateFormat.ShortDate))

Above will have null dates as blank. Also, it will remove the time and just get you the date portion.


SQL Server Transactional replication failed due to snapshot agent failure I'm trying to set transactional replication between 2 SQL Server 2012 named instances. I've followed a step by step manual and created a Publication.
 But once I look into Replication Monitor I Get the following error:

2013-12-05 13:08:23.29 Copyright (c) 2008 Microsoft Corporation.
2013-12-05 13:08:23.29 The timestamps prepended to the output lines are expressed in terms of UTC time.
2013-12-05 13:08:23.29 User-specified agent parameter values:
2013-12-05 13:08:23.29 --------------------------------------
2013-12-05 13:08:23.29 -Publisher SRV1\SQL2012
2013-12-05 13:08:23.29 -PublisherDB DB_PUB
2013-12-05 13:08:23.29 -Publication DB_Pub
2013-12-05 13:08:23.29 -Distributor SRV1\SQL2012
2013-12-05 13:08:23.29 -DistributorSecurityMode 1
2013-12-05 13:08:23.29 -XJOBID 0xE8D827CC232A234FAC049095C03EBA66
2013-12-05 13:08:23.29 --------------------------------------
2013-12-05 13:08:23.29 Connecting to Distributor 'SRV1\SQL2012'
2013-12-05 13:08:23.35 The replication agent had encountered an exception.
2013-12-05 13:08:23.35 Source: Replication
2013-12-05 13:08:23.35 Exception Type: Microsoft.SqlServer.Replication.ConnectionFailureException
2013-12-05 13:08:23.35 Exception Message: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)

I wonder what may cause this type of error?

It sounds like you may have set Force Encryption on the instance.  In SQL Server Configuration Manager, you can see this setting (server-side encrypting enforcement) by expanding SQL Server Network Configuration and then right-clicking on Protocols for YOURINSTANCENAME, and selecting Properties.  You will see the option there to toggle Force Encryption, as well as a tab for Certificate config.

I am by no means saying to disable forced encryption (if that is indeed your problem, I'm guessing somebody set that for a reason), but you will need to ensure that the communication between those two is properly configure.  Here is a good KB article that seems to explain what you're running into.


Store categories in database or use hard coded integers? An address can belong to either the mailing or billing categories.

When a user adds an address to a table, should the address category be an independent integer value set by a constant somewhere in the source code, or should it be a foreign key to a table that lists the categories available?

I'm concerned about performance and best practice.

The chances of adding or removing categories are slim to none.

Performance is an excellent thing to consider but I find it helpful to worry about other costs like data quality. Referential integrity ensures that no values would be assigned that don't have a match in the list of values table. Everyone knows 1 is mailing, 2 is billing, piece of pie. FNG comes on board and makes a code change and oops, we now have addresses with an address type of 12. And we've had that problem for 2 months now, customers are angry that they have not received their product and they're filling complaints with the BBB. RI would have caused the app to blow up when they tried to push those bad values into the database.

"Nobody's going to make that mistake here." Fine, the app is a success and brings in lots of money. Eventually someone's going to want to know more about what's going on. We want reports and yes, we also said we need all these new features done by last week. So now everywhere where you've embedded that knowledge that 1 is billing and 2 is mailing you need to repeat in the reporting thing (Excel, traditional reporting systems, ad-hoc queries, etc) you give to the end users to make them happy. Now you've got that logic in two places so any changes to that logic has to be propagated across two systems.

Each time you carry logic like that across systems just adds to your technical debt. Maybe your system is never going to grow like that and it's fine to embed the logic in the app code but I've found the quickest way to achieve immortality is to implement a quick fix.

Oh and did you notice I switched up the values in the report example? The FNG who you brought in could make a rookie mistake like that and who knows how long it's going to take to catch the mistake.


Problem with query in SQL Server I get an error with this query, how can "group by" column by codart and sum only the column Quantita:

Query:

SELECT
    Articolo.CodArt, Articolo.Prezzo,
    DistintaSemplice.Quantita,
    ROUND((DistintaSemplice.Quantita * Articolo.Prezzo), 2) as Totale
FROM
    [Fatture].[dbo].[SottoCapitolo]
INNER JOIN
    Fatture.dbo.Distinta ON Distinta.IdDistinta = SottoCapitolo.Codice
INNER JOIN
    Fatture.dbo.DistintaSemplice ON DistintaSemplice.IdDistinta = Distinta.IdDistinta
INNER JOIN
    Fatture.dbo.Articolo ON Articolo.IdArticolo = DistintaSemplice.IdArticolo
WHERE
    SottoCapitolo.Tipo = 'Distinta'
ORDER BY
    CodArt

You can use your actual query as a subquery and GROUP BY CodArt, Prezzo.

SELECT CodArt, Prezzo, SUM(Quantita) AS Quantita, SUM(Totale) AS Totale
FROM (
        SELECT
            Articolo.CodArt,
            Articolo.Prezzo,
            DistintaSemplice.Quantita,
            ROUND((DistintaSemplice.Quantita*Articolo.Prezzo),2) AS Totale
        FROM [Fatture].[dbo].[SottoCapitolo]
            INNER JOIN Fatture.dbo.Distinta ON Distinta.IdDistinta=SottoCapitolo.Codice
            INNER JOIN Fatture.dbo.DistintaSemplice ON DistintaSemplice.IdDistinta=Distinta.IdDistinta
            INNER JOIN Fatture.dbo.Articolo ON Articolo.IdArticolo=DistintaSemplice.IdArticolo
        WHERE SottoCapitolo.Tipo='Distinta'
    )
GROUP BY CodArt, Prezzo;

If you want to calculate Totale in function of SUM(Quantita):

;WITH Qty AS
(
    SELECT
        DistintaSemplice.IdArticolo,
        SUM(DistintaSemplice.Quantita) as Quantita,
    FROM [Fatture].[dbo].[SottoCapitolo]
        INNER JOIN Fatture.dbo.Distinta ON Distinta.IdDistinta=SottoCapitolo.Codice
        INNER JOIN Fatture.dbo.DistintaSemplice ON DistintaSemplice.IdDistinta=Distinta.IdDistinta
    WHERE SottoCapitolo.Tipo='Distinta'
)
SELECT
    Articolo.CodArt,
    Articolo.Prezzo,
    Qty.Quantita,
    ROUND((Qty.Quantita*Articolo.Prezzo),2) AS Totale
FROM Fatture.dbo.Articolo
    LEFT JOIN Qty ON Articolo.IdArticolo=Qty.IdArticolo
ORDER BY Articolo.CodArt;


Is common_schema safe? I just ran across common_schema, a utility for MySQL database administrators. It is a SQL script that creates a schema called common_schema in your database and plenty of stored procedures.

I need it for a corporate database that stores confidential information. How can I make sure it is safe? It is stored on Google Code and doesn't have a checksum or even less a signature. I'm just supposed to do some

mysql -u root -p < common_schema-2.2.sql

But somehow it doesn't feel right to dump 31K SQL lines into my DB. Am I missing something? Any ideas? Its author is Shlomi Noach, an active StackExchange user.

It's me :P

Good point on not having checksum for the sql file, I'll get some.

Your hesitation is commendable. And it's quite difficult to review the code line by line; there are stored procedures involved which are actually being executed -- I wouldn't want to review any single line of code of, say grep, or apache or... anything.

Noteworthy is that the project has moved to GitHub. The authenticy of the code in this link is as good as any SHA1 checksum I would publish on that same page.

So what next? This is known (to me) to have been downloaded tens of thousands of times; I've installed it in production on hundreds of servers; also I'm a nice guy.

It's actually a wonderful question; how can I make one trust my code? Providing a checksum is more for you to know no one else tampered with my code. I suppose you rely on the wisdom of the masses, on Google, on author's reputation (see my blog, my GitHub profile, BTW I work for GitHub). I got aware of this question having been notified by a friend in the MySQL community who is also a great stackeschange member... Thanks @RickJames

Oh: you can always import common_schema onto a replica, without endangering your master; then you are able to checksum your entire data to prove that common_schema hasn't done anything evil. The fact you can do it is likely to give you confidence. Or just do it.

I never had more fun answering a question on stackexchange.


Why use an int as a lookup table's primary key? I want to know why I should use an int as a lookup table's primary key instead of just using the lookup value as the primary key (which in most cases would be a string).

I understand that using a nvarchar(50) rather than an int would use way more space if it is linked to a table with many records.

On the other hand, using the lookup value directly would basically save us doing a join. I can imagine this would be a big saving if the join is always required (We're working on a web app so this counts quite a bit).

What are the advantages of using a int primary key (specifically for a lookup table) other than it being "the standard thing to do"?

The answer to your question is logical, not physical - the value you look up might change for business reasons. For example, if you index your customers by email address, what happens when an email address changes? Obviously this won't apply to all your lookup tables, but the benefits of doing it the same way across the entire application is that it makes your code simpler. If everything is integer → integer relations internally, you're covered.

Just read your comment to Sandy - perhaps in this case what you really want is a Check Constraint, not a foreign key/lookup table, e.g.:

create table icecream (flavour varchar(10))
go
alter table icecream add constraint ck_flavour check (flavour in ('Orange', 'Pista', 'Mango'))
go
insert into icecream (flavour) values ('Orange')
go
insert into icecream (flavour) values ('Vanilla')
go

Run this and you get:

(1 row(s) affected)
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "ck_flavour". The conflict occurred in database "GAIUSDB", table "dbo.icecream", column 'flavour'.
The statement has been terminated.

This is an efficient, high-performance method, but the disadvantage of course is that adding a new flavour means a code change. I would advise against doing it in the application - because then you need to do it in every app that connects to this DB, this is the cleanest possible design because there is only a single code path for doing validation.


NVARCHAR in SQLServer to VARCHAR in AWS Redshift I am trying to move a table from SQL server to AWS Redshift. While trying to move NVARCHAR fields, I read that Redshift converts NVARCHAR to VARCHAR internally. So, a NVARCHAR(100) in SQL Server will be equivalent to VARCHAR(100) or Varchar(200) in Redshift ? If I want to define the NVARCHAR fields from SQLServer directly as VARCHAR in Redshift, do I need to double the length of fields?

Got the answer from aws.

NCHAR and NVARCHAR types are converted to CHAR and VARCHAR types, respectively, and are stored in the specified number of bytes. A VARCHAR(120) column consists of a maximum of 120 single-byte characters, 60 two-byte characters, 40 three-byte characters, or 30 four-byte characters.
So, if I am storing double-byte characaters in SQL using NVARCHAR(x), I can define VARCHAR(2x) in Redshift to have the same storage behavior.
Following is the link for further reference:
http://docs.aws.amazon.com/redshift/latest/dg/r_Character_types.html


How exactly does trigram word-similarity work? The docs on the word_similarity function say:

  Returns a number that indicates how similar the first string to the most similar word of the second string. The function searches in the second string a most similar word not a most similar substring. The range of the result is zero (indicating that the two strings are completely dissimilar) to one (indicating that the first string is identical to one of the words of the second string).

But they are a bit vague about what the result should be if the 'first string' contains more than one word. What is the explanation for the following results:

select word_similarity('foo bar','foo bar baz');

| word_similarity |
| :-------------- |
| 1               |

select word_similarity('baz bar','foo bar baz');

| word_similarity |
| :-------------- |
| 1               |

select word_similarity('baz foo','foo bar baz');

| word_similarity |
| :-------------- |
| 0.8             |

dbfiddle here

It doesn't work like that, from the a mailing list thread in 2017-2018

  We searching for continuous extent of second string trigrams (in original orders) which has best similarity with first string trigrams.
  Possible solution could be forcing this extent boundaries to be at word
  boundaries. However, it would become less convenient to search for part
  of word. And we already have users adopt this feature.

The solution was to release pg_trgm 1.4 with new features which should drop with PostgreSQL 11. It will provide strict_word_similarity which will force extent boundaries to be at word boundaries. In addition there will be two new operators <<<-> and <<% which will do strict-word-extent operations on the index.

For more information see the docs on the development version of pg_trgm


Optimizing bulk update performance in Postgresql with dependencies Basically my question is the same as this one, but WITH dependencies, so drop/renaming the table is not a trivial option (I assume).

We are refactoring a large, poorly designed table which has many columns and references to it. It currently has a text field that should be a foreign key. The naive update looks like:

ALTER TABLE ADD COLUMN new_id int REFERENCES list(id);
UPDATE myTable SET new_id=(SELECT id FROM list WHERE name=old_text);
ALTER TABLE myTable DROP COLUMN old_text;

The above takes practically forever because the table is large, and basically gets temporarily doubled due to UPDATE being equivalent to INSERT/DELETE.

We do not need everything done in one transaction. So we are considering some sort of external script to do the updates in batches of 5000 or so, but tests indicate it will still be painful/slow.

Suggestions on how to improve performance?

Given that you can not afford to drop and recreate the table, this related answer would be a better fit:

Best way to populate a new column in a large table?

You might drop expendable indexes and recreate them when you are done (if they aren't completely expendable).

And all the general advice for performance optimization applies.

There is not much more you can do, if you have to update the table bit by bit. Faster alternatives drop and rewrite / recreate from scratch.


How to create a database or role in pgAdmin4? I want to create a database in pgAdmin4 but a corresponding item in the menu is grayed out.

I also tried to create a role, thinking that I might be lacking permissions to create database in first place, but it is also grayed out.

When pgAdmin4 loads it asks for master password, which I enter. What else am I expected to do?

This works for me if the user I am logged in as has either the superuser attribute, or the createrole and/or createdb attribute.

When you create a server (probably a poor wording, you are't initializing a server, but connecting to an existing one), pgAdmin4 definitely asks you for more information than just the master password.  Like what user you want to connect as.

If you want to connect as different users at different times, you need to "create server..." for each one.


How to delete tablespace and table after delete datafiles? I deleted datafiles with asmcmd. I need to delete tablespace and tables on this tablespace. I tried:

drop tablespace tablespace_name including contains cascade constraints

getting error:

ORA-14405: partitioned index contains partitions in a different tablespace

When I tried drop table, index or partition, getting this event and do nothing.

optimizer stats update retry.

How can I drop tablespace and all tables on this tablespace easily?

Thanks.

(Oracle 11.2.0.4.0)

According to the My Oracle Support documents, you might have hit Bug 19579109 : DROP TABLESPACE HANG ON RECURSIVE SQL.

Possible workaround would be to make sure that all paritions have statistics gathered before the drop table or set event 14534 before drop command as shown below.

alter session set events '14534 trace name context forever, level 1';
drop table <table_name>;

Refer to the MOS document for details.

Reference: Drop Tablespace Command Slow or Hanging (Doc ID 2060324.1)


SQL Server restore database with active connections I am trying to run this SQL script on a SQL Server instance to restore a test database to a newer copy of the live database.

RESTORE FILELISTONLY
   FROM disk = '\\staging_server\path\to\db_backup.bak'
-- Restore the files for
RESTORE DATABASE Test_DB
   FROM disk = '\\staging_server\path\to\db_backup.bak'
   WITH replace,
   MOVE 'Test_DB' TO 'D:\R2_BIN\Test_DB.mdf',
   MOVE 'Test_DB_log' TO 'D:\R2_BIN\Test_DB_log.ldf',
   stats = 5
GO

However, on running the script I receive the following error.

  Msg 3101, Level 16, State 1, Line 5
  Exclusive access could not be obtained because the database is in use.
  Msg 3013, Level 16, State 1, Line 5
  RESTORE DATABASE is terminating abnormally.

Is there some way I can force this operation to go through, and kill any connections it needs to to get it done? I tried to close down any applications that may connections to the DB, but some of the connections I simply cannot identify. There are several from my username and yet I only have one instance of SQL Server Management Studio open.

Anyway, is there some way to kill all connections to a specific database? There are quite a few databases running on this instance, so I don't want to just shutdown and restart the instance all together if I can help it.

You must switch to single user mode:

-- Close all connections and rollback all transaction
ALTER DATABASE Test_DB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

RESTORE FILELISTONLY
   FROM disk = '\\staging_server\path\to\db_backup.bak'
-- Restore the files for
RESTORE DATABASE Test_DB
   FROM disk = '\\staging_server\path\to\db_backup.bak'
   WITH replace,
   MOVE 'Test_DB' TO 'D:\R2_BIN\Test_DB.mdf',
   MOVE 'Test_DB_log' TO 'D:\R2_BIN\Test_DB_log.ldf',
   stats = 5
GO

ALTER DATABASE Test_DB SET MULTI_USER;
GO


Indexed Complex View The aggregate functions MIN,MAX,AVG, and STDEV are not allowed in an Indexed View in SQL Server (Create Indexed Views - MSDN Library).

Does SQL Server have a mechanism to index the results of the following view, even though INSERT, UPDATE, and DELETE operations would become much more expensive?

SELECT
    test.test_id,
    MIN(samp.result) AS tMin,
    MAX(samp.result) AS tMax,
    AVG(samp.result) AS tAvg,
    STDEV(samp.result) AS tStdev
FROM test
INNER JOIN samp ON test.test_id = samp.test_id
WHERE samp.status = 'OK'
GROUP BY test.test_id

Create a permanent table to house the computed results:

CREATE TABLE dbo.ViewResult
(
    test_id INT NOT NULL
        CONSTRAINT PK_ViewResult
        PRIMARY KEY CLUSTERED
    , tMin AS DECIMAL(18,5)
    , tMax AS DECIMAL(18,5)
    , tAvg AS DECIMAL(18,5)
    , tStDev AS DECIMAL(18,5)
);

Create a SQL Server Agent job that performs the following actions:

TRUNCATE TABLE dbo.ViewResult;

INSERT INTO dbo.ViewResult
SELECT
    test.test_id,
    MIN(samp.result) AS tMin,
    MAX(samp.result) AS tMax,
    AVG(samp.result) AS tAvg,
    STDEV(samp.result) AS tStdev
FROM dbo.test
    INNER JOIN dbo.samp ON test.test_id = samp.test_id
WHERE samp.status = 'OK'
GROUP BY test.test_id;

Schedule this Job to run as often as you need the "view" updated.

This will allow fast DML operations, while still providing reasonably fresh info about the data.


sp_startpublication_snapshot Parameter(s) I am creating a stored procedure that:

Restores a DB from a .bak giving the .mdf and .ldf a new name (so we have have several copies of the same DB up
(If specified in the SP's parameter) Creates three merge replication publications
(What I need help doing) Generating the snapshots for the three publications using sp_startpublication_snapshot

Here is my new brick wall... On this DB server, I have a 'shell' db that they will be running the SP from, that has a history table so I can keep track of who created/deleted databases using my SP's... The only parameter for sp_startpublication_snapshot is @publication... I can give it the publication name, but since I am not running it from the publishing database, how do I specify the publishing database?

i.e.: the publication shows up as:

[WC48_Database1]: upb_Inspection_PrimaryArticles

but I am running the script from the database [WC_QACatalog]

Any ideas about how to accomplish this?

Thank you,
Wes

Inside of your stored procedure use EXECUTE with a character string.  Something like this:

EXEC ('USE [WC48_Database1]; EXEC sp_startpublication_snapshot @publication = @PublicationName;');


PHPMyAdmin very slow inserts I was trying to insert around 70K records to mysql database through phpmyadmin and was shocked of how slow it is. It took around 5 hours to complete!

Please find below the details of the table/database:

I only have access to PHPMyAdmin and FTP. So I cannot use SSH to
upload the data unfortunately.
I was only uploading to one table with 8 columns. No foreign keys or computed columns of any kind. Only one auto-increment for the primary key which is not part of the query
I checked the status tab and no locks or running queries on that table to cause slowliness.
Some of the values are in Arabic (UTF8)

I tried the following methods to upload the data:

Separate Insert Queries through SQL tab: Extremely slow and takes 5
hours. It gave 500 error but it continued to upload the data and didn't stop.
Joined insert queries through SQL tab [e.g. (record1),(record2),(record3)]: takes long time and suprisingly does not add anything to the database. No errors as well.
Import Tab: Execution time errors
Through PHP: Memory was exceeded and do not have access to increase the memory as the server is not under my control

The insert query is simple

INSERT INTO mytable (`col1`,`col2`,`col3`,`col4`,`col5`,`col6`,`col7`,`col8`)values(1,100,10,'val1','val2','val3','val4','val5');

I would like to upload the data as fast as possible as I only have 5 minutes to provide that data to the customers. Any way to do a bulk insert to be as fast as possible. Alternatives to phpmyadmin that can be added through FTP are welcome as well.

The table is probably ENGINE=InnoDB.  The default is probably to commit a transaction after every INSERT.  You seem to be inserting one row at a time.  Is the input file a list of INSERTs?  Where did it come from?  If it came from mysqldump, re run the dump with the option to do multi-row inserts.  If the data is just text, arrange to use LOAD DATA.

Can you connect to mysql from another server?  That way you won't be limited to phpmyadmin.  Etc.

Check the Arabic.  Non-English characters cause trouble for a lot of people.  If they are mangled, you may need to reload after changing something.


10+ minute query time with DATEPART in WHERE clause I have a list of records with datetimes over more than 1 year. My goal is to compare the count of today's records with the count of records from last year, where the week # and day of the week from last year's search match today's week # and day of the week.

For example: if today is 07-26-2016, then the week number is 31, and the day is Tuesday (3). Then I would be searching for week number 31, Tuesday (3) of 2015. This day is 07-28-2015.

My problem is that, if I use a WHERE clause with the date in form '07-28-2015', the query completes in ~30 seconds. But if I use a WHERE clause with DATEPARTs for year, week, and day of week, my query takes > 10 minutes.

Here are the two queries to compare:

first, with the full date in the WHERE clause. This takes 30 seconds

SELECT COUNT(*)
FROM my_table
WHERE CAST(my_datetime AS DATE) = '2015-07-28'

now, with the DATEPARTs. This takes 10+ minutes

SELECT COUNT(*)
FROM my_table
WHERE DATEPART(yy,CAST(my_datetime AS DATE)) = 2015
  AND DATEPART(wk,CAST(my_datetime AS DATE)) = 31
  AND DATEPART(dw,CAST(my_datetime AS DATE)) = 3

I have also tried the second query without casting my_datetime as DATE, and it is just as slow. They DO work, and I have compared the results from the two, but it is necessary that I use the second query so that I can replace the static values (2015, 31, 3) with

DATEPART(yy,GETDATE())-1 / DATEPART(yy,CAST(GETDATE() AS DATE))-1
DATEPART(wk,GETDATE()) / DATEPART(wk,CAST(GETDATE() AS DATE))
DATEPART(dw,GETDATE()) / DATEPART(dw,CAST(GETDATE() AS DATE))

That way, the query will always give me results for  last year's corresponding date.

How do I optimize this query to be faster? Or am I approaching this incorrectly?

Why not try using a calendar table?  Using the code to create one (took about 1 second) from here.

SELECT COUNT(1)
FROM My_Table
JOIN #Dim
    ON My_Table.my_datetime = #Dim.date
WHERE DATEPART(year,CAST(getdate() AS DATE))-1 = [year]
  AND DATEPART(week,CAST(getdate() AS DATE)) = [week]
  AND DATEPART(weekday,CAST(getdate() AS DATE)) = [DayOfWeek]

In general (as I'm sure has been mentioned in the comments by now) any time you use functions on a column in the WHERE clause you are going to take a performance hit.  Frequently a very large one.  Among other things those columns are not SARGable (search argument, ie can use an index).


MongoDB Maxing CPU Usage I have a weird one here. We have set up our production MongoDB which was populated by our test one so we had something to work off of. As soon as we started the service we hit instant 100% CPU load with nothing running, so we removed the server from it's replica set and got it down to 2-3% CPU.

When we had all of our users active we saw it spike back up to 100% and we said we would leave it over night and monitor it for any signs of improvement.

The next morning we came in and the CPU was down to around 40% and it seemed very stable. Now about a week later we have come across the same issue as above except this has been happening for almost 4 days where we are maxed on CPU.

I used the db.currentOP() command and found one from "WT RecordStoreThread: local.oplog.rs" that has been running for 355254 seconds which equated to around 98 hours which matches the CPU being maxed.

The machine has 8 cores @ 2.4GHz and 16GB RAM so I wouldn't think that CPU would be this massive an issue at such an early stage.

Our config file looks like the following with the IP and Port stared out:

systemLog:
   destination: file
   path: E:\data\log\mongod.log
storage:
   dbPath: E:\data\db
net:
   port: xxxxx
   bindIp: xxx.xxx.xxx.xxx
#replication:
#   replSetName: "rs1"
security:
   authorization: enabled
#   keyFile: E:\data\mongodb.key

I have restarted the service today hoping to maybe release some resources but it instantly maxed out again.

I have looked at other issues on SO and none seem to have a real answer to resolve this problem. Thanks for taking the time to read this and if anybody has come across the same issue or might have any idea on how to solve it that would be great to get your feedback!

Strange indeed.

That local.oplog.rs is journaling what is enable (default) at 64 bit systems (32bit default value is false). So, running time for that thread should be same than your mongod uptime.

But journaling should not rise CPU usage. When you have high CPU usage, what mongotop and mongostat shows? What is happening in the system?

If you look result of top-command, "who" is eating most CPU power (one or more mongod processes?) and what is highest %-value us(er), sy(stem), ni(ce), id(le), wa(it)?


SQL Server Configuration Manager changes effected via Central Management Server When using Central Management Server (CMS), under Central Management Server Actions, SQL Server Configuration Manager (SSCM) is an option.

When launched, the GUI reflects the information of the CMS. Apart from what the GUI shows, I'm hopeful that changes made will affect all CMS managed instances.

Can anyone confirm whether changes made here will effect all the managed CMS instances? If possible, this could save much time if all the actions of SSCM can be performed against all the CMS-registered instances.

Taken from SQLServerPedia:

  Manage Your Services Remotely

  Another big advantage of CMS grouping all your servers together in a single place is that you can administrate the SQL Services on the
  target box easily. To do this right-click on the instance name. On the
  submenu you have a couple of service-related options. Under the
  Service Control option you can quickly Start, Stop, Pause, Resume or
  Restart your SQL Service. Of note, this action only applies to your
  SQL Engine service so if you have SQL Agent Service running (which
  relies on Engine) you may need to manually restart that service
  depending on how you have your services configured.

  For a little more control over the services on the target system select SQL Server Configuration Manager from the menu selection. This
  opens your typical Configuration Manager console but difference being
  that it now shows you the services on the target server you selected.
  To verify this you can see the target server's name in parenthesis
  next to SQL Server Configuration Manager in the left hand window pane.
  From here you can look at and configure your various SQL Server
  Services such as the database engine, Reporting Services, SQL Server
  Agent or SQL Server Browser. Be aware, however, this remote management
  of configuration manager only works on SQL Server 2005 editions and
  above.


Bidirectional Replication Push and Pull with SQL Server Please bear with me, I'm a developer working with a client without access to a strong DBA. I have a question about bi-directional replication with the following setup and requirements:

MSSQL Server Database A: the back-end of a web application in a remote environment
MSSQL Server Database B: a mirror of Database A that resides in a DMZ

Databases A and B can be updated independently from each other but need to stay in sync. Normally this would be a good candidate for bi-directional replication because the business rules are such that conflicts will not occur in the replicated tables.

However, the client has a security requirement that no transactions can be initiated from the remote environment into the DMZ.

Can I set up bi-directional replication so that the DMZ Database B pushes its changes to Database A and pulls Database A's changes in? Or do you suggest another strategy - replication or otherwise?

Thanks!

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

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