Invalid connection error on SQL Server 2008 I have this third party application that is interfacing with a MS SQL Server and I have some issue migrating its database from a MS SQL Server 2005 to A MS SQL Server 2008. The application was provided by a manufacturing company (it interfaces with a machinery) but produced by someone else (let's say a third third-part). I tried to contact our provider (the machinery producer), but basically they said they cannot reach the application developer and the product is unsupported. So I am on my own to solve this thing.

Currently the database instance is located on a box with the following features:
-Virtualized Windows 2003 SBS Server
-SQL Server 2005 (9.4.5000.00)

It has to be migrated to the following:
-Virtualized Windows 2008 R2
-SQL Server 2008 R2 SP2 (10.52.4000.0)

Both SQL Server are listening on the standard 1433 port.
The application connects to the DB through SQL authentication on a local network.

I have successfully installed the server and the db (.mdf and .ldf files) on the new MS SQL 2008 server: I can query the db through Sql Server Management Studio and it returns correct results. The DB is really simple: few tables with few relationships: basically the application just needs to send a code and retrieve a string. I can also connect to the db through the SQL Server Management Studio (using both windows and SQL authentication), but the application fails to connect, issuing this error: "[DBNETLIB][ConnectionOpen (Invalid Instance()).] Invalid connection".

I am not a MS SQL Server expert (I use it just for this application), can someone help me out?
Thanks,
Gio

I found a similar question with an answer, this one https://superuser.com/questions/314206/cannot-connect-to-sql-server-from-excel-2007 on superuser. It turned out that for some reason, on the MS SQL Server 2008 I had to put a named instance and not just the name of the server, e.g. if the server name is "yoda", on the application configuration I have just to put "yoda" if the backend is a MS SQL Server 2005, if I use MS SQL Server 2008 I have to put "yoda\sqlexpress,1433". I do not know if an alternative solution could be updating some libraries.


MongoVUE connect to mongodb with authencation failed! I had a mongodb, and a database named genbank. Genbank has a user whose role is "read" and "readAnyDatabase". It is very strange that I cannot connect to genbank through this role by MongoVUE, but I can connect successfully under administrator role.

The connection configuration is:

genbankReader is the username for genbank database.

the error message is:

Unable to connect to server *.*.*.*:40000: Invalid credential for database 'admin'..
Type: MongoDB.Driver.MongoConnectionException
Stack:    在 MongoDB.Driver.Internal.DirectMongoServerProxy.Connect(TimeSpan timeout, ReadPreference readPreference)
   在 MongoDB.Driver.MongoServer.Connect(TimeSpan timeout)
   在 MongoDB.Driver.MongoServer.Connect()
   在 MangoUI.MMongo.m7FnMeKibMFwcOmRDx4q(Object )
   在 MangoUI.MMongo.Open(Boolean useSamus)
   在 MangoUI.MMongo.Open()
   在 MangoUI.ComNavTree.d0Pck1r6Vh91JYOyd29(Object )
   在 MangoUI.ComNavTree.ExpandMe(MTreeNode expand)
   在 MangoUI.ComNavTree.tree_BeforeExpand(Object sender, TreeViewCancelEventArgs e)

Invalid credential for database 'admin'.
Type: MongoDB.Driver.MongoAuthenticationException
Stack:    在 MongoDB.Driver.Communication.Security.MongoCRAuthenticationProtocol.Authenticate(MongoConnection connection, MongoCredential credential)
   在 MongoDB.Driver.Communication.Security.Authenticator.Authenticate(MongoCredential credential)
   在 MongoDB.Driver.Communication.Security.Authenticator.Authenticate()
   在 MongoDB.Driver.Internal.MongoConnection.Open()
   在 MongoDB.Driver.Internal.MongoConnection.GetNetworkStream()
   在 MongoDB.Driver.Internal.MongoConnection.SendMessage(BsonBuffer buffer, MongoRequestMessage message, WriteConcern writeConcern, String databaseName)
   在 MongoDB.Driver.Internal.MongoConnection.SendMessage(MongoRequestMessage message, WriteConcern writeConcern, String databaseName)
   在 MongoDB.Driver.Internal.MongoConnection.RunCommandAs[TCommandResult](String databaseName, QueryFlags queryFlags, CommandDocument command, IBsonSerializer commandResultSerializer, IBsonSerializationOptions commandResultSerializationOptions, Boolean throwOnError)
   在 MongoDB.Driver.Internal.MongoConnection.RunCommandAs[TCommandResult](String databaseName, QueryFlags queryFlags, CommandDocument command, Boolean throwOnError)
   在 MongoDB.Driver.MongoServerInstance.Ping(MongoConnection connection)
   在 MongoDB.Driver.MongoServerInstance.Connect()
   在 MongoDB.Driver.Internal.DirectMongoServerProxy.Connect(TimeSpan timeout, ReadPreference readPreference)

Command 'authenticate' failed: auth fails (response: { "code" : 18, "ok" : 0.0, "errmsg" : "auth fails" })
Type: MongoDB.Driver.MongoCommandException
Stack:
Inputs::
Command:  authenticate
Ok:       False
ErrorMsg: auth fails
Request:  { "authenticate" : 1, "user" : "genbankReader", "nonce" : "264abe28fc4dd95", "key" : "ca2124ded060247d527941e55820fd80" }
Response: { "code" : 18, "ok" : 0.0, "errmsg" : "auth fails" }

My db user always uses mongoVUE but I cannot give him other privilege except for read. So, can any one help me solve my problem?

Your error looks like a standard "auth failed" exception... If all the parameters of your connection are correct for your setup, I'd suggest you try to logon from the mongo shell directly:

$>mongo 1.2.3.4:40000/genbank  -u genBankReader -p passwd1234 --authenticationDatabase admin --verbose

Use the --verbose flag to log on as both the "read-only" user and the "admin" user and see if you can tell more of what's wrong (if any).

Alternative you can use another mongo client GUI like Robomongo and see if that works.
(I used to use MongoVue but switched to Robomongo as it was less confusing and more shell-oriented)

Otherwise, you could have setup/added the users incorrectly. Use the admin user to check db.system.users collection of the proper database just to make sure. Perhaps take another look at http://docs.mongodb.org/manual/reference/privilege-documents/

Hope this helps, good luck


Add Primary Key column at the end of an Index If I have a table with the following primary key:

PRIMARY KEY (id)

And I want to query documents written by someone (e.g. display a preview of the latest 20), sorted by their "id", so I created an index:

INDEX from_id (from, id)

Is the "id" at the end of this index redundant because it ends with the primary key?
If yes, why?

In InnoDB, every "secondary" index (that is, index other than "PRIMARY") automatically adds the columns of the PK.

These are 'identical':

PRIMARY KEY(id),  INDEX(from, id)
PRIMARY KEY(id),  INDEX(from)

You could even do

PRIMARY KEY(from, id),  INDEX(id)

This combo is beneficial when id is AUTO_INCREMENT, but you often do a range on from.  Now the rows are "clustered" together, thereby being more efficient to fetch.  Meanwhile, INDEX(id) keeps AUTO_INCREMENT happy -- since it needs to know where it left off.

I assume you are doing:

SELECT ...
    WHERE `from` = '...'
    ORDER BY id DESC
    LIMIT 20;


Fragmented clustered primary key (sequential GUID) index after processing - SQL Server I'm in the process of evaluating a schema change on the primary keys of several of our biggest tables in our software.
Till now we used varchar(36) for all of our PKs and FKs and Hibernate generated the random GUIDs in insert operations.
Moreover SQL server just makes all those columns clustered indexes also, so I'm worried now (after researching about) for the index fragmentation that all those inserts (and we have a lot of inserts and deletes and updates) will cause to the indexes.
A query made on the tables showed that almost all indexes are heavily fragmented.

So I decided to change the column type for several of our biggest tables (between 3 million and 1 million, so not that big) from varchar(36) to unique identifier with the DEFAULT option newsequentialid().

I did a test yesterday on a much smaller db: after changing those PKs and after a big processing (about 20k inserts and I think the same amount of deletes) it shows an index fragmentation for the clustered keys (id) from 30% to 40%.

How is this possible?

Of course I rebuilt the indexes before the processing.

These are the fragmentation data of ax example table (46528 rows) -> 42% fragmentation:

Schema  Table   Index   avg_fragmentation_in_percent    page_count  fragment_count
dbo e2_per_out_presenze PK_e2_per_out_presenze_id   42,91   3714    1704
dbo e2_per_out_presenze FK_e2_per_out_presenze_2    1,78    448       36
dbo e2_per_out_presenze FK_e2_per_out_presenze_3    0       194       11
dbo e2_per_out_presenze FK_e2_per_out_presenze_4    0       444       12
dbo e2_per_out_presenze FK_e2_per_out_presenze_6    0       161       16
dbo e2_per_out_presenze FK_e2_per_out_presenze_7    0       166       14
dbo e2_per_out_presenze Index_8                     0       466       28

Now I'm worried that this schema change won't help that much.

EDIT: I think it's important to note that the fill factor on the tables is set to 80 and that after the insert it's very possible that the row gets also updated.

For the sake of the test I also include the fragmentation data of the table after the same test but without the sequential GUID (it shows anyway a big improvement in fragmentation, but I wonder how will it perform on bigger tables)

Schema  Table               Index                       avg_fragmentation_in_percent    page_count  fragment_count
dbo     e2_per_out_presenze FK_e2_per_out_presenze_7    95,03676471                     544         524
dbo     e2_per_out_presenze FK_e2_per_out_presenze_3    94,43413729                     539         515
dbo     e2_per_out_presenze FK_e2_per_out_presenze_6    93,56060606                     528         501
dbo     e2_per_out_presenze FK_e2_per_out_presenze_2    92,22343921                     913         854
dbo     e2_per_out_presenze PK_e2_per_out_presenze_id   86,9864487                      4649        4102
dbo     e2_per_out_presenze FK_e2_per_out_presenze_4    68,6827957                      744         522
dbo     e2_per_out_presenze Index_8                     0                               501         21

Now I'm worried that this schema change won't help that much.

Changing the column from a varchar(36) to a uniqueidentifier will greatly reduce storage requirements from 36 bytes per row to 16 bytes per row; for each 100,000 row table, that equates to a savings of 2MB.  2MB may not sound like much by itself, but since the column is the primary key, that 2MB applies to each non-clustered index on the table.  If any other tables are using foreign keys to ensure relational integrity, the space savings applies to those tables as well.  Realize also that this space savings doesn't only apply to on-disk storage, it also applies equally, and one might argue, more importantly, to rows contained in the buffer-pool.

It appears from a cursory glance at your sample table above that the average row size is somewhere around 600 bytes.  Converting the varchar(36) to a uniqueidentifier will immediately offer a 3.3% space savings for the clustered index, and more than that for each non-clustered index.

As a bonus, once you convert the column from a varchar(36) to a uniqueidentifier, it will no longer be possible to insert this is a bad key into the column, as is currently possible.  This will result in much better data integrity, and will potentially save you a lot of work down the road.

Having said that, I decided to try to recreate the problems you're seeing with index fragmentation.  I created a test table with the varchar(36) primary key column with 100,000 rows.  I then converted the column to a uniqueidentifier and was unable to see a drastic increase in index fragmentation.  My test bed code is:

USE tempdb;

/* create our test table, with a VARCHAR primary key */
IF OBJECT_ID('dbo.guid_insert') IS NOT NULL
DROP TABLE dbo.guid_insert;
CREATE TABLE dbo.guid_insert
(
    PK VARCHAR(36) NOT NULL
        CONSTRAINT PK_guid_insert
        PRIMARY KEY CLUSTERED
        WITH (
            DATA_COMPRESSION = NONE
            , PAD_INDEX = OFF
            , ALLOW_ROW_LOCKS = ON
            , ALLOW_PAGE_LOCKS = ON
            , FILLFACTOR = 80
            )
    , SomeData VARCHAR(600)
    , CreatedDate DATETIME
        CONSTRAINT DF_quid_insert_CreatedDate
        DEFAULT (GETDATE())
) ON [PRIMARY];

/* insert 100,000 rows into the test table */
INSERT INTO dbo.guid_insert (PK, SomeData)
SELECT NEWID(), REPLICATE('.', 600)
FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(num)
CROSS APPLY (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v1(num)
CROSS APPLY (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v2(num)
CROSS APPLY (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v3(num)
CROSS APPLY (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v4(num);

/* show the fragmentation details */
SELECT ObjectName = QUOTENAME(s.name) + '.' + QUOTENAME(o.name)
    , IndexName = QUOTENAME(i.name)
    , [FillFactor] = 'FILLFACTOR = ' + CONVERT(VARCHAR(50), CASE WHEN i.fill_factor = 0 THEN '100' ELSE i.fill_factor END)
    , CompressOption = 'DATA_COMPRESSION = ' + p.data_compression_desc
    , NumberOfFragments = ips.fragment_count
    , NumberOfPages = ips.page_count
    , AvgFragmentSizeInPages = ips.avg_fragment_size_in_pages
    , AvgFragmentationInPercent = ips.avg_fragmentation_in_percent
    , IndexType = i.type_desc
    , IsUnique = i.is_unique
    , IsPrimary = i.is_primary_key
    , IsPartitioned = CASE WHEN ps.data_space_id IS NULL THEN 0 ELSE 1 END
    , IsClustered = CASE WHEN i.type = 1 THEN 1 ELSE 0 END
FROM sys.indexes i WITH (NOLOCK)
    INNER JOIN sys.objects o WITH (NOLOCK) ON i.object_id = o.object_id
    INNER JOIN sys.schemas s WITH (NOLOCK) ON o.schema_id = s.schema_id
    INNER JOIN sys.data_spaces ds WITH (NOLOCK) ON i.data_space_id = ds.data_space_id
    INNER JOIN sys.partitions p WITH (NOLOCK) ON o.object_id = p.object_id
                                    AND i.index_id = p.index_id
    LEFT JOIN sys.partition_schemes ps WITH (NOLOCK) ON ds.data_space_id = ps.data_space_id
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), o.object_id, i.index_id, p.partition_number, 'DETAILED') ips
WHERE o.is_ms_shipped = 0
    AND NOT (
        o.type = 'TF' -- table valued function
        OR o.type = 'TT' -- table type
        OR o.type = 'SO' -- sequence object
        )
    AND i.index_id > 0
    AND i.is_disabled = 0
    AND i.is_hypothetical = 0
    AND (
        ds.type = 'FG' -- filegroup
        OR ds.type = 'PS' -- partition stream
        )
    AND i.type_desc IN
        ( --we only support rebuilding/reorganizing these index types:
            'CLUSTERED'
            , 'NONCLUSTERED'
            , 'XML'
        )
    AND ips.index_level = 0 -- leaf-levels only
    AND ips.fragment_count > 1
ORDER BY ips.fragment_count * ips.avg_fragmentation_in_percent DESC;

The fragmentation query results:

/* rebuild the index to remove fragmentation */
ALTER INDEX PK_guid_insert ON dbo.guid_insert REBUILD;

/* drop the primary key, since we can't modify the PK column when it is a primary key */
ALTER TABLE dbo.guid_insert
DROP CONSTRAINT PK_guid_insert;

/* convert the VARCHAR PK column into a UNIQUEIDENTIFIER column */
ALTER TABLE dbo.guid_insert
ALTER COLUMN PK UNIQUEIDENTIFIER NOT NULL;

/* recreate the primary key */
ALTER TABLE dbo.guid_insert
ADD CONSTRAINT PK_guid_insert
PRIMARY KEY CLUSTERED (PK);

/* add a DEFAULT constraint to create sequential keys */
ALTER TABLE dbo.guid_insert
ADD CONSTRAINT DF_guid_insert_PK
DEFAULT (NEWSEQUENTIALID())
FOR PK;

/* delete 10,000 random rows */
;WITH src AS
(
SELECT rn = ROW_NUMBER() OVER (ORDER BY NEWID()), *
FROM dbo.guid_insert
)
DELETE
FROM src
WHERE rn < 10000;

/* insert 100,000 more rows */
INSERT INTO dbo.guid_insert (SomeData)
SELECT REPLICATE('.', 600)
FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(num)
CROSS APPLY (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v1(num)
CROSS APPLY (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v2(num)
CROSS APPLY (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v3(num)
CROSS APPLY (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v4(num);

/* show the fragmentation details */
SELECT ObjectName = QUOTENAME(s.name) + '.' + QUOTENAME(o.name)
    , IndexName = QUOTENAME(i.name)
    , [FillFactor] = 'FILLFACTOR = ' + CONVERT(VARCHAR(50), CASE WHEN i.fill_factor = 0 THEN '100' ELSE i.fill_factor END)
    , CompressOption = 'DATA_COMPRESSION = ' + p.data_compression_desc
    , NumberOfFragments = ips.fragment_count
    , NumberOfPages = ips.page_count
    , AvgFragmentSizeInPages = ips.avg_fragment_size_in_pages
    , AvgFragmentationInPercent = ips.avg_fragmentation_in_percent
    , IndexType = i.type_desc
    , IsUnique = i.is_unique
    , IsPrimary = i.is_primary_key
    , IsPartitioned = CASE WHEN ps.data_space_id IS NULL THEN 0 ELSE 1 END
    , IsClustered = CASE WHEN i.type = 1 THEN 1 ELSE 0 END
FROM sys.indexes i WITH (NOLOCK)
    INNER JOIN sys.objects o WITH (NOLOCK) ON i.object_id = o.object_id
    INNER JOIN sys.schemas s WITH (NOLOCK) ON o.schema_id = s.schema_id
    INNER JOIN sys.data_spaces ds WITH (NOLOCK) ON i.data_space_id = ds.data_space_id
    INNER JOIN sys.partitions p WITH (NOLOCK) ON o.object_id = p.object_id
                                    AND i.index_id = p.index_id
    LEFT JOIN sys.partition_schemes ps WITH (NOLOCK) ON ds.data_space_id = ps.data_space_id
CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), o.object_id, i.index_id, p.partition_number, 'DETAILED') ips
WHERE o.is_ms_shipped = 0
    AND NOT (
        o.type = 'TF' -- table valued function
        OR o.type = 'TT' -- table type
        OR o.type = 'SO' -- sequence object
        )
    AND i.index_id > 0
    AND i.is_disabled = 0
    AND i.is_hypothetical = 0
    AND (
        ds.type = 'FG' -- filegroup
        OR ds.type = 'PS' -- partition stream
        )
    AND i.type_desc IN
        ( --we only support rebuilding/reorganizing these index types:
            'CLUSTERED'
            , 'NONCLUSTERED'
            , 'XML'
        )
    AND ips.index_level = 0 -- leaf-levels only
    AND ips.fragment_count > 1
ORDER BY ips.fragment_count * ips.avg_fragmentation_in_percent DESC; --start with the most fragmented indexes first

The fragmentation details after the conversion, with deleting and inserting rows:

As you can see, after the conversion, and deleting 10,000 rows, then inserting 100,000 rows, index fragmentation for the primary key index (the table itself) is just 0.41%; hardly a concern.

If you need further help with this problem (I realize your question is a year old), you need to show the DDL for the table, its indexes, and the code you used for the conversion test.


Alter column opreation failed due to disk space in SQL Server I am attempting to alter a column on a specific table; however the process is failing because the log file runs out of space once the disk runs out of space.

The size of the table is less than the amount of free disk space.

Can anyone tell me what I'm doing wrong?

Scenario :

Alter column size from numeric(9,10) to numeric(18,6)

Statistics :

Table size - 40 GB
Table rows - 40 million
Disk free space where this table located - 50 GB
Database Recovery model : Full
Mirroring is configured
SQL Server 2012 Enterprise

The error I'm receiving is:

  Alter column operation is failed with an error "The statement has been terminated. The transaction log for database 'x' is full due to 'LOG_BACKUP'

Numeric(9, 10) is not a valid data type specification.  The scale must be less than or equal to the precision.  I'll assume the actual column has a smaller scale specification.

When you change a column from a precision of 1-9 to one of 10-19, the storage size increases from 5 to 9 bytes.  This is a fully logged operation regardless of your recovery model and logging of DDL operations is expensive as you have found.

One method is to add a new NULL column, and then update the new column value from the old one in batches in order to reduce log space requirements and allow updates to be applied to the mirror.  Once all rows are updated, drop the old column and rename the old to the new.  It is best to update in ranges of your clustered index key.  If you need help with the batching of updates, post your existing table structure.

Keep in mind that mirroring might not be able to keep up during the operation (increasing log space needs) even if you backup the log during the update.  You should do this during a maintenance window anyway so you have the option of removing the mirror during the maintenance and reestablishing afterward.

Another method is to create a new table with only the clustered index, load with INSERT...SELECT, drop the old table and rename the new table to the old name, and recreate constraints and indexes.  This would allow you to minimize logging by removing the mirror and changing the recovery model to SIMPLE or BULK_LOGGED during the maintenance.


How to do a one-time log shrink on a database with transactional replication I have inherited a 10 GB database with an 80 GB log [only 3% in use according to DBCC SQLPERF(logspace)]. It seems safe to assume the extreme log growth was due to problems from long before I was hired.

The primary has a log shipping backup job that runs every 15 minutes. The primary has copy and restore jobs that run every 15 minutes.

When I attempt to shrink the log, I get "Cannot shrink log file 2 (DatabaseName_log) because the logical log file located at the end of the file is in use." I have retried this several times 15 minutes apart and even days apart, but always get the same result.

DBCC LOGINFO shows 784 VLFs, with only the first 245 and the last on with a status of 2. p_WhoIsActive shows that the longest open transaction has been running for less than 2 hours (long transactions are not unusual here due to a third-party Microsoft Access app with ODBC driver issues).

How can I successfully shrink this log (without creating an outage for users)?

Thanks,
Mark

The easiest solution to this issue is to set the database to simple recovery, shrink the log, then set it back to full recovery. In T-SQL this would be:

ALTER DATABASE [database] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE([logfilename], 1)
ALTER DATABASE [database] SET RECOVERY FULL WITH NO_WAIT

Changing the database to simple recovery may cause transactions to stop being replicated, but if scheduled during off hours it should shrink the log and return to full recovery with no side effects.

If that is unacceptable, you can use the sp_repldone stored procedure to manually mark transactions as distributed and reset the replication status of invalid transactions:

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,    @time = 0, @reset = 1


select n values per group Lets say I have the following table

-----------------------------
| user_id   | comment       |
-----------------------------
| 2         | thats cool    |
| 2         | awesome       |
| 3         | i hate this   |
| 3         | okay          |
| 6         | this is weird |
| 6         | hello?        |
| 6         | what is it    |
| 9         | how are you   |
| 16        | too slow      |
| 16        | yes           |
| 17        | alrighty      |
-----------------------------

How can you select two rows per user_id? So my results would be:

-----------------------------
| user_id   | comment       |
-----------------------------
| 2         | awsome        |
| 2         | thats cool    |
| 3         | i hate this   |
| 3         | okey          |
| 6         | this is weird |
| 6         | hello?        |
| 9         | how are you   |
| 16        | too slow      |
| 16        | yes           |
| 17        | alrighty      |
-----------------------------

Is this possible with a single efficient query? Or are sub-selected necessary?

You can use the technique described in:

http://blog.sqlauthority.com/2014/03/09/mysql-reset-row-number-for-each-group-partition-by-row-number/

to mimic:

row_number() over (partition by ... order by ...)

In your case that would be something like:

SELECT user_id, comment, row_number
FROM (
    SELECT @row_number:=CASE WHEN @user_id=user_id
                             THEN @row_number+1
                             ELSE 1
                        END AS row_number
         , @user_id:=user_id AS user_id
        , comment
    FROM t
       , (SELECT @row_number:=0,@user_id:='') AS u
    ORDER BY user_id, comment
) as v
WHERE row_number <= 2;


Store large compressed documents in Postgresql Where I work we keep approximately 500,000 image files related to processing for our customer in a file system attached to our Redhat 5.11 production server. We are planning an upgrade to Redhat 7.4 and have already done this for our development database servers. The number of documents is increasing and while we do need to archive older documents, we are talking about moving storage of the documents into a database where we have access to auditing and database level security. If we store the objects in Oracle, they won't be compressed unless we pay for the advanced compression option. We would like top avoid that cost.

Does Postgresql have similar compression for large binary objects? Does it make sense to try using Postgresql for this? I would use Database Heterogeneous Connectivity from Oracle to read and write the large objects. Are there other tools that people have used to store large objects in databases? Thanks

Postgres compresses automatically large objects chunks, as well as bytea through the TOAST mechanism.

But most PDFs are already compressed. As an example I have 1000 dedup-ed pdfs in my mailbox stored as large objets totalling 281MB, and the compression ratio brought by postgres on these is only 11%.

For large objects you may query the compressed size, as a superuser, with

SELECT sum(pg_column_size(data)-4) FROM pg_largeobject WHERE loid = :id

Compression of BYTEA might be a bit better because it compresses the entire contents as a whole (limited to 1GB), whereas large objects are compressed as individual chunks of 2048 bytes or less (in fact, pagesize/4)

The compression ratio of bytea contents can be measured by comparing octet_length(column) with pg_column_size(column)-4

  Does it make sense to try using Postgresql for this?

You should probably import a few hundred of your files in Postgres and see how they compress. For large objects it's a simple as \lo_import filename in psql.


SQL Database automatic back up I have an SQL database - I need to back it up everyday (at a minimum) - preferably multiple times a day - automatically so it definitely gets done.

I'm running Windows which the SQL database is stored on

What programs (prefer free ones) can I use to back it up - I'm completely new with SQL so if you're gonna explain something - do it in layman's terms thanks (:

It needs to get backed up to another server (on the lan).

Cheers for any help you can give!

You can do this directly through SQL Server. You can schedule the backups through SQL Server Agent and just call the backup command through the job. The job will allow you to schedule it as you please.

To make this even a bit easier you can create a Maintenance Plan directly in SQL Server and specify backing up the database.

There are a few options here, and no need to look outside of SQL Server for this functionality.

Here's an article on how to do this through a maintenance plan: http://weblogs.asp.net/sreejukg/archive/2010/01/20/scheduling-automated-backup-using-sql-server-2008.aspx


comparing ip and subnet (ipv4 and ipv6) - mysql How can I check if an ipaddress is part of a subnet, given ipaddress can be ipv4 or ipv6?

I currently have it working for ipv4 only:

set @ip = '1.1.1.1';
set @subnet = '1.1.1.0/32';
set @xor_bw_ip_n_subnet = inet_aton(@ip) ^ inet_aton(substring_index(@subnet, '/', 1));
select ((@xor_bw_ip_n_subnet) >> 32-substring_index(@subnet,'/',-1)) = 0;   -- if ip in subnet, then should be 0

For ipv6, how can I use inet6_ntoa/inet6_aton to check if IPaddress is part of a subnet (basically trying to do bitwise operation on ipv6 address)?

I am using mysql v5.6.19

Thanks,

I assume your IPv6 is in BINARY(16)?  Build a 128-row table of the BINARY(16) values for the subnet masks.  (/128 is all 1's.)  JOIN to the table and do masking on each half of the address --

WHERE (LEFT(ip, 8)  & LEFT(mask, 8))  == LEFT(ip, 8)
  AND (RIGHT(ip, 8) & RIGHT(mask, 8)) == RIGHT(ip, 8)

(No shifting.)


Can I recover an overwritten incident trace file? I was looking at incident trace file in oracle database but some how that file got overwritten. As of now I have ecrep_ora_26467_i49966.trm but trhe corresponding .trc file is lost. is it possible to recreate the lost trace file

No, not within Oracle.  This can only be recovered if you backed it up externally.


SQL Server backup job last 5 days We've been having this nightly backup job on SQL Server 2012 but it appends to the .bak file instead of overwriting, so while googling I discovered master.dbo.xp_delete_file

I noticed that I can delete several backups at once prior than date parameter

I'm thinking in keeping 3 days thus deleting like this:

declare @dt datetime
select @dt=getdate()-3
EXECUTE master.dbo.xp_delete_file 0,N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\',N'bak',@dt,1

All seems ok, but for the backup, can I create filenames like this? (note the @today in CONCAT() )

declare today varchar(10);
SELECT @today=CONVERT(char(10), GetDate(),126);

BACKUP DATABASE perfMaster TO DISK = CONCAT('D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\perfMaster_',@today,'.bak'
WITH NOFORMAT, NOINIT,
NAME = N'Full Database perfMaster Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 20

Or what methods do you employ to accomplish backups with some days history?

I use INIT and then my full backups are separate (and include a timestamp to immediately see when they were taken). I can use any method I want to clear out old files (C#, PowerShell, what have you, without having to know the internals of the backup files at all), rather than using xp_delete_file - which is undocumented, unsupported, and could change or be removed in a future release or even a service pack. I don't understand any advantage to stuffing multiple backup files into the same file (which you can't do if you name the file differently using the date anyway). I also don't know why you need to use CONCAT here, but in any case, you can't define an expression in the DDL that way. Why not just:

DECLARE @backup_file VARCHAR(255) = 'D:\Program Files\Microsoft SQL '
  + 'Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\perfMaster_'
  + CONVERT(CHAR(10), GETDATE(), 126) + '.bak';

BACKUP DATABASE perfMaster TO DISK = @backup_file
  WITH INIT, NAME = N'Full Database perfMaster Backup',
  SKIP, NOREWIND, NOUNLOAD, STATS = 20;

I would also suggest:

using COMPRESSION if your edition supports it.
not storing your backups on the local D:\ drive. If you lose your hard disk, you've lost your database and the backups.


SSDT/SSIS 2012 - How to automatically load some projects? I did not use the default projects folder in SSDT, ie SSIS 2012. I used my own instead. Every time I open SSIS, I have to load the projects manually. Is there any way I can load them automatically ?

It's a little unclear what you're after, but here are two different features in SSDT (Visual Studio) you may want to take advantage of. These can be accessed by going to Tools | Options, then selecting the appropriate node from the tree as shown in the screenshots.

First, you can set SSDT to open the solution that was open when you closed the application the last time:

Second, you can set the default location for your projects, so when you go to Open a project, it automatically goes to this location first:


Multiple Availability groups under same IP Is it possible to have 4 Availability Groups distributed on 3 servers all of them under the same IP?

For example I would like the users to connect to DATABASE01 on SQLSTACK01 no matter if the Database is in the server 1 or server 3.

Thanks!

In theory, each Availability Group needs its own IP address (and virtual network name) for the listener. This way, you can fail the primary around between places - after all, you could have one AG that's a primary on server1, while you fail a separate one of the AGs over to server2 because it's having corruption problems.

In practice, you can use Availability Groups without a listener, and just have your apps connect to a specific SQL Server, bypassing the listener (and IP requirements) altogether. However, this means that:

Managing failover is completely up to you
Every time you have a failover, you have to touch every one of your app's connection strings because the server name will change (or you have to use a DNS CNAME)
You can't have some of the AGs be primary on one server, and some on another (in other words, all 4 of the primary writeable workloads have to be on the same server)
You can't use read-only routing - if you want to run read-only workloads on the secondaries, your apps have to connect to those other servers by name

It's a lot of drawbacks, but it IS doable. (StackOverflow was running this way for a while, not sure if they still are, because we had so many problems with the AG listeners.)


Unable to see measurements in influxdb I am new to influxdb and I am having this trouble to show measurements.

Our setup is that prometheus(aws ec2) is writing data to Influxdb(aws ec2). When I execute 'SHOW MEASUREMENT' I cannot see anything. However, when I execute 'SHOW SERIES' I can see lot of time series.

Is there anyone who knows why I am seeing this? Thank you in Advance

The command is SHOW MEASUREMENTS.  You left off the 'S' at the end.

Hope that helps!


Tinyint structure is confusing I am confused to see this tinyint(3) structure. If tinyint can store only 1 byte then what is the purpose of giving the size in brackets? Is there something special with this parameter in MySQL?

That's not bytes, it's display width.  It's used as a hint for the maximum expected typical character width of values for the column, to applications that care to actually use the metadata -- which many don't.
As explained in the documentation:

MySQL supports an extension for optionally specifying the display width of integer data types in parentheses following the base keyword for the type. For example, INT(4) specifies an INT with a display width of four digits. This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. Whether it is used or not is up to the application.)
The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column display width from being displayed correctly. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range permitted by three digits are displayed in full using more than three digits.

The default value for an unsigned tinyint is (3) because that's the size of "255" which the widest number (in displayed character positions) that can be stored in an an unsigned tinyint.
The display width also does not change the storage requirements for the data types; so, for example, an INT(1) still takes up 4 bytes of storage.


Oracle11.2g post-installation problem on Oracle Linux kernel After installation of Oracle 11.2g in silent mode on the Oracle Linux kernel, I have the following error:

[u01/app/oracle/product/11/bin] $ sqlplus

sqlplus error while loading shared libraries: libsqlplus.so: cannot open shared
object file: No such file or directory

However, the shared object seems to be setup correctly:

[/u01/app/oracle/product/11/lib] $ ls -lrt /u01/app/oracle/product/11/lib | grep sqlplus
-rw-r--r--. 1 oracle oinstall   1475820 Aug  1  2009 libsqlplus.a
-rwxr-xr-x. 1 oracle oinstall   1218075 Aug  1  2009 libsqlplus.so

The user is oracle:

[oracle ~]$ id oracle
uid=501(oracle) gid=501(oinstall) groups=501(oinstall),502(dba)

The environment variables are:

[oracle ~]$ env
USER=oracle
LD_LIBRARY_PATH=/u01/app/oracle/product/11/lib
ORACLE_SID=orcl
ORACLE_BASE=/u01/app/oracle
PATH=/usr/lib/qt-3.3/bin:/usr/local/bin
    :/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin: /home/oracle/bin
    :/u01/app/oracle/product/11/bin
HOME=/home/oracle
ORACLE_HOME=/u01/app/oracle/product/11

kernel version:

2.6.32-300.3.1.el6uek.i686

*UPDATE FOR ANSWER *

[oracle@orcus lib]$ ldd /u01/app/oracle/product/11/bin/sqlplus
    linux-gate.so.1 =>  (0xb771f000)
    libsqlplus.so => /u01/app/oracle/product/11/lib/libsqlplus.so (0xb766e000)
libclntsh.so.11.1 => not found
    libnnz11.so => /u01/app/oracle/product/11/lib/libnnz11.so (0xb7415000)
    libdl.so.2 => /lib/libdl.so.2 (0x4c5dc000)
    libm.so.6 => /lib/libm.so.6 (0x4c616000)
    libpthread.so.0 => /lib/libpthread.so.0 (0x4c5e3000)
    libnsl.so.1 => /lib/libnsl.so.1 (0x4caef000)
    libc.so.6 => /lib/libc.so.6 (0x4c44a000)
    /lib/ld-linux.so.2 (0x4c424000)
    libclntsh.so.11.1 => not found

* after chmod /u01/app/oracle/product/11/lib/libclntsh* *

$ ldd /u01/app/oracle/product/11/bin/sqlplus
    linux-gate.so.1 =>  (0xb7831000)
    libsqlplus.so => /u01/app/oracle/product/11/lib/libsqlplus.so (0xb7780000)
    libclntsh.so.11.1 => /u01/app/oracle/product/11/lib/libclntsh.so.11.1 (0xb5a7b000)
    libnnz11.so => /u01/app/oracle/product/11/lib/libnnz11.so (0xb582e000)
    libdl.so.2 => /lib/libdl.so.2 (0x4c5dc000)
    libm.so.6 => /lib/libm.so.6 (0x4c616000)
    libpthread.so.0 => /lib/libpthread.so.0 (0x4c5e3000)
    libnsl.so.1 => /lib/libnsl.so.1 (0x4caef000)
    libc.so.6 => /lib/libc.so.6 (0x4c44a000)
    libaio.so.1 => /lib/libaio.so.1 (0x4c446000)
/lib/ld-linux.so.2 (0x4c424000)

Any idea why SQLPlus is complaining about libsqlplus.so?

Make libclntsh executable:

chmod +rx /u01/app/oracle/product/11/lib/libclntsh*

Then check the above commented ldd command again.


Why am I getting permission denied on an ownership change? My current user is pronto

mediapop_staging=> select current_user;
 current_user
--------------
 pronto
(1 row)

This user a standard super user created on AWS RDS.

mediapop_staging=> \du pronto
                        List of roles
 Role name |          Attributes           |    Member of
-----------+-------------------------------+-----------------
 pronto    | Create role, Create DB       +| {rds_superuser}
           | Password valid until infinity |

But I'm getting this:

mediapop_staging=> REASSIGN OWNED BY pronto TO mediapop_staging;
ERROR:  permission denied to reassign objects

Why? How can I resolve it?

There are some unintuitive permissions requirements when using REASSIGN without a superuser.  See this other pots where I answered the same question: https://stackoverflow.com/a/62557497/79079


Compiled version of stored procedure If I have administrator rights, would it possible to view details on the compiled version of a stored procedure or a view?

Is there a visual representation available?

Maybe this will be a good starting point, just click on the blue XML to show the execution paths that have been cached.

    SELECT TOP 100
    cp.objtype
    , st.objectid
    , cp.usecounts
    , ISNULL(qp.query_plan,'') [ActualQueryPlan CLick to Open]
    , DB_NAME(st.dbid) AS [DB_NAME]
    , OBJECT_NAME(st.objectid,st.dbid) AS [OBJECT_NAME]
    , st.text [SQLText]
    FROM sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
    CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
    LEFT OUTER JOIN sys.procedures sp ON Sp.object_id = st.objectid
    ORDER BY cp.usecounts DESC


Change path for binary logs and scheduling I want to change the default name of binary log and path. I saw the documentation where it discusses about base_name. However, I just need to confirm how to use it correctly. I want binary logs to be stored on another partition and also want to schedule binary logging to run at regular intervals in a day.

I am using MySQL 5.5.

You should be able to set the binary logs to a new partition by providing the absolute path:

log-bin=/my/other/dir/mysql-bin

You might need to copy the old binary logs to the new directory and update the .index file with the new path information (Do this only when mysql is not running).

Why would you only want to binary log during certain intervals of the day? Doing this could really mess with your ability to restore at any given time.


Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation Need urgent help on this. We have applied latest sql patches on one of the server (SQL 2014, SP3, CU4) and somehow we were not able to start the SQL due to following error.
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 200, state 7, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
Error: 3417, Severity: 21, State: 3.
Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it.
So, i have started the server with Trace flag 902 and run the script msdb110_upgrade script and it failed with
Msg 468, Level 16, State 9, Procedure #syscollector_upload_package, Line 37659
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the not equal to operation.
Can anyone please help to fix this issue?

The error is actually being caused by the line following the line you have highlighted in your screenshot (i.e. not the line starting with EXEC master.dbo.xp_instance_regread... )
This error is caused by a collation mismatch between msdb and tempdb. The reason that tempdb is even involved here is due to #syscollector_upload_package being a temporary stored procedure.
The following test will reproduce the error:
Setup
CREATE DATABASE [__TempCollationErrorTest] COLLATE SQL_EBCDIC278_CP1_CS_AS;
ALTER DATABASE [__TempCollationErrorTest] SET RECOVERY SIMPLE;
GO
USE [__TempCollationErrorTest];

Test
GO
CREATE PROCEDURE #ThisShouldError
AS
DECLARE @installpath nvarchar(2048) = N's';
IF @installpath = 'b' PRINT 4;
GO

Executing the code shown above will result in the following error:

Msg 468, Level 16, State 9, Procedure #ThisShouldError, Line XXXXX [Batch Start Line YYYYY]
Cannot resolve the collation conflict between "SQL_EBCDIC278_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Cleanup
GO
USE [master];
DROP DATABASE [__TempCollationErrorTest];

I'm guessing that when this instance was created (using Latin1_General_CI_AS for the instance-level collation), someone restored [msdb] from an instance that used SQL_Latin1_General_CP1_CI_AS as its instance-level collation, hence [msdb] having that collation.
Of course, a far less likely yet still technically possible scenario is this instance being created using SQL_Latin1_General_CP1_CI_AS as its instance-level collation, and someone (for whatever reason) restored the [model] database from an instance that used Latin1_General_CI_AS for the instance-level collation, hence [tempdb] having that collation.
You can check to see where things starting going wrong by executing the following query, focusing on the first two columns — [tempdb] and [msdb]:
SELECT DATABASEPROPERTYEX(N'tempdb', 'collation') AS [tempdb],
       DATABASEPROPERTYEX(N'msdb', 'collation') AS [msdb],
       DATABASEPROPERTYEX(N'model', 'collation') AS [model],
       DATABASEPROPERTYEX(N'master', 'collation') AS [master],
       SERVERPROPERTY('collation') AS [instance];

I'm not sure how much data you have in that [msdb] database (assuming that it is the one with the collation that doesn't master the others), but you will need to change its collation to match the others. This is easiest if this is a new install, but perhaps that is not the case with this being a system that is being patched.
Maybe you could export the data from [msdb] to a file, restore [msdb] from an instance created using Latin1_General_CI_AS for the instance-level collation, then reload the data into [msdb]. For the data import/export, perhaps look into using the SqlPackage.exe utility that comes with SQL Server Data Tools (SSDT). For getting a new [msdb], you might need to create a new instance of SQL Server (a throw-away, just to get a clean [msdb]), in which case you might be able to use SQL Server Express, or maybe best to use the install media used to create the current instance.
If that does not seem possible, then perhaps consider updating the collation of all databases, including the system DBs and even the instance itself, to Latin1_General_100_CI_AS_SC using the undocumented approach that I have described in detail in the following post:
Changing the Collation of the SQL Server Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?
I am suggesting Latin1_General_100_CI_AS_SC as it is:

a safe upgrade given it's the same code page (Windows 1252) and sensitivities (case-insensitive, accent-sensitive)
much improved over the base Latin1_General_* collations (i.e. those without _100_ in their name)
includes support (in terms of the built-in functions) for supplementary characters

I mean, Latin1_General_CI_AS is definitely much better than the US default of SQL_Latin1_General_CP1_CI_AS, but anyone using SQL Server 2008 or newer really should be using the version 100 (or newer) collations.
I recommend this due to assuming that the current instance level collation is Latin1_General_CI_AS, and the sqlservr -q approach makes no changes if attempting to change the collation to the current instance-level collation. So, a different collation would be necessary to use anyway in order to get the operation to process the databases, and the safest collation to use would be something very similar, such as the one I suggested, or even just Latin1_General_CI_AS_KS. If you must maintain using Latin1_General_CI_AS, then you can use Latin1_General_CI_AS_KS as a temporary collation to get all DBs to match, then run the operation a second time to make everything the desired Latin1_General_CI_AS. In this case, assuming you don't want to touch any user databases, those can be detached prior to running sqlservr -q, and then re-attach them once the system databases are all using the desired collation. This will keep the user databases from being affected by the operation (which is the safest approach if their collation ultimately won't be changing).


Create a view with dynamic column headers based on original table row data? Given the two sample tables here:

Tickets Table

ID  User    Description
------------------------------------
0   James   This is a support ticket
1   Fred    This is a ticket too

Properties Table

ID  TicketID    Label           Value
---------------------------------------------------------
0   0           Engineer        Scott
1   1           Engineer        Dale
2   0           Manu            Dell
3   1           Manu            HP
4   0           OS              Windows
5   1           OS              Linux

How can I arrive at a view like this:

ID  User    Description                 Engineer    Manu    OS
---------------------------------------------------------------------
1   James   This is a support ticket    Scott       Dell    Windows
2   Fred    This is a ticket too        Dale        HP      Linux

It is important to note that the properties table would not always be the same. Some tickets may have properties that others do not.

Is this even possible?

Answered by Bluefeet in https://stackoverflow.com/questions/10986031/sql-dynamic-view-with-column-names-based-on-column-values-in-source-table

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(p.label)
                    from tickets t
                    inner join properties p
                        on t.id = p.ticketid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

set @query = 'SELECT id, [user], ' + @cols + ' from
             (
                 select t.id
                        , t.[user]
                        , p.ticketid
                        , p.label
                        , p.value
                    from tickets t
                    inner join properties p
                        on t.id = p.ticketid
            ) x
            pivot
            (
                min(value)
                for label in (' + @cols + ')
            ) p '

execute(@query)


Missing FROM-clause entry with constraint on value from type in PostgreSQL I have an issue with the following script:

CREATE DOMAIN shop.amount AS numeric(8,2) DEFAULT 0 NOT NULL CHECK (value > 0::numeric);

CREATE TYPE shop.money AS (
   m_amount shop.amount,
   m_currency shop.currency
 );

CREATE TABLE shop.order_item
 (
   oi_id bigserial NOT NULL,
   oi_order_id bigint NOT NULL,
   oi_article_id bigint NOT NULL,
   oi_article_price shop.money NOT NULL,
   CONSTRAINT oi_pk_id PRIMARY KEY (oi_id),
   CONSTRAINT oi_fk_article_id FOREIGN KEY (oi_article_id)
       REFERENCES shop.article (a_id) MATCH SIMPLE
       ON UPDATE NO ACTION ON DELETE NO ACTION,
   CONSTRAINT oi_pk_order_id FOREIGN KEY (oi_order_id)
       REFERENCES shop."order" (o_id) MATCH SIMPLE
       ON UPDATE NO ACTION ON DELETE NO ACTION,
   CONSTRAINT price_cannot_be_less_than_ten CHECK (oi_article_price.m_amount >= 10::numeric)
 );

When I execute the script using PGAdmin I get

ERROR:  missing FROM-clause entry for table "oi_article_price"

I guess that the error is related to the constraint, somehow.
What's wrong?

You need to put the domain name in parentheses:

CONSTRAINT price_cannot_be_less_than_ten
  CHECK ( (oi_article_price).m_amount >= 10)


Presto SQL: TO_UNIXTIME I want to convert a readable timestamp to UNIX time in Amazon Athena (Presto).

For example: I want to convert 2018-08-24 18:42:16 to 1535136136000.

Here is my syntax:

    TO_UNIXTIME('2018-08-24 06:42:16') new_year_ut

My error is:

   SYNTAX_ERROR: line 1:77: Unexpected parameters (varchar(19)) for function to_unixtime. Expected: to_unixtime(timestamp) , to_unixtime(timestamp with time zone)

you need to cast the string '2018-08-24 06:42:16' to one of the allowed types

TO_UNIXTIME(cast ('2018-08-24 06:42:16' as timestamp) ) new_year_ut

or

TO_UNIXTIME( timestamp '2018-08-24 06:42:16' )


Making an ERD of a movie database I have a school project in which I have to design a database and my theme is Movie database of sorts. I created a basic ER diagram and I will post a picture here so you can tell me where are my mistakes (I know I have a lot of those)...
I also want to add some new entities like "Genre" but I have a M:M relationship between "Movie" and "Genre" and I'm not sure if I can leave a M:M relationship in a ER diagram. Any advice about what am I doing wrong or how to implement "Genre" entity would be helpful.

Can a Movie have > 1 director (like the Marvel movies and the brothers), if so this should be a many to many.

Can a movie win more than one award? If so it should be a many to many.

Can a move EVER be shot at > 1 studio? If so you need a many to many.

You solve the many-to-many by adding an intermediate table in the logical design phase. That new table has a composite key consisting of the foreign keys from the two tables participating in the relationship.

For example, for Movie and Director, you would have Move, MovieDirector, and Director. MovieDirector would have only 2 columns: MovieID and DirectorID.


What MDX can and SQL Can't perform on the same datatbase Can anyone explain or show me example(s) what MDX can do and SQL can not do on same database? please...

It's not a matter of what one can do vs the other, as you can always, in theory, write SQL to replace your MDX. It's a matter of how hard it is.

Example:

If you want the sales per month of your top 5 clients from last year in EMEA, your MDX query is something like

SELECT TopCount( [Clients].Members, 5, ( [Measures].[Sales], [Time].[2018] ) ) on Rows,
[Time].[2018].Children on Columns
from [Sales]
where [Geography].[EMEA]

and your cube definition will take care of what columns to query from which tables, getting the Territory name from the dim_territory table, the customer name from the customer table, etc.

The equivalent in SQL would be something like

Select c.customer_name, t.month_name, sum(f.sales)
from fact_sales f, dim_customer c, dim_time t, dim_geography g
where f.time_id = t.time_id
  and f.customer_id = c.customer_id
  and f.geography_id = g.geography_id
  and g.territory = 'EMEA'
  and t.year= 2018
  and c.customer_name in (
    select c.customer_name, sum(f.sales)
    from fact_sales f, dim_customer c, dim_time t
    where f.customer_id = c.customer_id
      and f.time_id = t.time_id
      and t.year = 2018
    group by t.year
    limit 5
  )
group by t.year, t.month_name
order by t.month_number

Not only the SQL query is much more complex to write (I'm not even sure what I wrote is correct, tbh), but a lot of variations that in MDX just require a small rewrite, in SQL they will require several nested queries, and lots of filtering, grouping etc.

Plus, MDX allows you a cross-tab result, where you get one column for each month (or whatever dimension level you choose), whereas in SQL it will return a normalised dataset and you'll have to pivot it on the client side if you want it in tabular format.


Limit connection to specific database with AlwaysOn Primary Using AlwaysOn, I have a user which previously we had granted rights on a database on the primary, disabled the login on the primary server and then created the same login on the secondary so that they could access the database on the secondary but not the primary (ignore failover scenarios please, not the issue).
The problem now is that I need to grant that same user access to the SQLAgentReader role in msdb so they can see jobs on the primary.  That itself is not a problem, but I will need to enable the login on the primary in which case he could then get back into the first database on the primary.
Is there a trigger I could write to tell if he is 1) connecting to that first DB and 2) whether he is on the primary and if both are true then block his connection JUST to that DB?  Maybe there is some better solution (besides the obvious use two logins with different permissions)?  So far all I have found is triggers on login to the server, not at the database level.

There's not a straightforward, built-in way of accomplishing what you want to do beyond what you suggest at the end of your question:

besides the obvious use two logins with different permissions

I've heard of folks doing something like this:

create a SQL Server Agent job that runs once every X seconds (might be 1, 5, 15, etc depending on your requirements)
in that job, check to see if we're running on the primary replica for this database (sys.fn_hadr_is_primary_replica OR the AG DMVs)
if it is the primary, query sys.dm_exec_sessions to see if that login / user is connected ( in the login_name)
if they are connected, check to see if they are using the off-limits database (in the database_id column, or pull the query text and check that)
if they are using the off-limits database, use the kill command on the session_id

It's not perfect (because the job is not continuous, and because it's hard to check if the off-limits is being used conclusively), but it's better than nothing (if you're stuck with the single login approach).


Converting VARCHAR to TIMESTAMP with milleseconds yields "ERROR: date/time field value out of range" I have a VARCHAR column with the value '2018-07-05T16:14:27.1427' and I convert it to timestamp with this command:

select last_run,
       to_char(to_timestamp(last_run,'yyyy-MM-dd HH24:mi:ss') - interval '2 hour','yyyy-MM-dd"T"HH24:mi:ss"Z"') as new_last_run
from schedule_tasks;

Until the version 9.6.10 of PostgreSQL, all works good, but now I will migrate to 10.5 and the same command returns ERROR. The same issue happens on PostgreSQL 11.

What I realize is that, on 9.6, the first value of milliseconds when I have 4 digits is added to seconds; for example, 2018-07-05T16:14:27.1427 converted is 2018-07-05T16:14:28.427.

I don't know if this behavior is normal in the newer versions and I will have to handle it, or if this is a bug.

Handling of date and timestamp input has been made stricter in Postgres 10. Quoting the release notes:

  Make to_timestamp() and to_date() reject out-of-range input
  fields (Artur Zakirov)

  For example, previously to_date('2009-06-40','YYYY-MM-DD') was
  accepted and returned 2009-07-10. It will now generate an error.

Explains what you observed. You'll need to sanitize your input.

Here is the thread where these changes were discussed.


How to tune the below query I am currently working on below table structure to pull the data/ generate any one time report.

Table ABC:

ID  Field       Value
1   Name        Nagarjuna
1   Gender      Male
1   Industry    Actor
2   Name        Sachin RT
2   Gender      Male
2   Industry    Sports
3   Name        Theresa
3   Gender      Female
3   Industry    Social Worker
4   Name        Saha
4   Gender      Female
4   Industry    Software Engineer
5   Name        Swe
5   Gender      Female
5   Industry    Doctor

In order to get the report in the format Name,gender,industry values, I am writing the below query

Select
  vname.Value, vgender.value, vindustry.value
from ABC [vname] with (nolock)
join ABC [vgender] with (nolock)
  on vname.id = vgender.id
join ABC [vindustry] with (nolock)
  on vgender.id = vindustry.id

where vname.field = 'Name'
  and vgender.field = 'Gender'
  and vindustry.field = 'Industry'

I am currently working on the table which has around 500k of records and for each id type there would be around 10 - 30 entries, so if more records are there for one id type then more joins would be added up.

Because of this executing it is taking a lot of time.

Is there any way that we can reduce this time taken for execution and also this table is used by an website where there would be around 1000 to 50000 users.

This is your query:

Select vname.Value, vgender.value, vindustry.value
from ABC [vname] with (nolock) join
     ABC [vgender] with (nolock)
     on vname.id = vgender.id join
     ABC [vindustry] with (nolock)
     on vgender.id = vindustry.id
where vname.field = 'Name' and
      vgender.field = 'Gender' and
      vindustry.field = 'Industry';

You can try indexes on this query.  I would try ABC(id, field, value) and ABC(field, id, value).

However, you might also try the following:

select max(case when field = 'Name' then value end) as Name,
       max(case when field = 'Gender' then value end) as Gender,
       max(case when field = 'Industry' then value end) as Industry
where field in ('Name', 'Gender', 'Industry')
from ABC
group by id;


How to get the sequence name for a serial column As far as I can tell, this query should show the expression for the new value of a serial column:

select d.adsrc
from (
 SELECT a.attrelid, a.attnum, n.nspname, c.relname, a.attname
 FROM pg_catalog.pg_attribute a, pg_namespace n, pg_class c
 WHERE a.attnum > 0
 AND NOT a.attisdropped
 AND a.attrelid = c.oid
 and c.relkind not in ('S','v')
 and c.relnamespace = n.oid
 and n.nspname not in ('pg_catalog','pg_toast','information_schema')
) x
left join pg_attrdef d on d.adrelid = x.attrelid and d.adnum = x.attnum
where x.relname = 'table_name' and x.nspname = 'schema_name' and x.attname = 'column_name'
;

It works most of the time, but if I rename the sequence, the new name is not reflected in the result of the query - it continues to show the original name of the sequence. Any ideas why?

In the comments, a_horse_with_no_name said:

  Are you using Greenplum or Postgres? In Postgres you can just use pg_get_serial_sequence

You can find the docs here.

  pg_get_serial_sequence returns the name of the sequence associated with a column, or NULL if no sequence is associated with the column. The first input parameter is a table name with optional schema, and the second parameter is a column name. Because the first parameter is potentially a schema and table, it is not treated as a double-quoted identifier, meaning it is lower cased by default, while the second parameter, being just a column name, is treated as double-quoted and has its case preserved. The function returns a value suitably formatted for passing to sequence functions (see Section 9.16). This association can be modified or removed with ALTER SEQUENCE OWNED BY. (The function probably should have been called pg_get_owned_sequence; its current name reflects the fact that it's typically used with serial or bigserial columns.)


MySql with rollup - not giving the total. What am I doing wrong? I'm a student and I just learned about the mysql GROUP BY and ROLLUP commands. But my WITH ROLLUP query isn't returning a total. Can someone please tell me what I'm doing wrong here?

Just like it's supposed to, it returns the total amount ordered for each product. But the last column called "GRAND TOTAL" doesn't have the total amount ordered.

Here is a screenshot -

Your query is not actually aggregating anything (there is not any aggregation funcion).  You should just try:

SELECT
    IFNULL(product_name, 'GRAND TOTAL') AS product,
    -- Need an aggregate function on the SELECT
    -- I think you just want to SUM all your revenue
    SUM((oi.item_price - oi.discount_amount) * oi.quantity) AS total_amount
FROM
    order_items oi
    JOIN products p ON p.product_id = oi.product_id
GROUP BY
    product_name
    WITH ROLLUP ;

... which I think is what you're trying to do.

This strange behaviour arises from the fact that MySQL (and MariaDB, for that matter) allows, in versions previous to the current one, for a GROUP BY to use unaggregated columns that are not part of the GROUP BY list. (See Why does MySQL allow “group by” queries WITHOUT aggregate functions?). This is not standard SQL and should best be avoided. This is handled in current version with ONLY_FULL_GROUP_BY.

Check it at dbfiddle here


Cannot find the object because it does not exist I have two databases DB_A and DB_B. From DB_A the developers are calling a stored procedure that will truncate table_a in DB_B. When they execute the stored procedure using the login from the application, we will call this "app_user", it returns the error message. Cannot find object "" because it does not exist or you do not have permissions.

Now my first instinct is to check the permissions for app_user on both DB_A and DB_B. The user is a db_owner on the latter. I ran the sp_change_users_login to see if the database user was orphaned, and it was not. I am not exactly sure why I am still getting this error. Any help will be appreciated.

This issue here is that it doesn't so much matter what the User in DB_B is because Database-level permissions, by default, do not transfer between Databases. There are ways of getting this to work that require little effort, but that are also huge security risks: enabling Cross-Database Ownership Chaining and/or enabling the TRUSTWORTHY Database property. But you don't need either of those. Instead, what you can do is:

Create a Certificate in DB_A
Sign the Stored Procedure in DB_A with that Certificate
Extract the Certificate bytes and Private Key bytes using the built-in functions: CERTENCODED and CERTPRIVATEKEY
Create that same Certificate in DB_B using the extracted Certificate and Private Key bytes
Create a User in DB_B from that Certificate
Add the Certificate-based User in DB_B to the db_owner Database Role.

Please see the following answer of mine on a related question that shows this working between two databases:

Giving Special Permissions to a Stored Procedure in SQL Server


How to replace default on a table I have a very big table and added a new NOT NULL column with DEFAULT on it. Not an unusual thing in DWH environment. It is an online operation, it works very quickly, and stores default in a table metadata, as we can learn there. It works perfectly, SELECTs return a default value. After some time I decided to change the default to a different value. And it looks like we can't achieve it by changing the default. I mean, we can drop old default and create new one, but all SELECTSs would show old default value. I'd try to avoid massive UPDATEs because they are long and painful. Is it possible?

--DROP TABLE #t

CREATE TABLE #t
(
  c1 int not null
)

INSERT INTO #t VALUES (1)

ALTER TABLE #t ADD c2 int not null CONSTRAINT t#c2def default -1
ALTER TABLE #t DROP CONSTRAINT t#c2def

SELECT * FROM #t
-- c2 = -1
ALTER TABLE #t ADD CONSTRAINT t#c2def DEFAULT -2 FOR c2

SELECT * FROM #t
-- still c2 = -1

And more generally, do we have more details on how this feature works? I'd expect that existing non-updated pages should show a new default. That's how I understand 'the default value is stored only in the metadata of the table and the value is looked up, as needed'. Unfortunately, it doesn't work like this. All rows show old default value. I'm checking it on SQL Server 2014.

I found my answers here. The default value is not the same as default constraint. It is created when we create first default constraint and can't be changed.

So, to answer my question - no, it is not possible. We can't change the default value for a column. This magic works only once. After this, we should do regular DML to change values in rows.


Hook to run code when Postgres DB launches I need to run code when a database/catalog in a Postgres cluster comes online, before any client has connected.

Specifically I need to do some cleanup such as dropping/truncating a particular table.

Is there a place for my code to be automatically run with a guarantee it will finish before any incoming client connection is accepted?

You have two methods of doing this,

Step back, your best bet is to use TEMPORARY TABLES

  TEMPORARY or TEMP
  If specified, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below). Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names. Any indexes created on a temporary table are automatically temporary as well.

  The autovacuum daemon cannot access and therefore cannot vacuum or analyze temporary tables. For this reason, appropriate vacuum and analyze operations should be performed via session SQL commands. For example, if a temporary table is going to be used in complex queries, it is wise to run ANALYZE on the temporary table after it is populated.

  Optionally, GLOBAL or LOCAL can be written before TEMPORARY or TEMP. This presently makes no difference in PostgreSQL and is deprecated; see Compatibility.

Alternatively, you could rig it into your daemon that starts the server by,

Creating two pg_hba.confs,

One that denies external connections.
One that permits external conections.

Create two config files, one to each pg_hba.conf
Start the server with the config file that points to the pg_hba.conf that denies external connections.
Run the code you want.
Stop the server.
Run the server with the config file that points to the pg_hba.conf that permits external connections.

You could do a similar thing with postgresql.conf's listen_addresses parameter.


ORA-01033 "ORACLE initialization or shutdown in progress" caused by ORA-10567 "REDO is inconsistent with data block" I faced an error while logging to database with regular user (non SYS/DBA):

C:\Users\user>sqlplus tester@orcl
ORA-01033 ORACLE initialization or shutdown in progress

However I can connect via sysman@orcl as sysdba user. Before I start with solving the issue, below are the errors that can tell what state my orcl database is in:

SQL> select * from dba_data_files;
ORA-01219: database not open: queries allowed on fixed tables/views only

So I use recover database and alter database open commands and they point at the same logfile and block:

SQL> recover database;
ORA-00600: internal error code, arguments: [3020], [3], [192], [12583104], [], [], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 3, block# 192, file offset is 1572864 bytes)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 3: 'E:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF'
ORA-10560: block type 'KTU SMU HEADER BLOCK'

SQL> alter database open;
ORA-01172: recovery of thread 1 stuck at block 192 of file 3
ORA-01151: use media recovery to recover block, restore backup if needed

I did also that:

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORA-12514: TNS:listener does not currently know of service
SQL> startup mount;
ORA-24324: service handle not initialized
ORA-01041: internal error. hostdef extension doesn't exist

After restarting oracle.exe service and diagnosing the database, the "redo" error has a bit changed (block 705):

ORA-10567: Redo is inconsistent with data block (file# 3, block# 705, file offset is 5775360 bytes)

My log files:

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                IS_
---------- ------- ------- ------------------------------------- ---
         3         ONLINE  E:\ORACLE\ORADATA\ORCL\REDO03.LOG     NO
         2         ONLINE  E:\ORACLE\ORADATA\ORCL\REDO02.LOG     NO
         1         ONLINE  E:\ORACLE\ORADATA\ORCL\REDO01.LOG     NO

I'm in a point where I cannot use my database at full even by a sysman user. Please help, advise.

EDIT:
After following this guide which ideally addresses my situation, I get different error and cannot follow it anymore:

SQL> alter system switch logfile;
ORA-01109: database not open

SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first

Question aside: Who can tell if this db is open or not? :-)

Since the problem is in your undo tablespace. You can drop it and create a new one. Steps in this article:

http://www.my-whiteboard.com/how-to-drop-and-recreate-oracle-undo-tablespace-and-its-data-files/


Postgres not using index when expression in WHERE condition The table my_table has an index on the column timestamp.

The following query makes use of this index:

explain select
  item_id,
  timestamp
  position
from my_table t
where t.timestamp > '2020-05-05'::timestamp

QUERY PLAN
(...)
->  Index Scan using (..) on "my_table" t  (cost=0.57..641762.36 rows=926839 width=52)

But this other query doesn't — almost identical but the WHERE condition depends on an expression:

explain select
  item_id,
  timestamp
  position
from my_table t
where t.timestamp > (select max(timestamp) from other_table)

QUERY PLAN
(...)
->  Seq Scan on "my_table" t  (cost=0.00..33523870.16 rows=343386904 width=52)

What  I'm getting trouble understanding is, once the select max() subquery has been executed, the max(timestamp) value is known and fixed, so how comes Postgres is unable to make an index scan based on this (now known) value? I don't see how it makes sense.

More importantly, how to circumvent this so that Postgres does use the index on my_table.timestamp? Is there a way around this?

The plan is determined before the query is executed, and such a choice cannot be made at execution time in PostgreSQL.

I see two ways:

execute two queries and use the result of the first in the second
add a second "cut off" condition like

AND t.timestamp > '2020-01-01'

that won't change the result, but let the optimizer know that an index scan will be the right choice


SPID Change for a process? On SQL Server 2012 can a process SPID change mid-operation? We have a very unique query that we've been monitoring (production, but controlled testing) and have seen it at different intervals with different SPIDs. It's a unique query that no one else should be running, at least with these parameters, and as such we shouldn't see it with a different SPID, unless... can a query can change SPIDs mid process?

My understanding is this is impossible, so more likely is that for some unknown reason the application is re-submitting this unique and performance destroying query, thus generating a new SPID, despite no user interaction.

No, the SPID (Server Process ID) is assigned the moment an application establishes a connection/session to the database and is retained until the connection/session ends.


Inline edit SQL Server database rows from Visual Studio I'm pretty sure Microsoft have pulled one of the most useful features for performing quick edits on a SQL Server Database within the Visual Studio IDE. It seems to have only affected SQL 2012 instances, but from the Server Explorer I can no longer right click on a table "Show Table Data", pop open the SQL pane, query the data then perform inline edits on the results (as if I were modifying a spreadsheet).

This means I now need to go into SSMS to make these kind of quick updates. Does anybody know of a plugin I can use with VS 2012 to bring back this functionality? It seems odd to me that Microsoft have two different development trajectories with SSDT and SSMS? Are they designed to serve different purposes? Can SSMS be integrated into the Visual Studio IDE? I'd rather have a unified development environment if possible.

Any thoughts on a workaround for this problem would be much appreciated.

EDIT

I know some purists would quiver at the thought of treating a database table like a spreadsheet, but semantically they are not a world apart, plus this is supported in SSMS. I'm more in favour of relying on GUI based approaches where I can to speed up routine tasks, why some would balk at this I have no idea?


How to set uuid-ossp as a default extension for whole server? I installed uuid-ossp for the default PostgreSQL database (postgres) using the following sql commands but whenever I create a new database I am forced to run the same commands once again for every new database.
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
ALTER EXTENSION "uuid-ossp" SET SCHEMA public;

Is there any way to install uuid-ossp once and ensure that every new database will have uuid-ossp enabled by default?
PS. I use PostgreSQL ver. 13.1

The CREATE DATABASE command always clones some existing template database.
If this parameter is not specified, postgresql uses the template1 database as the default. Thus, any prior changes in template1 database will be part of the new database (unless another TEMPLATE  has been specified explicitly).
You need connect to template1 database and create the uuid-ossp extension in this database.


Can't DROP GROUP, cannot be dropped because some objects depend on it RDS Version: PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.1161

I've been wrestling with this issue for a few days now, and all solutions I've found here and the AWS Forums aren't working for me.

Basically I'm trying to drop a group. This group has no users in it (though per the AWS docs that shouldn't matter).

I've revoked all privileges on database, schema, tables, and functions. They are not an owner nor part of an ACL in pg_tables, pg_views, pg_namespace, or pg_database.

They are also not a part of the ACL in pg_default_acl.

When I run DROP GROUP my_group; I receive the following error:

[Amazon](500310) Invalid operation: group "my_group" cannot be dropped because some objects depend on it Details: privileges for default privileges on new relations belonging to user dwowner in schema changehistory privileges for default privileges on new relations belonging to user dwowner in schema stage privileges for default privileges on new relations belonging to user dwowner in schema public 5 object in database prod;

Running:

ALTER DEFAULT PRIVILEGES
    IN SCHEMA stage, public, changehistory, admin
    REVOKE ALL ON TABLEs FROM GROUP my_group;

Doesn't result in changes to the pg_default_acl table (because they weren't there in the first place) and the above error continues when trying to drop the group.

