Diffrence in result from PostgreSQL (PostGIS) and SQL Server (Spatial) I have two points and I have to calculate difference between them.

I using below query in SQL Server.

SELECT geography::Point(27.185425, 88.124582, 4326)
       .STDistance(geography::Point(27.1854258, 88.124500, 4326));

Its running fine and result is :

8.12599260290125

Now with same point when I am running below query in PostgreSQL.

SELECT ST_Distance(ST_GeomFromText('POINT(27.185425 88.124582)',4326),
                   ST_GeomFromText('POINT(27.1854258 88.124500)', 4326));

and Now result is :

8.20039023523232

Can anyone tell me why there is difference in results when calculating distance between two points?

A couple of things that have caught you out here.

In the SQL Server version using the Point, the order of the coordinate is Lat Lon, eg Y X.  The OGC Point construct is X Y or Lon Lat.

The next is that the PostGIS query you've posted actually returns 8.20039023523232e-005 which while appearing similar to the SQL Server result, is quite a lot smaller.

The last issue is that the PostGIS query is treating the objects as Geometries and doing a cartesian distance on them.  You need to explicitly make them Geographies.

Here's a few ways to do the PostGIS query.  I've swapped the coordinate order

SELECT ST_Distance(ST_GeomFromText('POINT(88.124582 27.185425)',4326)::geography,
                   ST_GeomFromText('POINT(88.124500 27.1854258)',4326)::geography);

SELECT ST_Distance(ST_GeogFromText('SRID=4326;POINT(88.124582 27.185425)'),
                   ST_GeogFromText('SRID=4326;POINT(88.124500 27.1854258)'));

-- Adapted from the PostGIS Manual
SELECT ST_Distance(gg1, gg2) As spheroid_dist, ST_Distance(gg1, gg2, false) As sphere_dist
FROM (SELECT
    ST_GeogFromText('SRID=4326;POINT(88.124582 27.185425)') As gg1,
    ST_GeogFromText('SRID=4326;POINT(88.124500 27.1854258)') As gg2
    ) As foo  ;


SQL Server 2008 R2 Express Edition - generating database create script I need to script a database schema out to T-SQL for inclusion source control. The Express version of SSMS doesn't seem to let you script the entire database, just individual tables. Are there any free tools (scripts or otherwise) that can do this ?

This SQL script is a SQL Server 2005 table scripting tool that I wrote to reverse engineer a database schema to SQL.  It might do what you want.  Load it in SSMS and set 'Results to Text' from the menu.  Then run the script.  It will reverse engineer most of the major database objects.


get all posts with their associative tags without duplicates Two tables. Post and Tag. Many to Many bi-directional relationship.

Post Table              Tag Table             Post_Tag Pivot Table
---------------         ------------          -----------------
id   content            id   name             id_post   id_tag
---------------         ------------          -----------------
 1   hey there           1    php                1        1
                         2    python             1        2

I want to get all posts with their tags.

I've tried this:

SELECT P.title, T.tag
From Post P
JOIN Tag T
ORDER BY P.id DESC

It works, but it gives duplicates:

hey there, php
hey there, python

Is there a way to group tags together in one row here. Thanks

You didn't provide DDL and sample data so we can't test anything.
Also, it is not clear what is the join predicate, but I think you are looking for GROUP_CONCAT, something along the lines of (Pseudo code):

SELECT P.Title, GROUP_CONCAT(T.Tag SEPARATOR ',') AS Concatenated_Tags
FROM Post P INNER JOIN Tag T ...
GROUP BY P.Title;

HTH


Determine the size of a hashed varchar column I am using SQL Server.

I have this table:

create table MyTable (
ID int not null ,
Name varchar(500) )

and I am adding a name_hash column to store hash values for the name column:

alter myTable
    add
    name_hash AS HASHBYTES('SHA1', CONVERT(VARCHAR(90), Name))

How can I determine the resulting size of name_hash? Is it related to the size of the column that I want to apply the hashing to?

Well - it appears that a fellow Stack Exchange member (Richard Marskell - Drackir)  has figured this out here.

I've copied his answer below:

  You should use the binary datatype. You can use binary instead of
  varbinary because the hash function will always return the same number
  of bytes for the same type of hash (e.g. MD5, SHA1, etc.). This will
  cut down on the (slight) overhead required to manage a variable length
  binary (varbinary) column.

  In terms of what size to make it, you can run this query to check the
  length of each hash type:

SELECT  DATALENGTH(HASHBYTES('MD2', 'Testing')) AS [MD2Length],
        DATALENGTH(HASHBYTES('MD4', 'Testing')) AS [MD4Length],
        DATALENGTH(HASHBYTES('MD5', 'Testing')) AS [MD5Length],
        DATALENGTH(HASHBYTES('SHA', 'Testing')) AS [SHALength],
        DATALENGTH(HASHBYTES('SHA1', 'Testing')) AS [SHA1Length],
        /* 2012 only: */
        DATALENGTH(HASHBYTES('SHA2_256', 'Testing')) AS [SHA2_256Length],
        DATALENGTH(HASHBYTES('SHA2_512', 'Testing')) AS [SHA2_512Length];

And it should come out with this:

MD2Length MD4Length MD5Length SHALength SHA1Length SHA2_256Length SHA2_512Length
--------- --------- --------- --------- ---------- -------------- --------------
16        16        16        20        20         32             64


'SSL is required but the server doesn't support it (2026)' error while making an SSL MySQL connection I'm trying to make an SSL supported connection using MySQL Workbench 6.3.9.
My OS is Windows 10 x64 and I have installed MySQL Server 5.7 x64 with OpenSSL-1.0.2L (Win64 version). I made a non-SSL connection without any problem but when I am trying to make an SSL connection I always get this error: 'SSL is required but the server doesn't support it (2026)'
my.ini file contains these settings:
[client]
ssl-ca = "C:/vcerts/ca-cert.pem"
ssl-cert = "C:/vcerts/client-cert.pem"
ssl-key = "C:/vcerts/client-key.pem"
ssl
ssl-capath = "C:/vcerts"

[mysqld]
ssl-ca = "C:/vcerts/ca-cert.pem"
ssl-cert = "C:/vcerts/server-cert.pem"
ssl-key = "C:/vcerts/server-key.pem"
ssl
ssl-capath = "C:/vcerts"

At MySQL Workbench, after SSL Wizard's certificate generation, the SSL certificate settings are:

Any suggestions?

I managed to solve the problem by editing my.ini, step-by-step according to mySQL SLL setup documentation.
SSL settings inside my.ini should be:

[client]
ssl-ca=C:/vca/ca-cert.pem
ssl-cert=C:/vca/client-cert.pem
ssl-key=C:/vca/client-key.pem
[mysqld]
ssl-ca=C:/vca/ca-cert.pem
ssl-cert=C:/vca//server-cert.pem
ssl-key=C:/vca//server-key.pem

I guess the problem was that server understands "\s" (or "/s") as "space". So the last 2 server lines looked like:

ssl-cert=C:/vca/ erver-cert.pem
ssl-key=C:/vca/ erver-key.pem

According to server's logic. Plus configuration path/filename must be the same as windows service initialization path.

Configuration path/filename at mySQL Workbench:

Configuration path/filename at MySQL57 service:

"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.7\my.ini" MySQL57


Perform calculation from count of values for a distinct value My Goal is to show the top hosts with successful access attempts (Total, %)

200 = successful attempt
404 = unsuccessful attempt

logs table

host     | status
10.1.1.1   | 200
10.1.1.1   | 200
10.1.1.1   | 404
10.10.10.1 | 200
10.10.10.1 | 200
10.10.10.1 | 404

SELECT DISTINCT(host) AS Host, count(host) as Attempts
FROM logs
GROUP BY host
ORDER BY count(status) DESC;

Results in

Host       | Attempts
10.1.1.1   |    3
10.10.10.1 |    3

Query

SELECT DISTINCT(host) as Host, count(status) as Failed
FROM logs
WHERE status = '404'
GROUP BY host
ORDER BY count(host) DESC;

Returns

Host       | Failed
10.1.1.1   |    1
10.10.10.1 |    1

so far so good for getting started, now however when I try this query

SELECT DISTINCT(host) as Host,
    (SELECT count(status)
     FROM logs
     WHERE status = '404') AS Failed,
     (SELECT count(status)
     FROM logs
     WHERE status = '200') AS Successful
FROM logs
GROUP BY host
ORDER BY count(host) DESC;

it returns

Host       | Failed |Successful
10.1.1.1   |    2   |   4
10.10.10.1 |    2   |   4

I'm trying to get it to return 1 failure and 2 successful. I can see what it's doing (returning the total for all hosts). Eventually I'm trying to get to where I divide the total of the successful attempts (2) by total count(status) (3) and return a percentage. I have searched for hours on this one problem with no success, any help would be really appreciated.

First, distinct is not a function, it applies to the whole row. Most straight forward way would be:

Select host, status, count(1)
From logs
Where status in (200,404)
Group by host, status

This will however return one row for each status. My suggestion is to do what remains in the application since that is all about presentation. If you dont want to do that you can use a case expression to transpose the result:

Select host
    , count(case when status = 200 then 1 end) as #success
    , count(case when status = 404 then 1 end) as #failure
From logs
Where status in (200,404)
Group by host

Sorry about the formatting, typing from my mobile


SYSDBA password in Oracle DB I am connecting to Oracle DB using sysdba:

sqlplus / as sysdba

What is the default password for sysdba user. I tried in net and found out the default password is change_on_install but it is not working for me.

I am interested to know if Oracle DB maintains some file or DB table where it stores the passwords.

SYSDBA is not a user, it is a system privilege. When you connect as / you are connecting to the SYS user. You don't need a password when you connect as you are - as long as it's a local connection from an OS account in the DBA group.

You haven't said what happens when you try to connect, or why you think you need a password; possibly that is that failing (in which case giving the error message would be helpful) and giving you a SQL*Plus login prompt, maybe indicating you aren't in that group.

Passwords are stored within the DB, the exact location depends on version; but they're stored hashed (of course) so you're not going to be able to get the actual passwords in plaintext (without a lot of time and effort anyway).


Requests to local CouchDB have 40ms latency Requests to a CouchDB instance on the same machine have about 40ms latency when HTTP keepalive is used by the client, about 0-1ms latency when the client does not use keepalive. What causes this latency?

This is caused by Nagle's algorithm. CouchDB sends the HTTP headers and the response body in separate calls, causing the kernel to not deliver the response body for 40ms.

As a workaround, you can use this LD_PRELOAD shim to completely turn off Nagle's algorithm for the database process.


How can I find out what stats objects were used during a SQL Server 2014 query? I’ve read here that trace flags 3604, 9292 and 9204 enable debugging output that shows what statistics objects were used by the cardinality estimator during query compilation in SQL Server 2012.

