Cannot remove char 0x0000 from NVARCHAR column So, I know all about the Replace function and a bug with char(0).

I have a column (NVARCHAR(128)) that has some NCHAR(0x0000) characters from a bad import.

I'm using SQL Server 2008 R2.

The collation for the column is: SQL_Latin1_General_CP1_CI_AS.

I have tried all the stuff online that I can possibly find, and NOTHING will get the stinking char(0) characters out of the column.

Here's my latest attempt, with BAFFLING (bug in sql server?) results.

I have a function that loops through each char and replaces 0x0000 with a specific char.

ALTER FUNCTION dbo.ReplaceCharZero
(
  @testString NVARCHAR(MAX),
  @charToReplaceWith NCHAR(1) = ' '
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE
        @i INT = 1 ,
        @fixedString NVARCHAR(MAX) = ''
    WHILE @i <= LEN(@testString)
        BEGIN
            IF SUBSTRING(@testString, @i, 1) = CHAR(0x00)
                BEGIN
                    --PRINT 'Found' + CAST(@i AS VARCHAR)
                    SET @fixedString = @fixedString + @charToReplaceWith
                END
            ELSE
                BEGIN
                    --PRINT 'NOT Found' + CAST(@i AS VARCHAR)
                    SET @fixedString = @fixedString
                        + SUBSTRING(@testString, @i, 1)
                END
            SET @i = @i + 1
        END
    RETURN @fixedString
END

And here's what I do to test:

BEGIN TRAN

    DECLARE @ShortDescription NVARCHAR(128), @SupplierId INT, @Language CHAR(2)

    SELECT TOP 1 @ShortDescription = ShortDescription,
                 @SupplierId = SupplierID,
                 @Language = Language
    FROM   Supplier_Multilingual
    WHERE  ShortDescription LIKE '%' +  CHAR(0x00) + '%'

    SET @ShortDescription =  REPLACE(dbo.ReplaceCharZero(@ShortDescription, ' '), '-', ' ')

    UPDATE dbo.Supplier_MultiLingual
    SET    ShortDescription = NULL
    WHERE  SupplierID = @SupplierId
    AND    Language = @Language

    UPDATE dbo.Supplier_MultiLingual
    SET    ShortDescription = dbo.ReplaceCharZero(@ShortDescription, '')
    WHERE  SupplierID = @SupplierId
    AND    Language = @Language

    SELECT *
    FROM   Supplier_Multilingual
    WHERE  SupplierId = @SupplierId
    AND    Language = @Language
    AND    ShortDescription LIKE '%' +  CHAR(0x00) + '%'

ROLLBACK TRAN

In my test, I grab the column as a variable, I run my function on it to strip out the 0x0000, then I update the original column with a NULL, then I update it to my fixed variable, and then I run a query to see if 0x0000 chars still exist, which they do.

A bug in the REPLACE function? Or in SQL Server in general? I'm not so sure about that. The only "issue" here is merely not fully understanding how string comparisons are handled.

Collations define how certain characters will compare against other characters. Sometimes there are rules about certain combinations of characters equating to one or more other characters. And there are rules about characters such as 0x00 (null) and 0x20 (space) equating to each other or other characters. And, to make life more interesting, there are a few nuances specific to VARCHAR data using a SQL Server Collation (i.e. one starting with SQL_), as the following example shows:

SELECT REPLACE('VARCHAR with SQL_Latin1_General_CP1_CI_AS'+CHAR(0)+'Matches', CHAR(0),
               ': ' COLLATE SQL_Latin1_General_CP1_CI_AS);
SELECT REPLACE(N'NVARCHAR with SQL_Latin1_General_CP1_CI_AS'+NCHAR(0)+N'Matches', NCHAR(0),
               N': ' COLLATE SQL_Latin1_General_CP1_CI_AS);
SELECT REPLACE('VARCHAR with Latin1_General_100_CI_AS'+CHAR(0)+'Matches', CHAR(0),
               ': ' COLLATE Latin1_General_100_CI_AS);
SELECT REPLACE(N'NVARCHAR with Latin1_General_100_CI_AS'+NCHAR(0)+N'Matches', NCHAR(0),
               N': ' COLLATE Latin1_General_100_CI_AS);

Returns:

VARCHAR with SQL_Latin1_General_CP1_CI_AS: Matches

NVARCHAR with SQL_Latin1_General_CP1_CI_AS

VARCHAR with Latin1_General_100_CI_AS

NVARCHAR with Latin1_General_100_CI_AS

So let's take a look at this behavior using queries based on the ones found in @Max's answer. I added the N prefix to the string literals and to the CHAR(0), and I also added an extra NCHAR(0) just to make the next part easier to see. And I added queries to show the actual Code Points being used (to prove that the 0x0000 values are really in there, and a call to REPLACE() to see if that really has a bug in it).

DECLARE @Data NVARCHAR(255);
SELECT @Data = N'this is' + NCHAR(0) + N'a test' + NCHAR(0) + N'of null';

SELECT @Data;
SELECT CONVERT(VARBINARY(50), @Data);
SELECT REPLACE(@Data, NCHAR(0), N'~');

  this is

  0x7400680069007300200069007300000061002000740065007300740000006F00660020006E0075006C006C00

  this is

The first result shows that the string ends just after the "is" due to (null) termination. The second result shows the underlying codes, and I emphasized the two instances of the 0x0000 character. The third result shows that the REPLACE function doesn't seem to match the 0x0000 character to the NCHAR(0) that is passed in.

But should we be expecting that NCHAR(0) would be matching here? We can effectively disable all of the equivalence rules that are usually applied to string comparisons by forcing a binary collation. We will use a _BIN2 collation since the _BIN collations are deprecated and shouldn't be used unless you have a specific need for them.

Add the following query to the set above and re-run the batch.

SELECT REPLACE(@Data, NCHAR(0) COLLATE Latin1_General_100_BIN2, N'~');

You should get the following additional result:

  this is~a test~of null

So the REPLACE function does in fact work, and this was tested on both SQL Server 2008 R2, SP3 as well as SQL Server 2012 SP2.

Ok, so that only addressed the issue of REPLACE not working with NCHAR(0), but did not address NCHAR(0) equating to a space (i.e. NCHAR(32) or NCHAR(0x20)).

Now we will use an adaptation of the main query from @Max's answer. I again added an extra NCHAR(0) to the test string (really just replaced the space at position 8 with it), and I added the Code Point of the matching character to the RAISERROR message.

SET NOCOUNT ON;
GO
DECLARE @Data NVARCHAR(255);
SELECT @Data = N'this is' + NCHAR(0) + N'a test' + NCHAR(0) + N'of null';

DECLARE @i INT,
        @CodePoint INT;
SET @i = 1;

WHILE @i < LEN(@Data)
BEGIN
    IF SUBSTRING(@Data, @i, 1) = NCHAR(0) --COLLATE Latin1_General_100_BIN2
    BEGIN
        SET @CodePoint = UNICODE(SUBSTRING(@Data, @i, 1));
        RAISERROR (N'Found a NULL char (Code Point = %d) at position: %d',
                   10, 1, @CodePoint, @i) WITH NOWAIT;
    END;
    SET @i = @i + 1;
END;

This query (with the COLLATE clause still commented out) will return:

Found a NULL char (Code Point = 32) at position: 5
Found a NULL char (Code Point = 0) at position: 8
Found a NULL char (Code Point = 32) at position: 10
Found a NULL char (Code Point = 0) at position: 15
Found a NULL char (Code Point = 32) at position: 18

These are the same positions reported in @Max's test, but now it shows what Code Point it is matching in each case. And yes, it is equating to both 32 and 0.

Now, uncomment the COLLATE clause and re-run it. It will return:

Found a NULL char (Code Point = 0) at position: 8
Found a NULL char (Code Point = 0) at position: 15

Another way to accomplish this, and without using the COLLATE clause, is to change the IF statement to be:

IF ( UNICODE(SUBSTRING(@Data, @i, 1)) = 0 )

Of course, neither of these two fixes -- the WHILE loop using either the COLLATE clause or the UNICODE() function -- are needed to address the original problem of removing the 0x0000 characters from the input data since the simple REPLACE (using the COLLATE clause) handles that.

Summary:

If you are wanting to replace / remove characters from a string, there is no need to use a loop. The REPLACE function works just fine as long as you specify a _BIN2 collation for at least one of the 3 input parameters via the COLLATE keyword (and technically it doesn't matter which binary collation since binary collations only compare the numeric Code Point values).
If needing to test for a particular Code Point (such as in a loop as shown above), then it is probably fastest to use the UNICODE() function since that just reports what values are actually there. This should be faster than using the COLLATE keyword since that has to do more work.
If needing to test for a series of Code Points / characters, then use the COLLATE keyword, specifying a _BIN2 binary collation.


Number of rows in SQL dump Is there any way to check how many rows are in an SQL dump file? I have a large 32GB dump, and wanted to check how many rows are in the file.

I am using a windows machine with a MySQL server,

Thanks!

You should be safely calculate using wc -l filename.sql
On windows you may use following in command prompt: findstr /R /N "^" filename.sql | find /C ":"

As such not a part of question but out of intereset, you can also list content of the 32G dump using mysqldumpsplitter:
mysqldumpsplitter.sh --source filename --desc [More how-tos]


Oracle 10g express database link to Oracle 8i I upgraded a 9i install to 11g with only one problem.  Database links between 11g and our legacy 8i database are no longer supported.  I have to transfer data from 11g to 8i two or three times a day so I thought it might be possible to use 10g express to link the databases.
The data would be created in 11g, written to a table in 10g and then written to 8i.  (I agree this is not the most elegant solution and I can improve it using advanced queue tables).

Does 10g express support database links to Oracle 8i?

Edit: thanks for the link to the Oracle documentation.  Has anyone actually done this with 10g express?

Edit:  The 8i database is Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
The intermediate would be the 10g express for windows
The origin database is Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

Metalink 207303.1 is the Client/ Server Interoperability Matrix which will be the definitive documentation.  Since the express edition of 10g is based on Oracle 10.2, a database link to an 8.1.7 database should be supported.  Database links to earlier versions of 8i (8.1.5 or 8.1.6) are not supported from a 10.2 database.  For general sanity, I'd strongly suggest that the 8i side be running at least the 8.1.7.4 patchset but that isn't strictly required.


Counting entries of a unique entry of another field and concatenate the result with the unique field in a new column I want to create a query that will search the table and find the following:

If a POL_N entry has ENDO_N entries then the COUNT ENDO_N field will start counting the ENDO_N entries and will get the assigned name as shown below.

For example POL002 has two ENDO_N entries, hence the COUNT ENDO_N will be POL002-1 and POL002-2.

Do you have any suggestions?

I recreated the table specified with the next T-SQL code:

create table StackOverflowTest(POL_N nvarchar(100),ENDO_N nvarchar(100));
insert into StackOverflowTest(POL_N,ENDO_N)
values('POL001','0'),
      ('POL002','0'),
      ('POL002','ENDO001'),
      ('POL002','ENDO002'),
      ('POL003','ENDO003'),
      ('POL003','ENDO004');

T-SQL for getting the column specified. I would be wary of doing this on large datasets though, this is the unoptimized first draft:

  select POL_N,
         ENDO_N,
         case when ENDO_N = cast(0 as nvarchar(2))
         then cast(0 as nvarchar(2))
         else  POL_N +'-'+ cast(ROW_NUMBER() OVER (PARTITION BY POL_N ORDER BY ENDO_N desc) as nvarchar(100))
         end as COUNT_ENDO_N
    from StackOverflowTest
    order by POL_N, COUNT_ENDO_N;


SSIS flat file manager - skips the last record I've a a flat file which is pipe delimited as shown below:

header|header2
abc|abcdef
abc|abcdef

When I create a package on a 64 bit machine it always drops the last record. But on preview it shows both records.

Update:

If I do this it works:

header|header2
    abc|abcdef
    abc|abcdef
       |

Why is this and how do I fix it?

I had the same problem on Windows Server 2008 R2 x64.  A flat file source would not load the last record... So this answer helped me, and now it's working fine.

  Go to advanced column view, select all the columns and set the text qualification drop down to false for all the columns.


mysqldbcompare SELECT command denied to user I am trying to run mysqldbcompare to see the difference between two tables:

mysqldbcompare --server1=server1:2016@localhost --server2=server2:2016@localhost user:user --difftype=sql --run-all-tests

I get this error. I have already granted all privileges to users.

  ERROR: Query failed. 1142 (42000): SELECT command denied to user 'server1'@'localhost' for table 'proc'

You should enable the dbuser to have READ permissions to the proc table.

  The process attempts to perform a SELECT statement on the 'proc'
  table.  The 'proc' table is a system table which retains the
  procedures of all users on that mysql instance.  On shared hosting,
  allowing userA to see the procedures of userB, which would include
  information about table structure, etc., would be considered breach of
  security.All info found here

Or you can use the root user.


Why cannot I call a table function in iSeries DB2 that I just created? Using iSeries Navigator 'Run an sql script' window I created a new table function.
When I go to the Functions branch under the schema where it was created, I see the function among the other few. But when I call the function in the same Run an sql script window, I get an error:

SELECT * FROM TABLE(TESTDAT.FNREPORT(DATE('10/23/2013'), DATE('10/23/2013'), 'ANY')) AS T

SQL State: 42704
Vendor Code: -204
Message: [SQL0204] FNREPORT in TESTDAT type *N not found.

Using SquirrelSql client I can see the function in the schema as well, but cannot call it due to the same error.
I can call other functions in the schema, but not the one I just created. What can be the reason?

I can call Generate SQL, copy/paste the schema and function name into query window, and then it will throw the above error.

Update:

Full source code of the function

CREATE FUNCTION TESTDAT.FNREPORT (
    DATESTART DATE ,
    DATEEND DATE ,
    ICLASS CHAR(3) )
    RETURNS TABLE (
    A INTEGER )
    LANGUAGE SQL
    SPECIFIC TESTDAT.FNCMSREPORT
    NOT DETERMINISTIC
    READS SQL DATA
    CALLED ON NULL INPUT
    DISALLOW PARALLEL
    SET OPTION  ALWBLK = *ALLREAD ,
    ALWCPYDTA = *OPTIMIZE ,
    COMMIT = *NONE ,
    DECRESULT = (31, 31, 00) ,
    DFTRDBCOL = *NONE ,
    DYNDFTCOL = *NO ,
    DYNUSRPRF = *USER ,
    SRTSEQ = *HEX
    BEGIN ATOMIC
        RETURN
        SELECT 1 AS A FROM SYSIBM . SYSDUMMY1 ;
        END  ;

Just to clarify that the solutions suggested below did not work.
If the query above was changed to

SELECT * FROM TABLE(TESTDAT.FNREPORT(DATE('10/23/2013'), DATE('10/23/2013'), CAST('ANY' AS CHAR()3)) AS T

or parameter ICLASS removed, or all parameters removed from the function definition, the exact same error was reported.

As Heinz Z. did, I discovered the problem.

One of your function parameters is char, while you pass the string literal 'ANY', which is considered a varchar. The database engine looks for an overloaded version of the function with varchar parameters, but doesn't find it.

Solution is either

changing function parameter to varchar
or cast parameter to char in the function call:

SELECT *
FROM TABLE(TESTDAT.FNREPORT(DATE('10/23/2013'),
                            DATE('10/23/2013'),
                            CAST('ANY' AS CHAR(3))
          )) AS T

If doesn't work, try in any case to remove all parameters from the function and see if they are the culprits. Then you can investigate deeper adding one by one, try to work on dates format, for example you can try to pass current date instead of 10/23/2013.

Also you must investigate on why it thinks that FNREPORT is a type and not a function...


MySQL different result view vs query I have a following query. THIS returns me correct result. Note the criteria.

SELECT
        `review_score_aggregate`.`review_id` AS `id`,
        `review_score_aggregate`.`business_id` AS `business_id`,
        `review_score_aggregate`.`review_channel_id` AS `review_channel_id`,
        `review_score_aggregate`.`review_date` AS `review_date`,
        COUNT(0) AS `review_count`,
        `review_score_aggregate`.`average_score` AS `average_score`,
        TRUNCATE(AVG(`review_score_aggregate`.`rating`),
            2) AS `average_rating`,
        SUM((CASE
            WHEN (`review_score_aggregate`.`average_score` >= 0) THEN 1
            ELSE 0
        END)) AS `positive_count`,
        SUM((CASE
            WHEN (`review_score_aggregate`.`average_score` < 0) THEN 1
            ELSE 0
        END)) AS `negative_count`
    FROM
        `review_score_aggregate`
    WHERE business_id = 10 AND review_channel_id = 3 // CRITERIA
    GROUP BY `review_score_aggregate`.`business_id`
        , MONTH(`review_score_aggregate`.`review_date`)
        , YEAR(`review_score_aggregate`.`review_date`)

Correct result:

Now preferably I want to use view for this same query which is defined exactly like this:

CREATE VIEW `review_score_monthly_view` AS
    SELECT
        `review_score_aggregate`.`review_id` AS `id`,
        `review_score_aggregate`.`business_id` AS `business_id`,
        `review_score_aggregate`.`review_channel_id` AS `review_channel_id`,
        `review_score_aggregate`.`review_date` AS `review_date`,
        COUNT(0) AS `review_count`,
        `review_score_aggregate`.`average_score` AS `average_score`,
        TRUNCATE(AVG(`review_score_aggregate`.`rating`),
            2) AS `average_rating`,
        SUM((CASE
            WHEN (`review_score_aggregate`.`average_score` >= 0) THEN 1
            ELSE 0
        END)) AS `positive_count`,
        SUM((CASE
            WHEN (`review_score_aggregate`.`average_score` < 0) THEN 1
            ELSE 0
        END)) AS `negative_count`
    FROM
        `review_score_aggregate`
    GROUP BY `review_score_aggregate`.`business_id`
        , MONTH(`review_score_aggregate`.`review_date`)
        , YEAR(`review_score_aggregate`.`review_date`)

.. and through the application I would simply query from the view. Now this gives me way different result.

SELECT *
FROM review_score_monthly_view
WHERE business_id = 10
    AND review_channel_id = 3;

Wrong result??!

Can't find what causes this, any ideas please.

In the first case the filtering by WHERE is done before the grouping so only those with review_channel_id = 3 are aggregated, but in the second case all are aggregated and only those who get review_channel_id = 3 from group by are shown.

What's probably a worse problem - you use GROUP BY incorrectly by selecting columns which are neither grouped by, nor aggregated or functionally dependent on the grouping columns (id, review_channel_id etc.).

To explain that imagine a table of fruit:

kind  | color | price
------+-------+------
apple | red   |     5
apple | green |     7

You can do

select kind, avg(price) from fruit group by kind;
> apple - 6

but if you try

select kind, avg(price), color from fruit group by kind;
> apple - 6 - ??(red/green)

The column can contain only one value and you gave it no instructions to decide which one so it picks one "randomly" (= it depends on specific implementation and the plan used to execute the query - implementation details can change between versions and the plan can change between two executions) so you may get different results from the same query and no one of them is "right".

You are allowed to do that in MySQL because it cannot detect functional dependencies before version 5.7.5 so it lets you write the code but trusts you to check the dependencies yourself. You can make it not to trust you by enabling ONLY_FULL_GROUP_BY - with that your query would not be allowed at all.

In 5.7 the check was implemented which can decide if the column is functionally dependent on the grouping columns -> if for each group there is at most one possible value for that column. Imagine another table fruit_shape, where "apple" is "round" and "pear" is "pear-shaped", if you join such a table to the fruits, each "apple" will always only be "round" by definition so there is no need to pick from different values as with red/green => the shape is functionally dependent on the kind.

If you want to learn more, try this article and its follow-up for 5.7

Now for your case:

do not use views if you can avoid it - there are some performance hits because the planner cannot work freely with the views as it can in the case you use the full query (and GROUP BY in the view definition limits the options even more)
if you have to use the view for any reason, add the review_channel_id to the GROUP BY list - it will compute the results for all (business, channel) pairs (and for each month of those) and you can filter by WHERE in the query later - but if you used the full query in the first time, it would compute only those pairs you ask for
do not select the id (=review_id) if there are more than one of them for any (business, channel, year, month) tuple - it will not give any meaningful value
the same with the review date probably, select year() and month() instead (or make the date showing the first day of a given month, but I would guess even 5.7 will have a hard time to detect that dependency even when it exists)


Splitting a column to normalized table I have a de-normalized table as

Original De-Normalized Table

id  text
23  first,second|third,fourth,fifth|sixth

I want to normalize the database to create the following tables

Table 1: (Split by "|")

id  parent_id   value
1   23          first,second
2   23          third,fourth,fifth
3   23          sixth

and then,

Table 2 (Split by ",")

id  parent_id   value
1   1           first
2   1           second
3   2           third
4   2           fourth
5   2           fifth
6   3           sixth

The easiest and straightforward method is to use sub-query (SELECT), but my concern is the performance, as this process should be conducted for a very long table and regularly (new data coming).

Is there an efficient way to normalize this table with high speed?

I created a Stored Procedure solution to perform the following:

Convert the txt field of t1 into an extended INSERT list delimiting by '|'
Execute the extended INSERTs
Convert the value field of t2 into an extended INSERT list delimiting by ','
Execute the extended INSERTs

Here is the sample data

DROP DATABASE IF EXISTS all_denorm;
CREATE DATABASE all_denorm;
USE all_denorm
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
CREATE TABLE t1
(
    id INT NOT NULL,
    txt TEXT
) ENGINE=InnoDB;
INSERT INTO  t1 (id,txt) VALUES
(23,'first,second|third,fourth,fifth|sixth'),
(24,'seventh,eighth|ninth|tenth,eleven|twelfth'),
(25,'thirteenth|fourteenth|fifteenth|sixteen|seventeenth'),
(26,'eighteenth,nineteenth|twentieth');
CREATE TABLE t2
(
    id INT NOT NULL AUTO_INCREMENT,
    parent_id INT NOT NULL,
    VALUE VARCHAR(255),
    PRIMARY KEY (id)
) ENGINE=InnoDB;
CREATE TABLE t3 LIKE t2;

Here is the Stored Procedure

DELIMITER $$

DROP PROCEDURE IF EXISTS `all_denorm`.`process_denorm` $$
CREATE PROCEDURE `all_denorm`.`process_denorm` ()
BEGIN

    DECLARE given_id,current_id,last_id,processing INT;
    DECLARE delimiter_char CHAR(1);

    SET current_id = 0;
    SET processing = 1;
    SET delimiter_char = '|';
    WHILE processing = 1 DO
        SELECT MIN(id) INTO given_id FROM t1 WHERE id > current_id;
        IF ISNULL(given_id) THEN
            SET processing = 0;
        ELSE
            SET current_id = given_id;
            SELECT
                REPLACE(REPLACE(CONCAT('(''',REPLACE(txt,delimiter_char,'''),('''),''')'),'(','(QQQ,'),'QQQ',FLOOR(given_id))
                INTO @insert_list FROM t1 WHERE id = given_id;
            SELECT CONCAT('insert into t2 (parent_id,value) values ',@insert_list) INTO @insert_SQL;
            PREPARE stmt FROM @insert_SQL;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END IF;
    END WHILE;

    SET current_id = 0;
    SET processing = 1;
    SET delimiter_char = ',';
    WHILE processing = 1 DO
        SELECT MIN(id) INTO given_id FROM t2 WHERE id > current_id;
        IF ISNULL(given_id) THEN
            SET processing = 0;
        ELSE
            SET current_id = given_id;
            SELECT
                REPLACE(REPLACE(CONCAT('(''',REPLACE(value,delimiter_char,'''),('''),''')'),'(','(QQQ,'),'QQQ',FLOOR(given_id))
                INTO @insert_list FROM t2 WHERE id = given_id;
            SELECT CONCAT('insert into t3 (parent_id,value) values ',@insert_list) INTO @insert_SQL;
            PREPARE stmt FROM @insert_SQL;
            EXECUTE stmt;
            DEALLOCATE PREPARE stmt;
        END IF;
    END WHILE;

    SELECT * FROM t1;
    SELECT * FROM t2;
    SELECT * FROM t3;

END $$

DELIMITER ;

Here it is executed

mysql> DROP DATABASE IF EXISTS all_denorm;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DATABASE all_denorm;
Query OK, 1 row affected (0.00 sec)

mysql> USE all_denorm
Database changed
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS t2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS t3;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t1
    -> (
    ->     id INT NOT NULL,
    ->     txt TEXT
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO  t1 (id,txt) VALUES
    -> (23,'first,second|third,fourth,fifth|sixth'),
    -> (24,'seventh,eighth|ninth|tenth,eleven|twelfth'),
    -> (25,'thirteenth|fourteenth|fifteenth|sixteen|seventeenth'),
    -> (26,'eighteenth,nineteenth|twentieth');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE t2
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     parent_id INT NOT NULL,
    ->     VALUE VARCHAR(255),
    ->     PRIMARY KEY (id)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE t3 LIKE t2;
Query OK, 0 rows affected (0.05 sec)

mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS `all_denorm`.`process_denorm` $$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE PROCEDURE `all_denorm`.`process_denorm` ()
    -> BEGIN
    ->
    ->     DECLARE given_id,current_id,last_id,processing INT;
    ->     DECLARE delimiter_char CHAR(1);
    ->
    ->     SET current_id = 0;
    ->     SET processing = 1;
    ->     SET delimiter_char = '|';
    ->     WHILE processing = 1 DO
    ->         SELECT MIN(id) INTO given_id FROM t1 WHERE id > current_id;
    ->         IF ISNULL(given_id) THEN
    ->             SET processing = 0;
    ->         ELSE
    ->             SET current_id = given_id;
    ->             SELECT
    ->                 REPLACE(REPLACE(CONCAT('(''',REPLACE(txt,delimiter_char,'''),('''),''')'),'(','(QQQ,'),'QQQ',FLOOR(given_id))
    ->                 INTO @insert_list FROM t1 WHERE id = given_id;
    ->             SELECT CONCAT('insert into t2 (parent_id,value) values ',@insert_list) INTO @insert_SQL;
    ->             PREPARE stmt FROM @insert_SQL;
    ->             EXECUTE stmt;
    ->             DEALLOCATE PREPARE stmt;
    ->         END IF;
    ->     END WHILE;
    ->
    ->     SET current_id = 0;
    ->     SET processing = 1;
    ->     SET delimiter_char = ',';
    ->     WHILE processing = 1 DO
    ->         SELECT MIN(id) INTO given_id FROM t2 WHERE id > current_id;
    ->         IF ISNULL(given_id) THEN
    ->             SET processing = 0;
    ->         ELSE
    ->             SET current_id = given_id;
    ->             SELECT
    ->                 REPLACE(REPLACE(CONCAT('(''',REPLACE(value,delimiter_char,'''),('''),''')'),'(','(QQQ,'),'QQQ',FLOOR(given_id))
    ->                 INTO @insert_list FROM t2 WHERE id = given_id;
    ->             SELECT CONCAT('insert into t3 (parent_id,value) values ',@insert_list) INTO @insert_SQL;
    ->             PREPARE stmt FROM @insert_SQL;
    ->             EXECUTE stmt;
    ->             DEALLOCATE PREPARE stmt;
    ->         END IF;
    ->     END WHILE;
    ->
    ->     SELECT * FROM t1;
    ->     SELECT * FROM t2;
    ->     SELECT * FROM t3;
    ->
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;

Here is the output:

mysql> CALL process_denorm();
+----+-----------------------------------------------------+
| id | txt                                                 |
+----+-----------------------------------------------------+
| 23 | first,second|third,fourth,fifth|sixth               |
| 24 | seventh,eighth|ninth|tenth,eleven|twelfth           |
| 25 | thirteenth|fourteenth|fifteenth|sixteen|seventeenth |
| 26 | eighteenth,nineteenth|twentieth                     |
+----+-----------------------------------------------------+
4 rows in set (0.01 sec)

+----+-----------+-----------------------+
| id | parent_id | VALUE                 |
+----+-----------+-----------------------+
|  1 |        23 | first,second          |
|  2 |        23 | third,fourth,fifth    |
|  3 |        23 | sixth                 |
|  4 |        24 | seventh,eighth        |
|  5 |        24 | ninth                 |
|  6 |        24 | tenth,eleven          |
|  7 |        24 | twelfth               |
|  8 |        25 | thirteenth            |
|  9 |        25 | fourteenth            |
| 10 |        25 | fifteenth             |
| 11 |        25 | sixteen               |
| 12 |        25 | seventeenth           |
| 13 |        26 | eighteenth,nineteenth |
| 14 |        26 | twentieth             |
+----+-----------+-----------------------+
14 rows in set (0.01 sec)

+----+-----------+-------------+
| id | parent_id | VALUE       |
+----+-----------+-------------+
|  1 |         1 | first       |
|  2 |         1 | second      |
|  3 |         2 | third       |
|  4 |         2 | fourth      |
|  5 |         2 | fifth       |
|  6 |         3 | sixth       |
|  7 |         4 | seventh     |
|  8 |         4 | eighth      |
|  9 |         5 | ninth       |
| 10 |         6 | tenth       |
| 11 |         6 | eleven      |
| 12 |         7 | twelfth     |
| 13 |         8 | thirteenth  |
| 14 |         9 | fourteenth  |
| 15 |        10 | fifteenth   |
| 16 |        11 | sixteen     |
| 17 |        12 | seventeenth |
| 18 |        13 | eighteenth  |
| 19 |        13 | nineteenth  |
| 20 |        14 | twentieth   |
+----+-----------+-------------+
20 rows in set (0.04 sec)

Query OK, 0 rows affected (0.10 sec)

mysql>

Give it a Try !!!


Get-MSSQLLinkPasswords Powershell script doesn't seem to work under SQL Server 2016 I've been using the Get-MSSQLLinkPasswords Powershell script for a long time and it has been a huge help.  The script decrypts the passwords for all linked servers on a particular Windows server and shows them to you in plain text. This functionality is even incorporated into the dbatools Copy-SqlLinkedServer script.

There are security caveats for running the script as show here

  The script must be run locally on the MSSQL server (as DPAPI requires
  access to the local machine key). The user executing the script must
  also have sysadmin access to all the database instances (for the DAC
  connection) and local admin privileges on the Windows server (to
  access the entropy bytes in registry). In addition, if UAC is enabled,
  the script must be ran as an administrator.

This script has worked flawlessly until recent upgrades to SQL Server 2016.  I was wondering if anyone else has encountered any issues with this script under SQL Server 2016.  Perhaps some security model has changed in SQL Server 2016 and this script is unable to decrypt the passwords now.

As time permits, I will try to see if the dbatools Copy-SqlLinkedServer script still works on 2016.

I've tested dbatools' Copy-SqlLinkedServer from SQL Server 2016 to SQL Server 2016 and SQL Server 2014 to SQL Server 2016.

All linked servers and their respective logins were successfully migrated.

I checked both the permissions and ensured the same login was being used. What versions are to you migrating from? What error do you get?

(I am the creator of dbatools).


Can PostgreSQL use nulls in its indexes? I've been reading this book which says that

  The database assumes that Indexed_Col IS NOT NULL covers too large a range to be useful, so the database will not drive to an index from this condition.

I recognize that the book is more than 10 years old, but it has already proven quite useful--Using instructions gleaned from its pages, I've sped a query up by a factor of ten.

Further, in running EXPLAIN ANALYZE on a SELECT query, I've found that none of my indexes are being used, even when by all rights, they ought to be.

Thus, my question is:

Supposing there is a table which has a column, whose column definition includes "NOT NULL", and that an index exists which covers this column, would this index be used in a query of that table where the columns is part of the query?

Like:

CREATE TABLE my_table(
a varchar NOT NULL
);

CREATE INDEX ix_my_table ON my_table(a);

SELECT a from my_table;

PostgreSQL certainly can use an index for IS NOT NULL. I don't see any query planner assumptions about that condition, either.

If the null fraction for the column (pg_statistic.stanullfrac) is low enough to suggest that the index is usefully selective for the query, PostgreSQL will use an index.

I can't figure out what you're trying to say with:

  If this is correct, is my understanding that an index on a column defined as "NOT NULL" not be used in a query which uses that column?

Certainly an index won't get used for an IS NOT NULL condition on a NOT NULL column. It'd always match 100% of rows, so a seqscan will almost always be much faster.

PostgreSQL won't use an index if the index doesn't filter out a large proportion of rows for a query. The only likely exception is when you're asking for a set of columns covered by a single index, in an order matching that of the index. PostgreSQL might do an index-only scan then. E.g. if there's an index on t(a, b, c) and you:

select a, b FROM t ORDER BY a, b, c;

PostgreSQL might use your index, even though no rows are filtered out, because it only has to read the index and can skip reading the heap, avoid doing a sort, etc.


Keep track of the last read record in MySQL I have 2 databases in MySQL:

1) An input Latitude-Longitude_dB ('latlong_db', henceforth): It has the latitude and longitude of each reading from a GPS tracking device.

2) A Weather_db: I read the input latlongs from dB1, and calculate 'current' weather data for each pair of latlongs (eg: humidity, cloud_coverage) . This weather data is written into a Weather_db.

The issue is: I need to keep track of which record (which 'input latlong') was read last. This is so that I don't recalculate weather_data for the latlongs that I've already covered. How do I keep track of the last read input_latlong?

Thank you so much.

6 decimal places for lat/lng give a precision of 16cm (1/2 ft).  I would expect the GPS to fluctuate more than that.  In other words, should you really watch the lat/lng at that resolution, or maybe round to 3 decimal places?  That would give you 160m (520 ft) resolution, more like what the weather tracking is capable of.

Back to your question.

It is somewhat messy to fetch the "latest", especially if you want the "latest for each vehicle.  So, instead, I suggest keeping another table of the "Current" location.  (What you have is the "History" of the location.)

Do you need a history of the weather?  Or is the latest all you need?

Caution:  If you have thousands of vehicles and you are collecting lots of data every 10 minutes, you will rapidly fill up the disk.  So try to minimize the size of the datatypes in use.  Also, consider tossing data when a vehicle is 'idle'; this will cut in half the disk requirements?

More on lat/lng representation sizes versus representations:  http://mysql.rjweb.org/doc.php/latlng#representation_choices


Enable SSMS in SQL Server 2005 Developer Edition We just had an upgrade of all our computers here, and on one of them we loaded our licensed copy of Visual Studio 2008 and SQL Server 2005 Developer edition.  I then loaded the service packs (4 for SS).  There is still no icon for SSMS in the Start Menu.

How do I enable SSMS in this version of SQL Server?

(OS: Windows 7 64-bit)

Did you search your system for SqlWb.exe or ssms.exe? If those aren't there then run setup again and make sure you choose "Workstation Components, Books Online and Development Tools."


Mongo Cluster sharded replica cluster design question I have 5 old servers, each with single socket CPU, 4 physical cores(8 logical) and 128GB of RAM. I am getting started with mongoDB and plan to deploy MongoDB 4.2.2 on my 5 servers for a lab environment. I installed Virtual Box on each one, and plan to have a high-available installation of MongoDB. After a lot of reading I came up with a design and I'd like to get an opinion on my setup, particularly the shard and replica portion of the cluster.

I have the following:

5 Physical servers(denoted as P1 through P5) each with Virtual Box installed. All the VMs are running Ubuntu 16 Server edition guest OS.
3 Config servers sitting on 3 different VMs on 3 different physical servers. Each config server is allocated 1CPU and 4GB of RAM. Let's call these Conf1_P1 (Config server 1 is sitting on Physical server P1), Conf2_P2, Conf3_P3
2 mongos routers sitting on 2 different VMS on 2 different physical servers. Each Router is allocated 2CPUs and 8GB RAM. Let's call these Router1_P4 and Router2_P5
5 Arbiters sitting on 5 different VMs on 5 different physical servers. Each arbiter is allocated 1 CPU and 2GB of Ram. Let's call these Ar1_P2(Arbiter server 1 is sitting on Physical server P2), AR2_P3, AR3_P4, AR4_P5, AR5_P1
5 shards sitting on 5 different VMs on 5 different physical servers:

3 shards are allocated 2 CPUs, and 8GB of RAM. Let's call these SH1_P1, SH2_P2, SH3_P3.
2 Shards are allocated 1 CPU and 8GB RAM. Let's call these SH4_P4, SH5_P5. These are allocated 1 CPU because the physical server these VMS are sitting on only has 4 physical core, and 2 are already assigned to run Mongo Router.

5 replica members: 1 replica/shard on 5 different VMs, each is assigned 1 CPU and 8GB RAM. Let's call these REP_SH1_P2(the replica for shard 1 is sitting on physical server P2), REP_SH2_P3,  REP_SH3_P4,  REP_SH4_P5,  REP_SH5_P1
1 replica set for the 3 config servers
5 replica set, each with 2 members and 1 arbiter:

ReplSet1: SH1_P1, REP_SH1_P2, Ar1_P2
ReplSet2: SH2_P2, REP_SH2_P3, Ar2_P3
ReplSet3: SH3_P3, REP_SH3_P4, Ar3_P4
ReplSet4: SH4_P4, REP_SH4_P5, Ar4_P5
ReplSet5: SH5_P5, REP_SH5_P1, Ar5_P1

The summary for each physical server's resources usage is as follow

I decided to go with 5 shards to maximize parallelism (horizontal scaling).
The choice to have only 2 members in a replica set is due to disk space constrain. What are your thoughts on this particular design? Any concern I should be aware of?

From comments:

mongoS doesn't need much memory/CPU (or even its own machine) and can be located (installed) on the config servers.
config servers don't need that much memory, even when mongoS is there; 1-2GB is enough.

– JJussi


Is it safe to copy the binlogs from a running mysql server I want to backup a tokudb database.

I want to be able to restore it to the most recent state.
Which I am writing to a file at backup.

My guess would be to run something like this

mysql -e "SHOW MASTER STATUS; SHOW SLAVE STATUS; FLUSH LOGS" > mylogposition.log

Which will rotate the log files after saving the position?

As long as you have done flush logs and you have taken the logfilename just before flush logs say it should be fine.

Example current binlog file is bin-log00333 and after flush logs it is bin-log00334. You may scp the logs from bin-log00001 to bin-log00333 and don't take bin-log00334.


Why does SQL Server 2012 use 6x as much space for same DB? We have a legacy database in MS SQL Server 2005. It has the latest service pack, so the backup format is compatible with 2012. The database is around 9GB, nearly all of which is data (the transaction log has been backed up). The database backup is 9.1 GB. When we transfer the backup to the 2012 machine, and try to restore, it fails saying that there is insufficient disk space available. It says that 60 GB will be required to complete the restore.

Why is this? Does it have to make 6 temporary copies in order to migrate from 2005? Will it keep on using 60 GB after it is restored? Is there some way to do the restore without freeing up 60GB on the server?

Here is the output of sp_helpfile:

STARS_Data  1   C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\STARS.mdf  PRIMARY 9674880 KB  Unlimited   10% data only
STARS_Log   2   C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\STARS_1.LDF    NULL    299200 KB   Unlimited   10% log only

Note that the database only uses around 10GB on the 2005 server.

I backed up to a completely new file. There is only one backup in the file; it was not appended. There is no problem restoring to 2005: it doesn't use vast amounts of additional space.

The hard drive has 20GB free.

The backup size will be around the same size as the used space in the data file if you aren't using backup compression.

The database files total 60GB on the source server, so that's what's needed on the server where you are doing a restore. While only 9GB is in use, the file size is 60GB.

You could shrink the files down on the source server and then back it up, but shrinks are bad for performance reasons. Instead, free up some space on the restore server or add more space. Disks are cheap.


Capturing mongo replica set state from mongo shell I am trying to capture mongo replica set state from mongo shell. E.g.

[root@server ~]$ mongo
MongoDB shell version v3.6.8
connecting to: mongodb://127.0.0.1:27017
MongoDB server version: 3.6.8
rs0:SECONDARY>

I'd like to capture that SECONDARY keyword, and to do it in one command. I've tried something like mongo > output, however it requires a manual ctrl+c to terminate the process.

I have tried
parse rs.status() output to display one secondary node for mongodump destination

But the solution doesn't always work. Different mongo version can output different message. ARBITER node does not have authentication data either.

The output you are trying to capture is the default prompt for the mongo shell in interactive mode.

As at MongoDB 4.0 there isn't a built-in helper to return replica set member status as a string, but you can derive the same status in JavaScript.

For example, create the following as getstate.js:

    var mystate = 'UNKNOWN';

    // Note: isMaster command does not require authentication
    var isMaster = db.isMaster();

    if (isMaster.msg == "isdbgrid") {
        mystate = "mongos";
    } else if (isMaster.ismaster && !isMaster.setName) {
        mystate = 'STANDALONE';
    }

    if (isMaster.setName) {
        if (isMaster.secondary) {
            mystate = 'SECONDARY';
        } else if (isMaster.arbiterOnly) {
            mystate = "ARBITER";
        } else if (isMaster.primary === isMaster.me) {
            mystate = 'PRIMARY';
        }
    }

    print(mystate);

Now invoke mongo using --quiet to suppress any startup messages or warnings:

$ mongo localhost:27017 getstate.js --quiet
PRIMARY

$ mongo localhost:27018 getstate.js --quiet
SECONDARY

$ mongo localhost:27019 getstate.js --quiet
ARBITER


Permission denied for relation I have ran the following SQL in psql:

CREATE USER bspu LOGIN;

CREATE DATABASE bsp OWNER bspu;

GRANT ALL PRIVILEGES ON DATABASE bsp TO bspu;

\c bsp

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  client_id VARCHAR(20) NOT NULL,
  api_key VARCHAR(100) NOT NULL,
  api_secret VARCHAR(100) NOT NULL,
  auth_token VARCHAR(128) NOT NULL
);

When I login as bspu, and try to query the users table, I get the error:

permission denied for relation users

I tried running:

ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO bspu;

But it doesn't help. What am I doing wrong? Why does database owner NOT have permissions to query its own database?

EDIT:
I upgraded bspu to superuser for now, so I can continue work. Any further guidance appreciated.

DEFAULT PRIVILEGES do not change permissions for existing objects. They are the default privileges for newly created objects and only for the particular role they belong to. If you do not specify the role when running ALTER DEFAULT PRIVILEGES, it defaults to the current role (when executing the ALTER DEFAULT PRIVILEGES statement.
Also, since you are using a serial column, which creates a SEQUENCE, you'll want to set default privileges for sequences as well.
Run this on the user you create objects with, before you run the CREATE command:
ALTER DEFAULT PRIVILEGES [ FOR ROLE my_create_role] GRANT ALL ON TABLES TO bspu;
ALTER DEFAULT PRIVILEGES [ FOR ROLE my_create_role] GRANT ALL ON SEQUENCES TO bspu;

A word of caution for pgAdmin users. There is a bug in all versions of pgAdmin III and pgAdmin4 (including v5.3). The reverse engineered SQL script for the database or schema nodes displays DEFAULT PRIVILEGES ignoring  the owning user and is therefore incorrect in certain situations. I reported the bug (repeatedly), but the project encountered difficulties fixing it. pgAdmin III was discontinued in favor of pgAdmin4. The bug is still there in pgAdmin4.
For existing objects you may also be interested in this "batch" form of the GRANT command:
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO bspu;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO bspu;

More under this related question on SO:

Grant all on a specific schema in the db to a group role in PostgreSQL


Fetch data from SQL Server table of the ERP DB I have pretty old ERP system running on SQL Server 2005 (about 20GB database. Largest able has 2 million rows). Unfortunately, the ERP can't run on SQL Server 2008 or newer. I have a huge amount of legacy systems as well. Some of them are VBA frontend and stored procedures as backend and business logic. Some of them are C# applications. I know it is a very bad approach and I'm looking for better solution. I fetch data directly from ERP DB with SELECT WITH (NOLOCK).

If one day (hopefully soon) I will have to replace my ancient ERP with a new one, it will be a nightmare to update thousands of stored procedures, VBA and C# code.

The only solution I can think about is a definition of objects. For example, I plan to maintain P/N table which will hold all my part numbers and relevant data. On another hand, I have to merge the data from ERP to the P/N table every 5 minutes (either SSIS or stored procedure) to keep it updated. It is a huge load on my ERP DB. Considering tremendous amount of objects (warehouses, inventory data, logistics and financial transactions, engineering data) this approach will kill my ERP DB.

Can you propose a better solution?

If you really want to move lots of data off the source server often, maybe you should consider the db's native solution of

Replication.

You can organize it to push only what you want it to push.
It is made to operate in parallel to normal operation.  There are lots of configurations to balance getting it thorough versus not bothering the server too much (at both ends).  It can do things like read transactions logs with separate Services, avoiding running queries through the db engine at all.

There's a ton of information out there.
You can start with the official stuff at:
https://technet.microsoft.com/en-us/library/ms151198(v=sql.90).aspx
It walks you through a lot of the options, including
choosing an overall Replication type,
and considerations for Data Warehousing and Reporting scenarios.

Any internet search for more help on Replication, including on SO, will turn up a lot more.


Select Distinct on subset of columns, name different set of columns to return I am using PostgreSQL and have two tables:

Table_A
colA | colB | colC | colD | colE | colF | colG | colH | colI | colJ

Table_B
colA | colB | colC | columnD | colE | columnF | colG | colH | colI | colJ

I am trying to insert a number of rows from table_B into table_A. My problem is that Table_A has a primary key based on colA, colB, colC, colD, and colE. Table_B does not have this restriction, which means a simple insert won't work:

INSERT INTO Table_A (colA, colB, colC, colD, colE, colF, colG, colH, colI, colJ)
SELECT colA, colB, colC, columnD, colE, columnF, colG, colH, colI, colJ FROM Table_B;

I am trying to work around this by using DISTINCT in my selection from Table_B. However, I cannot determine the correct syntax to both select distinct on the 5 primary key columns used in Table_A, and select all ten columns to be inserted. I have tried

INSERT INTO Table_A (colA, colB, colC, colD, colE)
SELECT DISTINCT colA, colB, colC, columnD, colE FROM Table_B;

Which correctly pulls unique entries but does not populate columns F-J, and I have tried

INSERT INTO Table_A (colA, colB, colC, colD, colE, colF, colG, colH, colI, colJ)
SELECT DISTINCT(colA, colB, colC, columnD, colE) colA, colB, colC, columnD, colE, columnF, colG, colH, colI, colJ FROM Table_B;

But this fails, as the first column entry is a wrapped version of the 5 unique columns, and fails the insert due to column length restrictions - the SELECT DISTINCT in parens returns '' which obviously doesn't fit:

  ERROR: Value is too long for type character varying(12)

My goal would be form a query which gets all the unique combinations of colA, colB, colC, columnD, colE from Table_B, and inserts those full rows, including columnF, colG, colH, colI, colJ into Table_A.

You're almost there. SELECT DISTINCT returns that group of columns, as you've found out. If you use DISTINCT ON instead though, you'll get the rows you're looking for.

INSERT INTO Table_A
    (colA, colB, colC, colD, colE, colF, colG, colH, colI, colJ)
SELECT DISTINCT ON (colA, colB, colC, columnD, colE)
    colA, colB, colC, columnD, colE, columnF, colG, colH, colI, colJ
FROM
    Table_B

As pointed out by ypercube and in the Postgres docs, you can improve this query by adding ORDER BY. Without it, it seems that the choice between two conflicting rows is unpredictable.

INSERT INTO Table_A
    (colA, colB, colC, colD, colE, colF, colG, colH, colI, colJ)
SELECT DISTINCT ON (colA, colB, colC, columnD, colE)
    colA, colB, colC, columnD, colE, columnF, colG, colH, colI, colJ
FROM
    Table_B
ORDER BY
    colA, colB, colC, columnD, colE    -- needed as it is
--  , colX, colY                       -- to choose which row to pick
  ;


Get correct row version based on combination of dates - query simplification In the example below, I need to fetch the row that has the latest data based on a combination of dates. I cannot simply do MAX(insert_date), MAX(update_date) as it does not return the correct data. The way that it works right now is to get the MAX(insert_date) then do a self join to obtain the MAX(update_date) then self join to return the row values.

Is there a better and more efficient way of doing this? The example below only contains 4 rows but in production I will be processing about 1 million rows every few minutes.

Example:

create table #temp (
iud char(1) not null,
id int not null,
date date not null,
value decimal(9,2) not null,
insert_date datetimeoffset not null,
update_date datetime2 not null
);

insert #temp
values
('i', 1001, '2001-01-01', 2, '2001-01-01 00:00', '2001-01-01 00:00'),
('i', 1001, '2001-01-01', 9, '2001-01-01 00:00', '2001-01-01 01:00'),
('i', 1001, '2001-01-01', 7, '2001-01-02 00:00', '2001-01-01 00:30'),
('i', 1001, '2001-01-01', 4, '2001-01-02 00:00', '2001-01-01 00:00');

-- this is wrong as it returns no results
select t.*
from #temp as t
join (select iud, id, date, max(insert_date) as insert_date, max(update_date) as update_date
      from #temp
      group by iud, id, date) as x
on t.iud = x.iud
and t.id = x.id
and t.date = x.date
and t.insert_date = x.insert_date
and t.update_date = x.update_date;

-- this works, but can it be simplified?
select n.*
from #temp as n
join (
    select n.iud, n.id, n.date, n.insert_date, max(update_date) as update_date
      from #temp as n
      join (select iud, id, date, max(insert_date) as insert_date
              from #temp
          group by iud, id, date) as i
    on i.iud = n.iud
  and i.id = n.id
  and i.insert_date = n.insert_date
group by n.iud, n.id, n.date, n.insert_date) as x
on x.date = n.date
and x.insert_date = n.insert_date
and x.iud = n.iud
and x.id = n.id
and x.update_date = n.update_date
order by n.iud, n.id, n.date;

drop table #temp;

If I've understood what you are trying to do correctly then

WITH T AS
(
SELECT *,
       RN = RANK() OVER (PARTITION BY  iud, id, date ORDER BY insert_date DESC, update_date DESC)
FROM #temp
)
SELECT *
FROM T
WHERE RN=1;

May well perform better - especially if there is a covering index on (iud, id, date, insert_date DESC, update_date DESC)


SSMS Disk usage reports reports no data file for online database We have a rather large database running on SQL Server 2012. The data file is now 2 118 256 MB. For a couple of weeks now, whenever I run the SSMS report Disk Usage, it says at the very bottom that "There is no data file for MyDatabase database."

Anyone seen this before? The database is up and running. Is there a size constraint on what this report will show?

Yes, this report fails when the database is over 2 TB.
See this connect item: SQL Server Management Studio Standard Reports - Disk Usage

Create a new 2 TB database and run the Disk Usage report to display
results.

For Disk Space Used by Data Files, rather than displaying the Filegroup Name, Logical File Name, Physical File Name, Space Reserved,
Space Used, I get a message of "There is no data file for
database."


How to mention declared integer in where condition using dynamic sql? I tried with 1,2 and 3 quotes and with/without conversion, but I am getting

Error: Conversion failed when converting the varchar value

Can someone please help me?
with conversion
            left join xxx.xxx.PITM'+@Datemm_yyyy+' pitm
                on nep.s_pitm_c=pitm.pitm_c
                and  cast(year(s_tran_d) as nchar)='''+@yyyy+'''
                and cast(month(s_tran_d) as nchar) ='''+@mm+'''

            leftjoin xxx.xxx.PITM'+@Datemm_yyyy1+' pitm1
                on nep.s_pitm_c=pitm1.pitm_c
                and cast(year(dateadd(m,-1,s_tran_d)) as nchar) =''+ @yyyy1 +''
                and cast(month(s_tran_d)-1 as nchar)= '''+@mm1 +'''

without conversion
        inner join xxx.xx.PITM'+@Datemm_yyyy+' pitm on nep.s_pitm_c=pitm.pitm_c
        and  year(s_tran_d) =''+@yyyy+'' and month(s_tran_d)  =''+@mm+''

        inner join xxx.xx.PITM'+@Datemm_yyyy1+' pitm1 on nep.s_pitm_c=pitm1.pitm_c
        and year(dateadd(m,-1,s_tran_d))  =''+ @yyyy1 +'' and month(s_tran_d)-1= ''+@mm1 +''

        I also tried by keeping 1 or 2 or 3 single quotes.

When I put 2 quotes I am getting

Error: Conversion failed when converting the varchar value '+@yyyy+' to data type int.

Full code:
Declare @sql nvarchar(max)
Declare @StartDate Date;
Declare @EndDate Date;
Declare @Datemm_yyyy Char(7);
Declare @StartDate1 Date;
Declare @EndDate1 Date;
Declare @Datemm_yyyy1 Char(7);
declare @yyyy int;
declare @yyyy1 int;
declare @mm int;
declare @mm1 int;

Set @StartDate='20050501'
Set @EndDate=EOMonth(@StartDate);
Set @Datemm_yyyy=Replace(right(Convert(Varchar(10),@StartDate,105),7),'-','_')
Set @StartDate1='20050401'
Set @EndDate1=EOMonth(@StartDate1);
Set @Datemm_yyyy1=Replace(right(Convert(Varchar(10),@StartDate1,105),7),'-','_')
set @yyyy=year(@StartDate)
set @mm=month(@StartDate)
set @yyyy1=year(@StartDate1)
set @mm1=month(@StartDate1)

print @StartDate
print @EndDate
print @Datemm_yyyy
print @StartDate
print @EndDate
print @Datemm_yyyy
print @yyyy
print @yyyy
print @mm
print @mm1

While @StartDate<'20181101'
Begin
    Set @sql='

insert into ##loan_account_details_focis

select
nep.s_pitm_c,
nep.s_tran_d,
pitm.LOAN_CNSN_STRT_D loancategory_start_date,
pitm1.LOAN_CNSN_STRT_D lastmonth_loan_catgory_start,
pitm.TFPR_D transfer_pricing_date

from
xxx.DBO.RK_NEP_PROBABLE_POOL nep
left join xxx.xx.PITM'+@Datemm_yyyy+' pitm on nep.s_pitm_c=pitm.pitm_c
 and  cast(year(s_tran_d) as nchar)=@yyyy
       and cast(month(s_tran_d) as nchar) =@mm

leftjoin xxx.xx.PITM'+@Datemm_yyyy1+' pitm1 on nep.s_pitm_c=pitm1.pitm_c
and cast(year(dateadd(m,-1,s_tran_d)) as nchar)  =@yyyy1 and cast(month(s_tran_d)-1 as nchar)= @mm1
'
    exec (@sql)
    print @sql
    Set @StartDate=DateAdd(Month,1,@StartDate)
    Set @EndDate=EOMonth(@StartDate);
    Set @Datemm_yyyy=Replace(right(Convert(Varchar(10),@StartDate,105),7),'-','_')

    Set @StartDate1=DateAdd(Month,1,@StartDate1)
    Set @EndDate1=EOMonth(@StartDate1);
    Set @Datemm_yyyy1=Replace(right(Convert(Varchar(10),@StartDate1,105),7),'-','_')
    set @yyyy=year(@StartDate)
    set @yyyy1=year(@StartDate1)
    set @mm=month(@StartDate)
    set @mm1=month(@StartDate1)
    --print @startDate
End
;

IMHO the best option to deal with dynamic SQL is:

Build your command
PRINT the resultant text.
Copy & paste and execute it in a new query window just to check it.

Example:
CREATE TABLE t (id int identity, foo varchar(10));

INSERT INTO t VALUES ('RED'),('GREEN'),('BLUE');

DECLARE @cmd NVARCHAR(MAX);
DECLARE @param VARCHAR(10) = 'GREEN';

SET @cmd = 'SELECT id, foo FROM t WHERE foo = ' + @param

PRINT @cmd;

Then execute the command:
SELECT id, foo FROM t WHERE foo = GREEN

SELECT id, foo FROM t WHERE foo = GREEN

Msg 207 Level 16 State 1 Line 1
Invalid column name 'GREEN'.

db<>fiddle here


MySQL crashes and currently the error.log file is huge I've created a droplet on DigitalOcean created using Laravel Forge 6 months ago. Two weeks ago, we decided it was time to upgrade the droplet and we moved from a 4GB RAM/2CPUs to a 16GB RAM/6CPUs droplet and since a few days ago the MySQL server just crashes and the only way to make it work again is by rebooting the server (MySQL makes the server unresponsive).

When I type htop to see the list of processes is showing a few of /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysql.pid (currently is showing more than 30 entries like that).

The error log is bigger than 1GB (yes, I know!) and shows this message hundreds of times:

[Warning] InnoDB: Difficult to find free blocks in the buffer pool (21 search iterations)! 21 failed attempts to flush a page! Consider increasing the buffer pool size. It is also possible that in your Unix version fsync is very slow, or completely frozen inside the OS kernel. Then upgrading to a newer version of your operating system may help. Look at the number of fsyncs in diagnostic info below. Pending flushes (fsync) log: 0; buffer pool: 0. 167678974 OS file reads, 2271392 OS file writes, 758043 OS fsyncs. Starting InnoDB Monitor to print further diagnostics to the standard output.

The only thing that changed recently is now we send weekly notifications to customers (only the ones that subscribed to it) to let them know about certain events happening in the current week. This is kind of a intensive process, because we have a few thousands of customers, but we take advantage of Laravel Queues in order to process everything.

I've tried to change innodb_buffer_pool_size from the default value to 80% of available RAM (~13GB) and instead of the previous message, now it's showing:

"InnoDB: page_cleaner: 1000ms intended loop took 4228ms. The settings might not be optimal.".

And this change made the database run slower. For example, to process 30k records (the notifications thing that I mentioned) it took 6 hours but before the change it was taking around 3 (when it didn't crashed).

Is this a MySQL-settings related issue?

EDIT: Global Status and Variables after innodb_* suggested changes

Show Variables and Show Global Status

For the 4GB droplet, change the config: innodb_buffer_pool_size to 1500M and restart.

For the 16GB droplet, change the config:

innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 12
innodb_page_cleaners = 12

Revised Analysis  (After running more than a day)

Observations:

Version: 5.7.24-0ubuntu0.18.04.1-log
16 GB of RAM
Uptime = 1d 02:59:16
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

A lot of table scans and many are big.  This may be interfering with other InnoDB operations, hence indirectly stalling the page_cleaners.

Change to innodb_lru_scan_depth = 256 as a possible solution to the page_cleaner problem.

Details and other observations:

( innodb_lru_scan_depth ) = 1,024
-- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 511,813 / 786384 = 65.1% -- Pct of buffer_pool currently not in use
-- innodb_buffer_pool_size is bigger than necessary?

( Innodb_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 4,456,398,848 / 12288M = 34.6% -- Percent of buffer pool taken up by data
-- A small percent may indicate that the buffer_pool is unnecessarily big.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( join_buffer_size / _ram ) = 262,144 / 16384M = 0.00% -- 0-N per thread. May speed up JOINs (better to fix queries/indexes) (all engines) Used for index scan, range index scan, full table scan, each full JOIN, etc.
-- If large, decrease join_buffer_size to avoid memory pressure. Suggest less than 1% of RAM. If small, increase to 0.01% of RAM to improve some queries.

( local_infile ) = local_infile = ON
-- local_infile = ON is a potential security issue

( Handler_read_rnd_next / Com_select ) = 10,900,684,560 / 1418310 = 7,685 -- Avg rows scanned per SELECT. (approx)
-- Consider raising read_buffer_size (128K now; unclear whether raising it will help)

( Select_scan ) = 233,714 / 97156 = 2.4 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( Select_scan / Com_select ) = 233,714 / 1418310 = 16.5% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries

( Connections ) = 131,201 / 97156 = 1.4 /sec -- Connections
-- Increase wait_timeout; use pooling?

Abnormally small:

Innodb_dblwr_pages_written / Innodb_dblwr_writes = 2.31

Abnormally large:

Com_show_plugins = 0.26 /HR
Com_show_privileges = 0.037 /HR
Com_stmt_close = 21 /sec
Com_stmt_execute = 21 /sec
Com_stmt_prepare = 21 /sec
Innodb_buffer_pool_pages_free = 511,813
Performance_schema_file_instances_lost = 9
innodb_page_cleaners = 12
performance_schema_max_file_classes = 80
performance_schema_max_mutex_classes = 210

Abnormal strings:

innodb_fast_shutdown = 1
innodb_large_prefix = ON
log_slow_admin_statements = ON


Constraint to only allow unique value in a group I have a table in PostgreSQL 12 with columns "sample", "group", "value". There can be multiple samples in a group. Is it possible to implement a constraint so that each group can only have one distinct value?

EDIT:
This is for a rather complex materialized view, where I would like to make sure that there are no samples in a group that has a value that differ from the other samples. In that sense "value" should be a property of the group in this specific context, but that isn't always the case in the database. The use case is that samples are biological samples taken from different tissues ("value") and groups are e.g. "treated" and "control", and I would like to make sure that there aren't a mix of tissues within one group.

This should be ok:
('sample1', 'group1', 'value1'), ('sample2', 'group1', 'value1'), 'sample3', 'group2', 'value2'). Here there are two samples in group 1, but they both have the same value.

This should not be ok:
('sample1', 'group1', 'value1'), ('sample2', 'group1', 'value2'), 'sample3', 'group2', 'value2')

This should fail because the two samples in group 1 have different values.

I think some of the confusion came from that I used "unique" in the original post when I was talking about distinct values, but this has nothing to do with UNIQUE. For this to work a CHECK constraint for a row being added would depend on several other existing rows. I don't know if that works? Or if there should be a trigger maybe? I guess there could be an issue with race conditions on bulk inserts?

CHECK constraints cannot use subqueries, so you have to use a trigger:

CREATE FUNCTION check_one_value_per_group() RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF EXISTS (SELECT * FROM my_table
               WHERE "group" = NEW."group"
                 AND value <> NEW.value;
              ) THEN
        RAISE EXCEPTION 'different values in group %', NEW."group";
    END IF;
    RETURN NEW;
END;
$$;

CREATE CONSTRAINT TRIGGER one_value_per_group
AFTER INSERT OR UPDATE OF "group", value ON my_table
FOR EACH ROW
EXECUTE FUNCTION check_one_value_per_group();


backup history using availability groups When using availability groups it is hard to get backup history in situation of failover to secondary node. It would good to have msdb backup set data to be in-sync with all the servers involved in availability groups. can i create a procedure or script to have the backup history information for all the backups from different msdb databases to a central database ? What is the best way to do this ?

We made a SSIS package that collect msdb history from each instance and centralize it on a "DBA" database. We then "purge" the local msdb from each instance.

That way, we can query the centralize DB for any backup history and it prevent every msdb from getting to big.


Users Export From Mysql Version 8.0 to 5.7 I want to exports all users from mysql version 8.0 to 5.7. Can you guide me for this activity. I want same password and privs should migrate.

Issue fixed. I found mysql tables (db & user) columns available in 5.7. So i copy these tables from 8.0 to 5.7. And copy data only for available columns in tables db & user of mysql schema. Now its working fine.


Get data excluding time intervals in another table I have two tables, Message and BlockUser.

Table Message:

ID | Message | DateCreated
1  | M1      | 2017-09-20 13:00:00
2  | M2      | 2017-09-20 14:00:00
3  | M3      | 2017-09-21 13:00:00
4  | M3      | 2017-09-22 13:00:00
5  | M3      | 2017-09-23 13:00:00
6  | M3      | 2017-09-24 13:00:00
7  | M3      | 2017-09-25 13:00:00

Table BlockUser:

BlockStartDate       | BlockEndDate
2017-09-21 13:00:00  | 2017-09-22 13:00:00
2017-09-24 13:00:00  | 2017-09-24 14:00:00

Now I want rows from Message excluding those matching the intervals defined in BlockUser. So for my example the result should be:

ID | Message | DateCreated
1  | M1      | 2017-09-20 13:00:00
2  | M2      | 2017-09-20 14:00:00
5  | M3      | 2017-09-23 13:00:00
7  | M3      | 2017-09-25 13:00:00

I tried using this query but it didn't work:

WITH CTE AS (
         SELECT BlockStartDate,BlockEndDate FROM BlockUser
         )
SELECT * FROM Message
WHERE DateCreated NOT BETWEEN
(SELECT BlockStartDate FROM CTE)
AND
(SELECT BlockEndDate  FROM CTE)

It gives me this error:

  Subquery returned more than 1 value. This is not permitted when the
  subquery follows =, !=, <, <= , >, >= or when the subquery is used as
  an expression.

This is a classic anti-join scenario. You want rows from one table excluding those matching another table.

In English, you could put the condition like this:

  Select every row from Message for which there is no matching row in BlockUser.

In SQL, one way of expressing the above is like this:

SELECT
  *
FROM
  dbo.Message AS m
WHERE
  NOT EXISTS
  (
    SELECT
      *
    FROM
      dbo.BlockUser AS bu
    WHERE
      m.DateCreated >= bu.BlockStartDate
      AND m.DateCreated < bu.BlockEndDate
  )
;


PostgreSQL function that returns a 'casted' complex type from query I tried to define a PostgreSQL function that returns a complex type from a query via:

CREATE TYPE last_contract AS (
    contract_id bigint,
    contract_date timestamp
);

and

CREATE OR REPLACE FUNCTION delivery_last_contract(delivery bigint) RETURNS last_contract AS $func$
DECLARE ret last_contract;
BEGIN

    SELECT DISTINCT ON (tdf.delivery_id) ROW(tcf.contract_id, tcf.date)::last_contract INTO ret
        FROM task_delivery_fetch tdf
        INNER JOIN task_contract_fetch tcf ON tcf.fetch_id = tdf.fetch_id
        WHERE tdf.delivery_id = delivery
        ORDER BY tdf.delivery_id, tcf.contract_id DESC
        LIMIT 1;

    RETURN ret;

END;
$func$ LANGUAGE plpgsql;

sadly, all I get is an error:

SELECT delivery_last_contract(12197);
ERROR:  invalid input syntax for integer: "(13605,"2016-12-06 00:00:00+01")"
CONTEXT:  PL/pgSQL function delivery_last_contract(bigint) line 5 at SQL statement

Isn't there a way to actually return a single complex type returned from a query?

CREATE OR REPLACE FUNCTION delivery_last_contract(delivery bigint)
                   RETURNS last_contract AS $func$
  DECLARE ret last_contract;
  BEGIN
    SELECT tcf.contract_id, tcf.date
      INTO ret
      FROM task_delivery_fetch tdf
      INNER JOIN task_contract_fetch tcf ON tcf.fetch_id = tdf.fetch_id
     WHERE tdf.delivery_id = delivery
     ORDER BY tdf.delivery_id, tcf.contract_id DESC
     LIMIT 1;
    RETURN ret;
  END;
$func$ LANGUAGE plpgsql;

works. Simpler is better.


How to deny writes in all SQL Server databases (including new ones & restored ones) I want to create a login that is denied the ability to write to any databases - not just databases that exist today, but any newly created databases or databases that are restored from other servers.
I can't use database-only roles (not even in model), because those won't take effect on newly restored databases.
(Business purpose: I'm writing a blog post on how folks can set up a new login for themselves that has low privilege, and they can make sure that they don't accidentally have an "oops" moment when they copy/paste code or run it without a where clause.)

If you could live with a finite time (minimum 10-seconds) between executions to catch new/restored databases, you could create a scheduled SQL Server Agent Job and put something like this in a T-SQL Job Step:-
SET NOCOUNT ON

DECLARE @command nvarchar(max) ;

SET @command =
N'SET NOCOUNT ON

DECLARE @UserName sysname ;

SELECT @UserName = USRS.[name]
FROM sys.database_principals AS USRS
INNER JOIN sys.server_principals AS LGNS ON LGNS.[sid] = USRS.[sid]
WHERE LGNS.[name] = ''LoginName'' ;

IF IS_ROLEMEMBER(''db_denydatawriter'', @UserName) = 0
   BEGIN
      EXEC sp_addrolemember ''db_denydatawriter'', @UserName ;
   END ;' ;

EXEC sp_ineachdb
   @command      = @command,
   @state_desc   = N'ONLINE',
   @is_read_only = 0 ;


MySQL Cluster SQL Node not synchronizing I am new to MySQL Cluster and am trying to setup a new cluster for our new application.  Here is what I have set up on 5 CentOS 64 bit VM and got the cluster  to work using MySQL Cluster 7.2.  I am trying to test it and have some issues.

“I have successfully installed the Cluster with 5 nodes (2 Data, 1 Mgmt and 2 SQL Nodes). While testing the cluster I have hit on one scenario where I am stuck and cannot make it to work. Here is the screen shot of the Management Node displaying all cluster nodes:

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2  @10.0.3.138  (mysql-5.5.30 ndb-7.2.12, Nodegroup: 0)
id=3  @10.0.3.83  (mysql-5.5.30 ndb-7.2.12, Nodegroup: 0, Master)

[ndb_mgmd(MGM)] 1 node(s)
id=1  @10.0.3.135  (mysql-5.5.30 ndb-7.2.12)

[mysqld(API)]   2 node(s)
id=4  @10.0.3.87  (mysql-5.5.30 ndb-7.2.12)
id=5  @10.0.3.22  (mysql-5.5.30 ndb-7.2.12)

ndb_mgm>

Here is the scenario:

While all nodes in the cluster are working as a part of my test I shut down one of the SQL Node 4 and while this node is offline, I drop a database which is part of the cluster databases on SQL Node 5. When I bring up the offline SQL Node 4 and rejoin to the cluster the dropped database still shows up. It should sync with the old cluster databases meaning the dropped database when the SQL Node 4 was offline should be removed from the cluster and should not show up on SQL Node 4. This is a real scenario that can happen.

Also, I am searching for MySQL Cluster Test document which describes these scenarios and cannot seem to find it. “

Any Help will be greatly appreciated.

Thanks

There can be issues regarding the syncing of the FRMs stored centrally in the data nodes and the local copies on each MySQL Server. The workaround is to stop the mysqld; delete the FRM files for Cluster tables (and DB in this case) from the mysqld's datadir; start mysqld and then run SHOW TABLES.


How to update foreign keys in a large fact table using FORALL? I have 2 tables, one is a fact table (100000000 records), and the other is a catalog (2000), I need to update the FK at the fact table, can I use FORALL in this case?. I wrote this but it doesn't work.

CURSOR cur_matched IS
SELECT  PER_C_PERIOD_ORIG, PER_C_PERIOD
FROM   TBL_CATALOG
order by PER_C_PERIOD;

registro cur_matched%ROWTYPE;

VAR_PER_C_PERIOD_ORIG VARCHAR2(150);
VAR_PER_C_PERIOD  NUMBER;
vEstatus varchar(3);

TYPE tab_matched IS TABLE OF TBL_BIG_TABLE.PER_C_PERIOD%TYPE;

rec_matched tab_matched := tab_matched();
rec_match_tot tab_matched := tab_matched();

indx  number;

BEGIN

         OPEN cur_matched;
         LOOP

                indx := 0;

             FETCH cur_matched INTO VAR_PER_C_PERIOD_ORIG,VAR_PER_C_PERIOD;
             EXIT WHEN cur_matched%NOTFOUND;

                 SELECT  PER_C_PERIOD_ORIG
                 BULK COLLECT INTO  rec_matched
                 FROM TBL_BIG_TABLE
                 WHERE PER_C_PERIOD_ORIG = VAR_PER_C_PERIOD_ORIG;

                 FORALL indx IN 1 .. rec_match_tot.COUNT
                 UPDATE TBL_BIG_TABLE
                 SET PER_C_PERIOD =  VAR_PER_C_PERIOD
                 WHERE PER_C_PERIOD_ORIG = rec_match_tot(indx);

                COMMIT;

        END LOOP;
        CLOSE cur_matched;

I think this will do the trick, unless I have missed something. It has the benefit of less and more maintainable code, and performance wise it will likely outperform PL/SQL versions.

MERGE INTO tbl_big_table big
USING (
  SELECT
    per_c_period_orig,
    per_c_period
  FROM tbl_catalog
  ORDER BY per_c_period
) small
ON (big.per_c_period_orig = small.per_c_period_orig)
WHEN MATCHED THEN
  UPDATE SET big.per_c_period = small.per_c_period;

COMMIT;


How to configure database mail in sql server 2012 when I configure the database mail it is showing the following error, what it means ?

You don't have the required permission to access the database mail settings. Ask your DBA to configure it for you. If you don't have a DBA then you will need to logon to the server with an account that has sysadmin server role.


Oracle asm disk string profile string vs param string Oracle Database ASM disk string profile string vs param string ?

ASMCMD [+] > dsget
profile: /dev/disk*
parameter: /dev/disk*

Do we need to set both of them ?
what is consequence of setting one in these two ?

Thanks

asm_diskstring can be set in the ASM parameterfile and in the GPnP profile as well. Grid Infrastructure needs to know where to search for ASM disks before starting ASM and reading the ASM parameterfile, the GPnP profile makes that possible.

dsget

dsget
        Retrieves the discovery diskstring value that is
        used by the Oracle ASM Instance and its clients

Synopsis
        dsget [ --normal | --profile [ -f ] | --parameter ]

Description
        The options for the dsget command are described below.

        --normal        - Retrieves the discovery string from the Grid Plug
                          and Play (GPnP) profile and the one that is set in
                          the Oracle ASM instance. It returns one row each for
                          the profile and parameter setting. This is the
                          default setting.

        --parameter     - Retrieves the ASM_DISKSTRING parameter setting of
                          the Oracle ASM instance.

        --profile       - Retrieves the discovery string from the GPnP
                          profile.

        -f              - If -f is specified, retrieves the discovery string
                          from the local GPnP profile.  This parameter
                          can only be specified if --profile is specified.

Examples
        The following example uses dsget to retrieve the current discovery
        diskstring value from the GPnP profile and the ASM_DISKSTRING parameter.

        ASMCMD [+] > dsget
        profile: /devices/disk*
        parameter: /devices/disk*


database design - table with multiple nullable foreign keys Context:

I have 3 unrelated entities (tables) Entity1, Entity2 and Entity3
I have a table called Operation, which can have multiple targets.
A target for an operation can be Entity1, Entity2 or Entity3.

To model this structure, I first modeled it like this (Approach 1):
Table OperationTarget

FK to Operation (Not Nullable)
FK to Entity 1 (Nullable)
FK to Entity 2 (Nullable)
FK to Entity 3 (Nullable)
A constraint that enforces only one of them will be set, and others will be Null

Then, I didn't like the idea of creating a table where at each row, 2 of 3 columns will always be Null, so I created a different approach (Approach 2):
Table OperationTarget

FK to Operation (Not Nullable)

Table OperationTargetEntity1

FK to OperationTarget(Not Nullable)
FK to Entity 1 (Not Nullable)

Table OperationTargetEntity2

FK to OperationTarget(Not Nullable)
FK to Entity 2 (Not Nullable)

Table OperationTargetEntity3

FK to OperationTarget(Not Nullable)
FK to Entity 3 (Not Nullable)

This approach feels better in terms of normalization, but in practice, this will make my codebase more complex, and also my queries more complex, as I will be needing joins. I need the parent table there for other application requirements.
I am currently going with approach 2. Is approach 1 something I should be avoiding at all costs, or when justified by simplicity and performance, it would be OK to go with it?
I've been reading on the topic for hours, some people don't like using Nullable columns at all, some say it's OK when application requires them (when it is possible you don't know the value for a column for a specific row, for instance where a user optionally can provide their birth date), but not sure if approach 1 is justifiable here, or should be avoided.

I can't see the entire data model or exactly the context of "Operation" so I can't tell you if there's a better solution, but in general the second approach is preferable, any complexities it creates can be handled by creating a view that creates a simple presentation of the data without requiring someone to even ponder the joins that occur behind the scenes.  Any insert/update/delete procedures only need to be programmed once, the additional complexity will be minimal.
Yes, more tables... BUT:

Those tables will be narrow and small.
You would probably build some sort of filtered index on those columns to facilitate searches, and they would look remarkably identical to the tables you've created.  But in creating them you give more meaning to them than just breaking rules that exist for a good reason in order to save a few lines of code.
If in the future OperationTarget not has more "targets", much easier to add another table instead of more columns to the base table.

I'd also look harder at this to see if this is a good application for exclusive subtypes, as I said I don't have a full picture of your data model so it would be something you'd have to evaluate on your own.


Is it safe to add new features to production SQL Server Instance I need to add Integration Services Catalogs to our production instance of SQL Server 2008 in order to automate some SSIS packages. It wasn't included in the original installation 5 or 6 years ago.

Is there any danger in adding new features to the production server?

We have backups in place, but I would prefer to find a different automation solution if there is a probability that the data could become corrupt.

I would follow the steps from this post if I were to install the additional feature-
https://stackoverflow.com/questions/14551255/how-do-i-enable-integration-services-ssis-in-sql-server-2008

****EDIT****

I realized that Integration Services Catalogs isn't available for SQL Server 2008. So the link I posted is irrelevant. I'll leave the question up in case anyone has valuable input regarding adding features to existing SQL Server instances.

The only danger I would say is installing new features that require the database engine service to get taken down when you don't expect it to be taken down.

Other than that, adding new services to an old instance isn't dangerous on its own. I have added reporting, analysis, and integration services post install plenty of times over the years without any unforeseen issues.


Knowing your workload type using dmv's? Is there a DMV through which I can tell that my workload type is 'ad hoc'?

This is the one I would use, courtesy of Kimberly Tripp:

SELECT objtype AS [CacheType]
, count_big(*) AS [Total Plans]
, sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
, avg(usecounts) AS [Avg Use Count]
, sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
, sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC

It splits out the plan size and total number of plans by Type e.g. Adhoc, Prepared, Procedure, Trigger, View etc.

EDIT:

If you want to then find out the actual pressure each type is placing on the server, you can join the sys.dm_exec_cached_plans to sys.dm_exec_query_stats through the plan_handle and order by total_worker_time (for CPU) or total_elapsed_time (for Duration).


Class not registered error in SSAS Data Source View Has anyone come across Class Not Registered issue while opening Data Source Views in SSAS using Visual Studio 2015? There are similar posts not haven't found anything exactly the same.

Error text:

  Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)) (msddsp)

You have either installed an older version of SSDT/Visual Studio side by side with your 2015 visual studio or you are trying to open a data source view created in a newer version of SSDT/Visual Studio.

Some binary data stream is written in the .dsv file and includes the version information. The solution is to install a recent enough version of Visual Studio or SSDT and possibly re-register DDSShapes.dll.

See this MSDN post for details which explains:

  If you try to use SQL Server 2012 version of the SQL Server Data Tool
  to open the DSV modified by the SQL Server 2014 version of the SQL
  Server Data Tool, you gets this COM exception. The reason is, this
  leading 4 characters of binary data is the version of ATL used to
  compile the DDSShapes.dll. When this DLL reads the binary data, it
  check if the version of the ATL that was used to save the data, making
  sure it is below the version recognized by the DDSShapes.dll. "000a"
  is a higher value than "0008", so the SQL Server 2012 version of the
  SQL Server Data Tool throws an exception.

  The solution is to install the SQL Server 2014 version of the SQL
  Server Data Tool from https://msdn.microsoft.com/en-us/data/hh297027 .
  You should get the new version of the DDSShapes.dll in C:\Program
  Files (x86)\Microsoft SQL Server\120\Tools\Bin.


Set of datetime versioned rows, how to build the most up to date row I created this SQL fiddle with the sample data

The sample data is this:

--
-- Table structure for table `dogs`
--

CREATE TABLE IF NOT EXISTS `dogs` (
  `version_stamp` datetime NOT NULL,
  `dog_id` int(11) unsigned NOT NULL,
  `dog_name` varchar(30) DEFAULT NULL,
  `dog_color` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`version_stamp`,`dog_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `dogs`
--

INSERT INTO `dogs` (`version_stamp`, `dog_id`, `dog_name`, `dog_color`) VALUES
('2014-09-28 09:30:24', 19849, 'Ruediger', 'black'),
('2014-09-25 02:15:23', 19850, NULL, 'white'),
('2014-09-25 02:16:41', 19850, 'Bello', NULL),
('2014-09-26 02:15:23', 19850, NULL, 'brown'),
('2014-09-27 02:16:41', 19850, 'Dieter', NULL);

What I want, is the newest version of a row, e.g. dog row.

Say I enter the dog_id 19850 with date time stamp

2014-09-26 02:15:24

Then I want to get :
Bello brown

When I use a different timestamp, say:

2099-12-12 12:12:12

I want to get

Dieter, brown

Is there a way to do this in SQL?

Something like this should work if you specify the dog_id and version_stamp. It's going to SELECT the top dog_name and dog_color ORDER BY the latest version_stamp. Then you roll those up based on dog_id using MAX().

Fiddle example.

SELECT
   a.dog_id
  ,MAX(a.version_stamp) AS 'Version Stamp'
  ,MAX(a.dog_name) AS 'Dog Name'
  ,MAX(a.dog_color) AS 'Dog Color'
FROM
   (
    SELECT
      *
    FROM dogs
    WHERE version_stamp <= '2099-12-12 12:12:12'
    AND dog_id = 19850
    AND dog_name IS NOT NULL
    UNION
    SELECT
      *
    FROM dogs
    WHERE version_stamp <= '2099-12-12 12:12:12'
    AND dog_id = 19850
    AND dog_name IS NULL
    ORDER BY version_stamp DESC
    LIMIT 2
   ) a
GROUP BY
   a.dog_id

DOG_ID  VERSION STAMP   DOG NAME    DOG COLOR

19850   September, 27 2014 02:16:41+0000    Dieter  brown


How to remove duplicates values within particular ID Im struggling with removing duplicate values within certain employee ID. Here is the situation:

ID ||  VALUE1   || VALUE2   || DATE

11 || Position1 || Other1   || 2019-04-04
11 || Position2 || Other3   || 2019-05-04
11 || Position1 || Other1   || 2019-06-04

12 || Position2 || Other3   || 2019-01-04
12 || Position1 || Other1   || 2019-03-04
12 || Position2 || Other3   || 2019-04-04
12 || Position3 || Other1   || 2019-05-04

13 || Position1 || Other3   || 2019-04-04
13 || Position3 || Other1   || 2019-05-04
13 || Position1 || Other3   || 2019-06-04

What I want to do is to remove repeating values for column VALUE1 but only within certain ID and the value which stays is the newest. So the results can look like:

ID ||  VALUE1   || VALUE2   || DATE

11 || Position2 || Other3   || 2019-05-04
11 || Position1 || Other1   || 2019-06-04

12 || Position1 || Other1   || 2019-03-04
12 || Position2 || Other3   || 2019-04-04
12 || Position3 || Other1   || 2019-05-04

13 || Position3 || Other1   || 2019-05-04
13 || Position1 || Other3   || 2019-06-04

I dont know how to achieve that using partition by or distinct. Any advices?


How to migrate large blob table from mysql to postgresql? I'm now in process of migrating my MySQL database to PostgreSQL. Almost everything went fine (well, after lots of googling for correct mysqldump params etc.) except one table I have - actually the most important table in my app.

Table structure is very simple:

mysql> show create table samples;
.. skipped ...
CREATE TABLE `samples` (
      `File_ID` int(11) NOT NULL,
      `File` longblob,
      PRIMARY KEY (`File_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=binary

but it is very large (> 20 Gb).

I've tried to use --hex-blob parameter of mysqldump - but data in this format is not accepted by PostgreSQL when I tried to use resulting dumpfile as a command file. Another option I've tried is using --tab option to just get a dump and then insert it to PostgreSQL with COPY command - but --hex-blob is not working with --tab and PostgreSQL still does not accept the dumpfile saying there are invalid characters in it.

I'd be very glad to get any advice on this matter - although I'm starting to think that writing a custom migration tool is not a bad idea after all...

I think that simplest way is to use that --hex-blob switch on mysqldump and restore by psql, with decode(string text, type text). However it's not that simple, because you need to change a little produced dump (sed, awk), adding that decode function. For example:

mysqldump -u root -p --skip-quote-names --hex-blob --skip-triggers \
    --compact --no-create-info mysql samples > prepg.dump

sed "s/0x\([0-9,A-F]*\))/decode('\1','hex'))/g" prepg.dump > pg.dump

psql session:

CREATE TABLE samples
(
    file_id integer PRIMARY KEY,
    file bytea
);

\i 'path/to/pg.dump'


Clear old log entries from API while saving N of each type by user I have a log table that quickly gets over 20 GB and was hoping to develop a cron job that clears out the old entries while keeping the last 100 of each type for each user.

 id     |   user_id   |      api_route
1506          304           api/trayapp
1504          379           api/trayapp
1502          321           api/trayapp
1496          377           api/servers
1494          382           api/trayapp
1485          289           api/trayapp
1483          235           api/trayapp
1481          318           api/trayapp
1480          296           api/trayapp
1477          117           api/trayapp
1474          311           api/trayapp
1470          312           api/servers
1467          298           api/servers
1465          377           api/trayapp
1464          303           api/servers
1463          313           api/servers
1462          102           api/servers
1461          127           api/servers
1459          383           api/servers
1458          312           api/trayapp
1457          378           api/servers
1454          308           api/servers
1453          197           api/servers
1452          298           api/trayapp
1448          100           api/servers
1447          317           api/trayapp
1435          320           api/trayapp
1434          103           api/servers
1432          211           api/servers
1431          303           api/trayapp
1429          313           api/trayapp
1428          102           api/trayapp
1425          308           api/trayapp
1423          127           api/trayapp
1422          378           api/trayapp
1416          383           api/trayapp
1414          295           api/servers
1407          380           api/servers
1402          197           api/trayapp
1399          379           api/servers
1397          100           api/trayapp
1393          321           api/servers
1388          304           api/servers
1386          319           api/servers
1384          103           api/trayapp
1379          211           api/trayapp
1378          229           api/servers
1376          289           api/servers
1375          381           api/trayapp
1373          304           api/trayapp
1372          296           api/servers
1371          380           api/trayapp
1370          295           api/trayapp
1368          382           api/servers
1367          235           api/servers
1366          117           api/servers

I know I need to do a DELETE WHERE NOT IN (SELECT... but my first thought of how to do the select would be to use GROUP_CONCAT on the id, but then how would you use the results in the WHERE NOT IN? What would be your preferred method of removing all but the most recent? I do have created_at fields available but since the id is auto incremented it seems irrelevant.

Not going to be that simple.

Instead, write a script that walks through the table using the index on user_id for keeping track of where it is.

For each user, find the 100th from last, then DELETE any older ones.

This script will take minutes or hours, but who cares.  The script could even start over when it finishes -- keep going forever.

It will be desirable to have

INDEX(user_id, dt)

(I am assuming dt is the column giving the order within the user.)

More details...

The loop starts with finding the next $user_id:

SELECT user_id FROM tbl  WHERE user_id > $last_user_id
    ORDER BY user_id  LIMIT 1;

To find the 100th newest:

SELECT dt FROM tbl  WHERE user_id = $user_id
    ORDER BY user_id DESC  LIMIT 100,1

If that comes back empty, there is nothing to delete for this $user_id.  Else, use the return value ($dt) and do:

DELETE FROM tbl  WHERE user_id = $user_id  AND dt < $dt;

It might work to combine the SELECT and DELETE thus:

DELETE FROM tbl  WHERE user_id = $user_id
    ORDER BY user_id DESC  LIMIT 100, 99999

That says skip 100, then delete the next 99990 rows (working backward).

I have more discussion here .

(I have successfully implemented a similar script in a 200GB table.)


How does Pymongo connection work? I created a small speed test

from misc.Database import Database
import time

db = Database.getDb()

def main():
    test_db = db.test_db.find({})
    return "done"

if __name__ == '__main__':
    start = time.time()
    for i in range(10000):
        main()
    end = time.time()
    print(end - start)

where db is my pymongo client. When monitoring the mongod log, I realized it opened 2 connections when I run the test. When I run Robo3T, it opened 25 connections to the Mongod. Why is the connection not opened per request? How many connections will open each time you query the database?

First, you are using an old method to connect. The newer and better method is using MongoClient, which is supported in all recently released supported drivers. It's explained in detail here: https://mongodb.github.io/node-mongodb-native/driver-articles/mongoclient.html. Note that although the link discusses the node driver implementation of it, it is also relevant for pymongo.

Second, you're not iterating on the returned cursor. This means that the find() query was not executed on the server. I presume you see a really fast result in this test, which will not be the case if you're actually getting data from the server.

Third, Python is single-threaded by its nature. Pymongo is smart enough to realize you're not iterating the cursor, so it reuses the connection instead of creating a new one. Creating a new connection is very expensive, so drivers will not do it unless it's really necessary. The two connections you're seeing is likely one for monitoring server status, and the other for executing your query. The cost of opening a new connection leads to drivers using a connection pool instead.

Fourth, Robo3T is a GUI, so it naturally needs to open more connections since presumably it requires a lot of information from the server, perhaps asynchronously. This is a very different situation vs. a driver. You can't really compare the two.

Finally, performance testing is a tricky subject and must be done in a very controlled manner. Some things that needs planning:

How is the server provisioned, how big are the documents, how compressible are they, etc.
Can you determine whether any performance bottleneck is due to the server, the network, the driver, the query, or how the language is used. For example, if you try to iterate on the cursor using list(db.test_db.find()), how can you tell if the database is slow, or is it Python's list() method that is slow?
Running the testing code in the same machine as the database server might introduce resource contention, artificially skewing the result.