QuasarDB cluster stuck in IOWAIT on AWS EC2 instances I am experiencing a system freeze in IOWAIT on my QuasarDB cluster.
I am continuously ingesting data at 10 GBit/s using the QuasarDB batch API. After a couple of hours, I have all the qdb threads in IOWAIT and the whole system is unresponsive.
Before the system becomes unresponsive, I observe a spike of activity.
When the qdb is in the IOWAIT state, the whole system seems frozen: I can log in, but copying files is extremely slow.
I initially thought that I was over my limit of IOPS, but I have 8,000 IOPS provisioned, and the spikes are below 1,000 IOPS.
The system is not paging out, and the volumes are not full.
The cluster is 4 AWS EC2 c5n.18xlarge nodes, data is stored on mounted EBS GP3 volumes.
Any clue about what I should look at to find the root cause of the problem would be welcomed.

(Disclaimer: I work at QuasarDB)
Because you are using the newer gp3 EBS volume types, and you are reporting a system-wide instability of the system (rather than just the QuasarDB process), I believe this is related to the EBS volume types, rather than something related to QuasarDB.
We have observed stability issues with gp3 volumes under high pressure ourselves, with a system getting stuck. To the best of our understanding, this is related to an EBS host-level driver. GP3 instability have been report by others as well.
What I would recommend is to use the older GP2 EBS volume types, and see if that solves the issue. If you would still like to achieve high-throughput, you can combine multiple GP2 EBS volumes using mdraid as recommended by AWS themselves.


Can a factless fact table be a bridge table? As for bridge tables, they can be a helper table both for facts and dimensions. But can the factless fact table be a bridge table?

Disclaimer, I'm not into DW, so I may have completely misunderstood the question. That said, a factless fact table sounds like an oxymoron, but I googled for it and according to Factless fact table | James Serra's Blog it's an intersection between two or more dimension tables. A bridge table is typically used to implement an n-n relationship:

CREATE TABLE d1
( d1key ... not null primary key
, ... )

CREATE TABLE d2
( d2key ... not null primary key
, ... )

CREATE TABLE f
( d1key ... not null references d1(d1key)
, d2key ... not null references d2(d2key)
,    primary key (d1key, d2key)
);

so I don't see any controversy in such table being both a bridge table and a factless fact table.


How to exclude the entire result of a Grouped row based on a value of any of the rows it contains I have a table that looks something like this:
 ID | Class | Item
 ---+-------+------
 1  | 1     | 1
 2  | 1     | 2
 3  | 2     | 1
 4  | 2     | 3
 5  | 3     | 2

and a query that looks something like this:
Select Class, MAX(Item) FROM myTable WHERE Item <> 3 GROUP BY Class

This results in:
  Class | Item
 -------+------
  1     | 2
  2     | 1
  3     | 2

However I want to exclude all results where a Class contains an Item with the value of 3, not just rows that are 3 so it should return:
  Class | Item
 -------+------
  1     | 2
  3     | 2

You can use something like this:
Select Class, MAX(Item)
FROM myTable
WHERE class not in (
         select class from #demo where item  = 3
                    )
GROUP BY Class


Dynamic Column Label in Postgres 9.5 I am using postgres 9.5 and have built the following function:

CREATE or REPLACE FUNCTION func_getratio_laglag(_numeratorLAG text, _n1 int, _denominatorLAG text, _n2 int, _table text)
    RETURNS TABLE (date_t timestamp without time zone, customer_code text, index text, ratio real) AS
$BODY$
BEGIN
 RETURN QUERY EXECUTE
        'SELECT
    date_t,
    customer_code,
    index,
        (LAG('||quote_ident(_numeratorLAG)||',' || quote_literal(_n1)||') OVER W / LAG('||quote_ident(_denominatorLAG)||','|| quote_literal(_n2)||') OVER W) '
         || ' FROM ' || quote_ident(_table)
         || ' WINDOW W AS (PARTITION BY customer_code ORDER BY date_t asc);';
END;
$BODY$ LANGUAGE plpgsql;

All the function does is allow me the ability to pick a 2 different columns from a specified table and calculate a ratio between them based on different lag windows. To execute the function above I use the following query:

Select * FROM func_getratio_laglag('order_first',1,'order_last',0,'customers_hist');

The output of this gives me a table with the column labels date_t, customer_code, index and ratio. I have really struggled on how to output ratio as a dynamic column label. That is, I would like to make it contingent on the input parameters e.g. if I ran the select query above then I would like the column labels date_t, customer_code, index and order_first_1_order_last_0.

I am stuck, any advice or hints?

How to derive a column name in the return type from input parameters to the function?

The short answer: Not possible.
SQL is very rigid about column data types and names. Those have to be declared before or at call time at the latest. No exceptions. No truly dynamic column names.

For a couple of possible workarounds see the answer to the duplicate question on SO:

How to derive a column name in the return type from input parameters to the function?


Closing bracket ']' in LIKE wildcard I have a query that looks a bit like this:

SELECT * FROM Stuff WHERE name LIKE '[a\]]%'

My goal is to match all names starting with a or ] (closed bracket). However, this seems to match everything starting with a] or \\].

Is this possible without OR, a lot of auto-generated LIKEs?

You have to escape the ].
That means before any character you want to literally search for, example % or _ or ], you have to add an escape character. For example \% to search for a literal %

Example:

CREATE TABLE #Test( text varchar(50) )

INSERT INTO #test  VALUES (']'),('atest'),('zz')

SELECT * FROM #test  WHERE text LIKE '[a\]]%' {escape '\'}

To read more about escape chars in LIKE, you can check :

LIKE Predicate Escape Character

You can also test it using SQL fiddle:
SQL Fiddle


Proper way to export database to other servers I am using SSMS (I am a software developer, not a DBA) and in each application release I need to release the database with its tables (some with data some with scheme only).

I tried doing 3 things (SSMS):

Using Database --> Tasks -> Generate scripts...
This is fine, for small databases, when the scripts is over 200 MB, users reports memory problems, both when running sqlcmd.exe and from SSMS.
Moreover, I have to remember to script triggers and I am never sure whether the scripts generate identical database.
I tried using the Back Up option but I guess this option is intended for backup and not export data, it prevents me from exporting only the scheme for example.
Back ups also keep histories for differential restores which is not what I intend.
I tried out Script Database as... CREATE To... but these doesn't keep triggers and data.
Moreover, this script use hardcoded paths to .mdf and .idf which changes from a server to server.

What is the correct approach to replicate a database to another server ?

P.S. I am new to this Stack Exchange website, I read several question but didn't find an answer after looking on several questions.

If you want the schema and the data, the best and safest way is a copy_only backup. Don't like the backup history that comes along in the MSDB tables? Ok, delete it, then start a new backup chain by performing a full backup once the copy is restored on the new server.

If you just want schema, there are plenty of options. I prefer RedGate's SQL Compare, but there are plenty of alternative tools, including SSDT which (I think with the most recent update) has schema compare functionality.


Mariadb 10.1.13 - log positions similar but "seconds behind master" is behind The setup I have is a master/slave replication, both running Mariadb 10.1.13.

On the slave I noticed that the Seconds_Behind_Master parameter is showing a delay due to some form of lagging, but the values for Master_Log_File and Relay_Master_Log_File are the same.

I have checked that the server IDs are different, however both have the same gtid_domain_id.

               Slave_IO_State: Waiting for master to send event
                  Master_Host: X.X.X.X
                  Master_User: repl
                  Master_Port: 3321
                Connect_Retry: 60
              Master_Log_File: binary_.005825
          Read_Master_Log_Pos: 140625151
               Relay_Log_File: mysql20-relay-bin.000145
                Relay_Log_Pos: 138885256
        Relay_Master_Log_File: binary_.005825
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 138884970
              Relay_Log_Space: 1214370105
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 10056
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 21
               Master_SSL_Crl:
           Master_SSL_Crlpath:
                   Using_Gtid: Slave_Pos
                  Gtid_IO_Pos: 0-20-34183339144
      Replicate_Do_Domain_Ids:
  Replicate_Ignore_Domain_Ids:
                Parallel_Mode: conservative

Any advice on solving the lag between my master/slave setup would be appreciated.
What could the root cause be?

140625151 and 138884970 are not that close.  But 10056 seconds is a nearly 3 hours.  What query is the Slave executing?  Maybe a long-running ALTER?

There have been cases where Seconds_behind_master bounces between 0 and some large value; is that happening?


How to give the least permissions for CRUD-managing and running SQLServerAgent jobs as well as Maintenance Plans? Follow up to my question "What's the owner of SqlServerAgent job for?"

Basically, I am trying to understand how to create a user-login with least privileges for CRUD-maintaining SQLServerAgent jobs based on SQL Server Maintenance plans.

I've just checked on SQL Server 2008 R2 that SQLAgentOperatorRole, which is the most privileged role among SQL Server Agent Fixed Database Roles, doesn't have any access (even to view):

the Management\Maintenance Plans

(as well as) to

Management\Data Collection,
Management\Resource Governor)

which makes it quiet useless for maintaining SQLServerAgent jobs baased on maintenance plans (my case) ...

So, how to create a user CRUD-maintaining and running SQLServer Agent jobs as well as CRUD-managing Maintenance Plans?

Also, I'd like to better understand the phrase from msdn "Give Others Ownership of a Job":

  Assigning a job to another login does not guarantee that the new owner
  has sufficient permission to run the job successfully

What does guarantee that non-sysadmin role user as owner of a job would have sufficient permissions to run an SQL Server Agent job successfully?

With regards to maintenance plans there is no CRUD implementation. As stated in BOL here:

  To create or manage Maintenance Plans, you must be a member of the sysadmin fixed server role.

You might be able to pull off setting up granular control by granting access to the stored procedures used to create maintenance plans (at the bottom of the article I linked above):

  sp_add_job

  sp_add_jobstep

  sp_add_schedule

  sp_attach_schedule

I would rethink the business need for this before going to that level. You have to understand that the objects used and permissions required for maintenance plans could change between major version, or even service packs and cumulative updates.

If there is a dire business need for this, I would likely create a dedicated account strictly for this and not grant the day-to-day user account the permissions. Then hope I was supporting Enterprise Editions only and use SQL Server Audit to track the usage of that account.

Though you also have to entrust the level of permissions to the individual you are providing it to, whether that means a training lesson prior to giving the account information away or even having your HR department come up with a security policy for privileged access. If I am not comfortable giving privileged access to someone you can be assured it will be voiced (by email) to management, letting them provide the finial decision (by email). I would then save off that little email as documentation on why I provided the access (for auditing purposes).


How to UPDATE MAX and MIN of a GROUP BY? I have a SELECT query of a table with GROUP BY

