How to grant super privilege to the user? I have created a user and given privileges to the user1.

`grant all privileges on db1.* to user1@'%' with grant option;

Am using mysql workbench to import dumps to my database. While importing dumps to database db1,error occurs stating that

ERROR 1227 (42000) at line 49: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

In that dumps all tables are imported successfully but the error occurs while importing routines to the database. Is there anything wrong with the privilege, I have given to the user1. Please advice.

In a politically correct sense, what you just asked for is impossible. Why ?
The SUPER privilege is a global privilege, not a database level privilege.
When you created the user with
grant all privileges on db1.* to user1@'%' with grant option;

you populated the table mysql.user with user='user1' and host='%'. All other columns (global  privileges) were defaulted to 'N'. One of those columns is Super_priv. Here is the table:
mysql> desc mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N       |       |
| File_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N       |       |
| References_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher             | blob                              | NO   |     | NULL    |       |
| x509_issuer            | blob                              | NO   |     | NULL    |       |
| x509_subject           | blob                              | NO   |     | NULL    |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0       |       |
| plugin                 | char(64)                          | YES  |     |         |       |
| authentication_string  | text                              | YES  |     | NULL    |       |
| password_expired       | enum('N','Y')                     | NO   |     | N       |       |
+------------------------+-----------------------------------+------+-----+---------+-------+
43 rows in set (0.00 sec)

mysql>

Super_priv appears right after Show_db_priv.
The database level privileges got populated into mysql.db. Here is it:
mysql> desc mysql.db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(16)      | NO   | PRI |         |       |
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
| Update_priv           | enum('N','Y') | NO   |     | N       |       |
| Delete_priv           | enum('N','Y') | NO   |     | N       |       |
| Create_priv           | enum('N','Y') | NO   |     | N       |       |
| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
| References_priv       | enum('N','Y') | NO   |     | N       |       |
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)

mysql>

Notice that Super_priv does not exist in mysql.db.
To visualize this in pure SQL terms, login as user1 and run SHOW GRANTS; The output will have two lines:

GRANT USAGE ON *.* TO user1@'%' ...
GRANT ALL PRIVILEGES ON db1.* TO user1@'%' ...

There is a hack you can try but I normally would not recommend it.
STEP 01) Login to mysql as root@localhost (should have all privs)
STEP 02) Run this query
UPDATE mysql.user SET Super_Priv='Y' WHERE user='user1' AND host='%';

STEP 03) Run this query
FLUSH PRIVILEGES;

That theoretically should work. Then, user1 may work (I make no guarantees).
UPDATE 2014-12-19 15:24 EST
Metafaniel just asked

Great explanation, thanks. However if you do not reccomend that way to solve the problem, then what other way is the best one to gran a user this Super_priv?? Thanks! –  Metafaniel

Since a user with only DB access cannot have SUPER, the only thing one can do is change the DEFINER manually in the dump. The basic idea would be to mysqldump the routines alone to a text file. Then, edit the definer to user1@'%'. Then, you should be able to reload.

Oct 02, 2011 : Can mysqldump dump triggers and procedures?
Jul 25, 2011 : DROP PROCEDURE IF EXISTS not included in mysqldump
Jul 25, 2011 : Dump only the Stored Procedures in MySQL

Same Thing for Views

Mar 23, 2012 : MySQL Views Authorization
Jul 26, 2011 : Modify DEFINER on Many Views


Are there better alternatives rathet than joining a table twice for having one record for a specific column in the output? I have two tables with below structures:
create table TABLE_LC
( lc_num   NUMBER,
  brn_code NUMBER,
  brn_role NUMBER)

create table BRN
( brn_code NUMBER,
  brn_name VARCHAR2(50))

Some example data for each table is this:
"TABLE_LC":

lc_num    |  brn_code  |  brn_role
------------------------------------
  1       |     1      |  1010
  1       |     2      |  2020

"BRN":

brn_code  |  brn_name
---------------------
  1       |   BAHAR
  2       |    KAJ

I need To have one record for each lc_num in TABLE_LC and the desired output based on the given tables and data is this:
lc_num    |  Advising_Bank  |  Issuing_Bank
-------------------------------------------
  1       |     BAHAR       |  KAJ

What I've written is below query which gives me the correct output,considering the fact that we know brn_role=1010 represents Advising Bank and brn_role=2020 represents Issuing Bank
select t1.lc_num,
       t2.brn_name "Advising Bank",
       t4.brn_name "Issuing bank"
  from TABLE_LC t1
 inner join BRN t2
    on t1.brn_code = t2.brn_code
   and t1.brn_role = 1010
 inner join TABLE_LC t3
    on t3.lc_num = t1.lc_num
 inner join BRN t4
    on t4.brn_code = t3.brn_code
   and t3.brn_role = 2020

As you can see, I have joined the base table twice, I want to know if there are better ways of  getting the desired output ?
Thanks in advance

Better?  It depends.
If the primary key of TABLE_LC is (lc_num, brn_role) then you could do something like this:
SELECT
  lc_Num
 ,MAX
    (
      CASE
        WHEN brn_role = 1010 THEN brn_name
        ELSE NULL
      END
    ) AS Advising_Bank
 ,MAX
    (
      CASE
        WHEN brn_role = 2020 THEN brn_name
        ELSE NULL
      END
    ) AS Issuing_Bank
FROM
  (
    SELECT
      LC.lc_num
     ,LC.brn_role
     ,LC.brn_code
     ,BRN.brn_name
    FROM
      TABLE_LC LC
    INNER JOIN
      BRN BRN
        ON BRN.brn_code = LC.brn_code
  ) x
GROUP BY
  lc_num

Which, you'll note:

Is about the same amount of code
Is harder to understand
Even though it only performs one join, it still might not produce the best query plan, depending on whether or not TABLE_LC is index-organized.

You could also use PIVOT, but again, probably not that much time savings and you're left with the problem of dealing with goofy column names.
Joins on primary keys/appropriate indexes are generally not that expensive, so avoiding them only makes sense in certain situations.


Can we have dynamic SQL jobs in SQL Server Availability groups We need to push all sql data changes to our local caching infrastructure in real time. We are using SQL Server 2012 Availability Groups. In the process of avoiding triggers for population SQL Service broker queues, I am researching the use of SQL jobs as explained in this article: Async SQL execution

By calling this sql procedure from insert/update/delete stored procedures should avoid the usage of triggers. Creating/deleting sql jobs dynamically from stored procedure is valid/allowed in sql availability groups?

The SQL Agent Job will allow the data to be manipulated prior to sending the message to the Service Broker Queue.

SQL Agent jobs are not contained within the Availability Group. When using SQL Agent jobs to manipulate a database within an Availability Group, there are all sorts of special considerations that need to be taken, particularly related to failover.

What happens if the job is running during failover? The job will likely fail, as the replica it is running on is now a non-writable secondary, and probably needs to be re-run on the now-primary replica. What if a job has been created, but not started when there is a failover?

What happens if a row is inserted, then immediately updated? The two SQL Agent jobs would have a dependency in the order in which they run. This is an issue with using SQL Agent jobs in general, regardless of whether an AG is part of the equation, but would be exacerbated if the initial INSERT were "lost" in a failover.

Additionally in your specific scenario, you will be generating one SQL Agent Job per insert/update/delete. SQL Agent will only start one job per second. This means you will have a self-imposed limitation of one insert/update/delete operation per second. This will be a serious scalability issue, in addition to the headaches of job management.

Instead, have your CRUD procedures insert directly into the Service Broker Queue. The process to enqueue a message for Service Broker will likely perform much better than creating a SQL Agent job (but I've not tested it to give specific numbers). The Service Broker queue is contained within the database, so you do not have to worry about messages being "lost" in a failover.

If you need to do data manipulation, that can be done from within your Service Broker queue before additional processing/sending is done. Because that manipulation is being done within the Service Broker, it would all be asynchronous to your CRUD operations.

Using SQL Agent jobs for your scenario should not be necessary at all.


How to run mysqldump from windows command prompt I'm using non-install version of mysql5.1 on Windows.
I have placed "mysql" folder under c:\ and it seems to work fine.

But when I try C:\>mysql\bin\mysqldump.exe -u root -p mydatabase > c:\>mydatabase.sql from command prompt it doesn't work and gives back only Access Denied.
(It doesn't even ask to enter the password.)

How can I use mysqldump from windows command prompt?

it permissions conflict when try create file -

  c:>mydatabase.sql

create new folder c:\backup, change string to

C:\>mysql\bin\mysqldump.exe -u root -p mydatabase > c:\backup\mydatabase.sql

I think it just mistake when type there -

c:\>mydatabase.sql

it is also not correct, correct:

c:\mydatabase.sql

correct, but still will return error, right way - create folder


Is snapshot isolation good? My situation:

Table: User { Id, Name, Stone, Gold, Wood }

I have "write" threads:

miningThread (every minute)

UPDATE User SET Stone = @calculatedValue WHERE Id=@id
 UPDATE User SET Wood = @calculatedValue WHERE Id=@id

tradingThread (every minute)

UPDATE User SET gold = @calculatedValue WHERE Id=@id

constructionThread (every minute)

UPDATE User SET Wood = @calculatedValue WHERE Id=@id
 UPDATE User SET Stone= @calculatedValue WHERE Id=@id

And have "write" requests from users:

SellResource

UPDATE User SET Stone(Wood,Gold) = @calculatedValue WHERE Id=@id

(calculatedValue is calculated by C# bussiness logic code)

In this case, I have a lot "lost update" problems if i set read_commited_snapshot isolation level.
But if i set serializable or snapshot level, all work fine

Question

I was looking at an "isolation levels comparison" table and saw that serializable and snapshot isolation solve all problems with concurrent transactions. However, serializable is very slow.

Can I use snapshot isolation for all write transactions? I don't
want chaos in my tables. My business logic is hard and always changing.
Does snapshot isolation have flaws?
What isolation level is better for readonly transactions?

Summary:

Can I use snapshot isolation for all write transactions?

Yes, but depending on usage read committed snapshot might be better.

Does snapshot isolation have flaws?

Yes. It needs to store row versions of all active transactions, which requires disk/memory.

What isolation level is better for readonly transactions?

snapshot isolation, or read committed snapshot depending if there are several statements depending on each other and the size of the transaction. read committed if there are many updates and disk space on tempdb is a concern.
Deeper information
Which isolation level to use depends on your use-case and how you use your data within the transactions. So let's start with a deeper comparison of the levels.
The SQL Server maintains consistency by utilizing two different techniques: Locking and Row versioning.
Locking isolation levels
Locking works by the SQL Server issues a shared lock on the tables/rows that it reads, blocking other transactions from updating the data. The same goes for updates, which are issued an exclusive lock, blocking other transactions from reading the data. Locking occurrs in different parts of the database, which I will not go into, but for instance tables, rows, indexes.
READ COMMITTED is using locking to ensure that it only reads committed data. And that no other transactions are updating the data while it's being read.
This means that an update statement on rows that another transaction is currently selecting is being blocked. And, a select statement on rows that another transaction is updating is being blocked until that data is committed.
READ UNCOMMITTED ignores everything, and doesn't take any locks at all. This means that another transaction can perform an update on rows that the transaction is currently reading without being blocked, but it also have the effect that the transaction will receive data that the other transaction might not yet have committed.
SERIALIZABLE do the opposite, and locks everything. While READ COMMITTED releases the locks when it read the row, or when the statement is completed depending on the lock, SERIALIZABLE releases the locks once the transaction is committed. This means that another transaction that wants to update data that the transaction have read at least once, or another transaction wants to read data that the transaction has updated will be blocked until the transaction is committed.
Row versioned isolation levels
READ COMMITTED SNAPSHOT and SNAPSHOT ISOLATION uses row versioning instead.
Row versioning means that every time a row is modified, the SQL Server stores a version of the row, ensuring that it will stay the same when read by another transaction.
SNAPSHOT ISOLATION works in the way that when a read is done on a table, it retrieves the last version of the rows that were committed at the time the transaction started. This provides a consistent snapshot of the data within the transaction. Data modified after the transaction started will not be visible, but at the same time the transaction will not be blocked. To protect against lost updates, if the transaction wants to update some rows that have been changed by another transaction after the transaction begun, it will terminate the transactions due to conflicting data.
READ COMMITTED SNAPSHOT works in the same way as snapshot isolation, but instead of keeping the snapshot the entire transaction, it only keeps it for the duration of the statement. This means that two read statements within one transaction could receive different results.
However, when the transaction is doing an update, it is using the actual row instead of a previous row version and does not track if the row has been changed.
Since the SQL Server needs to keep each modified row that could be used by active transactions available, it stores them in tempdb. Due to this, tempdb needs to be large enough to incorporate all the changes. There is a background thread that checks which rows are still needed, and removes the rest, but if there is a long running transaction, it will prevent those rows to be removed. If tempdb will run out of space, no new row versions will be created, and any transactions attempting to access those (non-existing) rows will terminate.
Comments
READ COMMITTED SNAPSHOT is apart from READ UNCOMMITTED the most permissive when it comes to concurrency. It doesn't block any other DML statements, and keep a consistent view of the data within each statement.

SNAPSHOT ISOLATION is permissive and maintains better consistency than READ COMMITTED SNAPSHOT, with the drawback that due to its conflict resolution may fail when doing updates. Multiple statements within the same transaction are guaranteed to be consistent with each other.

READ COMMITTED is permissive when it comes to concurrent reads, not so much for updates. Due to locking, it is also slower than the row versioning levels.

READ UNCOMMITTED is not recommended to use due to it will read dirty data, if that is no concern then it is best due to no locking and no need to keep old row versions.

SERIALIZABLE is not recommended since it keeps locks on everything and therefore is slow and not good with concurrency.


UPDATE table SET value which SELECT from other table I need to update the column on Table_1, and then SET the value which select from Table_2

UPDATE top(3) a
SET col_1 = col_2_From_Table_2
// Problem here, select col_2 from Table_2 and update into col_1
FROM Table_1

Table_1

ID  | col_1
-------------
11  | null
11  | null
11  | null
11  | null
11  | null

Table_2

col_2
-----
AA
BB
CC

Expected Result of Table_1 (Update top 3 records which select form Table_2)

ID | col_1
----------
11  | AA
11  | BB
11  | CC
11  | null
11  | null

So really these tables aren't related in any way at all. Assuming you want each row in table 2 assigned to a row in table 1, here's one arbitrary way to do it:

;WITH t1 AS
(
  SELECT ID, col_1, rn = ROW_NUMBER() OVER (ORDER BY col_1)
    FROM dbo.table_1
    WHERE ID = 11 -- guessing here based on sample data
),
t2 AS
(
  SELECT col_2, rn = ROW_NUMBER() OVER (ORDER BY col_2)
    FROM dbo.table_2
)
UPDATE t1
    SET col_1 = t2.col_2
  FROM t1
  INNER JOIN t2
    ON t1.rn = t2.rn;

If you have other columns you want to order by, you can add them. If you only want three rows and there are more than three rows in table 2, add:

WHERE t2.rn <= 3;


What happens exactly when a chunk gets migrated? So, this is what MongoDB states:

  Independent of the secondaryThrottle setting, certain phases of the
  chunk migration have the following replication policy:

  MongoDB briefly pauses all application reads and writes to the
  collection being migrated, on the source shard, before updating the
  config servers with the new location for the chunk, and resumes the
  application reads and writes after the update. The chunk move requires
  all writes to be acknowledged by majority of the members of the
  replica set both before and after committing the chunk move to config
  servers.
  When an outgoing chunk migration finishes and cleanup occurs,
  all writes must be replicated to a majority of servers before further
  cleanup (from other outgoing migrations) or new incoming migrations
  can proceed.

Getting a real example: the first option "All queries on documents with productId value ranging between 18684 and 27851 will be routed to shard0000" would have been correct if the upper bound was 27850 instead?

If I understood it right, when I start the migration, if I try to run a query against documents ranging between the productId values 18684-27850, MongoDB already understands that it won't have to ask to shard0003 anymore, but instead it will have to ask for those documents to the NEW destination shard and thus redirect requests to shard0000.

So, I haven't actually tryed it, but I guess what you see is that, when you run the db.collection.find(), the cmd will "freeze" and after MongoDB successfully completed the chunk migration, it will retrieve the result set, is that right?

Yes and no! First config server copy all documents of moving chunk to destination server, then reading and writing is paused during time when chunk document is updated (one document, one value in that document), update is very fast process, so you don't actually notice anything. After that start cleanup at server where chunk was before.

So, you are very lucky if you managed to run collection.find() command just that moment when config server chunk collection is updated. And if you managed to do that, you probably cannot notice it or even measure that "slowness".


Application on Different SQL Servers In a Sql note "you should never put applications in each other's way if you do not have to.. Applications sharing memory, CPUs, internal tempdb  can cause an issue. One application submitting a runaway query can cause server to go to single halt." (SQL Server 2012 Unleashed) (Rankins)

Our company has 5 different applications. None of the multiple applications touch each other or utilize cross database joins. If company budget is not limited, resources are unlimited, and we have small overhead/ample time to administer different servers, should we place all applications on 5 servers boxes?

Unlimited everything?

Sure, buy five different physical boxes at $50,000 each hardware alone, use Core-based Enterprise licensing with SA, patch them all, administer them all, and pay annually for the licensing.

Realistically?  In a way you can justify to accounting?

Use a physical box either directly running Windows and SQL Server, or via a hypervisor,

give it plenty of RAM (enough to fit your entire DBs if you can afford it) - if you're stuck with Standard Edition, 128GB is the max (leave some for the OS)
a couple good, fast CPU's
and put your data on fast, reliable enterprise solid state drives - either local or on the SAN.
I do agree; do not put your actual applications on your SQL Server system/guest.

If they step on each other, look at the Resource Governor or separate VMs.  Watch out for licensing costs on the latter!


MySQL DNS issue: skip-name-resolve connection fast but cannot log into email MySql is used here with remote network connections, and after a reboot of the server these connections started taking 20-30 seconds to connect causing timeouts. Connections used to be nearly instant.

Adding skip-name-resolve fixes the delay as remote connections address the database by its IP address, however, this MySql server also hosts an email login database for an internal email server. The skip-name-resolve fix breaks all email logins from clients and servers.

The email server is a Postfix/Dovecot stack and using IMAP RoundCube Server and Thunderbird clients. I cannot figure out where in the email stack that addresses MySql configuration needs to be changed.

It seems the root of the problems would be a DNS issue, maybe an expired domain name in the configs somewhere. I spent hours looking and need some advice on tackling this. I'd rather solve the root DNS issue, if that's the problem - like track down an expired domain name or fault in the DNS addressing.

Workaround:

Keeping skip-name-resolve in place, I changed shunned users having non-IP number hostnames in the users table as follows:

UPDATE mysql.user SET Host='127.0.0.1' WHERE Host='localhost' AND User='username';
UPDATE mysql.db SET Host='127.0.0.1' WHERE Host='localhost' AND User='username';
FLUSH PRIVILEGES;

I will delve into the DNS problem when I have a spare weekend.

(Changed % to 127.0.0.1 as requested in a comment, as with % everyone would be able to access MySQL with username, with no IP checks. localhost is resolved as 127.0.0.1 in most cases (see your /etc/hosts). - [ed.])


Find insert/update/delete queries that reference a partitioned view I have just given a SQL Server with performance problems. The database is very large and use distributed views with a very large primary clustered key. (24 bytes).

I would like to change this primary key to another one (identity (1,1)) but in this case view won't be updatable. So I would like to find any update/insert/delete commands using this view. We have many procedures and linked servers too, which is why I can't review all procedures.

Any ideas? Profiling, but how? I know about instead of triggers, but I don't want to use them.

You can query from sql cache for long time:

SELECT cp.objtype AS ObjectType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.usecounts AS ExecutionCount,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st

You can add a where condition for the view name and the insert/update keyword in st.text.
You can schedule a job and save te results in a table and then check if the table contains rows for update/insert statements against your view.


Cannot create a row of size 11842 which is greater than the allowable maximum row size of 8060 The suspect table has 299 nvarchar(max) columns, 1 integer column and 1 nvarchar(255) column. Please do not ask me why, this is 3rd party vendor database. Anyway, according to my math, in the worst case scenario we should be well within 8060 limitation:

24x299 + 4 + 510 = 7690

Insert from external source fails with the above error during import process using vendor's import tool. Am I missing something in my calculation? Where possibly 11842 might come from? Any ideas?

Thank you!

This is down to the implementation of how VARCHAR(MAX) and NVARCHAR(MAX) actually store their data. If the number of bytes needing to be stored per column is less than or equal to 8000 bytes then the data will be stored in-row, if it is greater than 8000 bytes then it will be stored in LOB pages. When this happens, the the row holds a pointer to the LOB page that actually contains the data.

So, working backwards, the average length of data attempting to be stored stored in each column is:

(11842 - (4 + 510)) / 299 = 38 (approximate bytes / 19 characters)

As the value for each column is underneath the 8000 byte limit, it will attempt to store the data in-row and therefore exceed the limit for a page.

You can check out sys.dm_db_index_physical_stats to find out how much is actually stored in-row vs in LOB pages. Also, SQL Hints has a decent article that covers the basics.

Since you're probably stuck with this vendor database, and the data being imported, you can force off-row storage for all nvarchar(max) columns in the table with:

EXEC sp_tableoption 'dbo.mytable', 'large value types out of row', 1;


limit the number of rows returned when a condition is met? Is it possible to limit the number of rows returned when a condition is met?
I am working on a query to check if a student is ready to graduate, and they need to meet a certain number of credits per subject. I don't want all classes, because any class past the number of credits needed can be used for electives.

EDIT: I forgot to mention that this is SQL 2008 R2

I was hoping to be able to do something like this (which I know doesn't work)

select top(sum(credits) > 3.0) c.name, c.subject, c.credits, c.pass
from classes as c
where c.Subject = 'Math' and c.passed = 1

Any help would be great

Data

Subject        |  Name             | Grade | Credit | Pass
Social Studies | DL Psychology     | 83    | 0.50   | 1
Social Studies | Global Studies 10 | 82    | 1.00   | 1
Social Studies | Global Studies 9  | 83    | 1.00   | 1
Social Studies | Part. In Govern   | 84    | 0.50   | 1
Social Studies | US History 11     | 87    | 1.00   | 1

Query

select c.Subject,
           c.Name,
           c.credits,
           c.pass,
           c.score
    from @classes as c
    where (c.Subject = 'Social Studies' and c.pass = 1 and c.Name like '%Econ%')
    or    (c.Subject = 'Social Studies' and c.pass = 1 and c.Name like '%Gov%')
    or    (c.Subject = 'Social Studies' and c.pass = 1)
    group by c.Subject, c.Name, c.credits, c.pass, c.score
    having Sum(credits) <= 2.0

Im exprecting to see these rows returned

Expected Results

Subject        | Name              | Grade | Credit | Pass
Social Studies | Part. In Govern   | 84    | 0.50   | 1
Social Studies | DL Psychology     | 83    | 0.50   | 1
Social Studies | Global Studies 10 | 82    | 1.00   | 1

I'm not sure how this will scale but it's an answer :)

I should note that I'm summing for CN.RowNum < ClassNumbered.RowNum and not <= for a reason.  This way I can return everything lower than the requested value (2.0 in this case).  If I did <= then I would have to return everything <= 2.0 which won't work if the three values happen to be .5, 1 and 1 for a total of 2.5.  You wouldn't get the last class.  I also broke this up by subject so that if you have more than one subject you will get multiple groupings by subject.

WITH ClassNumbered AS (SELECT [Subject], Name, Grade, Credit, Pass,
                        ROW_NUMBER() OVER (ORDER BY [Subject],Credit,Grade) AS RowNum
                    FROM Classes),
    ClassTotals AS (SELECT [Subject], Name, Grade, Credit, Pass,
                        (SELECT ISNULL(SUM(Credit),0) FROM ClassNumbered CN
                            WHERE CN.[Subject] = ClassNumbered.[Subject]
                              AND CN.RowNum < ClassNumbered.RowNum) AS RunningTotal
                    FROM ClassNumbered)
SELECT *
FROM ClassTotals
WHERE RunningTotal < 2.0


Indexes are not visible in a MongoDB replica set On a local MongoDB instance i can connect using the mongo client, define an index using this command

db.collection.createIndex( { score: 1 } )

and then view that index and the default _id index using this command

db.collection.getIndexes()

If I connect to a Replica Set and run the same commands to create and view the indexes, all that is shown is an empty array

> db.collection.getIndexes()
[ ]

I know the indexes exist, as they are referenced if I use . explain() on a query.
So,how do i view the indexes when using a mongo Replica Set?

MongoDB shell version: 2.6.10

Mongo DB version
rs0:PRIMARY> db.version()
3.2.17

My local version of mongo was 2.6.10, and after upgrading the local version to 3.x I can now see the indexes in the remote replica set.

Default _id Index

As per MongoDB BOL Here MongoDB creates a unique index on the _id field during the creation of a collection. The _id index prevents clients from inserting two documents with the same value for the _id field. You cannot drop this index on the _id field.

  Note: In sharded clusters, if you do not use the _id field as the shard key, then your application must ensure the uniqueness of the
  values in the _id field to prevent errors. This is most-often done by
  using a standard auto-generated ObjectId.

Create an Index

To create an index, use  db.collection.createIndex()

db.collection.createIndex( <key and index type specification>, <options> )

The db.collection.createIndex() method only creates an index if an index of the same specification does not already exist.

  Note: MongoDB indexes use a B-tree data structure.

> use StackExchange
switched to db StackExchange
> show collections
position
test
> db.createCollection("score")
{ "ok" : 1 }
> db.score.createIndex( { score: 1 } )
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}
> db.score.getIndexes()
[
        {
                "v" : 2,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_",
                "ns" : "StackExchange.score"
        },
        {
                "v" : 2,
                "key" : {
                        "score" : 1
                },
                "name" : "score_1",
                "ns" : "StackExchange.score"
        }
]

Build Indexes on Replica Sets

For replica sets, secondaries will begin building indexes after the primary finishes building the index. In sharded clusters, the mongos will send  createIndex() to the primary members of the replica set for each shard, which then replicate to the secondaries after the primary finishes building the index.

To minimize the impact of building an index on your replica set, you can use the following procedure to build indexes.

  Important : To create unique indexes using the following procedure, you must stop all writes to the collection during this
  procedure.

  If you cannot stop all writes to the collection during this procedure,
  do not use the procedure on this page. Instead, build your unique
  index on the collection by:

  issuing db.collection.createIndex() on the primary for a replica set,
  or

  issuing db.collection.createIndex() on the mongos for a sharded cluster.

Ensure that your oplog is large enough to permit the indexing or re-indexing operation to complete without falling too far behind to catch up. See the oplog sizing documentation for additional information.
This procedure does take one member out of the replica set at a time. However, this procedure will only affect one member of the set at a time rather than all secondaries at the same time.
Before version 2.6  Background index creation operations become foreground indexing operations on secondary members of replica sets. After 2.6, background index builds replicate as background index builds on the secondaries.

For your further ref Here and Here


SQL Server Replication changing a tables ID numbers I am using a merge replication setup for our ERP system and I encountered this problem:

While creating a new entry on a table and with the last ID number given was 25, then new entry got the ID number 40000+ (I don't quite remember the exact number). I found a way to solve it with this query:

DBCC CHECKIDENT('tableName', RESEED, NEW_RESEED_VALUE)

but I wasn't sure if using this would be ok with the replication. I know that replication uses an ID pool range for each system connected to the publication. Anyway after restarting the System the ID seed was fixed.

What I want to ask is if there is a safe way to change the ID range for a whole table if needed while using replication.

You need to turn no 'NOT FOR REPLICATION' on the field, then the target will take the identity field from the source table rather than trying to generate its own

this can be done by:

alter table dbo.testtable
alter column [id] add NOT FOR REPLICATION

NOTE, I'm not sure what doing this while replication is enabled will do, if you do enable it while replication is on, ensure your replicate schema changes setting is on for that publication


Creating separate table as data retrieval queue I am storing pedigree data in a single table and each entry has reference to its two parents. Each time I add an entry I discover two new ones I need to collect. I am on the fence between two options to handle this:

Create a separate table that acts as a queue so that retrieval processes can simply delete an entry an fetch it, then add the two new entries to the end.
Add two flag bits to the main table: complete and processing. When an entry is fetched, we fill in its row and create two rows for its parents that have the completed flag set false. The processing flag is for parallel processing purposes.

Which method makes more sense?

Separate table.

Using a flag as 'is processed' to simulate a queue is an anti-pattern. Because, as the table grows, finding the rows that need to be processed becomes increasingly difficult. An index on the flag field is useless due to very low selectivity. Using a table as a queue alleviates the problem as the rows in the queue are deleted when processed.

You can make the case also at a more theoretical level as tables are for data (long lived, queried in various ways) and queues are for events (short lived, always dequeued and not queried etc) and they should be stored separately. Also separating the processing into queues will allow you to make much more complex workflows (basically, add more queues), while the same based on row state (flags or status field) is more difficult to achieve (again, the retrieval performance issue) and more invasive (workflow changes affect the row info, hence the model representation in the app).

See Using Tables as Queues and 5 subtle ways you're using MySQL as a queue, and why it'll bite you (I don't agree with everything in the second link, but is still a good read).


SQL Server 2014 Replication - The process could not connect to Subscriber 'xxx' I have just set up very basic transactional replication on SQL Server 2014.

The Publisher and Distributor are one and the same.

I have configured the Publisher to push to the Subscriber, so any and all configuration was done on the Publisher.

I set up all of the user accounts exactly as detailed in this Microsoft Tutorial.

The Publisher and Subscriber are successfully set up, and SQL Server Agent is running on both servers.

Regardless of this, when I check the status on the Publisher, I repeatedly get this message:

  The process could not connect to Subscriber '(subscriber-box)'

I've checked online, including here, here, here and here.

I've tried the suggestions and can't debug it, though I am pretty sure it's logon related.

Community Wiki answer generated from question comments by Brandon Williams

In the tutorial, the repl_distribution account will need to be added to the subscription database as db_owner.

In the New Subscription Wizard, on the Distribution Agent Security page, you specify a Process Account, which would be the repl_distribution account that was created on the Publisher. Now, further down on the page is a section 'Connect to the Subscriber'.  Choose 'By impersonating the process account'.

If the account exists at the Subscriber with the same username and password, and appropriate permissions, it will work.  This is known as pass-through authentication.  Alternatively, you can use SQL authentication to connect to the Subscriber.

Response from dthree:

I tripled checked all of the security settings to make sure it was all set up right, and in that I found that I shared and set the security settings on the wrong folder for the repldata snapshot. Fixed that, and between both fixes it worked.


Alternate of trigger in SQL server is there any other way beside creating a trigger in MSDB system tables to invoke a particular job when any job failed?

Basically, I want a particular job ran automatically only if any of the other jobs fails.

You can create a new job step at the end of the existing job that executes the "on-failure" job. You use the procedure sp_start_job to call this "on-failure" job. If the job fails, then have the on-failure action to execute that new job step. For this new step, make sure to set the on-success-action to "Quit the job reporting failure" so you know it failed.


How to add a constraint to check if the date entered (column) is today's date I was having this "database" exam and I bumped into the following question.

Alter table "x" to add a constraint that only lets you enter today's date in "x.datecolumb".

I clearly have no idea how to do so, so ...?

In all MySQL versions

  The CHECK clause is parsed but ignored by all storage engines.

MySQL Reference Manual, CREATE TABLE Syntax

The only way to solve Your problem seems to be a trigger pack checked constraint You need and discarded wrong data insertion. But this solution is not complete - there is a lot of situations when inserting/altering data do not fire the trigger action.


OFFSET X ROWS FETCH NEXT 50 ROWS ONLY not using indexes I have a query that is using OFFSET X ROWS FETCH NEXT 50 ROWS ONLY. If X is 160745 it uses the index, if X is 160746 it does not. There are more than 400000 records. With the index, it takes about 2 seconds to run, without the index it takes ~30 seconds.

We are using Azure S0

I know I can use query hints, however is there another option? Is there something in Azure that I can set to improve this? Any ideas would be appreciated.

**Note: assume all columns are no bigger than nvarchar(255) and only 6 columns.

Below is the query:

SELECT *
FROM [TableName]
ORDER BY [ColumnName] ASC
OFFSET 456450 ROWS FETCH NEXT 50 ROWS ONLY

It's hard to say without seeing query plans, the table structure, or the index definitions on the table. You should make sure that the index is covering the query. Other than that, you can encourage SQL Server to use the index even without an explicit hint to use the index. For example, if you are confident that the query should always use the index you could try setting a small rowgoal with a hint:

DECLARE @row_goal INT = 456450

SELECT *
FROM [TableName]
ORDER BY [ColumnName] ASC
OFFSET @row_goal ROWS FETCH NEXT 50 ROWS ONLY
OPTION (OPTIMIZE FOR (@row_goal = 1));

If you want to avoid hints entirely I recommend rewriting the query. Using OFFSET with large values requires special care. It's easy to end up with a plan that scans lots of rows and does a lot of unnecessary work just to get the next 50.

If your application pages through the data one page at a time you can consider the technique described here. The basic idea is that the application keeps track of the last value that the end user has seen. When you need a new page you can pass down the last value as a filter:

SELECT *
FROM [TableName]
WHERE [ColumnName] > ?
ORDER BY [ColumnName] ASC
OFFSET 456450 ROWS FETCH NEXT 50 ROWS ONLY;

If you need to be able to show an arbitrary 50 pages you can use the approach detailed here by Aaron Bertrand. With CTEs you can encourage the query optimizer to scan through a narrow index until it reaches the first row that you want. The query will get slower as you increase the OFFSET but it could perform much better than what you're doing now. The basic idea would be like this:

;WITH pg AS
(
  SELECT [key_column]
  FROM [TableName]
  ORDER BY [ColumnName] ASC
  OFFSET @PageSize * (@PageNumber - 1) ROWS
  FETCH NEXT @PageSize ROWS ONLY
)
SELECT t.*
  FROM [TableName] AS t
  INNER JOIN pg ON t.[key_column] = pg.[key_column] -- or EXISTS
  ORDER BY [ColumnName] ASC;

Finally, Paul White also writes about another way to efficient page through data here that does not use OFFSET at all.

With the right indexes I expect that at least one of the techniques described in this answer will work well for you.


How can I know whether postgres is currently running synchronous or asynchronous replication? I see from ps that:

postgres: wal sender process replicator X.X.X.X(38681) streaming 0/17AF5438

But is there any way for me to know whether it is synchronous or asynchronous? I made the relevant changes in postgresql.conf to switch from synchronous to asynchronous and restarted the postgres service, but I wanted to double check that the changes were parsed correctly and I am in fact running asynchronously.

If:

SHOW synchronous_standby_names;

on the master that's expected to have synchronous replicas reports a non-empty value, then synchronous replication is active.


How to TRIGGER/RETURNING this generator function in PostgreSQL? I am providing a key table to my users in various categories so I am thinking how to TRIGGER/RETURNING the function with each SELECT.
Functions to generate shorthand unique IDs

The thread answer Generating human-readable/usable, short but unique IDs but no salt so no.
http://hashids.org/ can be a valid option as proposed by the answer here
Any default PostgreSQL options?

Example of key table where I need the keys

Module 1
1. <key1>
2. <key2>
3. ...

Module 2
1. <key1>
2. <key2>

Keys are bound to the SERIAL primary keys in the table event_log.
I am thinking if the database can do the generation task for you i.e. how to bind your primary key to any algorithm that can generate such shorthand forms.
I have currently the data as the following in the table where SERIAL PRIMARY is not so clear

CREATE TABLE event_log (
    data_id SERIAL PRIMARY KEY NOT NULL,
    name VARCHAR(10) NOT NULL,
    module INTEGER NOT NULL, - - 1 or 2 or 3 as value
    time TIMESTAMP NOT NULL
);

Idea: Bind a TRIGGER and/or RETURNING nice_id to SELECT * FROM event_log WHERE module=1 queries

OS: Debian Linux 8.7
SQL: PostgreSQL 9.4 and/or R sqldf

I am thinking if the database can do the generation task for you i.e.
  how to bind your primary key to any algorithm that can generate such
  shorthand forms

Since you mention a salt, I assume you're looking for short strings
that are hard to guess or reproduce by an outsider, despite
being originated with a database sequence.
The permuteseq extension
essentially does that.

The function that produces a short string from an integer has
to be provided, for instance:

CREATE FUNCTION id_to_alpha(n int) RETURNS text
LANGUAGE plpgsql IMMUTABLE STRICT AS $$
DECLARE
 alphabet text:='abcdefghijklmnopqrstuvwxyz012345678';
 sign_char char:='9';
 base int:=length(alphabet);
 _n bigint:=abs(n);
 output text:='';
BEGIN
 LOOP
   output := output || substr(alphabet, 1+(_n%base)::int, 1);
   _n := _n / base;
   EXIT WHEN _n=0;
 END LOOP;
 RETURN CASE WHEN (n<0) THEN output || sign_char::text ELSE output END;
 RETURN output;
END; $$;

Then you may set the short id slug in a BEFORE INSERT trigger, like this:

CREATE EXTENSION permuteseq;

CREATE TABLE things(
 id serial,
 short_id text,
 name varchar(10) not null
);

-- keep the sequence short for short output strings
ALTER SEQUENCE things_id_seq MAXVALUE 1000000;

CREATE FUNCTION generate_short_id() RETURNS TRIGGER AS $$
DECLARE
 secret bigint := 123456789;  -- change for a different secret sequence
BEGIN
 NEW.short_id := id_to_alpha(range_encrypt_element(NEW.id, 1, 10000000, secret)::int);
 RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER things_trigger BEFORE INSERT ON things
FOR EACH ROW EXECUTE PROCEDURE generate_short_id();

Testing it:

INSERT INTO things(name) SELECT 'foo' FROM generate_series(1,10);
SELECT * FROM things;

 id | short_id | name
----+----------+------
  1 | wf3hg    | foo
  2 | tm6lg    | foo
  3 | riqbg    | foo
  4 | p6jp     | foo
  5 | h3r3c    | foo
  6 | 3sx5d    | foo
  7 | w8ecd    | foo
  8 | km3le    | foo
  9 | llt1e    | foo
 10 | xwtxc    | foo

In this example, there is both id and short_id in the table, but id
is technically redundant with short_id. You could create a sequence
outside of the table without the SERIALsyntax like this, and not
even have id in the table:

CREATE SEQUENCE seq_pk MAXVALUE 1000000;

CREATE TABLE things2(
 short_id TEXT
    DEFAULT id_to_alpha(permute_nextval('seq_pk'::regclass, 123456::bigint)::int)
    PRIMARY KEY,
 name text
);

INSERT INTO things2(name) values('foo');

SELECT * FROM things2;
 short_id | name
----------+------
 znyh     | foo

If you can't use permuteseq, as an alternative still based on
encrypting the ID, you may look at the plpgsql implementation of
SKIP32,
or XTEA for 64-bit (bigint)
which won't require a compilation or being superuser. The drawback is
that you cannot fine-tune the size of the output by changing the
sequence's range.


How to perform same DML statements on two different servers (Oracle and SQL-Server) simultaneously I have two servers, Oracle and SQL-Server.

I have same database on both the servers.

I want to perform DML (insert,update,delete).

How can I perform same DML statements on both the servers simultaneously ?

If I insert one statement in SQL-Server then the same statement should be updated in the Oracle database .

I do not have enough reputation to write a comment so I am writing here. It may be an unswer to your question.

I have never tried, but it should work. You can add an Oracle database into your SQL Server as a linked server and then write a trigger in the sql server and perform the insert to Oracle via OpenQuery. This should be the easiest way and should work.


Primary key: (a) set it on a single column for two distinct data points or (b) set it on a combination of two columns, each for a separate data point? I am designing a database for an e-commerce company. Each product will be identified by a unique ID (SKU). We want to format the SKU as LLLL000. LLLL is the parent product, for example Iphone X.
000 is the variation product, for example color: Gold, storage: 256, status: Open Box.

Following the example, the SKU for Iphone X, color: Gold, storage: 256, status: Open Box, would be something like: LKGH177
A different Iphone X would have: LKGH178.

My questions are:

Should I consider the entire SKU as a single-column primary key that contains two distinct data points, and so violating 1NF?
Should I consider the SKU split in two columns and set a two-column composite primary key?

Database is not my main field so I hope it is clear.

I suggest doing both. Create an attribute for SKU and two separate attributes for the product and sub-type. SKU would be a key and the product sub-types would be a composite key. Example:

CREATE TABLE product
(sku CHAR(7) NOT NULL,
skusub1 CHAR(4) NOT NULL,
skusub2 CHAR(3) NOT NULL,
...
UNIQUE (sku),
UNIQUE (skusub1,skusub2),
CHECK (skusub||skusub2=sku));

You can give the sub1 and sub2 attributes more meaningful names.

This is not actually a violation of 1NF. SKU is an important value in its own right and as long as you have the separate components in the database as well then there's no reason ever to split SKU into two parts. SKU is nearly always treated as a single value in my experience because although it may have encoded elements within it the encoding scheme is subject to variation. You may at some point have SKUs that don't follow the same scheme.

(note: in the above example I haven't identified a primary key because that's not relevant for this example)


Is it possible for unique key to break master-master replication in mysql? I have a master-master mysql setup with 2 servers running the exact same application making writes to such a table:

 CREATE TABLE `metric` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `host` varchar(50) NOT NULL,
  `userid` int(10) unsigned DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `sampleid` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique-metric` (`userid`,`host`,`name`,`sampleid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

auto_increment_increment is 2 and offsets are 0 and 1, so PK ids don't clash, but is it possible that with bad timing, 2 applications will create a row with an equal unique-metric index breaking replication on both mysql servers, since replication thread won't be able to insert replicated row into table due to another row already having the exact same index?

Though I've not tried this myself, I've done a quick documentation hunt and found that this part of the MySQL documentation is helpful.

The relevant part is:

  If a statement produces different errors on the master and the slave, the slave SQL thread terminates, and the slave writes a message to its error log and waits for the database administrator to decide what to do about the error. This includes the case that a statement produces an error on the master or the slave, but not both.

The next question would then be: "How do I know there has been an error?", to which I've found a useful script here, which could be run frequently as a cron task.


Data model Reference and schema I just started to learn MongoDB database and for my project.

I am trying to implement a MongoDB reference schema.

In my Menu table I would have a food ingredients attributes where it will store the food ingredients.

I am confused about designing my schema.

How am I going to build the schema with the food ingredients in Menu table?
Because it might be 100 ingredients, do I need to create an attribute for each of them ?
Or I can make one string attribute where I am going to store everything?

Here is an example of it.

var Menu = new Schema({
  foodTitle: String,
  foodIng: {
    title: String,
    ingredients: {
       rice: String,
       beans: String,
       meat: String,
       cheese: String,
       // etc...

    }
  },
});

As far as I have understood your question, you are trying to create a model for dishes and list their ingredients.

You should never, ever use values as keys. With a relational database, would you create a table which has a column for each imaginable ingredient? Hardly.

Furthermore, while redundancy isn't necessarily a bad thing, it is in keys. In a collection called "dishes" or "food", it is natural that an ingredient does not refer to a chemical compound, right?

So, here is how I'd model a dish:

{
  "_id": new ObjectId(),
  "name": "Katsuo no Tataki",
  "desc": "Lightly Broiled Bonito",
  "tags": ["fish","jp"]
  "ingredients": [
    { "name": "Bonito Filet", qty: "1/2" }
    { "name": "onion", qty: "1/2"},
    { "name": "garlic", qty: "2 cloves"}
  ]
}

When displaying a dish, you simply iterate over the ingredients array, displaying both name and qty.

In order to find all dishes which contain garlic, you simply do

db.dishes.find({"ingredients.name":/garlic/i})

or, to find recipes containing the fish tag:

db.dishes.find({"tag":/fish/i})

Note that I used a case insensitive regex search.


SQL Server takes far too much memory We have a problem with one of our SQL Servers that I have never seen before.

The SQL Server (Enterprise Edition) runs a database with only 70MB data. But after a few hours the server takes 10 GB memory or more. After a few days the server runs out of memory. I already configured the max memory parameter in SSMS, but it didn't help.

Do you have any ideas what to do to find the problem?

Here is the link to the result of dbcc memorystatus: https://drive.google.com/open?id=0B2FJAT6lteKJOEJmUFdqb0ZERFU

Database Version: Microsoft SQL Server 2014 (SP2-CU5) (KB4013098). Enterprise Edition. This is a virtual machine.

I checked another thread and found this query:

SELECT TOP (21) [type] = COALESCE([type],'Total'),
  mb = SUM(pages_kb/1024.0)
FROM sys.dm_os_memory_clerks
GROUP BY GROUPING SETS((type),())
ORDER BY mb DESC;

I ran this on the SQL Server and get the following results:

total = 2452,9
CACHESTORE_SQLCP = 2141,5
MEMORYCLERK_SQLBUFFERPOOL = 160
MEMORYCLERK_SOSNODE = 33
USERSTORE_SCHEMAMGR = 30.94

Is this normal that the cachestore value is that high after it has been running only hours?

Running this query:

select value_in_use
from sys.configurations
where name = 'max server memory (MB)'

Gives the result 8192. This is the value we configured for the instance.

At the moment we have to restart the SQL Server every night so that it is not using too much memory.

Your question, at first, surprised me. Usually, I've seen memory issues with max server memory being configured incorrectly. But then I started pouring over the cumulative updates and realized that their are minefields of memory leaks sewn throughout all these cumulatives for SQL 2014! It's like Microsoft took a hint from their SQL 6.5-7.0 playbook and went retro on us.

Rather than spend hours of trouble-shooting, I would consider:

Option A:
Taking a snapshot of the VM and installing the latest cumulative 7 update for SQL 2014 sp2. And rolling back if necessary. (it looks like a memory leak related to SSAS is included in this update).

Option B:
Create a Microsoft support ticket to help yourself and the rest of us out by possibly adding a memory leak fix to cumulative 8 when it comes out. And updating your question based on Microsoft's findings.

Personally, I don't think I would consider any reason satisfactory for SQL Server going much above it's max memory limit--for any situation.


Extremely Slow StatMan query issue with #SP2010 farm The issue I’m experiencing is explained in a blog-post I created for this, (easy to refer to and to manage the responses / solutions in 1 location) which can be found here: http://schoennie.blogspot.com/2013/09/slow-statman-query-issue-with-sp2010.html

Short summary is that this single server install of SharePoint 2010 on top of SQL 2008R2 (same server) is experiencing a VERY slow response for every first upload during a certain interval (seems to be every morning).
Analysing the SQL activities revealed to me that when you start an upload, this query get’s executed before the “insert part” of the upload:

SELECT StatMan([SC0], [LC0])
FROM   (SELECT TOP 100 PERCENT CONVERT(VARBINARY,
                                        SUBSTRING ([Content], 1, 100) +
                                        +SUBSTRING([Content], CASE
                                                                WHEN LEN([Content]) <= 200 THEN 101
                                                                ELSE LEN([Content]) - 99
                                                            END, 100)) AS [SC0],
                                        DATALENGTH([Content])   AS [LC0]
        FROM   [dbo].[AllDocStreams] WITH (READUNCOMMITTED)
        ORDER  BY [SC0]) AS _MS_UPDSTATS_TBL

I spent a couple of days now trying to get more info on this StatMan query and why it’s causing the disk i/o to go through the roof and
The Disk Queue Length to grow above the 5 value (normally around 0.01)

Please share your thoughts on this or maybe point me in a certain direction / resource ? I’m involved as SharePoint Consultant & DBA in this software space for about 8 years now, but I have not yet seen something like this !

Thanks so much in advance,
Jeroen

This is SQL Server updating the statistics on a table. It can be triggered automatically when about 20% of the data in your table changes, or on demand if you run jobs that update statistics.

You have a few ways to lessen the pain.

You can enable asynchronous statistics updates, which lets SQL Server update stats behind the scenes without blocking other queries. It'll still have a performance impact, though, as Kendra Little explains in The Secret IO Explosion.

You can manually update statistics proactively during maintenance windows. This doesn't stop the chances that 20% of your data will change, but it just reduces the likelihood.

You can disable automatic updating of statistics altogether. This is generally a pretty bad idea since your execution plans may be wildly incorrect, and it doesn't stop folks from manually updating the statistics either.


Normalization: Should common columns of two tables be pulled out into a third table? Should I further normalize my current design to eliminate redundancies?

I have the following tables:

empl: standard user table

empl_cat: employee "category"

empl_admin_cat: administrative "category"

Both of the category tables have a many-to-one relationship back to the empl table.

The employee category represents the categorization of the employee within the company.

The administrative category represents that employee's administrative oversight (if applicable) over other employees.

The tables are defined like this (irrelevant columns have been omitted):

empl (
    empl_id int PK,
    ...
)

empl_cat (
    empl_cat_id int PK,
    empl_id int FK,
    eff_date date,
    base_id int FK,
    eqp_id int FK,
    pos_id int FK
)

empl_admin_cat (
    admin_cat_id int PK,
    empl_id int FK,
    co_id int FK,
    base_id int FK,
    eqp_id int FK,
    pos_id int FK
)

I considered using a single table with a type column, but decided against it because they logically represent two different things, and while 3 of their columns are the same (base_id, eqp_id, pos_id), they each have one additional column (eff_date and co_id).  Was this the right decision?

Would it be worthwhile creating another table that has an autoincrement Primary Key which holds all possible variations of base_id/eqp_id/pos_id, with the empl_cat and empl_admin_cat tables referencing the appropriate record in this new table instead of having their own base_id/eqp_id/pos_id columns?

Edit: The base_id, eqp_id and pos_id fields represent their "base of operations", "operating equipment", and "position". For example, SFO/777/Captain. The co_id in the empl_admin_cat represents a company.  eff_date in empl_cat is an effective date.

I see 3 options

Do nothing
Superkey/subtype, that is put the common fields into empl
Change to a self referencing table to build a hierarchy

Now,

Option 2 is similar to what you've described (mostly)
Option 3 is where you'd use a recursive CTE

However, I can't help thinking that Option 3 or some variety would be better. Perhaps some more info about what the columns mean? (Personally I don't abbreviate column names because the names are opaque)


number field in varchar2 field I have a table with a number field (non-null). I have another table with a varchar2 field. Examples:

Users
UID,Name
123,Mickey
41128,Joe
43,Alice

Groups
UIDS,Name
(123,43),Children
(43),Admins
(),Deleted

I want to join the two tables where the number column is "in" the varchar2 column. As you can see, the UIDS column has been developed to look like the syntax of an "in" join. However, the following does not work:

select * from users join groups
where users.uid in groups.uids;

The number field cannot be in a string. I can cast the number field to a varchar 2 field, but it doesn't recognize the groups.uids column as a list of uids. It recognizes it as one value.

I believe that the next step I need to do is to strip the ( and ) from the uids value and then split it on the comma to create a list. Is that correct or is there a simpler way to merge these two tables?

Not sure, why this is marked "Active yesterday". Once I'm here…

If it needs to be done (which it shouldn't, if proper database design can be applied), this could be a way:

SELECT
  Users.userID
  , Users.name AS userName
  , Groups.uids
  , Groups.name AS groupName
FROM Users
INNER JOIN Groups
  ON INSTR(TRANSLATE(Groups.uids, '()', ',,'), ',' || TO_CHAR(Users.userid) || ',') > 0
;

It turns the parentheses around the list of group ids into commas to simplify the comparison against the comma padded userIDs. The latter is to prevent, e.g., 123 to match 12.

See it in action: SQL Fiddle

Please comment, if and as this requires adjustment / further detail.


SQL Server Average Lock Wait time Historical data Monitoring software has indicated average lock wait times rising to 500ms during the night.

Is there any way of confirming which locks these were as we currently do not log any performance data to a table so would be relying just on DMV's.

Will be looking at writing performance data to a table for future reference.

@zapcon, there is DMV master.sys.dm_exec_requests;

USE Master
GO
SELECT *
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
GO

The master.sys.dm_exec_requests; DMV provides details on all of the processes running in SQL Server. With the WHERE condition listed below, only blocked processes will be returned.

as well as Stored procedure such as sp_who2, & sp_who; through you can identify the blocked spid.

USE Master;
GO
EXEC sp_who2;
GO

The sp_who2 system stored procedure provides information about the current SQL Server processes with the associated users, application, database, CPU time, etc. The information returned can be filtered to return only the active processes by using the ‘active' parameter.

For your further Ref How to identify blocking in SQL Server


Is an anomaly possible if a serializable transaction interfers with another isolation level? Let's assume transaction A runs in SERIALIZABLE and transaction B runs in a more permissive isolation level, like READ UNCOMMITTED. Can Transaction A now suffer from any anomaly introduced by transaction B?

I found this SO question, but the answer doesn't give that information. I understand the referred manual page only for homogeneous scenarios:

  The guarantee that any set of concurrent serializable transactions will have the same effect as if they were run one at a time means that if you can demonstrate that a single transaction, as written, will do the right thing when run by itself, you can have confidence that it will do the right thing in any mix of serializable transactions

I found these references in the SQL draft indicating that SERIALIZABLE transactions will indeed not suffer from any anomaly induced by other isolation level transactions:

CD 9075-1:200x(E) page 27:

  The highest isolation level SERIALIZABLE, guarantees serializable execution, meaning that the effect of SQL-transactions that overlap
  in time is the same as the effect they would have had, had they not overlapped in time.

CD 9075-2:200x(E) page 124:

  Changes made to SQL-data or schemas by an SQL-transaction in an SQL-session may be perceived by that
  SQL-transaction in that same SQL-session, and by other SQL-transactions, or by that same SQL-transaction
  in other SQL-sessions, at isolation level READ UNCOMMITTED, but cannot be perceived by other SQL-
  transactions at isolation level READ COMMITTED, REPEATABLE READ, or SERIALIZABLE until the
  former SQL-transaction terminates with a .

That's the standard. But how do the implementations do in reality? I'm particularly interested in the defined behaviour of Postgres and MySQL.

PostgreSQL wiki https://wiki.postgresql.org/wiki/Serializable#PostgreSQL_Implementation states:

  Any transaction which is run at a transaction isolation level other than SERIALIZABLE will not be affected by SSI. If you want to enforce business rules through SSI, all transactions should be run at the SERIALIZABLE transaction isolation level, and that should probably be set as the default.

In your example since transactions A and B are not both SERIALIZABLE, anomalies can happen.


Can you set up a SQL server maintenance plan to restore a database? I am looking to setup a maintenance plan that makes a backup of a database (Database1) and then restores to another database (Database2) to confirm that it is able to restore successfully.

Can I do this as part of a maintenance plan and have SQL server mail me if it fails either the backup or the restore?

Yes you can. After your backup task, add an Execute TSQL task that has the restore command. Join them together with a success constraint.

Add a Send Mail task and join it to both the backup task and the restore task. Make it an OR constraint and convert it to a failure constraint rather than success


Trace to find "error messages"( SQL Server 2008 R2 ) I'm using this config, trying to find error when a certain host executes a task ( it runs inside a software ):

The user says that the software is running, but it's not updating what it needs to update.

So, I'm tracking their queries and procs, to find some errors.

Is this correct? If I use this:

select * from table where

It shows me an error ( that's what I want ).

Do you guys use something different? Is this a good way to find errors using trace? I use HOSTNAME with his machine.

Again, it's working. I just would like to know some other tips.

Thank you very much.

Have you looked into SQL Server's Ring Buffer, especially the Exception Ring Buffer?

From SQL Server Ring Buffers and The Fellowship of the Ring by Sudarshan Narasimhan, I got the following query which works great for detecting uncaught exceptions and errors:

SELECT CONVERT (varchar(30), GETDATE(), 121) as [RunTime],
    dateadd (ms, (rbf.[timestamp] - tme.ms_ticks), GETDATE()) as Time_Stamp,
    cast(record as xml).value('(//Exception//Error)[1]', 'varchar(255)') as [Error],
    cast(record as xml).value('(//Exception/Severity)[1]', 'varchar(255)') as [Severity],
    cast(record as xml).value('(//Exception/State)[1]', 'varchar(255)') as [State],
    msg.description,
    cast(record as xml).value('(//Exception/UserDefined)[1]', 'int') AS [isUserDefinedError],
    cast(record as xml).value('(//Record/@id)[1]', 'bigint') AS [Record Id],
    cast(record as xml).value('(//Record/@type)[1]', 'varchar(30)') AS [Type],
    cast(record as xml).value('(//Record/@time)[1]', 'bigint') AS [Record Time],
    tme.ms_ticks as [Current Time]
from sys.dm_os_ring_buffers rbf
cross join sys.dm_os_sys_info tme
cross join sys.sysmessages msg
where rbf.ring_buffer_type = 'RING_BUFFER_EXCEPTION' --and cast(record as xml).value('(//SPID)[1]', 'int') <> 0--in (122,90,161,179)
and msg.error = cast(record as xml).value('(//Exception//Error)[1]', 'varchar(500)') and msg.msglangid = 1033 --and [Error] = 4002
ORDER BY rbf.timestamp ASC


CPU usage constantly high after upgrading from Postgres 9.5 to 9.6 to 10.6 I upgraded my RDS instance (db.t2.medium) from Postgres 9.5 to 9.6 couple of days back. Since the upgrade, the CPU usage was almost 100% all the time. This brought down my application. Presuming that the problem is with the 9.6 version, I did another upgrade to 10.6. It got better but still considerably higher than the CPU usage for the original 9.5 version.

I haven't changed the application code during the upgrade. How can I check the reason for the increased CPU usage and possibly fix it?

ANALYZE VERBOSE;

You need to run an ANALYZE query on the whole database using the above command.

The issue is that the query plans postgres has generated are optimised for the previous version of postgres, when you do an RDS update it does not implicity regenerate these plans, this needs to be done manually (I'm sure there's a reason why AWS doesn't do this manually but I've really no idea why).

In my case I saw roughly a week of extremely high CPU usage, just as in your case, then after running ANALYZE my cpu dropped back to it's previous baseline. As you can see in the image below the upgrade (in my case from 9.4 - 9.5) was run on 11/27, the analyze query was run on 12/02.

(The VERBOSE is not strictly necessary but it's useful to be able to watch the progress of the command)


Mysql Slave Claims to be Running but New Records Not Appearing and No Delay I've created a new mysql master and a slave. My master is also a slave of another running instance. The replication between those two works fine so I don't believe my issue is there.

I created a dump from the master with master-data=1 then sent the dump to the slave and created wrote it < file.sql. If I run show slave status I get back (roughly, simplified for MVCE, if more is needed please specify):

Slave_IO_State: Waiting for master to send event
Master_Host: master_ip
Master_User: replicant_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 120
Relay_Log_File: ip-slave_ip-relay-bin.000004
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Exec_Master_Log_Pos: 120
Relay_Log_Space: 512
Master_Server_Id: 2222
Master_UUID: f2c7d7a1-bb6c-11e8-a3b1-02e34d38ec08
Master_Info_File: /var/lib/mysql/master.info
Seconds_Behind_Master: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400

then If I do show master status on the master I get back:

File: mysql-bin.000003
Position: 120

which looks correct, the position and the file match. Then if I view my users:

select userid, username from users order by userid desc limit 30;

I get 28 more records on my master and the master master (master master and master counts and values match) so the slave isn't getting new records despite the Slave_SQL_Running: Yes.

How is this possible? I guess I did something wrong but what, where should I look?

I looked at the mysql-bin.000003 but is says:

  Warning: this binlog is either in use or was not closed properly.

which makes sense it is the current file. I looked at the 00001 and 00002 to see if the queries I missed were in either of those files but I couldn't find them.

When running an intermediary replication that is also a master, log-slave-updates needs to be enabled to ensure that the upstream master, replicates to the downstream slave.


Why isn't there a way to label a column as "keyword" In a typical star schema, you end up with a bunch of columns that are an ID into some lookup table, where the lookup table is pretty much just this:

create table hostnames (
  id primary key,
  name varchar(200)
)

And when you select from your main table, you end up with a bunch of left joins, when you insert, you always have to lookup for the ID first, then insert if it's not there. It's a pain. 50% of my database related code is just for dealing with this.

It would be nice to have a way to indicate to the database that a given column has low number of distinct values, so that it can build its own internal lookup table and save space. The above would become:

create table sometablewithahostname (
    ...
    hostname varchar(200) keyword,
    ...
)

And there is no need to manage a the other tables.
Is there a RDBMS system that does that?

If your goal is to de-normalize while mitigating the storage space requirements, Hybrid Column Compression in Oracle 11gR2 can help with this.  It is only available on Enterprise Edition with a few specific types of storage.

  Storing column data together, with the same data type and similar
  characteristics, dramatically increases the storage savings achieved
  from compression. The database compresses data manipulated by any SQL
  operation, although compression levels are higher for direct path
  loads. Database operations work transparently against compressed
  objects, so no application changes are required.


UNIQUE constraint on a specific pairing I am trying to work out a special UNIQUE constraint: there are many columns in a Users table but only these two are relevant for the question:

id bigint,
active boolean,
...

I want to allow multiple rows to have the same id - but only one of them may have active = true.

E.g. There can be many rows with id = 1 and active = false, but only one can have id = 1 and active = true.

For this, you can make use of a very useful PostgreSQL feature - that of the partial index.

You have your table:

CREATE TABLE foo
(
  id BIGINT NOT NULL,
  --
  -- other fields
  --
  active BOOLEAN NOT NULL
);

Then, you do:

CREATE UNIQUE INDEX ON foo (id)
WHERE active;  -- if you prefer more clarity, could have "WHERE active = TRUE;"

So, we try it out (see the fiddle here):

--
-- `active` - all false for a given id - works
--
INSERT INTO foo
VALUES (1, FALSE), (1, FALSE), (1, FALSE);

check:

SELECT * FROM foo;

Result:

id  active
1   f
1   f
1   f

Another possibility - the specific one pertaining to the question:

--
-- one and only one `active` = true for a given id - works
--
INSERT INTO foo
VALUES (2, FALSE), (2, FALSE), (2, TRUE);

Check:

SELECT * FROM foo;

Result:

id  active
1   f
1   f
1   f
2   f
2   f
2   t

Final test:

--
--  attempt to INSERT two true `active` values for a given id - fails!
--
INSERT INTO foo
VALUES (3, FALSE), (3, FALSE), (3, TRUE), (3, TRUE);

Result:

ERROR:  duplicate key value violates unique constraint "foo_id_idx"
DETAIL:  Key (id)=(3) already exists.

The foo table is unchanged, which is what we want!


MySQL, repair a non-broken table Whenever a table crashes, MySQL returns as error: Table xxx crashed. Please repair the table.

Now I was wondering, what will happen if I repair a table that is healthy? Will it be ignored? Can I damage the table?

Reason: In the past days different tables have crashed and I'm considering issuing a repair on all tables as preventive measure.

Repairing a table that is not crashed shouldn't break anything in the ideal case. Unfortunately, the real world is not an ideal case; a power outage or a hardware failure during the repair can leave the table in an inconsistent state. That's why backing up the table before repairing it is strongly recommended.

Also having the database offline during the maintenance operations helps fixing it faster.

Please take a look at the documentation of REPAIR TABLE for more information.


Removing Outliers Grouping by Minute I have a very simple table storing the following fields per http request:

id serial
url varchar
date varchar (yyyy-mm-dd)
time varchar (hh:mm)
latency integer

And I am trying to calculate average latency per minute, however if i have 10 requests (9 of 1s and 1 of 100s) then average becomes useless. In order to solve that I was wondering about remove outliers.

So, basically what I want is to calculate the average excluding request which latency is bigger then 99.7%, naturally I want to group those requests per minute in order to follow "average" latency per minute.

My query is as follows:

select mdate,mtime,avg(latency)
from mtable
  join (
    select mdate,mtime,avg(latency) + 3*stddev(latency) as "uband"
    from mtable
    where url like '%ThePartialUrl%'
    group by, mdate, mtime
 ) as t
 on mdate=t.mdate and mtime=t.mtime and latency < t.uband
 where url like '%ThePartialUrl%'
 group by mdate,mtime
 order by mdate, mtime

However it is taking too much time.
Things I've already know/done:

url like requires a full scan regardless of index
mdate and mtime could be one column using datetime value
I have index for almost everything (mdate,mtime,latency)
ajusting work_mem parameters

The table itself has around 100M rows at total.

Looking at the execution plan it seems the nested loop (join) is the one taking most time since the subquery returns 40K rows (1 per minute during a whole month) and it shall be joined around to 10M rows for the specific '%ThePartialUrl%', resulting in 4 a billion loop.

Anything that could help me improve it?

I am using postgres 10.

Thanks and Regards

It seems there is no alternative to solve the problem without using a sub query, what is a performance constraint in my case, so I decide move to another approach.

I gave up about the trimmed means (thanks @Linas) and move to a percentile_disc indicator. Once all I wanted was to give a measure of latency for each request I did a simple query as follow:

select
  mdate,
  mtime,
  percentile_disc(0.997) withing group (order by latency) as "response"
from mtable
where url like '%MyPartialUrl%
group by mdate,mtime
order by mdate,mtime

As this way I grab the biggest latency took to answer a request for 99.7% of all users. In fact I am grabbing several percentiles following six sigma approach to show how much slowness per million requests we are getting on production.

Query took no more than 8 seconds to be executed, using the trimmed means before it was around 90 minutes.

Thanks all.
Regards


How can I call a remote User Defined Function with Parameters? We have one SQL server which contains a link to another server that is on a different box. I would like to create a user-defined-function on ServerA which returns the value of the udf on ServerB, however SQL is telling me that Remote function calls are not allowed within a function.

Is there a way around this?

The udf does have a parameter, so I cannot use OPENQUERY. I thought I had it working by using sp_executesql, however although this works in a blank query window, it doesn't work from a user-defined function.

declare @someValue bit

exec ServerB.MyDatabase.dbo.sp_executesql
    N'SELECT @someValue = dbo.[SomeUDF](@id)',
    N'@id int, @someValue bit OUTPUT',
    @id=@id, @someValue=@someValue OUTPUT

select @someValue

The entire function gets called as part of a query, so I don't think I can use a stored procedure

SELECT *
FROM SomeTable
WHERE dbo.SomeUDF(Id) = 1

You can use OPENQUERY. You just have to use dynamic SQL to build the OPENQUERY call

DECLARE @sql nvarchar(4000);

SET @sql = 'SELECT * FROM OPENQUERY(linkedServerName,
                          ''SELECT @someValue = dbo.[SomeUDF](' + CAST(@id etc) + ')'')';
EXEC (@sql)

However, you can't build this into a larger query on the local server.
UDFs + linked servers + parameters just don't play nice together...


Improving PostgreSQL cluster performance I am trying to cluster my table faster. The table is 27 GB worth of data, and has one index using a point geometry which is 19 GB in size. I am seeing about 5.5 hours to cluster the table.

I am assuming clustering is a IO heavy process, so anything to improve read/write times (new disk) would be my first thought, but are there any other things that could improve the time it takes to cluster a table?

I ran this test on a machine with an SSD and 12 GB of RAM, using postgresql 9.4

I found an interesting solution which you might be interested in. Basically, it involves creating a temporary clone of the table, where the data is copied into (this does the clustering). This will keep your original table available. After the insert is completed; you use the copy as new source; and discard the original (unclustered) table.

  The table is partitioned into three child tables: static1, static2, and latest. The parent table has a rule that redirects all inserts into the "latest" partition. This serves as the data collection point, where new rows sit until they are migrated into one of the other two partitions. The "static1" and "static2" partitions serve as front and back buffers for the cluster operation. Only one partition will actually have any rows at any given time (the front buffer); the other will be empty (the back buffer).

You can also have a look at pg_repack; which claims to provide an alternative to VACUUM FULL without the exclusive lock. This plugin basically does the same as the blog above does manually:

  To perform full table repacks, pg_repack creates a work table in the "repack" schema and sorts the rows in this table. Then, it updates the system catalogs directly to swap the work table and the original one.

Alternatively, playing with maintenance_work_mem might be a simple fix; increasing that might be enough.


Lock a MyIsam table across connections? I have a bunch of mysql statements (that work on one MyIsam table only) in my PHP script and I don't want to execute these statements concurrently at any time, regardless of how many connections are being used.

For example, two different clients execute the php script on my server and the script opens one mysql connection per each request. I want first to execute the entire bunch of statements in the 1st request and only when this is completed, to execute the bunch of statements in the 2nd request.

I tried:

using "START TRANSACTION" before the bunch of statements => the table is MyIsam and must stay so, so this has no effect.

using "LOCK TABLE t WRITE" before the bunch of statements => locks are not maintained across connections so this doesn't work.

Thanks for help.

I'm using GET_LOCK and RELEASE_LOCK in this situation.

  GET_LOCK() can be used to implement application locks or to simulate
  record locks. Names are locked on a server-wide basis. If a name has
  been locked within one session, GET_LOCK() blocks any request by
  another session for a lock with the same name. This enables clients
  that agree on a given lock name to use the name to perform cooperative
  advisory locking. But be aware that it also enables a client that is
  not among the set of cooperating clients to lock a name, either
  inadvertently or deliberately, and thus prevent any of the cooperating
  clients from locking that name. One way to reduce the likelihood of
  this is to use lock names that are database-specific or
  application-specific. For example, use lock names of the form
  db_name.str or app_name.str.

  If multiple clients are waiting for a lock, the order in which they
  will acquire it is undefined. Applications should not assume that
  clients will acquire the lock in the same order that they issued the
  lock requests.

Before running a bunch of statements, run:

SELECT GET_LOCK('my unique lock name', 10);

If it returns 1 (success) go on with your bunch of statements and in the end

SELECT RELEASE_LOCK('my unique lock name');

Add some handling of the timeout case, such as retry few times.

It doesn't matter whether your statements work with one table or many tables, whether they are MyIsam, or not. These application-specific locks are server-wide.


Is a single-table relational database similar to a simple NoSQL database? One advantage touted for NoSQL databases is the lack of a schema. In the case of a single-table relational database, there are no relationships between multiple tables, and new columns can be easily added to the single table. It seems like it is just as good as a NoSQL database, since there is no need to design a schema. So, is a single-table relational database similar to a simple NoSQL database?

Are there good reasons to use NoSQL databases if single-table relational databases are good enough, given that relational databases like SQLite are better tested and likely more stable?

No - a single table relational database is more akin to a spreadsheet than a NoSQL database. NoSQL is not about having no schema, it's more about having a flexible schema!

Wikipedia puts it very well when it says NoSQL:

  provides a mechanism for storage and retrieval of data that is modeled
  in means other than the tabular relations used in relational
  databases.

The key (pardon the pun) word here is modeled - i.e. there is a model, it's just not a relational one!

As an aside, it's well worth reading the entire article - it's detailed and well written!

IBM says:

  It's important to emphasize that the "No" in "NoSQL" is an
  abbreviation for "not only" and not the actual word "No." This
  distinction is important not only because many NoSQL databases support
  SQL like queries, but because in a world of microservices and polyglot
  persistence, NoSQL and relational databases are now commonly used
  together in a single application.

And indeed many NoSQL systems (it's difficult to say "vendors" since many of these systems are Open Source) are scrambling (or have scrambled) to put SQL interfaces on top of their systems because so many programmers are familiar with that paradigm (and it's one which has worked well over the years).

One could write entire essays (as indeed I have in College) on NoSQL vs. the classic RDBMS, and I'll go no further down this avenue other than to urge you to read around the topic.

It's fascinating to watch the evolution of the database ecosphere - new SSD chip technology, and for me, the most interesting development is the recent focus on LSM (Log Structured Merge tree) which is widely used in NoSQL systems and also NewSQL ones. IMHO, these NewSQL systems are the way of the future and I expect to see Oracle, SQL Server &c. to adopt many of their paradigms (or embrace and extend them to coin a phrase).

Again, it's a complex technology about which reams could be written, but from here:

  At the heart of the LSM algorithm is a rolling merge process:.... LSM trees cascade data over time from smaller, higher performing (but more expensive) stores to larger less performant (and less expensive) stores.

Instead of updating in place, data is "chunked" and from here:

  Once the in-memory tree reaches a threshold size, a new in-memory tree
  is created and the old tree synced to disk. Once written to disk,
  trees are read-only, though they are merged in the background with
  other on-disk trees to reduce the cost of reads.

You mention SQLite in your question - it may surprise you to learn (it did me) that there is a Key-Value "version" of SQLite that was developed between 2012 and 2014. From here:

  Lessons learned from SQLite4 have been folded into SQLite3 which
  continues to be actively maintained and developed. This repository
  exists as an historical record. There are no plans at this time to
  resume development of SQLite4.

I'll leave the last word to Brian Aker, former Director of Architecture for MySQL who presents his amusing take on NoSQL which is available on YouTube here. I think his views can be best summed up by a cartoon which he presented during that talk:

+1 for an interesting question (which I hope won't be closed!) and welcome to the forum!


Is it possible in SQL Server to change default "order by" direction from asc to desc? This question came up in a discussion at work... given the following T-SQL statement:

select firstname, lastname from users order by lastname;

the default sort order on the order by clause is asc.

Is there a setting within SQL Server that will allow you to change the default order used when the ASC|DESC modifiers are not specified?

No, the default sort order is always ASC.


Using db2look to mimic a database? I'm in the process of migrating some databases from iso8859-1 to utf-8. One of the databases contain 1000+ tables, loads of procedures, functions, triggers, constraints etc. I would like to automate the process as much as possible since this is going to happen several times, and for several instances of the system. Ideally I would like to hand over the work to my friends Jenkins and Ansible ;-)

My plan was to generate the ddl with db2look, but it fails to generate the "objects" in correct order. I tried running both with and without the -ct flag (fails for different reasons). For example:

connect to <db>;
create table t1 (x int not null);
create unique index t1pk on t1 (x);
alter table t1 add constraint t1pk primary key (x);
connect reset;

running

db2look -d <db> -e -td @ -ct

generates the wrong order for the index and the constraint.

CREATE TABLE "DB2INST1"."T1"  (
              "X" INTEGER NOT NULL )
             IN "USERSPACE1"
             ORGANIZE BY ROW@

ALTER TABLE "DB2INST1"."T1"
    ADD CONSTRAINT "T1PK" PRIMARY KEY
            ("X")@

CREATE UNIQUE INDEX "DB2INST1"."T1PK" ON "DB2INST1"."T1"
            ("X" ASC)
            COMPRESS NO
            INCLUDE NULL KEYS ALLOW REVERSE SCANS@

Removing -ct works in this trivial example, but fails in the actual database due to other dependencies.

I imagine migrating to UTF-8 is a quite common task so I'm curios on what people have done. The two possible solutions I see is to:

a) write a parser that inspects the catalog for database
   objects and sort them topologically using dependency tables.

b) write a parser that reads the output from db2look, identify
   each object and sort them topologically using dependency tables.

Clearly there are drawbacks with both of these, am I overlooking some trivial way to migrate the databases?

EDIT: An additional observation is that as long as an index does not contain any extra attributes compared to the implicitly created index through a unique / primary key constraint a QL0605W warning is raised. If on the other hand additional attributes are specified a SQL0601N error is raised. Example:

CREATE UNIQUE INDEX X1 ON T1 (C1, C2)
COMPRESS NO
INCLUDE NULL KEYS
DISALLOW REVERSE SCANS

generates a warning SQL0605W if a similar index is created via primary key statement.

On the other hand an index like:

CREATE UNIQUE INDEX X2 ON T2 (C1, C2)
INCLUDE (C3)
CLUSTER
COMPRESS NO
INCLUDE NULL KEYS
ALLOW REVERSE SCANS

generates an error SQL0601N. I assume this is due to the INCLUDE clause, but perhaps the CLUSTER clause will cause this behavior as well.

Few years ago we migrated our DB2 from 9.7 to 10.5. We also moved the database to new hardware and implemented data compression. Because of this we decided to create the database from scratch and export and import the data.

We used db2look and db2move to do the job. However, there was no way to get the DDL created by db2look in the correct order. We had to split the generated script into different parts for creating tables, creating triggers, creating indexes, etc.

Finally we end up with the following steps:

export the existing data
create the new database
create bufferpools and tablespaces
create tables
create primary keys
create indexes
create views
load data
run reorgs and runstats
create stored procedures, user defined functions and triggers

I hope it helps, even it is not the answer you may have expected.


How to deploy install of MS SQL Server 2017 Developer Edition via command-line script - not working I'm trying to deploy MS SQL Server 2017 Developer Edition via command-line script from a server share that holds the install media onto Windows 10 1803 (June release). However, I'm not having much luck. I'm pushing the script to the individual clients from a KACE "KBox" K1000 running the latest version (9.0.270). The installer fails at the get-go because it thinks I'm trying to install a feature not supported by the version of SQL Server. However, I got my information from Microsoft. I'm at a loss because I can't find documentation on the errors that are being reported.

Here's a snip of the Output Log generated at run-time on the "KBox":

I tried removing all references to AS and IS in the command statement, so that it would read:

\\AppServerBox\SQLServer2017Media\Developer_ENU\SETUP.EXE /QS
/IACCEPTSQLSERVERLICENSETERMS /ACTION=Install /ENU=True
/FEATURES=SQL,Tools /UpdateEnabled=0 /INSTANCENAME=MSSQLSERVER
/SQLSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE"
/SQLSVCSTARTUPTYPE=Automatic /SQLSYSADMINACCOUNTS="\Everyone"
/AGTSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE"
/AGTSVCSTARTUPTYPE=Automatic /SECURITYMODE=SQL /SAPWD="password"

and ran it in an elevated command prompt, directly on the client, and got this error again:

Some documentation said that Java JRE [1.7 or 1.8] is necessary, so I installed 1.8. This didn't seem to help. I keep getting the same error. Hopefully, someone can help resolve this issue before it's too late. Classes will be starting soon.

In the error is states the reason which seems fairly self-explanatory:

  You have selected a feature that is not supported for Windows Server Core... [Features Supported]

One of your input arguments is SQL which translates into:

SQL Engine
Replication
Fulltext
DQ
Polybase
Advanced Analytics (R)
R Packages

Instead of putting SQL in the features list, only put what you need. If what you need isn't supported on Windows Core, then don't install on Windows core.