They don’t work in SQL Server 2014, is there a new flag for this?

Yes, it’s flag 2363! Flag 3604 is just for redirecting the debugging output to the Messages window, so that stays the same in 2014.

SQL Server 2012

SELECT * FROM Test
WHERE Pred1 = 1 AND Pred2 = 2
OPTION
(
QUERYTRACEON 3604,
QUERYTRACEON 9292,
QUERYTRACEON 9204
)

SQL Server 2014

SELECT * FROM Test
WHERE Pred1 = 1 AND Pred2 = 2
OPTION
(
QUERYTRACEON 3604,
QUERYTRACEON 2363
)

Source: the SQL Server 2014 Selectivity Trace Flags section on this link.


How can I set a field when original lookup is NULL or isn't in the list? I am trying to merge our HR data.  I had to create my own tables to give a "regions" table

 DROP TABLE
IF EXISTS testgiver.ldap_karen;

CREATE TABLE testgiver.ldap_karen (uid VARCHAR (10) NOT NULL PRIMARY KEY) SELECT
  ldap_full.thomsnamedisplay,
    ldap_full.uid as 'uid',
    ldap_full.businesscategory,
    ldap_full.mail,
    ldap_full.givenname,
    ldap_full.sn,
    ldap_full.departmentnumber,
    ldap_full.title,
    ldap_full.thomsmgdescr,
    ldap_full.thomsmgdescroverride,
    ldap_full.thomscompanydescr,
    ldap_full.thomscompanydescroverride,
    ldap_full.thomslocationstreet1,
    ldap_full.thomslocationcity,
    ldap_full.thomslocationstatedescr,
    ldap_full.thomslocationpostal,
    ldap_full.thomslocationcountrydescr,
    ldap_full.thomssupervisorid,
    ldap_full.thomssupervisoriddescr,
    ldap_full.thomscompanyshortdescroverride,
    ldap_full.thomsbulevel1descr,
    ldap_full.thomsbulevel1shortdescr,
    ldap_full.thomsbulevel2descr,
    ldap_full.thomsbulevel2shortdescr,
    ldap_full.thomsroleindicatordescr,
    ldap_full.thomsjobcodedescr,
    ldap_full.thomsjobfamilycode,
    ldap_full.thomsjobfamilydescr,
    ldap_full.thomsrecordtypedescr,
    sup_email.mail AS supemail,
  reports_regionnames.regionname
FROM
    ldap_full
LEFT JOIN ldap_full AS sup_email ON ldap_full.thomssupervisorid = sup_email.uid
LEFT JOIN reports_regions ON ldap_full.thomslocationcountrydescr = reports_regions.thomslocationcountrydescr
LEFT JOIN reports_regionnames ON reports_regions.thomsregion1 = reports_regionnames.regiondid

This works fine and adds a region to each user.  However I would like to take it a step further.  I would like it to handle instances where and employee does not have ldap_full.thomslocationcountrydescr (so null) or if the employees ldap_full.thomslocationcountrydescr is not in the lookup table.  This seems simple but I have tried a few things and I don't think my syntax is even close.

Would simply like the region to be set to "bad" for each employee with a "new country" or no country.

Arguably, you would be better off with the NULL since that's the absence of a value, rather than arbitrary text indicating the absence of a valid value... but:

    -- 8< -- snip -- 8<--
    ldap_full.thomsjobfamilycode,
    ldap_full.thomsjobfamilydescr,
    ldap_full.thomsrecordtypedescr,
    sup_email.mail AS supemail,
    CASE WHEN ldap_full.thomslocationcountrydescr IS NULL
         THEN 'hey null source data here'
         WHEN reports_regionnames.regionname IS NULL AND ldap_full.thomslocationcountrydescr IS NOT NULL
         THEN 'my lookup table needs something'
         ELSE reports_regionnames.regionname END AS regionname /* << updated from comments below */
FROM
    ldap_full
    -- 8< -- snip -- 8<--

The last column selected will be the output of the CASE expression.

When ldap_full.thomslocationcountrydescr IS NULL then we return one string... otherwise when we didn't match the lookup table, yet ldap_full.thomslocationcountrydescr isn't null, we return a different string... otherwise (ELSE) we return the original value, from reports_regionnames.regionname.

The first valid match wins.

http://dev.mysql.com/doc/refman/5.6/en/control-flow-functions.html


MySQL Data directory move (WAMP) I removed the old WAMPServer 2.0 and it left my old MySQL data directory and my.ini intact.
I installed the new WAMPServer 2.2 and need to make my old databases active with the new WAMPServer MySQL.

When I set the data directory in the new my.ini to point to the old data directory, I get this error when trying to start the service:

Could not start the wampmysqld service on Local Computer.
Error 1067: The process terminated unexpectedly.

No other errors. In the Application Event log I see this:

Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist

How do I get around this as the old MySQL version was 5.1.36 and the new one is 5.5.16.

You'll need to copy over ALL the contents of your old data dir to the new data dir.  Specifically it sounds like you did not copy the mysql folder (read mysql database) over to the new data dir.


Compacting a sequence in PostgreSQL I have a id serial PRIMARY KEY column in a PostgreSQL table. Many ids are missing because I have deleted the corresponding row.

Now I want to "compact" the table by restarting the sequence and reassigning the ids in such a way that the original id order is preserved. Is it possible?

Example:

Now:

 id | data
----+-------
  1 | hello
  2 | world
  4 | foo
  5 | bar

After:

 id | data
----+-------
  1 | hello
  2 | world
  3 | foo
  4 | bar

I tried what was suggested in a StackOverflow answer, but it didn't work:

# alter sequence t_id_seq restart;
ALTER SEQUENCE
# update t set id=default;
ERROR:  duplicate key value violates unique constraint t_pkey
DETAIL:  Key (id)=(1) already exists.

First off, gaps in a sequence are to be expected. Ask yourself if you really need to remove them. Your life gets simpler if you just live with it. To get gap-less numbers, the (often better) alternative is to use a VIEW with row_number(). Example in this related answer:

Gap-less sequence where multiple transactions with multiple tables are involved

Here are some recipes to remove gaps.
1. New, pristine table
Avoids complications with unique violations and table bloat and is fast. Only for simple cases where you are not bound by FK references, views on the table or other depending objects, or by concurrent access. Do it in one transaction to avoid accidents:
BEGIN;
LOCK tbl; -- optionally: IN SHARE MODE to allow concurrent reads

CREATE TABLE tbl_new (LIKE tbl INCLUDING ALL);

INSERT INTO tbl_new -- no target list in this case
SELECT row_number() OVER (ORDER BY id), data  -- all columns in default order
FROM   tbl;

ALTER SEQUENCE tbl_id_seq OWNED BY tbl_new.id;  -- make new table own sequence

DROP TABLE tbl;
ALTER TABLE tbl_new RENAME TO tbl;

SELECT setval('tbl_id_seq', max(id)) FROM tbl;  -- reset sequence

COMMIT;

CREATE TABLE tbl_new (LIKE tbl INCLUDING ALL) copies the structure incl. constraints and defaults from the original table. Then make the new table column own the sequence:

Activerecord-import & serial column in PostgreSQL

And reset it to the new maximum:

How to reset Postgres' primary key sequence when it falls out of sync?

This carries the advantage that the new table is bloat-free and clustered on id.
2. UPDATE in place
This produces a lot of dead rows and requires (auto-) VACUUM later.
If the serial column is also the PRIMARY KEY (like in your case) or has a UNIQUE constraint, you must avoid unique violations in the process. The (cheaper) default for PK / UNIQUE constraints is to be NOT DEFERRABLE, which forces a check after every single single row. All details under this related question on SO:

Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

You could define your constraint as DEFERRABLE (which makes it more expensive).
Or you can drop the constraint and add it back when you are done:
BEGIN;

LOCK tbl;

ALTER TABLE tbl DROP CONSTRAINT tbl_pkey;  -- remove PK

UPDATE tbl t  -- intermediate unique violations are ignored now
SET    id = t1.new_id
FROM  (SELECT id, row_number() OVER (ORDER BY id) AS new_id FROM tbl) t1
WHERE  t.id = t1.id;

SELECT setval('tbl_id_seq', max(id)) FROM tbl;  -- reset sequence

ALTER TABLE tbl ADD CONSTRAINT tbl_pkey PRIMARY KEY(id); -- add PK back

COMMIT;

Neither is possible while you have FOREIGN KEY constraints referencing the column(s) because (per documentation):

The referenced columns must be the columns of a non-deferrable unique
or primary key constraint in the referenced table.

You would need to (lock all involved tables and) drop / recreate FK constraints and update all FK values manually (see option 3.). Or you have to move values out of the way with a second UPDATE to avoid conflicts. For instance, assuming you have no negative numbers:
BEGIN;
LOCK tbl;

UPDATE tbl SET id = id * -1;  -- avoid conflicts

UPDATE tbl t
SET    id = t1.new_id
FROM  (SELECT id, row_number() OVER (ORDER BY id DESC) AS new_id FROM tbl) t1
WHERE  t.id = t1.id;

SELECT setval('tbl_id_seq', max(id)) FROM tbl;  -- reset sequence

COMMIT;

Drawbacks as mentioned above.
3. Temp table, TRUNCATE, INSERT
One more option if you have plenty of RAM. This combines some of the advantages of the first two ways. Almost as fast as option 1. and you get a pristine, new table without bloat but keep all constraints and dependencies in place like in option 2.
However, per documentation:

TRUNCATE cannot be used on a table that has foreign-key references
from other tables, unless all such tables are also truncated in the
same command. Checking validity in such cases would require table
scans, and the whole point is not to do one.

Bold emphasis mine.
You could drop FK constraints temporarily and use data-modifying CTEs to update all FK columns:
SET temp_buffers = 500MB;   -- example value, see 1st link below

BEGIN;

CREATE TEMP TABLE tbl_tmp AS
SELECT row_number() OVER (ORDER BY id) AS new_id, *
FROM   tbl
ORDER  BY id;  -- order here to use index (if one exists)

-- drop FK constraints in other tables referencing this one
-- which takes out an exclusive lock on those tables

TRUNCATE tbl;

INSERT INTO tbl
SELECT new_id, data  -- list all columns in order
FROM tbl_tmp;        -- rely on established order in tbl_tmp
-- ORDER BY id;      -- only to be absolutely sure (not necessary)

--  example for table "fk_tbl" with FK column "fk_id"
UPDATE fk_tbl f
SET    fk_id = t.new_id  -- set to new ID
FROM   tbl_tmp t
WHERE  f.fk_id = t.id;   -- match on old ID

-- add FK constraints in other tables back

COMMIT;