id     col1     category
1      some     1
2      x        1
3      another  1
4      x        2       // should be removed
5      some     2
6      x        2       // should be removed
7      some     3
8      some     3
9      x        4       // should be removed
10     some     4
11     x        4
12     some     4

I want to SELECT or UPDATE to remove a row if the first or last (with id order) is x (a given value). In other words, I want to skip the first and last row of a category IF col1='x'.

I cannot use JOIN as it is a TEMPORARY table.

SELECT * FROM test WHERE id IN
(SELECT MIN(id) FROM test GROUP BY category HAVING col='x'
UNION SELECT MAX(id) FROM test GROUP BY category HAVING col='x')

Fiddle


Hash index use case in MongoDB Recently MongoDB added hash index to its features but in the documentation doesn't explain much about hash index use case and its privileges to normal MongoDB index. So I'll be grateful if you guide me to know more about it.

Hashed indexes were added in MongoDB 2.4 (March 2013) specifically to support hashed shard keys. I'm not aware of any use case outside of sharding.

When choosing a shard key you can generally get the best outcome (appropriate balance of read/write locality) by defining your own compound index. However, an effective shard key requires an understanding of the attributes of your chosen field(s) (eg. cardinality, divisibility, randomness) as well as your application use case (common update/read queries). The field(s) included in your shard key index also need to be present in every document and cannot be changed after insertion.

Where there is no natural choice of shard key based on your data, a hashed shard key can be used to achieve a more uniform distribution of values which will help distribute writes across multiple shards. The field being hashed still needs to have good cardinality (i.e. large number of different values), so ObjectId values or timestamps work well. The downside of a hashed shard key is that it supports equality queries, but cannot be used for range queries since the values in the index are effectively distributed.

For example, the default _id (ObjectId) includes a leading timestamp component. While the _id field is unique, immutable, and present in every document .. an ObjectId is not a suitable choice for a shard key because the values are monotonically increasing. If you shard on an ever-increasing value like an ObjectId, all of the new inserts will target a single "hot" shard that currently has the highest shard key value. Documents will then have to be re-balanced to your other shards, which renders your sharding ineffective for scaling -- a poorly sharded collection will be limited by the write throughput of a single shard plus the overhead of frequent re-balancing of newly inserted data between shards.

For more information, see: Shard a Collection Using a Hashed Shard Key.


MYSQL DATABASE BACKUP Currently my backup strategy invloves :-

1)I have one master server and two slaves (Data approximately 700 GBs and tables are of mixed innodb and myisam).

2)I take weekly and monthly backup from slave through mysqldump.

3)In case of slave failure ,will copy data from another slave.

My question is  should i change this backup strategy ??
Should i use percona xtrabackup and if yes why ??

700GB is too much for mysqldump. Restore will take too much time (but I would measure to know numbers). XtraBackup will be faster.

Taking backup from a slave is OK, but you have to make sure the master and slave are in sync. Use pt-table-checksum to verify that.

And what is missing in your strategy is backups verification. If you don't verify backups you don't have backups.


Trigger shouldn't block insert I have a trigger on insert but if the trigger fails the insert fails too.

is there a way to let the insert proceed even if the trigger fails?

EDIT: I use a trigger to send a email when a new record is entered. I want the record to be saved regardless if the email was sent or not..

How would I do that from a sp?

No

A trigger is part of the transaction
An error will abort the INSERT statement (if not in an explicit transaction)
An error in a trigger will doom a transaction (that is, must be rolled back)
An error in a trigger where TRY/CATCH blocks are not used (around the INSERT too) will abort the batch

Don't use a trigger. Use a stored procedure instead to decouple the logic from the INSERT.

Personally, a trigger failing means some complex DRI has failed or I can't write to a history table. That is a bad thing...

Edit:

Use Service Broker to send an notification from the trigger.
Another piece of code will pick it up and send asynchronously

I haven't implemented myself so I'll have to refer you to Google


Mysql list data from left join query I have 2 table and i want to pull data from both of them.
This is sample for my table structure:

Table tbl_a = id, time, note, customer
Table tbl_b = id, time, name, surname, note

This is my mysql request:

Select * from tbl_a left join tbl_b on tbl_a.customer=tbl.b=id where tbl_a.id=5;

When i run this query directly from mysql i can see result exactly as i want but i dont know how to display it in my php page.

i am doing it like this:

$sql = mysql_query("Select * from tbl_a left join tbl_b on tbl_a.customer=tbl.b=id where tbl_a.id=5");
$data = mysql_fetch_array($sql);

echo $data[id]." - ".$data[time]." - ".$data[note]." - ".$data[customer]." - ".$data[id]." - ".$data[name]." - ".$data[surname]." - ".$data[note];

i know that column names that are repeated could not be used again but i dont know what the right way is.

I suggest instead of using the ALL (*). just type the column names with an AS attached such as: TableA.id AS TableAId,TableB.id AS TableBId and so on.


MySQL 5.6 upgrade - reducing code changes / developer burden Does anyone know the key things that should be done when moving to MySQL 5.6 to reduce the amount of code changes required? I found that new installations of it have STRICT_TRANS_TABLES set for sql_mode by default.

I plan to go through the release notes and upgrade guides on the MySQL site in more depth but during a past upgrade which was only a point-release change, there was a backwards incompatible change that wasn't called out in the release notes

I wanted to see if anyone had anything noteworthy to mention from experience.

I ran headlong into this one 6 months

The only sane thing you can do is the following two steps

STEP 01

Add this to my.cnf under the [mysqld] group header

[mysqld]
sql_mode = ''

STEP 02

Login as root@localhost and run this

mysql> SET GLOBAL sql_mode = '';

Restarting mysql is not required.

STEP 03 (Optional)

STEP 02 only affects incoming connections after the change. You may have to kill all current DB Connections. Let the app reconnect and it will begin with sql_mode as blank. While restarting mysql is not needed for incoming connections, you could just run service mysql restart instead of custom scripting the killing of DB Connections.

If you want to custom script the killing of your connections, please see some of my past posts for exmaples on how to kill many connections:

May 10, 2011 : kill all queries - MySQL
Jun 05, 2013 : Too Many database connections on Amazon RDS
Dec 25, 2013 : Mysql. Block/disconnect slow queries

STEP 04 (Optional, Last Resort)

If you would like to make code changes, the only change would be to run

SET sql_mode = '';

as the first command for the connection. Then, you could run your regular queries thereafter.

GIVE IT A TRY !!!

NOTE #1 : The first two steps are more than enough for your app if you close your connection right after running your query. If your connections are persistent, then you will need STEP 03.

NOTE #2 : If you are not allowed to change the configuration, you could skip STEP 01 and STEP 02 and just run STEP 03 and STEP 04.


MySql - How can I speed up this query I have the following tables:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `account_data` text COLLATE utf8_unicode_ci,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `twitter_username` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `crypted_password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password_salt` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `persistence_token` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `single_access_token` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `perishable_token` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `login_count` int(11) NOT NULL DEFAULT '0',
  `failed_login_count` int(11) NOT NULL DEFAULT '0',
  `last_request_at` datetime DEFAULT NULL,
  `current_login_at` datetime DEFAULT NULL,
  `last_login_at` datetime DEFAULT NULL,
  `current_login_ip` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_login_ip` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `is_admin` tinyint(1) DEFAULT '0',
  `referrer_id` int(11) DEFAULT NULL,
  `partner` tinyint(1) DEFAULT '0',
  `subscription_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT 'free',
  `workflow_state` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `persona_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `persona_index` (`persona_id`)
) ENGINE=InnoDB

and the table:

CREATE TABLE `user_actions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `action_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `module` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `data` text COLLATE utf8_unicode_ci,
  `timestamp` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id_index` (`user_id`),
  KEY `action_type_index` (`action_type`),
  KEY `user_action_type_index` (`user_id`,`action_type`),
  KEY `timestamp_index` (`timestamp`),
  KEY `user_id_timestamp_index` (`user_id`,`timestamp`)
) ENGINE=InnoDB

the problem is with the following query:

    SELECT user_actions.*, users.twitter_username, users.email FROM `user_actions`
INNER JOIN users ON (user_actions.user_id=users.id) ORDER BY timestamp DESC LIMIT 0, 30

here is the explain:

user_actions
The table was retrieved with this index: user_id_timestamp_index
You can speed up this query by querying only fields that are within the index. Or you can create an index that includes every field in your query, including the primary key.
Approximately 76 rows of this table were scanned.
users
This table was retrieved with a full table scan, which is often quite bad for performance, unless you only retrieve a few rows.
The table was retrieved with this index:
No index was used in this part of the query.
A temporary table was created to access this part of the query, which can cause poor performance. This typically happens if the query contains GROUP BY and ORDER BY clauses that list columns differently.
MySQL had to do an extra pass to retrieve the rows in sorted order, which is a cause of poor performance but sometimes unavoidable.
You can speed up this query by querying only fields that are within the index. Or you can create an index that includes every field in your query, including the primary key.
Approximately 3445 rows of this table were scanned.

this query takes a long time to execute, any ideas how to improve?

Here is your original query:
SELECT
    user_actions.*,
    users.twitter_username,
    users.email
FROM
    `user_actions`
    INNER JOIN users
    ON (user_actions.user_id=users.id)
    ORDER BY timestamp
    DESC LIMIT 0, 30
;

First thing I notice is that you are joining two whole tables. Since you only need twitter_username and email from the users table, you should only join from users using three columns : id, twitter_username and email.
Second thing is the LIMIT clause. It is being executed after the join. You should execute it before the join. In your case, you are requesting for the 30 most recent user actions. If you can guarantee that only 30 rows are retreived from user_actions, the join should operate much faster.
If you read the answer from @DTest, his first two bulletpoints already tell you what's wrong the query because of the actions mysql will take in gathering data from each table. The key is to understand what the temp tables will look like while the query is being processed and where the data will reside (memory or disk).
What you need to do is refactor the query to fool the MySQL Query Optimizer. Force the query to produce smaller temp tables. In most cases, config changes in my.cnf should make a dramamtic difference. In other cases, such as this one, refactoring the query may be sufficient.
Here is my proposed change to your query that should work faster:
SELECT
    ua.*,
    u.twitter_username,
    u.email
FROM
    (SELECT * FROM `user_actions`
    ORDER BY timestamp DESC LIMIT 30) ua
    LEFT JOIN
    (SELECT id,twitter_username,email FROM `users`) u
    ON (ua.user_id=u.id)
;

Here are reasons for refactoring the query:
REASON #1
If you look at inline table ua, I retrieve only 30 rows using LIMIT. This will happen no matter how big the user_actions table gets. It is already ordered because the ORDER BY timestamp DESC happens before the LIMIT.
REASON #2
If you look inline table u, it has id,twitter_username,email. The id is needed to implement the join.
REASON #3
I use LEFT JOIN instead of INNER JOIN for two(2) reasons:

