Query with HAVING clause is taking too long I have query like this:

SELECT  msg.id, CONTENTS, UNIX_TIMESTAMP(msg.created_at) 'created_at',
        category, msg.`uid`,name, user_name, profile_pic, STATUS,
        isNotificationOn, onesignal_id, email, gender, phone,
        users.id userId, UNIX_TIMESTAMP(users.createdAt) 'createdAt',
        ( SELECT  COUNT(1)
            FROM  message_likes
            WHERE  uid = '006GA968vpb0O62xaOpKLuhR1cC3'
              AND  message_id = msg.id
        ) AS isLiked,
        ( SELECT  COUNT(*)
            FROM  follower_list
            WHERE  follower_uid = '006GA968vpb0O62xaOpKLuhR1cC3'
              AND  following_uid = msg.uid) AS isFollowing,
        COUNT(DISTINCT share.uid) + msg.shares 'shares',
        COUNT(DISTINCT likes.uid) 'likes',
        msg.`application`,
        `isModified`, `modificationReason`, `isVoilation`, `voilationReason`,
        `followers_count`, `following_count`
    FROM  user_messages msg
    JOIN  users  ON msg.uid = users.uid
    LEFT JOIN  message_shares SHARE  ON msg.id = share.message_id
    left JOIN  message_likes likes   ON msg.id = likes.message_id
    LEFT JOIN  followers_count  ON msg.uid = followers_count.following_uid
    LEFT JOIN  following_count  ON msg.uid = following_count.follower_uid
    GROUP BY  msg.id
    HAVING  created_at > '0'
      AND  category LIKE '%%%'
      AND  msg.uid LIKE '%'
      AND  msg.application LIKE 'demo'
      AND  isLiked='1'
      AND  msg.id <= '999999'
      AND  isVoilation <= '1'
    ORDER BY  msg.id DESC
    LIMIT  10 OFFSET 0

This will take more than 8 seconds where my data is about 20k rows per table.
But if I replace isLiked='1' with isLiked='%' it will execute within 1 second (60 - 70 ms) which is 1000 times faster. So what the mistake I am doing?

UPDATE:
After moving conditions from having clause to where clause, i am getting responce in 2 seconds.
Here is updated query:

SELECT  msg.id, CONTENTS, UNIX_TIMESTAMP(msg.created_at) 'created_at',
        category, msg.`uid`,name, user_name, profile_pic, STATUS,
        isNotificationOn, onesignal_id, email, gender, phone,
        users.id userId, UNIX_TIMESTAMP(users.createdAt) 'createdAt',
        ( SELECT  COUNT(1)
            FROM  message_likes
            WHERE  uid = 'CqITnqfe5iZHT8x74zYxuEwzGMY2'
              AND  message_id = msg.id
        ) AS isLiked,
        ( SELECT  COUNT(*)
            FROM  follower_list
            WHERE  follower_uid = 'CqITnqfe5iZHT8x74zYxuEwzGMY2'
              AND  following_uid = msg.uid) AS isFollowing,
        COUNT(DISTINCT share.uid) + msg.shares 'shares',
        COUNT(DISTINCT likes.uid) 'likes',
        msg.`application`,
        `isModified`, `modificationReason`, `isVoilation`, `voilationReason`,
        `followers_count`, `following_count`
    FROM  user_messages msg
    JOIN  users  ON msg.uid = users.uid
    LEFT JOIN  message_shares SHARE  ON msg.id = share.message_id
    left JOIN  message_likes likes   ON msg.id = likes.message_id
    LEFT JOIN  followers_count  ON msg.uid = followers_count.following_uid
    LEFT JOIN  following_count  ON msg.uid = following_count.follower_uid
    WHERE category LIKE '%'
      AND  msg.uid like '%'
      AND  msg.application = 'shayari'
      AND  msg.id <= '999999'
      AND  isVoilation <= '1'
    GROUP BY  msg.id HAVING isLiked=1
     ORDER BY likes.created_at desc

Now the glitch is if i remove order by clause it will execute in few milliseconds and with order by clause it is taking about 2-3 seconds.
Here is explain query:

There may be more suggestions, but these should help performance some...

        WHERE  uid = '006GA968vpb0O62xaOpKLuhR1cC3'
          AND  message_id = msg.id

Presumably, you have PRIMARY KEY(uid, message_id) ?

        WHERE  follower_uid = '006GA968vpb0O62xaOpKLuhR1cC3'
          AND  following_uid = msg.uid) AS isFollowing,

Some kind of index with both follower_uid and following_uid?

WHERE is done while gathering rows; HAVING is done after all the rows are gathered.  That is, WHERE is more efficient.  However, HAVING is required when working with aggregates; but you don't have such.

Don't include unnecessary things like foo LIKE '%'.  Instead, build the query without it.

Discourage users from providing LIKEs with leading wildcards.

message_likes -- Don't COUNT something and then just check for 0 or 1.  Instead, use EXISTS ( SELECT 1 FROM .. WHERE .. ) -- and this can be put directly in the WHERE (unless you also need to fetch the value).

"number = string" -- Avoid mixing numeric and non-numeric.  In some situations, it requires all rows to be dynamically changed from a string to a number before checking.

("voilation" - check spelling)

How much data?  What is the setting of innodb_buffer_pool_size?  How much RAM?

created_at > '0' -- Huh?  When you upgrade, beware of "zero dates".  Consider changing to NULL instead of 0.


Why is it that when I drop a field in table it says `#1050 - Table already exist` I want to drop some fields of a table but it returns an error.
the query is..

ALTER TABLE `tbl_incoming_bub` DROP `approved_date`, DROP `approved_by`, DROP `incoming_date`, DROP `incoming_by`, DROP `obligate_date`, DROP `obligate_by`, DROP `journal_date`, DROP `journal_by`, DROP `ana_date`, DROP `ana_by`;

and the error is.. #1050 - Table 'ncddp/#sql-ib176' already exists

Here is the screenshots..

Check out this post by Aleksandr Kuzminsky who not only provides a solution, but also (and maybe more importantly) an explanation of what's going on. Kuzminsky is a former Percona employee (Percona is a big hitter in the MySQL ecosphere) and a contributor to open source MySQL projects on Launchpad.

Basically, what happens is this

When you run an alter table, MySQL creates a temporary table with the new structure. The name of the new table is (something like) #sql-ib712.

It appears that MySQL doesn't choose very random names for these temporary tables. The problem arises when there's a crash in the middle of the ALTER TABLE process and the dictionary ends up with an orphaned table. Which wouldn't be a problem, except that MySQL (due to the non-random nature of temp table naming) has a good chance of giving the same name to a subsequent temporary table.

Obviously, Kuzminsky goes into a bit more detail, and provides a script to help fix your database.


Can anyone please tell me if these are correct statements about measure group partitioning in ssas? A partition is based either on a table or view in a data source, or on a table or named query in a data source view. The location where partition data is stored is defined by the data source binding. Typically, you can partition a measure group horizontally or vertically:

In a horizontally partitioned measure group, each partition in a measure group is based on a separate table. This kind of partitioning is appropriate when data is separated into multiple tables. For example, some relational databases have a separate table for each month's data.
In a vertically partitioned measure group, a measure group is based on a single table, and each partition is based on a source system query that filters the data for the partition. For example, if a single table contains several months data, the measure group could still be partitioned by month by applying a Transact-SQL WHERE clause that returns a separate month's data for each partition.

It seems like it should be the opposite.

I believe you are correct to object to this description.  The partitioning of a measure group is always a "horizontal partitioning" as the term is commonly used.  I submitted a Pull Request to change the docs to describe the options as a "multi-table partition scheme" or a "single-table partition scheme".


Unable to get rid of orphan temporary tables After migrating from MariaDB 5.5 to 10.0, I noticed an error message mentioning a table named piwik/#sql-1526_3a.

From there I started googling and landed on the innodb troubleshooting docs

Indeed, SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%'; gives the following result:

MariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';
+----------+--------+----------------+------+--------+-------+
| TABLE_ID | SCHEMA | NAME           | FLAG | N_COLS | SPACE |
+----------+--------+----------------+------+--------+-------+
|      323 | piwik  | #sql-1526_3a   |    1 |     11 |   320 |
|      265 | piwik  | #sql-5305_92b0 |    1 |     24 |   262 |
|      321 | piwik  | #sql-7920_48   |    1 |     24 |   318 |
+----------+--------+----------------+------+--------+-------+

I tried to follow MySQL troubleshooting instructions and did:

MariaDB [(none)]> use piwik
Database changed
MariaDB [piwik]> drop table `#mysql50##sql-1526_3a`;
ERROR 1051 (42S02): Unknown table '#mysql50##sql-1526_3a'

From there, I read more blog posts and stack exchange answers and noticed that there are no #sql-1526_3a.idb and #sql-1526_3a.frm files in my /var/lib/mysql/piwik/ folder.

I then tried the following:

$ cd /var/lib/mysql/piwik
$ cp piwik_user.ibd #sql-1526_3a.ibd
$ cp piwik_user.frm #sql-1526_3a.frm

and restarted MariaDB.

Then I tried droping the table again:

MariaDB [piwik]> drop table `#mysql50##sql-1526_3a`;
Database changed
Query OK, 0 rows affected (0.00sec)

Horray?!! Well, SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%'; still lists the #sql-1526_3a table. On disk, the .frm file got deleted but the .ibd file remains.

So? Does someone know how I can remove these orphan tables for real?

EDIT

It seems to me these trailing temporary tables are "referenced" from /var/lib/mysql/ibdata1 before I shrunk it and enabled innodb_file_per_table = 1.

This may work -- I found it interesting in any event:  How to remove Temp Tables the proper way


Reference implementation of a scalable storage system with strong consistency? Is there a storage system with these properties?

horizontally scalable - can handle arbitrary read and write throughput if you add enough nodes to the cluster
consistent, in the sense that all reads will reflect the latest successful writes
[edit] "safe" in the sense that if a write is reported successful, it is guaranteed that all subsequent successful reads will reflect that write
tolerant of individual node failures

In CAP terms, I'm interested in systems which guarantee consistency rather than availability in the event of network partitions. Or systems which at least can be configured to behave this way.

I'm not concerned with latency, as long as writes are guaranteed to terminate eventually.

I'm not asking which tool I "should use". I'm asking, out of academic interest, for a proof of concept of a CP storage system. It doesn't matter if it's modern, actively maintained, widely used or what not; I'm just seeking a reference implementation.

The image in this article is used quite a bit around the internet in various different forms but it highlights which systems fall in each category of CAP

So your are basically asking which horizontally scalable systems don't rely on eventual consistency. To which MongoDB, HBase and many more would be a good answer.


MongoDB: What does '[DataFileSync] flushing mmaps took ... ms for ... files' mean? I am building an index in MongoDB for a large collections (~300GB) and the db terminal window keeps printing:

[DataFileSync] flushing mmaps took 10920ms for 151 files

The time that it takes to flush mmaps is usually around 10 seconds and sometimes higher. I'm running MongoDB on a high performance machine so RAM availability is not an issue.

What does the data file sync message mean?

Is it an issue??

EDIT:

The real problem with the flushing routine is that MongoDB seems to freeze while doing that.

