Bad execution plan after stats update due to temp table A stored procedure query sometimes gets a bad plan after stats update on one of the tables, but can be recompiled to the good plan right after. Same compiled parameters.

Issue seems to come from a small temp table created in the SP and then joined to. The bad plan has a warning on the temp table that the join column has no stats. What gives?

SQL Server 2016 SP1 CU4, with 2014 compatibility level

Bad plan:

Good plan:

Stored procedure

USE AppDB
GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [MySchema].[MySP]
    @MyId VARCHAR(50),
    @Months INT
AS
BEGIN

    SET NOCOUNT ON

    SELECT *
    INTO #MyTemp
    FROM AppDB.MySchema.View_Feeder vf WITH (NOLOCK)
    WHERE vf.MyId = @MyId AND vf.Status IS NOT NULL

    SELECT wd.Col1
         , vp.Col2
         , vp.Col3
    FROM AppDB.MySchema.View_VP vp WITH (FORCESEEK)
    INNER JOIN #MyTemp wd ON wd.Col1 = vp.Col1
    WHERE vp.Col3 > DATEADD(MONTH, @Months * -1, GETDATE())

END

Inner view

USE AppDB
GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE VIEW [MySchema].[View_VP]
AS

    SELECT pp.Col1,
           pd.Col2 AS Col2,
           MAX(pp.Col4) AS Col3
    FROM P_DB..LargeTable pp WITH (NOLOCK)
    INNER JOIN P_DB..SmallTable pd WITH (NOLOCK) ON pp.P_Id = pd.P_Id
    WHERE pp.[Status] IN (3, 4)
    GROUP BY pp.Col1, pd.Col2

Plans

Redacted good plan and bad plan.

Additional information

The FORCESEEK hint was added at the time to try to handle this very same issue and stabilize the plan. And anyway, with or without it, I'd really like to understand what's happening here.

I'm not able to reproduce the issue at will, so it's hard to say if replacing the SELECT INTO with an explicit table would make a difference. However, I believe it should behave the same way.

SELECT
    database_id,
    is_auto_create_stats_on,
    is_auto_update_stats_on,
    is_auto_update_stats_async_on
FROM sys.databases
WHERE
    database_id IN (2, <relevant user databases>)

returns:

  database_id   is_auto_create_stats_on   is_auto_update_stats_on   is_auto_update_stats_async_on
 ------------- ------------------------- ------------------------- -------------------------------
  2             1                         1                         0
  7             1                         1                         1
  37            1                         1                         1

It's clear that this seek is terrible, but the question is why it doesn't do the good seek in the first place.

The query doesn't return 1m rows, the estimates are wrong. There could be slight changes in the output, but the number of rows is always pretty low (maybe hundreds at most).

Even the ones returning relatively many rows generate plans seeking by the Id and never by the status (which is not selective as you can see). I can't seem to reproduce the status seeking plan, no matter what values are compiled. I even tried to add a waitfor delay between the creation of the temp table and the second query, and update stats / recompile in a second session, with no effect either.

The bad plan has a warning on the temp table that the join column has no stats. What gives?

There may be a more esoteric reason for this, but it is more likely a simple statistics creation failure. This might, for example, occur when the task fails to get the memory resources it needs, or when statistics creation is being throttled (too many concurrent compilations). See the Microsoft White Paper Statistics Used by the Query Optimizer in Microsoft SQL Server 2008. You may be able to debug this further by looking at the auto stats Profiler or Extended Events and other events around the same time.

That said, a great deal more information and investigation would be needed to place the blame for the plan selection at the door of the missing temporary table statistics. Even without detailed statistics, the optimizer can still see the total cardinality of the temporary table, and this would seem to be an important factor here.

  ...but can be recompiled to the good plan right after. Same compiled parameters.

The @Months parameter might be the same, but the number of rows in the temporary table (from the unknown view View_Feeder) is different, and the plans provided do not show the value of @MyId.

Going from the information available: The 'good' plan (estimates only, no performance data presented) is based on a temporary table containing 4 rows. The 'bad plan' is based on a temporary table with 114 rows. Certainly a lack of density and histogram information may not be helpful, but it is easy to see how the optimizer might choose a different plan for 4 vs. 114 rows, albeit ones with unknown density and distribution.

If estimates on plan operators not dependent on the temporary table are wildly off, this is a strong signal that the current main-table statistics are not representative of the underlying data. The lack of information in the question makes this impossible to assess.

Nevertheless, it is possible to see that the optimizer is being asked to choose between sub-optimal alternatives here. Neither plan presented represents an 'obviously good' choice, since both involve lookups (lack of a 'covering' index) and late filtering (see next). Lookups in particular have a high cost associated with them, which depends sensitively on cardinality estimates.

Using a view restricts the optimizer and hinting choices:

The view contains a GROUP BY that prevents the predicate vp.Col3 > DATEADD(MONTH, @Months * -1, GETDATE()) from being pushed down, even though the transformation would be valid in this very specific case.

In-lining the view to the query would provide a natural way to filter the date/time column earlier (though the question does not state whether refactoring the query is an option).

It is not possible to hint an index on a view, and FORCESEEK simply asks the optimizer to find any index-seeking plan (not necessarily using the index you would prefer). Removing the view would likewise remove this restriction.

Allowing the predicate to push down should open up indexing opportunities on the large table as well. For example:

CREATE INDEX give_me_a_good_name
ON dbo.LargeTable (Col1, [Status], Col4)
INCLUDE (P_Id);

...provides a good access path for the rewritten query:

DECLARE @Date datetime = DATEADD(MONTH, @Months * -1, GETDATE());

SELECT
    MT.Col1,
    ST.Col2,
    MAX(LT.Col4)
FROM #MyTemp AS MT
JOIN dbo.LargeTable AS LT
    ON LT.Col1 = MT.Col1
JOIN dbo.SmallTable AS ST
    ON ST.P_id = LT.P_Id
WHERE
    LT.[Status] IN (3, 4)
    AND LT.Col4 > @Date
GROUP BY
    MT.Col1,
    ST.Col2
OPTION (RECOMPILE);

Another consideration is the effect of temporary table and statistics caching as described in my articles Temporary Tables in Stored Procedures and Temporary Table Caching Explained. If a good plan depends on the current content of the temporary object, an explicit UPDATE STATISTICS #MyTemp; before the main query, and adding OPTION (RECOMPILE) to the main query may be a good solution.

Alternatively, if one particular plan shape is always optimal for this query, you have many options available, including a variety of hints, plan guides, and query store plan forcing. You might find that using a table variable instead of a temporary table is the better choice, since it favours the low cardinality case, and does not provide (or rely on) statistics.

In summary, there are several general improvements that should be undertaken before worrying about the reasons for (of effect of) occasional missing statistics on the temporary table:

Ensure statistics are representative and useful to the optimizer
Check actuals vs. estimates for a range of parameter values
Provide good data access path(s) for the query by improving existing indexes
Remove the view if possible; or consider a 'parameterized view' (inline table-valued function) with an explicit predicate for the date/time parameter.
Ensure that automatic statistics creation is not being throttled unnecessarily
Use the right sort of temporary object for the task (table vs. variable)
Consider RECOMPILE if plan choice is very sensitive to parameter values
Add UPDATE STATISTICS and RECOMPILE if cached statistics are a problem
Consider a temporary table with a primary key instead of SELECT INTO if that provides useful information to the optimizer
Review the schema to ensure the optimizer has the most information possible (e.g. foreign keys, other constraints)
Consider the suitability of filtered indexes/statistics based on your knowledge of the data
Don't sprinkle NOLOCK hints to increase performance

Repro

The following was built from the limited information available in the redacted execution plans provided:

DROP VIEW IF EXISTS dbo.View_VP;
DROP TABLE IF EXISTS dbo.SmallTable, dbo.LargeTable, #MyTemp;
GO
CREATE TABLE LargeTable (P_Id integer NOT NULL, Status integer NOT NULL, Col1 integer NOT NULL, Col4 datetime NOT NULL);
CREATE TABLE SmallTable (P_id integer NOT NULL, Col2 integer NOT NULL)
CREATE TABLE #MyTemp (Col1 integer NOT NULL);
GO
CREATE VIEW dbo.View_VP
AS
    SELECT
        pp.Col1,
        pd.Col2 AS Col2,
        MAX(pp.Col4) AS Col3
    FROM LargeTable pp
    JOIN SmallTable pd
        ON pd.P_id = pp.P_Id
    WHERE
        pp.[Status] IN (3, 4)
    GROUP BY
        pp.Col1, pd.Col2;
GO
CREATE UNIQUE CLUSTERED INDEX PK_SmallTable ON dbo.SmallTable (P_id)
CREATE CLUSTERED INDEX ix_P_id ON dbo.LargeTable (P_Id)
CREATE INDEX ix_Col1 ON dbo.LargeTable (Col1)
CREATE INDEX ix_Status ON dbo.LargeTable ([Status])
GO
UPDATE STATISTICS dbo.LargeTable WITH ROWCOUNT = 32268200, PAGECOUNT = 322682;
UPDATE STATISTICS dbo.SmallTable WITH ROWCOUNT = 6349, PAGECOUNT = 63;
UPDATE STATISTICS #MyTemp WITH ROWCOUNT = 4;

The query is:

DECLARE @Months integer = 6;

SELECT wd.Col1
         , vp.Col2
         , vp.Col3
    FROM dbo.View_VP vp WITH (FORCESEEK)
    INNER JOIN #MyTemp wd ON wd.Col1 = vp.Col1
    WHERE vp.Col3 > DATEADD(MONTH, @Months * -1, GETDATE())

Without real statistics on the base tables, this favours plans close to the 'bad plan' example (using ix_Status):

This suggests that information about the selectivity of Col1 is an important factor in the optimizer's choice.


Can `sys.aud$` recycle itself? In Oracle 11, what happens if I leave sys.aud$ to get bloated up to soak up the whole SYSTEM TABLESPACE?

Does it start rewriting the oldest records?  Can Oracle be configured to do so?

Does it stop auditing? Can Oracle be configured to overwrite the oldest records instead?

Does the DBMS crash? It seems not, it will prevent further actions until an operator handles the situation?

The audit table does not 'clean' itself.  And there is not just a simple setting, like an init parameter, to do so.  You have to set up and schedule jobs to do that, and @eckes gave you a link to the documentation.  That same package (dbms_audit_mgmt) also provides a procedure for moving the audit table to another tablespace, getting it out of SYSTEM.  I strongly recommend you do so.  I just create a new tablespace dedicated to the audit table(s) and their indexes.


Difference between TDE/VPD/Data Masking/OLS - Oralce I've come across many kinds of data masking techniques used in Oracle database.. some are licensed some are included in enterprise edition itself.. leaving the licenses thing behind as it is restricted to ask about it here..

