Only SQL Server Enterprise Edition supports compression - how to work around that? I have a script that needs to create a temp table and in that temp table I create an index.

I noticed in some of my servers, the script would go on forever, and when checking from a different session what is currently running there was no clear message available.

after cancelling the query I get this message:

Message:
Cannot enable compression for object '#RADHE_sp_getsubscriptions__________________________________________________________________________________________0000000A923A'.
Only SQL Server Enterprise Edition supports compression.
Error: 7738
Severity: 16

this is the part of the script that is generating this error message:

IF object_id('TEMPDB..#RADHE_sp_getsubscriptions') IS NOT NULL
    DROP TABLE #RADHE_sp_getsubscriptions

    create table #RADHE_sp_getsubscriptions (
        publisher sysname NOT NULL,
        publisher_db sysname NOT NULL,
        publication sysname NOT NULL,
        replication_type int,
        subscription_type int,
        last_updated datetime,
        subscriberd_db sysname,
        update_mode int,
        last_sync_status int,
        last_sync_summary nvarchar(4000),
        last_sync_time datetime)

   CREATE CLUSTERED INDEX I_RADHE_sp_getsubscriptions
                       ON #RADHE_sp_getsubscriptions (publisher,Publisher_db,publication)
                     WITH (   PAD_INDEX = OFF
                            , FILLFACTOR = 100
                            , SORT_IN_TEMPDB = ON
                            , IGNORE_DUP_KEY = OFF
                            , STATISTICS_NORECOMPUTE = OFF
                            , ONLINE = OFF
                            , DATA_COMPRESSION=PAGE
                            , ALLOW_ROW_LOCKS = ON
                            , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]

Later on this is what I store in this table:

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

TRUNCATE TABLE #RADHE_sp_getsubscriptions

insert into #RADHE_sp_getsubscriptions
exec sp_MSenumsubscriptions

select * from #RADHE_sp_getsubscriptions

which gives this valuable info:

After checking this link about sql server version check and this one below as well:

How do you find SQL Server version/edition without SSMS installed?

I have just got this script to check what he have in the current server:

  SELECT
    CASE
       WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '8%' THEN 'SQL2000'
       WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '9%' THEN 'SQL2005'
       WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.0%' THEN 'SQL2008'
       WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '10.5%' THEN 'SQL2008 R2'
       WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '11%' THEN 'SQL2012'
       WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '12%' THEN 'SQL2014'
       WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '13%' THEN 'SQL2016'
       WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '14%' THEN 'SQL2017'
       --WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('productversion')) like '15%' THEN 'SQL2019'
       ELSE 'Unknown on April 2019'
    END AS MajorVersion,
      SERVERPROPERTY('productversion') as 'Product Version',
      SERVERPROPERTY('productlevel') as 'Product Level',
      SERVERPROPERTY('edition') as 'Product Edition',
      SERVERPROPERTY('buildclrversion') as 'CLR Version',
      SERVERPROPERTY('collation') as 'Default Collation',
      SERVERPROPERTY('instancename') as 'Instance',
      SERVERPROPERTY('lcid') as 'LCID',
      SERVERPROPERTY('servername') as 'Server Name',
    SERVERPROPERTY('EditionID') AS 'EditionID'

and used it in my create index script down below, after the create table:

        IF object_id('TEMPDB..#RADHE_sp_getsubscriptions') IS NOT NULL
            DROP TABLE #RADHE_sp_getsubscriptions

            create table #RADHE_sp_getsubscriptions (
                publisher sysname NOT NULL,
                publisher_db sysname NOT NULL,
                publication sysname NOT NULL,
                replication_type int,
                subscription_type int,
                last_updated datetime,
                subscriberd_db sysname,
                update_mode int,
                last_sync_status int,
                last_sync_summary nvarchar(4000),
                last_sync_time datetime)

           IF ( SELECT RADHE = CASE WHEN CONVERT(VARCHAR(128), SERVERPROPERTY ('edition')) LIKE '%Enterprise%' THEN 1 ELSE 0 END ) = 1

                   CREATE CLUSTERED INDEX I_RADHE_sp_getsubscriptions
                                       ON #RADHE_sp_getsubscriptions (publisher,Publisher_db,publication)
                                     WITH (   PAD_INDEX = OFF
                                            , FILLFACTOR = 100
                                            , SORT_IN_TEMPDB = ON
                                            , IGNORE_DUP_KEY = OFF
                                            , STATISTICS_NORECOMPUTE = OFF
                                            , ONLINE = OFF
                                            , DATA_COMPRESSION=PAGE
                                            , ALLOW_ROW_LOCKS = ON
                                            , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]

           ELSE

                   CREATE CLUSTERED INDEX I_RADHE_sp_getsubscriptions
                                       ON #RADHE_sp_getsubscriptions (publisher,Publisher_db,publication)
                                     WITH (   PAD_INDEX = OFF
                                            , FILLFACTOR = 100
                                            , SORT_IN_TEMPDB = ON
                                            , IGNORE_DUP_KEY = OFF
                                            , STATISTICS_NORECOMPUTE = OFF
                                            , ONLINE = OFF
                                            , DATA_COMPRESSION=NONE
                                            , ALLOW_ROW_LOCKS = ON
                                            , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]

--checking the index creation script
-- EXEC SP_COUNT 'TEMPDB..#RADHE_sp_getsubscriptions'

--Results:
 -- SERVER 1 - CREATE CLUSTERED INDEX I_RADHE_sp_getsubscriptions  ON [dbo].[#RADHE_sp_getsubscriptions__________________________________________________________________________________________0000000A9250] (  [publisher] ASC  , [publisher_db] ASC  , [publication] ASC  )  WITH (  PAD_INDEX = OFF, FILLFACTOR = 100  , SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, DROP_EXISTING = ON, DATA_COMPRESSION=NONE, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]  GO
 -- SERVER 2 - CREATE CLUSTERED INDEX I_RADHE_sp_getsubscriptions  ON [dbo].[#RADHE_sp_getsubscriptions__________________________________________________________________________________________00000001B274] (  [publisher] ASC  , [publisher_db] ASC  , [publication] ASC  )  WITH (  PAD_INDEX = OFF, FILLFACTOR = 100  , SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, DROP_EXISTING = ON, DATA_COMPRESSION=PAGE, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]  GO

and it is all working fine now.


Discover the original OS of a datafile from a oracle backup I have just been awarded with the task of recovering a oracle database with some given datafiles. However, nobody knows from which OS this backup comes from.

I have an AIX box to make the recover. Is there any way that i can determine the OS from the original datafiles?

Mount the database and make a level 3 datafile header dump:

alter session set tracefile_identifier = 'header_dump';
alter session set events 'immediate trace name file_hdrs level 3';

In the trace file, you will find this section:

Platform Information:    Creation Platform ID: 13
Current Platform ID: 13 Last Platform ID: 13

The platform ID identifies the platform of the datafile, and that is all what matters.
You can match the platform ID to platform name by using the information in the V$TRANSPORTABLE_PLATFORM view. For example platform 13 is x86 Linux 64-bit:

SQL> select platform_name from v$transportable_platform where platform_id = 13;

PLATFORM_NAME
--------------------------------------------------------------------------------
Linux x86 64-bit


Executing a Stored Procedure from a Trigger I set up an AFTER INSERT trigger on a table that executes a stored procedure.
For some reason, after an insert occurs on the table, the stored procedure doesn't run and prevents the insertion.

When I try to execute the stored procedure from new T-SQL, it works just fine.

I tried adding try catch to the trigger, but no error is caught.

I tried troubleshooting using SQL Server Profiler and I found out that the stored procedure starts running after the insert, but stops in the middle of it, although it says the statment is complete.

Any ideas what could have caused this, and how to fix it?

You will need to post the code for your trigger and the stored procedure as well as the table definitions in order to get the best assistance.  I was able to put together this quick sample which works for me.

When you are doing the try/catch, I assume you are logging the catch to a table somewhere?

/** Cleanup **/
IF OBJECT_ID('Temp1') IS NOT NULL
    DROP TABLE Temp1;

IF OBJECT_ID('Temp2') IS NOT NULL
    DROP TABLE Temp2;

IF OBJECT_ID('TempTest') IS NOT NULL
    DROP PROCEDURE TempTest;
GO

/** Create our objects **/
CREATE TABLE Temp1 (ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, TextValue VARCHAR(100) NULL);

CREATE TABLE Temp2
    (
        ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY
        , Temp1ID INT NULL
        , TextValue VARCHAR(100) NULL
    );
GO

CREATE PROCEDURE TempTest (@ID AS INT)
AS
BEGIN
    INSERT INTO Temp2 (Temp1ID, TextValue)
    SELECT ID
           , TextValue
    FROM Temp1
    WHERE ID = @ID;

END;
GO

CREATE TRIGGER trgTemp1After
ON Temp1
AFTER INSERT
AS
DECLARE @ID INT;

DECLARE curTest CURSOR READ_ONLY LOCAL STATIC FORWARD_ONLY FOR
    SELECT ID
    FROM INSERTED;

OPEN curTest;

FETCH NEXT FROM curTest
INTO @ID;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC TempTest @ID;

    FETCH NEXT FROM curTest
    INTO @ID;
END;

CLOSE curTest;
DEALLOCATE curTest;
GO

/** Insert into Temp1
    Ideally, we should see the same entry appear in Temp2
    **/
INSERT INTO Temp1 (TextValue)
VALUES ('This is a test');

SELECT ID
       , TextValue
FROM Temp1;

SELECT ID
       , Temp1ID
       , TextValue
FROM Temp2;


Why temdb and log file size is not reduced by SQL Server With a series of question Part1, Part2 and Part3. With the answers and research on internet I came to know that

TemDb is use for holding different values (ex.: Temp variable, Temp table, CTE, Hash result, Hasg aggregation etc.)
Log is use for storing data which comes from buffer and then it is written to the disk and once it is done SQL clears log entry with CHECKPOINT.

In both cases, the result stored is temporary and SQL take care of removing entry from temp database or (any database) log file. So, at any given instance when SQL Server is not running (not running any user defined query or function) then both temdb and log file size should be automatically shrink to minimum. [Assuming that SQL Server has its own process which uses temdb and database log file]
But when I see my log file, it shows me 80 GB and temdb as 45 GB when I am doing nothing on SQL Server.

So, why temdb and (all database) log file size does not reduce when query execution is done?

Also, how SQL Server query executes, I mean its workflow (ex.)

  Query Execution > Buffer Pool > Buffer pool writes entry in Log file/ Buffer pool use temdb > log file writes to disk

Shrinking a database is not a good idea. There are specific cases, but they require a DBA to carefully evaluate the situation.

Sql Server cannot have an idea if a query in, say, next five minutes is going to hit tempdb heavily. Without such a knowledge, shrinking and expanding tempdb is going to be unnecessary and, to be honest, stupid too.

TempDB is re-initialized whenever Sql Server service is restarted. In most of the cases, this effectively shrinks the tempdb data and log files.


How make convincing case to DBA to use Oracle external tables for 500 MB to 5 GB files? In our data feeds, we have a stack of XML files as well as numerous flat files to put into an Oracle11g database.  We flatten the XML into delimited files, and load the whole set using SQLLoader.

I want to try a proof-of-concept loading via TABLE ORGANIZED EXTERNALLY but I need to make a convincing case to DBA's that it won't do something evil to the server.  The only plausible test files I have are 400-600 MB, in production we'd add some multi-gigabyte files.

What are the risks and how should I approach them, any ideas?

UPDATE: Thanks for all of the helpful comments, folks.  More discussions with have yielded "we can't give you shell access on the database server to load files, we can't mount remote files via NFS" - these are basically security concerns.  We handle PII so DBA's are touchy.  Also, some concerns about who's providing the storage.

Any further suggested 'slam dunk' arguments on why 1) external tables are so much better than sqlloader or 2) why a testbed for external tables is low-risk?

