SQL Server Instance How to recover memory used I know that may be this question has been answered a lot of times, but I need a better way to solve it or to demonstrate to my boss that this is a normal behaviour or there is something that I'm overlooking.
I'm not a DBA, I'm a developer, and like my previous job, I've to take the DBA Mantle because we don't have a formal DBA on site or no one want's to take this thing seriously.
I have three instances of SQL Server, one with SQL 2008 R2 and two with SQL 2016. All are from production environment.
The SQL 2008 R2 is from our main System, it is hosted in a Virtual Server in Hyper-V and is a Dedicated Database Server. No other apps are allocated on that server, it is a pure database server.
The issue is that the server is configured with 30 GB of RAM, and SQL Server is configured to use from 4 GB minimum to 22 GB maximum.

If I do a restart, obviously the server memory usage goes to a flat line at server performance manager, releasing memory from memory used to the minimum configured.

I need help understanding this situation, since all documentation point to an irrefutable fact that this is normal in SQL Server, that I've already documented myself about it, but my boss and colleagues state that this is not normal behaviour for the database. If I do numbers I think the server is requesting more memory. They say this is not a normal behaviour of SQL Server and that there is something we are overlooking (Jobs, and other stuff). To monitor or try to determine the Server Behaviour, I started sending Database Health Report every two hours, to try to check what's going on. For them, the expected behaviour is that SQL Server RAM should rise up and down in the performance graph and not stay at the top all the time, and as I understand this is the expected behaviour or SQL Server by design.

But in different points in time, jobs rise to execute, and they finish normally, some time long running transactions are running, but when I run queries for them after the report is generated, they are gone.
Is there something I'm overlooking? Is this Normal?  Is there a way to release memory without rebooting the instance?  I've already tried a bunch of solutions

Is this Normal?

Yes.

my boss and colleagues state that this is not normal behaviour for the database

SQL Server will grow its memory usage up to your configured max server memory and only reduce its memory usage if the system runs low on physical memory.
If the system has plenty of available RAM, SQL Server will use it to cache data and execution plans, and to have memory on-hand to run queries.
If Windows sends out a global "low memory" signal SQL Server responds by triming its caches and de-allocating memory.
This is explained in detail in the docs:

The default memory management behavior of the SQL Server Database
Engine is to acquire as much memory as it needs without creating a
memory shortage on the system. The SQL Server Database Engine does
this by using the Memory Notification APIs in Microsoft Windows.
When SQL Server is using memory dynamically, it queries the system
periodically to determine the amount of free memory. Maintaining this
free memory prevents the operating system (OS) from paging. If less
memory is free, SQL Server releases memory to the OS. If more memory
is free, SQL Server may allocate more memory. SQL Server adds memory
only when its workload requires more memory; a server at rest does not
increase the size of its virtual address space.
Max server memory controls the SQL Server memory allocation, compile
memory, all caches (including the buffer pool), query execution memory
grants, lock manager memory, and CLR1 memory (essentially any memory
clerk found in sys.dm_os_memory_clerks).

Memory Management Architecture Guide - Dynamic Memory Management

Is there a way to release memory without rebooting the instance?

You can reduce the max server memory and SQL Server will trim its caches and reduce its memory usage online.  After it drops, you can increase max server memory again and SQL Server will re-grow the caches over time.
But this procedure is operationally pointless.  The memory isn't needed for anything else, and if it were, SQL Server would respond to the system low memory signal by reducing its memory utilization.


MySQL Database import into 5.7 hangs with CLI or with Workbench 6.3 CE I have a SQL dump file that I exported using Workbench from a server that is being decommissioned. The host and target OS is Microsoft Windows Server 2008 R2 (therefore is x64 natively). There seems to be no way in hell to import this SQL dump file as it will hang no matter what. File size is less than 500Mb. I did look at the database schema itself and tables seem to have overwritten the initially created DB with the same name, however the import will not do anything but hang. I'm not a DBA, just a network/sysadmin so I am most frustrated with getting this thing to import. Any suggestions anyone?

I ran into similar problem importing a 1.2G SQL File and it took about 10 hours on my Laptop (Core i-5 x64 8G RAM) Of course I was multitasking.

To find out the progress, I'd query every hour using this query

select table_name as "Table", round(((data_length + index_length) / 1024 / 1024), 2) AS "SIZE" from information_schema.TABLES where table_schema = "Name_of_Database_You_Import";

Thanks


MySQL RDS Slave stop replicating I have a master DB running on internal network and an AWS RDS slave replica from the Master DB. When I upload a fresh copy of the master, the slave will replicate for few days with no issues. After a couple of days, the slave status says its correct but records will be missing on the DB.
I ran both

show master status

AND

show slave status

The Master_log_file and Read_Master_log_Pos are matching.
One thing to mention we ran a huge dump on the master daily from a legacy system.

What could be the reason for the discrepancy  between the two and how to fix it ?

Thanks in Advance.

Update: Today we got yesterday data. It is possible that the amount of data we are replicating everyday cause the slave to get clogged and take time to get update ?

Update 2

After leaving Slave during the weekend. the Seconds_behind_Master went
 down to zero. Also here the result of show slave status

Slave_IO_State:    Waiting for master to send event,
Master_Host:    master_host_url,
Master_User:    replica,
Master_Port:    3306,
Connect_Retry:  60,
Master_Log_File:    mysql-bin.000325,
Read_Master_Log_Pos:    33015975,
Relay_Log_File: relaylog.003679,
Relay_Log_Pos:  54412,
Relay_Master_Log_File:  mysql-bin.000325,
Slave_IO_Running:   Yes,
Slave_SQL_Running:  Yes,
Replicate_Do_DB:    ,
Replicate_Ignore_DB:    ,
Replicate_Do_Table: ,
Replicate_Ignore_Table: mysql.plugin,mysql.rds_monitor,mysql.rds_sysinfo,mysql.rds_replication_status,mysql.rds_history,innodb_memcache.config_options,innodb_memcache.cache_policies,
Replicate_Wild_Do_Table:    ,
Replicate_Wild_Ignore_Table:    ,
Last_Errno: ,
Last_Error: ,
Skip_Counter:   ,
Exec_Master_Log_Pos:    33015975,
Relay_Log_Space:    142937,
Until_Condition:    None,
Until_Log_File: ,
Until_Log_Pos:  ,
Master_SSL_Allowed: No,
Master_SSL_CA_File: ,
Master_SSL_CA_Path: ,
Master_SSL_Cert:    ,
Master_SSL_Cipher:  ,
Master_SSL_Key: ,
Seconds_Behind_Master:  ,
Master_SSL_Verify_Server_Cert:  No,
Last_IO_Errno:  ,
Last_IO_Error:  ,
Last_SQL_Errno: ,
Last_SQL_Error: ,
Replicate_Ignore_Server_Ids:    ,
Master_Server_Id:   1,
Master_UUID:    ,
Master_Info_File:   mysql.slave_master_info,
SQL_Delay:  ,
SQL_Remaining_Delay:    ,
Slave_SQL_Running_State:    Slave has read all relay log; waiting for the slave I/O thread to update it,
Master_Retry_Count:Master_Bind: 86400,
Last_IO_Error_Timestamp:    ,
Last_SQL_Error_Timestamp:   ,
Master_SSL_Crl: ,
Master_SSL_Crlpath: ,
Retrieved_Gtid_Set: ,
Executed_Gtid_Set:  ,
Auto_Position   ,

Judging from the comments and your update to the question, it looks like the case where the data being loaded from legacy system is taking too long to be applied to the SLAVE.

Without the information on what your binlog_format is set to, I can only assume that it is set to ROW. You can try setting this to MIXED and then the SLAVE updates should run a lot quicker and not lag behind the master unless there are other factors affecting the SLAVE being slow.

When logged in as a privileged user, run the following from MySQL client:

SET GLOBAL binlog_format='MIXED';

If the above resolves your problem, then add this to your /etc/my.cnf to make it permanent across MySQL restarts.


How does Data Warehousing work in reverse? I've made many researches about data warehousing to learn more about it and I think I've grasped the idea. But I've read in an article or two that data warehousing allows you to do analysis on data brought from operational databases and then in some cases, the results are sent back to the operational databases where some data is updated and then used.

Could someone give me an example where that might work?

Because what I personally understood is that data warehousing allows to analyse the data in order to determine sales profitability or find patterns in sales, etc.

So is what I've read true? If so, are there examples?

This question is a bit too broad for a definitive answer, because there are endless possibilities for data to flow in all sorts of directions between various data stores. I also find it strange that the articles you read did not provide examples.

One general application of a data warehouse feeding back information to an operational system would be analytics, calculated over historical data, affecting operational system rules, such as:

Consolidated (in a DWH) daily sales data from all stores of a retailer are used to determine certain product discounts in the point-of-sale system and goods delivery schedules in the logistics application.
Bank capital level per Basel rules are calculated in the DWH nightly and generate orders and trading limits in the trading application for the next day.
Customer churn prediction model of a telco tells their marketing call center who to spam-call next.


Postgres query column with comma-delimited strings for array of strings I have a table that has data that looks like this:

id  oe
--  ---------------------------------------------------
 1  04715SNAA90,04715SNAA90ZZ,71501SNAA00,71501SNAA00ZZ
 2  64526915083,64526953474
 3  04715SNAA90,04715SNAA90ZZ,71501SNAA00,71501SNAA00ZZ
 4  F4DH8C607AA,F4DHAA
 5  F4DH8C607AA,F4DHAA
 6  04715SNAA90,04715SNAA90ZZ,71501SNAA00,71501SNAA00ZZ
 7  64526915083,64526953474
 8  F4DH8C607AA,F4DHAA
 9  F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA
10  F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA
11  04715SNAA90,04715SNAA90ZZ,71501SNAA00,71501SNAA00ZZ
12  64526915083,64526953474
13  F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA
14  F4DH8C607AA,F4DHAA
15  F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA

How can I write a query where I can pass an array of values and return rows where that column contains any of the values in the array?

