MySQL 5.7 foreign keys constraint invalid I have a database with around 150 tables, and many relations between them.

Now sometimes the foreign key relation of some tables becomes invalid. So while the foreign keys and indexes are set, and also set to cascade on update/delete; I get invalid indexes.

As example Table A has as row with a value in the foreign key relation 1. So Table B should have 1 as its primary key. Yet it's another value, usually 0 (not null).

Now I am not sure when this does happen, that is why I am asking here. I thought setting a foreign key, enforces the constraint to be valid. But apparently there must be some exception to it.

Things that I suppose could be the cause (I hope it's one of those, if these problems can happen during "normal" usage .. I will be scared):

rebuild / optimize table
export & import of the database through HeidiSQL or phpMyAdmin

The biggest problem with this is, that the database looks like it's running fine, as most queries work, only a few inserts/updates fail. So usually it gets by unnoticed, till some bug arises.

The actual questions:

Why does this happen?
How can I prevent this from happening?
If it can't be prevented, what are the "best practice" workarounds?

shop_award_product.shop_product_id is referenced with shop_product.id.

And well, shop_award_product.shop_product_id had 0 while shop_product.id was different 1 or 3.

CREATE TABLE `shop_award_product` (
    `shop_award_id` INT(11) UNSIGNED NOT NULL,
    `shop_product_id` INT(11) UNSIGNED NOT NULL,
    `sort_order` INT(11) UNSIGNED NOT NULL DEFAULT '1000',
    `created_at` DATETIME NOT NULL,
    `created_by` INT(11) UNSIGNED NOT NULL,
    `updated_at` DATETIME NOT NULL,
    `updated_by` INT(11) UNSIGNED NOT NULL,
    PRIMARY KEY (`shop_award_id`, `shop_product_id`),
    INDEX `shop_product_id` (`shop_product_id`),
    CONSTRAINT `fk_shop_awardproduct$shop_product` FOREIGN KEY (`shop_product_id`) REFERENCES `shop_product` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

CREATE TABLE `shop_award` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `website_id` INT(11) UNSIGNED NOT NULL,
    `created_at` DATETIME NOT NULL,
    `created_by` INT(11) UNSIGNED NOT NULL,
    `updated_at` DATETIME NOT NULL,
    `updated_by` INT(11) UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=4
;

Additional information:
MySQL version: 5.7.15-0ubuntu0.16.04.1

According to a comment, the problem has to be with import and disabling the check for foreign keys. But I am not sure yet that is the case, as I have always exported a db, and imported it again without actually modifying the SQL code. And the source DB is apparently ok, while the new DB has the problem (which I discover weeks later).

I am glad I got no response like "this stuff just happens from time to time".
But still have to figure out why it happens, I suppose it has to be when an export & import is being done, but I never edit the export. Still it's the only moment when FOREIGN_KEY_CHECKS is disabled.

No change of collation or character set during the import. I am not the only person able to edit the tables, but the chance that someone else changed that is very small. It has happened to me like 4 times in the past 6 months, and I have imported/exported about 100 db's in that time. This time, the tables involved and the project involved it is IMHO below 1% chance that someone else caused it.

I have solution to some of your problems

Q) Why does this happen?

A) because invalid or missing values in data-change statements such as INSERT or UPDATE, ...etc

Note disabling FOREIGN_KEY_CHECKS one of the main causes of this violation.

Q) Preventing or workaround
 There is some restriction   you can add to your database, I'm not sure if this practical (DBA should be able to decide)

SET sql_mode = 'STRICT_TRANS_TABLES';
SET sql_mode = 'STRICT_ALL_TABLES';

you can also use the following script https://github.com/michaelirey/mysql-foreign-key-checker to check foreign key problems and fix them.

Regarding rebuild / optimize table this may cause problem, it happened to me once on SQL server. not sure about MySQL.

my bad since the answer is not organized enough, I will try to re-organize it soon.


If I were you, I would use a classic normalised database structure rather than using JSON. I think that this is what you are driving at with your Option 3 (By using row constructors)?

I constructed a normalised version of your data. A sample is shown below - the full data is available at the sqlfiddle here.

CREATE TABLE country_location
(
  cl_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  country INTEGER NOT NULL,  -- you might wish to use TINYINT for some fields
  location INTEGER NOT NULL,
  cl_code CHAR (5) NOT NULL,
  cl_type INTEGER NOT NULL,
  cl_status INTEGER NOT NULL,
  CONSTRAINT country_location_code_uq UNIQUE (country, location, cl_code)
);

-- Experiment with these indexes on your own data!
-- but the plans seem good - better than with no index.

CREATE INDEX cl_ix ON country_location (country, location);
-- CREATE INDEX country_ix ON country_location (country);
-- CREATE INDEX location_ix ON country_location (location);

And the data:

INSERT INTO country_location
(country, location, cl_code, cl_type, cl_status)
VALUES (3, 6, '0Dxsh', 1, 1);
INSERT INTO country_location
(country, location, cl_code, cl_type, cl_status)
VALUES (3, 16, '0Dxsh', 1, 1);
INSERT INTO country_location
(country, location, cl_code, cl_type, cl_status)
VALUES (3, 4, '0Dxsh', 1, 1);
INSERT INTO country_location
(country, location, cl_code, cl_type, cl_status)
VALUES (3, 8, '0Dxsh', 1, 1);

A sample SELECT of the data from this table:

cl_id   country location    cl_code cl_type cl_status
    1         3        6      0Dxsh       1         1
    2         3       16      0Dxsh       1         1
    3         3        4      0Dxsh       1         1
    4         3        8      0Dxsh       1         1

There's not enough data to be really sure, but with the indexes, the PLANs seem to be optimal and you would get excellent results on single country-location record searches.

I don't know enough about your data to do the joins - I've created the tables in the fiddle and I'm sure that with the proper FOREIGN KEYs, your system would be very performant.


BCP export more rows per batch to file (queryout) I am using Windows 10 and running this straight from the command prompt (Administrator). Is there a way to change the row count being output to a text file?

BCP "SELECT * FROM db.dbo.table WHERE row_date = '2016-10-11'" queryout "C:/Users/table.csv" -t, -c -S "servername" -T

1000 rows successfully bulk-copied to host-file. Total received: 1000
1000 rows successfully bulk-copied to host-file. Total received: 2000
1000 rows successfully bulk-copied to host-file. Total received: 3000
1000 rows successfully bulk-copied to host-file. Total received: 4000
1000 rows successfully bulk-copied to host-file. Total received: 5000
1000 rows successfully bulk-copied to host-file. Total received: 6000

12136 rows copied.
Network packet size (bytes): 4096

Then I tried -b 5000 and -b10000:

BCP "SELECT * FROM db.dbo.table WHERE row_date = '2016-10-11'" queryout "C:/Users/table.csv" -t, -c -b 5000 -S "servername" -T

But it still outputs 1000 rows. I tried to increase the packet size with -a but that didn't impact it at all.

-b batch_size Specifies the number of rows per batch of imported data. [BOL]

The parameter changes import batch size only. I'm not aware of anyway to change the export row count reporting.


Syntax on Changing return String by Concatenation? I successfully created the scalar function FN_TotalBilled in SQL Server, which gives me the total amount invoiced to a company using tables Invoices(InvoiceID, InvoiceTotal,VendorID,..) and Vendors(VendorName, VendorID,.....), and I can get the function to return an integer value, BUT I cannot figure out how to get the function to return a value of the type $Integer. Here is what I have:

CREATE FUNCTION Fn_TotalBilled (@VendorName VarChar (50) )
RETURNS Int
BEGIN  RETURN (SELECT SUM(InvoiceTotal) FROM Invoices I join Vendors V
on I.VendorID=V.VendorID where VendorName = @VendorName ) ; END ;

Now, I can get a return value using:

SELECT dbo.Fn_TotalBilled('CompanyName') ,

Which spits out an integer.

BUT I want as a return to get a string $integer. This is what I am trying,
which is not working:

 Select '$'+ dbo.Fn_TotalBilled('IBM')

I get the error message:
"Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '$' to data type int."

I then tried :

Select CAST('$' AS Varchar) + dbo.Fn_TotalBilled('IBM')

But that did not work out either. Can someone help me get a return string
$dbo.Fn_TotallBilled ?  I also tried to change the function by selecting SELECT '$' + ...

What else can I do?

It looks like you're expecting the integer to get implicitly converted to string. What actually happens is the other way around - the string gets implicitly converted to integer.
The simplest version of what you want is:
Select '$' + Cast(dbo.fn_TotalBill('IBM') As Varchar)

But:

You shouldn't use Varchar without a length, it's bad practice. In this case maybe Varchar(16) would do?

You're missing cents. Normal practice would be to use Decimal(16, 2) to store it or perhaps even smaller. Also as per the other answer some people prefer the Money data type because of additional decimal places for rounding purposes that might be useful if compounding interest was involved.

T-SQL isn't going to do proper regional formatting for you (though it turns out 2012 added a way in the other answer).

But above all this generally, and almost certainly, isn't/shouldn't be done in T-SQL. You should always pass raw numerical values to the caller and let them do regional currency conversion for you in the presentation layer. That's where it belongs.
The only exception I've seen is when I've included the figure in an internal error message, for example to give me a figure where the balance is wrong for debugging purposes and this isn't going to flow through to the caller.


Sync two SQL Server databases So I've got a hosting package from hostgator with an MS SQL database and a local SQL Express 2012 database running. I'm trying to investigate syncing the two together, two-way syncing, and I'm unable to use the SQL replication tools in the SQL management studio, can I use the replication tools? - or do I need to use another tool?

Thanks!

Sorry if this is vague, please let me know and I can clarify.

SQL Server replication, creates jobs which runs through SQL Server Agent, but because SQL Server Agent doesn't run with SQL Server Express Edition, Replication also can not run and the feature is not added in the edition and you can not implement replication. So if you need to sync both databases you need to upgrade your database to standard edition or developer edition, write lengthy query and schedule using PowerShell, or use some third party SW.

Thanks


Foreign Keys and Data Import I have:

a CSV file (5000+ records);
an empty item table;
am empty favourite table;
a number of other empty tables.

I would like to populate the item table with the contents of the CSV file. Then I would like to create some foreign key relationships between both tables.

In what order do I do this?

I keep receiving an error every time I attempt this:

#1452 - Cannot add or update a child row: a foreign key constraint fails...

Note that my empty favourite table already has some foreign key constraints set up. I am using PhpMyAdmin and MySQL Workbench.

Am I supposed to have all the tables populated with data before I set up foreign key relationships? Or do I set up the relationships then populate with data?

You have several choices. You can either create the tables without the constraints & add them afterwards, or create the tables with the foreign keys & them import the data with foreign key checks disabled - simply run SET FOREIGN_KEY_CHECKS=0; in your mysql session to temporarily disable them.

For example:

mysql> insert into favourite values( 1,1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`tmp`.`favourite`, CONSTRAINT `favourite_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `item` (`item_id`))
mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into favourite values( 1,1);
Query OK, 1 row affected (0.00 sec)


Is encrypted database possible? I'm not a DBA but I've been asked to see if we can encrypt the data in sql server such that the admin, despite having access to SQL Server won't be able to see the data but application would still be able to query on it and read the data.

My limited knowledge of encryption tells me that if application encrypts the data then db won't be able to query it. If db encrypts the data then DBA can definitely read it.

So is there a way to prevent DBA from peeking into data but still be able to manage database?

You will need to encrypt the data within the application and store the encrypted values within the database.  Things like TDE and storage level encryption will not meet your requirements.


Changing the column width in Transactional Replication Changing the column width in SQL server Transaction Replication.
Can we do direct width change ? or we need to takeout the article and modify.
This change replicate to subscriber? or we need to do manually at subscriber

Changing the width of a column on a replicated table should be able to be done with a direct change without having to rebuild the replication.


sql server: updating fields on huge table in small chunks: how to get progress/status? We have a very large (100million row) table, and we need to update a couple of fields on it.

For log shipping, etc, we also, obviously, want to keep it to bite-size transactions.

Will the below do the trick?
And how can we get it to print some output, so we can see progress? (we tried adding a PRINT statement in there, but nothing was output during while loop)

The code is:

DECLARE @CHUNK_SIZE int
SET @CHUNK_SIZE = 10000

UPDATE TOP(@CHUNK_SIZE) [huge-table] set deleted = 0, deletedDate = '2000-01-01'
where deleted is null or deletedDate is null

WHILE @@ROWCOUNT > 0
BEGIN
    UPDATE TOP(@CHUNK_SIZE) [huge-table] set deleted = 0, deletedDate = '2000-01-01'
    where deleted is null or deletedDate is null
END

I was not aware of this question when I answered the related question ( Are explicit transactions needed in this while loop? ), but for the sake of completeness, I will address this issue here as it was not part of my suggestion in that linked answer.

Since I am suggesting to schedule this via a SQL Agent job (it is 100 million rows, after all), I don't think that any form of sending status messages to the client (i.e. SSMS) will be ideal (though if that is ever a need for other projects, then I agree with Vladimir that using RAISERROR('', 10, 1) WITH NOWAIT; is the way to go).

In this particular case, I would create a status table that can be updated per each loop with the number of rows updated thus far. And it doesn't hurt to throw in the current time to have a heart-beat on the process.

Given that you want to be able to cancel and restart the process, I am weary of wrapping the the UPDATE of the main table with the UPDATE of the status table in an explicit transaction. However, if you feel that the status table is ever out of sync due to cancelling, it is easy to refresh with the current value by simply updating it manually with the COUNT(*) FROM [huge-table] WHERE deleted IS NOT NULL AND deletedDate IS NOT NULL. and there are two tables to UPDATE (i.e. the main table and the status table), we should use an explicit transaction to keep those two tables in sync, yet we do not want to risk having an orphaned transaction if you cancel the process at a point after it has started the transaction but has not committed it. This should be safe to do as long as you don't stop the SQL Agent job.

How can you stop the process without, um, well, stopping it? By asking it to stop :-). Yep. By sending the process a "signal" (similar to kill -3 in Unix), you can request that it stop at the next convenient moment (i.e. when there is no active transaction!) and have it clean itself up all nice and tidy-like.

How can you communicate with the running process in another session? By using the same mechanism that we created for it to communicate its current status back to you: the status table. We just need to add a column that the process will check at the beginning of each loop so that it knows whether to proceed or abort. And since the intent is to schedule this as a SQL Agent job (run every 10 or 20 minutes), we should also check at the very beginning since there is no point in filling a temp table with 1 million rows if the process is just going to exit a moment later and not use any of that data.

DECLARE @BatchRows INT = 1000000,
        @UpdateRows INT = 4995;

IF (OBJECT_ID(N'dbo.HugeTable_TempStatus') IS NULL)
BEGIN
  CREATE TABLE dbo.HugeTable_TempStatus
  (
    RowsUpdated INT NOT NULL, -- updated by the process
    LastUpdatedOn DATETIME NOT NULL, -- updated by the process
    PauseProcess BIT NOT NULL -- read by the process
  );

  INSERT INTO dbo.HugeTable_TempStatus (RowsUpdated, LastUpdatedOn, PauseProcess)
  VALUES (0, GETDATE(), 0);
END;

-- First check to see if we should run. If no, don't waste time filling temp table
IF (EXISTS(SELECT * FROM dbo.HugeTable_TempStatus WHERE PauseProcess = 1))
BEGIN
  PRINT 'Process is paused. No need to start.';
  RETURN;
END;

CREATE TABLE #FullSet (KeyField1 DataType1, KeyField2 DataType2);
CREATE TABLE #CurrentSet (KeyField1 DataType1, KeyField2 DataType2);

INSERT INTO #FullSet (KeyField1, KeyField2)
  SELECT TOP (@BatchRows) ht.KeyField1, ht.KeyField2
  FROM   dbo.HugeTable ht
  WHERE  ht.deleted IS NULL
  OR     ht.deletedDate IS NULL

WHILE (1 = 1)
BEGIN
  -- Check if process is paused. If yes, just exit cleanly.
  IF (EXISTS(SELECT * FROM dbo.HugeTable_TempStatus WHERE PauseProcess = 1))
  BEGIN
    PRINT 'Process is paused. Exiting.';
    BREAK;
  END;

  -- grab a set of rows to update
  DELETE TOP (@UpdateRows)
  FROM   #FullSet
  OUTPUT Deleted.KeyField1, Deleted.KeyField2
  INTO   #CurrentSet (KeyField1, KeyField2);

  IF (@@ROWCOUNT = 0)
  BEGIN
    RAISERROR(N'All rows have been updated!!', 16, 1);
    BREAK;
  END;

  BEGIN TRY
    BEGIN TRAN;

    -- do the update of the main table
    UPDATE ht
    SET    ht.deleted = 0,
           ht.deletedDate = '2000-01-01'
    FROM   dbo.HugeTable ht
    INNER JOIN #CurrentSet cs
            ON cs.KeyField1 = ht.KeyField1
           AND cs.KeyField2 = ht.KeyField2;

    -- update the current status
    UPDATE ts
    SET    ts.RowsUpdated += @@ROWCOUNT,
           ts.LastUpdatedOn = GETDATE()
    FROM   dbo.HugeTable_TempStatus ts;

    COMMIT TRAN;
  END TRY
  BEGIN CATCH
    IF (@@TRANCOUNT > 0)
    BEGIN
      ROLLBACK TRAN;
    END;

    THROW; -- raise the error and terminate the process
  END CATCH;

  -- clear out rows to update for next iteration
  TRUNCATE TABLE #CurrentSet;

  WAITFOR DELAY '00:00:01'; -- 1 second delay for some breathing room
END;

-- clean up temp tables when testing
-- DROP TABLE #FullSet;
-- DROP TABLE #CurrentSet;

You can then check the status at any time using the following query:

SELECT sp.[rows] AS [TotalRowsInTable],
       ts.RowsUpdated,
       (sp.[rows] - ts.RowsUpdated) AS [RowsRemaining],
       ts.LastUpdatedOn
FROM sys.partitions sp
CROSS JOIN dbo.HugeTable_TempStatus ts
WHERE  sp.[object_id] = OBJECT_ID(N'ResizeTest')
AND    sp.[index_id] < 2;

Want to pause the process, whether it is running in a SQL Agent job or even in SSMS on someone else's computer? Just run:

UPDATE ht
SET    ht.PauseProcess = 1
FROM   dbo.HugeTable_TempStatus ts;

Want the process to be able to start back up again? Just run:

UPDATE ht
SET    ht.PauseProcess = 0
FROM   dbo.HugeTable_TempStatus ts;

UPDATE:

Here are some additional things to try that might improve the performance of this operation. None are guaranteed to help but are probably worth testing out. And with 100 million rows to update, you have plenty of time / opportunity to test some variations ;-).

Add TOP (@UpdateRows) to the UPDATE query so that the top line looks like:
UPDATE  TOP (@UpdateRows) ht
Sometimes it helps the optimizer to know how many rows max will be affected so it doesn't waste time looking for more.
Add a PRIMARY KEY to the #CurrentSet temporary table. The idea here is to help the optimizer with the JOIN to the 100 million row table.

And just to have it stated so as to not be ambiguous, there shouldn't be any reason to add a PK to the #FullSet temporary table as it is just a simple queue table where the order is irrelevant.
In some cases it helps to add a Filtered Index to assist the SELECT that feeds into the #FullSet temp table. Here are some considerations related to adding such an index:

The WHERE condition should match the WHERE condition of your query, hence WHERE deleted is null or deletedDate is null
At the beginning of the process, most rows will match your WHERE condition, so an index isn't that helpful. You might want to wait until somewhere around the 50% mark before adding this. Of course, how much it helps and when it is best to add the index vary due to several factors, so it is a bit of trial and error.
You might have to manually UPDATE STATS and/or REBUILD the index to keep it up to date since the base data is changing quite frequently
Be sure to keep in mind that the index, while helping the SELECT, will hurt the UPDATE since it is another object that must be updated during that operation, hence more I/O. This plays into both using a Filtered Index (which shrinks as you update rows since fewer rows match the filter), and waiting a little while to add the index (if it's not going to be super helpful in the beginning, then no reason to incur the additional I/O).


Trigger to create a variable Table to be sent to service broker I've setup a service broker for the first time and tested it... Looks good.
Everytime a DML operation happens on a specific table; Let's say the Price Table, a trigger calls a stored procedure passing it the inserted and deleted tables as XML parameters which will send the message (containing the 2 XMLs) to the queue.

The activation procedure then processes the queue, and inserts the changes done to the initial table into an audit table... Works Great.

The Problem occurs when the update on the Price table is a massive one.  In my case, I tested a Price change update on 105 000 Items.  The XML Inserted and Deleted Variables, contain 1.3 million lines each... Which causes major issues: TempDB grows infinitely and the processor goes up to 95% which is understandable... but never seems to complete the operation.

The code for the Trigger is Price_TAB_Audit_trig.
The trigger calls the SendMsgServiceBroker procedure to send the message to the Queue. See comments below for activation procedure.

I'm trying to figure out how I can break down the inserted and deleted variables (messages) into smaller XMLs.
My questions/Thoughts are:
Can I create table variables and pass that in the message instead of XML? If so, how to create variable tables in a trigger?
Should I create global unique temp tables and reference them in the message?

Any other suggestion, is welcome.
Thank you in advance
JG

You could send the xml data in predefined chunks by using something like the trigger in the following setup.

Create the testbed in tempdb:

USE tempdb;

IF EXISTS (SELECT 1 FROM sys.triggers t WHERE t.name = 'TriggerTest_Chunked')
DROP TRIGGER dbo.TriggerTest_Chunked;

IF EXISTS(SELECT 1 FROM sys.tables t WHERE t.name = 'TriggerTest')
DROP TABLE dbo.TriggerTest;

CREATE TABLE dbo.TriggerTest
(
    ID INT NOT NULL
);

IF EXISTS(SELECT 1 FROM sys.tables t WHERE t.name = 'TriggerTestTarget')
DROP TABLE dbo.TriggerTestTarget;

CREATE TABLE dbo.TriggerTestTarget
(
    OperationType INT NOT NULL
    , DEETS XML NOT NULL
);

The trigger:

CREATE TRIGGER dbo.TriggerTest_Chunked
ON dbo.TriggerTest
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @RowStart INT;
    DECLARE @RowEnd INT;
    DECLARE @RowCount INT;
    DECLARE @BatchSize INT;
    SET @BatchSize = 1000;

    SET @RowStart = 0;

    SELECT @RowCount = COUNT(1)
    FROM inserted;

    IF @RowCount > @BatchSize
        SET @RowEnd = @BatchSize
    ELSE
        SET @RowEnd = @RowCount;

    WHILE @RowEnd <= @RowCount AND (@RowStart < @RowEnd)
    BEGIN
        INSERT INTO dbo.TriggerTestTarget (OperationType, DEETS)
        SELECT 1, (
            SELECT ID
            FROM (
                    SELECT *
                        , rn = ROW_NUMBER() OVER (ORDER BY ID)
                    FROM inserted
                ) i_rn
            WHERE i_rn.rn > @RowStart
                AND i_rn.rn <= @RowEnd
            FOR XML PATH('')
        );

        SET @RowStart = @RowStart + @BatchSize;
        IF @RowEnd + @BatchSize > @RowCount
            SET @BatchSize = @RowCount - @RowEnd;
        SET @RowEnd = @RowEnd + @BatchSize;
    END

    SET @RowStart = 0;

    SELECT @RowCount = COUNT(1)
    FROM deleted;

    IF @RowCount > @BatchSize
        SET @RowEnd = @BatchSize
    ELSE
        SET @RowEnd = @RowCount;

    WHILE @RowEnd <= @RowCount AND (@RowStart < @RowEnd)
    BEGIN
        INSERT INTO dbo.TriggerTestTarget (OperationType, DEETS)
        SELECT 2, (
            SELECT ID
            FROM (
                    SELECT *
                        , rn = ROW_NUMBER() OVER (ORDER BY ID)
                    FROM deleted
                ) i_rn
            WHERE i_rn.rn > @RowStart
                AND i_rn.rn <= @RowEnd
            FOR XML PATH('')
        );

        SET @RowStart = @RowStart + @BatchSize;
        IF @RowEnd + @BatchSize > @RowCount
            SET @BatchSize = @RowCount - @RowEnd;
        SET @RowEnd = @RowEnd + @BatchSize;
    END
END
GO

Insert some test data to see what the trigger does:

INSERT INTO dbo.TriggerTest (ID)
SELECT ROW_NUMBER() OVER (ORDER BY o1.object_id, o2.object_id)
FROM sys.objects o1
    , sys.objects o2;

Show results:

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerTestTarget;

DELETE TOP(100)
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerTestTarget;


Oracle Alternative to EXISTS EXCEPT I wonder if there is an equally elegant solution for Oracle for the problem posted and answered here:

Comparing Columns that can contain NULLS - is there a more elegant way?

I can't get this solution (the one which was accepted) to work in Oracle environment.

This should be the Oracle equivalent to the accepted answer:

SELECT *
FROM a a1
  JOIN b b1
    ON a1.id = b1.id
   AND EXISTS(SELECT * FROM a a2 where a2.id = a1.id
              MINUS
              SELECT * FROM b b2 where b2.id = b1.id);

I have never seen a select a.* before (i.e. a select without a FROM clause using an alias instead of constant values) but I assume it maps to a co-related subquery

Note that onedaywhen's answer would work as well in Oracle (when replacing except with minus):

WITH a_minus_b AS (
  SELECT *
  FROM A
  minus
  SELECT *
  FROM B
)
SELECT *
FROM a_minus_b T
   JOIN B ON T.ID = B.ID;

SQLFiddle: http://sqlfiddle.com/#!4/3f369/1

Just for completeness: in standard SQL this could be written as:

SELECT *
FROM a
  JOIN b
    ON a.id = b.id
where (a.string, a.dt, a.b, a.num) is distinct from (b.string, b.dt, b.b, b.num);

which is e.g. supported by Postgres: http://sqlfiddle.com/#!15/b431b/1


MSSQL: High number of logical reads when inserting into fragmented heap I'm inserting a batch of rows - using INSERT INTO tblUploadBanking SELECT FROM ... - into a table, which is a heap and have 3 narrow NC indexes.

When I execute the query from SSMS with SET STATISTICS IO ON, I can see output like this:

Table 'tblUploadBanking'. Scan count 0, logical reads 709738, physical reads 493, read-ahead reads 429, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 3, logical reads 136234, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblTrans_Cash_HO'. Scan count 1, logical reads 96944, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblPaymentType'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblWorkstation_Group'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

When I sum the number of logical reads, I'm getting 942922 reads. But, when I look into the output from profiler, I can see for this query 1475067 logical reads, which is 532k more than the sum from statistics output. BTW, from Extended Events I'm getting same number like from profiler.

What are these hidden reads? The only thing I have found out so far is that there is a connection with heap and indexes fragmentation. When I rebuild the heap, the execution plan remains exactly the same - including cost distribution, rows estimations etc. The number of reads in statistics is about the same as well, but in the profiler the number of logical reads has dropped to 1007183, which is almost matching with the output from statistics. Also the CPU time has reduced by 25%.

With other queries I had even bigger discrepancies - for few sessions I saw in trace 6-10M reads, but according to statistics there were just about 900k of logical reads. These queries were also inserts into fragmented heap and rebuilding the heap "solved it" as well like in this case.

From my knowledge about the heaps, there shouldn't be any reason for slower inserts when the heap is fragmented. It could be because of fragmented NC indexes, but I would expect that logical reads needed for index insert are included in statistics for the given table.

PS: Please do not suggest me to turn table from heap to clustered table, it's a plan for the future which is not possible now.

When inserting into a table without specifying TABLOCK hint the operation is fully logged (every row insert is logged) and the number of logical reads reported by query statistics is roughly equal to the number of inserted rows; so it's the same in case of fragmented/not fragmented heap (1 insert = 1 destination table page access), but what is not counted here is PFS-pages access, and the Profiler does count it as logical reads.

When you insert into rebuilt heap, there is no need to try to insert in any existing page because they are all full, but when inserting to a fragmented heap there is free space in the heap and it should be checked for every row inserted (where it's possible to insert it).

IMHO, the fact that there is no more need to search in PFS pages is revealed at the first attempt to find a free space in the existing pages when all pages allocated for this heap are checked without success for a rebuilt heap, and the number of possible accesses the PFS pages grows as the fragmentation grows.
As soon as the searching for free space failes when trying to insert the Nth row, for the remaining rows it allocates new pages directly


Alter table on live production databases How do most "popular" (MySQL, Postgres...) database system handle altering tables on live production databases (like adding, deleting or changing the type of colums)?

I know the correct way is to backup everything schedule downtime and do then do the changes.

But... does any current database system support doing these things "on-line" without stopping anything? (maybe just delaying the queries that reference a column that is just being changed/deleted)

And what does it happen when I just do an ALTER TABLE... on a live running database?
Does everything stop when this happens?
Can data get corrupted?
etc.

Again, I'm mostly referring to Postgres or MySQL as these are what I encounter.

(And, yes, anytime I had to do this before I did it "the right way", backing things up, scheduling downtine etc. ...but I just want to know if it's possible to do this sort and things "quick and dirty" or if there is any DB system that actually has support for "quick, live and dirty" schema changes)

Someone just suggested Online Schema Change for MySQL from Facebook script (with a tutorial here and source here)... seems like a nice way to automate a the set of "hacky" ways to do it... has anyone ever used it in something resemblig production?

When you issue an ALTER TABLE in PostgreSQL it will take an ACCESS EXCLUSIVE lock that blocks everything including SELECT. However, this lock can be quite brief if the table doesn't require re-writing, no new UNIQUE, CHECK or FOREIGN KEY constraints need expensive full-table scans to verify, etc.

If in doubt, you can generally just try it! All DDL in PostgreSQL is transactional, so it's quite fine to cancel an ALTER TABLE if it takes too long and starts holding up other queries. The lock levels required by various commands are documented in the locking page.

Some normally-slow operations can be sped up to be safe to perform without downtime. For example, if you have table t and you want to change column customercode integer NOT NULL to text because the customer has decided all customer codes must now begin with an X, you could write:

ALTER TABLE t ALTER COLUMN customercode TYPE text USING ( 'X'||customercode::text );

... but that would lock the whole table for the re-write. So does adding a column with a DEFAULT. It can be done in a couple of steps to avoid the long lock, but applications must be able to cope with the temporary duplication:

ALTER TABLE t ADD COLUMN customercode_new text;
BEGIN;
LOCK TABLE t IN EXCLUSIVE MODE;
UPDATE t SET customercode_new = 'X'||customercode::text;
ALTER TABLE t DROP COLUMN customercode;
ALTER TABLE t RENAME COLUMN customercode_new TO customercode;
COMMIT;

This will only prevent writes to t during the process; the lock name EXCLUSIVE is somewhat deceptive in that it excludes everything except SELECT; the ACCESS EXCLUSIVE mode is the only one that excludes absolutely everyting. See lock modes. There's a risk that this operation could deadlock-rollback due to the lock upgrade required by the ALTER TABLE, but at worst you'll just have to do it again.

You can even avoid that lock and do the whole thing live by creating a trigger function on t that whenever an INSERT or UPDATE comes in, automatically populates customercode_new from customercode.

There are also built-in tools like CREATE INDEX CONCURRENTLY and ALTER TABLE ... ADD table_constraint_using_index that're designed to allow DBAs to reduce exclusive locking durations by doing work more slowly in a concurrency-friendly way.

The pg_reorg tool or its successor pg_repack can be used for some table restructuring operations as well.


SQL Server 2012 Page Compression - Determine compression ratio? We're using SQL Server 2012 and have page compression turned on for a number of our larger tables. Is there a way we can determine the compression ratio we're getting? We'd like to be able to determine the benefits of compression.

How about sp_estimate_data_compression_savings?

It allows you to estimate the gain from compression BEFORE you compress the table, but it's quite accurate.


Local user login access in sql server 2008 I have a created a local user called 'MynetDB' with SQL Server Authentication (password) in SQL server 2008. Server roles is public and user mapping to 2 databases as db_owner and public. Status has Permission to connect database engine: = Grant and Login: = Enabled.

When I try to connect to server database engine with SQL Server authentication it is giving error Login failed for user 'MynetDB'. (Microsoft SQL Server, Error: 18456)

I also tried from C# code giving same error. Please advise me how access the databases?

Edit:
I am getting error on Management studio also

C#

<remove name="MyNetDB" />
    <add name="MyNetDB" connectionString="Data Source={ServerName};Initial Catalog=MynetDB;User Id=MynetDB;Password=taD6eg4c;" providerName="System.Data.SqlClient" />

To figure out exactly what is happening, you should check the State that is reported in the message, either in the event log or SQL Server log. For example:

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

The state will usually help narrow down the exact cause. Here is a list of the states and their meanings:

Err  Message
 2   Invalid userid
 5   Invalid userid
 6   Attempt to use a Windows login name with SQL Authentication
 7   Login disabled or password mismatch
 8   Password mismatch
 9   Invalid password
11   Valid login but server access failure
12   Valid login but server access failure
13   SQL Server service paused
18   Change password required


Crow's notation: Identifying relationships without the FK in the PK I'm trying to wrap my head around the identifying/non-identifying relationships in Crow's Foot notation. I have read in most places that an identifying relationship means that the FK is a part of the entity's PK. However, wouldn't that be the same as simply having the FK be non-null and not include it in the primary key?

For example:

To me this is an identifying relationship, since a Student cannot exist without a University. Though, I do not have plans on including the FK from Student to University in the Student's PK.

According to this post, as pointed out by mustaccio, while the formal way of showing an identifying relationship is by including the FK in the PK in the child table, this is not always done since it might not be practical to have big PKs that include the foreign key(s).

The logical meaning is simply that the child cannot exist without the parent, and as long as it is true, the relationship is identifying.

Therefore, in the example I stated, if we assume that all the students in our database have to be enrolled in a university, the relationship between a Student and a University is classified as identifying, since a Student cannot exist without a University.


Dynamic query with cursors I need to execute a dynamic query.
I need to extract some values of a table. With these a values I can construct the table names where I must do another query and check every value that it gives me with a if clause.

I'm trying to make with cursor, but it seems don't let me put a variable in the second cursor.

What is the best method to do this. Could you help me please?

DECLARE
    CURSOR c_outer IS SELECT ACTIVE FROM COMM;
    CURSOR c_inner(p_name VARCHAR2) IS SELECT (sysdate-to_date(START_TIME,'YYYYMMDDHH24MISS'))*24 HOURS FROM p_name WHERE  UPPER(STATUS) = 'EXECUTING';
    HOURS NUMBER;
    TAB VARCHAR2(30);

BEGIN

    FOR r IN c_outer LOOP
       TAB :='A'||c_outer||'JOB';

        OPEN  c_inner(TAB);

        FETCH c_inner INTO HOURS;
                IF HOURS > 24 THEN
                        DBMS_OUTPUT.PUT_LINE('MAXIMMO');
                END IF;

        CLOSE c_inner;

    END LOOP;

END;

You can declare a reference cursor type and a cursor variable based on that type.
Then you define a select statement that the ref cursor will represent using a bind variable in the place of the dynamic table name you get from the outer cursor.
Open the ref cursor for the sql and populate the bind variable as you go with the 'USING' clause.
I've put the code below together but I haven't run or checked it in anyway so I suggest taking it as a guide to what you want rather than the line for line solution.
On top of that I can't really tell what exactly you are trying to achieve.

DECLARE
    CURSOR c_outer IS
       SELECT 'A'||c_outer.active||'JOB' AS active
         FROM comm;

    TYPE innerCurTyp IS REF CURSOR;
    v_innerCur innerCurTyp
    v_sql      VARCHAR2(1000);
    n_hours     NUMBER;

BEGIN
    FOR r_outer IN c_outer LOOP
       v_sql := q'[SELECT (sysdate-to_date(START_TIME,'YYYYMMDDHH24MISS'))*24 HOURS FROM ]'|| r_outer.active || q'[  WHERE  UPPER(STATUS) = 'EXECUTING']';
       OPEN  v_innerCur FOR v_sql
       LOOP
          FETCH v_innerCur INTO n_hours;
                IF n_hours > 24 THEN
                    DBMS_OUTPUT.PUT_LINE('MAXIMMO');
                END IF;
          EXIT WHEN v_innerCur%NOTFOUND;
       END LOOP;
       CLOSE v_innerCur;

    END LOOP;

END;


Should I just rely on Foreign Key Constraints for error handling? I have been using the following pattern to validate FOREIGN KEY (FK) parameters for stored procedures which execute INSERTs:
CREATE PROC spCreateChildFoo
    @ForeignKey_Id int,
    @Attribute varchar(50)
AS

IF NOT EXISTS (SELECT ForeignKey_Id FROM ForeignTable WHERE ForeignKey_Id = @ForeignKey_Id)
    BEGIN
        RAISERROR ('The input ForeignKey_Id does not exist', 16, 1)
        RETURN;
    END

INSERT INTO ChildTable (ForeignKey_Id, Attribute)
VALUES (@ForeignKey_Id, @Attribute)

I did this because I wanted to catch bad FK entries early in the execution of the stored procedure before any data is INSERTed, especially for procs that carry out multiple INSERTs. I also thought it would be a good way to return accurate error messages to ease debugging. This pattern works well at serving those two goals.
Questions

Is this a waste of time?

More specifically, should I just rely on FK constraints to validate the FK parameters and rollback if it fails?

Is it ultimately going to hurt the performance of my application as the database grows?

Yes, this is a waste of time at least when your stored procedure is just the INSERT statement that may cause the foreign key violation. Where a foreign key is in place, validation of any attempted FK value is made against the reference table by the database engine itself.

If the value does not exist in the reference table, you will both receive an exception and have your INSERT statement aborted.

So, this would be perfectly enough:

CREATE PROC spCreateChildFoo
    @ForeignKey_Id int,
    @Attribute varchar(50)
AS

INSERT INTO ChildTable (ForeignKey_Id, Attribute)
VALUES (@ForeignKey_Id, @Attribute);

On the other hand, if your procedure is more than a single INSERT statement and you would like to abort the entire procedure on an invalid insert at its beginning, then preliminarily checking the FK value may not be entirely pointless. However, valid FK values would still be causing double lookups, and that is neither good nor necessary. To achieve the same effect of aborting the entire procedure, you could just put your procedure's body into a TRY/CATCH block:

CREATE PROC spCreateChildFoo
    @ForeignKey_Id int,
    @Attribute varchar(50)
AS

BEGIN TRY
    INSERT INTO ChildTable (ForeignKey_Id, Attribute)
    VALUES (@ForeignKey_Id, @Attribute);

    ... -- other statements
END TRY
BEGIN CATCH
    RAISERROR ('The input ForeignKey_Id does not exist', 16, 1);
    -- or you could catch ERROR_NUMBER(), ERROR_MESSAGE() etc.
    -- and re-raise the original exception
END CATCH;

More info:

TRY...CATCH (Transact-SQL)
ERROR_LINE (Transact-SQL)
ERROR_NUMBER (Transact-SQL)
ERROR_MESSAGE (Transact-SQL)
ERROR_PROCEDURE (Transact-SQL)
ERROR_SEVERITY (Transact-SQL)
ERROR_STATE (Transact-SQL)


Does anyone knows how the filestream folder structure works? I am trying to find the database/table that these files correspond to. In the filestream data store they are always stored under two GUID.

Do they correspond to object id / column id? how to i translate back to know what tables and column they correspond to?

├───23617e6f-8c5d-4dba-9c99-6088a05a3e61
│   └───b644ba19-a592-4e1a-9d6f-e3914875a587
│           00000024-00000678-0009
│           00000024-00000688-0006
│
├───423da63b-0de5-4095-900f-2c5bfdbaf190
│   └───ccb1c3e9-8de5-4773-80fa-d9aa4a383b27
│
└───6e99731c-fc79-4859-a13e-ec2706e7ef60
    └───dd9465af-a05c-4636-ab73-c95be4a84254
            00000024-00000590-0009

I found it.

SELECT
    [o].[name] AS [Table],
    [cp].[name] AS [Column],
    [p].[partition_number] AS [Partition],
    [r].[rsguid] AS [Rowset GUID],
    [rs].[colguid] AS [Column GUID]
FROM sys.sysrowsets [r] CROSS APPLY sys.sysrscols [rs]
JOIN sys.partitions [p]
    ON [rs].[rsid] = [p].[partition_id]
JOIN sys.objects [o]
    ON [o].[object_id] = [p].[object_id]
JOIN sys.syscolpars [cp]
    ON [cp].[colid] = [rs].[rscolid]
WHERE [rs].[colguid] IS NOT NULL
    AND [o].[object_id] = [cp].[id]
    AND [r].[rsguid] IS NOT NULL
    AND [r].[rowsetid] = [rs].[rsid];
GO


MySQL - include a default value in LOAD XML INFILE statement I have the follwing table:

CREATE TABLE `tab1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `DEFAULT` VARCHAR(25) DEFAULT NULL,
  `ITEM` varchar(25) DEFAULT NULL,
  `TIMESTAMP` TIMESTAMP DEFAULT NULL,
  `NAME` varchar(25) DEFAULT NULL,
  `SURNAME` varchar(45) DEFAULT NULL,
  `LOADED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
);

I then have an XML file containing multiple entries such as:

<RECORD
  ITEM="item1"
  TIMESTAMP="2015-08-26 10:04:40"
  NAME="Bob"
  SURNAME="SMITH"
/>

I use MySQL to import the data with:

LOAD XML LOCAL INFILE 'c:/myFile.xml'
INTO TABLE `test`.`tab1`
ROWS IDENTIFIED BY '<RECORD>';

This works OK. However I need to specify a Default value for the DEFAULT field. But it needs to be specified at the time I load the file, and will be different for each file.
(for the sake of argument let's use the filename, myfile.xml)

How can I add a default value for this row during the XML Import?

I looked at using something like:

LOAD XML LOCAL INFILE 'c:/myFile.xml'
INTO TABLE `test`.`tab1` (@default="myfile.xml")
ROWS IDENTIFIED BY '<RECORD>';
SET DEFAULT=@default;

But it didn't like that. Is there a way round this?

When one needs to do something more complex that is available in the syntax, it is often useful to 'stage' the data through a temp table:

Perhaps:

CREATE TEMPORARY TABLE tmp ( ... DEFAULT 'myfile' ... );
LOAD XML ...;
INSERT INTO tab1 SELECT * FROM tmp;

or:

CREATE TEMPORARY TABLE tmp LIKE tab1;
LOAD XML ...;
UPDATE tmp SET DEFAULT = 'myfile';
INSERT INTO tab1 SELECT * FROM tmp;


Uncertainty about number format in MySQL In Oracle Database, fields like this:

      example number(6,4) not null;

Indicate that I'm reserving 6 digits for this column, 4 that are being used from the point to the right and 2 for the point to the left.

Is this the same in MySQL?

Thanks!!! I know this is basic but I have some time working on databases and always this question...

It's always a good idea to check the MySQL documentation for quick answers like this.

In a DECIMAL column declaration, the precision and scale can be (and usually is) specified; for example:
salary DECIMAL(5,2)
In this example, 5 is the precision and 2 is the scale. The precision represents the number of significant digits that are stored for values, and the scale represents the number of digits that can be stored following the decimal point.

The decimal type has these constraints on scale and precision:

The declaration syntax for a DECIMAL column is DECIMAL(M,D). The ranges of values for the arguments are as follows:

M is the maximum number of digits (the precision). It has a range of 1 to 65.

D is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than M.


Errors in DB2 8.0 db2diag.log We have a very old PeopleSoft HR system running on DB2 8.x.  Numerous times a day we get the following errors in the db2diag.log file.

 2019-11-04-01.22.24.012784-300 E16054C794         LEVEL: Error (OS)
 PID     : 229596               TID  : 772         PROC : db2hmon 0
 INSTANCE: hrprod               NODE : 000
 FUNCTION: DB2 UDB, oper system services, sqloPdbConnectSocket, probe:20
 MESSAGE : ZRC=0x810F0019=-2129723367=SQLO_CONN_REFUSED "Connection refused"
 CALLED  : OS, -, connect
 OSERR   : ECONNREFUSED (79) "Connection refused"
 DATA #1 : String, 32 bytes
 Unable to connect socket on path
 DATA #2 : socket, 4 bytes
 7
 DATA #3 : sockaddr, 39 bytes
 0x303B4F38 : 0001 2F75 7372 2F75 7365 7273 2F64 6232    ../usr/users/db2
 0x303B4F48 : 6173 2F64 6173 2F74 6D70 2F64 6173 6E61    as/das/tmp/dasna
 0x303B4F58 : 6D65 6470 6970 65                          medpipe
 DATA #4 : File name, 37 bytes
 /usr/users/db2as/das/tmp/dasnamedpipe

We're not sure what's causing the error but the reference to DAS in the error has me thinking the instance is trying to connect to the DAS server, or maybe the other way around.  The DBA who was responsible for the system said the error has appeared for a long time and he couldn't figure out the cause but it had no noticeable negative implications on functionality.  He retired last week.  I'd like to fix the issue if possible.   I'm not familiar with 8.4 and it's use of DAS other than DAS being needed to administer multiple instances, using a GUI, on the same server (there's only one instance on the server).

Any help tracking this down greatly appreciated. :)

Richard

Check the following if it's applicable to your case:
ECONNREFUSED (79) "Connection refused" message in the db2diag.log for the db2hmon process.

  Cause

  The DB2 Health Monitor periodically wakes up and checks for a
  connection to the DB2 administration server (DAS).

  Resolving The Problem

  This type of error is reported when the DAS (DB2 administration
  server) is not active. When the health monitor wakes up it checks for
  a connection to the DAS. In this case the connection is refused as
  either the DAS does not exist or it is not started.


Insert up to 10 missing products into a related product discounts table I have two tables: products and products_discounts. If a product is designated as a "Top 10" product, and it is missing a defined discount in products_discounts, I want to insert a record into products_discounts for each missing record. The inserted values would consist of the product_id and two other hard-coded values in the INSERT INTO.

  Products
      id, product_id, name, top_ten

  Products_Discounts
  id, product_id, discount_amount, discount_description

The following is how I plan to do this manually, but since I'll be repeating this process a few times a year, I'm looking to see if this can be done in a few lines of SQL.

(1) Get the list of products that meet my criteria.

SELECT p.product_id, pd.discount_amount FROM products AS p
LEFT OUTER JOIN products_discounts AS pd
    ON p.product_id = pd.product_Id
WHERE p.top_ten = 1
    AND pd.product_id IS NULL

(2) Use a text editor to construct each individual INSERT INTO statement. I'd use the output of step 1 to get the product_ids but the other two values would remain the same (hard-coded).

INSERT INTO products_discounts (product_id, discount_amount, discount_description)
VALUES ('<product_id goes here>', 10, 'Top 10 Product')

Can these steps be combined into a few lines of SQL?

This might do what you want:

SELECT 'INSERT INTO products_discounts (product_id, discount_amount, discount_description)
VALUES (''' + CONVERT(varchar(30), p.product_id) + ''', 10, ''Top 10 Product'')
'
FROM products AS p
LEFT OUTER JOIN products_discounts AS pd
    ON p.product_id = pd.product_Id
WHERE p.top_ten = 1
    AND pd.product_id IS NULL;

It generates statements for all top-10 products that don't have a product discount.  No need to do this in Excel.  Just copy-and-paste the results into a new query window in SSMS and run it.  Having said that, you'll likely want to automate this procedure, which could be accomplished using either a cursor like this:

DECLARE @cmd nvarchar(max);
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
FOR
SELECT 'INSERT INTO products_discounts (product_id, discount_amount, discount_description)
VALUES (''' + CONVERT(varchar(30), p.product_id) + ''', 10, ''Top 10 Product'')
'
FROM products AS p
LEFT OUTER JOIN products_discounts AS pd
    ON p.product_id = pd.product_Id
WHERE p.top_ten = 1
    AND pd.product_id IS NULL;

OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sys.sp_executesql @cmd;
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur;
DEALLOCATE cur;

Or in a set-based approach, like this:

DECLARE @cmd nvarchar(max);
SELECT @cmd = CASE WHEN @cmd IS NULL THEN N'' ELSE @cmd END
    + N'INSERT INTO products_discounts (product_id, discount_amount, discount_description)
VALUES (''' + CONVERT(varchar(30), p.product_id) + ''', 10, ''Top 10 Product'')
'
FROM products AS p
LEFT OUTER JOIN products_discounts AS pd
    ON p.product_id = pd.product_Id
WHERE p.top_ten = 1
    AND pd.product_id IS NULL;

EXEC sys.sp_executesql @cmd;


Why is there a separate describe and explain statement in MySQL I usually use DESCRIBE to get a description of a table, or a query. But, I was recently looking up some information on how to interpret the output of a DESCRIBE SELECT ... statement, and found that all the tutorials used EXPLAIN rather than DESCRIBE.
I found in the MySQL docs, this explanation:

The DESCRIBE and EXPLAIN statements are synonyms. In practice, the DESCRIBE keyword is more often used to obtain information about table structure, whereas EXPLAIN is used to obtain a query execution plan (that is, an explanation of how MySQL would execute a query).
The following discussion uses the DESCRIBE and EXPLAIN keywords in accordance with those uses, but the MySQL parser treats them as completely synonymous.

So, I'm curious why there are two separate commands. Is this needed to conform to the ANSI SQL spec, or is there a historic reason for the two separate commands?

This is another example of the liberties the MySQL developers have been taking when interpreting the SQL standard (if they ever read it, that is).
DESCRIBE [OUTPUT], with slightly different semantics, is a statement prescribed by the SQL standard, and its purpose is to provide information about the result set (column names and data types) of an executable SQL statement that returns a cursor (result set). This information is inserted to the SQL Descriptor Area (SQLDA) that an application can then access in order to properly process the result set. The standard does not allow for the form DESCRIBE <table name>. This means that the DESCRIBE implementation by MySQL is non-standard.
EXPLAIN is not a part of the SQL standard, but most major DBMSes use it, with slightly different syntax, to generate a statement execution plan in some form. It applies to all executable statements, not just those that return cursors. In that sense the MySQL implementation of EXPLAIN <statement> follows this convention.


Postgres Administration roles I need to work with a remote postgresql server. My application needs to perform the following actions:

to be able to create/drop database (I only need to work with 1 specific database)
to be able to lock db for new connections / to be able to drop all connections.
to work with the DB directly (SQL queries)

Assuming that I can request an administrator user for the DB I am going to work with, and that I'm not going to get a user with administrative permissions for the complete DB server, what is the possible solution for this matter? Is it possible to have one user that can do all that I need?

Thanks.

This was tested using PostgreSQL 9.1

This is assuming that you only want to be able to manage tables, indexes and other objects from within one database.

You will need to first create your user and database as postgres or a user that has the right to create logins and databases

When you create that user, you can make him the owner of the newly created database.  As the owner of the database, that user can drop and create tables as well as other objects within that database.

I have created four different sections.

PostgreSQL is on the same server as the application.
PostgreSQL is running on a distant server.
Locking example.
Disconnect users

1. PostgreSQL server is on the same machine as the application

Just make sure that the users can connect locally.  trust means you don't need a password, or you can use md5 to require one.

cat /path/to/postgresql/data/pg_hba.conf

# IPv4 local connections:
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust

Change to postgres user

su - postgres

connect to PostgreSQL

Now create the user and the database

-bash-3.2$ psql -d template1

template1=# CREATE ROLE myuser WITH LOGIN PASSWORD 'test123';
CREATE ROLE
template1=# CREATE DATABASE mydatabase WITH OWNER myuser;
CREATE DATABASE
template1-# \q

Connect to postgresql as your user

-bash-3.2$ ./psql -d mydatabase -U myuser

mydatabase=> create table mytable (t int not null);
CREATE TABLE

mydatabase=> \dt
         List of relations
 Schema |  Name   | Type  | Owner
--------+---------+-------+--------
 public | mytable | table | myuser

mydatabase=> drop table mytable;
DROP TABLE

2. PostgreSQL server is on a different machine than that of the application

Edit the authorization file on the remote PostgreSQL server.  For users connecting remotely, its better to require a password.

Authorization File

vi /path/to/postgresql/data/pg_hba.conf

Add in the ip address of the server that will be connecting to this PostgreSQL server.

# IPv4 local connections:
host    all             all     ip.address.application_server/32     md5

Edit postgresql.conf file on the remote PostgreSQL server.  You need to make sure that PostgreSQL server is listening on some ip address

vi /path/to/postgresql/data/postgresql.conf
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'          # list on all ip addresses of the server;
# or
listen_addresses = 'ip.address.psql.server'          # listen to one ip address;

Reload PostgreSQL

/etc/init.d/postgresql reload
#or
./pgctl reload -s -D /path/to/postgresql/data
server signaled

Connecting to PostgreSQL From Application server

-bash-3.2$  ./psql -h remote.ip.address.psql -U superuser -d template1
Password for user superuser:

template1=# CREATE ROLE myuser WITH LOGIN PASSWORD 'test123';
CREATE ROLE
template1=# CREATE DATABASE mydatabase WITH OWNER myuser;
CREATE DATABASE
template1-# \q

Connect to distant PostgreSQL server as new user

-bash-3.2$ ./psql -h remote.ip.address.psql -U myuser -d mydatabase
Password for user myuser:

mydatabase=> create table mytable (t int not null);
CREATE TABLE

mydatabase=> \dt
         List of relations
 Schema |  Name   | Type  | Owner
--------+---------+-------+--------
 public | mytable | table | myuser

mydatabase=> drop table mytable;
DROP TABLE

3. Locking example

http://www.postgresql.org/docs/current/interactive/sql-lock.html

Unless I'm mistaken, locking occurs at the table and/or row level unless you can put the database into read-only or standby.

Connected as myuser

mydatabase=> BEGIN WORK;
BEGIN
mydatabase=> LOCK TABLE mytable IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE
mydatabase=> SELECT t FROM MYTABLE ;
 t
---
(0 rows)

mydatabase=> INSERT INTO mytable VALUES (1);
INSERT 0 1
mydatabase=> COMMIT WORK;
COMMIT

4. Disconnect users

Find all active connections

SELECT usename, procpid, datname FROM pg_stat_activity;

Output:

test=# select usename, procpid, datname from pg_stat_activity;
 usename  | procpid | datname
----------+---------+---------
 test     |    4083 | test

Now that you have the procpid, you can terminate the connection.  You must be a superuser to run the next command.

SELECT pg_terminate_backend(4083) FROM pg_stat_activity WHERE datname = 'test';

Then if you want to prevent other normal users from reconnecting you can limit the number of connections to 0.  You can run this command as the db owner

ALTER DATABASE test WITH connection limit 0;

If a user attempts to reconnect, he will get this message

  test=> \dt
  FATAL:  terminating connection due to administrator command server
  closed the connection unexpectedly    This probably means the server
  terminated abnormally     before or while processing the request. The
  connection to the server was lost. Attempting reset: Failed.

On the otherhand, postgres would still be able to connect.

or

Disallow all connections, even postgres.  You have to be a superuser to run this.  I found this command on another post related to a similar question : Force drop db while others may be connected.

UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'test';


System to track what Zip Codes my vendors service I am designing a table configuration that will contain all of the zip codes in the United States. I have a .csv file of all the zip codes in the U.S., so thats at least a start.

This will end up as a one-to-many relationship between our vendors (they are waste services companies), and the various zip codes they service. Does anyone have advice on a good way to set up the tables to track this data? Some vendors may only service 5-10 zip codes, while the national players can handle nearly all 45,000 zip codes, so I'd like to set it up in a manner that this does not become a nightmare later on.

Any advice is appreciated!

I would say you would have:

Table: vendor
  id, name, etc

Table: vendor_area
  id, vendor_id, area_code_id

Table: area_code
  id, area_code, country_id, region_id (etc)

I'm suggesting that not knowing your business. But I would hate you go in a naive route assuming that you'll do US only and in a couple year you have to redesign the whole thing. The above will give you flexibility to expand and add more meta data to your areas/regions being covered.


Database Designing Issue I have a database having Millions of records in three tables. Now i want to split these three tables in multiple tables to reduce redundancy and data duplication.

A lot of records(Persons) have multiple city, address , telephone, fax.

So New tables will be Address, ,City ,Postcode ,State ,Country ,Telephone ,Fax

My question is , Is it fine to have City, Postcode, State, Country, Telephone tables separately or Just merge these tables in address table?

An address is a inseparable entity consisting of Street, City, State, Zip and Country. Those should all go into one table. There might be a reason to have an additional e.g. country table to store countries the person visited, but the address table should always contain at least the above five fields as a street without the rest is meaningless.

For phone numbers it makes sense to have a separate table. The fax numbers are usually stored in the same table differentiated by a type. That makes future extensions a lot simpler, but you could got with separate a fax table too. That would allow you to store additional information like e.g. if that fax number can receive color facsimiles.


Correct index not being used by Mariadb Fiddle: https://www.db-fiddle.com/f/m4vsq4ERyBhiBNdZqALmVP/0
I have two indexes, one for some_other_id and one forcreated_at , some_other_id.
The select query uses the first index on production server (~23M rows) while it uses the second one on my dev machine (1 row).
Server explain:
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: symbol_details
         type: ref
possible_keys: symbol_details_some_other_id_index,symbol_details_created_at_some_other_id_index
          key: symbol_details_some_other_id_index
      key_len: 8
          ref: const
         rows: 24152
        Extra: Using where

dev explain:
           id: 1
  select_type: SIMPLE
        table: symbol_details
         type: range
possible_keys: symbol_details_some_other_index,symbol_details_created_at_some_other_id_index
          key: symbol_details_created_at_some_other_id_index
      key_len: 5
          ref: NULL
         rows: 1
        Extra: Using where; Using index

Also in fiddle it is not using the second index, while clearly having limits on created_at.
Is is because of the number of rows?
I want to use such queries in production, but this way it is way too slow. A single query like this returns ~100 rows out of ~23M rows, and I thought the second index would cover this query, but it seems it somehow does not.
Any idea on how to use index, or created another index for this query, or modify query to use index.
The best solution would be to not touch the query, since it is generated by ORM. Using hints in query while using ORM is not exactly clean, but doable if it is the only choice.
Thanks!
Dev Mariadb version 10.4.13 and production is 10.4.12 (running in docker btw).
EDIT:
The first index is being used somewhere else, and it needs to be there (index on some_other_id). Maybe I can merge the two indexes into on? IDK yet.
EDIT2 why I chose to have two index:
Since some_other_id's cardinality is ~70k and created_at is ~400k I chose have second index to filter created_at first. Now I'm thinking if I was right to think this way or not.

select  count(*)
    from  symbol_details
    where  tsetmc_id = 41974758296041288
      and  created_at >  '2020-06-20'
      and  created_at <  '2020-06-31';

Optimal index, in this order:
INDEX(tsetmc_id, created_at)

When you started with the range value, it could not go past that to the 'id'.  The simple rule is:  Put the = columns first in the index definition.  More:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql
Further notes

The optimizer may decide that a table scan is faster than bothering with the INDEX.  This usually happens when it appears that more than about 20% of the table needs to be touched.  (Using the index implies bouncing back and forth between the index's BTree and the data's BTree.)  If it shuns the index, don't worry; it is probably a wise decision.

The order of the columns in the index is critical when a "range" is involved.  The Optimizer will stop considering columns once it gets past the "range".


First time using an Index - SQLite / Android I'm creating a SQLite Database for Android. I can do fairly simple SQL, but I haven't used  an index before. I'm working on a table with >150,000 records and I've never dealt with a database so large. I know that I need to add at least one index to make my queries faster. Here is the table:

CREATE TABLE IF NOT EXISTS T
(
    a TEXT, b TEXT, c INT, d TEXT // ... 14 more TEXT columns
)

My only queries on this table are only on a, b, and c like this:

SELECT * FROM T WHERE c = ?
SELECT * FROM T WHERE a LIKE ?% AND b LIKE ?%
SELECT * FROM T WHERE a LIKE ?%
SELECT * FROM T WHERE b LIKE ?%

This table is filled once, and no changes are made to it after that. It's acceptable to take some extra time after import to add the index.

Can some one more knowledgeable than me tell me what index or indices I should create and if I should wait until after filling the table to add them?

This is my first question on this site. I thank you for you looking at this for me!

WHERE c = ?

This lookup can be optimized with a simple index on the c column:

CREATE INDEX T_c on T(c);

WHERE a LIKE ? || '%'

There are several restrictions on indexes that can be used for LIKE searches.
In this case, you need a case-insensitive index:

CREATE INDEX T_a_ci ON T(a COLLATE NOCASE);

WHERE b LIKE ? || '%'

same here:

CREATE INDEX T_b_ci ON T(b COLLATE NOCASE);

WHERE a LIKE ? || '%' AND b LIKE ? || '%'

The database can use at most one index per table in a query, so it will use one of the indexes above.
If you have run ANALYZE, it might be able to choose the better one.

To check which indexes are actually being used, use EXPLAIN QUERY PLAN.

Whenever you change tables, any indexes must be updated too.
When you're filling an empty table, it is usually a better idea to create indexes afterwards.

When your application does many updates to some table, it might be worthwhile omitting some index(es) that are useful only for seldomly-used queries.


SQL 2012 Autogrow and file extension problem I had my data and log files set to auto grow by 10%.
This has caused the transaction logfile to grow to an astronomical 600 GB in size and led to the error

Autogrow of file PFMultilinguaklPOO_log in database
PFMultilingual took 523826 milliseconds.
Consider using ALTER Database to set a smaller FILEGROWTH for this file

This was causing DB timeouts.

I changed the auto growth setting from 10% to a fixed size to rectify this in the short term but I obviously need to shrink the size of my log file.
However when I went to investigate the best method of doing this I noticed a peculiar issue with the file extensions.

My data file has the .LDF extension and my log file has the .MDF extension which is obviously the wrong way round. I'm not sure how this happened. Can the extensions be changed back without any conflict?
I am attaching an image to show this.

I was wondering if anyone has seen this before and what the best way to resolve it is.

Can the extensions be changed back without any conflict?

Not while the database is active, but you can offline it, rename the files, then bring it back online:

Check the virtual filenames as you'll need to refer to them in a later step:
SELECT name, physical_name, type_desc FROM my_db.sys.database_files
Offline the database:
ALTER DATABASE my_db SET OFFLINE WITH ROLLBACK IMMEDIATE;
(note: the above will kick out other connections and active transactions, if you want to be a little safer to other potential users remove the "WITH ROLLBACK IMMEDIATE" and it will instead fail if there are other open connections)
Rename the files in the filesystem
Let SQL server know what you have done:
ALTER DATABASE my_db MODIFY FILE (NAME='data_file_name', FILENAME='d:\dbs\data\my_db.mdf');
ALTER DATABASE my_db MODIFY FILE (NAME='log_file_name', FILENAME='d:\dbs\data\my_db.ldf');
Bring the database back online:
ALTER DATABASE my_db SET ONLINE;

  I was wondering if anyone has seen this before

I've seen it. It is most likely that someone created the database by hand (rather than using SSMS or other tools), specified file locations (or just names) different to the defaults, but got the filenames slightly wrong. SQL Server would not have complained at all because is doesn't actually care what the filenames are, they could be named "B12746289863413584F2EFC7A9A35815" and "D040839B958143C890A760B5A489E944" and SQL Server would be just as happy to use them as instructed, the mdf/ndf/ldf and having meaningful names generally conventions are just to give us humans a visual clue as to the purpose of a given file.

  what the best way to resolve it is.

As it doesn't upset SQL Server I would be tempted to say "just leave it alone" for production databases. For databases used for development/QA/UAT/other then either the above or alternately detach and re-attach the database instead of offlining it.


Can I throttle individual databases instances on one SQL Server 2005 box I am running a very old server that has a database that is being permanently retired within 14 months.  My company will not invest anymore in hardware or software for this system as we are moving to a whole new system that is hosted within 6 months.  I have only found reference to this Exchange Entry, but that is about servers and potentially using Virtual Servers.

My server services about 500 users, of which roughly 100-125 are concurrent during any given time of the work day.   There are four 1.5 hr periods of very heavy activity during the day, and as the DBA and developer assigned to work with this server, I have about 1 hour in the morning and overnight jobs to do anything that could seriously impact performance for the machine.  There are 2 active production databases on it that will be used until the change over occurs in 3-6 months, and a development instance that I use for testing/fixing issues, since this is the only SQL server left in the organization.  (For those DBA's cringing at this point; All your DBA concerns about safe practice, let alone best practice, backups and DR, are valid and I have raised them, but they REALLY will not spend anymore on this system or the infrastructure.  So a new box is Moot.)

So, I am left with the desire to throttle (if possible) the two production databases to use no less than 80% of the resources, and give me a 0-20% usage so that if I run something like a DBCC command on the DEV area, it does not chew up the resources and give me the rash of 50 phone calls from the production users 5 minutes later?  I am looking for the ability to throttle so that I can do the data preparation and extraction testing as we migrate to the new system with out messing too much with the day to day business activity.

You could use the Windows System Resource Manager (WSRM) feature for this.
It is not present in Windows starting from 2012 R2, but since this is an old SQL 2005, I would expect the OS to be old-ish as well.

I've never used it myself, but, though a bit complicated, it might do the trick for you.


How to print more than 8,000 characters at a time to the SSMS Message window, without compromising text formatting? Whenever I write dynamic SQL, I typically include a PRINT @DynamicSQL statement in a comment right above the EXEC sp_ExecuteSQL @DynamicSQL statement so that the dynamic SQL can be easily read and debugged when needed.

I realized the PRINT statement has a limit of 8,000 characters before it truncates the string. I know I can loop over my @DynamicSQL variable the number of times 8,000 divides into it's length and print each 8,000 chunk per iteration, but then you lose the formatting where a statement in @DynamicSQL is across two chunks, which kind of defeats my purpose.

Has anyone found a better way to preserve formatting while printing a string more than 8,000 characters?...perhaps through a custom function or procedure?

I have not personally used this technique, but you could try LongPrint.  From that post:

  This very simple procedure is designed to overcome the limitation in
  the SQL print command that causes it to truncate strings longer than
  8000 characters.

  It will print the text passed to it in substrings smaller than 8000
  characters. If there are carriage returns (CRs) in the text, it will
  break up the substrings at the carriage returns and the printed
  version will exactly reflect the string passed.

  If there are insufficient CRs in the text, it will print it out in
  blocks of 8000 characters with an extra carriage return at that point.

  If it is passed a null value, it will do virtually nothing.

  I developed a need to display very lengthy strings while trying to
  debug a script that generated a very long dynamic SQL section. When I
  did not instantly find a script to do this on SQLServerCentral.com I
  decided it would be faster to write one myself than search the broader
  internet.

Copying the code from that post:

CREATE PROCEDURE [dbo].[LongPrint]
      @String NVARCHAR(MAX)

AS

/*
Example:

exec LongPrint @string =
'This String
Exists to test
the system.'

*/

/* This procedure is designed to overcome the limitation
in the SQL print command that causes it to truncate strings
longer than 8000 characters (4000 for nvarchar).

It will print the text passed to it in substrings smaller than 4000
characters.  If there are carriage returns (CRs) or new lines (NLs in the text),
it will break up the substrings at the carriage returns and the
printed version will exactly reflect the string passed.

If there are insufficient line breaks in the text, it will
print it out in blocks of 4000 characters with an extra carriage
return at that point.

If it is passed a null value, it will do virtually nothing.

NOTE: This is substantially slower than a simple print, so should only be used
when actually needed.
 */

DECLARE
               @CurrentEnd BIGINT, /* track the length of the next substring */
               @offset tinyint /*tracks the amount of offset needed */

set @string = replace(  replace(@string, char(13) + char(10), char(10))   , char(13), char(10))

WHILE LEN(@String) > 1
BEGIN

    IF CHARINDEX(CHAR(10), @String) between 1 AND 4000
    BEGIN

           SET @CurrentEnd =  CHARINDEX(char(10), @String) -1
           set @offset = 2
    END
    ELSE
    BEGIN
           SET @CurrentEnd = 4000
            set @offset = 1
    END

    PRINT SUBSTRING(@String, 1, @CurrentEnd)

    set @string = SUBSTRING(@String, @CurrentEnd+@offset, 1073741822)

END /*End While loop*/


Unable to backup postgresql database. pg_dump memory alloc error After 3 days of running an import routine written on RoR to move some 269,000 records from a legacy database via csv files the routine finally finished.
The very first thing I attempt to do is to backup the database using pg_dump before doing anything else.

This resulted in the following

  pg_dump -Ft hsb > hsb_after_full_import.tar

pg_dump: Dumping the contents of table "companies" failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR:  invalid memory alloc request size 18446744073709551613
pg_dump: The command was: COPY public.companies (id, category_id, city_id, name, address, town, neighbourhood, post_code, lon, lat, contact, email, facebook, twitter, listing_url, image_url, phone, website, listing_cid, promotion, description, rating, reviews, wheelchair_accessible, merchant_verified, uploaded, created_at, updated_at, server_id, opening_hours, multinational, places_id, featured_image_file_name, featured_image_content_type, featured_image_file_size, featured_image_updated_at, places_lat, places_lon, source, sanitised) TO stdout;

I am unable to find a tool to verify and fix any potential corruption. Indeed I have encountered many suggestions that Postgresql doesn't get corrupted (I find that impossible to believe) and therefore needs no repair/verification tools.

The import went smoothly and many tests of a few thousand records were undertaken before the full run to determine the suitability of the import script.

There is no point re-creating the database and re-importing if Postgresql is unable to handle this amount of data and just corrupt again.

I would really appreciate some guidance in the best way to proceed? Maybe an alternative backup tool?

The data appears to be OK. The app that is using the data seems to behave itself properly, maybe it's just the pure size of the dump and maybe an issue with the amount of ram (4gb) I have on my machine?

PostgreSQL version

pg_config --version
PostgreSQL 9.3.12

The database was created using Rails db rake task

  rake db:create

From PGAdmin III the create script looks like this

-- Table: companies

-- DROP TABLE companies;

CREATE TABLE companies
(
  id serial NOT NULL,
  category_id integer,
  city_id integer,
  name character varying(255),
  address character varying(255),
  town character varying(255),
  neighbourhood character varying(255),
  post_code character varying(255),
  lon character varying(255),
  lat character varying(255),
  contact character varying(255),
  email character varying(255),
  facebook text,
  twitter text,
  listing_url character varying(255),
  image_url character varying(255),
  phone character varying(255),
  website text,
  listing_cid character varying(255),
  promotion text,
  description text,
  rating character varying(255),
  reviews integer,
  wheelchair_accessible boolean NOT NULL DEFAULT true,
  merchant_verified boolean NOT NULL DEFAULT true,
  uploaded boolean NOT NULL DEFAULT true,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,
  server_id integer,
  opening_hours text,
  multinational boolean DEFAULT false,
  places_id character varying(255),
  featured_image_file_name character varying(255),
  featured_image_content_type character varying(255),
  featured_image_file_size integer,
  featured_image_updated_at timestamp without time zone,
  places_lat double precision,
  places_lon double precision,
  source character varying(255),
  sanitised boolean DEFAULT false,
  CONSTRAINT companies_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE companies
  OWNER TO wpsmiler;

I'm running Linux mint 17.3

select pg_total_relation_size('companies') gives me 113459200

If I run the query manually I get some records output (I assume 100 because of the message) then the same error

Query returned more than 100 copy rows, discarding the rest...

ERROR:  invalid memory alloc request size 18446744073709551613
********** Error **********

ERROR: invalid memory alloc request size 18446744073709551613
SQL state: XX000

After a lot of help from over at the PostgreSQL irc channel it was determined that the database was corrupted. The most likely cause of which was a hardware error on my ageing PC but also and much less likely a potential PG bug. I'm going to have to start again


How to find the session id of running jobs Trying to get the session id of executing jobs

;with JobDetails as
(
select Job_Id = left(intr1,charindex(':',intr1)-1),
       Step = substring(intr1,charindex(':',intr1)+1,charindex(')',intr1)-charindex(':',intr1)-1),
       SessionId = spid
    from master.dbo.sysprocesses x
    cross apply (select replace(x.program_name,'SQLAgent - TSQL JobStep (Job ','')) cs (intr1)
    where spid > 50 and x.program_name like 'SQLAgent - TSQL JobStep (Job %'
)
select *
    from msdb.dbo.sysjobs j
    inner join JobDetails jd on j.job_id = jd.Job_Id

but it throws the following error

  Msg 8169, Level 16, State 2, Line 47 Conversion failed when converting
  from a character string to uniqueidentifier.

I tried to cast the job_id to varbinary but it produces no result

;with JobDetails as
(
select Job_Id = left(intr1,charindex(':',intr1)-1),
       Step = substring(intr1,charindex(':',intr1)+1,charindex(')',intr1)-charindex(':',intr1)-1),
       SessionId = spid
    from master.dbo.sysprocesses x
    cross apply (select replace(x.program_name,'SQLAgent - TSQL JobStep (Job ','')) cs (intr1)
    where spid > 50 and x.program_name like 'SQLAgent - TSQL JobStep (Job %'
)
select *
    from msdb.dbo.sysjobs j
    inner join JobDetails jd on cast(j.job_id as varbinary) = jd.Job_Id

but when I copy paste a job_id from cte like this

select job_id, name
    from msdb..sysjobs
    where job_id = 0x128A47A31EAB8F4DA1AD852093D815F5

it works. Any idea how to fix this query

I used CONVERT to convert the msdb.dbo.sysjob.job_id to BINARY(16) and then used CONVERT (and a style) to convert that to a varchar(max) which could be compared to what comes out of your cte.  You can read more about CONVERT and styles by reviewing the documentation for CONVERT. This worked in my testing.

Try this:

;
WITH JobDetails
AS (
    SELECT DISTINCT Job_Id = left(intr1, charindex(':', intr1) - 1)
        ,Step = substring(intr1, charindex(':', intr1) + 1, charindex(')', intr1) - charindex(':', intr1) - 1)
        ,SessionId = spid
    FROM master.dbo.sysprocesses x
    CROSS APPLY (
        SELECT replace(x.program_name, 'SQLAgent - TSQL JobStep (Job ', '')
        ) cs(intr1)
    WHERE spid > 50
        AND x.program_name LIKE 'SQLAgent - TSQL JobStep (Job %'
    )
SELECT *
FROM msdb.dbo.sysjobs j
INNER JOIN JobDetails jd ON jd.Job_Id = convert(VARCHAR(max), convert(BINARY (16), j.job_id), 1)


How and when to use sys_refcursor in oracle Can some one give me a small explanation about how and when should someone use sys_refcursor ?

A cursor is a pointer to a result set for a query. By returning a sys_refcursor you allow the client to fetch as many or few of the rows from the query as it requires. In stateful applications this could be used to page through results.

A cursor can allow more flexibility than writing a PL/SQL function that returns an array as it's completely up to the client how many rows to fetch and when to stop. That said, I've not found many cases where this additional flexibility is useful.

It's worth noting that the sys_refcursor is weakly typed, so you can return pointers to queries which not only have different from or where clauses, but different numbers and types of columns as well. Alternatively you can use strongly typed cursor where the columns in the result set are fixed.

This enables you to write functions which return different queries, like so:

create function get_data ( type varchar2 ) return sys_refcursor as
  ret_cur sys_refcursor;
begin

  if type = 'EMP' then
    open ret_cur for select * from emp;
  elsif type = 'DEPT' then
    open ret_cur for select * from dept;
  end if;

  return ret_cur;
end;

However, if you're using sys_refcursor to create a generic "open a query" function like the above you're probably doing something wrong!


What happened to MySQL 6 & 7? With the recent release of MySQL 8, I can't seem to find any information about why version numbers 6 and 7 were skipped.  Anyone know?

According to Dave Stockes, a MySQL Community Manager for Oracle:
(from his blog post: MySQL 8 is coming)

  Years ago, before the Sun Microsystems purchase of MySQL AB, there was a version of MySQL with the number 6. Sadly, it was a bit ambitious and the change of ownership left it to wither. The MySQL Cluster product has been using the 7 series for years. With the new changes for MySQL 8, developers feel they have modified it enough to bump the big number.

Wikipedia article about MySQL also has these events:

  Sun Microsystems acquired MySQL AB in 2008.
  Version 5.1: production release 27 November 2008 (event scheduler, partitioning, plugin API, row-based replication, server log tables)
  MySQL 5.1 and 6.0-alpha showed poor performance when used for data warehousing – partly due to its inability to utilize multiple CPU cores for processing a single query.
  MySQL Server 6.0.11-alpha was announced on 22 May 2009 as the last release of the 6.0 line. Future MySQL Server development uses a New Release Model. Features developed for 6.0 are being incorporated into future releases.
  Oracle acquired Sun Microsystems on 27 January 2010.
  Work on version 6 stopped after the Sun Microsystems acquisition. The MySQL Cluster product uses version 7. The decision was made to jump to version 8 as the next major version number.

And for anyone who wants to see the code, here is a repository: MySQL 6.0 binaries and source code (6.0.11-alpha).


postgresql Cannot login with created users I am very new to postgreSQL, but quite familiar with SQL.

I have recently installed postgreSQL on Windows 10.  I ran through the startup tutorials and made a few DBs to mess around with.  I can login just fine with the postgres account with the command

psql -U postgres -d DatabaseName

However, I have thus far been unable to create a new user to log in with.

I tried the command

CREATE USER myUser WITH SUPERUSER PASSWORD 'password';

When I do this, it creates the user.  I can see this with the \du command:

Role name  |   List of Attributes                      | Member of
postgres   | Superuser, Create Role, Create DB, yada...| {}
myuser     | Superuser                                 | {}

I then try \q and logging in with my new user and get the following:

psql -U myUser -d Database
Password for myUser: password
psql: FATAL: password authentication failed for "myUser"

I have also tried createuser from cmd, this user showed up in the list of users as well, but I was again unable to login as these users.

Thank you for any help you can offer.

It's just a case folding problem. You're trying to connect with myUser but due to case folding rules, you created myuser in lowercase, as the \du output shows. Per documentation:

  Quoting an identifier also makes it case-sensitive, whereas unquoted
  names are always folded to lower case.

To create myUser with this exact case, enclose it in double quotes, as in:

CREATE USER "myUser" WITH SUPERUSER PASSWORD 'password';

Then the invocation psql -U myUser ... will work.

Or alternatively, don't recreate the user but always refer to it in lowercase.


What is the Best Way to detect if server is a Master inside a Stored Procedure? I'm want to create a stored procedure/event to do some maintenance tasks.  My setups are master/slave configured for failover scenarios (meaning the slave is not used for reporting, just might be promoted in the event of primary master failure).

I'd like to have this stored procedure run in the event scheduler on both.  During the normal course of operation I'd like it to do it's thing and let those operations flow through the normal chain of replication.  In the event of failover I'd like this to just detect it's not the master and continue the work on what was previously the slave.  (Less things to worry about getting in place during such an event).

I was thinking wrapping the initial call in a block of pseudo code that looked like

if I_AM_MASTER then

call real_work_sp;

end if;

What's the best way to test for I_AM_MASTER?  I know I can do show master status from the commandline, but I'm not sure how to capture that for purposes of this test.  How would I do that, or is there a better way?

P.S. The operations aren't just deletes I could just let "fall through" if they happened to get run twice.

This will work if the Master has a Slave Connected

SELECT COUNT(1) FROM
(SELECT user FROM information_schema.processlist) A
INNER JOIN
(SELECT user FROM mysql.user B WHERE repl_slave_priv = 'Y')
USING (user);

If the answer is...

> 0, Replication is running (This indicates this is a Master)
= 0, Replication is not running

You can also check the global status variable 'Slave_running'

SELECT COUNT(1) FROM information_schema.global_status
WHERE variable_name = 'Slave_running'
AND variable_value = 'ON';

If the answer is...

> 0, Replication is running (This indicates this is a Slave)
= 0, Replication is not running

You can also check the processlist for 'system user'

SELECT COUNT(1) FROM information_schema.processlist
WHERE user = 'system user';

If the answer is...

= 2, Replication is running (This indicates this is a Slave)
= 1, Replication is broken (This indicates this is a Slave)
= 0, Replication is not running

I hope these queries give you some direction.

CAVEAT

SHOW MASTER STATUS; just tells you what the current binary log is. If your Slaves  have binary logs disabled, then SHOW MASTER STATUS; return nothing. There is no way to capture this in the information_schema database.

The only obvious way is to either

SHOW SLAVE STATUS\G
Look for the master.info file in the OS

UPDATE 2011-12-30 12:50 EDT

Here is simply way to identify your master from your slaves: Create a Table whose sole purpose to is to hold the server names that make up your Master. Run these commands on all your DB Servers, (Masters and Slaves)

CREATE TABLE mysql.MasterList
(
    hostname VARCHAR(64),
    PRIMARY KEY (hostname)
) ENGINE=MyISAM;
INSERT INTO mysql.MasterList VALUES ('dbserver1'),('dbserver2'),('dbserver3');

Now just run this query to determine if it is a Master:

SELECT COUNT(1) INTO @IsThisMaster FROM
(SELECT variable_value hostname FROM information_schema.global_variables
WHERE variable_name='hostname') A
INNER JOIN mysql.MasterList B USING (hostname);

Here is a sample run on my PC using MySQL 5.5.12

mysql> show variables like 'hostname';
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| hostname      | LW-REDWARDS2 |
+---------------+--------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE mysql.MasterList
    -> (
    ->     hostname VARCHAR(64),
    ->     PRIMARY KEY (hostname)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO mysql.MasterList VALUES ('dbserver1'),('dbserver2'),('dbserver3');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(1) INTO @IsThisMaster FROM
    -> (SELECT variable_value hostname FROM information_schema.global_variables
    -> WHERE variable_name='hostname') A
    -> INNER JOIN mysql.MasterList B USING (hostname);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT @IsThisMaster;
+---------------+
| @IsThisMaster |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

mysql> INSERT INTO mysql.MasterList VALUES ('LW-REDWARDS2');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT COUNT(1) INTO @IsThisMaster FROM
    -> (SELECT variable_value hostname FROM information_schema.global_variables
    -> WHERE variable_name='hostname') A
    -> INNER JOIN mysql.MasterList B USING (hostname);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @IsThisMaster;
+---------------+
| @IsThisMaster |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

mysql>

Give it a Try !!!

UPDATE 2011-12-30 13:06 EDT

Here is the Stored Function you will need:

DELIMITER $$

DROP FUNCTION IF EXISTS `mysql`.`Is_This_A_Master` $$
CREATE FUNCTION `mysql`.`Is_This_A_Master` () RETURNS INT
DETERMINISTIC
BEGIN

    SELECT COUNT(1) INTO @IsThisReplicationMaster FROM
    (SELECT variable_value hostname FROM information_schema.global_variables
    WHERE variable_name='hostname') A
    INNER JOIN mysql.MasterList B USING (hostname);

    RETURN @IsThisReplicationMaster;

END $$

DELIMITER ;

Here is a Sample Call:

mysql> select mysql.Is_This_A_Master();
+--------------------------+
| mysql.Is_This_A_Master() |
+--------------------------+
|                        1 |
+--------------------------+

UPDATE 2011-12-30 13:12 EDT

Just remember

Once a Server Becomes a Slave, delete that hostname from mysql.MasterList
Once a Server Becomes a Master, insert that hostname into mysql.MasterList


Read TSQL variable from Temp Table I am hacking around trying to write a script to restore a backup from a known folder location.  The location will only ever contain one backup file.  I have come up with this so far, but I don't know how to get the value from the temp table into the restore database command.

CREATE TABLE #tempFileTable (FName VARCHAR(8000),Depth INTEGER, Files INTEGER)

INSERT INTO #tempFileTable
EXEC xp_dirtree 'S:\', 1, 1

SELECT TOP 1 FName FROM #tempFileTable

DROP TABLE #tempFileTable

DECLARE @DBName varchar(255) = 'DatabaseName'

RESTORE DATABASE @DBName FROM  DISK = ??? WITH  FILE = 1

GO

Maybe you could try something like :

declare @file varchar(50)
declare @DBName varchar(50)
declare @sql varchar(200)

CREATE TABLE #tempFileTable (FName VARCHAR(8000),Depth INTEGER, Files INTEGER)

INSERT INTO #tempFileTable
EXEC xp_dirtree 'D:\', 1, 1

select @file = FName
from #tempFileTable

set @DBName = 'MyDB'

set @sql = 'RESTORE DATABASE '+@DBName+' FROM  DISK = ''D:\'+@file+''' WITH  FILE = 1'

print @sql


Not Null Fields and Unmatched Number of Columns I am in charge of a task of migrating data from MySQL to PostgreSQL. I have already migrated the MySQL database to PostgreSQL with the help of pgloader. My problem is I need to insert the data from the migrated PostgreSQL to our production PostgreSQL which is using Django as backend. I have mapped the columns for each database and the numbers don't match. We have more columns in our production DB than the one that I migrated.

I want to know what to do with the fields/columns that will not be filled with data and has a NOT NULL constraints? How should I approach this? I am new in this kind of task and my apologies for asking.

I want to know what to do with the fields/columns that will not be filled with data and has a NOT NULL constraints? How should I approach this? I am new in this kind of task and my apologies for asking.

Remove the NOT NULL constraint on MySQL. You have columns that you don't need -- as evidenced by wanting to do this, and you're declaring them as necessary. There is nothing wrong with null. MySQL views NULL and NOT NULL as attributes of the type, and not constraints on the table. From the docs on the ALTER TABLE Syntax,

ALTER TABLE myTable
  MODIFY myCol INT NULL;

Where INT is the column type.

With PostgreSQL

For comparison, PostgreSQL has the more sane and standardized syntax,

ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL

Which looks like this,

ALTER TABLE myTable
  ALTER COLUMN myColumn
  DROP NOT NULL;


How to create a second socket for mysqld I would like to create second mysqld instance.

How can I create a second socket file?

I tried copying /var/run/mysqld/mysqld.sock and create an empty file mysqld2.sock but no luck.

You will want to use mysqld_multi, which is part of a typical MySQL server install. On my Ubuntu server, it was located at /usr/bin/mysqld_multi.

MySQL's Documentation on mysqld_multi

Here is a how to, I used to set up mysqld_multi: MySQL Multi How to

Update, running a 2nd instance from the shell

Start another mysqld using a different, port, socket and data directory

# make a new data directory
mkdir /var/lib/mysql2

# give ownership to mysql user
chown mysql:mysql -R /var/lib/mysql2

# install mysql database
mysql_install_db --user=mysql --datadir=/var/lib/mysql2/

# run 2nd daemon
/usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql2
--user=mysql
--log-error=/var/log/mysqld2.log
--pid-file=/var/lib/mysql2/mysql2.pid
--bind-address=127.0.0.1 --port=3307
--socket=/var/lib/mysql2/mysql2.sock &

Then connect to the 2nd instance with mysql

mysql --port 3307 --socket=/var/lib/mysql2/mysql2.sock


PostgreSQL 9.2 - 9.6 upgrade with zero downtime I have to upgrade from PostgreSQL 9.2 to 9.6. Following are the challenges that I am facing:

Since we have streaming replication setup, and PostgreSQL doesn't support lower to upper version upgrade when in streaming replication mode, when I upgrade the master I have to rebuild the slaves and that takes 3 hours. I don't have that time to spare. At any given time we need one slave and one master available. Is there any other way to upgrade without having to rebuild the slaves, using streaming replication?
To build logical replication we thought of using slony but slony has some limitations in that it does not automatically replicate:

Changes to large objects (BLOBS)
Changes made by DDL commands
Changes to users and roles
...and our application has continuous create commands. So we cannot use slony.

Please suggest something to avoid slave rebuild and to do upgrade with minimal downtime and one master and one slave ready.

Upgrading a database will need downtime on your servers. To minimize the downtime, i would suggest you install the newer version in a new server, create a replication setup on that and dump all data from old instance here, and change server pointing so that application points to new server to connect to database.