Trigger for removing group with no users left in I have three tables: USERS, GROUPS, and GROUP_MEMBERSHIP

CREATE TABLE USERS (
ID BIGSERIAL NOT NULL,
NICKNAME VARCHAR(20) NOT NULL constraint USER_EXISTS UNIQUE,
PRIMARY KEY (ID)
);

CREATE TABLE GROUPS (
ID SERIAL NOT NULL,
NAME VARCHAR(20) NOT NULL constraint GROUP_EXISTS UNIQUE,
PRIMARY KEY (ID)
);

CREATE TABLE GROUP_MEMBERSHIP (
ID SERIAL NOT NULL,
ID_USER BIGINT NOT NULL constraint USER_IN_GROUP UNIQUE,
ID_GROUP INT NOT NULL,
PRIMARY KEY (ID)
);

Now I am trying to create a trigger, which would fire after deleting a user from a group. If the group has no users left, then the group is deleted:

CREATE OR REPLACE FUNCTION DELETE_EMPTY_GROUPS()
RETURNS TRIGGER AS
$$
BEGIN
        IF NOT EXISTS(SELECT * FROM GROUP_MEMBERSHIP WHERE ID_GROUP = OLD.ID_GROUP)
        THEN
               SELECT DELETE_GROUP(OLD.ID_GROUP);
        END IF;
        RETURN NEW;
END
$$
LANGUAGE 'plpgsql';

DROP TRIGGER TR1 ON GROUP_MEMBERSHIP;
CREATE TRIGGER TR1
       BEFORE DELETE
       ON GROUP_MEMBERSHIP
       EXECUTE PROCEDURE DELETE_EMPTY_GROUPS();

Unfortunately, it does not work. I get the following error:

  ERROR:  record "old" is not assigned yet DETAIL:  The tuple structure
  of a not-yet-assigned record is indeterminate.

I tried BEFORE and AFTER, both resulted in the same error. What is the correct way of doing this?

If you don't specify FOR EACH ROW in your CREATE TRIGGER statement, it will default to FOR EACH STATEMENT.  In this case, the OLD and NEW records will never be assigned - in the end, which row should they refer to, if you change, for example, a hundred of them?
So, create your trigger as follows:
CREATE TRIGGER TR1
       AFTER DELETE
       ON GROUP_MEMBERSHIP
       FOR EACH ROW
       EXECUTE PROCEDURE DELETE_EMPTY_GROUPS();

Using the idea from @Darwin von Corax, you could also use a FOR EACH STATEMENT trigger to remove all groups that are now empty:
CREATE OR REPLACE FUNCTION DELETE_EMPTY_GROUPS()
RETURNS TRIGGER AS
$$
BEGIN
    PERFORM DELETE_GROUP(g.ID)
    FROM GROUPS AS g
    WHERE NOT EXISTS (
        SELECT 1
        FROM GROUP_MEMBERSHIP AS gm
        WHERE gm.ID_GROUP = g.ID
    );
    RETURN NULL;
END
$$
LANGUAGE 'plpgsql';

CREATE TRIGGER TR1
   AFTER DELETE
   ON GROUP_MEMBERSHIP
   FOR EACH STATEMENT
   EXECUTE PROCEDURE DELETE_EMPTY_GROUPS();

Notes:

I believe the trigger should be defined as AFTER DELETE in both cases.
I also believe that your function throws an error (ERROR:  query has no destination for result data) - use PERFORM instead of SELECT.
If you delete many rows (group memberships) at a time, the FOR EACH STATEMENT trigger might be more efficient, as it fires only once.


to use full text search or not? In my web app, I have a web page for admins that displays the following fields:

user id (numeric)
device id(numeric)
device description (text)
cell phone number (text)
user name (string ... but pulled in dynamically from another database)

The list of records can be quite large... into the thousands... and so the users have requested a search. It'd be nice if I could just provide a generic search box where they can specify either a user_id or a device description etc and the system will just search all fields and display matching records.

I've never built a full text search and am just researching how to do it now.
Based on my reading, I understand that a "document" / (tsvector in postgresql) can just be several different columns of data that are concatenated together.
Ignoring the fact that user name is from a different database altogether, what I'm wondering is if it makes sense for me to implement a full text search, or to just search the individual fields.
What factors should I be considering to make this decision?

Also, I don't really know how to design the system so that I could provide searching on the fields of data that are pulled in from another database, as is the case with the user names.

Any comments would be appreciated.

Thanks.

For website search you should use an actual search tool like Solr.  Full text search works in-theory, but performance slows when your data set starts to get big.  Search tools like Solr, Endeca, and Elastic Search are designed to solve problems like this, and will perform much better than full text search.

Feel free to experiment with full text search if you want, but I don't think it is a valid long-term solution for a website.


Run Query Across Oracle and Postgres We are in the process of migrating all our data from Oracle DB (12c Enterprise Edition Release 12.1.0.2.0) to Postgres (9.4.9). We are half way through the migration process and it is going to take a while to migrate all the tables. A SQL I have requires JOINs and sub-queries on tables across these two different DBs.

The obvious solution is to write a script that runs queries separately on these databases and combines the results in code.

Is there an easier alternative to this?

This is what foreign data wrappers are for.

There is one FDW for Oracle: https://laurenz.github.io/oracle_fdw/

A complete(?) list of available FDWs can be found in the Postgres Wiki: https://wiki.postgresql.org/wiki/FDW

See also CREATE FOREIGN TABLE


Storage solution to run IoT analytics I have several hundred IoT devices pushing data to my server every minute. The data is structured and it has following columns:

Site ID (String)
Event Start time (Date Time)
Event End Time (Date Time)
Data (String)

Data once created, does not change or update. The primary purpose of this database is going to run Analytics.

I started working with MongoDB, but its performance degraded after a few hundred thousand of rows. Someone suggested that as my data is structured, I do not need NoSQL. I moved to RDBMS (Postgres). It performed better than the earlier solutions but it is getting to its knees after a few million rows. Now someone else suggested that as I am not planning to use any features of a transactional database, an RDBMS is not a suitable solution. I probably need an Analytical Database.

I researched a bit for a better solution and found that Elastic Search, Vertica, HBase etc. can be a solution to my problem. There are probably a dozen of other solutions as well and I have zero experience with any of these.

My question is, how should I proceed? The learn, experiment, repeat loop seems to be too long for me.

Someone suggested that as my data is structured, I do not need NoSQL. I moved to RDBMS (Postgres). It performed better than the earlier solutions but it is getting to its knees after a few million rows.

How is getting to it's knees? You can easily index a few billion rows, with a BRIN index. Merge your start and end times into a tsrange, and cluster on the pkid and range.

CREATE TABLE foo (
  id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  startts tsrange
);
CREATE INDEX ON foo USING brin (id,startts);

I would consider frequently clustering on id until your workload won't permit that, but at a few million rows you've got a LONG way before you have a real scale problem.


Error Grouping By Multiple Columns using CASE Is it possible to group by multiple columns in a CASE statement? I get error "Incorrect syntax near ','." in below code...

SELECT
  CASE
     WHEN Type = 'Test'
       THEN Description
     ELSE MAX([Description])
  END AS 'Description'
GROUP BY
CASE
    WHEN Type = 'Test'
        THEN Description
    ELSE ''
END,
Type, Account, Structure

I guess that when Type has a certain value ('Test'), you want the Structure and Description columns to be considered for the grouping but otherwise to not use them at all. This is one way to do this:

GROUP BY
    Type, Account,
    CASE WHEN Type = 'Test' THEN Structure ELSE '' END,
    CASE WHEN Type = 'Test' THEN Description ELSE '' END

The '' should be a (any) constant expression that has the same datatype with Structure and Description respectively.

Alternatively, you could separate the 2 cases, which will give you more flexibility on adjusting the SELECT lists:

SELECT
    ---
FROM
    ---
WHERE
    Type = 'Test'
GROUP BY
    Type, Account, Structure, Description

UNION ALL

SELECT
    ---
FROM
    ---
WHERE
    (Type <> 'Test' OR Type IS NULL)
GROUP BY
    Type, Account ;


Having a unique ID for 2 different tables We would like to use two different tables, one will hold an object when it is 'Active', and the other will hold the object once it becomes'Non-Active'.
The ID is therefore unique per both tables (combined).
How can we create a constraint for the ID such that the ID appears only once for both tables?

We are using SQL Server.

This should do it I think.

CREATE TABLE Foo
(
FooId INT PRIMARY KEY,
Active BIT NOT NULL,
UNIQUE(FooId, Active)
)

CREATE TABLE FooActive
(
FooId INT PRIMARY KEY,
Active AS CAST(1 AS BIT) PERSISTED,
FOREIGN KEY (FooId, Active) REFERENCES Foo(FooId, Active)
)

CREATE TABLE FooInActive
(
FooId INT PRIMARY KEY,
Active AS CAST(0 AS BIT) PERSISTED,
FOREIGN KEY (FooId, Active) REFERENCES Foo(FooId, Active)
)


MySQL Master-Slave Replication additional columns on slave are being reset to their default value I have setup a master-slave replication in MySql. I have added additional columns to one of the tables in the slave database in order to store some additional information, which should not be an issue, since it is inline with the following rules: https://dev.mysql.com/doc/refman/5.7/en/replication-features-differing-tables.html

Now the issue that I am facing is that when I fill these additional columns, the data I fill them with is being lost once replication happens. By lost I mean that the data in these additional columns are reset to their default value.

Should this happen? I have not found anywhere where this behavior is documented and I can't understand why this is happening.

Does anybody can shed any light on this?

Thank you very much.

If the table definitions are different, you are asking for trouble and need to be careful.

In particular, if replication is using Row Based Replication, I suspect the missing columns would get defaults, but I would not trust it.

If you are writing to the Slave, you are asking for trouble.

But this may be a workaround -- instead of having extra columns, have an extra table for the extra columns.  The two table would have the the same PRIMARY KEY, except that the extra table would not say AUTO_INCREMENT.


How can I find time of an insertion? I am using:

SELECT top 10 *
FROM sys.fn_dblog(NULL,NULL)
WHERE Operation IN ('LOP_INSERT_ROWS') AND AllocUnitName = 'dbo.HeartBeat'

This return some rows, but I cannot tell which column contains a date and time.

I want to know WHEN the record was inserted. The inserted record contains a TIMESTAMP provided by the user BUT I want to see if the user is actually inserting the record at the time given by TIMESTAMP

You can find INSERT time using fn_dblog() function looking at [Begin Time] column of the corresponding begin transaction (with INSERT name) in the result set.

The tricky thing here is that the column AllocUnitName of this row (LOP_BEGIN_XACT) is NULL, while [Begin Time] is NULL for rows corrensponding to LOP_INSERT_ROWS where AllocUnitNamecan be found.

So you should use the fact that the transaction is the same, and once you individuated the rows of interest using your query that filters for 'LOP_INSERT_ROWS', you should use [Transaction ID] to find the time:

select  [Begin Time]
from sys.fn_dblog(NULL,NULL)
where [Transaction ID] = '0000:cb5f3b8a' -- put here the [Transaction ID] found using your query
and Operation = 'LOP_BEGIN_XACT';

Here is the picture illustrating what I'm talking about:


Over use of Oracle With Clause? I'm writing many reporting queries for my current employer utilizing Oracle's With clause to allow myself to create simple steps, each of which is a data oriented transformation, that build upon each other to preform a complex task.