Preserve the order of the query based on ua
Display all user actions in case the user_id in the ua no longer exists in the users tables.

Doing these things will force the temp tables to be smaller. Nevertheless, you will still need implement bulletpoint #3 from @DTest's answer to preempt having temp tables land on disk.


UPDATE waits for lock too long after SELECT in MySQL (Innodb) I have PHP function Pics::getPicture (yii2 ORM) which selects one row from table and then updates one field from this row. The table structure contains only id (PK), path (VARCHAR(500)) and seen (int(1)). Seen column is indexed.

My pseudo code:

SELECT * FROM pics WHERE id=:id LIMIT 1;
UPDATE pics SET seen=1 WHERE id=:id LIMIT 1;

And when client requests 10+ pictures in parallel (for different Id) then some UPDATE have unexpectedly hung for 1-10 seconds. I see Innodb_row_lock_waits is increased every time in this case.

Without UPDATE my code works very fast all time.

I've tried use transaction, UPDATE DELAYED (I'm not sure that I tried it correctly).

Are there some best practices for selecting and updating same rows? What additional information should I provide to clarify the question?

UPDATE 1 Server Configuration

Server: CPU: 1.5 Ghz, 1 Core, RAM 8Gb
Debian: 7.8
MySQL: 5.5.41

UPDATE 2

I've changed the SQL code to this but situation did't change. Also I removed INDEX for 'seen' column without any effects to this code.

BEGIN;
SELECT * FROM pics WHERE id=:id LIMIT 1 FOR UPDATE;
UPDATE pics SET seen=1 WHERE id=:id LIMIT 1;
END;

In mysql-slow.log I see next entries:

# Time: 150210  2:03:24
# User@Host: user[user] @ localhost [127.0.0.1]
# Query_time: 13.011485  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1423530204;
commit;
# Time: 150210  2:03:34
# User@Host: user[user] @ localhost [127.0.0.1]
# Query_time: 9.765468  Lock_time: 0.000037 Rows_sent: 0  Rows_examined: 1
SET timestamp=1423530214;
UPDATE `pics` SET seen=1 WHERE id=315 LIMIT 1;

UPDATE 3

Amount of rows in pics table are about 70.

SHOW CREATE TABLE pics;

CREATE TABLE `pics` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `image` char(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `archive` tinyint(1) NOT NULL DEFAULT '0',
 `seen` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `tbl_picture_timestamp` (`timestamp`),
 KEY `tbl_picture_archive` (`archive`),
 KEY `tbl_picture_seen` (`seen`),
) ENGINE=InnoDB AUTO_INCREMENT=319 DEFAULT CHARSET=utf8

SELECT COUNT(1) RowsReturned FROM pics WHERE id=315; (It took about 0.000035s)

RowsReturned 1

Perhaps you should impose a SELECT FOR UPDATE

SELECT * FROM pics WHERE id=:id LIMIT 1 FOR UPDATE;
UPDATE pics SET seen=1 WHERE id=:id LIMIT 1;

I have mentioned this before

Mar 08, 2013 : How are DB locks tied to connections and sessions?
Mar 12, 2013 : How I prevent deadlock occurrence in my application? (See MECHANISM #3)

UPDATE 2015-02-05 22:22 EST

There will always be row locks imposed, and there is no getting around it. Yet what is the difference between your code and my code ?

Think about it. Your running the SELECT in your code does not shield the rows from changes. Those row locks you are seeing would happen during the UPDATE. My code imposes the locks on rows and all corresponding index entries during the SELECT. This makes the UPDATE have less stress.

This is in agreement with the MySQL Documentation

  A SELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL UPDATE would set on the rows.

UPDATE 2015-02-10 22:05 EST

Your issue seems to be pointed out already by @RickJames: the index on seen field.

Take a look at the InnoDB Architecture (Picture From Percona CTO Vadim Tkachenko)

Please note two structures from the Picture

Insert Buffer With the InnoDB Buffer Pool (IB_IBP)
Insert Buffer With the System Tablespace (better known as ibdata1) (IB_SYSTBLSPC)

These structures are responsible for updating nonunique indexes.

Now, please note the effect of setting seen=1 on id = 315.

Assuming seen is either 0 or 1 (Cardinality of 2)

Having an index on seen with just 0 and 1 as values makes the index no different than managing two linked lists, each ordered by the PRIMARY KEY id.
Changing a 0 to 1 (or 1 to 0) will place information into IB_IBP
When it time to write the change to pics.ibd

Copy of the Changed Data Page is Written to the Double Buffer
Information Needed for Index Positioning is passed from IB_IBP into IB_SYSTBLSPC

The index entry of id 315 is moved from the side of the index where seen=0 to the other side of the index where seen=1

If you are changing the archive column in your benchmarking, these same machinations would also apply to the archive index.

In addition to the indexes being churned into and out of the InnoDB Buffer Pool, don't forget where this question started: ALL THE ROW LOCKS.

Keep something in minds about row locks. When a row lock is issued, an entire page can also be locked. The page can be the location of multiple primary key values. I have another posts where I discussed page locks (Dec 31,2012 : Queries getting stuck on very simple COUNT queries)

Please note in your comment, that you said

  And what is more important that in the most cases it works fast

Why not all cases ? Because of the reasons I just mentioned. @RickJames did mention first the seen index, and he is right to suggest removing that index. Along those same lines, I suggest removing the archive index as well (especially if the archive value is being changed in the benchmark). Without removing those indexes, look at all the stress you are imposing on the InnoDB Infrastructure even though the table has 70 rows. It would be far worse with thousands or millions of rows.


How to connect to a database in my vps from remote machine? I have installed MySQL on my VPS IP, and issued the following command:

mysql> GRANT ALL PRIVILEGES ON . TO 'root'@'%'IDENTIFIED BY 'passwd' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

service mysql restart

Now I want to log in to MySQL on my VPS IP from a remote machine.

mysql -h vpsip -P 3306 -u root -ppasswd

ERROR 2003 (HY000): Can't connect to MySQL server on 'vpsip' (111)

Why can't log in? How to connect to MySQL from a remote machine?

There could be several reasons why you cannot not connect:

The mysql server is not listening on the public interface. Make sure you are listening to the public ip by commenting the following lines (or setting bind-address to the corresponding public interface)

#skip-networking
#bind-address                   = 127.0.0.1

MySQL is not running in the 3306 port. Connect to the right one or reconfigure the port with:

port = 3306

You can check that mysql is listening in the fight port, right interface with:

$ netstat -tl

MySQL could not successfully start- easy to check, try to connect locally (using ssh) or check that it is running with:

$ pgrep mysqld
$ ps aux | grep mysqld

Your VPS or your provider may have a firewall activated on your server or on the datacenter, or running on a NAT, preventing incoming connections from outside of the server/network. You have to either disable the host network, connect to the public ip corresponding to the right private ip of the server or configuring the port in your control panel.

If for some reason you still cannot access mysql directly (or you do not want to expose the port publicly, which I recommend), you can tunnelize it using ssh, like this:

(localhost)$ ssh -N -L 3306:localhost:3306 user@vpsip
(localhost)$ mysql -h localhost -P 3306 -u root -ppasswd


Deny user to see any database through DB_NAME() I've already prevented user to see any database (deny view any database to [login]) and it works.

But how to prevent user to list any other database using DB_NAME()?

DB_NAME does not work as advertised before SQL Server 2016 (where the behaviour of DB_ID is also changed). For details, see:

Information disclosure with the db_name and db_id function (Connect bug report)

There is a similar situation with other metadata functions, including:

suser_name
suser_sname
suser_sid
user_id
database_principal_id
is_rolemember
is_srvrolemember

These issues remain unresolved (either by design, or by being 'fixed') as of the time this answer was written, as far as I know.


MariaDB is ignoring /etc/my.cnf The variable I am trying to change is innodb_buffer_pool_size. As you can see below the settings is 512M. But SELECT @@innodb_buffer_pool_size; returns 134217728.

When I execute: /usr/libexec/mysqld --help --verbose

My resulting config is:

Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf

/etc/mysql/my.cnf - does not exist
/etc/my.cnf - is shown below
~/.my.cnf - when I cd ~/ I am taken to /root where no my.cnf files exist. Does mysql run as root, would it be taken to the same directory? How can I tell?

Here is my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

skip-networking

innodb_thread_concurrency = 6
innodb_flush_log_at_trx_commit = 2
thread_concurrency = 12
table_cache = 1024
query_cache_size = 64M
query_cache_type=1
query_cache_limit = 4M
expire_logs_days=10
max_binlog_size=100M
join_buffer_size = 8M
tmp_table_size =256M
max_allowed_packet = 2M
thread_stack = 192K
thread_cache_size = 32
thread_concurrency = 4
table_open_cache  = 4096
myisam-recover=BACKUP
max_connections        = 2500
max_heap_table_size = 256M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
innodb_additional_mem_pool_size = 8M
innodb_log_buffer_size = 4M
innodb_log_file_size = 128M
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 0
innodb_buffer_pool_size = 512M
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:256M:autoextend
innodb_autoextend_increment=512
slow_query_log = Off
slow_query_log_file = /var/www/html/log/mysql/slow-queries.log
long_query_time = 2
innodb_file_per_table = 1

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[isamchk]
key_buffer              = 32M

I am also observing the same issues, I would suggest to please check the permissions to the mentioned path of my.cnf and the path mentioned by my.cnf.


Best way to create a materialized view in MySQL I am using MySQL 5.6. I am not being able to a create materialized view like I can in Oracle. I have seen one or two solutions like Flexview.

Can anybody tell me best way to create a materialized view in MySQL (auto refresh like in Oracle ) with the minimum of complexity?

Materialized Views Do Not Exist in MySQL.
Flexviews has been recommended in the DBA StackExchange before

Bill Karwin's post : Does MySQL have a version of Change Data Capture?
Redguy's post : Need some support on MySQL Query

Since you have it already, some due diligence and elbow grease on your part may be necessary to get going on using it (if you haven't already done so)
ALTERNATIVE #1
You might look into setting up Summary Tables. You are going to need to index your base tables in order to support aggregation before building Summary Tables.
ALTERNATIVE #2
Here is an old post from http://www.materialized.info/ which has a more down-to-earth strategy that looks intriguing. It involves using triggers. Looks like it could be fun ...
ALTERNATIVE #3
If your base tables are not that big and you can live with querying entire tables once a week, try the User Comments section of the MySQL Documentation on CREATE VIEW for ideas. Search for word materialized on that page.


PowerShell to get database schemas I am able to connect to a remote SQL Server with the instance name using PowerShell. I am using an XML file (something like a configuration file) which includes all my database details. PowerShell uses this for connection establishment.

Now I want to get all "schemas" associated with a particular database that is already defined in that XML file. How can I achieve that using Powershell scripting?

Any help would be really appreciated.

Assuming you have a valid instance of the SMO Server object defined as $ServerObject. The following code will allow you pull the names of all schemas in the database UserDatabase01:

$Database = $ServerObject.Databases | ? { $_.Name -eq "UserDatabase01" }
$Database.Schemas | Select Name

For more information on schema properties available through SMO, see the documentation for the Schema class. Unfortunately, the version of SMO for SQL 2005 is missing a lot of properties for this class. Newer versions are backwards compatible, you may want to look into installing a newer version:

SQL 2012 Feature Pack
SQL 2014 Feature Pack


Strange issue with mysql index cardiality I am facing a weird issue when trying to understand (explain) a query.

I have this table:

CREATE TABLE `trk_track` (
 `track_id` int(11) NOT NULL AUTO_INCREMENT,
 `track_date` date NOT NULL,
 `track_hour` int(11) NOT NULL,
 `track_createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `user_id` int(11) NOT NULL,
 `action_id` int(11) NOT NULL,
 `url_utm_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`track_id`),
 KEY `track_date` (`track_date`,`track_hour`),
 KEY `user_id` (`user_id`),
 KEY `action_id` (`action_id`),
 KEY `url_utm_id` (`url_utm_id`)
) ENGINE=InnoDB AUTO_INCREMENT=284538 DEFAULT CHARSET=utf8

There are about 200k lines in this table.

But something is very confusing:

1-) First the cardiality of url_utm_id index should be 200, but it shows '3' and after analyze table it shows '493', which is still wrong;