Thanks again,

Andrew Wolfe

IMO, an external table is much easier to manage and flexible than a SQLLoader script.  So if you're already doing a recurring SQLLoader load, I see no possibilities of evil in switching to external tables.

By doing the external table, even though the base of the table resides in a text file outside the database, the data can be accessed from within the database, no need for a separate SQLLoader tool/scripts.  So you can write procedures/packages to manipulate the data straight from the external table.  No need to load it anywhere...as long as it's in the directory you have set, it's already "loaded".

As a DBA, I'd much prefer managing a DML script using an external table than having to manage a SQLLoader script.  But SQLLoader has been around a while....if your DBA has too, it may be a tough sell. :)


How to deal with a table that exists in the ALL_OBJECTS table but cannot be selected I am seeing a weird behaviour in my Oracle DB schema around a 'missing' table.

The end goal is to gather statistics for all the tables that our team uses. All those tables have a common prefix - rca.

So, I am fetching the list of tables using:

SELECT *
FROM ALL_OBJECTS
where OBJECT_NAME like 'rca%'
  AND OBJECT_TYPE = 'TABLE';

This query returns a table called rca_related_materials.

We're using a function developed by some other team that does the gather stats job for us. We just pass the owner_name and the table_name to that function.

Calling that function for this table name or running

analyze table rca_related_materials compute statistics for table;

or even,

select *
from rca_related_materials;

returns the below Error report

SQL Error: ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:
*Action:

How can I get this entry removed from the ALL_OBJECTS table?

PS: I see the same entry coming up in USER_TABLES and DBA_TABLES also.

Database objects with lowercase names need to have their name enclosed in double quotes when referenced

Also, you left out the schema name.

Try

select *
from SCHEMA_NAME."rca_related_materials";


Detailed behaviour of mongos in context of sharded Mongo cluster I have a mongo cluster as follows:

3 shards, located in the 3 main global regions (APAC/EMEA/AMER)
The shardKey is "Region".
Each region has a mongos running locally, "close" to its users, "close" to its mongods.

Question: when the mongos receives an insert request for a document who's region is not the mongos "close" region, does the mongos:

a) route the insert into its "close" regional shard then let the balancer sort out moving the document to its correct shard.
OR
b) route the insert to the correct regional shard directly.

It seems to me that a) would give faster inserts than b) but takes longer for the database to settle down to its eventual state.

Which one is what mongos actually does?

Thanks!

It does the second option 'route the insert to the correct regional shard directly`.

See this answer for details.

Where to insert data after sharding?

More resource:

https://docs.mongodb.com/manual/core/sharded-cluster-query-router/


How to get Row Value & Sum/ Aggregate in Same row? (SQL Server) Just wondering if I have data like

 name  score
    A    2
    B    5
    C    7

I would like to have a 3rd row

     name  score  total
        A    2     14
        B    5     14
        C    7     14

I mean ultimately -- I want to find 'z-scores' or percentiles of various row entries across various totals. I'm just not sure if there's a simple way to compare row values to aggregate values, except by a subquery such as

select A.name, A.score, B.total
from myTable A
left join
(select name, sum(score) from myTable group by name) B
on A.name = B.name

That could work but it seems inefficient querying the same table twice for what is a calculation. Is there any simpler way of doing it that I'm missing?

Yes, there is a way. It's called "window aggregate functions":

select
    A.name, A.score,
    sum(A.score) over () as total
from myTable A ;

