How do I terminate the old connection when a user of the same account login again? I want to set up a server while multiple users could login at the same time, as long as they are not the "same user".
How could I set it up so when the user with the account username "Bob" login again, whether it's from the same computer or another, the old connection will be terminated?
Thanks for your help.
Much appreciated!
Edit:
"Bob" is a Database user account, the "User ID" in the "Connection String", I'm sorry I don't really know what it is called.

You probably want to use a Logon Trigger to manage this. This specific part of the docs linked above mentions how you can limit the number of sessions for a specific Login:

You can use logon triggers to audit and control server sessions, such
as by tracking login activity, restricting logins to SQL Server, or
limiting the number of sessions for a specific login.

The first example in the docs is close to what you want, except it'll rollback the newer logins. If you want to kill the older logins you can use the login_time column of sys.dm_exec_sessions and kill the older sessions for that specific login_name.


How can it be that count(distinct x, y) < # records distinct x, y I'd like to learn why the query

select distinct m.id, m.sex
from   member m
join   household h on m.household_id = h.id
join   contact c on c.household_id = h.id
where  c.contact_date between '2018-04-01' and '2018-04-30';

returns 923 rows, yet will return the value 918 with the query

select count(distinct m.id, m.sex)
from   member m
join   household h on m.household_id = h.id
join   contact c on c.household_id = h.id
where  c.contact_date between '2018-04-01' and '2018-04-30';

The query

select count(distinct m.id)
from   member m
join   household h on m.household_id = h.id
join   contact c on c.household_id = h.id
where  c.contact_date between '2018-04-01' and '2018-04-30';

also returns 923.

The statement select * from member m where sex not in ('Male', 'Female') returns no rows.

You have 5 records with m.sex is null I think.

Any operator applied to NULL (except IS [NOT] NULL and <=>) returns NULL treated as False.


SQL Server 2016 - Availability Groups - How To Copy Permissions To Secondary Node SQL Server 2016
SSMS v18.4

I want to create the same logins and give the same privileges on both node.
In primary node I have no problem about this operation.
But when I create the same login on secondary node and want to user mapping, it gives below error:

Create failed for login 'loginname'.
Additional information:
An exception occured while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
Failed to update database "dbname" because the database is read-only.
(Microsoft SQL Server, Error:3906)

But my database is not read-only.
I'm trying the give permission like this:
Node 2 (Secondary) -> Security -> Logins -> Right click on MyLogin-> Properties -> User Mapping -> Select database and choose dbo for default schema -> Select public and db_owner -> OK.

