Same server, same query, different response time I have a development server that has some problem to access the data, user reporting that it's too much slow sometimes.
The setup is:

* virtual server;
* 4 virtual CPU;
* 8 GB of virtual memory ;
* 80 GB of virtual HD (the real HD is a SDD one), I had still 36 GB available;
* OS Debian 9;
* Mysql 5.6.47;

To avoid all problems about network and the Web App, I simply do my queries directly on the host where Mysql is installed.
I had enabled the logging of slow query, and find the slowest query. This query start from a certain table, that I report below:

CREATE TABLE `MALICIOUS_TABLE` (
  `column_1` int(11) NOT NULL AUTO_INCREMENT,
  `column_2` varchar(8) NOT NULL,
  `column_3` datetime NOT NULL,
  `column_4` int(11) NOT NULL,
  `column_5` int(11) DEFAULT NULL,
  `column_6` int(11) DEFAULT NULL,
  `column_7` int(11) DEFAULT NULL,
  `column_8` tinyint(1) DEFAULT NULL,
  `column_9` datetime DEFAULT NULL,
  `column_10` int(11) DEFAULT NULL,
  `column_11` varchar(2048) DEFAULT 'column_11',
  `column_12` tinyint(1) DEFAULT NULL,
  `column_13` datetime DEFAULT NULL,
  `column_14` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`column_1`),
  KEY `fk_ual_aut_idx` (`column_2`),
  KEY `fk_aul_c_idx` (`column_4`),
  KEY `kf_ual_po_idx` (`column_5`),
  KEY `fk_ual_ute_idx` (`column_10`),
  KEY `column_1` (`column_1`),
  KEY `column_2` (`column_2`),
  CONSTRAINT `fk_aul_c` FOREIGN KEY (`column_4`) REFERENCES `t_table2` (`column_4`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_ual_aut` FOREIGN KEY (`column_2`) REFERENCES `t_tabl3` (`column_2`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_ual_po` FOREIGN KEY (`column_5`) REFERENCES `t_table4` (`column_5`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_ual_ute` FOREIGN KEY (`column_10`) REFERENCES `t_table5` (`column_10`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2357917 DEFAULT CHARSET=latin1

The table has a not so small number of records:

select count(*) from `MALICIOUS_TABLE`;
+----------+
| count(*) |
+----------+
|  2308414 |
+----------+
1 row in set (2,67 sec)

If I try the slowest query, always from the mysql command line on the server, every about 10 seconds, I got different response times, this is the production server, so users keep insert data:

SELECT count(*) FROM `MALICIOUS_TABLE` WHERE column_4 = 1 AND (column_8 is null) AND column_3 > CURDATE() - INTERVAL 30 DAY;
+----------+
| count(*) |
+----------+
|   666411 |
+----------+
1 row in set (4,39 sec)

SELECT count(*) FROM `MALICIOUS_TABLE` WHERE column_4 = 1 AND (column_8 is null) AND column_3 > CURDATE() - INTERVAL 30 DAY;
+----------+
| count(*) |
+----------+
|   666477 |
+----------+
1 row in set (4,94 sec)

SELECT count(*) FROM `MALICIOUS_TABLE` WHERE column_4 = 1 AND (column_8 is null) AND column_3 > CURDATE() - INTERVAL 30 DAY;
+----------+
| count(*) |
+----------+
|   666752 |
+----------+
1 row in set (17,02 sec)

The last attempt has a great variation of response time. At the beginning I thought that maybe indexes are the problem, I drop them and recreate them. Yet I got the huge variation of the response time.

The RAM of the server it's good, still getting about 2 giga of free RAM.

The query caching of Mysql it's active, and maybe the second attempt retrieve the query from the cache, and the last one no.

Any suggestion of what I can check to understand the problem? The machine, the db (now I'm trying to modify query cache settings) or the table itself?

Thank you in advance.

Results coming from the Query cache take about 1ms, regardless of the query.  The QC is simply a hashed lookup of SQL --> resultset.
On busy production systems, turning off the QC usually helps overall performance.
This composite index would speed up that query:
INDEX(col4, col8, col3)   -- col3 in 3rd place


Creating relationships between tables that where not specified in the given UML diagram Good evening!

I'm working on designing my very first actual database from the following UML class diagram (in French unfortunately):

I'm at the point of creating the relational sketch of it which I created this way:

Yet, it creates some issues when trying to ask to the database which are the clients that never ordered product number one?

Indeed, I don't know how to do it in SQL as far as it seems that there is no relations between Client, Commande and Produit. It gived the following sql code:

CREATE TABLE Client (
IDClient INT NOT NULL,
AdresseClient VARCHAR(255)NOT NULL ,
NomContact VARCHAR(255)NOT NULL,
NumeroSIRET VARCHAR(14) NOT NULL,
CONSTRAINT cclient PRIMARY KEY (IDClient)
);

CREATE TABLE Produit (
IDProduit INT NOT NULL  ,
PrixVente INT NOT NULL ,
QuantiteEnStock INT NOT NULL ,
CONSTRAiNT cproduit PRIMARY KEY (IDProduit)
) ;

CREATE TABLE Commande (
IDNumeroCde INT NOT NULL  ,
Date DATE NOT NULL ,
CONSTRAINT ccommande PRIMARY KEY (IDNumeroCde)
);

In relational algebra and calculus it gives:

The fact that I can't explain it in relational calculus let me think that I've done a mistake.

Edit with the new tables

I read all the links you provided and therfore add some comments and changes on my tables, can you tell me if I'm right on my assumptions?

But the last ones have the same primary key then. is it a problem?

UML class diagrams do not include relations in a class' attributes list, so when creating database tables representing those classes, the database designer must identify and specify them, much like the software developer must create the pointers and lists to support the related objects.

In your diagram, Commande relates to Client but does not explicitly specify a IDClient attribute. When creating the Commande table, you must include this attribute along with a foreign key to Client.

You can find further information about this in Database Design - UML/SQL:

  The UML representation of the Order class contains only its own descriptive attributes. The UML association tells which customer placed an order. In the database, we will need a different way to identify the customer; that will be part of the relation scheme (below).


Access: Making a Pivot Table with Multiple, Same Values as Columns First post ever, thanks for having me!

Currently we have an Access database connected to two access control devices tracking hours for different statuses: Check In, Check Out, Break Out, Break In, Overtime In, Overtime Out.

How our devices manage this though, is that they record each time independently, so in the Access database we have different rows telling the username, date and time, status, device and other interesting stuff that sadly don't help with the problem. What I want to focus on though is time, which I want to put in separate columns using Access' CrossTab mode, with TRANSFORM and PIVOT and what else.

The main issue is that the employees have many breaks during the day and sometimes many overtime schedules and I have no way to tell which is the first or the second break/overtime but the time when those happened.

Here is an example of what I have with Dave:

Name    | Date       | Auth     | Time  |
------------------------------------------------------------------
Dave    | 21.01.2019 | CheckOut | 18:00 |
Dave    | 21.01.2019 | BreakIn  | 16:45 |
Dave    | 21.01.2019 | BreakOut | 16:30 |
Dave    | 21.01.2019 | BreakIn  | 14:00 |
Dave    | 21.01.2019 | BreakOut | 13:00 |
Dave    | 21.01.2019 | BreakIn  | 10:00 |
Dave    | 21.01.2019 | BreakOut | 09:45 |
Dave    | 21.01.2019 | CheckIn  | 07:30 |

And here is what I'm trying to achieve with that:

Name    | Date       | CheckIn | BreakOut1 | BreakIn1 | BreakOut2 | BreakIn2 | BreakOut3 | BreakIn3 | CheckOut |
----------------------------------------------------------------------------------------------------------------
Dave    | 21.01.2019 |  07:30  |   09:45   |   10:00  |   13:00   |   14:00  |   16:30   |   16:45  |  18:00   |

Right now I managed to obtain somewhat what I planned to have, but I was only able to output one column for each Auth. If I manage to get the data that way I might be able to calculate exactly how many hours an employee has worked.

Do you think that using a CrossTab is a good idea to do such? If so could you please help me reach the objective? Not really looking for snippets of instant solutions, rather many ways to think on how to achieve it. I have a standard expertise with Access and database in general so one can never stop learning!

Thanks in advance, have a great day.

Leo

It's apparent that you intended to create a single Crosstab, but I couldn't help but notice the actual final goal:  "I might be able to calculate exactly how many hours".  Indeed, if you had the final data as you showed, it would be possible to get total times, but there are other normalized forms that allow for sums without special code for handling multiple breaks and/or differing number of breaks.  Below is a sequence of queries that gives you total times.  If you still prefer your Crosstab format, the first few queries provide a form which allows you to pivot on different break times as desired.

This set of queries may seem like overkill, but each has its purpose and this style of query coding is compatible with treating the data as a set.  This is simply a sequence of operations on the set.  A desire to do all things in a single query is an artificial requirement.  Some of these could be combined as nested queries, but that removes some convenience within Access Query Designer, and Access tends to throw fits with nested queries for TRANSFORMS as I've discovered.

Named query [BreakSeq].  This same query could produce combined labels like "BreakOut1" with small modifications:

SELECT Data.Naem, Data.Daet, Data.Time,
    IIf([Auth]='BreakIn','In','Out') AS InOut,
    1 + DCount("[Naem]","[Data]","[Naem]='" & [Naem] & "' AND [Daet]=#" & [Daet]
    & "# AND [Auth]='" & [Auth] & "' AND [Time] < #" & [Time] & "#") AS [Number]
FROM Data
WHERE (Data.Auth Like "Break*")
ORDER BY Data.Naem, Data.Daet, Data.Time;

Named query [BreakNorm].  Transform requires an aggregate function, but the assumptions along with the groupings guarantee unique values, so FIRST() will return such unique values.  Also, had your table been normalized with this form, subsequent queries would have been easier to produce:

TRANSFORM First(BS.Time) AS FirstOfTime
SELECT BS.Naem, BS.Daet, BS.Number, "Break" AS Type
FROM BreakSeq AS BS
GROUP BY BS.Naem, BS.Daet, BS.Number
ORDER BY BS.Naem, BS.Daet, BS.Number
PIVOT BS.InOut;

Named query [BreakDetail].  This produce negative minutes useful for adding to overall CheckIn and Out spans:

SELECT BN.*, DateDiff("n",[In],[Out]) AS Minutes
FROM BreakNorm AS BN;

Named query [BreakSum]:

SELECT BD.Naem, BD.Daet, BD.Type, Sum(BD.Minutes) AS BreakMinutes
FROM BreakDetail AS BD
GROUP BY BD.Naem, BD.Daet, BD.Type;

Now for the CheckIn and CheckOut... Named query [CheckSeq]:

SELECT Data.Naem, Data.Daet, IIf([Auth]='CheckIn','In','Out') AS InOut, Data.Time
FROM Data
WHERE (Data.Auth Like "Check*");

Named query [CheckNorm]:

TRANSFORM First(CS.Time) AS FirstOfTime
SELECT CS.Naem, CS.Daet, "Check" AS Type
FROM CheckSeq AS CS
GROUP BY CS.Naem, CS.Daet
ORDER BY CS.Naem, CS.Daet
PIVOT CS.InOut;

Named query [CheckDetail]:

SELECT CN.*, DateDiff("n",[In],[Out]) AS Minutes
FROM CheckNorm AS CN;

Now combine the results with a total sum of minutes worked per name and date:

SELECT BS.Naem, BS.Daet, [CD].[Minutes]+[BS].[BreakMinutes] AS WorkingMinutes
FROM BreakSum AS BS INNER JOIN CheckDetail AS CD
    ON (BS.Daet = CD.Daet) AND (BS.Naem = CD.Naem)
ORDER BY BS.Naem, BS.Daet;

This solution assumes the following.  If any of these assumptions are not guaranteed, exceptions should be identified and resolved, otherwise you'll need to code special logic to avoid errors and bogus numbers:

There is precisely one CheckIn and one CheckOut entry per name and date.
For each BreakOut entry there is a corresponding BreakIn entry, so that there are no unmatched pairs.
The in and out times are proper, so that the CheckOut time is after the CheckIn time and BreakOut times precede corresponding BreakIn times and that there are no overlapping break times.
CheckIn, CheckOut, BreakIn, BreakOut are the only authentication types.
Text columns [Naem] and [Auth] don't contain apostrophes (').  Otherwise, special care must be taken to properly delimit those values in some queries.

If you insist on the final form you showed, here's a solution with fewer queries.  Sorry if I made it too instant.

Named query [AllSeq]:

SELECT Data.Naem, Data.Daet, Data.Time, Data.Auth,
   IIf([Auth] Like "Break*","Break","Check") AS Type,
   IIf([Auth] Like '*In','In','Out') AS InOut,
   1 + DCount("[Naem]","[Data]","[Naem]='" & [Naem] & "' AND Daet=#"
      & [Daet] & "# AND Auth='" & [Auth] & "' AND Time < #" & [Time]
      & "#") AS [Number],
   IIf([Auth] Like "Break*",[Auth] & [Number],[Auth]) AS AuthNum
FROM Data
ORDER BY Data.Naem, Data.Daet, Data.Time;

Named query [AllCross]:

TRANSFORM First(AZ.Time) AS FirstOfTime
SELECT AZ.Naem, AZ.Daet
FROM AllSeq AS AZ
GROUP BY AZ.Naem, AZ.Daet
PIVOT AZ.AuthNum;

Named query [AllCrossSorted]:

SELECT AC.Naem, AC.Daet, AC.CheckIn, AC.BreakOut1, AC.BreakIn1,
       AC.BreakOut2, AC.BreakIn2, AC.BreakOut3, AC.BreakIn3, AC.CheckOut
FROM AllCross AS AC;


"[ftdc] serverStatus was very slow" - MongoDB 4.0.4 I'm seeing very frequent messages like this in mongod.log and doesn't give an exact reason for the server status was very slow.
looks like ftdc logs more information but interpreting ftdc log is very tedious method and is not human readable format.
Due to this issue mongo daemon stops working abruptly and becomes stale which results in replication issues and loosing quorum and hence DB inaccessible.
Error log:
2021-04-21T04:56:39.729+0000 I COMMAND [ftdc] serverStatus was very slow: { after basic: 2165, after asserts: 3087, after backgroundFlushing: 8785, after connections: 11612, after dur: 12922, after extra_info: 19620, after globalLock: 27972, after locks: 41890, after logicalSessionRecordCache: 51379, after network: 55947, after opLatencies: 62013, after opcounters: 67473, after opcountersRepl: 70586, after repl: 74168, after security: 78481, after storageEngine: 78629, after tcmalloc: 78629, after transactions: 78629, after transportSecurity: 78629, after wiredTiger: 78813, at end: 79004 }

Question :
how to troubleshoot further ?
Enabling verbose 5 logs does gives more information ?
Is it truly a memory issue ?
what's the next step to avoid this issue ?

That means, your server is overload. It can't even reply to the serverStatus command without delay.
Some people solved that by adding more hardware to their server. But, I think first of all you should check your queries and indexes, maybe you can optimize them.
Check out your queries:
db.currentOp()


How can I set a default session sql_mode for a given user? I want to have a default session sql_mode that is different from the global sql_mode.

I can set it manually, but I'd like to do it automatically.

I thought I could add it to .my.cnf, but that doesn't work. I tried adding it to the [mysql] group in .my.cnf, and it does not throw an error.

Yet, when I connect my session sql_mode, the session still inherits the global sql_mode.

I'm using MySQL 5.5.16.

As long as the account in question doesn't have the SUPER privilege, you could accomplish this with the global server variable init_connect, which is...

  A string to be executed by the server for each client that connects. The string consists of one or more SQL statements, separated by semicolon characters.

This is part of the global server configuration and gets executed right before processing of the first query sent by each new client connection (except for users with SUPER, because if you broke this, you couldn't log in to fix it otherwise).

The init_connect contents are executed by the server for all non-SUPER users... however, we could construct a CASE expression that uses the identity of the logged-in user via the CURRENT_USER() function and evaluates to the appropriate sql_mode for that user, and otherwise use the default.

The expression might be something like this:

mysql> SET @@sql_mode = CASE CURRENT_USER()
                        WHEN 'usera@%' THEN 'TRADITIONAL'  # this user gets 'TRADITIONAL'
                        WHEN 'userb@localhost' THEN 'ANSI' # this user gets 'ANSI'
                        ELSE @@sql_mode                    # everybody else gets the default value,
                        END;                               # from global sql_mode

Once you have the expression you want, wrap it up in string and assign it to the global variable init_connect.

mysql> SET GLOBAL init_connect =
           "SET @@sql_mode = CASE CURRENT_USER()
                             WHEN 'usera@%' THEN 'TRADITIONAL'
                             WHEN 'userb@localhost' THEN 'ANSI'
                             ELSE @@sql_mode
                             END;";

The same thing could then be added to the [mysqld] section of my.cnf to make your configuration persist across restarts, although you'd almost certainly have to remove the whitespace I added for readability and possibly adjust the quoting.

Tested on MySQL Server 5.5.30.  I attempted to benchmark whether this has any significant impact on initial connection time or first query execution time and my conclusion is that if there is an impact, it is most definitely in the sub-millisecond range and was too short to identify on the system under test.


how to improve query performance ## Only 1 query which returns 10 rows.
## It took about 66 secs

mysql> explain
    SELECT al.log_id , al.user_id , al.page_name ,
           al.section_name , al.submit_time , al.url  ,
           alfm1.value as 'source_env'  ,
           alfm2.value as 'website_id'  ,
           alfm3.value as 'index_id'
    FROM  admin_logs al
      LEFT OUTER JOIN
        (select * from admin_log_field_map where field_id = 3351
        )  alfm1 ON al.log_id = alfm1.log_id
      LEFT OUTER JOIN
        (select * from admin_log_field_map where field_id = 911
        )  alfm2 ON al.log_id = alfm2.log_id
      , admin_log_field_map alfm3
    WHERE 1=1
      AND al.page_name='Index Management'
      AND al.log_id=alfm3.log_id
      AND alfm3.field_id=891
      AND alfm3.value='jewelry'
    ORDER BY al.log_id DESC;

+----+-------------+---------------------+--------+--------------------+------------+---------+---------------------+-------+----------------------------------------------+
| id | select_type | table               | type   | possible_keys      | key        | key_len | ref                 | rows  | Extra                                        |
+----+-------------+---------------------+--------+--------------------+------------+---------+---------------------+-------+----------------------------------------------+
|  1 | PRIMARY     | <derived2>          | system | NULL               | NULL       | NULL    | NULL                |     0 | const row not found                          |
|  1 | PRIMARY     | al                  | ref    | idx_622,idx_pgname | idx_pgname | 78      | const               | 27720 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY     | alfm3               | ref    | idx_1533,idx_0311  | idx_1533   | 10      | cms.al.log_id,const |     1 | Using where                                  |
|  1 | PRIMARY     | <derived3>          | ALL    | NULL               | NULL       | NULL    | NULL                | 35358 |                                              |
|  3 | DERIVED     | admin_log_field_map | ref    | idx_0311           | idx_0311   | 5       |                     | 33098 | Using where                                  |
|  2 | DERIVED     | admin_log_field_map | ref    | idx_0311           | idx_0311   | 5       |                     |     1 | Using where                                  |
+----+-------------+---------------------+--------+--------------------+------------+---------+---------------------+-------+----------------------------------------------+
6 rows in set (0.20 sec)

mysql>
mysql>
mysql> show create table admin_logs\G
*************************** 1. row ***************************
       Table: admin_logs
Create Table: CREATE TABLE `admin_logs` (
  `row_mod` datetime DEFAULT NULL,
  `row_create` datetime DEFAULT NULL,
  `log_id` int(11) DEFAULT NULL,
  `user_id` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `page_name` varchar(75) COLLATE latin1_bin DEFAULT NULL,
  `section_name` varchar(125) COLLATE latin1_bin DEFAULT NULL,
  `submit_time` datetime DEFAULT NULL,
  `url` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `source_environment` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `log` longtext COLLATE latin1_bin,
  UNIQUE KEY `idx_622` (`log_id`),
  KEY `idx_pgname` (`page_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)

mysql> \! hostname
fol-piwikdb01.dca.ftd.untd.com
mysql> show create table admin_log_field_map\G
*************************** 1. row ***************************
       Table: admin_log_field_map
Create Table: CREATE TABLE `admin_log_field_map` (
  `row_mod` datetime DEFAULT NULL,
  `row_create` datetime DEFAULT NULL,
  `log_id` int(11) DEFAULT NULL,
  `field_id` int(11) DEFAULT NULL,
  `value` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  UNIQUE KEY `idx_1533` (`log_id`,`field_id`),
  KEY `idx_0311` (`field_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)

how to get better performance using above query?

First, lets rewrite the query by dropping the ANSI-89 implicit join syntax and removing the derived tables:

SELECT
       al.log_id , al.user_id , al.page_name ,
       al.section_name , al.submit_time , al.url  ,
       alfm1.value as 'source_env'  ,
       alfm2.value as 'website_id'  ,
       alfm3.value as 'index_id'
FROM
    admin_logs AS al
  LEFT OUTER JOIN
    admin_log_field_map AS alfm1
      ON  alfm1.field_id = 3351
      AND alfm1.log_id = al.log_id
  LEFT OUTER JOIN
    admin_log_field_map AS alfm2
      ON  alfm2.field_id = 911
      AND alfm2.log_id = al.log_id
  INNER JOIN
    admin_log_field_map AS alfm3
      ON  alfm3.field_id = 891
      AND alfm3.value = 'jewelry'
      AND alfm3.log_id = al.log_id
WHERE
    al.page_name = 'Index Management'
ORDER BY
    al.log_id DESC ;

I've also move some of the conditions from the WHERE clause to the responding ON clause, so it is more apparent which indexes you need.

From the two outer joins it's clear that from table admin_log_field_map, you only need the rows with a fixed field_id (either 3351 or 911) and then only the columns log_id (to do the join) and the value (to be returned in the SELECT list):

SELECT
--
       alfm1.value as 'source_env'  ,
--
  LEFT OUTER JOIN
    admin_log_field_map AS alfm1
      ON  alfm1.field_id = 3351
      AND alfm1.log_id = al.log_id

So, a good index for these 2 left joins would be (field_id, log_id, value)

The third (inner) join to admin_log_field_map, is different. You only need the rows with a fixed field_id (891) and fixed value ('jewelry') and then log_id (to do the join). After that, nothing is really needed for the SELECT list (the value is already known and fixed):

SELECT
--
       alfm3.value as 'index_id'
--
  INNER JOIN
    admin_log_field_map AS alfm3
      ON  alfm3.field_id = 891
      AND alfm3.value = 'jewelry'
      AND alfm3.log_id = al.log_id

So, a good index for these 2 left joins would be (field_id, value, log_id)

For the admin_logs table, you already have an index on (page_name) so it will probably be used for the WHERE condition.

So, what you can do is add these two indexes:

ALTER TABLE admin_log_field_map
    ADD INDEX idx__field_id__log_id__value
        (field_id, log_id, value)
  , ADD INDEX idx__field_id__value__log_id
        (field_id, value, log_id) ;


Using the output from mysqldiff.exe to transform database2 to match database1 This question is related to another I had asked earlier here: Passing passwords more securely to mysql utilities on the command line.

I have recently been trying to use mysqldiff.exe, part of MySQL Utilities group of tools. My goal is to spot any schema differences between database1 and database2, then going by what it says "on the label", I should be able to obtain statements that can transform either of the databases to match the other.

Here's example output I obtained by comparing database1.clients and database2.clients.

# WARNING: Using a password on the command line interface can be insecure.
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Comparing database1.clients to database2.clients            [FAIL]
# Object definitions differ. (--changes-for=server1)
#

--- database1.clients
+++ database2.clients
@@ -61,4 +61,4 @@
   CONSTRAINT `clients_ibfk_6` FOREIGN KEY (`state_code`) REFERENCES `states` (`state_code`) ON DELETE
SET NULL ON UPDATE CASCADE,
   CONSTRAINT `clients_ibfk_7` FOREIGN KEY (`rel_id`) REFERENCES `religions` (`rel_id`) ON DELETE SET NULL ON
UPDATE CASCADE,
   CONSTRAINT `clients_ibfk_9` FOREIGN KEY (`nationality_id`) REFERENCES `country` (`Code`) ON DELETE SET NULL
 ON UPDATE CASCADE
-) ENGINE=InnoDB AUTO_INCREMENT=8041 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
+) ENGINE=InnoDB AUTO_INCREMENT=691802 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT
Compare failed. One or more differences found.

Problem is, I was expecting the output to be usable SQL statements, so I can then run this on the relevant side of the compare. How can I use this tool to perform database schema synchronization as I described?

As it appears on the manual, use the option --difftype=sql in order to obtain the results as ALTER TABLEs.

You have an example on a recent MySQL Performance Blog post:

$ mysqldiff --force --difftype=sql \
--server1=root:msandbox@127.0.0.1:21489 \
--server2=root:msandbox@127.0.0.1:21490 \
employees:employees


View metadata refresh in MySQL Like other RDBMS, view meta data is stored at view creation time.

From CREATE VIEW in the MySQL docs

  The view definition is “frozen” at creation time, so changes to the underlying tables afterward [sic] do not affect the view definition.

I can't see a way to refresh this view meta data cleanly, unlike other RDBMS:

SQL Server: EXEC sp_refreshview 'MyView'
Oracle: ALTER VIEW MyView COMPILE

The system here appears to be "do this manually" with DROP/CREATE which isn't my style. The usual crowd who do this manual process are on leave so it falls to me this week.

I don't have a minion to nominate either: so any solutions please...

To see the view's definition, run one of the following:

SHOW CREATE VIEW viewname\G
SELECT * from information_schema.views where table_name='viewname'\G

Here is a quick example:

mysql> show create table mytable\G
*************************** 1. row ***************************
       Table: mytable
Create Table: CREATE TABLE `mytable` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `age` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from mytable;
+----+-----+
| id | age |
+----+-----+
|  1 |  10 |
|  2 |  15 |
|  3 |  20 |
|  4 |   5 |
+----+-----+
4 rows in set (0.00 sec)

mysql> create view mytable_tens as select * from mytable where MOD(age,10) = 0;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from mytable_tens;
+----+-----+
| id | age |
+----+-----+
|  1 |  10 |
|  3 |  20 |
+----+-----+
2 rows in set (0.00 sec)

mysql> show create view mytable_tens\G
*************************** 1. row ***************************
                View: mytable_tens
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=``@`` SQL SECURITY DEFINER VIEW `mytable_tens` AS select `mytable`.`id` AS `id`,`mytable`.`age` AS `age` from `mytable` where ((`mytable`.`age` % 10) = 0)
character_set_client: latin1
collation_connection: latin1_swedish_ci
1 row in set (0.00 sec)

mysql> select * from information_schema.views where table_name='mytable_tens'\G
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: johnlocke
          TABLE_NAME: mytable_tens
     VIEW_DEFINITION: select `johnlocke`.`mytable`.`id` AS `id`,`johnlocke`.`mytable`.`age` AS `age` from `johnlocke`.`mytable` where ((`johnlocke`.`mytable`.`age` % 10) = 0)
        CHECK_OPTION: NONE
        IS_UPDATABLE: YES
             DEFINER: @
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: latin1
COLLATION_CONNECTION: latin1_swedish_ci
1 row in set (0.02 sec)

Whichever method you choose, you can visibly see the query making up the view. You can sculpt the CREATE OR REPLACE VIEW using whatever is shown.

Hope this helps, and Welcome to the DBA MinionExchange  !!!


How do you use MAXDOP in a View? I have several views that are used to export data from SQL-Server into .csv files. They are all executed via SSIS packages. Our DBA has decided to set the "Max Degree of Parallelism" to 1 and told me to use MAXDOP where I think it makes sense.

I have now seen many times that MAXDOP really helps to pull big data amounts (especially when it results in a table scan over all partitions).

Questions

How can I use MAXDOP in a view ?
Is there a way around this restriction ?

You can't.

A view is a stored sub-query. MAXDOP is a query hint.


How can I escape XML field in XML-output query? I have following request:

SELECT XMLAGG(data) FROM
(SELECT XMLELEMENT(name item, XMLFOREST(
   title,
   body as description,
   date_created as date,
   'http://example.com/news'||id as link
)) AS data FROM NEWS) AS smthng;

The issue is that body is actually XML itself, so the question is -
How can I escape XML field (changing < to &lt; > to &gt; etc.) in this query?

The naive approach would be to wrap your query around another SELECT and use replace.

SELECT replace(
    (SELECT XMLAGG(data) FROM
         (SELECT XMLELEMENT(name item, XMLFOREST(
          title,
          body as description,
          date_created as date,
          'http://example.com/news'||id as link
         )
     ) AS data FROM NEWS) AS smthng)::text, '<', '&lt;')

And do the same for >. A easier and more elgant mthod would be to to it with the language that should deliver/process/... the XML.


SQL 2019 install....what is AzureAttestService? We started the installation and configuration of SQL Server 2019 on Windows 2019 and noticed after install a new service named 'AzureAttestService' was running and set to automatic. Services list the Description and Group field also as AzureAttestService. Below are a few questions:

What is the purpose of this service?
What is the relationship with SQL Server 2019?
Any negatives of stopping and disabling this service?

I've researched, but haven't found much information on AzureAttestService. If anyone has information on this service and can share I'd be greatly appreciative.

Thanks!

This service has to do with the Always Encrypted with Enclaves feature.  I have sent a note to the team to get this more explicitly documented for customers.  Some scenarios will break if that service is not enabled.

Conor Cunningham


lsnrctl: cannot execute binary file: Exec format error When I type lsnrctl from my command prompt the below error message is displayed:

oracle@Gaurav:~$ lsnrctl
bash: /u01/app/oracle/product/12.1.0/dbhome_1/bin/lsnrctl:
cannot execute binary file: Exec format error

Can anyone please provide me lsnrctl for oracle 12c, so that I could replace it?

First, don't worry about the comments. Your question or problem is total valid.

Second, there is a comment about "ubuntu isn't supported so your question should be deleted" which isn't right. If ubuntu isn't "supported", that means you will have probably more problems if you try to install it, and Oracle won't help you. But Oracle never can be really easy installed (I am thinking some like so easy as typing as an apt-get install oracle-11.2-server or like, which is easy. Sucking with a monstrous and buggy java installer, which Oracle DB has, isn't easy), not for the so-named "supported" distributions/systems and not for the "unsupported" ones. Second, your problem is still a valid database administration problem, even if you use ubuntu or not.

About your problem: it is really clear. lsnrctl had to be runnable. This error message has the very clear meaning: your lsnrctl has an invalid binary format in your architecture. For example, you are trying to run a 64-bit binary on a 32-bit system. Or you try to run an i386 binary on a ia64 system. Or your lsnrctl binary is somehow damaged.

The command file -szL /u01/app/oracle/product/12.1.0/dbhome_1/bin/lsnrctl could make clear, which is the real problem, thus what is happened to your lsnrctl. But it is very probable, that simply you have a bad distribution installed, and so you need a reinstall, give a try to this command before.


pgBouncer - multiple servers with same username but different password I have an active pgBouncer setup with one PostgreSQL server.
We are planning to add one more PostgreSQL server to the same pgBouncer. But the problem is that both databases servers have the same username, but with different passwords.
Server1:

user: admin
password: admin123

Server2:

user: admin
password: admin@213

This feature is already requested here, can someone help me with any possible solutions?

There is a good workaround at the link in your question.
Another thing you can try is to use auth_user and auth_query with pgBouncer so that it fetches the password from the respective database. I am not sure if that works or if pgBouncer caches the password for each user.


How to apply transpose method in MYSQL, Getting rows as collumns have a table of ideas that stores responses from evaluators and stores as one row per evalautor For ex : Evalautor 1 evaluated idea A then in table record gets added as
IDEA A, COMMENTS , EVALUATOR ID

as single idea have 4 evaluator response and i need to show data in this format
IDEA ID ||. EVALUATOR 1 || EVALUATOR 2|| EVALUATOR 3 || EVALUATOR 4
1 --        Comments --    Comments --   Comments --    Comments

Need assistance in creating logic to get information in format given above.
Query i am running :
 SELECT * from Toy_ideas GROUP BY evaluator id

but getting results as
Ideaid -- comments -- evaluator id

As yu will have different evaluators ver the time a dynamic approach is called for
if you have only a static, you can select the variable @sql and get ten result of the dynamc query

CREATE table Toy_ideas  (IDEA int, COMMENTS varchar(50), EVALUATOR_ID int)

INSERT INTO Toy_ideas VALUES(1,'coment1',1),
(1,'coment2',2),
(1,'coment3',3),(1,'coment4',5),
(2,'coment5',2),
(2,'coment6',4),(2,'coment7',5),
(3,'coment8',2),(3,'coment9',5)

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(EVALUATOR_ID = ',
     EVALUATOR_ID,
      ', `COMMENTS`,NULL)) AS `',
      CONCAT('EVALUATOR ',EVALUATOR_ID), '`'
    )
    ORDER BY EVALUATOR_ID
  )
  INTO @sql
FROM Toy_ideas;
SET @sql = CONCAT('select IDEA,',@sql,' from Toy_ideas
group by `IDEA`');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

IDEA | EVALUATOR 1 | EVALUATOR 2 | EVALUATOR 3 | EVALUATOR 4 | EVALUATOR 5
---: | :---------- | :---------- | :---------- | :---------- | :----------
   1 | coment1     | coment2     | coment3     | null        | coment4
   2 | null        | coment5     | null        | coment6     | coment7
   3 | null        | coment8     | null        | null        | coment9

db<>fiddle here


Replication with two publishers I have three servers with own databases, and database schemas are same. Let's call them servers A, B and C.

All three servers will be operational, which means that all three servers will serve for its region.

Data from servers B and C need to be replicated on server A, so that server A will contains data from all servers, but other two servers contain just own data.

I need to implement some replication solution to achieve this goal, and I am asking if it is possible to have two publishers (server B and C) and one server with two subscription (server A)? Is there any better solution? Which replication model will be the best?

You can use Transaction Replication, if you can fulfill its considerations regarding:

Transaction log space.
Disk space for the distribution database.
Primary keys for each published table.
Triggers.
Large object (LOB) data types.

Transaction Replication allows data modified at Publisher to be delivered to the Subscriber as they occur (in near real time). The data changes are applied to the Subscriber in the same order and within the same transaction boundaries as they occurred at the Publisher; therefore, within a publication, transactional consistency is guaranteed.

You can also look at this using transaction replication or this using merge replication.


Impact of moving the ldf and mdf files temporarily for few hours I want to copy my database files(ldf and mdf) into a separate box for few hours as there is a disk space issue in my current sql server box.
Currently i have mdf in one drive(:D) and ldf in another drive(:E).
I have to move both mdf and ldf into same drive,say :D.
I understand that it is recommended to have separate disk drives for LDF and MDF ,but this is a DEV box and to avoid purchase/delay client wants to go with this option.

I have two plans:

Take backups of all systems databases and user databases and save it
in a separate box.
Move MDF and LDF to a separate box for few hours

So after the partitioning/merging of disk drives are completed i can either:

Restore the backups.
Move the MDF and LDF to the merged drive.

For user database ,i understand that the below steps will be sufficient:

Bring the database offline
Move the file(s) to the new location manually.
Update the system catalog settings:

ALTER DATABASE DBNAME
        MODIFY FILE (
            NAME = 'DBNAME',
            FILENAME = 'D:\SQL_Data\DBNAME.mdf'
        )
        GO

ALTER DATABASE DBNAME
    MODIFY FILE (
        NAME = 'DBNAME_log',
        FILENAME = 'D:\SQLLog\DBNAME_log.ldf'
    )
Bring the database online

Can someone explain the dangers of second step or direct me how to do second step for system databases.?

You should don't have any issues I have done this many times before with no big issues.

One thing to consider is that if you have binaries in  E drive, you may have some issues, otherwise should be ok to go.

From what I remember in the past you should check OS privileges, once  I had issues with the SQL Server account access to the drives.

Another tip: If you plan to go with the move of the datafiles, you may want to try to shrink the tlog files before put offline, this way you will save some MB/GB and copy time will be faster.


Incorrect query filter according to the requirement My requirement wholly depends on the QUERY.

I am filtering my gridview on the basis of three parameters.

Project_name (Always required)
Date (Not always)
Vehicle_No (Not always)

So, what my requirement is,

  With project_name either any one can be used for filtering my gridview.

Currently my debugged query works exactly fine for any two parameters.

Below is my debugged query:-

SELECT DISTINCT sv.mkey, vehicle_no,
               TO_CHAR (date_in, 'dd-MM-yyyy')
            || ' & '
            || time_in vehicleindate_time,
               TO_CHAR (date_out, 'dd-MM-yyyy')
            || ' & '
            || time_out vehicleoutdate_time,
            gate_no_in || ' & ' || gate_no_out ingate_outgateno,
            gd.good_type goods_type, net_weight netweight,
               TO_CHAR (challan_date, 'dd-MM-yyyy')
            || ' & '
            || challan_no challandate_no,
            remark_in remarkin, NULL receipt_no, date_in
       FROM xxcus.xxgid_supinv sv,
            xxcus.xx_supinv_goodtype gd,
            xxcus.xxacl_xxgid_user_mst ms
      WHERE (gd.good_type_code(+) = sv.good_type)
        AND (       sv.project_id = '1368'
                AND TO_CHAR (date_in, 'dd-MM-yyyy') = '26-11-2015'
             OR vehicle_no = '0'
            )
        AND sv.delete_flag = 'N'
   ORDER BY date_in DESC, vehicle_no

The above query shows me the exact result for project_name and Date which I entered.

But when I add the third parameter say Vehicle_no it doesn't show the data for that exact vehicle_no it still shows me the same data which I looked for 2 parameters.

I am using Oracle for the database.

LATEST UPDATE WHICH IS NOT WORKING

I am confused as how to deal with the query, as Balazs' solution was working fine when I selected, Project_name, Date and then vehicle_no (not necessary). But it was not fetching proper result when I took first as Project_name, and Vehicle_no. Any guesses why?

  The issue  is when I only filter with Project_name and Vehicle_no the date_column value comes as 0 so it doesn't show me any row, which is incorrect as I have data for that Project_name and Vehicle_no.

I have used Balaz's second query for the solution which is below

WHERE (gd.good_type_code(+) = sv.good_type)
    AND (       sv.project_id = '1368'
            AND TO_CHAR (date_in, 'dd-MM-yyyy') = '26-11-2015'
         AND vehicle_no = decode(:vehicle_no, '0', vehicle_no, :vehicle_no)
        )

The problem is with the below bind variable substitution:

AND ( sv.project_id                 = DECODE(:'  1368','0', project_id, :'1368')
AND TO_CHAR (date_in, 'dd-MM-yyyy') = DECODE(:'  ', '0', date_in, :'  ')
AND vehicle_no                      = DECODE(:'  GJ-06-0Z-9202', '0', vehicle_no, :'  GJ-06-0Z-9202') ))

Your application should pass the value to the bind variable correctly. The query you need to use is:

AND sv.project_id = DECODE(:project_id, 0, project_id, :project_id)
AND date_in       = DECODE(:date_in, 0, date_in, :date_in)
AND vehicle_no    = DECODE(:vehicle_no, 0, vehicle_no, :vehicle_no)

The above DECODE is just like an IF-ELSE construct. It means, if the passed values are 0, then use the column value from the table, else, use the value that is passed.

Your application need to assign values to the following bind variables:

:project_id
:date_in
:vehicle_no

For example, in SQL*Plus:

SQL> var num number
SQL> exec :num := 10

PL/SQL procedure successfully completed.

SQL> print num

       NUM
----------
        10

SQL> var text varchar2(10)
SQL> exec :text := 'coder'

PL/SQL procedure successfully completed.

SQL> print text

TEXT
--------------------------------
coder

Also, no need to use TO_CHAR for the DATE. Pass the value as DATE. Else, use TO_DATE on the literal on the right hand side. Leave the date as it is, since you should compare the dates and not strings. TO_CHAR converts the date into string in your desired format. You need TO_CHAR only to display the date, not while comparing or date arithmetic.

UPDATE :

Your application should pass the value to the bind variable properly. After passing the value, your query still has the bind variable symbol :.

  :'  1368'

If you capture the bind variable value, then it should just be:

  '1368'

Also, there is a leading space in the value. Although, it won't throw any error, it would give you incorrect output.


Does REDUNDANCY retention policy guarantee PITR from the earliest backup? From ORACLE-BASE:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE RETENTION POLICY TO REDUNDANCY 5;
…
The recovery window is used to make sure that there are always sufficient backups to recover the database to any point in time with the last 'X' days. Any backups that fall outside this time period can be deleted. The redundancy option simply states that any backups in excess of the latest 'X' are no longer needed.

In other words, setting the RMAN retention policy to a recovery window explicitly guarantees PITR to any time within the recovery window. It isn't clear from this description whether PITR is guaranteed using the alternative redundancy configuration — does RMAN retain all archivelog backups necessary for PITR to any point since the earliest retained backup?

Does RMAN retain all archivelog backups necessary for PITR to any point since the earliest retained backup?

No, at least not if you have incremental backups in the mix:

  …we don’t guarantee a recoverability to a certain point  in time, just, the number of backupsets.

I'm not sure if the same applies if you aren't using incrementals, but there are too many edge cases with using a redundancy-based policy (see this horror story for example). Use a recovery window instead if you possibly can. The article you quote has the same advice:

  The safest option is to use the redundancy window as several backups on a single day could cause the redundancy option to mark valuable files as redundant.


I get ORA-13605 when trying to run SQL Tuning Advisor from SQL Developer When trying to use the SQL Tuning Advisor from SQL Developer I get this error.

Tuning advisor was working some days before.

The solution is very simple. The string of the query is just too long.

I'm trying to optimize a stored procedure using dynamic sql with a parameter to decide if the sql is executed or output.

I just copied the output into a fresh sql developer pane and tried to use the tuning advisor.

The problem is that the generated output has a lot of trailing blanks, I just have to remove them.


Oracle query that use to run in less than a second now takes 8 minutes We have an Oracle query in a report that use to run in less than 1 second. Now takes 8 minutes.

I've tried rebuilding all the indexes it uses. No impact.
The query has not been changed in months.
I personally tested the query on both an older clone of the production db and a new clone. It ran fast on the one and slow on the other. So it's real.
The explain plan on the query is perfect. No reason why it shouldn't be lightning fast.

I'm running out of ideas. Any suggestions?

Justin Cave, I think you are right. It's using something other than the explain plan.

I added the hint /*+ LEADING (table_alias, table_alias) */ referencing the first two tables in the explain plan and boom, it's running like lightning again.


MYSQL - How to make a table with a column that will sum columns from parent tables? I have a table called FOODS. FOODS has two foreign keys: IDsize and IDflavor.

The parent tables FLAVOR and SIZE each have a column price.

The table FLAVOR has columns IDflavor, flavor and price with an example row (1, 'Beef', 10). The table SIZE has columns IDsize, size and price with an example row (3, 'BIG', 15).

Is it possible to make a column in FOODS that will automatically sum the values of the price columns of the corresponding rows in the parent tables FLAVOR and SIZE (using the foreign keys)? So if FOODS has IDsize = 3 and IDflavor = 1, the column should automatically get 25 (10+15).

What you're asking to do is a type of denormalisation: You're adding redundant data to a single table in order to avoid having to query multiple tables to get the same information. This is done to increase performance of SELECT queries and/or simplify such queries.

Since the price columns are in different tables / rows, you unfortunately can't use generated columns.

So you're going to have to consider other options:

You could use a VIEW on top of the FOODS table which summed the prices from the two parent tables. So whenever you wanted to SELECT from the FOODS table (and you wanted to include sum_price), you instead SELECTed from this VIEW. Something like:

CREATE VIEW V_FOODS([the columns of FOODS], sum_price) AS
  SELECT [the columns of FOODS], FLAVOR.price + SIZE.price AS "sum_price"
  FROM FOODS
    INNER JOIN FLAVOR USING(IDflavor)
    INNER JOIN SIZE USING (IDsize);

(As pointed out by RDFozz, note that this isn't actually denormalisation of the data, since the actual data stay in the parent tables.)
You could use TRIGGERs to populate a sum_price column in FOODS. You would need a trigger on UPDATEs to each of FLAVOR and SIZE, and a trigger on INSERTs to FOODS.
You could have a sum_price column in FOODS and give your application the responsibility of keeping it up-to-date.
You could also give up and not denormalise. Just let your application join FOODS, FLAVOR and SIZE whenever you need the sum of prices.


Deleting with NOT EXISTS vs NOT IN gives different results I'm working with an older schema, and don't understand why my delete operation (to clear out orphan records) doesn't work as expected in some cases.  In others both of the queries return identical results :-/

For example, I have 3 models: house, office, and address.  house and office both have a nullable reference to an address.id, so an address record can belong to either of them but the address itself doesn't indicate which.

-- query 1
DELETE FROM address adr
WHERE
  NOT EXISTS(SELECT * FROM house  H WHERE H.address_id = adr.id) AND
  NOT EXISTS(SELECT * FROM office O WHERE O.address_id = adr.id);

-- query 2
DELETE FROM address adr
WHERE
  NOT adr.id IN (select address_id from house) AND
  NOT adr.id IN (select address_id from office);

query 1: deletes 3000 records (correct)

query 2: deletes 0 records

What is the obvious problem with query #2?  When I check the records deleted by #1 they really do not exist in either house or office.

Your address_id columns contain nulls, and thus you got caught by the three-valued logic of SQL.

https://www.postgresql.org/docs/current/static/functions-subquery.html#functions-subquery-in describes the behaviour of IN:

  Note that if the left-hand expression yields null, or if there are no equal right-hand values and at least one right-hand row yields null, the result of the IN construct will be null, not false. This is in accordance with SQL's normal rules for Boolean combinations of null values.

And of course NOT applied to null is null, not true, see https://www.postgresql.org/docs/current/static/functions-logical.html


Limiting the input to a field as some pattern i have a table student having field admission no. i want to restrict its input to the pattern '-/----/--'. If the input does not match this format then it should show an error. I tried doing this using LIKE constraint but the table was accepting all the values matching the pattern or not.....
please help

This is what you're after:

delimiter $
create trigger student_validate before insert on student
for each row
begin
    if new.admission not rlike '^[0-9][/][0-9][0-9][0-9][0-9][/][0-9][0-9]$'
    then
        signal sqlstate '45000' set message_text = 'Bad Format Admission Number';
    end if;
end$
delimiter ;


Replacing master.dbo.sysperfinfo with sys.dm_os_performance_counters I'm working with some old code that pulls performance counters, and part of what I'm doing is making sure we're doing some processes efficiently and effectively.

Right now I have this code that I'm looking at:

SELECT REPLACE(RTRIM(OBJECT_NAME), 'SQLServer:', '') AS 'Object',
       RTRIM(counter_name)   AS 'Counter',
       RTRIM(instance_name)  AS 'Instance',
       cntr_value            AS VALUE
FROM   MASTER.dbo.sysperfinfo
WHERE  OBJECT_NAME <> 'SQLServer:User Settable'

I'm proposing to replace it with:

SELECT REPLACE(RTRIM(OBJECT_NAME), 'SQLServer:', '') AS 'Object',
       RTRIM(counter_name)   AS 'Counter',
       RTRIM(instance_name)  AS 'Instance',
       cntr_value            AS VALUE
FROM   sys.dm_os_performance_counters
WHERE  OBJECT_NAME <> 'SQLServer:User Settable'

Which is faster and more compatible with everything past SQL 2000. There are no environments this will be used in that run SQL 2000 anymore, the earliest version the above code would be run using is SQL 2008. I've checked that the returned values are congruent and it works in the context of the rest of the code.

My question is: What are the differences between dm_os_performance_counters and master.dbo.sysperfinfo? Do I need to pull dm_os_performance_counters from the Master context?

What are the differences between dm_os_performance_counters and master.dbo.sysperfinfo?

sys.dm_os_performance_counters is the replacement for sysperfinfo.  Please see this BOL reference on the mapping of system tables to system views/DMVs.  You should be using sys.dm_os_performance_counters.

  Do I need to pull dm_os_performance_counters from the Master context?

No, DMVs (or system catalog views) can be accessed from the context of any database, provided you have the appropriate permissions to view the contents.  The below example for illustration purposes would be just fine:

use NonMasterDatabase;
go

select *
from sys.dm_os_performance_counters;


SQL Server 2005 Easy way to add all users to a database role I have three application databases: A, B and C. Users of A and B are all users in C; the users from A are in the public role with only SELECT permission, while the users from B are in a role with more permissions. Due to some recent changes, all of the users on C need to be in the other role and not just public. Since this will go out as a database patch and be executed automatically on 150+ databases, I would prefer it to be a simple, blanket script...i.e., a single statement that says "put all users in this role". I know that I can use the script below to create exec statements and loop through the results and execute them, but I'm hoping for something a bit more streamlined. I've searched around all over the place and can't find anything, but there's always hope for some undocumented system stored procedure that someone knows about.

SELECT 'EXECUTE sp_AddRoleMember ''' + roles.name + ''', ''' + users.name + ''''
FROM sys.database_principals users
INNER JOIN sys.database_role_members link ON link.member_principal_id = users.principal_id
INNER JOIN sys.database_principals roles ON roles.principal_id = link.role_principal_id
WHERE roles.name = 'TheNewRole'

Adam Machanic has a blog post on replacing xp_execresultset which in SQL 2000 did what you want: Replacing xp_execresultset in SQL Server 2005


How MongoDB allocate new data block when data block is full I am using MongoDB in my project. And I have some questions about how Mongo storage allocated.

For example, I have 16MB full data block, and I want to add new document into this data block. - How MongoDB allocate new data block ? - Will MongoDB move all data or only new document into a new data block ?

Data files are allocated in advance. The following is slightly simplified, for the sake of shortness.

When a MongoDB database is created, an empty datafile with a size of 64 MB is created. As soon as data is written to this pristine data file, a background process creates a new datafile in order to have one ready of the first datafile is full. This will be repeated every time a document is written to a pristine data file. For each new data file, the file size is doubled until the size to allocate reaches 2GB. From that point on, all new datafiles will have 2GB in size.

As for the data, this is a bit more complicated. Put simply, documents are never moved unless it is really necessary, for example if the document grows in size and exceeds its padding space. So if one of your data files is full and a new document is added, only the new document will be written to the new and formerly pristine data file (triggering the allocation of a new data file), and the old documents stay where they were.


Is not in (a,b,c) faster than where != a, b, c When writing a query which is faster?

SELECT * FROM table where column not in (a,b,c);

or:

SELECT * FROM table where column != a AND column != b AND column != c ;

I don't think there will be a noticeable difference. Query optimization has very little to do with the syntax of your query and a lot to do with the RDBMS query optimizer. The optimizer pulls apart your queries and optimizes it as it sees fit.


Changing identity column from INT to BIGINT I have a table with an identity column that is also a primary key. Currently, it has 50 million rows, with the highest value of the identity column sitting at 148,921,803. The table has a lot of DELETEs and INSERTS performed on it, hence the high value.

We want to change the data type from INT to BIGINT to prepare for the addition of more rows. Note that, there are no references to the PK column.

What is the best way to do this, with minimal downtime? I have two options.

Drop the PK and alter the column; or
The copy-drop-rename method, as described here:

As there is a primary key defined on identity column you wont be able to directly alter this column.

Both the approaches that you have mentioned in your question can be used and downtime depends on how your server is performing and number of rows reside in that table.

  Drop the PK and alter the column; or

First drop the PK

/****** Object: DROP Index [PK_DatabaseLog_DatabaseLogID]******/

ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [PK_TableName_ID]
GO

Alter Column

ALTER TABLE [dbo].[TableName] ALTER COLUMN [dbo.ID] BIGINT

Add Primary key

/****** Object: ADD Index [PK_DatabaseLog_DatabaseLogID]******/
ALTER TABLE [dbo].[TableName] ADD  CONSTRAINT [PK_TableName_ID] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)

This approach usually does not take much time. In my environment it takes mare seconds on big tables which have more than 5 million rows.

  The copy-drop-rename method, as described

You can use this approach as well. However, for this approach you need more downtime than Approach one as you have to sync the tables.


MAX DATE With NULL VALUE Trying to get the max date from my table, i can't retrieve the value when the date is null.
Sample :

the following table :

+----+--------+------+------------------------+
| id | parent |    Date                       |
+----+--------+------+------------------------+
|  1 | Alex   |   2018-10-01 00:00:00.0000000 |
|  1 | TIM    |   2012-11-07 00:00:00.0000000 |
|  3 | Cath   |    NULL                       |
+----+--------+------+------------------------+

To get the max date im using the following:

select id, Parent, Date From Table T1
Where Date = (select Max(Date) from Table T2 where T1.id= T2.id )

Wich works fine for the id = 1
But for the id = 3 it's not working

The result is :

1 | Alex   |   2018-10-01 00:00:00.0000000

Any ideas Why?

Aggregated functions does not consider rows with NULL values.

You could use a WINDOW FUNCTION to enumerate rows and then get first row only:

with ct as
(
    select id, Parent, Date,
           row_number() over (partition by id order by id, date desc) rn
    from   tbl
)
select id, Parent, Date
from   ct
where  rn = 1
order by id;

id | Parent | Date
-: | :----- | :------------------
 1 | Alex   | 01/10/2018 00:00:00
 3 | Cath   | null

db<>fiddle here


MySQL Workbench -- How to efficiently review large database tables? I have a database table with 900,000+ rows, 20 columns, mostly string data.  Using MySQL Workbench, I want to page through the entire contents of the table to see what exactly has been inserted into the table (I am reviewing it because I believe my code somehow accidentally only partially loaded the contents of a CSV file and I want to understand why and where the process ceased loading the entire CSV file).

The problem is that, because of the somewhat large size of the DB table, running a "SELECT * FROM..." query with "Don't Limit" causes my computer to take far too long (over 10 minutes) to produce a result in MySQL Workbench's result grid and basically appears to freeze up.

Is there a way to make the MySQL Workbench result grid load much much faster?  Or should I be using a different tool?

I'm a bit of a noob, so it's possible I'm doing something pretty stupid here.
Thanks in advance!

The first suggestion was to use the "pager less" command - but as pointed out, even at 10 pages a second, it would take 25 hours to get through 900,000 records.

Unfortunately, the "pager" command doesn't work on Windows. It was then suggested to send the output to a file and scroll through that - looking for a disconnect between the .csv input and the final table.

Then, this was proposed

    Or!! You could save your table as a .csv (with a different name
to your original file) and then do a diff on the files - that might
immediately show up problems?

This appears to have resolved the issue for the OP.


MySQL Bug required remedy : [Warning] Invalid (old?) table or database name '#sql-' We have a master & multiple slave replication in our production environment. At first, I found this error in the mysql error log on the master and later on the  slave. As for all the replication, the databases are fine, and slave updates are working well. Noevertheless, this error appears in the mysql error log. I just googled and checked. It's a bug.

Will it have any impact on the databases, as we had multiple db instances which is replicating master & multiple slave architecture?

Error Log :

111116 10:47:57 [Warning] Invalid (old?) table or database name '#sql-3d6f_122a#P#p7'
111116 10:47:57 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p8'
111116 10:47:57 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p9'
111116 10:47:57 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p10'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p1'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p2'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p3'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p4'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p5'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p6'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p7'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p8'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p9'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p10'
111116 10:48:46 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p1'
111116 10:48:46 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p2'
111116 10:48:46 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p3'
111116 10:48:46 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p4'
.....................................................................................;

Our error is in warning state. But as i googled and i can see the tag [error] state also:

[ERROR] Invalid (old?) table or database name 't1#p#p4'
[ERROR] Invalid (old?) table or database name 't1#p#p2'
[ERROR] Invalid (old?) table or database name 't1#p#p2'

I had a multiple Db instances around 70 & thousands of tables. What will be the query to trace out the particular table among multiple instances ?

Did any one faced this bug & solved the issue. I guess soon all my slaves will catch up this problem. What will be the impact in the production environment...?

Will enabling the innodb_lock_monitor cause many of these messages to be printed out!
create table innodb_lock_monitor(a int)engine=innodb; If so we handnt enabled. I had also seen in the innodb engine status. no sign of identity related to error/ warning.
"Do you start MySQL server with database created in previous version?" but i hadnt done it. It is running in the version 5.1.39
No plugins had been upgraded. So what could be root cause and remedy for this error/ bug..?

There is the presence of a bug in this case for two reasons:

111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p1'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p2'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p3'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p4'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p5'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p6'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p7'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p8'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p9'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p10'

REASON #1 : Any table starting with #sql is a temp table. If these tables are still present in any database folder and the datetime stamp, simply delete them.

REASON #2 : Look carefully at the suffix of each table. #P#p? resembles a partition tag. This would indicate an attempt to create a temp table using partitiions. That's sounds insane. There was bug report on this back in Feb 16, 2006 for MySQL 5.1.7-beta (closed Mar 15, 2006). The bug report is based on trying to do this manually. Is mysql attempting to do this internally ?

IMHO I would upgrade mysql away from MySQL 5.1 up to MySQL 5.5


Setting up Availability group for report server databases I am reading msdn article on using SSRS with AG and have few doubts on setting up the same:

Server A -  Will host Report Server services with scale out deployment
Server N1 - Node 1 with ReportServer and ReportServerTempdb database serving as primary replica
Server N2 - Node 2 is secondary replica of the ReportServer and ReportServerTempdb

Above nodes will be part of multi-subnet cluster with N1 in DC1 and N2 in DC2.

I am thinking to create the listener first with 2 IPs for those multi-subnet so that we can use same while installing reporting services and point the listener name in instance and it's report server database to it so that it is aware of primary replica even after fail-over.

Is my above understanding correct? Is that how it's suppose to work?

Secondly, document says automatic fail-over is not supported even when I use sync mode between N1 and N2? Is that true? And why would that be the case only with SSRS? Also if it is, can we automate the fail-over or does it have to be manual every-time since I am thinking data source application string can use the option MultiSubnetFailover=true ?

Appreciate your inputs.

The reason the documentation states that automatic failover is not supported is that there are post-failover tasks that must be carried out to ensure the Report Server is completely online. See this section for information on exactly what tasks need to be completed. Essentially it boils down to needing to stop the SQL Agent on the old primary and restart the Report Server service.

  I am thinking to create the listener first with 2ips for those multi
  subnet so that we can use same while installing reporting services and
  point the listener name in instance and it's report server database to
  it so that it is aware of primary replica even after failover. Is my
  above understanding correct? Is that how it's suppose to work?

Yes, build your AG and configure the listener first, then when you'r configuring the report server you can point it at the listener.

  Secondly, document says automatic failover is not supported even when
  I use sync mode between N1 and N2 ? Is that true? And why would that
  be the case only with SSRs? Also if it is , can we automate the
  faiover or does it have to be manual everytime since I am thinking
  data source application string can use the option multi subnet
  failover = true ?

There seems to be some crossover here where you're talking about report data sources and Report Server databases. RS databases are the databases used by report server to manage and maintain the report server. They're typically named ReportServer and ReportServerTempDB. These databases do not support automatic failover because there are post-failover tasks you must complete to bring the service back to a 100% normal state.

These tasks can be automated through scripts easily enough, but that is is what the documentation means by not supported. For your report data sources, these only need AG specific parameters in the connection string (such as MultiSubnetFailover or ApplicationIntent) if the source databases are in an AG as well. If they are then adding those keys to your connection strings will ensure the reports continue working post-failover. They should handle failover automatically.

Something to note is this line from the documentation:

  The use of MultiSubnetFailover, with the report server databases, is not supported.

What this means for you, given you have a multi-subnet cluster, is that you should set the RegisterAllProvidersIP setting to 0. This setting is 1 by default and means that each IP assigned to the listener is registered in DNS. If MultiSubnetFailover is enabled, the client will try to connect to each IP in parallel and return the connection from the first IP that responds. As this setting is not supported by the RS service for connections to the RS databases, after a failover you will likely encounter timeouts trying to connect.

The solution is to set RegisterAllProvidersIP to 0 and set a low TTL for that host record. This ensures that only the active IP is registered with DNS for the listener, and after failover the new IP is registered with DNS. This means new connections to the listener use the correct IP and you avoid timeouts.


Delete all types created by a user In Oracle 10g, how do I delete all the types owned by a specific user? This is the view that gives me the types I want to drop:SELECT type_name FROM user_types;

Use a query to generate a drop type script [in SQL Plus]:

set lines 132
set head off
set pages 0

spool drop_types.sql

select 'drop type '||type_name||';'
from user_types;

spool off

Then execute the script in SQL Plus:

@drop_types.sql


transactional replication, failing with communication link failure half way through bcp I am trying to replicate to a 4th subscriber on a remote site. 3 subscribers work fine locally. the other difference is this is a 2012 subscriber from a 2008 r2 publisher and remote distributor.

When delivering the bcp commands it fails with the following error.

The process could not bulk copy into table '"dbo"."FlightSchedule_tbl"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL0)

I have tried raising the historyverbose level to 2, but this is the only error i get from the agent history.. I have attempted to recreate the snapshot, but this still fails at the same point...

nothing unusual about the table, and its 5 million rows, so not massive amounts of data...

Column_name Type    Computed    Length
FlightId    int no  4
OrigAirport char    no  3
DestAirport char    no  3
DeptDate    datetime    no  8
ArriveDate  datetime    no  8
Airline char    no  10
SupplierId  int no  4
UpdateRef   int no  4
HarvestUpdateKey    int no  4
ActiveFlag  bit no  1

Can anyone help me diagnose this to get a useful error or suggest what the problem might be??  the connection is very slow to this datacentre which is a issue we are attempt to resolve... could this simply be a timeout ???

UPDATE - I configured a seperated publication with just this table in and set the verbose logging to 2. I then get

Error messages:
The process could not bulk copy into table '"dbo"."FlightSchedule_tbl"'. (Source:   MSSQL_REPL, Error number: MSSQL_REPL0)

An existing connection was forcibly closed by the remote host.
Unspecified error
Failed to send batch after max errors
TCP Provider: An existing connection was forcibly closed by the remote host. (Source: MSSQLServer, Error number: 10054)

Communication link failure (Source: MSSQLServer, Error number: 10054)

so looks like the network between datacentres...  strange that it would always die at the same point, would there be a reason for this ?

The error log on the target server
  Error: 4014, Severity: 20, State: 13
  A fatal error occurred while reading the input stream from the network. The session will be terminated (input error: 64, output error: 0).

If the link is bad, you must overrride -QueryTimeOut and -KeepAliveMessageInterval parameters. Check the SQL Server Agent job, identify the replication Job, then right-click Properties, then Steps, Edit and at the end of Commend just add -LoginTimeOut 120 -QueryTimeOut 7200 -KeepAliveMessageInterval 600


Get the first and last names of all employee who entered the project at the same time as at least one other employee SELECT
    EmpFname
    ,EmpLname
FROM
    Employee_m
WHERE
    (
    SELECT
        COUNT (w1.EnterDate)
    FROM
        WorksOn_m w1,WorksOn_m w2
    WHERE
        w1.EnterDate = w2.EnterDate
    ) >= 1

I tried this but the output gives all the names as the same row is compared. Any way around this?

my data:

You are missing conditions that say that the employee is working on the project in w1, and that the project in w2 is the same as the project in w1 but that the employee in w2 is not the same as the employee in w1.

The reason you get all rows is because the where condition always evaluates to true, for all employees. That is because the sub query simply asks for at least one row that has the same date in both w1 and w2. Consider that WorksOn_m has only one row:

  WorksOn_m
  -------------- EnterDate 12/03/2014

Is there a record in both w1 and w2 with the date 12/03/2014? Yes, because they're the same table. Are you applying any other conditions in the WHERE clause? No. So you'll get all employees, because you're basically saying "Give me all employees WHERE 1=1".


Is there a faster way to get UNION ALL behavior in Postgres? I have a table (t0), in my Postgres DB, with data that looks something like this:

t1_id  t2_id
1      1
2      1
2      1
4      null
4      null
5      null

And I have a query to return my desired results of:

t1_id  t2_id
1      1
4      null
5      null

My query looks something like this:

(
  SELECT DISTINCT ON (t2_id) t1_id, t2_id
  FROM t0
  WHERE t2_id IS NOT NULL
)
UNION ALL
(
  SELECT DISTINCT ON (t1_id) t1_id, t2_id
  FROM t0
  WHERE t2_id IS NULL
)

Is there a faster way to do an operation like this? It's not too bad, but I'm doing it in several places (with joins) and all these repeated queries seems to slow stuff down a bit. Seems like there must be a better way.

Here's the query in fiddle form: http://sqlfiddle.com/#!15/d41d8/3603

For the simple case, I can only think of minor improvements to the query:

(
  SELECT DISTINCT ON (t2_id)
         t1_id, t2_id
  FROM   t0
  WHERE  t2_id IS NOT NULL
  ORDER  BY t2_id, t1_id  -- to get consistent results
)
UNION ALL
(
  SELECT DISTINCT ON (t1_id)
         t1_id, NULL      -- cheaper
  FROM   t0
  WHERE  t2_id IS NULL
  -- if you retrieve more columns, add ORDER BY, too
)

As ypercube mentioned You need to add ORDER BY with an unambiguous list of expressions to get deterministic results.
You can use the constant NULL instead of t2_id in the second leg of the query. Also relevant for below index support.
The key to performance is indexing. Try two partial indexes matching the two parts of the query:

CREATE INDEX t0_part1_idx ON t0 (t2_id, t1_id) WHERE t2_id IS NOT NULL;
CREATE INDEX t0_part2_idx ON t0 (t1_id)        WHERE t2_id IS NULL;

You may or may not want to include additional columns to allow index-only scans.

Depending on table size and data distribution, there may be faster alternatives:

Select first row in each GROUP BY group? (last chapter)
Optimize GROUP BY query to retrieve latest record per user

Single SELECT

If you want to condense it into a single SELECT:

SELECT DISTINCT ON (coalesce(t2_id, t1_id), t2_id)
       t1_id, t2_id
FROM   t0
ORDER  BY coalesce(t2_id, t1_id), t2_id, t1_id;

Equivalent, except for sort order. If you want this to be fast, try a functional index:

CREATE INDEX t0_func_idx ON t0 (coalesce(t2_id, t1_id), t2_id, t1_id);


remove leading and trailing quotes I have a field that may start and end with quotes. I only want to remove the quotes if the string starts and ends with quotes. If quotes exists inside the string then I wish to retain them.

I have used REVERSE and stuff which works if the field does start and end with quotes but if it does not it just returns null.

How can I ensure I still get the field result if it did not start with quotes?

What I have:

REVERSE(STUFF(REVERSE(STUFF(fieldname, CHARINDEX('"', fieldname), LEN('"'), '')), CHARINDEX('"', REVERSE(STUFF(fieldname, CHARINDEX('"', fieldname), LEN('"'), '')), 1), 1, ''))

You could try something like this

SELECT CASE WHEN RIGHT(fieldname,1) = '"' AND LEFT(fieldname,1) = '"' THEN REVERSE(STUFF(REVERSE(STUFF(fieldname, 1, 1, '')), 1, 1, ''))
       WHEN RIGHT(fieldname,1) = '"' THEN REVERSE(STUFF(REVERSE(fieldname), 1, 1, ''))
       WHEN LEFT(fieldname,1) = '"' THEN STUFF(fieldname, 1, 1, '')
       ELSE fieldname END as fieldname
FROM #temp;

DB<>Fiddle


Azure Database Aliases I saw that you could use the new portal to create an alias for your database instance.  I have already created an internal DNS to point to our Azure SQL instance but of course connecting to it wants username@randomname .  How can I make an alias for the "random server name" so it is more usable for my clients?

thanks

Your question appears to be a duplicate of this question, but that doesn't have a formal answer which means it technically can't be a duplicate :) so I'll synthesize an answer here.  As you can see in this response on StackOverflow the latest version of the Portal should allow you to set the server name. I copied the picture over from that answer for convenience.

If that doesn't work you can go to this blog post as a fall back.  The meat of that blog post is here:

To use a meaningful alias, what you need to do is:

Using SQL Server Configuration Manager, in the SQL Native Client 11.0 Configuration\Aliases node, create an alias. Pick a meaningful name (ie; HRServer) for the Alias Name, leave the port at the default (ie: 1433), leave the protocol at the default (ie: TCP/IP) and provide your real server name (ie yy2195dk1k.database.windows.net) in the Server field.
Create the same alias in the SQL Native Client 11.0 Configuration (32bit)\Aliases node.
When connecting to the server in SSMS, enter your new alias (ie: HRServer) for the Server name, SQL Server Authentication for the Authentication method, and for the login, specify your login name followed by an @ symbol, followed by the first segment of the real server name (ie: mylogin@yy2195dk1k), then enter your password.
If the user isn't an admin user that has the ability to connect to the master database, you'll also need to manually set the database name using the Options button. (Note that you won't be able to browse for database names).

Later in the blog's comments they state:

After all, an alias is just a registry entry and so it can be pushed out by group policy like any other registry entry.

So you should be able to push out a group policy to any other clients which should allow them to use the alias if the Azure portal method doesn't work.
Please post an answer or comment on my answer if you find this doesn't work.  My answer is based on research not field experience.


Sample YAML Configuration Files for MongoDB? The MongoDB configuration options documentation lists all the available options that can be specified, but does anyone have a set of fully formed example YAML formatted config files for MongoDB instances in various roles?

A set of examples for the common roles would be a very useful starting point for those starting from scratch, or looking to test with the latest configuration file format.

Here are several examples of YAML configs for Linux (Windows paths and options are a little different), essentially explicitly setting some defaults and commonly used settings.

First, a standalone mongod with the default port, path, journal settings - this would be the type of configuration used for local testing, with a few extras so show the general style:

storage:
    dbPath: "/data/db"
    directoryPerDB: true
    journal:
        enabled: true
systemLog:
    destination: file
    path: "/data/db/mongodb.log"
    logAppend: true
    timeStampFormat: iso8601-utc
processManagement:
    fork: true
net:
    bindIp: 127.0.0.1
    port: 27017
    wireObjectCheck : false
    unixDomainSocket:
        enabled : true

Some notes on this config:

You generally don't want to have the object check off (wireObjectCheck: false) in production, but for a bulk load of data for testing purposes, it will speed things up a little and is a minimal risk in such an environment
This would not work for replication unless all member of the replica set were on the loopback IP address (as this is the only binding specified), so beware

Now, let's look at a sample config file for a typical production replica set member with authentication enabled and running as part of a sharded cluster:

storage:
    dbPath: "/data/db"
    directoryPerDB: true
    journal:
        enabled: true
systemLog:
    destination: file
    path: "/var/log/mongodb.log"
    logAppend: true
    timeStampFormat: iso8601-utc
replication:
    oplogSizeMB: 10240
    replSetName: "rs1"
processManagement:
    fork: true
net:
    bindIp: 192.0.2.1
    port: 27018
security:
    keyFile: "/data/key/rs1.key"
    authorization: "enabled"
sharding:
    clusterRole: "shardsvr"

Some notes on this configuration:

Again there are explicit declarations of defaults and implied settings (port is implied by clusterRole for example), generally this is recommended to avoid confusion
The IP binding is now the external IP address only, so communication on the loopback IP will now fail, but replication can work to remote hosts
The oplog defaults to 5% of free space, so it is common on large volumes to be more conservative and explicitly set the size allocated

Next up, a sample mongos config:

sharding:
    configDB: "config1.example.net:27019,config2.example.net:27019,config3.example.net:27019"
    autoSplit: true
systemLog:
    destination: file
    path: "/var/log/mongos.log"
processManagement:
    fork: true
net:
    port: 27017
    bindIp: 192.0.2.2
    maxIncomingConnections: 5000
security:
    keyFile: "/data/key/mongos.key"
    authorization: "enabled"

The only required changes here are removals that don't apply to the mongos (since it does not store data) and the addition of the configDB string, which must be identical on all mongos processes.  I added the maximum connections setting as an example, it's not required but can often be a good idea for larger clusters.

Rounding out the sharded cluster we have a sample config server, which is really a subset of the replica set member with some minor changes:

storage:
    dbPath: "/data/db"
    journal:
        enabled: true
systemLog:
    destination: file
    path: "/var/log/mongodb.log"
    logAppend: true
    timeStampFormat: iso8601-utc
processManagement:
    fork: true
net:
    bindIp: 192.0.2.3
    port: 27019
security:
    keyFile: "/data/key/config.key"
    authorization: "enabled"
sharding:
    clusterRole: "configsvr"

Finally, MongoDB 3.0 (not yet released at the time of writing this) will introduce several new options, especially with the introduction of the new storage engines.  Therefore, here is an example of how to configure the same replica set member, but this time with the WiredTiger storage engine and (the default) snappy compression method (note: altered from original because of SERVER-16266, and added sample engineConfig):

storage:
    dbPath: "/data/db"
    engine: "wiredTiger"
    wiredTiger:
        engineConfig:
            cacheSizeGB: 8
        collectionConfig:
            blockCompressor: snappy
systemLog:
    destination: file
    path: "/var/log/mongodb.log"
    logAppend: true
    timeStampFormat: iso8601-utc
replication:
    oplogSizeMB: 10240
    replSetName: "rs1"
processManagement:
    fork: true
net:
    bindIp: "192.0.2.1,127.0.0.1"
    port: 27018
security:
    keyFile: "/data/key/rs1.key"
    authorization: "enabled"
sharding:
    clusterRole: "shardsvr"

As a final bonus addition, I showed how to bind multiple IP addresses using a list, in this case an external IP and the loopback IP.


What is the StatementParameterizationType plan attribute? I have noticed execution plans sometimes include a StatementParameterizationType attribute.

What is this, what does it mean, and when does it appear?

The StatementParameterizationType attribute indicates the type of parameterization applied to the statement.

The values are documented in sys.query_store_query:

0 = None
1 = User
2 = Simple
3 = Forced

It appears on the root node of post-execution ("actual") plans only.

The Query Store must also be enabled to see this attribute (e.g. in SSMS).

AdventureWorks demo:

USE AdventureWorks2017;
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
ALTER DATABASE CURRENT SET PARAMETERIZATION SIMPLE;
GO
-- None (0)
SELECT COUNT_BIG(*)
FROM Production.Product AS P
WHERE P.Color = N'Red';
GO
-- User (1)
EXECUTE sys.sp_executesql
    @stmt = N'
        SELECT COUNT_BIG(*)
        FROM Production.Product AS P
        WHERE P.Color = @Color;',
    @params = N'@Color nvarchar(15)',
    @Color = N'Red';
GO
-- Simple (2)
SELECT A.AddressID
FROM Person.[Address] AS A
WHERE A.AddressLine1 = N'1 Smiling Tree Court';
GO
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
ALTER DATABASE CURRENT SET PARAMETERIZATION FORCED;
GO
-- Forced (3)
SELECT COUNT_BIG(*)
FROM Production.Product AS P
WHERE P.Color = N'Red';


Does PostgreSQL index null values? If I have a table like so:

Food
-----------------
name | price | x

Let's say I create an index on name & price, but the database has a bunch of null values for both name & price in some of the rows. Does PostgreSQL automatically skip indexing those rows? I heard this behavior might have changed in later releases.

Since at least PostgreSQL 8.3, PostgreSQL indexes NULL using a bitmap on the index. That means "yes" it's indexed. Except it takes a bit, rather than a byte[s]. There is no speed advantage insofar as btree indexes are concerned with using a non-NULL value. It will work just as you'd expect.

Dragons here: for more information see this,

http://patshaughnessy.net/2014/11/11/discovering-the-computer-science-behind-postgres-indexes


Inline SQL To Set Fully Qualified Name Why does my variable @FQN not set accordingly?  Is this a problem with my syntax?  A Problem with using a cursor?  What should I do so that my variable @FQN sets appropriately?  This is what my print statements show, my variable @table outputs properly, but my variable @FQN never changes?

  OneTwoThree
  Select Top 1 * FROM servername.database.dbo.newdatabase
  FourFiveSix
  Select Top 1 * FROM servername.database.dbo.newdatabase
  FourOmegaSix
  Select Top 1 * FROM servername.database.dbo.newdatabase

And here is full syntax:

Declare @table varchar(100), @sql varchar(4000), @FQN varchar(MAX)
Create Table #tablename (tablename varchar(1000))
Insert Into #tablename Values
('OneTwoThree'),
('FourFiveSix'),
('FourOmegaSix')

Declare c1 Cursor For

Select tablename
FROM #tablename

Open c1

Fetch Next From c1 Into @table

While @@FETCH_STATUS = 0
Begin
  if @table = 'OneTwoThree'
    BEGIN
        SET @FQN = 'servername.database.dbo.onetwothree'
    END
  if @table = 'FourFiveSix'
    BEGIN
        SET @FQN = 'servername.database.dbo.fourfivesix'
    END
  if @table != 'OneTwoThree' or @table != 'fourfivesix'
    BEGIN
        SET @FQN = 'servername.database.dbo.newdatabase'
    END
  Print @table

  Set @sql = 'Select Top 1 * '
             +'FROM '+@FQN+' '

  Print @sql

  Fetch Next From c1 Into @table

End

Close c1
Deallocate c1

Drop Table #tablename

Simplify:

SET @FQN = 'servername.database.dbo.newdatabase';

IF @table = 'OneTwoThree'
BEGIN
    SET @FQN = 'servername.database.dbo.onetwothree';
END

IF @table = 'FourFiveSix'
BEGIN
    SET @FQN = 'servername.database.dbo.fourfivesix';
END

Or:

SET @FQN = 'servername.database.dbo.' + CASE @table
  WHEN 'OneTwoThree' THEN 'onetwothree'
  WHEN 'FourFiveSix' THEN 'fourfixesix'
  ELSE 'newdatabase'
END;

Or even:

SET @FQN = 'servername.database.dbo.' + CASE
  WHEN @table IN ('OneTwoThree', 'FourFiveSix')
  THEN LOWER(@table) ELSE 'newdatabase'
END;

Also, you can do this whole thing without the cursor:

DECLARE @sql nvarchar(4000) = N'', -- always use nvarchar for dynamic SQL
        @FQN nvarchar(4000) = N'servername.database.dbo.';

CREATE TABLE #tablename (tablename varchar(1000));

INSERT #tablename(tablename) VALUES('OneTwoThree'),('FourFiveSix'),('FourOmegaSix');

SELECT @sql += N'
  SELECT TOP 1 * FROM ' + @FQN + CASE
  WHEN tablename IN ('OneTwoThree', 'FourFiveSix')
  THEN LOWER(tablename) ELSE N'newdatabase' END + N'; --' + tablename
FROM #tablename;

PRINT @sql;

DROP TABLE #tablename;

Output:

  SELECT TOP 1 * FROM servername.database.dbo.onetwothree; --OneTwoThree
  SELECT TOP 1 * FROM servername.database.dbo.fourfivesix; --FourFiveSix
  SELECT TOP 1 * FROM servername.database.dbo.newdatabase; --FourOmegaSix

(There's still a hidden loop there, but it's far less code to look at, maintain, etc.)


Logical trees: difference between Before Project Normalization and Join Collapsed The Before Project Normalization tree follows the Join Collapsed tree.
Using a sample set of ~250 queries - and Traceflags 8605, 8606, 8607, 3604 - I have not detected any difference between these two trees, in any of the sample queries.
Is it possible to construct a query where there is a difference between those two trees?
(This is research for a blog post - I will credit anyone who can help.)

Is it possible to construct a query where there is a difference between those two trees?

No.
The sequence of events is:

The Join-collapsed Tree is printed immediately after Heuristic Join Reordering.
Memory usage before project normalization is printed (if enabled).
Normalize Projects is called.

If project normalization has been disabled, it exits.
Otherwise, a call to Derive Group Properties is made.
Nothing has been added or changed, so properties are not rederived.
Normalize Projects prints the Tree Before Project Normalization.

The extra call to Derive Group Properties has almost zero cost if no work needs to be done. One can only speculate why the tree is debug printed on entry to Normalize Projects.


How do I reclaim reserved space in the sysjobstepslogs table I have a 36GB msdb database in my SQL Server 2008 R2 database server.  From what I have read this is past the point of being gigantic.  When I look at the tables in the msdb database the sysjobstepslogs table is using 97% of the space in the database.  I've done enough research to find out that the cause was several jobs had steps that had the Log to table option set + long running transactions + snapshot isolation on the msdb database.  This option has been disabled on those jobs.

I have tried to reduce its size by doing a truncate table, reindex, reorganize, check table, shrink file, shrink database (pretty much in that order) but with 0 rows, the sysjobstepslogs table still indicates that is has 35+GB reserved and used.

It has been suggested that I disable then reenable snapshot isolation to try to free up the space but according to this MSDN forum discussion
you cannot disable snapshot isolation on the msdb database.

I have contemplated dropping and recreating the table but how do I recreate the table as a system table and even if I did, is such a thing wise?

Any ideas how to reclaim this space?

You could try just rebuilding the table:

ALTER TABLE dbo.sysjobstepslogs REBUILD;

If you're on Enterprise (or Evaluation or Developer) you can do:

ALTER TABLE dbo.sysjobstepslogs REBUILD WITH (ONLINE = ON);

The table only has a single clustered index (PK_SomeAwfulGUID-basedName) and this rebuild operation should deallocate all of the pages that might still be associated with the table.


mysqldbcompare - ERROR: The list of objects differs among database I'm running:

# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.5 (Santiago)
# rpm -q mysql-server
mysql-server-5.1.73-3.el6_5.x86_64
#

and

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.1.73-log |
+------------+
1 row in set (0.00 sec)

mysql>

I'm trying to run mysqldbcompare against two mysql server and running into following error:

$ mysqldbcompare -vvv --server1=XXX:XXX@XXX:3306 --server2=XXX:XXX@XXX:3306 pressflow6:pressflow6
# server1 on XXX: ... connected.
# server2 on XXX: ... connected.
# Checking databases pressflow6 on server1 and pressflow6 on server2
#
ERROR: The list of objects differs among database pressflow6 and pressflow6.
$

I just ran mysqldump to export it on server1 and import it to server2 and I'm able to run it against other database without any issues.

Any ideas why I'm unable to run mysqldbcompare successfully?

Check that you have enough permission on both databases to see and select your tables.

You could do a poor's man mysqldbcompare to check that:

mysqldump -d -h server1 -u user1 -ppassword1 pressflow6 > objects1.sql
mysqldump -d -h server2 -u user2 -ppassword2 pressflow6 > objects2.sql
diff objects1.sql objects2.sql


MongoDB: Manual failover and failback between two datacenters? This is about MongoDB and datacenter failover and failback. We're thinking about using two datacenters and this MongoDB replica set:

DC1-M1  prio 1, votes 1  (datacenter 1, member 1)
DC1-M2  prio 1, votes 1
DC2-M3  prio 0, votes 1
DC2-M4  prio 0, votes 0   <-- please not, 0 votes. DC1 has a majority of voters

We think that we'd like to do datacenter failover manually (because other parts
of the failover process happens manually). We'd do failover by running
rs.reconfigure() in DC2 and including only members DC2-M3 and DC2-M4, and
setting their priority and votes to 1. Then we'll get a replica set in DC2
with two members only (M3 and M4) and one of them will become the primary.

Does this make sense, or can you think of any problems? I think we'd need to
specify force = true when running rs.reconfigure(), since we'd be connected to a secondary.  We'd basically follow these instructions:
http://docs.mongodb.org/manual/tutorial/reconfigure-replica-set-with-unavailable-members/#reconfigure-by-forcing-the-reconfiguration
We'd do datacenter failback to DC1 by running rs.reconfigure() again, and
adding back the members in DC1.

Do you see any problems with this?

What happes if DC1 has accepted a few writes after we did manual failover to
DC2? Then we have a split brain problem, and then what happens when we run
rs.reconfigure() and add back the members in DC1? MongoDB's automatic
rollback system will kick in?  (See
http://docs.mongodb.org/manual/core/replica-set-rollbacks/ ) But will it keep
the changes made to DC1 or those made to DC2?  It'd be important that the
changes in DC2 were kept; how can we ensure that those changes aren't lost?

Background:

We have 2 datacenters, and want DC1 to survive an outage of DC2. So we want to
keep the majority of the voters in DC1. At the same time, we'd like to be able
to failover to DC2, and fail back to DC1. We'd prefer not to involve any third
datacenter (e.g. a third datacenter with an arbiter), because our team
thinks it's bad to have dependencies on an additional datacenter. And we
currently think that we want to do failover manually anyway because other
parts of the datacenter failover process happen manually.

Best regards,
KajMagnus

I do not understand why the failover to DC2 has to be done manually (even if other parts have to be done manually: one thing less on your to do list in case of a major failure is always a good thing!).

In general, my feeling is that there are conceptual flaws in your setup.

Here is how I would do it and why.

I would not have manual failover. It is better to have slow access than none. What will happen in the current configuration is that if the primary fails, there will be a tie and therefor the whole set would enter secondary state, effectively turning the cluster into read-only mode. So even when everything else is fine in DC1 and there is no need for failing over to DC2, a failing primary will be a show stopper. With setup, you are artificially creating a single point of failure, effectively gainst the whole idea of a cluster, let alone a multi DC setup. Sounds like a Very Bad Idea™ to me. Automatic failover, even to DC2 sounds like a better idea. Slower reads and (depending on your write concern) slower writes still are better than read only mode.
I would have a third datacenter with only one instance: an arbiter. An arbiter can easily be run on a micro-machine as it will only be called in case of an election and an election is a cheap task in terms of RAM and computation power. The arbiter will help the set to always have a majority: If one DC gets disconnected for whatever reason, the other DC and the arbiter will form a majority. So if one DC goes down, you have only to worry about your other parts of your application. You don't have to wait
I am pretty sure that automatic failover for the other parts of your application can be achieved with some time and effort. Especially if you store all data in mongoDB and you have some sort of session replication available, it should be quite easy. Whether implementing automatic failover is worth the effort is pretty easy to calculate: Get your average downtime, find out how big the losses are created by this downtime in terms of money and customer satisfaction (if applicable). If the costs of implementing automatic failover is below or equal, go for automatic failover. I can help you with that if needed.


How to update values in production table with foreign keys? I have a prod MySQL table on 5.7.32 where I want to update the values. It coutains only 4K rows. However there is another table with a foreign key to the table I want to update and this one holds millions of rows.
table a
ID | DIMENSION | VALUE

table b
ID | A_ID | ..
Key: A_ID -> a.ID cascade

Now how do I update table a? I fear to cascade to table b in case I do a truncate before import. The new table values are on a development DBMS where initially thought to do a single export and then import with prior truncate.
The updated table holds additional rows, has removed rows and edited rows.
What would be the recommended way to update the data?
Could I simply use the following settings during export with PhpMyAdmin 4.5.7 and then import on prod?

If  you don't change the a ID simply do a
SET FOREIGN_KEY_CHECKS=0;
TRUNCATE TABLE [database_name.]table_name;
INSERT INTO new Values
SET FOREIGN_KEY_CHECKS=1;

If you want to change the a ID
First do your updates, so that you have all changes and then disable the foreing keys