The OVER () means that the aggregate (sum in this case) is over the whole result set. If you wanted the total over say different names (assuming you had rows with same name, you could use OVER (PARTITION BY name).


Replication is missing a specific set of rows on subscriber So I've got two servers, we'll call them Server A and Server B.

Server A has a table, Table 1, which gets replicated to Server B.

Server B has a table, Table 2, which gets replicated to Server A.

All replication is transactional and pushed from the publisher continuously.

On Server B, there is a trigger on Table 1 which will ultimately create a record in Table 2.

Other various processes also create additional records in Table 2. All of the records in Table 2 are supposed to get replicated back to Server A.

In short, the data from Server A, Table 1 is supposed to make a round trip back to Server A in table 2 along with other records. However only the 'other records' are coming back. None of the data which originated on Server A is coming through, even though it's been successfully inserted into Table 2 on Server B.

Any ideas?

Additional info:

When I generate a new snapshot all those records that didn't replicate are included in the snapshot and do get replicated over. My suspicions lie in what's happening behind the scenes when the trigger fired by replication inserts the data into Table 2 and perhaps something is preventing those records from replicating over.

The trigger looks something like:

Create Trigger [dbo].[trg_INS_TableA]
For Insert
As

Declare @<Various Parameters>

Select @<Various Parameters> = <Record Data>
From inserted

Declare @RC Int
Exec @RC = [MyDB].[dbo].[up_INS_TableB] @<Various Parameters>

And up_INS_TableB transforms and inserts the Table A data into Table B

The reason the round trip is not occurring is because your subscription from Server B to Server A has its @loopback_detection property set to TRUE, which is the default setting.

As we can see from sp_addsubscription, @loopback_detection specifies if the Distribution Agent sends transactions that originated at the Subscriber back to the Subscriber.  If set to true, the Distribution Agent does not send transactions that originated at the Subscriber back to the Subscriber.  If set to false, the Distribution Agent sends transactions that originated at the Subscriber back to the Subscriber.

I'm aware that the documentation states that this property is used with bidirectional transactional replication, however, I have reproduced your scenario and it appears to be being enforced with plain vanilla transactional replication as well.  Dropping the subscription and adding it back with @loopback_detection set to FALSE alleviated the problem and allowed for the round trips to occur.

Technically your topology can be considered Bidirectional Transactional Replication since both servers are publishing and subscribing to and from each other, even though the articles are different.

Keep in mind that you will need to drop and add the subscription back with @loopback_detection set to FALSE since sp_changesubscription does not allow you to change the @loopback_detection property on the fly.

I hope this helps.


SQL Server - Prevent Clustered Index Scan on a specific table My database contains a specific table that is quite large (250+M rows, 100+GB data space).

Generating a clustered index scan on this table is always a bad idea.

This table has multiple indexes and we never run a query on this table without specifying predicates on indexed columns.

Since we updated the server, we have experienced random query plan degradations. Queries that would run in a few seconds randomly start to timeout after multiple dozens of minutes.

Here are the server changes:

The compatibility level was updated from 2008 to 2016, thus the cardinality estimator changed.
SQL Server edition was updated from Standard to Enterprise.
SQL Server version (2016) was not changed.
The storage (SSD) was not changed.

Of course, I can easily solve this issue for one specific query by adding hints, but I have too many queries in my applications to broadly apply this solution.

Is there a way to globally prevent SQL Server from generating Clustered Index Scan on this table?

I was not able to reproduce the same bad query plans from my previous production issues (the bad query plans happen randomly), but I was able to generate similar query plans using a new simple query. I uploaded plans for the simple query without hint and with hint.

OPTION (RECOMPILE) could solve the issue. But it does not ensure that the new query plan will be better or that SQL Server will not switch to another query plan in a few days. I am not looking for a way to improve one particular query or to improve one particular query plan. I want to globally prevent Clustered Index Scan on my table.

I have many queries that query many different columns and I cannot include all columns in all indexes.

One global option that would probably solve my issues is to use the legacy cardinality estimator, but I hope to find something better.

Is there a way to globally prevent SQL Server from generating Clustered Index Scan on this table?

Yes.  Replace the clustered index with a clustered columnstore index.  It will be highly compressed.  And scans can eliminate unneeded columns, and (critically here) unneeded row groups.

The query here is tricky to optimze:

select t.Id, t.ExternalId, t.Source, tn.Code
from XXX.LargeTable t
join Security tn on t.SecurityId = tn.Id
where t.TransactionTime >= @d1
and t.TransactionTime <= @d2
and tn.NationalityId = 22

The optimal plan depends almost entirely on how wide the range [@d1, @d2] is.  But SQL Server can't have multiple plans for the same query.  You're getting a table scan here as the alternative plan has a huge estimated cost, due to the double nested loop join.  As it turns out if few enough rows qualify in the transaction table, that plan will be cheaper than the table scan, but SQL has to come up with a plan that works reasonably well for any values of @d1 and @d2.

If the date range turns out to be verry narrow, as here, the clustered index scan is a terrible plan, as it requires reading the whole table.

But in the case of a Clustered Columstore SQL Server only has to scan the TransactionTime column to find the matching rows.  Not only that, each 1,000,000 row column segment has the min and max in the column segment header.  So if the range is narrow many of the row groups can be eliminated without actually scanning all the TransactionTime values.

The "bad" plan had mainly IO waits

  <Wait WaitType="PAGEIOLATCH_SH" WaitTimeMs="523619" WaitCount="83735" />
  <Wait WaitType="MEMORY_ALLOCATION_EXT" WaitTimeMs="13910" WaitCount="14455586" />

Scanning a CCI for the same query would require a tiny, tiny fraction of the IO required for the clustered index scan.


Registered Server window does not show Database Engine or Local Server Groups I am running SQL Server Management Studio 2012 on a Windows 7 machine. After a failed attempt to import my registred servers from SSMS 2008, the icons for Database Engine and Local Server Groups are not displayed in the Registered Servers window: the Registred Servers window is empty.

As such I cannot register a server from the SSMS GUI, by right clicking the Local Server Groups.

How can I restore the Database Engine and Local Server Groups items?

In the folder %APPDATA%\Microsoft\Microsoft SQL Server\110\Tools\Shell you should find a file named RegSrvr.xml. That's the file where Management Studio stores local registered servers. If the file is missing or corrupt, SSMS won't display anything.

In this case, you can create a new file and paste this:

<?xml version="1.0"?>
<model xmlns="http://schemas.serviceml.org/smlif/2007/02">
  <identity>
    <name>urn:uuid:96fe1236-abf6-4a57-b54d-e9baab394fd1</name>
    <baseURI>http://documentcollection/</baseURI>
  </identity>
  <xs:bufferSchema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <definitions xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08">
      <document>
        <docinfo>
          <aliases>
            <alias>/system/schema/RegisteredServers</alias>
          </aliases>
          <sfc:version DomainVersion="1" />
        </docinfo>
        <data>
          <xs:schema targetNamespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
            <xs:element name="RegisteredServersStore">
              <xs:complexType>
                <xs:sequence>
                  <xs:any namespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" processContents="skip" minOccurs="0" maxOccurs="unbounded" />
                </xs:sequence>
              </xs:complexType>
            </xs:element>
            <xs:element name="ServerGroup">
              <xs:complexType>
                <xs:sequence>
                  <xs:any namespace="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" processContents="skip" minOccurs="0" maxOccurs="unbounded" />
                </xs:sequence>
              </xs:complexType>
            </xs:element>
            <RegisteredServers:bufferData xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08">
              <instances xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08">
                <document>
                  <docinfo>
                    <aliases>
                      <alias>/RegisteredServersStore</alias>
                    </aliases>
                    <sfc:version DomainVersion="1" />
                  </docinfo>
                  <data>
                    <RegisteredServers:RegisteredServersStore xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
                      <RegisteredServers:ServerGroups>
                        <sfc:Collection>
                          <sfc:Reference sml:ref="true">
                            <sml:Uri>/RegisteredServersStore/ServerGroup/AnalysisServicesServerGroup</sml:Uri>
                          </sfc:Reference>
                          <sfc:Reference sml:ref="true">
                            <sml:Uri>/RegisteredServersStore/ServerGroup/CentralManagementServerGroup</sml:Uri>
                          </sfc:Reference>
                          <sfc:Reference sml:ref="true">
                            <sml:Uri>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup</sml:Uri>
                          </sfc:Reference>
                          <sfc:Reference sml:ref="true">
                            <sml:Uri>/RegisteredServersStore/ServerGroup/IntegrationServicesServerGroup</sml:Uri>
                          </sfc:Reference>
                          <sfc:Reference sml:ref="true">
                            <sml:Uri>/RegisteredServersStore/ServerGroup/ReportingServicesServerGroup</sml:Uri>
                          </sfc:Reference>
                          <sfc:Reference sml:ref="true">
                            <sml:Uri>/RegisteredServersStore/ServerGroup/SqlServerCompactEditionServerGroup</sml:Uri>
                          </sfc:Reference>
                        </sfc:Collection>
                      </RegisteredServers:ServerGroups>
                    </RegisteredServers:RegisteredServersStore>
                  </data>
                </document>
                <document>
                  <docinfo>
                    <aliases>
                      <alias>/RegisteredServersStore/ServerGroup/AnalysisServicesServerGroup</alias>
                    </aliases>
                    <sfc:version DomainVersion="1" />
                  </docinfo>
                  <data>
                    <RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
                      <RegisteredServers:Parent>
                        <sfc:Reference sml:ref="true">
                          <sml:Uri>/RegisteredServersStore</sml:Uri>
                        </sfc:Reference>
                      </RegisteredServers:Parent>
                      <RegisteredServers:Name type="string">AnalysisServicesServerGroup</RegisteredServers:Name>
                      <RegisteredServers:ServerType type="ServerType">AnalysisServices</RegisteredServers:ServerType>
                    </RegisteredServers:ServerGroup>
                  </data>
                </document>
                <document>
                  <docinfo>
                    <aliases>
                      <alias>/RegisteredServersStore/ServerGroup/CentralManagementServerGroup</alias>
                    </aliases>
                    <sfc:version DomainVersion="1" />
                  </docinfo>
                  <data>
                    <RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
                      <RegisteredServers:Parent>
                        <sfc:Reference sml:ref="true">
                          <sml:Uri>/RegisteredServersStore</sml:Uri>
                        </sfc:Reference>
                      </RegisteredServers:Parent>
                      <RegisteredServers:Name type="string">CentralManagementServerGroup</RegisteredServers:Name>
                      <RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>
                    </RegisteredServers:ServerGroup>
                  </data>
                </document>
                <document>
                  <docinfo>
                    <aliases>
                      <alias>/RegisteredServersStore/ServerGroup/DatabaseEngineServerGroup</alias>
                    </aliases>
                    <sfc:version DomainVersion="1" />
                  </docinfo>
                  <data>
                    <RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
                      <RegisteredServers:Parent>
                        <sfc:Reference sml:ref="true">
                          <sml:Uri>/RegisteredServersStore</sml:Uri>
                        </sfc:Reference>
                      </RegisteredServers:Parent>
                      <RegisteredServers:Name type="string">DatabaseEngineServerGroup</RegisteredServers:Name>
                      <RegisteredServers:ServerType type="ServerType">DatabaseEngine</RegisteredServers:ServerType>
                    </RegisteredServers:ServerGroup>
                  </data>
                </document>
                <document>
                  <docinfo>
                    <aliases>
                      <alias>/RegisteredServersStore/ServerGroup/IntegrationServicesServerGroup</alias>
                    </aliases>
                    <sfc:version DomainVersion="1" />
                  </docinfo>
                  <data>
                    <RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
                      <RegisteredServers:Parent>
                        <sfc:Reference sml:ref="true">
                          <sml:Uri>/RegisteredServersStore</sml:Uri>
                        </sfc:Reference>
                      </RegisteredServers:Parent>
                      <RegisteredServers:Name type="string">IntegrationServicesServerGroup</RegisteredServers:Name>
                      <RegisteredServers:ServerType type="ServerType">IntegrationServices</RegisteredServers:ServerType>
                    </RegisteredServers:ServerGroup>
                  </data>
                </document>
                <document>
                  <docinfo>
                    <aliases>
                      <alias>/RegisteredServersStore/ServerGroup/ReportingServicesServerGroup</alias>
                    </aliases>
                    <sfc:version DomainVersion="1" />
                  </docinfo>
                  <data>
                    <RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
                      <RegisteredServers:Parent>
                        <sfc:Reference sml:ref="true">
                          <sml:Uri>/RegisteredServersStore</sml:Uri>
                        </sfc:Reference>
                      </RegisteredServers:Parent>
                      <RegisteredServers:Name type="string">ReportingServicesServerGroup</RegisteredServers:Name>
                      <RegisteredServers:ServerType type="ServerType">ReportingServices</RegisteredServers:ServerType>
                    </RegisteredServers:ServerGroup>
                  </data>
                </document>
                <document>
                  <docinfo>
                    <aliases>
                      <alias>/RegisteredServersStore/ServerGroup/SqlServerCompactEditionServerGroup</alias>
                    </aliases>
                    <sfc:version DomainVersion="1" />
                  </docinfo>
                  <data>
                    <RegisteredServers:ServerGroup xmlns:RegisteredServers="http://schemas.microsoft.com/sqlserver/RegisteredServers/2007/08" xmlns:sfc="http://schemas.microsoft.com/sqlserver/sfc/serialization/2007/08" xmlns:sml="http://schemas.serviceml.org/sml/2007/02" xmlns:xs="http://www.w3.org/2001/XMLSchema">
                      <RegisteredServers:Parent>
                        <sfc:Reference sml:ref="true">
                          <sml:Uri>/RegisteredServersStore</sml:Uri>
                        </sfc:Reference>
                      </RegisteredServers:Parent>
                      <RegisteredServers:Name type="string">SqlServerCompactEditionServerGroup</RegisteredServers:Name>
                      <RegisteredServers:ServerType type="ServerType">SqlServerCompactEdition</RegisteredServers:ServerType>
                    </RegisteredServers:ServerGroup>
                  </data>
                </document>
              </instances>
            </RegisteredServers:bufferData>
          </xs:schema>
        </data>
      </document>
    </definitions>
  </xs:bufferSchema>
</model>

This is what an empty registered servers file looks like in SSMS 2012. It should bring your SSMS back to normal.


Non-grouping field error with ONLY_FULL_GROUP_BY mode and tables that have the same column name I'm finding that MySQL seems to have an issue when ONLY_FULL_GROUP_BY mode is enabled and the query includes a HAVING that refers to grouped columns from different tables that have the same name.

This is the schema to replicate the issue:

CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);

And you need to add ONLY_FULL_GROUP_BY to your SQL mode:

SET SESSION SQL_MODE='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY'

And this is the query to create the error:

SELECT t1.c1, t2.c1
FROM t1, t2
GROUP BY t1.c1, t2.c1
HAVING t1.c1 > 1 AND t2.c1 > 1;

Error reads:

  Non-grouping field 'c1' is used in HAVING clause

Both t1.c1 and t2.c1 are grouped, so I don't see what is wrong with this query.

Additionally if the query refers to different column names from the same table, the query works.  For example:

SELECT t1.c1, t2.c2
FROM t1, t2
GROUP BY t1.c1, t2.c2
HAVING t1.c1 > 1 AND t2.c2 > 1;

Am I doing something wrong?  I can't find a report of this issue anywhere.  I can replicate the error on MySQL version 5.5.44 on Ubuntu and 5.6.24 on Gentoo.

SQLFiddle for this - http://sqlfiddle.com/#!2/da2704/4

The simple query above is just to demonstrate the issue.  Here is a version that cannot be achieved using WHERE instead of HAVING and suffers the same problem:

... HAVING t1.c1 > SUM(t1.c2) AND t2.c1 > SUM(t2.c2)

UPDATE:

A MySQL Bug has been raised for this - http://bugs.mysql.com/bug.php?id=80455

It seems a bug (MySQL 5.5 and 5.6). A temporary solution that can be useful is:

SELECT `t1`.`c1`, `der`.`_c1`
FROM `t1`, (SELECT `c1` `_c1`, `c2` `_c2`
            FROM `t2`) `der`
GROUP BY `t1`.`c1`, `der`.`_c1`
HAVING `t1`.`c1` > 1 AND `der`.`_c1` > 1;

SQL Fiddle demo

In MySQL 5.7.11, the query:

SELECT `t1`.`c1`, `t2`.`c1`
FROM `t1`, `t2`
GROUP BY `t1`.`c1`, `t2`.`c1`
HAVING `t1`.`c1` > 1 AND `t2`.`c1` > 1;

runs smoothly.


SQL Server 2016 TempDB physical file size is different than SQL System Catalog And Proportional Fill Algorithm is not working as expected When I see size of file physically on drive, it is different for one of the tempdb secondary data file when compared to size mentioned in system catalog. What can the reason for it?

In tempdb properties we are also seeing same thing as we say via Query. So query should not be an issue.

SELECT f.name AS [File Name] ,
       f.physical_name AS [Physical Name],
       CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB],
       CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, 'SpaceUsed') AS int)/128.0 AS DECIMAL(15,2)) AS [Available Space In MB],
       [file_id],
       fg.name AS [Filegroup Name],
       (f.growth*8)/1024 AS [AutoGrowth In MB]