Related, with more details:

How to delete duplicate entries?
How to “merge” two tables efficiently in SQL?


Mongodb crash after multiple query attempts Every once in a while our mongo service crashes () after displaying log of multiple messages like this:

Sun Apr  6 01:11:38.648 [conn2149] getmore prod_db.tweets query: { query: {}, $snapshot: true } cursorid:2408233643061247141 ntoreturn:0 exhaust:1 keyUpdates:0 numYields: 25 lock
s(micros) r:120718 nreturned:901 reslen:4197371 122ms
Sun Apr  6 01:11:38.769 [conn2149] getmore prod_db.tweets query: { query: {}, $snapshot: true } cursorid:2408233643061247141 ntoreturn:0 exhaust:1 keyUpdates:0 numYields: 22 lock
s(micros) r:73717 nreturned:905 reslen:4196587 1

If I understand correctly, the "numYields" means that it has tried "numYields" times to run the query, but yielded. However, I don't know which process might be blocking it, and not why it is crashing. Any idea?

That query is simply a long running read, without any criteria (so it is running against all data).  As it fetches back the data, it will be done in batches (based on your batch size) and then issue a getmore on the same cursor for the next set of results.

The numYields count does not mean the query is being blocked, it means that it yielded its lock when needed. This is usually done for a write, and usually when the original query had to page fault to disk to get data, then it resumes (when querying all data in a collection, this is going to happen often unless all your data + indexes fit in RAM).

Therefore, the query is not being blocked, in fact the getmore operations show that it is progressing over time - most long running reads will have a similar profile, especially if you are writing to the database at the same time.