I would like to know what is the difference between

Transparent Data Encryption

Virtual Private Database

Oracle Label Security

Data Masking pack

Oracle Vault

Ultimately each one of the above does the same job - mask the data right?

What are the differences between them??

If data masking pretty much only overwrites the data which I actually need to mask, won't a simple SQL update statement do that trick on a particular table? Why buy a tool for that?

Thanks!

Actually they don't do the same.

TDE encrypts the data in the database, you need to configure the wallet and have it open before you can see/use the data after that.

VPD is an Access Control Mechanism, it allows you to define sub-datasets that will be owned by different users, allowing them to see/use only the data that they actually own (even if the same set of tables are shared among different VPDs)

To have more detailed information about each tool, please review the documentation on https://docs.oracle.com/en/database/


Polybase with SQL Server 2019 on Linux or Containers I would like to install SQL Server 2019 on Linux either a full Linux server (Ubuntu 18.04) or on a Docker container with Polybase.  Installing MSSQL 2019 on a Linux Server or a container is relatively easy.  It's the Polybase bit that I cannot figure out.
Using the instructions at this Microsoft Docs site:  https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-linux-setup?view=sql-server-ver15
There is a reference to install Polybase on Ubuntu, which should be applicable in both cases (server or container).  When I attempted to install the Polybase packages on an Ubuntu server I got this result:
sudo apt-get install mssql-server-polybase
Installed without any issue
sudo apt-get install mssql-server-polybase-hadoop
The following packages have unmet dependencies: mssql-server-polybase-hadoop : Depends: mssql-zulu-jre-11 but it is not installable E: Unable to correct problems, you have held broken packages.
I could not find a solution to installing the package mssql-zulu-jre-11 on a Linux Server.
In a container, I logged in as a Root user and attempted the commands above:
apt-get install mssql-server-polybase
E: Unable to locate package mssql-server-polybase
I did verify that within the container the Linux instance has the correct sources for apt to find this package.  I am completely confused as to how to get Polybase running with SQL Server 2019 on Linux.  Have any of your had success?  Any help would be greatly appreciated!

After a lot of trial and error and a lot of research, I found a solution.
I tried both Ubuntu 16.04 and Ubuntu 18.04.  This installation approach works for both, but I only was able to get Polybase to work under Ubuntu 18.04.
I performed a fresh install of Ubuntu and SQL Server.  As with every attempt, everything went well except the installation of the mssql-server-polybase-hadoop package. It failed on attempting to find and install the mssql-zulu-jre-11 package.
However, I downloaded the .deb package and manually installed it from these locations:
Ubuntu 16.04:  https://packages.microsoft.com/ubuntu/16.04/prod/pool/main/m/mssql-zulu-jre-11/mssql-zulu-jre-11_11.43.56-1_amd64.deb
Ubuntu 18.04:  https://packages.microsoft.com/ubuntu/18.04/prod/pool/main/m/mssql-zulu-jre-11/mssql-zulu-jre-11_11.43.56-1_amd64.deb
After installing it with dpkg -i, I was able to then install the mssql-server-polybase-hadoop package.
This process allowed me to configure and use Polybase.  (Only with Ubuntu 18.04)


Does MySQL guarantee atomic upgrade from read lock to write lock? If a client session is currently holding a read lock (to allow concurrent reads while already preventing writes), and then acquires a write lock on the same table(s), does MySQL guarantee that the lock is continuous?

The Manual states:

  If a session issues a LOCK TABLES statement to acquire a lock while already holding locks,
  its existing locks are released implicitly before the new locks are granted.

And as far as I can see, it states nothing explicitly about "upgrading" a lock from read to write. As in

mysql> LOCK TABLE `awesome` READ;
mysql> SELECT ... FROM `awesome` ...
mysql> LOCK TABLE `awesome` WRITE;  # lock continuous, or dropped+reacquired?
mysql> INSERT INTO `awesome` ...
mysql> UNLOCK TABLES;

If I'm being paranoid about the verbiage in the documentation, I have to assume that there will be a brief moment between the two lock states where my session temporarily holds no lock at all, during which a concurrent session could potentially modify the table, bringing it into a state inconsistent with what I've seen in the SELECT phase.

I cannot rely on exceptions thrown by duplicate key violations, as there is no useful unique key (and it would probably quadruple the size of the table if MySQL even supported it). For the same reason, transactions wouldn't really help me, but I cannot use them anyways, as the table in questions is using the MyISAM engine for performance reasons. Locking really seems like my best bet, only the documentation seems a little conspicuous on the transitioning details.

And insights on how MySQL internally handles transitioning from read lock to write lock if the set of tables is the same?

The big-wooden-hammer-method would of course be to issue a single write lock up front. But that would force all selects to wait, obviously. OTOH, as I understand it, an infinite number of sessions can read-lock the same table concurrently (implicitly or explicitly) with no implications on select performance at all. The only thing the read lock does is preventing updates from hitting the table, which is exactly what I need in the first stage.

Indeed, the part of the manual you quoted does just what it says:

-- Connection #1
LOCK TABLE mytable READ; -- lock is acquired

-- Connection #2
LOCK TABLE mytable WRITE; -- execution is suspended, lock is NOT acquired

-- Connection #1
LOCK TABLE mytable WRITE; -- execution is suspended
-- READ lock was released, allowing Connection #2 to acquire the WRITE lock
-- Connection #2 resumes

Solution: do switch to InnoDB and use transactions. Performance of the InnoDB engine improved a lot in the recent versions.

-- Connection #1
BEGIN;
SELECT * FROM mytable LOCK IN SHARE MODE; -- lock is acquired

-- Connection #2
BEGIN;
SELECT * FROM mytable FOR UPDATE; -- execution is suspended, lock is NOT acquired

-- Connection #1
SELECT * FROM mytable FOR UPDATE;
-- deadlock is detected, Connection #2 is rolled back


Which column to index for this query? select DISTINCT id_request,username_request
from darkhast_follower
where darkhast_follower.id_request != '9762952594'
  AND darkhast_follower.id_request != 'null'
  AND darkhast_follower.status !=1
  AND NOT exists ( select *
                   from log_follow
                   where log_follow.other_id = darkhast_follower.id_request
                   AND log_follow.id= '9762952594')
LIMIT 5

Which column should be indexed? Or what is the best query to replace this?

Do not say

!= 'null'

Instead, say

IS NOT NULL

That is, unless you are actually storing the 4 characters "null" in a VARCHAR?

Indexes:

log_follow:  INDEX(other_id, id)   -- for the subquery
darkhast_follower:   no index is useful for this query

The query, as you have it, will scan darkhast_follower until it finds 5 rows matching the all the WHERE clauses. In doing so, it will check log_follow -- This task will be a table scan (slow) if you don't have a suitable index.  Else it will be fast.


Is a UNION query equivalent with or without DISTINCT in subqueries? Consider the following two queries.

SELECT Col1, Col2
FROM TblA

UNION

SELECT Col1, Col2
FROM TblB

and

SELECT DISTINCT Col1, Col2
FROM TblA

UNION

SELECT DISTINCT Col1, Col2
FROM TblB

These are logically equivalent. My question is if they are treated the same by the database engine. Does SQL Server recognize the redundancy and ignore the DISTINCT operator?

No, they are not necessarily "treated the same by the database engine." A test below shows that you might get different query plans.

In many cases, the difference between query plans may not matter for you. But in some (likely rare) cases it could matter significantly. For example, if SQL Server has a very poor cardinality estimate for one branch of your UNION, calculating the unique rows within that branch could result in a spill that would not have happened if the query only calculated the final set of unique rows.

SELECT @@version
-- Microsoft SQL Server 2017 (RTM-CU1) (KB4038634) - 14.0.3006.16 (X64)

SELECT create_date FROM sys.databases
UNION
SELECT create_date FROM sys.databases

SELECT DISTINCT create_date FROM sys.databases
UNION
SELECT DISTINCT create_date FROM sys.databases


innodb_log_file_size not upating even after changing my.cnf Hello team this is my first post here,

Server version: 10.0.31-MariaDB-0ubuntu0.16.04.2 Ubuntu 16.04

I am trying to import large db  and half way I get the error to increase Size of InnoDB Redo Log Files so I do follow this steps

mysql -uroot -p... -e"SET GLOBAL innodb_fast_shutdown = 0"
service mysql stop

change my.cnf to

# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

# innodb
innodb_buffer_pool_size = 256M
innodb_log_file_size = 32M

and then restart mysql

mv /var/lib/mysql/ib_logfile[01] /tmp
service mysql start

but still I see

MariaDB [(none)]> SHOW VARIABLES LIKE 'innodb_log_file_size';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| innodb_log_file_size | 50331648 |
+----------------------+----------+

my values are not updated ? can you please let me know is there anything I am missing ?

The buffer_pool_size seems rather low.  How much RAM do you have?

innodb_log_file_size can be virtually any size.  Make it as big as innodb_buffer_pool_size.

But, and this may be the problem, the setting must be in a section started by

[mysqld]

See also: https://www.percona.com/blog/2016/05/31/what-is-a-big-innodb_log_file_size/


Unable to find server name of SQL Server After installing SQL Server 2008 R2, it wants a server name.

I tried SQLCMD -L on cmd, but it shows the error below:

  Data source name not found and no default driver specified

What am I doing wrong?

Any kind of help will be appreciated.

You can go with two methods as given in answers to the Stack Overflow question How to find server name of SQL Server Management Studio.

User PoweredByOrange:
Open up SQL Server Configuration Manager (search for it in the Start menu). Click on SQL Server Services. The instance name of SQL Server is in parenthesis inline with SQL Server service. If it says MSSQLSERVER, then it's the default instance. To connect to it in Management Studio, just type  . (dot) OR (local) and click Connect. If the instance name is different, then use .[instance name] to connect to it (for example if the instance name is SQL2008, connect to .\SQL2008).
Also make sure SQL Server and SQL Server Browser services are running, otherwise you won't be able to connect.

or:

User sansa:
Run this Query to get the name
SELECT @@SERVERNAME


Count how many records belong to 2 categories in a n to n relation I have a database containing articles whose schema is more or less the following:

COLLECTIONS ( PK id )
> 1 : n >
TOPICS ( PK id, FK collection_id )
> n : n >
TOPICS_TO_ARTICLES( FK topic_id, FK article_id )
> n : n >
ARTICLES ( PK id)

What I need is to find a way to understand how articles and collections are related, and one of the metrics is "how many articles belong to "collection 1" AND "collection 2". I thought I found a working query, but I think I'm missing something. Here is my query:

SELECT
  COUNT(*) AS total
FROM
  topics_to_articles AS TTA