FROM sys.database_files AS f WITH (NOLOCK)
LEFT OUTER JOIN sys.data_spaces AS fg WITH (NOLOCK)
  ON f.data_space_id = fg.data_space_id
OPTION (RECOMPILE);

Also another major issue is proportional fill algorithm is not working as expected as couple of files are having more size when compared to other files.
What can be the reason for it?

Also another major issue is proportional fill algorithm is not working as expected as couple of files are having more size when compared to other files. What can be the reason for it?

One reason for this could be that the files were intentionally (mis)configured like this.

Try querying sys.master_files as well, which will have the last configured size value for the tempdb files:

SELECT
    [name],
    [file_id],
    [type_desc],
    [size] * 8 / 1024 [size_mb]
FROM sys.master_files
WHERE database_id = 2

You could manually shrink the main tempdb file to 350 MB to get the sizes allocations spreading around normally again.

Regarding your other question, I'm really not sure why the value in sys.database_files is different from what is shown in the file explorer.  Hopefully someone else can come along and solve that mystery.


MySQL InnoDB Plugin default engine type not accepting config setting I have a strange MySQL InnoDB issue that I've been struggling with for a few hours now. I'm asking this here, and also on the main StackOverflow site - I will cross-post the answer to the other site when answered.

I have enabled the InnoDB plugin as a replacement for the built-in version, and setting the innodb_file_format and innodb_file_per_table are not having the required effect. The MySQL status variables still show Anaconda as the default engine.

Any suggestions would be gratefully received!

Relevant section of my.cnf settings

The settings below do not exist elsewhere in the config overriding these options.

innodb_file_per_table   = 1
innodb_file_format      = Barracuda
innodb_strict_mode      = 1

Relevant entries in SHOW VARIABLES

This is how I know that Barracuda is available

| innodb_file_format              | Antelope               |
| innodb_file_format_check        | Barracuda              |
| innodb_file_per_table           | OFF                    |
| innodb_strict_mode              | OFF                    |
| innodb_version                  | 1.0.9                  |

Section of MySQL Log

The log is also showing that Barracuda is available...

111020 18:10:13 mysqld_safe Starting mysqld daemon with databases from /.../mysql_data/
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
111020 18:10:13  InnoDB: highest supported file format is Barracuda.
111020 18:10:13 InnoDB Plugin 1.0.9 started; log sequence number 5609019
111020 18:10:13 [Note] Event Scheduler: Loaded 0 events
111020 18:10:13 [Note] /.../mysql/libexec/mysqld: ready for connections.
Version: '5.1.48-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

I have finally found the answer, and it was [unsurprisingly] staring me in the face the whole time...

I had inherited the system from an earlier admin, who had made a mistake in the my.cnf file putting all the InnoDB specific parameters under a [innodb] heading, instead of under the [mysqld] heading where it should be.

Why did this take so long to locate? It was assumed that the config was working correctly in the first place. [slaps head]...

Incorrect file segment

[mysqld]
# Stuff here

[innodb]
# innodb stuff here

Correct file segment

[mysqld]
# Stuff here

# innodb stuff here

And now everything works as intended.


mysql select query to select from single table with multiple WHERE clauses i am new to database querying so apologies for this rudimentary query question. thank you in advance.

table called "neuron"
columns in "neuron" called "neuron_id", "neuron_name" + others (not relevant here)

neuron_id is unique identifier for each neuron_name;
say there are 5 entries, thus
neuron_id = 1,2,3,4,5 and corresponding
neuron_name = a,b,c,d,e

i would like to generate a list that contains neuron_id for select neuron_name. the following query does not work for me.

select neuron_id, neuron_name
from neuron
where neuron_name = "a" or "c" or "e"

i expect the following

1,a
3,c
5,e

but the above query only spits out

1,a

Try this:

select neuron_id, neuron_name
from neuron
where neuron_name = "a" or neuron_name = "c" or neuron_name = "e"

Edit:

The solution from ypercube is more semantically correct.

select neuron_id, neuron_name
from neuron
where neuron_name IN ('a', 'c', 'e')


Row locking within stored procedure I have a SQL Server Stored Procedure for getting the next number in sequence stored in a configuration table:

declare @val as int
SELECT @val = LongValue
from tblSystem
WHERE [Key] = @Key;

SET @val = @val + 1;

UPDATE tblSystem
set LongValue = @val
where [Key] = @Key

select @val;

Will running this within a Stored Procedure avoid any possible concurrent reads and avoid having a caller receive the same number? Should I use a transaction instead?

Any time you want to access a value more then once and you need to be sure that that value did not change in the mean time, you need to include the two accesses in a single transaction. But even more, you also need to make sure that appropriate locks are held.

If you are doing a SELECT followed by an UPDATE in a separate statement, you need to either switch the transaction isolation level to repeatable read or serializable or, even better, supply the WITH(UPDLOCK) hint on the first select. The UPDLOCK hint expresses the intend to later update this row. It will allow concurrent reads but prevent any other type of update request.

In SQL Server, other than in some competing RDBMSs, a stored procedure does not automatically provide transaction scope, so even with statements inside a procedure you still need to manage a surrounding transaction, either by always calling the procedure inside a transaction or by opening and committing a transaction inside the procedure.

All that being said however, in your case you don't even need multiple statements. Just combine the UPDATE with an OUTPUT clause:

UPDATE dbo.tblSystem
   SET LongValue += 1
OUTPUT INSERTED.LongValue
 WHERE [Key] = @Key;

Each statement in SQL Server is automatically executed within a "statement transaction" (no matter if called inside a procedure or not). Because you are using the UPDATE statement, SQL Server also takes care of the appropriate locking level. Therefore this is "threadsafe".

As an added bonus it should also execute faster.

Here is a SQL Fiddle to demonstrate this

MS SQL Server 2008 Schema Setup:

CREATE TABLE dbo.tblSystem([Key] INT PRIMARY KEY CLUSTERED, LongValue BIGINT);

INSERT INTO dbo.tblSystem VALUES(42,0);

GO
CREATE PROCEDURE dbo.GetNextValue
  @Key INT
AS
BEGIN
  UPDATE dbo.tblSystem
     SET LongValue += 1
  OUTPUT INSERTED.LongValue
   WHERE [Key] = @Key;
END;

Query 1:

EXEC dbo.GetNextValue 42;

Results:

| LONGVALUE |
|-----------|
|         1 |

Query 2:

EXEC dbo.GetNextValue 42;

Results:

| LONGVALUE |
|-----------|
|         2 |

Query 3:

EXEC dbo.GetNextValue 42;

Results:

| LONGVALUE |
|-----------|
|         3 |

Query 4:

EXEC dbo.GetNextValue 42;

Results:

| LONGVALUE |
|-----------|
|         4 |


Return rows that meet a condition in two different columns I have the following table describing the color and smell of an animal. I am trying to filter it to see what animals are the same color and smell. I do not care if multiple animals use the same color or use the same smell, but if they use the same color AND smell, that is what I want to see.

I have tried using union and also and select statement with in after a where. I think I understand how to compare multiple columns if they are in different tables, but I can't seem to do it if the columns are in the same table.

Table: Animals

Name| color| smell
-------------------------
cat | red  | bad
dog | red  | bad
rat | red  | good
bat | red  | decent
bee | red  | okay
ape | red  | okay
ant | red  | bad

I want to be able to return the following:

Name| color| smell
-------------------------
cat | red  | bad
dog | red  | bad
ant | red  | bad
bee | red  | okay
ape | red  | okay

Thanks for the help.

WITH cte AS ( SELECT *, COUNT(*) OVER (PARTITION BY color, smell)  cnt
              FROM animals )
SELECT name, color, smell
FROM cte
WHERE cnt > 1

PS. color and smell must be NOT NULL.


SQL Server 2016 (SP2-GDR) - correct upgrading path I've found in my organization VM with SQL Server 2016 (SP2-GDR) (KB4532097) - 13.0.5102.14 (X64), which I would like to patch to the latest available version.
Can I just install CU15 (KB4577775) on it or there is more complex path?
Best Regards

Yes, it's that simple. Applying a cumulative update applies all the fixes up to that point. More info here.


Suppress successful insert statements or review errors on large inserts in Oracle 12c I have ~130 SQL scripts that INSERT about ~750,000 records into an Oracle 12c database.

I have a master SQL that kicks all this off using this template:

set termout off
set echo off;

delete from table1;
@C:\Scripts\table1_DATA_TABLE.sql
set termout on;
REM ---
REM completed table1_DATA_TABLE.sql
REM ---
set echo off;
set termout off;
commit;

<repeat for every script>

The issue is, I'm getting thousands of 1 row inserted. and it's drowning out errors.  Is there a way I can suppress the successful INSERT notifications and only leave the errors?  Or, is there a way I can retrieve all the errors of a session/script ?

SET FEEDBACK OFF suppresses the insert notifications.

See the documentation.

You can also use SET ERRORLOGGING to log errors to a table of your choice. Again, see the documentation.


PostgreSQL closing connection during requests I have a PostgreSQL 10.12 server running on an Ubuntu 18.04 Server. Since today, I am suddenly getting errors when querying data from a Python script running on the same machine, and in some edge cases also in pgAdmin 4.

In about 50% of the cases, my Python script will fail to return any data because the DB server closed the connection. I am using SQLAlchemy and it is giving me the following error:

psycopg2.errors.AdminShutdown: terminating connection due to administrator command
server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request.

Interestingly, the same queries (which are simple "SELECT * FROM table" statements) run perfectly fine in pgAdmin. However, when I try to run the following query in pgAdmin, I get the message Connection to the server has been lost.

select * from mytable where mycolumn <> ''

This query specifically worked fine a few days ago. I couldn't find any other examples where this happens in pgAdmin, but there is surely something wrong.

What is weird is that the database has been running for over a month without any issues, and this only started today. I did try to restart the service to no avail.

The logs are not making much sense to me, but there are several things wrong.

The last few days, it constantly gave me the following message:

2020-04-06 19:43:26.959 CEST [7820] postgres@postgres FATAL:  password authentication failed for user "postgres"
2020-04-06 19:43:26.959 CEST [7820] postgres@postgres DETAIL:  Password does not match for user "postgres".
    Connection matched pg_hba.conf line 102: "host    all             all             all                     md5"

I am certain that I am using the correct password, as it does work 50% of the time of the queries. It is showing this message for the default postgres database and not my own, but it still confuses me why this happens as I'm using the default postgres user for both databases. I got this message a lot during the night where I was not doing anything with it either.

For the actual error regarding the query from pgAdmin, this is what I get:

2020-04-07 11:25:18.891 CEST [5497] LOG:  server process (PID 20569) was terminated by signal 11: Segmentation fault
2020-04-07 11:25:18.891 CEST [5497] DETAIL:  Failed process was running: SELECT * FROM transactionoutputs WHERE fileheader <> ''
2020-04-07 11:25:18.891 CEST [5497] LOG:  terminating any other active server processes
2020-04-07 11:25:18.891 CEST [20567] postgres@bitcoin WARNING:  terminating connection because of crash of another server process
2020-04-07 11:25:18.891 CEST [20567] postgres@bitcoin DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2020-04-07 11:25:18.891 CEST [20567] postgres@bitcoin HINT:  In a moment you should be able to reconnect to the database and repeat your command.

I can't seem to find any errors in the logs for the regular SELECT queries from my Python scripts, so I don't know why the connection is closed for this all the time. What is so confusing about this is that it worked perfectly fine just a yesterday, and I have not changed anything. Restarting PostgreSQL also did not help.

I'm not an expert with databases or Linux, so any help why this might happen and how I can figure out what exactly is happening is highly appreciated, thank you.

This kind of error looks like a bug in PostgreSQL:

  server process (PID 20569) was terminated by signal 11: Segmentation
  fault

Try to post to https://www.postgresql.org/list/pgsql-bugs/


Postgres foreign key to a foreign key Apologies for asking this like an interview question...
Say you have 3 tables A, B and C
A is huge
B references A’s primary key through a foreign key and is small
C wants to reference the data in B but doesn’t know how
Which of the following options seems better:

Add a column to C having a foreign key to B’s primary key which is a foreign key of A’s primary key
Add a column to C having a foreign key to A’s primary key directly
Something else...

The plan is for C to join with B using our new column, but which table should C reference?
My gut feeling is that on an insertion to C the constraint check will be faster if its referencing a smaller table but I'm not sure if that's how it actually plays out
Clarification: C is a subset of B and B is a subset of A and all proposed relations are 1:1

If A is in one-to-zero-to-one relation with B, then B inherits its primary key from A:

If B is in one-to-zero-to-one relation with C, then C inherits its primary key from B, which is the primary key of A:

Nothing out of sort here, this is perfectly acceptable.


How to know when to scale RDS MySQL instance? How to scale it? I'm running 1 single medium RDS instance for my websites.

How do I determine when I will need to scale it up?
Is there a way to scale it horizontally or I can only upgrade it to a bigger instance?

You need to scale it up when resource of CPU and disk io is the bottleneck and the cost of optimising the database is higher than scale up.

There are many way to scale it horizontally for sure. Master slave replication is one of the most used way to scale horizontally. You can also run MySQL cluster too.


#1170 - BLOB/TEXT column 'variable' used in key specification without a key length I am trying to create a database table for a blog website, but I have an error from a text variable.
the database structure looks like this:
CREATE TABLE place(
    id INT(8) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(30) NOT NULL UNIQUE,
    author VARCHAR(30) NOT NULL,
    city VARCHAR(30) NOT NULL,
    state VARCHAR(30) NOT NULL UNIQUE,
    latitude FLOAT NOT NULL UNIQUE,
    longitude FLOAT NOT NULL UNIQUE,
    description TEXT NOT NULL UNIQUE,
    post_date DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)ENGINE = MyISAM;

but, every time I do try to generate the table it shows up this error:
#1170 - BLOB/TEXT column 'description' used in key specification without a key length

The column is OK, but the UNIQUE constraint requires an index that is too big.
Some other things are wrong:  Surely there can be more than one place in a state?  Yet the UNIQUE constraint says that there can be only one row for each state.
Remove UNIQUE from any column definition that is not truly unique.
Also, I strongly recommend you use InnoDB, not MyISAM.
PRIMARY KEY is a Unique Index.
UNIQUE is an index and a uniqueness constraint.
INDEX is a way to access data faster.  But until you have the SELECTs, you may not know which columns (or combination of columns) should be indexed.


Query for View with unique entries from Database with repeated entries I have a table with columns of Hotel names, city name and country name (of the respective hotels). I need to create a view of unique entries which will contain city name and country name columns. Any suggestions on how to go about it?
Eg: There may be 100 hotels with city_name = Miami and country_name = USA. The view should however contain only 1 city_name = Miami and country_name = USA.

I figured I'd need a loop to run through the table but still struggling to put together a query.

Thanks in advance!

The simplest solution is this:

SELECT DISTINCT city_name, country_name FROM yourtable

That takes the list of result rows and returns each one only once, even if it appears in the table multiple times. See "DISTINCT" in http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm fro details.

If you also want to know mor information about each row group, like how many hotels exist in each city you can use GROUP BY instead:

SELECT city_name, country_name, COUNT(1) AS Cnt
  FROM yourtable
 GROUP BY city_name, country_name

This again returns each city only once but additionally counts how often it appears in the table. See http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm#i2065777 for more details on that.

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE hotels(name VARCHAR(100),city_name VARCHAR(100),country_name VARCHAR(100));

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel1','Seattle','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel2','Seattle','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel5','Seattle','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel7','Washington','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel8','New York','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel9','New York','USA');

Query 1:

SELECT DISTINCT city_name,country_name FROM hotels

Results:

|  CITY_NAME | COUNTRY_NAME |
-----------------------------
|    Seattle |          USA |
| Washington |          USA |
|   New York |          USA |

Query 2:

SELECT city_name,country_name, COUNT(1) cnt
  FROM hotels
 GROUP BY city_name,country_name

Results:

|  CITY_NAME | COUNTRY_NAME | CNT |
-----------------------------------
|    Seattle |          USA |   3 |
| Washington |          USA |   1 |
|   New York |          USA |   2 |

If you want to create a view for each country you can just add a WHERE clause with the country name like this:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE hotels(name VARCHAR(100),city_name VARCHAR(100),country_name VARCHAR(100));

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel1','Seattle','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel2','Seattle','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel5','Seattle','USA');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel7','Hamburg','Germany');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel8','Paris','France');

INSERT INTO hotels(name,city_name,country_name)
VALUES('hotel9','Paris','France');

CREATE VIEW US_Hotels
AS
SELECT city_name,country_name, COUNT(1) cnt
  FROM hotels
 WHERE country_name = 'USA'
 GROUP BY city_name,country_name;

CREATE VIEW Ger_Hotels
AS
SELECT city_name,country_name, COUNT(1) cnt
  FROM hotels
 WHERE country_name = 'Germany'
 GROUP BY city_name,country_name;

Query 1:

SELECT * FROM US_Hotels

Results:

| CITY_NAME | COUNTRY_NAME | CNT |
----------------------------------
|   Seattle |          USA |   3 |

Query 2:

SELECT * FROM Ger_Hotels

Results:

| CITY_NAME | COUNTRY_NAME | CNT |
----------------------------------
|   Hamburg |      Germany |   1 |


SQL INSERT Query takes 2 seconds and sometimes much longer to execute I know the question's title is bad, But I have  an hard time figure it out my self...
sometimes this query runs in 2-3 seconds and sometimes it's takes even 30 seconds

I have a table named ranking_temp_free:

I have one Index except the primary key:

I have no triggers or partitions on this table..

I'm using this Query:

INSERT IGNORE INTO ranking_temp_free
(countryID, categoryID, ranking, popID, app_id, check_time)
VALUES (/*Here comes 50,000 rows of Data*/);

And as I wrote before.. The time that takes the query to finish is not fixed as I wish to be...

Logs:

THERE ARE MORE LOGS BEFORE..

07-02-2013 00:24:37 Sending SQL: insert ignore into ranking_temp_free
07-02-2013 00:24:54 Inserted: 50000 rows
QUERY TIME : 17 SECONDS

07-02-2013 00:25:05 Sending SQL: insert ignore into ranking_temp_free
07-02-2013 00:25:41 Inserted: 50000 rows
QUERY TIME : 36 SECONDS

07-02-2013 00:26:02 Sending SQL: insert ignore into ranking_temp_free
07-02-2013 00:26:07 Inserted: 50000 rows
QUERY TIME : 5 SECONDS

07-02-2013 00:26:28 Sending SQL: insert ignore into ranking_temp_free
07-02-2013 00:26:35 Inserted: 50000 rows
QUERY TIME : 7 SECONDS

07-02-2013 00:26:46 Sending SQL: insert ignore into ranking_temp_free
07-02-2013 00:26:52 Inserted: 50000 rows
QUERY TIME : 6 SECONDS

07-02-2013 00:27:03 Sending SQL: insert ignore into ranking_temp_free
07-02-2013 00:27:17 Inserted: 50000 rows
QUERY TIME : 14 SECONDS

07-02-2013 00:28:19 Sending SQL: insert ignore into ranking_temp_free
07-02-2013 00:28:39 Inserted: 50000 rows
QUERY TIME : 20 SECONDS

AND SO ON.........

It's a simple INSERT so why the time is different?.

Just to be clear: THERE ARE NO ANY OTHER PROCESSES ON THE SQL SERVER!

EXAMPLE FOR THE TABLE DATA:

We found the solution to the slow random query execution time:

I added this line: SET TRANSACTION ISOLATION LEVEL READ COMMITTED right after the DB connect.

we are using PHP, So it's looks like this:

mysql_select_db($db, $link);
mysql_query("SET TRANSACTION ISOLATION LEVEL READ COMMITTED");

All our problem solved with this magic line.

Now our queries run in 40 minutes and not in 2.5 hours as it use too..

thanks all for your help.


How to allow in-progress transactions to complete when shutting down PostgreSQL? I'm working on automating as much as possible the mechanics of resizing an EBS volume on which we store all databases.  We're going to host a large number of large-but-infrequently-used databases on a single EC2 instance, where the secondary EBS volume hosts PGDATA (aka all the data files), whose configuration we've embedded in this script here.

One of the issues we've run into is that when extending the filesystem using fsadm under a running PostgreSQL, it determines that the target is busy and won't umount the volume.  This is expected.

I would like to shut down the database as gracefully as possible - while I'm fine to kill of open idle connections, I'd rather not kill it in the middle of any in-progress transactions.  I'm hoping to find some kind of mechanism that allows me to block new connections, block any further transactions from existing connections, and allow existing transactions to complete.

As far as I can tell, the most native way to shut down the database is using pg_ctl stop -m, where the -m takes one of three options: smart, fast and immediate.

From my experiments, it appears that

smart will fail if there are any open idle connections (which is too generous for my needs, as my developers may leave long-lived connections open in either their Django app or their PGAdmin consoles).
fast will kill all connections whether active or idle, which seems to set me up to risk corrupting an in-progress transaction

Does anyone have any suggestions for more gracefully shutting down PostgreSQL than killing off all active connections?  Are there SQL commands I can run before firing off pg_ctl stop?

I don't know of a way to do this automatically.  You could start a smart shutdown, and then loop through live connections terminating them if they are idle.  This would be a bit fragile, as if your grim reaper script were to lose its connection it could not re-establish it.  A less fragile way would be to edit pg_hba.conf to block all connections except from the grim reaper (instead of starting a smart shutdown), then do the same loop as above.  Once there are no other connections, you can do a regular fast shutdown.