It is also not likely that this query is the cause of any crash (it's just a read), it's more likely something else that is causing the crash, and you are equating this query with the crash because it happens to be running at the time when the crash occurs (people often suspect the serverStatus command for the same reason - it is run once a minute by MMS).  I would recommend posting the full messaging around the crash as a separate question for proper diagnosis.

For what it's worth, with snapshot set to true, and the fact that it is reading all data, I suspect this is a mongodump query (it defaults to using snapshot to avoid duplicates being dumped when data is moved).


Why number of matches in fulltext search get less when we put less strings? SELECT * FROM tableauxiliary WHERE MATCH (FullTextSearch)
AGAINST ('resta*' IN NATURAL LANGUAGE MODE)

show 5 results

SELECT * FROM tableauxiliary WHERE MATCH (FullTextSearch)
AGAINST ('restaurant*' IN NATURAL LANGUAGE MODE)

shows 56k

What am I missing? How can something contains restaurant without containing resta

I am using mysql and MyISAM

Maybe more than 50% of your tablerows contain words starting with "resta*".
Words that are present in 50% or more of the rows are considered common and do not match.

So you would only have those rows returned, that have a non-common word also starting with "resta*" in your result.

see: http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html

Try

SELECT * FROM tableauxiliary WHERE MATCH (FullTextSearch)
AGAINST ('resta*' IN BOOLEAN MODE)

see http://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html

Boolean full-text searches have these characteristics: - They do not use the 50% threshold that applies to MyISAM search indexes

I got this here:
Why does MATCH() give Score 0, if there are less rows in the database?


Postgresql condition on subquery results This is a new question based on the solution to my previous question

I have the following tables:

users

int (pk) user_id
date last_visit
int (fk) local_site

sites

int (pk) site_id

site_stats

int (fk) site_id
int total_users
int monthly_users
int weekly_users

And I am using this query to update the user_stats table

UPDATE site_stats
SET
    total_users = totalUsers,
    monthly_users = monthlyUsers,
    weekly_users = weeklyUsers
FROM
(
    SELECT
        u.local_site AS site,
        count (u.*) AS totalUsers,
        count(CASE WHEN u.last_visit >= now()::DATE - 30 THEN 1 END) AS monthlyUsers,
        count(CASE WHEN u.last_visit >= now()::DATE - 7 THEN 1 END) AS weeklyUsers
    FROM users u
    GROUP BY u.local_site
) AS subquery
WHERE site_stats.site_id = subquery.site ;

This works fine, except that if there are 0 users linked to a site in the time frame then no update will occur, so the table col will keep the previous value rather than showing 0.

What would be the best way to add a condition so that any sites with 0 linked users is updated with 0?

My first guess was to use a CASE in the WHERE clause?

Either you run an additional UPDATE (best in one transaction):

UPDATE site_stats s
SET    total_users   = 0
     , monthly_users = 0
     , weekly_users  = 0
WHERE  NOT EXISTS (SELECT 1 FROM users WHERE local_site = s.site_id)

Or use this instead (I would do that):

UPDATE site_stats s
SET    total_users   = COALESCE(u.total, 0)
     , monthly_users = COALESCE(u.monthly, 0)
     , weekly_users  = COALESCE(u.weekly, 0)
FROM   site_stats s0
LEFT   JOIN (
   SELECT local_site
        , count(*) AS total
        , count(last_visit >= now()::date - 30 OR NULL) AS monthly
        , count(last_visit >= now()::date - 7  OR NULL) AS weekly
   FROM   users
   GROUP  BY 1
   ) u ON u.local_site = s0.site_id
WHERE  s.site_id = s0.site_id;

Including some other minor simplifications.


Can't create table or database in sql server as permission denied in database master . (Logged in with windows authentication ) Can't create table or database in sql server as permission denied in database master.
Log in authentication is Windows authentication (./SQLEXPRESS)

In order to create a database, you need to either be a part of the dbcreator fixed server role, or have either of the following permissions on the server: CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE.

The fact that you are using Windows Auth is a neutral point, as within SQL Server it is a principal that still needs the required permissions to perform the necessary actions.


split SQL FULL Backups on ola hallengren maintanance solution I am using Ola Hallengren backup solution in all our environment, I have a very big Backup file which is taking 10+ hours to complete, so I am thinking to split SQL Server Full backup files.

anyone know how to split backup files in ola hallengren backup solution or else tell me if I need to create different maintenance plan only for one BIG DB

Thanks,
Ven

In addition to Scott's answer, there is also the NumberOfFiles parameter which would let you create multiple files under the same directory, if desired.


Rare error lasts only a second "Can't connect to local MySQL server through socket '/tmp/mysql.sock' (146)" 146=ECONNREFUSED This is a rare error that occurs on Solaris 10, have seen it from Perl, where this line
DBI->connect(...) or die "Whoops! $DBI::errstr"

outputs Whoops! Can't connect to local MySQL server through socket '/tmp/mysql.sock' (146) at something.pl line ...

I determined that on my system, 146=ECONNREFUSED

It is very rare for this error to occur, but it happens on an ongoing basis.
It always a temporary problem on this setup—lasting only a second or two.

The global variable max_connections has been ruled out as a problem.

This does not occur on TCP/IP based connections.

I am using the [mysqld_safe] log-error option in my.cnf.
The log outputs the following at startup:
mysqld started
InnoDB: Started; log sequence number...
[Note] .../libexec/mysqld: ready for connections.
Version: '5.0.27-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
There are no other log entries, so there's nothing here to indicate the problem.

Please let me know if you can suggest a setting for more detailed logging.

Can you enumerate the possible or likely causes of this error happening only occasionally and only for a second or two?

Life has many great mysteries. For the MySQL DBA, one of life's greatest mysteries is your question. It's been this way going on 12 years. In fact, I found a bug report that was resolved when the disappearance of mysql.sock was caused by trying to start mysqld with mysqld already running (a.k.a. Shooting mysqld in the Foot).

I have personally addressed workarounds to this situation many times in the StackExchange:

Jul 02, 2013 : MySQL crash. Unknown cause. Signal 11 (ServerFault)
Apr 22, 2013 : /usr/libexec/mysqld: Normal shutdown, but my team doesn't do that?
Mar 06, 2013 : How to properly kill MySQL?
Feb 28, 2013 : mysql restart won't kill child processes on CentOS
Dec 14, 2012 : Percona-server time out on /etc/init.d/mysql start (ServerFault)
May 08, 2012 : How to properly stop MySQL server on Mac OS X?

The mystery has to do with mysql.sock (the MySQL Socket file) disappearing. Once this happens, you cannot connect to mysqld using root@localhost. In fact, no user defined with host='localhost' in the mysql.user table can connect. TCP/IP connections can still be used. The workaround is to connect to mysqld using TCP/IP.

Try create the user root@'127.0.0.1'. From the mysql client you then do this:

mysql -uroot -p -h127.0.0.1 --protocol=tcp

The only way to bring back mysql.sock is to restart mysqld WHEN mysqld IS NOT RUNNING.

You should make sure you explicitly define the mysql.sock file in /etc/my.cnf

[mysqld]
socket=/var/run/mysqld/mysql.sock

Of course, go create the folder

mkdir /var/run/mysqld
chmod 777 /var/run/mysqld

Give it a Try !!!

UPDATE 2013-12-06 10:17 EST

Based on your comments...

  I included the log, please let me know if you know how to increase the detail

You are using MySQL 5.0.27, which means you are using a very old mysqld (with a truck load of bugs) and an old InnoDB Storage Engine. There may not be additional detail available. I also noted you are using a Source Distribution rather than RPM installed binaries.

  bring back mysql.sock is to restart mysqld. This does not happen for me. I never have to do anything. The problem lasts a second and goes away.

If the problem remains for only a second or two, I can see what is happening. When you run service mysql start, you are actually running mysqld_safe. At the bottom of mysqld_safe, there is an infinite loop. In the infinite loop, it calls mysqld and waits for a return value. Certain return values from mysqld will make mysqld_safe terminate (normal shutdown,condition where mysqld can't start again). Other return values will cause mysqld_safe to loop and try mysqld again.

SUGGESTION

You should upgrade to MySQL 5.6 to get the latest version of mysqld and the InnoDB Storage Engine. If the problem still persists, at least there will be less baggage due to better quality MySQL binaries. That way, you can rule out mysqld and look for other external factors. Also, you should download RPM binaries rather than compiling source because RPM version usually have more optimizations in place.

UPDATE 2013-12-06 10:39 EST

If you look back at the two links at the top of my answer

LINK#1 to Bug Report
LINK#2 to Bug Report

you will learn that the first link references MySQL 4.0.20, and the second link was from a bug report thread that said the version in question was MySQL 3.22.32 (the final release of MySQL 3 was 3.23.58)

Issues with mysql.sock has been around 13 years now due to shooting yourself in the foot (starting mysqld even though it is running), internal factors (very old version or clunky version of MySQL) or some external condition (lack of RAM or other OS resources).

Then, it looks like you found an internal factor. To verify that mysqld is correcting itself, run SHOW GLOBAL VARIABLES LIKE 'uptime';. If the number continues increasing and never resets, then you can rightly blame the clunky MySQL binary for not being more elaborate. MySQL AB was probably solving some internal issue and may have left some debug statements or trace points in the binary

UPDATE 2013-12-06 11:24 EST

  Just looked inside mysqld_safe. There is code to print mysqld ended and mysqld restarted. If mysqld did indeed terminate, mysqld_safe would have logged it. If mysqld was restarted by mysqld_safe, the fresh/new instance of mysqld would print out the log information including version number. Since the version number was printed only once, it is clear that mysqld was never ended and never restarted. – George Bailey 13 mins ago

  Just FYI, I found out that 146 is ECONNREFUSED, I edited it in to my question. – George Bailey 11 mins ago

Then, it looks like you found an internals issue. The source-compiled MySQL 5.0.27 mysqld may be just printing debug messages after an internal recovery. To verify mysqld is OK in terms of being up, just run SHOW GLOBAL VARIABLES LIKE 'uptime';. If the number does not reset and just keeps increasing, then mysqld is correcting itself from within. Given the old build you are using and the way it was compiled, I would not expect mysqld to be more forthcoming on its own.

You should see if mysqld was started with debug enabled. If not, try setting debug in my.cnfand see if it can get more verbose about its internals. I hope this reveals what the problem may be.


How to list all forced query plans and cancel one Using SSMS 2016 Query Store I forced a few weeks ago a specific query to use a specific plan. Now every now and then I have the error:

  Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN

I assume SET FORCEPLAN is passed automatically since the code executing that query doesn't.
So I would like to review my forced plan, maybe cancel it. But I can't find that query in the query store anymore.

Hence my question: is there a way to list all forced query plans and then how can I cancel one?

You should be able to get information about 'forced' plans by querying this catalog view (sys.query_store_plan) filtering on is_forced_plan = 1.  At the bottom of the page are links to other useful catalog views for Query Store.

This stored procedure (sp_query_store_unforce_plan) allows you to 'unforce' a plan.


User on SQLAgentOperatorRole unable to edit jobs My Active Directory account is on an AD group that is on msdb's SQLAgentOperatorRole. I'm able to list existing MSSQL Agent jobs, but I can only view their steps and schedules, I'm unable to edit them or create new.
I'm able to create new jobs, but on their step, when I select CmdExec, no proxy is listed.
If I remove this group from SQLAgentOperatorRole, no job is listed anymore.
When I log as sa I'm able to edit jobs and proxies are listed.
Am I missing something to be able to manage jobs?

See the documentation at https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles?view=sql-server-ver15. It clearly states what you can do with this role. It does not include privileges to edit a job owned by somebody else, for instance.


What does CREATE ASSEMBLY do with the registered dll? I created a dll in .NET for some custom CLR spatial functions in SQL Server 2008 R2. I sent the dll to a colleague along with a script to deploy it (CREATE ASSEMBLY then create the external functions etc).

She asked whether she needed to keep the file in the same location after it had been registered using CREATE ASSEMBLY. I didn't know, so I experimented by registering an assembly and creating the functions from it in sql server. Then I renamed the dll and restarted sql server. I found that the functions still worked after this so it appears that sql server has loaded the dll in some way and it doesn't need to stay on the file system. What's puzzling, though, is that the table sys.assembly_files does store the original path of the dll. Why would it do that if it isn't relevant?

So I'm not sure my test for this was right. But does anyone know what CREATE ASSEMBLY does with a dll? Does it copy/import it somewhere? Or was my test flawed and sql server does require the original dll to stay in the same location?

Thanks in advance.

The bits are copied into the database where you call CREATE ASSEMBLY or ALTER ASSEMBLY.

The path you select in Management Studio is only a convenience for it to read the file and dump out the bits into a varbinary(MAX) value (hit the Script button in that dialog to see what I mean).

This also means that the assembly is "carried" with the database if you do something like backup/restore on another server.


mysql logical ranking based on score I have a table like this that keeps a total score of a history exam for students.

+----+---------------+-------+---------+-
| id | name          | history |  rank  |
+----+---------------+-------+---------+-
|  1 | yngiid        |    97   |        |
|  2 | viyrp         |   217   |        |
|  3 | pae           |   599   |        |
|  4 | spohl         |   284   |        |
|  5 | shl           |   295   |        |
|  6 | okeer         |    73   |        |
|  7 | jmaany        |   657   |        |
|  8 | hxt           |    80   |        |
|  9 | yanier        |   599   |        |
+----+---------------+-------+----------+-

The history field as you can see contains the total score the student . Now, I would like to rank the students by their scores as in, the higher the points the lower the rank.

Expected output.

+----+---------------+-------+----------+-
| id | name          | history |  rank  |
+----+---------------+-------+----------+-
|  7 | jmaany        |   657   |   1    |
|  3 | pae           |   599   |   2    |
|  9 | yanier        |   599   |   2    |
|  5 | shl           |   295   |   3    |
|  4 | spohl         |   284   |   4    |
|  2 | viyrp         |   217   |   5    |
|  1 | yngiid        |    97   |   6    |
|  8 | hxt           |    80   |   7    |
|  6 | okeer         |    73   |   8    |
+----+---------------+---------+--------+-

I have looked at answers in stackoverflow  but the answers have some issues like:

In the below example, methods will increment the rank when users have the same score, as in the example of pae and yanier

|  7 | jmaany        |   657   |   1    |
|  3 | pae           |   599   |   2    |
|  9 | yanier        |   599   |   3    | # shold be 2 bc pae&yanier have equal points
|  5 | shl           |   295   |   4    |

Other solution will fix the above problem but skip the the next rank number if scores are the same example:

|  7 | jmaany        |   657   |   1    |
|  3 | pae           |   599   |   2    |
|  9 | yanier        |   599   |   2    |
|  5 | shl           |   295   |   4    | # should be 3 b/c .. logic

These are just some of the problems, so I am wandering if there is a solution to output the expected result.

Here is a sample table dump if it helps.

create table `test` (
    `id` int (11),
    `name` varchar (765),
    `history` int (11),
    `rank` int (11)
);
insert into `test` (`id`, `name`, `history`, `rank`) values('1','yngiid','97',NULL);
insert into `test` (`id`, `name`, `history`, `rank`) values('2','viyrp','217',NULL);
insert into `test` (`id`, `name`, `history`, `rank`) values('3','pae','599',NULL);
insert into `test` (`id`, `name`, `history`, `rank`) values('4','spohl','284',NULL);
insert into `test` (`id`, `name`, `history`, `rank`) values('5','shl','295',NULL);
insert into `test` (`id`, `name`, `history`, `rank`) values('6','okeer','73',NULL);
insert into `test` (`id`, `name`, `history`, `rank`) values('7','jmaany','657',NULL);
insert into `test` (`id`, `name`, `history`, `rank`) values('8','hxt','80',NULL);
insert into `test` (`id`, `name`, `history`, `rank`) values('9','yanier','599',NULL);

SELECT
    T1.id,
    T1.`name`,
    T1.history,
    T2.rank
FROM
    test T1
LEFT JOIN (
    SELECT
        history,
        (@v_id := @v_Id + 1) AS rank
    FROM
        (
            SELECT DISTINCT
                history
            FROM
                test
            ORDER BY
                history DESC
        ) t,
        (SELECT @v_id := 0) r
) T2 ON T1.history = T2.history
ORDER BY
    history DESC

with result as:

7   jmaany  657 1
3   pae     599 2
9   yanier  599 2
5   shl     295 3
4   spohl   284 4
2   viyrp   217 5
1   yngiid  97  6
8   hxt     80  7
6   okeer   73  8


Relocate all files to folder checkbox unavailable sql server 2012 Using SQL 2012 Enterprise edition

Following instructions here for restoring a backup:
http://msdn.microsoft.com/en-us/library/ms186390.aspx#Restrictions

I need to be able to rename the database because it is the second copy on the instance (testing purposes). But I cannot rename it nor change the folders that it will restore the MDF/LDF files to.

For whatever reason the "Relocate all files to folder" checkbox is not available.

Restoring form a SQL 2005 backup.

Try to do this with T-SQL:

restore database YourDatabase_NewName
from disk = 'C:\YourDir\YourDatabase.bak'
with
    move 'YourDataFileName' to 'C:\DataDir\DataFile.mdf',
    move 'YourLogFileName' to 'C:\LogDir\LogFile.ldf'
go


Fastest way to restore a Sql Server database Each of our 10000 integration tests need to restore the database to its original "clean" state before each of them are done, during a cleanup stage. Running all tests takes time, and 80% of the time is spent during the database restore operation.

The restore operation simply takes a backup from disc and restores it.
I am now wondering if there is a way to optimize this.
One option I am thinking of is to change the location of the backup file from disc to memory. Is that possible?

Another option would be to take a snapshot of the table data only, and save only that as the backup data. I assume then that the backup file becomes smaller, and therefore faster to restore. Is that possible?

Ideally, I would like to avoid restoring, and just reset the affected data (the data that each test produces). Something like: "revert any table rows that were modified since the test started"

What options do i have? I am not an expert in this area but it feels like my problem should have an easy solution.

Database snapshots are probably the right answer for your specific situation, per Nic's comment.

But for future users finding this question, there are definitely ways to improve native SQL database backup and restore times:

Backup your databases with compression: I/O is typically the bottleneck, this shifts some of the work to CPU.
Change your storage configuration: Store backups locally or on higher-performance SAN
Backup your databases to multiple files (stripes): This might depends on your specific storage and network configuration, but I've found 4- and 8-stripe backups and restores in our environment complete 2x-10x faster than single-stripe backups. Certainly worth some testing.
Make sure instant file initialization is enabled: Helps you carve out the space for restoring files very quickly
Reduce the size of the database: Drop old tables, etc. When possible, of course.
Shrink away empty space: All the appropriate warnings and caveats about shrinking apply, of course.
Enable page or row compression: Makes the database files smaller, so less to write upon restore.


SQL Server 2014 slow insert I have a very strange slow insert issue, here I am providing the table structure, and the insert statement used.

The database is clean, contains no other tables, no indices, no constraints, no triggers.

I tested this on two different machines, with high speed SSD drives, 8 cores 3.5 GHz processors. The server can read IO up to 700MB/s and can write up to 300MB/s

However, while executing the below insert statement, the processor is almost at 5% max and the IO operation less than 500KB/s which means that SQL Server is not using the server resources properly.

The maximum I can achieve is 250 row/s. The below insert statement is inserting 1000 rows and require 4 seconds to finish.

Any idea what is causing this slow in inserting?

SQL Server used is 2014 Developer Edition SP2

Thanks in advance.

CREATE TABLE [dbo].[MyTable](
    [ID] [int] IDENTITY(1666811137,1) NOT NULL,
    [col0] [smallint] NULL,
    [col1] [smallmoney] NOT NULL,
    [col2] [tinyint] NOT NULL,
    [col3] [tinyint] NULL,
    [col4] [tinyint] NOT NULL,
    [col5] [tinyint] NOT NULL,
    [col6] [datetime] NOT NULL,
    [col7] [varchar](15) NOT NULL,
    [col8] [varchar](20) NOT NULL,
    [col9] [tinyint] NOT NULL,
    [col10] [tinyint] NULL,
    [col11] [tinyint] NULL,
    [col12] [tinyint] NULL,
    [col13] [tinyint] NULL,
    [col14] [tinyint] NULL,
    [col15] [tinyint] NULL,
    [col16] [bit] NULL,
    [col17] [smallint] NULL,
    [col18] [varchar](17) NULL,
    [col19] [smallint] NULL,
    [col20] [tinyint] NULL,
    [col21] [varbinary](254) NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

/********* INSERT *********/
DECLARE @i int
SET @i = 0

WHILE @i < 1000
BEGIN
    SET @i = @i + 1

    INSERT INTO MyTable (col3, col1, col2, col4, col5, col6, col7, col8, col9, col21, col10, col11, col12, col13, col14, col18, col15, col16, col0, col19, col17, col20)
    VALUES (5, 0, 1, 1, 1, GETDATE(), 'ABC', '1234567890', 0, CAST('0x74657374' AS VARBINARY(MAX)), 1, 1, 1, 1, 0, 'VAL', 1, 0, 37, 37, 2, 0)
END

Looking at physical IO is not a good indication of what work your query is doing. SQL Server does not write changes to disk immediately--it writes to the buffer cache (in memory), then flushes dirty pages to disk during the next checkpoint. Likely, all 1000 inserts will fit into memory, and no writes will happen on the data file until the next checkpoint.

Instead, try looking at the wait type that you are experiencing when you experience slowness for this operation.

If you are experiencing WRITELOG waits, try wrapping the WHILE loop into an explicit transaction. This would reduce the number of times SQL Server flushes the log buffer to disk, and improve performance.

You can then investigate sys.dm_tran_database_transactions and sys.dm_io_pending_io_requests to zero in on whether the IO is pending at the OS or disk subsystem.

Before digging too deeply into the DMVs, you might try looking into the following things:

Is your log file properly sized? Do you have an appropriate number of VLFs?
Use Perfmon to look at the PhysicalDisk object to look at Avg. Disk sec/Read, Avg. Disk sec/Write, and Avg. Disk Queue Length. Are IOs taking longer than 15ms? Is your queue length larger than 1?
Move the transaction log to a different physical disk to segregate IO.

Assuming your disk subsystem is not the bottleneck (and you seem confident that is not the problem), then the first place I would look would be at the number of VLFs in your log file.


Delete every row that is not the first duplicate I want to enforce uniqueness on a column, but in order to do so, I need to manually clean up a lot of duplicates. I have found out that all the duplicates have no references to them, so a simple sorting by resh id and then manually deleting every duplicate resh id apart from the first one with works (a la DELETE FROM org_units WHERE id in (3,4,5)), but it is boring, so of course I would like to create some kind of query to clean up this mess.
The data looks like this:
org_units
ID  | RESH_ID
-------------
1   |  a
2   |  b
3   |  a
4   |  a
5   |  b
6   |  c

A first step would perhaps to do some kind of ordering:
select id, resh_id from org_unit order by resh_id, id;

I am not sure how to go from here to deleting everything apart from the first row in each "group" (group by might be handy?).
I would like to end up with a situation like
ID  | RESH_ID
-------------
1   |  a
2   |  b
6   |  c

This removes all rows apart from the first in each group ordered by resh id:
DELETE FROM org_unit o
WHERE o.id NOT IN
  (SELECT MIN(ID) AS id
   FROM  org_unit
   GROUP BY resh_id
   ORDER BY resh_id);

If you wanted the last two or three, you would use the window function ROW_NUMBER and select the wanted number

CREATE TABLE "org_units" (
  "ID" INTEGER,
  "RESH_ID" VARCHAR(1)
);

INSERT INTO "org_units"
  ("ID", "RESH_ID")
VALUES
  ('1', 'a'),
  ('2', 'b'),
  ('3', 'a'),
  ('4', 'a'),
  ('5', 'b'),
  ('6', 'c');

  DELETE FROM "org_units" o
where o."ID" in (SELECT MIN("ID")    FROM  "org_units" GROUP BY "RESH_ID" )

3 rows affected

SELECT * FROM org_units

ID | RESH_ID
-: | :------
 3 | a
 4 | a
 5 | b

CREATE TABLE org_units2 (
  "ID" INTEGER,
  "RESH_ID" VARCHAR(1)
);

INSERT INTO org_units2
  ("ID", "RESH_ID")
VALUES
  ('1', 'a'),
  ('2', 'b'),
  ('3', 'a'),
  ('4', 'a'),
  ('5', 'b'),
  ('6', 'c');

DELETE FROM "org_units2" o
where o."ID" NOT in (SELECT MIN("ID")    FROM  "org_units2" GROUP BY "RESH_ID" )

3 rows affected

SELECT * FROM org_units2

ID | RESH_ID
-: | :------
 1 | a
 2 | b
 6 | c

db<>fiddle here


PostgreSQL PITR restore I have a PostgreSQL 9.1 server running on a CentOS 5.8 32bit OS and I have enabled WAL archiving.   On this server, there are three databases: databaseA, databaseB and databaseC.  Someone deletes a table at 12 pm on databaseB and I want to restore databaseB to just before the time at which the table was deleted.  The time is now 3 pm.

How can I just restore databaseB up to 12 pm without losing three hours worth of data in the other two databases?

postgresql.conf

wal_level = archive
archive_mode = on
archive_command = 'test ! -f /opt/pgsql/logs/%f && cp %p /opt/pgsql/logs/%f'
max_wal_senders = 100

recovery.conf

restore_command = 'cp /opt/pgsql/logs/%f %p'
recovery_target_time = '2012-06-29 11:59:59 CEST'

I ran my base backup at 9 am

pg_basebackup -h 127.0.0.1 -D /opt/pgsql/backup

The PostgreSQL service was shutdown at 3 pm.

I would do a point in time recovery to a different location, restoring to the desired time, and pg_dump the problem database.  I would drop the one database on the normal location, create it again, and load the pg_dump output.

Make sure you make and save a file-system level copy of the cluster's data directory tree before you start anything like this.


Why does Deadlock occur for this INSERT/UPDATE combination despite seemingly holding an X lock? I am regularly seeing deadlocks in a production MySQL 5.5 database but cannot determine exactly what the cause is.

For context, we're inserting new records into a database which are triggered by another system. In some cases, these actions can be triggered twice, which has caused exceptions where the system correctly complains the row already exists.

We have therefore attempted to implement something of the form:

Check if any rows already exist, grabbing a lock so no one else can INSERT and invalidate this query
If any rows exist, return that row and end the transaction.
If no rows exist, INSERT the new row
Release the lock, allowing other transactions to complete step 1, and all being well, jumping out via 2.

The theory being that any duplicate attempts to INSERT will hit step 1 first, and as they'll have to wait for 4 to complete, will not be able to attempt a duplicate.

Here's the deadlock trace:

------------------------
LATEST DETECTED DEADLOCK
------------------------
160324 12:27:51
*** (1) TRANSACTION:
TRANSACTION 52CA7682, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 31 lock struct(s), heap size 6960, 2499 row lock(s)
MySQL thread id 21789278, OS thread handle 0x2ae64b6ca700, query id 652918526 hostname ip db_name Updating

update offer set OfferClickCounter = OfferClickCounter + 1 where quicklinkcode = 'yycajzzw'

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3625 page no 45 n bits 168 index `PRIMARY` of table `db_name`.`offer` trx id 52CA7682 lock_mode X waiting
Record lock, heap no 33 PHYSICAL RECORD: n_fields 25; compact format; info bits 0
 0: len 4; hex 800108d5; asc     ;;
...
 24: len 4; hex 80000081; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 52CA7679, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
16 lock struct(s), heap size 3112, 12 row lock(s), undo log entries 1
MySQL thread id 21789619, OS thread handle 0x2ae64afde700, query id 652918532 hostname ip the_db_name update

INSERT INTO user_offer (CreatedDate, ...) VALUES ('2016-03-24 12:27:51', ....)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3625 page no 45 n bits 168 index `PRIMARY` of table `db_name`.`offer` trx id 52CA7679 lock_mode X locks rec but not gap
Record lock, heap no 33 PHYSICAL RECORD: n_fields 25; compact format; info bits 0
 0: len 4; hex 800108d5; asc     ;;
...
 24: len 4; hex 80000081; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3625 page no 45 n bits 168 index `PRIMARY` of table `db_name`.`offer` trx id 52CA7679 lock mode S locks rec but not gap waiting
Record lock, heap no 30 PHYSICAL RECORD: n_fields 25; compact format; info bits 0
 0: len 4; hex 800108d2; asc     ;;
...
 24: len 4; hex 80001ee1; asc     ;;

*** WE ROLL BACK TRANSACTION (2)

My understanding from this Deadlock trace is that:

Transaction 2 is attempting to INSERT into the user_offer table - which as mentioned earlier required a SELECT .. FOR UPDATE. Transaction 2 therefore holds an X lock.
Transaction 1 wants an X lock to UPDATE the offer table, and is thus waiting on Transaction 2 (so far, so good)
Transaction 2 also needs an S lock to be granted on the same offer table (I believe due to the fact that the user_offer table and offer table are related by a foreign key on the user_offer table).

i.e.:

t0: T2: SELECT .. FOR UPDATE locks offer with X lock

t1: T1: UPDATE asks for offer X lock and waits

t2: T2: INSERT INTO user_offer asks for S lock for offer

S lock can't be granted at t2 because X lock is already held by t0 (even though it's the same transaction), and another X lock is in the 'queue' before hand. Deadlock.

Have I got that right? How would I resolve this?

Any thoughts welcome and much appreciated.

Can some or all of the 4 steps be replaced by a single query:

INSERT ... ON DUPLICATE KEY UPDATE ...

or maybe

INSERT IGNORE ... and check rows_affected?

It would be better if you showed us all the SQL between BEGIN and COMMIT.

Are you doing any time-consuming processing between BEGIN and COMMIT?  Can any of it be moved out.  The faster the transaction runs, the less likely it will deadlock.

Do you have code to rerun the transaction when it deadlocks?  This a useful "last resort".  (It is not yet obvious whether the particular deadlock can always be avoided.)


Is it possible to accidentally use some paid feature in Oracle XE database while in an Enterprise environment? IT in my org are wary about approving me installing Oracle Express Edition.
https://www.oracle.com/database/technologies/xe-downloads.html

Their reasoning is that apparently some DBA once said that devs can trigger some enterprise features accidentally while using it, and rack up hundred thousands of dollars in usage fees. I'm skeptical because I can't find any information online that would suggest that. Though I know Oracle doesn't have a great reputation.

From looking at XE description, it seems to be designed so that devs can install it on their environments to get a prototype working etc. Without having to worry about fees and licenses.
Is it true, can using Oracle XE on my dev box in a large company somehow rack up fees without knowing about it?

Oracle Express Edition is free software that can be used even for commercial purpose.

What the DBA said is true for Enterprise Edition where some features can not be completely disabled (e.g. Advanced Compression), and it was true for Standard Edition in versions below 11g (e.g. Diagnostic Pack).

Express Edition will be fine.

For a personal learning/development environment, you could even use Enterprise Edition, if you will be the only one using it.


Problem reading data from secondary when reorganizing clustered index We have an AOAG in SQL Server 2014 SP2 CU5 (3 nodes). There is a database with Read Committed Snapshot Isolation level ON. We have a large table compressed. Some of our bigger queries on this table are performed into the secondary.
Then there is a night job on the primary node to reorganize indexes on several tables. When it hits the clustered index of the mentioned table we get the following error:

Transaction aborted when accessing versioned row in table 'xxxx' in database 'yyyy'. Requested versioned row was not found because the readable secondary access is not allowed for the operation that attempted to create the version.

At some point, the big queries were performing the reads with the hint READUNCOMMITTED. I thought that it was the cause of this error so I removed them. But the error is still there.
Any ideas?
Current setup:

02 secondary is on synchronous mode
03 secondary on asynchronous mode

Table details

RowCounts: 122.567.668
TotalSpaceMB: 18.460
UsedSpaceMB: 18.238

Definitions:
CREATE TABLE [dbo].[big_table](
[ID] [int] NOT NULL IDENTITY(1, 1),
1 [int] NULL,
2 [datetime] NULL,
3 [int] NULL,
4 [int] NULL CONSTRAINT [DF_ccc_bUnits] DEFAULT ((0)),
5 [money] NULL,
6 [money] NULL,
7 [int] NULL,
8 [int] NULL CONSTRAINT [DF_ccc_MinDays] DEFAULT ((0)),
9 [int] NULL,
10 [int] NULL,
11 [float] NULL,
12 [money] NULL,
13 [int] NULL,
14 [int] NULL,
15 [nvarchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
16 [money] NULL,
17 [money] NULL,
18 [int] NULL,
19 [int] NULL,
20 [money] NULL,
21 [money] NULL,
22 [money] NULL,
23 [money] NULL,
24 [money] NULL,
25 [datetime] NOT NULL CONSTRAINT [DFcccadded] DEFAULT (getdate()),
26 [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
27 [money] NOT NULL CONSTRAINT [DFcccBrf] DEFAULT ((0)),
29 [money] NOT NULL CONSTRAINT [DFcccHB] DEFAULT ((0)),
30 [money] NOT NULL CONSTRAINT [DFcccFB] DEFAULT ((0)),
31 [money] NOT NULL CONSTRAINT [DFcccAllBoards] DEFAULT ((0)),
32 [money] NOT NULL CONSTRAINT [DFcccChildBrf] DEFAULT ((0)),
33 [money] NOT NULL CONSTRAINT [DFcccChildHB] DEFAULT ((0)),
34 [money] NOT NULL CONSTRAINT [DFcccChildFB] DEFAULT ((0)),
35 [money] NOT NULL CONSTRAINT [DFcccChildAllBoards] DEFAULT ((0)),
36 [int] NULL CONSTRAINT [DFcccShow_1] DEFAULT ((0)),
37 [timestamp] NOT NULL,
38 [money] NULL,
39 [money] NULL,
40 [money] NULL,
41 [money] NULL,
42 [money] NULL,
43 [money] NULL,
44 [money] NULL,
45 [money] NULL,
46 [int] NOT NULL CONSTRAINT [DFcccReleaseHour] DEFAULT ((0)),
47 [int] NULL,
48 [int] NULL,
49 [money] NULL,
50 [money] NULL,
51 [float] NULL
) ON [PRIMARY]
WITH (DATA_COMPRESSION = PAGE)
GO
CREATE UNIQUE CLUSTERED INDEX [IXccc] ON [dbo].[big_table] (1, 2) WITH (FILLFACTOR=90, DATA_COMPRESSION = PAGE) ON [PRIMARY]
GO
ALTER TABLE [dbo].[big_table] ADD CONSTRAINT [PKccc] PRIMARY KEY NONCLUSTERED ([ID]) WITH (DATA_COMPRESSION = PAGE) ON [secondary]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IXcccstamp] ON [dbo].[big_table] (36) INCLUDE (1, 2) WITH (FILLFACTOR=100) ON [PRIMARY]
GO

So, after running out of possible solutions we opened a support case to Microsoft. They asked to run a tool to gather some info while the process was running and afterwards they analyzed it. Here is their answer:

You select command is running fine if it starts before reorganize index job is initiated
Select command is failing if it starts after re-organize job is initiated.
Found the above behavior is expected behavior in AG.

Even though the read operations do not take shared locks because of row versioning, these operations take schema stability (Sch-S) locks, which can block redo operations that are applying DDL changes. DDL operations include ALTER/DROP tables and Views but not DROP or ALTER of stored procedures.
In our case while re-organize index is running on primary redo operation for the same is being performed on secondary replica and is acquiring Sch-M (Schema modification locks), when select command is trying to access the same replica it is not able to acquire Sch-S (Schema Stability) locks as it is already occupied by redo thread which is having Sch-M locks.
In this scenario your application is generating errors including time out errors.

To avoid this kind of situations it is recommended to schedule re-organize index task in off business hours

We don't have "off business hours", we run 24/7/365. Is not a definitive answer, but at least we know the root cause of this issue. So the approach will be to temporarily change connection string so the task that is failing will read from primary AG node instead of secondary AG node the day the reindex run.


SQL Server: Who created an audit and who altered it last? I am trying to find out who created an audit (found under Audits) but I don't seem to have any luck. I've already tried many queries such as these:

Blog 1, Blog 2, Blog 3
And

SELECT *
FROM   [sys].[objects]
SELEcT * FROM sys.server_audits

They seem to work for all other objects, but not for audit. I need to know which user created the audit and which user altered it last. sys.server_audits does not help.

A trace isn't a database-level object, so none of those queries you were reading about had any chance. The default trace has this information, though, under event 117 (AUDIT CHANGE AUDIT EVENT) - assuming you haven't disabled it and that the audit was created recently enough that it's still there (it's a rolling trace).

DECLARE @path NVARCHAR(260);

SELECT
   @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT *
  FROM sys.fn_trace_gettable(@path, DEFAULT)
  WHERE EventClass = 117
  ORDER BY StartTime DESC;


pg_dump -> import traject using "upsert" paradigm? Well say I have two physically separate database and I wish to replace database B with data from A. Except that I wish to keep the data in B which does not exist in A?
So basically I'd go over each entry in A and use an insert-or-replace like:
INSERT INTO table_name (name, value)
VALUES ('hello', 'world')
ON CONFLICT (name) do nothing;

IE the following databases would be merged into the third:
A)
name | value
A    | 1
B    | 2

B)
name | value
B    | 10
C    | 20