It was brought to my attention today that overuse of the with clause could have negative side effects on the Oracle server's resources.

Can anyone explain why over use of the oracle with clause may cause a server to crash? Or point me to some articles where I can research appropriate use cases? I started using the With clause heavily to add structure to my code and make it easier to understand, I hope with some informative responses here I can continue to use it efficiently.

Thanks!

Remember that the WITH clause is used to name sub-queries, and sub-query logic can be abused somewhat. The overhead for WITH shouldn't end up being much more than using a regular sub-query, but still don't try to have a ridiculous amount of nesting going on. So long as you have a good picture of how deep you're going with things, you should be fine.


How to find what is contributing the most to "ORA-01555: snapshot too old" error A customer has reported this error more then once in the last three months. What I'm trying to do is figuring out how to identify the procedures within the application that contributed the most in changing database blocks when this error is raised (or eventually other activities on the database from tools not strictly related to the application).
For the sake of clarity, before acting on UNDO size and UNDO RETENTION parameters, I'd like to investigate the application in order to identify bad practices that can raise the probability of hitting this error.
What I know for sure is:

the statement that errored is not the root cause, it's just a victime: here a bit of SQL tuning may make it run faster and avoid the issue
frequent commits (sort of loop like approach) made by concurrent session on tha same tables used by the errored statement, may contribute to hit this problem

How can I investigate the Oracle database in order to identify the procedures at the point #2 ? What Oracle dynamic views or diagnostic views can help me in this quest?
We're facing this porblem on Oracle 12.1 with Diagnostic and Tuning Pack

What I know for sure is ... the statement that errored is not the root cause, it's just a victim

Probably, but by no means certainly.
First thing to understand is that Oracle absolutely, totally, guaranteed-edly will not allow a Dirty Read.  Ever.  Each and every query that it runs is Point-in-Time consistent with the moment (System Change Number) that the query started.
To achieve this, Oracle uses Undo to effectively "roll-back", in-memory, any changes made to the data blocks since the query started.  Even if you're table-scanning a billion row table, any records added to the end of the table while the query is running will not appear in your results.
Obviously, Oracle needs the Undo to achieve this and, if those particular Undo entries get removed from the Undo Tablespace, then Bang!  ORA-01555.
Now, as you quite rightly say, lots of stuff could be making changes and "using up" all the Undo space but, almost as likely, is that the query is taking so long to do whatever it's doing that the Undo get aged out naturally.
How long does this query take to run?

Seconds?  OK, that would be a big worry.
Minutes?  This is the most likely scenario and, in the first instance, I would suggest you look into tuning the query, if possible.
Hours?  What did you expect would happen?  :-)


How to remove all emoji from SQL Server table How can I remove all the emoji icons from a SQL Server table?

I want to remove all   icons and keep only Hebrew and English Spaces.. letters

In the end I want to keep only Basic Latin (0020—007F) and Hebrew (0590—05FF).

One fairly simple method would be to use Regular Expressions via SQLCLR. For example:

DECLARE @Test NVARCHAR(500),
        @Expression NVARCHAR(500);

SET @Test = N'this' + NCHAR(0x0010) +
            N'$is % a<>TEST,;to}⌡↕strip╞╟╚══¶out_ç_ƒ▀ special-ĳ-೫-chars-舛-დ-א---B';

SET @Expression = N'[^\p{IsHebrew}\x20-\x7F]+';

SELECT @Test AS [Original],
       DATALENGTH(@Test) AS [OriginalBytes],
       SQL#.RegEx_Replace4k(@Test, @Expression, N'', -1, 1, NULL) AS [Cleaned],
   DATALENGTH(SQL#.RegEx_Replace4k(@Test, @Expression, N'', -1, 1, NULL)) AS [CleanedBytes];

returns:

this$is % a<>TEST,;to}⌡↕strip╞╟╚══¶out_ç_ƒ▀ special-ĳ-೫-chars-舛-დ-א---B        152

this$is % a<>TEST,;to}stripout__ special---chars---א---B                            112

The pattern / expression I used — [^\p{IsHebrew}\x20-\x7F]+ — will match 1 or more characters that are "not in the Hebrew Unicode block or in the range of 0x0000 - 0x007F", and replace them with an empty string.

I did also try a pattern of:

[^\p{IsHebrew}\p{IsBasicLatin}-[\p{Cc}]]

which should have been the same since "IsBasicLatin" is the range of 0x0000 - 0x007F, and then substract the Control Characters which have a range of 0x0000 - 0x001F. But for some reason the character class subtraction was not working.

To "clean" the table, you just put it in an UPDATE statement like so:

DECLARE @Expression NVARCHAR(500);

SET @Expression = N'[^\p{IsHebrew}\x20-\x7F]+';

UPDATE tbl
SET    tbl.field = SQL#.RegEx_Replace4k(tbl.field, @Expression, N'', -1, 1, NULL)
FROM   tbl
WHERE  SQL#.RegEx_IsMatch4k(tbl.field, @Expression, 1, NULL) = 1;

One difference between this pattern and the one used in my similar answer to Replace special characters in a column with space is the + at the end of the pattern here. That is what indicates "one or more" of whatever matches the thing to the left of it. In the case here, we are removing characters rather than replacing them individually with another character (such as a space, as was used in the other answer), so we can be a little more efficient and have the Regular Expression group several characters in a row to replace with the empty string.

To be fair, this could also be accomplished in pure T-SQL. You could create a UDF that uses PATINDEX to find characters to remove, and use STUFF(@String, position_from_PatIndex, 1, '') to remove them individually. Each time you would get the position from PATINDEX and exit / break if it returns 0. I don't see that being more efficient than the regular expression, especially since PATINDEX, unlike CHARINDEX, can't start at a specified location in the string, so you can't just pick up where you left off; you need to rescan the entire string each time you remove a single character.

I just tried the following and it returns the same result string (I just haven't put it into a UDF). Just add it to the end of the test above and run all of it together:

DECLARE @Position INT,
        @Pattern NVARCHAR(100);

SET @Pattern = N'%[^' + NCHAR(0x0020) + N'-' + NCHAR(0x007F)
                + NCHAR(0x0509) + N'-' + NCHAR(0x05FF) + N']%';
SET @Position = PATINDEX(@Pattern COLLATE Latin1_General_100_BIN2, @Test);

WHILE (@Position > 0)
BEGIN
  SET @Test = STUFF(@Test, @Position, 1, N'');
  SET @Position = PATINDEX(@Pattern COLLATE Latin1_General_100_BIN2, @Test);
END;

SELECT @Test AS [CleanedViaT-SQL],
       DATALENGTH(@Test) AS [CleanedViaT-SQLBytes];

returns:

CleanedViaT-SQL                                                   CleanedViaT-SQLBytes

this$is % a<>TEST,;to}stripout__ special---chars---א---B          112


SQL Server --> Informix DB Linked Server After about 3 days of trying, I am unable to create a linked server from SQL Server 2016 to Informix 11.5. I haven't tried this with other versions (2008R2, etc) since I wanted to set up the connection in a test environment first.

What I have Done

Installed the latest Informix OLE DB Driver on the SQL Server host machine
Configured a System DSN for the IBM Informix ODBC Driver Setup
Tested the connectivity of the DSN settings (test is good) after changing the locale settings so the client and server match, and server is correct.
Attempted to create the linked server in two ways below.
Googled and read everything I could to resolve this without success.

1) Using IBM Informix instructions with OLE DB Provider in which I get the following error (regardless if I use a DNS in provider string):

  Cannot initialize the data source object of OLE DB provider "Ifxoledbc" for linked server "UCCX". OLE DB provider "Ifxoledbc"  for linked server "UCCX" returned messazge "EIX000: (-23197)" Database locale information mismatch" (Microsoft SQL Server, Error: 7303)

The DB Locale is en_US.57372

2) Using Microsoft OLE DB Provider for SQL Server as explained in this blog which returns the error:

  Named Pipes Provider: Coould not open a connection to SQL Server. OLE DB provider SQLNCLI11 for linked server UCCX returned message "Login timeout expired"
  OLE DB provider SQLNCLI11 for linked server UCCX retunred a message "A network-related or instance-specific error has occured while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. SQLNCLI11 for linked server UCCX returned message "Invalid connection string attribute"

I have very little experience with Informix which should be apparent and this isn't a local installation, it's a Cisco instance as you probably gleaned from the blogs I was using. Any advice would be appreciated.

We had a nightmare setting something similar up, specifically with the issue of connecting to multiple Informix environments. I had several Informix instances that needed to be linked to a single SQL Server instance for warehousing purposes, and was told by our resident SME that this was impossible; only a single Informix instance could be referenced per server.

I would consistently receive the same errors as you, and nothing I was able to dredge up online proved helpful.

Ultimately, the solution had several parts, of which you've done the first few:

The correct IBM Informix ODBC Driver must be installed on the database server.
Use the SetNet32 application provided with the drivers and set up the Server Information tab appropriately. Additionally, set up a corresponding System DSN entry.
The trick with the Linked Server setup is to use the following (items in codeblocks are literal):

Provider: Microsoft OLE DB Provider for ODBC Drivers
Product Name: Ifxoledbc
Data Source: $YourSystemDSNName
Provider String

Driver = {IBM INFORMIX ODBC DRIVER}
Database = $YourDatabaseName
etc...

If you're more of a manual SQL kind of person, see below. The provider string is pulled into a variable for inline commenting purposes only, while still having executable code. Modify as needed:

DECLARE @provider NVARCHAR(4000);
SET @provider = N'Driver={IBM INFORMIX ODBC DRIVER};'
  + N'DATABASE= ;'  --Informix DB name
  + N'HOST= ;'      --Informix Hostname or IP adddress
  + N'SERVICE= ;'   --Informix service name, ex.  ifx1_tcp1
  + N'PROTOCOL= ;'  --Informix protocol, ex.  onsoctcp
  + N'SERVER= ;'    --Informix server, ex. ifx1
  + N'DB_LOCALE=en_US.819; CLIENT_LOCALE=en_US.819;'; --Change to your locale, as needed

EXEC master.dbo.sp_addlinkedserver
@server = N'UCCX', --Linked Server system name
@srvproduct=N'Ifxoledbc',
@provider=N'MSDASQL',
@datasrc=N' ', --Your System DSN Name
@provstr= @provider;

Since you're dealing with a single instance, it should be enough to simply match the values used to create your System DSN. If you need to add additional linked servers for other instances, that can be done following exactly the same format described above without additional DSN entries.

I hope that helps.


using a join to find data in a second table? I'm very new to SQL and don't really understand JOINS as much as I would like, or even if this would be a good case for a join.

I have two tables:

Table1

Headings:
"folder","OwnerNumber"

Data
"folderx","1,2"
"foldery","1,3"

and

table2:

Headings:
"record","emailaddress","samId"

data
"1","user1@domainX.com","user1"
"2","Billy@domainy.com","william"
"3","Admin@domainz.com","Tom"

My application has starts off with the folder name, and the application user's SAM ID. In this case, the app user is 'Tom' and he wants to know about 'folderx'.

I need a query that I can pass in "folderx" and "Tom" into and get the email addresses that belong to the users 1, 2 and Tom.

The same query, if asked for "foldery" and user "Mike" should give me email addresses for users 1 & 3 (with no match for the SAMID of what was passed in).