But this might not be necessary because:

  fast will kill all connections whether active or idle, which seems to set me up to risk corrupting an in-progress transaction

There will be no corruption  It will either commit, or will rollback with an error reported to the client.  The only risk is that a poorly written client might not correctly deal with the error, but if that is the case you probably have bigger problems than just expanding your EBS.


Efficient Database schema for multi-lingual content? Assume I have a table for blog posts, posts, and schema as

id
author
title
content

and each post can belonged to some categories

id
name
descrtiption

So a blog post can belong to multiple categories.

Obviously title and content in posts, and name and description in categories can be translated to different languages.

So, given the existing db schema design, what would be the most efficient and reasonable way to extend the support for a multi-lingual application?

The least work would be to put a language code in each table. The problem then is there is no way to show that, say, post 612 (English) is a translation of post 943 (Spanish).

To achieve that level of cross-reference it will be necessary to separate the post's existence from its content. There will be these tables:

Language
  Language_id
  Name

Post
  Post_id

Category
  Category_id

PostCategory
  Post_id
  Category_id

CategoryLanguage
  Category_id
  Language_id
  Name
  Description
  ...

PostLanguage
  Post_id
  Language_id
  Title
  Content
  ...

Ideally we would want both a post and its categories translated at the same time. It is difficult to enforce the existance of both simultaneously using declarative constraints. If this is a requirement it would be simpler to push enforcement into the application.

When displaying a post the corresponding categories should be those in the same language. To this end each user could be allowed to choose a language. There will need to be rules to determine what happens if an item has not been translated to the user's preferred language. Maybe a system default language is defined?


How does the Oracle DUAL table work? SQL> desc dual
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DUMMY                                              VARCHAR2(1)

SQL> select 4*5 from dual;

       4*5
----------
        20

SQL>

I find it really strange. If there is no column named 4*5 in dual, how does the select statement work?

Also, why don't I see the same behaviour when I create my own dual table?

SQL> create table dual2(dummy varchar2(1));

Table created.

SQL> desc dual2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DUMMY                                              VARCHAR2(1)

SQL> select 4*5 from dual2;

no rows selected

SQL>

From Wikipedia:

The DUAL table is a special one-row table present by default in all Oracle database installations. It is suitable for use in selecting a pseudocolumn such as SYSDATE or USER. The table has a single VARCHAR2(1) column called DUMMY that has a value of 'X'.

Thus, the dual table is a way to perform operations against what amounts to be an empty but not null table. This is useful when one doesn't care about the table, but needs to perform operations through a select statement. If the table had more than one row or column, multiple results would be returned (due to operating over the entire set of tuples when performing the operation.)
It shouldn't be used in production, unless you specifically need to invoke certain procedures through SQL.
4*5 is a mathematical operation, just as 'Foo'  is a string. Thus, just as one can select 4*5 from any table, just as one can select 'Foo' from any table, DUAL is a way of selecting it from a known-good table that will never have multiple results.
From the documentation (CONCEPTS):

DUAL is a small table in the data dictionary that Oracle Database and user-written programs can reference to guarantee a known result. The dual table is useful when a value must be returned only once, for example, the current date and time. All database users have access to DUAL.
The DUAL table has one column called DUMMY and one row containing the value X.

And the SQL Reference:

DUAL is a table automatically created by Oracle Database along with the data dictionary. DUAL is in the schema of the user SYS but is accessible by the name DUAL to all users. It has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value X. Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once. Alternatively, you can select a constant, pseudocolumn, or expression from any table, but the value will be returned as many times as there are rows in the table. Refer to "About SQL Functions" for many examples of selecting a constant value from DUAL.
Beginning with Oracle Database 10g Release 1, logical I/O is not performed on the DUAL table when computing an expression that does not include the DUMMY column. This optimization is listed as FAST DUAL in the execution plan. If you SELECT the DUMMY column from DUAL, then this optimization does not take place and logical I/O occurs.


Unexpected behavior from ltrim in postgres select ltrim('Test Thing', 'Test ');

Expected: 'Thing'
Actual Result: 'hing'

Why does the 'T' in 'Test' match and trim the 'T' from 'Thing'?

The 2nd parameter is the list of characters to trim, not a string as such.
The manual about ltrim(string text [, characters text]):

  Remove the longest string containing only characters from characters
  (a space by default) from the start of string.

So this:

select ltrim('abc cab b ca foo', 'abc ');

... only leaves 'foo'.

To actually remove one leading string 'Test ':

SELECT regexp_replace('Test ThingTest ', '^Test ', '');

But to update a table, this is typically much more efficient:

UPDATE tbl
SET    col = right(col, -5)  --  right(col, length('Test ') * -1)
WHERE  col LIKE 'Test %';

Only touches relevant rows (important!), can use an index where appropriate (important if only few rows in a big table are affected) and the expression is cheaper, too. About that index:

PostgreSQL LIKE query performance variations


Replicating from MySQL 5.1 master to 5.6 slave failing because 'INSERT ... VALUES (NOW())' results in 'Error_code: 1062' I am migrating away from some old MySQL 5.1 servers to some new MySQL 5.6 servers. During this process, I'm creating a new MySQL 5.6 slave from an existing MySQL 5.1 slave, using the procedure in the mysqldump reference guide.

For example, if my MySQL 5.1 servers are named 'master1' and 'replica1' and I have a new MySQL 5.6 server named 'replica2', the following should make replica2 a second slave of 'master1':

replica2 % mysqldump --login-path=replica1 --all-databases --dump-slave --include-master-host-port --apply-slave-statements --lock-all-tables  --add-drop-database > all.sql
replica2 % mysql < all.sql

And this seems work well, but replication fails with the following error complaining about duplicate entries for the primary key.

2015-06-12 10:00:00 1234 [ERROR] Slave SQL: Worker 0 failed executing transaction '' at master log mysql-bin.009332, end_log_pos 12341234; Error 'Duplicate entry '8072' for key 'PRIMARY'' on query. Default database: 'DATABASE'. Query: 'INSERT INTO "Member" ("Created") VALUES (NOW())', Error_code: 1062

Can I assume that 'INSERT INTO "Member" ("Created") VALUES (NOW())' is triggering the error here? Can I get replication to work without skipping rows with SET GLOBAL sql_slave_skip_counter = 1;?

Some additional details:

I'm using classic MySQL replication, and GTIDs are currently disabled.
The MySQL 5.1 servers are using STATEMENT-based replication, but the new MySQL 5.6 servers are using ROW-based replication.
I don't own the application code, and I cannot change the SQL.

Can I assume that 'INSERT INTO "Member" ("Created") VALUES (NOW())' is
  triggering the error here?

Yes that's exactly what the error telling

Error 'Duplicate entry '8072' for key 'PRIMARY'' on query.
Default database: 'DATABASE'. Query: 'INSERT INTO "Member" ("Created") VALUES (NOW())',
Error_code: 1062

  Can I get replication to work without skipping rows with SET GLOBAL
  sql_slave_skip_counter = 1;?

Yes, You can go and delete the row with the ID 8072 in the table.. you can add the option of slave-skip-errors = 1062 in my.cnf and restart mysql

But you have to be extremely wary about doing this, as the referential integrity is there for a reason, and without it, your slave could not be satisfactorily promoted to take the place of the master in the case of something going wrong with your master hardware...

The only clean way is:

In the master

You FLUSH TABLES WITH READ LOCK; in the database you want to
replicate
SHOW MASTER STATUS and copy the file name and the position
Excute a full backup to the database you want to replicate
Then release the tables with UNLOCK TABLES;

In slave

Restore the databse
Excute this CHANGE MASTER TO MASTER_HOST = 'masterhost', MASTER_USER
= 'masteruser', MASTER_PASSWORD = 'masterpass', MASTER_LOG_FILE = 'filename', MASTER_LOG_POS = 'position';
Then start slave;


INSERT [TableName] SELECT * FROM [TableName] I just ran across a query:

INSERT [TableName] SELECT * FROM [TableName]

Note that it is the same table. This query causes serious blocking on one of my test databases (I wanted to drop it but couldn't and this made me investigate the issue).

The lock type is LCK_M_X. I cannot kill the session - i get the following message:

  PID 56: transaction rollback in progress. Estimated rollback
  completion: 27%. Estimated time remaining: 5715216 seconds.

I don't want to wait the estimated 2 months for the rollback to complete.
The transaction isolation level of the database is READ COMMITED.

Is it possible to kill this session? How should you act if you find such a query in one of the mission critical databases?

That SPID is already killed and you will have to wait for the rollback process to finish. The estimated time is usually wildly inaccurate: don't worry about it. It will probably take from 1 to 2 times the execution time of the INSERT until the stage when it was killed.

If this happened in production, the answer would not be any different: wait for the rollback process to end. The database would not be consistent if the rollback operation was forcibly closed.


Merging continous date range using T-SQL I have a table containing a part time working pattern consisting of start and end dates together with hours values.

Where there is an gap between an end date and a subsequent start date, this indicates the member was full time.

Where the most recent entry has a NULL end date, this indicates the part time work is still ongoing. A NULL enddte should be a day before the subsequent start date, if one is present.

I'd like to merge the date ranges where there are no gaps (ie, subsequent row start date is 1 day ahead of previous row start date) and where the hrs1 and hrs2 values match. Other solutions for similar problems don't correctly handle the NULL in the final row.

CREATE TABLE #hours(
STARTDTE DATETIME,
ENDDTE DATETIME,
hrs1 DECIMAL(6,2),
hrs2 DECIMAL(6,2))

INSERT INTO #hours VALUES
('25 MAY 1990','30 NOV 1994',18,36),
('01 DEC 1994','31 DEC 1994',18,36),
('01 JAN 2000', NULL,20,36),
('16 JUN 2002','20 APR 2007',18,36),
('10 OCT 2008', NULL,20,36)

The expected output would be as follows:

25 MAY 1990 | 31 DEC 1994 | 18 | 36
01 JAN 2000 | 15 JUN 2002 | 20 | 36
16 JUN 2002 | 20 APR 2007 | 18 | 36
10 OCT 2008 | NULL        | 20 | 36

This query needs to be compatible with SQL Server 2008 so unfortunately lead/lag aren't an option to fill in any null end dates.

Assuming hours are added to table in chronological order. If not, sort them before inserting them!

First we need to relate each record to successive records.
You say that is done by matching their hrs1 & hrs2, and making sure the initial record ends one day before the next starts.
Your sample data only includes two records that are sequential, so I added a third (startdte='1995-01-01') to show this works in such a case.
If we only expected two records to be part of the same sequence, we could just use a join.  But if an indefinite number
may be part of the same sequence, then we must use recursion.  In this case, a CTE.  The first part of the UNION gets the parent records,
and the second part of the UNION gets all children.  To uniquely track which sequence is which, we use the 'chain' field as
a sort of bread crumb trail.  Finally, we query the root ID (which we tracked as we went along) and the 'chain' field to find the
first and last record in each sequence.  Then we join it all back to the original data.
Last, we clean up null 'ENDDTE's for full time records.