2-) When I try to run this query:

explain select count(track_id) from trk_track where url_utm_id in(185);

it shows:

id  select_type     table   partitions  type    possible_keys   key     key_len     ref     rows    filtered    Extra
1   SIMPLE  trk_track   NULL    ref     url_utm_id  url_utm_id  5   const   53  100.00  Using index

It is ok because there are only 53 url_utm_id(185) in trk_track.

But if I try to do:

explain select count(track_id) from trk_track where url_utm_id in(select url_utm_id from trk_url_utm where utm_id=102);

it shows:

id  select_type     table   partitions  type    possible_keys   key     key_len     ref     rows    filtered    Extra
1   SIMPLE  trk_url_utm     NULL    ref     PRIMARY,utm_id  utm_id  4   const   1   100.00  Using index
1   SIMPLE  trk_track   NULL    ref     url_utm_id  url_utm_id  5   db_trk.trk_url_utm.url_utm_id   577     100.00  Using index

This subquery:

select url_utm_id from trk_url_utm where utm_id=102

will return same '185' ID

But anyway it sounds like that it tries to make a join, look in explain and it scan 577 rows, it doesnt' make sense for me.

It should scan 53 also, shouldn't it?

1) cardiality in index of innodb table is  An estimate of the number of unique values in the index, MySQL Doc shows the details of it : https://dev.mysql.com/doc/refman/5.7/en/show-index.html

Change the innodb_stats_transient_sample_pages may get more accurate values, and https://dev.mysql.com/doc/refman/5.7/en/innodb-statistics-estimation.html may help you.

2) rows in explain shows the the number of rows MySQL believes it must examine to execute the query, it's also a approximation. So the value only means MySQL believes is should examine 577 rows, not examine 577 rows exactly.


can't set batched_key_access=on in optimizer_switch on mariadb 5.5 I am switching my application from a Mysql5.6 to a Mariadb5.5. I am support to add batched_key_access=on in optimizer_switch, but when I do that, mariadb refuse to start :

170104 17:16:14 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
170104 17:16:25 mysqld_safe Starting mysqld daemon with databases from /data/mysqldata
170104 17:16:26 [ERROR] /data/mysql/bin/mysqld: Error while setting value 'batched_key_access=off' to 'optimizer_switch'
170104 17:16:26 [ERROR] Aborting

When I read Mariadb Docs, I am supposer to set mrr=on which is what I do :

optimizer_switch='index_condition_pushdown=off'
optimizer_switch='mrr=on'
optimizer_switch='mrr_cost_based=off'
optimizer_switch='batched_key_access=on'

Am I doing something wrong?

Thank you for your help.

There is no batched_key_access key in MariaDB -- the functionality was released in MariaDB 5.3, long before MySQL 5.6, so it naturally had its own naming, from which MySQL diverged. What you might be looking for is join_cache_bka, but please read the page about block-based algorithms in full, as you also need to choose a suitable value for join_cache_level, depending on your needs.


mysqld - cannot connect to socket after changing datadir location I have this weird problem after changing the MySQL datadir location from default "/var/lib/mysql" It was working yesterday, but today I get "Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'," a typical message if the daemon is not running and it's looking for the socket file in the old datadir location.

I changed the my.cnf file and mysqld file to point to the new location, NEW_DIR.
The daemon runs, but I cannot open mysql.

Thanks in advance.

# cat /etc/my.cnf
[mysqld]
datadir=/NEW_DIR/mysql
socket=/NEW_DIR/mysql/mysql.sock

# cat /etc/init.d/mysqld | grep datadir
get_mysql_option mysqld datadir "/NEW_DIR/mysql"
datadir="$result"
get_mysql_option mysqld socket "$datadir/mysql.sock"

# /etc/init.d/mysqld start
Starting MySQL:                                            [  OK  ]

# tail mysqld.log
120203 11:12:20  mysqld started
120203 11:12:20  InnoDB: Started; log sequence number 0 43655
120203 11:12:20 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.77'  socket: '/NEW_DIR/mysql/mysql.sock'  port: 3306  Source distribution

# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

The problem is the mysql client program configuration.  You can specify the --socket=/NEW_DIR/mysql/mysql.sock command-line option or add it under the [mysql] section of your my.cnf file.

Please look at the documentation for specifying options through option files: http://dev.mysql.com/doc/refman/5.0/en/option-files.html

Since changing the socket path affects all the client programs, then it will make sense to update the global my.cnf and set the socket option under the [client] section.


How do I change 'localhost' to the *.*

That part of the question is not clear, since 'localhost' refers to a hostname, while '*.*' refers to all databases, all objects.

If you are wanting to give access to the username 'ssc' from any IP or host, not just 'localhost' -- that's what it sounds like -- then there are two different approaches.

First, though, you need to understand that an individual "user" in MySQL is the combination of username and host specification -- not just the username.  A user called 'ssc'@'%' would be a user with username 'ssc' who could log in from any host ('%' is the host wildcard, not '*'); however, that's not really the same "user" as 'ssc'@'localhost' as far as MySQL is concerned.

Technically, you could just update the grant tables, changing entries for that user to the wildcard hostname '%'.  These are in the mysql schema, tables named user, db, tables_priv, columns_priv, procs_priv, and (in newer versions) proxies_priv.

These can be updated with standard queries (e.g., UPDATE mysql.user SET host = '%' where host = 'localhost' AND user = 'ssc', and a similar query for the 'db' table).

After you update them you have to issue the FLUSH PRIVILEGES; statement to reload the already-in-memory privilege structures from the tables.

The other approach, of course, is the DROP USER to remove the old user and GRANT the desired privileges to the new user.  This can be accomplished even if you don't know the user's password, because the IDENTIFIED BY {password} clause in the GRANT statement will also accept an already-encrypted password (which you can get from the mysql.user table).

The potential problem you'll have with both of these approaches is the way some objects have a DEFINER user, that establishes the privileges associated with the object, no matter who is actually using the object at the time.  Stored programs (triggers, procedures, functions, and events) and views are all affected by this.  Base tables and databases themselves are not.

Since 'ssc'@'localhost' and 'ssc'@'%' (or another other host spec) are not really "the same user," it follows that making the changes using either method above means you will have to identify and then redefine any objects the previous user has defined in the database, using a new DEFINER declaration (or allowing the definer to be set implicitly) ... or they will not work, once you remove or redefine the user.

As an example, here's a view that was created by user 'hr2'@'foo'.  I'll drop that user, then examine the view.

mysql> drop user hr2@foo;
Query OK, 0 rows affected (0.00 sec)

mysql> show create view hr2v;
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View                                                                                | character_set_client | collation_connection |
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| hr2v | CREATE ALGORITHM=UNDEFINED DEFINER=`hr2`@`foo` SQL SECURITY DEFINER VIEW `hr2v` AS select `test`.`t1`.`id` AS `id` from `t1` | utf8                 | utf8_general_ci      |
+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set, 1 warning (0.01 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------------------+
| Level | Code | Message                                                         |
+-------+------+-----------------------------------------------------------------+
| Note  | 1449 | The user specified as a definer ('hr2'@'foo') does not exist |
+-------+------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

Note that I am logged in with SUPER privilege, but now that I've dropped the user who was DEFINER of the view, I am prevented from opening that view.

mysql> select * from hr2v;
ERROR 1449 (HY000): The user specified as a definer ('hr2'@'foo') does not exist

Aside: if you end up in a circumstance where this comes up, the first fix is to simply re-create the user.  Unlike in some systems where a user has a hidden identifier beyond their visible username, MySQL treats the recreated user as identical, since it's just matching strings.

But you can prevent this problem by verifying that no objects are associated with the user you'll be removing, or by finding them and fixing them first.

SELECT * FROM information_schema.views WHERE definer ='user@host';
SELECT * FROM information_schema.triggers WHERE definer ='user@host';
SELECT * FROM information_schema.routines WHERE definer ='user@host'; # stored procs and functions
SELECT * FROM information_schema.events WHERE definer ='user@host';

There are two important notes here, also.  First, note that the quoting is different.  Instead of 'user'@'host' these are expressed as 'user@host' (or 'user@%').  Also, unlike the grant tables, which really are tables, the information_schema "tables" are not tables at all, but rather representations of server internals, queryable via the SQL interface... and as such, they cannot be updated.  Each object you identify has to be dropped and redefined so its security context will be associated with a valid user, at which time the DEFINER shown in the respective table in information_schema will show the new values also.


MySQL Deterministic Procedures As a general rule, should I be declaring all my stored procedures with the DETERMINISTIC keyword if they are indeed deterministic?

It seems to me that the majority of stored procedures will be deterministic. Am I right in thinking that the only non-deterministic procedures are ones that call non-deterministic functions like RAND() or CURDATE()?

Anyway, the reason I'm asking is because when I use the Data Restore feature within MySQL Workbench I get this error:

ERROR 1418 (HY000) at line 1209: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

Is it better practice to simply set the 'log_bin_trust_function_creators=1' instead? Bear in mind that I have 50+ stored procedures.

It makes sense to use the deterministic keyword for procedures that are really deterministic, because:

  Declaring a deterministic routine as NONDETERMINISTIC might diminish
  performance by causing available optimizations not to be used.

But:

  Am I right in thinking that the only non-deterministic procedures are
  ones that call non-deterministic functions like RAND() or CURDATE()?

No, a procedure that reads data from the database and bases it's result on that data (in addition to it's inputs) is also non-deterministic (because that data might change between calls).