Even simple queries from a terminal window on the same host have to wait for the flushing to finish in order to complete and ~10 seconds (up to 150s) is way too long. The routine happens (at irregular intervals of 1, 2, or 3 mins) even if no operations are run on the server and there are zero connections active.

I'm running MongoDB 2.2.1 2008R2+ on Windows Server 2008 R2.

MongoDB flushes the data changes in memory to disk using a background thread every 60 seconds (it's tunable, see syncdelay).  During normal operations, that is going to be inserts, updates, deletes and the like (including changes to indexes) and will roughly correlate to your activity in terms of those operations.  The mmaps piece relates to the fact that MongoDB uses memory mapped files

When you build an index, you are going to be creating a lot of new in-memory data and that is all going to be persisted to disk every 60 seconds.  How quick that flush is will be a function of:

How much data has to be flushed to disk (in your case how much index building has gone on)
How fast your disk can write that data out

Given that your example shows that it is taking 10 seconds to flush, that would imply that the disk is struggling a bit - single digit seconds is high, double digits is worrying.  Of course it does depend on the nature of your disk - if an SSD were taking 10 seconds (for example) that would need to be a huge amount of data.

That explains the messaging, but besides all that index builds are intensive (and blocking) operations - you can run them in the background on a primary to avoid some of the impact, but they will run in the foreground on secondaries until SERVER-2771 is fixed.  As a result the recommended way to build indexes with a replica set is outlined here:

http://docs.mongodb.org/manual/administration/indexes/#index-building-replica-sets

The impact will depend on how long the build takes, what you are doing at the time etc. but if you follow the instructions above you should be able to avoid any of those effects impacting your end users.


Azure SQL - dataspace used vs max dataspace I have a single Azure SQL database in Azure with the following database data space

As I read the documentation: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-file-space-management the "Used space" will keep growing, and I myself need to shrink it. That is perfectly fine, and as expected. I can also see that the allocated space is a bit larger than the used space. This is how i hope it works:

when used space is nearing the allocated space, some more space is allocated
when used space is at max size we have no more space available thus we run out of space

I am a bit scared that I have misunderstood something and my database (and site) will stop working when used space is at allocated space. I am kind of new into the world of Azure.

It was something i just noticed today. I have a monitor set up to monitor data space used (in percent), and i will be alerted when it hit max. But i will never hit max if it doesn't grow beyond allocated space.

First of all, no need to worry as this is normal behaviour.

The link in your answer From msdocs on Data space allocated gives us the info needed:

  The amount of space allocated grows automatically, but never decreases
  after deletes. This behavior ensures that future inserts are faster
  since space does not need to be reformatted.

This space allocated grows automatically and will stay the same way when it grows out.

If the database keeps on growing, eventually the allocated space will be the same size as the maximum storage size. This is comparable to the autogrowth behaviour of regular sql server databases.

A useful part of the azure portal is that there are these little (i) marks on many settings.

These should point you in the right direction, the learn more points us to above linked page.

In short

  when used space is nearing the allocated space, some more space is
  allocated

yes (as long as you are not at the database limit)

  when used space is at max size we have no more space available thus we
  run out of space

and yes

Another way to get the size is checking the space available in the properties of the azure sql db with ssms.

This could mean that even sql server itself does not know that a mechanism like allocated space is used.

Proof

growing out the previously mentioned database a bit:

increases the used space & allocated space:

Afterwards I deleted the data & shrinked the database (Which you shouldn't ;)), also not with dbcc shrinkdatabase, double foul there.

dbcc shrinkdatabase([databasename])

Results in the same allocated space, but less used space:


SSIS 2008 - Getting Execute SQL Task to update data with parameters I feel this is s simple task but I'm tripping over how to do it.

What I have is a very simple Data Flow Task that calls a stored procedure.

SELECT [Id]
      ,[Error]
  FROM [CEGSalesforce].[dbo].[OpportunityLineItem_OrderTracking]

I want to update data in another location based on the Id above. I have a "Execute SQL Task" flow that calls another stored procedure with this code in it

UPDATE [Integration].[Staging].[OrderTracking]
   SET TriggerIntegration = CASE
                              WHEN @Error = 'Operation Successful.'
                              THEN 0
                              ELSE 1
                            END
     , Error = @Error
 WHERE SalesforceOpportunityLineItemId = @Id

Here is how my variables are setup

Here is how my SQL Task is setup. I've tried Single row, full result set, anything to get it to run my update as I expect it

And my parameter mapping

And finally, my Result Set

Not sure what I'm doing wrong. Hoping I can get some direction

In the ever confusing world of SSIS supporting multiple systems, you have chosen an OLE DB Connection manager (generally a good to the only choice). The downside to using an OLE DB Connection manager with parameters, either in an Execute SQL Task or an OLE DB Source, is that parameters are ordinal based.

Execute SQL Task

In your example SQL, there are 3 parameters

UPDATE [Integration].[Staging].[OrderTracking]
   SET TriggerIntegration = CASE
                              -- Zero-eth ordinal
                              WHEN @Error = 'Operation Successful.'
                              THEN 0
                              ELSE 1
                            END
               -- First ordinal
     , Error = @Error
 WHERE SalesforceOpportunityLineItemId = @Id -- second ordinal

In your Parameter Mapping image, you need to list the User::Error twice. The Parameter Name will be 0 and 1 while User::Id has a parameter name of 2

User:Error Input NVARCHAR 0 50
User:Error Input NVARCHAR 1 50
User:Id    Input NVARCHAR 2 50

You have only provided two parameters where three are needed.

Change your result set type to None as the invocation of the above SQL does not generate a result set (or a single row of output)

Data Types

In your original parameter mapping, you indicate that the SSIS Variable User::Id should be Numeric when it lands in your OLE DB Connection but within SSIS you have it defined as a string. Unless you have a reason for data type conversions, you might be better served by keeping your types consistent. Also, is it really numeric and not int/bigint or int32/int64 in SSIS parlance.

Why doesn't the query work as is?

The query is running but no results are found WHERE SalesforceOpportunityLineItemId = @Id is testing where SalesforceOpportunityLineItemId is equal to NULL (since no value is supplied for the parameter @Id). And as we have all painfully discovered at some point in our careers, NULL will never be equal to anything.

Other thoughts

If I have misread all of that and you are trying to generate a result set from  [CEGSalesforce].[dbo].[OpportunityLineItem_OrderTracking] and use the Error/Id combo, then you are likely looking for a different pattern. Google "Shred Recordset SSIS" but the basics will be

Execute SQL Task  (use select query, with full result set to a new Variable of type Object, call it rsUpdates)

Foreach Recordset Enumerator (use an ADO.net Recordset as the source with rsUpdates and assign values to @User::Error and @User::Id)

Execute SQL Task (use the update procedure with two variables)

Note, no data flow is required for this approach

Edit #1

A question via comment "Now that I know I need three parameters, I need to have 3 "?" behind my stored proc, correct?" I can say.. maybe

If the procedure is defined as

CREATE PROCEDURE dbo.uspU_UpdateIntegrationFlag
(
    @error varchar(50)
,   @Id int
)
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE [Integration].[Staging].[OrderTracking]
    SET TriggerIntegration = CASE
                              -- Zero-eth ordinal
                              WHEN @Error = 'Operation Successful.'
                              THEN 0
                              ELSE 1
                            END
               -- First ordinal
     , Error = @Error
    WHERE SalesforceOpportunityLineItemId = @Id -- second ordinal
END

then ordinal based explanation above will apply to the stored procedure's parameters of @error and @id which are denoted correctly with the ?, ? and names of (0,1)


You don't need any auxiliary tools. This works on any platform.
COPY to a simple temporary ancillary table:

CREATE TEMP TABLE tmp(txt text);

COPY tmp FROM '/path/to/your_file.csv' (FORMAT csv, ENCODING 'utf8', DELIMITER E'\b');

I chose backspace as delimiter (E'\b') which never occurs. This way you get one text column.
Assuming UTF8 encoding. Adapt if necessary.
If you can't use SQL COPY, consider the psql meta command \copy instead:

Postgres client copy (\copy) command doesn't have access to a temporary table?

Create the target table by parsing the row in the next step:

CREATE TABLE target AS
SELECT split_part(txt, ',', 1) AS patient_id
     , split_part(txt, ',', 2) AS diagnosis
     , string_to_array(substring(txt, '^(?:[^,]*,){2}(.*)'), ',')::real[] AS arr
FROM   tmp;

Simple and fast.

The temporary table dies at the end of the session automatically.
The first line of integer values can be stored in real[], too.
About split_part():

Split comma separated column data into additional columns

The regular expression in substring(): '^(?:[^,]*,){2}(.*)' - it basically says: omit the first two fields and take the rest.
This assumes no white space between values else you might want to use trim(). Related answer with more explanation and links for regular expressions:

Filter strings with regex before casting to numeric

Since an array does not seem useful for your purpose, you might instead just keep the comma-separated list:

     substring(txt, '^(?:[^,]*,){2}(.*)') AS list


What privileges does the SQL Azure Migration Wizard need to copy a SQL Azure database? Currently have 2 users in our SQL Azure subscription. One is the god login created when setting up the server, other is very limited account for our www app (has db_datareader and db_datawriter).

The www user does not have enough privileges to use the SQL Azure Migration Wizard. Connecting to server shows the db as grayed out / disabled.

I want to create a 3rd user with just enough privileges to use the mw for copy / backup. What roles / privileges does this user need? Does anyone have some prepared sql scripts?

From
Copying Databases in SQL Azure

Permissions

In order to copy a database in SQL Azure, your login requires the following permissions:

On both servers: The login must have the same login name and password on both SQL Azure servers.

On the destination server: The login must be a member of the server-level dbmanager role. Note: the server-level principal of your SQL Azure server is not a member of the dbmanager role, but automatically has the same permissions. For more information about managing logins in SQL Azure, see Managing Databases and Logins in SQL Azure.

On the source server: The login must be the DBO of the source database. Only the login that created the source database, the DBO, can copy that database to another database on the same server or on a different server.


how to mongorestore mongoDB 4.2 to documentDB 4.0? I need to do mongorestore from my mongodb 4.2 to DocumenDb 4.0 compatible i got the command from aws
which is this
mongorestore --ssl --host="abc.aws.com:27017" --username=user --password=pass --sslCAFile rds-combined-ca-bundle.pem dump

but it is showing me this error
[1] 1120
-bash: dGFp: command not found
[root@mongo-tf-doc01 dump]# 2021-04-06T12:10:48.434+0000        error connecting to host: could not connect to server: server selection error: server selection timeout
current topology: Type: Single
Servers:
Addr: abc.aws.com:27017:27017, Type: Unknown, State: Connected, Average RTT: 0, Last error: connection(abc.aws.com:27017[-121]) connection is closed

I am able to connect documentdb successfully so i know this is not a connection problem but error occurs when i restore

This happened because AWS DocumentDB is only mongo 4.0 compatible and I was trying to restore from mongo 4.2, to solve this I just used AWS DMS as it was free, process is simple and there are many tutorials available from AWS and as well as other people on youtube


Why doesn't join elimination work with sys.query_store_plan? The following is a simplification of a performance problem encountered with the Query Store:

CREATE TABLE #tears
(
    plan_id bigint NOT NULL
);

INSERT #tears (plan_id)
VALUES (1);

SELECT
    T.plan_id
FROM #tears AS T
LEFT JOIN sys.query_store_plan AS QSP
    ON QSP.plan_id = T.plan_id;

The plan_id column is documented as being the primary key of sys.query_store_plan, but the execution plan does not use join elimination as would be expected:

No attributes are being projected from the DMV.
The DMV primary key plan_id cannot duplicate rows from the temporary table
A LEFT JOIN is used, so no rows from T can be eliminated.

Execution plan

Why is this, and what can be done to obtain join elimination here?

The documentation is a little misleading. The DMV is a non-materialized view, and does not have a primary key as such. The underlying definitions are a little complex but a simplified definition of sys.query_store_plan is:

CREATE VIEW sys.query_store_plan AS
SELECT
    PPM.plan_id
    -- various other attributes
FROM sys.plan_persist_plan_merged AS PPM
LEFT JOIN sys.syspalvalues AS P
    ON P.class = 'PFT'
    AND P.[value] = plan_forcing_type;

Further, sys.plan_persist_plan_merged is also a view, though one needs to connect via the Dedicated Administrator Connection to see its definition. Again, simplified:

CREATE VIEW sys.plan_persist_plan_merged AS
SELECT
    P.plan_id as plan_id,
    -- various other attributes
FROM sys.plan_persist_plan P
    -- NOTE - in order to prevent potential deadlock
    -- between QDS_STATEMENT_STABILITY LOCK and index locks
    WITH (NOLOCK)
LEFT JOIN sys.plan_persist_plan_in_memory PM
    ON P.plan_id = PM.plan_id;

The indexes on sys.plan_persist_plan are:

╔════════════════════════╦══════════════════════════════════════╦═════════════╗
║       index_name       ║          index_description           ║ index_keys  ║
╠════════════════════════╬══════════════════════════════════════╬═════════════╣
║ plan_persist_plan_cidx ║ clustered, unique located on PRIMARY ║ plan_id     ║
║ plan_persist_plan_idx1 ║ nonclustered located on PRIMARY      ║ query_id(-) ║
╚════════════════════════╩══════════════════════════════════════╩═════════════╝

So plan_id is constrained to be unique on sys.plan_persist_plan.

Now, sys.plan_persist_plan_in_memory is a streaming table-valued function, presenting a tabular view of data only held in internal memory structures. As such, it does not have any unique constraints.

At heart, the query being executed is therefore equivalent to:

DECLARE @t1 table (plan_id integer NOT NULL);
DECLARE @t2 table (plan_id integer NOT NULL UNIQUE CLUSTERED);
DECLARE @t3 table (plan_id integer NULL);

SELECT
    T1.plan_id
FROM @t1 AS T1
LEFT JOIN
(
    SELECT
        T2.plan_id
    FROM @t2 AS T2
    LEFT JOIN @t3 AS T3
        ON T3.plan_id = T2.plan_id
) AS Q1
    ON Q1.plan_id = T1.plan_id;

...which does not produce join elimination:

Getting right to the core of the issue, the problem is the inner query:

DECLARE @t2 table (plan_id integer NOT NULL UNIQUE CLUSTERED);
DECLARE @t3 table (plan_id integer NULL);

SELECT
    T2.plan_id
FROM @t2 AS T2
LEFT JOIN @t3 AS T3
    ON T3.plan_id = T2.plan_id;

...clearly the left join might result in rows from @t2 being duplicated because @t3 has no uniqueness constraint on plan_id. Therefore, the join cannot be eliminated:

To workaround this, we can explicitly tell the optimizer that we do not require any duplicate plan_id values:

DECLARE @t2 table (plan_id integer NOT NULL UNIQUE CLUSTERED);
DECLARE @t3 table (plan_id integer NULL);

SELECT DISTINCT
    T2.plan_id
FROM @t2 AS T2
LEFT JOIN @t3 AS T3
    ON T3.plan_id = T2.plan_id;

The outer join to @t3 can now be eliminated:

Applying that to the real query:

SELECT DISTINCT
    T.plan_id
FROM #tears AS T
LEFT JOIN sys.query_store_plan AS QSP
    ON QSP.plan_id = T.plan_id;

Equally, we could add GROUP BY T.plan_id instead of the DISTINCT. Anyway, the optimizer can now correctly reason about the plan_id attribute all the way down through the nested views, and eliminate both outer joins as desired:

Note that making plan_id unique in the temporary table would not be sufficient to obtain join elimination, since it would not preclude incorrect results. We must explicitly reject duplicate plan_id values from the final result to allow the optimizer to work its magic here.


How to access mongodb replica set after restart Yesterday I created a replica set and everything seemed to work. I closed all the cmd's when i was done.

I am assuming this replica set has not been deleted or anything, since old dbs i've created still are available. How can i access the replica set?

My replica set consists of three nodes on ports 27017, 27018 and 27019.
I have started

one mongod (the default one, for 27017)
mongo --port 27017
rs.status() which returns

"ok" : 0,
"errmsg" : "not running with --replSet",
"code" : 76,
"codeName" : "NoReplicationEnabled"

The node is most likely a secondary node, but it shouldn't matter. Even if I were to start the other two mongod's, i am feeling i'm missing something here.

I know this is probably a very stupid question. I am a beginner to mongo so please bear with me.

Check to see what processes you have running:

ps aux | grep mongod

You should see something like that:

mongod --port 27017 --replSet replica --dbpath s1 --logpath s1/monogd.log --fork
mongod --port 27018 --replSet replica --dbpath s2 --logpath s2/monogd.log --fork
mongod --port 27019 --replSet replica --dbpath s3 --logpath s3/monogd.log --fork

You will be able to connect using:

mongo "mongodb://localhost:27017, localhost:27018, localhost:27019/?replicaSet=replica"


SQL SERVER Convert BLOB data to STRING I am trying to extract blob data from a SQL SERVER 2008 R2 database. The data is annotation data that an application stores. I have done some research and I have been partially successful in converting the data. I get about 20-30 first characters of each entry.
This is the SQL statement I used:

select CONVERT(varchar(MAX), CONVERT(varbinary, blob)) from annotation_data;

How can I get all the data?

The value truncates because you have not specified a length for the convert to varbinary. Without a specific length you get the default which is 30.

select convert(varchar(max), convert(varbinary, '1234567890123456789012345678901234567890'));
select convert(varchar(max), convert(varbinary(max), '1234567890123456789012345678901234567890'));

Result:

123456789012345678901234567890
1234567890123456789012345678901234567890


Why does a Columnstore index build often wait on SLEEP_TASK? I'm building a Columnstore index right now. The output from sp_whoisactive:

This is a dev machine. Nothing else is running. The server has 8GB of memory available to it. The CPU is nearly idle. The build seems to be in the global dictionary sampling phase because sqlservr.exe is doing random disk IO and writing almost nothing.

Does this SLEEP_TASK indicate that I can take action to make the index build go faster? Why is the build "sleeping" sometimes?

This is SQL Server 2014 SP1.

SLEEP_TASK Idle Generic Sleep.
SLEEP_TASK is a general wait, used for all sorts of things where a more specific wait type doesn't exist, but it does crop up with Hashing operations (join/aggregate) that spill to tempdb, and occasionally at an exchange (Parallelism operator) where a consumer is waiting on something that isn't a normal CXPACKET or EXECSYNC wait. I would check for Hash spills first
SLEEP_TASK Wait Type in SQL Server - What does it indicate?


Query without precision brings different result I have pasted a formula from Excel and pasted onto SQL Server Management Studio.

Once I execute it, I get an unexpected result:

SELECT (((10320238-2092189)/2092189)*100.0)/4.0 --returns 75.000000

But after adding precision to each value within query, it returns the correct result:

SELECT (((10320238.0-2092189.0)/2092189.0)*100.0)/4.0 --returns 98.31866289250000

Why the first query won't bring 98% as result?
What is the inner data type of 10320238? Is it integer?

The engine is

Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64)
    Oct 20 2015 15:36:27
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

It's called "integer math" - when you divide two integers, any remainder is discarded. Compare:

SELECT integers = (10320238-2092189)/2092189,
       decimals = (10320238.0-2092189.0)/2092189.0;

Results:

integers    decimals
--------    ------------
3           3.9327465157

From the division topic in Books Online:

  If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.


Performance issue with larger resultsets SQL Server I currently have a stored procedure in SQL Server where I execute a SELECT statement multiple times based on the variables I pass to the stored procedure. The stored procedure counts how many records are going to be returned for every filter a user can enable. The stored procedure isn't the issue, I transformed the select statement from te stored procedure to a regular select statement which looks like:

DECLARE @contentRootId int = 900589
DECLARE @RealtorIdList varchar(2000) = ';880;884;1000;881;885;'
DECLARE @publishSoldOrRentedSinceDate int = 8
DECLARE @isForSale BIT= 1
DECLARE @isForRent BIT= 0
DECLARE @isResidential BIT= 1
--...(another 55 variables)...

--Table to be returned
DECLARE @resultTable TABLE
(
    variableName varchar(100),
    [value] varchar(200)
)

-- Create table based of inputvariable. Example: turns ';18;118;' to a table containing two ints 18 AND 118
DECLARE @RealtorIdTable table(RealtorId int)
INSERT INTO @RealtorIdTable SELECT * FROM dbo.Split(@RealtorIdList,';') option (maxrecursion 150)

INSERT INTO @resultTable ([value], variableName)
SELECT [Value], VariableName FROM(
    Select count(*) as TotalCount,
        ISNULL(SUM(CASE WHEN reps.ForRecreation = 1 THEN 1 else 0 end), 0) as ForRecreation,
        ISNULL(SUM(CASE WHEN reps.IsQualifiedForSeniors = 1 THEN 1 else 0 end), 0) as IsQualifiedForSeniors,
        --...(A whole bunch more SUM(CASE)...
    FROM TABLE1 reps
    LEFT JOIN temp t on
            t.ContentRootID = @contentRootId
            AND t.RealEstatePropertyID = reps.ID
        WHERE
            (EXISTS(select 1 from @RealtorIdTable where RealtorId = reps.RealtorID))
            AND (@SelectedGroupIds IS NULL OR EXISTS(select 1 from @SelectedGroupIdtable where GroupId = t.RealEstatePropertyGroupID))
            AND (ISNULL(reps.IsForSale,0) = ISNULL(@isForSale,0))
            AND (ISNULL(reps.IsForRent, 0) = ISNULL(@isForRent,0))
            AND (ISNULL(reps.IsResidential, 0) = ISNULL(@isResidential,0))
            AND (ISNULL(reps.IsCommercial, 0) = ISNULL(@isCommercial,0))
            AND (ISNULL(reps.IsInvestment, 0) = ISNULL(@isInvestment,0))
            AND (ISNULL(reps.IsAgricultural, 0) = ISNULL(@isAgricultural,0))
            --...(Around 50 more of these WHERE-statements)...
            ) as tbl

    UNPIVOT (
        [Value]
        FOR [VariableName] IN(
        [TotalCount],
        [ForRecreation],
        [IsQualifiedForSeniors],
        --...(All the other things i selected in above query)...
        )
    ) as d

    select * from @resultTable

The combination of a RealtorId and ContentRootID gives me a set default set of X amount of records. When I choose a combination which gives me ~4600 records, the execution time is around 250ms. When I execute the sattement with a combination that gives me ~600 record, the execution time is about 20ms.

I would like to know why this is happening. I tried removing all SUM(CASE in the select, I tried removing almost everything from the WHERE clause, and I tried removing the JOIN. But I keep seeing the huge difference between the resultset of 4600 and 600.

EDIT: I also tried to use a temporary table for @resultTable instead of the table variable.

EDIT2: The execution plan of the one with many results: https://gist.githubusercontent.com/anonymous/8c91631b2ecf6e42d787583da20b2a7e/raw/f8097765814486b35e8e8ba90ea0a0611703690c/gistfile1.txt and with few results: https://gist.githubusercontent.com/anonymous/734cc06417204b1c2bf24d6eff075c67/raw/87ab011776d71659e27cdd58fc25c3414d6c5309/gistfile1.txt I don't think the plans differ too much

Looking at the execution plan XML, there are a few things sticking out that may be the cause of your issue.

You are selecting everything back to the client. There are inherent wait times with transferring data over a network. The larger query is transferring almost 8x more data.
The larger query has a spill to TempDB. This means that parts of the query are using disk instead of memory. This will clearly slow it down much more than the smaller query.
You are running the under the compatibility level for SQL Server 7.0, so the fact that your arithabort setting is different for each execution can be causing issues. See here - http://www.sommarskog.se/query-plan-mysteries.html.

Regarding #3, you are either not running these the same way, or you are changing session level settings in between runs. You need to run these exactly the same way to get execution plans that can be compared accurately.


Sql Server Transaction logs during large deletes At my work, I am going to be moving millions of records to a different server, and removing them from the source server. My DBA has set up a backup scenario where change backups are taken every 5 minutes. He had this warning for me regarding deleting the records:

  ...it is going to create a gigantic transaction log that will probably
  cause the 5 minute transaction log backups to fail...

Are there any ways to get around this?

We don't know why your transaction log backups will fail but we can discuss solutions. He could be concerned about transaction log bloat, when the transaction log would grow so large that it filled the drive where the log file is stored.

First, and easiest solution: If you're moving all the data out of the table entirely, simply truncate the table. This is minimally logged.

Second: Split your DELETE statement into batches. Find a way to divide your data, let's say in batch sizes of 5,000.

DELETE FROM OldDBTable
WHERE ID IN
          (SELECT TOP 5000 ID
           FROM OldDBTable
           WHERE [Statement to define deleted data here])

You could probably increase that batch size if you want. Then just set a
WAITFOR DELAY '0:05:00' to wait 5 minutes for your next batch.


Combine relational DB and Elastic search We have a large quanitity of text files we want to free-text/full text search, combined with relational structured metadata about the text file.
So, a search could be "Give me all files that belong to group X(or sub groups of X), have author (Ari and Bari and Mari), belongs to organization Y, and contains the text "synthetic". The latter part being a full-text search, and the other being already stored as relational data in our existing db.

In our database(which is rather complex), there are stored a way to ID the files, and a ton of various metadata about the file, spread among tens of tables, ranging from simple 1-1 relationships, to 1-many sets pr file, and even tree-structure relationship(things like "this file is type X, type X is a subgroup of type Y, etc).
And this metadata may change over time, all over the application(which is huge).

Now, I as a database admin, thought this could be solved by using SQL Server to do the search for structured metadata already in the DB, constraining the search to candidate-files, and then passing the candidate file id's to elastic search for full-text searching. (Re-indexing the file on elastic when a file is added or commited is trivial in our code)

However, the elastic-guys in our project naturally had a different idea:
To extract all the meta-data as well as the full-text content from the files, to elastic-search, and run the search exclusively in elastic.

This allows them to run full powered lucene queries easily, and load is taken off the database, which is nice. However, this also to me, introduces a nightmare to keep the structured metadata in sync, and blindly re-indexing/syncing everything periodically is not possible due to the scale of data.

I can see merits/concerns to both options. Is there a best practice for this kind of thing?

Use both.

There is a line that needs to be drawn by you and your team here. SQLSERVER is more expensive than ElasticSearch so when I faced a similar issue it made more sense to spend CPU resources on elasticsearch than on sqlserver.

There's a few things that can make you decide to index your text data in elasticsearch

What kind of load are you looking at ?

A few searches per minute or dozens per seconds ?
This is subjective but again if a large amount of your database resources are spent on this one query you might want to offload that.

Keep your data structured

I find elasticsearch query language to be far less intuitive than SQL. I strongly suggest to have an as-normalized-as-possible version of your data in a standard relational database. Then base your elastic index on that.

Elasticsearch is great at many things but writing complex ad hoc queries with aggregations and/or subqueries isn't one of them.

How do you sync the data then ?

Triggers and queues are what I've been using.

Add a trigger on the table that has data you want to keep track of. Here's what one of the queues I've done looks like.

The trigger logs the action (insert/update/delete) and from there you know what to do in your Elasticsearch index. I've found rebuilding the entire record in elastic wasn't too expensive so this is what I do.

This way, you can take a project with a large code base and index any data you like in elasticsearch without having to do any code changes. Everything is handled by the state of your data in your RDBMS of choice.

Elasticsearch (and all the other nosql/document stores) have amazing use cases but storing relational data as a main database is not one of them. Use relational databases for that.


MAX Query returns set results when using dbmail I am setting up alerts in a system and I want to query a db table and get the max date passed in to a data row and only that one entry.

For some reason, when I do it, the HTML email that comes through has the same date over and over again.

How do I get it to where it stops repeating itself and just does the max date only?

SET @tableHTML16 =
    N'
BDI Feeds Last Received Batch
' +
    N'
' +
    N'
Cerner	CPACS	DSCH123	' +
    CAST  ((SELECT   td = (select max(CPProcessedDateTime) from eiwdata..eiwtcontentreceived (nolock) where CapturePointDesc='OneC_Cerner_BDI:OCBDI1'), '',
                     td = (select max(CPProcessedDateTime) from eiwdata..eiwtcontentreceived (nolock) where CapturePointDesc='OneC_CPACS_BDI:OCBDI1'), '',
                     td = (select max(CPProcessedDateTime) from eiwdata..eiwtcontentreceived (nolock) where CapturePointDesc='OneC_DSCH123_BDI:OCBDI1'), ''

            from eiwdata..eiwtcontentreceived (nolock)

        FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'
' ;

You can see what it looks like when it gets to this portion of the script by looking in sysmail all items:

BDI Feeds Last Received Batch

Cerner	CPACS	DSCH123

2018-02-28T17:28:13.450	2018-02-28T16:31:51.113	2018-02-28T16:55:35.433

2018-02-28T17:28:13.450	2018-02-28T16:31:51.113	2018-02-28T16:55:35.433

2018-02-28T17:28:13.450	2018-02-28T16:31:51.113	2018-02-28T16:55:35.433

2018-02-28T17:28:13.450	2018-02-28

I of course had to shorten it, but it just goes over and over again.

You have at least one from eiwdata..eiwtcontentreceived (nolock) too many. You are fetching the max value for each row in eiwdata..eiwtcontentreceived.

SET @tableHTML16 =
    N'
BDI Feeds Last Received Batch
' +
    N'
' +                          -- close
 here
    N'
Cerner	CPACS	DSCH123
' +
    CAST  ((SELECT   td = (select max(CPProcessedDateTime) from eiwdata..eiwtcontentreceived (nolock) where CapturePointDesc='OneC_Cerner_BDI:OCBDI1'), '',
                     td = (select max(CPProcessedDateTime) from eiwdata..eiwtcontentreceived (nolock) where CapturePointDesc='OneC_CPACS_BDI:OCBDI1'), '',
                     td = (select max(CPProcessedDateTime) from eiwdata..eiwtcontentreceived (nolock) where CapturePointDesc='OneC_DSCH123_BDI:OCBDI1'), ''

/*
             This table reference should be removed
             from eiwdata..eiwtcontentreceived (nolock)
*/

        FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'
' ;


Best way to store different types of data in a single column, which should be query friendly - MySql I'm working on a project in which user can create their own web services from UI by simply selecting number of fields (Max 200) and defining datatype for each field.
I'm considering following table structure for the same :

ServiceMaster
+-----------+-------------+-------------+
| ServiceId | ServiceName | FieldsCount |
+-----------+-------------+-------------+
| 1         | Service1    | 2           |
+-----------+-------------+-------------+
| 2         | Service3    | 3           |
+-----------+-------------+-------------+

ServiceDetails
+------------------+-----------+--------+---------+-------+-------------+
| ServiceDetailsId | ServiceId | Field1 | Field2  | Field3| Field...200 |
+------------------+-----------+--------+---------+-------+-------------+
| 1                | 1         | 5      | Active  | NULL  | NULL ...    |
+------------------+-----------+--------+---------+-------+-------------+
| 2                | 2         | High   | 9.0     | 7     | NULL ...    |
+------------------+-----------+--------+---------+-------+-------------+
| 3                | 1         | 2      | Running | 7     | NULL ...    |
+------------------+-----------+--------+---------+-------+-------------+

I'm not adding DataTypeDetails table here for clean and simple code, but yes I have one table to hold datatype for fields.
If you see above table structure, I can define Varchar or any string datatype for all Fields, but if I do that then while applying filters on table I have to CAST fields data with it's original datatype. Like, 9.0 (Float) or 7 (Int) etc.
I think this will slowdown query if I will use TYPECASTING for all columns to apply filters. This able may have Billions of records or more.
So is there any alternate way to achieve the same with better performance.

This is basically a question about a multitenant application; and cal also be thought of as a particular application of an Entity Attribute Value scenario.

You can have different approaches, on top of your current one:

Have one ServiceDetails table per user/service (per tenant), with fields defined according to his/her/its needs. Your query will use ServiceDetails1 ... ServiceDetailsN tables, instead of just 1.  Instead of having your table DataTypeDetails, you can use the database metadata (information_schema) if needed. This is the most query friendly way of doing things.
Instead of having field1, field2, ..., field200, have a much larger collection of fields: field1_text, field1_integer, field1_float, field1_timestamp, field2_... and use the version of your column that has the proper type for your specific case. You will have an enormous amount of NULLs, and probably a lot of indexes. This uses only (a very large and sparse) table. It's not normally the best approach, but it is a possibility.
Use a database that allows for more flexible structures (such as JSON) for variable-schema. (PostgreSQL JSONB data-type, which can be indexed, would be a good fit for this scenario; even if you will still need to cast types).

References:

Introduction to Designing Multi-Tenant Web Applications
Three Approaches to Managing Multi-Tenant Data
Entity–attribute–value model


Deadlock Explanation I've inherited a SQL Server 2012 database that uses triggers to copy all changes made to dbo tables into an audit schema, providing functionality similar to system-versioned temporal tables.

One of these triggers has recently been causing deadlocks:

ALTER TRIGGER [dbo].[ClientBusinessUnitBehaviour_Trigger_Update]
   ON  [dbo].[ClientBusinessUnitBehaviour]
   AFTER UPDATE
AS

BEGIN

        SET NOCOUNT ON;

        INSERT INTO adt.[ClientBusinessUnitBehaviour]
        SELECT getdate(),'Update', * FROM inserted

        UPDATE [a]
        SET a.[RowVersion] = CASE WHEN ea.MaxRowVersion IS NULL THEN 0x01 ELSE CONVERT(VARBINARY(1), CONVERT(INT, ea.MaxRowVersion) + 1) END
        FROM adt.[ClientBusinessUnitBehaviour] AS [a]
        LEFT JOIN (SELECT Id, MAX([RowVersion]) AS MaxRowVersion FROM adt.[ClientBusinessUnitBehaviour] WHERE Id IN (SELECT Id FROM inserted) GROUP BY Id) AS [ea] ON ea.Id = a.Id
        WHERE a.[RowVersion] IS NULL

END

I haven't seen actual SQL Server logs, but I can access reports that expose data about deadlocks, and these say that both processes involved in the deadlocks are running the second part of this trigger, the UPDATE statement.

Please could someone explain how this can cause a deadlock? My very limited understanding of deadlocks is that they normally occur when two processes are accessing objects in a different order, so I'm not sure how two processes running the same SQL can end up deadlocking.

The transaction isolation level is READ COMMITTED, and READ_COMMITTED_SNAPSHOT is on.

The audit tables don't have primary keys, so I suspect the problem may be similar to the one described here, but that doesn't seem to explain why there's a deadlock, instead of one process just blocking the other temporarily.

Having spoken to the original developer it's become clear that the UPDATE is not required, but I'd like to understand what the problem was.

When running the second statement, each session has already inserted rows in adt.[ClientBusinessUnitBehaviour], and has those new rows exclusively locked.

The UPDATE statement may need to read the new rows inserted by the other session to discover if they have a NULL RowVersion column.


Subtracting dates give me error in Postgresql I use age to get the interval between to date fields. Their type is timestamp.

My code is as below:

select age(timestamp MySecondDateField, timestamp MyFirstDateField)

The data in my field contains:

2014-01-26 09:53:17.036359

How should I subtract two dates?

When I use age as below it works:

select age(timestamp '2001-04-10', timestamp '1957-06-13')

If those columns are timestamp (or date) columns, just use

age(MySecondDateField, MyFirstDateField)

The timestamp or date prefix is only needed for date/timestamp constants.

If those columns are varchar then you should change them to a proper timestamp/date datatype.

Until you do that, you can use the to_date() or to_timestamp() function to convert a string to a date, e.g:

to_timestamp(MySecondDateField, 'yyyy-mm-dd hh24:mi:ss')

For more details on those functions, please see the manual: http://www.postgresql.org/docs/current/static/functions-formatting.html

You should never store dates (or numbers) as varchar/text columns. To fix your table, you can use a conversion function when you run the alter table:

alter table wrong_design
   alter MyFirstDateField type timestamp
   using to_timestamp(some_col, 'yyyy-mm-dd hh24:mi:ss.us')


How to use PostgreSQL bit_or function to aggregate column? I wish to use PostgreSQL's bit_or function on several rows of a single column of my table, but I can't figure out the proper usage for this.

Suppose I have bitwise flags in my column flags, and the rows of interest hold the numbers 1 (B00000001), 2 (B00000010), and 13 (B00001101). I want the output to be 15 (B00001111).

What I've tried so far is as follows:

SELECT bit_or(SELECT flags FROM items);
-- ERROR:  syntax error at or near "select"
SELECT bit_or((SELECT flags FROM items));
-- ERROR:  more than one row returned by a subquery used as an expression
SELECT bit_or(array(SELECT flags FROM items));
-- ERROR:  function bool_or(integer[]) does not exist
SELECT bit_or(select array(SELECT flags FROM items));
-- ERROR:  syntax error at or near "select"

Do you have any advice?

bit_or is an aggregate function, like sum or count.

SELECT bit_or(flags) FROM items;


Backing up a MongoDB cluster with mongodump? Can anyone indicate the right way to backup a MongoDB database with mongodump?

I have tried:

mongodump --host "cluster0-shard-00-00-2djno.mongodb.net:27017,cluster0-shard-00-01-2djno.mongodb.net:27017,cluster0-shard-00-02-2djno.mongodb.net:27017" \
  --authenticationDatabase admin --ssl --username admin \
  --password mypassword -o /tmp

When I do that I get:

Failed: error connecting to db server: no reachable servers

Verifying connection to an individual node with both the ping and telnet commands such that these nodes are valid.

The mongodump version is 3.2.13.

Upgrading to MongoDB 3.4 was necessary, since the cluster string did not appear to work with 3.2.

Also, based on the blog post for 'importing data', a small tweak to the host parameter seemed necessary, in the form of adding the 'Cluster0-shard-0/' prefix :

mongodump --host "Cluster0-shard-0/cluster0-shard-00-00-2djno.mongodb.net:27017,cluster0-shard-00-01-2djno.mongodb.net:27017,cluster0-shard-00-02-2djno.mongodb.net:27017" \
  --authenticationDatabase admin --ssl --username admin \
  --password mypassword --oplog -o /tmp

Edit: updated the response based on @jjussi's feedback. Also, we aren't trying to avoid using the mongo cloud backup, which is more convenient, it is that we just feel better having our own local copy, for various reasons.


SQLNCLI10.x vs 11.x when connecting SSIS 2008R2 to a SQL Server 2012 database Starting from the question and working backwards.

My question: is it OK to use SQLNCLI10.1 as a provider for SSIS 2008 R2 to connect to SQL Server 2012 database(s)?

I have a SSIS box running SQL Server & SSIS 2008 R2. The SSIS packages connect variously to different data sources including different SQL Server databases, and some of these databases are on SQL Server 2012. In line with the general principle that you would use the right provider for the source you are connecting to, I would expect that the "right" choices are SQLNCLI11 to connect to SQL Server 2012, and SQLNCLI10 to connect to SQL Server 2008 & 2008 R2.

However using SQLNCLI10.1 connects fine to SQL Server 2012 and appears to be fully functional and bug free.

Couple this with these other factors:

The SSIS box I refer to is one element of the dev lifecycle so to change this box means also installing v11 on several other boxes.
The added complexity of having to choose the right provider for the right DB Connection instead of a single SQLNCLI (there are no SQL Server 2000 / 2005 sources to worry about).  Especially since the ConnMgr doesn't allow you to change it once set.
Issues such as this which imply there are greater risks in using 11 over 10.

I realise open or discussion type questions are frowned upon, so, Q&A:

Are there any RISKS in staying using SQLNCLI10 to connect from SQL Server 2008 R2 (SSIS) to SQL Server 2012?
Would there be any performance loss for the same, over using v11 instead?
Am I losing functionality or risking breakage by using 10.1 instead of 11.x?

Thanks.

According to this and this, there are upgrades to the sqlcmd.exe utility that I am assuming are reflective of underlying changes in the libraries. For instance,

  Beginning in the ODBC Driver 11 for SQL Server, there is no limit on the amount of data that retrieved in a single column when –y0 is specified.

I realize that this says the ODBC driver, but according to another link (that I can't post because of reputation limits), the SQLNCLI is some kind of wrapper for the ODBC drivers.

  The SQL Server Native Client...contains the SQL Server ODBC driver for Linux and  ODBC/ OLE DB provider for Windows ,  supporting native connectivity to Microsoft SQL Server.

From my limited searching, there don't appear to be any issues with using 10 to connect to a later sql server instance, but if there are SQL server features from that instance that you're taking advantage of, and you need access to them from your packages, then you're going to want to upgrade.

I can't speculate on performance, and would recommend benchmarking the two methods on your dev box to see what the differences are.

You are certainly losing functionality. I can't find any release notes that would indicate what functionality, per se, but the links above seem to hint at their being sql server specific functionality that is included in the NCLI APIs.


How to give access to a user to a view, but not to the table the view is using? I have a user (called myUser) which has db_datareader role on myCustomDatabase.

myCustomDatabase and MSDB are owned by sa user.

I've enable chaining (it's already enabled on MSDB):

ALTER DATABASE myCustomDatabase SET DB_CHAINING ON;

I created a view on myCustomDatabase as following:

CREATE VIEW myCustomDatabase.dbo.myView AS
    SELECT run_date  FROM msdb.dbo.sysjobhistory

I would like myUser be able to call this view, and not the following query:

SELECT * FROM msdb.dbo.sysjobhistory // Should not work
SELECT * FROM myCustomDatabase.dbo.myView // Should work

With described permissions I get following error:

The SELECT permission was denied on the object 'sysjobhistory', database 'msdb', schema 'dbo'.

Where am I wrong with db chaining?

Edit:

Here the script I run with succeed on a database test, but I still don't succeed to apply this changes on an already existing database:

CREATE DATABASE myCustomDatabase ;
GO

ALTER DATABASE myCustomDatabase SET DB_CHAINING ON;
USE myCustomDatabase ;
GO

CREATE VIEW dbo.myView AS
    SELECT run_date  FROM msdb.dbo.sysjobhistory
GO

// The login myUser already exists on my server
CREATE USER myUser FROM LOGIN myUser;

GRANT SELECT ON dbo.myView TO myUser;

Here the script I run on my already existing DB:

/*CREATE DATABASE thisDatabaseAlreadyExists ;
GO*/

ALTER DATABASE thisDatabaseAlreadyExists SET DB_CHAINING ON;
USE thisDatabaseAlreadyExists ;
GO

create  VIEW dbo.myView AS
    SELECT run_date  FROM msdb.dbo.sysjobhistory
GO

/* The user already exist, he's got db_datareader role on thisDatabaseAlreadyExists*/
/*CREATE USER myUser FROM LOGIN myUser;*/

GRANT SELECT ON dbo.myView TO myUser;

It was a problem about the database owner.

Even if you can see an owner on this interface:

Be sure that an owner is entered into files tab:


How to restore a database from the SQL 2008R2 log? Today, for a great oversight, did a deal with a big mistake, which I forgot to put the WHERE keyword in the UPDATE statement...
I searched in the google, I found a lot of things of how to restore the database ( FROM BACKUP ), but the backup was done daily database simply disappeared :(

Now, the only information I have remaining is the log file of SQL, I found the ApexSQL log, that program make a SQL undo script, but, just for a 10th process... I have 2024 to restore, and, I don't want to pay U$ 1.5k for this program, now, I want to know, how I can restore the informations, have a FREE program to make this ?

There is no free tool to do this. Basically, your only other option is to restore a backup. For that you need to have made a full backup of the database. If you have one, you can recover your table at the time of your full backup by restoring it into another database, and copy the table back to your original database.

Plus, if your database is in full recovery model, you can backup your transaction log and try to restore it after the full backup restore and other transaction log backups that you might have taken in between if you have a backup plan.
So, do you have backups of your database, and when were they taken?


Disable autocommit for SQL Server connection in Oracle SQL Developer I am using Oracle's SQL Developer to execute queries against my SQL Server 2008 database as I simply can't work with SSMS as long as it doesn't support Ctrl + Return to execute statements...

Anyway, statements in SQL Server seem to be autocommit even when using Oracle SQL Developer. Is it possible to change this on a permanent basis? BEGIN TRANSACTION is not a solution for me.

SQL Server uses autocommit mode by default. This cannot be changed permanently.

There are two ways implicit transactions (non-autocommit) can be turned on:

At the server level such that new sessions use it by default, using sp_configure 'user options' -- this may or may not work depending on how SQL Developer was implemented.
For a session, using SET IMPLICIT_TRANSACTIONS ON.


Oracle Enterprise Manager 11g Database Control - Agent Unreachable Single instance DB. I can login to Oracle Enterprise Manger (OEM). However,  on the Home Page it says:

Status - Agent Unreachable

Most tasks such as checking RMAN status, adding datafiles, etc. can still be done through the OEM. I do not know how Agent Unreachable impacts the OEM in this case (DB Control)
I checked status of the agent from $ORACLE_HOME/bin/emctl status agent, it showed:

The Agent is not Running

So, I want to start the agent, but running the command $ORACLE_HOME/bin/emctl start agent is not working. The command is not available.
In Oracle docs, it is mentioned that the command emctl start agent should be executed from AGENT_HOME/bin. However, I do not know the AGENT_HOME path.
How can I start the OEM Agent or figure out what the underlying issue is?

Do you have separate Oracle Homes for your Database and Grid Infrastructure (which was new to 11gR2, IIRC)?
If so, then the Agent probably runs from the Grid Infrastructure's Oracle Home.
Annoyingly, the bin directory in both Oracle Homes contains a program called emctl but only one of them will work as you expect!
If in doubt use the Linux find command to locate anything called emctl ...
find ???  -type f  -name emctl  -ls

I'd suggest, once you've found it, you add the Agent's "Oracle Home" into /etc/oratab under its own name, to make it easier to find it next time.


How to get statement which causes error 3609? How to find out which statement causes error Msg 3609, Level 16, State 1, Line 4
The transaction ended in the trigger. The batch has been aborted.?

The code seems very straight-forward:

-- 1. Check 1
IF (EXISTS (
... check ...
   ))
BEGIN
  RAISERROR('Does not allow 1', 16, 1);
  IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
  RETURN;
END;

-- 2. Check 2
IF (EXISTS (
... check ...
   ))
BEGIN
  RAISERROR('Does not allow 2', 16, 1);
  IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
  RETURN;
END;

I have check if transaction exists, so I must not see that error after raised error.

EDIT:

I agree with you @gbn. I know that operation in the TRIGGER is one transaction.

The problem is that in our code (written by me and one colleague) there is a case in which RAISERROR does not rollbacks trigger. I see that in practive - the error is raised by the trigger but the new data is inserted. The syntax there was opposite - first ROLLBACK and then RAISERROR. I found that switching places will fix situation (I don't know why it works only in that way).

I decided to play a bit with @@TRANCOUNT. But when I inserted several trancount the trigger start giving me 3609. Removing @@TRANCOUNT did not fix the issue. Because I have ROLLBACK with check if there is open transaction 3609 is very strange for me. There is another trigger, but even if I disable it I still receive 3609.

If you use TRY/CATCH in the calling code and trigger, then the error handling process becomes a lot more predictable and reliable.

Without this, an error in the trigger (here caused by RAISERROR) aborts the batch. So you'll never see the error from RAISERROR.

See Why TRY CATCH does not suppress exception in trigger for more

Note: a trigger is always in a transaction because it is part of the DML. Any trigger code/actions must be atomic with the DML of course.


Simple survey database design I'm creating a survey for visitors of my event.
However it's been a while since I created a database. So I need some help.

I found some solutions but they are way too extensive and that is something I don't need.

Visitors need to stay anonymous but they can leave their email behind (seperate table Emails that isn't linked to anything atm).
They have about 20 questions, some are open, some are one option(radio) and some are multiple options (checkboxes).
The questions need to be reusable.

That's about it. I just don't know how to go beyond the many-to-many in the diagram.

Generally one row per respondent, and one column per question item.  A battery of ten check-all-that-apply questions would thus get ten columns (either boolean or integer).  Each open end gets a String field.  During analysis, you can get fancy and aggregate questions, stack repeated batteries of questions, etc. in SAS, SPSS or Excel.


Get list of excluded databases from command line by asking user Based on this I'm working on this bash script for export all databases from MySQL but exlude some ones and also system ones. This is how it looks like:

read -e -p "Enter MySQL root user: " -i "root" root_user
read -e -p "Enter MySQL root user password: " root_password

FILENAME="mysqlbck"-$(date +%d-%m-%Y-%T).sql
read -e -p "Enter backup filename: " -i "$FILENAME" file_name
read -e -p "Enter directory for save backup: " -i "/home" directory

DATABASES_TO_EXCLUDE=""
EXCLUSION_LIST="'information_schema','performance_schema','mysql'"

for DB in `echo "${DATABASES_TO_EXCLUDE}"`
do
    EXCLUSION_LIST="${EXCLUSION_LIST},'${DB}'"
done

SQLSTMT="SELECT schema_name FROM information_schema.schemata"
SQLSTMT="${SQLSTMT} WHERE schema_name NOT IN (${EXCLUSION_LIST})"
MYSQLDUMP_DATABASES="--databases"

for DB in `mysql -ANe"${SQLSTMT}"`
do
    MYSQLDUMP_DATABASES="${MYSQLDUMP_DATABASES} ${DB}"
done

MYSQLDUMP_OPTIONS="--routines --triggers"
mysqldump -u"$root_user" -p ${MYSQLDUMP_OPTIONS} ${MYSQLDUMP_DATABASES} > "$directory/$filename"

I have two main issues around it:

DATABASES_TO_EXCLUDE should holds a comma separated list for each DB that I want to exclude, how do I ask, in command line, for those names and build something like db1,db2,db3 and so on and put the values on the var? I mean something like the flow below:

Enter DB name: db1
// DATABASES_TO_EXCLUDE="db1"
Enter DB name: db2
// DATABASES_TO_EXCLUDE="db1,db2"
Enter DB name: db3
// DATABASES_TO_EXCLUDE="db1,db2,db3"
Enter DB name:
// got empty value stop asking for DB name
// and continue script execution on EXCLUSION_LIST line and below

I'm having two issues executing the script as it's right now. See the console output below:

Enter MySQL root user: root
Enter MySQL root user password: ********
Enter backup filename: mysqlbck-12-03-2015-11:49:22.sql
Enter directory for save backup: /home
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
/usr/local/bin/script-task: line 132: /home/: Is a directory

Why do I get access denied while trying to use mysqldump? I have tried the way you are seeing on the script above and also tried this one:

    mysqldump -u"$root_user" -p"$root_password" ${MYSQLDUMP_OPTIONS} ${MYSQLDUMP_DATABASES} > "$directory/$filename"

Both have failed with same error, any advice?

As per second issue shouldn't $directory/$filename read as /home/mysqlbck-12-03-2015-11:49:22.sql or the problem is the %T parameter and the : on the filename? Where is the error?

Please look back at my post again.

DATABASES_TO_EXCLUDE

not supposed to be comma separated
supposed to be space separated

EXCLUSION_LIST

supposed to be a comma-separated list of single-quote enclosed database names
this is for the sake of forming the SQL to extract names from information_schema.schemata

If you made DATABASES_TO_EXCLUDE="db1,db2,db3", add the following lines to remove the commas

DBX=${DATABASES_TO_EXCLUDE}
DATABASES_TO_EXCLUDE=`echo "${DBX}" | sed 's/,/ /g'`

Your filename should look at little different

FILENAME="mysqlbck"-`date +"%Y%m%d-%H%M%S"`.sql

As for "Access Denied", you need to make sure you have the proper grants to the database.


Single DELETE statement deadlocking in Sybase ASE I've got a fairly straightforward process running on ASE 15.7, that creates rows in two tables and later deletes them.  The tables are both configured to use datapages for the locking scheme, and the column I'm using to delete the rows is the first column in the clustered index.  Chained transaction mode is on, but the stored procedure is not part of some external transaction.

For some reason, I occassionally see deadlocks on the first line of the stored procedure.  What's weird is that it's deadlocking with another spid running that exact same line!  If the rows they're both trying to delete share data or index pages, shouldn't one spid be blocked while the other one acquires the locks and then completes?  I'm at a loss as to how to resolve or even explain this, and haven't found anything in Sybooks Online.

This is the procedure - I've anonymized the object names, but otherwise the code is copied directly from the source file.

create procedure spCleanup (
    @firstColumnInClusteredPrimaryKey int
) as
begin

delete from tableName where firstColumnInClusteredPrimaryKey = @firstColumnInClusteredPrimaryKey

delete from relatedTableName where firstColumnInClusteredPrimaryKey = @firstColumnInClusteredPrimaryKey

end
go

This is the deadlock information from Sybase's errorlog file.  Again I've anonymized the object, server and user names, but the text is otherwise copy-pasted directly from the log.

00:0011:00000:00166:2013/10/08 00:41:56.16 server  Deadlock Id 100 detected
Deadlock Id 100 detected. 1 deadlock chain(s) involved.

Deadlock Id 100: Process (Familyid 0, Spid 134, Suid 13) was executing a DELETE command in the procedure 'spCleanup' at line 8 at nested level 1.
Deadlock Id 100: Process 134 was involved in application 'appName'.
Deadlock Id 100: Process 134 was involved on host name 'serverName'.
Deadlock Id 100: Process 134 was involved in transaction '$del'.
SQL Text: spCleanup  2916
Executing procedure: spCleanup
Subordinate SQL Text: delete from tableName where firstColumnInClusteredPrimaryKey = @firstColumnInClusteredPrimaryKey

Deadlock Id 100: Process (Familyid 0, Spid 166, Suid 13) was executing a DELETE command in the procedure 'spCleanup' at line 8 at nested level 1.
Deadlock Id 100: Process 166 was involved in application 'appName'.
Deadlock Id 100: Process 166 was involved on host name 'serverName'.
Deadlock Id 100: Process 166 was involved in transaction '$del'.
SQL Text: spCleanup  2911
Executing procedure: spCleanup
Subordinate SQL Text: delete from tableName where firstColumnInClusteredPrimaryKey = @firstColumnInClusteredPrimaryKey

Deadlock Id 100: Process (Familyid 0, Spid 166) was waiting for a 'exclusive page' lock on page 67858 of table 'tableName' in database 'dbName' but process (Familyid 0, Spid 134) already held a 'exclusive page' lock on it.
Deadlock Id 100: Process (Familyid 0, Spid 134) was waiting for a 'update page' lock on page 67941 of table 'tableName' in database 'dbName' but process (Familyid 0, Spid 166) already held a 'exclusive page' lock on it.

Deadlock Id 100: Process (Familyid 0, Spid 134) was chosen as the victim.
Victim process host = `serverName', user = `userName' program name = `appName' host processes = `dbServerName' .

End of deadlock information.

Original post describes a classic deadlock scenario with a twist, namely, a single DELETE ... WITH statement, which acquires 2 different kinds of locks.

Recommendation - change table from All Page Lock (APL) to Data Row Lock (DRL) scheme

clustered index will be replaced by placement index separate from data page
index access / updates will use latches rather than logical locks
row-level deadlock conflict is less probably (than page-level's)

Both SPIDs are executing the same sproc with the 1st DELETE ... WHERE statement demanding 2 different kinds of locks:

initially an UPDATE lock on a page when scanning for a row to delete (UPDATE lock is compatible with shared locks only but not with another UPDATE lock or exclusive lock);
once a target row is found on a page, then the UPDATE lock must be upgraded to an exclusive lockbefore deleting that row.

The DEADLOCK events posted in your original question confirm the scenario :

SPID 134 has held an exclusive lock on a page while waiting to acquire an UPDATE lock on another page i.e. it has deleted at least one row already and is continuing with searching for another (row to delete);
SPID 166 blocks the first SPID's acquiring an UPDATE lock on a page while waiting to apply an exclusive lock on another page, which has an exclusive lock held by SPID 134 already (after deleting 1 row or more).


MySQL : "FLUSH TABLES WITH READ LOCK" started automatically I would like to understand how this happened. I was running a query that would take a long time, but should not lock up any table. However, my dbs were practically down - it seems like it was being locked up by FLUSH TABLES WITH READ LOCK

  03:21:31  select type_id, count(*) from guid_target_infos group by type_id
  02:38:11  select type_id, count(*) from guid_infos group by type_id
  02:24:29  FLUSH TABLES WITH READ LOCK

But i did not start this command. can someone tell me why it was started automatically?

Some people use FLUSH TABLES WITH READ LOCK to lock a DB Server and perform mysqldumps. Doing this will trigger a full flush of any InnoDB table that has dirty pages in the InnoDB Buffer Pool.

Over the years, I have scripted some backups using mysqldump prefaced with FLUSH TABLES WITH READ LOCK. Within one DB thread, I would run FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400);. I would then capture the MySQL ProcessID running the SLEEP function. When the mysqldump was finished, I would kill the MySQL ProcessID running the SLEEP function.

I knew some clients that manually ran the FLUSH TABLES WITH READ LOCK by hand in one session and ran the mysqldump in a separate ssh session.

Either way, FLUSH TABLES WITH READ LOCK was issued with this purpose in mind. Please look over the slow query logs for SLEEP function calls. Also, check coworkers for any crontab jobs doing backups with it or any adhoc backups requiring FLUSH TABLES WITH READ LOCK. The mysqld process would not issue that command on its own by design.


Merge reports 'You must rerun snapshot because current snapshot files are obsolete', gets caught in Death Loop We have a very large Merge publication for a data warehouse with some large (.bcp files on the order of 20GB) fact tables that is causing us tremendous trouble.

Specifically, we created a publication and a subscription, and things ran OK w/ a couple hiccups (disk space). After the first hiccup (log space exhausted) we saw this:

You must rerun snapshot because current snapshot files are obsolete.

On restart, it did pretty much what one would expect (went through the process, skipping things it had already done).

At the end we saw the 'nothing to do' message, but immediately it went back into the ftp/apply process, failing b/c when it tries to apply the .sch file 'can't drop the table because it is being used in replication'.

So things are obviously hosed. Restarting from zero might be advised but we did the 'remove publication' stored procedure on the offending subscriber last night so we should have been clean.

Also, this whole process (downloading and applying) took 9 hours, so if possible I would like to get to the root of it before restarting the process. Why would the snapshot have become obsolete in such a short period? What could have gone awry? If you have any questions or need info, let me know.

For problems with dropping the table the things I would look at making sure there isn't a trigger, view or foreign key preventing the table from being dropped. Next time you apply the snapshot run Profiler pointed at the target database/server to return only Exceptions and Error messages while applying the snapshot against the subscriber. The messages returned should guide you to the exact cause.

Regarding the snapshot becoming invalid, double check the retention period configured for your distribution database. If this is less than the total time it takes to successfully apply the snapshot to the subscriber it will invalidate the snapshot and re-run the snapshot generation and restart the copy to the subscriber.


Add INCLUDE term to primary key index in Postgres 11 I work with Postgres 11 and would like to change the index that backs the primary key of a large table (~750 million rows). The primary key is the bigint column id and I would like to include an extra column using the INCLUDE term. This needs to happen without a table rewrite (i.e. no new/changed columns).

Dropping the current PRIMARY KEY constraint isn't convenient, because a lot of other tables reference the target table. I suppose it would be possible to drop the FOREIGN KEY constraints on all those tables, then drop the PRIMARY KEY constraint, recreate using a new index and then recreate the FOREIGN KEY constraints. Is there a better way to do it?

Without messing with the catalogs (which is not commendable), the only option I can think of would require you to do without foreign keys for a while:

You can define a second UNIQUE index on the table that contains the appropriate INCLUDE clause.

If you use the CONCURRENTLY clause of CREATE INDEX, that shouldn't be disruptive.

Then you can delete the original primary key constraint and all dependent foreign keys using DROP INDEX ... CASCADE.

Then use ALTER TABLE ... ADD CONSTRAINT ... USING INDEX to turn the unique index into a primary key constraint.

Now you can re-create the foreign key constraints.


SQL Delete rows where a dataname will equal mulitple variables Researching similar common queries have really helped but I am still none the wiser as to my issue. To note that the database I intend to use this on contains around 1000 rows (same 2 columns listed below) of which 300-350 will need deleting depending on their ID.

E.g. I think the query should be something like (table = mytable):

Title ID
One   1
Two   2
Three 3
Four  4

delete from mytable
where ID = 1 or 2 or 3;

Obviously when it comes to deleting 300 odd rows, putting an operator inbetween every variable probably isn't the best way of this (nor probably correct anyway) hence why I am asking this question!

Any advice would be much appreciated, thanks!

You could use the IN operator for example

WHERE id IN (1,2,3)

How are you deciding which rows you want deleting, that might help us answer?


SQL Developer: choose the tablespace where a new table will be stored When creating a new table, is it possible to specify the tablespace where the table is going to be stored?

Check Advanced, and under Storage, Define Storage, where you can choose the tablespace.


Postgresql one master multiple slaves failover My problem is same as the one asked in below topic

Streaming Replication Failover - how to point second slave at new master?

Currently I am working on Postgresql 9.5, I have one master and two slave servers, after master crashed and one of the slaves is assigned as new master, I want to change the master of other slave without full base backup.

I tried the steps written in answer, but it does not seem to work, can someone please guide me on solving this problem?

I am writing some details of my configuration, in case it helps.

standby_mode = on
primary_conninfo = 'host=10.10.10.160 port=5432 user=repuser password=*****'
restore_command = 'cp /archivedir/%f %p'
trigger_file = '/tmp/postgresql.trigger.5432'

The 10.10.10.160 is the IP address of new master server.

Also /archivedir folder seems empty in both master and slave servers, is this normal? The below settings are present in the postgresql.conf:

wal_level = hot_standby
archive_mode = on
archive_command = 'test ! -f /archivedir/%f && cp %p /archivedir/%f'
max_wal_senders = 3
hot_standby = on

I manage to solve it with dezso's help.

I used cascading replication for this. Current architecture is like M -> S1 -> S2.

Master: 10.10.10.146,
S1: 10.10.10.130,
s2: 10:10:10:160,

First of all I created repuser in all three systems with below command.

sudo -u postgres createuser -U postgres repuser -P -c 5 --replication

It will ask you to assign a password, type anthing you like and repuser will be created. We will use this user for replication related operations.

On Master server current configuration is:

postgresql.conf

listen_addresses = 'localhost, 10,10,10,146'
wal_level = hot_standby
archive_mode = on
archive_command = 'test ! -f /here i wrote full directory starting from root/%f && cp %p /here i wrote full directory starting from root/%f'
max_wal_senders = 3

pg_hba.conf

# Allow replication connections
host     replication     repuser         10.10.10.130/32        md5
host     replication     repuser         10.10.10.160/32        md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

After this configurations restart master server.

Now we need to get full backup from master to S1. First stop the postgresql service on S1. Then remove the data directory (directory is var/lib/pgsql/9.5/data for my system). After removing run full backup command:

sudo -u postgres pg_basebackup -h 10.10.10.146 -D /var/lib/pgsql/9.5/data -U repuser -v -P --xlog-method=stream

This will copy the data folder to S1 server. Now open postgresql.conf in S1, and make below changes

listen_addresses = 'localhost, 10,10,10,130'
wal_level = hot_standby
archive_mode = on
archive_command = 'test ! -f /full directory starting from root/%f && cp %p /full directory starting from root/%f'
max_wal_senders = 3
hot standby=on

Open pg_hba.conf and make below changes:

# Allow replication connections
host     replication     repuser         10.10.10.160/32        md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

And finally create a recovery.conf file under data folder. recovery.conf should be like:

standby_mode=on
primary_conninfo='host=10.10.10.146 port=5432 user=repuser password=password_of_repuser'
trigger_file='/full directory starting from root/any_folder_name'

For example if you write trigger_file = '/tmp/trigger_failover' in recovery.conf file, then create a folder under /tmp directory named trigger_failover, S1 will start failover process and will start to serve as master server.

After above configurations start S1 server and now M and S1 should work as master and slave. Next we need to configure S2 server. Steps are pretty much same as configuring S1.

First stop the postgresql service on S2. Then remove the data directory as we did in S1. In my case S2 has same folder architecture  with S1, so data folder is also present in /var/lib/pgsql/9.5/data directory. Now run below command this time we will get full backup from S1 server.

sudo -u postgres pg_basebackup -h 10.10.10.30 -D /var/lib/pgsql/9.5/data -U repuser -v -P --xlog-method=stream

No we will change configuration files in S2. In postgresql.conf make below changes:

listen_addresses = 'localhost, 10,10,10,160'
wal_level = hot_standby
archive_mode = on
archive_command = 'test ! -f /full directory starting from root/%f && cp %p /full directory starting from root/%f'
max_wal_senders = 3
hot standby=on

And in recovery.conf make below changes:

standby_mode=on
primary_conninfo='host=10.10.10.160 port=5432 user=repuser password=password_of_repuser'
recovery_target_timeline='latest'

And you are finished. Now start postgresql service in S2 and see replication mechanism work in M, S1 and S2.

By this architecture:

if M goes down, you can trigger S1 and make it work as new master and
S1 will have replication with S2.
If S1 goes down you can easily point S2 to M just changing
primary_conninfo command in recovery.conf file in S2 and you will have
replication with M and S2.
And finally if S2 goes down M will still have replication with S1.


Postgres: Unsafe use of new value of enum type I want to alter a constraint after I have added a new value to an enum I use. So I originally have an enum called activity_state where I want to add a new value. But I also need to change the constraint I put on the field completed_at.
  `DO $$ BEGIN
    CREATE TYPE activity_state AS ENUM ('TODO', 'SCHEDULED', 'DONE');
  EXCEPTION
    WHEN duplicate_object THEN null;
  END $$;

  CREATE TABLE IF NOT EXISTS ${activitiesTable} (
    ...
    state activity_state NOT NULL,
    completed_at timestamptz CHECK (
         (completed_at IS NULL AND state != 'DONE')
      OR (completed_at IS NOT NULL AND state = 'DONE')
    ),
  );

  -- Alter state enum
  ALTER TYPE activity_state ADD VALUE IF NOT EXISTS 'DONE_BY_PASSED';

  -- Alter constrain on
  ALTER TABLE ${activitiesTable} DROP CONSTRAINT maintenance_activities_check;
  ALTER TABLE ${activitiesTable} ADD CONSTRAINT maintenance_activities_check CHECK (
         (completed_at IS NULL AND (state != 'DONE' OR state != 'DONE_BY_PASSED'))
      OR (completed_at IS NOT NULL AND (state = 'DONE' OR state = 'DONE_BY_PASSED'))
    );

The enum seems to be added correctly, but once I use it my new check constraint I get the error:
error: unsafe use of new value "DONE_BY_PASSED" of enum type activity_state
Not sure how I can alter my constrain once the enum is changed? I am using postgres 12.

You have forgotten to show us the hint you got:
ERROR:  unsafe use of new value "DONE_BY_PASSED" of enum type activity_state
HINT:  New enum values must be committed before they can be used.

You also forgot to mention that you ran the ALTER TYPE and the ALTER TABLE statements in the same transaction.
Like the hint says, you have to commit the ALTER TYPE before you can use the new enum value.


large number of tables and postgres query performace I have a scenario where there are 40000 tables in the database. but there are no huge unions. Very simple single table select queries are performing much slower than usual (table with just 600,000 rows and 10-15 columns).

Does the (large) number of tables in a database can affect query performance? If Yes, How?

If anything, the large number of tables would affect planning time.

EXPLAIN (ANALYZE, BUFFERS) your query to see the planning time and figure out where the time is spent.


What is the largest whole number in Oracle SQL table that can be stored without rounding errors? I want to "concatenate" numbers to create temporary ids for tuples of objects, but I am worrying about precission loss and rounding errors. Query below suggests that there are no problems with rounding at least up to 10^27.

select t.remvalue, t.logvalue, t.totvalue, (t.totvalue - 1000000000*1000000000 ) as diffvalue18, (t.totvalue - 1000000000*1000000000*1000000000 ) as diffvalue27  from (
select 1 as remvalue, 9 as logvalue, 1000000000 + 1 as totvalue from dual union all
select 2 as remvalue, 18, 1000000000*1000000000 + 2 from dual union all
select 3 as remvalue, 18, 1000000000*1000000000 + 3 from dual union all
select 4 as remvalue, 27, 1000000000*1000000000*1000000000 + 4 from dual
 ) t
--  Rem log  total   difff18       diff27
--4 4   27  1E27    9.99999999E26   4      --diff 27 is correct

How far can I actually go with such "concatenation of integers without running into a problem?

Since ypercube suggested that the max_value << 10^100 I did a binary search.
 Oracle supports 39 digit precision for whole numbers.


Quite often you can use row_number() to select only certain rows of the data. so maybe something like this would work:

select * from (
  SELECT
    instance_name ,
    DU.database_name ,
    DU.collection_time ,
    DU.dbsize ,
    DU.logsize ,
    DU.reservedpages ,
    DU.usedpages,
    row_number() over (
      partition by instance_name, DU.database_name, convert(date, DU.collection_time)
      order by DU.collection_time desc) as RN
    ...
) X
WHERE RN = 1
ORDER BY instance_name ,
         database_name ,
         collection_time;


How can I verify that my SQL Server edition is Express? I have a SQL Server (version 11.0.5058). I tried to restore a database from a bak file and I got an error saying:

  CREATE DATABASE or ALTER DATABASE failed because the resulting
  cumulative database size would exceed your licensed limit of 10240 MB
  per database.

I was told that I do not have an Express instance, and thus I do not have a database limit of 10GB. I was not able to find information to verify this. Although under Help->About the word Express does not occur. How can I verify the database limit information?

What can be the problem?
Is this something in my configurations I can change?

You have to check the SERVERPROPERTY ('Edition') AS ServerEdition to find if your installed instance is an Express edition or standard or enterprise.

You can use below T-SQL :

SELECT
   SERVERPROPERTY ('MachineName') AS PhysicalMachineName,
   SERVERPROPERTY ('ServerName') AS SQLServerName,
   SERVERPROPERTY ('Edition') AS ServerEdition,
   CAST(SUBSTRING(@@Version,charindex('SQL',@@version, 1),15) AS VARCHAR(255)) +  ' + '
       + CAST (SERVERPROPERTY ('productlevel')AS VARCHAR (50)) + ' + (Build'
       + CAST (SERVERPROPERTY ('ProductVersion') AS VARCHAR (50)) +')' AS ProductVersion

Another variation that tells you the database size limit based on the version (note that -1 means unlimited)

SELECT
   SERVERPROPERTY ('MachineName') AS PhysicalMachineName,
   SERVERPROPERTY ('ServerName') AS SQLServerName,
   SERVERPROPERTY ('Edition') AS ServerEdition,
   CAST(SUBSTRING(@@Version,charindex('SQL',@@version, 1),15) AS VARCHAR(255)) +  ' + '
       + CAST (SERVERPROPERTY ('productlevel')AS VARCHAR (50)) + ' + (Build '
       + CAST (SERVERPROPERTY ('ProductVersion') AS VARCHAR (50)) +')' AS ProductVersion
    , case  when cast(SERVERPROPERTY ('Edition') as varchar(255)) like '%express%'
                then
                    case
                        when (
                                CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') as varchar(128)), 4) as int) >=10  -- major
                             and CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') as varchar(128)), 3) as int) in (0,50) -- minor
                              ) then 10  -- this is sql server 2008R2 and up
                        when (
                                CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') as varchar(128)), 4) as int) <= 10
                             and CAST(PARSENAME(CAST(SERVERPROPERTY('ProductVersion') as varchar(128)), 3) as int) < 50
                              ) then 4  -- this is sql server 2008 and less
                      end
                        else -1
        end  as EditionLimit

Starting sql server 2008R2, Express edition database limit was increased from 4GB to 10GB.


Optimization of variables in my.cnf (mysql) for Drupal I just get a Dedicated Server running UBUNTU and I need to optimize the variables in my.cnf for Drupal 7.

I understand that this is tailed job. Then Is there any tool to help me to do this? I am a newby at this.

I found this article (which seems a little outdated). It shows some values for a server with 500mb of RAM.

My server has 16Gb of RAM. I guess is not as ease as to multiply those values by 32...?

I use InnoDB tables.

For a setup with 500mb of RAM your my.cnf file may look like this:

[mysqld]
max_connections = 150
max_user_connections = 150
key_buffer = 36M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 3M
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 1800
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 1000
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
tmp_table_size = 16M
innodb-flush-log-at-trx-commit=2

Your config looks pretty good. If you have 16 GB of ram and it is a dedicated server for Mysql, then I would bump innodb to 8GB.
Mysql will also need to "warm up" which is the time is takes to start caching the most frequently used tables. Depending on your application, query cache might not be useful. (I would turn if off). Make sure to turn on Slow Query log, set long query to 5 sec. If nothing shows up after a day or two, set long query to 3 sec.
with a 8GB innodb_buffer, you should be able to handle a database size > 20GB.
You can always increase the innodb buffer pool but it will require a restart.

slow-query-log          = 1
slow-query-log_file     = /mysqldb/logs/.hostname..-slow-query.log
long_query_time         = 5
log_slow_verbosity      = full


How to troubleshoot/debug Oracle permission problems An application is connecting to Oracle to do stuff. It's not working but I don't get any ORA errors in the application itself. I'm thinking its a permission problem. So my question is what is the best way to check to see if a oracle account  tried to perform an operation that it didn't have permission to do? It would also help to know what operation it tried to perform so I can grant it.

Another option (short of full auditing) is a servererror trigger for the schema in question (or the whole database).

drop table error_log;
create table error_log (error_time timestamp, username varchar(50), msg varchar(4000), stmt varchar(4000));

CREATE OR REPLACE TRIGGER servererror_trigger
  AFTER SERVERERROR
  ON SCHEMA
declare
   sql_text ora_name_list_t;
   msg_     varchar2(2000) := null;
   stmt_    varchar2(2000) := null;
begin

  for depth in 1 .. ora_server_error_depth loop
    msg_ := msg_ || ora_server_error_msg(depth);
  end loop;

  for i in 1 .. ora_sql_txt(sql_text) loop
     stmt_ := stmt_ || sql_text(i);
  end loop;

  insert into error_log
  (error_time, username, msg, stmt)
  values
  (current_timestamp, ora_login_user, msg_, stmt_);
end;
/

If it's for a single schema/user the trigger needs to be created under that user. You also can trap errors for the whole database by specifying ON DATABASE. In that case you'll need to create that trigger and the table as DBA I assume (haven't tried that).

Now nearly all errors that happen will be recorded by the trigger (see the manual for a list of errors that will not be reported: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/create_trigger.htm#LNPLS1992)


Comparing records from same table and deleting the old ones Greeting,
I have a beginner experience with programming in Oracle PL/SQL. The problem I am trying to solve is that in a hypothetical situation, I want to compare records in the same table based on the arbitrary columns called effdt(effective date) and position_nbr(position number). They would be based on new and old records. If the the new effdt field is greater than or equal to(>=) the old effdt based on the old and new position_nbr being equal, I want to delete the old effdt record from the table. Below is the small procedure I have developed to execute.
DECLARE
table_cnt_001           NUMBER;
CURSOR ITR1
IS
(SELECT *
FROM
TABLE1 O
INNER JOIN TABLE1 N
ON
  N.POSITION_NBR = O.POSITION_NBR
WHERE
  N.EFFDT >= O.EFFDT);
BEGIN
SELECT COUNT(*) INTO effdt_cnt_001 FROM TABLE1;
--Checking if table is empty
IF table_cnt_001 !=0 THEN

FOR IT IN ITR1 LOOP
--BEGIN
IF (N.EFFDT >= O.EFFDT) AND (N.POSITION_NBR = O.POSITION_NBR) THEN
DELETE FROM TABLE1 O
WHERE EXISTS(SELECT O.EFFDT FROM TABLE1 O);
END IF;
COMMIT;
--END;
END LOOP;
END IF;
END;

I have gotten the error identifier 'N.EFFDT' must be declared. As far as I am concerned, I am refencing the cursor in the for loop and I am not sure why the if statement requests for this column to be declared. Please let me know what could be wrong with this logic or if there is a better way to solve this problem. Thanks in advance.

You don't need PL/SQL for this, you can delete using a sub-query:
delete from table1 where ID in (
  select ID from (
    select ID
      ,count(*) over(partition by POSITION_NBR, EFFDT) as cnt
      from table1
    )
  where cnt > 1 --more than one row matches the grouping condition
);


Adding column from SSMS wizard in SQL Server Today, I am reading through Pinal Dave's blog about adding column in SQL SERVER....One thing make me surprised that while adding column from SSMS wizard, it goes from following process:

Create a New Table with new columns
Insert Data from previous table to new table
Redo all the keys and constraints
Drop old table Rename
the new table to use the old table’s name

Why does SQL SERVER take such time and resource consuming process? Is there any benefit from such long process....

It only does that if you try and insert the new column between already existing ones.

If you add it to the end of the table it will just do a simple

 ALTER TABLE T ADD some_column INT

The reason SSMS does this is because there is no TSQL syntax to reorder the columns in a table. It is only possible by creating a new table.

I agree that this would be a useful addition. Sometimes it would make sense for the new column to be shown next to an existing column when viewing the table definition (e.g. to have a created column next to a last_modified column or telephone_number grouped with address and email columns).

But a related Connect Item ALTER TABLE syntax for changing column order is closed as "won't fix". The only workaround apart from rebuilding the table would be to create a view with your desired column order.


INSERT and SELECT query I am trying to insert 5-star ratings by James Cameron for all movies in the database. Review date must be leaving as NULL.
Here are my tables

My way is:

INSERT INTO rating(stars)
SELECT rating.stars FROM rating
INNER JOIN movie
ON rating.mID = movie.mID
WHERE rating.stars='5'
AND movie.director='James Cameron'
AND rating.ratingDate IS NULL;

Why does my query doesn't work as expected? It just returned my empty set.

because where rating.stars = 5 rating.ratingdate is not null.

Hence your select part gets no data to insert.