declare @hours table (
ID int IDENTITY(1,1), -- we need a unique identifier to easily reference each row.
RootID int,
STARTDTE DATETIME,
ENDDTE DATETIME,
hrs1 DECIMAL(6,2),
hrs2 DECIMAL(6,2))

INSERT @hours (STARTDTE,ENDDTE,hrs1,hrs2) VALUES
('25 MAY 1990','30 NOV 1994',18,36),
('01 DEC 1994','31 DEC 1994',18,36),
('1 JAN 1995','1 FEB 1995',18,36),
('01 JAN 2000', NULL,20,36),
('16 JUN 2002','20 APR 2007',18,36),
('10 OCT 2008', NULL,20,36)

;with
hours (chain,recordsInChain,ID, ParentID, RootID,STARTDTE,ENDDTE,hrs1,hrs2) AS (
    select
        convert(varchar(1000),convert(varchar(10),hc.id)+','), -- This gives us a snapshot of the entire series of records to which this record is related.
        1, hc.ID, hc.ID ,hc.ID, hc.STARTDTE,
        hc.ENDDTE,
        hc.hrs1, hc.hrs2
    from
        @hours hc
        LEFT OUTER JOIN @hours hp on hc.hrs1=hp.hrs1 and hc.hrs2=hp.hrs2 and hc.STARTDTE = dateadd(dd,1,hp.ENDDTE) -- Record starts on the day after previous record ends.
    where hp.id is null -- We want root records.  Not records with parents.
    UNION ALL
    select convert(varchar(1000),chain +convert(varchar(10),hc.id)+ ','),recordsInChain +1,  hc.ID, hp.ID ParentID,
        convert(int,left(hp.chain,charINDEX(',',hp.chain)-1))
        , hp.STARTDTE, hc.ENDDTE, hc.hrs1, hc.hrs2
    from
        @hours hc
        INNER JOIN hours hp on (hc.hrs1=hp.hrs1 and hc.hrs2=hp.hrs2 and hc.STARTDTE = dateadd(dd,1,hp.enddte))
        -- We need the end date for fulltime records, but this next line is not allowed in the recursive part of a CTE so we will have to get it later.
        --LEFT OUTER JOIN @hours hFullTime on hc.ENDDTE is null and hFullTime.id = hc.id + 1 and hFullTime.STARTDTE > hc.STARTDTE
)
select hS.startdte,isnull(hE.ENDDTE,hFullTime.STARTDTE-1) enddte,hs.hrs1,hs.hrs2 --,hs.*,hE.*,isnull(hE.ENDDTE,hFullTime.STARTDTE-1)
from
    ( select rootid, max(recordsinchain) recordsInChain from hours  group by rootid ) hRange
    inner join hours hS on hRange.RootID = hS.ID
    inner join hours hE on hRange.rootID = hE.RootID and hE.recordsInChain=hRange.recordsInChain
    -- Now we will get the end date for full time records, since we couldn't do it in the CTE.
    LEFT OUTER JOIN @hours hFullTime on hE.ENDDTE is null and hFullTime.id = hE.id + 1 and hFullTime.STARTDTE > hE.STARTDTE


How to query multiple tables to see if they share a common column? Say I have 100 tables without a uniform naming system. I.e. table 1 is named "tbl_Budget", table 2 is "tbl_Price", etc. I need an efficient way to query each of them to determine if they have a particular column name, say column A, then need the output to look something like this:

TABLE NAME | HAS COLUMN
tbl_Budget   TRUE
tbl_Price    FALSE

In other words, I need to do something like this, but for multiple tables: https://stackoverflow.com/questions/3395798/mysql-check-if-a-column-exists-in-a-table-with-sql

One possible way is selecting from information_schema.tables and a correlated subquery from information_schema.columns.

SELECT t.table_name `TABLE _NAME`,
       coalesce((SELECT 'TRUE'
                        FROM information_schema.columns c
                        WHERE c.table_schema = t.table_schema
                              AND c.table_name = t.table_name
                              AND c.column_name = '<column>'),
                'FALSE') `HAS COLUMN`
       FROM information_schema.tables t;

And another is conditional aggregation from information_schema.columns.

SELECT table_name `TABLE NAME`,
       CASE
         WHEN count(CASE column_name
                      WHEN '<column>' THEN
                        1
                    END) > 0 THEN
           'TRUE'
         ELSE
           'FALSE'
       END `HAS COLUMN`
       FROM information_schema.columns
       GROUP BY table_name;


Using GROUP BY WITH ROLLUP on column that contains NULL I have the following table (see it on SQL Fiddle) (I created to break down my problem):

| ID | word    |
----------------
| 5  | "Hello" |
| 6  |  NULL   |
| 7  | "World" |
| 8  | "World" |

Now I want to count the number of occurrences of each word using GROUP BY word WITH ROLLUP. The NULL in the column word of the row generated by the ROLLUP should be replaced by "total":

SELECT
  ID,
  ifnull(word, "total") as word,
  count(*) as occurrences
FROM test
GROUP BY word WITH ROLLUP;

The problem is that it also replaces the NULL in the record with the amount of rows where words is NULL:

| ID |  word | occurrences |
|----|-------|-------------|
|  6 | total |           1 | <- Here lies the problem
|  5 | Hello |           1 |
|  7 | world |           2 |
|  7 | total |           4 |

So I now use count(word) to distinguish whether it's a NULL from my data or it's a NULL created by ROLLUP so I can replace it with either "empty" or "total":

SELECT
  ID,
  if(count(word) = 0, "empty", ifnull(word, "total")) as word,
  count(*) as occurrences
FROM test
group by word with ROLLUP;