Now, I know I can do this with multiple calls from my app.  One to get the numbers:

get ownernumber from table1 where folder = 'folderx'

then taking that value, and making the query in my app,

select emailsaddresses from table2 where record='1' or record='2' or samID = 'Tom'

But I figured there had to be a better way, and a way that didn't involve making two calls from my application to SQL to get the data that was all within SQL to begin with.

I suspect this is a JOIN, but what I am not clear on is:

The response from the first query gives me a single string "1,2" and am using my application to split that, and reformat it to a proper select statements for my second query.  How would I break that up within a SQL query?
if I were able to simply do some kind of @Query2 = split('result from query 1'), how would I then use that result set to make the query that goes against table 2?  Items may be listed as "1,2,3" or as "1, 2, 3" in the source, so possible spaces will need to be taken into account.

All I need when I am done is the email addresses that own a specific folder, and the email address of any matching SAMID (if one exists).

Rules:

Folder "ownernumbers" should never be blank
SAMID may or may not have a matching entry.
There may be duplicate email addresses in table2 (but not usually)
Record number is assigned by SQL so it should always be unique in Table2.

My database is the latest version of SQL Express downloaded from Microsoft.  My app is written in Powershell but for the sake of this question, I doubt that it matters.

If desired, I can add the powershell code, but this really isn't a powershell specific question.

Finally, I won't be offended if you treat me like a newbie here.  When it comes to SQL, I am self taught (bad practices as well as good) and sometimes need an answer spoon fed so I understand it.  Please do not assume I know what I am doing here.  If you make assumptions, please detail them so I can understanding them fully.

This is a very small dataset.  Maybe 1000 records in all.  Query does not have to be under efficient and a more understandable solution is preferred to a terse but more efficient one.

In SQL Server 2016 or better, you can use STRING_SPLIT. I didn't dig in to all of your rules but here is a general approach. Note that the DECLARE @Tables are just there to mock up data. You'd remove those and change @Table1 and @Table2 in the final query to test against your real tables.

DECLARE @Table1 TABLE(folder varchar(32), OwnerNumber varchar(32));
INSERT @Table1 VALUES('folderx','1,2'),('foldery','1,3');

DECLARE @Table2 TABLE(record int, emailaddress varchar(320), samId varchar(32));
INSERT @Table2 VALUES(1,'user1@domainX.com','user1'),
  (2,'Billy@domainy.com','william'),(3,'Admin@domainz.com','Tom');

-- input params
DECLARE @folder varchar(32) = 'folderx',
        @samID  varchar(32) = 'Tom';

SELECT * FROM @Table1 AS t1
OUTER APPLY STRING_SPLIT(t1.OwnerNumber, ',') AS sp
FULL OUTER JOIN @Table2 AS t2
ON sp.value = t2.record
WHERE t1.folder = @folder
   OR t2.samID = @samID;

Now, of course, with this brittle design, you'll need to protect yourself from values in OwnerNumber like 1,foo,2 but hopefully that's something the home grown application somehow already protects you from.


SELECT blocks UPDATE in Snapshot Isolation We have a database running on SQL Server 2016 SP1 Enterprise and on which Allow Snapshot Isolation is enabled. In this scenario readers do not block writers and writers do not block readers.

Today we saw that a SELECT-query was blocking an UPDATE-query.
These are the queries at issue:

First query:

IF @@TRANCOUNT = 0 SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SELECT TOP 10 Field1, Field2, Field3 FROM [dbo].[TABLE1]
WHERE ([Field1] = @SV1 AND (1=1))
ORDER BY A.[Field1] ASC,A.[Field2] ASC
OPTION (KEEP PLAN, KEEPFIXED PLAN, LOOP JOIN)

Second query:

UPDATE [dbo].[Table1] SET [Field4]= @V128, [Field5]= @V140, [Field6]= @V141 WHERE [Field3] = @ID0

If a understand versioning correctly, a SELECT-query do not use any locks because it works on a snapshot of the data (taken at the start of the transaction). How is it possible that the SELECT-query blocks the UPDATE?

if @@trancount <> 0 then that SELECT will run with the current transaction isolation level.  Which (unless it already happens to be SNAPSHOT) will require S locks for reading data.

If you want all SELECTS using the default READ COMMITTED isolation level to use row versioning instead of S locks, set READ COMMITTED SNAPSHOT on the database.


mysqldiff utility on all tables I am using the mysql diff tool at http://dev.mysql.com/doc/mysql-utilities/1.3/en/mysqldiff.html

It works great but I have to do this for each table

mysqldiff --difftype=differ --server1=PASSWORD@localhost --server2=root:PASSWORD@localhost pos.phppos_additional_item_numbers:compare.phppos_additional_item_numbers

Is there a way to have this repeat on all tables without writing a script?

If I remove the table names it just compare the name and NOT the schema of tables

I found a solution to my problem; here is what I did:

<?php
ini_set('display_errors', 1);
set_time_limit(0);
$db_host = 'localhost';
$db_user = 'root';
$db_password = 'PASSWORD';
$db_1 = $argv[1];
$db_2 = $argv[2];
$verbose = isset($argv[3])? '-'.$argv[3] : '';

$conn = mysqli_connect($db_host, $db_user, $db_password, $db_1);
$show_tables_query = mysqli_query($conn, 'SHOW tables');

$tables = array();
while ($row = mysqli_fetch_assoc($show_tables_query))
{
    $tables[] = $row['Tables_in_'.$db_1];
}

foreach($tables as $table)
{
    echo "$table\n*******************************\n\n";
    system("mysqldiff $verbose --difftype=differ --server1=$db_user:$db_password@$db_host --server2=$db_user:$db_password@$db_host $db_1.$table:$db_2.$table");
    echo "\n\n";
}

?>


CHECKPOINT prevents database drop I'm working in the following scenario: I'm creating an script that recreates an entire database.  The script involves destruction an recreation of the database and all its objects, as well as a great number of insertions from linked servers.
The idea is to be able to recreate the database at will until the development phase ends.  My problem is that the script generates high log activity, and soon a CHECKPOINT occurs.  I'm killing all connections in my script prior to drop the database, but as CHECKPOINT is a system process I can't kill it, and my script fails.
I don't need recovery for this database at this stage, my question is if I can disable the logs for this database to avoid the CHECKPOINT.

No, Logs cannot be disabled.  Even if you set the database option for SIMPLE recovery model, the log will still exist since it is necessary for transactionally consistent activities, which are a mainstay of SQL Server.

Nonetheless, running in the SIMPLE recovery model should reduce the conflicts that you are facing.  Since this is SQL Server 2008, why not try getting rid of the database prior to rebuilding with something like the following:

EDIT: Minor update to script to show how to keep control of the database.

USE master;
GO
ALTER DATABASE yourdatabase SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
GO
USE yourdatabase; -- So that you become the SINGLE_USER for the next step
GO
ALTER DATABASE yourdatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
USE master;
GO
DROP DATABASE yourdatabase;
GO

The first ALTER may not be necessary, but it helps prevent someone else getting in as the "single" user.


mysqldump select queries appearing in slow query log after upgrade We recently upgraded from MariaDB 5.5 to MariaDB 10.5
Since doing so, we have a couple of select queries appearing in the slow query log when we backup databases using mysqldump.
One of the tables is an InnoDB table with 125,000 records.  The other is a MyISAM table with 220,000 records.
I guess this might not seem like any great concern, however for many years we never had anything in the slow query log related to mysqldump.  So I am wondering if we should be worried?  Did the behaviour of mysqldump change between versions 5.5 and 10.5, or could there be some other local setting that we perhaps had optimsied previously, that now isn't?
Our slow query time is set to 5 seconds, which is what it was previously.  The offending queries are taking just under 6 seconds.
Thanks
EDIT
After further investigation, it turns out we are piping mysqldump through gzip, to compress the resulting dump files.  Removing gzip from the equation results in each dump completing in 3 seconds or less.
The fact still remains that our setup remained unchanged other than the upgrade from MariaDB 5 to 10, and this didn't seem to be an issue previously.
In any case, whilst piping to gzip results in a nice "one liner", I have changed it to dump the files as plain text, then gzip them afterwards. I feel better knowing that the dump queries complete much faster, and aren't held up by gzip.

By default, MariaDB's mysqldump will log queries (--log-queries enabled by default) to the slow log when executing the reload of a dump. The MariaDB doc says:

When restoring the dump, the server will, if logging is turned on, log the queries to the general and slow query log. Defaults to on; use --skip-log-queries to disable. Added in MariaDB 10.1.1.

This was introduced back in MariaDB 10.1.1. You could try skip-log-queries as an option for mysqldump.
This may or may not affect the dump process itself.
If you are seeing SELECTs from the mysqldump in the slow log, the table being dumped might be waiting for a lock to release (in the case of a MyISAM table) or an table with a lot of transactional activity (in the case of InnoDB) when generating the INSERT INTO for thousands of rows.
SUGGESTION
You could initialize the reload session to ignore the slow query log by hiking the long_query_time for the local session:
mysql --init-command="SET long_query_time = 10000000;" < dump-file.sql


MongoDB duplicate "_id " for an array "_id" Consider db "test" in my standalone mongodb instance.
I created a collection called testCol and inserted a document with the following syntax :

db.testCol.insert({_id:{"age":22,"empId":1},"DOJ":"22JUL"})

which results in :

WriteResult({ "nInserted" : 1 })

and upon quering

db.testCol.find()

I get

{ "_id" : { "age" : 22, "empId" : 1 }, "DOJ" : "22JUL" }

However upon inserting another document with the same _id with different positioning of the array elements with syntax :

db.testCol.insert({_id:{"empId":1,"age":22},"DOJ":"22JUL"})

which results in :

WriteResult({ "nInserted" : 1 })

and upon quering the collection using

db.testCol.find()

I get

{ "_id" : { "age" : 22, "empId" : 1 }, "DOJ" : "22JUL" }
{ "_id" : { "empId" : 1, "age" : 22 }, "DOJ" : "22JUL" }

Is this a bug because to query the document with the unique _id using the syntax :

db.testCol.find({"_id.age":22,"_id.empId":1})

will return

{ "_id" : { "age" : 22, "empId" : 1 }, "DOJ" : "22JUL" }
{ "_id" : { "empId" : 1, "age" : 22 }, "DOJ" : "22JUL" }

Why is this happening ? If mongodb follows a specific _id structure then shouldn't it return only 1 document from the final find command and if mongodb doesn't follow a specific structure then how does it let me insert multiple documents

I realised order matters to MongoDB when using an object as the value for a unique field such as the _id field.

Not sure if it can be termed a bug.

That said, with reference to your query:

db.testCol.find({"_id.age":22,"_id.empId":1})

what you are actually doing is querying based on individual components of the _id field and not the _id field as a whole.

You will be better served if your query targets the _id as a whole. This query should achieve what you intended.

db.testCol.find({"_id": {"age" : 22, "empId" : 1}})


