Permissions in triggers when using cross database certificates I use cross database certificates (as explained by Erland Sommarskog) to control access to a certain database in my environment ( SQL Server 2008 R2 ).

I have stored procedures in database A that update tables in database B.  This has always worked for a variety of stored procedures in db A and tables in db B until now.  I am trying to update a table in db B, but the table has a trigger on it.  This trigger is inserting additional data in another table in db B.  I am getting the error:

  Msg 916, Level 14, State 1, Procedure table_trigger, Line 11 The server
  principal "sql\login" is not able to access the database
  "B" under the current security context.

I tried granting insert permissions for database B user that is tied to the certificate to insert into that other table, but it did not resolve the error.  Do I have any options other than changing the trigger so it uses WITH EXECUTE AS OWNER?

Here is DDL to replicate the issue:

CREATE LOGIN [GuggTest] WITH PASSWORD=N'abcd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

CREATE DATABASE A;
CREATE DATABASE B;

USE A;

CREATE TABLE dbo.SPtoUpdate
    (
      ID INT
    , ILoveFishing VARCHAR(255)
    );
INSERT INTO dbo.SPtoUpdate
        ( ID , ILoveFishing )
VALUES  ( 1,'Musky'),( 2,'Pike'),( 3,'Yellow Perch');
CREATE TABLE dbo.TriggerToInsert
    (
      ID INT
    , ILoveFishing VARCHAR(255)
    , ChangeDate DATETIME2
    );
GO

CREATE TRIGGER dbo.SPtoUpdateTrigger ON dbo.SPtoUpdate
    FOR UPDATE
AS
    DECLARE @datetime DATETIME2;
    SELECT  @datetime = GETDATE()

    INSERT  INTO dbo.TriggerToInsert
            ( ID , ILoveFishing , ChangeDate )
    VALUES  ( 1 , 'Yes' , @datetime );
GO

CREATE CERTIFICATE BExecutor
   ENCRYPTION BY PASSWORD = 'Obfuscated'
   WITH SUBJECT = 'Execute sp from B to A',
   START_DATE = '20140101', EXPIRY_DATE = '20300101'
GO

BACKUP CERTIFICATE BExecutor TO FILE = 'C:\temp\crossdbcert.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\crossdbcert.pvk' ,
                  ENCRYPTION BY PASSWORD = 'Obfuscated',
                  DECRYPTION BY PASSWORD = 'Obfuscated')
GO

CREATE USER BExecutor FROM CERTIFICATE BExecutor

GRANT UPDATE ON dbo.SPtoUpdate TO BExecutor
GRANT SELECT ON dbo.SPtoUpdate TO BExecutor
--Also give insert on dbo.TriggerToInsert
GRANT INSERT ON dbo.TriggerToInsert TO BExecutor

USE B
GO

CREATE USER [GuggTest] FOR LOGIN [GuggTest];
EXEC sp_addrolemember N'db_owner', N'GuggTest'
GO

CREATE PROCEDURE dbo.UpdateTableInA
AS
    BEGIN
        UPDATE  A.dbo.SPtoUpdate
        SET     ILoveFishing = 'Walleye'
        WHERE   ID = 2;
    END

GO

CREATE CERTIFICATE BExecutor FROM FILE = 'C:\temp\crossdbcert.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\crossdbcert.pvk' ,
                  ENCRYPTION BY PASSWORD = 'Obfuscated',
                  DECRYPTION BY PASSWORD = 'Obfuscated')
GO

EXEC master..xp_cmdshell 'DEL C:\temp\crossdbcert.*', 'no_output'
GO

ADD SIGNATURE TO dbo.UpdateTableInA BY CERTIFICATE BExecutor
    WITH PASSWORD = 'Obfuscated'
GO

--Log In or Change execution context to GuggTest, then EXEC dbo.UpdateTableInA

The problem here is that while the Certificate links the stored procedure in DatabaseA with the User in DatabaseB that has INSERT permissions on the two tables, the Trigger on the table being inserted into directly from the stored procedure is another module in the chain, and permissions acquired from Certificates do not pass along to other modules in a chain. Meaning, the Certificate allowed the Stored Procedure to insert into the table via the User, and even execute the Trigger. But, no permissions have been given to the Trigger to do anything related to objects (doing something like SELECT 1; would work).

In this case, permissions need to be given to the Trigger, via that same Certificate, so that it can take whatever actions are necessary. This can be accomplished, at the very least, by counter-signing the Trigger. And you do that by executing ADD COUNTER SIGNATURE TO [TriggerSchema].[TriggerName] BY CERTIFICATE ...;. After that, it should just work, even without direct INSERT permission for the Certificate-based User on the table being inserted into by the Trigger.

The example code below reproduces the problem, fixes the problem by adding a counter signature, but does not grant INSERT permissions to the trigger-populated table.

CLEANUP

USE [master];
GO
IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'DatabaseA')
BEGIN
    PRINT 'Dropping [DatabaseA] DB...';
    ALTER DATABASE [DatabaseA] SET OFFLINE WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [DatabaseA] SET ONLINE;
    DROP DATABASE [DatabaseA];
END;

IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'DatabaseB')
BEGIN
    PRINT 'Dropping [DatabaseB] DB...';
    ALTER DATABASE [DatabaseB] SET OFFLINE WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [DatabaseB] SET ONLINE;
    DROP DATABASE [DatabaseB];
END;

IF (SUSER_ID(N'JohnnyLunchbucket') IS NOT NULL)
BEGIN
  PRINT 'Dropping [JohnnyLunchbucket] Login...';
  DROP LOGIN [JohnnyLunchbucket];
END;

IF (OBJECT_ID(N'tempdb..#CertInfo') IS NOT NULL)
BEGIN
  PRINT 'Dropping [#CertInfo] Temp Table...';
  DROP TABLE #CertInfo;
END;

SETUP

USE [master];

EXECUTE AS LOGIN = N'sa';
PRINT 'Creating databases...';
CREATE DATABASE [DatabaseA] COLLATE Latin1_General_100_CI_AS_SC;
CREATE DATABASE [DatabaseB] COLLATE Latin1_General_100_CI_AS_SC;
REVERT;
GO

-- Default for both options should be OFF, but just to be sure:
ALTER DATABASE [DatabaseA] SET DB_CHAINING OFF;
ALTER DATABASE [DatabaseA] SET TRUSTWORTHY OFF;

ALTER DATABASE [DatabaseB] SET DB_CHAINING OFF;
ALTER DATABASE [DatabaseB] SET TRUSTWORTHY OFF;
GO

CREATE LOGIN [JohnnyLunchbucket] WITH PASSWORD = 'OhSoSecure;)';

USE [DatabaseA];

CREATE USER [JohnnyLunchbucket] FOR LOGIN [JohnnyLunchbucket];
GO

--DROP PROCEDURE dbo.InsertIntoTableWithoutTrigger;
CREATE PROCEDURE dbo.InsertIntoTableWithoutTrigger
(
    @SomeValue NVARCHAR(50)
)
AS
SET NOCOUNT ON;

INSERT INTO [DatabaseB].[dbo].[TableWithoutTrigger] (SomeValue)
VALUES (@SomeValue);
GO

GRANT EXECUTE ON dbo.InsertIntoTableWithoutTrigger TO [JohnnyLunchbucket];
GO

CREATE PROCEDURE dbo.InsertIntoTableWithTrigger
AS
SET NOCOUNT ON;

INSERT INTO [DatabaseB].[dbo].[TableWithTrigger] (SomeOtherValue)
VALUES (NEWID());
GO

GRANT EXECUTE ON dbo.InsertIntoTableWithTrigger TO [JohnnyLunchbucket];

CREATE CERTIFICATE [PermissionsCert]
  AUTHORIZATION [dbo]
  ENCRYPTION BY PASSWORD = 'WeakPassword'
  WITH SUBJECT = 'Used to test granting permissions to code',
  EXPIRY_DATE = '2099-12-31';

ADD SIGNATURE TO [dbo].[InsertIntoTableWithoutTrigger]
    BY CERTIFICATE [PermissionsCert]
    WITH PASSWORD = 'WeakPassword';

ADD SIGNATURE TO [dbo].[InsertIntoTableWithTrigger]
    BY CERTIFICATE [PermissionsCert]
    WITH PASSWORD = 'WeakPassword';

-- Save Certificate info in temporary table so we can recreate in DatabaseB
SELECT CERTENCODED(CERT_ID(N'PermissionsCert')) AS [PublicKey],
       CERTPRIVATEKEY(CERT_ID(N'PermissionsCert'), 'OtherPassword', 'WeakPassword')
              AS [PrivateKey]
INTO   #CertInfo;
GO

USE [DatabaseB];

DECLARE @SQL NVARCHAR(MAX);

SELECT @SQL = N'CREATE CERTIFICATE [PermissionsCert] AUTHORIZATION [dbo] FROM BINARY = '
               + CONVERT(NVARCHAR(MAX), [PublicKey], 1)
               + N' WITH PRIVATE KEY (BINARY = '
               + CONVERT(NVARCHAR(MAX), [PrivateKey], 1)
               + N', DECRYPTION BY PASSWORD = N''OtherPassword'''
               + N', ENCRYPTION BY PASSWORD = ''WeakPassword'');'
FROM   #CertInfo;

PRINT @SQL;
EXEC (@SQL);

CREATE USER [PermissionsUser] FROM CERTIFICATE [PermissionsCert];

--DROP TABLE dbo.[TableWithoutTrigger];
CREATE TABLE dbo.[TableWithoutTrigger]
(
  [TableWithoutTriggerID] INT NOT NULL IDENTITY(1, 1)
     CONSTRAINT [PK_TableWithoutTrigger] PRIMARY KEY,
  [SomeValue] NVARCHAR(50)
);

GRANT INSERT ON [dbo].[TableWithoutTrigger] TO [PermissionsUser];

CREATE TABLE dbo.[TableWithTrigger]
(
  [TableWithTriggerID] INT NOT NULL IDENTITY(1, 1)
     CONSTRAINT [PK_TableWithTrigger] PRIMARY KEY,
  [SomeOtherValue] NVARCHAR(50)
);

GRANT INSERT ON [dbo].[TableWithTrigger] TO [PermissionsUser];

CREATE TABLE dbo.[TablePopulatedByTrigger]
(
  [TablePopulatedByTriggerID] INT NOT NULL IDENTITY(1, 1)
     CONSTRAINT [PK_TablePopulatedByTrigger] PRIMARY KEY,
  [DuplicatedValue] NVARCHAR(50)
);
GO

CREATE TRIGGER dbo.CopySomeOtherValue
ON dbo.[TableWithTrigger]
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.[TablePopulatedByTrigger] ([DuplicatedValue])
        SELECT ins.[SomeOtherValue]
        FROM   inserted ins;
END;
GO

TEST 1: Trigger fails

USE [DatabaseA];

EXECUTE AS LOGIN = 'JohnnyLunchbucket';
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO

SELECT * FROM [DatabaseB].[dbo].[TableWithoutTrigger];
SELECT * FROM [DatabaseB].[dbo].[TableWithTrigger];
SELECT * FROM [DatabaseB].[dbo].[TablePopulatedByTrigger];
INSERT INTO [DatabaseB].[dbo].[TableWithoutTrigger] ([SomeValue]) VALUES (N'test 0');
USE [DatabaseB];
/* -- All 5 statements above get the following error:
Msg 916, Level 14, State 1, Line xxxxxx
The server principal "JohnnyLunchbucket" is not able to access the database
    "DatabaseB" under the current security context.
*/

