SELECT query: filter unable to distinguish between two different characters In a recent support case, an error was traced to the difference between two similar-looking characters: ― (Unicode U+2015) and ー (Unicode U+30FC). A select statement with a WHERE clause was unable to distinguish between the two characters. Below is a simplified reproduction:

USE master;
GO

-- Create test DB.
CREATE DATABASE Test COLLATE Japanese_CI_AS;
GO

USE Test;
GO

-- Create test table.
CREATE TABLE Foo
(
    id INT NOT NULL PRIMARY KEY,
    descr NVARCHAR(50) NOT NULL
);

-- Insert test data.
INSERT INTO Foo (id, descr)
    VALUES (1, NCHAR(0x2015))
        , (2, NCHAR(0x30fc));

-- Select data.
-- Regardless of the WHERE, cannot distinguish between the two different characters.
SELECT * FROM Foo WHERE descr = NCHAR(0x2015);
SELECT * FROM Foo WHERE descr = NCHAR(0x30fc);

-- Only able to distinguish characters by adding a binary collation.
SELECT * FROM Foo WHERE descr = NCHAR(0x2015) COLLATE Japanese_Unicode_BIN
SELECT * FROM Foo WHERE descr = NCHAR(0x30fc) COLLATE Japanese_Unicode_BIN2;

The resolution was to fix the data to use only one of the characters. However, I am at a loss to explain why the SELECT query is unable to distinguish between the two characters without manually applying a binary collation. I'd appreciate if someone could explain what is happening here so that similar issues can be avoided in the future.

Both of those characters actually match more than just each other. Testing in both SQL Server 2012 SP3 and SQL Server 2016 SP1, and across the following 4 Collations (everything-sensitive) in each of those versions:

Japanese_XJIS_100_CS_AS_KS_WS_SC  (16)
Japanese_90_CS_AS_KS_WS_SC  (12)
Japanese_Bushu_Kakusu_100_CS_AS_KS_WS_SC  (15)
Japanese_Unicode_CS_AS_KS_WS  (13)

I found that each of those 2 characters matched anywhere from 10 - 14 additional characters (numbers in parenthesis in list above is total matches for the group that includes the two characters in the question):

Dec     Hex         Character
8213    0x2015      ―
12293   0x3005      々
12337   0x3031      〱
12338   0x3032      〲
12339   0x3033      〳
12340   0x3034      〴
12341   0x3035      〵
12347   0x303B      〻
12445   0x309D      ゝ
12446   0x309E      ゞ
12448   0x30A0      ゠
12540   0x30FC      ー
12541   0x30FD      ヽ
12542   0x30FE      ヾ
40981   0xA015      ꀕ
65392   0xFF70      ｰ

You can test with something along the lines of:

SELECT NCHAR(0x2015), NCHAR(0x30fc)
WHERE NCHAR(0x2015) = NCHAR(0x30fc) COLLATE Japanese_Unicode_CS_AS_KS_WS;

However, testing via the ICU Collation Demo (be sure to uncheck "diff strength" and check "sort key"), selecting either one of the two Japanese options from the drop-down, and using any combinations of radio-buttons, doesn't ever produce a match for U+2015, though U+30FC does seem to match U+FF70. So, based on this, it would appear that perhaps these two characters shouldn't match. It is always possible that one or more weights was entered incorrectly.

However (again), we must also keep in mind that what we see in that ICU Collation demo and on the Unicode site (the main pages, at least -- not prior versions) isn't the same version of Unicode that SQL Server is using. SQL Server is using either version 4 or 5 of Unicode, while the current stuff we see in those two places online is version 10..

Hence, unfortunately, you might have to stick with occasionally using a binary collation to distinguish between these characters.

Other notes:

Don't use the deprecated _BIN Collations. Just use the newer / current _BIN2 Collations
Just FYI, for binary Collations, the locale doesn't make a difference for NVARCHAR data. And the only difference for VARCHAR is the Code Page that is used (i.e. the character set). Meaning, with NVARCHAR data, there is no difference between Japanese_Unicode_BIN2 and Latin1_General_100_BIN2.
The new "Variation Selector Sensitive" (_VSS) Collations introduced in SQL Server 2017 do not help with this. This is primarily due to Variation Selectors being specific code points in the range of U+FE00 to U+FE0F and supplementary code points U+E0100 to U+E01EF. The characters in question here are not in either of those ranges. Also, Variation Selectors are zero-width, non-printable combining characters that attach to the preceding character.

There was a slight chance that the newer Collations would handle these characters better simple by being newer and hence having the opportunity to have weights added or fixed. However, there was no improvement as discovered using the following test:

;WITH cte AS
(
  SELECT TOP (65535) ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS [Num]
  FROM [master].[sys].[columns] col1
  CROSS JOIN [master].[sys].[columns] col2
)
SELECT cte.[Num], NCHAR(cte.[Num])
FROM cte
WHERE NCHAR(cte.[Num]) COLLATE Japanese_XJIS_140_CS_AS_KS_WS
     IN (NCHAR(0x2015), NCHAR(0x30fc))

Both Japanese_Bushu_Kakusu_140_CS_AS_KS_WS_VSS and Japanese_XJIS_140_CS_AS_KS_WS_VSS returned the same rows as Japanese_Bushu_Kakusu_100_CS_AS_KS_WS_SC and Japanese_XJIS_100_CS_AS_KS_WS_SC, respectively.


High CPU in SQL Server supporting SharePoint databases while running FAST search crawl I have a a SQL Server 2012 (SP1) #cu5 (X64) instance used as SharePoint 2010 back end and for the most part use only 10 - 30% CPU but there is a high CPU usage when running FAST search crawl(both incremental and full ).

Following Microsoft recommendation for SharePoint SQL instances Auto Create and Auto Update Statistics are turned off and SharePoint is supposed to take care of statistics and index maintenance using health analyzer rules and timer jobs. For the most part it does ok job but up on investigating the queries creating high CPU usage I found out there are missing statistics but i cant create them since modifying SharePoint databases will make them unsupported.

Please let me know if you have been in a similar situation and any helpful advice will be appriciated. Also let me know if you think https://sharepoint.stackexchange.com/ is the best place to post this instead of DBA SE.

SQL Server Environment:
HP ProLiant DL385p Gen8, Microsoft Windows Server 2008 R2 Enterprise SP1, AMD Opteron(tm) Processor 6204(single Socket, 2 Core, HT enabled)
Microsoft SQL Server 2012 (SP1) #cu5 (X64)

SharePoint Environment(I don't have much detail on this right now):
SP 2010 SP2(2 app server, 2 Web server, 1 FAST search server)

DaniSQL Feb 28 '14 at 19:46: Convert comment to Answer

Yes it is resolved. We have done multiple things in parallel so its hard to tell what exactly fixed it.

We applied latest service pack and CU on all servers including SharePoint and SQL, started maintaining indexes and updating stats manually in addition to SharePoint.

Then run the full crawl couple times and problem went away.


How to increase the amount of memory sql server uses on the server? On the db server, we have 4 GB of memory. Currently, the memory utilization of Sql Server 2005 on that server is 1,725,000 K and the total memory usage is stable at 2.04 GB. The Cpu usage on the server though is at 99% almost constantly.

How can I increase the memory that Sql Server uses so that it performs more operations using memory rather than reading from disk etc.?

This is configurable through SSMS.  However if you have a 32 bit build and are not booting with the /3GB switch the figures shown look suspiciously like the hard limits that will be imposed by the operating system anyway.

You can configure it from SSMS by right-clicking on the database server in the explorer, and you can see the memory usage policy under the 'memory' set of properties.

If you have a 32 bit server you may be able to use AWE to get more than your 2GB or so, although older versions of Std edition Windows (2003R1 and earlier) still restrict you to fairly small memory sizes.  If you have a more recent version of the operating system and a 64 bit build you should be able to use up to 32GB of memory.


Mysql: Is it possible to handle 1000 concurrent while having low cpu? We're running Centos 7, we're in the process Mariadb 5.5, upgrading to 10.3 today. PHP 5.

The queries are bad, and there are far too many of them to optimize, but I'm trying what I can, the database structure is bad.

The client doesn't care about query optimization, he just wants to lower CPU usage, he runs locust, a load testing tool, give it 1500 users and 1000 concurrent, sees the CPU at in the 90s % and says this is bad. The search functionality is the problem he said.

Again I'm telling you, the queries are bad, I just got hired and doing what I can, but is he looking at the right metrics?

What have I tried to do other than fixing queries:

Adding indexes, there were no indexes, also my.cnf

innodb_stats_on_metadata = 0
innodb_buffer_pool_dump_at_shutdown =1
innodb_buffer_pool_load_at_startup =1
## innodb_buffer_pool_instances should match the number of cores on the server

innodb_log_file_size = 2047M
innodb_flush_method=O_DIRECT
sync_binlog=0
innodb_thread_concurrency = 48
innodb_read_io_threads = 24
innodb_write_io_threads = 24

## set innodb_buffer_pool to 50% to 70% of the ram on the server so if the server have 32G, then set it to 16G. 50% is a good start
## turn off slow query log, if you're not logging, and only log for small durations when you want to monitor things
slow_query_log =0

I tried to optimize what I can from the slow query log as well. Also trying to use proxySQL, which I heard it intercepts the query and sends 1/10 of the queries to the server, because of caching.

The client has a streaming app, during peak events, he hits 7k users, not sure if that counts as concurrent users and highly doubt that all of them would search. They have an on-premise server

DL380 Gen10
2x Intel Xeon Silver 4110 / 2.1 GHz
64 GB RAM
300 GB HDD

My questions:

Is there any measure one could take that I didn't know about to
reduce the CPU usage of search queries?
If I were to optimize all the queries, would I expect lower CPU under such a load test? They want the CPU usage to drop to like 50%, I just feel that they're looking at the wrong metrics. They have the right to do so because it used to be that whenever cpu got to 100%, server would go down.

Here's how to identify which 'bad' query to fix that will give you the most bang for the buck:  The slowlog plus pt-query-digest.  More details here:  http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

I rarely need to go down the list more than 3 items to make a dramatic improvement.

If you are weak on knowing what indexes to add, don't simply "index ever column".  See http://mysql.rjweb.org/doc.php/index_cookbook_mysql

  Is there any measure one could take that I didn't know about to reduce the CPU usage of search queries?

No.  High CPU is fixed by using selected composite indexes and/or reformulating the queries.  Show us the queries.  "Search" is too vague a term.

Here's an extreme anecdote:  The CPU was pegged at 100%.  The slowlog pointed at one query.  It had WHERE DATE(foo) = '...'.  By making that "sargable", the CPU dropped to 2%.

As for benchmarks...  Beware.  They tend to see how many connections they can run before running out of horsepower.  That is, the results are guaranteed to say 100% CPU!  The real metric they get out is "queries/second" or "maximum connections".

Furthermore, it has repeatedly been documented that if you push mysql beyond some number of concurrent users, throughput goes down!  A decade ago, that number was about 6; now it is more like 64.  But 1000?  No way.  It is spinning its wheels because all 1000 are stumbling over each other.

How to handle 1000 users?  Replication feeding multiple Slaves.  With such, you could have 1000 slaves, each running a mere 10 connections; CPU will be very low.  Etc.


Restore from SQL Server 2014 Enterprise to BI Is it possible to restore a SQL Server 2014 Enterprise database with table partitioning (Production) to a SQL Server 2014 BI Version (Test/Development) ?

Of course I don't expect to use the table partitioning Feature on BI version, but I do expect a runnable version ...

You can restore it if you're not using any Enterprise Edition features.

In this case, you are using table partitioning so that restore path is not supported.

You can run:

SELECT *
FROM sys.dm_db_persisted_sku_features;

...to check for other features that can block your cross-edition restore.

Consider using Developer Edition as your test/development platform. It is very cheap and has all of the features of Enterprise Edition.


How to use a conditional ORDER BY in MySQL? I have the table courses where we have some fields and also the field course(string), favorited(bool), favoritedDate(dateTime), dateUpload.

The teacher uploads the course and this date is stored in the dateUpload field. If the teacher flag this course as favorite this information is a number 1 in the field favorited and the date it was flagged is stored in the field favoritedDate.

Now I need to retrieve all courses from a specific teacher and then order the list by date: If the course is flagged favorited=1 should ordered by favoritedDate,dateUpload desc, if favorited=0 should be ordered only by  dateUpload desc.

This is what I have so far but it is not working:

SELECT * from courses where teacherId = 23
ORDER BY
  CASE favorited WHEN 1 THEN favoritedDate, dateUpload
    else dateUpload
  END
  DESC
;

Any idea how to solve this?

A case expression returns an atomic value so you can't return:

favoritedDate, dateUpload

I think this will do what you want:

ORDER BY
    CASE favorited WHEN 1 THEN favoritedDate
                          ELSE dateUpload
    END DESC, dateUpload


UPDATE BIGINT[] column from a SELECT I have to update a bigint[] column named permissao_ver.
Example value: '{1,2,3,4,5,11,44,56,75,11}'.

Pseudo code:

UPDATE callcenter.pausa
SET permissao_ver = '{"(SELECT cod_grupo FROM crm.usuariosgrupos
                        WHERE habilitar = 1)"}'::bigint[]