It gives this error all of the databases for secondary node.
How can I solve this problem without doing failover (failover can solve this because it does primary node to Node-2 but I don't want to failover)?
If it possible with using PowerShell, how can I do it on this operation on PowerShell for Node-2?
Thank you.
Best regards,

Use sp_help_revlogin procedure to safely script SQL logins and their passwords (and Windows-authenticated Logins as well), so you can re-create all those logins on secondary replica with exactly same SIDs, as they have on primary replica

https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

After you create all necessary logins on secondary replica with same SIDs as on primary, you need to use ScriptLoginPermissions procedure, find out if your logins on primary replica have any server-level permissions.

Those are permissions that are stored in the master database. If any of your logins have any server-level permissions on primary, you need to grant those permissions on secondary replica to these logins. ScriptLoginPermissions procedure helps you to generate T-SQL for that, it can be found here:
https://github.com/aleksey-vitsko/Database-Administrator-Tools

Database level permissions, that are granted at user-database level, you do not need to grant those at secondary replica.
And you do not need to do login to user mapping at secondary replica. Because any permission or user changes at primary, are copied to secondaries automatically, in Availability Groups.

You do not need to do any failover, all database-level permissions on primary, are also synchronized to secondary replica. You can't do any changes to permissions on secondary replica's, because user databases there are in read-only state
p.s. Important to have logins on primary and secondary replicas with matching SID (security identifiers, which can be seen at sys.server_principals)


Why are the passwords in MySQL (5.x) not salted? I'm working with a legacy application that is connected to a MySQL database (5.x). When I created two different users for the MySQL database with the same password, the hash of those passwords are the same.

Is there an option to enable salting of the passwords in mysql.user?

No, there isn't.

If this is a major issue for you, there is a PAM plugin available from Percona that works with MySQL if you want to authenticate against something external rather than using the internal mechanism.  MariaDB has a different solution.

Oracle also has a proprietary (non-free) PAM plugin for people who just like to give them money.

Although, unless you have some kind of compliance issue to deal with, the practicalities of the MySQL authentication protocol are such that this seems like a non-issue.  After all, your database server should generally not be accessible on the Internet, and if it is, it should be restricted to the hosts that can connect, and those hosts should be required to use SSL.

The server stores, essentially, the output of this expression:

CONCAT('*',UCASE(SHA1(UNHEX(SHA1('password')))));

mysql> select PASSWORD('password');
+-------------------------------------------+
| PASSWORD('password')                      |
+-------------------------------------------+
| *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select CONCAT('*',UCASE(SHA1(UNHEX(SHA1('password')))));
+--------------------------------------------------+
| CONCAT('*',UCASE(SHA1(UNHEX(SHA1('password'))))) |
+--------------------------------------------------+
| *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19        |
+--------------------------------------------------+
1 row in set (0.00 sec)

But that's apparently not just a misguided attempt along the lines of "if hashing once is good, then hashing twice is better."  It has to do with the way the authentication protocol works.

When a client tries to authenticate, the server sends a 20-byte random challenge string.

The client takes that string, and calculates this:

SHA1('password') XOR SHA1( "20-bytes random data from server"<concat>SHA1(SHA1('password')))

So the client is calculating the value that should be stored in the password column (if the correct password has been entered), appending that value to the 20 random bytes, taking the SHA1 of the whole thing, XORing it with the SHA1 of the password itself, and sending that back to the server.

Given that the inverse function of XOR is also XOR, The server can then take the response, and, having also calculated the SHA1() of the string including the 20 bytes, can XOR it back into SHA1('password'), then SHA1() that value...

SHA1(client-response XOR SHA1(CONCAT("same random 20-bytes",mysql.user.password)))

...and the output would be the value stored in the password column of the mysql.user table.

So in this exchange, neither the plaintext password nor what's stored in the password field is ever sent over the wire, just a 20-byte random challenge and a 20-byte response, which is itself the XOR of two hashed values and would also not be usable in a replay attack against the same account or on another account with the same password unless the server sent the same challenge and the passwords are the same.

Since two clients with the same password would arguably "never" get the same challenge, they would just as infrequently "never" send the same response for authentication.

Unless the server sent the salt to the client, I don't readily see how such an exchange could take place.  On the other hand, if the server were to send the salt to the client, that implies that the salt would have to exist on the server... and if the salt exists on the server, and I am in possession of one known password/salt combination, then identifying other accounts whose salt and my password matches their password hash, then identifying other accounts where my password could be used is no less trivial that it is under the current scheme.


Getting error while starting mysql by just typing in mysql terminal I am getting an error while starting mysql, by just typing mysql in the  terminal:

The error is:

amtz@amtz-VPCEA23EN:~$ mysql
ERROR 1045 (28000): Access denied for user 'amtz'@'localhost' (using password: NO)

That is not how you start mysql from the command line.

When you type mysql in the command line it will launch the mysql-client not the mysql-server, which looks for the user/password options to be provided as arguments, if they aren't provided it will expect them to be specified in ~/.my.cnf or /etc/mysql/my.cnf. If it still can't find the details there then it will then try the your current TTY user, which is what has happened to you.

If you want to login from the command line you can provide the username and password like so:

mysql -u <username> -p

If you don't want to have to do that every time then you can set the details in your ~/.my.cnf file.

[client]
user=<username>
password="<password>"

If you do actually want to start the mysql-server then it would depend on your operating system. For instance on Ubuntu you would do that with:

sudo service mysql start

Generally speaking you should be able to do

/etc/init.d/mysql start


Combine Results in Same Column to get Average Lets say I have a table that looks like the following:

Average Score
Team
Month

2
New York Team
May

3
San Fran Team
May

2
Chi Team
May

3
Texas Team
May

2
New York Team
June

3
San Fran Team
June

2
Chi Team
June

3
Texas Team
June

I want to get the average for certain combinations of teams. How would I get the average of New York Team + San Fran team in May, and the average for the Chi team + Texas team?
Basically I want New York + San Fran to = Tier 1 (average)
and Chi + Texas to = Tier 2 (average)
Thanks!

You can use a with clause and get so the average from every team per month and then simply add the data as you see fit

CREATE TABLE sportstable
    ("Average Score" int, "Team" varchar(13), "Month" varchar(4))
;

INSERT INTO sportstable
    ("Average Score", "Team", "Month")
VALUES
    (2, 'New York Team', 'May'),
    (3, 'San Fran Team', 'May'),
    (2, 'Chi Team', 'May'),
    (3, 'Texas Team', 'May'),
    (2, 'New York Team', 'June'),
    (3, 'San Fran Team', 'June'),
    (2, 'Chi Team', 'June'),
    (3, 'Texas Team', 'June')
;

✓

8 rows affected

WITH average_month AS (SELECT "Month",
MAX(CASE WHEN "Team" = 'New York Team' THEN "Average Score"
            ELSE 0
       END) AS "New York Team",
       MAX(CASE WHEN "Team" = 'San Fran Team' THEN "Average Score"
            ELSE 0
       END) AS "San Fran Team",
       MAX(CASE WHEN "Team" = 'Chi Team' THEN "Average Score"
            ELSE 0
       END) AS "Chi Team",
       MAX(CASE WHEN "Team" = 'Texas Team' THEN "Average Score"
            ELSE 0
       END) AS "Texas Team"
FROM
   sportstable
GROUP BY "Month")
SELECT
    "Month"
    ,("New York Team" + "San Fran Team" ) AS Tier1
    ,("Chi Team" + "Texas Team") AS Tier2
FROM
    average_month

Month | tier1 | tier2
:---- | ----: | ----:
May   |     5 |     5
June  |     5 |     5

db<>fiddle here


Function to compute difference between 2 times I want to create a simple function in Postgres to find the difference between 2 TIME - not TIMESTAMP. As shown below, it accepts 4 parameters: hour, minute, second and expire (hour). In this example I have commented out seconds, just working on minutes.
CREATE OR REPLACE FUNCTION time_diff(hr INT, min INT, sec INT, exp_hr INT)
RETURNS INT
LANGUAGE plpgsql AS
$$
DECLARE
    cur_time    TIME;
    expire_time TIME;

    diff_interval INTERVAL;
    diff INT = 0;
BEGIN
    cur_time    = CONCAT(hr,  ':',  min, ':', sec) AS TIME; -- cast hour, minutes and seconds to TIME
    expire_time = CONCAT(exp_hr, ':00:00') AS TIME;         -- cast expire hour to TIME

    -- MINUS operator for TIME returns interval 'HH:MI:SS;
    diff_interval = expire_time - cur_time;

    diff = DATE_PART('hour', diff_interval);

    diff = diff * 60 + DATE_PART('minute', diff_interval);

    --diff = diff * 60 + DATE_PART('second', diff_interval);

    RETURN diff;
END;
$$;

Example:
01:15:00 - 02:00:00 should give me 45 minutes, so I do the following and I get the correct answer.
select * from time_diff(1, 15, 0, 2);

However, if I do this: 23:15:00 - 01:00:00 - the should give me 105 minutes (60 + 45).
select * from time_diff(23, 15, 0, 1);

But the result I am getting is -1335. I am trying to work out where I have gone wrong here.
Also I am invoking DATE_PART functions, this seems to be a quite an expensive process in terms of CPU usage. Is there a better way of optimising this function. With the first example I am getting results in 0.007s on 2018 i7 Mac mini. Although I do think this function is quick, but could it be better?

Assuming exp_hr is always ahead in time, but never more than 24 hours.
I suggest to return an interval for simplicity. Then it works for hours, minutes, seconds, microseconds etc. alike.
Besides correct, this should be faster by orders of magnitude:
CREATE OR REPLACE FUNCTION f_time_diff(hr INT, min INT, sec INT, exp_hr INT)
  RETURNS interval
  LANGUAGE sql PARALLEL SAFE IMMUTABLE AS
$func$
SELECT CASE WHEN hr >= exp_hr       -- wrap around midnight
            THEN 24 + exp_hr - hr
            ELSE exp_hr - hr
            END * interval '1 hour'
                - interval '1 min' * min
                - interval '1 sec' * sec;
$func$;

Note that this returns 24h for input = output exactly. You might decide otherwise for the corner case.
Demo:
test=*> SELECT f_time_diff( 1, 15, 0, 2) AS example1
test-*>      , f_time_diff(23, 15, 0, 1) AS example2
test-*>      , f_time_diff( 1, 15, 0, 1) AS example3
test-*>      , f_time_diff( 1,  0, 0, 1) AS example4;
 example1 | example2 | example3 | example4
----------+----------+----------+----------
 00:45:00 | 01:45:00 | 23:45:00 | 24:00:00
(1 row)

If you actually need the number of minutes as integer, (truncating seconds if any), extract the epoch and use integer division:
SELECT EXTRACT(epoch FROM f_time_diff(23,15,0,1))::int / 60 AS example2;  -- 105

(Or build that into the function.)
db<>fiddle here
The most expensive part is the type conversion now. If you input time / interval, it gets much faster, yet. Or better yet: timestamp or timestamptz, then it gets trivial and you wouldn't need a function to begin with ...
Concatenating strings before casting to time (like you had it) is much more expensive, yet.


How to run a query in PL/SQL with Serializable transaction isolation I want to run this SQL in Oracle 11g:

declare a number;
  begin
  select count(*) into a  from item w where w.Item_num='MOH601' ;
  dbms_output.put_line(a);
  end ;

How to run this query with isolation Serializable? As I have learnt before, Oracle will lock the rows with w.Item_num='MOH601' when using Serializable.

Alter the session:

declare a number;
  begin
    EXECUTE IMMEDIATE 'ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE';
    select count(*) into a  from item w where w.Item_num='MOH601' ;
    dbms_output.put_line(a);
  end ;


MySQL Replace -- Query or in Shell? I have numerous tables with some funky character replacement going on. I found some information on running an update to replace these, but it's in numerous tables and numerous fields.

update <table_name>
set <field> = replace(<field>,'â€™','\'');
update <table_name>
set <field>= replace(<field>,'â€¦','...');
update <table_name>
set <field>= replace(<field>,'â€“','-');
update <table_name>
set <field>= replace(<field>,'â€œ','"');
update <table_name>
set <field>= replace(<field>,'â€','"');
update <table_name>
set <field>= replace(<field>,'â€˜','\'');
update <table_name>
set <field>= replace(<field>,'â€¢','-');
update <table_name>
set <field>= replace(<field>,'â€¡','c');

Do you all have any advice on running this across every field for all the tables? I thought about doing a mysql dump and using sed to replace everything, but I'm not sure how to structure the commands since they're all special characters.

Any help would be greatly appreciated.

You could try to query the table information_schema.columns and find every table in your MySQL DB Instance that have character fields. Here is the table's layout:

mysql> show create table information_schema.columns\G
*************************** 1. row ***************************
       Table: COLUMNS
Create Table: CREATE TEMPORARY TABLE `COLUMNS` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `COLUMN_NAME` varchar(64) NOT NULL DEFAULT '',
  `ORDINAL_POSITION` bigint(21) unsigned NOT NULL DEFAULT '0',
  `COLUMN_DEFAULT` longtext,
  `IS_NULLABLE` varchar(3) NOT NULL DEFAULT '',
  `DATA_TYPE` varchar(64) NOT NULL DEFAULT '',
  `CHARACTER_MAXIMUM_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `CHARACTER_OCTET_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `NUMERIC_PRECISION` bigint(21) unsigned DEFAULT NULL,
  `NUMERIC_SCALE` bigint(21) unsigned DEFAULT NULL,
  `CHARACTER_SET_NAME` varchar(32) DEFAULT NULL,
  `COLLATION_NAME` varchar(32) DEFAULT NULL,
  `COLUMN_TYPE` longtext NOT NULL,
  `COLUMN_KEY` varchar(3) NOT NULL DEFAULT '',
  `EXTRA` varchar(27) NOT NULL DEFAULT '',
  `PRIVILEGES` varchar(80) NOT NULL DEFAULT '',
  `COLUMN_COMMENT` varchar(1024) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

Here is a query to get every character-based column type for all user-defined tables:

mysql> select DISTINCT COLUMN_TYPE from information_schema.columns
    -> WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
    -> AND (COLUMN_TYPE REGEXP 'text$'
    -> OR COLUMN_TYPE REGEXP '^varchar'
    -> OR COLUMN_TYPE REGEXP '^char');
+--------------+
| COLUMN_TYPE  |
+--------------+
| mediumtext   |
| varchar(512) |
| varchar(20)  |
| longtext     |
| text         |
| varchar(200) |
| varchar(255) |
| varchar(100) |
| tinytext     |
| varchar(25)  |
| varchar(64)  |
| varchar(32)  |
| varchar(60)  |
| varchar(50)  |
| varchar(250) |
| varchar(150) |
| varchar(10)  |
| varchar(45)  |
| char(5)      |
+--------------+
19 rows in set (0.09 sec)

You could join that query to a second query. The second query would have the name of every table with those column types:

SELECT BB.table_schema,BB.table_name,BB.COLUMN_NAME FROM
(select DISTINCT COLUMN_TYPE from information_schema.columns
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND (COLUMN_TYPE REGEXP 'text$'
OR COLUMN_TYPE REGEXP '^varchar'
OR COLUMN_TYPE REGEXP '^char')) AA
INNER JOIN
(SELECT table_schema,table_name,COLUMN_NAME,COLUMN_TYPE
FROM information_schema.columns
WHERE table_schema NOT IN
('mysql','information_schema','performance_schema')) BB
USING (COLUMN_TYPE);

Now, message the output to make it write a generic script for you:

SELECT CONCAT('UPDATE ',db,'.',tb,' SET ',fld,
'=REPLACE(',fld,',''oldchar'',''newchar'');')
UpdateCommand
FROM (SELECT db,tb,fld FROM
(
    SELECT table_schema db,table_name tb,COLUMN_NAME fld FROM
    (select DISTINCT COLUMN_TYPE from information_schema.columns
    WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
    AND (COLUMN_TYPE REGEXP 'text$'
    OR COLUMN_TYPE REGEXP '^varchar'
    OR COLUMN_TYPE REGEXP '^char')) AAA
    INNER JOIN
    (SELECT table_schema,table_name,COLUMN_NAME,COLUMN_TYPE
    FROM information_schema.columns
    WHERE table_schema NOT IN
    ('mysql','information_schema','performance_schema')) BBB
    USING (COLUMN_TYPE)
) AA) A;

Output should look something like this:

+-----------------------------------------------------------------------------------------------+
| UpdateCommand                                                                                |
+-----------------------------------------------------------------------------------------------+
| UPDATE example.user SET user_name=REPLACE(user_name,'oldchar','newchar');                     |
| UPDATE garbage.rolando SET post_content=REPLACE(post_content,'oldchar','newchar');            |
| UPDATE garbage.rolando SET post_title=REPLACE(post_title,'oldchar','newchar');                |
| UPDATE garbage.rolando SET post_excerpt=REPLACE(post_excerpt,'oldchar','newchar');            |
| UPDATE garbage.rolando SET post_status=REPLACE(post_status,'oldchar','newchar');              |
| UPDATE garbage.rolando SET comment_status=REPLACE(comment_status,'oldchar','newchar');        |
| UPDATE garbage.rolando SET ping_status=REPLACE(ping_status,'oldchar','newchar');              |
| UPDATE garbage.rolando SET post_password=REPLACE(post_password,'oldchar','newchar');          |
| UPDATE garbage.rolando SET post_name=REPLACE(post_name,'oldchar','newchar');                  |

Last step for generic script generation is to output the query to a text file like this:

mysql -u... -p... -A --skip-column-names -e"SELECT CONCAT('UPDATE ',db,'.',tb,' SET ',fld, '=REPLACE(',fld,',''oldchar'',''newchar'');') UpdateCommand FROM (SELECT db,tb,fld FROM (     SELECT table_schema db,table_name tb,COLUMN_NAME fld FROM     (select DISTINCT COLUMN_TYPE from information_schema.columns     WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')     AND (COLUMN_TYPE REGEXP 'text$'     OR COLUMN_TYPE REGEXP '^varchar'     OR COLUMN_TYPE REGEXP '^char')) AAA     INNER JOIN     (SELECT table_schema,table_name,COLUMN_NAME,COLUMN_TYPE     FROM information_schema.columns     WHERE table_schema NOT IN     ('mysql','information_schema','performance_schema')) BBB USING (COLUMN_TYPE) ) AA) A;" > CharUpdateScript.sql

You can customize the generic script from there.


WHERE statement returning out of bounds returns I have this query that I'm trying to work with, and ultimately I'm trying to get it to return results as soon as it matches one of my status WHERE requirements....but that's not even my question. My question is that my query is returning a value that is TOTALLY out of bounds of my WHERE requirements, and it's just perplexing me as to why.
I have a table named leads. It has about ~30,000 rows in the table, but the specific dataset I want to be targeting is here:
+---------+--------+---------+
| lead_id | status | owner   |
+---------+--------+---------+
|    3653 |     18 | 6585993 |
|    3984 |      3 | 6585993 |
|    4100 |     18 | 6585993 |
|    8916 |     21 | 6585993 |
|   20329 |      1 | 6585993 |
+---------+--------+---------+

Lead ID is the primary key, it's int(11) and AUTO_INCREMENTing, status is a FOREIGN KEY, and owner is a FOREIGN KEY as well.
Here is the query I'm working with:
SELECT lead_id, status, owner FROM leads
    WHERE owner = 6585993 AND (`status` = 1) OR (`status` = 21) OR (`status` = 3)
    LIMIT 1;

My ultimate goal with this query is I want to have it cascade through my last three WHERE requirements (ie, first check the owner requirement, then - if there exists no lead with a status of "1", then check for leads with a status of "21" and if no lead exists with a status of "1" or "21", then check for leads with a status of "3", and if no leads exist with any of those three statuses who also have the correct owner requirement, return none.
With how the query is written above, it returns the what I expect:
+---------+--------+---------+
| lead_id | status | owner   |
+---------+--------+---------+
|   20329 |      1 | 6585993 |
+---------+--------+---------+

Here's the problem...when I change my status cascade to look like this (change it so that the status of "21" is to be selected first):
SELECT lead_id, status, owner FROM leads WHERE owner = 6585993 AND (`status` = 21) OR (`status` = 1) OR (`status` = 3) LIMIT 1;

It returns this:
+---------+--------+---------+
| lead_id | status | owner   |
+---------+--------+---------+
|    9435 |      1 | 1083618 |
+---------+--------+---------+

And herein lies my ultimate question...
Why does the above query return a response with a completely wrong owner?

First and foremost:
Change your where clause and put all OR's inside a single parenthesis.
SELECT
    lead_id, status, owner
FROM
    leads
WHERE
    owner = 6585993
    AND ((`status` = 1) OR (`status` = 21) OR (`status` = 3))
ORDER BY
    CASE `status`
        WHEN 1 THEN 0
        WHEN 21 THEN 1
        WHEN 3 THEN 2
    END
LIMIT 1
;

Then, if you prefer and order, set and ORDER BY clause using a case to assign the new values, 1 = First (0), 21 = Second (1) and 3 = Last (2).

lead_id | status |   owner
------: | -----: | ------:
  20329 |      1 | 6585993

db<>fiddle here


"Temporary file write failure" when trying to change primary key in MySQL In MySQL, When I trying to change primary key by this query:

 ALTER TABLE `resources` DROP PRIMARY KEY, ADD
 PRIMARY KEY (`role_id`, `category_id`, `lang`, `model`, `rule`);

I execute this query with MySQL root user, but this error occurred:

  #1878 - Temporary file write failure.

My table design is:

CREATE TABLE `resources` (
    `role_id` INT(11) UNSIGNED NOT NULL ,
    `category_id` SMALLINT(5) UNSIGNED NOT NULL DEFAULT 1 ,
    `lang` BINARY(2) NOT NULL ,
    `model` SMALLINT(5) UNSIGNED NOT NULL ,
    `rule` CHAR(4) NOT NULL ,
    PRIMARY KEY (`category_id`, `lang`, `model`, `rule`, `role_id`)
 )
 ENGINE = InnoDB;

This table have about 2000 rows.

I use MySQL 5.6

Why this error has occurred?

what should I do?

I had the same problem, I did the following:

create table resources_with_key like resources;
alter table resources_with_key add primary key(`category_id`, `lang`, `model`, `rule`, `role_id`);
insert into resources_with_key select * from resources;


Sorting Report entries on alphanumeric combination I am trying to move over a database that was previously stored in an excel workbook.  The previous setup had a different worksheet for each bookshelf and the individual shelves were listed at "A-levels" such as A1, A2, A32, etc...  However, on Some shelves, there are subsets such as A9A1, A9A2, A9A5, etc...

The report format that I have created has each different bookshelf on a new page when printed, but based on the sorting, when there are multiple entries for the same shelf (A11A1, A11A4, etc...) it falls apart and they don't stay in order.

Is there any sort expression that you could use to first sort by number, then IF there is another letter, sort by letter, and then sort that subset by their respective numbers?

Your tag says MS Access, if you are using Jet/ACE in MS Access, you can sort by a user defined function.

Very roughly

SQL

SELECT t.ID, t.SortHere, SortOrder([SortHere]) AS ToSort
FROM Table t
ORDER BY SortOrder([SortHere]);

Code

Function SortOrder(ToSort)

Part1 = Left(ToSort, 1)

For i = 2 To Len(ToSort)
    If Not IsNumeric(Mid(ToSort, i, 1)) Then
        Part2 = Part2 & Mid(ToSort, 2, i - 2)
        Part3 = Mid(ToSort, i, 1)
        Part4 = Mid(ToSort, i + 1)
        Exit For
    End If
Next

If Part2 = vbNullString Then
    Part2 = Mid(ToSort, 2)
End If

SortOrder = Part1 & Format(Part2, "0000") _
          & Part3 & Format(Part4, "0000")

End Function

I am sure you can come up with a better function with a little thought.


Query for sub-array in multi-dimentional array How do I fix the following sql query:

DROP TABLE IF EXISTS array_test;

CREATE TABLE array_test (
  id serial primary key,
  name text,
  array_column integer[][]
);

insert into array_test (name, array_column) VALUES
  ('rowa', '{{1,2,3},{4,5,6}}'),
  ('rowb', '{{4,5,6},{7,8,9},{10,11,12}}');

This following query triggers a syntax error. How do I query for values with this sub array?

select * from array_test where '{1,2,3}' = ANY (array_column);

Here is one way in Postgres 9.3+ with generate_subscripts():

SELECT a.*
FROM   array_test a
JOIN   LATERAL generate_subscripts(a.array_column, 1) i
               ON a.array_column[i:i] = '{{1,2,3}}'::int[];

This way I am comparing whole slices of a 2-dimensional array. Note the double curly braces!: '{{1,2,3}}'. LATERAL and a join condition is probably the shortest syntax. If your slices are not unique within each array, rather use an EXISTS semi-join to get unique rows in the result (also works with older versions):

SELECT a.*
FROM   array_test a
WHERE  EXISTS (
   SELECT 1
   FROM   generate_subscripts(a.array_column, 1) i
   WHERE  a.array_column[i:i] = '{{1,2,3}}'::int[]
   );

If you are unfamiliar with the Postgres array implementation, this may seem contrived. Read on ...

Defining a column as integer[][] (signifying a 2-dimenstional array) is effectively the same as just integer[], because Postgres does currently not differentiate array data types with different dimensions. Each array type can have any number of dimensions as long as they match up internally.

Per documentation:

  The current implementation does not enforce the declared number of
  dimensions either. Arrays of a particular element type are all
  considered to be of the same type, regardless of size or number of
  dimensions. So, declaring the array size or number of dimensions in
  CREATE TABLE is simply documentation; it does not affect run-time
  behavior.

The simple expression:

WHERE 1 = ANY (array_column)

... tests for equality with any base element, ignoring dimensions completely. Per documentation

  The left-hand expression is evaluated and compared to each element of
  the array using the given operator.

Bold emphasis mine. It's just not possible to compare array slices this way.
Similar with array operators:

WHERE '{1,2,3}'::int[] <@ array_column

... would match for all arrays that include all of the base elements, again regardless of dimensions.

Per documentation:

  Array comparisons compare the array contents element-by-element, using
  the default B-tree comparison function for the element data type. In
  multidimensional arrays the elements are visited in row-major order
  (last subscript varies most rapidly). If the contents of two arrays
  are equal but the dimensionality is different, the first difference in
  the dimensionality information determines the sort order.

Bold emphasis mine.

Related older answer on SO with some other techniques:

PostgreSQL two dimensional array intersection


Finding contiguous ranges in grouped data I have a table that records the data of a car race with the following structure:

ID int, (id of car)
Duration timestamp, (time taken to drive)
Tour int,(no of laps made)
Pilot int,(drivers)

where Duration is in time and tour=no of laps which consists of values from 0 to 3000 and pilot has two values 2,3 (2 drivers with id 2 and 3)
The table shows the no of laps done within given time for each pilot.
The pilots drive the car within the time.Assume pilot 2 has drove the car first next pilot 3 and so on..

Now,I want total tour(difference) and total duree(difference) done by pilot having an id value 2 ?

SAMPLE DATA:

ID|DURATION|TOUR| PILOT
1 |15:00:01|216 |  2 \
2 |15:00:02|216 |  2 /   1 sec.
3 |15:00:03|217 |  7
4 |15:00:04|218 |  7
5 |15:00:05|219 |  7
6 |15:00:06|220 |  2 \
7 |15:00:07|221 |  2 /   1 sec.
8 |15:00:08|222 |  7
9 |15:00:09|222 |  7
10|15:00:10|223 |  2 \
11|15:00:11|224 |  2  |
12|15:00:12|225 |  2 /   2 sec.

Now I need the desired result:

For pilot id = 2, I need the tour difference along with duree(total)

Ans: 219 i.e 216 + (221-220) + (225-223)

First tour ID: 216 + (Dif of laps of each group) = 216 + 1 + 2 = 219

And duree(total) = Sum(Dif time of each group) = 00:00:04

I've set up the next sample on rextester:

DROP TABLE IF EXISTS race;
CREATE TABLE IF NOT EXISTS race (ID int, DURATION time, TOUR int, PILOT int);
INSERT INTO race VALUES (1 , '15:00:01', 216, 2);
INSERT INTO race VALUES (2 , '15:00:02', 216, 2);
INSERT INTO race VALUES (3 , '15:00:03', 217, 7);
INSERT INTO race VALUES (4 , '15:00:04', 218, 7);
INSERT INTO race VALUES (5 , '15:00:05', 219, 7);
INSERT INTO race VALUES (6 , '15:00:06', 220, 2);
INSERT INTO race VALUES (7 , '15:00:07', 221, 2);
INSERT INTO race VALUES (8 , '15:00:08', 222, 7);
INSERT INTO race VALUES (9 , '15:00:09', 222, 7);
INSERT INTO race VALUES (10, '15:00:10', 223, 2);
INSERT INTO race VALUES (11, '15:00:11', 224, 2);
INSERT INTO race VALUES (12, '15:00:12', 225, 2);

First it flags involved groups:

| ID | DURATION | TOUR | PILOT | GRP |
|----|----------|------|-------|-----|
| 1  | 15:00:01 | 216  | 2     | 1   |
| 2  | 15:00:02 | 216  | 2     | 1   |
| 6  | 15:00:06 | 220  | 2     | 2   |
| 7  | 15:00:07 | 221  | 2     | 2   |
| 10 | 15:00:10 | 223  | 2     | 3   |
| 11 | 15:00:11 | 224  | 2     | 3   |
| 12 | 15:00:12 | 225  | 2     | 3   |

Then calculates time difference of each group:

| DUREE | DIF_TOUR | MIN_TOUR |
|-------|----------|----------|
| 1     | 0        | 216      |
| 1     | 1        | 220      |
| 2     | 2        | 223      |

And finally returns aggregated values:

SELECT SUM(DUREE) DUREE, MIN(MIN_TOUR) + SUM(DIF_TOUR) TOURS
FROM
     (SELECT MAX(DURATION) - MIN(DURATION) AS DUREE,
             MAX(TOUR) - MIN(TOUR) AS DIF_TOUR,
             MIN(TOUR) AS MIN_TOUR
      FROM (SELECT ID, DURATION, TOUR, PILOT,
                   CASE WHEN TOUR <> @LTOUR AND TOUR <> @LTOUR + 1
                        THEN @GRP := @GRP + 1 ELSE @GRP END AS GRP,
                   @LTOUR := TOUR
            FROM   (SELECT @GRP := 0, @LTOUR := 0) X,
                   (SELECT ID, DURATION, TOUR, PILOT
                    FROM race
                    WHERE PILOT = 2
                    ORDER BY PILOT, TOUR) Y) Z
      GROUP BY GRP) W;

| DUREE | TOURS |
|-------|-------|
| 4     | 219   |

Rextester here


Selecting the popular posts using three tables I have three tables:

posts - contains the posts by users,
post_reviews - contains the likes on the posts, and
post_share - contains the post id and user id of that which is shared.

I would like to select five popular posts using these three tables. The popular posts are those that have the highest no of reviews and shares. I am finding it really hard to construct a SQL query for this.

A slight help or guidance in this matter will be highly appreciated.

UPDATE:

The table structure is as follows:

table posts{

id - PK
uid - FK
post - text
}

table post_reviews{

id - PK
pid - FK (Post Id)
uid - FK
like_unlike - ENUM
}

table post_share{

id - PK
pid - FK (Post Id)
uid - FK
}

5 Most Popular Posts By Reviews

SELECT A.uid,B.post_count reviews,A.post
FROM posts A LEFT JOIN
(
    SELECT * FROM
    (
        SELECT pid,COUNT(1) post_count
        FROM post_reviews
        GROUP BY pid
        ORDER BY post_count DESC
    ) AA LIMIT 5
) B
ON A.id = B.pid;

5 Most Popular Posts By Shares

SELECT A.uid,B.post_count shares,A.post
FROM posts A LEFT JOIN
(
    SELECT * FROM
    (
        SELECT pid,COUNT(1) post_count
        FROM post_shares
        GROUP BY pid
        ORDER BY post_count DESC
    ) AA LIMIT 5
) B
ON A.id = B.pid;

I hope this gives you a start...


Is shrinking a transaction log OK to do on a live database? In Microsoft SQL Server 2005, is it OK to shrink the log file of an online database, or will it cause an interruption of service?

DBCC SHRINKFILE itself does not make the database go offline. However, simply executing this command will not necessarily reduce the size on disk of the log, which depends on when the last full (for SIMPLE recovery types) or t-log (for FULL recovery type) backup was taken. If there's not been a recent backup of the logs then there'll be no log space to reclaim.

Finally, unless there has been some unforeseen, explosive growth due to an abnormally large write operation/batch, you may want to reconsider shrinking the file because the database is showing a need for a log file that large and the server needs to dedicate resources to expanding it back out. Perhaps adding storage would be a reasonable alternative.


Unable to add new instance on MySQL InnoDB cluster I'm setting up new MySQL InnoDB cluster 8.0.16 consisting of three instances:

srv-mysql-01
srv-mysql-02
srv-mysql-03

I run JS-script in mysql shell for build cluster:

mysqlsh --file=/tmp/MakeCluster.js

Script:

var dbPass = "Somepassword"
var clusterName = "cluster"

try {
  print('Setting up InnoDB cluster...\n');
  shell.connect('admin@srv-mysql-01:3306', dbPass)
  var cluster = dba.createCluster(clusterName);
  print('Adding instances to the cluster.');
  cluster.addInstance({user: "admin", host: "srv-mysql-02", password: dbPass})
  print('.');
  cluster.addInstance({user: "admin", host: "srv-mysql-03", password: dbPass})
  print('.\nInstances successfully added to the cluster.');
  print('\nInnoDB cluster deployed successfully.\n');
} catch(e) {
  print('\nThe InnoDB cluster could not be created.\n\nError: ' + e.message + '\n');
}

Cluster has been created successfully but while adding 2-th instance to cluster it give below error:

A new InnoDB cluster will be created on instance 'admin@srv-mysql-01:3306'.

Validating instance at srv-mysql-01:3306...

This instance reports its own address as 192.168.100.201

Instance configuration is suitable.
Creating InnoDB cluster 'cluster' on 'admin@srv-mysql-01:3306'...

Adding Seed Instance...
Cluster successfully created. Use Cluster.addInstance() to add MySQL instances.
At least 3 instances are needed for the cluster to be able to withstand up to
one server failure.

Adding instances to the cluster.A new instance will be added to the InnoDB cluster. Depending on the amount of
data on the cluster this might take from a few seconds to several hours.

Adding instance to the cluster ...

Validating instance at srv-mysql-02:3306...

This instance reports its own address as 192.168.100.202

Instance configuration is suitable.

The InnoDB cluster could not be created.

Error: Cluster.addInstance: WARNING: Not running locally on the server and can not access its error log.
ERROR:
Group Replication join failed.
ERROR: Error joining instance to cluster: 'srv-mysql-02:3306' - Query failed. MySQL Error (3092): ClassicSession.query: The server is not configured properly to be an active member of the group. Please see more details on error log.. Query: START group_replication: MySQL Error (3092): ClassicSession.query: The server is not configured properly to be an active member of the group. Please see more details on error log.

I'd like to fix this error.

I checked all instances on possible problems using shell-command dba.checkInstanceConfiguration() and didn't look errors.

But there are some errors in MySQL log on 2-th instance:

2019-07-20T23:24:43.085920Z 0 [ERROR] [MY-011526] [Repl] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: 77aabf2e-aa76-11e9-9a4d-525400f0e95b:1-2 > Group transactions: 114adc66-ab3f-11e9-a109-525400f0e95b:1-7,

....

2019-07-20T23:24:43.086303Z 0 [ERROR] [MY-011522] [Repl] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'

my.conf

[mysqld]

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

bind-address=192.168.100.202
port=3306

# Replication part
server_id=202
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay-log=srv-mysql-02-relay-bin
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

default_authentication_plugin=mysql_native_password

# Group replication part
transaction_write_set_extraction=XXHASH64
loose-group_replication_start_on_boot=OFF
loose-group_replication_local_address="192.168.100.202:33061"
loose-group_replication_bootstrap_group=OFF
report_port=3306
report_host=192.168.100.202

[mysql]
default-character-set=utf8

Any help would be appreciated.

I found solution the issue:

1.It's neccessary to configure and validate every instance with dba.checkInstanceConfiguration() and dba.configureInstance().
2.Specify port number of instance which we'll add to the cluster.
Example in my script:
cluster.addInstance({user: "admin", host: "srv-mysql-02", port:3306, password: dbPass})


Oracle XMLAGG issue with combining cells that have comma separated values in cells Have a situation where I'm trying to use xmlagg to get:

     | ID | name
row1 | 1  | apple
row2 | 1  | apple,pear,banana
row3 | 1  | apple,banana
row4 | 1  | pear,banana,orange
row5 | 2  | orange
row6 | 2  | orange,pear
row7 | 2  | pear, banana

to:

1 | apple,pear,banana,orange
2 | orange,pear,banana

Is this possible using xmlagg?
Thanks

Test data:

select * from t_ ;

C1   C2   C3
row1 1    apple
row2 1    apple,pear,banana
row3 1    apple,banana
row4 1    pear,banana,orange
row5 2    orange
row6 2    orange,pear
row7 2    pear,banana

Query:

select
c2 ||' | '|| rtrim (
   xmlagg( xmlelement( e, c3 || ',' ) ).extract('//text()')
, ','
) result
from
  (
    select distinct c2, regexp_substr(c3,'[^,]+', 1, level ) c3 from t_
    connect by regexp_substr(c3, '[^,]+', 1, level) is not null
  )
group by c2
;

RESULT
1 | apple,pear,orange,banana
2 | pear,orange,banana

LISTAGG() may also work -

select
c2 ||' | '|| rtrim (
   listagg(c2 , ',') within group ( order by c2 )
) result
from
  (
    select distinct c2, regexp_substr(c3,'[^,]+', 1, level ) c3 from t_
    connect by regexp_substr(c3, '[^,]+', 1, level) is not null
  )
group by c2
;

RESULT
1 | apple,banana,orange,pear
2 | banana,orange,pear

Dbfiddle here.


Line breaks handled inconsistently by SSMS Probably more of a SQL Management Studio question than SQL, per se, but when I run the following simple query:

SELECT results = 'Foo' + CHAR(13) + CHAR(10) + 'Bar'

I appear to get different results depending on whether I choose "Results to Grid" or "Results to Text".

With SSMS set to "Results to Text", I get (as expected):

results
--------
Foo
Bar

(1 row affected)

But with SSMS set to "Results to Grid", then copy and paste the results, I get

Foo  Bar

There is an option to change this behavior, go to Tools > Options > Query Results > SQL Server > Results to Grid, and check the box for "Retain CR/LF on copy or save":

You may have to exit and re-load SSMS for this change to take effect.


How to Handle Database for Application which Starts from Scratch Annually I am working on an application which will be used by an organisation annually, however each year they will be starting from a clean sheet. Which of the following two approaches (or any other approach) is most suitable:

Run the entire application from one database with a field in all relevant tables for the year it applies for. E.g. User("Mary", "Smith", "mary.smith@example.com", 2012) where 2012 shows that Mary's account is valid in 2012.
Have separate databases for each year and one main database which references which database is related to which year.

Honestly, I'd go with #1 with a twist in all but a rare circumstance.  The rare circumstance that would make me go with separate databases (or, more likely, one database with separate schemas) is if there are fundamental data type changes between years, and the data structure changes so often and completely that it really is a new database altogether.  That's probably not the case, though, because you're talking about a single application.  And because it's a single app, there's probably some common reference data that will remain static (or slowly-changing) through the years, and probably some functionality that they'll want, like the ability to replicate old users (so they don't have to keep typing in the values each year).

So instead, I'd go with one database, one set of tables, and an indicator telling to which year that data belongs.  Your example of a User seems a little odd to me, though.  If you're talking about application users, I'd only put them in once and if you need to break it down by year, have a User-Access table that has your user ID and the year as the two columns.  Then, your application knows only to allow that user access to the relevant year(s), without having to repeat data.

Here's the twist I mentioned above:  in addition to marking yearly data with a year indicator, you might have a second schema for archived data.  Partition each of the relevant tables by year and, as the year ends and you're getting ready for the next year, roll them off into the Archive schema.  The data would remain for historical and reporting purposes, while leaving your application free to handle the current year as though nothing had happened.

Archiving would be optional in this scenario, however.  If you have a lot of data and people rarely go back to look at it, it might make sense to move those off to archive tables, but even if not, you could still stick the old data on slower disks and with table partitioning by year, rarely touch those partitions even if they are in the same table.


How to influence files (pre)allocation steps (max growing value) in MongoDB 3.4 with WiredTiger We have in environment deployed some really small DBs (5GB) as directory per DB.Under dirs is LVM with XFS, where we have implemented logic for "brake" when DB space should be full-filled (all is due to some strict disk quotas - not really interesting at all I think...).
Our solution runs on MongoDB v3.4.19 on CentOS 7.5. Everything looks ok.

Some of DBs has more than 60% used disk space by single collection and in our scenario we encountered that, while restoring 2.3GB dump (all into 5GB database dir/=disk, the preallocation after 2GBs asks OS for another space in (I think) power of 2 strategy (or something very similar).

This situation hits ours triggers, and eventually should lead to lock of DB (which is still better than fall of whole cluster because of no space left).
Basically same situation we have with 10GB DB. (And in some way we can say that similar situation can occur with literally any capacity...)

My point of question is: Am I able to influence steps (maximum size lower than ~2GB) of (pre)allocation growing of Mongo/WT for restoring (only) or at all?

(Performance impact is not highest prioritized in this case.)

I've found some promising possibilities through db.adminCommand and configStrings for storage engines, but I was not able to affect or list some of possible parameters nor change something to turn things to good...
Especially file_extend parameter of WT through

db.adminCommand( { setParameter: '1', wiredTigerEngineRuntimeConfig: { <parameter> } } )

looked promising but I didn't find way to pass it to cluster...
Some of tries ended with error 22 - Invalid argument, but I wasn't able to find more about...

The other found, seemed suitable for MMAP engine only - newCollectionsUsePowerOf2Sizes=false.

This makes restoring of dumps (eventually backups) into cluster much more bothersome...

Hope that issue is clearly explained and any hint/tip would be appreciated...
If some additional info needed, let me know.

Update:
I've done screenshots where (I believe) is clearly visible what I'm talking about above... There are $ ls output (upper left), $ df output (upper right) and $ mongorestore progress (bottom left). (Better once to see than thousand times to hear (read)...)

Beginning

First alloc (to 1.4GB - used 650MB)

Second alloc (to 2.4GB - used 1.1GB)

Third alloc (to 4.4GB - used 2.1GB)

At the restore end (still allocated 4.4GB - used 2.3GB)

Back to normal (extra space deallocated)

Thank you.

Zdenek

WiredTiger does not preallocate data files. Only journal files are pre-allocated (at 100MB per pre-allocation), see journaling process.

The disk exhaustion you're seeing is likely due to these journal files. Journal files are optimized for quick writes, and their contents will be persisted in a more permanent manner on a WiredTiger checkpoint which occurs every ~60 seconds. Typically, once persisted in data files, you should see less disk used, since now the journal files can be removed. Note that the journal files are only removed by WiredTiger following a checkpoint and at no other times.

I would suggest you to allow for some temporary extra space during this restore/data load process. Once the database is in a steady state, you would see a more accurate size.

The power-of-2 preallocation is specific to MMAPv1 storage engine, which is deprecated. This is not relevant to WiredTiger.


Best Setup for Database Cluster I need to set up a good database clustering solution for a system that I am putting into production. I was hoping to get some advice from the experts as I am a primarily a developer and not a database administrator.

Background
My team and I are replacing a legacy system with a .NET-based system. Currently, we are doing parallel testing and slowly migrating users over to the new system; however, it strikes me that our current database solution is not adequate to handle the load once thigs really start going. Right now I have our entire database on one Dell PowerEdge Server running SQL Server 2008 R2 on it. Its been fine for development and for basic testing but when we decide to pull the plug on the old system and go forth completely with the new system we will need things like failover, load balancing, redundancy, etc. I have never had to implement a cluster before because everywhere I worked this was already implemented, and was not my responsibility to maintain. Since we are implementing the PC-based system from scratch I am looking for a starting point.

Recommendations
What do you recommend for my current situation: We have about 50 internal users that will be doing everything from data entry, claims processing, and the like, and the possibility of about 1500-2000 concurrent connections from outside. Is it better to do a virtual or physical setup? Fibre Channel or SATA?

Failover is easy to do. That just requires shared storage, and a couple of servers.

SQL Server doesn't support scale out to multiple nodes.  However a properly designed database with only a couple of thousand users should be able to run on a dual chip server pretty easily.  My companies systems support about 35k transactions per second on a quad chip server (with 6 cores per chip).

Like any database server you'll want to use multiple LUNs for each part of the system.  1 for data, 1 for indexes, 1 for transaction logs, one for tempdb.

If you can't afford a SAN (you'll be looking at a six figure purchase for the SAN alone) then you should look at database mirroring instead.  This when used with a witness server can give you automatic failover in the event of a failure, and doesn't require shared storage.  The different volumes for storage should still be used.

Before you can design your high availability solution (clustering, mirroring, log shipping, etc) you need to figure out what you are trying to protect against.  How much data are you willing to loose?  How long can you afford to be down during failover?


How do I query a JSON array stored in a JSON object? I have a column with name 'date' of type json.

And need to return all records that contain the "ID" equal "10"

Below is the json structure of the column 'date'

{
  "Name": "ABC",
  "ID": [
    "10",
    "20",
    "30"
  ]
}

How will the query return what I need?


Everyone warns against shrinking a database, but will it work in my case? 1 GB db with tables removed now 100 MB Everyone is warning against shrinking a database, e.g.
SQLAuthority

But will it be a valid option in my case? The database was originally more than 1 GB,
but with a table removed from it (moved to another database), the database is now 100 MB when I do a backup of it, as opposed to the MDF being more than 1 GB still.

What if I shrink the DB, then rebuild all the indexes? Is all of this still taboo?

A 1 GB database is next to nothing as far as storage is concerned.  If you are really looking to save space at that level, I'd be apt to question the environment specifics.  Even on a small box, 1 GB is negligible.

I recommend you read up on the pit falls of shrinking your database files (more specifically your data file[s]).  Paul Randal wrote a great article on this very topic.  The moral here is to know what is happening and what you are doing to your data.  If that is acceptable, and the business demands the smaller database then proceed cautiously.  I will quote Paul Randal with his recommended practice in a situation like this:

  So what if you do need to run a shrink? For instance, if you’ve deleted a large proportion of a very large database and the database isn’t likely to grow, or you need to empty a file before removing it?

  The method I like to recommend is as follows:
   - Create a new filegroup
   - Move all affected tables and indexes into the new filegroup using the CREATE INDEX … WITH (DROP_EXISTING = ON) ON  syntax, to move the tables and remove fragmentation from them at the same time
   - Drop the old filegroup that you were going to shrink anyway (or shrink it way down if its the primary filegroup)

  Basically you need to provision some more space before you can shrink the old files, but it’s a much cleaner mechanism.

Will your database never need to grow to this size again?  This is the main question that should be on the forefront of your mind when you consider shrinking a database.  It's all about proper sizing, not just for today, but for the long-term foreseeable future.

And, most importantly, do not make this part of routine maintenance.  For reasons why, see Paul's blog post.


Understanding psql prompts What is the difference between postgres=# and postgres-#? I tried googling it and looking on the official site but could not get a hit for the symbols.

psql has three different prompts, source:

The prompts psql issues can be customized to your preference. The three variables PROMPT1, PROMPT2, and PROMPT3 contain strings and special escape sequences that describe the appearance of the prompt.

Prompt 1 is the normal prompt that is issued when psql requests a new command.

Prompt 2 is issued when more input is expected during command entry, for example because the command was not terminated with a semicolon or a quote was not closed.

Prompt 3 is issued when you are running an SQL COPY FROM STDIN command and you need to type in a row value on the terminal.

You can see the defaults with \set in PSQL, or looking here in the source code
#define DEFAULT_PROMPT1 "%/%R%# "
#define DEFAULT_PROMPT2 "%/%R%# "
#define DEFAULT_PROMPT3 ">> "

These are all format strings. They render different in different prompt levels, from the source above describing %R,

%R

In prompt 1 normally =, but @ if the session is in an inactive branch of a conditional block, or ^ if in single-line mode, or ! if the session is disconnected from the database (which can happen if \connect fails).
In prompt 2 %R is replaced by a character that depends on why psql expects more input: - if the command simply wasn't terminated yet, but * if there is an unfinished /* ... */ comment, a single quote if there is an unfinished quoted string, a double quote if there is an unfinished quoted identifier, a dollar sign if there is an unfinished dollar-quoted string, or ( if there is an unmatched left parenthesis.
In prompt 3 %R doesn't produce anything.

So there ya have it, %R changes based on the PROMPT level.


High MySQL throughput On a big server with many user and website from 11AM - 00AM our MySQL throughput realised in max (60 - 80M)
this timestamp our customer website load slowing .

We haven't any Disk IO , and server HDD : 6 * 600 G SAS 15K + 64 G RAM

MySQL config :

innodb_buffer_pool_size=8192M (2 G above mysqltuner recommendation)

Screenshot:

Any idea to solve this problem?

This comment add after first answer :

Hello Dear Damiano :
Thank you so much your answer . about your comment :
I will attach the complete screen from server monitor . (end of this comment)

about slow query :
I have done this , but every 24 hrs we have under 500 slow query . and most them not occur repeatedly .

an other thing , this server big and we have High traffic web site on it , so I can not ask customer don't use server resource .

mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 11573    |
| Qcache_free_memory      | 27291504 |
| Qcache_hits             | 83817933 |
| Qcache_inserts          | 36512628 |
| Qcache_lowmem_prunes    | 15633028 |
| Qcache_not_cached       | 4338965  |
| Qcache_queries_in_cache | 29930    |
| Qcache_total_blocks     | 106130   |
+-------------------------+----------+

Here You are all thing need :
http://www.4shared.com/zip/jX54_Dprba/data.html

1 screen shot + 1txt file from output server

From the graph you provided (that I'm reporting below, for the convenience of other readers), I saw you're running munin to monitor your server.

As such, it would help to have a look also to other graphs (like CPU, Memory, I/O and, even more important, network-traffic) that unfortunately where not included in your question.

Anyway, the munin mysql throughput plugin (that generate the graph below) describe itself as: "mysql_bytes - Plugin to monitor the number of bytes sent from and received by mysql"

So based on the graph, we saw that your MySQL is serving content to the underlying network at a speed ranging from 40MBps (Mega Bytes per second) to 70/80MBps.

Should this not be normal in your particular case, than, in addition to give a look to this answer, you can check/monitor what's going on on your server, searching for high-traffic query results.

As a first step, I suggest:

show processlist: to see which users are connected and which query are they running on which DB, running for how much time (should they be "long");
enable "slow query log": to see which queries are requiring lots of time (and, hence, might impact your performances while, at the same time, having high changes to be optimized);

Should this not be enough to get a figure on what is going on, you can:

enable "general log": please note that this will cause a write-log for every query run by your mysql instance. This might seriously impact your performance so... use it wisely.

As a first guess, I think you are running under two conditions:

a small number of queries, returning very large recordsets, executed relatively few times (...so to generate 40/80MB of traffic) (eg.: a single recordset made by 100.000 records, each of 300 bytes, counts for up to 30MB of traffic. So two of them, per second, can... answer your problem).
a sligher higher number of queries, returning relatively small recordsets, but executed very frequently (...so to generate 40/80MB of traffic as well) (eg: a single recordset made by 10 records, each of 1000 bytes, counts for up to  1MB of traffic when executed 100 times per second).

The slow-query-log will help you catching the first issue. The general-log is the only one helping in the second case.

Also:

if you are NOT experiencing any issues in terms of storage-I/O, than everything is happening in RAM and... mysql surely cached all the recordsets. In such a case show status will help you figuring out what's going on.

Should you provide further details, we might be more helpful.

P.S.: I'm not a mysql-performance-tuner at all so don't blame me for all the (more important) tuning issues I've surely missed :-)


MySQL slow query log to identify queries that scan tables MySQL seems not to have setting to show you queries that use SOME indexes but still also do table scans

We have some badly performing queries and were hoping to find them systematically but the log_queries_not_using_indexes setting seems only to look for queries that use NO INDEXES AT ALL, rather than using full table scans.

Anyone to confirm? Is there a new option coming?

I find log_queries_not_using_indexes to be useless.  More useful is the queries that take a long time; this will include any queries that do some or all table scans and are slow, but exclude trivial table scans that are fast.

If a table has 5 rows, it does not really matter whether it uses an index or does a table scan -- the speed will be virtually the same.

These start with the most important queries to work on.  I suggest you work on the first 3, then wait for the impact to happen and get new slowlog:

mysqldumpslow -s t   # to sort by most impact first
pt-query-digest

Also, long_query_time=1 is probably a good starting point.

(I keep my ear to the ground; I have not heard of the option you suggest.  You could file a "feature request" with bugs.mysql.com.)

More

The Optimizer sometimes decides that a table scan is likely to be faster than using an index.  This happens when it estimates that more than 20% of the rows will be touched.  (The "20" varies with the phases of the moon.)  This is because of bouncing between the BTree of a secondary index and the data BTree has some cost.


Problem with a complex query I've facing problem with my MySQL database. I know how to process when I want to do many conditions on one same column.

Example of a table :

No | Letter
------------
1 | a
5 | b
4 | b
4 | c
5 | b
6 | g
4 | a

Typically, if I want to show the 'No' that has Letter a AND Letter b AND Letter C, this query works well :

 SELECT No
 FROM table
 WHERE letter IN ('a', 'b', 'c')
 GROUP BY No
 HAVING COUNt(*) = 3

But what happens, if in the query, I want to subdivide each letter into many occurences and find the 'No' that could have one occurence of GROUP A(a,x,y,z) AND one occurence of GROUP B(b,w,l) AND one occurence of GROUP C(c,k,j) ?

 SELECT No
 FROM table
 WHERE letter IN ('a OR x OR y OR z' AND 'b OR w OR l' AND 'c OR k OR j')
 GROUP BY No
 HAVING COUNt(*) = 3

I don't know how to do that and I'm lost.

Do you mean exactly one occurrence?  Or at least one occurrence?

These will (I think) find No for "at least":

SELECT No  FROM tbl AS m
WHERE  EXISTS ( SELECT 1 FROM tbl WHERE
                 No = m.No AND  letter IN ('a', 'x', 'y', 'z') )
  AND  EXISTS ( SELECT 1 FROM tbl WHERE
                 No = m.No AND  letter IN ('b', 'w', 'l') )
  AND  EXISTS ( SELECT 1 FROM tbl WHERE
                 No = m.No AND  letter IN ('c', 'k', 'j') )

Another...

SELECT No
    FROM ( SELECT No, GROUP_CONCAT(letter SEPARATOR '') AS lets
               FROM table
               GROUP BY No
               HAVING lets RLIKE '[axyz]'
                  AND lets RLIKE '[bwl]'
                  AND lets RLIKE '[ckj]' ) AS n

(Nothing even close to your invented IN syntax is allowed.)


Switch user to 'postgres' user on macOS results in "su: Sorry" error The Postgres installers kindly provided to the community by EnterpriseDB.com creates a postgres user account on macOS as is common.

In a console session in Terminal.app, I need to switch to that user. My goal is to run a text editor with that user’s privileges so that I can edit the pg_hba.conf file controlling Postgres user-authentication rules.

When I try:

su - postgres

…I am prompted for the user's password, which I enter successfully, yet get the error message:

  su: Sorry

I am sure the password is correct. What can I do to access the postgres user?

sudo su postgres

Having found this closed Question on Stack Overflow and its duplicate, I will post here the solutions found there and there.

➥ You must use the sudo command combined with the su command.

sudo su postgres

When prompted, enter your macOS admin user password rather than the postgres user’s password. At this juncture, your Mac admin user is the one executing the sudo command, not the postgres user.

While I do not understand why we must combine the sudo with the su, it does indeed work for me in macOS Mojave. I did not find this tip among my various BSD and Linux related searching efforts, so perhaps this is a Mac-only Apple thing, perhaps an extra security measure. Or perhaps in the Unix-style OSes, the su command must be run by the root user, and in macOS the root account is disabled by default for security purposes – our Mac admin user account is less powerful than root, and may not be authorized by default to run su? Dunno, don't care, the workaround works.

Modern alternative: sudo -u

According to Craig Ringer and to Erwin Brandstetter, the more modern form is:

sudo -u postgres -i

According to this page:

-u means “run a specified command as this specified user”.
postgres is specifying the username under which we want to run the command.
-i simulates an initial login, meaning the shell will be initialized running that user’s startup scripts.
No command is provided, so an interactive shell is executed.

For more discussion on the older and newer approaches, see this Question on sister site, What's the difference between sudo su - postgres and sudo -u postgres?. The answer there by Craig Ringer advises: (a) Forget about sudo su, and (b) use sudo -u instead.

Run apps as postgres user

To run a psql session as the postgres user:

sudo -u postgres psql

To run the nano text-editor app as the postgres user:

sudo -u postgres nano


Entity or attribute if only one field beside key and attribute for other entity? Let's say I have an entity person which has an attribute Email. But the person can have more than one Email address. So I have to put this attribute into a separate table.

But what I do not understand in this situation: Is Email now an entity or an attribute? I would say entity, because it has its own table. But I would say attribute, because this is the only function of it.

Its has become its own Entity. You now have a 1:N relationship between Person and Email.


Why can a new user select from any table? When I create a user in Postgres,

create user test with password 'test';

The user ends up being able to connect to every database in the instance.  The user can also select from any table:

# psql -h localhost testdb test
Password for user test: *****
psql (8.4.20, server 9.1.13)
WARNING: psql version 8.4, server version 9.1.
         Some psql features might not work.
Type "help" for help.

testdb=> select * from testtable;
 id
------------
      1
(1 row)

The \l database listing shows that testdb has empty Access privileges.  So why can a newly created user connect to that database?

The newly created "test" user can also select data from tables.  Why can he do so without a grant of select privileges?

I'd like to create a user that can only connect to one database and only read from certain views.  How can I prevent a user from reading directly from tables?

Also addressing the question in the comments.

Role public

Per documentation:

  The key word PUBLIC indicates that the privileges are to be granted
  to all roles, including those that might be created later. PUBLIC
  can be thought of as an implicitly defined group that always includes
  all roles.

Bold emphasis mine. Membership in public cannot be revoked (or granted). You can only revoke privileges from public. In your case, to block out all roles without explicit privileges, like @Robert already provided:

REVOKE ALL ON DATABASE testdb FROM PUBLIC;

Role postgres

The database role postgres is a superuser by default. You can take that away from postgres - but that would be unwise: everybody (including some client programs) expects postgres to be a superuser. You can also create more superusers (careful with that!). Superusers don't need privileges. The manual once more:

  It should be noted that database superusers can access all objects
  regardless of object privilege settings. This is comparable to the
  rights of root in a Unix system. As with root, it's unwise to operate
  as a superuser except when absolutely necessary.

As to your question:

  Is it true that if \l or \dn+ displays empty access privileges, that
  is the same as postgres=UC/postgres, =UC/postgres

Not exactly.

Privileges are granted by the owner or a superuser (or a role that was granted the privilege to do so). postgres=UC/postgres would mean postgres granted it, which is not accurate for empty privileges, but the effect is the same in a default installation.
However, public gets no privileges for new schemas and only CONNECT and TEMP (can create temporary tables) privileges for databases by default, not the CREATE privilege (cannot create schemas). That would be something like =UT/?? according to the list of possible privileges - the manual once more:

        r -- SELECT ("read")
        w -- UPDATE ("write")
        a -- INSERT ("append")
        d -- DELETE
        D -- TRUNCATE
        x -- REFERENCES
        t -- TRIGGER
        X -- EXECUTE
        U -- USAGE
        C -- CREATE
        c -- CONNECT
        T -- TEMPORARY
  arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
        * -- grant option for preceding privilege

You can change default privileges. I wrote more about that in the related answer mentioned in the comments:

Grant privileges for a particular database in PostgreSQL

But those default privileges only apply objects created after defaults were changed. So what does an empty ACL entry mean? The manual on the system catalog pg_default_acl:

  Note that when an ACL entry in another catalog is null, it is taken to
  represent the hard-wired default privileges for its object, not
  whatever might be in pg_default_acl at the moment.

Bold emphasis mine.


How to measure a perfomance of SQL Server database? I have the task to improve the performance of a SQL Server 2012 database (one of 4 in an instance) by 60% confirmed by corresponding statistics.

So, I need to measure "performance" of the RDBMS database before performance tuning and optimization and after.

Which metrics are better suited for this?

Trying to answer the obvious questions ahead ...

I/O (hardware) bottlenecks are absent since the SQL Server runs on a virtual rack having plenty of physical resources under it.

The database is used by approx. 60 users (mostly 8 hours a day) with widely varying load (per sec).

This is a company management task, so the results of this task should be easy to grasp.

UPDATE:
the corresponding my question was answered in StackOverflow.com by @Martin von Wittich and, then, deleted by moderator/community:

  Run different kinds of queries (INSERT, UPDATE, DELETE) in a loop
  (e.g. 1000 times), then divide the runtime by the amount of loop
  iterations. Now you now how long a single query of that type takes.

  Then do whatever you want to do to improve the performance, and
  compare the results.

Quite good for me. Also I do not see why it was deleted in SO with the reason: "This question does not appear to be about programming" and closed in this site with: "There are either too many possible answers, or good answers would be too long for this format."

I/O (hardware) bottlenecks are absent since the SQL Server runs on a virtual rack having
  plenty of physical resources under it.

Let's get this straight:

Most virtualization is pathetic - not even low, pathetic - on the IO side.
"having plenty" means nothing unless you qantify there is no IO problem.

At the end, come up with the metcis you need to measure, and find the bottlenexks. But starting with "virtual rack has no IO issues" will give you smiles from most people using virtualization.


Feedback on an entity-relationship diagram for a movie theater business domain I have an assignment due next week, and for that assignment I am supposed to learn MySQL and create an entity-relationship diagram (ERD).

My current attempt

Now I've made this ERD

The issue

I went to my professor for feedback and he just told me that it is not a good ERD and refused to give me any further feedback.

I'm still a noob, but my question to you is: How do I improve this ERD? I was thinking about removing the Ticket entity type since that might be confusing...

The following are examples used to convey the point, there are multiple instances of each of these examples within your ERD.

Naming

Consistency is key. Some table names are pluralised (Seats) some are not (Ticket). Your table contains data about many tickets so I normally prefer pluralised table names.

You're prefixing every column with the name of the table. This makes for column names like staff_staff_id, which isn't instantly understandable to readers. If I named the primary key of Staff ID then you store staff.id and you might access tickets.staff_id, which makes everything more understandable.

Relations

There's a couple of relations that don't exist, for example you've got seat_id in Ticket (makes sense if you have preassigned seating) but no relation to Seats.seats_id? Why not? If your cinema/theatre doesn't have preassigned seating then you don't need this data.

There are some duplicates within tables. Why does Ticket contain both staff_id and staff_staff_id?

Data duplication

There are some unnecessary (unenforced) relations that are allowing for data inconsistencies. If a MovieTime must happen within a Hall an a ticket must be at a MovieTime then there's no need for Ticket to contain any information about the hall in which the movie is going to be shown.

It's unclear what the difference between the time_of_screening and screening_time is. Maybe one of them is the number of minutes the screening runs for? This information is already within Movie and so is another duplication.

Data

There's no need to persist the age as you've already got the date of birth. I've written about this on Stack Overflow a few times.

It's quite an assumption that all staff and movie names will be 45 characters or less.

I find the inclusion of sex in the staff table a little off-putting. What's the purpose of storing this information? Are the chromosomes of the person selling you a movie ticket that important in this cinema/theatre. I also don't understand why it's 10 characters long.

How to start

Think about exactly what information is required to go within each table. If you're not sure it's required then don't add it in. If you need to add something later then so be it, but you haven't included a lot of unnecessary data. I'd set-up some test tables with dummy data to see if what you're creating will actually work.

Every relation to another table should be enforced. If you've got some unenforced relations then either add a foreign key constraint or remove the column.

Your cinema probably has tickets, so removing the data will prevent you selling tickets.


MySQL Monitoring Script in Shell or Perl for Linux I need a MySQL Monitoring Script in Bash or Perl for Linux.

I found some but they are quite big with a lots of checks that are not
useful at all for me and in this case I should customise the script
a lot.

Any idea for most important parameters of MySQL monitoring in a Shell or Perl
script?

Following parameters should be monitored:

qcache_hits
SHOW GLOBAL STATUS;
SHOW SESSION STATUS;
slow_queries
max_connections
key_buffer_size
mysql availability  STATUS
Error log
SHOW ENGINE INNODB STATUS;
max_used_connections
Handler_read%

For mysql server monitoring you should concern these-
 Mysql server is running or not by command (service mysql status) and by telnet the server that is running or not. You should consider these parameters.

ibdata size
no of connections or Threads
reads per second
write per second
cpu load
memory utilization
no of open tables
query per second
insert/update per second
mysql-server uptime
slow queries
flush tables
no of questions


ORDER BY clause kills query performance Context:

PostgreSQL 10, with 3667438 records in users table, the users table has a JSONB called social, we usually use a strategy of indexing computed function outputs, so we can aggregate information into a single index.
The output of the engagement(social) function it a double precision numeric type.

Problem:

The problematic clause is ORDER BY engagement(social) DESC NULLS LAST, there is also a btree index idx_in_social_engagement with DESC NULLS LAST attached to this data.

Fast query:

EXPLAIN ANALYZE
SELECT  "users".* FROM "users"
WHERE (follower_count(social) < 500000)
AND (engagement(social) > 0.03)
AND (engagement(social) < 0.25)
AND (peemv(social) < 533)
ORDER BY "users"."created_at" ASC
LIMIT 12 OFFSET 0;

Limit  (cost=0.43..52.25 rows=12 width=1333) (actual time=0.113..1.625
rows=12 loops=1)
   ->  Index Scan using created_at_idx on users  (cost=0.43..7027711.55 rows=1627352 width=1333) (actual time=0.112..1.623 rows=12 loops=1)
         Filter: ((follower_count(social) < 500000) AND (engagement(social) > '0.03'::double precision) AND (engagement(social) <  '0.25'::double precision) AND (peemv(social) > '0'::double precision) AND (peemv(social) < '533'::double precision))
         Rows Removed by Filter: 8
 Planning time: 0.324 ms
 Execution time: 1.639 ms

Slow query:

EXPLAIN ANALYZE
SELECT  "users".* FROM "users"
WHERE (follower_count(social) < 500000)
AND (engagement(social) > 0.03)
AND (engagement(social) < 0.25)
AND (peemv(social) > 0.0)
AND (peemv(social) < 533)
ORDER BY engagement(social) DESC NULLS LAST, "users"."created_at" ASC
LIMIT 12 OFFSET 0;

Limit  (cost=2884438.00..2884438.03 rows=12 width=1341) (actual time=68011.728..68011.730 rows=12 loops=1)
->  Sort  (cost=2884438.00..2888506.38 rows=1627352 width=1341) (actual time=68011.727..68011.728 rows=12 loops=1)
        Sort Key: (engagement(social)) DESC NULLS LAST, created_at
        Sort Method: top-N heapsort  Memory: 45kB
        ->  Index Scan using idx_in_social_engagement on users  (cost=0.43..2847131.26 rows=1627352 width=1341) (actual time=0.082..67019.102 rows=1360633 loops=1)
            Index Cond: ((engagement(social) > '0.03'::double precision) AND (engagement(social) < '0.25'::double precision))
            Filter: ((follower_count(social) < 500000) AND (peemv(social) > '0'::double precision) AND (peemv(social) < '533'::double precision))
            Rows Removed by Filter: 85580
Planning time: 0.312 ms
Execution time: 68011.752 ms

The select goes with * because I need all the data stored in each row.

Update:

CREATE INDEX idx_in_social_engagement on influencers USING BTREE ( engagement(social) DESC NULLS LAST)

Exact index definition

Your ORDER BY clause is on:

engagement(social) DESC NULLS LAST, "users"."created_at" ASC

But I suspect your index is just on:

engagement(social) DESC NULLS LAST

So the index is not capable of fully supporting the ORDER BY.

You can reproduce the same issue without using either JSONB or expression indexes.  You might be able to salvage the situation by creating a composite expression index over both columns in your ORDER BY.

If the PostgreSQL planner were infinitely wise, it might be able to use the existing index efficiently.  It would have to march up engagement(social) DESC NULLS LAST until it collected 12 tuples which met all the rest of the filter requirements.  Then it would have continue marching up that index until it collected all the rest of the tuples which were tied on engagement(social) with the 12th tuple (and which met the other criteria).  Then it would have to re-sort all of the collected tuples on the full ORDER BY, and apply the LIMIT 12 to that expanded and re-sorted set.  But the PostgreSQL planner is not infinitely wise.


Matching separate unicode characters as one I am writing a C# program, and I'm querying a SQLite database. I have multiple diacritical characters (e.g ã, õ, ó, ê) which I need to match in my search. I have the command

"SELECT * FROM DesanoDict1 WHERE Desano LIKE '%" + string + "%' ORDER BY Desano ASC"

However, I have the problem where those diacritical characters are entered in as separate ones (i.e the ã is an a + ~). I could make a custom keyboard with the single unicode characters (as one instead of two separate characters), but the users that will be using the program will probably be using either the same or a similar keyboard to the one I'm currently using, so it will only create problems. I am very new to database and querying, so any help here would be greatly appreciated.

Unicode is a complex beast and database engines tend to be at best superficially aware of it. They certainly don't tend to know about sequence equivalences.

I would suggest making sure that you store data in a consistent normalised form, and normalise search parameters in the same way before searching. I'm not really a c# person, but https://msdn.microsoft.com/en-us/library/ebza6ck1(v=vs.110).aspx looks like a decent place to start for how to go about that.

Obviously you will have existing data that is not normalised, so if you go with this route you'll need to write something to scan the data and replace string values with normalised versions as needed.


Precautions for moving a SQL cluster from an old to a new datastore My storage team  wants to move a SQL cluster (2 node VM) from an old data store to Storage vMotion . What are the precautions I can take before this exercise is performed? I am planning to take database snapshots before the exercise and then shutdown the cluster and databases. The RDM's will be reconnected once the VM's have been moved to the new datastore. Are there any other best practices which I can follow before this process? Please advice.

I would:

Run DBCC CHECKDB on all databases prior to the move to ensure no consistency errors.
Take full SQL Server Backups of all user and system databases. Use native backup or your preferred third party tool.
When taking the backup, ensure it is verified.
Run DBCC CHECKDB on all databases post the move to ensure no consistency errors.


Using Resource Governor and allocate more memory to satisfy high Memory Grant request by queries? On a SQL Server 2016 database server, I have few queries which are really slow and which ask for more memory (queries are not optimized). This is making the whole database slow as lots of users are running the same query. This is causing other queries to wait. I get CXCONSUMER wait (due to parallel execution) and RESOURCE_SEMAPHORE.

So this is creating memory pressure (I believe that these slow queries are creating memory contention for other queries and in turn creating memory pressure) and i think this is causing my plan cache to clear in fixed intervals.

I read that Memory Grant % by default is 25%. I am thinking of increasing this 25% to give memory for queries. I am trying to solve the problem temporarily for now.

The queries which are not optimized are LINQ queries and changing that will require some time for the team.

So please suggest whether increasing Memory Grant % in resource governor is a good idea or not?

I have total of 128GB RAM and 75% (102 GB) of it is allocated to SQL Server.

This is the opposite of what you should do.

When you have queries waiting on RESOURCE_SEMAPHORE, they're waiting for other queries to finish running, and give up their memory. The queries that are waiting aren't even running.

Letting all queries use more memory will only make the problem worse. You'd wanna configure resource governor to make queries ask for smaller grants. This could also cause problems, though, because any spills to disk would get worse.


TDE in a SQL Cluster I'm researchig into databnase encryption and whilst TDE seems to do exactly what it says on the tin, there seems to be no information on how to configure it up in a Microsoft failover SQL Cluster, or if there are any caveats about encryption in a cluster environment (beyond those that are listed on MSDN).

Trying to distill the information I have (mostly from here and here but from other random googles), I think the answer is that I have to do nothing and it just works. The master key and certificate are in the master database and the DMK is in the user database, so in a failover situation, everything exists in the MDF files for the failover node to run with (rather than any keys lurking in the registry which would need to be sync'd twixt nodes).

The problem is there's nothing that explicitly states this (or I've completely failed to read the sentance that says it), so I'm just seeking clarification that I'm not missing something obvious, that nothing special needs to be configured and it does "just work".

Yup, I have 2 clusters with TDE in both SQL 2008 R1 and SQL 2012 and nothing special was required. The master key is encrypted by the Windows DPAPl (I think that's the right acryonym) service, but then the generated key is in the Master DB that gets moved between nodes as you stated.


SQL Server table appears locked for no reason My database was running just fine this morning, up until it wasn't.

This query:

SELECT TOP (1) *
FROM tblTask;

comes back in no time at all, while this query:

SELECT *
FROM tblTask this_ with (updlock, rowlock);

which is generated by NHibernate in my app, is timing out in my software.  In SSMS, I killed the query after letting it run for 9 minutes.  The table has a total of 6 records.

The problem started coming up after the virtual machine that hosts the software running this query was rebooted this morning.  Given the locking hint, I would have guessed that there was somehow a stray lock stuck on that table.  I restarted the database, and also rebooted the server itself, but I still get the same behavior.

This table is only hit by a Windows service - nothing else should be locking the table and preventing access to it.  The web site (which uses the rest of the database but not this table) appears to be running perfectly fine.

I'm not sure where to go from here.  Any help?

Based upon my investigation with sp_whoisactive (thanks @billinkc), it was a blocking issue with the Windows service accessing the table.  I found the issue in code and corrected it.

What is still unclear to me is why this was not an issue prior to the server reboot, why it suddenly started coming up.


performances of distinct/group by I'm trying to select the most recent authors of messages in a room, using this (simplified) table:

             Table "public.message"
 Column  |  Type   | Nullable |               Default
---------+---------+----------+-------------------------------------
 id      | bigint  | not null | nextval('message_id_seq'::regclass)
 room    | integer | not null |
 author  | integer | not null |
 created | integer | not null |
Indexes:
    "message_pkey" PRIMARY KEY, btree (id)
    "message_author_created_room" btree (author, created, room)
    "message_room_author_created" btree (room, author, created)
    "message_room_created" btree (room, created)
    "message_room_id" btree (room, id)

The problem is a query like this is slow:

select message.author as id, max(message.created) as mc from message
where room=12 group by message.author order by mc desc limit 50;

And here's the explain(analyze, verbose, buffers):

miaou=> explain (analyze, verbose, buffers) select message.author as id, max(message.created) as mc from message
where room=12 group by message.author order by mc desc limit 50;
                                                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10627.14..10627.26 rows=50 width=8) (actual time=54.887..54.901 rows=50 loops=1)
   Output: author, (max(created))
   Buffers: shared hit=490
   ->  Sort  (cost=10627.14..10629.19 rows=820 width=8) (actual time=54.885..54.891 rows=50 loops=1)
         Output: author, (max(created))
         Sort Key: (max(message.created)) DESC
         Sort Method: top-N heapsort  Memory: 29kB
         Buffers: shared hit=490
         ->  Finalize GroupAggregate  (cost=1000.46..10599.90 rows=820 width=8) (actual time=14.019..54.788 rows=160 loops=1)
               Output: author, max(created)
               Group Key: message.author
               Buffers: shared hit=490
               ->  Gather Merge  (cost=1000.46..10583.50 rows=1640 width=8) (actual time=14.007..54.636 rows=248 loops=1)
                     Output: author, (PARTIAL max(created))
                     Workers Planned: 2
                     Workers Launched: 2
                     Buffers: shared hit=490
                     ->  Partial GroupAggregate  (cost=0.43..9394.18 rows=820 width=8) (actual time=3.439..34.733 rows=83 loops=3)
                           Output: author, PARTIAL max(created)
                           Group Key: message.author
                           Buffers: shared hit=2989
                           Worker 0: actual time=0.297..49.593 rows=116 loops=1
                             Buffers: shared hit=1550
                           Worker 1: actual time=6.624..40.612 rows=60 loops=1
                             Buffers: shared hit=949
                           ->  Parallel Index Only Scan using message_room_author_created on public.message  (cost=0.43..8904.09 rows=96377 width=8) (actual time=0.030..20.067 rows=73907 loops=3)
                                 Output: author, created
                                 Index Cond: (message.room = 12)
                                 Heap Fetches: 139
                                 Buffers: shared hit=2989
                                 Worker 0: actual time=0.035..28.355 rows=109834 loops=1
                                   Buffers: shared hit=1550
                                 Worker 1: actual time=0.030..23.723 rows=79112 loops=1
                                   Buffers: shared hit=949
 Planning time: 0.211 ms
 Execution time: 57.071 ms

I'd like to know how to make that faster, the real important goal being to get the N most recent authors. Is there a faster way to query that information?

select count(author) from message where room=12;

Because author can not be null just do SELECT count(*). Doing so may result in this being an index-only scan on either message_room_created or message_room_id. If you need the unique authors, which you don't have you'll have to use count( DISTINCT author ).

Also this being planned as a parallel query. That's relatively new functionality. You may want to try SET max_parallel_workers_per_gather = 0 to disable parallel query functionality and reposting the results.

Either way

  this one is slow (same duration, about 45ms)

If you've got millions rows an index scan and heap fetches, it's not really fair to call 45ms "slow."


SQL Server mirroring for more than 10 databases I have SQL Server instance that has more than 40 databases and I want to make mirroring to another instance server.

But when I read about mirroring I find this restriction:

  On a 32-bit system, database mirroring can support a maximum of about 10 databases per server instance because of the numbers of worker threads that are consumed by each database mirroring session, for 64-Bit Operating system you can mirror more than 10 databases depending on the number of processors and worker threads.

Question

How can I make mirroring for all my databases? Or, what I need as a servers equipment to make mirroring for all my 40+ databases?

Database Mirroring is a legacy technology that has been superseded by AlwaysOn Availability Groups

  Maximum number of availability groups and availability databases per computer:

  The actual number of databases and availability groups you can put on
  a computer (VM or physical) depends on the hardware and workload, but
  there is no enforced limit. Microsoft has tested up to 10 AGs and 100
  DBs per physical machine, however this is not a binding limit.
  Depending on the hardware specification on the server and the
  workload, you can put a higher number of databases and availability
  groups on an instance of SQL Server.

Prerequisites, Restrictions, and Recommendations for Always On availability groups


MySQL Security permissions Is it possible in MySQL to block users from executing these statements?

DESC "table"
SHOW TABLES
SHOW CREATE TABLE "table"

Or I need to write a MySQL transport port wrapper ?

GRANT ON TABLE does not help to hide database structure from a user.
I want my developers to not be allowed of dumping database structure and show tables name

The configuration file: /etc/my.cnf

[mysqld]
skip-show-database
skip-networking
bind-address=127.0.0.1
#skip-show-tables
#skip-table-stucture-show

What I am about to say would be rather crazy but entirely possible. Here it goes...

CRAZY SUGGESTION #1

Download the source code for the mysql client
Remove the commands

DESC "table"
SHOW TABLES
SHOW CREATE TABLE "table"

Compile the source
Give your developers access to this special compiled version of the mysql client

CRAZY SUGGESTION #2

Download MySQL Proxy
Program the LUA scripts to ignore

DESC "table"
SHOW TABLES
SHOW CREATE TABLE "table"

Have your developers access DB using MySQL-Proxy-based IP Addresses

EPILOGUE

I would say Give it a Try, but I have never done anything like this before.

Notwithstanding, it would be a brave C/C++ or LUA soul indeed to engage in such an undertaking...


Ranking System with Different Categories I want to create a system where the user can be ranked by several different factors.

For example, the user competes in the following categories

Wins
Points

I need to know what the position of the ranking in each category last week and today.

What is the best way to do this:

Creating a table for each category (userWins and userPoints), and update as needed?
Create a table with columns where I can tell that this line is part of a category?

On each approach, how do I update and sort the rank?

Imagine this scenario even though I may have 20 different categories.

Track the facts that drive your ranking system in normalized tables according to what makes sense for those facts without regard to how it drives your ranking system.

Then have a separate table or tables that converts your basic facts into a points system that allows you to do simple sorts for ranking.

The reason to keep these things separate is that your basic facts are driven by some kind of usage of your system, whatever that may be.  You want the code for tracking this usage to be clean and maintainable.  Even if you didn't have a ranking system, these facts would exist.  The logic to maintain these facts is independent of the logic to calculate rankings.  The ranking rules are an extra layer on top of the facts.  Also, your rules for ranking could (easily) change over time, so you don't want to tangle up your basic facts with less stable rules.


Is there any ways to retrieve datatable in row field in mysql database I want to retrieve data table in row field has fetch from database. Simply, one to many relationship in result set from stored procedure.

Table 'car'

Id  Car
1   BMW
2   Mercedes
3   Audi

Available in many color

Table 'CarColor'

Id CarId    Color
1   1     Metalic White
2   1     Acrylic White
3   2     Glossy Red
4   2     Glossy White
5   3     Frosty Black
6   3     Frosty White

Consequently, I want result set from stored procedure like

Id  Car       CarColor
1   BMW      'CarColor Datatable' (i.e. Datatable of CarColor table relevant row)
2   Mercedes 'CarColor Datatable' (i.e.  Datatable of CarColor table relevant row)
3   Audi     'CarColor Datatable' (i.e.  Datatable of CarColor table relevant row)

The closest you can get is to use the GROUP_CONCAT function:

SELECT car.*, GROUP_CONCAT(color SEPARATOR ',')
FROM car
INNER JOIN carColor ON car.id=carColor.carID
GROUP BY carColor.carID;

[sqlfiddle]

Be aware of the group_concat_max_len option that can truncate your concat results.


sub and sub-sub item organization system So I am looking to find the best database design for the following situation. I will refer to my entities as 'articles' as that would best/most-easily describe them.

So the articles have already been created and have

id  | name   | content
1   | aricle1| the frog jumped...
2   | aricle2| Joe met stan...
3   | aricle3| Mikey has never...

Now, I would like to be able to retrospectively make certain articles sub-articles of other articles, and make other articles sub-articles of sub-articles (sub-sub articles). Most articles will NOT have a sub article, but many will.

So essentially it is building lots of little trees, with a presumed max-depth of 5.

I would like to be able to shift a set (articles and all sub-articles) under the domain of another article with (relative) ease.

After having done plenty of reading, I am almost 100% certain anything relating to the nested set model would be overkill and not appropriate.

Other than that however, I don't have much idea as to what is the proper approach here.

Sincere thanks for any help. It is greatly appreciated.

If you can only have one Parent article, add a column and call it 'parentArticle'.

ALTER TABLE table_name
ADD parentArticle int

This will link to the article id of the parent article. You can then have as many depth as you would like.

  I would like to be able to shift a set (articles and all sub-articles)
  under the domain of another article with (relative) ease.

You could then add a 'category' column and a category table or, to keep it under articles, have a master article named after its category.