INNER JOIN
  topics AS T ON T.id = TTA.topic_id
INNER JOIN
  articles AS A ON TTA.article_id = A.id
WHERE
  T.collection_id = cat_1_id AND A.status = 1 AND TTA.article_id IN(
  SELECT
    TTA2.article_id
  FROM
    topics_to_articles AS TTA2
  INNER JOIN
    topics AS T2 ON T2.id = TTA2.topic_id
  INNER JOIN
    articles AS A2 ON TTA2.article_id = A2.id
  WHERE
    T2.collection_id = cat_2_id AND A2.status = 1
)

I was pretty sure about this query being ok, but I noticed that if I invert the two "collection_id" variables (like c1 = 1 and c2 = 2 and then c2 = 1 and c1 = 2) I obtain two different results.

Is there a way to get that info?

When you join topics to topics_to_articles to articles, each article may be tied to multiple topics. And, multiple topics may be associated with each collection. So, you may be counting the same article multiple times.

For example:

Collection A is tied to topics a,b,c
Collection B is tied to topics b,d,e
Article 1 is tied to topics a,b,e
Article 2 is tied to topics a,c
Article 3 is tied to topics b, d, e

If cat_1_id is 1 and cat_2_id is 2:

the IN clause will contain article IDs 1 and 3 (while the query will list them more than once, the IN clause doesn't care how many times an ID is listed, just whether it's there or not)
The main query will count:

article 1, topic a
article 1, topic b
article 1, topic c
article 3, topic b

If cat_1_id is 2 and cat_2_id is 1:

the IN clause will contain article IDs 1, 2, and 3
The main query will count:

article 1, topic b
article 1, topic e
article 3, topic b
article 3, topic d
article 3, topic e

The first query gives you 4; the second query gives you 5; you should really get 2 (because there are two distinct articles returned).

And, that word is the solution - DISTINCT

Change your two lines to:

SELECT
  COUNT(DISTINCT A.article_id) AS total

This way, when the same article_id shows up multiple times, it will still only be counted once.


Column To generate Flag Values for use in bitwise comparisons We are in the process of creating a new application where various tables are used to hold enumerations.  Since the IDs in these tables will be used in bitwise comparisons, we'd like to ensure new rows inserted will automatically use the decimal number that corresponds to the next bit being "set".

For instance, if we had an alarm clock application, we might include a "Weekdays" table which could be used in the following way:

CREATE TABLE dbo.Weekdays
(
    WeekdayID INT NOT NULL
        CONSTRAINT PK_Weekdays PRIMARY KEY CLUSTERED
    , WeekdayName VARCHAR(20)
);

INSERT INTO dbo.Weekdays
VALUES (1, 'Sunday')
    , (2, 'Monday')
    , (4, 'Tuesday')
    , (8, 'Wednesday')
    , (16, 'Thursday')
    , (32, 'Friday')
    , (64, 'Saturday');

CREATE TABLE dbo.AlarmsDefined
(
    AlarmID INT NOT NULL
        CONSTRAINT PK_AlarmsDefined PRIMARY KEY CLUSTERED
    , Weekdays INT NOT NULL
);

INSERT INTO dbo.AlarmsDefined (AlarmID, Weekdays)
VALUES (1, 50);

SELECT W.WeekdayName
FROM dbo.AlarmsDefined AD
    INNER JOIN dbo.Weekdays W ON (AD.Weekdays & W.WeekdayID) = W.WeekdayID

The results of the above SELECT query:

I'd like to be able to define the Weekdays table in the above example such that if we added another row to the table, the next WeekdayID would be automatically set as 128, which is the next decimal number that can be used in bitwise comparisons.

This of course can be accomplished by putting in the values by hand into the primary key column.

But is there a way to specify an automatically incrementing value which would adhere to the flag values? For example these would be the unique PK values generated:

ID
--
1
2
4
8
16
32
...

and the trigger would automatically look at the last value and generate the next in the sequence needed?

I would use something like this:

CREATE TABLE #test
(
    id INT IDENTITY(0,1) NOT NULL
    , val AS (POWER(2,id)) PERSISTED
    , CONSTRAINT pk_test PRIMARY KEY
        (
            val
        )
);
GO

INSERT #test DEFAULT VALUES;
GO 15

SELECT * FROM #test;


Is it possible to have a shared secondary Mongo DB instance between two replica sets..? I have two separate mongo db instances say A and B running in a remote location and i want to maintain a collective synchronized copy/backup of these instances in a separate mongo instance say C. So i just want to know is it possible to configure two separate replica sets for A and B instances having C as common secondary instance for both the sets..?

No you cannot have shared secondary as the sync is based on oplog replay plus a single monogd cannot be a member of 2 replica set.

The only way to have a single machine be a member of multiple replica sets is to run multiple mongod servers on that machine.


Best procedure to measure query optimization effects? I could not find valuable answers or explanations to the following question:

How should a procedure to test query improvements effects look like? Using SSMS I am not absolutely sure about the steps.

Currently I do the following:

restore the production DB to an isolated test server, to cut off external influence / usage that falsifies results by additional server usage.
rebuild all indexes and statistics.

Then I repetitively execute the following steps:

SET STATISTICS IO ON - to get information about IO usage
DBCC FREEPROCCACHE; CHECKPOINT; - to free plan cache and consider e.g. index changes when repeating / testing the changes
DBCC DROPCLEANBUFFERS; - to avoid usage of cached data to make sure I have a fair comparison on each query execution
run the query

After each execution I quickly import all the statistics outputs into an excel sheet, to compare the changes to originals (see picture) as well as the runtime, checking if the number of returned rows still match the original and inspect the new execution plan to see the effects.

Unfortunately this process takes a lot of time because each execution with cleared caches takes about 15 minutes to complete.

Is this procedure fine or should I consider changes?
Is clearing out the data cache really necessary or is it still a fair comparison to execute the query with the data cache since there is no other users active that could make changes to that cache?

At the risk of stating the obvious, first decide what you are optimizing for. Almost always it is elapsed time, though you may be trying to reduce CPU use (to reduce license fees) or IO (to avoid a disk upgrade, say). Then find a way to measure that thing, holding other variables constant. Be sure your chosen measure actually measures what you target and not a bunch of other stuff too.

For example, running a query in SSMS and looking at the status bar execution time will show round-trip network time plus plan compilation time plus IO time plus query execution time plus client caching time plus SSMS rendering time. For large result sets and slow networks the overhead and variability may swamp any actual improvement.

Having a completely clean bufferpool and plan cache for each itteration will give great reproducability. It is unlikely to be the actual production scenario, however. Since production is what you're trying to make fast then I'd suggest you test in a production-like environment, with similar memory & disk, plans compiled and the working set in the bufferpool.


BACKUP LOG cannot be performed because there is no current database backup I have a SQL Server 2005 database using the full recovery model. If I execute the following commands:

ALTER DATABASE test SET RECOVERY SIMPLE;
ALTER DATABASE test SET RECOVERY FULL;

or

BACKUP LOG test WITH  truncate_only;

Then try to run a log backup:

BACKUP LOG test TO  DISK = 'backupfile.bak'

I receive the error message:

  BACKUP LOG cannot be performed because there is no current database backup.

Question:

How do I check that I will not be able to take a log backup, without running a backup log command?

last_log_backup_lsn will be null in sys.database_recovery_status.


SQL SERVER 2012 error am trying to restore a backup, the backup is from a database in the current instance, want to restore the backup with a different name for test purposes, this was possible in 2008 R2 but in 2012 its not possible, anybody know a work around

You may use t-sql for restore. Make sure that you create new files instead of replacing your existing database.

Try someting like this:

    USE [master]
    RESTORE DATABASE [new database name] FROM
    DISK = N'path to your full backup' WITH
    MOVE N'logicaldatafilename' TO N'path to your mdf file',
    MOVE N'logicallogfilename' TO N'path to your ldf file',
    NOUNLOAD,  STATS = 5

GO

I hope it helps.
Janos


entries needs INDEX(valid, tenent_id)
users needs INDEX(active, user_id)

More discussion (Index Cookbook).

More specifics

LEFT probably prevents the optimizer from picking which table to start with.  Assuming the LEFT is important, let's break the query in two:

FROM entries WHERE e.tenant_id=? AND e.valid=1

The link above explains that the optimal index starts with anything with '=', so (tenant_id, valid) in any order.  (I suggested one of them.)

Then it moves on to the other table:

LEFT JOIN users u
    ON u.user_id = ...
WHERE u.active=1

Hence, (active, user_id) in any order.

(Sorry, I need to augment the Cookbook with more discussion of JOIN.)

If you could get rid of LEFT, then a different set of indexes might be useful:

(active)
(valid, user_id, tenant_id) -- in any order

Having both pairs of indexes would let the optimizer pick which is better.  However, some consolidation is desirable (again, see the link).  These two would suffice for either table order.  Now the column order is important:

(active, user_id)   -- also handles (active)
(valid, tenant_id, user_id)  -- also handles (valid, tenent_id)

More on your title question of "not sure if index is being used".  In the EXPLAIN, NULL and 116710 imply that it is not.  But index and Using index imply that it is.  The tie-breaker is Using join buffer (Block Nested Loop) which says that it is loading the index into RAM to use it.  (This is a case where join_buffer_size matters.)

The 4-column index you had was somewhat useful, but it

did not have all the needed columns (active) to be covering
had an extra column (group_id)
was in a sub-optimal order: the extra column came before the all the needed columns, so valid was not as useful as it could be.


What is WITH QUERY EXPANSION MODE in MySQL Fulltext Search MATCH (col1,col2,...) AGAINST (expr [search_modifier])

search_modifier:
{
     IN NATURAL LANGUAGE MODE
   | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
   | IN BOOLEAN MODE
   | WITH QUERY EXPANSION
}

Could you please explain to me what QUERY EXPANSION is, and what does MySQL do with this mode?

The short answer to your question is that query expansion enhances the original search term by adding extra, relevant, information to the search term. Mis-quoting an example from Derek's link:

  An example could be searching for books by Georges Simenon about
  Maigret, when a user is not sure how to spell “Maigret”. A search for
  “Megre and the reluctant witnesses” finds only “Maigret and the
  Reluctant Witnesses” without query expansion. A search with query
  expansion finds all books with the word “Maigret” on the second pass.

This tutorial should make things clearer:

Using MySQL Query Expansion

Summary: in this tutorial, you will learn about MySQL query expansion to widen the search results based on automatic relevance feedback.

Introduction to MySQL Query Expansion

In some cases, users want to search for information that based on the knowledge that they have. Users use their knowledge to define keywords to search for information, and typically those keywords are too short. To help users to find what they want based on the too-short keywords, MySQL full-text search engine introduces a concept called query expansion.

