Is there a way to partition a mysql table, by range columns, where the 2 columns in question are of datetime and bigint? I need to partition a table into 4 partitions, p0 to p3: with p0 having all rows with "id"=0, and p1 having all rows with "id"=1 and "doj" before february, and p2 having "id"=1 and "doj" before march, and partition "future" with others. The value for id, is guaranteed to be either 0 or 1 always.

I've tried to come up with a solution and the following is what i arrived at:

create table temp
(id int not null primary key,
 doj datetime not null primary key)
partition by range columns(id,month(doj))
(partition p0 values less than (1, 13),
 partition p1 values less than (2, 2),
 partition p2 values less than (2, 3),
 partition p3 values less than(maxvalue, maxvalue));

I get the following error when i try and execute the above sql command:

  You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(doj)) (partition p0 values less than (1, maxvalue), partition p1 values less t' at line 4

Any insights as to how to partition the table in a manner described above would be much appreciated.

Version of MySql used is:
5.6.19-67.0-log

The solution can be:

create table temp
(id int not null primary key,
 doj datetime not null primary key,
 doj_month tinyint) -- add field for partitioning
partition by range columns(id,doj_month)
(partition p0 values less than (1, 13),
 partition p1 values less than (2, 2),
 partition p2 values less than (2, 3),
 partition p3 values less than(maxvalue, maxvalue));

-- create triggers for additional field calculations
create trigger tr_i_temp
for insert on temp
for each row
set NEW.doj_month = month(NEW.doj);

create trigger tr_u_temp
for update on temp
for each row
set NEW.doj_month = month(NEW.doj);

But you must to remember that there exists a lot of cases when triggers are not fired (cascade operations, LOAD DATA, etc.). If you use such operations you MUST update this additional field "by hands" (for example, update query which reassigns doj field).


How to sum the values of a json column filtered with regex? How to sum the values of a json column filtered with regex?
Columns:
A.name: Varchar with name product
B.products: JSON with time active By product
Query:
select
    A.name as product,
    sum(((REGEXP_MATCH(B.products->>'status', 'Active:.(.*?)\"'))[1])::float) as metric
from
    tbl_accounts A
inner join tbl_products B on
    A.identifier = B.identifier
where
    B.products->>'status' like '%Active%'
group by
    A.name,
    B.products

Explanation data
JSON 1: {"times": ["Stoped: 49.05", "Active: 23.26"]}
JSON 2: {"times": ["Stoped: 59.05", "Active: 33.26"]}
Desired Output: 56.52
Output:

ERROR: could not identify an equality operator for type json

The immediate reason for the error message is that the data type json has no equality operator. See:

How to query a json column for empty objects?
How to remove known elements from a JSON[] array in PostgreSQL?

You have:
...
group by
    A.name,
    B.products  -- type json!?

You can do that, using jsonb instead of json, where an equality operator is defined. But do you really want to group by B.products? (Same JSON documents?) Maybe you meant to write B.products->>'status' (Same status?) Or just GROUP BY A.name?
Aside: there may also be a simpler way to extract numbers that with REGEXP_MATCH(). You would have to define possible values B.products->>'status' and disclose the exact intention of the expression.
If you are at liberty to do so, it's typically best to store numbers in a separate key or even separate table column ....
jsonpath query in Postgres 12 or later
Your added sample values suggest you might be able to use jsonpath in Postgres 12 or later. Based on jsonb (not json).
Note: this is a proof of concept. If possible, normalize the table design and store numbers in a dedicated table column. Much simpler and more efficient.
Index
jsonpath operators can also be supported with a (default) jsonb_ops GIN index. I narrow down the scope with the expression products->'times':
CREATE INDEX products_times_gin_idx ON products USING gin ((products->'times'));

Index only helps for selective queries where not most rows have to be processed anyways!
Basic query to filter qualifying rows with jsonpath
Can use above index.
SELECT *
FROM   products B
WHERE  B.products->'times' @? '$[*] ? (@ starts with "Active: ")';

jsonpath expression explained:
$[*] ... look at each array element of outer nesting level
? ... run the following test
(@ starts with "Active: ") ... Does element value start with 'Active:'?
... unnest and return only qualifying JSON array elements
SELECT *
FROM   accounts A
JOIN   products B USING (identifier)
     , jsonb_path_query(B.products->'times', '$[*] ? (@ starts with "Active: ")') act
WHERE  B.products->'times' @? '$[*] ? (@ starts with "Active: ")' -- optional, to use idx
;

...  get results as text
SELECT *
FROM   accounts A
JOIN   products B USING (identifier)
     , jsonb_array_elements_text(jsonb_path_query_array(B.products->'times', '$[*] ? (@ starts with "Active: ")')) act
WHERE  B.products->'times' @? '$[*] ? (@ starts with "Active: ")' -- optional, to use idx
;

See:

How to turn JSON array into Postgres array?

... and aggregate the number part
Arriving at your final query:
SELECT A.name as product, sum(right(act::text, -8)::float)  -- -8 = length('Active: ')
FROM   accounts A
JOIN   products B USING (identifier)
     , jsonb_array_elements_text(jsonb_path_query_array(B.products->'times', '$[*] ? (@ starts with "Active: ")')) act
WHERE  B.products->'times' @? '$[*] ? (@ starts with "Active: ")' -- optional, to use idx
GROUP  BY 1;

db<>fiddle here
Related:

Update all values for given key nested in JSON array of objects


Why does this query not return records where both fields using CASE WHEN are zero? I'm attempting challenge 13 on this SQLzoo page, but my query returns one fewer row than it's supposed to.

SELECT mdate,
  team1,
  SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) AS score1,
  team2,
  SUM(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) AS score2
  FROM game JOIN goal ON matchid = id
GROUP BY mdate, matchid, team1, team2

When both SUM values would be zero, the query just doesn't return than record and I've no idea why.

You need to do a LEFT OUTER JOIN on goal to pick up the 0-0 games.

SELECT mdate,
  team1,
  sum(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) score1,
team2,
  sum(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) score2
  FROM game
LEFT OUTER JOIN goal ON matchid = id
group by mdate, matchid, team1,team2
order by mdate, matchid, team1 ,team2

You were nearly there.


MySQL How to Script to create indexes on all date columns in database I have a lot of tables in the database that are using a datetime stamp - I need to create a script that will create indexes on all columns in each table that does not already have an index .

So far I can get all the columns of type date, time etc..
I can get all of the existing indexes .. but I am having trouble combining them , as I intend to

