Logging remote transactions in mariadb I know how to log all queries to a file in mariadb, is there a way for me to log only remote transactions?

I've got a situation where I have a server with black-box software that does a lot of work from a central server and a lot of work on it's own server (side-by-side with mariadb). The central server seems to be doing a crazy load of unnecessary labor and I want to be able to separate what it's doing from what the other processes are doing.

So, how can I log only queries done by users logged in from remote locations?

First of all- you should create different database users for different access roles of applications- even they use the same data. That will make easier to monitor each individual app. Even if you have to share the user name, you can create different accounts with different source ips (or ip ranges) to differentiate user activity.

You have several options for query auditing that can work better, faster and more fine-grained than the general query log, for example:

Install an audit plugin. This is basically the ability to filter the general log in an efficient way so you write the results you want. MySQL/MariaDB provide a general api, and there are several plugins that allow you some flexibility on that filtering, normally using for security auditing:

https://mariadb.com/kb/en/mariadb/about-the-mariadb-audit-plugin/#excluding-or-including-users
https://github.com/mcafee/mysql-audit

Enable and use the integrated profiling utility performance_schema. While this is usually used to monitor performance and problems, it is so flexible that you can filter by any way imaginable. By default it will only get you user usage summaries and digests, but you can enable extended logging for a single user/database/etc. https://dev.mysql.com/doc/refman/5.6/en/performance-schema-pre-filtering.html
Use the slow query log. You can change the slow_query log limit to log only queries that take more that certain amount of seconds, or to log only certain percent of all queries. It contains the user name. This has a bit more control over what exactly you want to log, and then you can use tools like pt-query-digest to summarize what was going on.
Use tcpdump. In extreme cases, you can sniff your own traffic (if it is not encrypted), to identify queries happening between 2 servers. As any network connection, you can set where you sniff, and what kind of traffic/port you use, so that can quickly tell you what is going on: https://www.percona.com/blog/2008/11/07/poor-mans-query-logging/ There are some utilities to convert traffic to a more usable format, including the above pt-query-digest tool.


"sqlplus as sysdba" startup error My setup:

Oracle 11g
32G RAM
8 CUP threads

Three oracle instances have been running. But running this:

sqlplus "/as sysdba"

gives me a prompt:

  Connected to an idle instance.

When I issue the startup  command, I get this error:

  ORA-04031 unable to allocate 2048 bytes shared memory  ("shared pool","unknow object","sga heap(1,0)","procs:ksunfy");

But three Oracle instances are working now.

Maybe I changed some config incorrectly. What can I do to fix this?

Launch this command on OS prompt and will obtain:

[oracle@datab ~]$ oerr ORA 04031
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause:  More shared memory is needed than was allocated in the shared
//          pool.
// *Action: If the shared pool is out of memory, either use the
//          DBMS_SHARED_POOL package to pin large packages,
//          reduce your use of shared memory, or increase the amount of
//          available shared memory by increasing the value of the
//          initialization parameters SHARED_POOL_RESERVED_SIZE and
//          SHARED_POOL_SIZE.
//          If the large pool is out of memory, increase the initialization
//          parameter LARGE_POOL_SIZE.

You can try to increase values as help says, or go and take a look here, for example.


MySQL InnoDB Sorting Issue I was surprised to see MySQL's InnoDB showing an interesting behavior, which I cannot fully explain. According to the official MySQL InnoDB Documentation:

  "All indexes other than the clustered index are known as secondary
  indexes. In InnoDB, each record in a secondary index contains the
  primary key columns for the row, as well as the columns specified for
  the secondary index. InnoDB uses this primary key value to search for
  the row in the clustered index"

So to my understanding, any single-column index is actually a compound index over the selected column and the primary key (please correct me if this is not the case). Thus if I select from a table filtering by an indexed column and sorting by the primary key it should be an effective operation not requiring a filesort.

In practice, however, this is not the case as I illustrate below:

mysql> describe object_settings;
+-------------------+--------------+------+-----+---------+----------------+
| Field             | Type         | Null | Key | Default | Extra          |
+-------------------+--------------+------+-----+---------+----------------+
| object_setting_id | int(11)      | NO   | PRI | NULL    | auto_increment |
| object_id         | int(11)      | NO   | MUL | NULL    |                |
| name              | varchar(50)  | YES  | MUL | NULL    |                |
| value             | varchar(255) | YES  |     | NULL    |                |
+-------------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> select * from object_settings;
+-------------------+-----------+------+-------+
| object_setting_id | object_id | name | value |
+-------------------+-----------+------+-------+
|                 1 |        10 | foo  | bar   |
|                 2 |        10 | bar  | foo   |
|                 3 |        11 | bar  | foo   |
|                 4 |        12 | bar  | foo   |
|                 5 |        13 | bar  | foo   |
+-------------------+-----------+------+-------+
5 rows in set (0.00 sec)

mysql> explain select * from object_settings where object_id = 10 order by object_setting_id DESC;
+----+-------------+-----------------+------+---------------+-----------+---------+-------+------+-----------------------------+
| id | select_type | table           | type | possible_keys | key       | key_len | ref   | rows | Extra                       |
+----+-------------+-----------------+------+---------------+-----------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | object_settings | ref  | object_id     | object_id | 4       | const |    2 | Using where; Using filesort |
+----+-------------+-----------------+------+---------------+-----------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)

Why is there a filesort operation present all of a sudden? Or does the MySQL Documentation mean something completely different to my understanding?

I have some rather distressing news: ORDER BY can still wreak some havoc with filesorts.

With all the hype about this being addressed and fixed, there is simply no way to get InnoDB to effectively use the index on an ORDER BY.

Start with the Ground Zero of InnoDB row data, the Clustered Index.

Rows are tagged with

a 6-byte transaction ID field
a 7-byte roll pointer field

Rows tend to be ordered by the whatever order the data was entered. The columns of a PRIMARY KEY are included in secondary indexes and are used to search for rows in the Clustered Index, Unfortunately, the two ID fields are not really used in dictating any ordering of rows within the Clustered Index. (For more info, please see MySQL Documentation on InnoDB Physical Row Structure)

Here is something even more disturbing: Did you know you could order rows in a table by the columns of the PRIMARY KEY or any arbitrary ordering you choose?

Here is the syntax:

ALTER TABLE tblname ORDER BY col_name [, col_name] ...

This could speed up some queries that a PRIMARY KEY ordered, but what's disturbing is that it only applies to MyISAM tables. Why not InnoDB ?

According to the MySQL Documentation on ALTER TABLE ... ORDER BY:

  ORDER BY enables you to create the new table with the rows in a
  specific order. Note that the table does not remain in this order
  after inserts and deletes. This option is useful primarily when you
  know that you are mostly to query the rows in a certain order most of
  the time. By using this option after major changes to the table, you
  might be able to get higher performance. In some cases, it might make
  sorting easier for MySQL if the table is in order by the column that
  you want to order it by later.

  ORDER BY syntax permits one or more column names to be specified for
  sorting, each of which optionally can be followed by ASC or DESC to
  indicate ascending or descending sort order, respectively. The default
  is ascending order. Only column names are permitted as sort criteria;
  arbitrary expressions are not permitted. This clause should be given
  last after any other clauses.

  ORDER BY does not make sense for InnoDB tables because InnoDB always
  orders table rows according to the clustered index.

This comes as no surprise to me since I mentioned this in one of my earlier posts : (Aug 29, 2011 : Preordering the table by a specified column)

Therefore, doing an ORDER BY on an InnoDB table never guarantees proper index selection due to its internal index organization. Thus, one should not be surprised by a filesort on an InnoDB table no matter what secondary indexes the table has.


Can PostgresSQL cluster have synchronus and asyncronus standbys at the same time for the same dbs? In this question, Write concern in PostgreSQL Laurenz Albe explained how to control the set of standby servers when data is committed to PostgresSQL, and that gave birth to another question. What if I want to have N standbys, and want to have synchronous replication enabled for some M subset of N, is that possible? A use case could be a cold backup. I don't care of data inconsistency for N-M subset, I just need to have that on M subset of N standbys.

Yes,

There is a setting for this which is called synchronous_standby_names.

You can give expression like ANY 3 (r1, r2, r3, r4) which says "proceed commit as soon as at least any three standbys reply"

If you want to specify exact replication name just name them like r1,r4.

Here is sample usage;

ALTER SYSTEM SET synchronous_standby_names TO 'ANY 3 (r1, r2, r3, r4)';
SELECT pg_reload_conf();

Note: The standby names (which are r1,r2..) are defined by application_name option of standby's primary_conninfo setting value.


ETL, Production, Development -- Should these be different servers? Instances? You can say I've become an "accidental DBA" as people have left this company and I'm managing many aspects of the BI system now suddenly.

My predecessor left quite the mess regardless but I'm trying to think of the best way forward in terms of hardware architecture and logical partitioning.

We have a SQL-Server Data Warehouse. We have ETL scripts (on the same server). We don't have a Test-Production split for database development.

My questions:

Should the ETL scripts (SSIS and Pentaho PDI) be located on the same server as the data warehouse?

I understand this is a complicated question. My understanding is this.

Pros for separate ETL server: Contrary to belief, SSIS and SQL Server don't play nicely via memory management and SQL Server plays best by itself. Scalability and optimization generally favor a separate ETL environment as ETL processes have entirely different needs (far fewer backup needs, far less storage needs).

Cons: Well, additional hardware costs. Additional SQL Server license costs in the case of SSIS (an additional one if separated) so for Standard edition it's an extra $900 and for enterprise and additional $13k or whatever. ALSO, our data warehouse is only used during the day (by developers) - ETL is only run overnight - there is little overlap. Only potentially backup processes may interfere. So maybe the 'competition' aspects are overblown and resource-pooling (for more CPU hardware, which our company IS FRUGAL when it comes to hardware) is advantageous.

Same question but for Test and Production environments.

What exactly is the point of an "instance" separation, as opposed to a database (one lower) or server (one higher) separation?

I guess they have wider configuration options, but -- well even servers can be running on the same physical hardware/ computer. What are the pros of separation Test/ Prod databases via 2 servers or just 2 instances? I take it with 2 instances you're sacrificing configuration for simplicity?

There are probably a hundred reasons if we think about it really hard, but there are really just a few reasons that are so important that we never bother thinking of others:

Resources:  If you haven't had a situation yet were someone did something on a non-production server that sucked up all the memory, CPU, or I/O, you will eventually have that if you continue to work in IT long enough.  You don't want this happening in production.  Yeah, the developers won't do any testing at night when the production ETL processes are running--until they are up against a deadline.  Even if you use resource governing, separate instances--whatever, again, once you've been around long enough (my apologies if you feel that you have), you'll still not want them running on the same box.
Security: Ideally you want to have Active Directory groups for any access that doesn't use a SQL login.  If your development and production are on the same instance, then all of the developers have some access to the production instance.  Ideally you want to allow only as few people as possible the ability to even log in to the production instance
Changes: You are limited to what changes can be tested if all environments use the same system.  Can't test a new service pack or cumulative update if everything's on the same instance.
The Future: If you have separate systems for each environment, you will be much more likely to be able to accommodate future needs.  For example, isolating non-production systems from production systems is becoming A Thing for security purposes.  So workstations can talk to dev and prod, but dev and prod servers are prohibited from communicating.  Can't do this if it's all on the same system.
Reduced Probability of Accidents: If you have "dev" in the name of every server, database, SQL login, etc. that is in the development environment, you are much less likely to have someone accidentally run something in production.  Lots of horror stories out there when credentials were the same name and then it just took a server name typo to bring down production.

Using multiple instances would certainly be a step in the right direction, but you still have risk of resource contention, inability to test impact of OS updates, security changes to OS, different driver versions, etc.

The same principles apply as to whether to run the SSIS jobs on the same server.  Ideally, you would have a separate SSIS server so that you avoid the resource contention--you seem to have a good grasp of that.  Whether it's worth it for you organization to pay for the additional hardware and software licenses--no way for us to tell.  Present it to management, and if they decline then you are covered if things go badly due to too many things running on one system.


MySQL #1064 error on line 2 I'm quite new on learning SQL and I seem to be running into an issue with the following code:

CREATE TABLE `read`
  (`order` NOT NULL UNIQUE AUTO_INCREMENT,
  `title` VARCHAR(50) NOT NULL,
  `url` VARCHAR(50) NOT NULL,
  PRIMARY KEY(`title`,`url`)
)DEFAULT CHARSET=UTF8;

It's returning the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL UNIQUE AUTO_INCREMENT, title VARCHAR(50) NOT NULL, url VARCHAR(50' at line 2

Try like below, it will help you...

CREATE TABLE `read`
  (`order` MEDIUMINT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(50) NOT NULL,
  `url` VARCHAR(50) NOT NULL,
  PRIMARY KEY(`order`)
)DEFAULT CHARSET=UTF8;


Representing SQL constraints on a table I have this table:

CREATE  TABLE `sold` (
`part_ID` INT(11) NOT NULL ,
`date` DATE NOT NULL ,
 PRIMARY KEY (`part_ID`, `date`) ,
 FOREIGN KEY (`part_ID` )
 REFERENCES `part` (`part_ID` );

This table represent parts sold each day, constraint says number of sales should be at least 25 and at most 100.

I think it should start with something like this:

CHECK ( NOT EXISTS ...

Sorry, to tell you: Check Constraints are not implemented in MySQL.

You are probably better off writing a BEFORE INSERT,BEFORE UPDATE trigger that would check the count using something like

SELECT COUNT(1) into UnitSold
FROM sold WHERE partId = OLD.partId

SCENARIO : Implement a check constraint by means of a trigger

We have a simple sold table that look like this:

use test
DROP TABLE IF EXISTS `sold`;
CREATE  TABLE `sold` (
`part_ID` INT(11) NOT NULL ,
`date` DATE NOT NULL ,
`name` VARCHAR(20),
 PRIMARY KEY (`part_ID`, `date`)
);

Let create it:

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS `sold`;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE  TABLE `sold` (
    -> `part_ID` INT(11) NOT NULL ,
    -> `date` DATE NOT NULL ,
    -> `name` VARCHAR(20),
    ->  PRIMARY KEY (`part_ID`, `date`)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql>

Here is the trigger to only allow 3 sales per part_ID

DELIMITER $$
CREATE TRIGGER sold_beforeinsert_trigger
BEFORE INSERT ON sold FOR EACH ROW
BEGIN
    DECLARE UnitsSold INT;

    SELECT COUNT(1) INTO UnitsSold FROM sold WHERE part_ID = NEW.part_ID;
    IF UnitsSold >= 3 THEN
        SELECT COUNT(gummybears) INTO UnitsSold
        FROM information_schema.schemata;
    END IF;

END; $$
DELIMITER ;

Let's create it:

mysql> DELIMITER $$
mysql> CREATE TRIGGER sold_beforeinsert_trigger
    -> BEFORE INSERT ON sold FOR EACH ROW
    -> BEGIN
    ->     DECLARE UnitsSold INT;
    ->
    ->     SELECT COUNT(1) INTO UnitsSold FROM sold WHERE part_ID = NEW.part_ID;
    ->     IF UnitsSold >= 3 THEN
    ->         SELECT COUNT(gummybears) INTO UnitsSold
    ->         FROM information_schema.schemata;
    ->     END IF;
    ->
    -> END; $$
Query OK, 0 rows affected (0.08 sec)

mysql> DELIMITER ;
mysql>

Here is some sample data to load and display:

SET @DaysBack = 0;
SET @DaysBack = @DaysBack + 1; SET @GivenDate = DATE(NOW() - INTERVAL @DaysBack DAY);
INSERT INTO `sold` VALUES ( 1, DATE(@GivenDate), 'rolando');
INSERT INTO `sold` VALUES ( 2, DATE(@GivenDate), 'pamela');
INSERT INTO `sold` VALUES ( 3, DATE(@GivenDate), 'dominique');
INSERT INTO `sold` VALUES ( 4, DATE(@GivenDate), 'diamond');
SELECT * FROM sold;

Let's run this:

mysql> SET @DaysBack = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @DaysBack = @DaysBack + 1; SET @GivenDate = DATE(NOW() - INTERVAL @DaysBack DAY);
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `sold` VALUES ( 1, DATE(@GivenDate), 'rolando');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `sold` VALUES ( 2, DATE(@GivenDate), 'pamela');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `sold` VALUES ( 3, DATE(@GivenDate), 'dominique');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `sold` VALUES ( 4, DATE(@GivenDate), 'diamond');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM sold;
+---------+------------+-----------+
| part_ID | date       | name      |
+---------+------------+-----------+
|       1 | 2013-03-10 | rolando   |
|       2 | 2013-03-10 | pamela    |
|       3 | 2013-03-10 | dominique |
|       4 | 2013-03-10 | diamond   |
+---------+------------+-----------+
4 rows in set (0.00 sec)

mysql>

Let run it again without the first line

mysql> SET @DaysBack = @DaysBack + 1; SET @GivenDate = DATE(NOW() - INTERVAL @DaysBack DAY);
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `sold` VALUES ( 1, DATE(@GivenDate), 'rolando');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `sold` VALUES ( 2, DATE(@GivenDate), 'pamela');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `sold` VALUES ( 3, DATE(@GivenDate), 'dominique');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `sold` VALUES ( 4, DATE(@GivenDate), 'diamond');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM sold;
+---------+------------+-----------+
| part_ID | date       | name      |
+---------+------------+-----------+
|       1 | 2013-03-10 | rolando   |
|       2 | 2013-03-10 | pamela    |
|       3 | 2013-03-10 | dominique |
|       4 | 2013-03-10 | diamond   |
|       1 | 2013-03-09 | rolando   |
|       2 | 2013-03-09 | pamela    |
|       3 | 2013-03-09 | dominique |
|       4 | 2013-03-09 | diamond   |
+---------+------------+-----------+
8 rows in set (0.00 sec)

mysql>

and again

mysql> SET @DaysBack = @DaysBack + 1; SET @GivenDate = DATE(NOW() - INTERVAL @DaysBack DAY);
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `sold` VALUES ( 1, DATE(@GivenDate), 'rolando');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `sold` VALUES ( 2, DATE(@GivenDate), 'pamela');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `sold` VALUES ( 3, DATE(@GivenDate), 'dominique');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `sold` VALUES ( 4, DATE(@GivenDate), 'diamond');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM sold;
+---------+------------+-----------+
| part_ID | date       | name      |
+---------+------------+-----------+
|       1 | 2013-03-10 | rolando   |
|       2 | 2013-03-10 | pamela    |
|       3 | 2013-03-10 | dominique |
|       4 | 2013-03-10 | diamond   |
|       1 | 2013-03-09 | rolando   |
|       2 | 2013-03-09 | pamela    |
|       3 | 2013-03-09 | dominique |
|       4 | 2013-03-09 | diamond   |
|       1 | 2013-03-08 | rolando   |
|       2 | 2013-03-08 | pamela    |
|       3 | 2013-03-08 | dominique |
|       4 | 2013-03-08 | diamond   |
+---------+------------+-----------+
12 rows in set (0.00 sec)

mysql>

and again

mysql> SET @DaysBack = @DaysBack + 1; SET @GivenDate = DATE(NOW() - INTERVAL @DaysBack DAY);
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `sold` VALUES ( 1, DATE(@GivenDate), 'rolando');
ERROR 1054 (42S22): Unknown column 'gummybears' in 'field list'
mysql> INSERT INTO `sold` VALUES ( 2, DATE(@GivenDate), 'pamela');
ERROR 1054 (42S22): Unknown column 'gummybears' in 'field list'
mysql> INSERT INTO `sold` VALUES ( 3, DATE(@GivenDate), 'dominique');
ERROR 1054 (42S22): Unknown column 'gummybears' in 'field list'
mysql> INSERT INTO `sold` VALUES ( 4, DATE(@GivenDate), 'diamond');
ERROR 1054 (42S22): Unknown column 'gummybears' in 'field list'
mysql> SELECT * FROM sold;
+---------+------------+-----------+
| part_ID | date       | name      |
+---------+------------+-----------+
|       1 | 2013-03-10 | rolando   |
|       2 | 2013-03-10 | pamela    |
|       3 | 2013-03-10 | dominique |
|       4 | 2013-03-10 | diamond   |
|       1 | 2013-03-09 | rolando   |
|       2 | 2013-03-09 | pamela    |
|       3 | 2013-03-09 | dominique |
|       4 | 2013-03-09 | diamond   |
|       1 | 2013-03-08 | rolando   |
|       2 | 2013-03-08 | pamela    |
|       3 | 2013-03-08 | dominique |
|       4 | 2013-03-08 | diamond   |
+---------+------------+-----------+
12 rows in set (0.00 sec)

mysql>

AH HA !!! The trigger stopped the INSERT cold in its tracks where a part_ID had three rows in the sold table. MySQL Triggers are not the greatest. Notice I had to SELECT the count of a column that does not exist in information_schema.schemata to cause the trigger to abort.


Question about set log_slow_verbosity='query_plan' in MariaDB ans MySQL I have a question about set log_slow_verbosity='query_plan'

I set log_slow_verbosity='query_plan' on MariaDB 10.

I check the slow log and find more detailed information such as: full_scan; Full_join; tmp_table_on_disk.

When I set set log_slow_verbosity='query_plan' on MySQL 5.7:

mysql> set session log_slow_verbosity='query_plan,innodb';
ERROR 1193 (HY000): Unknown system variable 'log_slow_verbosity'

How to enable this parameter on MySQL 5.7 and get more detailed information in slow log?

The mysql manual shows you what parameters are accepted, and allow you to tune your system.  It's possible this is limited to long_query_time and min_examined_row_limit, while log_slow_verbosity is specific for mariadb.


How to read queries of master that sends to slaves? I want to see queries that Master sends to slaves in Mysql ?
Is there a way to see that ?

SEE QUERIES AS THEY HAPPEN ON THE SLAVE

On a running Slave, you should see to connections with the username system user

Login to the Slave and run this

SELECT * FROM information_schema.user WHERE user = 'system user'\G

SEE QUERIES BEFORE THE SLAVE GETS TO PROCESS THEM

On a running Master, the binary logs contains all completed SQL commands.

To the see the commands, you need to use the mysqlbinlog command utility agaunst the binary logs on the Master or you could run it against the relay logs on the Slave

Here are my earlier posts on how to apply mysqlbinlog in a variety of ways

Feb 18, 2014 : mysqlbinlog failing with " ERROR 1032 (HY000) at line 48: Can't find record in `` " Error
Sep 06, 2013 : MySQL : Extract SQL statements from binlog
Sep 03, 2013 : Get missed transactions from slave
Feb 04, 2013 : MySQL Point in Time Recovery For Just a Single Schema?
Aug 16, 2012 : How to see what was executed in the database via binlogs for specific date?
May 26, 2012 : Trying to understand SHOW SLAVE STATUS\G
Oct 26, 2011 : Multi-threaded replay of master activity for load/performance testing


Transactional replication from SQL Server 2000 to 2008 I have legacy databases on two SQL Server 2000 servers that I am trying to replicate to a SQL Server 2008 R2 database using transactional replication.

The 2008 server is not in the same LAN, so I am replicating across the internet. I created aliases so that each server can connect to the others by name (required for replication) more or less as described by MohammedU (and others) here: http://social.msdn.microsoft.com/forums/en-US/sqlreplication/thread/9a8cf6b1-a449-4748-b3c2-1c13e2bfcc5b/

The only difference is that I used Client Network Utility rather than Configuration Manager to create aliases on the SS2000 machines. This seems to work fine.

I set up one SS2000 server, call it Distributor, to be the distributor for both. I have successfully created publications on Distributor and replicated them using push subscriptions to the SS 2008 server, call it Subscriber.

I have now set up publications on the second SS2000 server, call it Publisher. I created push subscriptions for them in the same manner. These subscriptions are not working.

In the Replication Monitor on Distributor, the Distribution Agents for publications on Publisher have the following error messages:

Error message: The process could not connect to Subscriber 'SUBSCRIBER'
Error details: Login failed for user 'SUBSCRIBER\Guest.'
(Source: SUBSCRIBER (Data source); Error number: 18456)

I also tried creating pull subscriptions on Subscriber. The wizard said they were created succesfully, and the corresponding Distribution Agents showed up correctly in the Replication Monitor on Distributor, but the replication never happened. The Agents kept displaying a message that the snapshot was not available, even though it was.

Can anyone help?

Could you try moving the distributor over on to the SQL 2008 box. Replication got much better after SQL 2005. Looks like you are using anonymous subscriptions, do you not have VPN so that servers can see each other? I am not familiar with Internet based subscribers so probably not much help on its specifics.


PostgreSQL alternative to SQL Server’s `try_cast` function Microsoft SQL Server has what I consider a remarkably sensible function, try_cast() which returns a null if the cast is unsuccessful, rather than raising an error.

This makes it possible to then use a CASE expression or a coalesce to fall back on. For example:

SELECT coalesce(try_cast(data as int),0);

The question is, does PostgreSQL have something similar?

The question is asked to fill in some gaps in my knowledge, but there’s also the general principle that some prefer a less dramatic reaction to some user errors. Returning a null is more easily taken in one's stride in SQL than an error. For example SELECT * FROM data WHERE try_cast(value) IS NOT NULL;. In my experience, user errors are sometimes better handled if there is a plan B.

If casting from one specific type to one other specific type is enough, you can do this with a PL/pgSQL function:

create function try_cast_int(p_in text, p_default int default null)
   returns int
as
$$
begin
  begin
    return $1::int;
  exception
    when others then
       return p_default;
  end;
end;
$$
language plpgsql;

Then

select try_cast_int('42'), try_cast_int('foo', -1), try_cast_int('bar')

Returns

try_cast_int | try_cast_int | try_cast_int
-------------+--------------+-------------
          42 |           -1 |

If this is only for numbers, another approach would be to use a regular expression to check if the input string is a valid number. That would probably  be faster than catching exceptions when you expect many incorrect values.


How to send Unicode symbols using SQL Server Database Mail? How can I send Unicode symbols using SQL Server Database Mail? My goal is to send a symbol on the subject of an email. Let's say that, for example, I want to send this  " Subject with symbol" on the subject. The symbol I want to use come from Segoe UI Emoji font.

Thanks!

Make sure you always use nvarchar as the data type throughout the whole flow, and always prefix Unicode strings with N. There is a big difference:


Oracle OEM Surpressing Warning Messages We are trying to stop having a certain warning message emailed to us. The specific error is a ORA 600 - 17183 and 17302. We have been told by Oracle that we can ignore these warnings as they are a bug in Oracle 12.0.1.2 and are patched in the next version.

Currently we are running OEM 12.1.0.3.0 and are upgrading soon to 12.1.0.5.0. We have done research online to try and figure out the correct regex expression to ignore the above mentioned codes in the Alert log but to no avail. This is what we currently have to try and ignore the 17302 message in the Metric Collection Settings > Generic Alert Log Error:

.*ORA-0*(54|1142|1146)\D.*|.*ORA-00600:.*\[17302[^\]]*\].*

Any ideas/thoughts why this wouldn't work to suppress the emails?

For anyone else with the issue, I contacted Oracle Support and they gave me the following regex string to add-in and it worked:

.*ORA-0*(600|54|1142|1146)\D.*|[17(302|183)[^\]]*\].*


character varying vs. text as function parameter types in PostgreSQL Is there any significant difference (in performance for example) between character varying vs. text as types for db function parameters? I have 2 usage examples:

Example 1:

CREATE OR REPLACE FUNCTION func1(table_name <character varying|text>)
  RETURNS integer AS $$
DECLARE
  RESULT integer;
BEGIN
  EXECUTE 'SELECT COUNT(*) FROM ' || table_name INTO result;
  RETURN result;
END;
$$ LANGUAGE plpgsql;

Example 2:

CREATE OR REPLACE FUNCTION func2(filter_value <character varying|text>)
  RETURNS integer AS $$
DECLARE
  RESULT integer;
BEGIN
  SELECT COUNT(*) INTO RESULT FROM <some_table> WHERE <some_column> = filter_value;
  RETURN result;
END;
$$ LANGUAGE plpgsql;

NOTE in the 2nd example the some_column type is text if filter_value is of type character varying and vice versa

When it comes to performance, these data types are exactly the same, down to the implementation in C.

If in doubt, use text, because that is the preferred type of its type class, and the data type resolution rules for functions will resolve all unknown strings to text implicitly. With character varying you often have to use explicit type casts.


Oracle 12c EM error on login I'm trying to access an Oracle 12c EM and when I try to login it shows me a load error box with the errors that I expose below, which could be happening? Is there a possibility to reconfigure EM Express? If possible, I would like to know where the logs are, thanks.

ORA-00979: not a GROUP BY expression
ORA-06512: at "SYS.PRVT_AWR_VIEWER", line 9171
ORA-06512: at "SYS.PRVT_AWR_VIEWER", line 13196
ORA-06512: at "SYS.PRVTEMX_DBHOME", line 876
ORA-06512: at "SYS.PRVTEMX_DBHOME", line 144
ORA-06512: at "SYS.WRI$_REPT_DBHOME", line 91
ORA-06512: at "SYS.DBMS_REPORT", line 898
ORA-06512: at line 1

This is a shortened, but public version of an MOS (My Oracle Support) note:

WHEN LOGIN VIA EM EXPRESS, ORA-979 IS RAISED (Doc ID 2390782.1)

Here we can find this information:

  CHANGES

  setting "_fix_control"='11657903:ON'

Which gives away the solution, without accessing the private content. Someone enabled the patch for bug 11657903, and that change caused this error. Try disabling the applied patch for bug 11657903:

alter session set "_fix_control"='11657903:OFF';

But this will not have any effect, as logging in to EM creates a new session. Instead of it, you can disable it at system level:

alter system set "_fix_control"='11657903:OFF';

This solves your problem, but that patch was applied to fix other issues, it is not nice to disable it completely. If you want to set a parameter dynamically, on LOGON, only for specific sessions, you can do that with a logon trigger. Here is an example of such a trigger that shows you how to set _fix_control for a specific user and patch, just change it to your needs.

http://karandba.blogspot.com/2015/03/tracing-session-with-client-identifier.html

CREATE OR REPLACE TRIGGER set_trace_logon_test1
AFTER LOGON ON SCOTT.SCHEMA
WHEN (lower(sys_context('USERENV', 'CLIENT_IDENTIFIER')) like '%hr%')
DECLARE
lcommand varchar(200);
BEGIN
EXECUTE IMMEDIATE 'alter session set tracefile_identifier=''From_Trigger''';
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12 : 10053 trace name context forever, level 1''';
EXECUTE IMMEDIATE 'alter session set "_fix_control"="6765823:off"';
insert into sys.trigger_test values (sys_context('USERENV', 'CLIENT_IDENTIFIER'),sysdate,'LOGON');
commit;
END set_trace_logon_test1;
/


Getting an error when trying to start the database (end-of-file on communication channel) I installed Oracle 12c on Ubuntu 3-4 months ago, and it worked perfectly until now. I'm not sure what's causing the error.
Logging in with: sqlplus / as sysdba

Running: SQL> startup
I get the error:

SP2-0642: SQL*Plus internal error state 2133, context 3114:0:0
Unsafe to proceed
ORA-03114: not connected to ORACLE

Running: startup open  I get this

SQL> startup open
ORACLE instance started.

Total System Global Area 2483027968 bytes
Fixed Size          2927432 bytes
Variable Size         671089848 bytes
Database Buffers     1795162112 bytes
Redo Buffers           13848576 bytes
Database mounted.

ORA-03113: end-of-file on communication channel
Process ID: 4438
Session ID: 357 Serial number: 16101

I tried searching info on the SP2-0642, and see that things that could cause this kind of error, is if environmental variables end in a /

For example: old LD_LIBRARY_PATH=/Applications/instantclient10_1/
Or if NLS_DATE_FORMAT is entered incorrectly etc. But none of these apply to me, as I have not changed anything. I just turned on my computer, and trying to connect to the database, it gives me the error mentioned above. I'm have no idea what to do, since the solutions described are already as should be (those I found).

One thing I suspect is... I live in Denmark, when it is fall, we change our time, so that it is one hour behind, and when it is spring, we put so it is one hour ahead. So our time will change all of a sudden compared to other countries. And this weekend that has passed, we changed our clock so the time was put one hour ahead. It hits me that is after we changed our clock that I try to restart the database. Could this be the problem? And how can I check this, and if possible, change the timezone when I can't connect to the database?

---------------------------------------------Update--------------------------------------------------

I found out that (possibly) the real problem is this (seen in the alert log):

ORA-19815: WARNING: db_recovery_file_dest_size of 4781506560 bytes is 100.00% used, and has 0 remaining bytes available.

I should be able to change the size like this:

SQL>  alter system set db_recovery_file_dest_size=20G scope=both;

But since I get the error described above (end-of-file...) I am not able to change or evne see the values of any parameter. Is there some other way to change the size of the db_recovery_file_dest?

If your database is in archive log mode and you don't need it, disable it. This prevents that archive logs are written. Check with archive log list.

If Automatic archival ist enabled, and you don't need online backups, disable it with alter database noarchivelog; – the database needs to be in nomount state to do it.

With rman you can delete the not needed archivelogs (or backups).

rman target /
delete archivelog all
exit

This should clean up your FRA. Of course do this only for testing purposes, don't do this in a production environment! But as you are using Ubuntu as base for your installation I don't think it is a production DB.


Can a TVF use multiple CTE statements internally? I'm trying to make a multi-statement table valued function and failing due to what I think is a limitation of functions and CTEs.

The intended function defines an input parameter(@Param) and and output table(@ResultTable).

It then executes a complex insert statement involving a CTE into that table variable which is (of necessity) terminated by a semicolon.

I then attempt another complex update statement to that table variable involving a CTE and receive an error saying that "Must declare the scalar variable "@ResultTable". Apparently it has dropped out of scope somehow.

I have used this sort of pattern in the past, so my only thought is that the CTEs seem to limit the scope in some way. Is this a known limitation?

Using the following cobbled-together-example, I'm able to utilize 3 different CTE's to work against the output table variable.  Perhaps you could identify the difference between your process and this simple example?

IF EXISTS (SELECT *
           FROM   sys.objects
           WHERE  object_id = OBJECT_ID(N'[dbo].[TEST_TVF_CTE]')
                  AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
  DROP FUNCTION [dbo].[TEST_TVF_CTE]

GO
CREATE FUNCTION [dbo].[TEST_TVF_CTE] (@sInputList VARCHAR(8000))
RETURNS @OutTable TABLE (item VARCHAR(8000))

BEGIN
    WITH FIRSTCTE
    AS (
        SELECT TOP 1 TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLES
        ORDER BY TABLE_NAME
        )
    INSERT INTO @OutTable
    SELECT *
    FROM FIRSTCTE;

    WITH SECONDCTE
    AS (
        SELECT TOP 1 TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLES
        ORDER BY TABLE_NAME DESC
        )
    INSERT INTO @OutTable
    SELECT *
    FROM SECONDCTE;

    WITH FINALCTE
    AS (
        SELECT TOP 1 'XYZ' AS TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLES
        ORDER BY TABLE_NAME DESC
        )
    UPDATE a
    SET a.item = b.table_name
    FROM @OutTable a
    JOIN FINALCTE b ON 1 = 1

    RETURN
END
GO

SELECT *
FROM DBO.TEST_tvf_cte('...')


How to get max value of union query? My database schema is as follows

  Department (dept_id, dept_name)

  Student (student_id, student_name, major, level, age)

  Professor (prof_id, prof_name, dept_id)

  Class (name, meets_at, room, prof_id)

  Enrolled (student_id, class_name, semester)

I want to find the age of the oldest student who is either a “History” major or enrolled in a course taught by “Michael Miller”.

My attempt was:

SELECT MAX(s.age)
FROM  professor as p
join class as c  on p.prof_id = c.prof_id
join enrolled as e  on e.class_name = c.name
join student as s ON  s.student_id = e.student_id
 WHERE p.prof_name =' Michael Miller'
 union SELECT max(s.age) from student as s where s.major ='history' ;

The result is:

MAX(s.age)

NULL

20

How can I select the maximum of this query? I would like to get the value 20.

Any help is very much appreciated.

You could wrap your query inside a subquery and select a max from that subquery like:

SELECT MAX(age) FROM
(
SELECT s.age
FROM  professor as p
join class as c  on p.prof_id = c.prof_id
join enrolled as e  on e.class_name = c.name
join student as s ON  s.student_id = e.student_id
 WHERE p.prof_name =' Michael Miller'
 union SELECT s.age from student as s where s.major ='history'
) as subquery;

or rely on a sorting and a limit like this

SELECT age FROM
(
SELECT s.age
FROM  professor as p
join class as c  on p.prof_id = c.prof_id
join enrolled as e  on e.class_name = c.name
join student as s ON  s.student_id = e.student_id
 WHERE p.prof_name =' Michael Miller'
 union SELECT s.age from student as s where s.major ='history'
) as subquery
ORDER BY age DESC
LIMIT 1;

But you could also select the maxes before unions like this:

SELECT MAX(age) FROM
(
SELECT max(s.age) AS age
FROM  professor as p
join class as c  on p.prof_id = c.prof_id
join enrolled as e  on e.class_name = c.name
join student as s ON  s.student_id = e.student_id
 WHERE p.prof_name =' Michael Miller'
 union SELECT max(s.age) AS age from student as s where s.major ='history'
) as subquery;

or

SELECT age FROM
(
SELECT MAX(s.age) AS age
FROM  professor as p
join class as c  on p.prof_id = c.prof_id
join enrolled as e  on e.class_name = c.name
join student as s ON  s.student_id = e.student_id
 WHERE p.prof_name =' Michael Miller'
 union SELECT MAX(s.age) AS age from student as s where s.major ='history'
) as subquery
ORDER BY age DESC
LIMIT 1;

Have a look at my SQLFiddle here

I created 3 approaches (using simplified table structures and sample data).

Using

CREATE TABLE Table1
    (`id` int, `age` int)
;

INSERT INTO Table1
    (`id`, `age`)
VALUES
    (1, 5),
    (2, 6),
    (3, 3)
;

CREATE TABLE Table2
    (`id` int, `age` int)
;

INSERT INTO Table2
    (`id`, `age`)
VALUES
    (1, 6),
    (2, 8),
    (3, 1)
;

and these 3 queries

SELECT MAX(age) FROM
(
  SELECT age FROM Table1
  UNION
  SELECT age FROM Table2
 ) as results

 ;
 SELECT age FROM
 (
   SELECT MAX(age) as age FROM Table1
   UNION
   SELECT MAX(age) as age FROM Table2
 ) AS results
 ORDER BY age DESC
 LIMIT 1
 ;

 SELECT max(age) FROM
 (
   SELECT MAX(age) as age FROM Table1
   UNION
   SELECT MAX(age) as age FROM Table2
 ) AS results
 ;

All three return '8' but the third query looks like it's the most efficient one.
Test it on your table structures and see what you like best.


How to find all invalid document based on jsonSchema validator? One of the new feature in MongoDB 3.6 is jsonSchema validator. I can create a collection with an validator like:
{
  "validator": {
    "$jsonSchema": {
      "properties": {
        "name": {
          "bsonType": "string",
          "description": "name"
        },
        "age": {
          "bsonType": "int",
          "minimum": 10,
          "maximum": 30
        }
      },
      "required": ["name", "age"],
      "bsonType": "object"
    }
  }
}

But what if the collection already existed with some documents, that don't fullfill the validation schema? How can I found out all invalid documents based on the validator?

You can use the $jsonSchema query operator to find relevant documents.
For example, using the mongo shell:
// Retrieve the current validator for the "students" collection
var schema = db.getCollectionInfos({ name: "students" })[0].options.validator

// Find any documents not matching the schema
db.students.find({ $nor: [schema] })

Similarly, you can use the $jsonSchema query operator to confirm expected matches before adding or updating the validator for a collection:
var schema = {
  $jsonSchema: {
    properties: {
      name: {
        bsonType: "string",
        description: "name",
      },
    },
    required: ["name"],
    bsonType: "object",
  },
}

// Find documents matching schema
db.students.find(schema)


SQL Server memory issues after upgrade from Express I have a SQL Server 2014 Standard (x64) instance that was recently upgraded from SQL Express.

The VM that the SQL instance is on has ~16GB of RAM and SQL MAX MEMORY has been configured to use ~12GB

I want to understand while i configured: max server memory (MB) = 12000, why the SQL instance is not consuming more than ~4154MB when i verified it with following query:

select (physical_memory_in_use_kb/1024) usage_in_MB
from sys.dm_os_process_memory

max server memory is the maximum you will allow SQL to use.  This is primarily set to leave memory for other process like the operating system, or if you have multiple instances on one machine.

  Setting max server memory value too high can cause a single instance of SQL Server might have to compete for memory with other SQL Server instances hosted on the same host. Source

SQL only uses as much memory as it needs, so it is not unusual to see memory usage much lower, when SQL does not need it.

Even when you think SQL should use more CPU, sometimes it chooses not to. I have some index reorg jobs that I want to work harder, but they will never use more then 10 or 20% of CPU.  See for yourself, make several "Ugly" indexes and then use the tool of your choice to fix them.

Setting Max at 12GB when you have 16GB is a well balanced choice in most single instance configurations.


Is any caching mechamism used for non-persisted computed columns? Does SQL Server cache the result of a non-persisted computed column so it can be reused without incurring cost of recalculation?

Additional Context
I've always given the advice that if using computed columns you should use the PERSISTED option unless you're expecting a higher insert/update frequency than read frequency, or if you need better performance around inserts/updates than you do reads of the data (i.e. since the cost of calculation has to be incurred on one or the other, so the decision is really where you want to pay that cost).

There's also the consideration of the additional storage for the computed data, but typically that's pretty negligible and cheap, so not much of a consideration.

However, I wanted to check if my advice is entirely accurate, since SQL may be more intelligent... i.e. Once SQL's calculated a computed column, it could record this value in memory so that it doesn't have to recalculate the value on subsequent queries. SQL could have a timestamp on the cached value and another on the underlying record data to say if that record's been changed since the computed value was calculated to determine whether the cached value is still valid.

Is there anything like this in place / does it depend on available resources (e.g. memory), or other factors (e.g. does the cached value have a TTL beyond that of the process lifetime)? I've never read anything implying that this exists, but I'd be surprised if there weren't some optimization going on under the covers.

The non-persisted computed column values are not cached in memory.

Here's a brief-ish demo that confirms this.  First I'll create a database for our testing:

CREATE DATABASE CC_BufferPool_Test;
GO
USE [CC_BufferPool_Test];
GO

And then create a table without a computed column as a baseline.  The column sizes in this table are specifically chosen so that 4 rows will exactly fill one 8KB data page (and thus one 1 page in SQL Server's buffer cache):

CREATE TABLE T
(
    C1 INT NOT NULL IDENTITY PRIMARY KEY,
    Filler CHAR(2011) NOT NULL
);
GO

INSERT INTO T (Filler) VALUES(REPLICATE('A', 2011));
GO 4

Next I'll create the same table, but with the addition of a computed column:

CREATE TABLE T_WithCC
(
    C1 INT NOT NULL IDENTITY PRIMARY KEY,
    C2 AS 'Row #' + CONVERT(CHAR(8), C1, 1),
    Filler CHAR(2011) NOT NULL
);
GO

INSERT INTO T_WithCC(Filler) VALUES(REPLICATE('A', 2011));
GO 4

Next, I'll make sure these 8 rows are written to disk, and then clear the buffer cache, and then select all 8 rows so that they get pulled into memory:

CHECKPOINT
DBCC DROPCLEANBUFFERS;

SELECT * FROM T;
SELECT * FROM dbo.T_WithCC;

Finally, I'll use this query that I totally did not steal from Aaron Bertrand*

From that, you can see there is only one page in memory for each of those two tables.  If the computed column were stored in memory, it would have pushed the second table's rows onto a second page.

You can also see the calculation occurring each time in the actual execution plan:

If you do need to "cache" non-persisted computed columns for specific queries, you can actually create a nonclustered index on them.  That way they are stored in the index, but not on the base table.

*see Determine SQL Server memory use by database and object, and here's the code:

;WITH src AS
(
    SELECT
        [Object] = o.name,
        [Type] = o.type_desc,
        [Index] = COALESCE(i.name, ''),
        [Index_Type] = i.type_desc,
        p.[object_id],
        p.index_id,
        au.allocation_unit_id
    FROM sys.partitions AS p
        INNER JOIN sys.allocation_units AS au
            ON p.hobt_id = au.container_id
        INNER JOIN sys.objects AS o
            ON p.[object_id] = o.[object_id]
        INNER JOIN sys.indexes AS i
            ON o.[object_id] = i.[object_id]
            AND p.index_id = i.index_id
    WHERE
        au.[type] IN (1,2,3)
        AND o.is_ms_shipped = 0
)
SELECT
    src.[Object],
    src.[Type],
    src.[Index],
    src.Index_Type,
    buffer_pages = COUNT_BIG(b.page_id),
    buffer_mb = COUNT_BIG(b.page_id) / 128
FROM src
    INNER JOIN sys.dm_os_buffer_descriptors AS b
        ON src.allocation_unit_id = b.allocation_unit_id
WHERE b.database_id = DB_ID()
GROUP BY
    src.[Object],
    src.[Type],
    src.[Index],
    src.Index_Type
ORDER BY buffer_pages DESC;


Large Transaction Backup On my SQL database I run a full backup at 6am and transactional backups every 15 minutes on week days starting at 8am.

Every Monday the first transactional backup is 7 Gb. I want to know why, my database is only 500Mb.

Am I correct in thinking that something is running between 6am and 8am that is causing this massive tran backup? Or could it be something over the weekend that causes the first tran backup to be so large.

You most probably re-index your database between the log backups. This means your log backup contains a lot of transactions from these maintenance operations.
Please check your maintenance plans.
The size of transaction log can be huge compared to your actual database, imagine this situation. You delete 100k rows and add 100k rows between your backups. Your database will in the end be the same size, but you will have a big transaction log.


searching against decrypted column I'm using Postgres on AWS RDS.

My application is encrypting selected columns in a table using AWS KMS encryption.

I tried but could not find a way to configure KMS keys and use the decrypt() in select query.

For example, my app is encrypting myTable.secureColumn using KMS.

Now, I'm trying to fire below query:

select * from myTable where decrypt(secureColumn, 'key', 'aes') = 'data';

I didn't find how to configure KMS keys in pgCrypto and use in above query.

Has anyone tried this?

Thanks in advance.

What you are trying to do won't work because pgcrypto's decrypt function is not going to receive the KMS key you encrypted the data with.

Essentially, when you set up KMS it generates a key pair consisting of a public key and a private key. These are generated on a Hardware Security Module (HSM) and the public key is something you can query the KMS api for. The private key remains on the HSM and never leaves it. You can hand data to it for decryption using the api, but you cannot receive the key; the whole point of using KMS is that the private key remains secret, even from you.

What setting up KMS on RDS postgres does get you is that your tablespace and your backups are encrypted. If you also want to do row level encryption over and above the data at rest protections that are provided when you create your database with the --storage-encrypted True flag set; you will need to manage another set of keys for that.

The right way to do that is to generate symmetric encryption keys that are then encrypted with a KMS master key. Your application would use the API to decrypt those keys on start and would retain them in memory, passing them to the database with every query that needed decrypt operations.

Indexing an encrypted column is impossible by definition since the cryptext should appear random to anyone who does not have the key. The query you posted above would be slow even if you had the key to look at the column since it would have to perform the decryption operation for every row. Generally speaking if you are using row level encryption you want to be encrypting dependent attributes not key attributes; since selection, sorting and searching all require access to the plaintext of the values.


AG groups don't sync user DB collation change to replica servers? We have 2012 AG group set up between server A and server B, where A is the primary, B is the replica

1) When we changed the user DB collation on server A, on server B, the user DB still shows the old collation. Is that by design? So we would need to redo AG to make the change show on the replica servers?

2) When we change the columns and other objects' collation on server A, does that get changed automatically on the replica server through AG sync process?

3) Is there any advantage of having server collation being SQL_Latin1_General_CP1_CI_AI while the DBs being Latin1_General_CI_AI ?

4) Lastly, if the default DB collation is different than SOME of the table/column collation, would that be an issue for AG/DB in general?

Thank you!

1) When we changed the user DB collation on server A, on server B, the user DB still shows the old collation. Is that by design? So we would need to redo AG to make the change show on the replica servers?

It may show the old collation, but it's been changed. Please note that changing the collation at the database level does not change it on any of the items underneath until it is specifically changed, however all new items will use that by default.

If you don't trust me (I'm a random internet person!) and you have a test system you'll see that this data is cached. Change the collation, then restart the secondary replica. Now check the collation. I do not know, though, why the metadata cache is not refreshed.

  2) When we change the columns and other objects' collation on server A, does that get changed automatically on the replica server through AG sync process?

Yes, assuming it's a database level object in a database in the AG.

  3) Is there any advantage of having server collation being SQL_Latin1_General_CP1_CI_AI while the DBs being Latin1_General_CP1_CI_AI ?

Depends, does you application require a specific collation or options? The advantage is for the application to sort and display data properly.

  4) Lastly, if the default DB collation is different than SOME of the table/column collation, would that be an issue for AG/DB in general?

It's not an issue for AGs, it's a potential issue for the application. The AG will happily send the log block changes all day.


How to examine PostgreSQL server's SSL certificate? Suppose there is a PostgreSQL server running and it has SSL enabled.  Using "standard" Linux and PostgreSQL tools, how can I examine its SSL certificate?

I'm hoping for output similar to what you would get from running openssl x509 -text ....  And I'm hoping for a one- or two-liner command line answer so I don't have to resort to running a packet sniffer.

I do not have access to the PostgreSQL server, so I cannot look at its configuration files directly.

I do not have a superuser login, so I can't get the value of the ssl_cert_file setting and then pg_read_file on it.

Using openssl s_client -connect ... doesn't work because PostgreSQL doesn't seem to want to do the SSL handshake right away.

From a quick look at the psql documentation, I could not find a command-line parameter that makes it show that information on startup.  (Though it does show me certain cipher information.)

It looks like OpenSSL's s_client tool added Postgres support using the -starttls in 1.1.1, so you can now use the full power of OpenSSL's command line tools without additional helper scripts:

openssl s_client -starttls postgres -connect my.postgres.host:5432 # etc...

References:

Git commit
s_client manpage


mysql (mariadb) won't start after power outage (MacOS) /var/log/system.log says (over and over)
Oct 13 19:34:01 Data-Server com.apple.xpc.launchd[1] (com.mariadb.server[2128]): Service exited with abnormal code: 1
Oct 13 19:34:01 Data-Server com.apple.xpc.launchd[1] (com.mariadb.server): Service only ran for 0 seconds. Pushing respawn out by 10 seconds.

/usr/local/var/mysql/Data-Server.local.err says (once recently, repeated a number of times well before the crash)
2020-10-13  2:44:25 20019181 [Warning] Aborted connection 20019181 to db: 'EcoReality' user: 'root' host: '10.1.2.2' (Got timeout reading communication packets)

First thing I did was to shutdown the launchctl entry, to keep it from constantly restarting.
# launchctl unload /Library/LaunchDaemons/com.mariadb.server.plist
Then I tried invoking mysqld manually:
# sudo  /usr/local/bin/mysqld -u mysql
2020-10-13 20:46:09 0 [Note] /usr/local/bin/mysqld (mysqld 10.4.6-MariaDB) starting as process 2364 ...
2020-10-13 20:46:09 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-10-13 20:46:09 0 [Note] InnoDB: Uses event mutexes
2020-10-13 20:46:09 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-10-13 20:46:09 0 [Note] InnoDB: Number of pools: 1
2020-10-13 20:46:09 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-10-13 20:46:09 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-10-13 20:46:09 0 [Note] InnoDB: Completed initialization of buffer pool
2020-10-13 20:46:09 0 [ERROR] InnoDB: Invalid log block checksum. block: 81635496 checkpoint no: 2609153 expected: 296846624 found: 3735928559
2020-10-13 20:46:09 0 [ERROR] InnoDB: Missing MLOG_CHECKPOINT at 41797373564 between the checkpoint 41797373564 and the end 41797373440.
2020-10-13 20:46:09 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2020-10-13 20:46:09 0 [Note] InnoDB: Starting shutdown...
2020-10-13 20:46:09 0 [ERROR] Plugin 'InnoDB' init function returned error.
2020-10-13 20:46:09 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-10-13 20:46:09 0 [Note] Plugin 'FEEDBACK' is disabled.
2020-10-13 20:46:09 0 [Note] CONNECT: Version 1.06.0009 January 27, 2019
2020-10-13 20:46:09 0 [ERROR] Unknown/unsupported storage engine: InnoDB
2020-10-13 20:46:09 0 [ERROR] Aborting

So now I'm a bit stumped at the lack of diagnostic messages. Is there any way to coax more info out of mysqld when it goes down? Or should I just start incrementing innodb_force_recovery until something interesting happens?

it s clearly telling you that it looks for INNOdb driver and can not find it.
that means that your filesystem can not read the needed files. try to repair the installation, if they are missing or look for the reason.


tsql to find out memory, cpu and IP utilization by a databases over a week - MS SQL Server 2005 and above I want to identify resource (CPU, memory and IO) usage by a database on a SQL 2005 and above server, over a week or a month. Can anyone please help in this?

Thanks in advance

There are several options you can use:

You can use Use Dynamic Management Views (DMVs), such as sys.dm_exec_sessions, sys.dm_os_performance_counters, sys.dm_os_memory_brokers, sys.dm_os_memory_nodes, sys.dm_exec_procedure_stats, sys._dm_os_sys_info, sys.dm_exec_requests, sys.dm_exec_requests, and many more, depending on what you actually want to monitor.

As SQL Server doesn't store the performance metric values in an archive table, you can query the views on a schedule and insert the results into a table that you will use as a repository.You cfan query and analyze the records easily then.

Another question is - what counters to monitor.

For processor usage: Processor : % Processor Time, Processor Queue Length

For memory, Available memory bytes, Total server memory, and Target server memory

You can find a complete list of counters recommended for monitoring here: Performance Monitor Counters

Note that when you use the dm_os_performance_counters view, it's essential to understand the counter_type. There are five different values, and the current value is calculated differently for each value type. You have examples and explanations here: sys.dm_os_performance_counters

Another option is to use a third party tool that collects the metrics you want to monitor, stores them in a repository and shows historic data for the time period you select. As the history data is stored in SQL tables, you can also easily query the data yourself and create reports.

Such a tool is ApexSQL Monitor, and it has built-in graphs for the last day, week, and month. It will have the reports available soon.

Disclaimer: I work for ApexSQL as a Support engineer


How do I run a SQL Server 2005 job on Windows 7? I have Windows 7 and SQL Server 2005 Version 9.00.1399.

I can successfully create an SSIS Package, however I am unable to execute it successfully as a scheduled job. When I run the job, SQL Agent reports Unable to start execution of step 1 (reason:Could not get proxy data for proxy_id = 1). The step failed.

All SQL services are running successfully.

The same kind of activity on Windows Server 2008 R2 Edition and SQL Server 2005 Version 9.00.4035 works successfully.

Does anyone know why this wouldn't work on Windows 7?

Probably the major problem is that your username don't have rights.
There are some examples how to create Accounts:

SSIS run  on different Account

SSIS runninng job

I would try set Owner "yourself" your PC Administrator user.


count multiple occurences of attribute values for each row in sql I have a mytable structured as follows and I would like to count occurences of values for attribute in each row:

id | attribute
--------------
1  | spam
2  | egg
3  | spam

With

SELECT id, attribute, COUNT(attribute) FROM mytable GROUP BY attribute

I only get

id | attribute | count
----------------------
1  | spam      | 2
2  | egg       | 1

But what I would like as a result is

id | attribute | count
----------------------
1  | spam      | 2
2  | egg       | 1
3  | spam      | 2

How to achieve this?

select
  m1.id,
  m1.attribute,
  (select count(*) from mytable m2 where m2.attribute = m1.attribute)
from
  mytable m1
;

Another version:

select
  m1.id,
  m1.attribute,
  m2.c
from
  mytable m1
  join (SELECT attribute, COUNT(attribute) as c FROM mytable GROUP BY attribute) m2
  on (m1.attribute = m2.attribute)
;

A better version for databases with analytic/window functions:

select
  m1.id,
  m1.attribute,
  count(*) over (partition by m1.attribute)
from
  mytable m1
;


Why is real table size much bigger than estimated size? I'm doing some benchmarks and I found a strange thing - real table size is in 7 times bigger than estimated size

I created a table with 1 int column (4 bytes)

CREATE TABLE `t_int` (
  `c_int` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And inserted 57M records with value 1. I expected to get table 228Mb (4 bytes * 57M records), but got 1.8Gb

           Name: t_int
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 57391085
 Avg_row_length: 36
    Data_length: 2089795584
Max_data_length: 0
   Index_length: 0
      Data_free: 5242880
 Auto_increment: NULL
    Create_time: 2017-01-12 22:42:23
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:

optimize tables has not changed the results

Why is that?

MySQL 5.7.11

For each row InnoDB use additional disk space, such as:

If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.
Records in the clustered index contain fields for all user-defined columns. In addition, there is a 6-byte transaction ID field and a 7-byte roll pointer field.

so, when You test it as described - it will use 30+- bytes per row, and this is will be similar with result
additional source of information:

https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html
https://dev.mysql.com/doc/refman/5.5/en/innodb-physical-record.html


Redo lag on AG readable secondaries: is there a way to overcome it? I have a quite busy (1000+ batch requests per second, 350+ workers on average) system built on sql server 2016. It's an availability group with 3 synchronous replicas.
I'd like to redirect read requests on secondaries, but due to the redo lag (and the way the app works), I'm concerned about the fetched data being stale.

I know I can get the last hardened lsn and last redone lsn from sys.dm_hadr_database_replica_states - but is there any way to wait for the redo to catch up avoiding a while loop?

Thanks!
Gabriele

It sounds like you have a few different questions in here:

Could a query be modified to check to see if redo is current on the underlying data is done, and if not, stall execution until redo catches up? This seems like a really dangerous idea because when replication gets behind, you'll have more and more queries using up worker threads, waiting for replication to catch up. I can easily see worker thread exhaustion hitting here and causing threadpool waits because it's essentially the same scenario that blocking causes.

Could queries be redirected to the most current secondary? Yes, by using a load balancer between your app and the SQL Servers. You can build logic into load balancers to run periodic health checks, and when a server fails the load check, it's taken out of the available connection list until it's able to pass health checks again. This is a pretty common strategy for web servers, but fairly rare for database servers (since it's a lot of work to build out.)

Can redo be done faster? Yes, test to see whether serial or parallel redo is a better fit for your workloads, and make sure you're getting the one you want. Microsoft has blogged about the gotchas with parallel redo, including gems like only the first databases in your AG (by database ID) get parallel redo.


Joining to Queries side by side calculating sales values by year I want to join 2 tables from 2 different queries side by side.

Q1= Employees sales value for one year

Q2= Employees sales values for another year

Expected output:
Employee / TotalSalesValue_1996 / TotalSalesValue_ 1997

Below is my unsuccessful attempt, it seams to duplicate the Value of Q2.

select A1.Employee, A1.Title, A1.TotalSalesValue_1996, A2.TotalSalesValue_1997
from
(
  select concat(e.titleofcourtesy,' ', e.firstname, e.lastname) as Employee, e.Title, sum((od.unitprice * od.quantity) - (1 * od.discount)) as TotalSalesValue_1996
  from employees as e
  inner join orders as o on o.employeeid=e.employeeid
  inner join order_details as od on od.orderid=o.orderid
  where o.ShippedDate between '1996-01-01' and '1996-12-31'
  group by e.title, concat(e.titleofcourtesy,' ', e.firstname, e.lastname)
) as A1
,
(
  select concat(e.titleofcourtesy,' ', e.firstname, e.lastname) as Employee, e.Title, sum((od.unitprice * od.quantity) - (1 * od.discount)) as TotalSalesValue_1997
  from employees as e
  inner join orders as o on o.employeeid=e.employeeid
  inner join order_details as od on od.orderid=o.orderid
  where o.ShippedDate between '1997-01-01' and '1997-12-31'
  group by e.title, concat(e.titleofcourtesy,' ', e.firstname, e.lastname)
) as A2;

*Please note that I used one of the free data bases available (northwind.sql)

Use a JOIN clause. So instead of the comma betwee the tables, put FULL OUTER JOIN. And then after A2 put ON A1.Employee = A2.Employee AND A1.Title = A2.Title.

But a better option might be to use a single query, with:

select
   concat(e.titleofcourtesy,' ', e.firstname, e.lastname) as Employee,
   e.Title,
   sum(CASE WHEN o.ShippedDate between '1996-01-01' and '1996-12-31' THEN (od.unitprice * od.quantity) - (1 * od.discount) ELSE 0 END) as TotalSalesValue_1996,
   sum(CASE WHEN o.ShippedDate between '1997-01-01' and '1997-12-31' THEN (od.unitprice * od.quantity) - (1 * od.discount) ELSE 0 END) as TotalSalesValue_1997
from employees as e
inner join orders as o on o.employeeid=e.employeeid
inner join order_details as od on od.orderid=o.orderid
where o.ShippedDate between '1996-01-01' and '1996-12-31'
group by e.title, concat(e.titleofcourtesy,' ', e.firstname, e.lastname);


So here's what I did. I put what you had in a temp table and then grouped the results like so:

;WITH data AS (
  SELECT grp, k, k_ts = k + '_ts', ts, val
  FROM @Source

)
SELECT *
INTO #tmp
FROM data
PIVOT ( MAX(val) FOR k IN ([color], [shape], [subst]) ) p1
PIVOT ( MAX(ts) FOR k_ts IN ([color_ts], [shape_ts], [subst_ts]) ) p2

Select
    grp
    , max(color) as color
    , max(shape) as shape
    , max(subst) as subst
    , max(color_ts) as color_ts
    , max(shape_ts) as shape_ts
    , max(subst_ts) as subst_ts
from #tmp
group by grp

It's not pretty, but it worked.


Best mysql db design to solve timezone issues I faced problem with timezone on my last two projects. So What is best way to sort out problems with timezone on db design.

I mean, What format should i added date time stamp.

On of my problem. My client asked me to expire particular event on users timezone. How can i solve this.

Thanks in advance.

Off the top of my head, the simplest thing I could think of would be to store three values:

UTC (DATETIME),
timezone offset for datetime in minutes (SMALLINT) (-1440 to 1440)
DST (daylight saving time), 0 or 1 (TINYINT)

I originally was thinking of timezone offset in hours. @yercube reminded me of other places with 15 min or 30 min additional offsets . For example, these cities have these times:

New York City at 4:00 PM
Bangalore 1:30 AM next day
Katmandhu 1:45 AM next day

You would have to

perform date arithmetic to handle display of datetimes
compare datetimes as pure UTC values


What is the best way to recover from a mysql replication fail? Today, the replication between our master mysql db server and the two replication servers dropped.
I have a procedure here which was written a long time ago and i'm not sure it's the fastest method to recover for this issue.
I'd like to share with you the procedure and I'd appreciate if you could give your thoughts about it and maybe even tell me how it can be done quicker.

At the master:

RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
And copy the values of the result of the last command somewhere.

Wihtout closing the connection to the client (because it would release the read lock) issue the command to get a dump of the master:

mysqldump mysq
Now you can release the lock, even if the dump hasn't end. To do it perform the following command in the mysql client:

UNLOCK TABLES;
Now copy the dump file to the slave using scp or your preferred tool.

At the slave:

Open a connection to mysql and type:

STOP SLAVE;
Load master's data dump with this console command:

mysql -uroot -p < mysqldump.sql
Sync slave and master logs:

RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;
Where the values of the above fields are the ones you copied before.

Finally type

START SLAVE;
And to check that everything is working again, if you type

SHOW SLAVE STATUS;
you should see:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
That's it!

At the moment i'm in the stage of copying the db from the master to the other two replication servers and it takes more than 6 hours to that point, isn't it too slow?
The servers are connected through a 1gb switch.

We can start replication from the position from where it stopped. You should check the Mysql  log on slave servers log must have the last mysql bin log postion where the replication stopped.

 STOP SLAVE;

 RESET Slave;

 FLUSH TABLES WITH READ LOCK;

 CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;

 Where the values of the above fields are the ones where replication stopped.

Finally type

 START SLAVE;


Fastest MySQL pagination and ORDER from a huge UNION ALL VIEW Let's say we have a schema with table 'month' which have the current information and table 'year' which have the historic information. Each table has around 65,000 rows.

This schema exists in several places, let's say place A, B, and C.

It's requested to fetch ordered information from every schema. No possible 'materialized view'

According to me, I need to unite all the results (without LIMIT):

CREATE VIEW all_results AS
SELECT * FROM A.month
UNION ALL
SELECT * FROM A.year
UNION ALL
SELECT * FROM B.month
....

...and then apply the ORDER BY, the COUNT and the LIMIT

SELECT * FROM all_results ORDER BY column1 LIMIT 0,23
SELECT count(*) FROM all_results

but this approach is taking almost 20 seconds (which is too much for a web solution).

Is there a better way to do this?

(Added from dup question)  This variant also takes 20 seconds.

SELECT SQL_CALC_FOUND_ROWS * FROM(
SELECT * FROM A.month
UNION ALL
SELECT * FROM A.year
UNION ALL
SELECT * FROM B.month
....
) AS united
LIMIT 0,23

And the count the total: SELECT FOUND_ROWS()

Rethink the need for getting the total count; it is probably the most costly part.
Time each of the two queries separately.  Optimize them separately.  Then re-time.
Do you really need all (*) columns?  TEXT and BLOB columns extract an extra toll.
There is no advantage in having a VIEW; instead there is a big disadvantage in that the UNION will be run twice!  (In MySQL 8.0, WITH might avoid this 'twice'.)
For the query with the LIMIT, you can and should do the limit on each subquery.  More details here .
Each table needs INDEX(column1) .

This should significantly speed up (more than 2X) the big count:

SELECT  ( SELECT COUNT(*) FROM A.month )
      + ( SELECT COUNT(*) FROM A.year )
      + ( SELECT COUNT(*) FROM B.month )
      ... ;

This is because COUNT(*) is faster than either the gathering of all the rows into a temp table or the counting of rows in that table.

I predict that the result of all my suggestions will get it under 5 seconds.  If not, please provide SHOW CREATE TABLE for each table.  And what MySQL version you are using.

Paginate through 10K rows??

Let approach your problem from the other side.  If I calculate correctly, there are about 10,000 pages in your output?  What user can page through that many pages before dying if old age??

If you have more than a few dozen pages, then, for the sake of the users sanity, come up with another way to show the data to them.  Pagination is not practical.

Let me provide a simple example -- Paginating through all a list of all cities in the world...

Plan A, like pagination...  First have the user pick what country.  (Even that is clumsy since there are about 250 countries.)  Then pick a province/state/canton/etc.  Then pick A..Z, then page through the cities.

Plan B, moving farther afield...  Provide some "search" mechanism(s) -- for searching by part of the name, by country/state/province/etc, by population range, by part of the world, etc.


Choose two columns for same purpose or it should be two different columns? We are an ecommerce website. We provide Finance option for our stocks, we have these finance options for few stocks. We have a stock table which has two columns:

IsFinanceAvailable: true/false. This tells if stock has emi available or not.
EmiAmount: This gives emi amount if its applicable. The stock may have finance but need not have emi.

We may have a case where IsEmiAvailable is true but EmiAmount is not available, but if EmiAmout is available, IsEmiAvailable has to be set true.

Should we have two separate columns or infer all info just with EmiAmount. For example:

EmiAmount null: This means no finance

EmiAmount 0: This means finance available but no emiAmount

EmiAmount>0

Should we have two different columns or just emi amount would do? what are pros and cons?

If there are only 3 states for EmiAmount (and barring other complications that may need to be added) I would be inclined to use the EmiAmount (NULL, 0, >0) to define the state.

Why add a flag (which is duplicate information) if the data itself easily describes the state that you need to know?

I greatly prefer avoiding adding duplicate data which must itself be maintained. And a single source of the status (the actual data) is all that you need.

Of course, there may be complications unmentioned that could complicate matters.


Use of NOLOCK with CDC capture tables In using SQL Server's Change Data Capture, I notice that the fn_cdc_get_all_changes_... table valued functions all use NOLOCK when querying against the capture tables. Is this a recommended practice when querying against the capture tables and if so, why?

It appears that this was added in the 2008 time period.  You can see the Connect item with the title "MSIT-MSO: Use NOLOCK while querying the cdc._CT table in CDC TVF" at:

http://connect.microsoft.com/SQLServer/feedback/details/331486/msit-mso-use-nolock-while-querying-the-cdc-captureinstance-ct-table-in-cdc-tvf

It is interesting that the request was made to prevent the CDC updates from blocking the CDC queries that select the data.  This change was posted to the Connect item on 3/18/2008.

Considering the potential velocity of change, this may make sense for this case, but it does not make NOLOCK a generally good idea for other code.


Error on import of mysqldump file - Illegal double value found during parsing I am currently getting the following error while trying to import / restore a mysqldump file:

ERROR 1367 (22007) at line 445: Illegal double '1.79769313486232e+308'
value found during parsing

I looked at various places online and could not find anything that was similar.

Some background:

I am trying to create a new replication box and the mysqldump is new from another replica box. Both boxes are:

  CentOS 5.7 with MySQL 5.0.77-4

Used the following to create the mysqldump:

$ mysqldump --lock-all-tables --all-databases | gzip -9 > mysqldump.sql.gz

The new replication box has nothing on there right now and all the databases / tables are supposed to be created by mysqldump. Any insights?

Thanks!

There is current open bug report on this one.

The bug report has a suggested work around at the bottom entry [22 Jan 2010 6:46]:

replace 1.79769313486232e+308 \'1.79769313486232e+308\' -- filename

Give it a Try !!!


Windows Logs Application is full of "Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: ****]" I have Windows Server 2016, with SQL Server 2017, and found Windows Log Application is full of Login failed messages (as follows) :

  Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: ****]

Client's IP addresses are various. I don't have any maintenance plan in SQL Server

What's the problem?

A login attempt was made from the IP address given. The attempt was to log in using the credential sa. A password was supplied but it was not the correct password.

sa is the default sysadmin credential in SQL Server. Errorlog spam with this message usually means 1 of 2 things.

You have an application that was configured to use the sa crendential
You've made yourself a honeypot and a malicious actor has gained a pipe to your database server in an attempt to log in and take it over.

If it's #1, then go to the application you're hosting at that IP address and change its webconfig.

If it's #2, then you need to blacklist that IP address - preferably by firewalling your database server off from the public internet (which you should really do anyways to be frank).

See also:

sa best practices
Securing SQL Server


Identity proceeds as normal, but with a leading 10...? So I noticed a very strange thing when tinkering with my database.

I had just inserted a row which was ID 47, then after inserting some more, I realised that somehow my Identity had skipped all the way to 146, so it had skipped 999 to get there.

It's not a huge problem, or a gamestopper, just very strange, is there any way I can solve this? Short of backing up the data and recreating the table.

For the record, I didn't do anything at all between the data insertion - it seems to be totally random (of course it's not, something caused it I'm sure...)

This is a known behaviour difference in SQL Server 2012 because of a change in the way IDENTITY is implemented.

See related Connect Item for some discussion and possible workarounds (if this is a real issue for you)


Why is the candidate composite index not used? Consider the following table :
CREATE TABLE `entityusage_trackbyyear` (
  `track_id` binary(16) NOT NULL,
  `album_id` binary(16) DEFAULT NULL,
  `library_id` binary(16) DEFAULT NULL,
  `firstdateofinterval` date NOT NULL,
  `territory_id` binary(16) NOT NULL,
  `playbacks` int(10) unsigned DEFAULT 0,
  `userplaybacks` int(10) unsigned DEFAULT 0,
  `userdownloads` int(10) unsigned DEFAULT 0,
  `usersingledownloads` int(10) unsigned DEFAULT 0,
  PRIMARY KEY (`firstdateofinterval`,`track_id`,`territory_id`),
  KEY `album_id` (`firstdateofinterval`,`album_id`,`territory_id`),
  KEY `library_id` (`firstdateofinterval`,`library_id`,`territory_id`),
  KEY `territory_id` (`firstdateofinterval`,`territory_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And the following query :
SELECT
    BIN_TO_UUID(album_id) AS e,
    firstdateofinterval AS p,
    BIN_TO_UUID(territory_id) AS t,
    SUM(playbacks) AS c1,
    SUM(userplaybacks) AS c2,
    SUM(userdownloads) AS c3,
    SUM(usersingledownloads) AS c4
FROM
    entityusage_trackbyyear
WHERE
    firstdateofinterval >= '2018-01-01'
        AND firstdateofinterval <= '2018-09-30'
        AND library_id = UUID_TO_BIN('c72cbb1e-5848-39ee-a201-664f26ee9204')
GROUP BY p , e

Why does MariaDB insist on using the PRIMARY KEY index, and not the library_id index which is a composite index over both firstdateofinterval + library_id - which are the columns given in the query criteria?
When i explicitly set USE INDEX (library_id) it skips using an index entirely.
I must be doing something wrong!
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: entityusage_trackbyyear
         type: range
possible_keys: PRIMARY,album_id,library_id,territory_id
          key: PRIMARY
      key_len: 3
          ref: NULL
         rows: 1218204
       r_rows: 685956.00
     filtered: 100.00
   r_filtered: 0.13
        Extra: Using where; Using temporary; Using filesort

The UUID_TO_BIN and BIN_TO_UUID are functions used to convert UUIDs to / from the "human readable" representation to a compact binary(16) data type.

You have to FORCE the index, the optimizer will only then  use your wanted index, else you give it only a hint
SELECT
    BIN_TO_UUID(album_id) AS e,
    firstdateofinterval AS p,
    BIN_TO_UUID(territory_id) AS t,
    SUM(playbacks) AS c1,
    SUM(userplaybacks) AS c2,
    SUM(userdownloads) AS c3,
    SUM(usersingledownloads) AS c4
FROM
    entityusage_trackbyyear
    FORCE INDEX (territory_id)
WHERE
    firstdateofinterval >= '2018-01-01'
        AND firstdateofinterval <= '2018-09-30'
        AND library_id = UUID_TO_BIN('c72cbb1e-5848-39ee-a201-664f26ee9204')
GROUP BY p , e


Multiple primary keys in PostgreSQL I have the following table:

CREATE TABLE word(
word CHARACTER VARYING NOT NULL,
id BIGINT NOT NULL,
repeat INTEGER NOT NULL
);
ALTER TABLE public.word OWNER TO postgres;
ALTER TABLE ONLY  word ADD CONSTRAINT "ID_PKEY" PRIMARY KEY (word,id);

When I try to restore it using following command:

psql -U postgres -h localhost -d word -f word.sql

it gives me this error:

  multiple primary keys for table "word" are not allowed

How can I use multiple primary keys in postgres?

how can i use multiple primary keys in postgres ?

You can't. It's an oxymoron - the definition of a primary key is that it's the primary key, singular. You can't have more than one.

You can have multiple unique constraints. You can have a primary key that contains multiple columns (a composite primary key). But you can't have more than one primary key for a table.

However, the code you show doesn't produce the error you mentioned:

$ psql -U postgres regress <<__END__
CREATE TABLE word(
word CHARACTER VARYING NOT NULL,
id BIGINT NOT NULL,
repeat INTEGER NOT NULL
);
ALTER TABLE public.word OWNER TO postgres;
ALTER TABLE ONLY  word ADD CONSTRAINT "ID_PKEY" PRIMARY KEY (word,id);
__END__
CREATE TABLE
ALTER TABLE
ALTER TABLE
$

At a guess you've actually already defined this table and you're ignoring the previous errors, then showing only the last one. If I re-run this code I get the output:

ERROR:  relation "word" already exists
ALTER TABLE
ERROR:  multiple primary keys for table "word" are not allowed

The real error here is the first one, of course.

I strongly recommend always using -v ON_ERROR_STOP=1 in psql, e.g.:

$ psql -v ON_ERROR_STOP=1 -U postgres regress <<__END__
CREATE TABLE word(
word CHARACTER VARYING NOT NULL,
id BIGINT NOT NULL,
repeat INTEGER NOT NULL
);
ALTER TABLE public.word OWNER TO postgres;
ALTER TABLE ONLY  word ADD CONSTRAINT "ID_PKEY" PRIMARY KEY (word,id);
__END__

ERROR:  relation "word" already exists
$

See how it stops at the first error?

(It'd be the default but that'd break backward compatibility).


Finding "words" separated by commas in a single column with the CONCAT_WS() function I have a problem when using commas separating distinct values contained in a column. I call a query as follows:

$findd = KL123,OTHER123

$query = "SELECT *
            FROM test1
           WHERE concat_ws(' ', column1, column2) LIKE '%".$findd."%'
              OR concat_ws(' ', column2, column1) LIKE '%".$findd."%'";

So, I would like to search for words separated by commas with the following value $findd= KL123,OTHER123 in a table that contains the following data:

ID  name  column1    column2
--  ----  ---------  -------
 1  dan   KL123
 2  lolo  ASI22      OTHER123,KL145
 3  lala  TA123
 4  ttoo  OTHER123

But no results "found". I would like to "find" the IDs 1,2,4.

But if I search without commas the value $findd= OTHER123 works with the same data shown above, because I "get" the IDs 2,4

The question

How can I solve this task with commas?

My level in MySQL is very basic and I find it difficult to solve this.

WHERE FIND_IN_SET("LK123", CONCAT_WS(",", ...))

It does require the use of "comma" as the separator, and you cannot have commas in the strings.

https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_find-in-set

Or... FULLTEXT

ADD FULLTEXT(column1, column2)

WHERE MATCH(column1, column2) AGAINST("+KL123 +OTHER123" IN BOOLEAN MODE)

Yes, your app will need to construct this syntax.


Having values or some state in a database Suppose I have a table with several price values in it

ID Int, Primary Key, not null
AveragePrice Decimal, not null
MinPrice Decimal, not null
MaxPrice Decimal, not null

Each price value can be known and present (represented as a positive value) or it can be

infinite (without upper bound)
unknown
irrelevant
customer specific

There may be more even states.

How to model this in a database?

Currently these states are modelled as negative constants, e. g. a price of -10 means "unknown", -20 means "irrelevant". Since prices are normally positive this kind of works but I hope there exists a more elegant solution.

There is already a mechanism in SQL to flag missing information. It is NULL. It should be considered a flag indicating meta data about the corresponding column rather than a value of that column itself.

If NULL is too blunt & you need to distinguish various reasons for data being missing you can add your own flags. These will not have built-in support, of course. You will have to implement them as additional columns within the table and check them explicitly in code. You will have to think through the truth tables for your new flags so out handle them consistently in predicates and logic.

If you have to hold customer-specific prices then you will need a table with a customer key in it. Something like:

PriceID Int, Primary Key, not null
CustomerID Int, Primary Key, not null
AveragePrice Decimal, not null
MinPrice Decimal, not null
MaxPrice Decimal, not null


Locking issues under transaction on Postgres I recently got a "Out of shared memory - You might need to increase max_locks_per_transaction" error.

This is a long running process where I import a bunch of CSV files after proper processing.
I loop through them and open a transaction for each file, process it and then when I'm done with the file, close the transaction and move to the next file.

These files are not big, the biggest clocking in at around 12K lines.

The processing I do, as far as PG is concerned, amounts to doing a few SELECTs (a couple per line, no joins or anything) and then an UPDATE of one existing row (at most one per line).

The problem is, pg_locks fills up with transactions that have no relation, are of type transactionid and have a mode of ExclusiveLock.
I got as many as 20K rows like that in pg_locks and, besides the ones that, as expected, refer ti the two tables I'm using, an overwhelming majority looks like this (sorry, the indentation will be screwed up, I'm posting a link to pastebin too):

"locktype" "database" "relation" "page" "tuple" "virtualxid" "transactionid" "classid" "objid" "objsubid" "virtualtransaction" "pid" "mode" "granted" "fastpath"

"virtualxid" "" "" "" "" "11/18291" "" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "t"
"transactionid" "" "" "" "" "" "61840165" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "f"
"transactionid" "" "" "" "" "" "61843843" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "f"
"transactionid" "" "" "" "" "" "61833173" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "f"
"transactionid" "" "" "" "" "" "61835511" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "f"
"transactionid" "" "" "" "" "" "61846000" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "f"
"transactionid" "" "" "" "" "" "61838308" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "f"
"transactionid" "" "" "" "" "" "61832936" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "f"

http://pastebin.com/V4K3AvHD

Basically, none of those records have either a database or a relation, only a transaction id.

The only different row is the first one, which I take as coming from the transaction acquiring a lock on itself.

All the locks are being acquired by the same connection, and apparently only when executing the UPDATE, which really is no harder than

UPDATE t SET foo = COALESCE(foo, 0) + 23.4 WHERE bar = 'hey' and baz = 'ho'

and can affect at most one row.

When I'm done processing the file and commit the transaction, all those thousands of records just disappear from pg_locks, going back to a more normal number of between 15 and 30.
It's as if each UPDATE somehow acquired a lock, making the record count of pg_locks increase to the tune of one every one or two seconds.

I assume I must be doing something wrong, but I can't figure out what.

As a temporary workaround I indeed increased max_locks_per_transaction, but I would really like to actually solve the problem.
I don't think an increase of max_locks_per_transaction is warranted for my use case, which is quite simple.

Also, note that nobody else is accessing the two tables I'm using. Nobody is even accessing any table in the same schema.

I'm on Windows Web Server 2008 R2.

PostgreSQL 9.3.5, compiled by Visual C++ build 1600, 64-bit

Connecting with Npgsql 2.2.5

Can anyone shed some light?

I thought about possible indexing issues, but the table in question is not huge by any means (fewer than 50K rows), and doing a SELECT on it with the same criteria used by the UPDATE is very fast.

Thanks in advance.

It sounds like the code might be using SAVEPOINTs to handle errors, and not releasing the savepoints before proceeding. That would explain the large number of virtual xid locks.

RELEASE SAVEPOINT after you're done with a step.

You might also want to consider batching the work into smaller chunks, as the:

SAVEPOINT
Try it
ROLLBACK TO SAVEPOINT if it fails, RELEASE SAVEPOINT if it succeeds

pattern works, but has some performance costs that scale with number of savepoints in a transaction.

This applies to PL/PgSQL BEGIN ... EXCEPTION blocks too.


Can SQL Server 2014 Standard Edition Trial be upgraded to a full Core license? If I install a copy of SQL Server 2014 Standard Trial edition can that version be upgraded (in place without a full re-install) to the full version (core license) afterwards? I'd love to get this stuff installed and configured while I'm waiting for the licensing but I don't want to cause myself grief if it can't be easily licensed.

EDIT: More info, for anyone else that finds this question -

The Microsoft site doesn't specify anything beyond "SQL Server 2014" when you download the evaluation edition. But since they don't let you pick the specific edition I think Shanky's comment is correct (it is the Enterprise edition).

As Thomas pointed me to, here are the upgrade paths from SQL Server 2014 Evaluation Enterprise (since Microsoft loves moving/killing links):

SQL Server 2014 Evaluation Enterprise 2
SQL Server 2014 Enterprise (Server+CAL or Core License)
SQL Server 2014 Business Intelligence
SQL Server 2014 Standard
SQL Server 2014 Developer SQL Server 2014
Web Upgrading from Evaluation Enterprise (a free edition) to any of
the paid editions is supported for stand-alone installations, but is
not supported for clustered installations.

Do you mean upgrade from Evaluation to the full Enterprise?  If so, then yes that is supported.  Take a look at the link here on BOL to show supported edition upgrades (scroll towards the bottom, it has SQL Server 2014 upgrades between editions and what's possible).


Why is my postgres `base` directory filling up rapidly? My pg_xlog directory was filling up because my standby server was down. This brought the volume to 100% usage and shut down postgres. I brought up the standby servers, moved pg_xlog to another volume, symlinked it back into the data directory, and restarted the DB. DB volume was then at 43% without pg_xlog. The standby server got caught up, pg_xlog went from 4600 files to 1100 over a couple hours and I went to bed. Standby server went down overnight again. When I woke up, pg_xlog had 11k files and growing, and the base directory had filled up the volume with 66GB to 95%.

So, where the hell did all this new data in base come from overnight?

To generate 10k WAL files overnight, your database is obviously very busy.  It is not surprising that the non-WAL of such a busy database might also grow rapidly. Indeed, why wouldn't it?  If your database were that busy but not growing, that is the weird thing which would be in need of explanation, based on what you know about how your database is used.

Do you have hot_standby_feedback turned on?

If your database is still up, you can use these queries to figure out where all the space is going, although I usually just start with '\l+' and '\dit+' in psql (those ones don't sort by size, so if you have a lot of objects to scan through, the other ones may be better).  If your database is not up, you can use OS tools like 'du' and 'ls' to to figure out where the space is going.  But translating file names back to table names without a running database will not be easy.