This works for my use case but it still has a flaw:
If word is NULL in all rows, count(word) is also 0 in the final row showing the total: (SQL Fiddle with different data (only record#6))

| ID |  word | occurrences |
|----|-------|-------------|
|  6 | empty |           1 |
|  6 | empty |           1 |  <- word should be "total"

The following statement delivers the result I want:

SELECT
  ID,
  ifnull(word, "total") as word,
  count(*) as occurrences
FROM
(
SELECT
  ID,
  ifnull(word, "empty") as word
FROM test
) tmp
GROUP BY word WITH ROLLUP

But since in most answers around here the use of subqueries seems to be discouraged, I am wondering if there is a better solution.

you have a "solution", so by saying

  I am wondering if there is a better solution.

I think you want to comment about it. Let me focus on several aspects:

SELECT ID [...] GROUP BY word, independently of having or not a WITH ROLLUP is wrong. You are selecting a field whose value is undetermined. In particular, with MySQL you get exposed of returning a random value; or if you use an strict mode (recommended, and by default in the latest versions of MySQL), of the query to fail: 'db_9_b0ff7.test.ID' isn't in GROUP BY
Be it formally or not, WITH ROLLUP Note- (I refer to MySQL implementation) is something that is nice to have in some cases because it avoids a double rescan of the table, but I have not seen a lot in production code. Nothing wrong with it, but they were an exclusive MSSQL feature until it was implemented on SQL1999, and either it was not available widely back in the day or, like in the case of MySQL it was a limited implementation. Reference: https://www.percona.com/blog/2007/09/17/using-group-by-with-rollup-for-reporting-performance-optimization/
I do not see in principle a problem with your final query, if we run explain on both queries (without ID), we see:

MariaDB [test]> EXPLAIN SELECT   if(count(word) = 0, "empty", ifnull(word, "total")) as word,   count(*) as occurrences FROM test group by word with ROLLUP\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

Warning (Code 1052): Column 'word' in group statement is ambiguous
MariaDB [test]> EXPLAIN SELECT   ifnull(word, "total") as word,   count(*) as occurrences FROM ( SELECT   ifnull(word, "empty") as word FROM test ) tmp GROUP BY word WITH ROLLUP\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using filesort
1 row in set, 1 warning (0.01 sec)

Normally the hate towards MySQL is because its limitations on early versions of MySQL. I am not going to say that it is now perfect, there will be still issues, as MySQL development, as it has always been, focused on a fast, easy-in, easy-out RDBMS, and not a full-blown relational engine, but a subquery in the right place is ok (after all, some queries will require a subquery, no matter what. Your usage is ok- it will only have an overhead of going over the summarized rows again and I suppose on the real deal, those will not be significant compared to the full set.

Note- on my instance up here, the subquery is not showed, but you can see it with materialized subqueries in 5.6 here: http://sqlfiddle.com/#!9/b0ff7/7 (It will execute the inner query and then the outer part, with no issue). An index could definitely help here (due to the filesort), but that depends on the final number of records. The subquery may hurt performance if an index (which does not exist now) cannot be used because it. You have to test your specific mysql version and create such an index.
I think your problem is with the data model- in the SQL world (outside of Oracle) NULL is an unknown/invalid value. Seem to be correlating that to 'empty' which is not right- if a word is known to not exist, it should be '' (the empty string, not NULL). I do not know if you can change that, but I do not like your current model. I understand that you may not have something to say about it, so I will not focus on that. Alos you count NULL words? More reasons to use '' instead of NULL.
Now the final question is, is it possible to do it without a subquery on MySQL, and will it be better/less ugly? I do not really see a way- we could do a join, but it will still be to a subquery. I can think of an alternative, which is:

SELECT * FROM (
    SELECT   word,
    count(*) as occurrences
    FROM test
    GROUP BY word with rollup)
tmp
ORDER BY occurrences;

That makes sure that the last row contains the summary (and if there is other null, it is the actual null value). The order would happen even on the case of only null values. On the contrary, it may even slower as it requires ordering, and again you may need an index, and that index may or may not be used.


Is there a way to give GRANT OPTION recursively for subsets of permissions? In SQL Server, I would like to create a role that has the ability to manipulate database objects as well as create other roles and grant those roles subsets of its permissions.

CREATE ROLE deploymentRole;
CREATE ROLE subRole;
GRANT SELECT TO deploymentRole WITH GRANT OPTION;

CREATE TABLE dbo.testTable (id INT NULL);
CREATE USER deployUser WITHOUT LOGIN;
ALTER ROLE deploymentRole ADD MEMBER deployUser;

EXECUTE AS USER = 'deployUser';

--This works
SELECT * FROM dbo.testTable

--This also works
GRANT SELECT TO subRole AS deploymentRole;

--This does not work
GRANT SELECT ON dbo.testTable TO subRole AS deploymentRole;
-- Cannot find the object 'testTable', because it does not exist
-- or you do not have permission.

While having SELECT (or another permission) on the entire database implies having SELECT on an object within the database, having the GRANT OPTION for SELECT on the entire database does not imply you have the GRANT OPTION for SELECT on an object in the database. Said another way, the GRANT OPTION does not seem to be recursive down the hierarchy.

In this case, I would like members of the deploymentRole to be able to grant subsets of their permissions to other principals without having to give explicit GRANT OPTIONs on all permissions for all objects now and in the future. Is there a way to apply the GRANT OPTION recursively or another paradigm I should be considering?

I do not want to grant permissions on individual objects as our schema is constantly changing. I want my role to be able to have database level permissions and also grant smaller subsets of its permissions without having to enumerate objects.

While having SELECT (or another permission) on the entire database implies having SELECT on an object within the database, having the GRANT OPTION for SELECT on the entire database does not imply you have the GRANT OPTION for SELECT on an object in the database.

Correct. There is a difference between "actual" / "explicit" permissions and "effective" permissions. Granting SELECT at the Database-level is a distinctly different permission than granting SELECT on one or more objects within that Database. You would see this difference when looking in the System Catalog View sys.database_permissions. Hence, SELECT permissions on individual tables is not an actual subset of the Database-level SELECT permission. The same holds true for SELECT permission on a Table as opposed to SELECT permission on individual columns in that Table.

Now, while it would help to get some feedback / clarification on the overall goal of this request, it at least appears -- based on the example code in the question -- that the desire is to have a deployment / rollout Login (or Logins) that are not in the sysadmin fixed server role. To that end, you can still accomplish this type of setup using Module Signing. The idea is to create a Certificate and Certificate-based Login and associated User that cannot be Impersonated (i.e. can't log in or become current Login/User via EXECUTE AS) but can hold all of the required permissions necessary to carry out the tasks that the deployment process needs to do. Then, you create one or more Stored Procedures that will execute the required commands. Signing the Stored Procedure(s) with the same Certificate that was used to create the Login and User will allow the permissions granted to the Certificate-based Login and User to apply to code within the Stored Procedure. Given the dynamic nature of the operation, it will need to be done via Dynamic SQL, but this works just fine with Module Signing.

This approach allows you to have a generic set of permissions (assigned to the Certificate-based Login / User) that can be used to assign other permissions. For the moment, it seems easiest to put the User in the db_owner fixed database role as that allows for doing anything you would need to do. And since Module Signing gives the permissions to the code, not the Login / User / Role executing the module (which is how it works with Impersonation via EXECUTE AS), anyone who can execute that code can only ever do what the code is set up to do. In this way, Module Signing allows for much more granular control over extending permissions than Impersonation (it doesn't matter what the permissions granted to the Certificate-based Login / User can do, it only matters what the signed code actually does). And if someone changes that signed code, then the module loses the signature, alerting you to the change, at which time you can review the changes and if ok, then re-sign the module.

Please see the example below that allows for granting SELECT on a particular object to a given role. It is very easy to extend the example to allow for creating the new Role as well as anything else that needs to be done as part of the deployment process.

Solution Setup, Part 1 (General Supporting Structure)

USE [master];
GO

CREATE CERTIFICATE [DeploymentPermissionsKey]
  ENCRYPTION BY PASSWORD = 'not_really_a_password'
  WITH SUBJECT = 'Deployment Permissions';

CREATE LOGIN [DeploymentPermissions]
  FROM CERTIFICATE [DeploymentPermissionsKey];

GRANT VIEW SERVER STATE TO [DeploymentPermissions];

DECLARE @Certificate VARBINARY(2000),
        @PrivateKey VARBINARY(2000);

SELECT @Certificate = CERTENCODED(CERT_ID(N'DeploymentPermissionsKey')),
       @PrivateKey = CERTPRIVATEKEY(CERT_ID(N'DeploymentPermissionsKey'),
                                    'EncryptPass', 'not_really_a_password');

---

USE [tempdb];

--DROP CERTIFICATE [DeploymentPermissionsKey];
DECLARE @CertSQL NVARCHAR(MAX) = N'
CREATE CERTIFICATE [DeploymentPermissionsKey]
  FROM BINARY = ' + CONVERT(NVARCHAR(MAX), @Certificate, 1) + N'
   WITH PRIVATE KEY ( BINARY = ' + CONVERT(NVARCHAR(MAX), @PrivateKey, 1) + N',
                      DECRYPTION BY PASSWORD = ''EncryptPass'',
                      ENCRYPTION BY PASSWORD = ''not_really_a_password''
                    );
';
PRINT @CertSQL;
EXEC(@CertSQL);

--DROP ROLE [DeploymentRole];
CREATE ROLE [DeploymentRole];

--DROP USER [MrDeploy];
CREATE USER [MrDeploy] WITHOUT LOGIN;

ALTER ROLE [DeploymentRole] ADD MEMBER [MrDeploy];

--DROP USER [DeploymentPermissions];
CREATE USER [DeploymentPermissions]
  FROM LOGIN [DeploymentPermissions];

ALTER ROLE [db_owner] ADD MEMBER [DeploymentPermissions];

Solution Setup, Part 2 (Allow Granting Permission to Role)

Please note that due to needing to use Dynamic SQL, we need to protect against SQL Injection by checking / verifying the string input parameters: @RoleName and @ObjectName.

USE [tempdb];
GO
CREATE
--ALTER
PROCEDURE dbo.GrantObjectPermissionsToRole
(
  @RoleName [sysname],
  @ObjectName NVARCHAR(261),
  @PermissionID TINYINT
)
AS
SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX);

-- Check parameter values to prevent errors and SQL Injection:
IF (NOT EXISTS(
               SELECT dp.*
               FROM   sys.database_principals dp
               WHERE  dp.[type] = 'R' -- DATABASE_ROLE
               AND    dp.[name] = @RoleName
              )
    )
BEGIN
  RAISERROR(N'Role does not exist: %s', 16, 1, @RoleName);
  RETURN -1;
END;

IF (OBJECT_ID(@ObjectName) IS NULL)
BEGIN
  RAISERROR(N'Object does not exist: %s', 16, 1, @ObjectName);
  RETURN -2;
END;

SET @SQL = N'GRANT '  + CASE @PermissionID
                           WHEN 1 THEN N'SELECT'
                           WHEN 2 THEN N'EXECUTE'
                           ELSE N'Invalid_PermissionID_'
                                + CONVERT(NVARCHAR(10), @PermissionID)
                         END
            + N' ON ' + @ObjectName
            + N' TO ' + @RoleName
            + N' AS [dbo];';

PRINT @SQL; -- debug
EXEC(@SQL);

RETURN;
GO

GRANT EXECUTE ON dbo.[GrantObjectPermissionsToRole] TO [DeploymentRole];

ADD SIGNATURE TO dbo.[GrantObjectPermissionsToRole]
  BY CERTIFICATE [DeploymentPermissionsKey]
  WITH PASSWORD = 'not_really_a_password';

Example Setup

USE [tempdb];
GO

CREATE TABLE [dbo].[TestTable] ([ID] INT NULL);

CREATE ROLE [TestRole];

CREATE USER [TestUser] WITHOUT LOGIN;

ALTER ROLE [TestRole] ADD MEMBER [TestUser];

Tests

Test 1 shows that initially, TestUser, who is in TestRole, does not have permission to SELECT from TestTable.

Test 2 shows that the deployment User, MrDeploy, also does not have SELECT permission on TestTable. Additionally, MrDeploy does not even have the ability to grant any permissions to TestRole. However, MrDeploy can execute the Stored Procedure which does grant the SELECT permission to TestRole.

Test 3 shows that TestUser, via its membership in TestRole, now does have SELECT permission on TestTable. Using sys.fn_my_permissions() we can see the "effective" permissions which cascade down to individual columns. However, selecting from sys.database_permissions we can see the "actual" permission which is just on the Table itself (showing that actual permissions do not cascade).

Test 4 grants the Database-level SELECT permission to MrDeploy so that it can be tested against the sys.fn_my_permissions() functions (to see how the permission cascades down to subobjects) and the sys.database_permissions system catalog view (to see that there are no object-level permissions at all). This again illustrates the difference between "effective" and "actual" / "explicit" permissions.

--- Test # 1 ---
EXECUTE AS USER = 'TestUser';
SELECT SESSION_USER AS [CurrentUser];

SELECT * FROM [dbo].[TestTable]; -- error:
-- The SELECT permission was denied on the object 'TestTable',
--    database 'tempdb', schema 'dbo'.

REVERT;
SELECT SESSION_USER AS [CurrentUser];

--- Test # 2 ---
EXECUTE AS USER = 'MrDeploy';
SELECT SESSION_USER AS [CurrentUser];

SELECT * FROM [dbo].[TestTable]; -- error:
-- The SELECT permission was denied on the object 'TestTable',
--    database 'tempdb', schema 'dbo'.

GRANT SELECT ON [dbo].[TestTable] TO [TestUser]; -- error:
-- Cannot find the object 'TestTable', because it does not exist or you
--    do not have permission.

GRANT SELECT ON [dbo].[TestTable] TO [TestUser] AS [dbo]; -- error:
-- Cannot find the user 'dbo', because it does not exist or you do not have permission.

SELECT dp.*
FROM   sys.database_principals dp
WHERE  dp.[type] = 'R' -- DATABASE_ROLE
AND    dp.[name] = N'TestRole'; -- no rows due to no VIEW SERVER STATE permission

EXEC dbo.[GrantObjectPermissionsToRole]
  @RoleName = N'TestRole',
  @ObjectName = N'TestTable',
  @PermissionID = 1;
-- Success!

SELECT * FROM [dbo].[TestTable]; -- error (still):
-- The SELECT permission was denied on the object 'TestTable',
--    database 'tempdb', schema 'dbo'.

REVERT;
SELECT SESSION_USER AS [CurrentUser];

--- Test # 3 ---
EXECUTE AS USER = 'TestUser';
SELECT SESSION_USER AS [CurrentUser];

SELECT * FROM [dbo].[TestTable]; -- Success!!

SELECT * FROM sys.fn_my_permissions(N'dbo.TestTable', 'OBJECT');

SELECT *,
       USER_NAME([grantee_principal_id]) AS [Grantee],
       USER_NAME([grantor_principal_id]) AS [Grantor]
FROM   sys.database_permissions
WHERE  [class] = 1 -- OBJECT_OR_COLUMN
AND    [major_id] = OBJECT_ID(N'dbo.TestTable');

REVERT;
SELECT SESSION_USER AS [CurrentUser];

--- Test # 4 ---
GRANT SELECT TO [MrDeploy];

EXECUTE AS USER = 'MrDeploy';
SELECT SESSION_USER AS [CurrentUser];

SELECT * FROM sys.fn_my_permissions(N'dbo.TestTable', 'OBJECT');

SELECT *,
       USER_NAME([grantee_principal_id]) AS [Grantee],
       USER_NAME([grantor_principal_id]) AS [Grantor]
FROM   sys.database_permissions
WHERE  [class] = 1 -- OBJECT_OR_COLUMN
AND    [major_id] = OBJECT_ID(N'dbo.TestTable');

REVERT;
SELECT SESSION_USER AS [CurrentUser];