MERGED)
name | value
A    | 1
B    | 2
C    | 20

If I use the -c option from pg_dump it would just throw away the "c" value in the merged table.

I'd simply restore the table in another schema and forge my upsert command to insert/update B data from A data.
If you're using the plain text format using pg_dump, you'll be able to change the search_path at the beginning of the file to create your new table on the new schema.
If you're using a custom format, you can either get the SQL format using pg_restore and change it or use the -l flag to get the list file and edit it accordingly.
Have a nice day


3 Node mongo db authentication setup fails I have 3 node mongodb replica set as below

Primary node Secondary node Arbiter node

Replication set is enabled for all three. I am trying to set up authentication for these nodes and i followed the below steps to configure it.

Step 1: Created a root user, added him to role: root with password in the primary node

Step 2: Created a key file using the command - openssl rand -base64 741 > mongodb.key in the primary node and copied the key file to all the 3 nodes

Step 3: Added the below configuration to all three nodes.

Security: authorization: enabled keyFile: /pathtokeyfile

Step 4: sudo service mongod restart, on all three nodes

After completing above steps i tried accessing the DB with username and password in the primary node and i could read the DB and files

When i try the same in secondary and arbiter nodes i get the below error

  rsss0:SECONDARY> show dbs
  2019-03-06T09:10:40.884+0000 E QUERY    [thread1] Error: listDatabases failed:{
      "operationTime" : Timestamp(1551863438, 9),
      "ok" : 0,
      "errmsg" : "not master and slaveOk=false",
      "code" : 13435,
      "codeName" : "NotMasterNoSlaveOk",
      "$clusterTime" : {
          "clusterTime" : Timestamp(1551863438, 9),
          "signature" : {
              "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
              "keyId" : NumberLong(0)
          }
      }
  } :
  _getErrorWithCode@src/mongo/shell/utils.js:25:13
  Mongo.prototype.getDBs@src/mongo/shell/mongo.js:67:1
  shellHelper.show@src/mongo/shell/utils.js:860:19
  shellHelper@src/mongo/shell/utils.js:750:15
  @(shellhelp2):1:1