A few questions:

dwowner is in fact the owner of those schemas, but what does that have to do with my_group?
the end of the error references the prod database. I ran this query in dev. I've run the same revoke statements on prod as well. Running the DROP GROUP my_group; on prod results in a similar error, this time referencing the dev database.

There's obviously some object permission relationship that I'm missing somewhere but I have absolutely no clue at this point

What I found is that in order to drop a group you have to revoke all privileges that the group might have been assigned. A group can have default privileges, usage on schema, and grants on tables. I used following 5 statements and then was able to drop the group.

--Drop All users from the group
alter group <groupname> drop user <username>;
--REVOKE usage on the group
REVOKE ALL ON SCHEMA <schema1>,<schema2>,<schema3>,<schema4>,<schema5>,public
    FROM group <groupname>;
--REVOKE grants on tables
REVOKE ALL ON ALL TABLES IN SCHEMA <schema1>,<schema2>,<schema3>,<schema4>,<schema5>,public
    FROM group <groupname>;
--REVOKE Default privileges on TABLES
ALTER DEFAULT PRIVILEGES for USER <username> IN SCHEMA <schema1>,<schema2>,<schema3>,<schema4>,<schema5>,public
    REVOKE ALL ON TABLES FROM group <groupname>;
--REVOKE Default privileges on FUNCTIONS if any
ALTER DEFAULT PRIVILEGES for USER <username> IN SCHEMA <schema1>,<schema2>,<schema3>,<schema4>,<schema5>,public
    REVOKE ALL ON FUNCTIONS FROM group <groupname>;