It is worth pointing out that whether a procedure is DETERMINISTIC or not needs to be considered in a replication context:

  When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise, it may be unsafe for data recovery or replication.

although:

  If row-based or mixed binary logging is in use, the statement is accepted and replicated even if the function was defined without the DETERMINISTIC keyword.


XQuery doesn't update XML data I am trying to run the script to update an XML column:

UPDATE DataImpTable
SET serviceData.modify('replace value of (/SMObjInfo/CentralData/SMData/CentralSDItem/ControlData/text())[1] with "9876"')
WHERE identifier=5

<SMObjInfo xmlns="DataService/1.0.0.0" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
  <CentralData>
    <SMData>
      <CentralSDItem>
        <ControlData>1234</ControlData>
      </CentralSDItem>
    </SMData>
  </CentralData>
</SMObjInfo>

Change value of ControlData to 9876 but the value doesn't seem to change in the XML for the /SMObjInfo/CentralData/SMData/CentralSDItem/ControlData value.

Is it anything to do with typed and untyped XML?

You'll need to declare the namespaces in the modify function.

Something like this:

DECLARE @xml xml = N'<SMObjInfo xmlns="DataService/1.0.0.0" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
  <CentralData>
    <SMData>
      <CentralSDItem>
        <ControlData>1234</ControlData>
      </CentralSDItem>
    </SMData>
  </CentralData>