As shown in your question you are now connected with the secondary node. Since secondary may contain stale data, by default MongoDB won't allow you to read any data from secondary.
There are two options to read your data

Connect primary node (use the correct port number )
From secondary, run rs.slaveOk(). This will allow you to read data from the secondary.

Note: run rs.status() or rs.isMaster() to find out which one is primary.


When to use unique composite keys? When creating a database structure, I tend to create unique composite keys for every group of data that needs to be unique. Beside them I usually use a primary key (usually INT AI id), unless a composite key is really enough to identify the records.

On one hand, the benefit is that I can avoid the insert of 'bad records'.
Are there cases where the usage of these keys becomes too much or is sign of bad db design?

If the intention is to build a relational database, then the objective should be to reflect the informational characteristics of the relevant business environment in the database layout, so the short answer is:
A database designer must define one or more composite KEYs whenever each row of the table under consideration has to be uniquely differentiated by the values of one or more combinations of columns.
In this way, a database designer is responsible for (1) the identification of that business context requirement and for (2) the declaration of the corresponding multi-column KEY constraint(s).
As you know, it is his or her duty to (i) identify and (ii) declare single-column KEYs too, whenever necessary.
Therefore, the general procedure should be to always analyze very carefully each particular situation, inspecting the meaning ascribed to every individual table and column, taking into account all their interconnections regarding the entire database layout.
Extra column for system-controlled surrogates
By analyzing very carefully each particular situation, one can as well determine the quite specific cases when, apart from the declaration of one or more single- or multi-column natural KEYs (as demanded), a table may benefit from the inclusion of an extra column to retain system-controlled surrogatesa (surrogates for brevity, which are implemented, e.g., via an IDENTITY property in Microsoft SQL Server or an AUTO_INCREMENT attribute in MySQL), as such an artefact is at all times an attached element that entails supplementary administration tasks (e.g., it regularly needs a accompanying INDEX at the physical level, with all its implications).
The point brought up above is pertinent because, according to my interpretation of the following question excerpt “I usually use a primary key (usually INT AI id)”, what you are describing is, precisely, a column that retains surrogates.
In this respect, since optimizing the usage of the database platform resources (the database management system, the hardware, etc.) is paramount, it must be mentioned that a database practitioner should employ any kind of technical additions only when it is justified by the features of the business context represented in the database of significance.
A column holding surrogates that somewhat work as “substitutes” for the respective natural KEY values can sometimes be of help when, e.g., it is utilized in tables that:

Represent entity types that are at the top layer of the relevant conceptual data hierarchy.
Have natural KEYs that are wide and/or heavy, so it may be useful to prevent referencing them from other tables via FOREIGN KEY (FK) constraints.

In this manner, FK references are made to a column containing values that are physically lighter in terms of bytes (when compared with references made to a relatively large combination of columns and/or references made to columns set up with heavy types and sizes) which has impacts on, e.g., disk space and memory consumption due to the reduction of the physical-level scaffoldings that support the concerning logical-level definitions.
General advantages of multi-column keys
Data manipulation
In cases of tables that stand for (conceptual) entity types that belong in the lower layers of the data hierarchy of the business context of interest, composite PRIMARY KEYs (PKs) tend to be more effective since they commonly are called for in (logical) SELECT operations that include columns that point to PKs of tables at higher layers of the aforementioned hierarchy, while columns with surrogate values are typically not involved.
Along these lines, the use of composite PKs is generally convenient to avoid needless JOINs (which does not imply in any way that the JOIN operator is a bad tool, quite the opposite, it is an essential, elegant and powerful instrument with regard to data derivation that functions very fast in the right settings), and in agreement with the optimization of platform resources previously brought up, one should employ and supply mechanisms for data manipulation that are as efficient as possible.
For instance, with a hypothetical logical-level SQL-DDL layout like the one that follows:
--
-- Let us suposse that we are building a database in which:

-- a. A row of Foo may be associated with zero-one-or-many rows of Bar.

-- b. A row of Bar can be associated with zero-one-or-many rows of Baz.

-- c. A row of Foo is identified by the values of its own columns
--    (Qux, Corge, Grault, Garply, Etcetera) alone.

-- d. A row of Bar is identified by its BarDateTime value of creation along with
--    the FooId value of the Foo row with which it is associated.

-- e. A row of Baz is identified by its BazNumber value along with
--    the FooId and BarDateTime values of the Bar row with which it is associated.

-- 1. The representation of the ‘top layer’
--    of the hypothetical conceptual data hierarchy:
CREATE TABLE Foo (
    FooId       INT         NOT NULL, -- Column for surrogates.
    Qux         CHAR(60)    NOT NULL,
    Corge       CHAR(80)    NOT NULL,
    Grault      VARCHAR(90) NOT NULL,
    Garply      CHAR(75)    NOT NULL,
    Etcetera    CHAR(48)    NOT NULL,
    CreatedDate DATE        NOT NULL,
    --
    CONSTRAINT Foo_PK PRIMARY KEY (FooId),
    CONSTRAINT Foo_AK UNIQUE      (Qux, Corge, Grault, Garply, Etcetera) -- ‘Voluminous’ composite ALTERNATE KEY.
);

-- 2. Conveying the ‘middle layer’ of the
--    conceptual data hierarchy:
CREATE TABLE Bar (
    FooId       INT      NOT NULL,
    BarDateTime DATETIME NOT NULL,
    Waldo       CHAR(30) NOT NULL,
    Etcetera    CHAR(62) NOT NULL,
    --
    CONSTRAINT Bar_PK        PRIMARY KEY (FooId, BarDateTime), -- Composite PK.
    CONSTRAINT Bar_to_Foo_FK FOREIGN KEY (FooId)
    REFERENCES Foo (FooId)
);

-- 3. Portraying the ‘bottom layer’
--    of the conceptual data hierarchy.
CREATE TABLE Baz (
    FooId       INT      NOT NULL, -- This component of the PK provides ease of ‘navigation’.
    BarDateTime DATETIME NOT NULL, -- This component of the PK also provides ease of ‘navigation’.
    BazNumber   INT      NOT NULL,
    Fred        CHAR(28) NOT NULL,
    Etcetera    CHAR(57) NOT NULL,
    CreatedDate DATE     NOT NULL,
    --
    CONSTRAINT Baz_PK        PRIMARY KEY (FooId, BarDateTime, BazNumber), -- Meaningful composite PK.
    CONSTRAINT Baz_to_Bar_FK FOREIGN KEY (FooId, BarDateTime)             -- Composite FK.
    REFERENCES Bar (FooId, BarDateTime)
);

--

…it is possible to JOIN (a) columns FROM table Foo with (b) columns FROM table Baz without having to “pass” first “through” (c) table Bar, making use of Foo.FooId and Baz.FooId as the only necessary conditions. To this extent, when one can save one or more “manoeuvres” in a certain data manipulation operation, more platform resources will be available for other concurrent processes.
In this series of posts you can see a more “concrete” example of a conceptual schema and its respective logical representation where the configuration of composite PKs offers multiple advantages.
Tendencies
Nevertheless, these are tendencies, hence, as noted before, you should always inspect every exact case (considering its effects on the overall scenario) to define whether or not appending a column for surrogates is beneficial.
Data constraints
On the other hand, there is a quite relevant detail that you refer to in the paragraph cited below:

When creating a database structure, I tend to create unique composite keys for every group of data that need to be unique. Beside them I usually use a primary key (usually INT AI id), unless a composite key is really enough to identify the records.

It is really important to take that detail into account because a surrogate value does not carry business context meaning, thus it does not uniquely identify the real data elements of a row, and that aspect is exclusively guaranteed, at all times, by meaningful natural (be it single- or multi-column, either PRIMARY or ALTERNATE) KEY values.
Apart from that, it is as well opportune to remember that composite KEYs, working in conjunction with FKs, are of great value to establish declarative restrictions that ensure that the pertinent rows comply with the cardinality ratios of the conceptual relationships/associations that exist between the entity types represented by the tables of a database.
As you are well aware, the task of constraining the data properly is of enormous significance in the field of database administration, for it ensures that the information managed is consistent with its conceptualization in the real world (i.e., the applicable business environment). In this manner, having consistent data, the involved manipulation operations yield reliable results, and the end users of a database (and/or one or more application programms sharing access to it) can use it as a valuable instrument to make well-informed decisions.
Relevant scenario
Some of these topics and other logical, physical and practical factors are discussed in my answer to the question entitled

“Third Normal Form: Composite PRIMARY KEY vs System-Assigned Surrogate (IDENTITY)”,

so you might find those posts of help.
Terminology
As for terminology concerns, you may also find of interest my deliberations contained in the meta post entitled

“Issues about the [unique-key] (sic) tag and its synonyms”.

Endnote
a System-controlled surrogates (i.e., those generated and assigned by the database management system) were assessed in the 1979 paper named Extending the Database Relational Model to Capture More Meaning by Dr. Edgar Frank Codd, the creator of the relational paradigm. In such paper, it is prescribed that, among other points, system-controlled surrogate values must never be displayed to database users, although in most databases I have observed, that rule is not complied with (perhaps it should be enforced by the database management system itself, automatically).
 This answer is based on the supposition that, in the scenario at hand, system-controlled surrogates are shown just like normal business context values. It is worth to point out that system-controlled surrogates are not part of the original relational model, as published in the 1970 paper entitled A Relational Model of Data for Large Shared Data Banks, also written by Dr. Codd, of course.


How to find repeating records effectively? I have a registration application for registering 6 courses. The program will check the repeated register users first. Those users that needed to be checked comes from last year. If users registered for one course, they cannot register for any other courses.
---firstroundregisted table structure---

id|englishname  |idcard |
1 |CHAN SIU MING|A1234  |
2 |TAK HO KANG  |Z4123  |

---Contain >5000 last year registered users records for the 6 courses---

The sql in PHP as below.
SELECT * FROM firstroundregisted
WHERE englishname='".$_POST['studentname']."'
AND idcard='".$_POST['idcard']."'
LIMIT 1;

Is this sql the fastest way to find out if the user registered or not?
Others:

I asked a question here :
How to increase Max_used_connections?
Some gentlemen suggested me to optimizing the sql query so I start a new question to focus on optimizing. Thank you all for your help.

Is this sql the fastest way to find out if the user registered or not?

It's close enough.
If the table is indexed on englishname (and, preferably, idcard as well) then yes.  Indeed, if those two uniquely identify each row, then they really ought to be the Primary Key of the table.
If the table is not indexed, then it really ought to be.
However ...
Never use "select *" in Production code.
Never use raw, User-supplied values directly in dynamically generated SQL.
Doing so is one of the quickest ways to get your table and, possibly, your whole database well-and-truly fouled up.  The problem is known as an SQL Injection Attack and your code is wide open to one.
Obligatory XKCD Reference: Little Bobby Tables.
Your best plan would be to move to using Prepared/Parameterised Statements but, failing that, you must "escape" the values from the User so that they form valid and safe SQL.


Why can't I get a lock on an InnoDB table? I have problem with lock table command: LOCK TABLE v3_cam_date WRITE;
the command always running with state:   "Waiting for table metadata lock".

Try to find out which thread was hold the lock on the table v3_cam_date by using command:

SHOW OPEN TABLES FROM reportingdb
 LIKE '%v3_cam_date';

Database    Table         In_use    Name_locked

reportingdb v3_cam_date 0   0

(In_use=0 => have no any lock on the table).
And I can't get write lock only on the table v3_cam_date while can get write lock on other tables.

Try to SHOW FULL PROCESSLIST;
return about 30 thread with idle state.

Detail List
InnoDB Status
MySQL : 5.5.13
Engine: InnoDB

Thanks,

According to the MySQL Documentation on MetaData Locking

  If the server acquires metadata locks for a statement that is
  syntactically valid but fails during execution, it does not release
  the locks early. Lock release is still deferred to the end of the
  transaction because the failed statement is written to the binary log
  and the locks protect log consistency.

Somone submitted this state as a bug, but the bug report claims it is not a bug.