--drop the group finally
drop group <groupname>;


Using like over charindex to search for pattern We where using charindex to search for a string as shown below

SELECT *
FROM tablename
WHERE ( Charindex('47a%$.abc',CAST(columnname AS VARCHAR(MAX)))>0 )

I am planning to replace charindex with LIKE as i require to search only whether the pattern exists not the position.
So i want to know is these any disadvantage in using LIKE over charindex for my case.I am using charindex in several cases. So i want it to work for all sql datatypes ,all characters including special characters(including % (as like already uses %pattern%).So please tell me is there any disadvantage in using like over charindex

The disadvantage is that you need to escape all characters of special significance in the pattern syntax.

Following your previous question it seems the way you do the escaping affects the cardinality estimates.

Testing on SQL Server 2008/2012

SELECT name
/*Copying into a temp table as on 2012 this table is now in the readonly
 resource database*/
INTO #T
FROM   master..spt_values

Then

SELECT *
FROM   #T
WHERE  name LIKE '%\%%' ESCAPE '\'

gives an estimated row count of 625.33/627.575. But

SELECT *
FROM   #T
WHERE  name LIKE '%[%]%'

gives an estimated row count of 1.8563/1.86296.

In fact no name values in that table contain a literal % character so the second one appears to be more accurate in this case.

The response to this Connect Item states

  We do not have support for precise cardinality estimation in the
  presence of user defined escape characters. So we probably get a poor
  estimate and a poor plan. We'll consider addressing this issue in a
  future release.


Why have “unidirectional ‘mapping’” tables? I've been reading about how Pinterest sharded their MySQL databases here.

One of the things they talk about in that post is having “unidirectional ‘mapping’” tables such as

 CREATE TABLE board_has_pins (
  board_id INT,
  pin_id INT,
  sequence INT,
  INDEX(board_id, pin_id, sequence)
) ENGINE=InnoDB;

and then an opposite pin_owned_by_board.

My question is, why would you have these “unidirectional tables” for “mapping” and not just one with a different index structure? I don't think it can stop duplicates due to the sequence being a unix timestamp, but the index does limit only being able to select by the board_id in this instance or the full index (efficiently anyway). So why not have separate indices on board_id and pin_id? Is this down to the fact that a pin could in theory live on a separate shard even though they try to keep them on the same one so it makes sense to make it “unidirectional”?

I agree that it is a strange table.

No uniqueness constraint, so dups can occur.  I would change INDEX to PRIMARY KEY.

No index the opposite direction, so unlikely to use it that way.  I would add another column and INDEX(bin_id, board_id, pin_to_board sequence)

On the other hand, perhaps there is a lot of activity in the table -- such as updating all the sequence values frequently.  Still, InnoDB would really prefer to have a PRIMARY KEY, and that triple seems like the 'right' PK.

I would probably work harder to avoid potential timestamp dups.


Using a subquery in place of a SELECT fieldname ...or how to achieve the same result? My goal is to get a collection of ID's from Table 1 based on a query, a collection of ID's from Table 2 based on a query, and insert a new record into Table 3 for each permutation.

Basically, this:

INSERT INTO `TABLE3` (Field1,Field2,Field3)
SELECT null,`UID`, (SELECT `UID` FROM `TABLE2` WHERE `IsDefault` = 1)
FROM `TABLE1`
WHERE `Group` LIKE 'V';

So if the subquery:

(SELECT `UID` FROM `TABLE2` WHERE `IsDefault` = 1)

returns a list of IDs:

1, 2, 3

And the main select query:

SELECT null,`UID`, $i
FROM `TABLE1`
WHERE `Group` LIKE 'V';

returns another list of IDs:

200, 300, 400

Then TABLE3 after the query runs should be:

1 - 200
1 - 300
1 - 400
2 - 200
2 - 300
2 - 400
3 - 200
3 - 300
3 - 400

When I tried the above query, I get:

#2014 - Commands out of sync; you can't run this command now

So I'm not sure what the proper syntax is to do an INSERT...SELECT  where one of the "hard values" should be treated as a "foreach" variable from the subquery results?

What you want to do is called a Cartesian product and uses CROSS JOIN:

SELECT NULL, t1.UID, t2.UID
FROM table1 t1
CROSS JOIN table2 t2
WHERE T1.`Groups` LIKE 'V'
    AND t2. `IsDefault` = 1;

This mixes each rows from table1 with each rows from table2.  (see sqlfiddle)


How to force higher values of open_files_limit or table_open_cache? I'm having a frustrating problem where setting my.cnf to values higher than I currently have them does not 'take'.

This is MySQL 5.6.14 on MacOS X 10.8.3

I'm trying to address dropped connections and other seemingly capacity related issues.

For example:  My database's current stats are:

mysql> SHOW GlOBAL STATUS LIKE "%open%";

+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| Com_ha_open                | 0      |
| Com_show_open_tables       | 0      |
| Innodb_num_open_files      | 1581   |
| Open_files                 | 5557   |
| Open_streams               | 0      |
| Open_table_definitions     | 4199   |
| Open_tables                | 5000   |
| Opened_files               | 54805  |
| Opened_table_definitions   | 4247   |
| Opened_tables              | 8515   |
| Slave_open_temp_tables     | 0      |
| Table_open_cache_hits      | 104991 |
| Table_open_cache_misses    | 8479   |
| Table_open_cache_overflows | 3459   |
+----------------------------+--------+

My current my.cnf values are:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
max_allowed_packet=128M
wait_timeout=57600
table_open_cache = 5000
table-definition-cache = 10000
open_files_limit = 10000
max-connections = 15
thread-cache-size = 16K
max-heap-table-size = 6024M
tmp-table-size = 6024M
query-cache-size = 256K
query-cache-limit = 16M
key_buffer = 512M
long-query-time = 5
join-buffer-size = 1M
innodb_buffer_pool_size = 3046M

And that produces:

mysql> SHOW VARIABLES LIKE '%open%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| have_openssl               | DISABLED |
| innodb_open_files          | 5000     |
| open_files_limit           | 10025    |
| table_open_cache           | 5000     |
| table_open_cache_instances | 1        |
+----------------------------+----------+
5 rows in set (0.00 sec)

Which is at it should be.

I would like to raise my open limits to

table_open_cache = 10000
table-definition-cache = 20000
open_files_limit = 60000

But when I do the my.cnf seems to invalidate and mysql reports:

mysql> SHOW VARIABLES LIKE '%open%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| have_openssl               | DISABLED |
| innodb_open_files          | 400      |
| open_files_limit           | 256      |
| table_open_cache           | 400      |
| table_open_cache_instances | 1        |
+----------------------------+----------+
5 rows in set (0.00 sec)

Which obviously kills my server.

From the looks of your numbers, you must have hit some bug or pre-GA behavior. Why?
According to the MySQL Documentation, the default for table_open_cache is -1 for GA releases of MySQL 5.6. This tells mysqld pick a fair starting value give current OS conditions. What makes that value weird is the fact that 400 is the default for MySQL 5.6.7. The first GA release is 5.6.8. It may be a coincidence, but my guess would be that you set table_open_cache a little too high. Evidently, 5000 is a tolerable value for your DB Server.
SUGGESTION
Try experimenting with table_open_cache_instances setting it to 2 or 4. Then restart mysql and see.


PostgreSQL COPY TO doesn’t honor Linux group privileges I have issues executing COPY to command, receiving below error msg:

ERROR:  could not open file "/home/pgsql/TMP/out.txt" for writing:
Permission denied

Postgres engine is being run as postgres user
Postgres user belongs to app group
TMP folder has below privileges set:

drwxrws---  2 pgsql app       23 Mar 28 19:47 TMP

I don’t understand why it doesn’t work.
I did quick check login as postgres user and creating file and this worked:
sudo -i -u postgres

If I change privileges to 777 all works as expected (I can do copy to command with no issues)
So as to me it looks postgres doesn’t somehow honor Linux groups rights
Can someone guide me how to resolve that (777 is not an option to me)

Server reboot helped. Privileges are treated as expected
(Not sure if only DB engine is enough to fix that thought - I had to reboot entire machine)
Thanks @Colin for tip


Lookup transformation not seeing new records I'm trying to load data from one database to another. I am using the lookup transform that looks at the target table to see if the record already exists.

I am getting duplicate data when the package succeeds.

Why doesn't the lookup transform refresh the table it's looking at every time it is being run?

Any ideas on how to create the lookup transformation so that it will refresh its connection to the table?

I want to keep the records I already have, and add any new records but the package should not load any duplicates.

How do I make sure not to get duplicated data by making sure the lookup transformation sees the newly added data immediately?

By default, the lookup transformation uses full cache and doesn't refresh it's data. You would need to change the lookup transformation to "No Cache" to make sure it executes a query every time the task has to look up a value:

Please refer to the guidance on the SSIS team blog:

Lookup cache modes

  When to use no cache:

  When your reference table is changing (inserts, updates, deletes)
  ...


Inconsistent information from different DMVs I've a server with 12 GB of physical RAM. I just lowered its 'Max Server Memory (MB)' setting from 10 GB to 8 GB.

If I look at the processes' committed memory in Perfmon, I see:

select *
from sys.dm_os_performance_counters
where [object_name] like 'SQLServer:Memory Manager%';
go

Target Server Memory (KB):  8388608
Total Server Memory (KB):   8388608

Showing that SQL Server has already relased the additional 2 GB of memory from the Buffer Pool.

However, if I look at the sys.dm_os_sys_info:

select [bpool_committed]
     , [bpool_commit_target]
     , [bpool_visible]
from sys.dm_os_sys_info;
go

I get inconsistent results:

bpool_committed     = 1048576
bpool_commit_target = 1048576
bpool_visible       = 1048576

Shouldn't sys.dm_os_sys_info reflect the information on the performance counters?

I'm running Microsoft SQL Server 2005 SP1 on Windows Server 2003.

There is no inconsistency:

  1048576 * 8 = 8388608 KB => 8 GB;

Both Perfmon and sys.dm_os_sys_info show 8 GB;

[bpool_visible], [bpool_commit_target] and [bpool_committed] - in SQL 2005 all these are defined as "Number of 8-KB buffers ..." (http://msdn.microsoft.com/en-us/library/ms175048(v=sql.90).aspx), so you have to multiply the result by 8 to get KB; Starting with SQL 2008 R2 you no longer have to do the multiplication.

If you want to use a multiplication within the query try:

SELECT CAST((bpool_committed * 8) / (1024.0 * 1024.0) AS DECIMAL(20, 2)) AS buffer_pool_committed_memory_in_GB
    , CAST((bpool_commit_target * 8) / (1024.0 * 1024.0) AS DECIMAL(20, 2)) AS buffer_pool_target_memory_in_GB
    , CAST((bpool_visible * 8) / (1024.0 * 1024.0) AS DECIMAL(20, 2)) AS buffer_pool_visible_memory_in_GB
FROM sys.dm_os_sys_info;


Performance hit with column alias I have a database in SQL Server 2008, with the final data table having about 2 million rows. The development team is implementing a new application and they have suggested changing the column name to reflect what the business likes it to be. They already have their application built in the test environment with the new column names. Will there be a performance penalty if I have them connect to my database via a view with column name aliased to match the column names used by the application? I need to provide a justification for not to change the column names in the application. Any help will be appreciated. Thanks.

A view, being just a query persisted in the database, should not theoretically have any performance hit. Same is true for column or table aliases. The performance hit, if any, will be barely noticeable once the execution plan is compiled and cached by the database engine. So, there is no performance justification not to use column aliases based on views. Having said that, I second Aaron Bertrand that the application should not have been written in incorrect schema. I would fix the application just for the sake of best practices and not to "create a permanent messy layer of indirection" as Aaron Bertrand said.


PostgreSQL Streaming Replication - LOG: archive command failed with exit code 1 When I bring up the replica Node I am get the following error in the log

2020-04-27 00:12:26.278 EDT [3084] LOG:  archive command failed with exit code 1
2020-04-27 00:12:26.278 EDT [3084] DETAIL:  The failed archive command was: test ! -f /var/lib/postgresql/pg_log_archive/main/00000001000000000000000C && cp pg_wal/00000001000000000000000C /var/lib/postgresql/pg_log_archive/replica/00000001000000000000000C

The achive command is set to below:

archive_command = 'test ! -f /var/lib/postgresql/pg_log_archive/main/%f && cp %p /var/lib/postgresql/pg_log_archive/replica/%f'

The configuration:

Primary Server postgresql.conf:

wal_level = replica
wal_log_hints = on
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/pg_log_archive/main/%f && cp %p /var/lib/postgresql/pg_log_archive/replica/%f'
max_wal_senders = 10
wal_keep_segments = 64

Standby server postgres.conf:

wal_level = replica
wal_log_hints = on
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/pg_log_archive/replica/%f && cp %p /var/lib/postgresql/pg_log_archive/main/%f'
max_wal_senders = 10
wal_keep_segments = 64
hot_standby = on

recovery.conf:

standby_mode = on
primary_conninfo = 'host=192.168.56.103 port=5432 user=postgres password=****'
restore_command ='cp var/lib/postgresql/pg_log_archive/replica/%f %p'
recovery_target_timeline ='latest'

There are two possibilities:

You have archive_mode = always on the standby.

Then the standby server will try to archive WAL segments (redundantly).
The standby server is not really a standby server, but a standalone database. That means that you have made a mistake setting up the standby server.

The way to determine is that is the case, run

SELECT pg_is_in_recovery();

If that returns FALSE, the server is not a standby.


MySql - Global index I'm new to MySql partitioning and have a question regarding indexes.
let say i have the following table:

CREATE TABLE `members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `member_rating` int(11) DEFAULT '0',
  `persona_id` int(11) NOT NULL,
  `high_value_type` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`,`user_id`),
  UNIQUE KEY `user_id` (`user_id`,`persona_id`),
  KEY `member_rating_index` (`member_rating`),
  KEY `persona_index` (`persona_id`),
  KEY `high_value_members_index` (`user_id`,`high_value_type`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY HASH (user_id)
PARTITIONS 1000 */

to my understanding, everything will work fine as long as i query the table while providing user_id, my question is: how will the table perform when i'll make a query with out user_id, say something like:

SELECT * FROM members where persona_id=3

any ideas?

p.s. I'm running on MySql 5.1 / innodb

You would not be able to partition that table as you need to remove the unique key and make it a regular index instead.

To answer your question, if you query on column that isn't partitioned then MySQL would query every partition one-by-one until it answers the query.
So if you query a partitioned table on a column that it is not partitioned on, you may get slower results then if the table was not partitioned.

That is why you need to choose the column you want to partition very carefully.

I would select a column that fits 80% of the most important use cases.


Can Microsoft JDBC Driver 4.0 connect to SQL Server 2019 and perform DML without issue? The SQL version compatibility BOL article advises that the minimum compatible JDBC driver version for SQL Server 2019 is 7.4.
Can an older JDBC driver still connect to SQL Server 2019? Or can only the driver versions listed as compatible connect to the specified SQL Server versions?

Microsoft and my testing have confirmed that JDBC drivers not marked as compatible in the SQL version compatibility matrix might connect to a non compatible version of SQL Server and continue to work without issue.

However, there is no guarantee that all functionality detailed in the Driver feature support matrix for Microsoft SQL Server is supported. They also only test the  driver versions marked as compatible against the stated version of SQL Server.
JDBC version 4.0 was not specifically tested.