Select (CONCAT('ALTER TABLE ' , table_name , ' ADD INDEX (' , column_name , ');') FROM
MyIdentifiedColumns ..

The goal here is to create indexes on all of these columns of type date, timestamp where no index currently exists with a script - NOT manually in workbench .. So how can I do this ?

Get my Columns in the database that are of the proper date time

select col.table_schema as database_name,
       col.table_name,
       col.ordinal_position as column_id,
       col.column_name,
       col.data_type,
       col.datetime_precision
from information_schema.columns col
join information_schema.tables tab on tab.table_schema = col.table_schema
                                   and tab.table_name = col.table_name
                                   and tab.table_type = 'BASE TABLE'

where col.data_type in ('date', 'time', 'datetime', 'timestamp')
      and col.table_schema not in ('information_schema', 'sys',
                                   'performance_schema', 'mysql')
     and col.table_schema = 'mydatabase'

And to get all of the Indexes currently in the database

select index_schema,
       index_name,
       group_concat(column_name order by seq_in_index) as index_columns,
       index_type,
       case non_unique
            when 1 then 'Not Unique'
            else 'Unique'
            end as is_unique,
        table_name
from information_schema.statistics
where table_schema not in ('information_schema', 'mysql',
                           'performance_schema', 'sys') AND index_schema = 'mydatabase'
group by index_schema,
         index_name,
         index_type,
         non_unique,
         table_name
order by index_schema,
         index_name

The larger the table, the longer the ADD INDEX will take, unless you have a current version, which has a rapid add index.

Your code seems promising.  You could complete it by building a Stored Procedure to loop through the constructed ALTER TABLE commands to perform+execute them.

But first, run the stored routine to just SELECT the constructed ALTERs.  Desk check the output.  (And/or show us the output.)


How is a T-SQL Trigger that only fires on real changes made? I have a table trigger on UPDATE and INSERT that adds a row to another table.  It only needs to add a row if one of four columns are changed.  I tried using IF UPDATE(col) to test for changes but it has a blind spot.  It only tests that some value came in.  I need to go deeper, I need to compare the old and new values to see of a true change has occurred.  It has to work with both INSERT and UPDATE.

In the case of an UPDATE that's easy because both the inserted and deleted tables have values I can compare within the trigger.  However, for the INSERT only the insert table has values.  Because I need this all in the same trigger, how do I handle that INSERT case?

Here is the script of the trigger I want to modify:

ALTER TRIGGER [dbo].[trATPerson_alter]
   ON  [mydb].[dbo].[AT_Person]
   AFTER INSERT,UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- Not all updates require a push
    IF (UPDATE([First_Name]) OR UPDATE([Last_Name]) OR UPDATE([JobCode]) OR UPDATE([Inactive]))
    BEGIN
        INSERT INTO [mydb].[dbo].[AT_Person_To_Push] (
                [Facility],
                [VendorID],
                [Person_code],
                [First_Name],
                [Last_Name],
                [JobCode],
                [Alink],
                [Inactive]
            )
        SELECT  [Facility],
                [VendorID],
                [Person_code],
                [First_Name],
                [Last_Name],
                [JobCode],
                [Alink],
                [Inactive]
        FROM inserted
    END
END

You can handle both INSERT and UPDATE with an EXCEPT set operator.
The EXISTS will only evaluate to TRUE both if it's just an INSERT, or if it's an UPDATE with different values for any of these columns.

IF EXISTS (
           SELECT First_Name, Last_Name, JobCoe, Inactive FROM inserted
           EXCEPT
           SELECT First_Name, Last_Name, JobCoe, Inactive FROM deleted
          )
BEGIN...


Mysql database slow responding to connects when having only few concurrent connections We have a database setup with in master/slave. It runs innodb and its on a 8 core with 32gb.

When i run "ab" to do a load test on a website (4 frontend webservers) which does the query's with 10 concurrent users on the database it will handle the first batch 'ok' after which the average response time will go up.

    Percentage of the requests served within a certain time (ms)
    50%     20
    66%     26
    75%     31
    80%     34
    90%     46
    95%   5018
    98%   5025
    99%   5033
    100%   5041 (longest request)

As you can see, the most are going up to 5 seconds each while the first batch was below 50 ms (which would be ok) i thought it might have something to do with those 5 seconds which stands out. I added skip resolving, but also did not help.

The example query i am using is:

  SELECT imageURL, linkURL, imageWidth, imageHeight, androidLinkURL
  FROM stuff
  WHERE clued = 1 AND status = 'a'
  ORDER BY RAND()
  LIMIT 1;

The load of the machine itself sits pretty much idle (0.3) and when i connect to the server and issue a 'show processlist' i would expect to see at least a couple of these query's coming by. I am only using the master server to talk too, same issue is also on the slave though.

Before this started i thought it was max open files, so i increased that but with only 10 connections its not. Then i decreased the time_waits for connection because that may run out, again 10 connections should not pose a problem. I also had massive IO caused by the innodb logging which i turned to 1.

 innodb_flush_log_at_trx_commit 1

I also saw my connections coming in from both ipv4 and ipv6, so i binded the mysql to ipv4 only. I checked dmesg to see if some syn attack might be causing the problem and tweaked that too but again only 10 concurrent should not be a problem.

I also saw now and then some writes to tmp file, even though it was not in this particular test query, so i also changed that to a ramfs filesystem but also did not help.

Basicly when this happens, connecting to the server grinds to a halt and starts stacking up. Above query is just a example, other query's are cached on php with memcached.

You can find my variables here: http://pastebin.com/YLASDSa5 (disregard the "logging" options that are turned on, those are normally off)

UPDATE: I believe its is no longer a specific database issue. I have installed apache locally on the box of the database server and i see no issues at all. Doing a load test from another box also works fine to the apcache on the box, is also fine.

Here is the weird part, if i do a load test with 10 concurrent users and set my resolv.conf timeout to 1 second it will stall with 1 second as shown in the load test example. If i do 100 concurrent connenctions i get this:

 50%   1084
 66%   1099
 75%   1121
 80%   1156
 90%   2146
 95%   2177
 98%  10002
 99%  10002
100%  10005 (longest request)

As if it has increments of 1 second, then to 2, then in the end to 10. Its something to do with the dns, but i am clueless to what.

Feel free to move the question to the appropriate exchange since it no longer fits Stack Exchange.

The problem was that we have a haproxy in front of the setup. Each call to a webserver was creating a dns query on each of the webservers because the virtualhost was a "servername" with no ip supplied. Adding a local dns bind caching daemon solved this issue although its not the best sollution. (also didn't show up in the beginning since it was udp traffic)

So in the end this was not database related.


How to have more than 100 entries in case statement as a variable I wrote a case statement with > 100 choices where I am using the same statement in 4 places in a simple query.

The same query twice with a union between them but also is doing a count and therefore the group by also contains the case statement.

This is to relabel some company names where different records for the same company are spelled differently.

I tried to declare a variable as a VarChar(MAX)

declare @CaseForAccountConsolidation varchar(max)

SET @CaseForAccountConsolidation = 'CASE
       WHEN ac.accountName like ''AIR NEW Z%'' THEN ''AIR NEW ZEALAND''
       WHEN ac.accountName LIKE ''AIR BP%'' THEN ''AIR BP''
       WHEN ac.accountName LIKE ''ADDICTION ADVICE%'' THEN ''ADDICTION ADVICE''
       WHEN ac.accountName LIKE ''AIA%'' THEN ''AIA''
       ...

When I went to use it in my select statement - the query just returned the case statement as text and didn't evaluate it.

I also was unable to use it in the group by - I got this error message:

Each GROUP BY expression must contain at least one column that is not an outer reference.

Ideally I would like to have the CASE in just a single place - so that there is no chance of me updating one line and not replicating that elsewhere.

Is there some way of doing this?

I am open to other ways (Like maybe a function - but I am not sure how to use them like this)

Here is a sample of the SELECT I am currently using

SELECT
   SUM(c.charge_amount) AS GSTExcl
   ,dl.FirstDateOfMonth AS MonthBilled
   ,dl.FirstDateOfWeek AS WeekBilled
   ,CASE
       WHEN ac.accountName like 'AIR NEW Z%' THEN 'AIR NEW ZEALAND'
       WHEN ac.accountName LIKE 'AIR BP%' THEN 'AIR BP'
       WHEN ac.accountName LIKE 'ADDICTION ADVICE%' THEN 'ADDICTION ADVICE'
       WHEN ac.accountName LIKE 'AIA%' THEN 'AIA'
       ELSE ac.accountName
   END AS accountName
   ,dl.FinancialYear
   ,CONVERT(Date,c.date_charged) AS date_charged
FROM [accession] a
   LEFT JOIN account_code ac ON a.account_code_id = ac.account_code_id
   LEFT Join charge c ON a.accession_id = c.accession_id
   LEFT JOIN dateLookup dl ON convert(date,c.date_charged) = dl.date
WHERE a.datecreated = CONVERT(DATE,now())
GROUP BY
   dl.FirstDateOfMonth
   ,dl.FinancialYear
   ,dl.FirstDateOfWeek
   ,CONVERT(Date,c.date_charged)
   ,CASE
       WHEN ac.accountName like 'AIR NEW Z%' THEN 'AIR NEW ZEALAND'
       WHEN ac.accountName LIKE 'AIR BP%' THEN 'AIR BP'
       WHEN ac.accountName LIKE 'ADDICTION ADVICE%' THEN 'ADDICTION ADVICE'
       WHEN ac.accountName LIKE 'AIA%' THEN 'AIA'
       ELSE ac.accountName
   END

UNION

SELECT
   SUM(c.charge_amount) AS GSTExcl
   ,dl.FirstDateOfMonth AS MonthBilled
   ,dl.FirstDateOfWeek AS WeekBilled
   ,CASE
       WHEN ac.accountName like 'AIR NEW Z%' THEN 'AIR NEW ZEALAND'
       WHEN ac.accountName LIKE 'AIR BP%' THEN 'AIR BP'
       WHEN ac.accountName LIKE 'ADDICTION ADVICE%' THEN 'ADDICTION ADVICE'
       WHEN ac.accountName LIKE 'AIA%' THEN 'AIA'
       ELSE ac.accountName
   END AS accountName
   ,dl.FinancialYear
   ,CONVERT(Date,c.date_charged) AS date_charged
FROM [accession] a
   LEFT JOIN account_code ac ON a.account_code_id = ac.account_code_id
   LEFT Join charge c ON a.accession_id = c.accession_id
   LEFT JOIN dateLookup dl ON convert(date,c.date_charged) = dl.date
WHERE a.datecreated = DATEADD(YEAR,-1,CONVERT(DATE,now()))
GROUP BY
   dl.FirstDateOfMonth
   ,dl.FinancialYear
   ,dl.FirstDateOfWeek
   ,CONVERT(Date,c.date_charged)
   ,CASE
       WHEN ac.accountName like 'AIR NEW Z%' THEN 'AIR NEW ZEALAND'
       WHEN ac.accountName LIKE 'AIR BP%' THEN 'AIR BP'
       WHEN ac.accountName LIKE 'ADDICTION ADVICE%' THEN 'ADDICTION ADVICE'
       WHEN ac.accountName LIKE 'AIA%' THEN 'AIA'
       ELSE ac.accountName
   END

The purpose for this UNION is to return all data for a timeperiod, and ALSO to return data  for the same timeperiod for 12 months previously

EDIT:  Added a missing "CATCH-ALL"
EDIT2: Added a second ½ of the UNION statement
EDIT3: Corrected the GROUP BY to include some other necessary elements

Put the data into a table

CREATE TABLE AccountTranslate (wrong VARCHAR(50), translated(VARCHAR(50));

INSERT INTO AccountTranslate VALUES ('ADDICTION ADVICE%','ADDICTION ADVICE');
INSERT INTO AccountTranslate VALUES ('AIR BP%','AIR BP');
INSERT INTO AccountTranslate VALUES ('AIR NEW Z%', 'AIR NEW ZEALAND');

and join to it.

SELECT ...,COALESCE(AccountTranslate.translated, ac.accountName) AS accountName
FROM
....,
account_code ac left outer join
AccountTranslate at on ac.accountName LIKE AccountTranslate.wrong

That way you can avoid keeping the data up to date in multiple places.  Just use the COALESCE where you need it.  You can incorporate this into CTE or VIEWs as per the other suggestions.


Trying to create a collation on Postgres 9.6; a nonsensical error message I'm running Postgres 9.6.1 on a Debian-based Docker image. My default locale is en_US.utf8. I'm trying to create a collation of type ja_JP.utf8. I have the Japanese locale installed as demonstrated by the command locale -a:

$ locale -a
C
C.UTF-8
en_US.utf8
ja_JP.utf8
POSIX

However, when trying to create the Japanese collation in psql, I get the following, nonsensical error message:

# create collation ja_JP (LOCALE="ja_JP.utf8");
ERROR:  could not create locale "ja_JP.utf8": Success

Googling a bit finds me a bug report from over two years back: https://www.postgresql.org/message-id/20140525154511.GA21233%40alap3.anarazel.de

It looks the same, and judging from my symptoms, it isn't fixed yet! Is there any way to work around this bug?

I found a workaround: the bug happens only if an earlier attempt to create a collation has failed. After a failure, the bug seems to keep happening until the server process is restarted. So to create the collation, you have to get it right from the start, or restart the process to try again. To restart a Docker container, use

docker restart $DB_CONTAINER_NAME_HERE


mysql replication master 5.5 slave 5.1 error on create database I have a replication system where my master has a mysql 5.5 version while the slave is mysql 5.1. I know that this is not supported because the slave version should be greater than the master, but we cannot upgrade yet.

Everything is going fine until a new database is created in the master, then the slave replication fails and shows the error:

Query caused different errors on master and slave.
Error on master: message (format)=
'Column count of mysql.%s is wrong. Expected %d, found %d. Created with MySQL %d, now running %d.
Please use mysql_upgrade to fix this error.' error code=1558 ; Error on slave: actual message='no error', error code=0. Default database: 'newdb'. Query: 'DROP DATABASE newdb'

My workaround is:

set global sql_slave_skip_counter=2;
create database newdb;
start slave

After doing a checksum of all database tables everything seems ok, but we have an awful application that creates databases very often, so I would like to know if there is a way that doesn't imply coding to solve the problem without manually creating the database.

Every replication event generated by the master includes the error code that resulted from execution of the query that generated the event.  This is usually "success" (0, no error).

When the slave executes a query, it expects the error it encounters to be the same error as the master encountered (again, usually "no error").  When this doesn't happen, replication stops.  Note that the error message from the master isn't preserved, just the code.  That's why you see placeholders in the error message, because the slave displays the sprintf() template for the error message.

But note, carefully, where the error is.

Check the master server's error log.

It looks as if the master was not correctly upgraded to MySQL 5.5; specifically, it looks like the mysql_upgrade utility was not used to upgrade the system tables to be fully compatible with MySQL 5.5.

http://dev.mysql.com/doc/refman/5.5/en/mysql-upgrade.html

You are indeed running an unsupported configuration, with a newer master and an older slave... but in this case, it appears that your master has a problem that is actually unrelated to replication.

Error on master:
message (format)=
'Column count of mysql.%s is wrong. Expected %d, found %d.  Created with MySQL %d, now running %d.
Please use mysql_upgrade to fix this error.'
error code=1558 ;

The master encountered an error; you may find similar errors in the master's log, although it's possible that they only occur at startup, if at all.  In any event, your application should have seen this error also... perhaps it's ignoring it. :(

The slave didn't encounter any error executing the actual query.

Error on slave: actual message='no error', error code=0.

It occurred to me that it's very important that the actions performed by mysql_upgrade can't be safely replicated, since the slave server is older.  It turns out, this was anticipated in the design, so the mysql_upgrade utility appears to be safe to run on a master, without disconnecting the slave, even if the slave is older (or was already upgraded). According to this comment in the source code of mysql_upgrade.c:

Master and slave should be upgraded separately.
All statements executed by mysql_upgrade will not be binlogged.


Slow mysql fulltext query I have a little problem with the performance of a mysql-query which uses a fulltext index.

The following query

SELECT Mention.id
FROM mentions AS Mention
WHERE (MATCH (`Mention`.`title_text`, `Mention`.`content_text`, `Mention`.`author_text`)
AGAINST ('"hannover 96"' IN BOOLEAN MODE))

takes about 3 seconds.

If I change the fulltext-condition to

SELECT Mention.id
FROM mentions AS Mention
WHERE (MATCH (`Mention`.`title_text`, `Mention`.`content_text`, `Mention`.`author_text`)
AGAINST ('+hannover +96' IN BOOLEAN MODE))

the query takes just about 0.001 seconds.

So is there a possibility to get a better performance of the first query? It’s important to search for the complete string “hannover 96” and not for records which contain hannover and 96.

Thanks for some tips!

Best regards,

Timo

I can explain why the first query is slow, but not why the second one is fast. :)

My theory is that ft_min_word_len is longer than "2" on your system (default is 4) so the "word" '96' is not actually making it into the full-text index... so you're doing a full table scan or at least you're having to find all of the rows containing 'hannover' in the index and then discarding the rows not matching the final criteria.  Unfortunately, EXPLAIN SELECT isn't as valuable as usual when it comes to queries involving a fulltext index.  It just tells you whether that index is going to be attempted, apparently.

Take a look at these:

https://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html

https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_ft_min_word_len

If you aren't, already, then you may want to use SELECT SQL_NO_CACHE ... when benchmarking queries... or you may be getting a response from the query cache that is deceptively speedy.


How to free SQL Server memory? I have a SQL Server sandbox installation on my local computer. I have set the memory SQL Server instance can use to 1000 mb. When running an intensive operation, memory usage rises up to 1000 mb. When operation is over, SQL Server is still holding the memory. How to free this memory reservation?

Stop/start the service, nothing else will release the memory back to the OS.

Obviously not something you'd ever want to do with an operational server but perfectly reasonable for a local sandbox. With 3 different instances on my laptop, its the only viable way.

Edit following @Nick's comment.

  As James noted, if it reserves more than the minimum you specify, it
  should in theory release it when it doesn't need it and there is
  memory pressure on the machine.

What's your definition of "doesn't need it"? Once the buffer pool has been filled, it isn't going to drop pages out until they become disfavoured by other pages being brought in. If you want the memory back immediately, as is the case for @jrara, you've got to stop/start.


Failed to restored indexes using mongorestore - MongoDB Note: I already know about --noIndexRestore

I have an org collection with the following document structure,

{
    "_id" : ObjectId("5cc2bf9f5ca45131b8f8e427"),
    "name" : "National",
    "parent" : "null",
    "level" : 1,
    "label" : "National",
    "identifier" : "Default",
    "client_id" : ObjectId("5af3178c141292374cbf363e")
}

I have created the indexes on the following fields,

name
parent
level
identifier
client_id

When I check indexes,

db.org.getIndexes();

Result:

/* 1 */
{
    "v" : 2,
    "key" : {
        "_id" : 1
    },
    "name" : "_id_",
    "ns" : "testdbname.org"
},

/* 2 */
{
    "v" : 2,
    "key" : {
        "name" : 1
    },
    "name" : "name_1",
    "ns" : "testdbname.org"
},

/* 3 */
{
    "v" : 2,
    "key" : {
        "parent" : 1
    },
    "name" : "parent_1",
    "ns" : "testdbname.org"
},

/* 4 */
{
    "v" : 2,
    "key" : {
        "level" : 1
    },
    "name" : "level_1",
    "ns" : "testdbname.org"
},

/* 5 */
{
    "v" : 2,
    "key" : {
        "identifier" : 1
    },
    "name" : "identifier_1",
    "ns" : "testdbname.org"
},

/* 6 */
{
    "v" : 2,
    "key" : {
        "client_id" : 1
    },
    "name" : "client_id_1",
    "ns" : "testdbname.org"
}

All of the above indexes looks fine to me. But when I take backup on server and restore it somewhere else it gives me the following error while restoring the org collection.

reading metadata for testdbname.org from archive 'testdbname_05-10-2019_00%3A55%3A31.archive'
2019-10-05T10:20:40.979+0500    restoring testdbname.org from archive 'testdbname_05-10-2019_00%3A55%3A31.archive'
2019-10-05T10:20:41.226+0500    reading metadata for testdbname.questionbank from archive 'testdbname_05-10-2019_00%3A55%3A31.archive'
2019-10-05T10:20:41.237+0500    restoring indexes for collection testdbname.org from metadata
2019-10-05T10:20:41.240+0500    Failed: testdbname.org: error creating indexes for testdbname.org: createIndex error: Values in v:2 index key pattern cannot be of type object. Only numbers > 0, numbers < 0, and strings are allowed.

For the people who will come here in the future the issue was, I was using MongoDB 4.0 on the local and server was on 4.2:

Starting in version 4.2, mongodump uses Extended JSON v2.0 (Canonical) format for the metadata files. To parse these files for restore, use mongorestore version 4.2+ that supports Extended JSON v2.0 (Canonical or Relaxed mode) format.
For details please see "Metadata Format" section on this : docs.mongodb.com/manual/reference/program/mongodump


In Sybase, what can I do to figure out why my Transaction Log is filling up? We have a process that runs each morning to load about 40,000 rows from one server to another.  In the past few weeks, the process has been having a 'log suspend' once or twice a week.  This is preceded by a "log segment has fallen critically low" message.

I am a programmer and not a DBA, but our DBA has said the problem is on the programming end.  But since neither the process nor the number of rows has changed, I think it's something on the database side.

The DBA said he increased the size of the transaction log a few times and that's not solving the problem.

Does anyone have any thoughts where I should start?

The transaction log is where Sybase stores all changes made to a database. It can be dumped separately from the database. Dumping the transaction log basically provides some kind of incremental backup functionality.

The first thing you need to figure out is whether you need the transaction log on this database i.e. is it OK for you if all data created since the last database dump are lost in case of a crash. If it is the case, a solution would be to enable truncation of the transaction log on checkpoint.
If you need the transaction log, you'll have to create a threshold action to dump it to a file. This will empty it and you can proceed.


SQL Server license from AWS Marketplace with ? cores We are planning to buy a SQL Server 2012 Enterprise edition license from AWS Marketplace for our company. We have exhausted our previous licenses and we need to add a new region with SQL Server in cloud. This SQL instance will be in peer-to-peer replication with SQL instances in other regions.
I also found Microsoft certified vendors who sell these licenses. These vendors clearly specify the number of cores for which SQL edition is licensed. In AWS Marketplace though, I don't find the number of cores specified. Does that mean if I use a R5.2X EC2 instance to host this SQL edition, it will be licensed (and billed) for 4 physical cores and if I use it in a R5.4x EC2 instance, it will be licensed(and billed) for 8 physical cores?
If anyone has experience buying SQL Server in AWS Marketplace, please advise.
Thanks in advance!

The market place should sell you the number of core licenses you need based on the instance size you select at purchase time.

The price will be based on a per hour or per year rate that you choose at check out. That price is just for the software license, you are still required to pay for the ec2 instance run costs as well as the EBS volume(s) attached to the instance and the data transfer costs.

Example:AWS Marketplace

In the screenshot below the selected instance of an m4.xl for a year the sql server licenses would cost $1607 however you would still need to pay the run cost for the m4.xl at $0.864 an hour plus $0.1 per GB of provisioned EBS storage.

If you were to assume as a simple example, you purchased the licenses for a year and ran the m4.xl on demand with an hourly rate of $0.864 and needed 100GB of EBS for the volume the cost would look:  1607 + ((720 hours per month * 12) * 0.864)+ ((0.1* 100GB) * 12 months) = $9,191.96 + (data transfer costs?)


PGAdmin 4 - server status / view log file I recently upgraded to Postgresql 9.6 and PgAdmin 4.

Now, one handy feature of PgAdmin 3 was the "server status" window, which I used to look at the log file.

I can't find that in PgAdmin 4. How can I view the server log in PgAdmin 4? Or is there a nice alternative?

I have followed the guide here to use a foreign data wrapper so I can access the log file through SQL.

First, create the extension:

create extension file_fdw;

Next, set up postgres.conf to support csv logging. Essentially, these settings:

  log_destination='csvlog'

  logging_collector='on'

  log_filename='postgresql.log'

And also the following, from the documentation:

  Set log_filename and log_rotation_age to provide a consistent,
  predictable naming scheme for your log files. This lets you predict
  what the file name will be and know when an individual log file is
  complete and therefore ready to be imported.

  Set log_rotation_size to 0 to disable size-based log rotation, as it
  makes the log file name difficult to predict.

  Set log_truncate_on_rotation to on so that old log data isn't mixed
  with the new in the same file.

Create, the server and the table, and the log can be queried!

create server logserver FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE postgres_log
(
  log_time timestamp(3) with time zone,
  user_name text,
  database_name text,
  process_id integer,
  connection_from text,
  session_id text,
  session_line_num bigint,
  command_tag text,
  session_start_time timestamp with time zone,
  virtual_transaction_id text,
  transaction_id bigint,
  error_severity text,
  sql_state_code text,
  message text,
  detail text,
  hint text,
  internal_query text,
  internal_query_pos integer,
  context text,
  query text,
  query_pos integer,
  location text,
  application_name text)
SERVER logserver OPTIONS (filename 'pg_log/postgresql.csv', format 'csv');

select message from postgres_log;


Transaction terminated running SELECT on secondary AG group Using SQL Server 2016, Always-On

Running a simple SELECT on a table within a secondary Availability Group.

select distinct some_column
from some_table oo (nolock)
inner join some_other_ ss (nolock) on ss.some_column= oo.some_other_column

SSMS eventually sends back this error:

  Msg 3948, Level 16, State 2, Line 14061 The transaction was terminated
  because of the availability replica config/state change or because
  ghost records are being deleted on the primary and the secondary
  availability replica that might be needed by queries running under
  snapshot isolation. Retry the transaction.

The same SELECT with (NOLOCK) works fine on the primary AG.

With or without the NOLOCK it fails most of the time 9/10 and sometimes executes correctly but that's rare. No changes were made to the AG at all.

There are. no changes in the AlwaysOn_Health extended event session.

The low_water_mark_for_ghosts in sys.dm_hadr_database_replica_states is NULL for some and for others there is a value. For those with a value, this is also the primary.

Using the script provided by Nic:

;
WITH    PrimaryStats
          AS ( SELECT   DB_NAME(database_id) AS DatabaseName ,
                        low_water_mark_for_ghosts ,
                        ar.replica_server_name ,
                        ar.availability_mode_desc
               FROM     sys.dm_hadr_database_replica_states hdrs
                        JOIN sys.availability_replicas ar ON hdrs.replica_id = ar.replica_id
               WHERE    ar.replica_server_name = @@SERVERNAME
             )
    SELECT  DB_NAME(database_id) AS DatabaseName ,
            hdrs.low_water_mark_for_ghosts AS LowWaterMarkSecondaryReplica,
            ps.low_water_mark_for_ghosts AS LowWaterMarkLocalReplica,
            ps.low_water_mark_for_ghosts - hdrs.low_water_mark_for_ghosts AS GhostWatermarkDiff,
            ar.replica_server_name AS ReplicaNode,
            DATEDIFF(SECOND, last_redone_time, GETDATE()) AS RedoDiffSec,
            last_redone_time
    FROM    sys.dm_hadr_database_replica_states hdrs
            JOIN sys.availability_replicas ar ON hdrs.replica_id = ar.replica_id
            JOIN PrimaryStats ps ON ps.DatabaseName = DB_NAME(database_id)
    ORDER BY
            DatabaseName ASC,'NODE ' + right(ar.replica_server_name ,1);

The output is:

When the LowWaterMarkSecondaryReplica is NULL, its because that's the instance I'm currently executing this query from.

This is caused by one or two things, much like the error says:

A config change was made to the AG
The primary restarted
The ghost cleanup low watermark was set

The first two are fairly obvious as to why this would be an issue. The 3rd is not.

Since secondary replicas automagically remap read-committed isolation level to the snapshot isolation level a few things need to kick in. One of those things is row versioning, since after all SI is an optimistic concurrency.

Row versioning depends on the version store rows to be available on the secondary. If the low water for ghost cleanup is changed, then the secondary may need to cleanup records that are currently used for queries being satisfied by the version store which have a timestamp lower than the new ghost cleanup low watermark. When this is the case, those versions will be cleaned up and any SI datasets that required them will no longer be valid. That's when this error will surface for the session for that dataset.

select distinct some_column
from some_table oo (nolock)
inner join some_other_ ss (nolock) on ss.some_column= oo.some_other_column

If this query runs quickly, I don't see why it should error out like that as I doubt your low watermark is changing that rapidly. Thus, I'm going to assume this takes some time to run. Having the appropriate indexes, not using a long transaction, and up-to-date statistics will help with this query. Additionally, the NOLOCK hint is completely ignored on the readable secondary, you may already know that and it may just be code re-use which is fine but I wanted to point it out anyway.

  We are considering using read-intent to resolve the issue, do you think that will resolve the problem?

ApplicationIntent is only used for two things in general with AOAGs:

read only routing (ROR)
to access AG Replicas that are set as secondary mode read-intent only.

This will not solve the underlying problem. IMHO you have two options, make the query run faster (if it isn't already) or open up a support ticket with MS. The reason I say open a ticket is this query should not constantly fail 24/7 and the issue should be looked into.


Mongo sharding issue with chunk split and Data transfer I am getting this error in my primary shard

warning: moveChunk failed to engage TO-shard in the data transfer: still waiting for a previous migrates data to get cleaned, can't accept new chunks, num threads: 3
Tue Jul 22 11:06:47.639 [conn405736] MigrateFromStatus::done About to acquire global write lock to exit critical section

This is from Mongos collection :

Thu Jul 24 11:01:52.056 [conn148389] warning: splitChunk failed - cmd: { splitChunk: "ibeat20140724.visitor", keyPattern: { host: 1 }, min: { host: "eisamay.indiatimes.com" }, max: { host: "timesofindia.indiatimes.com" }, from: "shard0000", splitKeys: [ { host: "navbharattimes.indiatimes.com" } ], shardId: "ibeat20140724.visitor-host_"eisamay.indiatimes.com"", configdb: "192.168.24.192:27017,192.168.24.54:27017,192.168.24.55:27017" } result: { who: { _id: "ibeat20140724.visitor", process: "ibeatdb61:27017:1399428004:822699867", state: 1, ts: ObjectId('53d09a4705eb5bebaa264d1f'), when: new Date(1406179911981), who: "ibeatdb61:27017:1399428004:822699867:conn426252:1780690214", why: "split-{ host: "eisamay.indiatimes.com" }" }, ok: 0.0, errmsg: "the collection's metadata lock is taken" }

This is in from change log collection:

{ "_id" : "ibeatdb61-2014-07-21T23:28:04-53cda20405eb5bebaae92ed5", "server" : "ibeatdb61", "clientAddr" : "192.168.22.106:57441", "time" : ISODate("2014-07-21T23:28:04.308Z"), "what" : "moveChunk.from", "ns" : "ibeat20140721.pageTrendLog", "details" : { "min" : { "host" : { "$minKey" : 1 } }, "max" : { "host" : "beautypageants.indiatimes.com" }, "step1 of 6" : 0, "step2 of 6" : 177, "note" : "aborted" } }

Could you please help me to resolve these errors

Regards
Viren

You really only have 2 choices when a primary is still processing deletes from previous migrations (which is why you are getting the failure to engage error):

Wait for the deletes to finish
Step down the primary of that shard (assuming it is a replica set)

The first action may take a long time if the shard in question is under heavy load, but it is the safest way forward.  The second option will cause the primary to step down to secondary and terminate the background threads for the deletes - if you have only a single node then your only option is a restart, and downtime.  Either way, this will allow the new primary (or restarted node) to accept migrations, but it will create orphans which you will need to manually clean up later.

If you are in any doubt, then the best thing to do is wait for the deletes to finish.


Need to fetch latest record always and ignore if there is no latest data Basically i want to send mail whenever there is a new record in this table throughout the day with list of all records got created.

I have a table with one date column. i have to always fetch the latest data based on that column and it should not give nay result if there is no latest record after the last one.

For ex:- latest record pulled at 9 AM and after that there is no update then it should not pull the same data again.

I would look at creating a trigger on the table, and having the trigger set to email the inserted rows as appropriate.

There is no point me explaining how exactly, as Aaron Bertrand gave a super answer to this premise here: https://stackoverflow.com/questions/10755469/send-e-mail-from-a-trigger

I have implemented this sucesfully to send email alerts when certain values change or rows are deleted. Works perfectly.


SQLite, update one row with another row data I have a following table
CREATE TABLE LoadRecords(RoomNum INTEGER DEFAULT 0, UserNum INTEGER DEFAULT 0, PresetNum INTEGER DEFAULT 0, PresetName TEXT DEFAUTL 'Preset', Load1 INTEGER DEFAULT 0, Load2 INTEGER DEFAULT 0, Load3 INTEGER DEFAULT 0, Load4 INTEGER DEFAULT 0, Load5 INTEGER DEFAULT 0, Load6 INTEGER DEFAULT 0, Load7 INTEGER DEFAULT 0, Load8 INTEGER DEFAULT 0, Load9 INTEGER DEFAULT 0, Load10 INTEGER DEFAULT 0, Load11 INTEGER DEFAULT 0, Load12 INTEGER DEFAULT 0, Load13 INTEGER DEFAULT 0, Load14 INTEGER DEFAULT 0, Load15 INTEGER DEFAULT 0, Load16 INTEGER DEFAULT 0, Load17 INTEGER DEFAULT 0, Load18 INTEGER DEFAULT 0, Load19 INTEGER DEFAULT 0, Load20 INTEGER DEFAULT 0, Circuit1 TEXT DEFAUTL '-', Circuit2 TEXT DEFAUTL '-', Circuit3 TEXT DEFAUTL '-', Circuit4 TEXT DEFAUTL '-', Circuit5 TEXT DEFAUTL '-', Circuit6 TEXT DEFAUTL '-', Circuit7 TEXT DEFAUTL '-', Circuit8 TEXT DEFAUTL '-', Circuit9 TEXT DEFAUTL '-', Circuit10 TEXT DEFAUTL '-', Circuit11 TEXT DEFAUTL '-', Circuit12 TEXT DEFAUTL '-', Circuit13 TEXT DEFAUTL '-', Circuit14 TEXT DEFAUTL '-', Circuit15 TEXT DEFAUTL '-', Circuit16 TEXT DEFAUTL '-', Circuit17 TEXT DEFAUTL '-', Circuit18 TEXT DEFAUTL '-', Circuit19 TEXT DEFAUTL '-', Circuit20 TEXT DEFAUTL '-')

I would like to update all Load1, Load2, Load3... Load20 from one row to few other rows within the same RoomNum but different UserNum inside of the same table.

is there a query that can do that for specific PresetNum ?
Edit.
This is what I have used. I wish SQLite supported loops as I really need to be able to change RoomNum plenty of times...

For MySQL (the question is MySQL-tagged)
UPDATE table t1, table t2
SET t1.field1=t2.field1, t1.field2 = t2.field2, ...
WHERE
      /* copy one record                     */
/*    t1.id = 123 AND t2.id = 456                       */
      /* copy a bunch of consecutive records */
/*    t1.id BETWEEN 111 AND 123 AND t2.id - t1.id = 321 */

For SQLite (the question is SQLite-tagged too)
UPDATE table
SET field1 = ( SELECT field1
               FROM table t
               WHERE t.id = 123 )
   /* or       WHERE t.id - table.id = 123     */
WHERE id = 456
/* or      WHERE id BETWEEN 444 AND 456        */


How can I tell if an index is being used to sort in MySQL? I have a query with an ORDER BY clause which uses a column which is the last column on an index which is being used in the WHERE clause, essentially of the form:

SELECT
  cols
FROM
  tables
WHERE
  col_1 = x
  AND col_2 = y
  AND col_3 = z
ORDER BY col_4

and the index is created on columns (col_1, col_2, col_3, col_4) in that order.

When I profile the query over 99% of the time is spent in the "Sorting result" state. col_4 is a timestamp column if that makes any difference. I understand that ORDER BY can only use an index under certain circumstances, but I'm still a bit mystified as to precisely when the optimiser would do so.

You should append EXPLAIN EXTENDED before query and see result yourself.

It should have an entry for

possible_keys

If this column is NULL, there are no relevant indexes. In this case, you may be able to improve the performance of your query by examining the WHERE clause to check whether it refers to some column or columns that would be suitable for indexing.

and

Keys

The key column indicates the key (index) that MySQL actually decided to use. If MySQL decides to use one of the possible_keys indexes to look up rows, that index is listed as the key value.

For more information you can refer this Explain output and Explain join_types


Stream Aggregate for Sort? Trying to understand Couple of things in the above query plan.

Why is there a Stream Aggregate in the query when I dont have a group
by.  I am guess it has something to do with the join being merge and
its doing a sort?
Secondly and more importantly why does an estimate    of 13 going
into the stream aggregate comes out as an estimate of    24,595,900?
This is causing the secondary problem of Object4 getting    Clustered
index scanned instead of a nested loop.  I had to split the query into two queries instead of using a OR and the join turns into a nested loop and seeks into Object4.

Query Plan

https://www.brentozar.com/pastetheplan/?id=BJk2eFLul

To answer your first question:

  Why is there a Stream Aggregate in the query when I dont have a group by. I am guess it has something to do with the join being merge and its doing a sort?

SQL Server is effectively rewriting your query. This query:

select Object1.Column1
FROM Object2 Object3 WITH (ROWLOCK)
INNER JOIN Object4 Object1 WITH (ROWLOCK) ON Object3.Column2 = Object1.Column1
WHERE Object3.Column2 in (SELECT Column2 FROM Object5)
or Object3.Column3 in (SELECT Column2 FROM Object5);

Can also be written like this:

select Object1.Column1
FROM
(
select Object3.Column2
FROM Object2 Object3 WITH (ROWLOCK)
WHERE Object3.Column2 in (SELECT Column2 FROM Object5)

UNION

select Object3.Column2
FROM Object2 Object3 WITH (ROWLOCK)
WHERE Object3.Column3 in (SELECT Column2 FROM Object5)
) Object3
INNER JOIN Object4 Object1 WITH (ROWLOCK) ON Object3.Column2 = Object1.Column1;

That's why you have two different access methods on Object2 in the query plan. The Merge Join (Concatenation) operation isn't actually a merge join. It's just implementing a UNION ALL and combining the results. The stream aggregate that you mentioned groups by Object3.Column2. This both removes duplicates from the Merge Join (Concatenation) and sorts the data so that it can be used in the following MERGE JOIN to Object4.

To answer your second question:

  Secondly and more importantly why does an estimate of 13 going
  into the stream aggregate comes out as an estimate of 24,595,900? This is causing the secondary problem of Object4 getting Clustered index scanned instead of a nested loop. I had to split the query into two queries instead of using a OR and the join turns into a nested loop and seeks into Object4.

It looks like a bug. After some searching I found an article by Paul White explaining the issue. The bug is reported to Connect if you want to vote for it or add yourself as affected.

In short, the cardinality estimate is 24595900 because 27328800 (table cardinality of Object2) * 0.9 = 24595900 after rounding. You're on SQL Server 2008 so the legacy CE calculation is used:

  A similar issue occurs with the pre-2014 cardinality estimator, but the final estimate is instead fixed at 90% of the estimated semi join input (for entertaining reasons related to a inversed fixed 10% predicate estimate that is too much of a diversion to get into).

I recommend reading through the entire article. The issue is a little difficult to summarize, but I will attempt to do so by quoting part of the text near the end:

  The cardinality estimator uses the Fixed Join calculator with 100% selectivity. As a consequence, the estimated output cardinality of the semi join is the same as its input, meaning all 113443 rows from the history table are expected to qualify.

  The exact nature of the bug is that the semi join selectivity computation misses any predicates positioned beyond a union all in the input tree. In the illustration below, the lack of predicates on the semi join itself is taken to mean every row will qualify; it ignores the effect of predicates below the concatenation (union all).

  A similar issue occurs with the pre-2014 cardinality estimator, but the final estimate is instead fixed at 90% of the estimated semi join input (for entertaining reasons related to a inversed fixed 10% predicate estimate that is too much of a diversion to get into).

Splitting up the query as you did should prevent the issue. You also might have some luck by rewriting the query using UNION (see Example 4 in the article).


4 operations with Postrgres (select price from product_price where product_id=1)/(select price from comp_product_price where comp_product_id =1)

I'm holding the current price in the product price and the competitor product price at the comp_product_price. I would like to get the ratio of these. Above query gives the syntax error.
How can i handle the issue? Thanks.

Use a JOIN:
select pp.price / cpp.price
from product_price pp
  join comp_product_price cpp on pp.product_id = cpp.comp_product_id
where pp.product_id=1


Confused about Primary Keys, and Foreign Keys Im brand new to database design and, I'm slightly confused about Primary Keys, and Foreign Keys and my database design.

Context:
I'm working on a database of museums and Categories will be one way the user will be able to search. e.g click on "Music" and a list of music related museums turn up. Pretty simple.

Problem:
I'm stuck and confused on the Normalisation process of the tables and IF I've got my keys in the right order.

This is what I've got...

I'm not sure, is this overly complicated? OR would it be easier, to add the contents of the "Location" and "Admission" table into the "Details" table and just relate the "Details" to "Categories" table. So, from my understanding going from 1-to-many (at present?) to then becoming 1-to-1???

—

I apologise if this makes no sense, I am new, obviously VERY confused. It's tricky teaching yourself!

Thanks everyone, I'm really grateful. If you need to see a larger image, it's here...

www.graphicsdesigned.co.uk/Museumscema3.jpg

Cheers!

Teaching yourself database design is indeed very tricky.  You might benefit from finding a good tutorial that would help you master the basics.  If you want suggestions concerning a good learning vehicle, ask.

Here is a very oversimplified summary of some of the basics.

Before you can design a database, you have to analyze the subject matter in some detail.  To quote GregH, "completely understanding how the business works".  One way to do this is to get some reliable source on Museums and how they work in the real world, or whatever approximation to the real world you are willing to settle for.  You may rely on your own memory for this, but it's better to get some input from experts on Museums, your subject matter.  ER modeling can help summarize what you learn.

Another thing you need to spend a little time on is how you intend to use the data.  Your design can be influenced by your intended use.  This can turn out to be a moving target.  You may decide to use the data in more than one way, somewhere in the future.  Think ahead as best you can.

Then, you are ready to design your tables.  Normalisation is one fairly mechanical approach to avoiding certain pitfalls that can creep up on you if you just design at random.  You table design should reflect the relational model of data.  The relational model is slightly different from the ER model, in that foreign keys are used to link tables together.  But the question of which table non key data should go in is nearly impossible to get right unless you have a clear understanding of the subject matter.

good luck.


Combine results from two queries? I need to combine the results from two queries into one result set and order them altogether.

This is the working, raw SQL code I have:

SELECT * FROM (
    SELECT
      t1.id AS entity_id,
      t2.title,
      'query1' AS entity_type
    FROM
      `table1` t1,
      `table2` t2
    WHERE
      t1.id = t2.id
  UNION ALL
    SELECT
      t3.id AS entity_id,
      t4.title,
      'query2' AS entity_type
    FROM
      `table3` t3,
      `table4` t4
    WHERE
      t3.id = t4.id
) AS results ORDER BY results.entity_id

The query above works perfectly, but I need to convert it into a specific syntax for a CMS database API, which does not support such queries.

I am looking for something without the SELECT * FROM(subquery) as such queries can be converted easily.

Is there any command I can use to UNION ALL and order records from both sub-queries?

Thank you!

I would consider taking the union queries and make a view using them.  You can then treat the union as if it was a table.


Question concerning minimal/cannonical cover For the question above, I am confused with how to approach simplifying the left hand side of BDCE->A. Do I get rid of E, as D depends on it, or do I get rid of CD as well, as CD->A?

Here's what I thought I did: Computed the closure of B,C,D,E,BC,BD,DE,CD,CE,BCD,BCE,BDE,CDE, then saw which closures actually use the dependency BCDE->A, and which dependencies actually get A. So far, CD is the smallest amount of attributes that get A.

The above leaves me with CD->A. Is this correct?

The algorithm to compute the minimal cover of a set of dependencies requires that, when there are multiple attributes on the left part, we should eliminate the extraneous ones, that is the attributes such that the remaining ones still determine the right part.

In other words, if you have a dependency like BCDE → A, you should in turn try to eliminate each attribute on the left part, and see if A is contained in the closure of the remaining attributes.

Let’s try the algorithm in your case:

1. Try to eliminate B, and see if A ∈ CDE⁺; CDE⁺ = ABCDE, so B can be eliminated
2. Try to eliminate C from CDE, and see if A ∈ DE⁺; DE⁺ = ABCDE, so C can be eliminated
3. Try to eliminate D from DE, and see if A ∈ E⁺; E⁺ = BCE, so D cannot be eliminated
4. Try to eliminate E from DE, and see if A ∈ D⁺; D⁺ = ABCDE, so E can be eliminated

So, the dependency BCDE → A can be substituted by D → A. Applying this algorithm to all the dependencies (remember to check at the end if there are superfluous dependencies), produces the following canonical cover:

D → A
D → E
E → B
E → C


Returning 1 Max Value when Some Counts Are Equal in Access I'm building a database that needs to return peak traffic data for Month-over-Month and Year-over-Year Comparisons in Access. We are trying to determine what our busiest Monday is, Tuesday, Wednesday, and so on in each month. The date needs to be attached to the return value. It should look like this.

Sep
Date    DOW Data
9/12    Mon 995
9/13    Tue 982
9/14    Wed 986
9/1     Thu 1004
9/2     Fri 1008
9/17    Sat 750
9/18    Sun 954

I'm trying to run a SQL query that counts all of the individual records for a day, sorts by Day of Week, finds the max, and then returns that value. We are running into two major problems though. I can get it to look like the table above without the Date attached by running this query.

SELECT DOW, MAX(Counts) AS Max
FROM
(
SELECT DOW, Start, Count(*) AS Counts
FROM Outbound
GROUP BY DOW, START
)
GROUP BY DOW;

*"DOW" is a number value signifying the day (1=Monday, etc...) and "Start"is the actual date.

It returns the below table.

DOW Max
1   995
2   982
3   986
4   1004
5   1009
6   750
7   954

This has everything I need but that date. However, the 'Max' Value is not unique, so I can't relate it back to an earlier table to get a date associated with this count, because there may be several that have that same value. I also can't use DISTINCT because the 'Start' Value is different on every record. We only need to return one date if there are several with the max value, because this is used more for MOM and YOY comparison, so inter-month comparison isn't important.

To sum it up, we can either get the right data, but without the date, or get the date, but too much data.

Any ideas?

When you join this result set (as a subquery?) back to the main table, why don't you do another GROUP BY and get the MAX(Start)?

Select MaxCount.DOW As DayNumberOfWeek, Max(Date) As LatestDate, MaxCount.Max
From
  (
  SELECT DOW, MAX(Counts) AS Max
  FROM
    (
    SELECT DOW, Start, Count(*) AS Counts
    FROM Outbound
    GROUP BY DOW, Start
    )
  GROUP BY DOW
  ) As MaxCount
  Inner Join
    (
    SELECT DOW, Start, Count(*) As Counts
    FROM Outbound
    GROUP BY DOW, Start
    ) As DailyCount On MaxCount.DOW = DailyCount.DOW And MaxCount.Max = DailyCount.Counts
Group By MaxCount.DOW, MaxCount.Max;

Apologies if I made some syntax errors, I don't have Access handy right now.


best local database (for PC) I have a PC which runs a local desktop application that connects to a logic server with a database.
additionally i need a small cache database in this PC so when the client goes offline for some reason i can still have some of the data to pull and still use

I read this article about local database. I can't use Microsoft Access because of it's access limitation of one process can access the mdb file  (sound ironic :) ). On the other hand I want to avoid using SQL Server Express because it has quite an overhead per PC installation and maintenance.

I do want a SQL engine that will allow me triggers so I'll be able to cache or extract data from the PC to the server and vice versa automatically and not develop a service to cache the data

NoSQL like mongoDB can't be use in here because the data structure is mad of a lot of relationships

Any opinion or experience in this area will be helpful

Consider SQLite.  It is lightweight, reliable, and well proven in production.  In fact, the creators claim it is the most widely deployed database in the world.

It works well as a data interchange format as well as a database, using tools like sftp to sync files between servers.  In addition, developers can easily work with SQLite files in just about any stack.


How can I figure out when a session opened a transaction to debug a session being blocked by a sleeping session that has an open transaction? I can reproduce a problem where my SQL Server stops responding.
I can see that one session is blocked by another session.
When I look at the status of the blocking_session_id, I see that it is 'sleeping'.
If I look at what sessions have open transactions I see that the sleeping session has an open transaction.

I have been using SQL Server Profiler to try to determine when it opened the transaction, but I have not been able to figure out how to make the trace log contain any events that look like a transaction being opened.

You should be able to use a couple of system views to get you where you want to go:

SELECT [dtat].[transaction_begin_time]
FROM [sys].[dm_tran_active_transactions] AS [dtat]
JOIN [sys].[dm_tran_session_transactions] AS [dtst]
    ON [dtat].[transaction_id] = [dtst].[transaction_id]
WHERE [dtst].[session_id] = @spid


Can I configure the PostgreSQL locale so that it ignores umlauts in ILIKE queries? I'm using PostgreSQL to look for city names matching a user-entered string. I'd like to be able to match a row with the name "München" (German for "Munich") if the user types "Munchen". In other words, I'd like to be able to ignore umlauts.

For example:

SELECT * FROM cities WHERE cities.name ILIKE Mu%

would match

cities
id        name
1         München

Is this possible?

A common solution to this problem is to have a "shadow" or "search" column for proper names. This solution has the advantage of being portable.

For example, here in Ireland, there are many people who are called MacGuire and there are those called McGuire. Depending on other names, some Irish people also use accents in a particular name and some don't. Furthermore, some people put a space between "Mac" and "Guire". And don't even get me started on the problems that names such as O'Malley cause with SQL, i.e. escaping the single quote.

So, in a shadow column, one might have rules
"Mac"/"Mc"/"Mac " -> MC (shadow columns are normally kept all caps - another source of varialbility (could have Mcguire). "O" and "O'" -> O.

What's more, say the first name was Rónán (Ronán and Ronan are also common), so another rule would be

á -> A and

ó -> O

You can apply these rules via ON INSERT/UPDATE triggers.

So, this would lead to a table thus:

Display_Name    Search_name    Address
------------    -----------    -------
Ronan MacGuire  RONAN MCGUIRE  1 Clontarf D1
Ronan McGuire   RONAN MCGUIRE  2 Malahide D2
Rónán Mcguire   RONAN MCGUIRE  3 Mary's terrace D3

And then the service agent will distinguish on the basis of address.

So, I am suggesting that something similar might be a solution to your problem.


Range Extraction Algorithm for Muti-Part aka Composite Indexes in MySQL I went through Range Extraction Documentation for MySQL Indexes. They have explained for single column indices. What is the procedure to reduce complex multi column (at least two) criteria?

What I am essentially asking is, is there an algorithm or any literature(s) that explains how to simplify/reduce range intervals from complex predicates? Assuming B-TREE index or any index with sequential arrangement of key values.

For example:-

Single Column Examples

From

(duration > 5 and duration < 10) or (duration < 100)

To

NULL < duration < 100

From

(duration > 5 and duration < 10) or (duration < 100)

To

5 < duration < 10
100 < duration

Two Column Examples

From

(duration in (9,10) and service_id > 500)

To

9 <= duration <= 9 AND 500 < service_id
10 <= duration <= 10 AND 500 < service_id

From

(duration in (9,10) and service_id = 500) or (duration = 19 and service_id=570)

To

9 <= duration <= 9 AND 500 <= service_id <= 500
10 <= duration <= 10 AND 500 <= service_id <= 500
19 <= duration <= 19 AND 570 <= service_id <= 570

Historically, MySQL has kept the relevant algorithms simple, even if less than optimal.  In particular, OR has been poorly optimized.  5.7 and 8.0 have new code, but I don't know if your examples have improved any.

EXPLAIN FORMAT=JSON sometimes gives clues.  Also, see the "Optimizer Trace".

If you have sample data, use the Handler counts to deduce whether they are well optimized:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts.

But, be aware that MySQL will often do a table scan instead of using an obvious index.  This is legitimate because of the cost of bouncing between the BTree for the secondary index and the BTree that contains the data.  Typically, if more than 20% of the index needs to be touched, it will prefer to do a table scan.  (The "20%" is no a hard-coded number, but is a gross simplification of the "cost-based" model that is used.)

Search for mysql cost based optimization.  There have been a few articles written on the topic.


Seconds_Behind_Master high even though Read_Master_Log_Pos and Exec_Master_Log_Pos are very close From what I understand from https://dev.mysql.com/doc/refman/5.7/en/show-slave-status.html, seconds_behind_master is the difference in the timestamp of where the IO thread is currently writing in the slave and where the SQL thread is executing now.  And the difference between Read_Master_Log_Pos and Exec_Master_Log_Pos should co-relate to the value of seconds behind master. But that is not the case here.

My DB had a significant slave lag, and I was trying to see if it was because of IO thread or the the SQL thread.

This is the result of show slave status.

         Slave_IO_State: Waiting for master to send event
                Master_Host: 10.85.162.164
                Master_User: replication
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: 1085162164-mysql-binary.001952
        Read_Master_Log_Pos: 148220048
             Relay_Log_File: mysqld-relay-bin.002127
              Relay_Log_Pos: 148035459
      Relay_Master_Log_File: 1085162164-mysql-binary.001952
           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: 148035282
            Relay_Log_Space: 148220460
            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: 540
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: 1085162164
                Master_UUID: 288abc1b-803e-11e7-9c70-02010a55a2a4
           Master_Info_File: /var/lib/mysql/master.info
                  SQL_Delay: 0
        SQL_Remaining_Delay: NULL
    Slave_SQL_Running_State: freeing items
         Master_Retry_Count: 86400
                Master_Bind:
    Last_IO_Error_Timestamp:
   Last_SQL_Error_Timestamp:
             Master_SSL_Crl:
         Master_SSL_Crlpath:
         Retrieved_Gtid_Set:
          Executed_Gtid_Set:
              Auto_Position: 0

This is the status on the master.

| File                           | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
| 1085162164-mysql-binary.001952 | 776669711 |              |                  |                   |

The master_log_file and relay_master_log_file are in the same file, which is also the same file that the master is writing into. Here the difference between Read_Master_Log_Pos and Exec_Master_Log_Pos is also very small around 180000.

But there is a large difference to the position in the position of the master, which would suggest that the IO thread is the one that is lagging.

From the docs, seconds_behind_master's description contained the following statement.

  In essence, this field measures the time difference in seconds between the slave SQL thread and the slave I/O thread.

Which should be very low in this case. But it is pretty high at 540 seconds.

Also, the value of seconds behind master is oscillating in values from 500 -> 0 -> 501 -> 0 -> 501 -> 0 -> 502 -> 0. What could cause this? One reason according to https://www.percona.com/blog/2013/09/16/possible-reasons-when-mysql-replication-lag-is-flapping-between-0-and-xxxxx/, might be that the IO link is saturated (#4), but I saw the network usage. It was around 50 MBps while the capacity of the link is 75 MBps. Any other possible reasons that could cause this?

Regarding the seconds_behind_master oscillation.
I found that the description in seconds_behind_master in the mysql documentation is not accurate. I checked the source code.

long time_diff= ((long)(time(0) - mi->rli->last_master_timestamp)
                            - mi->clock_diff_with_master);

Seconds behind master is calculated based on the difference between current timestamp on the slave and the timestamp on the statement which is being executed by the SQL thread and any initial difference in the clocks between the slave and the master.
And if the SQL thread is completely caught up with the IO thread (i.e. SQL thread has executed all the statements in the relay log file), it is shown as 0 in mysql5.6 (This was changed to show NULL in this case in 5.7)

In my case, the IO thread was the one that was lagging, I checked the status of the binlog file on the master and relay log file on the slave. The IO thread was waiting for the binlog_dump thread to send data to the slave. For some reason, the IO thread was not fetching/able to fetch all the data from the master(Could be a network related issue. Looking into it). The rate of data growth on the slave's relay log file was slower than the rate of the data growth on the master's binlog even when there was a lot of data that can be fetched. And before the IO thread is able to fetch more data and write to the relay log file, the SQL thread was finishing executing those statements. And seconds_behind_master becomes 0.

Whenever SQL thread is executing these events in the relay log file, it shows a huge lag and once it has completed executing these events, the value falls to 0.


MySQL pivot query trouble sorting column by total I am trying to return results for a matrix that represents the net weight that a country imports from another country.

I have managed to write a query that produces a pivot type table in mysql all data is coming from a single table. I have managed to dynamically create the columns (export countries) and sort rows by the total net weight imported by that country.

Where I am coming undone is sorting the export countries which are the columns. I am easily able to sort them alphabetically, however I need to somehow total each column when I dynamically generate that column and then sort by the sum of that column.

Here is an example of the table before the my query:

REPORTER | PARTNER | NET_WEIGHT | YEAR | COMMODITY
--------------------------------------------------
Spain    | USA     | 3          | 2010 | wheat
Mexico   | France  | 5          | 2011 | wheat
Norway   | USA     | 2          | 2012 | wheat
Egypt    | Canada  | 5          | 2010 | wheat
Germany  | UK      | 1          | 2011 | wheat
Peru     | France  | 3          | 2011 | wheat

This is an example of the structure that I am aiming to achieve.

REPORTER  | TOTAL  | USA   | France  | Canada  | UK
------------------------------------------------------
TOTAL     |        | 5     | 4       | 3       | 3
------------------------------------------------------
Spain     | 9      | 3     | 4       | 2       | NULL
Egypt     | 6      | 2     | NULL    | 1       | 3
Germany   | 3      | 1     | NULL    | NULL    | NULL

Here is the query I have produced so far which produces something similar to the above table, however not sorted correctly:

SET @@group_concat_max_len = 500000;
SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT CONCAT('GROUP_CONCAT(IF(`Partner` = ''', `Partner`,''', `NetWeight`, NULL)) AS ''',`Partner`,'''')
ORDER BY `Partner` ASC)
INTO @sql FROM `tblAnnualData`;

SET @sql = CONCAT('SELECT `Reporter`,SUM(`NetWeight`) AS Total,', @sql,' FROM `tblAnnualData`
                    WHERE `Commodity` = ''wheat''
                    AND `Year` = 2013
                    GROUP BY `Reporter`
                    ORDER BY `Total` DESC');

As you can see in the statement I am able to sort the columns by 'Partner' alphabetically but what I would like to achieve is sorting the sum of the column net_weight descending. So that the highest values are in the top left decreasing as you go right and down the table.

Can this be done? I have seen examples using  WITH ROLLUP but can't seem to get anything to work.

I've edited your example and I used WITH ROLLUP, CASE and FIELD statements to sort and make this:

Information:

mysql> SELECT * FROM test.tblAnnualData;
+----------+---------+------------+------+-----------+
| REPORTER | PARTNER | NET_WEIGHT | YEAR | COMMODITY |
+----------+---------+------------+------+-----------+
| Egypt    | Canada  |          5 | 2010 | wheat     |
| Germany  | UK      |          1 | 2011 | wheat     |
| Mexico   | France  |          5 | 2011 | wheat     |
| Norway   | USA     |          2 | 2012 | wheat     |
| Peru     | France  |          3 | 2011 | wheat     |
| Spain    | USA     |          3 | 2010 | wheat     |
+----------+---------+------------+------+-----------+
6 rows in set (0.00 sec)

Dynamic Query:

SET @@group_concat_max_len = 500000;
SET @QUERY1 = NULL;

SELECT GROUP_CONCAT(DISTINCT CONCAT(" SUM(CASE WHEN PARTNER = '",PARTNER,"' THEN NET_WEIGHT ELSE 0 END) AS '",PARTNER,"'")
ORDER BY PARTNER ASC)
INTO @QUERY1
FROM tblAnnualData;

SET @QUERY1 = CONCAT("SELECT
                        REPORTER,
                        TOTAL,
                        USA,
                        France,
                        Canada,
                        UK
                    FROM (SELECT
                            IFNULL(REPORTER,'TOTAL') AS REPORTER,
                            SUM(NET_WEIGHT) AS TOTAL,",@QUERY1," FROM tblAnnualData
                    WHERE COMMODITY = 'wheat'
                    #AND Year = 2011
                    GROUP BY REPORTER WITH ROLLUP) AS A
                    ORDER BY FIELD(REPORTER,'TOTAL') DESC,
                        TOTAL DESC,
                        REPORTER ASC;");
PREPARE QUERY1 FROM @QUERY1;
EXECUTE QUERY1;

It is the same as this query:

SELECT
    REPORTER,
    TOTAL,
    USA,
    France,
    Canada,
    UK
FROM (SELECT
        IFNULL(REPORTER,'TOTAL') AS REPORTER,
        SUM(NET_WEIGHT) AS TOTAL,
        SUM(CASE WHEN PARTNER='USA' THEN NET_WEIGHT ELSE 0 END) AS USA,
        SUM(CASE WHEN PARTNER='France' THEN NET_WEIGHT ELSE 0 END) AS France,
        SUM(CASE WHEN PARTNER='Canada' THEN NET_WEIGHT ELSE 0 END) AS Canada,
        SUM(CASE WHEN PARTNER='UK' THEN NET_WEIGHT ELSE 0 END) AS UK
    FROM tblAnnualData
    GROUP BY REPORTER WITH ROLLUP) AS A
ORDER BY FIELD(REPORTER,'TOTAL') DESC,
    TOTAL DESC,
    REPORTER ASC;

Why FIELD?

I used FIELD to sort by first when the field is TOTAL (that is the REPORTER aggregated field of the row generated by WITH ROLLUP), then I sort by the TOTAL of NET_WEIGHT. After that I finish with the REPORTER, just in case if some REPORTER has same TOTAL of other/others.

Testing the Dynamic Query:

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

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

mysql>
mysql> SELECT GROUP_CONCAT(DISTINCT CONCAT(" SUM(CASE WHEN PARTNER = '",PARTNER,"' THEN NET_WEIGHT ELSE 0 END) AS '",PARTNER,"'")
    -> ORDER BY PARTNER ASC)
    -> INTO @QUERY1
    -> FROM tblAnnualData;
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SET @QUERY1 = CONCAT("SELECT
    "> REPORTER,
    "> TOTAL,
    "> USA,
    "> France,
    "> Canada,
    "> UK
    "> FROM (SELECT
    "> IFNULL(REPORTER,'TOTAL') AS REPORTER,
    "> SUM(NET_WEIGHT) AS TOTAL,",@QUERY1," FROM tblAnnualData
    "> WHERE COMMODITY = 'wheat'
    "> #AND Year = 2011
    "> GROUP BY REPORTER WITH ROLLUP) AS A
    "> ORDER BY FIELD(REPORTER,'TOTAL') DESC,
    "> TOTAL DESC,
    "> REPORTER ASC;");
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE QUERY1 FROM @QUERY1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Statement prepared

mysql> EXECUTE QUERY1;
+----------+-------+------+--------+--------+------+
| REPORTER | TOTAL | USA  | France | Canada | UK   |
+----------+-------+------+--------+--------+------+
| TOTAL    |    19 |    5 |      8 |      5 |    1 |
| Egypt    |     5 |    0 |      0 |      5 |    0 |
| Mexico   |     5 |    0 |      5 |      0 |    0 |
| Peru     |     3 |    0 |      3 |      0 |    0 |
| Spain    |     3 |    3 |      0 |      0 |    0 |
| Norway   |     2 |    2 |      0 |      0 |    0 |
| Germany  |     1 |    0 |      0 |      0 |    1 |
+----------+-------+------+--------+--------+------+
7 rows in set, 1 warning (0.00 sec)

mysql>

Try it in SQLFiddle


Oracle 11g INDEX_SS Hint Given a table with columns A, B, and C of type CHAR(15) and an unique index

If I have a query with a WHERE clause specifying A and C, oracle's optimizer chooses to do an index range scan. But if I know that the cardinality of B is low, should I hint oracle to use an index skip scan? Will a skip scan be more performant skipping a non-leading column (in this case, B) than a range scan? Will the skip scan skip both A and B in the index or will it just skip B?

In general, the optimizer is much better at figuring out whether a range scan would be more efficient than a skip scan (or vice versa). If the optimizer is making the wrong choice, the problem almost always comes down to the human not providing the optimizer with the proper information in the form of accurate statistics on the table and on the indexes (including things like histograms on columns that contain skewed data).

For any given query, the only way to know with certainty which of two plans is more efficient is to try it out with your data on your hardware with your query. You can try adding the hint to force the plan to change and you can then measure which approach is actually more efficient. If the plan the optimizer is choosing isn't the most efficient, I would tend to suspect that the root cause is that some aspect of the statistics are not correct but that is something that you'd need to look at a 10053 trace to get to the bottom of in most cases. Rarely, there are cases where the optimizer is wrong because the optimizer isn't able to take into account some aspect of the data (for example, until recently, it was unable to deal with situations where the data in multiple columns was not independent such as a car table with make, model, and year columns where knowing one value constrained the set of valid values in the other columns).


What does % in this WHERE clause do? I'm doing training and one of the scripts has the following command:

SELECT SUM(Col2) FROM clust_table WHERE Col1 % 3 = 1

I would like to know what this snippet is for in the WHERE clause: Col1 % 3 = 1

I did some research on the internet and found no references about this command.

It is being used as a Modulo Operator; returning the remainder of a number divided by another.

In your example, the WHERE clause is limiting the results to only those where the Col1 value divided by 3 leaves a remainder of 1. (e.g. 4,7,10, etc.)


Good databases for unanchored LIKE condition Basically I need a database that is good with queries like LIKE %abc%.

I already tried PostgreSQL with GIN indexes and it is very good, but maybe there is something even better? I also tried MongoDB and found out that the query like "/abc/" works really badly, and Mongo indexes support only "/^abc/".

My database structure is very simple.

Example query in PostgreSQL:

SELECT DISTINCT(id), title FROM data AS data
INNER JOIN datatosynonym AS dts ON dts.data_id = data.id
WHERE dts.synonym_simple LIKE "%abc%"

And in MongoDB

db.data.find({synonymssimple: /abc/})

Where synonymssimple is an array of strings.

Example data in PostgreSQL

data table:

id | title | timestamp
 1 |  Abc  | 1145836800
 2 |  Qwe  | 1145836800

datatosynonym table:

id | synonym_simple | data_id
 1 |       abc      |   1
 2 |       bac      |   1

My benchmarks show the following results:

PostgreSQL with b-tree indexes and %abc% query - ~15ms per query
PostgreSQL with b-tree indexes and abc% query - ~1ms per query
PostgreSQL with GIN indexes and %abc% query - ~1.5ms per query
PostgreSQL with GIN indexes and abc% query - ~1ms per query
MongoDB without indexes and /abc/ query - ~25ms per query
MongoDB with b-tree indexes and /abc/ query - ~80ms per query
MongoDB with b-tree indexes and /^abc/ query - ~0.25ms per query

Sadly I can't use /^abc/ query.

One technique is to split the "unanchored" query into two "anchored" parts.

As you've shown a B-Tree search with a trailing wildcard is fast. The problem is you also need a leading wildcard. If you can turn the leading wildcard into a trailing one it would be good. The REVERSE function will help here. The query becomes

WHERE dts.synonym_simple LIKE "abc%"
AND dts.synonym_simple_reversed LIKE "cba%"

To be effective there must be an index on the reversed text. This is an overhead at write-time and will use additional storage. Waiting until read-time would require a scan of the data, which rather defeats the purpose. See examples here amongst others.


How to get sql server login name and their roles in a query I Need to create a query in SQL Server that will return login name and role.

However there's connection between the tables

Here is a solution I found it will give you the list of memberships on every DB on the server.

DECLARE @DB_USers TABLE
(DBName sysname,
 UserName sysname,
 LoginType sysname,
 AssociatedRole varchar(max),
 create_date datetime,
 modify_date datetime)

INSERT @DB_USers
EXEC sp_MSforeachdb

'USE [?]
 SELECT ''?'' AS DB_Name,
 CASE prin.name
    when ''dbo''
    THEN prin.name + '' (''+ (SELECT SUSER_SNAME(owner_sid) FROM master.sys.databases WHERE name =''?'') + '')''
    ELSE prin.name
 END AS UserName,
   prin.type_desc AS LoginType,
   ISNULL(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,
   create_date,
   modify_date

 FROM sys.database_principals prin
   LEFT OUTER JOIN sys.database_role_members mem
      ON prin.principal_id=mem.member_principal_id

 WHERE prin.sid IS NOT NULL
   AND prin.sid NOT IN (0x00)
   AND prin.is_fixed_role <> 1
   AND prin.name NOT LIKE ''##%'''

SELECT dbname,
       username,
       logintype,
       create_date,
       modify_date,
       STUFF(

     (SELECT ',' + CONVERT(VARCHAR(500),associatedrole)
      FROM @DB_USers user2

      WHERE user1.DBName=user2.DBName
        AND user1.UserName=user2.UserName

 FOR XML PATH('')),1,1,'') AS Permissions_user
 FROM @DB_USers user1

 GROUP BY dbname,
          username,
          logintype,
          create_date,
          modify_date

--HAVING username IN (<Use this to filter on multiple usernames.>)

 ORDER BY DBName,username


Multiple WHERE conditions on same column =val1 but doesnt=val2 or val3 I am trying to filter a table which has NON-UNIQUE transaction id's, joined to a products table on 1 common column. The filter needs to be as follows:

equal to :group1 AND
NOT equal to :group2 AND
NOT equal to :group3

I originally started with the below query but without the AND rk_group <> ____ conditions.

SELECT COUNT(DISTINCT txn_id)
                    FROM 1_txns
                    INNER JOIN 2_products USING (sku)
                    WHERE rk_group = :group1
                    AND rk_group <> :group2
                    AND rk_group <> :group3
                    ;

I have also tried

SELECT COUNT(DISTINCT txn_id)
                    FROM 1_txns
                    INNER JOIN 2_products USING (sku)
                    WHERE rk_group NOT IN ( :group2, :group3)
                    ;

I have also tried multiple combinations of joins and IN() and NOT IN() and it's still returning all teams ids including those where the NOT groups are present.

Can someone point me in the right direction please.

Schema info relevant for the query:

table 1_txns
(txn_id, sku)

table 2_products
(sku, rk_group)

Sample data

Txn_id, rk_group

------

    1,group1
    1,group2
    2,group1
    3,group1
    3,group3

If the above were my data, and group1 is my = group, and groups2 and 3 are my != groups, then I should return a row count of 1. Which is txn id '2'. All methods I've tried so far will return a count of 3.

NOTE: there are only 3 distinct rk_groups in the 2_products table.

Your WHERE clause does not make much sense, because it is applied to each row individually and it is pointless to check if the same value is equal to a and at the same time not equal to b or c – of course, it will not be equal to b or c if it is a. What you want instead, therefore, is for the conditions to be applied to a group of rows as a whole – more specifically, to each group of rows sharing the same txn_id.

So, you need to use GROUP BY and, to apply conditions to groups of rows, HAVING. This query will give you the list of txn_id values matching your requirements:

SELECT
    t.txn_id
FROM
    1_txns AS t
    INNER JOIN 2_products AS p USING (sku)
GROUP BY
    t.txn_id
HAVING
    COUNT(p.rk_group = :group1 OR NULL) > 0
    AND COUNT(p.rk_group IN (:group2, group3) OR NULL) = 0
;

As you do not seem to want the list, only the number of its items, use the above as a derived table to count the rows:

SELECT
   COUNT(*)
FROM
(
    SELECT
        txn_id
    FROM
        1_txns AS t
        INNER JOIN 2_products AS p USING (sku)
    GROUP BY
        t.txn_id
    HAVING
        COUNT(rk_group = :group1 OR NULL) > 0
        AND COUNT(rk_group IN (:group2, group3) OR NULL) = 0
) AS s
;

As you can see, COUNT(DISTINCT ...) is not necessary: the derived table is grouping by txn_id and thus cannot return duplicates – so, COUNT(*) is enough to get the correct result.

In case you are not aware, the OR NULL bit lets the COUNT function count only matches and omit mismatches, as explained in detail in this answer:

Why do I need “OR NULL” in MySQL when counting rows with a condition


SSAS OLAP Semi-Additive Measure over Time Dimension Issue:
Help, I'm relatively new to building OLAP cubes in Microsoft's SQL Server Analysis Services and I need help with a semi-additive measure over a time dimension.
In other words, I want to show the ending balance as a non-additive measure at quarterly and yearly aggregation points.
Facts:

I have a measure called Ending Balance which is the sum of all the accounts ending balances.

Dimensions:

I have a date dimension called As of Date. This dimension can be aggregated by year, quarter, and show monthly data points.

Desired Outcome:

I am using SQL Server Enterprise edition.
I've made sure that the Time dimension is properly specified with the correct types and attribute relationships. Reference link

Solution
The easiest way to accomplish the above is to set the CustomRollupColumn in your date dimension for both your Quarter and Year dimensions to the following equation:
'TAIL(NonEmptyCrossJoin(
    DESCENDANTS([Date].[Date Hierarchy].CurrentMember,1),1)
).item(0)'

Where:

[Date] = Dimension Name
[Date Hierarchy] = Your custom date hierarchy dimension

Important Considerations
You cannot manually set the CustomRollupColumn equation in Visual Studio editor. This field needs to provided by a column in your dimension dataset. While this seems a little strange, it actually provides you with a ton of flexibility.
To do this, create a custom column in your DSV (or include it in your dimension table). Here is an example:

Finally, once you've completed this step navigate to your date dimension and set your CustomRollupColumn parameter equal to this newly created field. Here is an example:

Make sure you do this for both your Quarter and Yearly dimension.
Final notes
I still need to conduct some tests, but if everything works I will come back and mark this as answered. So far, everything is working great. Partially completed quarters will reference the most recent provided month (data point). Same thing with the year dimensions.
Resources that were helpful in figuring this out:

MSDN Forum Post - Get Last Value
How to Determine the First or Last Member with Data
Using the descendants function
Creating custom roll-ups Major help!
Last child aggregation MSDN forum conversation


BI - Cube Concept and MDX Query Help In a Mondrian Schema, I have a fact table for Sales, a dimension table Time and a dimension table Client.

I need to create a Saiku report where i can show how many clients joined per year. I have a the Time dimension and a join_date in Client (which DO NOT link to the Time Dimension key, is just a date field),.

If I create a cube adding Join_date as a Level of the Client Table, I wouldn't know how to filter that latter in the Saiku.

Does anyone know how is the best way to add JOIN_DATE on client? I just need a report: Clients that have joined to the company by year.

And how should <Property> work in cubes? When i tested using this <Property>, it was not visible in Saiku...

you can do this with help of MDX Custom filer on filter option when we generated report for cube.

there you write query regaring dimension table and fact table with help of select measures.[attribute] on rows from cube name where conditon apply  . and so on...


In SQL Server, can user X be granted ability to change permissions for all users except Y? ...and if so, how?  Sorry for such a newbie question.

Thanks

You can grant the permission with the ability to grant to others but you would have to put something else in place to restrict that user from granting it to a specific user. It is grantable or not.


Database events that will cause deletion of index In SQL server if we try to change a data type of a column having index ,then it deletes the indexes associated with that column and changes the data type . I was not aware of it and was faced problem of index being deleted so i wanted to know is there any database changes of events (SQL commands) that will cause deletion of index.

No. To my knowledge, changing a column that is included in an index is not possible (you'll get an error message to that effect). You would have to drop the index before changing the column, and then re-apply the index again. The only exception to this that I am aware of is that you can change a column from NOT NULL to NULL without having to drop the index (but not the other way around).

However, if you use SSMS to change a table, SSMS may drop and re-create the index for you, but it shouldn't drop the index either way.

On a side note, if you have an indexed view, modifying the view till drop any indexes on the view.


How to check Oracle database connection encryption type How to check the Oracle database connection encryption type?(between Oracle database and other client applications)How do I assure between Oracle database and Toad the database connection is encrypted? (My Toad version is 9.5 and Oracle database version is 11g)

Don't confuse Oracle Advanced Security with encrypted SSL client connections to the database.  The Advanced Security option is comprised of two main features, Data Radaction and Transparent Data Encryption.  TDE is the encryption of data within tables, so that if someone captures the datafiles they won't be able to read table data in the clear inside the file.

An encrypted SSL connection between a client and the database is just part of the Oracle Net Services and is included with every version.  The Oracle documentation explains how to set that up.

To answer your question, run this query:

SELECT sys_context('USERENV', 'NETWORK_PROTOCOL') as network_protocol FROM dual;

If you are connected via SSL you will get the response

NETWORK_PROTOCOL
----------------
tcps

If you get "tcp," then you are on a non-SSL connection.


table is corrupted after getting 1114 error : table is full PC is Intel Core 2 Duo with 4G of RAM. Windows 7 32bit
innodb_buffer_pool_size=1G with innodb_buffer_pool_instances=2

I was loading data from CSVs into mysql table using mysql import command. Mysql gave me Table Full Error: 1114 at when my table size was exactly 4G. (Size of .idb file in the ProgramData directory.)
After that error, I am not able to query the able using SELECT command or not even able to use mysqldump on it. When I do so, Mysql57 service stops and I have to restart it. So inshort, I am not able to query my table.
I checked my error log and I got this error message every time, I query the table.
2016-12-23T23:08:40.158223Z 3 [ERROR] InnoDB: Trying to access page number 3 in space 86, space name ccrawlg/ccrawloct16, which is outside the tablespace bounds. Byte offset 0, len 16384, i/o type read. If you get this error at mysqld startup, please check that your my.cnf matches the ibdata files that you have in the MySQL server.
2016-12-23T23:08:40.161223Z 3 [ERROR] InnoDB: Server exits.
After searching the web, I seemed to me that my database is corrupted and then I tried inndo_force_recovery=1to6 in my my.ini file but unable to do mysqldump and take a backup.
All the above steps happened twice with me as I retried loading all my csvs after first failure.
When I retried, I noticed that the size of idb file was exactly the same as the previous idb file in kbs when i said - Table is full.

Edit:-

My disk doesn't lack in space, there is sufficient space on the disk. It's just that mysqlimport gave table full error at idb size of 4Gs.
My problem is exactly same as this post here :-
https://stackoverflow.com/questions/38245974/mysql-server-crashes-innodb-outside-the-tablespace-bounds
Import statement:-
`mysqlimport -i --fields-terminated-by="," --lines-terminated-by="\n" -uroot -psomething --local ccrawlg ccrawloct2016 --verbose
Show create table is below

CREATE TABLE ccrawloct2016

(
  'email' varchar(50) NOT NULL,
  'source' longtext,
  `date` varchar(20) DEFAULT NULL,
  `domain` mediumtext,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Link to my.ini file :-
https://drive.google.com/file/d/0BztaImknhgDFWUFsdEE2MXFxWDA/view?usp=sharing

I hope some expert out there could help me get out of this problem. I will provide more info needed.

Thanks in advance,

DROP the table.
Re-import, but make sure the table is MyISAM, not InnoDB.

Now you can at least see if the data is bad.  And you can experiment with INSERT INTO inno_table SELECT * FROM myisam_table.  There is a chance (slim chance) that CSV is the problem.  MyISAM is more compact on disk, so a larger file can be loaded before hitting 4GB (in the .MYD file).

Another thought...  Did you have indexes (other than the PRIMARY KEY)?  Try loading without the indexes in place.

Was the data in PK order?  That will make the loading faster and more efficient and take less space.  Can you sort the CSV before importing?

Generally, loading with the PK in place is best for InnoDB.  Please provide the CREATE TABLE so we know what you are working with.

Another thing to try:  Use the mysql commandline tool; within it, use the LOAD DATA command.  (I don't hold out much hope for this because I would assume that mysqlimport uses the same command.)

Yet another:

Break the source file into multiple pieces (each under, say, 1GB).  Import them into different tables.  Then use SQL statements to put them together.  And get rid of the intermediate tables.


DTUTIL performance slower in SQL Server 2012? We've noticed that since deploying Windows 2012/SQL Server 2012, the performance of DTUTIL has decreased. DTUTIL.exe consumes 50% on each of 2 cores on an 8 GB VM. The same syntax against Win 2008 R2/SQL Server 2008 R2 shows almost no impact on a VM with similar specs. Overall, it takes ~90-120 seconds to deploy a package.

We're using 64-bit SQL Server 2012 Enterprise Edition on SP1; the syntax of the command is:

"C:\Program Files\Microsoft SQL Server\110\DTS\Binn\dtutil.exe" /DestS COMPUTER1 /FILE "e:\folder\SSISPackage.dtsx" /COPY "SQL;FOO\BAR\SSISPackage" /QUIET

We have not yet tried this using the new SSIS approach in 2012. We have validated that the behavior repros across multiple different packages. We've also looked at all of the post-SP1 cumulative updates; they do not appear to address this problem, but we also have not applied them either to rule that out.

Has anyone else encountered/resolved this? Granted, package deployment is not a high frequency action, but we do notice it during our automated deployments.

The answer to this is yes, we had to convert the package to SQL 2012 format.  For now we keep our packages in SQL 2008 format in source control, and at deploy time migrate the page to 2012 format.  Once done, the performance is on par.


Fastest tool or technique for occasionally syncing Postgres 9.6 databases Occasionally I need to sync latest data and schema from production to staging db server. What tools or techniques do you recommend if syncing speed is a priority? The sync doesn't need to be in realtime.

Some inputs:

Postgres version is 9.6
DB size is less than 50GB
A full dump and restore takes about one hour. I want to beat this.

The staging db server is used for QA purpose. Whenever new code is merged, the staging app as well as the staging db will be updated. New code is merged several times a day.

One solution I'm thinking about: use something similar to the checkpoint concept.

Initially, restore a full dump to staging server.
Whenever a new code is merged:

2.1 Restore the staging db state to the latest check point
2.2 Get the latest state from production
2.3 Save a new checkpoint

Is it a good direction, and could I use the WAL as the "checkpoint" here?
Thank you.

Occasionally I need to sync latest data and schema from production to staging db server. What tools or techniques do you recommend if syncing speed is a priority? The sync doesn't need to be in realtime.

Well, normally in such a schemas you have to wipe the stating. You didn't say that explicitly but it's critical to your question. If you have people writing to staging and developing with staging then you can't just update it and assume that only the master wrote to it. In such a case your own method is to wipe and reload.

In order to that faster, you can simply, back up the data-directory or use pg_basebackup. Then when you need to reset the database,

Shut down the database,
Restore the data directory.
Copy over the wal files from the archive into ./pg_wal
Restart the server.

Note you'll have to keep around all of the WAL logs since your last base backup.

This is pretty well documented in the PostgreSQL 11 docs here,