This works because I get another value from a lookup table and I'd rather not do it that way as there is no index on the hol column in the pubnet table.
[please don't suggest adding one as I cannot modify DB]

SELECT * FROM pubnet
where hol in (select hol from id2hol where id in ('F3DH19860AA', 'F4DHAA'))

This would be the result set:

id  oe
--  ---------------------------------------------------
 4  F4DH8C607AA,F4DHAA
 5  F4DH8C607AA,F4DHAA
 8  F4DH8C607AA,F4DHAA
 9  F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA
10  F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA
13  F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA
14  F4DH8C607AA,F4DHAA
15  F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA

You need to convert the comma separated values to an array, then you can use the overlaps operator &&

select *
from pubnet
where string_to_array(oe, ',') && array['F3DH19860AA', 'F4DHAA']


decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5 i recently testing the move of our db from one server to another.
as part of this process i copied all the data to the new db and started testing it.
as a comparison i run a store procedure whose execution on the old DB took about 2.5 hours on the new db it took about 8.
now the amount of data is the same and the sp is the same the differences is in:

old db

mysql version 5.0.45
pc
OS Name Microsoft Windows XP Professional
Version 5.1.2600 Service Pack 3 Build 2600
Processor   x86 Family 6 Model 15 Stepping 13 GenuineIntel ~2394 Mhz
Total Physical Memory   2,048.00 MB

new db

mysql version 5.5.25a
pc
OS Name Microsoft(R) Windows(R) Server 2003, Standard Edition
Version 5.2.3790 Service Pack 2 Build 3790
System Manufacturer Dell Inc.
System Model    PowerEdge 2900
System Type X86-based PC
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Processor   x86 Family 6 Model 15 Stepping 11 GenuineIntel ~1862 Mhz
Total Physical Memory   4,090.64 MB

if any one has any idea what to check i will be happy to hear.

one of the SP that takes now alot of time is:

BEGIN
DECLARE tempSlot INT(10);
DECLARE tempPort INT(10);
DECLARE tempOnx INT(10);
DECLARE tempLagId INT(10);
DECLARE tempIp TEXT;

DROP TABLE IF EXISTS `temp_ports_table`;
CREATE TEMPORARY TABLE `temp_ports_table` (
  `ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `IP` TEXT,
  `LAG` INT(10) UNSIGNED ,
  `SLOT` INT(10) UNSIGNED ,
  `PORT` INT(10) UNSIGNED ,
  `ONX` INT(10) UNSIGNED ,
    PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO temp_ports_table (IP, LAG)
    SELECT DISTINCT IP, LAG_ID
    FROM  n_to_1_table
    WHERE LAG_ID IS NOT NULL AND LAG_ID > 0;
INSERT INTO temp_ports_table (IP, SLOT, PORT, ONX)
    SELECT DISTINCT IP, SLOT, PORT, ONX
    FROM  n_to_1_table
    WHERE LAG_ID IS NULL OR LAG_ID <= 0;
SET @numOfPortsInNto1Table:=(
    SELECT count(ID)
    FROM temp_ports_table accessChassis
    GROUP BY ID>0);
IF @numOfPortsInNto1Table<=0 THEN
  SET @numOfPortsInNto1Table:=0;
END IF;
 INSERT INTO debug_table(DATE, TIME, RESULT) SELECT CURDATE(), CURTIME(), 'N:1 Modifications Stored Procedure : Created Temporary Tables';

SET @tempNto1tableCounter:=1;
WHILE @tempNto1tableCounter<=@numOfPortsInNto1Table DO

    SET @tempFlowInstanceId := 0;

    SELECT tempPortsTable.SLOT, tempPortsTable.PORT, tempPortsTable.ONX, tempPortsTable.LAG, tempPortsTable.IP
        INTO tempSlot, tempPort, tempOnx, tempLagId, tempIp
            FROM temp_ports_table tempPortsTable
            WHERE tempPortsTable.ID=@tempNto1tableCounter;

    IF (tempLagId IS NOT NULL AND tempLagId > 0) THEN
        UPDATE n_to_1_table
            SET INSTANCE_ID=@tempFlowInstanceId:=@tempFlowInstanceId+1
            WHERE
                IP=tempIp AND
                LAG_ID=tempLagId;
    ELSE
        UPDATE n_to_1_table
            SET INSTANCE_ID=@tempFlowInstanceId:=@tempFlowInstanceId+1
            WHERE
                IP=tempIp AND
                SLOT=tempSlot AND
                PORT=tempPort AND
                ((tempOnx IS NULL AND ONX IS NULL) OR ONX=tempOnx) AND
                (LAG_ID IS NULL OR LAG_ID <= 0);
    END IF;
    SET @tempNto1tableCounter:=@tempNto1tableCounter+1;

END WHILE;
 INSERT INTO debug_table(DATE, TIME, RESULT) SELECT CURDATE(), CURTIME(), 'N:1 Modifications Stored Procedure : Modified All N:1 Flow INstance ID According To Interface';
 DROP TABLE IF EXISTS `temp_ports_table`;
 INSERT INTO debug_table(DATE, TIME, RESULT) SELECT CURDATE(), CURTIME(), 'N:1 Modifications Stored Procedure Finished';
END

If all your data is InnoDB, I have a surprise for you.

It is a little-know fact, but there are some occasions when an older version of MySQL/InnoDB can outrun/outgun a newer of MySQL/InnoDB. How can that be possible?

I wrote about this in DBA StackExchange before

Why mysql 5.5 slower than 5.1 (linux,using mysqlslap) (Nov 24, 2011)
Query runs a long time in some newer MySQL versions (Oct 05, 2011)
Multi cores and MySQL Performance (Sep 20, 2011)
How do I properly perform a MySQL bake-off? (Jun 19, 2011)

You will have to perform some due diligence in configuring MySQL to harness more threading and more CPUs.


Will the source dataset for a cursor update on each iteration if within the cursor loop I'm updating one of the tables that dataset references? In this example:

DECLARE @KeyField INT, @ValueField INT

DECLARE CursorName CURSOR FAST_FORWARD
FOR SELECT T1.KeyField, T1.ValueField
FROM Table1 AS T1
LEFT JOIN Table2 AS T2
    ON T1.ValueField = T2.ValueField
WHERE T2.KeyField IS NULL -- Filter out any records that have the same value in T2 as T1

OPEN CursorName FETCH NEXT FROM CursorName
INTO @KeyField, @ValueField

WHILE (@@FETCH_STATUS = 0)
BEGIN

    INSERT INTO Table2 (KeyField, ValueField)
    SELECT @KeyField, @ValueField

    FETCH NEXT FROM CursorName
    INTO @KeyField, @ValueField

END

CLOSE CursorName
DEALLOCATE CursorName;

(I forgot how much I "loved" cursors.)

Would the update to Table2 inside the cursor loop body, cause that value to be filtered out from the dataset on the next iteration of the loop?...in other words does updating Table2 modify the dataset that uses Table2 for the original cursor definition.

in other words does updating Table2 modify the dataset that uses
  Table2 for the original cursor definition.

You are inserting data into table2 and these data changes could be visible unless you specify the STATIC keyword.

STATIC

  Specifies that the cursor always displays the result set as it was
  when the cursor was first opened, and makes a temporary copy of the
  data to be used by the cursor. All requests to the cursor are answered
  from this temporary table in tempdb

There are other implications to what data is visible to the cursor depending on the declaration of the cursor.

Keywords such as KEYSET, DYNAMIC, FAST_FORWARD,FORWARD_ONLY impact this behaviour.

An example in the documentation on FORWARD_ONLY

  ...All insert, update, and delete statements made by the current user (or
  committed by other users) that affect rows in the result set are
  visible as the rows are fetched. Because the cursor cannot be scrolled
  backward, however, changes made to rows in the database after the row
  was fetched are not visible through the cursor...

When using FORWARD_ONLY, data could be visible depending on what row the cursor is located at.

However, in your example, due to the filter used:

WHERE T2.KeyField IS NULL -- Filter out any records that have the same value in T2 as T1

Even if new rows are added, the inserted data will be filtered out as non nulls are inserted into Table2.

You could test this adaptation of your query to create an endless looping cursor.

DROP TABLE dbo.Table1;
DROP TABLE dbo.Table2;
CREATE TABLE dbo.Table1(KeyField int,ValueField int);

INSERT INTO dbo.Table1
SELECT TOP(10) ROW_NUMBER() OVER( ORDER BY(SELECT NULL)),
ROW_NUMBER() OVER( ORDER BY(SELECT NULL))
FROM MASTER..spt_values;

CREATE TABLE dbo.Table2(KeyField int,ValueField int)
INSERT INTO dbo.Table2
SELECT TOP(10) ROW_NUMBER() OVER( ORDER BY(SELECT NULL)),
ROW_NUMBER() OVER( ORDER BY(SELECT NULL))
FROM MASTER..spt_values;

DECLARE @KeyField INT, @ValueField INT;
DECLARE @counter int = 0;

DECLARE CursorName CURSOR
FOR SELECT T1.KeyField, T1.ValueField
FROM Table1 AS T1
LEFT JOIN Table2 AS T2
    ON T1.ValueField = T2.ValueField
WHERE T2.KeyField IS NOT  NULL; -- CHANGED TO NOT NULL

OPEN CursorName FETCH NEXT FROM CursorName
INTO @KeyField, @ValueField

WHILE (@@FETCH_STATUS = 0)
BEGIN

    INSERT INTO Table2 (KeyField, ValueField)
    SELECT @KeyField, @ValueField
        SELECT @KeyField, @ValueField
    FETCH NEXT FROM CursorName
    INTO @KeyField, @ValueField
    SET @counter += 1
END

CLOSE CursorName
DEALLOCATE CursorName;

SELECT @counter;


Select a range of integers I have a 'range' of integers (in this case destination ports) that occur:

For example: 33848-33254.

I want to select 'all' of these without having to do one at a time. is there a range operator? Or should I just use 'between'?

You can use BETWEEN or greater than/less than operators:

select *
from yourtable
where col1 >= 33254 and col1 <= 33848;

or

select *
from yourtable
where col1 between 33254 and 33848;

see SQL Fiddle with Demo


How to troubleshoot hung queries in PostgreSQL RDS? I have strange behavior from an Amazon RDS instance running PostgreSQL 9.6.11. This is new behavior from a system that has otherwise been behaving properly without any changes to code in the last few months. Additionally, we have a staging environment that the latest database and code changes were replicated to and it is not having these problems.

Nightly, thousands of jobs are run against the database server that consist mainly of very large SELECT queries (In the order of 8000 lines or so) that calculate statistics. Queries that typically take up to 1 minute, hang up, and do not complete, even after hours. However, if the server is rebooted to clear the queries, then I can run the same queries manually and they complete normally. The jobs will continue to process normally, and then eventually hang up again on stuck SELECT statements (usually 5 or 6, and it is not the same statements. They all vary slightly.).

I have done a lot of research and troubleshooting, but after determining that the query wasn't the problem, I'm at a loss.

The hung statements are all in active state and have been processing for hours. The server is pegged at 99%+ load.

What things should I look for? What problems could cause this issue? Why would a combination of large SELECT statements hang up? What commands can I run to get more information?

The most important information in addition to the connection state of the hung queries would be whether they are in any particular wait event.

You can get that information by running “SELECT state, wait_event_type, wait_event FROM pg_stat_activity” (you may need to collect this in a cron job to get the data over time)

Note that RDS also shows this information in “Performance Insights”, if you have it enabled.

Since you are on an older Postgres patch release, I would also investigate an upgrade of the minor version to see if that fixes the issue you are seeing.


Which collation should I use for biblical Hebrew? Which SQL Server collation should I use for biblical Hebrew? The database under consideration needs to accommodate diacritics (i.e., vowels, accents, trope, etc.).

First: There is no distinction, collation-wise, between biblical Hebrew and modern Hebrew. We are just dealing with Hebrew.

Second: Regardless of anything else, you want to use the newest set of collations, which are the _100_ series as they have newer / more complete sort weights and linguistic rules than the older series with no version number in the name (technically the are version 80). Normally I highly recommend using the newest version of whatever collation you need, but at least in this one instance there is good reason to use the version with no version number in the name. The version 100 (or newer) collations are far more complete, and can distinguish between supplementary characters (or even fully support them if using an SC or 140 collation), but assuming that you are not dealing with supplementary characters, then the version 80 (no version number in name) collations do a better job of handling Hebrew (see item "Sixth" below).

Third: There is no concept of "Kana" (or Kana-type) in Hebrew, so you can ignore any of the collation variations with _KS in their names (as that is a sensitivity that you will never use).

Forth: The collations ending in _SC support supplementary characters (i.e. full UTF-16) so usually best to pick one of those, if available (meaning: if you are using SQL Server 2012 or newer).

Fifth: You don't want a binary collation (_BIN or _BIN2) as those can't distinguish between Hebrew letters with both vowels and cantillation marks that are the same but have the combining characters in different orders, nor can they ignore vowels and other marks to equate things like א and אֽ.

For example (vowel and cantillation mark combining characters in opposite order):

SELECT NCHAR(0x05D0) + NCHAR(0x059C) + NCHAR(0x05B8),
       NCHAR(0x05D0) + NCHAR(0x05B8) + NCHAR(0x059C)
WHERE  NCHAR(0x05D0) + NCHAR(0x059C) + NCHAR(0x05B8) =
       NCHAR(0x05D0) + NCHAR(0x05B8) + NCHAR(0x059C) COLLATE Hebrew_100_CS_AS_SC;
-- אָ֜  אָ֜

SELECT NCHAR(0x05D0) + NCHAR(0x059C) + NCHAR(0x05B8),
       NCHAR(0x05D0) + NCHAR(0x05B8) + NCHAR(0x059C)
WHERE  NCHAR(0x05D0) + NCHAR(0x059C) + NCHAR(0x05B8) =
       NCHAR(0x05D0) + NCHAR(0x05B8) + NCHAR(0x059C) COLLATE Hebrew_100_BIN2;
-- no rows

Sixth: It depends on how you will be interacting with the string values. Hebrew does not have upper-case/lower-case letters, yet there are a few code points that are affected by case-sensitivity. There are even a few code points that are width-sensitive. Accent-sensitive / insensitive affects diacritics used for vowels, pronunciation, and cantillation marks (i.e. trope).

Do you need to distinguish between final and non-final forms of the same letter? There are five letters in Hebrew that look different when used as the final letter of a word. SQL Server handles this via case-sensitivity / _CS collations (though, unfortunately, it appears to be broken in the newer, and usually better, version 100 and newer collations):

SELECT NCHAR(0x05DE) AS [Mem],
       NCHAR(0x05DD) AS [Final Mem]
WHERE  NCHAR(0x05DE) = NCHAR(0x05DD) COLLATE Hebrew_CI_AS_KS_WS;
-- 1 row (expected; all sensitive except case)
-- Mem  Final Mem
-- מ    ם

SELECT NCHAR(0x05DE) AS [Mem],
       NCHAR(0x05DD) AS [Final Mem]
WHERE  NCHAR(0x05DE) = NCHAR(0x05DD) COLLATE Hebrew_CS_AI;
-- no rows (expected; all insensitive except case)

SELECT NCHAR(0x05DE) AS [Mem],
       NCHAR(0x05DD) AS [Final Mem]
WHERE  NCHAR(0x05DE) = NCHAR(0x05DD) COLLATE Hebrew_100_CI_AI;
-- no rows (expected 1 row; all insensitive)

Do you need to distinguish between pronunciation marks, vowels, and cantillation marks? SQL Server handles this via accent-sensitivity / _AS collations (though, unfortunately, it appears to be broken in the newer, and usually better, version 100 and newer collations). Please note that all three of these are grouped together under accent-sensitivity and cannot be controlled separately (i.e. you can't do vowel sensitive but cantillation mark insensitive).

Pronunciation marks

There are several letters that have two different sounds. Sometimes the only indicator for which sound to use is the context of what word the letter is in (and sometimes even the surrounding words), such as in the actual Torah (where there are no pronunciation marks or vowels). But, that same text in other forms, as well as other text, will have dots placed either inside the letter, or for the letter Shin, above the letter. The letter Shin can have either an "sh" or "s" sound. To indicate the "sh" sound (i.e. letter "shin"), there is a dot above on the right side, while a dot above on the left side denotes the "s" sound (letter "sin"):

SELECT NCHAR(0x05E9) AS [Shin],                            -- ש
       NCHAR(0x05E9) + NCHAR(0x05C1) AS [Shin + Shin Dot], -- שׁ
       NCHAR(0x05E9) + NCHAR(0x05C2) AS [Shin + Sin Dot]   -- שׂ
WHERE  NCHAR(0x05E9) = NCHAR(0x05E9) + NCHAR(0x05C1) COLLATE Hebrew_CS_AI_KS_WS
AND    NCHAR(0x05E9) = NCHAR(0x05E9) + NCHAR(0x05C2) COLLATE Hebrew_CS_AI_KS_WS;
-- 1 row (expected; all sensitive except accent)

SELECT NCHAR(0x05E9) AS [Shin],                            -- ש
       NCHAR(0x05E9) + NCHAR(0x05C1) AS [Shin + Shin Dot], -- שׁ
       NCHAR(0x05E9) + NCHAR(0x05C2) AS [Shin + Sin Dot]   -- שׂ
WHERE  NCHAR(0x05E9) = NCHAR(0x05E9) + NCHAR(0x05C1) COLLATE Hebrew_CI_AS
OR     NCHAR(0x05E9) = NCHAR(0x05E9) + NCHAR(0x05C2) COLLATE Hebrew_CI_AS;
-- no rows (expected; all insensitive except accent)

SELECT NCHAR(0x05E9) AS [Shin],                            -- ש
       NCHAR(0x05E9) + NCHAR(0x05C1) AS [Shin + Shin Dot], -- שׁ
       NCHAR(0x05E9) + NCHAR(0x05C2) AS [Shin + Sin Dot]   -- שׂ
WHERE  NCHAR(0x05E9) = NCHAR(0x05E9) + NCHAR(0x05C1) COLLATE Hebrew_100_CI_AI_SC
OR     NCHAR(0x05E9) = NCHAR(0x05E9) + NCHAR(0x05C2) COLLATE Hebrew_100_CI_AI_SC;
-- no rows (expected 1 row; all insensitive)

Vowels

SELECT NCHAR(0x05D0) AS [Aleph],                           -- א
       NCHAR(0x05D0) + NCHAR(0x05B8) AS [Aleph with vowel] -- אָ
WHERE  NCHAR(0x05D0) =
       NCHAR(0x05D0) + NCHAR(0x05B8) COLLATE Hebrew_CS_AI_KS_WS;
-- 1 row (expected; all sensitive except accent)

SELECT NCHAR(0x05D0) AS [Aleph],                           -- א
       NCHAR(0x05D0) + NCHAR(0x05B8) AS [Aleph with vowel] -- אָ
WHERE  NCHAR(0x05D0) =
       NCHAR(0x05D0) + NCHAR(0x05B8) COLLATE Hebrew_CI_AS;
-- no rows (expected; all insensitive except accent)

SELECT NCHAR(0x05D0) AS [Aleph],                           -- א
       NCHAR(0x05D0) + NCHAR(0x05B8) AS [Aleph with vowel] -- אָ
WHERE  NCHAR(0x05D0) =
       NCHAR(0x05D0) + NCHAR(0x05B8) COLLATE Hebrew_100_CI_AI_SC;
-- no rows (expected 1 row; all insensitive)

Cantilation marks

Technically, according to official Unicode data, Hebrew cantillation marks are ignorable and should only register as a difference here when using a binary collation. However, SQL Server treats them the same as accents (unfortunately) and cannot ignore them separately from pronunciation marks or vowels.

SELECT NCHAR(0x05D0) AS [Aleph],                                       -- א
       NCHAR(0x05D0) + NCHAR(0x05A8) AS [Aleph with cantillation mark] -- א֨
WHERE  NCHAR(0x05D0) =
       NCHAR(0x05D0) + NCHAR(0x05A8) COLLATE Hebrew_CS_AI_KS_WS;
-- 1 row (expected; all sensitive except accent)

SELECT NCHAR(0x05D0) AS [Aleph],                                       -- א
       NCHAR(0x05D0) + NCHAR(0x05A8) AS [Aleph with cantillation mark] -- א֨
WHERE  NCHAR(0x05D0) =
       NCHAR(0x05D0) + NCHAR(0x05A8) COLLATE Hebrew_CI_AS;
-- no rows (expected; all insensitive except accent)

SELECT NCHAR(0x05D0) AS [Aleph],                                       -- א
       NCHAR(0x05D0) + NCHAR(0x05A8) AS [Aleph with cantillation mark] -- א֨
WHERE  NCHAR(0x05D0) =
       NCHAR(0x05D0) + NCHAR(0x05A8) COLLATE Hebrew_100_CI_AI_SC;
-- no rows (expected 1 row; all insensitive)

Do you need to distinguish between wide and non-wide forms of the same letter? There are eight letters in Hebrew that are stretched (wide) but only for the purpose of using in Torah scrolls (either hand-written / real or printed) to keep the fully-justified columnar format (which is actually how it appears in Torah scrolls). SQL Server handles this via width-sensitivity / _WS collations (interestingly-enough, it appears to be the only sensitivity that works correctly in the newer version 100 and newer collations, though sadly, it's the least likely to be used):

SELECT NCHAR(0x05DC) AS [Lamed],
       NCHAR(0xFB25) AS [Wide Lamed]
WHERE  NCHAR(0x05DC) = NCHAR(0xFB25) COLLATE Hebrew_CI_AI;
-- no rows (expected 1 row; all insensitive)

SELECT NCHAR(0x05DC) AS [Lamed],
       NCHAR(0xFB25) AS [Wide Lamed]
WHERE  NCHAR(0x05DC) = NCHAR(0xFB25) COLLATE Hebrew_100_CS_AS_KS_SC;
-- 1 row (expected; all sensitive except width)
-- Lamed    Wide Lamed
-- ל        ﬥ

SELECT NCHAR(0x05DC) AS [Lamed],
       NCHAR(0xFB25) AS [Wide Lamed]
WHERE  NCHAR(0x05DC) = NCHAR(0xFB25) COLLATE Hebrew_100_CI_AI_WS_SC;
-- no rows (expected; all insensitive except width)

So, perhaps Hebrew_CI_AI for the columns, and you can override per expression / predicate via the COLLATE statement if you need to use a variation, such as COLLATE Hebrew_CS_AI or Hebrew_CI_AS or Hebrew_CS_AS.

Additional Notes

You will need to store the data in NVARCHAR columns / variables. You could accomplish most of this in regular 8-bit VARCHAR using the Windows-1255 code page (which is what all Hebrew_* collations use), including combining characters for vowels and pronunciation dots:

;WITH Hebrew AS
(
    SELECT NCHAR(0x05E9) + NCHAR(0x05C1) + NCHAR(0x05B8)
           COLLATE Hebrew_100_CS_AS AS [Shin]
)
SELECT
  Hebrew.[Shin] AS [Unicode],
  CONVERT(VARCHAR(20), Hebrew.[Shin]) AS [CodePage1255],
  CONVERT(VARBINARY(10), CONVERT(VARCHAR(20), Hebrew.[Shin])) AS [CodePage1255_bytes]
FROM   Hebrew;
-- Unicode  CodePage1255    CodePage1255_bytes
-- שָׁ        שָׁ               F9D1C8

However, only the Unicode Hebrew block contains the cantillation marks (i.e. trope; code points U+0591 through U+05AF) plus a few extras (code points U+05C4 through U+05C7), and the Alphabetic Presentation Forms block contains the wide variant of several letters plus some other stuff.
Please see the official Unicode Standard (Version 12.1), Chapter 9: Middle East-I, Section 9.1: Hebrew, pages 361-366 (389-394 of the PDF) for a more detailed description of how Unicode handles Hebrew text.
According to the official Unicode CLDR (locale-specific tailorings) rules for the Hebrew ("he" and "he-IL") culture, U+05F3 HEBREW PUNCTUATION GERESH should either match or come before, U+0027 APOSTROPHE. Normally, U+05F3 sorts after apostrophe. This behavior is indeed seen when using the ICU collation demo and switching between the "root" / standard sort order (used by US English / "en-US") and "he". However, this behavior does not seem to be available in either .NET or SQL Server:

SELECT NCHAR(0x05F3)
WHERE  NCHAR(0x05F3) <= N'''' COLLATE Hebrew_100_CS_AS_KS_WS;
-- no rows

SELECT NCHAR(0x05F3)
WHERE  NCHAR(0x05F3) <= N'''' COLLATE Hebrew_CS_AS_KS_WS;
-- no rows

While an unfortunate omission, it does make sense given that I don't see any "he" or "he-IL" -specific tailorings in the Windows Sorting Weight Table files. This could very well mean that there is no actual difference between the Hebrew_* and Latin1_General_* collations outside of the associated code page, which is only used for VARCHAR data, and that does not apply here.
Please see related question and answer: How To Strip Hebrew Accent Marks

O.P. replied with:

  Yes, I need to distinguish between: 1) final and non-final forms of the same letter 2) pronunciation marks 3) vowels, and 4) cantillation marks.

In that case, since you don't need to ignore differences between those properties, you can use the 100 level collations. The example below shows a Hebrew letter (Sin) with a pronunciation mark, a cantillation mark, and a vowel. There are six versions so that each possible combination of ordering of the combining characters could be represented. There is a seventh entry that uses the other dot to create the letter Shin with the same base letter, vowel, and cantillation mark. The query shows that only the six "Sin" entries match each other (even with different byte orders), but not the "Shin".

I included the use of Latin1_General and Japanese_XJIS_140 collations to show that the rules work with those as well in case you need to make use of them (the 140 collations, only in Japanese, have more upper-case / lower-case mappings than older versions). But in general, probably best to stick with the Hebrew collations, and use a non-100 version if you need to ignore differences in vowels, marks, dots, and final vs non-final forms.

DECLARE @Shin   NVARCHAR(5) = NCHAR(0x05E9), -- base Hebrew letter
        @Dot    NVARCHAR(5) = NCHAR(0x05C2), -- Sin Dot
        @Mark   NVARCHAR(5) = NCHAR(0x05A8), -- Cantillation Mark (i.e. trope)
        @Vowel  NVARCHAR(5) = NCHAR(0x05B8); -- Vowel

DECLARE @Dot_Mark_Vowel NVARCHAR(20) = @Shin + @Dot + @Mark + @Vowel,
        @Dot_Vowel_Mark NVARCHAR(20) = @Shin + @Dot + @Vowel + @Mark,
        @Vowel_Dot_Mark NVARCHAR(20) = @Shin + @Vowel + @Dot + @Mark,
        @Vowel_Mark_Dot NVARCHAR(20) = @Shin + @Vowel + @Mark + @Dot,
        @Mark_Vowel_Dot NVARCHAR(20) = @Shin + @Mark + @Vowel + @Dot,
        @Mark_Dot_Vowel NVARCHAR(20) = @Shin + @Mark + @Dot + @Vowel,
        @ShinDot_Mark_Vowel NVARCHAR(20) = @Shin + NCHAR(0x05C1) + @Mark + @Vowel;

SELECT @Dot_Mark_Vowel AS [Sin], @ShinDot_Mark_Vowel AS [Shin];

;WITH chr AS
(
  SELECT *
  FROM   (VALUES
            (@Dot_Mark_Vowel, 'Dot + Mark + Vowel'),
            (@Dot_Vowel_Mark, 'Dot + Vowel + Mark'),
            (@Vowel_Dot_Mark, 'Vowel + Dot + Mark'),
            (@Vowel_Mark_Dot, 'Vowel + Mark + Dot'),
            (@Mark_Vowel_Dot, 'Mark + Vowel + Dot'),
            (@Mark_Dot_Vowel, 'Mark + Dot + Vowel'),
            (@ShinDot_Mark_Vowel, 'ShinDot + Mark + Vowel')
         ) tmp([Hebrew], [Description])
) SELECT chr1.[Hebrew],
         '--' AS [---],
         chr1.[Description] AS [Description_1],
         CONVERT(VARBINARY(20), RIGHT(chr1.[Hebrew], 3)) AS [Bytes_1],
         '--' AS [---],
         chr2.[Description] AS [Description_2],
         CONVERT(VARBINARY(20), RIGHT(chr2.[Hebrew], 3)) AS [Bytes_2]
  FROM   chr chr1
  CROSS JOIN chr chr2
  WHERE  chr1.[Description] <> chr2.[Description] -- do not compare item to itself
  AND    chr1.[Hebrew] = chr2.[Hebrew] COLLATE Hebrew_100_CS_AS_SC
  AND    chr1.[Hebrew] = chr2.[Hebrew] COLLATE Latin1_General_100_CS_AS_SC
  AND    chr1.[Hebrew] = chr2.[Hebrew] COLLATE Japanese_XJIS_140_CS_AS;

-- this query returns 30 rows


Is there a database tool that bashes data with HUGE excel files? Ok this is hard to explain and im not sure if this is the right place to ask it but.

Im 2 weeks in on my new work and I feel like our work can be a lot more easier if we dont rely on just Excel for our tasks.
We deal with a lot of excel files. And these excel files are a lot. Per day we have 2000+ entries. So when we do monthly data, that excel file would be 2000x30 days. Imagine that.

Basically we just do matching with these excel files. All these excel files have different formats but they all have this unique mobile number (i work at a telco company) so we bash data with the excel files we have. Currently, we just use Access to bash data. But its not efficient. Access is REALLY SLOW.

So its like data mining. Lets say we are given this mobile number they want to investigate (we are in the fraud analysis team) so its my job to backtrack and check that phone number's usage. But our company has an inefficient way of handling their data. Like theres various excel files that arent centralized.

Anyway, any suggestions on how to deal with this? Are there alternative tools? etc. Since you guys know about database and i know its a rule that Excel shouldnt be used for database management. I do have some background with mysql and mssql but thats just basic commands.. Im not sure if it can be incorporated with my job.

(also, is there a word for what i do exactly? they call it data mining. We just match a mobile number and look for its corresponding data across different sources)

What you're describing is known as "Excel spreadsheet hell". Your scenario sounds like a disaster - constant firefighting, messy data... the list goes on.

I'm not sure what sort of data volumes you're dealing with. Oracle's solution to this is Apex. It's potentially a lot more than that, but take a look here.

If your volumes are not too large, you could use Oracle XE as your backing database for your systems. Apex only works with Oracle - hardly surprising for a tool which comes free with their database system.

You might ultimately want to upgrade to one of the other Oracle editions - but ultimately, managing data is not a trivial exercise and in order to improve your systems, some financial outlay may be required.

You could take one of the simpler systems, convert it to Apex and show management the potential of using a proper RDBMS to manage their data. It might require some midnight oil on your part, but if it works, it could be of benefit to your company and you.

All I would say, is that your system is untenable and will collapse under its own weight at some point in time. Either try and make it better or resign.

p.s. I hope you don't work for my telco! :-)


MySQL connection timeouts Apparently, a former DB administrator configured our main DB server (located in Germany) so that it only would be accessable from certain IPs. Now we just added a new host (which needs to access that data) and for the life of me I cannot get it to do so. PHP-programs trying to connect just clock-out for about 30 seconds and then they time out with the following error:

  [19-Jan-2016 13:35:51 America/New_York] PHP Warning: mysql_connect():
  Connection timed out in /home/criterion/public_html/main_sms.php on line 10

I checked the IP tables and added the IP of the new client, to no avail.

Can anybody tell me WHERE is the "allowed IPs" list or something of that sort that grants or denies access to the database?

I did a " select * from user_privileges;"  and it listed several records.  The funny thing is that among the IPs listed there were not the ones that DO work. So that tells me that is not the place.  I must be looking in the wrong place.

Inside MySQL, see the GRANTs.  They are hopefully of the form of

GRANT ALL ON dbname.* TO user@'11.22.33.44' IDENTIFIED BY '...'.

Your probably may be solved as simply as copying that and changing the IP address.  Well, change the IDENTIFIED BY part to IDENTIFIED BY PASSWORD('*1234ASDF...') -- the 41-char encrypted password you see in mysql.user.

Note: If you are on 5.7, or have an "authentication plugin" the instructions may be quite different.

Or...

It may be that the OS has firewall protections for port 3306 (or whatever MySQL is using), and you need to poke another hole in the firewall.


mysql database under symlinked directory: ERROR 1018 (HY000): Can't read dir of './/' (errno: 13) I'm using mysql 5.5 on an ubuntu linux server.
I need to move a database from its current location (under /var/lib/mysql) to a disk with more space, so I attempted to move the directory and create a symlink to the new location.  When I do this, I get

ERROR 1018 (HY000): Can't read dir of './<dbname>/' (errno: 13)

when I attempt to show tables after starting mysql again.  The error goes away if I move the database directory back to its original location. (But, I need to move to a larger disk, so I've got to figure this out somehow)

I've looked around on this site and google; The documentation and the accepted answer to this question indicates what I'm doing should work (I'm attempting to use option 3 from the accepted answer).

So, is this still supported?  If so, what am I doing wrong?

Here's some additional information to jumpstart things:

when I run ls -lL /var/lib/mysql all the files are owned by mysql (user & group)
I've also changed ownership of the actual symlink to mysql:mysql (due to comments on this question)
Both filesystems are on local disks (ext4)

It turns out that the ubuntu mysql package uses apparmor.  I found a github blog entry (no longer available a/o Oct 2019) describing my scenario and the steps to fix it, summarized below:

  Here's the gotcha: Ubuntu comes with AppArmor. AppArmor's security model is to bind access control attributes to programs rather than to users. In my /etc/apparmor.d/usr.sbin.mysqld file I've made these changes:

 # I disabled these two lines;
 # Comments indicate you don't need to
 /var/lib/mysql/ r,
 /var/lib/mysql/** rwk,
 # Added these two lines
 /example_ssd_partition/mysql/data r,
 /example_ssd_partition/mysql/data/** rwk,


Truncate and insert: Heap and build index or directly into Clustered table In ETL is it better to drop the index before inserting millions of rows and right after create the index again, or to simply insert into the empty table with the indexe in place.
I know that I can test it and measure it (have not done that yet) but I what to understand the reason, that is what is more expensive: sort and insert into a clustered index or to create an index.
I have kept my index in place and when I insert I see a sort at the end of the execution plan. Also the clustered index insert operator right before the root node is quite expensive (basically all the cost is divided between the sort and the clustered index insert operators).
I use TABLOCK for my insert, recovery model is simple and table is rowstore.

I would keep the clustered index in-place especially if the data is being inserted in large lumps (rather than lots of individual inserts).
You should drop the non-clustered indexes if rebuilding the data from scratch.
NOTE: As per your mention of truncating, this answer is talking about rebuilding a table from scratch.
Considerations would be different if you were adding millions of rows to a table that already contained billions.

the clustered index insert operator right before the root node is quite expensive

It will be as that is the step that is writing all the data to permanent storage. You'll get a similarly expensive step with a heap.

(basically all the cost is divided between the sort and the clustered index insert operators).

This is expected. If you turn the index off so that you have a heap, when you readd the clustered index it will have to reread the data, perform the same sort, and rewrite the pages in the new order - so it will be as expensive, probably more so, than the initial insert with the clustered index turned on.

I know that I can test it and measure it

This is a very good point!
Don't just measure the data insert though: remember that creating the index on an already populated table will be expensive too, so it is "unfair" to compare just the "insert heap" & "insert with CI" not "insert heap + build CI" and "insert with CI". Also, it will need more space in the relevant data file as it will have two copies of the data as the index is being rebuilt (the heap and the newly forming clustered index that will replace it when complete).
Try both in otherwise empty fresh (therefore near zero length data files) test DBs to see the different file growth effects too.

but I what to understand the reason

I suggest trying it, with the index rebuilds too, and look at the work done as displayed in the query plans and the IO statistics.


Unable to install MySQL 5.7.12 in RedHat in a separate directory I am trying to install the MySQL version 5.7.12 into RedHat in a non default data path.

The installation syntax used

/app/mysql/5.7/bin/mysqld --initialize --user=mysql --basedir=/app/mysql/5.7 --datapath=/app/mysql/data

/app/mysql/data is a softlink to /data

I got the following error.

2016-05-19T02:57:28.087915Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicitdefaults_for_timestamp server option (see documentation for more details).
2016-05-19T02:57:28.090891Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
2016-05-19T02:57:28.090910Z 0 [ERROR] Aborting

bin/mysqld --initialize --user=mysql --datadir=/app/mysql/data --basedir=/app/mysql/5.7

I specified --user parameter and the error no longer appearing.

2016-05-19T10:27:17.440353Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2016-05-19T10:27:17.661087Z 0 [Warning] InnoDB: New log files created, LSN=45790
2016-05-19T10:27:17.713953Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2016-05-19T10:27:17.777797Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: xxxxxxx-xxxx-xxxx-xxx-xxxxxxx.
2016-05-19T10:27:17.780306Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2016-05-19T10:27:17.781230Z 1 [Note] A temporary password is generated for root@localhost: xxxxxxx


Mongodb covered query executionStats Hello i'd like some help understanding the following execution stats.

For this query

db.family.find({ "_id" : ObjectId("5c87890b3adcde7da566f0d7")},{_id:1}).explain("executionStats")

        "executionSuccess" : true,
        "nReturned" : 1,
        "executionTimeMillis" : 0,
        "totalKeysExamined" : 1,
        "totalDocsExamined" : 1,

Specifically not sure why totalDocsExamined is 1 because I would have imagined that sine there is an index on ObjectId, and only that is returned , no documents need to be examined?

Kindly help.

Your covered query example is hitting a special case optimisation: an equality query on the _id index.

Since the _id index is always both required and unique, equality queries on _id  choose this index and bypass some of the usual stages of query planning and execution. This is a fast path for common queries looking up documents by their primary key.

When this optimisation happens your explain output will include an IDHACK input stage and the  detailed executionStatswill  not show any rejectedPlans (since the query planner is bypassed for this path). The IDHACK stage always examines the document so won't be a covered query (as at MongoDB 4.2).

The IDHACK optimisation is specific to equality queries, so if you want a workaround for a covered query on _id you could use a different expression like $in:

db.family.find({ "_id" : { $in: [ObjectId("5c87890b3adcde7da566f0d7")]}},{_id:1}).
     explain("executionStats")

A $in query goes through the normal query planning path so other candidate plans will be considered and the explain results will include an IXSCAN stage instead of IDHACK. This query can also be covered (only index keys examined).


"Unspecified Error" when executing SSIS Package I am executing an "Initial Load" SSIS package, which truncates all ETL tables first, then re-populates them by pulling data from a remote server.  I get the dreaded "Unspecified Error" at a different point each time, and different ETL subflow each time.  Other times, but rarely, everything runs fine.  I have no idea how to get more information or where to even look.

The Server running the package is a Intel Xeon E5-2640 @ 2.50 GHz with 144 GB ram. (HP Proliant DL380p Gen8)
It's running Windows 2012 Server Standard.
Software versions:
SQL Server 2012
Microsoft SQL Server Management Studio 11.0.5058.0
.Net Framework 4.0
Operating System: 6.2.9200

Connection to the remote server is done through OLEDB:
Connection String: Data Source=MyServerURL;User ID=MyDWTransferUser;Initial Catalog=DBName;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;

See screenshot of the failure attached.

Previously, I was receiving "Communication Link Failure" error.  The internet connection was upgraded, and also we installed SP2 on SQL 2012 on this DW server.  Now receiving "Unspecified Error" on this package, after various times of execution, on a different sub process each time.  So it's not an SQL issue, but a configuration issue.

Any tips appreciated.

I was not able to get a more specific error message than: "Unspecified Error".

I solved the problem by improving the performance of the queries used.

There was a particular expensive proc which took 10 minutes to run. It joined 8 tables, one of which had 13m rows, had 5 where clauses, and produced 13 million rows. It was being run almost 20 times in the course of the various SSIS sub-packages.  Sometimes it would be run 2-3 times in a single package, producing the same set of results, but obviously table-scanning each time.

So I created a table to store the results of that expensive query, made a scheduled job that runs daily at midnight to populate it once.  Then i just joined the results of that table in each place where previously the expensive proc was being run.

Now the job runs in 25 minutes consistently (instead of 3 hours), and i haven't seen a single failure in the so far 8 runs it executed.

So I suspect that the under-the-hood issue with the "Unspecified Error" was an out of memory exception that may or may not have been linked to the fact that the query was being executed over VPN linked server connection.


ApplicationIntent=ReadOnly Traffic when no Readable Secondary Available We have a SQL 2012 AlwaysOn Availibility Group with 2 nodes.  We have synchronous commit between the 2 replicas. They are both set to accept ReadOnly connections when acting as secondaries and to accept All Connections when acting as Primary.

We have ReadOnly routing setup for readonly traffic via the listener and with ApplicationIntent=ReadOnly in the connection string.  It all works fine.

A couple of days ago we rebooted the readable secondary and during the time it was down, the readonly traffic failed to connect to a readable secondary (as expected), but wasn't redirected to the Primary as I thought it would. Instead the following error was received

  Unable to access the SalesDb database because no online secondary replicas are enabled for read=only access.

My question is, if no readable secondary replicas are available for some reason, does the readonly traffic not get routed to the Allow All Connections Primary replica by the listener, rather than getting a connection error?

Any answers gratefully received.

AcombLil

There are several steps to configuring a server to accept ReadOnly traffic.  The following link walks you through it, http://msdn.microsoft.com/en-us/library/hh710054.aspx ,but basically you need to configure each server in the AG and then set up the routing for each.

Here's the T-SQL involved:

ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER01' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER01' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER02' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG1]
 MODIFY REPLICA ON
N'COMPUTER02' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER02.contoso.com:1433'));

ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON
N'COMPUTER01' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER02','COMPUTER01')));

ALTER AVAILABILITY GROUP [AG1]
MODIFY REPLICA ON
N'COMPUTER02' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER01','COMPUTER02')));
GO

Sounds like you may be missing the configuration and/or routing information for the primary.


Computed Column Bit based on other Bit Columns What would be the formula to compute a bit column from other bit columns?

For example:

CREATE TABLE #Employee
(
[empNumb]       [INT] identity(1,1) PRIMARY KEY,
[IsOk]          [BIT] NOT NULL, -- This is computed from other three columns.
[HasValidHours] [BIT] NOT NULL,
[NoDemerits]    [BIT] NOT NULL,
[NoAccidents]   [BIT] NOT NULL
);

ALTER TABLE #Employee ADD CONSTRAINT [DF_Employee_HasValidHours]
  DEFAULT ((0)) FOR [HasValidHours];
ALTER TABLE #Employee ADD CONSTRAINT [DF_Employee_NoDemerits]
  DEFAULT ((0)) FOR [NoDemerits];
ALTER TABLE #Employee ADD CONSTRAINT [DF_Employee_NoAccidents]
  DEFAULT ((0)) FOR [NoAccidents];

IsOk would be computed from the and-ing of HasValidHours, NoDemerits and NoAccidents.

Do you mean IsOk = 1 if the other three bit columns all = 1 else IsOk = 0?

If so: -

CREATE TABLE #Employee
(
[empNumb]       [INT] identity(1,1) PRIMARY KEY,
[IsOk]          AS [HasValidHours] & [NoDemerits] & [NoAccidents],
[HasValidHours] [BIT] NOT NULL,
[NoDemerits]    [BIT] NOT NULL,
[NoAccidents]   [BIT] NOT NULL
);

See & (Bitwise AND) (Transact-SQL)


Going from long/lat cords to Geometry or Geography type When I make a query:

select *,
       concat(pickup_longitude,' ',pickup_latitude) as pickup_pt,
       concat(dropoff_longitude,' ',dropoff_latitude) as dropoff_pt

it usually defines the pickup_pt and the dropoff_pt as datatype text from default apparently.

Is there a way to define the column in another datatype?

Perhaps something like:

concat(pickup_longitude,' ',pickup_latitude) as pickup_pt of type geometry

Can you cast? No.

geometry and geography are PostGIS types, and your types are likely float, double, or something like that.

Can you create PostGIS types? Yes.

What you want to do is use ST_MakePoint(long,lat).

SELECT *,
   ST_MakePoint(pickup_longitude, pickup_latitude) as pickup_pt,
   ST_MakePoint(dropoff_longitude, dropoff_latitude) as dropoff_pt

If you don't have PostGIS.

Get it. Install it. Use it: CREATE EXTENSION postgis;
Or, if you can't get or have a good reason for not wanting it, then and only then should you even consider using the native type, point.

SELECT *,
   point(pickup_longitude, pickup_latitude) as pickup_pt,
   point(dropoff_longitude, dropoff_latitude) as dropoff_pt

Geometery vs Geography

Geography to geometry conversions are a function to PostGIS. But you can do it with

SELECT ST_MakePoint(long,lat)::geography;

Or, you can use another geometery srid with

SELECT ST_SetSRID( ST_MakePoint(long,lat), srid );


Creating a table with parent_id from a flat table I have a flat table

(id, city, county, state),

Example

(1,'Beckingen', 'Merzig-Wadern', 'Saarland')

A city belongs to a county. A county belongs to a state.

First my attempt was to divide the data into 3 tables cities, counties, and states  and to build up the associations via linking tables (cities_counties). Now I want to create a 'locations' table out of it, where county is parent of city and state is parent of county.

locations:
(id, name, parent_id, type)

 (type is 'city','county', or 'state')

What would be best practice to create the adjacent list from the flat list?

I'm working in a LAMP environment, just in case a php script would fit.

I wrote a php Script that does the job :)

not generic at all and straightforward, but it works

mysql_query('truncate table list');

$cities = array();

$res = mysql_query('select city from temps');

while ($row = mysql_fetch_assoc($res))
    $cities[] = $row['city'];

$counties = array();

$res = mysql_query('select distinct county from temps');

while ($row = mysql_fetch_assoc($res))
    $counties[] = $row['county'];

$states = array();

$res = mysql_query('select distinct state from temps');

while ($row = mysql_fetch_assoc($res))
    $states[] = $row['state'];

//insert germany as root node

mysql_query('insert into list(name,parent_id,type) values("Germany", NULL, "Country")');
$rootid = mysql_insert_id();

foreach ($states as $state) {
    mysql_query("insert into list(name,parent_id, type) values('$state','$rootid','State')");
}

foreach ($counties as $county) {
    //determine state
    $res = mysql_query("select state from temps where county='$county' LIMIT 1");
    $row = mysql_fetch_row($res);
    $state = $row[0];

    $res = mysql_query("select id from list where name='$state' LIMIT 1");
    $row = mysql_fetch_row($res);
    $parent_id = $row[0];

    mysql_query("insert into list(name,parent_id,type) VALUES('$county','$parent_id','County')");
}

foreach ($cities as $city) {
    //determine state
    $res = mysql_query("select county from temps where city='$city' LIMIT 1");
    $row = mysql_fetch_row($res);
    $county = $row[0];

    $res = mysql_query("select id from list where name='$county' LIMIT 1");
    $row = mysql_fetch_row($res);
    $parent_id = $row[0];

    mysql_query("insert into list(name,parent_id,type) VALUES('$city','$parent_id','City')");
}


How to normalize my db schema for a quiz application? I am putting together a quiz application.   (I'm calling each quiz a "challenge")

I have a "challenge" table and a "challenge_question" table which is pretty straightforward.

challenge table:
id
name
num_questions (I want to randomize and only display a certain number of Qs per each challenge)

challenge_question table:
id
challenge_id
question (text of the question)
required (some questions will be required to be displayed per each challenge)
question_type (here I indicate what type of question: Mult. choice, T/F, Ordering, or Fill-in-the-blank)

Where I'm having trouble in normalization is with the types of question options:  Multiple choice, true/false, ordering, and fill-in-the-blank. I wonder, should I have a separate table for each type of challenge-question-option? Or is it possible to combine these into one somehow..

If I combined all possible options into a single table, it might look be something like this

challenge_options table:
id
challenge_question_id
option (text field, can be used for both multiple choice and for fill-in-the-blank style questions)
is_true (boolean field for true/false questions)
is_correct (boolean, used to determine multiple choice answer)
ordering_option_1
ordering_option_2
ordering_option_3
ordering_option_4 (these ordering fields would store the correct sequence of an ordering style of question)

This really strikes me as less than ideal.  Actually, I don't see how the ordering_options would work out correctly at all.  Wouldn't I need to have each of those be a separate option?  Surely there must be a better way.  Any suggestions/criticisms or alternate approaches?

Instead of option I would use another table option_type with a PK and a name field and use a fk-relationship in challenge_options. You can easily add new kinds of challenges.

I am not sure if I would use is_true / is_correct within challenge_options. I would use a second table with user_id, challenge_options_id and a boolean correct/false.


Unable to alter table - reason code is 10 I'm trying to run this:
ALTER TABLE AMMLIBT.SAGRPPF ADD UNIQUE (ID) ADD CHECK ( IS_BUDGETABLE IN ( 0 , 1 )) ;

Which gives me this:

SQL State: 55019
Vendor Code: -7008
Message: [SQL7008] SAGRPPF in AMMLIBT not valid for operation. Cause . . . . . :   The reason code is 10.  Reason codes are:
[...]
10 -- A constraint or trigger is being added to an invalid type of table, or the maximum number of triggers has been reached, or all nodes of the distributed table are not at the same release level.
[...]
Recovery  . . . :   Do one of the following based on the reason code:
[...] 10 -- Specify tables that are valid for constraints or triggers.
[...]

...And I have no idea why and can't seem to find anything online explaining this.
The table is journalled and I've deleted all dependents.
I'm on IBM DB2 V7R1
How do I fix this?

Thanks to Charles for pointing out this command: DSPFD AMMLIBT/SAGRPPF

Which showed me that:

  Maximum members . . . . . . . . . . . . . . : MAXMBRS    *NOMAX

To fix this, I needed to change '*NOMAX' to '1'. To do this, I went to my greenscreen and did:

type chgpf ammlibc/sagrppf
F4
F10
Move cursor to 'Maximum members'
Change '*NOMAX' to '1'
Enter


SSIS 2008R2 - Use Package Config Value in Expression Is it possible to add a SQL Server package configuration value for an Expression attribute on, for example, a 'Flat File Connection Manager'?

Yes you can. In you expression you can use: "+@[User::(variable name)]+"

More info: https://anothersqlgeek.wordpress.com/2013/03/28/ssis-dynamic-connections-part-1/


Count number of duplicate rows, sort from most to least (statistic) I'm trying to make statistic of top-10 most visited URL. I have one simple table. For each URL visit, one row added with that URL as content. Most visited URL has the most number of duplicate.

I've found many reference to SELECT count(*) FROM tablename WHERE url='tofind' but that only works if I supply the URL data.

Suggestion are welcome.

Have you tried something like:

SELECT COUNT(url) AS My_Count
FROM tablename
GROUP BY url
ORDER BY My_Count DESC
LIMIT 10 ;


How to implement constraints that go beyond a single row in PostgreSQL? How to implement constraints that go beyond a single row in PostgreSQL?

Examples 1:
With 3 tables: Person, Food which has a field called type, and Purchase which links Persons to Food. How can I enforce that a person can only purchase food of the same type from previous purchases if any?

Examples 2:
With 5 tables: Session, Auth which has a sessionId and accountId fields, Account, Link which links Accounts to Users, and User. How can I enforce that a Session can only link to Accounts (via Auth) that are linked to the same User (via Link)? In other words, a Session cannot link to Accounts that are linked to different Users.

Thank you!

For your first example, let's assume your tables are defined in the following way:

CREATE TABLE person
(
    person_id integer PRIMARY KEY,
    person_name text NOT NULL
) ;

CREATE TABLE food
(
    food_id integer PRIMARY KEY,
    food_name text NOT NULL,
    food_type text NOT NULL  /* Unnormalized, for simplicity */
) ;

CREATE TABLE purchase
(
    purchased_at timestamp not null default now(),
    person_id integer NOT NULL REFERENCES person(person_id),
    food_id integer NOT NULL REFERENCES food(food_id),
    PRIMARY KEY (person_id, food_id, purchased_at)
) ;
CREATE INDEX idx_purchaser_food_id
    ON purchase(food_id, person_id) ;

I would create a function that checks for your constraint. It's a simple SQL statement, with one corner case (the first purchase), and a "literal" translation of your requirement: the current food type is in (the set of already existing ones):

CREATE FUNCTION
    allowed_food_type_for_person(in _person_id integer, in _food_id integer)
RETURNS
    boolean AS
$body$
    SELECT
        -- Corner case for first purchase
        (NOT EXISTS
             (SELECT
                  *
              FROM
                  purchase
              WHERE
                  person_id = _person_id
             )
        )
        OR
        (
           -- Current food types
          (SELECT
              food_type
          FROM
              food
          WHERE
              food_id = _food_id)
          IN
          -- Existing food types
          (SELECT
              food_type
          FROM
              purchase
              JOIN food USING(food_id)
          WHERE
              person_id = _person_id)
          )
$body$
LANGUAGE SQL STABLE ;

Now you can add the constraint you wish:

ALTER TABLE purchase
    ADD CONSTRAINT check_purchase_food_type_is_limited
        CHECK(allowed_food_type_for_person(person_id, food_id));

Now, let's imagine this is your data:

INSERT INTO person
   (person_id, person_name)
VALUES
   (1, 'Alice Cooper'),
   (2, 'Bob Geldorf') ;

INSERT INTO food
   (food_id, food_name, food_type)
VALUES
   (1, 'Banana', 'Fruit'),
   (2, 'Orange', 'Fruit'),
   (3, 'Pear', 'Fruit'),
   (4, 'Lettuce', 'Vegetable'),
   (5, 'Coliflower', 'Vegetable'),
   (6, 'Cellery', 'Vegetable'),
   (7, 'Asparagus', 'Vegetable') ;

You can have 'Alice' do a first purchase:

INSERT INTO
    purchase
    (person_id, food_id)
VALUES
    (1, 1) ;    -- Alice purchased a Banana (fruit)

Now, she buys an orange, OK:

INSERT INTO
    purchase
    (person_id, food_id)
VALUES
    (1, 2) ;    -- Alice purchased an Orange (fruit), Ok

But if she tries to buy 'Lettuce':

INSERT INTO
    purchase
    (person_id, food_id)
VALUES
    (1, 4) ;    -- Alice tries to purchase a Lettuce => fail

ERROR:  new row for relation "purchase" violates check constraint "check_purchase_food_type_is_limited"
DETAIL:  Failing row contains (2017-07-11 19:46:08.830481, 1, 4).

You can play with it at dbfiddle here

For your 2nd example, you'd do something equivalent, but more sophisticated. In any case, there are other ways to solve the "diamond problem" you seem to have, by defining the tables in a different way. Check Data inconsistency prohibition if a table refers to another via two many-to-many relationships, for a very good explanation.

Caveat: As per comment from @DanielVerité: this can have concurrency problems that actual constraints don't have. They can (most probably) be solved by using TRANSACTION ISOLATION LEVEL SERIALIZABLE; incurring the heavy penalty it implies.

Side question: Why are you against people having a diverse diet? ;-)


What happens if I interrupt or cancel `pg_upgradecluster`? I'm upgrading a PostgreSQL 9.5 cluster to 9.6 using pg_upgradecluster. It's taking an awful lot of time, so I'd like to cancel the process, delete unneeded databases, and start again. What are the risks?

By default, pg_upgradecluster doesn't use pg_upgrade. It's creating a logical dump with pg_dump and restoring it (with pg_restore) on the new cluster.

To avoid this and speed up the process, you need to add the -m upgrade option.
If you need to speed up the process once more, you can add the --link so that the upgarde will be inplace. Be carefull, you can't cancel the process if you did it or you will loose your cluster.

If you didn't use the -m upgrade option and the --link option, you can cancel the process, drop the new cluster and create it again:

pg_dropcluster 9.6 <cluster_name>
pg_createcluster 9.6 <cluster_name>


How to get query runtime in PL/pgSQL to be as fast as console query runtime? Basically I want to assign the result of a query to a custom type attribute. However I noticed that querying directly from the PostgreSQL console was about 0.071 ms and inside the function was 0.400 ms and 0.170 ms after a few calls. explain analyze even shows the usage of indexes in the first case but not on the second.

Here's what I'm doing.

CREATE OR REPLACE FUNCTION fun_isliked(
    par_client client.id%type,
    par_feed feed.id%type
)
RETURNS boolean
AS
$$
BEGIN
    RETURN (
        EXISTS(
            SELECT
                1
            FROM
                feedlike fl
            WHERE
                fl.client = par_client
                AND fl.feed = par_feed
                AND fl.state = 1
        )
    );
END;
$$ LANGUAGE plpgsql STABLE;

Here are the outputs of explain analyze of the two cases described above:

postgres=# explain analyze select exists (select 1 from feedlike where client = 13 and feed = 68 and state = 1);
                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=8.30..8.31 rows=1 width=0) (actual time=0.037..0.037 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Index Scan using feedlike_client_feed_unique on feedlike  (cost=0.28..8.30 rows=1 width=0) (actual time=0.034..0.034 rows=1 loops=1)
           Index Cond: ((client = 13) AND (feed = 68))
           Filter: (state = 1)
 Total runtime: 0.086 ms

postgres=# explain analyze select * from fun_isliked(13, 68);
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Function Scan on fun_isliked  (cost=0.25..0.26 rows=1 width=1) (actual time=0.398..0.398 rows=1 loops=1)
 Total runtime: 0.416 ms

What is a possible workaround in order to effectively get same runtime inside the function? Is it even possible? Also, I'm running PostgreSQL 9.3.

I found that this question in SO had what I needed but after I tried everything in the selected answer and had no success on reducing the runtime I decided to ask a new question.

The elephant in the room is function overhead. When calling a function instead of raw SQL, Postgres needs to look up the function in the system catalogs (possibly picking the best match in case of function overloading) and consider settings of the function. And then there is the overhead for the function call itself. This adds a tiny overhead cost.

That only really matters for very simple queries like your example - which should much rather be implemented with a plain SQL function. You still encounter some (tiny) function overhead with an SQL function, but it can be "inlined" if it's a simple SELECT and some preconditions are met.

Like always, repeated calls profit from the populated cache. And in the case of plpgsql functions, it may also start to use a generic plan after some (typically 5) calls, if that seems promising.

Consider this related discussion on pgsql-general.

Query plans for code inside plpgsql functions

You are wondering:

  explain analyze even shows the usage of indexes in the first case but not on the second [plpgsql function].

plpgsql functions are black boxes to the query planner, which does not examine the contents of the function. They act as optimization barriers. Unlike with SQL functions their content cannot be inlined in outer queries.

This is the reason why EXPLAIN ANALYZE doesn't show what's happening inside the plpgsql function and you therefore see no index scan. The index (most probably) is still used. You can use the additional module auto_explain to have a peek on the query plans for SQL code inside the function. Consider the answers to this related question for details:

Postgres query plan of a UDF invocation written in pgpsql


How to search for a name in two columns in MySQL? I want to search a name 'Hamed Haddadi'. 'Hamed' is first name and 'Haddadi' is the last name of the user. When I search individually in the first name and last name columns it shows a record.

My query is:

SELECT *
FROM nbaplayermaster
WHERE firstname LIKE "%Hamed%" OR
      lastname LIKE "%Haddadi%";

But whenever I search his name in two columns, it shows an empty result set in MySQL.

My query is:

SELECT *
FROM nbaplayermaster
WHERE firstname LIKE "%Hamed Haddadi%" OR
      lastname LIKE "%Hamed Haddadi%";

I want to search his full name in two columns.

I suspect that Hamed is the first name and Haddadi is the last name, you can do it in 3 ways;

Solution 1:

Split the query in name and last name:

SELECT *
FROM nbaplayermaster
WHERE firstname LIKE "%Hamed%" OR
      lastname LIKE "%Hamed%";

Solution 2:

Use the wildcard % in between the names(spaces)

SELECT *
FROM nbaplayermaster
WHERE firstname LIKE "%Hamed%Haddadi%" OR
      lastname LIKE "%Hamed%Haddadi%";

Solution 3:(recommended)

Add a fulltext index on this 2 columns:

ALTER TABLE nbaplayermaster ADD FULLTEXT KEY `full_name` (`firstname`,`lastname`);

and then search using the fulltext index

SELECT * FROM nbaplayermaster WHERE MATCH(firstname,lastname) AGAINST ('Hamed Haddadi');


INSERT succeeds but all inserted values become NULL I tired to execute an insert directly from phpMyAdmin as follows:

INSERT INTO oracle.PLAYLIST_MUSIC ( TID,
                ID,
                STATUS,
                CREATED_BY,
                CREATED_DATE,
                UPDATED_BY,
                UPDATED_DATE,
                ORDER
            )
VALUES(TID = 56919,
       ID = 115948,
       STATUS = '1',
       CREATED_BY = 15217,
       CREATED_DATE = NOW(),
       UPDATED_BY = 15217,
       UPDATED_DATE = NOW(),
       ORDER = 0)

The SQL executed successfully, however I found that all the inserted values became NULL, including the system generated values like NOW().

Does anybody have an idea?

Your insert syntax is wrong, using column=value in the values clause doesn't do what you think it does.

Try:

INTO oracle.PLAYLIST_MUSIC ( TID,
                ID,
                STATUS,
                CREATED_BY,
                CREATED_DATE,
                UPDATED_BY,
                UPDATED_DATE,
                `ORDER`
            )
VALUES(56919,
       115948,
       '1',
       15217,
       NOW(),
       15217,
       NOW(),
       0)

The values must be put in the same order as the column names appear in the insert statement.

It would probably be a good idea to quote the order column, since that's a reserved word.

I'm guessing the nulls come from the fact that the columns aren't bound to anything in the values clause, causing them to be null. And null = any value evaluates to null:

mysql> select null=1 ;
+--------+
| null=1 |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

But that's just a guess about what is actually happening, I'm surprised that parsed at all.


In Postgres - when transaction IDs wraparound what are their numbers? In Postgres 11, when transaction IDs pass the 4 billion wraparound mark, assuming that the database is properly autovacuumed, what will be the next txid returned by txid_current()?

Will the returned transaction IDs start again from 1 or will they continue to have IDs greater than 4 billion and the txid_current() will return numbers greater than 4 billion.

The documentation says "... these functions export a 64-bit format that is extended with an “epoch” counter so it will not wrap around during the life of an installation."  So my understanding is that txid_current() returns bigint and the the returned txid will not start from zero. postgresql.org/docs/11/functions-info.html

The docs state that

  the XID counter wraps around to zero


Oracle 12c, can not chose standard edition when install! i'm trying to install Oracle 12c on Windows 2012 R2 ... I get Enterprise Edition option only!

I need to install Standard Edition!

There was no Standard Edition released from version 12.1.0.1

Download 12.1.0.2 for Standard Edition.


Ensuring Uniqueness Across Columns I'll lead with this: Perhaps the database design is wrong, so I will accept a re-design as a solution!

Consider the following table:

ID | TypeA | RefA | TypeB | RefB
-- + ----- + ---- + ----- + ----
3  |     1 |   10 |     2 | 200
4  |     1 |   11 |     2 | 210
5  |     1 |   12 |     2 | 220
6  |     1 |   13 |     2 | 230

The idea is that we are mapping References from an unknown number of systems. This is not a "From and To" scenario, but a two-way mapping.

We are looking for a solution that satisfied all of the below statements:

A new row cannot be inserted where the combination of TypeA, RefA and TypeB already exists (could be achieved by unique constraint)
A new row cannot be inserted where the combination of TypeB, RefB and TypeA already exists (again, could be achieved by unique constraint)
The pairs of columns must be checked both ways around

To clarify, the following rows defined below would all be invalid:

ID | TypeA | RefA | TypeB | RefB
-- + ----- + ---- + ----- + ----
?  |     1 |   10 |     2 | 201   <-- Type 1 and Ref 10 already exists
?  |     1 |   20 |     2 | 210   <-- Type 2 and Ref 210 already exists
?  |     1 |   12 |     2 | 220   <-- This row already exists exactly like this
?  |     2 |  220 |     1 |  21   <-- Type 2 and Ref 220 already exists (in the B columns)
?  |     2 |  300 |     1 |  13   <-- Type 1 and Ref 13 already exists (in the A columns)

What is the best way to approach this?

Please let me know if anything is unclear in the above!

For the timebeing we have implemented the solution found here: https://stackoverflow.com/questions/11414976/unique-constraint-on-two-columns-regardless-of-order

More specifically we have:

Added a Constraint so that TypeB must be greater than TypeA
Added a Unique Key for TypeA, RefA and TypeB
Added a Unique Key for TypeB, RefB and TypeA

This is a workaround as it forces changes to the business logic to ensure the products are sorted correctly before choosing which columns to add the information to.

Leaving this answer unaccepted in the hopes of a flashier answer.


SQL Server Express 2005 DB over size but data < 1GB I have a SQL Express 2005 instance which is not working correctly due to it hitting the 4GB DB size limit. I have looked at the data in the DB and cannot see how this is possible - the data is less than 1GB.

I checked with this query:

SELECT
  sum(p.rows) as RowCounts,
  sum(a.total_pages) as TotalPages,
  sum(a.used_pages) as UsedPages,
  sum(a.data_pages) as DataPages,
  (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB,
  (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB,
  (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
FROM
  sys.tables t
INNER JOIN
  sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
  sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
  sys.allocation_units a ON p.partition_id = a.container_id
WHERE
  t.NAME NOT LIKE 'dt%' AND
  i.OBJECT_ID > 255 AND
  i.index_id <= 1
GROUP BY
  t.NAME, i.object_id, i.index_id, i.name
ORDER BY
  sum(a.total_pages) DESC

The output of this query shows the following table sizes:

105370,32028,32015,839,250,250,6
601107,11866,11857,11811,92,92,92
636064,11666,11088,10150,91,86,79
350950,3458,3377,3360,27,26,26
146825,2001,1980,1971,15,15,15
118641,1881,1837,1829,14,14,14
68834,1778,1756,1747,13,13,13
79954,1107,1051,570,8,8,4
87993,1025,1006,1001,8,7,7
63738,801,793,789,6,6,6
591,719,718,3,5,5,0
29614,404,395,158,3,3,1
43082,401,378,376,3,2,2
13314,275,230,86,2,1,0
20982,218,170,157,1,1,1
476,86,80,3,0,0,0
2382,82,66,20,0,0,0
634,74,60,53,0,0,0

There are more tables but their size is all zero from here downwards.

Where is the additional space going? I would delete some records but it seems there is a large amount of space being lost somewhere. I have tried compacting and shrinking the DB to no avail.

EDIT: sp_spaceused reports the following:

Reserved    Data        Index size  Unused
4194152 KB  552584 KB   3523920 KB  117648 KB

So it appears the additional space is being used by the index?

First, check your database for indexed views, they would be missed by your original script (courtesy of Michael J Swart):

select OBJECT_SCHEMA_NAME(object_id) as [SchemaName],
       OBJECT_NAME(object_id) as [ViewName],
       Name as IndexName
from sys.indexes
where object_id in
  (select object_id from sys.views)

If that list contains any rows, run the following to see how much space each uses:

EXEC sys.sp_spaceused @objname = N'dbo.YourView'

Second, try this script to distinguish between data and index space in each table (a variation of alpav's script here):

create table #tbl(
  name nvarchar(128),
  rows varchar(50),
  reserved varchar(50),
  data varchar(50),
  index_size varchar(50),
  unused varchar(50)
)

exec sp_msforeachtable 'insert into #tbl exec sp_spaceused [?]'

select name, rows,
    convert(int, substring(reserved, 1, len(reserved)-3)) as reserved_kb,
    convert(int, substring(data, 1, len(data)-3)) as data_kb,
    convert(int, substring(index_size, 1, len(index_size)-3)) as index_kb,
    convert(int, substring(unused, 1, len(unused)-3)) as free_kb
 from #tbl
    order by 5 desc

drop table #tbl

That should at least help you determine what table is responsible.

Once you've done that, you'll need to take further action:

Drop unneeded tables, indexed views, and indexes
Truncate or delete unneeded data
Drop and re-create indexes with a higher fill factor
Drop and re-create indexes with fewer indexed fields, or fewer included fields.

Beyond that, you'll have to look at the data structure (data types used, redundant data, etc), but that's not an easy fix.


Powershell SQL Script Execution in Selected Schemas Only I have many schemas in one database. In that database I have some SQL scripts to be executed. I want to execute those SQL scripts in only selected schemas.

Suppose I have 10 schemas in total (A to J). I want to execute a SQL script in all schemas except B,G,H and prompt the user for the excepted schemas. How can I do this?

Hopefully I can help you answer this question if I understand the question correctly. You wish to run the same query against multiple schemas in a database. I assume each schema has identical tables. As suggested below by Shawn you will need to import the module SQLPS in Powershell to run the scripts below.

Assume the database with 3 schemas as follows

    CREATE DATABASE [aSchemaCheck]
    GO

    USE [aSchemacheck]
    GO
    CREATE SCHEMA [Aschema] AUTHORIZATION [db_owner]
    GO

    CREATE TABLE ASchema.Name
    (
        time time NOT NULL,
        Name char(10) NULL,
    )
    GO

    CREATE SCHEMA [BSchema] AUTHORIZATION [db_owner]
    GO

    CREATE TABLE BSchema.Name
    (
        time time NOT NULL,
        Name char(10) NULL,
    )
    GO

    CREATE SCHEMA [CSchema] AUTHORIZATION [db_owner]
    GO

    CREATE TABLE CSchema.Name
    (
        time time NOT NULL,
        Name char(10) NULL,
    )
    GO

What you can do is use Invoke-SQLCmd and create a $query string and replace the Schema in the query as follows

    $query = "INSERT INTO [$Schema].[Name]
               ([time]
               ,[Name])
         VALUES
               (CONVERT(time,GetDate())
               ,'AName')
    GO"

    Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Query $query

You can specify the schemas in the script and loop through them as follows by putting the above query inside a loop as follows

    $Schemas = 'ASchema','BSchema','CSchema'
    foreach($Schema in $Schemas)
    {
    }

If we you want to run the query against all user defined schemas you can do the following

    $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Server
    $db = $srv.Databases[$Database]
    $Schemas = $db.Schemas|Where-Object{$_.IsSystemObject -eq $false}|select Name -ExpandProperty Name

    foreach($Schema in $Schemas)
    {
    }

If you want to select the Schemas you can do so using Out-GridView -Passthru press CTRL and click to choose multiple

    $srv = New-Object Microsoft.SqlServer.Management.Smo.Server $Server
    $db = $srv.Databases[$Database]
    $Schemas = $db.Schemas|Where-Object{$_.IsSystemObject -eq $false}|Out-GridView -PassThru|select Name -ExpandProperty Name

    foreach($Schema in $Schemas)
    {
    $query = "INSERT INTO [$Schema].[Name]
               ([time]
               ,[Name])
         VALUES
               (CONVERT(time,GetDate())
               ,'AName')
    GO"

    Invoke-Sqlcmd -ServerInstance $Server -Database $Database -Query $query
    }

if you wanted to use a .sql file against each schema you would use

Invoke-Sqlcmd -ServerInstance $Server -Database $Database -InputFile $SQLFile


sqlplus (Oracle XE) doesn't start on CentOS 7 I followed the steps of this document: https://docs.oracle.com/cd/E17781_01/install.112/e18802/toc.htm#XEINL121, but sqlplus doesn't start. Even if I used the command: "sqlplus /nolog".
The other services start: APEX, Main page on: 127.0.0.1:8080, and It can connect with SYSTEM user in the page. However, sqlplus still doesn't start.
I executed the command: /etc/init.d/oracle-xe start, and shows a message with "OK".

When I check the status of the oracle-xe service, it shows: active (exited).

What does it mean "exited"? Why not "running"?
How can I execute sqlplus?

This version of Oracle XE is based on 11g2 edition, which is not compatible with CentOS 7.

In fact, it isn't even 100% compatible with CentOS 6, however it will run there without problems, after you install a number of RPM packages not installed/required by Oracle installer (especially libaio both 32/64-bit).

These are formal OS requirements for XE 11g2:

Oracle Linux, Red Hat Enterprise Linux, or CentOS - versions 4.7+ or 5.2+
SUSE Linux Enterprise Server - versions 10 SP2 or 11


Postgresql similar of SQL Server Query Store Is there any extension available for Postgresql that is similar to SQL Server query store. So that it can help identify performance bottleneck.

SQL Server query store provides information on how many times query executed, resource consumption per query. https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15 provides more detail on query store.

Yes there is such an extension: pg_stat_statements

  The pg_stat_statements module provides a means for tracking execution statistics of all SQL statements executed by a server.

Tools like PoWA use this extension and provide others performance metrics that you might be interested in.


How to instruct Oracle 11g Standby database MRP to bring down as soon as applying the given archive log file? Scenario:
Let us consider the following:
Oracle 3 node RAC.
RMAN Managed Recovery and Dataguard are configured between production and standby DBs.
The SCNs of 3 nodes are S1, S2, S3 at some point of time respectively.
Smax=max(S1,S2,S3)
Update1(with reference to Balazs Papp Advice):
log_1_x.arc, log_2_y.arc, log_3_z.arc where log file sequence number x<y<z and they contain the sequence number Smax

Please advise on bringing down Orace 11g Standby database MRP as soon as applying the given archive log file with name, log_3_z.arc!

archive_xyz.log contains the sequence number Smax

This is where you are wrong. You have 3 instances, you have 3 redo threads, so you have 3 archivelogs containing the change at Smax.

Because of this, you need to recover the database until SCN/change, not "archive_xyz.log".

alter database recover managed standby database until change Smax;

Note that, this does not apply the change at Smax.

managed_standby_recovery::=

  UNTIL CHANGE Clause

  Use this clause to instruct Redo Apply to recover redo data up to, but
  not including, the specified system change number.


How to structure a listings table Say I have a table called listing and it has the following columns:
id | name   | description | price | created_date|
 0 | iPhone | A phone     | 1000  |   7/4/2020  |

Since I want to track when a listing is sold, I'm considering adding a sold_date column.
My concern is that this would cause a bunch of rows in the listing table to have a null in the sold_date column since every listing wouldn't be considered sold. Would having null values be okay in this case, or should I create another table that would contain the sold_date column and references a listing?

There is a normal form that would separate this column into its own entity (table). If you want to go down that path it's a legitimate choice. In my experience it's rare, though.
Accounting for unknown values is part of life and business computing. Procedural languages have their techniques. SQL has nulls. One way or another you'll eventually have to deal with nulls in the system, even if all table columns are defined "NOT NULL". Outer joins, for example, may introduce them.
As for performance, RDBM systems have efficient mechanisms to deal with nulls so this should not be a concern.
Were I writing this I would add the nullable column to the existing table.


Query Plans Change Performance Worse in SQL Server 2014 We recently upgraded our server from SQL Server 2008R2 to SQL Server 2014. We have a query that ran fine in 2008R2 but now in 2014 runs incredibly much slower and has a bad execution plan.

I've done several tests...

Switch the 2014 DB back to 2008/2012 compatibility mode.
Test the query using Pagination.

Both of those resulted in the query running the same as it did and fast as SQL Server 2008R2.

Why is the plan so bad and query run so long in SQL Server 2014?

This image shows 2 queries, one using rownumber the way it ran in 2008R2, and then the second is the fix with pagination. Both ran in 2014, both very different, but in 2008 we see the same performance as if we used pagination in 2014.

This is not an issue, it is by design and is considered a performance improvement.

If your queries do not run as expected you will need to address this in the code of your database/application.

You can force the old carnality estimator by using the trace flag 9481 at the server, session, or query level (using OPTION (QUERYTRACEON 9481)). This will force it no matter what the compatibility level of the database is.

There is also a ton of information that the CSS Team posted here.

A more detailed post on the Cardanility Estimator and changes around that process in SQL Server 2014, includes example.


Is there a (non-binary) MySQL collation that doesn't treat different mathematical symbols as the same character? I've run into a real headache with MySQL's collations and non-BMP characters (ones with Unicode codepoints above U+FFFF).

Basically, given a table and data like:

CREATE TABLE `math` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `symbols` varchar(32) character set utf8mb4 not null,
  PRIMARY KEY (`id`),
  UNIQUE KEY `symbols` (`symbols`)
);
INSERT INTO `math` VALUES (1,'');

(You may not have a font to display the character in the string literal above. It's U+1D542 MATHEMATICAL DOUBLE-STRUCK CAPITAL K)

things look OK:

mysql> select * from math;
+----+---------+
| id | symbols |
+----+---------+
|  1 |        |
+----+---------+
1 row in set (0.00 sec)

mysql> select * from math where symbols = '';
+----+---------+
| id | symbols |
+----+---------+
|  1 |        |
+----+---------+
1 row in set (0.00 sec)

So far so good. But then there's this crap:

mysql> select * from math where symbols = '';
+----+---------+
| id | symbols |
+----+---------+
|  1 |        |
+----+---------+
1 row in set (0.00 sec)

and

mysql> INSERT INTO `math` VALUES (2,'');
ERROR 1062 (23000): Duplicate entry '?' for key 'symbols'

(The string literal above has U+1D543 MATHEMATICAL DOUBLE-STRUCK CAPITAL L. Note that MySQL's error message has a ?, but the U+1D542 in the results of the SELECT above does dispaly correctly for me, so there don't seem to be encoding issues as far as IO with the server.)

(Code above updated; it originally had 1 for the primary key, which fails for obvious reasons.)

Screenshot for those with font issues:

So, MySQL thinks these two characters are the same? I know it case-folds, but this isn't a matter of casing.

Needless to say, I didn't even realize I had this problem till it came up in production, because the real-world data involved rarely differs on only these characters. However, this is totally unacceptable collation behavior.

Switching to the binary collation does fix it, however I'm using Django to access the database, and when I use a binary collation it then gives me bytes instead of characters (I can decode them myself, but that's a big pain).

I'm guessing the issue has something to do with these character being outside the BMP, but it still surprising bad behavior.

Is there a way to get MySQL to use sensible collation, short of writing and installing one myself?

I suspect the non-BMP characters are the problem, since I also tried:

⛇ (U+26C7 BLACK SNOWMAN) (a BMP character) works fine.
 (U+1F300 CYCLONE) (a SMP character) gives the same error as above
 (U+1F0A1 PLAYING CARD ACE OF SPADES) (SMP) same error
 (U+20003 CJK UNIFIED IDEOGRAPH-20003) (SIP) same error

(I can't link these to codepoints.net, since I don't have enough reputation. Should be fairly obvious what their URLs are though.)

I'm using MySQL 5.5.40 on Ubuntu 14.04.

From 10.1.14.1 Unicode Character Sets in the MySQL 5.5 Reference Manual (emphasis added):

  For supplementary characters in general collations, the weight is the weight for 0xfffd REPLACEMENT CHARACTER. For supplementary characters in UCA collations, their collating weight is 0xfffd. That is, to MySQL, all supplementary characters are equal to each other, and greater than almost all BMP characters.

and:

  The current rule that all supplementary characters are equal to each other is nonoptimal but is not expected to cause trouble. These characters are very rare, so it will be very rare that a multi-character string consists entirely of supplementary characters.

So the answer to your question appears to be "no".

finally:

  If you really want rows sorted by MySQL's rule and secondarily by code
  point value, it is easy:

  ORDER BY s1 COLLATE utf32_unicode_ci, s1 COLLATE utf32_bin

Though it is not clear to me how this should be applied for comparisons.

The documentation extracts quoted above are unchanged for MySQL 5.7.


Automatic database restore fails as cannot find file A while back I asked for help automating a database restore using a backup file provided from another server: Automatically restore SQL Server database from file from another server. I'm pleased to say that I now have this working as a SQL Server Agent job.

We are now trying to trigger this job as part of a maintenance plan that runs on the other (dev) server. At the moment we have a maintenance plan that runs on the live server and performs the following steps, in sequence:

Back up the target databases
Call an external process to copy the created backup files
Clean up any backup older than 2 days (runs at the same time as 2)
Start the restore job on the other server

When I run the maintenance plan on live it reports success, however when we check the job history on the dev server it shows that it has failed saying that it could not find the file. Running the job again returns success, and a manual check indicates that the files have been copied correctly.

It would seem that the restore job is being started too soon but I can't figure out why. Is there any reason why the restore job is being triggered before the copy process has completed?

EDIT: I've checked the maintenance plan logs and the restore job step starts 1 second after the transfer step. Obviously the transfer takes longer than this, so I can at least see evidence of the problem.

Copy step of your job will just inform operating system to start copy process and will mark this step completed and this will initiate next step "Restore Database", but meanwhile operating system is still copying your file to destination.

Before restore, you must verify file existence first.

Declare @file_exists int
EXEC xp_fileexist 'c:\MyDatabase.bak', @file_exists out
IF @file_exists = 1
BEGIN
  RESTORE DATABASE ....................
END
ELSE
  RAISERROR ('Restore Job Step Failure!', 16, 1)


Display only table names with psql How can I get the table names without a header or footer or anything else from a specified Postgres database with psql? That is, if I have tables called "table1" and "table2" then the output will be:

table1
table2

and only that. I've been trying various combinations of things, but it seems like it should be a lot easier than I'm making it.

In PostgreSQL (as described in documentation, The Information Schema):

SELECT table_name
FROM information_schema.tables
WHERE table_type='BASE TABLE'
AND table_schema='public';

For MySQL you would need table_schema='dbName' and for MSSQL remove that condition.

Notice that "only those tables and views are shown that the current user has access to". Also, if you have access to many databases and want to limit the result to a certain database, you can achieve that by adding condition AND table_catalog='yourDatabase' (in PostgreSQL).

If you'd also like to get rid of the header showing row names and footer showing row count, you could either start the psql with command line option -t (short for --tuples-only) or you can toggle the setting in psql's command line by \t (short for \pset tuples_only). This could be useful for example when piping output to another command with \g [ |command ].