MySQL table queries extremely slow I am using MySQL 8.0.17 and I have a table with just 7 million rows defined like
CREATE TABLE `measurement` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `field1` int(11) DEFAULT NULL,
  `field2` varchar(200) NOT NULL,
  `field3` datetime(6) NOT NULL,
  `field4` double NOT NULL,
  `field5` double NOT NULL,
  `field6` double NOT NULL,
  `field7` double NOT NULL,
  `field8` double NOT NULL,
  `field9` double NOT NULL,
  `field10` double NOT NULL,
  `field11` double NOT NULL,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index2` (`field2`),
  KEY `index3` (`field3`)
) ENGINE=InnoDB AUTO_INCREMENT=7435062 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

and queries on it are extremely slow. For example,
select count(*) from measurement;

takes around 10 minutes. I have another table in the same database with roughly the same number of rows and columns where the same query takes 35 ms. When I do an explain on the above query I get
id|select_type|table             |partitions|type |possible_keys|key       |key_len|ref|rows   |filtered|Extra      |
--|-----------|------------------|----------|-----|-------------|----------|-------|---|-------|--------|-----------|
 1|SIMPLE     |measurement       |          |index|             |index3    |8      |   |7132937|   100.0|Using index|

Before I added the other two indices this query took 17 minutes. Everything was created using Django 3.1. When I try to analyze the table the result is
Table      |Op     |Msg_type|Msg_text|
-----------|-------|--------|--------|
measurement|analyze|status  |OK      |

Where do I need to look to figure out what my issue is?

It sounds you have a slow disk, small ram and the main cache is too small.
How much RAM?  What is the value of innodb_buffer_pool_size.  SSD or HDD?
Without the secondary indexes, it had no choice but to read the entire table which was probably about 200MB?  But the buffer pool is only 16MB or 128MB (old defaults), hence "caching" was useless.
Moving to a much smaller secondary index (with 8-byte DATETIME(6) and 4-byte INT) was somewhat faster, but still too big to be cached.
I don't want to advise on the buffer_pool size without first knowing how much (or little) RAM you have and whether there are other processes running.


"Force Encryption" vs "Force Protocol Encryption" in SQL Server I'm trying to understand how the different settings affect the behaviour of my server-client communication. On the server I have installed a self-signed CA with an issued exchange cert which also has been configured to be used by my SQL service.

At first I'd like to see this connection fail when the root CA has not been installed on the client. But whatever I do it seem to work anyway.

To better understand why, I'm trying to list all options and what effect they should produce. But I'm not sure I have understood it correct ...

Can anyone please help me to correct and fill out the missing pieces here?

When enabling the "Force Encryption" setting on the SQL Server:

In practice this is the same as setting Encrypt=True;TrustServerCertificate=True; in my connection-string. The client have no say in whether encryption should be used or not and whether the server should be trusted or not.
This option can be used to encrypt individual service instances.
Supports self-signed exchange certs without CA.

When enabling the "Force Protocol Encryption" setting on the SQL Server:

All client connections to all services on the server are encrypted.
Requires an exchange cert issued by a trusted CA available on both client and server.

When using the "Force Protocol Encryption" setting on the Client:

This single client will force use of SSL and requires an exchange cert issued by a trusted CA available on this machine. Without it this connection will fail.

When enabling the "Force Protocol Encryption" setting on both Server and Client:

This is not recommended. But why? What happens and what will fail?

When enabling both "Force Encryption" and "Force Protocol Encryption" on the Server:

What will this yield? Does it matter what Force Encryption is set to when Force Protocol Encryption is enabled?

Microsoft's MSDN blog has a table describing the possible conditions and their outcomes.

See Selectively using secure connection to SQL Server to understand client side setting and connection property options impacting secure connections for just the client involved. The server and other clients are not impacted.


Postgresql - Error in function that returns table I just defined this function to return all column names from a given table:

create or replace function GET_COLUMNS(in tbl_name character varying(30))
returns table(colunas character varying) as $$
begin
    SELECT column_name
    FROM information_schema.columns
    WHERE table_schema = 'Main'
    AND table_name   = tbl_name;
end;
$$ language 'plpgsql'

But When I call it using select * from get_columns('tabfuncionarios'); I just got the following error:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function get_columns(character varying) line 3 at SQL statement

I'm using postgresql 9.4.5 version

You don't need plpgsql for this. A plain sql function will do:

create or replace function GET_COLUMNS(in tbl_name character varying(30))
   returns table(colunas character varying)
as
$$
    SELECT column_name
    FROM information_schema.columns
    WHERE table_schema = 'Main'
    AND table_name   = tbl_name;
$$
language sql;

Additionally: the language name is an identifier. Don't put it in single quotes.


How do I return two aliases from the same table query? How would I do something like this:

SELECT *
from notifications
WHERE read=false AS unread_notifications
  and WHERE read=true
  AND deleted=false AS read_notifications;

Result Example:

unread_notifications : [
  { id: ...,
    read: false,
    deleted: false,
    subscriber: ...,
    messageDate: ...,
    message: ...
  },
  ...etc
];

read_notifications : [
  { id: ...,
    read: true,
    deleted: false,
    subscriber: ...,
    messageDate: ...,
    message: ...
  },
  ...etc
];

In other words, I want to combine two queries into one:

SELECT * FROM notifications WHERE read=false LIMIT 99;
SELECT * FROM notifications WHERE read=true AND deleted=false LIMIT 99;

Example:

CREATE TABLE notifications (
  id VARCHAR(36) DEFAULT uuid_generate_v1mc(),
  read BOOLEAN DEFAULT false,
  deleted BOOLEAN DEFAULT false,
  subscriber VARCHAR NOT NULL,
  messageDate TEXT DEFAULT TO_CHAR(NOW(), 'Mon DD, YYYY HH24:MI:SS'),
  message TEXT
);

INSERT INTO notifications (subscriber, message, read)
  VALUES
  ('Sherry Waters', 'has been added to the Carlotta Corp gateway.', false),
  ('Carl Sagan', 'has cancelled his membership to the Carlotta Prime plan.', false),
  ('Parker Posey', 'is late to pay for the Carlotta Prime plan.', false),
  ('Bob Aronssen', 'has been succesfully charged for the Carlotta Prime membership!', false),
  ('Axle Root', 'has been suspended due to non-payment', true),
  ('Shaniqua Smith', 'has been succesfully charged for the Carlotta Primer membership!', true),
  ('Adam Vicks', 'has parked his membership and is now an inactive subscriber', true);

This query will return both queries as a single result. The last column added QueryIndicator is there to indicate which query the results came from. Note that UNION ALL allows duplicates, use UNION if you need to filter them out.

(SELECT
    id
    ,key
    ,READ
    ,deleted
    ,subscriber
    ,messageDate
    ,message
    ,'Unread' AS QueryIndicator
FROM notifications
WHERE READ = false LIMIT 99)

UNION ALL

SELECT
    id
    ,key
    ,READ
    ,deleted
    ,subscriber
    ,messageDate
    ,message
    ,'Read' AS QueryIndicator
FROM notifications
WHERE READ = true
    AND deleted = false LIMIT 99;

Here would be a JSON export example. Based on code found by Jack Christensen on hashrocket.

DB Fiddle Results and Example

select row_to_json(t)
from (
  select (
      select array_to_json(array_agg(row_to_json(d)))
      from (
SELECT
    id
    ,key
    ,READ
    ,deleted
    ,subscriber
    ,messageDate
    ,message
FROM notifications
WHERE READ = true
    AND deleted = false LIMIT 99
     ) d
    ) as Read_Notifications
    FROM notifications
WHERE READ = true
    AND deleted = false LIMIT 1)t
    union all
    select row_to_json(t)
from (
  select (
      select array_to_json(array_agg(row_to_json(c)))
      from (
SELECT
    id
    ,key
    ,READ
    ,deleted
    ,subscriber
    ,messageDate
    ,message
FROM notifications
WHERE READ = false   LIMIT 99
     ) c
    ) as Unread_Notifications
    FROM notifications
WHERE READ = false
 LIMIT 1)t;


How to check the memory allocator used by my mysql 5.7.20 I am suspecting memory leak in my mysql 5.7.20 server as per below filed bug
https://bugs.mysql.com/bug.php?id=83047

So i installed jemalloc in my centos 7 server and rebooted mysql(service mysqld restart).
Now i wanted to check whether mysql has picked jemalloc as the mem allocator or not

I tried

pt-mysql-summary | grep -A5 -i "memory management"
Memory management library
jemalloc is not enabled in mysql config for process with id 89539 The End

where can i check that information in running mysql instance?
Please help.. thanks in advance.

You can see which shared libaries mysqld has loaded with:

lsof -p $(pidof mysqld) | grep mem

If its using jemalloc it should be listed.


How are pages reallocated in a a data file after shrinkfile sql server I understand that shrinking the data files is a bad idea in SQL Server due to index fragmentation.
I want to ensure that I completely understand before arguing my point.
I know that when dbcc shrinkfile (unless with truncateonly) is run, the pages from the back are moved to free spaces in an earlier position.

Does this function get the very last page in the data file and place it in the first available space?

In other words, if we have this situation:

[ Page 1 ] [ Page 2 ] [ empty1 ] [ empty2 ] [ Page 3 ] [ Page 4 ] [ empty3 ] [ empty4 ] [ Page 5 ]

...after executing a dbcc shrinkfile would [ Page 5 ] be moved to [ empty1 ] because that is the first available slot? and so on...

In the end it eventually would look like this:

[ Page 1 ] [ Page 2 ] [ Page 5 ] [ Page 4 ] [ Page 3 ]

Any help would be appreciated.

The shrink algorithm works exactly as you described it. A good description can be found in Paul Randal's blog: https://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

  Why does this happen? A data file shrink operation works on a single
  file at a time, and uses the GAM bitmaps (see Inside The Storage
  Engine: GAM, SGAM, PFS and other allocation maps) to find the highest
  page allocated in the file. It then moves it as far towards the front
  of the file as it can, and so on, and so on.

In your example, Page5 ends up in Empty1.


Schema for a lease platform I have following tables:

A room I want to lease (has user.id as ForeignKey)
A users table for users who can rent
it
An availability table for time periods the room is to use with
prices for that specific period
A transaction table with check-in
and out dates (has member.id and room.id as ForeignKey
A billing table

How will I handle the billing?

Billing so far:

If I were you I would make sure to separate all of the data. Is this going to be completely controlled in the database, or is there going to be an application? If it is an application I would have a users table and permissions tables for the people using the application. I would do a separate a customers table for your customers. I agree with the availability table, but if you are using a price based system I would store that separately. For billing I would set up a few tables. I would have a payment type table and a transaction table with due dates. I like the idea of a check in and out table as well.


Log backup vs differential backup if latter is broken Under full recovery mode, will a differential backup "break" the previous log backups?

Let me give an example: Let's say we have the following backups:

FULL backup 1
Log backup 1
LogBackup 2
DIFF backup 1
LogBackup 3
LogBackup 4

Normally to restore in this situation, one would restore as follows:

FULL backup 1
DIFF Backup 1
LogBackup 3
LogBackup 4

My question is as follows: If the DIFF Backup 1-file is corrupted somehow, could I then restore the database with just the FULL and LOG backups? Like this:

FULL backup 1
LogBackup 1
LogBackup 2
LogBackup 3
LogBackup 4

Any help is appreciated.
If this has been answered in another post, please let me know (I tried searching).

Under full recovery mode, will a differential backup "break" the previous log backups?

No. A differential backup will not break the log chain. Log backups rely on LSNs (Log Sequence Numbers).

A log chain is broken when :

You take adhoc log backup without using COPY_ONLY option.
When you change the recovery mode e.g. from full to simple.
When you use TRUNCATE_ONLY or NO_LOG (Both deprecated in SQL 2008) or backup to NUL (its like taking a backup and deleting it !)
When you revert the database from a database snapshot. For this scenario, you have to take a full backup - even differential wont work !

  If the DIFF Backup 1-file is corrupted somehow, could I then restore the database with just the FULL and LOG backups?

Yes, provided the log backups are not corrupted as well. Since differential got corrupted, there are chances that your subsequent backups might be corrupted.

You can even re-initialize a secondary log shipping database from a differential backup and then start applying log backups if your log backup chain is broken & provided a full backup has not ran (or you ran a full backup with COPY_ONLY option).

You have to be careful, that running a FULL backup without COPY_ONLY will reset the differential base as each differential backup will contain changes since the LAST full backup.

Read these answers as they are relevant :

Restricting users to COPY ONLY backups
Finding gaps in log backups


sp_send_dbmail usage best practice I would like to get some clarity on the usage best practices of sp_send_dbmail and I cannot find any answers online.

Our developers love to use sp_send_dbmail to send out application related emails from within the application. Say for instance a user forgets their password, they will send the password reset email that a user requests by executing sp_send_dbmail. This is just one of the many types of emails they send using this stored procedure. Is this a good practice? To me it feels like a dirty solution as there are many API's out there that can handle email sending in your application and as a DBA, I don't like enabling these options on the production servers. I just don't have a choice. Maybe with enough motivation and proof that it's bad practice and/or potentially dangerous for various reasons I can sway them to change it.

SQL Server core licenses are expensive: using a SQL Server machine's CPU to send thousands of emails has no justification, especially when there are countless better options in terms of features, manageability, setup, troubleshooting.

Another point is that tight coupling between the email and database features makes the application difficult to scale, isolate and manage. One server - one service.

Database mail, in my opinion, should be used for administrative purposes only (alerting) and not for sendind business email messages.


Is this really in 3nf? I  cannot comment on https://stackoverflow.com/questions/8437957/difference-between-3nf-and-bcnf-in-simple-terms-must-be-able-to-explain-to-an-8, so I have to post my question here. It is about the relations on this table cited as the most favored answer on that thread:

 Pizza    Topping     Topping Type
 -------- ----------  -------------
 1        mozzarella  cheese
 1        pepperoni   meat
 1        olives      vegetable
 2        mozzarella  cheese
 2        sausage     meat
 2        peppers     vegetable

According to the source of the answer, it is in 3nf. However, I thought there is only one candidate key which is (Pizza,Topping). (Pizza,Topping Type) cannot be a candidate key because a Pizza can have multiple Topping Type values.

A candidate key must be able to identify a distinct tuple, and (Pizza, Topping Type) does not meet this condition. So it seems to me that the above relation is not in 2nf, and therefore not in 3nf. Topping Type only tells something about Topping, but not Pizza. Am I missing something?

The example is provided in the answer in the linked question. The author of that answer - and example - states that (Pizza, Topping Type) is a candidate key of the relation - ther's also discussion in the comments below the answer.

So the example has the added restriction that every pizza has not more than one toppings of any type.

This may not be your usual definition of a pizza but with this assumption - even if we assume the common sense dependency that Topping -> Topping Type - the relation is indeed in (2NF and) 3NF.


Mysql Memory table getting many locks On my site I log every pageview (date, ip, referrer, page, etc) in a simple mysql table.

This table has the following activity:

1 SELECT per minute
1 DELETE per minute
no UPDATE queries
Lots of INSERTS (about 300 per second).

This table is never bigger than 200MB.

Today, I changed this table from an InnoDB table to a MEMORY table, this made sense to me to prevent unnecessary hard disk IO. I also prune this table once per minute, to make sure it never get's too big. And this information is not very important, so on restart of MySQL it does not matter that it is removed.

--

Performance wise, things are running fine. But I noticed that while running tuning-primer, that my Current Lock Wait ratio is quite high using MEMORY table.

Current Lock Wait ratio = 1 : 561

If I change this table to InnoDB the ratio is:

Current Lock Wait ratio = 0 : 78600946

My question: Should I worry about this Lock Wait Ratio? And is there something I can change in my configuration to improve things so that the lock wait ratio isn't so high using MEMORY tables?

You have to keep in mind two major aspects about using the MEMORY Storage Engine

ASPECT #1

The MEMORY table behaves like a MyISAM table in that it performs a full table lock when doing INSERTs, UPDATEs, and DELETEs (MySQL Documentation says Locking granularity : Table). I would be very concerned with a high lock ratio if it bottlenecks your I/O performance. If 300 INSERTs per second is coming at this table, you can be fully confident that there will be 300 full table locks per second.

ASPECT #2

No matter how much data you load into a MEMORY table, mysqld will always contact the .frm file of the MEMORY table to verify the table's existence before query parsing. Although the .frm is not a large file, it will incur a very tiny amount of disk I/O. Thousands, or even just hundreds, of queries against a MEMORY table can reveal a noticeable, even a significant, amount of disk I/O.

Since this is the case with the MEMORY storage engine, you must also avoid MyISAM because it has the same locking granularity (Full Table Locking).

Here are my recommedations

Recommedation #1

Cleanup InnoDB so that it has innodb_file_per_table enabled.

Recommedation #2

Use a large InnoDB Buffer Pool

Since the table is 200MB, you can make InnoDB Buffer Pool 5 times as large (restart required)

[mysqld]
innodb_buffer_pool_size=1G

Recommedation #3

Upgrade to MySQL 5.5 in order to have multithreaded reads and write that engages multiple CPUs for InnoDB. If you have InnoDB 5.1.38+ and are using the InnoDB Plugin, the same options that engages multiple CPUs for InnoDB already exists.

Recommedation #4

Common sense would tell me to recommend turning the table back to InnoDB


Why do two identical strings have a different length but the same binary value? I am trying to return a distinct set of department names from a table - nothing special. However, when using the following query duplicates are displayed:

  select distinct department_name
    from dbo.departments;

I also tried:

  select distinct department_name
    from dbo.departments
group by department_name;

So this led me to believe that I might have hidden characters in the values and sure enough, when I checked the length of the strings they returned different values. So, I decided to locate the hidden characters using the function from this question on stack overflow. Strangely enough this only return SPACE. I then tried the following query and it made no difference at all:

select distinct ltrim(rtrim(department_name)) as department_name
  from dbo.departments;

Intrigued, I cast the values to VARBINARY and noticed that they have the exact same binary value, and performing a DISTINCT on the binary value does indeed produce a unique result set.

I have tried casting between VARCHAR and NVARCHAR and to a different collation also (the values are in the same column, within the same database using Latin1_General_CI_AI). I really need to be able to get a distinct set from this table. Does anybody know what might be causing this issue?

UPDATE

After some further investigation, this issue seems to only happen for strings that end with the hex value 0xA000. Any value in the column not ending with this character are fine.

UPDATE 2

If I remove the 0xA000 character from the string I can then apply DISTINCT as normal like so:

DECLARE @binary VARBINARY(8) = 0xA000;
DECLARE @string VARCHAR(8) = CONVERT(VARCHAR(MAX), @binary);
 UPDATE dbo.departments
    SET department_name = REPLACE(department_name, @string, '');

But this won't work long term as users can update this table and I would need to adjust every query to do a replacement in the WHERE clause. I am using a workaround right now which is no more than using MIN to return the entry with the shortest length. This is less than ideal as the problem with distinct is also affecting most of the other language elements as well, for example GROUP BY, ORDER BY, any window functions and COUNT.

Good job investigating so far. Some initial notes:

I wouldn't worry about that function from the S.O. answer.
RTRIM and LTRIM only trim spaces, not white-space in general:

SELECT RTRIM('A    ') + 'a';
-- Aa

SELECT RTRIM('A    ' + CHAR(9)) + 'a'; -- CHAR(9) = tab
-- A        a

Adding GROUP BY (2nd query) doesn't change that query since it was implied in the first query ;-).
Whether 0xA000 is 2 VARCHAR characters or 1 NVARCHAR character, there does not seem to be any special behavior with this sequence of bytes for either datatype, using either Latin1_General_CI_AI or Latin1_General_100_CI_AI.

Still, something is amiss. You simply cannot have different length (or different anything) with the same binary value. How was length determined: LEN or DATALENGTH? Is it possible that the value got truncated somewhere in the testing so that it then appeared to be the same?

In order to help further, we need to know two things (please update the question with the results):

The exact datatype of the department_name. Please find via:

SELECT typ.[name], col.*
FROM   sys.columns col
INNER JOIN sys.types typ
        ON typ.[user_type_id] = col.[user_type_id]
WHERE  col.[object_id] = OBJECT_ID(N'dbo.departments')
AND    col.[name] = N'department_name';

The output of the following query:

SELECT dept.department_name,
       LEN(dept.department_name) AS [name_chars],
       DATALENGTH(dept.department_name) AS [name_bytes],
       CONVERT(VARBINARY(MAX), dept.department_name) AS [name_hex]
FROM   dbo.departments dept
ORDER BY dept.department_name

Just find a set of rows for a name that looks the same but is showing duplicates via DISTINCT.

For the moment, I can say that if you have character 0 (ASCII value 0, Code Point U+0000, CHAR(0), NCHAR(0)) in your values then appearances can be deceiving since char(0) is the "null terminator" for strings. So neither it nor anything after it will be displayed, but it and everything after are still part of the string:

DECLARE @Test TABLE
(
  [Something] VARCHAR(50) COLLATE Latin1_General_CI_AI
);

INSERT INTO @Test ([Something])
VALUES ('Sometimes you' + CHAR(0) + 'feel like a nut');

INSERT INTO @Test ([Something])
VALUES ('Sometimes you' + CHAR(0) + 'feel like a nut');

INSERT INTO @Test ([Something])
VALUES ('Sometimes you' + CHAR(0) + 'don''t');

SELECT DISTINCT [Something],
       LEN([Something]) AS [Something_chars],
       DATALENGTH([Something]) AS [Something_bytes]
FROM   @Test;
/*
Something        Something_chars    Something_bytes
Sometimes you    19                 19
Sometimes you    29                 29
*/

Enjoy: Peter Paul Almond Joy & Mounds - "Feel Like A Nut" (1980)

TEMPORARY UPDATE (WAITING FOR QUESTION TO BE UPDATED WITH QUERY OUTPUT):

Based on this info from a comment:

  I used both LEN and DATALENGTH to perform the check. For two exact same strings I got (20,40) and (21,42) respectively.

it is clear that:

these are definitely different values, and
the datatype of the column is NVARCHAR (since DATALENGTH is twice LEN)

That means that the 0xA000 value is a single, UTF16LE character. Due to being Little Endian (bytes in reverse order), the actual Code Point is U+00A0. That character is:

No-Break Space

As in our favorite HTML character: &nbsp;

All you need to do is remove those characters on the way into the DB, using:

REPLACE(@InputParam, NCHAR(0x00A0) COLLATE Latin1_General_100_BIN2, N'')

For example:

SELECT CONVERT(VARBINARY(MAX),
      REPLACE(N'test' + NCHAR(0x00A0), NCHAR(0x00A0) COLLATE Latin1_General_100_BIN2, N'')
    );

To be clear, all of that work messing with VARBINARY and VARCHAR, etc is unnecessary.

  But this won't work long term as users can update this table and I would need to adjust every query to do a replacement in the WHERE clause.

True, updating every WHERE clause is not a workable solution. This is why you need to sanitize the input on the way in. There are a limited number of entry points for the data (INSERT / UPDATE procs for the UI, possibly some ETL processes), so it shouldn't be that bad. You can request that the developers strip out "bad" characters before they call the stored procedure(s), but there is no guarantee that they will, or that new code will, or that things won't change later, or that they will be able to fix ETL processes, etc.


Cannot fix ERROR:ORA-01031: insufficient privileges I have tried almost every solution on the internet however I still cannot fix this problem. I cannot login as sysdba every time I try SQL>connect /as sysdba I get the error ERROR:ORA-01031: insufficient privileges.

My account is in ora_dba group. I'm using oracle 10g. I'm new to database so I really don't know what to do.

I assume that you run this on the server that hosts the database and that you have set the ORACLE_SID to the one of the database that you want to access. You must run the orapwd application. This generates a file that holds the password. Check the documentation for the exact syntax. It depends a bit on the OS on which you host the database.


Multiple Availability Groups on one instance I want to add second availability groups to Always on group and I need to change the port to 5023 but it is read-only as shown below:

The first availability group has an endpoint which is listening on port 5022. how can I change the port for second and third availability group?

The first availability group has an endpoint which is listening on port 5022. how can I change the port for second and third availability group?

All availability group traffic flows over a single endpoint and there is only one endpoint allowed per instance. So, in this case, since you already have an endpoint setup you only need create the new availability group.


How to check if the SYSTEM_VERSIONING for a table is ON? I know that SQL Server 2016 let us to use the SYSTEM_VERSIONING like :

CREATE TABLE EmpSalary
(
     EmpID int NOT NULL PRIMARY KEY CLUSTERED
   , SalaryAmt decimal (10,2) NULL
   , SalaryBeginDT datetime2 GENERATED ALWAYS AS ROW START NOT NULL
   , SalaryEndDT datetime2 GENERATED ALWAYS AS ROW END NOT NULL
   , PERIOD FOR SYSTEM_TIME (SalaryBeginDT, SalaryEndDT)
)
WITH (SYSTEM_VERSIONING = ON);

Also to deactivate this, it is only necessary to ALTER the table:

ALTER TABLE EmpSalary SET (SYSTEM_VERSIONING = OFF );

My question is how to check if the SYSTEM_VERSIONING for a table is ON and then ALTER the table?

You could query sys.tables

ALTER TABLE EmpSalary SET (SYSTEM_VERSIONING = On );
select name, temporal_type, temporal_type_desc from sys.tables where name = 'EmpSalary'

| name      | temporal_type | temporal_type_desc              |
|-----------|---------------|---------------------------------|
| EmpSalary | 2             | SYSTEM_VERSIONED_TEMPORAL_TABLE |

ALTER TABLE EmpSalary SET (SYSTEM_VERSIONING = OFF );
select name, temporal_type, temporal_type_desc from sys.tables where name = 'EmpSalary'

| name      | temporal_type | temporal_type_desc |
|-----------|---------------|--------------------|
| EmpSalary | 0             | NON_TEMPORAL_TABLE |


Defining a primary key: Customer numbers are unique only within a country but may be duplicated across different countries I am facing the following problem:

I have a table with customer information like the customer number, i.e. customerID set up as the primary key, and, besides a number of other data, also a country value in the form of a country code, countryID.

However, customer data from other countries should also be imported into this table. Although the customer numbers are unique within a country, they are not cross-country. Customer numbers should not be changed for organizational reasons.

How can a key still be ensured for each customer in this table?

I would use countryID, CustomerID as your unique key as you have stated that you will have a unique customer id for each country. I would start with countryID as that will be unique in the system and you can add in customerID in order to get a unique key for each customer.


User privileges error on server maria db Create trigger Drop trigger User privileges error on server maria db

User : user1
DB : db1

Error

  15:00:46 DROP TRIGGER IF EXISTS audit_trigger Error Code:
  1419. You do not have the SUPER privilege and binary logging is enabled (you might want to use the less safe
  log_bin_trust_function_creators variable)
  0.266 sec

Script

DELIMITER $$
DROP TRIGGER IF EXISTS audit_trigger$$
CREATE TRIGGER `audit_trigger` BEFORE UPDATE ON `table` FOR EACH ROW
BEGIN
    IF OLD.dateModify <> NEW.dateModify THEN
        INSERT INTO  table SELECT * FROM thistable where id = NEW.id ;
    END IF ;
END $$
DELIMITER ;

Is it possible to give super privileges for user1 to only db1
i dont want this user to have access to other tables on the server

Believe it or not, I wrote about this back on Jun 20, 2014. If you look in my post MySQL user without SUPER privilege, I clearly explain how running command like

GRANT ALL PRIVILEGES ON db1.table TO 'user1'@'%';
GRANT ALL PRIVILEGES ON db1.* TO 'user1'@'%';

never gives away SUPER privilege

If you run this query

SELECT table_name FROM information_schema.columns
WHERE table_schema='mysql' AND column_name='super_priv';

it only comes back with user.

+------------+
| table_name |
+------------+
| user       |
+------------+

Here is something interesting

In MySQL 5.6, running DROP TRIGGER requires the TRIGGER privilege. Same is true for MySQL 5.7, MySQL 5.5, and MySQL 5.1
In MySQL 5.0, running DROP TRIGGER requires the SUPER privilege

Running this query

SELECT table_name FROM information_schema.columns
WHERE table_schema='mysql' AND column_name='trigger_priv';

comes back with

+------------+
| table_name |
+------------+
| db         |
| user       |
+------------+

This shows you that globally and at the database level you can grant the TRIGGER privilege, provided the user can access the specified schema. You cannot give away the TRIGGER privilege to a user just for a single table.

Thus, if a user has full access to database db1 and you want that user to drop triggers in another database (such as db2), then simply run

GRANT TRIGGER ON db2.* TO user1@'%';

Be very careful how you assign individual grants like this

In your particular case, DROP TRIGGER requires TRIGGER privilege in MariaDB


How to do a bulk load of a database when application are relying on it? In a daily routine, I need to drop and do a bulk load of a database.

The problem is that I have a WebApp relying on that database. So I can't drop the database just like this.

What would be a good way to tackle this problem ?

Do you need to drop and reload the database?  In a case like yours, I would look at a way to synchronize the database with the reference set (the data you are loading).

An alternative approach would be to have two databases.  Load a different one each day.  Configure the webapp so that you can swap databases on the fly.


Error while executing SSIS Package When I tried executing a SSIS Package I came across following error.

  [OLE DB Destination [148]] Error: SSIS Error Code DTS_E_OLEDBERROR.
  An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record
  is available.  Source: "Microsoft SQL Server Native Client 10.0"
  Hresult: 0x80004005  Description: "The statement has been
  terminated.". An OLE DB record is available.  Source: "Microsoft SQL
  Server Native Client 10.0"  Hresult: 0x80004005  Description:
  "Violation of PRIMARY KEY constraint 'PK_user'. Cannot insert
  duplicate key in object 'dbo.users'. The duplicate key value is (-3)."

I was trying to pull data from one table to another table across two DB servers.
How can I proceed further?

Looks like you are trying to insert duplicate PK_user IDs into your destination table, that is set to be unique as well.

You'll need to join the 2 sets and only write the unique PK_user results or remove the unique constraint on your destination table.


Login failed for user Error: 18456, Severity: 14, State: 11 I have an AD group XYZ that I have added to SQL Server security with data_reader permissions.

The XYZ group has around 10 users in there who are successfully able to access the SQL Server database. I recently added a new user to this group (at AD level), but this person is not able to access SQL Server (through Mgmt Studio) and he's getting the error below

  Login failed for user. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.

  Error: 18456, Severity: 14, State: 11.

I have already verified AD permissions are setup properly, user has restarted his machine, he is not part of any group that has DENY access and  the SQL Server XYZ group has been removed and readded to the SQL Server instance in Mgmt Studio and server has been restarted.

Any ideas on how to proceed further?

Thanks!

States 11 & 12 mean that SQL Server was able to authenticate you, but weren't able to validate with the underlying Windows permissions. It could be that the Windows login has no profile or that permissions could not be checked due to UAC. Try running SSMS as administrator and/or disabling UAC. Another reason could be that the domain controller could not be reached.

Refer to to the below link for more information on Error states.

https://sqlblog.org/2011/01/14/troubleshooting-error-18456


Overlapping Transactions Apolgies in advance if this has been discussed before, or is even fundamental enough for it to be outlined on, say, Wikipedia. I just felt that asking would be more efficient!

Anyway, I want to ask about transactions, from a theoretical point of view. In particular, say we have two (or more) users looking at a dataset and the following occur at the times indicated (tX):

t0   User1 starts a transaction
t1   User2 starts a transaction
t2   User1 runs a DML operation on the dataset
t3   User2 runs a different DML on the same dataset
t4   User1's operation finishes and is committed
t5   User2 commits

My question is: how do the DML operations run between User1 and User2 interact with each other? Does the operation started at t2 by User1 operate on the data as it was defined at the start of their transaction (i.e., at t0), and likewise for User2's operation, so that there is no interaction? Or, if say User1 committed their operation after User2's transaction had started but before they had started their operation, would the data User2 was looking at be updated respectively? Moreover, how is consistency maintained in the case of any conflicts?

For all general cases, the second transaction will be blocked until the first one commits or rolls back. As Martin notes, your isolation level may affect this, but the entire point of ACID compliance is to keep them separate.


Strange Garbage Character in SQL server database like ÂÃÂÃÂÃÂÃÂÃÂÃÂÃ I am using HTMLBOX editor for getting Notes/RichText Data input from User. I found there are lot of garbage characters in my database and they keep on doubling on each time user save/update notes.

Below is code sample for my PHP to SQL server connection.

<?php
class Model {
    public static $db;

    public static function initialize() {
        self::$db = new PDO('odbc:Driver={SQL Server};Server='.DBCONNECTION_SERVER.';Database='.DBCONNECTION_DATABASE.'; Uid='.DBCONNECTION_USER.';Pwd='.DBCONNECTION_PASS.';', null, null,array(PDO::ATTR_PERSISTENT => true));
        self::$db->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);
        self::$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        self::$db->setAttribute(PDO::ATTR_EMULATE_PREPARES , false);
    }
}
?>

I search for same problem and most of solution suggest to use UTF8 encoding. I set below attribute but no success. :(

self::$db->setAttribute(PDO::SQLSRV_ATTR_ENCODING , PDO::SQLSRV_ENCODING_UTF8);

Please see below screenshot.

Update:

Column Type : NVARCHAR(MAX)

User Input : User can enter or Copy/paste anything. Those garbage character appears when user copy paste from Outlook or other Rich Text Source. The single quote(') from outlook get converted to (Ã¢ÂÂ) garbage character.

According to the MSDN page for PDO::setAttribute, PDO::SQLSRV_ENCODING_UTF8 is the default value, so setting it explicitly probably wouldn't change the behavior.

Try the other option, PDO::SQLSRV_ENCODING_SYSTEM, which seems to have worked in this case from the MSDN forums: Some characters improperly retrieved through PDO driver.


Difference between request and session in sp_blitzwho? I'm starting to use sp_blitzwho, and I was wondering about a few of the columns.

There are a set of columns that start request_ such as request_writes, and then a set of columns that start session_, such as session_writes. Could someone explain the difference between these?
There is a column called wait_info. This holds a wait time and type, that I assume is what the proc call is waiting on? Is this correct?

If so what is the top_session_waits column showing? This column often has a few Waits in there?

One of the cool things about open source stored procedures like sp_BlitzWho is that you can actually see the source code. It's really useful when you have questions about where data is coming from and what it means.

If I open sp_BlitzWho and do a control-F for request_writes, for example, I see that it's:

COALESCE(r.writes, s.writes) AS request_writes,

And a little further down, I can see which tables the r and s aliases point to:

FROM sys.dm_exec_sessions AS s
             LEFT JOIN sys.dm_exec_requests AS r

From there, you can hit the documentation for sys.dm_exec_sessions and sys.dm_exec_requests, which explain that a request is a currently executing task (like a query), and a session is someone's connection that has been open for a while, which may have performed several requests over time.

As you suspected, wait_info is indeed what the query is waiting on - but rather than spoon-feeding you that one, I'll let you start your journey of reading the source code there. Hope that helps!


how to verify DB hive already exists by expect script we create the database hive and some other commands

in case we run the expect script again on the machine that hive already created

then we get - ERROR hive already exists

so how to verify database hive already created ?

And by this verification we can escape the expect script

# su - postgres
 Last login: Sun Aug 13 11:12:03 UTC 2017 on pts/0
 -bash-4.2$ psql
 psql (9.2.13)
 Type "help" for help.

postgres=# CREATE DATABASE hive;
ERROR:  database "hive" already exists

my expect script: ( from my bash script )

set timeout -1

#exec the scripts

spawn timeout 60 ssh root@IP
expect "#"
spawn su - postgres
expect "$"
send "psql\n"
expect "=#"
send "CREATE DATABASE hive;\n"
.
.
.
.

you can use IF EXISTS clause:

create database if exists hive;


Sort name by Thai character is not working I am trying to sort a table by name. The name are in Thai character;

>Select name from product_categ
                    name
--------------------------------------------
 ระงับกลิ่นกาย
 สกินแคร์ (ดูแลผิวหน้า)
 รองเท้า
 ผู้หญิง
 เมคอัพ
 น้ำหอม
 วิตามิน อาหารเสริม
 เส้นบุก, บุกก้อน

When I try to sort the table by name, it does not work.(it does sort but is sorting in some different way / probably using the equivalent of Thai Character ?)

>Select name from product_categ order by name;
                    name
--------------------------------------------
 ชา กาแฟ เครื่องดื่มชงสำเร็จ
 ร่ม กันฝน พวงกุญแจ
 หมวก
 หมวก
 เล็บ
 แป้ง

So i tried to google and find a solution where i came across using collation directly in the query.However the query is not working for me.

select name from product_categ order by name COLLATE 'th_TH';

I receive and error mentioning error at 'th_th';

Any suggestion to fix this will be a much appreciated.

You need to specify that it's a UTF-8 collation:

SELECT name
FROM product_categ
ORDER BY name COLLATE "th_TH.UTF-8";


On-the-fly query/command modification I'm responsible for administrating an instance of SQL Server 200x which has the misfortune of serving as a backend to a turnkey application written with a disgusting performance problem. It repeatedly uses the pattern of preparing a static cursor for which it will only use the first few entries of, and often that takes tens of milliseconds. There are thousands of these queries in a continuous stream.

Truth of the matter is that due to the curious design of the application data integrity isn't an issue at all with any of these queries, so the fact that they're static is miserable since performance would be about thirty times faster if they were dynamic.

After dealing with the vendor for weeks I've been getting nowhere. Truth is all I really need to do is change one parameter in the way the application uses sp_cursorprepexec. I could even do it in a global way across the entire server if necessary.

I'm looking at any and all solutions, whether it be using a feature in SQL Server 200x that I haven't been able to find, writing a small app that sits in between SQL Server and the client and modifies the data (although I wouldn't be looking forward to figuring out how to deal with the TDS protocol in such a way), or somehow renaming sp_cursorprepexec and replacing it with a wrapper.

Sky's the limit.

(FYI, we're running SQL Server 2005 right now, but if there's a convincing reason to shell out the extra money to upgrade the license it could happen.)

This is not a technical problem, it's a contract management problem.  Don't frig with the turnkey application.  It gives the vendor a get-out-of-jail-free card that allows them to ignore their service level agreement.

Either,

Throw hardware at the problem (SSDs, bigger server, more RAM etc.), or
Talk to whomever manages the relationship with your vendor.  Push the responsibility for system performance back on to the vendor, and/or
Investigate the business case for moving to a competitor's product.

Messing with the application is the last thing you should do, particularly if relations with the vendor are getting tense.

You need to make sure your management knows that the performance issue is down to the architecture of the system.  Get that in writing somehow; write up a report describing the issue and the reasons you can't fix it unilaterally.  Make sure it's emailed to all of your internal stakeholders and you have a record of it being sent.  Arrange a meeting to follow that up and explain it.  That covers your arse - this is quite important if the vendor (or the party that authorised the purchase) tries to get political.

Now, escalate the issue with your I.T. management function, making sure it's well documented.  As you understand the root cause of the problem, this shouldn't be an issue.  The vendor has sold you a system that is not fit for purpose.  It's now a management issue.  Light the blue touch paper and stand back.

If you've already done this, then you've done everything that you can do without interfering with the application itself.  Again, I can't say this strongly enough: do not do anything to the application that could void the SLA.


How to make postgresql SUM to be more accurate on large amount of floating point data? I'm trying to SUM 8 million floating point (REAL) values with simple query like this:

SELECT SUM(metric) FROM metrics;

However, it returns very inaccurate result.
It should return 137,586.77, but it returns 137,303 (283.77 difference)

Is there a way to force a query to be more precise?

According to the documentation of the REAL datatype http://www.postgresql.org/docs/9.2/static/datatype-numeric.html#DATATYPE-FLOAT it has a precision of 6 decimal digits. That's what you are seeing. If you need more precision you need to use double or a number with fixed precision.

Maybe casting the reals to something more precise and summing the result will give you what you need, eg:

SELECT SUM(metric::double) FROM metrics;


mysql join by 2 ways - how it works? - which gives performance difference I had 2 different ways to query the content which showed performance difference when executed. The 1st way is

EXPLAIN select SOME_COLUMNS
from
( select *
  from A
  where CONDITION
) p
inner join
( select *
  from B
)st
on p.id = st.id;

and the output of this query returned:

"id"   "select_type"   "table"    "type"   "possible_keys"   "key"    "key_len"   "ref"   "rows"   "Extra"

 1       PRIMARY     derived3  ALL       NULL                 NULL    NULL    NULL      25607     "   "
 1        PRIMARY    derived2  ALL        NULL                NULL     NULL    NULL      21037   Using where; Using join buffer
 3        DERIVED             A    ALL          NULL               NULL      NULL    NULL      23202    "   "
 2        DERIVED             B    ref        IDX_A_TYPE_ID   IDX_A_ID  98      "   "        12411  Using where

The other way is

EXPALIN SELECT SOME_COLUMNS
FROM A p, B s
WHERE p.id = s.id
AND p.CONDITION;

The output of this looks like this:

id       select_type       table       type       possible_keys       key     key_len    ref     rows   Extra
 1       SIMPLE           p            ref       PRIMARY,IDX_A_TYPE_ID      IDX_A_TYPE_ID    98    const    12411    Using where
 1      SIMPLE            s           ref             PRIMARY       PRIMARY     4   local_db.p.entity_id    1

Why is there such a difference in the query execution plan between the 2 and number of rows being fetched in each step of query execution is more in the 1st approach? Please explain it.

There is a differences because:

In first query doing the following:

create temp table with data from A filtering out by CONDITION
create temp table with data from B
for each row in temp_A we traverse temp_B. In this case temp_A using FULL SCAN, temp_B access by index

Second query

For each row from A which fall under CONDITION we join with table B.

As you can see in second query there are "preloading" data into tables and scan works in different way.


How to Load XML child nodes in to multiple tables I would like to load the below XML into 2 tables

Table 1: Acct Table - it should have the ACCT_NUM and a Key to tie to the Table 2 below

Table 2:  Should have all the Contacts (1 record per Contact_Type) and should be able to link back to Table 1

Here is my XML:

<?xml version="1.0" encoding="UTF-8"?>
<Extract>
<Request>
<ACCT_NUM>111111</ACCT_NUM>
<Data>
<Contacts>
<Contact CONTACT_TYPE="CRM">
<CONTACT_TOLL_FREE>800.123.4444</CONTACT_TOLL_FREE>
<CONTACT_ADDRESS_LINE_1>PO Box 11111</CONTACT_ADDRESS_LINE_1>
</Contact>
<Contact CONTACT_TYPE="Collections">
<CONTACT_TOLL_FREE>800.123.5555</CONTACT_TOLL_FREE>
<CONTACT_ADDRESS_LINE_1>PO Box 22222</CONTACT_ADDRESS_LINE_1>
</Contact>
</Contacts>
</Request>
<Request>
<ACCT_NUM>222222</ACCT_NUM>
<Data>
<Contacts>
<Contact CONTACT_TYPE="CRM">
<CONTACT_TOLL_FREE>888.123.6666</CONTACT_TOLL_FREE>
<CONTACT_ADDRESS_LINE_1>PO Box 33333</CONTACT_ADDRESS_LINE_1>
</Contact>
<Contact CONTACT_TYPE="Collections">
<CONTACT_TOLL_FREE>888.123.7777</CONTACT_TOLL_FREE>
<CONTACT_ADDRESS_LINE_1>PO Box 44444</CONTACT_ADDRESS_LINE_1>
</Contact>
</Contacts>
</Request>
</Extract>

Any help is greatly appreciated.

As I pointed out in my comment there is a missing </Data> tag in your example. But if the file has it, you can use the next command to extract XML values:

SELECT
    t2.lin.value('(./ACCT_NUM)[1]', 'int') as ACCT_NUM
    ,t3.lin.value('(./@CONTACT_TYPE)[1]', 'varchar(100)') as CONTACT_TYPE
    ,t3.lin.value('(./CONTACT_TOLL_FREE)[1]', 'varchar(100)') as CONTACT_TOLL_FREE
    ,t3.lin.value('(./CONTACT_ADDRESS_LINE_1)[1]', 'varchar(100)') as CONTACT_ADDRESS_LINE_1
    INTO #tmp
FROM
    txml
CROSS APPLY
    data.nodes('Extract/Request') as t2(lin)
CROSS APPLY
    t2.lin.nodes('Data/Contacts/Contact') as t3(lin);

NOTE: I've used a temporary table #tmp to store the returned values.

Then you can use this values to build/insert that values into the two tables:

SELECT
    ACCT_NUM
INTO
    #table1
FROM #tmp;

SELECT
    ACCT_NUM,
    CONTACT_TYPE,
    CONTACT_TOLL_FREE,
    CONTACT_ADDRESS_LINE_1
INTO
    #table2
FROM #tmp;

And this is the final result:

SELECT * FROM #table1;

SELECT * FROM #table2;

| ACCT_NUM |
| -------: |
|   111111 |
|   111111 |
|   222222 |
|   222222 |

ACCT_NUM | CONTACT_TYPE | CONTACT_TOLL_FREE | CONTACT_ADDRESS_LINE_1
-------: | :----------- | :---------------- | :---------------------
  111111 | CRM          | 800.123.4444      | PO Box 11111
  111111 | Collections  | 800.123.5555      | PO Box 22222
  222222 | CRM          | 888.123.6666      | PO Box 33333
  222222 | Collections  | 888.123.7777      | PO Box 44444

db<>fiddle here


Why does Casting this string as a decimal fail? Why does casting this result of REGEXP_SUBSTR() to a DECIMAL fail?
SELECT
    REGEXP_SUBSTR('Cost (-$14.18)', '(?<=Cost [(]-[$])[0-9.]+') AS _extracted,
    CAST(REGEXP_SUBSTR('Cost (-$14.18)', '(?<=Cost [(]-[$])[0-9.]+') AS DECIMAL(8,2)) AS cost_1,
    CAST((SELECT _extracted) AS DECIMAL(8,2)) AS cost_2,
    CAST((SELECT _extracted) * 1 AS DECIMAL(8,2)) AS cost_3,
    CAST('14.18' AS DECIMAL(8,2)) AS cost_4;

+------------+--------+--------+--------+--------+
| _extracted | cost_1 | cost_2 | cost_3 | cost_4 |
+------------+--------+--------+--------+--------+
| 14.18      |  14.00 |  14.00 |  14.18 |  14.18 |
+------------+--------+--------+--------+--------+

Casting a plain string as in cost_4 seems to work. Multiplying the REGEXP_SUBSTR() result by 1 also appears to work.  But simply casting the result as I've done with cost_1 and cost_2 fails to produce the correct fixed point version of _extracted.
Oddly, in my application using the backreference as I've done for cost_2 actually produces the correct result.  Was unable to reproduce elsewhere but thought it worth mentioning.

This has been a long-standing issue with MySQL with people reporting this very issue as a bug since 2011. I have found that the problem is almost completely dependent on the collation being used within the REGEXP_SUBSTR() function.
For instance, if you cast the result of REGEXP_SUBSTR() as a CHAR(100), your decimals remain intact:
mysql> SELECT CAST(CAST(REGEXP_SUBSTR('Cost (-$14.18)', '[0-9.]+') AS CHAR(100)) AS DECIMAL(8,2)) AS result;

result
-----
14.18

The result returned by REGEXP_SUBSTR() used a UTF-16 character set before MySQL 8.0.17. Versions after this supposedly use the same character set as configured by the client (See bug #94203 reported by Rick James), but this does not appear accurate. My SQL client is configured to use UTF-8 everywhere. Running your initial query in my client produces the exact same results as you shared in the question.
However, if I CONVERT( ... USING 'UTF8'):
SELECT CAST(CONVERT(REGEXP_SUBSTR('Cost (-$14.18)', '[0-9.]+') USING 'UTF8') AS DECIMAL(8,2)) AS result;

result
-----
14.18

Surprise, surprise. A correct number.
Generally in this situation I do the very same thing that you did for cost_3; I multiply the returned value by 1, then cast it to the desired type. You can save a step by casting as FLOAT, but this sometimes has precision implications.
It is not a great answer, but it is one that can be used across multiple versions of MySQL.


How to manage stored procedure dependencies in SSDT? I have multiple stored procedures, some that depend upon others. When publishing my database, I receive an error when creating some stored procedures due to dependency issues. SSDT seems to be creating the stored procedures in alphabetical order, not dependency order like I need.

For example, suppose sp_ProcA depends upon sp_ProcB. SSDT is trying to create sp_ProcA first and fails because sp_ProcB hasn't been created yet.

I've looked through project and script settings. I've found nothing related to dependency management.

The order in which Stored Procedures are created in SQL Server should not, in most cases**, cause any errors. If dependent objects do not exist at the time of CREATE PROCEDURE or ALTER PROCEDURE, you should merely get a warning about SQL Server not being able to register the dependency in the meta-data (i.e. sys.sql_expression_dependencies).

If you are getting an actual error, then most likely you have a certain build option set to do this. Go to Project Properties and then to the Build tab. There is a check-box for "Treat Transact-SQL warnings as errors". You probably have that checked. You can either un-check that option OR if you like having it to catch other warnings, then trying specifying the warning in the "Suppress Transact-SQL warnings:" text field.

** Linked Servers / OPENQUERY / OPENROWSET, by default, attempt to validate the remote schema unless you alter the Linked Server definition to specify LazySchemaValidation.


SSIS deployment failed - working with files I've developed a simple SSIS package that will open a csv file, select a number of column, add two derived columns and then copy the results to a file.

This works within visual studio (even if it takes ages) where I constructed it. The problem is when I attempt to deploy it to our sql server it fails.

It keeps say that:

"The specified parameter value string is too long. The string must be no more than 8000 characters."

But I have no idea what it is talking about as none of my columns is over 1000 characters.

There isn't much info on this error but is it possible that the number of rows (400,000+) is the source of the problem?

This isn't a problem with your data - it is a problem with your package. Usually, this is a problem with the size of your query.

It appears that internally, SSIS uses a varchar(8000) value somewhere that could actually use a varchar(max) value. When you run up against this limit, you get this ambiguous error.

There are several ways you can approach this. You can shorten your query (by removing excessive whitespace, replacing spaces with tabs, compacting indents, etc).

You can also split your query out into multiple variables or parameters, then concatenate the pieces back together with an expression task before executing the query.

Depending on the query, you could also convert it into one or more stored procedures or views, and call those within your package.

But if you keep the query inside the package, you need to reduce its length (or its piece's lengths) to less than 8000 characters.  Have fun.

Have fun - nothing is more fun than refactoring a query.


Force drop db while others may be connected I need to remove a database from a PostgreSQL DB cluster. How can I do it even if there are active connections? I need sort of a -force flag, that will drop all connections and then the DB.

How can I implement it?

I'm using dropdb currently, but other tools are possible.

PostgreSQL 13 added:
DROP DATABASE mydb WITH (FORCE);

The manual:

FORCE
Attempt to terminate all existing connections to the target database.
It doesn't terminate if prepared transactions, active logical
replication slots or subscriptions are present in the target database.
This will fail if the current user has no permissions to terminate
other connections. Required permissions are the same as with
pg_terminate_backend, described in Section 9.27.2. This will
also fail if we are not able to terminate connections.

In PostgreSQL 12 and earlier, you cannot drop a database while clients are connected to it.
At least, not with the dropdb utility - which is only a simple wrapper around DROP DATABASE server query.
Quite robust workaround follows:
Connect to your server as superuser, using psql or other client. Do not use the database you want to drop.
psql -h localhost postgres postgres

Now using plain database client you can force drop database using three simple steps:

Make sure no one can connect to this database. You can use one of following methods (the second seems safer, but does not prevent connections from superusers).
/* Method 1: update system catalog */
UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'mydb';

/* Method 2: use ALTER DATABASE. Superusers still can connect!
ALTER DATABASE mydb CONNECTION LIMIT 0; */

Force disconnection of all clients connected to this database, using pg_terminate_backend.
 SELECT pg_terminate_backend(pid)
 FROM pg_stat_activity
 WHERE datname = 'mydb';

 /* For old versions of PostgreSQL (up to 9.1), change pid to procpid:

 SELECT pg_terminate_backend(procpid)
 FROM pg_stat_activity
 WHERE datname = 'mydb'; */

Drop the database.
 DROP DATABASE mydb;

Step 1 requires superuser privileges for the 1st method, and database owner privileges for the 2nd one. Step 2 requires superuser privileges. Step 3 requires database owner privilege.


Latency appear to be an issue with linked server A client have a data warehouse server with a linked server set-up. The data warehouse server is SQL Server 2014, while the linked server is a SQL Server, but version unknown.

Those two servers was previously physically at the same location, but a while ago the linked server was moved across the Atlantic. The performance was drastically hurt.

While I do understand that the latency is affected, I did not expect the throughput to be hurt as much. I'm talking about 20 minutes before and 15 hours after the relocation.

Monitoring wait stats shows pretty much async_network_io only.

I've tried to research how a query is processed across linked servers, but have not been able to find something useful. It appears to me that it's not as simple as the data warehouse sending a query to the linked server and the linked server starts to feed data back to the data warehouse. I do get the impression that it is continuous messages sent back and forth and therefor the latency plays a huge part.

The queries are simple selects from single tables.

Setting this up in SSIS improves performance to an acceptable level, but will take a lot of time to set up.

Anyone knows what's going on here?

UPDATE - Further details requested from David S

A typical table that is selected from the linked server and inserted to the local server is ~5 000 000 rows @ 4 GB

The query runs on a view:

CREATE VIEW [dbo].[Order_line]
AS
SELECT [ordno]
  ,[divcode]
  ,dbo.UTCOFFSET([regdate]) as regdate
  ...
  ,[o08rqtyb]
  ,[marowver]
FROM [LinkedServer].[Db1].[dbo].[O08T1]

The query to insert and update rows looks like this (Contained in a SP)

UPDATE
    T1
SET
    T1.[ordno] = T2.[ordno],
    T1.[divcode] = T2.[divcode],
    ...
    T1.[marowver] = T2.[marowver],
    T1.[updateTime] = GETDATE()
FROM
    [t_Order_line] T1
JOIN [Order_line] T2 ON
    T1.shorto08 = T2.shorto08 and
    T1.marowver < T2.marowver

INSERT INTO [ASTRO_Auburn].[dbo].[t_Order_line]
       ([ordno]
       ,[divcode]
       ...
       ,[marowver]
       ,[insertTime])
SELECT T1.[ordno]
    ,T1.[divcode]
    ...
    ,T1.[marowver]
    ,GETDATE()
FROM [Order_line] T1 WITH (NOLOCK)
LEFT JOIN [t_Order_line] T2 ON
    T1.shorto08 = t2.shorto08
WHERE t2.[ordno] IS NULL

I know that there are questionable decisions taken when this was set-up, but it is the legacy from another time.

Fetching the query plan that is executed on the remote server only shows a Table scan. Possibly that scan is executed twice? But that's not the major issue here.

The queries are simple selects from single tables.

There are a number of details that you need to include that may have an effect here. Are they many small resultsets (a few rows or small rows) or small numbers of large results (many rows and/or large columns like BLOBs)? If it is a mix can you separate them out and test the performance separately?

How are you making SELECTS to the remote end? Direct inserts into a local table with something like INSERT localtable SELECT stuff FROM server.db.schema.table or are you reading the results into your own code for further processing before INSERT? If the latter than you might need to look into which cursor options you (or the framework you are calling through) are using and see if there are less latency sensitive options that are applicable (assuming the framework you are using allows you this level of control). If the options used mean that each row is request separately then latency will be far more of an issue then if they allow the rows to be read over the network in bulk and navigated locally.

  Setting this up in SSIS improves performance to an acceptable level

If you are using exactly the same statements to retrieve the data in SSIS as your previous method then that could imply one of three things, or a mix of them:

The cursor options as mentioned above (SSIS is more likely to be favour latency friendly options which may in other circumstances be sub-optimal, because it is more often used to work with more widely distributed servers)
Connection pooling which your current method might not be making as much advantage of (again, here we need more details to do more than guess). SSIS will use the same connection rather than renegotiating for each query, your other process may not be doing this.
Possibly concurrency: is your workflow in SSIS allowing it to submit several queries at the same time, so it can submit new queries to the remote end without first having to wait for the previous ones to complete, where the workflow in your current process is entirely sequential?