EXEC [dbo].[InsertIntoTableWithoutTrigger] @SomeValue = N'test A'; -- SUCCESS!!!

EXEC [dbo].[InsertIntoTableWithTrigger]; -- ERROR:
/*
Msg 916, Level 14, State 1, Procedure CopySomeOtherValue, Line xxxxxx
The server principal "JohnnyLunchbucket" is not able to access the database
    "DatabaseB" under the current security context.
*/

REVERT;
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];

-- Check to make sure that dbo.InsertIntoTableWithoutTrigger really did work:
SELECT * FROM [DatabaseB].[dbo].[TableWithoutTrigger];
-- 1    test A

TEST 2: Trigger succeeds

Please note that the only change being made is ADD COUNTER SIGNATURE; there is no GRANT INSERT ON dbo.TablePopulatedByTrigger TO [PermissionsUser];.

USE [DatabaseB];

ADD COUNTER SIGNATURE
    TO dbo.[CopySomeOtherValue]
    BY CERTIFICATE [PermissionsCert]
    WITH PASSWORD = 'WeakPassword';
GO

USE [DatabaseA];

EXECUTE AS LOGIN = 'JohnnyLunchbucket';
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO

INSERT INTO [DatabaseB].[dbo].[TableWithTrigger] ([SomeOtherValue]) VALUES (N'Test B');
INSERT INTO [DatabaseB].[dbo].[TablePopulatedByTrigger]([DuplicatedValue]) VALUES ('Test B')
/*
Msg 916, Level 14, State 1, Line xxxxxx
The server principal "JohnnyLunchbucket" is not able to access the database
    "DatabaseB" under the current security context.
*/

EXEC [dbo].[InsertIntoTableWithTrigger]; -- SUCCESS!!!

REVERT;
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];

SELECT * FROM [DatabaseB].[dbo].[TableWithTrigger];
SELECT * FROM [DatabaseB].[dbo].[TablePopulatedByTrigger];
-- 2    968DB092-C3DE-4E4B-92B9-E21CA551A5FA
-- 1    968DB092-C3DE-4E4B-92B9-E21CA551A5FA


"No such interface supported" after upgrading SourceSafe project to 2012 SSMS format. We had SQL Server 2008 r2 project.
Then we converted it to SSMS 2012 format.
Now I can't open the converted project.
I am getting an error

C:\Views.ssmssqlproj : error  : Unexpected error encountered. It is recommended that you restart the application as soon as possible.

Error: No such interface supported
File: vsee\internal\inc\vscomptr.inl
Line number: 259

The older project was using SourceSafe 6. I wonder if it is the reason. SQL Server Management Studio 2012 doesn't support SourceSafe 6 anymore.

Microsoft has logged this issue:
http://support.microsoft.com/kb/2727824

and has included the fix starting in the following update:

2723749 Cumulative update package 3 for SQL Server 2012

According to Microsoft, this fix should be available in Service packs:

  Microsoft SQL Server 2012 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2012 Service Pack RTM hotfix to an installation of SQL Server 2012 Service Pack RTM. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

So, the latest SQL Server 2012 service pack contains the fix, and it worked for me.


GROUP BY multiple columns I'm trying to draw stats out of a table and depending on the increment, group them by year, month, date.

The table is as follows