The query expansion is used to widen the search result of the full-text searches based on automatic relevance feedback (or blind query expansion). Technically, MySQL full-text search engine performs the following steps when the query expansion is used:

First, MySQL full-text search engine looks for all rows that match
the search query.
Second, it checks all rows in the search result and finds the
relevant words.
Third, it performs a search again but based on the relevant words
instead the original keywords provided by the users.

From application perspective, you can use the query expansion when the search results are too few. You perform the searches again but with query expansion to offer user more information that are related and relevant to what they are looking for.

To use the query expansion, you use the WITH QUERY EXPANSION search modifier in the AGAINST() function. The following illustrates the syntax of the query using the WITH QUERY EXPANSION search modifier.

SELECT column1, column2
FROM table1
WHERE MATCH(column1,column2)
      AGAINST('keyword',WITH QUERY EXPANSION)

MySQL Query Expansion Example

Let’s take a look at an example to see how MySQL query expansion works.

We will use the productName column of the products table to demonstrate the query expansion feature. First, we enable the full-text search feature for this column.

ALTER TABLE products
ADD FULLTEXT(productName)

Second, we search for product whose product name contains the 1992 keyword without using query expansion.

SELECT productName
FROM products
WHERE MATCH(productName) AGAINST('1992')

Gives

+-----------------------------------+
| productName                       |
+-----------------------------------+
| 1992 Ferrari 360 Spider red       |
| 1992 Porsche Cayenne Turbo Silver |
+-----------------------------------+
2 rows in set (0.00 sec)

As you see, the search results has 2 products whose product name contains 1992.

Third, we can widen the search result by using query expansion as the following statement:

SELECT productName
FROM products
WHERE MATCH(productName)
      AGAINST('1992' WITH QUERY EXPANSION)

Gives

+-------------------------------------+
| productName                         |
+-------------------------------------+
| 1992 Porsche Cayenne Turbo Silver   |
| 1992 Ferrari 360 Spider red         |
| 2001 Ferrari Enzo                   |
| 1932 Alfa Romeo 8C2300 Spider Sport |
| 1948 Porsche 356-A Roadster         |
| 1948 Porsche Type 356 Roadster      |
| 1956 Porsche 356A Coupe             |
+-------------------------------------+
7 rows in set (0.00 sec)

We got more rows in the search result when we used query expansion. The first two rows are the most relevant and the other rows come from the relevant keyword that are in the first two rows e.g., Ferrari.

Notice that blind query expansion tends to increase noise significantly by returning non-relevant results. It is highly recommended that you use query expansion only when the searched keyword is short.

In this tutorial, we have introduced you to MySQL query expansion to widen the search results when the keywords provided by users are short.

Source: Using MySQL Query Expansion


How the transaction logs are maintained in MSSQL RDS? Just curious, I was asked that if RDS takes transactional log / incremental log backup for point in time recovery, does RDS shrink the logs too?

In MSSQL instance, we shrink logs if transactional logs are full. Do we need to monitor the transactional logs in RDS?

If RDS takes backup from transactional log, is it safe to shrink the logs manually if it goes beyond expectation?

RDS is closed source and hidden, but assuming that it is like PostgreSQL in that respect:

The PostgreSQL transaction log is managed differently. It is split into segments of 16MB (by default). As soon as the current segment is full, the next one is used. The full transaction log segment is archived and deleted. These archived logs are not managed by PostgreSQL, and you keep them around as long as you want to be able to restore the database.

So it makes no sense to say that the transaction log gets full or that it gets shrunk, all this is not necessary.


What is difference between starting Instance with -T3608 and with -f? I simulated a corruption of model database on test environment just for learning purposes. While reading others blogs and articles about it I stuck on the question, what is difference between starting SQL Server Service with undocumented -T3608 and -f flags?
When I tried to start service with -f, then got error message and service didn't start. However, in case of -T3608, it started smoothly. For -T3608 flag, I read that, -T3608 starts all databases without performing recovery and -f starts instance with minimal configurations. What is minimal configuration, I can't find info about it.