</SMObjInfo>';
SET @xml.modify('
    declare default element namespace "DataService/1.0.0.0";
    replace value of (/SMObjInfo/CentralData/SMData/CentralSDItem/ControlData/text())[1]
    with "6789"
    ');
PRINT CONVERT(nvarchar(max), @xml);

In your original xml fragment, you have declared the following namespace that is never used:

xmlns:i="http://www.w3.org/2001/XMLSchema-instance"

If your actual xml documents do make use of this namespace, and you want to modify those elements, you'd need to add the following declaration into the @xml.modify function:

declare namespace i="http://www.w3.org/2001/XMLSchema-instance";

Results (formatted for readability):

<SMObjInfo xmlns="DataService/1.0.0.0" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
    <CentralData>
        <SMData>
            <CentralSDItem>
                <ControlData>6789</ControlData>
            </CentralSDItem>
        </SMData>
    </CentralData>
</SMObjInfo>

I wrote a blog post about the modify function, along with some more examples at SQLServerScience.com


Postgres: Missing data after Binary Copy - Is VACUUM FULL required? I'm running Postgres 9.0 on a relatively big (10TB) and old Database. I must move that database to new server hardware, so I copied the whole $PGDATA directory via rsync of a LVM Snapshot and used archive/restore Commands to get all the changes after the copy process. I switched to the new server using the trigger_file.

Everything looked good so far, but after a few hours I'm having some tables where data is not showing up in SELECTs (I think that was instant after copying, but have no evidence).

I did ANALYZE and VACUUM on the table - no data there. But when I do a VACUUM FULL on the table, the data appears.

I couldn't find anything comparable in the postgres documentation except the Transaction Id wraparound. The autovacuum was disabled for 1/2 year at the old server.

A week ago I activated it and autovacuum did its job as I saw.

On the old database server, the data from my test table is is returned by SELECTs.

Did anyone encounter such a problem? I'm looking for a query that can show me which of the tables needs such a vacuum full.

Why cannot I copy the data directory at the file system level is also hard to understand for me at the moment. Did I miss anything from the documentation that needs to be done after using the Trigger File?

I don't think it's relevant, but the version at the old server was 9.0.18 and at the new one 9.0.23.

To answer myself.

It was somehow related to  https://www.postgresql.org/message-id/flat/111D0E27-A8F3-4A84-A4E0-B0FB703863DF%40s24.com#111D0E27-A8F3-4A84-A4E0-B0FB703863DF@s24.com i think.

The OS changed from Debian7 to Debian8, and so the dynamically linked libc6 changed.
Had to do a REINDEX on all my Tables with Indexed String Columns.


Grouping Based on Column Consecutive Values I have table with let say the below 3 columns

Date                    OfferId         Title
4/29/16 - 5/19/16       123             title1
5/20/16 - 5/30/16       123             title2
5/31/16 - 6/2/16        123             title3
6/03/16 - 6/13/16       123             title1
6/14/16 - 6/16/16       123             title4
6/17/16 - 6/20/16       123             title2
6/21/16 - 6/25/16       123             title4

i have some other columns which need sum

Where i put range like this 4/29/16 - 5/19/16 it means i have multiple rows for those dates with the same title.

as you can see the

title1 is same between 4/29/16 - 5/19/16 and 6/03/16 - 6/13/16
title2 is same between 5/20/16 - 5/30/16 and 6/17/16 - 6/20/16
title4 is same between 6/14/16 - 6/16/16 and 6/21/16 - 6/25/16

So when we do grouping on Title so what it does is return 4 results

select Min(Date),OfferId, Title from temp group by Title;

using the above query the results will be

Min(Date)   OfferId         Title
4/29/16     123             title1
5/20/16     123             title2
5/31/16     123             title3
5/31/16     123             title4

what i wanted is it should be look like this so i can see all the history the title got changed.

Min(Date)   OfferId         Title
4/29/16     123             title1
5/20/16     123             title2
5/31/16     123             title3
6/03/16     123             title1
6/14/16     123             title4
6/17/16     123             title2
6/21/16     123             title4

How can I achive this

SELECT (@prev = Title) AS same,
       Title,
       `Date`,
       @prev := Title AS t
    FROM ( SELECT @prev := '' ) AS Init
    JOIN ( SELECT `Date`, Title FROM tbl ORDER BY `Date` LIMIT 999999 ) AS x
    HAVING same = 0

See if that gets the right info, plus some junk.  Then get rid of the junk:

SELECT `Date`, Title
    FROM ( the entire query above ) AS y
    ORDER BY `Date`


Why does this MySQL 5.6 stored procedure not compile with MySQL Workbench? Why doesn't this MySQL 5.6 stored procedure compile with MySQL Workbench?

CREATE PROCEDURE spDataLocationSetActive
(
          IN driveLetter    varchar(1),
          IN active         tinyint(1)
)
BEGIN

if(      Select  DriveLetter
                        From    DataLocations
                        Where   DriveLetter = driveLetter      )
then

                UPDATE
                        DataLocations
                SET
                        Active = 1
                WHERE
                        DriveLetter = driveLetter;
end if;

END

'DriveLetter', 'varchar(128)', 'NO', 'PRI', '', ''
'Directory', 'varchar(200)', 'NO', '', 'SCR\\DATA\\', ''
'MaxUsage', 'smallint(6)', 'NO', '', '95', ''
'Sequence', 'smallint(6)', 'YES', '', NULL, ''
'ErrorMessage', 'varchar(255)', 'YES', '', NULL, ''
'DriveCapacity', 'bigint(20)', 'YES', '', NULL, ''
'DriveFreeSpace', 'bigint(20)', 'YES', '', NULL, ''
'Active', 'tinyint(1)', 'NO', '', '1', ''

Here is the result  of Show Create Table DataLocations.

Table,"Create Table"
DataLocations,"CREATE TABLE `DataLocations` (
  `DriveLetter` varchar(128) NOT NULL DEFAULT '',
  `Directory` varchar(200) NOT NULL DEFAULT 'SCR\\DATA\\',
  `MaxUsage` smallint(6) NOT NULL DEFAULT '95',
  `Sequence` smallint(6) DEFAULT NULL,
  `ErrorMessage` varchar(255) DEFAULT NULL,
  `DriveCapacity` bigint(20) DEFAULT NULL,
  `DriveFreeSpace` bigint(20) DEFAULT NULL,
  `Active` tinyint(1) NOT NULL DEFAULT '1',
  `InactiveCount` smallint(6) NOT NULL DEFAULT '0',
  `LastInactiveDateTime` datetime NOT NULL DEFAULT '2001-01-01 00:00:00',
  `Enabled` tinyint(1) NOT NULL DEFAULT '1',
  `LastEnabledDateTime` datetime NOT NULL DEFAULT '2001-01-01 00:00:00',
  PRIMARY KEY (`DriveLetter`),
  KEY `IX_DataLocations_DriveLetter_Active_Enabled`      (`DriveLetter`,`Active`,`Enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1"

and here is the  insert statement

INSERT INTO `scdvr`.`DataLocations`
(`DriveLetter`,
`Directory`,
`MaxUsage`,
`Sequence`,
`ErrorMessage`,
`DriveCapacity`,
`DriveFreeSpace`,
`Active`,
`InactiveCount`,
`LastInactiveDateTime`,
`Enabled`,
`LastEnabledDateTime`)
VALUES
('/',
'SCDVR/',
85,
1,
15,
80000000000,
1,
0,
'2001-01-01 00:00:00',
1,
'2001-01-01 00:00:00');

I was wondering how to fix its syntax. Thank you.

You have to put DELIMITER $$ at the beginning of MySQL script file.

DELIMITER $$
CREATE PROCEDURE spDataLocationSetActive
(
          IN driveLetter5  varchar(128),
          IN active         tinyint(1)
)
BEGIN
DECLARE J INT;
Select count(*) INTO J
                        From    DataLocations dl
                        Where   dl.DriveLetter = '/'  LIMIT 1;

IF (J IS NULL) THEN

        SELECT dl.Enabled FROM DataLocations dl;
ELSE
        SELECT dl.Enabled FROM DataLocations dl;
END IF;

END


Moving mysql database to new partition with different partition "type" Sorry, Possibly a dumb question, but something which needs clarification.

Currently mysql is stored on sda which is around 1TB. Now our DB has outgrown that, almost !!. So trying to create new partition of 3 TB so that we can move DB to new partition.

Current sda is partition type 83 (aka Linux)

We added new sdb partition of 3 TB but creating partition type with "linux" can only go max up to 2 TB.

My question is, does it matter if we move SQL files to "new" partition with different partition "type" ? e.g. 85 (linux Extended, which can go up to max size of 16TB)

You could omit partitioning /dev/sdb and just put a filesystem on it.

mkfs.xfs -L MYSQLDATA  /dev/sdb

(other filesystems like ext3/4 will also work).

And in /etc/fstab

LABEL=MYSQLDATA /var/lib/mysql  xfs    defaults 1 2

To migrate:

systemctl stop mysql.server
mount /dev/sdb /mnt
chown mysql: /mnt
mv /var/lib/mysql/* /mnt/
umount /mnt

Then use:

mount /var/lib/mysql
restorecon -Rv /var/lib/mysql # if you have selinux
systemctl start mysql.service


Prevent reset of auto_increment id in Innodb database after server restart I recently read that because of how InnoDB recalculates the AUTO_INCREMENT value when the server restarts, any records on the high end of the ID list may have their IDs reused.

Normally, this isn't a problem, because when a user is deleted everything associated with the ID is deleted from other tables too.

But I'm deliberately leaving their forum posts orphaned, labelled as "Posted by =User #123=", so that past conversations are retained. Clearly, should an ID be reused, this will be a problem.

I've never had this issue before because there were always enough new users to make it unlikely for an ID to be reused in this way. However on my new project signups are rare and inactive user deletions frequent (especially since the "Open Alpha" accounts only last for three days as a preview), and such ID reuse has happened three for three now.

I have "fixed" the issue by saving the correct value for AUTO_INCREMENT elsewhere and using that instead of relying on the internal value. Is there an actual way to have InnoDB remember the actual last value?

(avoiding the issue by never deleting)

Since you want to keep the "Posted by =User #123=" information after you delete the user with id=123, you could also consider using 2 tables for storing users data. One for Active users  and one for all (including deleted ones from the active users). And never delete those ids from the AllUser table:

CREATE TABLE AllUser
( user_id INT AUTO_INCREMENT
, ...
, PRIMARY KEY (user_id)
) ;

------
--- Forum posts FK should reference the `AllUser` table

CREATE TABLE ActiveUser
( user_id INT
, ...
, PRIMARY KEY (user_id)
, FOREIGN KEY (user_id)
    REFERENCES AllUser (user_id)
) ;

------
--- All other FKs should reference the `ActiveUser` table

This will of course complicate the insert new user operation. Any new user will mean 2 inserts, one in each table. Deleting a user though will be by deleting from the ActiveUser table only. All FKs will be deleted with cascading, except the forum posts, which will be referencing the Alluser table (where no deleting will ever happen).


How to set ADR Base directory correctly on OracleDB on Linux? Installed Oracle Database on Linux based OS. Listener is ok. But Oracle is not. (I changed all angular brackets to vertical, because parser not shows angular brackets)

  ORA-48108: invalid value given for the diagnostic_dest init.ora parameter
  ORA-48140: the specified ADR Base directory does not exist
  [/volumeX/app/oracle/product/11.2.0/xe/dbs/|ORACLE_BASE|]
  ORA-48187: specified directory does not exist

Thats line in initXE.ora and init.ora:

  diagnostic_dest='|ORACLE_BASE|'

Result of echo $ORACLE_BASE:

  /u01/app/oracle

u01 is a symlink of volumeX directory.

How to solve this problem?

It is on Linux DiskStation 3.2.40.

Just set:

diagnostic_dest=/u01/app/oracle

or

diagnostic_dest=/volumeX/app/oracle

The error message you receive indicates that the value for this parameter is currently stored in a format that the database can not interpret. When this happens, the database will search for this directory in $ORACLE_HOME/dbs as seen below the ORA-48140 line.

Or you can just simply delete this line, and the parameter value will default to $ORACLE_BASE.


Multiple "GROUP_CONCAT"s with WHERE clause In a table as

id    name      type           info
1     BMW       car            yes
2     Reno      car            no
3     IBM       electronics    no
4     Sony      electronics    yes
5     Mazda     car            yes

I use GROUP_CONCAT to get the list of each type, but I want to separate the concatenated column to multiple columns categorized by the column info. It should be something like

SELECT type,
       GROUP_CONCAT(name) ORDER BY id ASC SEPARATOR ' ') AS list_with_info
       GROUP_CONCAT(name) ORDER BY id ASC SEPARATOR ' ') AS list_without_info
       FROM table1 GROUP BY type

How can I introduce WHERE clause or using other approach to return multiple concatenated columns?

SELECT type,
       GROUP_CONCAT( CASE WHEN info = 'yes' THEN name ELSE NULL END
                     ORDER BY id ASC SEPARATOR ' ') AS list_with_info,
       GROUP_CONCAT( CASE WHEN info = 'no' THEN name ELSE NULL END
                     ORDER BY id ASC SEPARATOR ' ') AS list_without_info
FROM table1
GROUP BY type ;

Tested at SQL-Fiddle: test-1

If you wanted the results in two rows instead of one, it would be easier, just GROUP BY both type, info:

SELECT
    type, info,
    GROUP_CONCAT( name ORDER BY id ASC SEPARATOR ' ')
      AS list
FROM table1
GROUP BY type
       , info ;

This can also be used to provide the one-row-per-type format you want:

SELECT
    type,
    MIN( CASE WHEN info = 'yes' THEN list END )
      AS list_with_info,
    MIN( CASE WHEN info = 'no' THEN list END )
      AS list_without_info
FROM
    ( SELECT
          type, info,
          GROUP_CONCAT( name ORDER BY id ASC SEPARATOR ' ')
            AS list
      FROM table1
      GROUP BY type
             , info
    ) AS grp
GROUP BY type ;

Tested at SQL-Fiddle: test-2

The above two queries would benefit from an index on (type, info, name)

The following would benefit from an index on (info, type, name):

SELECT
    dt.type,
    grpy.list  AS list_with_info,
    grpn.list  AS list_without_info
FROM
    ( SELECT DISTINCT type
      FROM table1
    ) AS dt
  LEFT JOIN
    ( SELECT
          type,
          GROUP_CONCAT( name ORDER BY id ASC SEPARATOR ' ')
            AS list
      FROM table1
      WHERE info = 'yes'
      GROUP BY type
    ) AS grpy
      ON grpy.type = dt.type
  LEFT JOIN
    ( SELECT
          type,
          GROUP_CONCAT( name ORDER BY id ASC SEPARATOR ' ')
            AS list
      FROM table1
      WHERE info = 'no'
      GROUP BY type
    ) AS grpn
      ON grpn.type = dt.type ;

Tested at SQL-Fiddle: test-3


ORA-00845 after deleting files accidentally at /dev/shm I have two instances (ORA1 and ORA2) of Oracle 11g at the same machine. One is starting the other cannot startup anymore.

What happend:

First my disk was full, so I accidentally deleted all ora*-files under /dev/shm. After deleting other files I had enough disk space to start instance ORA2. But when I tried to start ORA1, I get ORA-00845: MEMORY_TARGET not supported on this system

I cannot even start ORA1 in nomount phase:
    SQL> startup nomount;
    ORA-00845: MEMORY_TARGET not supported on this system

ORA1 and ORA2 should be configurated equally. /dev/shm has 2G and following parameter are set in ORA2:

SQL> show parameter target

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 0
memory_target                        big integer 0
parallel_servers_target              integer     32
pga_aggregate_target                 big integer 382M
sga_target                           big integer 1152M

What needs to be done? Any hints or explanation appreciated.

Thanks in advance!

As described in https://www.krenger.ch/blog/ora-00845-memory_target-not-supported-on-this-system/ and suggestion of MarcoBaldelli /dev/shm just needs to have enough space. So the solution was to increase /dev/shm:

mount -o remount,size=4G /dev/shm

When MEMORY_TARGET is set to 0 (as in my case) it seems to be as following (see Oracle Documentation):

For the MEMORY_MAX_TARGET initialization parameter, decide on a maximum amount of memory that you would want to allocate to the database for the foreseeable future. That is, determine the maximum value for the sum of the SGA and instance PGA sizes. This number can be larger than or the same as the MEMORY_TARGET value that you chose in the previous step.


Querying a SQL Server's CNAME alias instead of the actual instance name We don't have a DBA here and our infrastructure analyst is trying his best to give us a test instance on a server using a CNAME alias which neither he nor I have done before.

The actual database instance being used is called AN-UATSQL03.domain.com but it has been set up with a host name of [ALT-STG-SQL-ATLNTA1].

Connection on management studio requires a server name of [ALT-STG-SQL-ATLNTA1\DWH] to work. The underlying server being used does have an instance of \DWH but this wasn't needed in the DNS setup, however we can only connect if the instance part of \DWH is added on at the end. That's one weird occurrence.

Right now, if we query on this new instance like so;

SELECT *
FROM [ALT-STG-SQL-ATLNTA1\DWH].[Database].[dbo].[Column]

It doesn't work, even though on Management studio we connected to ALT-STG-SQL-ATLNTA1\DWH as the Server name. It's like the alias isn't recognised in the catalogue and the actual instance that the alias is pointed to in the DNS is what the Query also wants to have referenced.

While [server] in a SQL query from within that same server isn't really necessary, we do that in cases where we are joining across linked servers to resources that have very similar structures, so we know which is which.

We're out of our depth here really in trying to make this work. We need to test the migration of objects from an existing SQL server to this new one via the UAT server that the host name points to in the DNS. We wanted to be able to test it, if it works we can migrate to a live server after altering any SQL code to use the new host name of [ALT-STG-SQL-ATLNTA1] for the server which would stay the same.

Any advice would be so much appreciated!

The reason the CNAME is not working is you are using a named instance.  When you specify the name of the instance as in SERVER\INSTANCE the SQL Server client looks up the port number of the instance via the SQL Server Browser service.  Because you are using a name that is different from the actual SQL Server instance name, that lookup fails.

Instead of connecting via AN-UATSQL03\DWH, try connecting directly using the port number, as in:

tcp:AN-UATSQL03,xxx

Where xxx is the port number.  You can get the port number either from SQL Server Configuration Manager, or via this query:

SELECT SPID = @@SPID
    , InstanceName = @@SERVERNAME
    , InstancePortNumber = CONVERT(varchar(255), local_tcp_port)
    , dec.auth_scheme
    , USER_NAME = USER_NAME()
    , SUSER_SNAME = SUSER_SNAME()
    , dec.connect_time
FROM sys.dm_exec_connections dec
WHERE dec.session_id = @@SPID;

You'll need to ensure the port number is not being assigned dynamically; use SQL Server Configuration Manager to set it to a manually configured TCP/IP port.  These Docs should help you out.

This query in your question implies you have a SQL Server linked server setup:

SELECT *
FROM [ALT-STG-SQL-ATLNTA1\DWH].[Database].[dbo].[Column]

If you only want to access the DWH instance via that linked server, you can simply define the linked server using the "real" name, but name the linked server object the aliased name.  Something like this should work:

EXEC master.dbo.sp_addlinkedserver @server = N'AN-UATSQL03\DWH'
    , @srvproduct=N''
    , @provider=N'SQLNCLI'
    , @provstr=N'SERVER=ALT-STG-SQL-ATLNTA1\DWH;'
    , @catalog=N'tempdb';

That would allow you to reference the linked server as [AN-UATSQL03\DWH] while actually connecting to [ALT-STG-SQL-ATLNTA1\DWH].  So your sample query would be:

SELECT *
FROM [AN-UATSQL03\DWH].[Database].[dbo].[Column]


Get a list of databases that have a certain table I'm trying to get a list of databases from a Postgresql instance that contains a certain table.  I've started using the system catalog tables to try and figure this out.  I was trying to use pg_database, pg_class, pg_tables but I couldn't seem to find a way to link pg_class/pg_table back to pg_database to get the database for a given table.

If anyone has any ideas please let me know.  It's probably just something stupid that I'm misising.

Thanks in advance.

This is not possible by a single query, because queries cannot span multiple databases, and the list of tables is only contained within the individual databases.


Should all machines which run mongo instances (mongod or mongos) in a Mongo DB cluster have unique hostnames (also at OS level)? I have been using Mongo DB Cluster for some data analysis (Mongo DB version 2.4.5) and while inserting some data into the cluster, I found an error which is the same as mentioned in the bug : https://jira.mongodb.org/browse/SERVER-10578

My configuration is as follows:

1) Node1 - mongoconfig server, mongod shard

2) Node2 - mongoconfig server, mongod shard

3) Node3 - mongoconfig server, mongos

4) Node4 - mongos

I changed the OS hostnames of all the machines and made sure that hostnames specified in the DNS server and OS hostnames are the same. After that, the problem got solved.

But now I have a requirement that I need to add mongos instances (nodes/machines) on the fly which will have different IP addresses but the same hostname (both at the OS level and in DNS entries). I will still continue to have unique hostnames (both at OS level and in DNS entries) for all the mongo config servers and mongo shard servers.

Will that work correctly or will I run across the same problem as mentioned in the bug?

Does the requirement of unique hostnames at OS level hold true even for mongos instances or is it just for shards?

Actually, it is a Very Bad Idea™ to do a load balancing this way with MongoDB. First of all, you can give multiple mongos instances to the driver. If one mongos goes down or becomes unavailable due to overload, the driver will be happy to access the next available mongos. Second, the config servers and mongod instances get unnecessary load for continuously updating the cluster "map". Third, they most likely will initiate and tear down connections continuously, too.

The solution would be to use CNAMEs at the DNS level instead of multiple A records for a host, but maintain unique hostnames on the OS level. That is, the hostname command should return a unique value on every host of the cluster, including the mongos instances.

A word on DNS based round robin load balancing: It's crap when used on it's own. You don't have any control over the load actually sent to each of the balanced servers. In case you have caching DNS servers between you and your biggest group of unique users, you actually might worsen the load for an individual server. You better use a "real" loadbalancer in front of your servers, something like HAProxy, varnish or nginx (depending on your application). In case you need / want to have more than one of those, you can use DNS based round robin load balancing on these, though from my experience, it is better to make one load balancer highly available to prevent a single point of failure.


SQL Server & SSDT : Error modeling database during Publish after adding CREATE AGGREGATE / EXTERNAL NAME I am using SSDT to create a database containing CLR user defined aggregates.
The aggregates are intended to sit in different schema. That is not possible with CLR objects inside SSDT, without using a post-deployment script to move the objects after deployment.
What I am attempting to do is:

Create the CLR aggregates in a single schema (the "CLR" schema) using the "Default schema" option
Use a CREATE AGGREGATE wrapper in the desired schema to call the CLR aggregate

For example, I have a CLR aggregate like:
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = -1)]
public struct StatsEntropy : IBinarySerialize
{
 ... etc
}

This get compiled and becomes CLR.StatsEntropy in the database. The database is called AnalysisFunctions, so it has an assembly also called AnalysisFunctions. This is no problem.
I then create a wrapper in the Stats schema like so:
CREATE AGGREGATE [Stats].[Entropy]
(
    @Values FLOAT
)
RETURNS FLOAT
EXTERNAL NAME AnalysisFunctions.StatsEntropy

This works without error when I build via debug. I have my debug connection set to a named instance of SQL Server. If I press F5, it compiles and deploys without any problem. The code is now usable on the server, including the wrapper.
But, if I attempt to publish to a server using Publish, it does not work. Instead it fails with:

Errors occurred while modeling the target database. Deployment cannot continue.

There are no further error messages either in the Data Tools Operations panel or error list. ssdttrace shows no useful information:

This only happens when the database already exists on the server. If I drop the database, it can deploy successfully. I can deploy successfully via debug even if the database already exists, it is only via Publish that the problem occurs. I did not start getting this problem until I added the aggregate wrappers.
Previously I was also getting errors indicating that the assembly AnalysisFunctions had an unresolved reference due to the use of EXTERNAL NAME - again working via debug, not via publish. But now that error has mysteriously vanished.
I am using Visual Studio 2017 15.9.21 and SSDT 15.1.62002.01090, deploying to SQL Server 2019.
Does anyone have any idea what this error might be, or how I can debug it?

I can't reproduce this problem using the same version of SSDT as you.  I do have a slightly newer Visual Studio (15.9.24), but I'd be surprised if that matters.  Here's my (very minimal) setup, please let me know if I've misunderstood some part of your scenario:
SSDT Project

clr.sql
CREATE SCHEMA [clr]

Stats.sql
CREATE SCHEMA [Stats]

StatsEntropy.cs
using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[SqlUserDefinedAggregate(Format.Native)]
public struct StatsEntropy
{
    public void Init()
    {
        // Put your code here
    }

    public void Accumulate(SqlString Value)
    {
        // Put your code here
    }

    public void Merge(StatsEntropy Group)
    {
        // Put your code here
    }

    public SqlString Terminate()
    {
        // Put your code here
        return new SqlString(string.Empty);
    }

    // This is a place-holder member field
    public int _var1;
}

Default Schema

Normally that failure during preview generation indicates that there are objects in your target database that don't exist in the SSDT model (dacpac).  That's discussed on Stack Overflow here: SSDT Publish errors on Creating Publish Preview
You can use the "Schema Compare" feature to check and see if that's the case for you.
If there are no conflicting objects in the target, then it would be helpful if you could edit your question to include your publish settings (the ".publish.xml" file, or at least screenshots of the "Advanced" window when publishing).  It could be some odd combination of options causing the problem.


Most efficient way to get oldest row in MySQL by id I have 2 tables:

conversation

CREATE TABLE `conversation` (
  `conversation_id` int(11) NOT NULL,
  `title` varchar(200) COLLATE utf16_czech_ci NOT NULL,
  `beginning_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_id` int(11) DEFAULT NULL
)

message

CREATE TABLE `message` (
  `message_id` int(11) NOT NULL,
  `text` varchar(5000) COLLATE utf16_czech_ci NOT NULL,
  `add_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `is_seen` int(11) NOT NULL,
  `conversation_id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL
)

When I create a new conversation, I also need to create a new message (the beginning message for the conversation). Later, this conversation may have thousands of messages. But what I need to do in certain situations is that I need to take a conversation with certain conversation_id from database and get the oldest message (the beginning message for the conversation) for this conversation. What is the most efficient way to get this message?

I did something like this but the ordering may slow it later, when conversations have maaany messages:

SELECT * FROM message WHERE conversation_id=some_id ORDER BY add_date ASC LIMIT 1

For the specific query that gets only one row from the table:

SELECT *
FROM message
WHERE conversation_id = @some_id
ORDER BY add_date ASC
LIMIT 1 ;

you only need a common (btree) index on (conversation_id, add_date). The query will need to do only an index seek (which will find the PK value) and then a seek on the table. The operations should be both very fast (O(logn)), so the size of the table will not matter much.

After you add the index (or any index), you don't need to do anything more. Whenever a query runs, if  the optimizer decides that the index is useful (for that query, which can be the specific query above or any other), then it will use the index.

Side notes: please add PRIMARY KEYs to the tables.


Putting some calculations on a database entry from phpmyadmin I am creating an online store website for sale/purchase. I have to put a specific percentage of an amount (User enters) in database.

for example
If user enters 100. I have to deduct 30% of it and I want to put 70 in database. Is there a way I can perform this check from phpmyadmin ?. I know I can do it from code but can I do it from phpmyadmin ?

Thanks for help.

You could do this in phpMyAdmin, but you shouldn't.

If you insist, you could use an input transformation to do so. You'd have to write one, but doing so shouldn't be too difficult if you have a basic knowledge of PHP. However, this is a bad idea for many reasons, the biggest of which is that you shouldn't be giving your users access through phpMyAdmin. phpMyAdmin is meant as a tool for a system administrator to manage their databases and by giving your users direct access, they in turn can do whatever they want, including changing the percentage to work in their benefit or modifying costs, quantities, or order details that they shouldn't have access to. Plus the interface is not what you want to present to a user, anyway. You should write your own custom application code (or use some application designed for sales and purchase) rather than forcing your users to interact with your database through phpMyAdmin.

I know you said you don't want to do it through code, but the two ways to handle this properly are through your application code or through code on the MySQL side (either through a stored procedure or a trigger on that column).


Oracle symmetrical one-way replication for cheap HA: unfashionable or technically unfeasible? We have two smallish unrelated databases on two big Oracle Enterprise servers. Instead of buying more licenses and servers for high-availability replicas, can we replicate database B to server A and database A to server B, as long as either server can handle the load for both databases if necessary?

Seems like there are several underlying questions:

Which replication methods can replicate an active DB to a standby DB in both
directions between two servers with good failover?
How much lighter is a replicate-to-standby workload than a
production workload?
If people aren't doing this, are hidden gotchas stopping them or is
it just not in fashion?

These days, Data Guard is included with Enterprise Edition.If you have enough spare capacity on each of the servers, I believe they can work as a physical standby for each other.

Logical and possibly bi-directional replication is more complex, and you would have to consider the types of objects and replication in the solution. Most solutions does not easily support the replication of DML etc.

Note that Data Guard configured as physical standby might still consume a considerable amount of resources and depending on the configuration, may impact on production performance on both servers.


How to replicate CREATE USER command? I'm replicating database master to master and everything works ok except for mysql.user table.

When I create user in DB-1, that user is not created om DB-2.

I have this on both servers in cnf

binlog_ignore_db=information_schema
binlog_ignore_db=performance_schema
binlog_ignore_db=ssl

binlog_do_db=mysql

replicate_do_db=mysql
replicate_do_table=mysql.user

Status is showing that replication is ok.

SHOW SLAVE STATUS \G

Thank you for help.

EDIT 1:
I have also tried to sync all databases:

binlog_do_db=mysql
binlog_do_db=information_schema
binlog_do_db=performance_schema
binlog_do_db=ssl

replicate_do_db=mysql
replicate_do_db=information_schema
replicate_do_db=performance_schema
replicate_do_db=ssl

EDIT 2:

mysql  Ver 15.1 Distrib 10.2.26-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Ubuntu 16.04

Solved.
Before command CREATE USER you have to put:

use mysql;

and then it is replicated to slave server


How to get the current line number from an executing stored procedure Is there a function or keyword that will allow me to get the current line number in a stored procedure?

I know there's an undocumented LineNo function that allows you to set the line number, and affect the output of system error messages
https://stackoverflow.com/questions/4054511/what-exactly-does-the-t-sql-lineno-reserved-word-do

I know there's a function ERROR_LINE() that's available inside of a BEGIN CATCH ... END CATCH.

ERROR_LINE() does what I need, but I want to use it from outside a CATCH block. Anywhere in the file.

 DECLARE @InsertSource VARCHAR(1000) = object_name(@@procid)
 EXEC PROC_Accounting_Transaction_Insert ... other parameters..., @InsertSource

Currently I'm just hard-coding the call # as it appears in the stored procedure body, but it's getting old quickly.

DECLARE @InsertSource VARCHAR(1000)

SET @InsertSource = object_name(@@procid) + '#1'
EXEC ...

SET @InsertSource = object_name(@@procid) + '#2'
EXEC ...

This question actually came up 2.5 years ago on StackOverflow, and I posted an Answer there:

SQL print line number in comment of dynamically created stored procedure?

The relevant portion of that Answer is copied below:

You can use TRY / CATCH with a forced error as the CATCH block can return the line number that the error occurred on via the ERROR_LINE() function. The full construct, formatted for readability, is:

DECLARE @Line INT = -1; -- default to an invalid line #

-- some code

BEGIN TRY
    ;THROW 50000, 'Line#', 1; -- all 3 values are arbitrary, but required
END TRY
BEGIN CATCH
    SET @LineNumber = ERROR_LINE();
END CATCH

-- some code

Now, to get the @LineNumber variable to populate with the line number that it is being set on, you can reduce that construct to a single line as follows:

BEGIN TRY;THROW 50000,'',1;END TRY BEGIN CATCH;SET @Line=ERROR_LINE();END CATCH

Please note that the THROW command started in SQL Server 2012. If you are using SQL Server 2005, 2008, or 2008 R2, then you need to use RAISERROR() function instead of THROW.


Get 10th and 90th percentile by customer I have a table which contains customers and scores (based on different factors, irrelevant in this case; a customer can have multiple scores), which looks like this:

customer_id | score | score_giver_id
====================================
          1 | 100   | 1
          1 | 102   | 1
          1 | 101   | 1
          1 | 140   | 1
          2 | 131   | 3
          1 | 44    | 1
          3 | 223   | 1
          3 | 1     | 2
          3 | 201   | 1
          3 | 211   | 1
          3 | 231   | 1
          3 | 243   | 1

The score_giver_id is irrelevant, but I'd still like to fetch it.

In the example above, when getting the 50th percentile, grouped by customer_id, the result should be (I picked the 50th percentile in this example, because it illustrates what I want to do better):

customer_id | score | score_giver_id
====================================
          1 | 101   | 1
          2 | 131   | 3
          3 | 223   | 1

I used the method described here.

I need to get the value that is at the 10th percentile, respectively at the 90th percentile in PostgreSQL. I've seen that since 9.4 there is an ntile function, but I don't really understand how it works, what it does, and if it helps me.

I've found a nice snippet for MySQL, which works (even though there are some caveats), but I'd like to use built-in functions if available (for MySQL there are none, hence the snippet).

It seems you are after the percentile_disc() ordered-set aggregate function.
The documentation says the following about it:

percentile_disc(fraction) WITHIN GROUP (ORDER BY sort_expression)
discrete percentile: returns the first input value whose position in the ordering equals or exceeds the specified fraction

The syntax is a bit strange for an aggregate, but using it is easy:
SELECT percentile_disc(0.9) WITHIN GROUP (ORDER BY score)
  FROM customer_score
 GROUP BY customer_id;

You define the column from which to take the percentile in the ORDER BY clause.


MySQL Create Index Semantics: Clustered vs. Nonclustered? I'm new to databases and specifically MySQL, so pardon me if this is a simple question.

I'm trying to make sense of what happens with MySQL when I call CREATE INDEX. I know that primary keys are unique and correspond to a clustered index but what happens if I have a table where I have no primary key defined and create an index. I know by default the data structure is a B-tree but it's not clear if this corresponds to a clustered or nonclustered index in the typical sense.

Specifically I have a table that that has four columns as integers:

sales(item_id, time, price, location_id)

The only column that has unique values is time. If I create this table and then later run CREATE INDEX dx ON sales(item_id); what happens?

From here I'm lead to believe that when I create the table without the primary key, because time is unique that is the clustered index and so I'm creating a non-clustered index on item_id.

My questions are: is this correct? If so how can I create a clustered index on a non-unique column in MySQL?

(This assumes you are using `ENGINE=InnoDB.)

Your table must have a primary key:  You have 3 choices:

(preferred)  You explicitly provide such.
There is a UNIQUE index with non-null column(s).  (Sloppy, just make it the PK)
(not a good option)  A hidden PK will be provided.

The PK will be clustered and unique.  That is the only choice for a pk in MySQL.

No other index can be clustered.  Again, that is by-definition.

There are 3 choices for structure of an index:

(the most common) B+Tree.  (Everything else in this answer is B+Tree)
FULLTEXT -- for searching for words in text
SPATIAL -- for 2-dimensional searches, such as geographic

  sales(item_id, time, price, location_id)

Perhaps you need PRIMARY KEY(item_id, time).  But that assumes you will never have two sales for the same item at exactly the same time.

So, it might be safer to have a 5th column:

id INT UNSIGNED AUTO_INCREMENT NOT NULL,
PRIMARY KEY(id)

Let's see the SELECTs; from them we can decide what secondary index(es) you need.  Or do it yourself here .

  My questions are: is this correct? If so how can I create a clustered index on a non-unique column in MySQL?

That is a trick that very few people have discovered.  Once you have something that is unique (such as the 5th column, above), do this instead of the PK above:

PRIMARY KEY(time, id),
INDEX(id)

Now, even it time is not unique, the PK is clustered and unique because of id tacked on.  AUTO_INCREMENT does not require more than "being the first column in some index".


Required hardware to build a development environment in Sql Server We have a bunch of applications that depends on about 10 different databases servers (all sql server) in our network.

We are looking to build a development environment where we could have a mirror of the production databases.

Our plan is to have one server and put all the 10 databases inside it as separate sql server instances. All the 10 databases currently sum up to 1TB of disk space.

We won't have many users connected to these databases and won't do any kind of heavy use, just development.

What kind of hardware do I need to run this and not have a too bad performance? We came up with a Dell machine that is in our budget with 2x Xeon Quad Core + 32GB Ram + 8TB of disk (SATA 7200RPM) configured in RAID. Will that do?

Thanks!

Ideally, you'd have the same configuration in development as you do in production.  That way, you can have a higher probability that if it's slow in dev, it will be slow in prod, and if it's fast in dev, it will be fast in prod.  I'm guessing that this would be a pretty significant drop from what you have in production.  But given that few places have the budget to have dev match prod, I understand dealing with what you have.

With 10 instances, the part I'd be concerned about is the RAM.  Figuring that somewhere around  4-6 GB should be held back for the OS, any other software on that server (A/V, etc.), and an emergency buffer, this means you have about 26-28 GB of RAM for 10 instances, or under 3 per instance.  I don't know what you're averaging now, but it's probably a good bit more.

As such, I'd try to max out that server's RAM and set the maximum memory per instance equal to what it is in production.  Even though there won't be many people using the server, skimping on RAM will give you false negatives with performance and will make it harder for developers to get accurate performance tests.

Unless you have dozens of developers constantly going at the databases, you'll probably find CPU to be adequate (though not really good).  The low disk speed is a bit concerning, but get enough RAM and the disk speed shouldn't be too big of an issue for a development server.  Again, you'll likely find additional performance-related problems in dev that you won't in prod (if you have more CPUs per server and faster disks and more spindles available per server), but when you're on a budget, those are the trade-offs you have to make sometimes.

Edit

I would be totally remiss if I didn't point out Glenn Berry's book on the topic.


Is it possible to set order in which inherited tables a scanned in postgresql? I have a postgresql measurement table which is partitioned based on logdate column by month. Suppose I have city_id integer column and want to select latest row with city_id = 125.

When I run explain select * from measurement where city_id = 125 limit 1; I get

Limit  (cost=0.00..3.39 rows=1 width=12)
  ->  Append  (cost=0.00..308.25 rows=91 width=12)
     ->  Seq Scan on measurement  (cost=0.00..0.00 rows=1 width=12)
           Filter: (city_id = 125)
     ->  Seq Scan on measurement_y2006m01  (cost=0.00..34.25 rows=10 width=12)
           Filter: (city_id = 125)
     ->  Seq Scan on measurement_y2006m02  (cost=0.00..34.25 rows=10 width=12)
           Filter: (city_id = 125)
     ->  Seq Scan on measurement_y2006m03  (cost=0.00..34.25 rows=10 width=12)
           Filter: (city_id = 125)

What is the default order in which those Seq Scan actions are performed on inherited tables? And can I explicitly set it so that the latest tables are scanned first as I am expecting to select recently added rows more often?

I ...  want to select latest row with city_id = 125.

Bold emphasis mine. Your query does not match the declared objective. Should be something like:
SELECT * FROM measurement WHERE city_id = 125 ORDER BY logdate DESC LIMIT  1;
Which should make Postgres scan the latest partition first.
Assuming logdate is defined NOT NULL. Else use ORDER BY logdate DESC NULLS LAST:

PostgreSQL sort by datetime asc, null first?

If that doesn't work for some reason (your setup and Postgres version are undisclosed), you can also spell it out:
(SELECT * FROM  measurement_y2006m03 WHERE city_id = 125 ORDER BY logdate DESC)
UNION ALL
(SELECT * FROM  measurement_y2006m02 WHERE city_id = 125 ORDER BY logdate DESC)
UNION ALL
(SELECT * FROM  measurement_y2006m01 WHERE city_id = 125 ORDER BY logdate DESC)
LIMIT  1;

Parentheses are required to make the ORDER BY apply to each SELECT instead of the whole query. Maybe you get an even better query plan by adding LIMIT 1 to each SELECT additionally. You'll have to test.
Postgres will stop scanning as soon as it has found the first row. Related answers with more explanation:

Way to try multiple SELECTs till a result is available?
Use column values in ORDER BY and LIMIT clauses

Index
For best performance you would have composite indices on (city_id, logdate DESC) on all child tables (or at least the latest ones).