WHERE habilitado = 1 AND permissao_ver is null

I need to get the codes the above select will return in a format matching the bigint[] column. Like: '{XX, XX, XX, X, XX, X}'.

Use array:

UPDATE callcenter.pausa
SET permissao_ver = array(SELECT cod_grupo
                          FROM crm.usuariosgrupos
                          WHERE habilitar = 1)
WHERE habilitado = 1
  AND permissao_ver is null


Is it good practice to refer to a table with different names in a database schema? We have a database currently running with a table named a certain way (let's say thing for the sake of example), and references to that table named the same way (FK would be named thing_id for instance).
We also used to have this thing concept in the code at some point, but then it was decided for various reasons to rename it (let's say to stuff), which was done over time. Now, the code no longer mentions thing except in SQL queries.
We unfortunately can't easily rename the table or columns since they're being heavily used, and we were wondering how to deal with new tables referring to this concept of thing/stuff.

Some of the team think that the schema should be the most up-to-date as possible, and that we should use the new naming (stuff) everywhere new (in join table names and FK names referring to thing)
Others think that the database schema should always stay consistent, and thus we should keep using the old naming (thing) anywhere, and handle the renaming in the code itself

We're trying to understand what are best practices here and what is advised to do in this situation where the DB schema and the code naming diverge?

There are many examples of best practices for schema naming e.g. here and here
Keeping it simple and based on experience and opinion of course, when designing schemas, we want:
PrimaryKey: Auto-incremented value in the naming pattern <entity name(singular)>_ID e.g. thing_id or ThingID.
UniqueKey: Add a unique constraint on a unique key. In a strong entity, usually on a real-world reference (e.g. student_id). In associative tables, these are usually on combinations of foreign keys (e.g student_id, course_id).
Foreign Keys: These are references to auto-incremented values in their source tables and so are the same data type and name as the values they reference. So without looking at the whole schema, if I see ThingID in a different table not called Thing, I know it points to the primary key of the Thing table.
In your case, I would say you should:

First decide what the correct solution should be for future maintainability and support of the schema alone without any other considerations. Imagine you are a new team member and you look at this new design with its naming structure, would you be able to understand it?
Determine what is preventing the team from executing the correct solution e.g. no one is comfortable with the code base or you didn't write it or you don't feel that anyone on the team has the wherewithal to modify the code and not break the application.
If #2 is insurmountable, then consider

a. Making the changes in #1 but add a layer like a view (add a "v_tablename") with the same old names and call the views instead of the tables from the code so the application works
b. Making the changes in #1 but add a layer or interface in the code base which allows some mapping of new field classes to old existing field classes so the application works
c. Isolate the schema and application in a separate parallel environment in which changes are made in both code and schema and verified thoroughly to ensure that the application works.

If you have zero access to the code base, then you just make the correct changes for the new tables - perhaps using a prefix-naming standard so you can identify old schema vs new schema.
If old schema has the correct naming, of course continue with it.

Overall, treating the codebase with undeserved trepidation can force inch-by-inch workarounds/accommodations and eventually, the team ends up with a design and system in both code and schema that no-one fully understands. This possible reality can add risk to your organization.


InnoDB Error - InnoDB: Error: trying to remove tablespace 264 from the cache but InnoDB: it is not there Im using AWS aurora which is MySQL 5,6 Recently I can see an error like below.

2017-02-14 20:42:34 2b2ca6a00700 InnoDB: Error: trying to remove tablespace 264 from the cache but
InnoDB: it is not there.
2017-02-14 20:42:34 2b2ca6a00700 InnoDB: Error: trying to remove tablespace 264 from the cache but
InnoDB: it is not there.
2017-02-14 20:49:56 2b2ca6a00700 InnoDB: Error: trying to remove tablespace 378 from the cache but
InnoDB: it is not there.
2017-02-14 20:49:56 2b2ca6a00700 InnoDB: Error: trying to remove tablespace 378 from the cache but
InnoDB: it is not there.
2017-02-14 20:50:41 2b2ca6a00700 InnoDB: Error: trying to remove tablespace 361 from the cache but
InnoDB: it is not there.
2017-02-14 20:50:41 2b2ca6a00700 InnoDB: Error: trying to remove tablespace 361 from the cache but
InnoDB: it is not there.
2017-02-14 20:51:10 2b2ca6a00700 InnoDB: Error: trying to remove tablespace 393 from the cache but
InnoDB: it is not there.
2017-02-14 20:51:10 2b2ca6a00700 InnoDB: Error: trying to remove tablespace 393 from the cache but
InnoDB: it is not there.

I don't know why its coming, Can anyone tell what its actually trying to say?

That seems to be a AWS problem bug.
Please, see https://forums.aws.amazon.com/thread.jspa?threadID=226520


dump selected result with fields name It is simple to dump select result into a file:
select *  from table_name into outfile  result.txt;

The result.txt contains records ,but no fields in it,how can dump selected result  with fields name?

You can use two steps for large tables
First get all colum nnames from your
set session group_concat_max_len = 1000000;

select GROUP_CONCAT(CONCAT("'",COLUMN_NAME,"'"))
from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table_name '
AND TABLE_SCHEMA = 'schema_name'
order BY ORDINAL_POSITION

And use the collected information for the next Query instead of ColumnName1-3
SELECT 'ColumnName1', 'ColumnName2', 'ColumnName3'm, 'ColumnName4'
UNION ALL
SELECT `ColumnName1`, `ColumnName2`, `ColumnName3`, `ColumnName4`
    FROM table_name
    INTO OUTFILE '/path/result.txt'

If the table is not that big, you can combine the both with user defined variables and prepared statements


mysqladmin - u root -p ping is not executing I am getting the below error in mysql while trying to ping. i am using mysql version 5.5.37-0ubuntu0.12.04.1. when ever i use mysqladmin i am getting the below error.

Please suggest how to clear this issue

karthick.g.s@sumtwo:~$ mysqladmin -u root -p ping
Warning: mysqladmin: unknown variable 'loose-local-infile=1'
mysqladmin: unknown variable 'local-infile=1'

You probably have:

local-infile=1
loose-local-infile=1

In your [client] section on /etc/my.cnf , /etc/mysql/my.cnf or /home/karthick.g.s/.my.cnf or somewhere else.

Change it to a [mysql] section so it doesn't affect mysqladmin.

You can also do:

$ mysqladmin --no-defaults -u root -p ping


Multiple DB restore in sequence I am restoring several MariaDB databases into a Amazon AWS RDS MariaDB instance from an AWS EC2 machine in the same VPC / region etc.

I am using a shell script like so:

mysql -u root -h hostname.amazonaws.com -ppassword < db1.sql
mysql -u root -h hostname.amazonaws.com -ppassword < db2.sql
mysql -u root -h hostname.amazonaws.com -ppassword < db3.sql
...

The sequence is important since the later databases have VIEWS and JOINS that reference earlier databases. I have figured that part out and doing the DBs one at a time manually works as expected.

The issue is, when I chain the DBs in a single shell script, the later restore for, say DB2, complains that some table in DB1 does not exist. The exact error message is below:

  ERROR 1146 (42S02) at line 482: Table 'db1.table_name' doesn't exist

My guess is that the MySQL client sends the whole SQL file to the RDS server and starts on the next SQL file, without waiting for the previous one to complete successfully.

Is there any way I can get the earlier SQL files to block till it completes successfully or fail and break subsequent SQL files from executing?

Client and server info below:

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

Server version: 10.0.31-MariaDB MariaDB Server

My guess is that the MySQL client sends the whole SQL file to the RDS server and starts on the next SQL file, without waiting for the previous one to complete successfully.

That is not the problem.

The problem is, mysqldump only takes care of this, automatically, if you use it as intended:

mysqldump [options] --databases db1 db2 db3

Otherwise, it essentially assumes any dependencies will already have been met on the server when you restore a dump file, so it does nothing to resolve them except within the individual db being restored.

If you create a single dump file, like this, specifying all the databases you need but using the --no-data option, you'll get a small file with all your tables and views, but no data.  Restore that first, and then your 3 individual files.


How do I map a SQL Server login to a user that already exists in a read-only database? One of our database users complains that they can't select from one of the databases in this particualr instance while they have no problems in accessing others in this very same instance. The one that they have access problems is shown as "Standby / Read-Only". The others are just normal.

So, I checked the Security -> Logins for the given principal and looked at the principal's properties to see if there were any user mappings to the read-only database for this login. There were no such mappings for that particular db while there were mappings for other databases and all with "db_datareader" role membership. Initially, I wanted to add a user mapping for this db as well; However, it failed with the below message:

  "Failed to update database X because the database is read-only"

Then, I checked the read-only database and found that a user exists in there with the same name as the account that had this issue. It also had "db_datareader" role membership. So, I guess I just need to link the two; however, I don't know how. Please help me! Thanks.

Since the status of the database is Standby, I'm assuming this database is a Log Shipping secondary. If that's the case, the database cannot be written to at all on the secondary (making it writable would break Log Shipping).

Also, since you said that other databases (presumably not read-only) have correct mappings, I'm assuming you're using a SQL login. If that's the case, all you should have to do is recreate the login on the secondary server using CREATE LOGIN ... WITH SID to match the SID from the primary (look in sys.server_principals on the primary server to find it).

On the other hand, if you're using Active Directory for the login, you'll have to add the user principal to the primary database so that when it comes across to the secondary in a log backup, there's no need to do any remapping (note: depending on the situation, this may be a lot easier said than done!).


mongodb crashed with the error or permission denied Our mongodb server was running for the last two months without any error. Suddenly it crashed and below is the log.

2017-08-11T13:41:41.320+0100 E STORAGE  [thread1] WiredTiger (13) [1502455301:320924][1281:0x7f11bce8e700], log-server: /var/lib/mongo/journal: opendir: Permission denied
2017-08-11T13:41:41.320+0100 E STORAGE  [thread1] WiredTiger (13) [1502455301:320991][1281:0x7f11bce8e700], log-server: dirlist journal prefix WiredTigerPreplog: Permission denied
2017-08-11T13:41:41.321+0100 E STORAGE  [thread1] WiredTiger (13) [1502455301:321013][1281:0x7f11bce8e700], log-server: log pre-alloc server error: Permission denied
2017-08-11T13:41:41.321+0100 E STORAGE  [thread1] WiredTiger (13) [1502455301:321021][1281:0x7f11bce8e700], log-server: log server error: Permission denied
2017-08-11T13:41:42.000+0100 I -        [ftdc] Assertion: 13538:couldn't open [/proc/1281/stat] errno:13 Permission denied
2017-08-11T13:41:42.000+0100 W FTDC     [ftdc] Uncaught exception in 'Location13538 couldn't open [/proc/1281/stat] errno:13 Permission denied' in full-time diagnostic data capture subsystem. Shutting down the full-time diagnostic data capture subsystem.
2017-08-11T13:41:50.006+0100 I NETWORK  [initandlisten] connection accepted from 127.0.0.1:45196 #205521 (23 connections now open)
2017-08-11T13:41:50.006+0100 I NETWORK  [conn205521] end connection 127.0.0.1:45196 (22 connections now open)
2017-08-11T13:42:36.004+0100 E STORAGE  [thread2] WiredTiger (13) [1502455356:4361][1281:0x7f11bb68b700], file:WiredTiger.wt, WT_SESSION.checkpoint: WiredTiger.turtle: fstat: Permission denied
2017-08-11T13:42:36.004+0100 E STORAGE  [thread2] WiredTiger (13) [1502455356:4466][1281:0x7f11bb68b700], checkpoint-server: checkpoint server error: Permission denied
2017-08-11T13:42:36.004+0100 E STORAGE  [thread2] WiredTiger (-31804) [1502455356:4480][1281:0x7f11bb68b700], checkpoint-server: the process must exit and restart: WT_PANIC: WiredTiger library panic
2017-08-11T13:42:36.004+0100 I -        [thread2] Fatal Assertion 28558
2017-08-11T13:42:36.004+0100 I -        [thread2]

***aborting after fassert() failure

Please tell me why it happened and what should I do to avoid this crash in future. I am using mongodb version 3.2. How come the file permission changed while the server is running.

It's not mongodb fault. Your system went to "read only" mode.
You can see errors on that log file. Positive is that system managed to write that information to log file because it makes easier to debug this problem.

/var/lib/mongo/journal: opendir: Permission denied
couldn't open [/proc/1281/stat] errno:13 Permission denied

Check /var/log/syslog (or /messages) for more info, something fatal happened on that node and it's operating system!


How do convert a 66,862,521 row table from MyISAM to InnoDB without going offline for several hours? is it possible (and how) to convert a huge MyISAM table into InnoDB without taking the application offline. It requires to insert a couple of rows into that table every second but it is possible to suspend it for about 2 minutes.

Obviously ALTER TABLE ... engine=innodb will not work. Therefor I had the plan to create a new table with the innodb engine and copy the content into it. And in the end, suspend the application log thread and RENAME TABLE.

Unfortunately even doing the copying in small batches of 100 rows generates significant lag after some time.

Edit: Existing rows are never changed, this table is used for logging.

Create a Master-Master setup as follows:

Create second master, MasterB
MasterB acts as slave to logTable
Create logTable_new as innodb
Run INSERT INTO logTable_new SELECT * FROM logTable (psuedocode) on MasterB, which sends the replication over to MasterA
When logTable_new on MasterA finishes syncing, swap out the tables


How would you implement conversation groups/ locking for multiple users of a Service Broker Queue? How do you implement conversation groups for multiple users using their own instance of the user applications but sending related messages to the service broker queue?  Any good example of this kind of implementation?  I had imagined that I could set the conversation group ID myself, guaranteeing that certain groups of messages from various users will be related, but it seems that this value is going to be automatically generated as a UNIQUEIDENTIFIERS... Is this even the best way to do it?

I would like to make sure to catch and lock related messages where two users "on the same team" for instance are bidding on the same item together, so that the server application logic can properly process those related messages together (basically only the first one will be accepted and the second one will be rejected until the user's view can be updated so they know that the other user changed the bid already).

flow direction is from users A, B, C sending xml messages to the queue, then a dequeuing service which sends the xml to an external web service.  The messages we are sending are structured xml messages which will modify orders on different items on the external web service after dequeuing.

Thank you!

You cannot use conversation groups to exclude application instances, if that's what you're trying to do. If Instance A needs to receive messages from the queue only for Instance A and Instance B needs to receive messages only for Instance B then instance A needs to use queue A and instance B needs to use queue B. Conversation group can be used only when Instance A and Instance B can both process any messages, but you want to exclude them from processing correlated messages concurrently. Every time I've seen someone trying to pre-generate conversation group IDs, the idea was always bad.

Update

The initiator cannot control the conversation group of the destination (target). Even if A and B were in the same conversation group at the initiator site, this does not mean they would be the same at the target site since conversation group is a local concept and doe snot travel with the message. If you want the messages sent by A and B to belong to the same conversation group on the target side, then the dialog has to be started in the 'reversed' order: the destination begins the dialog(s) and places them in a single conversation group, then the target(s) start sending messages on this dialog(s). So conversations act like an 'invitation' to send messages, the logical 'target' acts as the actual 'initiator'.

Update #2.

this is how it would work, in theory: say you have 10000 products. To do the 'reverse' pattern, when a user want to participate in the application it needs to 'join' in. So he starts a conversation with the server and send a message 'I want to join' (lts call this 'conversation 0'). The server processes this message by starting a conversation with this new user for each product. Now the user has 10000 conversations on which it can send 'bids'. For product A it uses conversation 1, for product B conversation 2 etc. When user B wants to join in it also starts a new conversation with the server and sends an 'I want to join' message. The server responds by starting 10000 conversation with this new user, again one for each product, and it makes sure each one is in the corresponding group for the product, so conversation for product A with user 1 is in the same group as conversation for product a for user 2.

Now obviously anyone will figure that this scheme is flawed: it requires number_of_products X number_of_user conversations, it makes adding and removing products a pain (must maintain all those user conversations!) and so on. One alternative is to multiplex products per conversation. Say the server only starts 10 conversations with each user and the user uses the conversation corresponding to the last digit of the product id (so product 1 goes to conversation 1, but so does product 11 or 101). This is more viable, it requires far fewer conversations, and requires no special conversation management when products are added or removed. You may consider that is a downside that now the server locks not all messages for product 1, but also all messages for product 11, all for 101 etc, but consider this: it only matters if you have more processing threads on the server than the number of conversations per user. If you have 5 threads, then it doesn't matter that you locked 1, 11, 101, there still are messages to process for the other 4 threads. Only if you'd have 11 threads it would matter, since the 11th thread has nothing to process.

Now I'm not advocating to deploy exactly this, I'm just pointing out some possibilities. In most cases the CG locking would be per user, not per product, and adding this extra dimension of using CG locking to avoid concurrency problems on each product is a little unorthodox.

And don't forget that the only construct that guarantees order in SSB is a conversation. So if users have to send bids for A on conversation 1  followed by bids for B on conversation 2 then there is no guarantee that the bid for A is going to be processed after the bid for B is processed. The only guarantee is that if user 1 sends two bids for A, they will be processed in the order sent.

Also, if two different users send bids for product A then there is no guarantee of the order of processing of these bids. However, if the bids for product A end up on the same CG then there is a guarantee that only one 'processor thread' will see both bid from user 1 and bid for user 2, but be careful because there is no guarantee that the bids are presented in the RECEIVE result set in the order they were received. RECEIVE only guarantees that:

all messages in the result set are from the same CG
the messages belonging to a conversation are in order

but the order of conversations in the result is basically random (is the driven by the conversationhandle ordering, a GUID).

Before I forget: remeber there is the also MOVE CONVERSATION but relying on MOVE (rather than starting conversations directly in the correct CG) is very very very deadlock prone.


First run of planner very slow on massively partitioned PostgreSQL 9.6 table I have a rather massive table of financial data in Postgres 9.6. It is partitioned on two levels, first by trading day (DATE type) and subsequently by security ID (BIGINT). Overall the table comprises 20 per-day paritions which are each further subdivided into 15 per-instrument partitions. I use the conventional method of partitioning using CHECK constraints. The partitions are unlogged tables which I load in-bulk via COPY.

When prforming EXPLAIN ANALYZE on trivial queries which can be satisfied from a single partition I see that the first run of the planner can take up to 30 seconds in some cases. I suppose this has to do with some attributes of the tables which the planner uses taking time to load into memory. What's causing this behavior? How can I "warm up" the database to avoid this lengthy planning which surprises my users?

Example EXPLAIN ANALYZE output:

trading_offline=# EXPLAIN ANALYZE--
select * from public."ticks"
where trading_day ='2016-06-20'::date and inst_id= 1535681 and is_trade = true
and qty<-200;

First time - planning took 2.4 seconds:

                                        QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Append  (cost=0.00..0.00 rows=1 width=161) (actual time=0.016..0.016 rows=0 loops=1)
->  Seq Scan on ticks  (cost=0.00..0.00 rows=1 width=161) (actual time=0.014..0.014 rows=0 loops=1)
        Filter: (is_trade AND (qty < '-200'::integer) AND (trading_day = '2016-06-20'::date) AND (inst_id = 1535681))
Planning time: 2415.293 ms
Execution time: 0.051 ms
(5 rows)

xeur_trading_offline=# explain analyze select * from xeur."ticks"
where trading_day ='2016-06-20'::date and inst_id= 1535681 and is_trade = true
and qty<-200;

Subsequent times - planning is down to ~ 40 ms:

                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Append  (cost=0.00..0.00 rows=1 width=161) (actual time=0.017..0.017 rows=0 loops=1)
->  Seq Scan on ticks  (cost=0.00..0.00 rows=1 width=161) (actual time=0.017..0.017 rows=0 loops=1)
        Filter: (is_trade AND (qty < '-200'::integer) AND (trading_day = '2016-06-20'::date) AND (inst_id = 1535681))
Planning time: 42.067 ms
Execution time: 0.062 ms
(5 rows)

                       QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Append  (cost=0.00..0.00 rows=1 width=161) (actual time=0.024..0.024 rows=0 loops=1)
->  Seq Scan on ticks  (cost=0.00..0.00 rows=1 width=161) (actual time=0.024..0.024 rows=0 loops=1)
        Filter: (is_trade AND (qty < '-200'::integer) AND (trading_day = '2016-06-20'::date) AND (inst_id = 1535681))
Planning time: 42.082 ms
Execution time: 0.070 ms
(5 rows)

PostgreSQL holds lot of catalog attributes in session based system cache. When query uses lot of partitions (more than 100), then first plannning requires lot of reading operations. And it can be too slow.

The number of partitions should not be bigger than 100. If you have more, then you can see some artefacts.

In your case, the connection pooling can be solution. Try to install pgbouncer.


SQL 2012 Availability Group issue - The core cluster resources do not move when we initiate AG fail-over I have a Windows 2012R2 cluster with two nodes and a File Share quorum. I am running SQL 2012 SP2 and I have configured a test AG with a dummy database.

When I fail-over the AG, the Availability Group Listener and the associated Availability Group Role fail-over as expected, but the Cluster Core Resources, specifically the Cluster Virtual Network Name, does not. In order to move the Cluster VNN I need to right-click on the cluster, go to more actions and select "Move Core Cluster Resources", but shouldn't this be occurring automatically?

I think I need to specify a dependency somewhere, but for the life of me I cannot find where.

There is no reason to move the cluster core resources. They are NOT part of the availability group and function entirely on their own. There is no need to even have these on the same server as any of your AG resources.

The core cluster group provides the administrative point for the cluster which is completely independent from any other resources on the cluster. Leave it migrate on its' own.


What are the best practices for backing up a secured SQL Server 2012? I want to state right off the bat that I'm not looking for information on how to backup a SQL Server. I've got that taken care of.

I'm looking for some form of documentation that will tell me the steps I need to take/things I need to keep in mind to properly back up a secured (with a 3rd party certificate) SQL Server 2012.

What, other than the actual database information, do I need to send to my backup solution so I can ensure minimal downtime in a worst case scenario (knock on wood)?

There's different things you can do with certificates: you can use them to encrypt endpoint communications (like mirroring or AlwaysOn Availability Groups), or you can use them to encrypt databases with Transparent Data Encryption (TDE).

If you're using them to encrypt endpoint communications, just back up the certificates once and keep them in a safe place. I wouldn't expect shops to routinely rotate those certificates.

If you're using them with Transparent Data Encryption, back up both the certificate and the key as described in Books Online:

http://technet.microsoft.com/en-us/library/bb934049.aspx

With TDE, though, you should routinely rotate the keys. Otherwise, when anybody steals the certificate and key, then they can decrypt all of the backups going forward. When you rotate the key, you'll want to immediately back that up again. You can't run backups while the database is re-encrypting with the new key, but back it up again as soon as the re-encryption process is done.


Log Reader replication agents are constantly restarting I am recreating (push) transactional replication from a SQL 2000 instance to a SQL 2008 instance which I had to break in order to do a db migration. The 2008 instance is both the distributor and a subscriber.

About 1/2 of my subscriptions are failing with

  Could not find stored procedure 'sp_MSins_dboTABLE'.

(more info):

  Command attempted: if @@trancount > 0 rollback tran (Transaction
  sequence number: 0x0002A2B400008D19000100000000, Command ID: 20)

  Error messages: Could not find stored procedure
  'sp_MSins_dboTABLE'. (Source: MSSQLServer, Error number: 2812)
  Get help: Could not find stored procedure
  'sp_MSins_dboTABLE'. (Source: MSSQLServer, Error number: 2812)
  Get help:
  I've chased just about every path I can find, from running sp_scriptpublicationcustomprocs (I executed the result in the distribution database) to deleting the subscription, then the publication, and then re-creating them.

The working subscriptions were created using the same methodology.

Here are my create scripts:

https://gist.github.com/3152160

Edit:

Update --- My failing subscriptions are also throwing the following error (through Profiler):

  Table already has a primary key defined on it.

It sounds like you just need to reinitialize the subscription which will recreate all the objects on the subscriber.

EDIT (Added by asker):

After re-initialization, if there are errors to the effect that it couldn't find a CRUD stored procedure, fire up SQL Profiler and look for errors surrounding that procedure. Make sure you filter the text based on the procedure name from Replication Monitor (It turns out that the command being executed was the stored procedure with a ;2 after it).

Then run EXEC sp_scriptpublicationcustomprocs 'PublicationName' on the publisher to get scripts to create the procedure. Create that procedure on the subscriber db including the group number that the process is attempting to run after the group name (e.g. [sp_MSins_dboTABLE];2). It feels hacky, but it will get replication functioning healthily again.


Select Table Vs Select View? I was told from a SQL-DBA that there is a huge gap in performance between these statements, both statements are called from a stored procedure:

-- Procedure that select from table directly
Select * From [Table] Where condition

-- Procedure that select from a view
Select * From [View] Where condition

The SQL-DBA told me that selection from table is faster because when you select from a View, all data will be selected first then filtered, but from table it will apply filtration directly. I am a developer and I don't have much knowledge in database so I was hoping for some expert to advice me in this matter.

Upon answering this question, I think I will be able to know weather using Views is a best practice or not.

The SQL-DBA told me that selection from table is faster because when
  you select from a View, all data will be selected first then filtered,
  but from table it will apply filtration directly.

As a general statement this is nonsense. The view definitions are expanded out during query optimization and the predicates are pushed down as if you had filtered against the table directly.

You can check the execution plans to confirm this.

If you are filtering on the result of a calculated expression then this may be problematic however (the same as any unsargable expression on a table) and there have been issues with SQL Server not pushing predicates down as far as it could do for views containing window functions.


Getting credentials from mysql db I have a request from a customer and I am quite sure the answer is no, but wondering if someone has a different answer.

Background

As you know MySQL installation create a database called "mysql" where it stores the databases we create and also the users.

In the user table, there is a field called "authentication_string" where the user password is saved.

Project

On this project each time a customer creates an account a new database user and database is created.

When a customer logs in through a web interface, the system calls an API to authenticate him/her. After that the root db user is used to connect to customer database, not their own database credentials, why? because they do not want to save user and password on database (This is a temporal solution).

They want to change the application so after authentication/authorization process and they would somehow only needed root credentials to somehow get user and password from "mysql db" and then use them to create the connection using customer db credentials.

Is this possible? Or is there some mysql parent - children configuration where this scenario is possible?

Project uses MySQL 5.7

they would somehow only needed root credentials to somehow get user
  and password from "mysql db" and then use them to create the
  connection using customer db credentials. Is this possible?

No, this is not possible, passwords are stored hashed. Hashes are one-way functions where if you get the password, you can get the hash, but it is virtually impossible to do the opposite.

Your API must store temporarily the password, somewhere when it is created (probably on memory), and then send it securely to the client application, then discarded. If that is not desired, you should try a different authentication method that doesn't require passwords or shared secrets.

MySQL authentication should be separate from an application authentication. You could store user credentials (properly hashed and salted) in a database table, but you should keep the users of your application and the users of a database separated (or use an external authentication mechanism, as suggested above).


'SELECT TOP' performance question I have a query which runs much faster with select top 100 and much slower without top 100. The number of returned records is 0.
Could you explain the difference in query plans or share links where such difference explained?

The query without top text:

SELECT --TOP 100
*
FROM InventTrans
     JOIN
     InventDim
     ON InventDim.DATAAREAID = 'dat' AND
        InventDim.INVENTDIMID = InventTrans.INVENTDIMID
WHERE InventTrans.DATAAREAID = 'dat' AND
      InventTrans.ITEMID = '027743' AND
      InventDim.INVENTLOCATIONID = 'КзРЦ Алмат' AND
      InventDim.ECC_BUSINESSUNITID = 'Казахстан';

The query plan for the above (without top):

https://pastebin.com/cbtJpxFf

The IO and TIME statistics (without top):

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

(0 row(s) affected)
Table 'INVENTDIM'. Scan count 0, logical reads 988297, physical reads 0, read-ahead reads 1, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTTRANS'. Scan count 1, logical reads 1234560, physical reads 0, read-ahead reads 14299, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 6256 ms,  elapsed time = 13348 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

The used indexes (without top):

1. INVENTTRANS.I_177TRANSIDIDX
   4 KEYS:
 - DATAAREAID
 - INVENTTRANSID
 - INVENTDIMID
 - RECID
2. INVENTTRANS.I_177ITEMIDX
   3 KEYS:
   - DATAAREAID
   - ITEMID
   - DATEPHYSICAL
3. INVENTDIM.I_698DIMIDIDX
   2 KEYS:
   - DATAAREAID
   - INVENTDIMID

The query with top:

SELECT TOP 100
*
FROM InventTrans
     JOIN
     InventDim
     ON InventDim.DATAAREAID = 'dat' AND
        InventDim.INVENTDIMID = InventTrans.INVENTDIMID
WHERE InventTrans.DATAAREAID = 'dat' AND
      InventTrans.ITEMID = '027743' AND
      InventDim.INVENTLOCATIONID = 'КзРЦ Алмат' AND
      InventDim.ECC_BUSINESSUNITID = 'Казахстан';

The query plan (with TOP):

https://pastebin.com/0dyu6QZd

The query IO and TIME stats (with TOP):

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

(0 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTTRANS'. Scan count 15385, logical reads 82542, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'INVENTDIM'. Scan count 1, logical reads 62704, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 265 ms,  elapsed time = 257 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

The used indexes (with TOP):

 1. INVENTTRANS.I_177TRANSIDIDX
     4 KEYS:
     - DATAAREAID
     - INVENTTRANSID
     - INVENTDIMID
     - RECID
 2. INVENTTRANS.I_177DIMIDIDX
    3 KEYS:
    - DATAAREAID
    - INVENTDIMID
    - ITEMID
 3. INVENTDIM.I_698DIMIDIDX
    2 KEYS:
    - DATAAREAID
    - INVENTDIMID
 4. INVENTDIM.I_698ECC_BUSUNITLOCIDX
    3 KEYS
    - DATAAREAID
    - ECC_BUSINESSUNITID
    - INVENTLOCATIONID

Will deeply appreciate any help on the topic!

SQL Server builds different execution plans for TOP 100, using a different sort algorithm. Sometimes it's faster, sometimes it's slower.

For simpler examples of it, read How Much Can One Row Change A Query Plan? Part 1 and Part 2.

For in-depth technical details, plus an example of where the TOP 100 algorithm is actually slower, read Paul White's Sorting, Row Goals, and the TOP 100 Problem.

The bottom line: in your case, if you know that no rows will be returned, well...don't run the query, eh? The fastest query is the one you never make. However, if you need to do an existence check, just do IF EXISTS(stick query here), and then SQL Server will do an even different execution plan.


Securely generate a UNIQUEIDENTIFIER in SQL Server I intend to be using a UNIQUEIDENTIFIER as an access key that users can use to access certain data. The key will act as a password in that sense.

I need to generate multiple such identifiers as part of an INSERT...SELECT statement. For architectural reasons I want to generate the identifiers server-side in this case.

How can I generate a securely random UNIQUEIDENTIFIER? Note, that NEWID would not be random enough as it does not promise any security properties at all. I'm looking for the SQL Server equivalent of System.Security.Cryptography.RandomNumberGenerator because I need unguessable IDs. Anything based on CHECKSUM, RAND or GETUTCDATE would also not qualify.

SELECT CAST(CRYPT_GEN_RANDOM(16) AS UNIQUEIDENTIFIER)

Should do the trick I would have thought.

CRYPT_GEN_RANDOM

  Returns a cryptographic random number generated by the Crypto API
  (CAPI).


timestamp based concurrency I am wondering if someone could help me out with a question I have about time stamp based concurrency.

I found the following practice problem on a website

It basically asks which of the following 4 will occur for the LAST transaction:

(a) the request is accepted.

(b) the request is ignored.

(c) the transaction is delayed.

(d) the transaction is rolled back.

The  stands for the order of the time stamps and the  = read  = write

st1; st2; r2(A); co2; r1(A); w1(A)

The solution they give is choice (d) which had me kind of confused as to how they are coming to that.  I may be mistaken about how the time stamps work but I thought that if a transaction wishes to access a shared variable it must have the earliest time stamp.  In this example transaction 2 tries to read(A) first but I thought that should be delayed (since transaction 1 has the earlier of the 2 time stamps.  So once we get to the r1(A), w1(A) since transaction 1 has the earlier stamp that should succeed as I understand it so I'm not sure why they choose (d).  If I am misunderstanding the concept could someone please explain it to me I would greatly appreciate it.

"Transaction T can read or write data item X only if X had not been accessed by a "later" transaction.  Otherwise, the operation of T is rejected; T is aborted and restarted with new TS".

In other words we have:

ST1 and then ST2 any data that is read or write by T2, could not be access by T1 or it will rollback that is because T2 start after T1.

I found it here in the slide 28

I hope it helped you


When installing SQL Server 2014 SSIS, what are the meaning of "authentication level"? After installing SSIS 2014, it is the time to deal with the permissions.

When dealing with the DCOM Config (control Panel->Administrative Tools-> Component Services), you want to set up the permissions for Microsoft SQL Server Integration Services 12.0 as you can see on the picture below:

You right click on it, and on the first tab, called General, you see the option Authentication Level. The default is Call, but there are other options, as you can see on the picture.

Where is the documentation for this? What does each option really mean?

Here is a link on SQL Server 2014 for you to Grant Permissions to Integration Services Service

https://msdn.microsoft.com/en-us/library/Hh213130(v=sql.120).aspx

The first step is: Run Dcomcnfg.exe. Dcomcnfg.exe provides a user interface for modifying certain settings in the registry.

The following link for DCOM settings were last reviewed in 2006, but the options look unchanged.

https://support.microsoft.com/en-us/kb/176799

This describes Default Authentication Level (Packet Level) which is the screenshot that you posted.

Other subheadings describe several other facets of the DCOM setup.


Import bacpac from SQL Azure into local server fails. We are trying to import a bacpac into a local SQL 2017 instance but it gives the following error. This suddenly started happening, previously we didn't have any problem with it so I can't think of any change that could have caused this.

Copying the SQL database via the portal into another SQL Azure server still works thou.

This is a rather big database (12GB of data, bacpac is around 900mb thou).

I'm really baffled why he is giving me some sort of SQL Server 2014 error.

My current SQL server version:

Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)   Aug 22 2017 17:04:49   Copyright (C) 2017 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Pro 10.0  (Build 16299: )

Please install the  latest versions of SSMS, then try to run the wizard again. Please download the latest version from here.


Schema-less/flexible + ACID Database? I am looking at rewriting a VB based on-premise (locally installed) application (invoicing+inventory) as a web based Clojure application for small enterprise customers. I am intending this to be offered as a SaaS application for customers in similar trade.

I was looking at database options: My choice was an RDBMS: Postgresql/ MySQL. I might scale up to 400 users in the first year, with typically a 20-40 page views/ per day per user - mostly for transactions not static views. Each view will involve fetch data and update data. ACID compliance is necessary(or so I think). So the transaction volume is not huge.

It would have been a no-brainer to pick either of these based on my preference, but for this one requirement, which I believe is typical of a SaaS app: The Schema will be changing as I add more customers/users and for each customer's changing business requirement (I will be offering some limited flexibility only to start with). As I am not a DB expert, based on what I can think of and has read, I can handle that in a number of ways:

Have a traditional RDBMS schema design in MySQl/Postgresql with a single DB hosting multiple tenants. And add enough "free-floating" columns in each table to allow for future changes as I add more customers or changes for an existing customer. This might have a downside of propagating the changes to the DB every time a small change is made to the Schema. I remember reading that in Postgresql schema updates can be done real time without locking. But not sure, how painful or how practical is it in this use case. And also, as the schema changes might also introduce new/ minor SQL changes as well.
Have an RDBMS, but design the database schema in a flexible manner: with a close to entity-attribute-value or just as a key-value store. (Workday, FriendFeed for example)
Have the entire thing in-memory as objects and store them in log files periodically.(e.g., edval, lmax)
Go for a NoSQL DB like MongoDB or Redis. But based on what I can gather, they are not suitable for this use-case and not fully ACID compliant.
Go for some NewSQL Dbs like VoltDb or JustoneDb(cloud based) which retain the SQL and ACID compliant behaviour and are "new-gen" RDBMS.
I looked at neo4j(graphdb), but not sure if that will fit this use-case

In my use case, more than scalability or distributed computing, I am looking at a better way to achieve "Flexibility in Schema + ACID + some reasonable Performance". Most of the articles I could find on the net speak of flexibility in schema as a cause leading to performance(in the case of NoSQL DBs) and scalability while leaving out the ACID/Transactions side.

Is this an "either or" case of 'Schema flexibility vs ACID' transactions or Is there a better way out?

Option 1

There are several reasons for this, which I'll explain below.  First, here's how to do it.

Use your choice of standard RDBMS platform.
Set up your schema with several user-configurable fields, and make your application facilitate the configuration on a per-tenant basis.
From the per-tenant metadata, you can create a per-tenant view of their data, which has the filters built in, and the columns named from your metadata.  Any reports provided can also inherit the metadata.  If they want to do M.I. off the data then provide them an extract of the transactional data, or maybe some additional MIS application on a different server if they will pay for that.
Don't try to provide more customisation than this (i.e. no radical changes to the schema) unless the client is prepared to pay for their own private instance and maintaing a custom build.

The reasons behind this are:

These database systems will handle the sort of volumes you describe on fairly ordinary hardware.  You don't really have the sort of transaction volume that merits a NoSQL database.  Unless you have some other architectural reason to want one, there's not much point in going bleeding-edge.
They're mature, well-understood technologies.
System management, backup/restore, replication, reporting and disaster recovery are all well sorted on RDBMS platforms.
You can get client libraries including JDBC for all major RDBMS platforms.
Views can be used for the per-user customisation and generatedfrom your application metadata.
It's substantially more efficient than XML fields or EAV structures.


Subquery for column names of tablefunc crosstab queries Is it possible to get the values for the names of the columns for the crosstab query from a subquery?

CREATE TABLE mytable(employee_name text, month date, allocation double precision);
INSERT INTO mytable VALUES('A','2017-11-30','0.5');
INSERT INTO mytable VALUES('B','2017-11-30','0.8');
INSERT INTO mytable VALUES('B','2017-12-31','0.5');

SELECT * FROM crosstab
(
  'SELECT employee_name , month , allocation FROM mytable ORDER BY 1',
  'SELECT DISTINCT month FROM mytable ORDER BY 1'
)
AS
(
    employee_name text
    # Here a subquery to get column names
    # automatically eg : 'SELECT DISTINCT month FROM mytable'
);

No, you can't make your resultset's schema dependent on the values inside the table. But you can use dynamic sql to generate a query.

SELECT FORMAT($$

  SELECT * FROM crosstab
  (
    'SELECT employee_name , month , allocation FROM mytable ORDER BY 1',
    'SELECT DISTINCT month FROM mytable ORDER BY 1'
  )
  AS
  (
    employee_name text,
    %s
  )
$$,
  'something here...'
);

Now you just want to fill out that 'something here..'

SELECT FORMAT(
    $$
        SELECT * FROM crosstab
        (
            'SELECT employee_name , month , allocation
             FROM mytable
            ORDER BY 1, 2',
            'SELECT DISTINCT month FROM mytable ORDER BY 1'
        )
        AS
        (
            employee_name text,
            %s
        )
    $$,
    string_agg(
        FORMAT('%I %s', month, 'double precision'), ', '
        ORDER BY month
    )
)
FROM (
    SELECT DISTINCT month
    FROM mytable
)
    AS t;

Then run the query that produces, and you get..

 employee_name | 2017-11-30 | 2017-12-31
---------------+------------+------------
 A             |        0.5 |
 B             |        0.8 |        0.5
(2 rows)


What port is used by SQL Server to write backups to a remote server? I have a SQL Server 2008 R2 Standard instance in a "DMZ", so that it can be client-facing. I am trying to have it write its backups to a central location that is within the network. The engineers suggest that they can open a port for me. Hence the question: What port is used by SQL Server to write backups to a remote server?

Remote file access occurs over SMB. Remote backups are just another form of remote file access. See File and Sharing Protocol Stack.

If you use SMB over TCP directly (ie. disable NetBIOS) then the port used is 445, both UDP and TCP. If you use NetBIOS then it requires 137 both UDP and TCP, 138 UDP and 139 TCP. See Directly Hosting SMB over TCP/IP:

  NetBIOS over TCP traditionally uses the following ports:

nbname            137/UDP
nbname            137/TCP
nbdatagram        138/UDP
nbsession         139/TCP

  Direct hosted "NetBIOS-less" SMB traffic uses port 445 (TCP and UDP).


mongodb - enableSharding for database takes five to six minutes to complete I'm seeing some odd behavior when invoking the enableSharding command for a database. First a bit of background:

I'm using MongoDB v3.2.7 and the Java driver. Currently I have only one replica set (three nodes) which represents one shard (just one shard for - plans to scale horizontally in the future). This is front ended by three mongos nodes.

The application leveraging this MongoDB "cluster" creates thousands (greater than 10K) of databases. Inside of each database is roughly 15 collections. The databases and collections are created and initialized at runtime by the application as needed. Part of the initialization is to invoke "enableSharding" on the database and then create the collections and finally shard the collections. Normal behavior for the application also includes dropping and creating this databases somewhat frequently.

This implementation is not yet in production but still in development phase. We are trying to mimic the MongoDB architecture that we plan on going to production with. However, it seems about twice a month the primary member of the shard/replica set will begin taking five to six minutes to complete the enableSharding command. The command does complete, but associated application logic "times out". All other operations on the primary node proceed as normal. There seems to be no impact to queries or writes. It also continues to act as the "primary". Again the enableSharding command completes - it just takes five to six minutes.

One other notable item is that we use "listDatbases" as part of our monitoring. I do notice that the call to "listDatabases" also begins to slow down when the enableSharding command for a database begins to take five to six minutes to complete. I guess a few questions would be:

Has anyone else observed this issue? Or maybe something related?
Should our application be initializing databases and collections at runtime so frequently?
Is the greater than 10K databases * 15 collections going to come back and bite us?

Mainly I'm just wondering why enableSharding for a new database would slow down so much. The only way we can clear it up is to restart the replica set member.

Thanks,
Terence

The enableSharding command is basically a meta data operation that changes one document in the databases collection, and that happens on the config servers (at least it was the last time I checked it in detail in 2.6). If this is slow, it's because you have contention there (at the config servers), likely due to activity from the other collections being sharded and balanced.

Each initial collection sharding, chunk migration, balancer lock will hit various tables on the config servers, and potentially contend with your enable sharding command (the meta data operations are special cases too, with 2 phase commit etc.). You can look at scaling up the config databases (will only work if they are struggling with resources based on your current load, not if you are hitting locking contention), or if this is only a once-off and not indicative of ongoing load, then checking for completion of other operations, balanced collections etc. will make your initial configuration run slower but less prone to timeout and error. The other option would be to alter the timeout in the code to wait longer for the command to complete, of course.


SQL Query- field calculation based on date I'm writing a query for sales targets per week, however I'm getting a little stuck on this.
Targets are per month, per year, so for example, Brand A has a monthly target (changes each month)

The snippet from the report is as follows:

SELECT BrandName
      ,SUM((TargetMonThur *4) +TargetFriday)
WHERE [MONTH] IN (
        DATEPART(MM,@DAY1),
        DATEPART(MM,@DAY2),
        DATEPART(MM,@DAY3),
        DATEPART(MM,@DAY4),
        DATEPART(MM,@DAY5)
    )
    AND [YEAR] IN (
        DATEPART(YY,@DAY1),
        DATEPART(YY,@DAY2),
        DATEPART(YY,@DAY3),
        DATEPART(YY,@DAY4),
        DATEPART(YY,@DAY5)
    )

However the issue appears when the start of the week/ end of the week is a different month to the other days. at this point, it calculates both month's targets * 4.

I think I'm being a complete noob on this, but any help would be great.

Thanks in advance

One solution to this problem would be to create a static calendar table. In the scope of your problem, this table would be important because there are holidays that must be considered when calculating the week's goals, as it is not fair to compare the targets an entire week with a partial week (holidays).

The downside of these static tables is proper management and organization of the calendar for the New Year's and holidays change. It is very important to put reminders in this routine to minimize the chance of a possible future problem.

Consider this risk for the proposed solution.


SQL Server 2005/2008 logging connections How can I audit SQL connections to the database from

Windows credential
Sql Users

Is there some built-in feature where I can determine number of:

all connection
active connection
unclosed connection
current status
historical prevue

I hope that I'm going right way. I have application that takes all RAM to the SQL Engine and transaction logs raising to the unbelievable size on HDD durring one-two days.

No, you're not going about this in the right way.

  I have application that be able to take all RAM to the SQL Engine

SQL Server will use as much as it needs unless you limit it. Read Brent Ozar's A Sysadmin’s Guide to Microsoft SQL Server Memory.

  transaction logs raising to the unbelievable size on HDD during
  one-two days

9 times out of 10 this is due to the database being in FULL recovery and no log backups being taken. See Paul Randall's article Running Out of Transaction Log Space.

If you want to monitor the items you listed:

Using SQL Server Profiler
sp_whoisactive
SQL Server Perfmon Best Practices


EAV for status of tables I am faced with this situation,  an application that has multiple states for different entities. Entity A can have Opened, Closed and Waiting. Entity B has its own set of states, I was considering using Attribute and Value table to fill this gap without tightly coupling static strings in the app which would not scale as well as using the database. I am now sure how to be structure this in the database however and need some help, currently I have
Is this a good approach?
Update
I was trying to have the database save states that are possible for other Entities. For example, Orders can have states such as Completed or Processing etc... instead of having enums in my code I was thinking to have those saved in the database and use them from one source which I think would be more scalable in the event we get more states in the future. I was looking for an efficient way of storing states that could reference different entity types.

In my experience, the question around putting enums and their descriptions into code or the database, really depends on the complexity and target audience of the app you're writing.
To be more precise, I tend to store descriptions of enum values in the database when there is a chance that I'll be localizing the database and need to share that localization with reporting solutions or 3rd party systems that won't accept localization through some other more traditional method.
Having said that, I wouldn't suggest extrapolating enums and their descriptions into an entity-attribute-value system.  Typically, I might create an enum table for each data or fact table in my database.  So, if I have a table for storing Invoices, and that table has a column for InvoiceStatus, I might have a table called InvoicesStatusDescriptions with a couple of rows that describe the various invoice statii.  Then, if I want to quickly localize the app, I can simply change the description value for each enum value.
An example may help:
CREATE TABLE dbo.Invoices
(
    InvoiceID int NOT NULL
        IDENTITY(1,1)
        CONSTRAINT Invoices_pk
        PRIMARY KEY
        CLUSTERED
    , InvoiceStatusID tinyint NOT NULL
        CONSTRAINT Invoices_status_fk
        FOREIGN KEY
        REFERENCES dbo.InvoiceStatusDescription(InvoiceStatusID)
);

The InvoiceStatusID column references the following table:
CREATE TABLE dbo.InvoiceStatusDescription
(
    InvoiceStatusID tinyint NOT NULL
        CONSTRAINT InvoiceStatusDescription_fk
        PRIMARY KEY
        CLUSTERED
    , InvoiceStatusDescription nvarchar(30) NOT NULL

);

Some sample data:
INSERT INTO dbo.InvoiceStatusDescription (InvoiceStatusID, InvoiceStatusDescription)
VALUES (1, N'Started')
    , (2, N'Payment Requested')
    , (3, N'Payment Received')
    , (4, N'Closed');

INSERT INTO dbo.Invoices (InvoiceStatusID)
VALUES (1)
    , (3);

Then you can provide a query like this to get localized versions of the status of each invoice:
SELECT i.InvoiceID
    , id.InvoiceStatusDescription
FROM dbo.Invoices i
    INNER JOIN dbo.InvoiceStatusDescription id ON i.InvoiceStatusID = id.InvoiceStatusID

With the sample data, output looks like this:

InvoiceID
InvoiceStatusDescription

1
Started

2
Payment Received

You can even add a column to the InvoiceStatusDescription table indicating the locale, but in my experience it would be easier to simply replace the description values in each enum table when the user wants to switch to a different locale.
Having said all that, your code is still going to need to understand the meaning of the enum values, so you'll still likely create discrete enums in your code.  Keeping the two systems in sync can be a challenging problem space in large systems.


Which costing factors go into the optimizer choosing different types of spools? Spoolum

In SQL Server there are several kinds of spools. The two that I'm interested are Table Spools and Index spools, outside of modification queries.

Read only queries, particularly on the inner side of a Nested Loops join, may use either a Table or Index spool to potentially reduce I/O and improve query performance. These spools can be Eager or Lazy. Just like you and me.

My questions are:

Which factors go into the choice of Table vs. Index Spool
Which factors go into the choice between Eager and Lazy Spools

This is a little broad but I think I understand the True Question and will answer accordingly. Just going to talk about table vs index spool though. I don't think it's quite correct to view there as being a choice between table and index spools. As you know, it's possible in a single subtree to get an index spool, a table spool, or both an index spool and a table spool. I believe it's generally correct to say that you get an index spool under the following conditions:

The query optimizer has a reason to transform a join into an apply
The query optimizer actually performs the transform to the apply
The query optimizer uses the rule to add an index spool (at minimum the index spool must be safe to use)
The plan with the index spool is selected

You can see most of these with simple demos. Start by creating a pair of heaps:

DROP TABLE IF EXISTS dbo.X_10000_VARCHAR_901;
CREATE TABLE dbo.X_10000_VARCHAR_901 (ID VARCHAR(901) NOT NULL);

INSERT INTO dbo.X_10000_VARCHAR_901 WITH (TABLOCK)
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

DROP TABLE IF EXISTS dbo.X_10000_VARCHAR_800;
CREATE TABLE dbo.X_10000_VARCHAR_800 (ID VARCHAR(800) NOT NULL);

INSERT INTO dbo.X_10000_VARCHAR_800 WITH (TABLOCK)
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

For the first query, there's nothing to seek on:

SELECT *
FROM dbo.X_10000_VARCHAR_901 a
CROSS JOIN dbo.X_10000_VARCHAR_901 b
OPTION (MAXDOP 1);

So there's no reason for the optimizer to transform the join into an apply. You end up with a table spool due to costing reasons. So this query fails the first test.

For the next query, it's fair to expect that the optimizer has a reason to consider an apply:

SELECT *
FROM dbo.X_10000_VARCHAR_901 a
INNER JOIN dbo.X_10000_VARCHAR_901 b ON a.ID = b.ID
OPTION (LOOP JOIN, MAXDOP 1);

But it's not meant to be:

This query fails the second test. A complete explanation is here. Quoting the most relevant part:

  The optimizer does not consider building an index on the fly to enable
  an apply; rather the sequence of events is usually the reverse:
  transform to apply because a good index exists.

I can rewrite the query to encourage the optimizer consider an apply:

SELECT *
FROM dbo.X_10000_VARCHAR_901 a
INNER JOIN dbo.X_10000_VARCHAR_901 b ON a.ID >= b.ID AND a.ID <= b.ID
OPTION (MAXDOP 1);

But there's still no index spool:

This query fails the third test. In SQL Server 2014 there was an index key length limit of 900 bytes. This was extended in SQL Server 2016 but only for nonclustered indexes. The index for a spool is a clustered index so the limit remains at 900 bytes. In any case, the index spool rule can't be applied because it could lead to an error during query execution.

Reducing the data type length to 800 finally provides a plan with an index spool:

The index spool plan, not surprisingly, is costed significantly cheaper than a plan with no spool: 89.7603 units vs 598.832 units. You can see the difference with the undocumented QUERYRULEOFF BuildSpool query hint:

This isn't a complete answer, but hopefully it's some of what you were looking for.


What happens if transaction rollback fails? So failure while committing leads to rollback. But what if rollback also fails due to some reasons and the database has changed due to partial commitment. How is the database restored to its original state?

The database in question is postgresql.

I think your question hits the core of the database reliability properties; the ACID properties. They are there in order to guarantee the database's reliability. The transaction log plays a vital role here. You probably know the ACID properties, so Iam not diving into that.
If a rollback fails, then you would have a serious problem. The reliability of the database cannot be guaranteed. In other words; you probably have some sort of corruption in your transaction log and will end up with an inconsistent database. I am not sure how it works with PostgreSQL, but for MS SQL Server it will set the DB offline and restarts it, which will trigger a recovery action and it will try to rollback the transaction again. If that doesn't work you will probably have to restore the database from an earlier backup (log, differential or full).


sql server partitioned view execution plan questions I have created a partitioned view for  3 tables tb_sales2010,tb_sales2011,tb_sales2012 using check constraint
(respectivly for each table)
datepart(year,[Date])=(2010)
datepart(year,[Date])=(2011)
datepart(year,[Date])=(2012)

schema for table 2010 (same for all other tables)

CREATE TABLE [dbo].[tb_Sales2010](
    [Date] [smalldatetime] NOT NULL,
    [ID] [int] NOT NULL

 CONSTRAINT [PK_tb_Sales2010] PRIMARY KEY NONCLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tb_Sales2010]  WITH CHECK ADD  CONSTRAINT [CK_Date_2010] CHECK  ((datepart(year,[Date])=(2010)))
GO

My query is

SELECT TOP 1 *
FROM partitionedTb_sales
WHERE DATEPART(YY, DATE) = 2011

My question is, the execution plan indicates that it is scanning all 3 tables. Instead of looking at the check constraint and saying table b has the information it needs. Why is that. i feel the execution plan should only show 1 table being scanned.

Statistics turned on results

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'tb_Sales2012'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'tb_Sales2011'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'tb_Sales2010'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

View Definition
CREATE VIEW PartitionedTb_Sales
AS
SELECT * FROM tb_sales2010

UNION ALL

SELECT * FROM tb_sales2011

UNION ALL

SELECT * FROM tb_sales2012

The DATEPART() function still has to be performed on every row before any filtering can be done/calculated.

Your view would be much more efficient if you changed it like this:

CREATE VIEW PartitionedTb_Sales
AS
SELECT *, 2010 AS SalesYear FROM tb_sales2010
UNION ALL
SELECT *, 2011 AS SalesYear FROM tb_sales2011
UNION ALL
SELECT *, 2012 AS SalesYear FROM tb_sales2012

Then, instead of using DATEPART() to calculate the year for each row, it will already be defined.

SELECT TOP 1 *
FROM partitionedTb_sales
WHERE SalesYear = 2011


Indexing or changing one-to-many relationship I have these two Microsoft SQL Server 2005 tables:

ObjectLicenses (ObjectLicenseID [PK]; ObjectID; LicenseType; LicenseNumber)
1|1|A|000001
2|1|A|000002
3|1|B|000003
4|2|C|000004

Objects (ObjectID [PK])
1
2
3

Table ObjectLicenses has a primary key on column ObjectLicenseID. Table Objects has a primary key on column ObjectID.

I want to return a list of all LicenseType's, by Object ID. However, this takes a long time, possibly due to lack of index(es)...currently, I use a "FOR XML PATH" query to do this:

SELECT      O.ObjectID

            ,STUFF( (SELECT      '; ' +
                                 CONVERT(VARCHAR(12), ROW_NUMBER() OVER
                                 (ORDER BY OL.ObjectLicenseID)) +
                                 ') ' + ISNULL(OL.LicenseType, '')

                       FROM      ObjectLicenses OL

                      WHERE      O.ObjectID = OL.ObjectID
                      FOR XML PATH(''), TYPE
                    ).value('.', 'VARCHAR(MAX)')
                    , 1, 2, ''
                  ) AS 'LicenseType'

  FROM      Objects O

How might I index or otherwise change this model/query to speed it up?

The relationship seems fine; the problem seems to be in the presentation. If you want this query to work faster, just return the join to the client, and perform the looping / concatenating on the client.

This sounds like a tongue-in-cheek answer, but I'm 100% serious. Why does SQL Server have to do the work of transposing your rows to a concatenated column? Your client language is certainly much better equipped to do that (loop through the joined results, ordered by ObjectID, and start a new concat whenever a new ObjectID is encountered). It may be less efficient at the very high end (say, thousands of license types per ObjectID) but I suspect you're not at an edge case.


semaphore in SQL Server I wrote a stored procedure in sql to take a number from table.
it has lots on records which has only column.
whenever a call it , is should take the first record and return it after that delete the record.
it works properly when we have just one thread,the problem appears when I use multithreading in my program and the procedure return the duplicate number.
the procedure is like below:

ALTER PROCEDURE [dbo].[GetSubscribeNo] @Sbs_Type    INT,
                                       @subscribeNo INT OUTPUT
AS
  BEGIN
      SET @subscribeNo = -1;

      BEGIN TRAN T1;

      UPDATE UNIT_SBS_NOS
      SET    Free_SBS_NO = Free_SBS_NO
      WHERE  Sbs_Trf_Type = @Sbs_Type;

      SELECT @subscribeNo = (SELECT TOP(1) FREE_SBS_NO
                             FROM   UNIT_SBS_NOS WITH(nolock)
                             WHERE  Sbs_Trf_Type = @Sbs_Type);

      DELETE FROM UNIT_SBS_NOS
      WHERE  FREE_SBS_NO = @subscribeNo;

      INSERT INTO All_Subscribe
                  (Subscribe_No)
      VALUES      (@subscribeNo);

      COMMIT TRAN T1;
  END

Generally your current code shouldn't allow two concurrent transactions to process the same @Sbs_Type simultaneously.

Two concurrent threads can't both acquire U locks on the rows matching WHERE Sbs_Trf_Type = @Sbs_Type.

If two threads simultaneously call GetSubscribeNo with the same @Sbs_Type when there are no matching rows whilst meanwhile a third transaction inserts a row for that @Sbs_Type I can see how this would fail however.

One or both of the GetSubscribeNo calls would need to get past the UPDATE statement before the insert and thus fail to lock anything that would block the other one.

A better way of doing this anyway would be with the OUTPUT clause.

ALTER PROCEDURE [dbo].[GetSubscribeNo] @Sbs_Type    INT,
                                       @subscribeNo INT OUTPUT
AS
  BEGIN
      DECLARE @Out TABLE (Subscribe_No INT);

      SET XACT_ABORT ON;
      BEGIN TRAN T1;

      WITH T
           AS ((SELECT TOP(1) *
                FROM   UNIT_SBS_NOS WITH (ROWLOCK, READPAST)
                WHERE  Sbs_Trf_Type = @Sbs_Type))
      DELETE FROM T
      OUTPUT DELETED.FREE_SBS_NO INTO @Out;

      /*Default to -1 if no rows*/
      SELECT @subscribeNo = ISNULL(MAX(Subscribe_No), -1)
      FROM   @Out

      INSERT INTO All_Subscribe
                  (Subscribe_No)
      VALUES      (@subscribeNo);

      COMMIT TRAN T1;
  END


Using EXCEPT in a recursive common table expression Why does the following query return infinite rows?  I would have expected the EXCEPT clause to terminate the recursion..

with cte as (
    select *
    from (
        values(1),(2),(3),(4),(5)
    ) v (a)
)
,r as (
    select a
    from cte
    where a in (1,2,3)
    union all
    select a
    from (
        select a
        from cte
        except
        select a
        from r
    ) x
)
select a
from r

I came across this while trying to answer a question on Stack Overflow.

The BOL description of recursive CTEs describes the semantics of recursive execution as being as follows:

Split the CTE expression into anchor and recursive members.
Run the anchor member(s) creating the first invocation or base result set (T0).
Run the recursive member(s) with Ti as an input and Ti+1 as an output.
Repeat step 3 until an empty set is returned.
Return the result set. This is a UNION ALL of T0 to Tn.

Note the above is a logical description. The physical order of operations can be somewhat different as illustrated here

Applying this to your CTE I would expect an infinite loop with the following pattern

+-----------+---------+---+---+---+
| Invocation| Results             |
+-----------+---------+---+---+---+
|         1 |       1 | 2 | 3 |   |
|         2 |       4 | 5 |   |   |
|         3 |       1 | 2 | 3 |   |
|         4 |       4 | 5 |   |   |
|         5 |       1 | 2 | 3 |   |
+-----------+---------+---+---+---+

Because

select a
from cte
where a in (1,2,3)

is the Anchor expression. This clearly returns 1,2,3 as T0

Thereafter the recursive expression runs

select a
from cte
except
select a
from r

With 1,2,3 as input that will yield an output of 4,5 as T1 then plugging that back in for the next round of recursion will return 1,2,3 and so on indefinitely.

This is not what actually happens however. These are the results of the first 5 invocations

+-----------+---------+---+---+---+
| Invocation| Results             |
+-----------+---------+---+---+---+
|         1 |       1 | 2 | 3 |   |
|         2 |       1 | 2 | 4 | 5 |
|         3 |       1 | 2 | 3 | 4 |
|         4 |       1 | 2 | 3 | 5 |
|         5 |       1 | 2 | 3 | 4 |
+-----------+---------+---+---+---+

From using OPTION (MAXRECURSION 1) and adjusting upwards in increments of 1 it can be seen that it enters a cycle where each successive level will continually toggle between outputting 1,2,3,4 and  1,2,3,5.

As discussed by @Quassnoi in this blog post. The pattern of observed results is as if each invocation is doing (1),(2),(3),(4),(5) EXCEPT (X) where X is the last row from the previous invocation.

Edit: After reading SQL Kiwi's excellent answer it is clear both why this occurs and that this is not the whole story in that there is still loads of stuff left on the stack that never can get processed.

  Anchor Emits 1,2,3 to client Stack Contents 3,2,1

  3 popped off stack,  Stack Contents 2,1

  The LASJ returns 1,2,4,5,  Stack Contents 5,4,2,1,2,1

  5 popped off stack,  Stack Contents 4,2,1,2,1

  The LASJ returns 1,2,3,4  Stack Contents 4,3,2,1,5,4,2,1,2,1

  4 popped off stack,  Stack Contents 3,2,1,5,4,2,1,2,1

  The LASJ returns 1,2,3,5  Stack Contents 5,3,2,1,3,2,1,5,4,2,1,2,1

  5 popped off stack,  Stack Contents 3,2,1,3,2,1,5,4,2,1,2,1

  The LASJ returns 1,2,3,4  Stack Contents
  4,3,2,1,3,2,1,3,2,1,5,4,2,1,2,1

If you try and replace the recursive member with the logically equivalent (in the absence of duplicates / NULLs) expression

select a
from (
    select a
    from cte
    where a not in
    (select a
    from r)
) x

This is not allowed and raises the error "Recursive references are not allowed in subqueries." so perhaps it is an oversight that EXCEPT is even allowed in this case.

Addition:
Microsoft have now responded to my Connect Feedback as below

  Jack's guess is correct: this should have been a syntax error;
  recursive references should indeed not be allowed in EXCEPT clauses.
  We plan to address this bug in an upcoming service release. In the
  meantime, I would suggest to avoid recursive references in EXCEPT
  clauses.

  In restricting recursion over EXCEPT we follow the ANSI SQL standard,
  which has included this restriction ever since recursion was
  introduced (in 1999 I believe). There is no widespread agreement over
  what the semantics should be for recursion over EXCEPT (also called
  "unstratified negation") in declarative languages such as SQL. In
  addition, it is notoriously hard (if not impossible) to implement such
  semantics efficiently (for reasonably sized databases) in an RDBMS
  system.

And looks like the eventual implementation was made in 2014 for databases with compatibility level of 120 or higher.

  Recursive references in an EXCEPT clause generates an error in compliance with the ANSI SQL standard.


How to check if a PostgreSQL table is temporary? How can I write a query to check if a PostgreSQL (10.x) table is a temporary table?

I looked at the pg_class system table, but couldn't find any column about whether the relation is temporary.

SELECT * FROM pg_class WHERE False;
 relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relrowsecurity | relforcerowsecurity | relispopulated | relreplident | relispartition | relfrozenxid | relminmxid | relacl | reloptions | relpartbound
---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+------------+------------+-------------+----------------+----------------+----------------+---------------------+----------------+--------------+----------------+--------------+------------+--------+------------+--------------
(0 rows)

Quote from the manual

  relpersistence:  p = permanent table, u = unlogged table, t = temporary table


Which one is better way to insert data? I have two cases

case

create  proc    createEmployee
    @firstName varchar(255),
    @lastName varchar(255) = null
as
insert into Employee_Tbl values (@firstName, @lastName)

case

create  proc    createEmployee
    @firstName varchar(255),
    @lastName varchar(255) = null
as
insert into Employee_Tbl(FirstName) values (@firstName)

declare @id as int
set @id = SCOPE_IDENTITY()
if @lastName is not null
begin
   update Employee_Tbl
       set LastName  = @lastName
    where EmpId = @id
end

Of the two code blocks above, which one performs better?

First of all , The stored procedures you have are totally different. So the performance are not really compare-able.

Thought the final output you receive is the same , in the first SP you are doing a simple insert where as in second SP you are inserting and then doing an update.
So it is pretty obvious that the second procedure is doing more work


MySQL Cluster Installation I am very new to mysql-cluster.I downloaded the binaries and was trying to configure it on my local machine.

Can anybody suggest how to install it on single machine with the 1 management node,2 data nodes,1 sql node.

Also what are the importance of each type of node?

I need the basics steps to install the mysql-cluster.As I have installed it by seeing some website and stuck in between i am getting an error as

mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------+
| Level | Code | Message                                                                         |
+-------+------+---------------------------------------------------------------------------------+
| Error | 1296 | Got error 4009 'Cluster Failure' from NDB. Could not acquire global schema lock |
| Error | 1296 | Got error 4009 'Cluster Failure' from NDB                                       |
+-------+------+---------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

I also have gone through the manual but no results.

Anybody can help me out in this?

For single machine setups, you can also check out the Cluster Sandbox:
http://www.severalnines.com/resources/mysql-cluster-sandbox-pre-defined-packages
Good luck!
Vinay
http://www.severalnines.com/
Edit By RolandoMySQLDBA 2012-05-15 13:34 EDT
Check out the Training Slides : http://severalnines.com/mysql-cluster-training
IMHO it would be a fine starting point for beginners to install & learn MySQL Cluster.


Need help with data model Schema does not list all fields, but only the relevant ones

User adds credits/funds to his account using paypal or Internet Banking (which has to be verified manually, and approved, so the 'status' field in the receipts' table)
After a receipt has been approved, the amount will be added to the account_balance of the user
When a user makes a purchase from the site, the amount will be deducted from his account_balance
Also, a user can request a withdrawal of his funds

Table: users

id | name | email | password | account_balance | created_at

Table: receipts

id | user_id | payment_method | transaction_datails | amount | status | created_at

Table: purchases

id | user_id | product_id | amount | created_at

Table: withdrawals

id | user_id | amount | created_at

Question:

Is it better to have a transactions table that stores all receipts, withdrawals, purchases in one table?

No.

You should combine receipts and purchases, because they are 1 to 1 correlated - there cant be more than one receipt for one paypal purchase, and vice versa. Same goes for Bank Purchases. Therefore ALL of these basically constitute "Purchases". What you call 'receipt' just seems to be the act of CONFIRMING the purchase, and nothing else.

Withdrawals should be in a single table, and should have its own details as necessary ( paypal out-payment, bank out-payment etc, if applicable in future). Because withdrawals can be more than one - a guy who has $100 in his account can withdraw 4 $25 withdrawals in differing times. Which will have to be paid out in its own payment method and confirmed in its own fashion. Therefore withdrawals is a different identity.

And all the searches involving these entities will happen in that fashion too - you cant search a withdrawal IN RELATION TO two different purchases in Purchases. Or vice verse. They are independent entities which are only linked by user_id, and very loosely by account balance - the latter only as a limiting factor for the other.

Userts table is solid as it is and needs no change.


Oracle startup error (could not open parameter file) I installed the Oracle 11g server on Ubuntu 12.10. But I can't start the server. When I start up the server, the following error occurs:

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/xe/dbs/initXE.ora'

How can I solve this issue? I think the connect failed because target host or object does not exist. How can a target host be specified?

Running:

$ ls -latr /u01/app/oracle/product/11.2.0/xe/dbs/initXE.ora

returns:

-rw-r--r-- 1 oracle dba 2637 Mar 13 17:07 /u01/app/oracle/product/11.2.0/xe/dbs/initxe.ora

Running echo $ORACLE_SID displays nothing - an empty line.

I see two things wrong here.  Oracle tries to open a parameter file in the $ORACLE_HOME/dbs directory with the format "spfile" + $ORACLE_SID + ".ora".  If it cannot find it, it then tries to open "init" + $ORACLE_SID + ".ora".  This is where the problem(s) are.

By virtue of the fact that Oracle is trying to open a parameter file named "initXE.ora", your SID must be (or was at one point) equal to "XE".

1) This is the easy part, so try this first.  Since (in your comments) doing an echo $ORACLE_SID yields nothing, try setting it.

export ORACLE_SID=XE

Then try starting Oracle again.

2) In your comments above, you show the file is named "initxe.ora".  Is that really the name of the file?  Or did your paste into a comment somehow lowercase the output of the ls command?

The reason I point this out, is that Ubuntu (Linux) has a case-sensitive filesystem.  So initxe.ora and initXE.ora are actually two different files.  So if your comment is correct and the file is lower-cased, you'll want to set your ORACLE_SID to "xe" instead:

export ORACLE_SID=xe

Try that, and see if it works.


Definition for "logical difference"? I'm currently reading "SQL and Relational Theory" by C.J. Date. And although I'm quite far in the book, I've got a few basic questions. I want to know what the term "logical difference" means, The book does try to explain the term with examples, but does not really explain what it means (or maybe I'm understanding it wrong??)

Here is a small part from the book:

I've said that there's a logical difference between a relation and a picture of a relation. The concept of logical difference derives from a dictum of Wittgenstein's:

All logical differences are big differences.

I know what a logical difference means intuitively, I know what the difference is between a relation and a picture of a relation. What I want is a formal-like definition of the concept of a "logical difference" so I have a good idea of what it means.

See Date's book:

Date on Database: Writings 2000-2006

Chapter 4: On the Notion of Logical Difference

Salient quote:

  you'll be aware that I often appeal in my writings to the notion of
  logical difference. That notion is one I find extraordinarily useful
  in my own work; it's a great aid to clear and precise thinking… The
  intent of what follows [in chapter 4 of this book], then, is to offer
  a brief introduction to the concept of logical difference.

…bearing in mind this is Chris Date, for whom "brief introduction" actually amounts to 15 pages of closely-typed prose including footnotes and endnotes!

Follow the above link to the Google Books copy of the book, which should open in your browser, where the chapter seem to be available in full. After you've read the chapter, I'm sure you shall have a good understanding of the concept of logical difference.