CREATE TABLE `channel_stats` (
  `channel_id` int(10) unsigned DEFAULT NULL,
  `datestamp` date DEFAULT NULL,
  `udatestamp` int(10) unsigned DEFAULT NULL,
  `ip_country` char(2) DEFAULT NULL,
  `billing_country` char(2) DEFAULT NULL,
  `payment_method` varchar(45) DEFAULT NULL,
  `trans_type` varchar(45) DEFAULT NULL,
  `trans_status` varchar(45) DEFAULT NULL,
  `trans_currency` char(3) DEFAULT NULL,
  `trans_amount` decimal(13,4) unsigned DEFAULT NULL,
  `base_amount` decimal(13,4) unsigned DEFAULT NULL,
  `trans_fees` decimal(13,4) DEFAULT NULL,
  `volume` int(10) unsigned DEFAULT NULL,
  UNIQUE KEY `channel_stats_UNIQUE` (`channel_id`,`datestamp`,`ip_country`,`billing_country`,`payment_method`,`trans_type`,`trans_status`,`trans_currency`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and the query I'm trying to use (which doesnt work) looks something like this

select
    datestamp,
    trans_type,
    trans_status,
    sum(base_amount) 'total_amount',
    sum(trans_fees) 'total_fees',
    sum(volume) 'total_volume'
from channel_stats
where channel_id = p_channel_id
and udatestamp >= p_startdate and udatestamp <= p_enddate
group by case
    when p_increment = 'days' then
        (datestamp)
    when p_increment = 'months' then
        (year(datestamp), month(datestamp))
    when p_increment = 'years' then
        (year(datestamp))
end, trans_type, trans_status;

does anyone know how i can achieve this?

EDIT:

How it would get called for each increment

call uic_get_dashboard_transactions_100('1',(unix_timestamp() - 60 * 60 * 24 * 30),unix_timestamp(),'days');
call uic_get_dashboard_transactions_100('1',(unix_timestamp() - 60 * 60 * 24 * 365),unix_timestamp(),'months');
call uic_get_dashboard_transactions_100('1',(unix_timestamp() - 60 * 60 * 24 * 365 * 5),unix_timestamp(),'years');

It works if I break it out which I think in terms of processing, is a better way of doing it, just creates more code than probably necessary. Current solution:

CREATE PROCEDURE `uic_get_stats_transactions_100`(
    p_channel_id int unsigned,
    p_startdate int unsigned,
    p_enddate int unsigned,
    p_increment varchar(10)
)
BEGIN
    case p_increment
        when 'days' then
            select
                datestamp,
                trans_type,
                trans_status,
                sum(base_amount) 'total_amount',
                sum(trans_fees) 'total_fees',
                sum(volume) 'total_volume'
            from channel_stats
            where channel_id = p_channel_id
            and udatestamp >= p_startdate and udatestamp <= p_enddate
            group by datestamp, trans_type, trans_status;
        when 'months' then
            select
                datestamp,
                trans_type,
                trans_status,
                sum(base_amount) 'total_amount',
                sum(trans_fees) 'total_fees',
                sum(volume) 'total_volume'
            from channel_stats
            where channel_id = p_channel_id
            and udatestamp >= p_startdate and udatestamp <= p_enddate
            group by year(datestamp), month(datestamp), trans_type, trans_status;
        when 'years' then
            select
                datestamp,
                trans_type,
                trans_status,
                sum(base_amount) 'total_amount',
                sum(trans_fees) 'total_fees',
                sum(volume) 'total_volume'
            from channel_stats
            where channel_id = p_channel_id
            and udatestamp >= p_startdate and udatestamp <= p_enddate
            group by year(datestamp), trans_type, trans_status;
    end case;
END

I prefer to take advantage of the 'string' format of dates:
LEFT(datestamp, 4) is the same as YEAR(datestamp), etc:

mysql> SELECT LEFT(NOW(), 4), LEFT(NOW(), 7), LEFT(NOW(), 10);
+----------------+----------------+-----------------+
| LEFT(NOW(), 4) | LEFT(NOW(), 7) | LEFT(NOW(), 10) |
+----------------+----------------+-----------------+
| 2015           | 2015-02        | 2015-02-15      |
+----------------+----------------+-----------------+

So, your complex CASE boils down to deciding between 4, 7, and 10, thereby shortening the code significantly.  (This trick works for DATE, DATETIME, TIMESTAMP, but not timestamps stored as INT.)

As a side note: It would be better to use country CHAR(2) CHARSET ascii, which takes only 2 bytes.


Trigger to keep account unlock history How can create trigger that will insert username and date to a table,when oracle user account unlocked to keep account lock history

You can write a database trigger like this:

CREATE OR REPLACE TRIGGER account_trigger
   BEFORE ALTER ON DATABASE
   WHEN ora_dict_obj_name = 'USER'
BEGIN
   INSERT INTO LOG_TABLE (...) VALUES (...);
END;

Check this document to get more information

Maybe you can also use AUDIT ALTER USER; to get this information. See AUDIT


Page Splits on an empty table I have a monitoring tool that alerted me to high page splits, 50% of new allocations. After some Google Fu, I found the following query to look and see:

SELECT IOS.index_id,
       O.NAME                       AS OBJECT_NAME,
       I.NAME                       AS INDEX_NAME,
       IOS.leaf_allocation_count    AS PAGE_SPLIT_FOR_INDEX,
       IOS.nonleaf_allocation_count PAGE_ALLOCATION_CAUSED_BY_PAGESPLIT
FROM
<db>.sys.Dm_db_index_operational_stats(Db_id(N'DB_NAME'), NULL, NULL, NULL) IOS
JOIN <db>.sys.indexes I
  ON IOS.index_id = I.index_id
     AND IOS.object_id = I.object_id
JOIN <db>.sys.objects O
  ON IOS.object_id = O.object_id
WHERE  o.NAME NOT LIKE 'sys%'
       AND i.NAME IS NOT NULL
       AND IOS.leaf_allocation_count != 0
       AND IOS.nonleaf_allocation_count != 0
ORDER  BY IOS.leaf_allocation_count DESC

The results showed that one index in particular was high in page splits/allocations caused by page splits. This table is empty.

Forgive my ignorance but how can an empty table cause page splits? There are no inserts, updates or deletes of the table.

SQL Server 9.0.5057

Any thoughts or direction would be appreciated.

if DB_ID(N'DB_NAME') returns null you will get object_id's from all databases.  Try running from the target database:

use db_name

SELECT IOS.index_id,
       O.NAME                       AS OBJECT_NAME,
       I.NAME                       AS INDEX_NAME,
       IOS.leaf_allocation_count    AS PAGE_SPLIT_FOR_INDEX,
       IOS.nonleaf_allocation_count PAGE_ALLOCATION_CAUSED_BY_PAGESPLIT
FROM
sys.dm_db_index_operational_stats(Db_id(), NULL, NULL, NULL) IOS
JOIN sys.indexes I
  ON IOS.index_id = I.index_id
     AND IOS.object_id = I.object_id
JOIN sys.objects O
  ON IOS.object_id = O.object_id
WHERE  o.NAME NOT LIKE 'sys%'
       AND i.NAME IS NOT NULL
       AND IOS.leaf_allocation_count != 0
       AND IOS.nonleaf_allocation_count != 0
ORDER  BY IOS.leaf_allocation_count DESC


Backup too big to restore I have a backup which in file explorer is roughly 160GB.

If I run

select sum((size*8)/1024) From sys.database_files where type=0

I get a result of 169493, fine.

When I try to restore this database to another server (the old DBA set up a job which restores the database automatically each morning from the latest backup, then runs DBCC CHECKDB on it) it fails. If i try RESTORE VERIFYONLY it turns out I need 288GB in order for this to work. Why? The backup is not compressed.

Thanks

From your question, it sounds like the restore has been working daily, for some time.  As of today it suddenly stopped working.

Yesterday it restored fine with a restored size of around 160GB today it needs 288GB to restore.

One of two things probably happened (or a combination of both)

The data files (mdf.ndf.) grew a bunch either from lots of new data coming in, or from someone increasing the file size.
The log files grew a bunch, probably because of an auto grow when someone ran a big ugly query.

You will need to look at the source database to find out what is bigger than it was yesterday. Then you will need to figure out why it grew. Then you will need to make a decision with the business on if the source should be shrunk back down, or if the secondary server needs to have more space added to it.


Is this work flow suitable for Amazon RDS? I am considering to set up a MySQL database via amazon web services (AWS). Consider the following steps which characterize my workflow:

There is a daily data inflow into several AWS EC2 instances
On each EC2 instance, incoming data is stored in a RData file (tabular format)
RData files from all EC2 instances should be exported as tables to a central database (file size is relatively small, less than 10 MB per file)
Using R/RStudio, data cleaning and data aggregation routines need to be performed on the central database
All steps must be automated via cron jobs

Steps 3 and 4 are my main concern.

Is this a standard work flow which can be integrated easily with Amazon Relational Database Service (RDS)?

Or should I consider a different approach (for example, running the SQL database on a separate EC2)?

The difference in RDS and MySQL on EC2 is that RDS is a managed deployment of MySQL (you can't change server settings) but not vanilla MySQL either.

So if you do not need to alter MySQL's server settings, RDS would be fine. Mostly due to it being EBS backed (Elastic Block Storage) EC2 has ephemeral storage (not persistent) unless you install it with an EBS backed volume (for all your data files) which is a best practice.

if you need to process the data in some other way, rather than just transfer and store it you will need a EC2 instance anyway. So, you could do it all on on EC2 instance, or separate the concerns.

As far as the 10MB data transfers you will be fine and potentially all free on Amazon's network, although if you start using their other services for data processing in and out of their network, make sure to have all your resources in a Availability Zone together, as data transfer costs go up from free to $/GB->TB a month you may be transferring across the wire.

Check their free tier out, you could possibly do all this for 12 months and never spend a dime.

EDIT:
To correct myself slightly, you can't change ALL the server variables but, you can change some of the more important ones still on RDS.
RDS Server Variables Article

Good luck.


MySQL, operation DELETE I have:

DELETE FROM songs
WHERE songs.id_album = album.id
AND album.id_band =16`

  Error: #1054 - Unbekanntes Tabellenfeld 'album.id' in where clause.

Table album exists, field id too, 100%.

Where is error?

Thank you.

You can't use the album table because it isn't actually included in the query. (in the FROM clause). You need to do this instead:

DELETE songs.*
FROM songs
  JOIN album
    ON album.id = songs.id_album
 WHERE album.id_band = 16

See the section titled Multi-Table Deletes in the documentation for DELETE.

Alternatively, you could use WHERE EXISTS:

DELETE FROM songs
 WHERE EXISTS (SELECT *
                 FROM album
                WHERE id_band = 16
                  AND id = songs.id_album)


Why would I not run "index reorganization" regularly? I tried index reorganization queries several times so far. They look like this:

ALTER INDEX ALL ON TableName REORGANIZE

Such query may take hours but it looks like it doesn't interfere with other database operations. For some table its effect is next to nothing but for some tables it actually saves like 15% of overall database space.

What if I just run this query for all indices every week or so? Is there any reason not to do so?

Reorganizing an Index should be done when you have elevated amounts of white space within your index (i.e. avg_page_space_used_in_percent column in the sys.dm_db_index_physical_stats DMV).  Many people (and even solutions) in the SQL Server community say you need to look at Index Fragmentation levels (e.g. the avg_fragmentation_in_percent in that same DMV), but this is not as much a concern depending on the type of underlying storage (e.g. anything that's RAIDed fragments your data for redundancy purposes regardless).  Others also have come to the same conclusion.

The downsides to Reorganizing your Indexes when not needed is that this operation will purge portions of your cache so it can have sufficient room to perform the reorg within memory. Reorgs aren't as destructive to your cache as Rebuild operations, but they will reduce your Page Life Expectancy (PLE) and potentially purge useful data that you will have to re-read from disk (e.g. increased I/O operations).  Additionally, Reorg operations do NOT update Index Statistics.  Updated Statistics do more to produce optimal executions plans than defragmented indexes, so if you're going to mindlessly run a nightly operation I would say Updating Stats is the better choice over Reorging instances.

The right answer though is to have a solution check your White Space thresholds and reorg/rebuild indexes when needed.  Have another process check your statistics and update when needed.  There are a few solutions out there, such as Ola's maintenance solution, Minionware's Reindex solution, etc.  But I often find myself customizing solutions to fit my needs.


syntax error at or near TO (ROLLBACK TO SAVEPOINT) The following sql is a part from a FUNCTION ,i want to rollback to rbk SAVEPOINT :

  for ptrEnt in entite loop
      -- positionnement du point de retour pour une entitÃƒÂ©
    savepoint rbk;
      -- purge de la table des redevances de l''entite
    test := pckg_activ.initredevanceentite(ptrEnt.cod_ent);
      -- calcul de la redevance
      test := pckg_activ.calculredevanceentite(ptrEnt.cod_ent);
    -- controle calcul de la bonne execution pour l''entitÃƒÂ©
    if (test = 0) then
        -- initialisation des departements associes a une entite
        test := pckg_activ.initdepartement(ptrEnt.cod_ent);
      -- validation des modifications
      commit;
      -- generation de l''etat recapitulatif
      test := pckg_activ.recapentitegestionnaire(ptrEnt.cod_ent,ficHisto);
    else
        rollback TO SAVEPOINT rbk;
      report := jour + 7;
      update sav_rdv_date
          set date_dem = report
          where cod_ent = ptrEnt.cod_ent;
        RAISE NOTICE '!!erreur de patrimoine => abandon du traitement pour cette entite';
      commit;
    end if;
  end loop;

entite is a cursor that has been declared and the for loop is in a BEGIN bloc.

I get the error ERROR:  syntax error at or near "TO". I think that the syntax is correct , it it related to the cursor ?

You cannot use transaction management statements in PostgreSQL functions.

Limited support for that was added in procedures, introduced in v11.

But you can (implicitly) use savepoints by using an EXCEPTION clause in a block.

So instead of

LOOP
   SAVEPOINT a;

   /* something */

   IF /* something else */ THEN
      ROLLBACK TO SAVEPOINT a;
      /* still more */
   END IF;
END LOOP;

you should use

LOOP
   BEGIN
      /* something */

      IF /* something else */ THEN
         RAISE EXCEPTION 'catchme';
      END IF;
   EXCEPTION
      WHEN raise_exception THEN
         /* still more */
   END;
END LOOP;


Selecting 'edge' records in a sequence of data I have a table of items which run sequentially by date with both a start and end for each entry:

ID | Start            | End
----------------------------------------
1  | 2012-08-20 00:00 | 2012-08-20 23:59
2  | 2012-08-21 00:00 | 2012-08-21 23:59
3  | 2012-08-22 00:00 | 2012-08-22 23:59
4  | 2012-08-23 00:00 | 2012-08-23 23:59
5  | 2012-08-24 00:00 | 2012-08-24 23:59
6  | 2012-08-28 00:00 | 2012-08-28 23:59
7  | 2012-08-29 00:00 | 2012-08-29 23:59
8  | 2012-08-30 00:00 | 2012-08-30 23:59

Note that there are gaps in the data (for a weekend and a bank holiday).  Is it possible to write a query which selects the records at the edges of the gaps?  What I want is a general purpose way to end up with something like this:

ID | Start            | End
----------------------------------------
1  | 2012-08-20 09:00 | 2012-08-20 23:59
2  | 2012-08-21 00:00 | 2012-08-21 23:59
3  | 2012-08-22 00:00 | 2012-08-22 23:59
4  | 2012-08-23 00:00 | 2012-08-23 23:59
5  | 2012-08-24 00:00 | 2012-08-24 17:00
6  | 2012-08-28 09:00 | 2012-08-28 23:59
7  | 2012-08-29 00:00 | 2012-08-29 23:59
8  | 2012-08-30 00:00 | 2012-08-30 17:00

Note that the start time of 1 and 6 has been updated, as has the end time of 5 and 8.

In the real database I am forced to used this day by day data layout as it has to retain compatibility with a legacy client, so I can't simply insert the ranges directly.  Also in the real data I could be dealing with several hundred of these records at a time with multiple gaps.

The target platform is SQL2005/2008, though bonus points for solutions which work in SQL2000 compatibility mode.

You can use outer apply the get the lead and lag values and in the column list you can use datediff to see if the gap is something other than one day.

select T1.ID,
       case
         when datediff(day, Lag.Start, T1.Start) = 1 then T1.Start
         else dateadd(hour, 9, T1.Start)
       end as Start,
       case
         when datediff(day, T1.Start, Lead.Start) = 1 then T1.[End]
         else dateadd(hour, 17, T1.Start)
       end as [End]
from YourTable as T1
  outer apply
    (
      select top(1) T2.Start
      from YourTable as T2
      where T2.Start < T1.Start
      order by T2.Start desc
    ) as Lag(Start)
  outer apply
    (
      select top(1) T2.Start
      from YourTable as T2
      where T2.Start > T1.Start
      order by T2.Start
    ) as Lead(Start)

The query above works from SQL Server 2005 even with compatibility SQL Server 2000 (80).

If you find your way to SQL Server 2012 you can use lead and lag instead of simulating with an outer apply.

select T1.ID,
       case
         when datediff(day, lag(T1.Start) over(order by T1.Start), T1.Start) = 1
         then T1.Start
         else dateadd(hour, 9, T1.Start)
       end as Start,
       case
         when datediff(day, T1.Start, lead(T1.Start) over(order by T1.Start)) = 1
         then T1.[End]
         else dateadd(hour, 17, T1.Start)
       end as [End]
from YourTable as T1

SQL Fiddle


Which should be used after messing up clustered index? Update statistics, Reindex or Reorganize If I have a clustered single column index, e.g FooId in SQL Server 2008, 2012 and I know I have messed up the order of the rows to not have sequential order of the FooId column anymore, which command/s should be used:

Update statistics
Rebuild
Reorganize

EDIT

Scenario
FooId is a clustered pk of type sequential uniqueidentifier, but for keeping it simple it this post we use #1 as id values.

#1 inserted

#2 inserted

#3 inserted

#1 selected

#1 deleted

#2 selected

#2 deleted

#2 inserted (that is reusing the clustered PK of #1)

#1 inserted (that is reusing the clustered PK of #1)

Isn't this going to break the sequential order? Will it not be stored like this now:

#3

#2

#1

//Daniel

You may have misunderstood the way a clustered index works - by definition the data is logically ordered in the sequence of the clustering key, although it's possible for the clustered index to become physically fragmented as page-splits occur when new data is inserted.

If you want to reduce fragmentation either REBUILD or REORGANIZE can be appropriate.
The rule of thumb is to do nothing when fragmentation is below 5%, REORGANIZE when fragmentation is between 5% and 30%, and REBUILD when fragmentation is above 30%.

Index fragmentation is recorded in the DMV sys.dm_db_index_physical_stats. See also the MSDN page on REBUILD/REORGANIZE

It's worth noting that unless you are running SQL Server Enterprise edition, REBUILD cannot be performed on-line - meaning that your table will be inaccessible for the duration of the command. REORGANIZE is always carried out on-line.


How to set a search_path default on a psql cmd execution? I'm looking for an option to create an alias command for a psql console connection which allowed setting the search_path, but I am not finding any option on psql util, nor the option to execute a command without exiting. any idea?

I would like to avoid preconditions as setting environment options and have a oneliner if possible

I finally found it, its not a psql option, but it fits a oneliner:
PGOPTIONS=--search_path=myschema psql -h myHost -U myUser -dmyDb -p myPort.

If anybody improves it with a working version call for both unix and windows ill approve


How to enable "only" drop partition DDL for Oracle Golden Gate? I'm struggling with how to do this, and hoping somebody could assist with some ideas.
Oracle Golden Gate - general question. Version shouldn't be specific, however, I am currently running on:
Golden Gate: 12.2.0.1.170919
Oracle DB: 12.1.0.2.0
Using Golden Gate, Integrated Extract (and Replicat).
I have setup a test table using Interval partitioning (by month).
I have setup a GG flow to capture the data/rows for this table.
This works perfectly, no issues.
I then try to setup/enable DDL replication for this table.
I add the following to the extract .prm file:
DDL INCLUDE OBJNAME myschema.*

and that works perfectly. It captures ALL ddl ..
ALTER TABLE
TRUNCATE TABLE
CREATE INDEX
... etc.

Now, I want to restrict the DDL so that it captures ONLY :
ALTER TABLE myschema.* DROP PARTITION ...

that's it ... no TRUNCATEs, no DROPs, no other ALTER TABLE
ie ALTER TABLE myschema.mytable MODIFY ( col ....
don't want to capture that - for example.
I've tried some of the following .. all to no avail:
DDL INCLUDE OBJNAME myschema.*   &
    INCLUDE OPTYPE DROP          &
    INCLUDE OBJTYPE 'PARTITION'

this still allows ALL DDL
DDL INCLUDE OBJNAME myschema.*   &
    INCLUDE OPTYPE DROP          &
    INCLUDE OBJTYPE 'PARTITION'  &
    EXCLUDE ALL

This doesn't allow ANY DDL at all ..
DDL INCLUDE OBJNAME myschema.*   &
    INCLUDE OPTYPE DROP          &
    INCLUDE OBJTYPE 'PARTITION'  &
    EXCLUDE OPTYPE TRUNCATE      &
    EXCLUDE OPTYPE CREATE

That manages to prevent TRUNCATEs and CREATEs ..
however, I can't add "ALTER" or "DROP" .. as it starts affecting/preventing the DROP PARTITION clause.
I've opened a ticket with Oracle to get some feedback from them .. however, so far, not much luck there.
I was hoping somebody here might actually know how to do this ?
Thanks!

Ok, so worked out a solution with Oracle via the SR .. wanted to put the solution here for completeness.
The solution was to use following DDL commmand:
DDL INCLUDE MAPPED INSTR 'DROP PARTITION'


Why am I getting the error "non-boolean type specified in a context where a condition is expected" for this request? I am getting this error :
"Error 102 : non-boolean type specified in a context where a condition is expected"
for this request :

DECLARE @num_dossiers TABLE (num_dossier INT,indice NVARCHAR(3))
insert into @num_dossiers
select num_dossier,indice from dossier where num_sec=57

delete from constitue where (num_dossier,indice) in select (num_dossier,indice) from @num_dossiers

The request triggering the error is the last one :

delete from constitue where (num_dossier,indice) in select (num_dossier,indice) from @num_dossiers

What could be the cause of this error ?

SQL Server doesn't support a multi-column IN clause (unlike Oracle).

delete
from constitue
where (num_dossier,indice) in select (num_dossier,indice) from @num_dossiers

This can be rewritten as a EXISTS:

DELETE c
FROM constitue c
WHERE EXISTS
    (SELECT 1 FROM @num_dossiers nd WHERE n.num_dossier = c.num_dossier AND n.indice = c.indice);


PostgreSQL: Creation date of a table I've recently finished a project, during which many DB tables were created.

Most of these tables contain temporary garbage, and I am looking for a simple way to list all these tables.

Is there a way to list all DB tables sorted according to their creation date?

Found this on Stackoverflow:  https://stackoverflow.com/questions/2577168/postgresql-table-creation-time


One table or several tables? I have several types of writings: articles, projects, reviews, etc.

I have a table containing paragraphs (fields id (INT) and partext (TEXT)).

Each writing consists of several (ordered) paragraphs. So a description of a writing is a table consisting of fields id, seq (sequential number of the paragraph in the writing), par (the ID of the paragraph).

My question: Should I create a table for each kind of writings (individual tables for articles, projects, reviews, etc.) or create one table with ENUM field specifying whether it is an article, project, review, etc.?

Create one table to house all of the writing types, and use a foreign key relationship to a type table.

The most dominant reason to do this is because what happens when you want to add another writing type (say, "dictionary")?  You'd have to add another table, and most likely alter application code.

Not fun, and a bad design.  Go with one table and a type field.


Delete corrupt empty filegroup We have a large database (1.8TB) with eight filegroups created on SQL 2000 and now running on SQL Server 2005 9.0.5057 since early 2012. Simple recovery.

On September 27, we got a torn page error in one of the filegroups and error 824. The vendor of the third-party app that uses this database asked us to restore that filegroup to a new database, which I did over that weekend. The vendor created a new filegroup and copied everything from the restored filegroup to repair the production database. Everything seems fine operationally, but we still have the corruption in the old empty filegroup.

CHECKDB found 254 allocation errors and 0 consistency errors.

There are also 9 page errors like this:
Msg 8906, Level 16, State 1, Server ANNIE, Line 1
Page (5:4114) in database ID 5 is allocated in the SGAM (5:3) and PFS
(5:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT
ALLOCATED   0_PCT_FULL'.

Only one of those 9 pages is listed in suspect_pages.

I ran DBCC PAGE on the 9 pages in the CHECKDB output. 8 pages are type 2 and 1 is type 10. All list object ID as m_objId (AllocUnitId.idObj) = 41899 and OBJECT_NAME(41899) returns null.

Also all the errors are on file_id 5, which is the filegroup that is now empty.

It seems like if we could delete the corrupt filegroup, we would be OK, but SHRINKFILE and REMOVE FILE fail because of the torn page error.

Is there any other way to delete the empty filegroup?

Thanks for any advice!

Make sure the file group is actually empty by looking at

SELECT *
FROM sys.allocation_units AS AU
JOIN sys.filegroups AS F
ON AU.data_space_id = F.data_space_id
ORDER BY F.name;

If it is empty:

1) Try one of the DBCC CHECKDB repair options (REPAIR_FAST or REPAIR_REBUILD). If you are sure that there is no corruption of any type in any other file it should even be safe to do a "REPAIR_ALLOW_DATA_LOSS" but make sure you have a good backup when using that last one. (It is a very good idea to have a backup anyway). Save all the output you get for later analysis. If any repair was successful empty the files as before.

2) If that did not help you should be able to just drop the files: http://technet.microsoft.com/en-us/library/ms175122.aspx
However, that might leave the database in some kind of "funky" state (see above link), so I would try 1) first.


SQL query output on one line Sorry, I'm not a expert in scripting,

I need to create a query in MS SQL Server to get db and db log size on one row. I am using PRTG to monitor 4 SQL servers and i need to know if there's any growth in the database and logs, eater in percentage or in bites,

I have found this new script that is perfect, it gives me all the info i need then i can set thresholds on the outputs,  the problem is PRTG can only read the info form one row, so the only info that shows up in PRTG is the database info, i also need to get the log size.

I have found this script that gives me all the info i need and i would like to send the output to one row if possible.

USE [maccadk]
GO
SELECT
    [TYPE] = A.TYPE_DESC
    ,[FILE_Name] = A.name
    ,[FILEGROUP_NAME] = fg.name
    ,[File_Location] = A.PHYSICAL_NAME
    ,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0)
    ,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0))
    ,[FREESPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)
    ,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)
    ,[AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -'
        WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END
        + CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted'
            ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END
        + CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]' ELSE '' END
FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id
order by A.TYPE desc, A.NAME;

Thanks
Gilles

Turning rows into columns known as a PIVOT operation. There are several ways to implement this operation, including a built-in expression. The simplest to understand, and most flexible, I find to be the CASE construct.

I'll use an in-line CTE to wrap your query. There's no magic here, it's just a convenient way of using your existing code. I've added column type_desc to help distinguish the source of each amount. The built-in function DB_NAME() is used to give a "key" value to the output.

;with YourData as
(
    select
        DBName = DB_NAME(),
        type_desc,
        [Name],
        physical_name [Path],
        CAST(size AS BIGINT)*8192 [TotalBytes],
        CAST(FILEPROPERTY(name,'SpaceUsed') AS BIGINT)*8192 [UsedBytes],
        (case when max_size<0
             then -1
             else CAST(max_size AS BIGINT)*8192 end) [MaxBytes]
    from sys.database_files
)
select
    DBName,
    RowsTotalBytes = sum(case when type_desc = 'ROWS' then TotalBytes end),
    LogTotalBytes  = sum(case when type_desc = 'LOG' then TotalBytes end)
from YourData
    group by DBName;

The values we want occur in your query as two rows in a single column. The CASE expressions separate them into individual columns, with a NULL in the other column, still in two rows. By grouping I collapse the two rows into as single row. Hence the purpose for introducing DB_NAME().

Using SUM() serves two purposes: it returns a single value for all files of a type and ignores the NULL introduced for the other type_desc.

If you have multiple data or log files and want a separate output value for each use Name in the when instead of type_desc.


SQL Server 2012: set a column to only use a single case? Is there a way to tell SQL Server 2012 to store both A and a as A (or both A and a as a) for some given column such that every insert statement does not need to have UPPER() in it?

You can do this silently using an INSTEAD OF trigger:

CREATE TRIGGER dbo.tr_table_name
  ON dbo.table_name
  INSTEAD OF INSERT
AS
BEGIN
  SET NOCOUNT ON;
  INSERT dbo.table_name(col1, col2, col_you_care_about)
    SELECT col1, col2, UPPER(col_you_care_about)
    FROM inserted;
END
GO

If you want to raise an error, you can use a CHECK constraint:

CREATE TABLE dbo.blart(a VARCHAR(32));

ALTER TABLE dbo.blart ADD CONSTRAINT ck_col
  CHECK (CONVERT(VARBINARY(32), a) = CONVERT(VARBINARY(32),UPPER(a)));
GO

INSERT dbo.blart(a) VALUES('A'); -- succeeds
GO

INSERT dbo.blart(a) VALUES('a'); -- fails

Result:

  Msg 547, Level 16, State 0
  The INSERT statement conflicted with the CHECK constraint "ck_col". The conflict occurred in database "dba", table "dbo.blart", column 'a'.

But I have to agree with @swasheck above - what difference does it make how it's stored? If you just want it displayed in upper case, do that at rendering time, not storage time.


Usage of NON Clustered Index in SQL Server I have 2 Index:

CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[TableA]
(
    [Column1] ASC,
    [Column2] ASC
)

CREATE NONCLUSTERED INDEX [IX_2] ON [dbo].[TableA]
(
    [Column3] asc
)

Now I have 2 update:

Update TableA SET Column3='' where Column1=''

the above Update was using Index 1st and then Key lookup to clustered Index

Update TableA SET Column3='' where Column3=''

This update  was using  2nd index

Based on the Update I thought I can delete 2nd index and update the first one with

CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[TableA]
(
  [Column1] ASC,
  [Column2] ASC
)
INCLUDE(Column3 asc)

So with the updated index the First Update works fine,it uses the updated Non Clustered Index , but for second update it ask me to create a non clustered index on column3.

Then I tried

CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[TableA]
(
  [Column1] ASC,
  [Column2] ASC,
  [Column3] asc
)

But still for second update it ask me to create a new non clustered index.

But why it ask me to create new, because what I understand is Column1, Column2, Column3 will be present in Root level for non clustered Index

So with the updated index the First Update works fine,it uses the updated Non Clustered Index , but for second update it ask me to create a non clustered index on column3.

The updates will always work fine. Is this an update query you run very frequently?
Because in general (extra) non clustered indexes slow down inserts.
All your data is in the Clustered Index (CIX). The Non-Clustered Indexes (NCIX) are a copy of your Clustered Index, but in a order you specify generally in a different manner than your Clustered Index.

Update TableA SET Column3='' where Column1=''
the above Update was using Index 1st and then Key lookup to clustered Index
Update TableA SET Column3='' where Column3=''
This update was using 2nd index

In the first query, your server has to look where "Column1=''". The most quick way to do that is to use the NCIX that is sorted on Column1 and than look up the record in the CIX (that is probably not).
In your second query, it uses the other index because it is sorted on Column3

Based on the Update I thought I can delete 2nd index and update the first one with

Unfortunately no, when you include the column it is not used in sorting.

CREATE NONCLUSTERED INDEX [IX_1] ON [dbo].[TableA]
(
[Column1] ASC,
[Column2] ASC,
[Column3] asc
)

In this NCIX it is sorted on last, so it is probably not sorted well enough for the server to quickly find the record.
Look at this example where we sort on Column1, Column2, Column3:
Product      | Category   | Price
-----------------------------------
Apple        | Fruit      | 1.10
Apple pie    | Pastry     | 5.00
Apple Iphone | Electronic | 899.00
Apple Watch  | Electronic | 459.00
Bananas      | Fruit      | 1.50
Pear         | Fruit      | 1.25
Pineapple    | Fruit      | 2.00

See how the price is all over the place? and even the second column in the "index" isn't "sorted"?
The server has to scan the whole index/table to find the right price.
That's why it recommends a new Index on just Column3. But in my honest opinion, unless you run this query frequently do not create an index for this, more indexes make inserts and updates take longer (because they all need the latest values).


Problems updating with inner join with multiple on statements I have an orders table on a database that contains geographical information about the city and state that the order is from.  This includes latitude and longitude for doing haversine calculations.

I have a new geo-location database containing the latitude and longitude of said city states that differs slightly from my previous database.  This introduces some odd behavior because when I run haversine calculations, mileage will not be calculated correctly due to the slight differences in latitude and longitude from the previous database.

What I need to do is update the latitude and longitude on the orders table to match what occurs in the geo-location database.  This is based off the city and state that is on the order.  I am trying to update my orders table like so:

update ProcurementPortal.orders as orders
inner join
ProcurementPortal.cities_extended as geo
on orders.city = geo.city
and orders.state = geo.state_code
set
orders.lat = geo.latitude,
orders.lon = geo.longitude
where
orders.city = geo.city
and
orders.state = geo.state_code

The problem I am having is that MysqlWorkbench gives me the following error.

  Error Code: 2013. Lost connection to MySQL server during query

Is the syntax I provided correct?  Would it update the orders how I expect it to?  I can run this from the command line, but it runs for a long time and I'm not sure it's really doing what it's supposed to be doing.

I'm fairly new to mysql, so some clarification on how I could accomplish this would be wonderful.

Output of SHOW CREATE TABLE orders - note there are some garbage columns on this.  Needs a lot of cleaning up.  The city / state column I refer to in posted query is oCity and oState:

CREATE TABLE `orders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned DEFAULT NULL,
  `company_id` int(10) unsigned DEFAULT NULL,
  `action_menu` text COLLATE utf8_unicode_ci,
  `oCity` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `oState` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  -- many other columns
  `latitude` double(8,2) DEFAULT NULL,
  `longitude` double(8,2) DEFAULT NULL,
  -- more columns
  PRIMARY KEY (`id`),
  KEY `orders_company_id_foreign` (`company_id`),
  CONSTRAINT `orders_company_id_foreign`
    FOREIGN KEY (`company_id`)
    REFERENCES `companies` (`id`) ON
    DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=70120 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Output of SHOW CREATE TABLE cities_extended:

CREATE TABLE `cities_extended` (
  `city` varchar(50) NOT NULL,
  `state_code` char(2) NOT NULL,
  `zip` int(5) unsigned zerofill NOT NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `county` varchar(50) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Running the query in question from the command line runs for a while, then returns the following error:

  ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

^ this might be due to the process still running.  I killed the process and it's actually going through now.  Just waiting to see if I get the same 2013 error.

First, you have conditions duplicated in the ON and the WHERE clause. That is not needed, the condition are only needed once, preferably in the ON clause as they are used for the join of the two tables. But that is not what's causing the slow execution (and the lost connections).

The reasons for being slow are three:

you are joining a 63K with a 363K table without any index to help. That is the major issue.
the columns used in the join do not have same type, length, character set and collation. This is another major issue, worse than the first. Even if you add indexes, but keep the same types, the indexes will not be of much help. More seriously, it's a question if you can / are allowed to change the types or the applications depend on the existing types.
you are updating potentially the whole table. This is rather minor as the table is small, only 63K rows. We'll see if that is still a problem, after we fix the major issues.

The first thing that needs addressing is the types (+ lengths + charsets + collations) of the two columns. Assuming that you are allowed to change the types of the table order, you should change the types (both varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL) to match the types of the geo table (one varchar(50), the other char(2), both CHARSET=latin1, unknown/default collation.)

This will be tricky and perhaps error prone, so I suggest you first take a backup of the whole database, or just the table orders, before you attempt to change anything.

If I were you, I'd then first (before changing anything), check the data in these two columns and whether their maximum length is less than 50 and 2 respectively, to be sure that the truncation of the length will not change any valuable data.

I'd also check the collation of the cities_extended.city and cities_extended.state_code columns, it's probably the default latin1_swedish_ci.

(Of course these two steps above can be skipped - but only if you don't really care about the orders table and you can recreate it from scratch if needed.)

After everything is double checked, we can proceed with the actual changing of the types:

ALTER TABLE ProcurementPortal.orders
    MODIFY COLUMN oCity
        varchar(50)
        CHARSET 'latin1'
        -- COLLATE 'latin1_swedish_ci'
        DEFAULT NULL,
    MODIFY COLUMN oState
        char(2)
        CHARSET 'latin1'
        -- COLLATE 'latin1_swedish_ci'
        DEFAULT NULL ;

(the COLLATE 'latin1_swedish_ci' are probably not needed, the defaults will be used, so they are commented out.)

If that goes well and there are no errors, we can proceed with the final step, which is to add the indexes:

Add two indexes, one on each table:

ALTER TABLE ProcurementPortal.orders
   ADD INDEX state_city_ix
       (oState, oCity) ;

ALTER TABLE ProcurementPortal.cities_extended
   ADD INDEX state_city_ix
       (state_code, city) ;

After that (it will take a while but you only need to do this once), you can proceed with the update statement.

If it's still taking too long, I'd consider making the update in batches (of say 1K-5K rows per time). But with so small tables, I don't think you need that.


After SSRS to PowerBI Reporting Server migration, parameters are rendering goofy when trying to enter data We just successfully migrated a SSRS 2016 instance to PBIRS. Users are reporting weird behavior when trying to enter data in a multi value parameter.

When they click on a parameter to enter in data, normally a drop down text entry appears and they type in multiple values. That still happens, but the box goes renders to the bottom right of the screen and you only see that box on the screen (see picture) This happens in all web browsers.

EDIT: It appears that the box is showing where the parameter is supposed to show up if the report is full screen. I cannot see all of the parameters like we used to in SSRS

In case anyone views this a few years later, the issue was with a PowerBI Report Server update. They released a hotfix and things were fixed.


How can I use a full-text search on a jsonb column with Postgres? So i have a jsonb column that has entries like this: https://pastebin.com/LxJ8rKk4

Is there any way to implement a full-text search on the entire jsonb column?

PostgreSQL 10+

PostgreSQL 10 introduces Full Text Search on JSONB

CREATE INDEX ON table
   USING gin ( to_tsvector('english',jsondata) );

The new FTS indexing on JSON works with phrase search and skips over both the JSON-markup and keys.


Get first and last record of a selection without using min() max() My energy-meter is writing the (accumulated) consumtion every minute to a db. In order to get my todays consumption I use:
SELECT
  (MAX(energy_kwh) - MIN(energy_kwh)) AS kwh_today
FROM logging.main_meter
WHERE DATE_FORMAT(strtime, '%Y-%m-%d') = CURDATE();

Problem is: max() and min() appear to slow the query down a lot. I added a key to column energy_kwh but it didn't help. I bet there is a better way to get the first and last record for the calculation.
The table (its actually much bigger, ~130 columns, so I reduced it to the relevant part):
CREATE TABLE 'main_meter`
(
  id int(11) NOT NULL AUTO_INCREMENT,
  timestamp int(11) DEFAULT NULL,
  strtime datetime DEFAULT NULL,
  energy_kwh double unsigned DEFAULT '0',
) ENGINE=InnoDB AUTO_INCREMENT=1717655 DEFAULT CHARSET=latin1;

as it turned out, it wasn't the MIN()/MAX() functions that slowed the query down, but the
WHERE DATE_FORMAT(strtime, '%Y-%m-%d') = CURDATE()

statement. Since the table has a column with the unix-timestap, using this instead accelerates it a lot:
WHERE TIMESTAMP > UNIX_TIMESTAMP(CURDATE())

Query-time from ~3.6s down to ~0.02s. I call that a good improvement.


Single collection for multiple keys I built a little setup with a raspberry pi and a temperature and humidity sensor. Every 5 minutes it reads sensor values and sends them both + a timestamp and a custom ID (there are multiple pis, each with its own ID) to a nodejs API which then stores them in a single mongodb collection. A GET request to this API then returns all objects from the collection with the id provided via GET.

Here is a json sample object of the data structure from my collection:

{
    "_id": "5c935a046b4fcc27f2a81cd8",
    "id": "livingroom",
    "time": "2019-03-21 10:31:48.601374",
    "temp": "22.0",
    "hum": "46.0"
}

Im pretty new to mongodb and databases in general but my programming experience tells me that just dumping everything into a single collection is not a good way to handle my data (propably, I really don't know).

So my question is: Are there any big gotchas of me handling my data like this or am I fine for this projects scope? Do I need to expect big performance hits if this collection gets too big?

The data you are generating from raspberry pi, temperature and humidity sensor is a time series data. MongoDB has documented some best practices and key tips for storing the time series data.

Time Series Data and MongoDB: Part 2 – Schema Design Best Practices

This explains the following schema patterns for time series data.

Scenario 1: One document per data point

Scenario 2: Time-based bucketing of one document per minute

Scenario 3: Size-based bucketing


Splitting table with many field for convenience example Is this good idea to split table with many field for on two parts for convenience?
Real example:
class UserData(Base):
    __tablename__ = "users_data"

        id = Column(Integer, primary_key=True, index=True)
        age = Column(Integer, index=True)
        first_name = Column(String, index=True)
        last_name = Column(String, index=True)
        country = Column(String, index=True)
        city = Column(String, index=True)
        comment = Column(String, index=True)
        users_meta = relationship("UserMeta", uselist=False, back_populates="users_data")

    class UserMeta(Base):
        __tablename__ = "users_meta"
        id = Column(Integer, primary_key=True)
        email = Column(String, unique=True, index=True)
        hashed_password = Column(String)
        created = Column(DateTime(timezone=True), server_default=func.now())
        last_visited = Column(DateTime(timezone=True))
        is_active = Column(Boolean, default=True)
        user_data_id = Column(Integer, ForeignKey('users_data.id'))
        users_data = relationship("UserData", back_populates="users_meta")

So in one table only id and data about user, the second table is a meta info.
Currently I'm don't know how many times and which fields I will use more than other.
A one more bonus question it is, is I need to create third table with correlation between email and id or I can do it without it?
P.S. I hope the data is pretty understandable to no translate in standard SQL format

There are a couple of issues with your proposed design.
Starting with the simplest issue:  The "age" column. Attributes like "age" are calculated values that depend on an underlying attribute. You must be storing the date somewhere else in the database.  I recommend that you compute the age in an SQL statement or make it into a virtual column.
Second, the facts in the user_meta table seem to mash several concepts together:  User authentication, account info, and contact information. Given that mix, I'm guessing you will have only one row per user--unless you allow users to create multiple login identities based on email address. If you have a one-to-one relationship, why are you splitting it into two tables?  Absent more information about your use case, I actually think you need 5 tables:  User identity, physical addresses, email addresses, account activity, and passwords.
Your proposed schema has poor information security. Passwords should be stored separately so you can limit access and implement other security measures.  Similarly, you probably want to limit access to email addresses.
So my recommendation is that you look at ALL your user attributes and see what the logical groupings are. Consider which sets of information have a one-to-one or many-to-one relationships. Consider the security aspects when making the groupings.


pg_timezone_names view is empty - can be recreated? For some strange reason pg_timezone_names view is empty. Is there a way to recreate it?

The pg_timezone_names view is populated when the server starts - it recursively reads the timezone files from <your Postgres dir>/share/timezone and they are used to provide data for the view.

Double check that the files exist (as they should in a full installation) and that the user you are running the database under has read permission on all of the directories and files under <your Postgres dir>/share/timezone.

You'll need to restart the RDBMS for changes to take effect.


When we are estimating the table size for mySQL table, why do we add primary key twice? When we are estimating the table size for mySQL table, why do we add primary key size twice?
I saw this post How to estimate/predict data size and index size of a table in MySQL , and I noticed that the size of primary key is already in the row size.  Why do we add the primary key size again?

why do we add primary key twice?

This is an effect of InnoDB index structure.

MySQL 8.0 Reference Manual  /  ...  /  Clustered and Secondary Indexes

  All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index.

In table structure shown in the thread there is 2 indices: primary (by ID) and secondary (by CountryCode). The PK expression value is included in both - explicitly in PK and implicitly in SI. It is described in RolandoMySQLDBA's answer.

If there were three indexes in the table, the size of the primary index would be counted three times. 4 indexes - 4 times. And so on...

If no PK / UK in the structure then InnoDB adds inner 6-byte record number as a PK, and you must add 6 * (1 + indices count) bytes to each record's indices storage size.


Steps to restore a SQL Server database to a point in time Time given lies in all three backups, full, diff ,log:

full - 12 midnight
diff - 4 am ( every 4 hours)
log  - 15 minutes interval

When:

time restore is 5:17
time restore is 5:14

How should I answer that?

Your question is bit unclear; you have not specified whether there was a failure and at which time the failure happened.

In general, restore steps are:

Restore the latest full backup with no recovery
Restore the latest diff backup with no recovery
Restore transaction log backups with no recovery, except the last transaction log backup which falls withing the recovery time and specify the stop at time.

i.e. in above scenario:

for 5:17

restore full backup that was taken at midnight with no recovery
the diff backup taken at 4am with norecovery
restore all t-log backups until 5.15 with no recovery
restore t-log backup taken at 5.30 with stop at 5.17 and with recovery

For 5:14, do step 1, do step 2, step 3 all t-log backups till 5am with no recovery.  Then restore t-log backup taken at 5.15 with stop at 5.14 with recovery.


MySQL table with multiple values in one field I'm building a database for a real estate company.

The real estate properties are managed by sales people using a CodeIgniter website.

How should I proceed with the database design for this table?  Fields such as address, location, price etc., are pretty straightforward, however there are some sections like Kitchen Appliances, 'Property Usage' etc., where the sales people can check multiple values for each field.

Furthermore, a property can have multiple people attached to it from the table people, such as owner, seller, property lawyer, etc.  Should I use one field for these people or create another table and normalize the bindings?

Is the best way to proceed just using one field and using serialized data or is there a better way for this?

You can keep one table for property and one master table for roles and them assign that role to a user in the user table and can keep one to many association between property and users by having separate table property_users


MySQL: Slow log; append CPU usage I have the MySQL slow log feature enabled: http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html

But sometimes the query_times are high simply due to high CPU load.

How can I append the current CPU load to each entry in the MySQL slow log (it writes to a file)?

While I don't know of any tool that provides this information specifically, Percona Server does have a setting (log_slow_verbosity=full) that enables extended statisitics in the slow log along with microsecond granularity.  The data does not explicitly include CPU utilization, but it does provide a number of additional statistics about the internals of MySQL and InnoDB that may help you determine whether or not the problem is CPU related or a result of poor optimization.

This is an example of the additional information in the slow log:

# Time: 120114  6:34:33
# User@Host: user[user] @  [10.10.10.10]
# Thread_id: 28313080  Schema: mydb  Last_errno: 0  Killed: 0
# Query_time: 0.588882  Lock_time: 0.000068  Rows_sent: 3  Rows_examined: 183839  Rows_affected: 0  Rows_read: 100
# Bytes_sent: 121  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0
# InnoDB_trx_id: 9903E4DB1
# QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No
# Filesort: No  Filesort_on_disk: No  Merge_passes: 0
#   InnoDB_IO_r_ops: 0  InnoDB_IO_r_bytes: 0  InnoDB_IO_r_wait: 0.000000
#   InnoDB_rec_lock_wait: 0.000000  InnoDB_queue_wait: 0.000000
#   InnoDB_pages_distinct: 11359

Also, pt-query-digest from the Percona Toolkit understands this additional information and will summarize the slow query log for you and produce output, per query, that looks like this:

# Query 1: 0.09 QPS, 0.07x concurrency, ID 0x20112A1CCDBDBA4E at byte 4337645
# Scores: Apdex = 1.00 [1.0], V/M = 0.01
# Query_time sparkline: |     ^  |
# Time range: 2012-01-14 06:34:33 to 08:51:57
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         63     775
# Exec time     59    539s   522ms   855ms   695ms   816ms    87ms   640ms
# Lock time     18    60ms    43us   498us    76us   103us    36us    66us
# Rows sent      1   3.15k       0      38    4.17   12.54    4.90    2.90
# Rows examine  22 135.93M 179.38k 179.90k 179.60k 174.27k       0 174.27k
# Rows affecte   0       0       0       0       0       0       0       0
# Rows read      0   3.43k       0     118    4.53   20.43   11.93    0.99
# Bytes sent     1 103.07k      82     576  136.18  246.02   63.85  118.34
# Merge passes   0       0       0       0       0       0       0       0
# Tmp tables     0       0       0       0       0       0       0       0
# Tmp disk tbl   0       0       0       0       0       0       0       0
# Tmp tbl size   0       0       0       0       0       0       0       0
# Query size     5 222.51k     294     294     294     294       0     294
# InnoDB:
# IO r bytes     0       0       0       0       0       0       0       0
# IO r ops       0       0       0       0       0       0       0       0
# IO r wait      0       0       0       0       0       0       0       0
# pages distin  63   8.44M  10.66k  12.72k  11.15k  11.34k  357.67  10.80k
# queue wait     0       0       0       0       0       0       0       0
# rec lock wai   0       0       0       0       0       0       0       0
# String:
# Databases    mydb
# Hosts
# InnoDB trxID 9903E4DB1 (1/0%), 9903E4E09 (1/0%)... 773 more
# Last errno   0
# Users        user
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `mydb` LIKE 'table'\G
#    SHOW CREATE TABLE `mydb`.`table`\G
# EXPLAIN /*!50100 PARTITIONS*/

...followed by the query itself.

I have blogged a bit about using pt-query-digest and other tools for finding performance bottlenecks (and promised more blogging, but haven't gotten around to it yet).

Another tool which may help you is pt-stalk and pt-collect.  This will allow you to set a threshold based on a running server variable, such as when the number of concurrently running threads jumps up (Threads_running) and then collect a wealth of data about the running system, including cpu, I/O, MySQL statistics, the running processlist, etc to give you the ability to perform a thorough post-mortem.

The additional statistics are only available in Percona Server, but these tools will all work with MySQL GA.


string or binary data would be truncated in table '******', column '******'. Truncated value: '******' One of our applications report on error message 2628:
string or binary data would be truncated in table '******', column '******'. Truncated value: '******'
instead of
String or binary data would be truncated in table 'mytable', column 'mycolumn'. Truncated value: 'myvalue'.
which I get with SSMS.
What setting do I need to change to get the full message?
I've tried to search for this, but all I get is that I need to set VERBOSE_TRUNCATION_WARNINGS to ON and compatibility_level to 150.
Which I've done ages ago.
The application has this error handler:
 try
     {
         ES.isWorking = true;
         Worker worker = new Worker(new DBConnection(Settings.ConnectionString));
         worker.DoWork();
         ES.isWorking = false;
     }
 catch (Exception ex)
     {
         ES.isWorking = false;
         this.eventLog.WriteEntry("In OnTimer exception ! message: " + ex.Message.ToString());
         errorHandler.HandleException(ex, "In OnTimer exception !", ErrorSeverities.Error);
     }

The errorHandler looks like this
Public Function HandleException(ByVal incommingEx As System.Exception,
                                   ByVal note As String,
                                   ByVal errorSeverity As ErrorSeverities,
                                   Optional ByVal sessionId As String = "",
                                   Optional ByVal applicationSource As String = "",
                                   Optional ByVal ignoreDB As Boolean = False,
                                   Optional ByVal noMail As Boolean = False) As Integer
    Dim stackTraceBuilder As New System.Text.StringBuilder
    Dim targetSite As String = ""
    Dim errorTime As DateTime = Now
    Dim messageBuilder As New System.Text.StringBuilder
    Dim innerEx As Exception = incommingEx.InnerException
    Dim errorId As Integer = 0
    Dim innerCount As Integer = 1
    Dim loggedToDb As Boolean = False
    Dim appSource As String = Me._applicationSource
    If applicationSource <> "" Then
        appSource = applicationSource
    End If
    '... more code'

    messageBuilder.AppendLine("(" + incommingEx.GetType.Name.ToUpper + ")" + vbCrLf + incommingEx.Message + " - SOURCE: " + incommingEx.Source)
    '...'
    While Not innerEx Is Nothing And innerCount <= 5
        messageBuilder.AppendLine("INNER" + Convert.ToString(innerCount) + " (" + innerEx.GetType.Name.ToUpper + "):" + vbCrLf + **innerEx.Message** + " - SOURCE: " + innerEx.Source)
        If Not innerEx Is Nothing AndAlso Not innerEx.StackTrace Is Nothing Then
            stackTraceBuilder.AppendLine("INNER" + Convert.ToString(innerCount) + ": " + innerEx.StackTrace) 'Me.GetNumberedStack(innerEx))
        End If
        innerEx = innerEx.InnerException
        innerCount += 1
    End While
    '... more code where the error message is inserted in a log table, via a sproc'
End Function

We have not seen this behavior before, but we have recently updated the .net framework to version 4.7.2, so it could be related.
the error text "string or binary" is comming from the bit that goes

innerEx.Message +
and that is a simple built-in Exception data type. So it is VB and/or .net and/or SQL Server that is building the string, not us.

The error comes after a call to a stored procedure, where the user has probably entered a varchar(1000) text. The stored procedure adds a bit more, and the table hasn't got room for all 1100 bytes. Hence the error.
The stored procedure does not have a try-catch block.

It looks like this table was defined with dynamic data masking, and the user that the application uses to access the database doesn't have permission to view masked data (which is good!).
This is why the behavior differs between the application and SSMS: I expect you're using a higher-privileged user when running the query from SSMS
Here's a demo:
CREATE TABLE dbo.Test
(
    Id int IDENTITY PRIMARY KEY,
    Filler varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL
);
GO

CREATE USER TestUser WITHOUT LOGIN;
GRANT SELECT ON dbo.Test TO TestUser;
GRANT INSERT ON dbo.Test TO TestUser;
GO

EXECUTE AS USER = 'TestUser';
INSERT dbo.Test
    (Filler)
VALUES
    (REPLICATE(N'A', 101));
REVERT;

The result of which is:
Msg 2628, Level 16, State 1, Line 12
String or binary data would be truncated in table '******', column '******'. Truncated value: '******'.

To get the full message in the application, you'll need to grant the user the UNMASK permission:
GRANT UNMASK TO TestUser;

Running the INSERT code again results in the full error text:
Msg 2628, Level 16, State 1, Line 14
String or binary data would be truncated in table 'tempdb.dbo.Test', column 'Filler'. Truncated value: 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'.

When using Azure SQL Database, Dynamic Data Masking can also be configured in the Azure Portal user interface.  You may want to check there (although as far as I can tell, masks set up this way still update the sys.columns metadata):

I would also check if there are other databases that are part of the same logical SQL Server instance in Azure - maybe there are some kind of strange, cross-database queries going on.


Left Join of a Subselect query very slow I'm using mysql 5.5. and I have three tables:

newsletter_newsitem: contains a row per news item
newsletter_newsitem_areas: a many-to-many table that relates a news item to a region (a news item can have many regions)
newsletter_geoarea: contains a row per region.

I want to get all the countries a news item have so I crafted the following query which take less than a second to execute:

SELECT nlni_areas.newsitem_id,
       ifnull(GROUP_CONCAT(nlga.name ORDER BY nlga.name
                         SEPARATOR '|'), 'Worldwide') as name
FROM  newsletter_geoarea as nlga
    INNER JOIN newsletter_newsitem_areas as nlni_areas
        ON nlni_areas.geoarea_id = nlga.id
WHERE nlga.type = 2
GROUP BY nlni_areas.newsitem_id

This query returns a news item id and a concatenated list of countries. (one row per news item). However, I want to LEFT JOIN the result of the query to the newsletter_newsitem table to extract more data from each news item so I crafted the following query:

SELECT
    nlni.id as id,
    opp_country.name as countries
FROM
    newsletter_newsitem as nlni
    LEFT JOIN (
        SELECT nlni_areas.newsitem_id,
               ifnull(GROUP_CONCAT(nlga.name ORDER BY nlga.name
                              SEPARATOR '|'), 'Worldwide') as name
        FROM  newsletter_geoarea as nlga
            INNER JOIN newsletter_newsitem_areas as nlni_areas
                ON nlni_areas.geoarea_id = nlga.id
        WHERE nlga.type = 2
        GROUP BY nlni_areas.newsitem_id
    ) as opp_country
        ON opp_country.newsitem_id = nlni.id
WHERE
    nlni.archive_id IS NULL
    AND nlni.id IS NOT NULL

This query turns out to be extremely slow (take more than 5 min). I'm joining by columns that are indexed (nlni_areas.geoarea_id nlga.id, nlni.id).

I can't explain why joining the news item table with the subselect table takes that long given that both tables have only a single row per news item.

The EXPLAIN statement:

I could've joined the tables without using a subselect but since I need to perform other aggregations (similar to this one) I can't afford to have more than one row per news item or I'll have duplicates.

I'm not sure in my accuracy... test

SELECT nlni.id as id,
       COALESCE(GROUP_CONCAT(nlga.name ORDER BY nlga.name SEPARATOR '|'),
                'Worldwide') as countries
FROM newsletter_newsitem as nlni
LEFT JOIN newsletter_newsitem_areas as nlni_areas ON nlni_areas.newsitem_id = nlni.id
LEFT JOIN newsletter_geoarea as nlga ON nlni_areas.geoarea_id = nlga.id AND nlga.type = 2
GROUP BY nlni.id
WHERE nlni.archive_id IS NULL
  AND nlni.id IS NOT NULL


How to define the logical name in a backup ? ( Sql Server 2008 ) I saw this question but it didn't help me.

Is there a simple way to do :

Backup database SomeDatabase
   to disk ='c:\teste\SomeDatabase.bkp'

configuring the Logical Name?

We have some databases, restored of backups of another database (to follow same structure) and when I use RESTORE FILELISTONLY, it shows me a different name. So the Database TestDBA has the logical name BANK.

The logical file names returned by RESTORE HEADERONLY are those of the source database.  You cannot change these logical names during the backup or restore.  The backup file always contains the both the original logical and physical file names.

The original logical names may be specified with the MOVE option of RESTORE to relocate/rename the physical files during the restore process.  After the restore, you can rename the logical names using ALTER DATABASE:

ALTER DATABASE Teste_Dan
MODIFY FILE(NAME='Teste_Ferdando_Index', NEWNAME='Teste_Dan_index');

Personally, I avoid including the database name in the logical name to avoid this issue, only including the database name in the physical name.  Unfortunately, the database name is often included in the logical name by default.


using port range for connecting to Postgres I am new to postgres and I would like to use it with postGIS. It all works perfectly. The default port I use is 5432. I would like to know if it is possible to use multiple ports i.e 5430-5431-5432-5433 ...etc or a range i.e 5430-5 to connect to the database.
The reason behind this is that I am trying to have several updates from different sources and I would not like o have a bottle neck using the same port.

No, that's not possible.  A single Postgres server can only listen on a single port.

But: the communication through the port is not a bottleneck.

For every connection made, Postgres creates a dedicated backend process once that is established the backend process does all the work.

If you want to increase network throughput you could install a second network interface and make Postgres listen on both interfaces.

If concurrent use of a single server port was a problem, websites wouldn't be able to handle the load they can. After all a webserver only listens on a single port as well (80 or 443)


What are the implications of creating an Integration Services Catalog (SSISDB) where msdb-deployed packages exist? I have inherited a production 2016 SQL Server with SSIS installed, which has many SSIS packages deployed to msdb (mostly warehouse ETL workloads that run nightly).
I would like create a new Integration Services Catalog and deploy future SSIS projects to SSISDB. Can these two deployment methods and package locations coexist? In other words, if I create the new catalog and start using the project deployment model for new SSIS projects, will that have any effect on the dtsx packages already in msdb?

Yes, you can use both the Package Deployment Model (msdb) and the Project Deployment Model (SSISDB) in tandem with no ill-effects.
As a consultant, if I walked into a place that had both, it'd indicate that they've been using SSIS before the 2012 release and there's been no reason to upgrade existing work to a new model. In heavily regulated industries, going through the bureaucracy just to make your life easier often isn't a consideration. That or someone had a very good reason for using the legacy/msdb.
A lot of "design sins" (lack of configuration, lack of logging) in the Package Deployment Model aren't a concern with the Project Deployment Mode (logging is automatic and connection managers are easily externally configurable). The Project Deployment Model also makes parent/child design patterns more easily implemented.
From a management perspective, that's the real beauty of the SSISDB. If you want to run a package, you call the native procedures and it spins up dtexec. For packages in msdb, you are looking at either an xp_cmdshell call or a SQL Agent job as there's no native way to  run them.
Backup and recovery of an SSISDB is a touch more complex due to key management but it's documented.
Deployment of packages uses two different mechanisms (dtutil vs isdeploymentwizard/managed object model/bulk insert)
Execution uses the same executable: dtexec


PostgreSQL stored procedure which can return one of two custom rowtypes I want to have one function, which can return two different types of rows according to given parameter. For example (simplified example) I have function like this:

CREATE OR REPLACE FUNCTION get_numbers(_type integer)
    RETURNS SETOF RECORD
    LANGUAGE plpgsql
AS $$
    DECLARE
        _number_column text;
    BEGIN
        CASE _type
            WHEN 1 THEN
                _column_name := 'normal_numbers';
            WHEN 2 THEN
                _column_name := 'special_numbers';
        END CASE;

        RETURN QUERY EXECUTE 'SELECT * FROM ' || quote_ident(_column_name)
    END;
END;

And I have two different custom composite types:

CREATE TYPE normal_number AS (
    id_normal_number integer,
    normal_number text,
);

CREATE TYPE special_number AS (
    id_special_number integer,
    special_number text,
);

When I call it SELECT * FROM get_numbers(1) I want to get:

id_normal_number  normal_number
-------------------------------
1                 00001
2                 00002
3                 00003

And for SELECT * FROM get_numbers(2) it should return:

id_special_number  special_number
--------------------------------
1                  S0001
2                  S0002
3                  S0003

The point is to call one function, execute dynamic SQL query, get some columns and cast it to custom row type according to procedure parameter (to have different column names in the result).

I tried to create another 2 procedures get_normal_numbers() and get_special_numbers() declared with RETURNS SETOF normal_number / RETURNS SETOF special_number and from the inside call get_numbers(1) or get_number(2) but I didn't manage to get it working.

I don't know how to properly cast the returned set of rows or how to correctly return that set from get_numbers(1) -> get_normal_numbers() -> application

I can edit / add some more details if it'll be needed.

Since your two types are composed from the same basic types in the same order, they are compatible with each other.  So the return type will be the same for functions using them - if I understand correctly, your problem is you want to have the different names of the returned columns depending on the type.

This cannot be done from the database, but you can easily do it from your application.  It can be done like (ugly pseudocode)

if type == 1 {
    execute("SELECT 1 AS id_normal_number, '00001' AS normal_number FROM your_function()")
} else {
    execute("SELECT 1 AS id_special_number, 'S0001' AS special_number FROM your_function()")
}

ie. changing only the names, but using the same DB function.  Or you can invoke two different functions, this way you can avoid dynamic SQL, which may be desirable for readability/performance, depending on some circumstances.


SQL Server error log entry : Error: 17806, Severity: 20, State: 14 I have error in my log for a few weeks, I searched a lot but I couldn't found useful answer.

I did closed SQL Server port for public IP, but I have problem yet.

  Error: 17806, Severity: 20, State: 14.
  SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed [CLIENT: 10.10.3.25]

  Time raised: 27 Jan 2015 2:23 PM

It was raised error while this system was off.

Any time I have seen this error in my environment (0x8009030c and State 14), it has been because the SQL Server engine account was running with a Domain service account, and that account was locked at the time a Kerberos connection was attempted.  The connection would work, but it would be kicked down to NTLM.

Method 1:

Go to register  start –> run … Regedit
Go to:  HKLM\System\CurrentControlSet\Control\LSA
Add a DWORD value called “DisableLoopbackCheck”
Set this value to 1
Rebooted after making this change.

After this registry change was effected.

Method 2. This somehow worked for me: Restarting the sql browser services:

Also, for more info on the related issue, refer to below article:

http://www.allenkinsel.com/archive/2010/06/sql-server-and-sspi-handshake-failed-error-hell/


Is it important to design the database the right way from the beginning? I'm a beginner web developer and I use MySQL and sometimes MS SQL Server. However, I don't have that much information about databases, just basic information to be able to build some tables.

Now, what if my site grows and I improved my skills one day in databases and I found that I want to re design my database, will be that possible without affecting the way my application works or without losing any data?

Possible?  Probably.
Realistic?  Probably not depending on what "without affecting the way my application works" means to you.

As with anything else, if you build a piece of software on shaky foundations, it's likely that if you decide that you need to re-engineer some component down the road, it will be relatively painful and will likely involve changes to other places in your application.  If you build your application with a language or framework that needs to be changed out in the future, you'll likely need to make changes to the way the application works.  If you build an application that doesn't scale, going in to improve performance later will be more difficult will likely involve substantial changes throughout the code base.  If you build an application on a database that isn't properly designed, re-designing it, converting all the data, and propagating the changes to the application will be time consuming.

That doesn't, of course, mean that you shouldn't build anything until you're an expert in everything.  A large part of becoming an expert is making mistakes and learning from them.  If your site gets popular enough and you learn enough about databases to conclude that your initial data model was flawed and that it is worth reworking the entire data model, that probably implies that you've been quite successful and that having to work on a "version 2 architecture" is a good problem to have.  On the other hand, it's very likely worth picking up a book on basic data modeling reading up a bit on normalization as you design your data model to hopefully avoid the most easily avoided pitfalls.


SQL Server transactional replication issue with database We have on the server a database and we want to replicate a database on another server. we've set a transactional replication, but we have issues with the default constraints on the tables:

On the left is schema of the table from a backup from the original server and on the right is the schema from the replicated backup. Is there some setup on the replication so that the constraints remain the same on both databases? I've used Sql Schema Compare from visual Studio 2013 to compare the databases.

The schema on the left is your own DDL statement. The schema on the right is a DDL statement generated by your schema comparison tool.

The statement on the right shows constraint names for your DEFAULT constraints. Yours does not have them but that does not mean SQL Server allows you to create a nameless constraint. When you do not provide a name explicitly, SQL Server picks one for you, because ultimately a constraint must have a name. The names on the right are the ones chosen by SQL Server.

So, the two sets of constraints most likely are the same in both databases. It is only the DDL statements that differ, because yours does not assign constraint names.

You can assign your own names if you use the syntax shown in the query on the right. That is, instead of declaring a default value like this:

DEFAULT (default_value)

use the following syntax:

CONSTRAINT constraint_name DEFAULT (default_value)

That way the names will always be predictable and, when comparing the schemas, you will not run into this issue again.

The same applies to all types of constraints in SQL Server (defaults, primary keys, foreign keys, check constraints).


SQL Server: How do you prevent data being modified through an exposed view? I have a database: DB 1

In a different database DB 2, I want to create a bunch of views that reference data in DB 1. These views will be relatively complex, with lots of joins, aggregations, sub-queries, etc.

I don't want users of DB 2 to be able to modify the data in DB 1. i.e. I don't want users to be able to be able to execute INSERT, UPDATE, DELETE queries on the view.

This blog post: http://sqlhint.com/sqlserver/views/creating-readonly-views

suggests I can use an INSTEAD clause. I would prefer to just dissalow certain query clauses on the view itself except for a particular user. Is this possible?

This is handled through the view's and table's security. Grant only SELECT permission to the view and the underlying tables.

Without write access, INSERTs, UPDATEs and DELETEs through the view will fail.

If the users have access through a group, and you want the group to still have access but not these specific users, you could use the DENY permission since DENY has precedence over GRANT.


How to check if a record is linked with two or more values in a left join In MySQL, I have a table of products connected with a different table with categories.

I often show the list by making a LEFT JOIN and GROUPING the results:

e.g. LEFT JOIN gives me:

Name | Category
----------------
Cool cap | clothes
Cool cap | hats

Then GROUPING by ID gives me 1 result.

The question: How can I show a list of products with category='clothes' AND category='hats'? Because the item won't show up because each category shows on a new line.

GROUP BY Name
HAVING COUNT(*) >= 2

You may need to do something like this:

WHERE ...
  AND ( SELECT COUNT(*)
           FROM tbl
           WHERE Name = ...
           GROUP BY Name
           HAVING COUNT(*) >= 2 )

For checking for exactly a certain list:

           WHERE Category IN ('clothes', 'hats')
           GROUP BY Name, Category
           HAVING COUNT(*) = 2

(There can't be more than 2 after the GROUP BY; this confirms that both are included.)

           WHERE Category IN ('clothes', 'hats', 'scarves')
           ...
           HAVING COUNT(*) >= 2

would check for any 2 of those 3 (or all 3).


how I Backing Up SQL Server database? I am new to SQL Server Admin and am hoping to get some help? I have a database that only contains views of data from another database. It has no tables at all.
Is there a way to backup the data in the views?
Thanks in advance.

Once you have taken steps to backup the other database that contains the real data, you could also backup the views from your views only database, by scripting them out using SQL Server Management Studio

From SQL Server Management Studio
Via Object Explorer

Expand the database you wish to backup views for
Right click on the database and select Tasks -> Generate Scripts
Choose "Select specific database objects"
Check views or just the views you want to backup

Select Next
Choose Save to new query window

Choose next and then next again
Click on finish and the view or views you selected will be scripted to a new window


ORA-01113: file # needs media recovery ORA-01110 I have mistakenly added datafile to the tablespace... And was trying to drop it. I run the following:

alter database datafile 'datafile_name'  OFFLINE;

now when I run

alter database datafile 'datafile_name'  ONLINE;

it shows

ORA-01113: file 30 needs media recovery
ORA-01110: data file 30: 'datafile_name'

also status of the file is RECOVER... What can I do? I cant restart database....

Found the answer:

SQL> recover datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE02.DBF';

alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE02.DBF';

alter  TABLESPACE EXAMPLE  drop datafile  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE02.DBF'

But for production DB it needs a lot of time to recover... Can't I drop that file without recovering it?

One more question...
Another datafile was available during adding that file.. is there any chance that newly created datafile was used for storing data? I think oracle uses datafile until it will not exceed its maximum size and then switching to another available file... is not it so?

I am asking this because I want to be sure that newly created file doesn't contain any necessary data...


Why does query error with empty result set in SQL Server 2012? When running the following queries in MS SQL Server 2012 the second query fails but not the first. Also, when run without the where clauses both queries will fail. I am at a loss why either would fail since both should have empty result sets. Any help/insight is appreciated.

create table #temp
(id     int primary key)

create table #temp2
(id     int)

select 1/0
from #temp
where id = 1

select 1/0
from #temp2
where id = 1

An initial look at the execution plans shows that the expression 1/0 is defined in the Compute Scalar operators:

Now, even though execution plans do start executing at the far left, iteratively calling Open and GetRow methods on child iterators to return results, SQL Server 2005 and later contains an optimization whereby expressions are often only defined by a Compute Scalar, with evaluation deferred until a subsequent operation requires the result:

In this case, the expression result is only needed when assembling the row for return to the client (which you can think of occurring at the green SELECT icon). By that logic, deferred evaluation would mean the expression is never evaluated because neither plan generates a return row. To labour the point a little, neither the Clustered Index Seek nor the Table Scan return a row, so there is no row to assemble for return to the client.
However, there is a separate optimization whereby some expressions can be identified as runtime constants and so evaluated once before query execution begins.
In this case*, an indication this has occurred can be found in the showplan XML (Clustered Index Seek plan on the left, Table Scan plan on the right):

I wrote more about the underlying mechanisms and how they can affect performance in this blog post. Using information provided there, we can modify the first query so both expressions are evaluated and cached before execution starts:
select 1/0 * CONVERT(integer, @@DBTS)
from #temp
where id = 1

select 1/0
from #temp2
where id = 1

Now, the first plan also contains a constant expression reference, and both queries produce the error message. The XML for the first query contains:

More information: Compute Scalars, Expressions and Performance

An example where an error is reported due to runtime constant caching, but there is no indication in the execution plan (graphical or XML):
SELECT TOP (1) id
FROM #temp2
WHERE id = 1
ORDER BY 1/0;


How to Reduce Mysql Size After Dropping Table I dropped a corrupted table with large size to reduce the server size.

But the server size still the same!

I entered the MYSQL location in my server and found a .ARN file with the name of the dropped table with 28GB size.

I tried to optimize the table after dropping it, but sure its not exist

Is it safe to remove .ARN file through the command line ?

Or what can I do to free this 28GB?

You seem to have leftover some temporary file of the ARCHIVE engine: https://dev.mysql.com/doc/refman/8.0/en/archive-storage-engine.html

This engine is barely supported, and I would not be surprised that is has bugs. Assuming there is no .arz file, and that no client is accesing it, it should be safe to drop. One recommendation I always follow is to rename or backup any db-related file before removing it. That will give you an extra guarantee in the worst case possible.

As an extra comment- avoid archive engine at all costs- if you need a compressed table you can use InnoDB, TokuDB or RocksDB.


Tool to monitor database data I would like to tool that when record data changed inside all tables. so later i would get a report like

table user

added record - 3,user3..

deleted record - 2,user2..

This solution does not require triggers but requires setup and causes a performance hit if you enable it for all tables.

Auditing has been built into Oracle for many releases.  There is an article here which goes into some detail.
Basically, you turn it on, tune it for what level of detail you want and the output is available as a dba view or as XML.

Check the system parameter AUDIT TRAIL, it sounds like you want the fine grained auditing available from 9i onwards so you can access the SQL_BIND and SQL_TEXT.

There is an SCN number so you can flashback to the state of the data when the change was made.

Keep in mind that there is a performance hit for all this. You must have the hardware and resources available if this is a production system.


SSIS Package Error when Scheduled in Job I have created a package that brings in data from another database and with the data package populates a table in SQL database. The job runs fine when it is executed. I've imported it into SQL and it still works fine. But when I try to automate it using SQL Job agent, I get the error that Requested registry access is not allowed, and in the log says:

  The application-specific permission settings do not grant Local
  Activation permission for the COM Server application with CLSID
  {485F9D22-B7A7-446B-BBCB-23706B2624E3} and APPID
  {83B33982-693D-4824-B42E-7196AE61BB05}

I'm using SQL management studio 2012.

There is a step-by-step instructions on how another person had fixed it via this link.  Essentially, the set-up best practices weren't followed when setting up the SSIS service and their DBA/admin used the network service.  This caused some permissions not to grant properly during set-up.