Both of these links describe DDL (Data Definition Language) against a table as a contributing factor. What is interesting to keep in mind is that doing something like TRUNCATE TABLE is, in reality, DDL. We have to aware of any SQL we run. We may think it is SQL that is part of a transaction when, in fact, it is DDL. TRUNCATE TABLE is an example of DDL we think is DML. This can cause current transactions to commit and may have unpredictable results when it comes to writing binary log events as the first link states:

  Before MySQL 5.5.3, when a transaction acquired a metadata lock for a
  table used within a statement, it released the lock at the end of the
  statement. This approach had the disadvantage that if a DDL statement
  occurred for a table that was being used by another session in an
  active transaction, statements could be written to the binary log in
  the wrong order.


Alternative way to compress NVARCHAR(MAX)? I am trying to compress some tables that have NVARCHAR(MAX) fields. Unfortunately, the row and the page compression do not have the desire impact (only ~100/200 MB saved for 20 GB table). Also, I am not able to apply column store and column store archival compressions because they do not support compression of NVARCHAR(MAX) fields.

Can anyone tell if I have any alternatives here?

I also guess the row and page compression do not have effect because the content of the NVARCHAR(MAX) columns is unique.

Both page and row compression do not compress BLOBs.

  Because of their size, large-value data types are sometimes stored separately from the normal row data on special purpose pages. Data compression is not available for the data that is stored separately.

If you want to compress BLOBs you need to store them as VARBINARY(MAX) and apply your stream compression algorithm of choice. For example GZipStream. There are many examples how to do this, just search for GZipStream and SQLCLR.


Why is this query sometimes slow, and what can I do to make it faster? I have a query to bin some log lines on a pre-processed "timeslice" field (this is unique every 15 minutes, for example "2018-04-11-15-3-3") There are approximately 6,000,000 rows in the database.

The performance seems slow and inconsistent; sometimes it will return a single timeslice and take 0.5 seconds, sometimes it will return thousands and take 0.0002 seconds.

SELECT  count(*), time_slice
    FROM  logs
    WHERE  deployment_id = 123
      and  type="FOO"
      and  received_at BETWEEN "2019-04-01 00:00:00" AND "2019-04-30 23:59:59"
    GROUP BY  time_slice

timeslice is a VARCHAR(18)
deployment_id is an INT(10)
type is a VARCHAR(16)
received_at is a DATETIME

I've created an index on each of those columns, which helps. It's still very inconsistent.

EXPLAIN SELECT tells me that it's using the deployment_id and type indexes.

Alternatively, is there a better way to bin lines on a 15 minute interval?

Most likely, the EXPLAIN plan is probably stating that the query will perform an index merge. That's when two indexes are searched and the intersection between them yields the results.
SUGGESTION
You can probably achieve slight better performance by creating one index as follows:
ALTER TABLE logs ADD INDEX (deployment_id,type,received_at,time_slice);

This will read from the index only to generate results. It should not have to read from the table. The reason ? Every column in the query is in the index.
TRADEOFF
This will make the table bigger but can improve read speed.
If the performance is the same after creating the index, you can probably do without the index at this time.
GIVE IT A TRY !!!


Quorum for 3 nodes AlwaysOn Availability Group If I am going to set an Alwayson Availability group with 3 nodes, with read-only routing. Do I need to configure a Quorum ?

You don't have a choice as to whether or not to have quorum: Always On Availability Groups are built on top of Windows Server Failover Clustering, which requires quorum voting to understand who the primary is for a given AG at a given time.

I think what you might be asking is whether or not you need an old-school quorum drive (like a Q drive on a SAN). These days, Windows Server Failover Clustering (WSFC) can either use a quorum drive witness, OR a file share witness.

Windows Server 2012 R2 includes dynamic witness capabilities: if you have an even number of voters (like if 1 of your 3 cluster nodes goes down), then Windows will automatically add in your witness in order to be a tiebreaker. That's why you want to configure the witness (either a disk or a file share) ahead of time - Windows will automatically manage its voting rights whenever you have node failures or network failures.

I hate telling people to read the manual, but in this case, Books Online's quorum section is actually fantastic. Just make sure to read the appropriate one for your version of Windows, because Win2008R2, 2012, and 2012R2 all have different quorum options.


Structuring group membership I have more of a conceptual question if anyone has examples or some ideas of how others have accomplished this.

On a sales reporting site, a user should be able to pull information based on the following hierarchical structure:

Account Level
Department Level
Division Level

The categories are encompassing as you move up the chain (ie. Dept includes all accounts within that group). A user could have access to several groups within each tier. But a user would not have cross-category filters (ie. both a Department & Division level filter).

Would all this be accomplished in a single table with a column indicating the filter level and several rows per user with each criteria? What other suggestions are there?

What we do in LedgerSMB is to have a system of business reporting dimensions (two tables, one of which establishes the dimensions and the second establishes the hierarchies of each dimension).  In your case you could probably just have one table of departments, with a self join to generate the hierarchy (WITH RECURSIVE CTE in a select statement would generate it).

Then you can have a view which generates your hierarchy for it, and you can link your accounts to groups.

This way they can be arbitrarily nested.  You aren't limited to three levels.  You could have 5 or 10 levels with some performance cost and you don't have to change your data.


I have a query which does not return result with the desired alias names of columns in PostgreSQL The following is my query which does not return result with the desired alias column names:

with d as (select distinct emp.ahrims_id, rank.name as rank
from hr_employee as emp, ana_rank as rank,
unit_identification_code as uic, record_record as rec
where emp.unit_identificatin_code_id = uic.id and
rec.hr_employee_id = emp.id and
rank.id = emp.rank_id
and uic.code in ('Z00100','Z00200','Z00300','Z00400','Z00450','Z00500','Z00698','Z00600',
'Z00699','Z00680','Z00700','Z00697','Z00800','Z00900','Z01000','Z01100',
'Z01200','Z01300','Z39000','Z39012','Z10000','Z10005','Z10007','Z10009',
'Z10100','Z10101','Z10200','Z10300','Z10301','ZACA97','ZOCG01','ZACA99',
'ZACA98','ZOCG02','Z10400','Z10500','Z10600','Z10700','Z10800','Z11000',
'Z36000','Z37000','Z20000'))
select(select count(*) as "GSG1 Military NCO" from d where rank in
('MSG','SFC','SGM','SGT','SSG','G1','G2','G3','G4','G5')),
(select count(*) as "GSG1 Military SDR" from d where rank in ('SDR','G6')),
(select count(*) as "GSG1 Civilian NCO" from d where rank in ('Civ11','Civ12','Civ13','Civ14','Civ15')),
(select count(*) as "GSG1 Civilian SDR" from d where rank in ('Civ16'))

It returns:

  ?column? | ?column? | ?column? | ?column?
----------+----------+----------+----------
     1615 |      695 |        0 |        1

My desired result:

   GSG1 Military NCO | GSG1 Military SDR | GSG1 Civilian NCO | GSG1 Civilian SDR
---------------------+-------------------+-------------------+------------------
     1615            |      695          |        0          |        1

You have to define the aliases for the columns outside the subqueries:

with d as (
  select distinct emp.ahrims_id, rank.name as rank
  from hr_employee as emp, ana_rank as rank,
       unit_identification_code as uic, record_record as rec
  where emp.unit_identificatin_code_id = uic.id and
        rec.hr_employee_id = emp.id and
        rank.id = emp.rank_id and
        uic.code in (
          'Z00100','Z00200','Z00300','Z00400','Z00450','Z00500','Z00698','Z00600',
          'Z00699','Z00680','Z00700','Z00697','Z00800','Z00900','Z01000','Z01100',
          'Z01200','Z01300','Z39000','Z39012','Z10000','Z10005','Z10007','Z10009',
          'Z10100','Z10101','Z10200','Z10300','Z10301','ZACA97','ZOCG01','ZACA99',
          'ZACA98','ZOCG02','Z10400','Z10500','Z10600','Z10700','Z10800','Z11000',
          'Z36000','Z37000','Z20000')
  )
select (select count(*) from d where rank in ('MSG','SFC','SGM','SGT','SSG','G1','G2','G3','G4','G5')) as "GSG1 Military NCO",
       (select count(*) from d where rank in ('SDR','G6')) as "GSG1 Military SDR",
       (select count(*) from d where rank in ('Civ11','Civ12','Civ13','Civ14','Civ15')) as "GSG1 Civilian NCO",
       (select count(*) from d where rank in ('Civ16')) as "GSG1 Civilian SDR"


Login report of Sysadmin We have got a requirement to provide login report from SQL Server for Sysadmin only - This would have log-in date and time when anyone logged into SQL server having sysadmin privilege.

Is there anyway we can get report from SQL server with details of log-ins of only those users who are Sysadmins on the database server.

I guess, in security tab we can enable both Success and Failed Logins however enabling this would add lot of overhead on server, currently we have enabled this only for failed logins. And I don't know if there is anyway to enable success login only for sysadmin and not for others.

Any help would be appreciated.

You should write a logon trigger that will log only sysadmins.
Here you can find more about logon triggers:
Logon Triggers

And here is a practical example of its use:
SQL Server – Login Auditing using Logon Triggers

P.S. To find out if the login is a sysadmin you can use this code:

select is_srvrolemember ('sysadmin',original_login())

UPDATE

If the logon trigger does not use EXECUTE AS clause (and in case of OP this is true), it's executed under the current login. So to check out if the login is sysadmin is sufficient execute

select IS_SRVROLEMEMBER('sysadmin');

without using original_login(). This will work even in case when the AD group is sysadmin, and Windows login is not sysadmin directly but access the server through membership in this AD group


Special letter behaviour problem? I asked question in very blurry manner because I cannot explain in short sentence what is my problem about.

In general I have a set of procedures which transforms text and XML. Inside the procedures I have a lot of NVARCHAR and XML variables and a table with XML column. Inside my data I have special letters (for example ò). When I run all the code the special character disappears and is visible as ? - which mean that I got VARCHAR instead of NVARCHAR.

In general I got everything ok until the final steps. I got EXEC which call stored procedure with ad hoc constructed variable of type NVARCHAR. I have into a table which contains XML field. In this case I got ?. If I run procedure manually (not in EXEC INTO) I got proper symbol.

Any ideas why this happens?
I checked all variables and they are NVARCHAR.

The question is a bit vague, but typically when you have Unicode characters and you end up with question marks or boxes, the problem is actually relatively simple. When you declare a Unicode string with 'single quotes', you need to prefix them with an N''. N stands for national, not nvarchar (admittedly, a U prefix - for Unicode -  would have made more sense).

Compare:

SELECT Prefix = N'', NoPrefix = '';

Results:

Prefix      NoPrefix
------      --------
           ??