Say that you allocated something way way too big for the memory in the machine. Locks, for instance. SQL Server tries to create that much memory, but it isn't available, so SQL Server doesn't start. This is the scenario there -f comes in. Nowadays, there are some safety built into sp_configure to avoid ridiculous values (like way too low max server memory), so it might not be easy to actually get to that point in the first place. Another thing that -f does is to create tempdb where master.mdf is, instead of where you said it should be created (imagine a crashed tempdb disk - and SQL server can't live without tempdb).
Above is very different from not performing startup recovery of the databases at startup, which is what 3608 means.


Design datastore persistence based on state of the Entity I have an entity Content to persist in the database. The entity has an attribute LifeCycleState that denotes the present state of the content.

For the sake of elaborating further, assume we have DRAFT, REJECTED, ARCHIVED life cycle states. Corresponding to each of these, there is some detailed information that is supposed to be persisted in the database. Such as:

DRAFT {creationDetails}
REJECTED {creationDetails, updateDetails, rejectReason}
ARCHIVED {creationDetails, updateDetails, archivalReason}

Moreover, we are currently using a NoSQL datastore(mongodb) to persist the Content entity.

The question here in terms of designing the schema is based on the fact that the content would be in one state at a time, which is when any other state information would be useless to read.

For example, a content moves from state DRAFT to REJECTED to ARCHIVED, the users would just be interested in the archivalReason and would not worry about the intermediate state. [Not of interest to the question, there is a status change history that we are persisting anyway for bookkeeping the writes.]

Our current implementation defines an attribute stateSpecificDetails under Content and dumps all the attributes pertaining to various states within it, but that seems like an unwanted clutter to a single attribute knowing the fact that only one of such attribute would actually be set based on the state transitions.

Is there are a cleaner way of defining the entity or creating a separate collection for such content state based persistence?

Mongo allows the content of a record to change. You can add any values you need and remove any you don't each time you persist a record.

The application could have an object class for each state. To mutate a record read it into an instance of whatever class corresponds to its current state. Then map the values you intend to retain to an object of the new state. This new-state object will not have attributes for the values which are no longer relevant. Now persist the new object. The on-disk representation will have only those values applicable to the new state.

Whether you have a collection per state or hold all the records in a single collection is, to a large extent, an operational concern. One risk with separate collections is that the system may fail after the new record is written but before the old one is removed, leaving duplicates. You'll need some clean-up logic on start-up to account for this.


Using exception with raise_application_error I'm developing with Oracle 11g. How can I use a named exception in RAISE_APPLICATION_ERROR like this:

DECLARE
  my_fault EXCEPTION;
  PRAGMA EXCEPTION_INIT (my_fault, -20000);
BEGIN
  RAISE_APPLICATION_ERROR(my_fault, 'my error message');
END;

But I got an error. I have to use the error code explicity.
The Oracle Database Documentation says:

  Restriction on exception

  You can use exception only in an EXCEPTION_INIT pragma, RAISE statement, RAISE_APPLICATION_ERROR invocation, or exception handler.

I'm confused.

There are two ways to raise exceptions in Oracle.  If you want to specify your own message, you wouldn't declare a local variable of type exception.  You'd simply put the error code in your raise_application_error call

BEGIN
  raise_applicaton_error( -20001, 'Some message' );
END;

If you want to declare a local exception type, then you wouldn't use raise_application_error to throw it.  You'd just use raise.  But then you won't be able to specify a custom message.

DECLARE
  my_fault EXCEPTION;
  PRAGMA EXCEPTION_INIT (my_fault, -20000);
BEGIN
  RAISE my_fault;
END;

If you just don't like hard-coding the error code in your raise_application_error call, you can put that in a local integer variable

declare
  myfault_code integer := -20001;
begin
  raise_application_error( myfault_code, 'Some error' );
end;


RDS import from SQL backup I'm migrating a SQL 2008 database from a corporate data center to an RDS intance on AWS.

So far I have performed a native backup which gave me a .bak file

Is it possible to simply upload this to S3 and 'restore' the .bak to a new RDS instance?

What's the best approach to take?

Yes you can restore native backups and AWS has gone to the trouble of writing a very complete tutorial on how to backup from and restore to a RDS SQL Server instance.

restore backup

exec msdb.dbo.rds_restore_database
        @restore_db_name='database_name',
  @s3_arn_to_restore_from='arn:aws:s3:::bucket_name/file_name_and_extension';

There are some limitations to keep in mind and they have explained them all in detail. Some of those limitations are database size, and the types of instances you can restore to: The micro rds sql server instance for example doesn't support restores or backups.

Also keep in mind the difference in versions between what you have locally and the RDS version you are restoring to. Amazon offers SQL Server 2008R2 and up.

https://aws.amazon.com/rds/sqlserver/

Craig


locked from root and can't change privileges on another user I was getting error 1698 when I was trying to log into root

orpheus@Roedelius:~$ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

I did this to fix it

mysql> UPDATE mysql.user SET authentication_string=PASSWORD('woofwoof')  WHERE  User='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

but that didn't work and now I've been getting error 1045 when I try to log into root. The top answer here https://stackoverflow.com/questions/39281594/error-1698-28000-access-denied-for-user-rootlocalhost to my same problem says its because of anonymuos users, but I don't have any.

orpheus@Roedelius:~$ mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

these are all my users, none are anonymous, I think.

mysql> SELECT user, host FROM mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| orpheus          | localhost |
| root             | localhost |
| tom              | localhost |
+------------------+-----------+

so I tried granting all privileges to root and I get error 1698 again

mysql> grant all privileges on *.* to root@localhost identified by 'woofwoof' with grant option;
ERROR 1698 (28000): Access denied for user 'orpheus'@'localhost'

when I try to see grants for root I get this

mysql> show grants for 'root'@'localhost';
ERROR 1141 (42000): There is no such grant defined for user 'root' on host 'localhost'

but for my other user, orpheus, I get this

mysql> show grants for 'orpheus'@'localhost';
+------------------------------------------------------+
| Grants for orpheus@localhost                         |
+------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'orpheus'@'localhost' |
+------------------------------------------------------+

I don't know why this is happening, but I just want to access root again. I would appreciate any help with this.

This solution belongs to the previous version of MySQL.
By logging in to MySQL using socket authentication, you can do it.

sudo mysql -u root

Then the following command could be run.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';


InnoDB compression: memory usage InnoDB offers table compression for Barracuda-type tables and the recommendation seems to be to "adjust" buffer_pool_size. The documentation is rather vague about the definite impact of compression on memory usage. The article on compression internals is just stating:

  a configuration with more memory dedicated to the InnoDB buffer pool tends to run better when using compressed tables than a configuration where memory is highly constrained.

Which translates to "more is better than less" for me - pretty useless in most scenarios.

So in a non-CPU-bound workload where the buffer pool size is significantly smaller than the database size but "meets the needs" (i.e. has an acceptable hit rate), will enabling compression on tables without changing the buffer pool size hurt or benefit the buffer pool hit rate?

The aforementioned compression internals article is elaborating that both, the compressed as well as the uncompressed versions of a page need to be present in memory for the page to be available to the engine, thus potentially increasing memory usage. On the other hand, the eviction algorithm seems to first evict uncompressed pages, leaving the compressed versions in the pool and offering the advantage that more pages might fit into the buffer pool if the system would be able to keep up with decompressing and re-evicting pages.

I addressed this issue 1.5 years ago : innodb_file_format Barracuda

Just from reading the documentation, I was able to figure out mathematically that the smaller you make the key_block_size, the less extra space you need to add to the InnoDB Buffer Pool.

Here is the chart I made up in that earlier post

You have a DB Server with a 8G Buffer Pool
You ran compression with key_block_size=8

8 is 50.00% of 16
50.00% of 8G is 4G
raise innodb_buffer_pool_size to 12G (8G + 4G)

You ran compression with key_block_size=4

4 is 25.00% of 16
25.00% of 8G is 2G
raise innodb_buffer_pool_size to 10G (8G + 2G)

You ran compression with key_block_size=2

2 is 12.50% of 16
12.50% of 8G is 1G
raise innodb_buffer_pool_size to 9G (8G + 1G)

You ran compression with key_block_size=1

1 is 06.25% of 16
06.25% of 8G is 0.5G (512M)
raise innodb_buffer_pool_size to 8704M (8G (8192M) + 512M)

Given the following

BPS = Buffer Pool Size
KBS = key_block_size
BPSFC = Buffer Pool Size For Compression

The ideal size for the Buffer Pool in a perfect world would be

BPSFC = BPS * (16 + KBS) / 16

From this formula, less is more. The smaller KBS is, the smaller the increase in BPS needs to be. Additoinally, it would appear that the CPU would worker a little harder in response to tighter compression. How ?

For KBS=8, evicting one uncompressed page leaves room for 02 compressed pages
For KBS=4, evicting one uncompressed page leaves room for 04 compressed pages
For KBS=2, evicting one uncompressed page leaves room for 08 compressed pages
For KBS=1, evicting one uncompressed page leaves room for 16 compressed pages
For KBS=X, evicting one uncompressed page leaves room for 16/X compressed pages

I can see the CPU working harder to introduce more compressed pages with tighter compression. This could result in evicting LRU compressed pages more often.

If this is the case, it would appear that making the BPS even bigger than the suggested values from these formulas would give CPU some relief as well as reduce the rate of the eviction of compressed pages from the Buffer Pool.

EXAMPLE: From my first chart, an 8GB Buffer Pool would need to be 12GB for key_block_size=8. Making the Buffer Pool larger than 12GB would relieve the CPU of a lot of this heavy lifting (in the form of evicting uncompressed pages). Perhaps doubling the Buffer Pool Size may be need to be explored.

As for your question

  will enabling compression on tables without changing the buffer pool size hurt or benefit the buffer pool hit rate?

I would say it would hurt, no such much because of the hit rate against the compressed pages, but because of the additional house cleaning in terms of the management of uncompressed pages and the CPU usage required for it. This would lower InnoDB performance no matter how much tuning the rest of the Storage Engine gets.


Trouble Enabling Query Caching on MySQL 5.6? Recently I discovered our MySQL 5.6 database is not using any query caching and I wanted to change that.

I added

[mysqld]
query_cache_type = 1
query_cache_size = 50M
query_cache_limit = 2M

to a file called my-default.ini which is located under C:\Program Files\MySQL\MySQL Server 5.6.  I could not find any other .ini file or .cnf file in any directory, and this computer does not have a C:\ProgramData directory where I usually would find it.

Running SHOW VARIABLES LIKE 'have_query_cache' does show have_query_cache    YES.  But SHOW VARIABLES LIKEquery_cache_typeshowsOFF.  AndSHOW STATUS LIKE 'qcache%'` shows all zeros.

Is it possible my my.cnf is missing or deleted?  Do I have to recreate it, or rename the my-default.ini to just my.ini?  I think perhaps a coworker changed the file name or moved it not knowing what it was potentially.

End of the day, how can I ensure that query caching is enabled on my MySQL 5.6 server?

Running it on Windows Server 2003.

You are right. Your problem is actually very simple.
In Windows, mysqld is looking for my.ini, not my-default.ini
SUGGESTION
Open Windows Command Line (as Administrator) and run the following:
cd "C:\Program Files\MySQL\MySQL Server 5.6"
copy my-default.ini my.ini
net stop MySQL
net start MySQL

Keep in mind that MySQL was running with default settings and no my.ini
So, maybe the lines above
[mysqld]
query_cache_type = 1
query_cache_size = 50M
query_cache_limit = 2M

should be the only lines in my.ini


How to get pg_archivecleanup on Amazon Linux 2014.03? I have an Amazon EC2 instance based on Amazon Linux AMI 2014.03. I installed PostresSQL from their repos:

yum install postgresql-libs postgresql postgresql-server postgresql-devel

I've read about Amazon's unexpected upgrade from 9.1 to 9.2, so I added this line to the end of /etc/yum.conf so hopefully I can control any upgrades:

exclude=postgresql*

So far so good. Now I'm setting up continuous archiving and I want to run pg_archivecleanup.  However I can't find the postgresql-contrib package on the Amazon distro, and it's not listed in the packages:

http://aws.amazon.com/amazon-linux-ami/2014.03-packages/

Is there someplace else to get that pre-compiled?  I tried the following to compile it myself:

sudo su -
cd /usr/local/src
wget http://ftp.postgresql.org/pub/source/v9.2.7/postgresql-9.2.7.tar.gz
tar -xzf postgresql-9.2.7.tar.gz
cd /usr/local/src/postgresql-9.2.7
./configure --without-readline # fails until you add --without-readline
cd /usr/local/src/postgresql-9.2.7/contrib/pg_archivecleanup
gmake
gmake install

but the gmake fails:

gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard pg_archivecleanup.o  -L../../src/port -Wl,--as-needed -Wl,-rpath,'/usr/local/pgsql/lib',--enable-new-dtags  -lpgport -lz -lcrypt -ldl -lm  -o pg_archivecleanup
/usr/bin/ld: cannot find -lpgport
collect2: error: ld returned 1 exit status
gmake: *** [pg_archivecleanup] Error 1

Trying to figure out the -lpgport issue led me to some very obscure bug reports here and here. Tracking bugs in Linux itself seems a little extreme just to compile one file.

What is my best option?

Find another source for the contrib package - where?
Find a way to compile this module - how?
Ditch Amazon Linux and start from scratch with an Ubuntu AMI.
Forget pg_archivecleanup and write a script to delete old WAL files.

EDIT

I'm using Elastic Beanstalk with some pretty tight integration to the Amazon AMI. I'm sure it could be made to work with another AMI, but I expect there would be other gotchas.  What about this option, adapted from this post:

# Change to home directory to download the software
cd ~/
# Get the right postgresql package (Redhat 64 Bit)
wget http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-redhat92-9.2-7.noarch.rpm
# Install the package
sudo rpm -ivh pgdg-redhat92-9.2-7.noarch.rpm
sudo yum install postgresql92-contrib

Does that give me the best of both worlds, an Amazon AMI with an official Postgres repo?  If necessary, I could completely re-install Postgres from that RHEL repo.

With the help of this AWS forum thread, I realized that this line in /etc/yum.conf

exclude=postgresql*

excludes not only updates but installs.  Once I commented that out (temporarily), I was able to use

sudo yum install postgresql-contrib

to install postgresql-contrib directly from the Amazon repo--no need for changing the repository or the operating system.


Postgresql: invalid input syntax for integer: "false" Below is my table structure

CREATE TABLE configurations
(
  id serial NOT NULL,
  key text,
  description text,
  value text,
  CONSTRAINT configurations_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

I am inserting data with below query

insert into configurations
(id, key, description, value)
select 1,'key1','D1',25
UNION
select 2,'key2','D2',365
UNION
select 3,'key3','D3','2017-01-01'

But i am getting below error

ERROR:  invalid input syntax for integer: "2017-01-01"
LINE 7: select 3,'key3','D3','2017-01-01'

I don't know why i am getting this error. value "2017-01-01" is a text value and column data type is also text then why i am getting integer specific error.

The UNION result set data types will be determined by logic that favours values that are not string literals -- it is described in detail in the manual, also note that, without explicit type specification, string literals are treated as being of type unknown.

Specifically, these rules apply in your case:

  5. Choose the first non-unknown input type which is a preferred type in
  that category, if there is one.

  ...

  7. Convert all inputs to the selected type. Fail if there is not a conversion from a given input to the selected type.

This basically means that if you have at least one numeric literal value in a particular (in your case fourth) column in your UNIONised query, Postgres will attempt to coerce the values in the same column in other SELECTs into an number, which obviously fails for the character value '2017-01-01'.

Subsequently, if you have at least one character value in that column that cannot be cast into a number, you will have to use explicit character values for the fourth column in all SELECTs:

insert into configurations
(id, key, description, value)
select 1,'key1','D1', '25'
UNION
select 2,'key2','D2', '365'
UNION
select 3,'key3','D3','2017-01-01'

H/T to Andriy M for his comment that prompted me to read the manual more thoroughly.


SSMS How do I open a new Query Window using Same Connection Instance of current Window Coming from Oracle I have discovered that SSMS connects slightly different than Oracle's SQL Developer.  Oracle you click ONE connection and then all tabs you open then use that Connection.  SSMS creates a completely new Connect/Instance for each window/tab I open.  I don't want this.  TWO pet peeves come to mind with this behavior I am trying to get around.

  View affect of Script before COMMIT in another Window

I want to be able to run a script in one window... and in another Window have the ability to Query the Non-Committed data to simply view the results... Currently I have to use the SAME Window I ran the script in order to simply VIEW the affect. (Commit/Rollback buttons come to mind - Oracle I miss you sometimes).

  View data stored in #TempTable from another Window

I am creating a #TempTable using a script... but want to query it's data from another Window.  Currently when I open another window (Unlike Oracle's SQL Developer) it opens the window using a new connection/instance.  How do I query the data in #TempTable from another window rather than the window I ran the script in?

The above is just simply for organizational reasons... it's SUCH a pain to run a script... and then from that same tab have to then write in other code when I don't want to edit/add any other code to the page that contains the script I ran.

If I'm doing something wrong or missing some Point please let me know.

If I'm doing something wrong or missing some Point please let me know.

You're not missing anything.  SSMS always has a separate session per tab.

  it's SUCH a pain to run a script... and then from that same tab have
  to then write in other code when I don't want to edit/add any other
  code to the page that contains the script I ran.

In SSMS the Execute command (F5,Ctrl-e), always runs the highlighted text in the query window, if there is any.

And so the normal way people use SSMS query windows here is to highlight sections of code to run, often separating the main script from the test/debug bits by using multi-line comments.

For instance when developing a stored procedure, then query window might look like:

create or alter procedure SomeProc @param int
as
/*
  declare @p int = 12
  exec SomeProc @p
*/
begin
  ...
end

Then run the whole script to recreate the proc, and highlight the test harness lines and run to test it.


MySql crashing for no apparent reason We have a relatively busy site with ca. 30.000 connections per day. Things run fine, except that 1 or 2 times per day the MySql server crashes for no apparent reason and then restarts automatically. In the error logfile we see that it says that some tables are marked as crashed, but actually they have not, they are all still fine and usable. In the logfile I have added here, it can be seen that we have twice a strange 'Got timeout reading communication packets' from suspicious sources, but they are 5 hours, respectively about 1 hour before the actual crash happens at 06:25.10. The last message with a crashed table is seen 28 minutes before the crash happens, so its probably not related? Could it be that one of the 2 warnings that can be seen is triggering the crash?

This sort of thing is typically caused by an out-of-memory error. It could be a segfault/bug in MySQL, but this is less likely. Check your syslog for additional clues, there should be something in there, regardless of whether it is an OOM or a segfault.


Optimizer failing to load statistic when creating plan in SQL Server 2017 "Calculator failed. Replanning." This morning, the statistics on one of our filtered indexes updated, and when a plan that uses that statistics recompiled it stopped using that index (and started scanning the entire clustered index).

As this filtered index has included columns to cover the query, I had to wonder why.

When I tried the statement with options (RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 2363), I saw that the optimizer failed to load the statistic for the filtered, including column index:

Plan for computation:

  CSelCalcColumnInInterval

      Column: QCOL: [VendorPost1].StatusCode

Loaded histogram for column QCOL: [VendorPost1].StatusCode from stats with id 14

Calculator failed. Replanning.

We recently changed the compatibility mode in our databases to 2017 (having migrated in early Aug), and this is second time we've had an issue with stats not loading.  The first time, a stat update with full scan fixed the issue, but this seems like it should be unnecessary.

Unfortunately, we can not find anything online that describe the issue, and I can't seem to recreate with test tables.

Does anyone know how to fix or even where additional information can be found?  I'm starting to worry this is a bug we'll need to involve Microsoft in.

For a calculator to fail does not necessarily mean that loading of stats failed.

See Joe Sack's post with the query_optimizer_estimate_cardinality event indicating CSelCalcColumnInInterval CalculatorFailed:

The CSelCalcAscendingKeyFilter Calculator

Regarding the default for any values not in the histogram, Itzik Ben-Gan has a great summary in Seek and You Shall Scan Part II: Ascending Keys, but focuses on numeric data types.


"Centralising" a "decentral" design I have a web app using a database per client (around 120 and growing, 20k users in total). Each database has its own user table with user_id, username, password, etc. Meaning: if a user is member of multiple sites, the user has multiple accounts, passwords, etc.
I want to "centralise" the user tables, so users can log in once for all sites they are member of.
The username is the email address; which is used on a lot of places in the app.
Ideally I'd use a few "centralised" tables (user with columns username, password, etc; login_has_site linking user with site), while leaving the "decentralised" user tables untouched (containing username but also site specific preferences).
But, how do you maintain integrity between "central" and "decentral" tables when e.g. email address changes? Looking for strategy/ how to approach. Any thoughts?
Additional information
Basic schema
CREATE TABLE `login` (
  `login_id` int(11) NOT NULL,
  `username` varchar(255) NOT NULL,
  `password` text NOT NULL,
  `salt` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `login_has_site` (
  `login_id` int(11) NOT NULL,
  `site_id` int(11) NOT NULL,
  `date_start` date NOT NULL,
  `date_end` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- In each client database:
CREATE TABLE `user` (
  `login_id` int(11) NOT NULL,
  `user_id` varchar(255) NOT NULL,
  -- other fields
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

What would be the advantage of recording the user_id in the "client" database in the "central" database vs storing login_id in the "client" database?
Also: the username column is referenced in a lot of places in the codebase. Would it be wise to make it a key in the central database and use e.g. on update cascade to maintain integrity, while not having to rewrite all code?

I suggest creating a single common database. In this common database create your user table.

Instead of a login_has_site field, have a table of access that can be joined:

primary user id (of common table)
client name (other database)
user id in client db

After you have have that you can start to deprecate the fields in the user table across all but the common database's user table.


Check if a SQL Login exists without SA How can I check if a SQL Login exists for an arbitrary domain user if the account I'm running doesn't have SA rights to the instance (but will be dbo on at least one DB)

Ideally I'd like the solution to be automate-able for inclusion into a utility at some point in the future.

If I create a user in my DB and attempt to map it to a SQL Login that doesn't exists will it report that it doesn't exist or just give me a permissions error?

SQL Server will allow you to create a database user for a domain account, even if the corresponding login does not exist. No error will be returned as long as the user creating the database user has CREATE USER permissions and the domain account is valid.

However, the new user will not be able to authenticate to the instance until a login for that account (or group the account is a member of) is created.


errno 98 but no other mongod running Here are my config options:

options: { config: "/etc/mongod.conf"
, net: { bindIp: "127.0.0.1, 10.0.2.57" }
, processManagement: { fork: true, pidFilePath: "/var/run/mongodb/mongod.pid" }
, storage: { dbPath: "/data" }
, systemLog: { destination: "file", logAppend: true, path: "/log/mongod.log" }
}

First thing I do is check to see if any mongod is running using

ps aux | grep mongod

I see no results as expected (I've run sudo chkconfig mongod off to make sure the service doesn't start at boot).

When I run

sudo mongod service start

And check /log/mongod.log I see this

getaddrinfo(" 10.0.2.57") failed: Name or service not known
ERROR: listen(): bind() failed errno:98 Address already in use for socket: 0.0.0.0:27017

I then run this

netstat -an | grep :27017

And get no results. So then I check to see if a mongod is running using

ps aux | grep mongod

I get get no results.

When I remove my 10.0.2.57 from the config file the service starts and I can connect to it.

Do I need to explicitly have that 10.0.2.57 in my conf file to allow external connections to mongo? If so, what am I doing wrong?

Disclaimer: I'm very weak with Linux.

Remove the space between the comma and the second IP. It does not look like mongod is handling gracefully.


How to get transaction command text from SQL server Log file I got a list of the transaction from fn_dblog and would like to get the command text of the specific transaction by [Transaction Id]. Would you please guide me about that

This isn't logged anywhere (by default). You can investigate the "server audit" functionality and see if that gives you what you want.


How does SQL recursion actually work? Coming to SQL from other programming languages, the structure of a recursive query looks rather odd.  Walk through it step by step, and it seems to fall apart.

Consider the following simple example:

CREATE TABLE #NUMS
(N BIGINT);

INSERT INTO #NUMS
VALUES (3), (5), (7);

WITH R AS
(
    SELECT N FROM #NUMS
    UNION ALL
    SELECT N*N AS N FROM R WHERE N*N < 10000000
)
SELECT N FROM R ORDER BY N;

Let's walk through it.

First, the anchor member executes and the result set is put into R.  So R is initialized to {3, 5, 7}.

Then, execution drops below the UNION ALL and the recursive member is executed for the first time.  It executes on R (that is, on the R that we currently have in hand:  {3, 5, 7}).  This results in {9, 25, 49}.

What does it do with this new result?  Does it append {9, 25, 49} to the existing {3, 5, 7}, label the resulting union R, and then carry on with the recursion from there?  Or does it redefine R to be only this new result {9, 25, 49} and do all the union-ing later?

Neither choice makes sense.

If R is now {3, 5, 7, 9, 25, 49} and we execute the next iteration of the recursion, then we will end up with {9, 25, 49, 81, 625, 2401} and we've lost {3, 5, 7}.

If R is now only {9, 25, 49}, then we have a mislabeling problem.  R is understood to be the union of the anchor member result set and all the subsequent recursive member result sets.  Whereas {9, 25, 49} is only a component of R.  It is not the full R that we have accrued so far.  Therefore, to write the recursive member as selecting from R makes no sense.

I certainly appreciate what @Max Vernon and @Michael S. have detailed below.  Namely, that (1) all the components are created up to the recursion limit or null set, and then (2) all the components are unioned together.  This is how I understand SQL recursion to actually work.

If we were redesigning SQL, maybe we would enforce a more clear and explicit syntax, something like this:

WITH R AS
(
    SELECT   N
    INTO     R[0]
    FROM     #NUMS
    UNION ALL
    SELECT   N*N AS N
    INTO     R[K+1]
    FROM     R[K]
    WHERE    N*N < 10000000
)
SELECT N FROM R ORDER BY N;

Sort of like an inductive proof in mathematics.

The problem with SQL recursion as it currently stands is that it is written in a confusing way.  The way it is written says that each component is formed by selecting from R, but it does not mean the full R that has been (or, appears to have been) constructed so far.  It just means the previous component.

The BOL description of recursive CTEs describes the semantics of recursive execution as being as follows:

Split the CTE expression into anchor and recursive members.
Run the anchor member(s) creating the first invocation or base result set (T0).
Run the recursive member(s) with Ti as an input and Ti+1 as an output.
Repeat step 3 until an empty set is returned.
Return the result set. This is a UNION ALL of T0 to Tn.

So each level only has as input the level above not the entire result set accumulated so far.

The above is how it works logically. Physically recursive CTEs are currently always implemented with nested loops and a stack spool in SQL Server. This is described here and here and means that in practice each recursive element is just working with the parent row from the previous level, not the whole level. But the various restrictions on allowable syntax in recursive CTEs mean this approach works.

If you remove the ORDER BY from your query the results are ordered as follows

+---------+
|    N    |
+---------+
|       3 |
|       5 |
|       7 |
|      49 |
|    2401 |
| 5764801 |
|      25 |
|     625 |
|  390625 |
|       9 |
|      81 |
|    6561 |
+---------+

This is because the execution plan operates very similarly to the following C#

using System;
using System.Collections.Generic;
using System.Diagnostics;

public class Program
{
    private static readonly Stack<dynamic> StackSpool = new Stack<dynamic>();

    private static void Main(string[] args)
    {
        //temp table #NUMS
        var nums = new[] { 3, 5, 7 };

        //Anchor member
        foreach (var number in nums)
            AddToStackSpoolAndEmit(number, 0);

        //Recursive part
        ProcessStackSpool();

        Console.WriteLine("Finished");
        Console.ReadLine();
    }

    private static void AddToStackSpoolAndEmit(long number, int recursionLevel)
    {
        StackSpool.Push(new { N = number, RecursionLevel = recursionLevel });
        Console.WriteLine(number);
    }

    private static void ProcessStackSpool()
    {
        //recursion base case
        if (StackSpool.Count == 0)
            return;

        var row = StackSpool.Pop();

        int thisLevel = row.RecursionLevel + 1;
        long thisN = row.N * row.N;

        Debug.Assert(thisLevel <= 100, "max recursion level exceeded");

        if (thisN < 10000000)
            AddToStackSpoolAndEmit(thisN, thisLevel);

        ProcessStackSpool();
    }
}

NB1: As above by the time the first child of anchor member 3 is being processed all information about its siblings, 5 and 7, and their descendants, has already been discarded from the spool and is no longer accessible.

NB2: The C# above has the same overall semantics as the execution plan but the flow in the execution plan is not identical, as there the operators work in a pipelined exection fashion. This is a simplified example to demonstrate the gist of the approach. See the earlier links for more details on the plan itself.

NB3: The stack spool itself is apparently implemented as a non unique clustered index with key column of recursion level and uniqueifiers added as needed (source)


MySQL schedular every day in a time periode every hour This schedular i made doesn't work how i would like it to work.

The idea is that it will run every day in a time periode from 06:15:00 till 23:59:59 and in that only periode run every hour

That means it will run on 06:15 - 07:15 - 08:15 etc.. till 23:15 and start over again the next day

Server version: innodb_version:5.7.25

DELIMITER |

/*  6  7  8  9  10  11  12  13  14  15  16  17  18  19  20  21  22  23  */
ALTER
    EVENT `Database`.`Import_data01_view`
    ON SCHEDULE
        EVERY 1 HOUR
        STARTS '2019-04-15 06:15:00'  ON COMPLETION PRESERVE ENABLE
    DO BEGIN

/* do things */

END |

DELIMITER ;

With the info i found my own solution for it:

DELIMITER |

/*  6  7  8  9  10  11  12  13  14  15  16  17  18  19  20  21  22  23  */
ALTER
    EVENT `Database`.`Import_data01_view`
    ON SCHEDULE
        EVERY 1 HOUR
        STARTS str_to_date( date_format(now(), '%Y%m%d 0615'), '%Y%m%d %H%i' )
        ON COMPLETION PRESERVE ENABLE
    DO BEGIN

 IF time(CURRENT_TIMESTAMP) between time('06:14:59') and time('23:59:59')
      THEN

/* doe something */

end if;

END |

DELIMITER ;

-- main action to perform at 6:15 - 7:15 - ... 23:15
CREATE PROCEDURE main_action()
BEGIN
    -- ...
END;

-- event procedure
CREATE PROCEDURE evt_action()
BEGIN
IF HOUR(CURRENT_TIMESTAMP) > 5 THEN
    CALL main_action();
END IF;
END;

-- event
CREATE EVENT evt_name
ON SCHEDULE EVERY 1 HOUR STARTS '2019-04-15 06:15:00'
DO CALL evt_action();

-- start scheduler
SET GLOBAL event_scheduler = ON;


db_datawriter vs grant delete, update, insert I have an application set up in which the service account uses a user defined role which has each object in the database defined with specific permissions. It seems that every table in the database is listed here and they all have update, delete, insert, and select - so, essentially, db_datareader (select) and db_datawriter (insert, update, delete). The role also has select on all views, and execute on all procs.

When a user tries to update a document using a word plugin, with only the user role permissions, the update fails. If i grant db_datawriter on top of the user role to the account and the update is then attempted, it succeeds.

Where could the discrepancy be here? Why if both of these options are essentially giving the same permissions can we be getting different outcomes?

You say "It seems that every table in the database is listed here and they all have update, delete, insert, and select".  If you have not explicitly verified that insert, update, and delete permissions have been provided on every object via this role; these permissions may be missing on some objects.

The role [db_datawriter] guarantees that insert, update, and delete permissions are provided on all objects within the database.  This also includes any future objects to be created.


SQL Server Developer Edition 2012 or 2014 - Download I've heard (I am pretty sure) that Developer Edition is free of charge and for downloading it registration is not required. But when I started looking for it today I could not find where to get it from. Is my understanding wrong or right? Sorry I didn't find a similar question here. Any ideas?

Developer Edition is free

As of March 31, 2016 Microsoft SQL Server Developer Edition is now free for SQL Server 2014 and up for Visual Studio Dev essential members (which is free as well).

For all editions, see How to buy SQL Server.

If you want to explore SQL Server, you can download SQL Server Evaluation Edition for 180 days, and then purchase it.

Microsoft® SQL Server® 2014 & 2016 Evaluation
Microsoft® SQL Server® 2012 Evaluation


MySql Database solution for specific web app problem Well, I need to create a comment system for posts that people add to my web app. This web app is just like facebook or myspace posts. Users add posts and people can comment on them. But the problem is I don't know how to store each comment in mysql table. How can I map each comment to the specific author.

I am new to mysql and how can I learn to solve things like that. Is there any books on the subject. I know some basic sql.

Thank you!

You could use Nested Set, but CRUD operations are more complex than other patterns, and there's no referential integrity.

The Closure Table pattern is simpler to CRUD and supports integrity, but requires an extra table.

Recursive Query is the simplest and supports integrity, but you would need a more advanced database like Postgres, SQL Server, or Oracle.

http://blog.mclaughlinsoftware.com/2009/04/03/t-sql-hierarchical-query/

http://karwin.blogspot.ca/2010/03/rendering-trees-with-closure-tables.html


INSERT INTO returns ERROR 1452 I'm trying to move data from one table to another, but having an error:

mysql> INSERT INTO activities_article (category_id, created_at, title, promo, content, promo_image_url, coordinates, vk_id, updated_at, hot)
SELECT category_id, created_at, title, promo, content, promo_image_url, coordinates, vk_id, NOW(), False
FROM dorogirussia.activities_article WHERE category_id=4;

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`dorogi`.`activities_article`, CONSTRAINT `activities_articl_category_id_632c9272_fk_activities_category_id` FOREIGN KEY (`category_id`) REFERENCES `activities_category` (`id`))

Is there any way to fix that?

Please make sure you have the value "4" in "id" column in dorogi.activities_category and also change the query to the following:

INSERT INTO dorogi.activities_article (category_id, created_at, title, promo, content, promo_image_url, coordinates, vk_id, updated_at, hot)
SELECT category_id, created_at, title, promo, content, promo_image_url, coordinates, vk_id, NOW(), False
FROM dorogirussia.activities_article WHERE category_id=4;

Hope this solves your problem !!


Deleting Rows is Increasing Index Size We have a very large table in a SQL Server 2008 R2 Standard server which I am archiving rows by copying them into another database on a separate disk and then deleting them from the original table using an SSIS data flow. The table has a bigint Primary Key and rows are being deleted in numerical order of this key. As I am deleting rows, however, the overall Index size of the table is steadily increasing, and I cannot figure out why. The data size is remaining the same throughout this process.

Here are the table details:

Row Count: ~300,000,000
Data Size: ~65 GB
Index Size: ~65 GB

Rows are being deleted out at a rate of ~350,000 per hour.

The column definitions use the following data types: smallint, int, bigint, char, varchar, nvarchar, uniqueidentifier, bit, datetime

The table has one Primary Key which is the Clustered key on the table as well as 4 non-clustered indexes.

The table is also part of a replication maintenance plan. The replicated copy of the table is decreasing in both Data Size and Index Size!

I performed the same process on another table last week and I could see both the Data Size and Index Size of that table decreasing as I deleted rows.

Is there any explanation as to why the Index Size keeps going up in this case?

Alright so I believe you doing and INSERT is increasing the size then after you do your OLEDB command which is sending hundreds of thousands of DELETE commands to SQL Server is what's causing your problem. So here is a short Demo/Tutorial on how to do this DELETE operation more effectively. Even if this doesn't solve your index problem it will make your package run more efficiently.

STEP 1: Build a staging table for this information.

You're going to want to CREATE a table that's sole purpose is to hold the columns necessary for the delete. Whatever makes up your Primary Key will do.

STEP 2: Truncate the Staging table.

It's important to truncate first as this will all for re-run if the package fails.

Step 3: Data Flow task.

Next add a Data Flow Task and open.

If you are doing your archive elsewhere than disregard the Multicast and the Archive destination in the next image.

STEP 3: Delete

That should give you a pretty good idea of how to do this if you have any other question feel free to ask.

The reason this will be more efficient is because you are then doing set based operations instead of sending 350K DELETE commands to SQL server.


MySql UPDATE of tiny table is costly (0.2sec) - how to analyse further While optimizing some indexes I detected a curious case in the slow_query_log.
These are not the actual tablenames but for simplicity:
# Thread_id: 1293932  Schema: xx  QC_hit: No
# Query_time: 0.205087  Lock_time: 0.000046  Rows_sent: 0  Rows_examined: 1
# Rows_affected: 1  Bytes_sent: 52
UPDATE language SET lastUpdate = current_timestamp() WHERE languageID = '12';

Table 'language' has exactly 12 rows. It has only a few columns. It has only 1 index: the primary key on languageID (INT). This is thus the simplest table you can imagine. Why is this update statement taking 0.2 seconds?
For reference, select statements on rows of 10k-100k's of rows are quite fast:
# Thread_id: 1294423  Schema: xx  QC_hit: No
# Query_time: 0.111802  Lock_time: 0.000266  Rows_sent: 0  Rows_examined: 14279
# Rows_affected: 0  Bytes_sent: 50
INSERT INTO session (sessionID, personID, lastDate)
    SELECT 8, a.personID, current_timestamp() FROM personA a INNER JOIN personP p on p.personID = a.personID LEFT OUTER JOIN view_bigViewB ON view_bigViewB.personID = a.personID WHERE view_bigViewB.someValue = 'x' ;

Above query is simplified a lot, and please do not pay too much atention to it, but let's say this is really a dramatic big data set that is being processed in only 0.1sec with full table scans. It is an INSERT that did not insert any rows, so no write operation was needed.
Of course, a write operation is more costly but 0.2s seems just too slow. How can I analyze what exactly is making up this .2 seconds?
Using MariadDb 10.3.27
SHOW CREATE TABLE

CREATE TABLE `language`
(
   `languageID` int(11) NOT NULL AUTO_INCREMENT,
   `name` varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL,
   `lastUpdate` datetime DEFAULT NULL,
    PRIMARY KEY (`languageID`)
)
ENGINE=InnoDB
AUTO_INCREMENT=16
DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Output of innodb-parameters: https://pastebin.com/ReGHc1xp

Your innodb system variables indicate the innodb buffer pool is at 128M (the default). Its quite possible that the slowness of this single PK based update was due to this table not being in the innodb buffer pool. Look at the SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool%' and adjust the pool size up until the Innodb_buffer_pool_reads vs Innodb_buffer_pool_read_requests is very small (<1%).
This will be the case if 128M of more recently used innodb tables are in memory.
Another potential cause if there is a significant amount of write activity on the the server the innodb flushing /writing could be causing a delay because of storage limits.
If a f{data}sync of the disk can take 0.2 seconds occasionally that could correspond to the query time.


What is Required to use Oracle Performance Manager to monitor SQL Server? I am working with a customer who has several SQL Server and Oracle DBMS server instances.  My customer would like to setup up performance data collection, monitoring and alerting for all of their databases.  My strength is in SQL Server and I know lots of products/tools for doing this there, but there seems to be very few options for one tool or product that can do both Oracle and SQL Server at the same time.

One option that has been suggested is Oracle Performance Manager, which apparently can also monitor SQL Server.  I have been trying to read up on it, but there's a lot of doc out there and I am not very familiar with it's implicit context (the Oracle Product suite(s)) so I am having trouble filtering it down to the parts that I need right now to make a recommendation to my client.

Here are the things that I am trying to figure out:

Does Oracle Performance Manager fulfill any/all of these Performance functions for Oracle databases?:

a. Performance Data Collection (ie., retained in a database)

b. Performance Monitoring (live views of performance)

c. Performance Alerting (live notification of exceeding configurable thresholds)

d. Performance Reporting (this would be nice but not necessary, I know how to write a report from data tables, even in Oracle)
Which can Oracle Performance Manager fulfill for SQL Server databases?
What specific Oracle Product(s) does my customer need to have or purchase in order to get these functions?
Are there any other requirements or restrictions that we should know about?

Any help on this is much appreciated.

I think you are probably looking for Oracle Enterprise Manager or OEM as it is normally referred as, a good starting point for more information is here.

a. Oracle database starting with 10G+ do their own data collection (Automatic workload Repository), however, you need to be licensed to access much of that data. You can still use statspack (which is free). OEM actually uses this collected performance metrics and uses them for display via its pages.
b. Live views are available to use (the V$ and GV$) in Oracle, no experience with SQLServer, but I suspect you have similar views available.
c. OEM can send you alerts on events and conditions that exceed user defined thresholds. This is configurable via templates or custom metrics (aka User Defined Metrics).
d. OEM has some performance reporting built-in, but you can use OEM repository to roll your own reports, it is not  very difficult (have done it in the past). OEM repository is simply a different database, the SYSMAN schema contains tables and views which expose all collected data. You can adjust retention, only constrained by available storage. It can provide your historic information as well as realtime performance for Oracle. You will have to check the plug-in for similar capability for SQLServer.

OEM can (with using optional plugins) monitor SQL Server, check this link for a brief overview. In my shop there is no SS, so I don't have first hand experience in using these plugins.
Licensing information for OEM is here, I'd definitely recommend reading this manual to  you and your customers.
There could be, but once you read through the information linked above, you will get a better idea. Then a quick chat with your Oracle Sales team will yield more information as well.
Some of the extension packs for OEM are listed here.

Hope this gives you enough information to get started on it.


is there a DDL generator out there where you can start with CSV/TSV to create DDL for Cassandra Is there a DDL generator out there where you can start with CSV/TSV (or maybe even JSON) to create DDL for Cassandra?

DDL generator for a Cassandra keyspace:
DESC KEYSPACE the-Keyspace-name;

CSV generator for a Cassandra table:

COPY TO
DSBulk.


Fill in NULL values with those from another another table MySQL 5.6

Table: Employees

    EmployeeID   |   Name   |   PIN
    -------------+----------+---------
    1            |   Bob    |  1234
    2            |   Jim    |  3214
    3            |  Claire  |  4321
    4            | Jessica  |  NULL
    5            |  James   |  NULL
   ...           |   ...    |  ...

Table continues and all the PINs are NULL.

There's a second table: PINS

    PinID   |   PIN
    --------+--------
    1       |   1010
    2       |   1983
    3       |   5281
    4       |   6292
    5       |   9998
   ...      |   ...

Table continues on.
These numbers need to be added in to the null values. There can be no duplicates and they need to be in the order that they're listed in.

Goal: Employee

    EmployeeID   |   Name   |   PIN
    -------------+----------+---------
    1            |   Bob    |  1234
    2            |   Jim    |  3214
    3            |  Claire  |  4321
    4            | Jessica  |  1010
    5            |  James   |  5281
   ...           |   ...    |  ...

I've been attempting stuff similar to this:

UPDATE Employees
SET PIN =(SELECT PIN
          FROM PINS
          WHERE Employees.PIN <> PINS.PIN )
WHERE PIN IS NULL;

I'm at a loss, is there any way to bring the PINS over?

EDIT: I made some progress with:

UPDATE Employees
SET PIN = (SELECT PINS.PIN
           FROM PINS
           WHERE PINS.PinID = Employees.EmployeeID)
WHERE EXISTS ( SELECT 1
              FROM PINS
              WHERE PINS.PinID = Employees.EmployeeID)

;

however it replaces the starting PINS as well

EDIT2: added the goal table

First you need to relate the first empty PIN in Employees with the first PIN in PINs, the second empty PIN with the second available PIN and so on. Then you will be able to use an update with a join and populate the rows thus related.

One way is to assign row numbers to both sets and use the row numbers as the joining criterion. As MySQL does not support the standard SQL ROW_NUMBER() analytical function, you have to do without it.

One alternative is to assign the row numbers with the help of a variable, like this:

SELECT
  s.EmployeeID,
  @r := @r + 1 AS RowNumber
FROM
  (SELECT @r := 0) AS x
  CROSS JOIN
  (
    SELECT
      EmployeeID
    FROM
      Employees
    WHERE
      PIN IS NULL
    ORDER BY
      EmployeeID ASC
  ) AS s
;

The above produces a row set containing employee IDs and corresponding row numbers. Store the resulting set as a temporary mapping table and do the same for the PINs table, unless its PinID column already is a row number column. Based on your comment to Thronk's answer that the PINs table is imported from Excel, I am going to assume that the values in PinID have indeed been generated in Excel as row numbers essentially.

So, after you save the mapping set like this:

CREATE TEMPORARY TABLE Mapping
AS
SELECT
  s.EmployeeID,
  @r := @r + 1 AS PinID
FROM
  (SELECT @r := 0) AS x
  CROSS JOIN
  (
    SELECT
      EmployeeID
    FROM
      Employees
    WHERE
      PIN IS NULL
    ORDER BY
      EmployeeID ASC
  ) AS s
;

you can use it in the UPDATE statement like this:

UPDATE
  Employees AS e
  INNER JOIN Mapping AS m ON e.EmployeeID = m.EmployeeID
  INNER JOIN PINs AS p ON m.PinID = p.PinID
SET
  e.PIN = p.PIN
;

You can play with this method at Rextester.


How to do WHERE x IN (val1, val2,...) in plpgsql I would like to update a selection of rows in a table; this works...

UPDATE t1 SET col1 = 'newvalue' WHERE col0 in (2, 4, 5);

But how can I do the same in a plpgsql function? The following gives a syntax error...

CREATE OR REPLACE FUNCTION foo(intarray int[])
RETURNS VOID AS
$BODY$

BEGIN
  UPDATE t1 SET col1 = 'newvalue'
  WHERE col0 in intarray;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

The error...

ERROR:  syntax error at or near "intarray"
LINE 7:       WHERE col0 in intarray;

Edit:

If we replace in intarray; with in (intarray); the function is recorded but when running SELECT * FROM foo(ARRAY[1,3,5]) the error becomes...

ERROR:  operator does not exist: integer = integer[]
LINE 2:       WHERE col0 in (intarray)

IN (...) expects a literal list of values.

If you write x IN (some_array) then PostgreSQL expects x to also be an array, since you're testing to see whether the array x appears in the one-element list of arrays (some_array).

If you instead want to see if the value x appears as an element of the array some_array you must write:

x = ANY (some_array)

(Note that you can use ANY with pretty much any operator, not just =, and that there's an ALL as well as an ANY.)


Is it Possible to have a database table table of morethan 3000 fields I have a form of more than 3000 fields and i want to create a table without splitting it into two.

Is it okay? Or it will have problems?

3000 optional fields today; 3001 tomorrow.  You are looking at all-to-frequent ALTER TABLE .. ADD COLUMN..

Keep the "common" columns as MySQL columns.  Put the rest into a JSON string.  Be aware that it is messy and inefficient to filter or sort on data buried inside a json string.

The EAV schema pattern has, in my opinion, worse drawbacks.


Mirrored tables on PostgreSQL I have some values under "account" and I need to have the same values under "invoice", so, I'm trying to find a way to symlink my tables, so every time one gets updated, the another have the new value too. I'm developing under OpenERP, I can't trow that update directly on code, I need to write the rule using PgAdmin or something like.

I need to junction symlink 2 tables under Postgres.

ACCOUNT                INVOICE
|ID|payment_type|@@@@@@|ID|account_payment_type|
|#1|cc          |@@@@@@|#1|cc                  |
|#2|cash        |@@@@@@|#2|cash                |
|#3|cash        |@@@@@@|#3|cash                |

From your example it seems like invoice can be replaced with a simple VIEW, which is similar to a "symlink" in nature:

CREATE VIEW invoice AS
SELECT id, account_payment_type FROM account;

All done.
Else you need to be more specific in your question. Start with providing the complete table definition for both tables and add more information what is covered in each ...


How to remove an installed AE certificate from server using PowerShell Scripts? I have created the encryption certificate using below PowerShell script in dev. environment. Now, I need to create deployment document to configure the encryption on staging environment.

$cert = New-SelfSignedCertificate -Subject "AlwaysEncryptedCert" -CertStoreLocation Cert:LocalMachine\My -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage KeyEncipherment -KeySpec KeyExchange -KeyLength 2048

I need to keep the rollback PowerShell scripts to remove the certificate from local machine. What is the correct way of removing the certificate using PowerShell scripts? Can anyone guide me on this?

It is just Remove-Item to remove the certificate, and then include -DeleteKey to also drop the private key.

Based on your command it should be something like this as you have to pass in the thumbprint of the certificate to the remove command, so we need to look it up first:

Get-ChildItem -Path Cert:\LoaclMachine\My -Recurse -DocumentEncryptionCert |
    Where-Object Subject -eq 'AlwaysEncryptedCert' | Remove-Item -WhatIf