Optimize query with multiple IN statements So I have a large query which I have been trying to optimize as over time it has gotten fairly slow (~116k records taking a few seconds to get a result).  The context is a social network feed where the user is "authorized" to see the posts.  Each post has an associated tag (which can be a whole location, a classroom or a student.  The slow part in question looks like this:

WHERE (
  "post"."deletedAt" IS NULL AND (
    EXISTS (
      SELECT 1
        FROM "tags"
       WHERE "tags"."postId" = "post"."id" AND (
         "tags"."classroomId" IN (classroom ids) OR
         "tags"."locationId" IN (location ids) OR
         "tags"."studentId" IN (student ids)
       )
    )
  )
)

When I run EXPLAIN ANALYZE on the query I get this...

->  HashAggregate  (cost=4636.49..4637.35 rows=86 width=4) (actual time=23.245..23.269 rows=47 loops=1)"
    Group Key: tags_1."postId"
    ->  Seq Scan on tags tags_1  (cost=0.00..4636.22 rows=107 width=4) (actual time=1.147..23.214 rows=47 loops=1)
        Filter: (("classroomId" = 11) OR ("locationId" = 27) OR ("studentId" = 29))
        Rows Removed by Filter: 136955

It would be ideal to be able to somehow index these fields so that it would be faster to get all posts for specific users and places.  Does the community here have any recommendations for how I would be able to speed this section of query up?

I managed, so far, to make the query about 50% faster by indexing on the comments and likes but this is still a large bottleneck for me.  I have tried a few indices to help this but it hasn't really helped.  Maybe indices aren't the problem, is there a more efficient way to do multiple IN queries that I'm not aware of?

I appreciate any feedback anyone can give!  This is Postgres 9.5.2 by the way.

Possible optimisations

There are a few tricks you can employ:

Use a partial index to try to skip out all the deleted_at rows.
Use a few strategic composite indexes on tags to look for specific classrooms, students or locations.
Try to have those indexes to be covering indexes
Make sure that PostgreSQL has got all the information about row visibility (i.e. VACUUM ANALYZE) so thtat PostgreSQL tries to use index only scans.
Have the query avoid ORs in conditions (they tend to be very difficult to optimize). Use UNION instead.

Practical approach

This is how I have tried to mimick your scenario on DBFiddle (I have slightly changed your column naming and followed a more "PostgreSQL-standardish" approach):

First, I create a simplified version of your tables, and fill it with an amount of data similar to the one you state

 -- Simplified version of posts table...
 CREATE TABLE posts
 (
     post_id integer primary key,
     deleted_at timestamp without time zone default NULL
 ) ;

 -- Simplified version of tags table...
 CREATE TABLE tags
 (
     tag_id integer primary key,
     post_id integer NOT NULL REFERENCES posts(post_id),

     classroom_id integer,
     location_id integer,
     student_id integer
 ) ;

 -- Insert 116K posts
 INSERT INTO posts (post_id, deleted_at)
 SELECT
    generate_series(1, 116001),
    case when random() < 0.25 then date '2017-01-01' else NULL end AS deleted_at ;

 -- Insert 136K tags
 INSERT INTO tags(tag_id, post_id, classroom_id, location_id, student_id)
 SELECT
      generate_series(1, 136000) AS tag_id,
      random()*116000 +1 AS post_id,
      random()*100 +1 AS clasroom_id,
      random()*100 +1 AS location_id,
      random()*100 +1 AS student_id ;

At this point, I execute a query taht tries to mimick the provided WHERE condition, and have PostgreSQL explain it:

 -- Explain how long does it take to use the "reference query"
 EXPLAIN ANALYZE
 SELECT
     post_id
 FROM
     posts
 WHERE
     posts.deleted_at IS NULL AND
     (
     EXISTS
       (
       SELECT 1
         FROM tags
        WHERE tags.post_id = posts.post_id  AND
          (
          tags.classroom_id IN (1, 20, 35) OR
          tags.location_id IN (32, 40, 23) OR
          tags.student_id IN (20, 30, 40)
          )
       )
     ) ;

 -- DBFiddle (at this moment, and under current load conditions) takes about 70 to 120 ms to perform this query

 | QUERY PLAN                                                                                |
 | :--------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
 | Nested Loop  (cost=4015.66..5369.37 rows=276 width=4) (actual time=41.336..72.243 rows=8094 loops=1)                                                                   |
 |   ->  HashAggregate  (cost=4015.37..4017.37 rows=200 width=4) (actual time=41.320..44.876 rows=10787 loops=1)                                                          |
 |         Group Key: tags.post_id                                                                                |
 |         ->  Seq Scan on tags  (cost=0.00..3999.04 rows=6534 width=4) (actual time=0.035..37.036 rows=11284 loops=1)                                                    |
 |               Filter: ((classroom_id = ANY ('{1,20,35}'::integer[])) OR (location_id = ANY ('{32,40,23}'::integer[])) OR (student_id = ANY ('{20,30,40}'::integer[]))) |
 |               Rows Removed by Filter: 124716                                                                                |
 |   ->  Index Scan using posts_pkey on posts  (cost=0.29..6.75 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=10787)                                             |
 |         Index Cond: (post_id = tags.post_id)                                                                                |
 |         Filter: (deleted_at IS NULL)                                                                                |
 |         Rows Removed by Filter: 0                                                                                |
 | Planning time: 0.353 ms                                                                                |
 | Execution time: 73.526 ms                                                                                |

To use the tricks explained before:

 -- We create a few indexes
 -- One *conditional* index to clear out "deleted_at" rows in posts
 CREATE INDEX idx_posts_alive ON posts(post_id) WHERE deleted_at IS NULL ;

 -- Compound indexes for classroom, location and student + post_id in the tags table
 CREATE INDEX idx_tags_classroom_id ON tags(classroom_id, post_id) ;
 CREATE INDEX idx_tags_location_id ON tags(location_id, post_id) ;
 CREATE INDEX idx_tags_student_id ON tags(student_id, post_id) ;

and make statistics up-to-date for PostgreSQL:

 -- Update all table information to allow for index-only-scans
 VACUUM ANALYZE tags;

 -- (id. just in case, for table posts)
 VACUUM ANALYZE posts;

And now, we

     -- An alternate version of the query, that focus first on tags (using index-only scans)
     -- It avoids ORing conditions (which tend to be difficult to optimize)
     -- and uses, instead, UNIONs

 EXPLAIN ANALYZE
 SELECT
     posts.post_id
 FROM
     tags
     JOIN posts ON posts.deleted_at IS NULL and posts.post_id = tags.post_id
 WHERE
     tags.classroom_id IN (1, 20, 35)
 UNION
 SELECT
     posts.post_id
 FROM
     tags
     JOIN posts ON posts.deleted_at IS NULL and posts.post_id = tags.post_id
 WHERE
     tags.location_id IN (32, 40, 23)
 UNION
 SELECT
     posts.post_id
 FROM
     tags
     JOIN posts ON posts.deleted_at IS NULL and posts.post_id = tags.post_id
 WHERE
     tags.student_id IN (20, 30, 40) ;

 -- In DBFiddle, as of now, this provides about 31 ms exeuction time (about 3x better than the original query)

 | QUERY PLAN                                                                                |
 | :-------------------------------------------------------------------------------------------------------------------------------------------------------------- |
 | HashAggregate  (cost=7097.27..7185.50 rows=8823 width=4) (actual time=34.253..36.499 rows=8094 loops=1)                                                         |
 |   Group Key: posts.post_id                                                                                |
 |   ->  Append  (cost=0.71..7075.22 rows=8823 width=4) (actual time=0.044..30.810 rows=8688 loops=1)                                                              |
 |         ->  Nested Loop  (cost=0.71..2335.97 rows=2956 width=4) (actual time=0.044..8.648 rows=2562 loops=1)                                                    |
 |               ->  Index Only Scan using idx_tags_classroom_id on tags  (cost=0.42..118.05 rows=3931 width=4) (actual time=0.030..0.876 rows=3391 loops=1)       |
 |                     Index Cond: (classroom_id = ANY ('{1,20,35}'::integer[]))                                                                                |
 |                     Heap Fetches: 0                                                                                |
 |               ->  Index Only Scan using idx_posts_alive on posts  (cost=0.29..0.55 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=3391)                 |
 |                     Index Cond: (post_id = tags.post_id)                                                                                |
 |                     Heap Fetches: 0                                                                                |
 |         ->  Nested Loop  (cost=0.71..2354.87 rows=2999 width=4) (actual time=0.023..9.642 rows=3037 loops=1)                                                    |
 |               ->  Index Only Scan using idx_tags_location_id on tags tags_1  (cost=0.42..119.03 rows=3987 width=4) (actual time=0.018..1.090 rows=4076 loops=1) |
 |                     Index Cond: (location_id = ANY ('{32,40,23}'::integer[]))                                                                                |
 |                     Heap Fetches: 0                                                                                |
 |               ->  Index Only Scan using idx_posts_alive on posts posts_1  (cost=0.29..0.55 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=4076)         |
 |                     Index Cond: (post_id = tags_1.post_id)                                                                                |
 |                     Heap Fetches: 0                                                                                |
 |         ->  Nested Loop  (cost=0.71..2296.15 rows=2868 width=4) (actual time=0.023..9.715 rows=3089 loops=1)                                                    |
 |               ->  Index Only Scan using idx_tags_student_id on tags tags_2  (cost=0.42..115.99 rows=3813 width=4) (actual time=0.018..1.159 rows=4117 loops=1)  |
 |                     Index Cond: (student_id = ANY ('{20,30,40}'::integer[]))                                                                                |
 |                     Heap Fetches: 0                                                                                |
 |               ->  Index Only Scan using idx_posts_alive on posts posts_2  (cost=0.29..0.56 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=4117)         |
 |                     Index Cond: (post_id = tags_2.post_id)                                                                                |
 |                     Heap Fetches: 0                                                                                |
 | Planning time: 0.667 ms                                                                                |
 | Execution time: 37.769 ms                                                                                |

The end result is a 3x speed increase when doing SELECT. Obviously, this will need updating depending a lot on your specific case (I've made a very simplified version to show the tricks). Obviously, this comes at the cost of maintaining a certain number of indexes, and increasing INSERT and UPDATE times. You hae to analyze, for your specific case, if this is an overall improvement or an overall worsening..

dbfiddle here


MYSQL showing access denied for use '"@ localhost' when trying to create a new database I just installed a new server and tried to create a new database via phpmyadmin.

When I try to create a new database I get the below error message:

To be precise MYSQL said:

  #1044 - access denied for use '"@ localhost'

Does anyone know what may be causing this error or what steps should I take to further troubleshoot?

1. Try to login Login with any valid mysql user credentials.

If the root user of MySQL doesn't have a password (which is a bad pratice) ensure to set below in config.inc.php file (for eg path: /etc/phpMyAdmin/config.inc.php)

$cfg['Servers'][$i]['AllowNoPassword'] = True;

2.You can also create a new mysql user using below.

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'new_password';

GRANT ALL privileges ON my_db.* TO 'new_user'@'localhost'; IDENTIFIED BY 'new_password';

flush privileges;


TSQL and Table Lock on one row So we have a table that is being used for concurrent access across a number of threads and processes. For certain actions, we want to get an exclusive table lock on the entire table, blocking all access until the action is complete.

Previously, one of our developers did this:

SELECT 1 FROM [TableName] (TABLOCKX);

This worked just fine while our table was small, but as it grew larger became a problem.

After doing some research of this query it seems that it was running once for each row in the table (what SELECT 1 would do, naturally) when really what we wanted was to just select one row, then get our table lock and get out as quickly as possible. I then proposed that we change the query to:

SELECT TOP 1 1 FROM [TableName] (TABLOCKX);

We tested this and it seems to work. The execution plan also recognizes that this will only touch 1 row (whereas the previous one touched all rows).

The question that I have is: Does this work the way that I am thinking? Will this block all access to the table for all operations (including NOLOCK operations). Is there a better way to do this?

Note: The reason that we're doing it this way is that we have a fairly complex .NET library in which we are building some queries on the fly. Because we want them to be modular, we want to make sure that devs don't have to modify or duplicate the queries themselves and can simply note at the beginning of the transaction that they are going to be doing updates to the database. Thereby we can make a single .LockTables(X) which will lock the right tables for the corresponding group.

Any queries that read the table WITH (NOLOCK) or with READ UNCOMMITTED isolation level will proceed despite your WITH (TABLOCKX).

More info about lock modes can be found here:
https://technet.microsoft.com/en-us/library/ms175519(v=sql.105).aspx

Personally I'd say that if you have queries reading from the table WITH (NOLOCK) then they should fully anticipate that they will get bad results so blocking those reads wouldn't be a priority. If your those queries need to return data that is actually correct then they should not use NOLOCK. https://sqlstudies.com/2015/03/18/why-not-nolock/


One Big Database vs. Several Smaller Ones We have a situation were we can (A) deploy instances of an applications in one MySQL database using table prefixing or (B) use different MySQL databases for each instance of the application, for e.g.,

Setup "A":

central_database
  app1_table1
  app1_table2
  app1_tablen
...
  appn_table1
  appn_table2
  appn_tablen

The end result being a large db with many tables.

Setup "B":

app1_db
  table1
  table2
  tablen

...

appn_db
  table1
  table2
  tablen

The end result being many databases with some tables.

All things equal (e.g., amount of data, number of app instances, etc), what are the pros and cons of going with either approach? What would be detrimental to database performance and maintenance? The application is PHP 5 based, run over Apache 2.x, and we're running MySQL 5.x.

Many thanks for your time and thoughts!

I ran a system with the best part of a thousand databases, spread across multiple servers. They were all an identical structure and were synchronised with a template database which was on each of the machines.

This allowed me the ability to migrate databases from one db to another if one was getting excessively over-loaded, and as the client mix changed, I could create new databases on different servers to load balance across the servers. This was the biggest advantage I got from the system, in that I had multiple large lumps of tin performing multiple complicated queries simultaneously on the separate servers.

The great thing about this, is that you can add servers to the configuration at your own speed, as each server starts to get over-loaded, add another into the mix, migrate some dbs across to the new server and end up with a nicely load balanced set of servers. A really nice and simple way to add scale to the system as and when it is required!

The reason I went with this approach rather than the single huge database approach, was the sheer size of the potential database that would have been created... each of the 1000 databases had 200 tables, and many of the individual tables within each of the databases comprised many hundreds of millions of rows of data!

A single database configuration would have required certain tables (approx 8 of them) to have multi-billions of rows of data, and the total db size would have been over 10Tb. We were able to have multiple servers with 5Tb of RAID 10 storage, with many databases on each.

That's what I would do! Hope it helps your decision making... :)


XACT_ABORT ON not working as expected on SQL Server 2012? It looks like XACT_ABORT ON is not working as expected. Here are a table and a procedure that inserts into it:

CREATE TABLE rain
    (
      rain_time DATETIME ,
      location VARCHAR(100)
    );
GO

CREATE PROCEDURE insert_rain
    @rain_time DATETIME ,
    @location VARCHAR(100)
AS
    BEGIN;
        SET XACT_ABORT ON;
        BEGIN TRANSACTION;
        PRINT 'before insert';
        INSERT  INTO rain
                ( rain_time, location )
        VALUES  ( @rain_time, @location );
        PRINT 'after insert';
        COMMIT;
    END;
GO

I've created a trigger to imitate a runtime error:

CREATE TRIGGER rain_no_insert
ON rain
FOR INSERT
AS
RAISERROR('Cannot insert', 16, 1);
GO

When I invoke the insert procedure, I do get the error, but the execution does not stop, because the last PRINT prints its 'after insert' message:

EXEC insert_rain
    @rain_time = '2015-03-30 12:34:56',
    @location = 'Wautoma, WI';

before insert
Msg 50000, Level 16, State 1, Procedure rain_no_insert, Line 5
Cannot insert

(1 row(s) affected)
after insert

Also the transaction commits:

SELECT * FROM dbo.rain;

rain_time               location
----------------------- ---------------------------------
2015-03-30 12:34:56.000 Wautoma, WI

Why my XACT_ABORT does not abort?

Check Microsoft documentation, specifically the top line:

  The THROW statement honors SET XACT_ABORT.  RAISERROR does not. New applications should use THROW instead of RAISERROR.

I believe that is your problem, right there.

https://msdn.microsoft.com/en-us/library/ms188792.aspx


Install pg_trgm in PostgreSQL 8.4 on Windows XP I just tried to enter in psql shell as user postgres:
> psql -U postgres

then connect to my database
> \c MyDATA

and then try to execute this command to install similarity function from pg_trgm
> \i c:/postgresql/8.4/share/contrib/pg_trgm.sql

I got this result, and seems not to be working:
SET
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE OPERATOR
psql:c:/postgresql/8.4/share/contrib/pg_trgm.sql:44: NOTICE:  el tipo Â«gtrgmÂ»
no ha sido definido aÃºn
DETALLE:  Creando una definiciÃ³n de tipo inconclusa.
CREATE FUNCTION
psql:c:/postgresql/8.4/share/contrib/pg_trgm.sql:49: NOTICE:  el tipo de argumen
to gtrgm estÃ¡ inconcluso
CREATE FUNCTION
CREATE TYPE
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE OPERATOR CLASS
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE OPERATOR CLASS

Is probably something that I have been missing, but I don't know what?
Is this the correct way to install it in 8.4 under XP? (I can't change OS).

Answered in comments by a-horse-with-no-name:

Those are only "notice" message, not errors (I don't understand what they say, but NOTICE is never a problem or even an error). All statements ran without error, so everything is fine and the module was created. But you should really upgrade to a modern version (12 or 13) and also ditch Windows XP.


How to declare reserved words as variables in Oracle PL/SQL? What is the best way to bypass the Oracle SQL reserved-word constraint for PL/SQL value declaration?  I realize bind and substitution may be acceptable alternatives for an actual query, but what about Oracle PL/SQL?  For example I would like to declare a numeric variable as "num", structured below within the Oracle PL query:

SET SERVEROUTPUT ON
declare
  num number;
begin
   num := 10;
   DBMS_OUTPUT.put_line('The value of num ' || num);
end;

NUM is not a reserved word.

The best way is not to use reserved words.

If you insist on using them, you can put them between quotation marks. This does not work:

SET SERVEROUTPUT ON
declare
  begin number;
begin
   begin := 10;
   DBMS_OUTPUT.put_line('The value of begin ' || begin);
end;
/

This does:

SET SERVEROUTPUT ON
declare
  "begin" number;
begin
   "begin" := 10;
   DBMS_OUTPUT.put_line('The value of begin ' || "begin");
end;
/

Quotation marks also make the variable name case-sensitive. "begin", "Begin" and "BEGIN" are different variables.


SQL SERVER 2014 FCI Cluster taking too long to failover I support a multi-instance, two node failover cluster (active-active).  Both instances are running SQL 2014, on Windows Server 2008R2.  The nodes each have 3/4 TB of memory and 32 cores (64 with HT).  SQL is configured with a Max memory of 350GB for each instance.  There are between 60 and 100 databases per instance with 3-5 TB of datafiles in  per instance.

I’m having some issues with failover times that I’m trying to resolve.
The issue appears to be centered around shutting down the SQL instance prior to the failover.  When a manual failover is performed, we do the following to speed things up to this point.

Checkpoint all databases just prior to failover to get as many
dirty pages written to disk prior to the beginning of the failover
as possible.  Failovers were much longer prior to adding this step
Manual failover is initiated, SQL goes into SA only mode for 1-2
minutes and then the SQL instance stops.
It takes another 1-2 minutes to release the memory back to the OS before the failover occurs and the resource comes up on the other node.

If the SQL instance has only limited data in memory the failover happens much more quickly.

We looked at I/O and CPU metrics and don’t see any significant issues.

I’m looking for resources and ideas to help reduce the time it takes for this failover to happen.
Thanks,
-Luke.

The issue appears to be centered around shutting down the SQL instance prior to the failover.

You are correct, because that's exactly what is happening. You're shutting it down nicely on one node, moving resources, and starting it up on another node. The question actually doesn't have anything to do with clustering but with speeding up a clean SQL Server shutdown.

  When a manual failover is performed, we do the following to speed things up to this point.

When SQL Server cleanly shuts down, it flushes all of the buffers and asks internal systems to shut themselves down.

So, how do you make this faster? First, this is only really the case when you are cleanly shutting down - which most likely won't happen during a real failure. Secondly, you don't let SQL Server shut down - I would look into using Availability Groups and testing your manual failover times to compare and contrast the differences.

Additionally, no matter what you use, indirect checkpoints are much more effective than traditional.


pgpool load balancing is sending all queries only to master My two postgresql servers are configured for streaming replication, which is working fine.

Pgpool is configured for Master Slave mode / Load Balance Mode.

pgpool.conf:

listen_addresses = '*'
port = 9999
backend_hostname0 = 'master-postgres-ip'
backend_port0 = port-no
backend_weight0 = 1
backend_data_directory0 = 'data-dir'
backend_hostname1 = 'slave-postgres-ip'
backend_port1 = port-no
backend_weight1 = 1
backend_data_directory1 = 'data-dir'
load_balance_mode = on
master_slave_mode = on
master_slave_sub_mode='stream'

I expected all write queries will go to primary and read queries will be distributed between two. But, all the queries are going only to master. However, if I stop master, queries are going to slave.

Can somebody tell me what might be going wrong?

pgpool gives below log on startup:

2015-11-03 17:25:56: pid 21284: LOG:  find_primary_node: checking backend no 0
2015-11-03 17:25:56: pid 21284: LOG:  find_primary_node: checking backend no 1
2015-11-03 17:25:56: pid 21284: DEBUG:  SSL is requested but SSL support is not available
2015-11-03 17:25:56: pid 21284: DEBUG:  authenticate kind = 3
2015-11-03 17:25:56: pid 21284: ERROR:  failed to authenticate
2015-11-03 17:25:56: pid 21284: DETAIL:  invalid authentication message response type, Expecting 'R' and received 'E'
2015-11-03 17:25:56: pid 21284: DEBUG:  find_primary_node: no primary node found

Initially, I have set sr_check_period in pgpool.conf to 0 (zero) to disable SR checking. I thought of avoiding this check and invariably balance the load. But this has caused the problem.

After I have given a non-zero value to this attribute, load balancing started working.


Extracting Extended Entity Relationship Diagram from a Relational Database I am researching how to extract an Extended Entity Relationship (EER) diagram from a Relational Database.

I'm using Visual Studio 2010 and SQL Server 2008.

I've found tools for Entity Relationship Diagrams (ER) on the Internet, however I haven't find any for EER.

Do you know where I might find anything on this topic?

The only significant reference to the notation I've ever read is the Elmasri and Navathe textbook - EER gets taught in database papers from time to time, but I don't think it's widely used in practice..  I'm not aware of any data modelling tools with support for the notation.


Handling unique IDs on client side Reading around on the topic hasn't got me closer to understanding best practices. I'm also relatively new and self-taught in programming, so might have some questions about how to actually execute the solution.

The app I'm building requires a lot of DOM elements to be generated based on database rows. These should be updatable by the user and at the minute I'm using the auto incrementing primary key as an html attribute id.

Obviously I don't want users to modify this, or in the associated javascript object, to then affect other users' data when saved or updated. I have considered the following options:

Not using auto incrementing but a uuid or guid, but I've read these are not all that advisable, especially for db performance.
Concealing the client side id by hashing it when served on the server side. This also doesn't sound particularly performant.
Having a 'lookup table' that is referenced and produces another, non-incremental id. Doesn't seem like a bad idea but I'm a bit unsure how best to go about generating this and how it should look.
Including an extra column in the tables that would be updated that contains for example the users name and would be combined with the primary key. This seems simple enough but the idea of adding this extra column to every table that might be updated sounds redundant and not normalized, etc.
Keeping the primary keys visible but producing a session table for the user that tracks what the which IDs were sent out and therefore which can be updated. New rows can be generated so long as other ones belonging to other users aren't updated.

Or, maybe I'm barking up the wrong tress here :) Thanks!

UUID's make poor primary keys, but there's nothing wrong with them when used as a regular table attribute.  Instead of exposing the AUTO_INCREMENT column to the client connection, adding a UUID column and exposing that should have an insignificant impact overall, and nothing that can't be addressed with a non-clustered index on the new UUID column, if necessary.

I would likely only bother adding the UUID column to the main user table and altering my update procedures to always require the user UUID as a parameter ( and might even, at that point, leave the other parameters as the current AUTO_INCREMENT value ), reinforcing the intent that a particular user can only update their own data, but there's nothing wrong with adding the UUID column to every table which may receive updates and replacing any exposed AUTO_INCREMENT values with the new UUID either.


Why are Denali sequences supposed to perform better than identity columns? In his answer to Which is better: identity columns or generated unique id values? mrdenny says:

  When SQL Denali comes out it will support sequences which will be more efficient than identity, but you can't create something more efficient yourself.

I'm not so sure. Knowing Oracle's sequences, I have either to create a trigger for insert, encapsulate each insert into a call of a stored procedure, or pray that I do not forget to properly use the sequence when I do an ad-hoc insert.

I doubt that the advantages of sequences are so obvious.

I'll answer here as well.  It has to do with the internals of how IDENTITY and SEQUENCE work.

With IDENTITY, SQL Server pre-caches values into memory so that they are readily available. See Martin Smith's answer for the details. As values are used, a background process generates more values.  As you can imagine this pool can run out pretty quickly, leaving the application at the mercy of the background process that is generating the values.

With SEQUENCE, SQL Server allows you to define how large the cache should be.  While SQL Server doesn't actually keep the values in the cache, it only keeps the current value and the top end value, this will greatly reduce the amount of IO that is needed to create values.

Don't set the cache too high, as this will reduce the number of numbers which can be used: if SQL Server were to crash, any values specified in the current cache range which weren't used would be lost.

As for row insertion, just specify a default value for the column, like so:

DEFAULT (NEXT VALUE FOR Audit.EventCounter),


Indexes in a big table. (About 400 mil of rows) I have a little deal. I have this table in a SQL Server 2012 Standard edition database. The table contains about 400 mil of rows.

CREATE TABLE [dbo].[prodeje]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [partner] [tinyint] NOT NULL,
    [id_paragon] [varchar](20) NOT NULL,
    [typ_pohybu] [varchar](2) NULL,
    [store] [varchar](20) NULL,
    [datetime] [datetime] NOT NULL,
    [date] [date] NULL,
    [id_prod] [varchar](50) NOT NULL,
    [customer] [varchar](50) NULL,
    [price_per_unit] [decimal](18, 2) NULL,
    [stock_price] [decimal](18, 2) NULL,
    [pc] [decimal](18, 2) NULL,
    [amount] [decimal](16, 4) NULL,
    [action] [varchar](50) NULL,
    [action_cupon] [varchar](50) NULL,
    [bonus_gratis] [varchar](50) NULL,
    [fin_sleva] [decimal](18, 2) NULL,
    [exclude] [tinyint] NULL
) ON [PRIMARY]

It's a table with sales. So most of the query will include column partner, store, date, id_prod, price per unit * amount and distinct count on column id_paragon for baskets. Column id_prod is foreign key for table with products. Column exclude is column with information if the row is good. All query will contains in where clause exclude=0.

And I am thinking about indexes. Because it's more usually use part of the year, so my first think was that i will do clustered index on column date. And in query i query first only data what i need. So I will query on few mil of rows and it will be quick. And index for id_prod. But I think that is not good way to do the indexes. So I have questions for you, what indexes will you do on this table.

If you need more info write a comment. I will add what you want for think about it.

Thanks

Since you are optimizing for reads you could simply add one optimal index per query. So if your query is

select price_per_unit from Sales where exclude = 0 and partner = 42

Create the index (exclude, partner) include (price_per_unit).

You say that writes and disk spaces are not of significant concern. So add the optimal indexes to support your reads.


SQL1641N error on linux while running db2start using db2 express c on linux (LUW) but the file system is not mounted using nosuid I am trying to install and start db2 on my linux machine using the following script:

#!/usr/bin/env bash

MKDIR=/bin/mkdir
RM=/bin/rm
TAR=/bin/tar

DB2_SERVER_PROGRAM_PATH=/home/alok/Documents/Installers
DB2_SERVER_PROGRAM=v10.5_linuxx64_expc.tar.gz

echo "changing to $DB_SERVER_PROGRAM_PATH"
cd ${DB2_SERVER_PROGRAM_PATH}
echo "removing expc"
${RM} -rf expc
echo "untarring ${DB2_SERVER_PROGRAM}"
${TAR} zxf ${DB2_SERVER_PROGRAM}

echo "changing to expc"
cd expc

echo "Installing db2 as root"
sudo ./db2_install

echo "Adding user group db2grp1 as root"
sudo groupadd db2grp1
echo "Adding user group dasadm1 as root"
sudo groupadd dasadm1
echo "Adding user group db2fgrp1 as root"
sudo groupadd db2fgrp1

echo "Adding user db2inst1. Please provide password for new user db2inst1."
sudo useradd -g db2grp1 -G dasadm1 -m db2inst1
sudo passwd db2inst1

echo "Adding user dasusr1. Please provide password for new user dasusr1."
sudo useradd -g dasadm1 -G db2grp1 -m dasusr1
sudo passwd dasusr1

echo "Adding user db2fenc1. Please provide password for new user db2fenc1."
sudo useradd -g db2fgrp1 -m db2fenc1
sudo passwd db2fenc1

cd /opt/ibm/db2/V10.5/instance

echo "Creating DAS as root via user dasusr1."
sudo ./dascrt -u dasusr1

echo "Creating database instance as root via user db2fenc1 and db2inst1."
sudo ./db2icrt -u db2fenc1 db2inst1

echo "Setting communication protocol to tcpip as user db2inst1."
sudo -u db2inst1 ../adm/db2set DB2COMM=tcpip
echo "Setting SVCENAME 50000 for dbm cfg as user db2inst1."
sudo -u db2inst1 ../bin/db2 update dbm cfg using SVCENAME 50000

echo "starting db2 as user db2inst1."
sudo chmod +x ../adm/db2start
sudo -u db2inst1 ../adm/db2start

netstat -an | grep 50000

Everything works fine, till I get to the penultimate line:

sudo -u db2inst1 ../adm/db2start

SQL1641N  The db2start command failed because one or more DB2 database
manager program files was prevented from executing with root
privileges by file system mount settings.

Now I checked /etc/fstab and it says:

proc            /proc           proc    nodev,noexec,nosuid 0       0
/dev/sda1       /               ext4    errors=remount-ro,user_xattr 0       1
UUID=d4d794eb-1e68-4ddf-a9ec-249a22f084fb none            swap    sw             0       0
/swapfile swap swap defaults 0 0

My installation path, /opt/ibm/db2/V10.5/adm is basically on /dev/sda1 and it's not mounted using the nosuid option.

Here is the output of mount:

alok@hostname:~$ mount
/dev/sda1 on / type ext4 (rw,errors=remount-ro,user_xattr)
proc on /proc type proc (rw,noexec,nosuid,nodev)
sysfs on /sys type sysfs (rw,noexec,nosuid,nodev)
none on /sys/fs/fuse/connections type fusectl (rw)
none on /sys/kernel/debug type debugfs (rw)
none on /sys/kernel/security type securityfs (rw)
udev on /dev type devtmpfs (rw,mode=0755)
devpts on /dev/pts type devpts (rw,noexec,nosuid,gid=5,mode=0620)
tmpfs on /run type tmpfs (rw,noexec,nosuid,size=10%,mode=0755)
none on /run/lock type tmpfs (rw,noexec,nosuid,nodev,size=5242880)
none on /run/shm type tmpfs (rw,nosuid,nodev)
binfmt_misc on /proc/sys/fs/binfmt_misc type binfmt_misc (rw,noexec,nosuid,nodev)
shmfs on /dev/shm type tmpfs (rw,size=2048m)
gvfs-fuse-daemon on /home/alok/.gvfs type fuse.gvfs-fuse-daemon (rw,nosuid,nodev,user=alok)

This again indicates that / is not mounted in nosuid mode.
So why am I getting error SQL1641N? Is it a different problem throwing out this message instead?

The files in /opt/ibm/db2/V10.5/ are not to be used for a specific instance.  When you run the db2icrt command, it copies a series of files and directories into ~db2inst1/sqllib, and sets the permissions accordingly.  (Some of the directories in ~db2inst1/sqllib will be soft links back to directories in /opt/ibm/db2/V10.5).

For example, you should find that ~db2inst1/sqllib/adm/db2start has permissions set to 6555 (i.e. -r-sr-sr-x), owned by root, and have the group owner set to the instance owner's primary group.

You should not be executing things from /opt/ibm/db2/V10.5/adm.  That is why the permissions of the files in that directory are set the way they are – /opt/ibm/db2/V10.5/adm/db2start should be 0444.

So, to start your instance, you should be executing ~db2inst1/sqllib/adm/db2start.

Be aware that you also need to initialize the db2inst1 user environment prior to starting the instance, so you should use the -i option for sudo to handle this:

sudo -i -u db2inst1 /home/db2inst1/sqllib/adm/db2start

One last note:  All of this configuration (except perhaps the db2start) can be done using a response file and db2setup.   db2_install has been deprecated, so using response files is the way to handle this going forward.


MDW Data Collector Server Activity collection step 3 suddenly stopped working For many weeks my data collections from my database instace have been uploading to my MDW server. Suddenly step 3 of the Server Activity Collector has stopped working giving the following error:

Job Name          DataCollector Server Stats Coll n Upload
Step Name         collection_set_3_noncached_collect_and_upload_upload
Duration          00:00:05
Sql Severity      0
Sql Message ID    0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

  Message:
  Executed as user: domain_svcAccount. SSIS error. Component name: DFT - Find and save sql text, Code: -1071636471, Subcomponent: LKUP - Look up query text on target server [2], Description: SSIS Error Code DTS_E_OLEDBERROR.

  An OLE DB error has occurred. Error code: 0x80040E14.   An OLE DB record is available.

  Source: "Microsoft SQL Server Native Client 11.0"
  Hresult: 0x80040E14  Description: "The handle that was passed to dm_exec_sql_text was invalid.".

  SSIS error. Component name: DFT - Find and save sql text, Code: -1071611309, Subcomponent: LKUP - Look up query text on target server [2], Description: OLE DB error occurred while fetching parameterized rowset. Check SQLCommand and SqlCommandParam properties.  SSIS error. Component name: DFT - Find and save sql text, Code: -1073450974, Subcomponent: SSIS.Pipeline, Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "LKUP - Look up query text on target server" (2) failed with error code 0xC0208253 while processing input "Lookup Input" (16). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

  The master package exited with error, previous error messages should explain the cause.
  Process Exit Code 5.  The step failed.

Has anyone encountered this error before?

This error:

  0x80040E14 Description: "The handle that was passed to dm_exec_sql_text was invalid."

indicates a "handle" was passed into the sys.dm_exec_sql_text function that was not an actual, valid, SQL handle.

You'll see this error if you run this code:

SELECT *
FROM sys.dm_exec_sql_text(0)

sys.dm_exec_sql_text is used to obtain the query text for queries that reside in the plan cache, and can be seen in action via this sample code:

SELECT TOP(10)
    decp.plan_handle
    , dest.text
FROM sys.dm_exec_cached_plans decp
CROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) dest;

It looks like the data collector has a bug; what version of SQL Server do you have installed?


Extract data to another computer I have SQL Server 2008 R2 Express edition, and I want to export the data in that database to another computer so I can import it again in that computer.

How please?

What I have tried

Right-click in the database -> Tasks -> Export -> Set server name + password + set database source.

My problem

I found that I have to set the destination, but I didn't find the destination as a file or something like that, I just found that the destination is the same database which I am extracting the data from.

Help please, thanks in advance

There are a few ways to do this, some easier and safer than others.  You could mess around with detaching and attaching, but I will never recommend that solution for normal cases like these.  Since you have your database on SQL Server Express Edition, then it can't be larger than 10 GB.  It'll be a quick and painless process to just backup the database, and jump over to your other machine and do the restore.

The Import/Export features aren't worth the hassle in this case.  Backup and restore would be the easiest route.

If you're looking to only move a certain amount of data (say, a handful of tables), then I'd just as soon generate the scripts (for data and schema if that's desired).  You can do this by right-clicking the database in Object Explorer, Tasks, and Generate Scripts.  You'll have numerous options for this task, and you can specify a subset of objects to script out.  Then you could run that script on your destination instance.

Just to reiterate, I'd do the backup/restore for the entire database copy, or generate scripts for a subset of objects.


What is the difference between terminating an Oracle sql statement with semicolon or with a slash on the next line? The following two queries seem to be equivalent when executed in sql*plus:

1.

select * from user_tables;

2.

select * from user_tables
/

Is there any difference between both versions?

There is no difference for a single SQL statement.  The '/' character on its own line tells SQL*Plus to execute the command in the buffer.  You can use the semicolon at the end of most SQL statements as a shorthand for the '/'.  If you want to execute a PL/SQL block or to execute a handful of SQL statements like CREATE TYPE, however, you need to use the '/'

SQL> begin
  2    null;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> create type foo as object (
  2    col1 number,
  3    col2 number );
  4  /

Type created.

Different tools may have slightly different conventions for how you execute multiple SQL and PL/SQL statements in a script so be aware that this is SQL*Plus specific.


How to start my 9.2 data directory using an 8.4 server? I have an old version of postgres (8.4.13) that doesn't run for various reasons. I plan to move the database files in there to a new install of postgres 9.3. How would I go about doing this? For one I can not start up postgres when I run:

postgres -D /var/lib/pgsql/9.2/data

As I get :

FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 9.2, which is not compatible with this version 8.4.13.

The easiest route would be to figuring out how to run this server but if not how would I locate the db files in it?

EDIT:

How this actually happened was last week I wanted to intiliaze postgres server commands so I could easily stop and restart the server since my knowledge of postgresql is pretty limited. I ended up installing postgresql-server for RHEL and it was initially working fine. The commands weren't working so I forgot about it. The databases were working fine too at that time. I log on today and to my shock the commands were working but it also wiped the dbs. So my presumption is it installed a completely new server that could be accessed via the commands.

Somehow this tampered whatever settings I had.

Typing postgresql --version gives me version 8.4.13.
If I can only start it now by pointing postgres -D to the correct pgsql.

There are two pgsql dirs:

/var/lib/pgsql/9.2/data
/var/lib/pgsql/data

The latter seems to be the newest install of postgresql as the pg_hba.conf files are unedited.

I used to be able to start the 8.4.13 server with 9.2 it would just give me a WARNING: Some features might not be available. I don't know why installing postgresql-server would change this now. As they are separate dirs.

Only the same major version can read the data files.

You need to upgrade properly. To do that you'll have to install 8.4, then use pg_upgrade, or use pg_dump and pg_restore. See the manual for more information.

That said, the error actually has the versions the other way around - it says you're running 8.4 binaries and trying to point them at a datadir from 9.2. Verify this with postgres --version. If you turn out to have 8.4 binaries and a 9.2 datadir then pg_upgrade won't do you any good - you just need to install 9.2 (or use the correct PATH if it's already installed) and run the 9.2 datadir using 9.2 binaries.

Update given extra info in question:

It is very likely that both 8.4 and 9.2 are running fine on your machine, just on different ports.

sudo netstat -ltnp |grep postgres

will show you listening PostgreSQL servers, and

psql -p 5433

(or whatever) will connect to a different port.

The databases appeared to vanish because when you installed the new PostgreSQL it became the default version that's run.

If you find that the 8.4 install is in fact not running, you probably just need to change the port it starts on in its configuration so it runs on a different port, then connect to it as shown above.


SQL Server Cluster Failover Making DB Inconsistency Error I encountered the following DB tables inconsistency error whenever I do a cluster 'move' over from Server-A to Server-B and then do a DBCC CHECKDB on the database in Server-B:

  SQL Server detected a logical consistency-based I/O error:incorrect
  checksum. It occurs during read of page(1:142) in dtabase ID 4 at
  offset 0x0000000011c))) in file
  'X:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\MSDB.mdf.

Sometimes, the above DB error may vary and occurred to other DB tables :(

The two servers (A & B) are using Window Server 2008 R2 Enterprise Edition (SP1). Each server has its own NAS (SUN Storage 7410), and using Double-Take Availability to create a single copy of the database across the servers. Also, each server has 2 iSCSI connectors (redundancy purpose) connected to the NAS.

The peculiar thing is the DB inconsistency error did NOT happen when I change either one of the following configuration:

Move from Server-B to Server-A (no DB error on Server-A using DBCC)
Software disable one of the 2 iSCSI connectors on Server-B
Use internal local harddisk, instead of NAS in Server-B (Server-A still using NAS)

Anyone can help to advise me what else can I do to diagnose the problem to Server-B.

After many trials and errors, the fault lies in NIC card of Server-B. After I replaced it, the cluster move-over operates smoothly without any DB inconsistency ;) However, I do not understand how a NIC card can cause DB inconsistency (a 'ping' to the NIC card works well)


(MYSQL) Select union of rows depending on row before? I have a table in MySQL that looks like this:

CREATE TABLE IF NOT EXISTS `mytable` (
`id` int(11) NOT NULL,
  `data` VARCHAR(255),
  `otherid` int(11),
  ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

With some rows:

ID|STUFF|OTHERID
-----------------
1 |"stuff"| 2
2 |  "x"  | 4
3 |  "y"  | NULL
4 |  "z"  | NULL

I want to select first the row where data = "stuff" and then all the following rows (which could be "infinite") where the next rows are found by id = lastrow.otherid. Like this:

ID|STUFF|OTHERID
-----------------
1 |"stuff"| 2
2 |  "x"  | 4
4 |  "z"  | NULL

If there is no row with data = something, then no rows should be selected.

Any suggestions?

About 4.5 years ago, I wrote a rather aggressive stored procedure to traverse data stored in a hierarchy and bring back all descendants within that table : Find highest level of a hierarchical field: with vs without CTEs

I took the code from the GetFamilyTree function and wrote it as a procedure for you

STORED PROCEDURE

DELIMITER $$
DROP PROCEDURE IF EXISTS GetDataFromData $$
CREATE PROCEDURE GetDataFromData (GivenData VARCHAR(255))
ThisStoredProcedure:BEGIN

    DECLARE rv,q,queue,queue_children VARCHAR(1024);
    DECLARE queue_length,front_id,pos INT;

    SET rv = '';

    DROP TABLE IF EXISTS DataFromData;
    CREATE TEMPORARY TABLE DataFromData LIKE mytable;

    SELECT COUNT(1) INTO @FoundCount FROM mytable WHERE data = GivenData;

    IF @FoundCount = 1 THEN
        SELECT id INTO @FoundID FROM mytable WHERE data = GivenData;

        SET rv = @FoundID;
        SET queue = @FoundID;
        SET queue_length = 1;

        WHILE queue_length > 0 DO
            SET front_id = FORMAT(queue,0);
            IF queue_length = 1 THEN
                SET queue = '';
            ELSE
                SET pos = LOCATE(',',queue) + 1;
                SET q = SUBSTR(queue,pos);
                SET queue = q;
            END IF;
            SET queue_length = queue_length - 1;

            SELECT IFNULL(qc,'') INTO queue_children FROM
            (SELECT GROUP_CONCAT(otherid) qc FROM mytable WHERE id = front_id) A;

            IF LENGTH(queue_children) = 0 THEN
                IF LENGTH(queue) = 0 THEN
                    SET queue_length = 0;
                END IF;
            ELSE
                IF LENGTH(rv) = 0 THEN
                    SET rv = queue_children;
                ELSE
                    SET rv = CONCAT(rv,',',queue_children);
                END IF;
                IF LENGTH(queue) = 0 THEN
                    SET queue = queue_children;
                ELSE
                    SET queue = CONCAT(queue,',',queue_children);
                END IF;
                SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;
            END IF;
        END WHILE;

        SET @sql = CONCAT('INSERT INTO DataFromData SELECT * FROM mytable WHERE id in (',rv,')');
        PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
    END IF;

    SELECT * FROM DataFromData;

END $$
DELIMITER ;

SAMPLE DATA FROM YOUR QUESTION

DROP DATABASE IF EXISTS magistermundus;
CREATE DATABASE magistermundus;
USE magistermundus
CREATE TABLE IF NOT EXISTS `mytable` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `data` VARCHAR(255),
    `otherid` int(11),
    PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO mytable (data,otherid) VALUES ('stuff',2);
INSERT INTO mytable (data,otherid) VALUES ('x',4);
INSERT INTO mytable (data,otherid) VALUES ('y',NULL);
INSERT INTO mytable (data,otherid) VALUES ('z',NULL);
SELECT * FROM mytable;

SAMPLE DATA FROM YOUR QUESTION LOADED

mysql> DROP DATABASE IF EXISTS magistermundus;
Query OK, 1 row affected (0.03 sec)

mysql> CREATE DATABASE magistermundus;
Query OK, 1 row affected (0.01 sec)

mysql> USE magistermundus
Database changed
mysql> CREATE TABLE IF NOT EXISTS `mytable` (
    ->     `id` int(11) NOT NULL AUTO_INCREMENT,
    ->     `data` VARCHAR(255),
    ->     `otherid` int(11),
    ->     PRIMARY KEY (id)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO mytable (data,otherid) VALUES ('stuff',2);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO mytable (data,otherid) VALUES ('x',4);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO mytable (data,otherid) VALUES ('y',NULL);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO mytable (data,otherid) VALUES ('z',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM mytable;
+----+-------+---------+
| id | data  | otherid |
+----+-------+---------+
|  1 | stuff |       2 |
|  2 | x     |       4 |
|  3 | y     |    NULL |
|  4 | z     |    NULL |
+----+-------+---------+
4 rows in set (0.00 sec)

mysql>

STORED PROCEDURE EXECUTED

mysql> CALL GetDataFromData('stuff');
+----+-------+---------+
| id | data  | otherid |
+----+-------+---------+
|  1 | stuff |       2 |
|  2 | x     |       4 |
|  4 | z     |    NULL |
+----+-------+---------+
3 rows in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql>

CAVEAT # 1

Writing this using UNION SELECT was not practical since the depth of the tree of data is not known. This code will handle any depth of data.

CAVEAT # 2

This code does not handle circular references in data (Where (id,otherid) and (otherid,id) coexist in this table).


New foreign key and merge replication We are experiencing an issue when creating a new foreign key to an existing table, which is being replicated to another SQL Server 2008. More specifically, we are adding a new foreign key to this replicated table Foo which references a non-replicated table Bar:

ALTER TABLE Foo WITH CHECK ADD  CONSTRAINT [FK_Foo_Bar2]
FOREIGN KEY([BarId2])
REFERENCES Bar ([BarId])

After executing this script on the main database, the merge replication seems to send it to the subscriber (and a new line is added to the sysmergeschemachange table). And this is where things go wrong: Since Bar is not being replicated, the constraint/fk cannot be added on the subscriber, and thus replication stops completely, because the schema change script fails. This has caused us to re-setup the whole replication several times now!

I wonder why SQL Server 2008 would send this script to the subscriber in the first place? After all, other relations to non-replicated tables had (correctly) not been added in the initial snapshot either.

So it did pretty much what it is described in Make Schema Changes on Publication Databases:

  If a publication is set to allow the propagation of schema changes,
  schema changes are propagated regardless of how the related schema
  option is set for an article in the publication. For example, if you
  select not to replicate foreign key constraints for a table article,
  but then issue an ALTER TABLE command that adds a foreign key to the
  table at the Publisher, the foreign key is added to the table at the
  Subscriber. To prevent this, disable the propagation of schema changes
  before issuing the ALTER TABLE command.

To boot, your exact case is give as an example :) Also, the solution is given there: disable schema changes replication before issuing the ALTER TABLE.

As to why it behaves this way, I would say that a system that would make 'smart' decision what schema changes to replicate and what not would be not only very hard to design, but also very error prone and would cause a lot of surprises and break the principle of least astonishment a lot of times. The gist of it is the relations between objects that are published and objects that are not are often ambiguous or hard to detect (think why is the subject of dependency tracking so vast...). And even when detected, the semantics of a change that affects both a published article and a non-published object are ambiguous to say the least: allow the change only locally? Block the change? I can assure you that whichever answer you venture forward, someone will complain it breaks his case... because the answer is dependent on specific soft business rules, not hard language rules.


How can I (re-)create the Entity Relationship Diagram (ERD) of an existing hidden Oracle database? More precisely, I wan't to re-create the database model / schematics / the Entity Relationship Diagram (ERD) showing all the links between the tables of this existing hidden Oracle Database.

In my job, I use a proprietary application interface (PAI) to manage users and their activities.
This PAI hides the underlying database and any connection to it.
This means that I cannot use standard SQL management interfaces to access this database and its data.

My job requires that I produce User reports.
To do so, I have to extract these reports by using a specific menu of this PAI.
In this menu I can only execute an existing SELECT statement or POST a new SELECT statement in one TextBox.
Unfortunately, the data extracted by the existing SELECT statements is not appropriate to the business requirements.
Furthermore the documents provided by the editor of this PAI contain an extremely light and partial series of schematics of the underlying database.

So, What would you suggest I do to have a better vision of all these data links between the database tables ?

Once I have a better vision of these links, I will be able to write SELECT statements that are extracting more appropriate data for my employer's business.

Extra information :
The PAI prohibits the execution of the following statements : insert, update, dll & dml, functions or procedures.

I can execute the following SELECT Statements :

  SELECT * FROM all_tables      --> results in 800 tables
  SELECT * FROM cols
  SELECT * from tabs
  SELECT * FROM all_views
  SELECT * FROM user_tables
  SELECT a.owner||'.'||a.table_name "Referenced Table",b.owner||'.'||b.table_name "Referenced by", b.constraint_name
  "Foreign Key" from all_constraints a, all_constraints b where
  b.constraint_type in('P','R') and a.constraint_name =
  b.r_constraint_name

Many database modelling tools can extract the required data and create an initial ERD diagram. You will likely need to rearrange the diagram, but the initial diagram will provide a good starting point.

Given the limitations, you may need to use queries that generate DDL and create a database based on this DDL.  I would avoid selecting from the all_* views as many of those objects will be system related, rather than application related.


12 hour format time in SQL datatype I can convert time datatype to 12 hour format using convert etc. But is there any option to save the time in 12 hour format in SQL tables using any settings or property or any other methods?

You should not be concerned on how SQL Server stores the data. For now, you should use the TIME data type and if you want the 12 hour format, then convert it in the front end application or during SELECT statement using CAST() or CONVERT()


pt-table-checksum to compare two tables? I'm trying to find a way of comparing two tables in two databases (master and slave), as I have applied some slightly different settings to one, and need to see what affect it has (if any) on the data etc.

Ordinarily I would use MySQL Utilities dbcompare which has worked well in the past, but this doesn't seem to have the option for a single table, and mysqldiff is too limited.

So I downloaded percona-toolkit to my Debian Server (the slave) and am looking at pt-table-checksum. But the Page on the Percona website is very little help, instead filled with all manner of options, but seemingly no examples.

I set up a test schema, with one table, and diffent data.

So far I have got as far as:

pt-table-checksum h=1.2.3.4,P=3306,u=xxxx,p=xxxx, -h1.2.3.5 --databases=test --tables=t1 --no-check-binlog-format

(apparently there is some problem with Row Based Replication [?] hence --no-check-binlog-format)

But this returned:

# A software update is available:
#   * The current version for Percona::Toolkit is 2.2.14.

TS             ERRORS  DIFFS     ROWS  CHUNKS SKIPPED   TIME  TABLE
08-17T09:54:32      0      1        4       1       0   0.291 test.t1

Not exactly what I had in mind. Am I missing something, or have I completely misunderstood what pt-table-checksum does?

I'm not sure why I didn't get the latest version either. I ran sudo apt-get update followed by sudo apt-get install percona-toolkit. (as you may have gathered, I'm not really a Linux person)

If you have a Windows workstation and have odbc driver to MySQL, then you might try my utility - there is a free edition. Maybe it will help you.
https://selectcompare.com


Create employee rows for each date in date range I have a list of employees.
I need to create output which is one row per date in a date range for each employee.  The date range will always be the last 15 days, so it is expected to have 15 rows for each employee.  A "few thousand" employees. This is SQL Server.

SAMPLE SOURCE EMP TABLE:
EMP1
EMP2

RESULT:
EMP1, JAN 1
EMP1, JAN 2
EMP1, JAN 3
EMP2, JAN 1
EMP2, JAN 2
EMP2, JAN 3

Here is an example of using recursion to build a common expression date table and then cross join to the employee table.

--demo setup
declare @EmpTable table (EmpName varchar(50))
insert into @EmpTable(EmpName) values('emp1'),('emp2')

--use recursion to build a common table expression date table
;WITH dates ([Date])
AS (
    SELECT convert(DATE, '2019-01-01') AS [Date] -- Put the start date here

    UNION ALL

    SELECT dateadd(day, 1, [Date])
    FROM dates
    WHERE [Date] < '2019-01-15' -- Put the end date here
    )
SELECT e.empname
    ,substring(DATENAME(month, ([date])), 1, 3) + ' ' + DATENAME(day, ([date])) AS [DateDescription]
FROM dates d
CROSS JOIN @EmpTable e
ORDER BY empname, [date]
OPTION (MAXRECURSION 32767) -- Don't forget to use the maxrecursion option!

| empname | DateDescription |
|---------|-----------------|
| emp1    | Jan 1           |
| emp1    | Jan 2           |
| emp1    | Jan 3           |
| emp1    | Jan 4           |
| emp1    | Jan 5           |
| emp1    | Jan 6           |
| emp1    | Jan 7           |
| emp1    | Jan 8           |
| emp1    | Jan 9           |
| emp1    | Jan 10          |
| emp1    | Jan 11          |
| emp1    | Jan 12          |
| emp1    | Jan 13          |
| emp1    | Jan 14          |
| emp1    | Jan 15          |
| emp2    | Jan 1           |
| emp2    | Jan 2           |
| emp2    | Jan 3           |
| emp2    | Jan 4           |
| emp2    | Jan 5           |
| emp2    | Jan 6           |
| emp2    | Jan 7           |
| emp2    | Jan 8           |
| emp2    | Jan 9           |
| emp2    | Jan 10          |
| emp2    | Jan 11          |
| emp2    | Jan 12          |
| emp2    | Jan 13          |
| emp2    | Jan 14          |
| emp2    | Jan 15          |


SELECT all rows when a variable is null How do you select all values when @Troop is NULL. And if there's a better way to do this all together then what would that be?

WHERE DateModified BETWEEN @BeginDate AND @EndDate
    AND
    d.CreatorLEA =
        CASE
        WHEN (@Troop IS NOT NULL) THEN @Troop
        ELSE -----SOMETHING HERE?------
        END

If I understand what you are asking then like this.

WHERE DateModified BETWEEN @BeginDate AND @EndDate
AND  (@Troop IS NULL OR d.CreatorLEA = @Troop)


Distinct counts from large table are too slow I have a table with 1.2 billion rows. The table has 60-70 columns, the vast majority of which are empty.

The table is indexed on subscription_id, among other indexes.

My query is:

SELECT count(distinct listing_id)
FROM apps
WHERE subscription_id = 1298;

This particular subscription_id is going to have roughly 30M rows to count. Unfortunately I am unable to do so as the query takes in excess of one hour just to  get that count.

I know there must be some way to improve it. Would it be improved if I created an index on subscription_id and listing_id together?

Would it be improved if I created an index on subscription_id and listing_id together?

Possibly yes, an index on (subscription_id, listing_id) (columns in this order!) might very well allow a cheaper index-only scan. You did not provide enough information to say more.

If it's all about this particular subscription_id, then a partial index would help a lot more:

CREATE INDEX ON apps (listing_id) WHERE subscription_id = 1298;

If there are more than a few duplicate listing_id per subscription_id, use
this adapted query with a recursive CTE:

WITH RECURSIVE rcte AS (
   (
   SELECT listing_id
   FROM   apps
   WHERE  subscription_id = 1298
   ORDER  BY listing_id
   LIMIT  1
   )

   UNION ALL
   SELECT a.listing_id
   FROM   rcte r
   JOIN   LATERAL (
      SELECT a.listing_id
      FROM   apps a
      WHERE  a.subscription_id = 1298
      AND    a.listing_id > r.listing_id
      ORDER  BY a.listing_id
      LIMIT  1
      ) a ON a.listing_id IS NOT NULL
   )
SELECT count(*) FROM rcte;

Requires index from above to be fast. For many duplicates it can be faster by orders of magnitude. The point is to work around a still missing (up to Postgres 12) index skip scan. See:

Fast way to count distinct column values (using an index?)
Optimize GROUP BY query to retrieve latest row per user

If, OTOH, there are no duplicate listing_id, then get rid of DISTINCT to make it a lot faster.


Get value increment during an hour of a day I query YouTube Data Api for a list of most popular videos on a channel and then get their statistics, 4 times per hour (each 15 minutes, by cron). The data is stored in Postgres, but dumping it and loading into another SQL DB wouldn't be a trouble.
Now I have following table of data:

 video_id| views_count | likes_count | timestamp
---------+-------------+-------------+---------------------
     foo | 100         | 1           | 2018-12-01 12:01:03
     foo | 101         | 1           | 2018-12-01 12:16:06
     foo | 105         | 1           | 2018-12-01 12:31:01
     bar | 199         | 0           | 2018-12-01 12:01:02
     bar | 200         | 0           | 2018-12-01 12:16:08
     bar | 301         | 5           | 2018-12-01 12:31:02
     ... | ...

UPD: Here's the schema (pasted to sqlfiddle):

CREATE TABLE video_statistics
(
  video_id TEXT not null,
  views_count INTEGER not null,
  likes_count INTEGER not null,
  timestamp TIMESTAMPTZ not null
);

How should I query that data in order to get increments by hour in view_counts and likes_count columns, grouped by video?
To clarify what I want to get:

hour_of_day|video_id|views_increment|likes_increment
-----------+--------+---------------+---------------
     ...   | ...
     11    | foo    | 4             | 0
     12    | foo    | 5             | 1
     ...   | ...
     11    | bar    | 73            | 0
     12    | bar    | 102           | 5
     ...   | ...

In other words, it's a "best time to post video" based on historical data, taking into account data during many weeks and months.
Should I rather dump the data into some timeseries DB or other, more appropriate for such cases DB, and query it there? Or should I just resort to calculating this in code?

One possibility is to first row_number() the records to get the first and last value per video, day and hour. Then join the two sets of first and last values to get the respective differences. Group the result on video and hour and get the sum or the average per video per day.

SELECT first.video_id,
       first.timestamp_hour,
       sum(last.views_count - first.views_count) views_count_diff_sum,
       sum(last.likes_count - first.likes_count) likes_count_diff_sum,
       avg(last.views_count - first.views_count) views_count_diff_avg,
       avg(last.likes_count - first.likes_count) likes_count_diff_avg
       FROM (SELECT video_id,
             timestamp_day,
             timestamp_hour,
             views_count,
             likes_count
             FROM (SELECT video_id,
                          timestamp::date timestamp_day,
                          date_part('hour', timestamp) timestamp_hour,
                          views_count,
                          likes_count,
                          row_number() OVER (PARTITION BY video_id,
                                                          timestamp::date,
                                                          date_part('hour', timestamp)
                                             ORDER BY timestamp ASC) rn
                          FROM elbat) first
             WHERE rn = 1) first
            INNER JOIN (SELECT video_id,
                               timestamp_day,
                               timestamp_hour,
                               views_count,
                               likes_count
                               FROM (SELECT video_id,
                                            timestamp::date timestamp_day,
                                            date_part('hour', timestamp) timestamp_hour,
                                            views_count,
                                            likes_count,
                                            row_number() OVER (PARTITION BY video_id,
                                                                            timestamp::date,
                                                                            date_part('hour', timestamp)
                                                               ORDER BY timestamp DESC) rn
                                            FROM elbat) last
                               WHERE rn = 1) last
                       ON last.video_id = first.video_id
                          AND last.timestamp_day = first.timestamp_day
                          AND last.timestamp_hour = first.timestamp_hour
       GROUP BY first.video_id,
                first.timestamp_hour;


How do you minimize chance of collision between system-named constraints on temp tables? We have occasionally hit the error “There is already an object named 'PK__#TempTab__0796211ACE71813B' in the database” when some of our procedures  attempt to create temp tables with a system-named PK constraint like so:

CREATE OR ALTER PROCEDURE dbo.StoredProc AS
BEGIN
    CREATE TABLE #TempTable (id INT NOT NULL, PRIMARY KEY CLUSTERED (id))
    ...
END

Querying tempdb.sys.objects reveals a couple of hundred constraints of the form PK__#TempTab__XYZ. Many were created hours ago and haven’t been modified since. There were very few active sessions when I looked at tempdb.sys.objects, so it is hard to believe there are that many temp tables currently in use. We do have many similarly named temp tables created in a lot of stored procedures.

I think temp table caching is responsible here. It appears that these PK constraints stick around in tempdb until their associated cached plans are removed. Testing has shown that causing temp table caching to be disabled (creating stats, adding named constraints, etc.) causes the PK constraint to get removed from tempdb.sys.objects when the temp table goes out of scope. Triggering recompiles and flushing the proc cache also clear out those PK constraints from tempdb.sys.objects.

I am aware that system-named constraints are not guaranteed to be unique as described in this post Can SQL Server create collisions in system generated constraint names?.

My questions are:

Am I wrong to assume that temp table caching increases the chance of a name conflict for system-named constraints on temp tables?
Could plan cache bloat be responsible for the large number of PK__#TempTab__XYZ constraints that I am seeing in tempdb.sys.objects, thus increasing the chance of two constraints having the same name? I'm trying to understand why we have seen this rare error this often.
If we can't ensure that system-named constraints on temp tables will never encounter naming collisions, what can we do to reduce the chance of these collisions in general?

The easiest solution is to use a unique clustered index which does not have to be unique in the system (unlike a primary key constraint). Otherwise, they mostly function the same - a unique index is still a constraint that prevents duplicates and can also be used as the target of foreign keys.

I forget if this specific inline index syntax was possible in 2016 (I only have 2019 locally), but this works fine on newer versions:

CREATE TABLE #fooA(id int, INDEX PK1 UNIQUE clustered(id));
CREATE TABLE #fooB(id int, INDEX PK1 UNIQUE clustered(id));

If that syntax was not yet valid on 2016, you could create a unique clustered index in a second step, which you'd have to name yourself (but you wouldn't have to find some convoluted way to make that name unique).

Also, instead of over-using generic names like #TempTable, use names more specific to the table contents / procedure functionality (like #Orders, #Customers, etc). In cases where you need an explicit PK, this would at least reduce the chance of collisions if all of your PKs will no longer be system-named PK__#TempTab_....

As an aside, I don't think I've ever seen so many temp tables in a system that there are collisions on these long, tried-to-be-unique names. Are you trying to overwhelm the metadata?


use cases for db.collection.validate() - checks the structures within a namespace for correctness by scanning the collection’s data and indexes I found on MongoDB mailing list one use case for db.collection.validate():

  The count() command is generally an estimate of the current document
  count, based upon statistics provided by the storage engine.

  After an unclean shutdown of a mongod using the WiredTiger storage
  engine, count statistics reported by count may be inaccurate. If this
  does occur you can run validate() on each collection to restore the
  correct statistics.

are there other use cases? Maybe regular DBA job task? We have regularly unclean shutdowns.

  The validate command checks the structures within a namespace for
  correctness by scanning the collection’s data and indexes. The command
  returns information regarding the on-disk representation of the
  collection.

  The validate command can be slow, particularly on larger data sets.

There is no prescribed need to run db.collection.validate(true) on a regular basis for a healthy MongoDB deployment. Validation with the true or {full:true} parameter can be resource intensive, as this iterates through the collection's data & index structures.

The validate(full) command is typically only used as a diagnostic aid in the event of suspected local data corruption or (for WiredTiger in particular) to true up collection counts after an unclean shutdown. As at MongoDB 3.4, validate() is a read-only command with the exception of validate(true) which will check & adjust collection counts if you are using the WiredTiger storage engine.

Validation generally only surfaces obvious problems in data structures, and cannot detect all possible forms of data corruption. Successful validation can be used as a sanity check if one of your replica set members is encountering data problems that result in an obvious assertion and you want to verify if other secondaries appear to be healthy. If full validation is unsuccessful on a replica set member, the general remedy is to resync the member rather than attempting to repair (which may result in a divergence of data from the other replica set members).

  We have regularly unclean shutdowns.

If you have regular unclean shutdowns and are using WiredTiger it would definitely be advisible to run validate to true up collection counts after a restart as these may be inaccurate. Ideally it would be preferable to investigate and resolve the issues or practices leading to your frequent unclean shutdowns.


ORA-21780: Maximum number of object durations exceeded I am getting this error when records are streamed over to another database

Oracle DB version:11.2.0.3.0

Transaction Flow:
DML on Schema1@DB1 Streamed to Schema2@DB2 then trigger on this table will insert into
Schema3@DB2 then trigger on this table will insert into table in Schema4@DB2

ORA-21780 error happening at the last stage.

Please advise.

Somehow you are trying to keep too many PL/SQL objects (I mean "TYPE AS OBJECT" objects). There is an internal limitation of around 64k of these per session. This is on the client side (not on server side), so this is a programming error, not related to database administration.

Not if that matters, but if you are simply curious why this message speaks about "durations" see this doc.


Can we protect data form Database Administrator by encrypting the data? How can we protect data on my database which is MySQL form database administrator stealing it?

Will encrypting it prevent it from being stolen? If yes, how as he already has access to the database he can de-crypt it?

I have an old post where I discussed an actual product I was evaluating : How to properly secure MySQL database?. The product Gazzang (now Cloudera) would basically encrypt the data and the framework requires using specific keys. Even if you copied the files, the keys not being present in the target system made everything inaccessible just from an OS standpoint.

If you do not trust a DBA with the key, you may have issue specific MySQL grants to SysAdmin/DBAs: SHUTDOWN, PROCESS, SHOW DATABASES, SUPER, REPLICATION CLIENT, and REPLICATION SLAVE. All other GRANTs would need to be given to Project Leaders and Lead Developers.

As you can tell from this answer, your question makes the leap from technology to office politics. We all wish there was some 100% automated HIPAA compilance framework around a database. Until then, you would need to scrupulously define what an operational DBA can and cannot do.


Alternative to updating every row in my table My syntax below works (may not be the most efficient syntax, but it almost serves the purpose).  The issue that I have with it, is that when the UPDATE statement is run, due to the JOIN condition each row that fits the criteria is updated.  I would like to only UPDATE 1 row, per empName.  Maybe I could use the field AcctIDWorked to update either the MAX() or the MIN() of this field?  Doesn't matter either way honestly as I just need ONE row per empName updated.

How can I accomplish this?

DDL Below and current syntax

IF OBJECT_ID('dbo.__SalesDetailInfo', 'U') IS NOT NULL
    DROP TABLE dbo.__SalesDetailInfo;

IF OBJECT_ID('dbo.__Sales', 'U') IS NOT NULL
    DROP TABLE dbo.__Sales;

Create Table __Sales
(
    EmpName varchar(max)
    ,TotalSaleAmount decimal(10,2)
)

Insert Into __Sales (EmpName, TotalSaleAmount) Values
('OWT', '167.30'),('Fit D', '512.22'),('Mike',  '4147.60')
,('Curtis', '66.96'),('Curtis', '1165.58'),('Curtis',   '1507.64')
,('Curtis', '1551.79'),('Curtis',   '8132.13'),('Curtis',   '28.62')
,('Sam',    '499.00'),('Curtis',    '1159.76'),('Sam',  '2011.98')
,('Mike',   '450.00'),('Mike',  '1453.60'),('Sam',  '762.45')
,('Curtis', '1056.00'),('Mike', '1596.50'),('OWT',  '1768.80')
,('Mike',   '1200.00'),('Mike', '1500.00'),('Fit D',    '17921.30')
,('Curtis', '456.73'),('Curtis',    '944.23'),('Curtis',    '2242.00')
,('Mike',   '437.50'),('Sam',   '3258.07'),('Sam',  '3258.07')
,('OWT',    '7263.57'),('OWT',  '7225.57'),('OWT',  '224.63')
,('Curtis', '569.89')

Create Table __SalesDetailInfo
(
    EmpName varchar(max)
    ,TotalSales decimal(10,2)
    ,AcctIDWorked int
)

Insert Into __SalesDetailInfo (EmpName, AcctIDWorked) Values
('Curtis', 1), ('Curtis', 2), ('Curtis', 3), ('Curtis', 4)
,('Fit D', 22), ('Fit D', 11), ('Fit D', 44)
,('Mike', 110), ('Mike', 111), ('Mike', 113), ('Mike', 114)
,('Sam', 300), ('Sam', 310), ('Sam', 340)
,('OWT', 500), ('OWT', 550), ('OWT', 580), ('OWT', 590)

;WITH UPDATECTE AS
(Select EmpName, sumTSA=SUM(TotalSaleAmount)
FROM __Sales
GROUP BY EmpName)
UPDATE __SalesDetailInfo
SET TotalSales = sumTSA
FROM UPDATECTE cte
  INNER JOIN __SalesDetailInfo t
    ON RTRIM(LTRIM(cte.EmpName)) = RTRIM(LTRIM(t.EmpName))

select t.*, s.sumTSA
from __SalesDetailInfo t
join ( Select EmpName, sumTSA = SUM(TotalSaleAmount)
       FROM __Sales
       GROUP BY EmpName
     ) as s
ON RTRIM(LTRIM(s.EmpName)) = RTRIM(LTRIM(t.EmpName))

You could use that to create a view.


Should I worry about failed login attempts on SQL Server I have a couple SQL Server Express Databases which are accessible through a number of asp.net/IIS websites plus Remote Desktop (me only). The web sites use integrated security and I have one user account that I use.  The problem is the logs show almost continuous attempts at failed logins.  These occur every 5-10 seconds or so, from one IP for about 5 minutes, then the IP changes.  The user name being attempted is usually 'sa' or some variation (I don't have an 'sa' user).  I have blocked several hundred of these IPs using Windows Firewall, but they just keep coming.  This results in huge log files and I guess a lot of bandwidth being used.  So my question: Do I have enough security in place that I don't have to really worry about this - or is there something more I should be

If possible, you should change your security approach to be denied by default, allowed by permission.

Block access from all unknown IP's.  Allow access from trusted IP's.


Move a database (from decomisioned server) to an existing server with heavy ongoing loads We have a situation where one of our database approx. 60 GB in size with transactions running every 2-3 mins 24*7 has to be moved, since the server it is on is going to be decommissioned.

Now, we cannot upgrade the above server to a different environment as its application is compatible with that particular env.

Now, we have 3 similar env, where we thought of migrating this database to. But the challenge is all these 3 exiting DB servers are heavily used. They also have process like transactional replication and log shipping set on them as well. Those servers are all on windows 2003 with SQL 2K5 running on them. I know this should have been upgraded , but for now we want to stick is application does not support the upgrade.

Please suggest from you're experience what can be done here? Should we think to migrate that DB on those heavy servers, using some analysis as moving may require additional storage and CPU or RAM. Or will it be better to cancel the decommission and let that DB reside there?

Edit: I am just looking for recommendations on how to calculate/make a process to meet those parameters say, X amount of RAM/CPU/Storage will be needed if I am going with option of moving the database on the heavy loaded servers

A couple of transactions running every 2-3 minutes and a 60GB database is pretty light IMHO and shouldn't add much load to an existing instance unless it's at the edge of it's capacity.

A couple of quick questions to take into account.  You are just moving the database not creating a new instance on the "heavily used" machines correct?  On the old instance is this the only database?

I would check RAM/CPU usage on each server.  Pick the lowest usage instance of the three and compare that to the usage on the old instance.  Remember that the CPU/RAM usage for an instance is more than just that for the database (even if it's the only user DB on the instance).  Also that SQL is pretty good at managing multiple databases/workloads.

In the end your best bet is going to be to use a development machine, make a copy of the database there and load test.  If everything goes well there then move to the lowest use old instance.

If you have the option to add CPU/RAM to the newer servers then that will be a nice safety net.  Move the DB and monitor. If it needs more memory add it, more CPU add that.


trim is not working for few string I want to trim dot (.) from a column value. Just say string is "Mr." and I need to trim the dot from the string. I am expecting "Mr"

Lets say,

I have two fields in my table

 id   |  value
 1       Mr.
 2       Ms.
 11      Mr.
 12      Ms.

But while running below query, not getting as i expected.

 select trim(value, '.') from mytable

 value
 (text)
 Mr
 Ms
 Mr.
 Ms.

And noticed, for id 1 & 2 values is coming as expected. but for id 11 & 12 still the dot is coming. I really could not understand what is the issue.

(Using Postgresql 9.1)

I looked at the trim function and it removes the character from the beginning or the end of the string.  I'd bet that the two where it's not working have spaces after the ".".  Try trimming spaces off the end first then trim the "."

select trim(trim(value,' '), '.') from mytable


Is there a way to hide mysql innodb cluster queries from general_log? When I start my innodb cluster I get many lines of cluster related logs in the mysql general_log file like below, how can I hide these from the log?
2020-06-26T14:40:43.719029Z     31774 Connect   mysql_router1_6dv772yz7qu1@dbsg1 on  using SSL/TLS
2020-06-26T14:40:43.719494Z     31774 Query     SET @@SESSION.autocommit=1, @@SESSION.character_set_client=utf8, @@SESSION.character_set_re
sults=utf8, @@SESSION.character_set_connection=utf8, @@SESSION.sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
2020-06-26T14:40:43.719659Z     31773 Quit
2020-06-26T14:40:43.720231Z     31774 Query     SET @@SESSION.group_replication_consistency='EVENTUAL'
2020-06-26T14:40:43.720509Z     31774 Query     START TRANSACTION
2020-06-26T14:40:43.720740Z     31774 Query     SELECT * FROM mysql_innodb_cluster_metadata.schema_version
2020-06-26T14:40:43.721185Z     31774 Query     select cluster_type from mysql_innodb_cluster_metadata.v2_this_instance
2020-06-26T14:40:43.723301Z     31774 Query     select I.mysql_server_uuid, I.endpoint, I.xendpoint from mysql_innodb_cluster_metadata.v2_instances I join mysql_innodb_cluster_metadata.v2_gr_clusters C on I.cluster_id = C.cluster_id where C.cluster_name = 'prodcluster' AND C.group_name = '8a70e310-a997-4d59-8e81-92dab9daa5be'
2020-06-26T14:40:43.723985Z     31774 Query     COMMIT
2020-06-26T14:40:43.724317Z     31774 Query     show status like 'group_replication_primary_member'
2020-06-26T14:40:43.725621Z     31774 Query     SELECT member_id, member_host, member_port, member_state, @@group_replication_single_primary_mode FROM performance_schema.replication_group_members WHERE channel_name = 'group_replication_applier'
2020-06-26T14:40:43.793743Z     31771 Query     START TRANSACTION
2020-06-26T14:40:43.933131Z     31770 Query     SELECT * FROM mysql_innodb_cluster_metadata.schema_version
2020-06-26T14:40:44.032471Z     31771 Query     SELECT * FROM mysql_innodb_cluster_metadata.schema_version
2020-06-26T14:40:44.171571Z     31770 Query     select cluster_type from mysql_innodb_cluster_metadata.v2_this_instance
2020-06-26T14:40:44.226921Z     31774 Quit

There is obviously a whole lot of administrative MySQL noise in the general_log.
You cannot really screen them out. So, what can do you ???
SUGGESTION: USE THE SLOW LOG !!!
The Slow Log ??? Yes, that's exactly what I said : USE THE SLOW LOG !!!
You are probably saying : "Why would I use the slow log when I need to general log ???"
Here is the problem you seem to be facing: you want to hide administrative SQL commands like:
select cluster_type from mysql_innodb_cluster_metadata.v2_this_instance

and
select I.mysql_server_uuid, I.endpoint, I.xendpoint from
mysql_innodb_cluster_metadata.v2_instances I join
mysql_innodb_cluster_metadata.v2_gr_clusters C
on I.cluster_id = C.cluster_id
where C.cluster_name = 'prodcluster'
AND C.group_name = '8a70e310-a997-4d59-8e81-92dab9daa5be'

You can make the slow log behave like the general log by setting this:
SET GLOBAL long_query_time = 0;

Then, everything that executes will land in the slow log.
So, how can you screen out the administrative SQL commands ??? Think about how long it takes for administrative SQL to execute. Try setting this:
SET GLOBAL long_query_time = 0.5;

Once you set this, everything that takes less than 0.5 seconds will not be recorded in the slow log. I am sure administrative SQL commands will not take 0.5 seconds or more to run.
Once you are satisfied that administrative SQL commands are not being recorded in the slow log, go add that setting in the my.cnf file:
[mysqld]
long_query_time = 0.5

or if you are using MySQL 8.0
mysql> SET PERSIST long_query_time = 0.5;

SUMMARY

Disable the general log
Enable the slow log
Run SET GLOBAL long_query_time = 0.5; in mysql
Add long_query_time = 0.5 under [mysqld] in /etc/my.cnf


Mongodb: Does not seem to respect capped size limit Running mongodb on win2012

Mongo db version v2.6.4

Our collection is created with this param:

capped:true, size:500000000000    // 500 gb

But the data dir, that only holds this one collection, has grown to 720gb.

There is no replication in use at all on this mongo server.

What can I do to keep the data dir at ~ 500 gb?

This db does get massive amounts of inserts at times, but we also need to control the max size (and let it discard old data).

Is it possible that the capped size does not include indexes?

Or is there other overhead I should account for? (and how?

Or?

db stats

> db.stats
function (scale){
    return this.runCommand( { dbstats : 1 , scale : scale } );
}
> db.stats()
{
        "db" : "logging",
        "collections" : 3,
        "objects" : 289637086,
        "avgObjSize" : 2076.3166847770317,
        "dataSize" : 601378314192,
        "storageSize" : 606012620432,
        "numExtents" : 305,
        "indexes" : 12,
        "indexSize" : 147827443456,
        "fileSize" : 776943435776,
        "nsSizeMB" : 16,
        "dataFileVersion" : {
                "major" : 4,
                "minor" : 5
        },
        "extentFreeList" : {
                "num" : 0,
                "totalSize" : 0
        },
        "ok" : 1
}
>

The command we use to create the collection:

db.createCollection("LogItem", { capped:true, size:500000000000 })

So the collection we write to is created with the capped cmd. and that is where all the space is

I can't do db.collection.stats() because out of disk space...

Thank you!

OK, we solved this.

Issue is that mongo auto creates the collection when incoming data comes in.

So the problem was:

Drop collection
// transactions are coming in
Create collection as capped

But #3 comes too late, as the collection is already present, as not capped, because of #2.

Solution:

stop mongo
bring up mango on non standard port // to prevent auto creation of collection
create collection as capped
stop mongo, bring it back up on regular port

Now I have a capped collection!


Sync User Passwords Across SQL Server AlwaysOn Availability Group I'm looking for a method to sync users across an AG when they initiate a password change on their own.

Historically (before setting up the AG), they could use the command line or, for app users, they had access to a web front-end that issued sp_password on their behalf; this was elegant because it was connecting as the user and the web server side didn't need elevated permissions set up on it.  With a user's default database now in an AG, the password change succeeds on the primary but fails on the secondary because the database is inaccessible.

I've tried changing the user's default database to master temporarily (fails to add it back while in an AG), permanently (some apps expect the default to be their own database), and modifying the CGI to use master as the database when connecting (works on primary, fails on secondary).

I can find docs on synchronizing users the first time (had no trouble with this) but having a hard time finding anything on how a user might change their own password in an AG when they are only allowed to connect to the primary.

Any ideas?  I'd still be grateful if it were glaringly obvious and embarrassing.

Edit: I meant to add: I could create a user with elevated permissions (probably 'alter any user') to change the user's password or to copy the password_hash across but I've been asked to try to limit that exposure of credentials on the web server side, if possible.

Thanks.

With a user's default database now in an AG, the password change succeeds on the primary but fails on the secondary because the database is inaccessible.

Passwords are stored in the master database, there is no need to connect to that database on the secondary. Unless the database is a contained database, server logins are also stored in the master database. Contained databases take care of themselves as contained users can be "logins" and are "contained" within the database, thus automatically included.

  I've tried changing the user's default database to master temporarily (fails to add it back while in an AG), permanently (some apps expect the default to be their own database), and modifying the CGI to use master as the database when connecting (works on primary, fails on secondary).

If you're using must_change or password policies you'll have an event raised or a message returned about this and can do it programmatically.

Ex: https://technet.microsoft.com/en-us/library/ms131024(v=sql.110).aspx

  Any ideas?

A few come to mind:

Buffer the password (hashed) from the initial password change request. Check to see if login is mapped to any databases that currently reside in any availability group. Get the distinct replica names for each availability group. Connect to each replica and issue the password update.
Run a job on all replicas and check for logins that have been updated recently. This information you can get from sys.server_principals (modify_date) and can use sp_help_revlogin to get the command needed up update the login. This could be run every so often (10 minutes, 15, etc.)
Use event notifications for the ALTER_LOGIN DDL event and have an activated stored procedure that did #2 (the one above) but on demand at the same time rather than on a schedule.

There are other combinations of this using server side traces and/or extended events but it all boils down to about the same process.


How do I figure out how Postgres was installed on my Linux system? I'm using Postges 9.6 on Ubuntu Linux 16.  I'm trying to figure out how the Postgres server was installed so I can figure out a command to start and stop it.  It evidently isn't installed as a Linux service

[rails@server ~]$ sudo service postgresql status
Redirecting to /bin/systemctl status postgresql.service
Unit postgresql.service could not be found.

"which psql" reveals

[rails@server ~]$ which psql
/usr/bin/psql

I'm sort of at a loss for the next steps in figure out the command to start and stop it.

If you are simply looking to start and stop it, and a service or systemctl module doesn't exist on your machine, you can use pg_ctl to start and stop the Postgres server.  You will also need to have the path to the data directory handy (typically, the folder that contains postgresql.conf and pg_hba.conf), and pass that in along with the -D flag:

pg_ctl -D /path/to/data_dir start|stop

If no data directory exists, you will either need further assistance to help you get an existing database cluster up and running, or you can use initdb to create a new database cluster


ssis cannot convert between unicode and non-unicode data types even though the source and the destination columns are both unicode I faced an error: 'SSIS cannot convert between unicode and non-unicode string data types' even though the source and the destination columns are both unicode (NVARCHAR) data type. The source db and the destination db are on the same server.

The source is a query:

SELECT
   CASE
        WHEN [Gender__c] = 'Female' THEN 'N'
        WHEN [Gender__c] = 'Male' THEN 'M'
        WHEN [Gender__c] = 'Nainen' THEN 'N'
        WHEN [Gender__c] = 'Mies' THEN 'M'
        When [Gender__c] = 'Ei tiedossa' THEN 'Ei tiedossa'
        ELSE NULL
    END as Gender__c
  FROM [database].[table]

Anybody seen the same?

You said that the input column is showing in the advanced editor as non Unicode even though the source data column is Unicode.

Based on your query, I don't think the source data column is Unicode - because the source data column isn't what you think it is.

Gender__C may be an NVARCHAR column - but it's not your source column. Your source column is based on the values returned by your CASE statement - that those are all CHAR/VARCHAR values.

To make the values returned by the CASE statement NCHAR/NVARCHAR, tell SQL that your text literals are to be treated as Unicode values, by putting an N before the opening single quote:

SELECT
   CASE
        WHEN [Gender__c] = N'Female' THEN N'N'
        WHEN [Gender__c] = N'Male' THEN N'M'
        WHEN [Gender__c] = N'Nainen' THEN N'N'
        WHEN [Gender__c] = N'Mies' THEN N'M'
        When [Gender__c] = N'Ei tiedossa' THEN N'Ei tiedossa'
        ELSE NULL
    END as Gender__c
  FROM [database].[table]

[Since Gender__c is an NVARCHAR, I went ahead and made your comparison values NVARCHAR as well].

Try this, and SSIS should recognize that Gender__c in the result set is a DT_WSTR.


Dates changed to 0000-00-00 Previously, on my table (~81000 rows), I have had date and time fields set as VARCHAR instead of date/time. All those dates were of the type MM/DD/YY. I changed the type from VARCHAR to DATE and TIME respectively and most of my dates, which had DD >= 13 to 0000-00-00.

Now, I am left with a backup I took 3 months ago (~77400 rows) and then the newer ones. Is there any way that I can restore the older dates?

The way I see it, I'll have to try updating the dates using the older .SQL file for those 77400 rows and after that will have to write a script to check and increment date with each passing 24 hour span in the TIME column.

I didn't have a backup taken after 12th April and didn't consider taking one while altering the database. :(

EDIT

  mysql  Ver 14.14 Distrib 5.1.61, for debian-linux-gnu (i486) using readline 6.1

CREATE TABLE `latest` (
  `msg` VARCHAR(280) NOT NULL,
  `id` VARCHAR(20) NOT NULL,
  `ctg` VARCHAR(10) NOT NULL,
  `date` VARCHAR(50) NOT NULL COMMENT 'date_format( %m/%d/%y )',
  `time` VARCHAR(50) NOT NULL COMMENT 'date_format( %H:%i:%s )',
  `fid` BIGINT(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`fid`)
) COLLATE='utf8_general_ci' ENGINE=InnoDB ROW_FORMAT=DEFAULT AUTO_INCREMENT=80685

I changed the table code from that to this:

CREATE TABLE `latest` (
  `msg` VARCHAR(280) NOT NULL,
  `id` VARCHAR(20) NOT NULL,
  `ctg` VARCHAR(10) NOT NULL,
  `date` DATE NOT NULL COMMENT 'date_format( %m/%d/%y )',
  `time` TIME NOT NULL COMMENT 'date_format( %H:%i:%s )',
  `fid` BIGINT(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`fid`)
) COLLATE='utf8_general_ci' ENGINE=InnoDB ROW_FORMAT=DEFAULT AUTO_INCREMENT=80685

Well, usually I use the following approach: instead of altering a live column type (if it's not something really trivial), I add another column of a new type, then set all the values in it (more on it below), then delete the old column after doing all that (and checking that all works ok), using its name as a name for a new column.

Perhaps this approach can still be used. To convert old values use something like this:

UPDATE my_table
   SET datetime_column = STR_TO_DATE(varchar_column, '%d-%m-%Y')

Please take note that actual format literal might be different in your case; consult the manual and test it for some exact value to be sure.