Strategy to find the unique bivariate polynomials I have table that stores Tutte polynomials of a large set of graphs. These are bivariate polynomials with integer coefficients. An example of a polynomial taken from Wikipedia is:

Since the polynomial may be "sparse", I've saved the data for each graph in an SQLITE table as:

CREATE TABLE IF NOT EXISTS tutte_polynomial(
    graph_id   INTEGER,
    x_degree   UNSIGNED INTEGER,
    y_degree   UNSIGNED INTEGER,
    coeff      INTEGER
);

What I'd like to do now is determine the number of unique polynomials over the graph_id. When my database was small, I used a python script to store each polynomial as a tuple in a dictionary - this solution runs out of memory for larger databases.

The number of polynomials is the number of distinct graph_id values.
(You probably have a separate table for graphs; in this case, the SELECT DISTINCT subqueries can be replaced with SELECT graph_id FROM graph.)

To count unique polynomials, we exclude any that are duplicates.

A polynomial is a duplicate if there exists any other polynomial with a smaller ID (the smallest ID would be the non-duplicate) and with the same coefficients.

Two polynomials have the same coefficients if there are not any differences in the possible x/y/coefficient combinations for both, i.e., for each row of one polynomial, the same row must exist for the other polynomial.
In other words, there must not exist any row that does not have a match for the other polynomial.

And now that we have the description in the language of set theory, we can translate it directly into SQL.
(I've used compound SELECTs for the innermost comparisons to avoid yet another level of negated subquery lookups.)

SELECT COUNT(*)
FROM (SELECT DISTINCT graph_id
      FROM tutte_polynomial) AS a
WHERE NOT EXISTS (SELECT 1
                  FROM (SELECT DISTINCT graph_id
                        FROM tutte_polynomial) AS b
                  WHERE b.graph_id < a.graph_id
                    AND NOT EXISTS (SELECT x_degree, y_degree, coeff
                                    FROM tutte_polynomial
                                    WHERE graph_id = a.graph_id
                                    EXCEPT
                                    SELECT x_degree, y_degree, coeff
                                    FROM tutte_polynomial
                                    WHERE graph_id = b.graph_id)
                    AND NOT EXISTS (SELECT x_degree, y_degree, coeff
                                    FROM tutte_polynomial
                                    WHERE graph_id = b.graph_id
                                    EXCEPT
                                    SELECT x_degree, y_degree, coeff
                                    FROM tutte_polynomial
                                    WHERE graph_id = a.graph_id))

If computing the count dynamically is too slow, you could try to create a temporary table that contains the data in a format that is easier to count:

CREATE TABLE polys_as_string AS
SELECT group_concat(data)
FROM (SELECT graph_id,
             x_degree || '|' || y_degree || '|' coeff AS data
      FROM tutte_polynomial
      ORDER BY graph_id, x_degree, y_degree)
GROUP BY graph_id;

CREATE INDEX polys_as_string_index on polys_as_string(data);

SELECT COUNT(DISTINCT data) FROM polys_as_string;


Postgresql, write archive file over mount, secure against simultaneous access? I am using Postgres 9.2.1 and am saving my archivable WAL over a NFS share.

I just use the basic command, given as an example in the postgresql.conf

test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f

Does the cp command somehow secure that my backup server, which reads the WAL archives, doesn't read half copied WAL files? Do I have to manually define a cp command that lets the file end with .tmp and then call rename afterwards to give it its proper name?

Has someone got a example of an save archive_command?

Neither NFS nor cp will protect you from partial write. The usual way of mitigating problems with write atomicity is to use a temporary file and a mv after copying (or writing) is complete.  You might use something like:

    test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/tmp/%f && \
    mv /mnt/server/archivedir/tmp/%f /mnt/server/archivedir/%f


MySQL Replication Between Three Hosts? We have a need to move our Moodle server to a different host, and our current host runs a LAMP stack that likely runs on a VM.  It is accessed most of the time.  In the event that the current host lets us take a copy of the VM, we will just take the copy of the vm and reconfigure it for our new host; but if that doesn't work out, then we will need to replicate the database since it doesn't have much downtime when users are not accessing the system.

We did something similar with our exchange server when we cut it from a local exchange server to Office 365; just replicated it until everything was working smoothly and then cut over the DNS records when everything was working correctly.

If we do the replication of the MySQL database, it may require us to replicate the changes between three database servers, since the one it is now hosted on only opens the database port to our local network, and therefore the two sites could not communicate directly; so we'll likely need a third-party to copy them through, probably a MySQL server on the local network.

Does this sound feasible or necessary?

It is feasible (you can chain-replicate more than 2 servers), but unnecessary: you can forward access using a VPN or, if you do not want to set that up, with simple SSH port forwarding.

This applies even if you have a Windows server -you can install OpenSSH or any other SSH software or application that allows you to forward ports. SSH is the standard secure tool on unix.


Set indexes for database used for login and register actions I am thinking on how to set indexes to improve performance of a database for register and login actions.I have seen that alphabetical indexes are not good so I don't know how to do...Can you help me?In this type of databases what are the methods to implement performance?

I think you don't need to have any indexes (Rather than primary key) for user table which is mainly used for authentication, because most of the fields in user table has unique rows (correct me if i am wrong), so Cardinality is very high. If you index high cardinality field it might be a performance hit. You can go through this article for more information about cardinality and indexing


MySQL queries to some tables in information_schema kill all connections to server Running this query:

SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS

kills all connections to the server.

If I add a condition like where table_name = 'some table' it does not.

MySQL command line client gives this error:

ERROR 2013 (HY000): Lost connection to MySQL server during query

A jdbc-connected client gives this:

The last packet successfully received from the server was 766 milliseconds ago.  The last packet sent successfully to the server was 724 milliseconds ago.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1121)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3603)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3492)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4043)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2809)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2758)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:894)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:732)
    at \\.\\.\\हिñçêČάй語简�?한\\.mꆯᜫꋱꐚassert.execute(Unknown Source)
    at \\.\\.\\हिñçêČάй語简�?한\\.lꑰꋘꏇᠣpublicbreak.a(Unknown Source)
    ... 1 more
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3052)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3503)
    ... 11 more

Things I've tried that I thought sounded relevant based on other people's questions that seemed similar:

grant all on . / flush privileges / restart
installing a newer patch level of mysql
repairing the table (it's more than just this one, but trying to repair gives the same error)

Any ideas on how to fix this?

It turns out there was some corrupt index.

First, I queried my server to find the log file location: show variables like 'log_error'

Then I tail -f /path/to/error_log and ran the offending query again.

It shows up in the logs like this:

2017-07-12T18:12:26.860805Z 3 [ERROR] InnoDB: Flag mismatch in page [page id: space=124998, page number=130] index `index_some_table_on_some_column` of table `some_database`.`some_table`
2017-07-12 13:12:26 0x700000d51000  InnoDB: Assertion failure in thread 123145316274176 in file btr0btr.cc line 173
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
18:12:26 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=402653184
read_buffer_size=2097152
max_used_connections=1
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1013751 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7fa999103c00
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 700000d50e90 thread_stack 0x40000
0   mysqld                              0x00000001061493e7 my_print_stacktrace + 61
1   mysqld                              0x00000001060c9965 handle_fatal_signal + 690
2   libsystem_platform.dylib            0x00007fff8caff52a _sigtramp + 26
3   ???                                 0x0000700000d48820 0x0 + 123145316239392
4   libsystem_c.dylib                   0x00007fff90ebc6df abort + 129
5   mysqld                              0x000000010632df11 _Z23ut_dbg_assertion_failedPKcS0_m + 161
6   mysqld                              0x0000000106171693 _ZL22btr_root_fseg_validatePKhm + 0
7   mysqld                              0x0000000106171f86 _Z12btr_get_sizeP12dict_index_tmP5mtr_t + 51
8   mysqld                              0x00000001061d2467 _ZL24dict_stats_analyze_indexP12dict_index_t + 305
9   mysqld                              0x00000001061d41a9 _Z17dict_stats_updateP12dict_table_t23dict_stats_upd_option_t + 558
10  mysqld                              0x00000001061d45cf _Z17dict_stats_updateP12dict_table_t23dict_stats_upd_option_t + 1620
11  mysqld                              0x0000000106227518 _ZN11ha_innobase4openEPKcij + 1236
12  mysqld                              0x0000000105b89394 _ZN7handler7ha_openEP5TABLEPKcii + 48
13  mysqld                              0x000000010608dc6d _Z21open_table_from_shareP3THDP11TABLE_SHAREPKcjjjP5TABLEb + 3442
14  mysqld                              0x0000000105f9f886 _Z10open_tableP3THDP10TABLE_LISTP18Open_table_context + 3292
15  mysqld                              0x0000000105fa1c7b _Z11open_tablesP3THDPP10TABLE_LISTPjjP19Prelocking_strategy + 1185
16  mysqld                              0x0000000105fa2d9c _Z21open_tables_for_queryP3THDP10TABLE_LISTj + 69
17  mysqld                              0x000000010603a570 _ZL25fill_schema_table_by_openP3THDP11st_mem_rootbP5TABLEP15st_schema_tableP19st_mysql_lex_stringS8_P18Open_tables_backupb + 603
18  mysqld                              0x000000010603a202 _Z14get_all_tablesP3THDP10TABLE_LISTP4Item + 4161
19  mysqld                              0x0000000106040c2a _Z24get_schema_tables_resultP4JOIN23enum_schema_table_state + 721
20  mysqld                              0x000000010602a583 _ZN4JOIN14prepare_resultEv + 129
21  mysqld                              0x0000000105fc1a95 _ZN4JOIN4execEv + 229
22  mysqld                              0x000000010602a0de _Z12handle_queryP3THDP3LEXP12Query_resultyy + 558
23  mysqld                              0x0000000105ffe9cb _ZL21execute_sqlcom_selectP3THDP10TABLE_LIST + 855
24  mysqld                              0x0000000105ff94f7 _Z21mysql_execute_commandP3THDb + 1959
25  mysqld                              0x0000000105ff8361 _Z11mysql_parseP3THDP12Parser_state + 842
26  mysqld                              0x0000000105ff7291 _Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command + 5090
27  mysqld                              0x0000000105ff7e18 _Z10do_commandP3THD + 414
28  mysqld                              0x00000001060b0941 handle_connection + 432
29  mysqld                              0x000000010638013c pfs_spawn_thread + 308
30  libsystem_pthread.dylib             0x00007fff8e60a99d _pthread_body + 131
31  libsystem_pthread.dylib             0x00007fff8e60a91a _pthread_body + 0
32  libsystem_pthread.dylib             0x00007fff8e608351 thread_start + 13

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fa99912ea30): SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS
Connection ID (thread ID): 3
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

In the interest of time, and because this happened to be an old database whose data could not have changed since the last backup I had, I just reimported its data, and that solved the issue.


Auto cleanup querystore not working We have a SQL Server 2016 SP1 CU7 Enterprise server where we enabled the query store on a database. This is a highly used database.The Max Size has been set to 200MB and Stale Query Threshold is 1 day.
I've used sys.database_query_store_options to check the properties of the query store and everything works perfect as long as the current_storage_size_mb stays under the 200MB.
I've set Size Based Cleanup Mode to Auto but no cleanup happens, normally a cleanup should be triggered when the query store is 90% full. What happens  next is that the query store goes into readonly mode because there is no available space left.
I've tried changing Query Store Capture Mode to Auto but nothings changes.If I then change Stale Query Threshold to 30 days the actual_state_desc changes to READ_WRITE and the query store starts capturing again. The current_storage_size_mb just becomes higher than the max_storage_size_mb and after a while the actual_state_desc goes back to READ_ONLY.

Can anybody explain this behavior?

A few days ago I updated the server to SQL Server 2016 SP2 and the problem disappeared. Now when the querystore hits 90% it is cleaned up till it reaches 80%.

I don't know if SP2 was the solution or was it the restart of the SQL Service due to the update.


Not able to Create Database or Restore Database on the Azure SQL Managed Instance I am trying to restore database on the Azure SQL Managed Instance using Azure blob container URL.

Reference

CREATE CREDENTIAL [https://zzdevbackup.blob.core.windows.net/dbbackup]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE'
, SECRET = 'sv=2017-11-09&ss=b&srt=sco&sp=rwdlacg=XzzcvDrhcmdldnfQ9Opu4M4Kqq1MT3tsxOADiRCQBf%2BXo%3D'

RESTORE FILELISTONLY FROM URL =
   'https://zzdevbackup.blob.core.windows.net/dbbackup/Azure_Data_Sync_Test.bak'

RESTORE DATABASE [Azure_Data_Sync_Test] FROM URL = 'https://zzdevbackup.blob.core.windows.net/dbbackup/Azure_Data_Sync_Test.bak'

I am getting the below error

  Msg 3201, Level 16, State 2, Line 7
  Cannot open backup device ‘https://ZZZdevbackup.blob.core.windows.net/dbbackup/Azure_Data_Test.bak’. Operating system error 2(The system cannot find the file specified.).

Please help me to figure out this issue.

Please make sure you created the storage account as shown below:

You need to use Classic and Standard.


In which timezone does psql display "timestamp without timezone"? What I mean is the following: if I create a timestamp without timezone (timestamp) column and store there some value, internally microseconds are stored, but when issuing select this is converted to a string with date and time. But in which timezone the converted time is?
I tried to change timezone in my computer (PostgreSQL is running locally, so I reopened psql), tried changing it in postgresql.conf (restarting the service) and setting timezone per session. However as far as I saw, the output doesn't change. I'm sure I'm missing something. Thanks!
Sample code:
create table tzo (wotz timestamp);
insert into tzo values (now());
table tzo;

Output:
            wotz
----------------------------
 2020-08-11 17:51:37.244901
(1 row)

timestamp without time zone is time zone agnostic, so it is not adjusted to any time zone on display. This data type just doesn't have a time zone.
You can convert it to a certain time zone with AT TIME ZONE:
SHOW timezone;

   TimeZone
---------------
 Europe/Vienna
(1 row)

SELECT TIMESTAMP WITHOUT TIME ZONE '2020-08-01 12:00:00' AT TIME ZONE 'UTC';

        timezone
------------------------
 2020-08-01 14:00:00+02
(1 row)

Then the timestamp is interpreted as Viennese time, converted to UTC and displayed in my local Viennese time zone, which is offset two hours.


About schema design of NoSQL DB.(How to query multiple data?) I just got a requirement, to build a web only with Redis.

Data of the web is something like this(I write as RDBMS):
User Info:
user_id,first_name,last_name,age,.....
Address:
address_id,city,state,country,....

There is no problem at all if only write one user or get one user with "user_id".

But, what should I do if want to get all user younger than 20, or all user live in New York?

I can save all user_id under a key 'New York', but that's ugly isn't it.
If want to add a new condition, that need scan all data to build the index.

And if the condition become, live in 'New York' and younger than 20, well....

Is there have some amazing way to do that with Redis?

I can save all user_id under a key 'New York', but that's ugly isn't it.

No, it isn't ugly and it's very much what any RDBMS does when it maintains an index. The difference with Redis is that it doesn't do that for you so you'll have to do these keys' management in the code.

  But, what should I do if want to get all user younger than 20, or all user live in New York?

Consider using Redis' Sets and Sorted Sets for storing these attributes. Redis provides several set operations (union, intersect...) that make building these conditions easier.

  Is there have some amazing way to do that with Redis?

That's pretty much it. It's always about a trade off - choose a full-fledged RDBMS for the comfort and you'll pay with performance, with Redis it's the other way around :) That said, it's not that bad once you "repress" some of your currently-dominant relational database instincts.

Another viable approach that may fit your requirements is to use an object mapper to abstract some of the underlying Redis details. Depending on your choice of programming language, there are quite a few out that do a lot of the work for you (at the expense of a little performance of course ;)).


MySQL - Binary Log File size growing more than the maximum limit I have a MySQL 5.7 running on a Centos 6. I enabled Binary Logging and didn't provide any custom value for the maximum size of Binary Log in my configuration file. By default, the parameter max_binlog_size = 1073741824 which is equivalent to 1GB. This means, once a binary log reaches the size of 1GB, a new file should be created.

I observed in my server that all the binary log files are of different sizes with lot of contrast like 200GB, 165GB, 4GB etc...

Why does this happen when the maximum size is 1GB for a binary log file? Is it necessary to mention even the default maximum size explicitly in the configuration file?

The reason your binary logs are so gigantic is simple: YOU ARE COMMITTING TOO MUCH DATA PER SINGLE TRANSACTION !!! Why is this the case ? According to the MySQL Documentation on max_binlog_size:

A transaction is written in one chunk to the binary log, so it is never split between several binary logs. Therefore, if you have big transactions, you might see binary log files larger than max_binlog_size.

SUGGESTION
Please try to commit data in smaller chunks.


Re-adding constraint after ALTER fails on row that exists I'm getting a failure trying to add a constraint that previously existed on a table after having dropped it to do an alter:

mcqueen=# ALTER TABLE ONLY public.mcqueen_base_imagemeta2
mcqueen-#     ADD CONSTRAINT mcqueen_base_imageme_image_id_616fe89c_fk_mcqueen_b FOREIGN KEY (image_id) REFERENCES public.mcqueen_base_image(id) DEFERRABLE INITIALLY DEFERRED;
ERROR:  insert or update on table "mcqueen_base_imagemeta2" violates foreign key constraint "mcqueen_base_imageme_image_id_616fe89c_fk_mcqueen_b"
DETAIL:  Key (image_id)=(5648463223) is not present in table "mcqueen_base_image".

I can select that image_id in the both referenced tables and the rows do exist.

I tried changing the PK on the row in mcqueen_base_image to a difference one and back and even deleting and re-adding the row, but neither worked. I finally deleted the row, but then failed on another. Since this table has some 7 billion rows, waiting 2 hours for each failure and deleting that row is not a workable solution.

Is there anything I can do to "repair" this issue?

UPDATE: The root issue ended up being some corruption in our backup restore process, so the test DB was just plain corrupt, so no re-indexing or even reloading of tables helped the issue.

If you get that message although the image_id is present in mcqueen_base_image, you must be dealing with data corruption.

Try to

REINDEX mcqueen_base_image;

and see if the problem persists.

If there are still problems, you'll have to repair the data manually. Then

dump and restore the database to a new cluster to get rid of any lingering data corruption.
investigate the cause of the corruption.


Is there an efficient way to see the cause for "String or binary data would be truncated"? This is a follow-up on this question. It is also related to this feature request from Microsoft.

However, many years have passed and several major releases reached the market since it was reported.

Question: does SQL Server 2017 provide any mechanism to ease finding out the root cause of this error? Or it is as hard to investigate as it was about 9 years ago when the issue was reported?

There has been no change. SQL Server 2017 still offers up the same vague error message and does not provide any mechanism to discover the offending row/column.

This Connect item had over 1,600 votes when Connect was retired:

Please fix the "String or binary data would be truncated" message to give the column name

The latest comment there, from Microsoft, was this:

  Latest update - the developer working on it understands the challenges involved in creating a full fix. It may be tricky to plumb the information about columns needed to generate a full error message down to the actual conversion function in such a way that won't impact insert or update performance. We may implement something cheap in the short term such as logging the type and length of the data being truncated. It's still too early to know when such a fix would reach a publicly visible release.

The best I can suggest is to vote for that item (and comment on it, describing the business value this would add for you).


Is using the alpha version of Percona 5.6 safe? I'm really sorry if my question is kind of very basic, but is something that is really making me thinking a lot about it for a long time. As Percona has a lot of lines of optimizing codes, I run it instead of standard MySQL. I've always been running Percona 5.5 with no problems. However, I'd like to update to version 5.6 in order to get a plugin that uses fulltext search working in my InnoDB (as InnoDB supports FULLTEXT in newest MySQL 5.6 versions. I have two (2) options:

1) Remove Percona and install MySQL 5.6.10 (the standard version)

2) Install Percona 5.6 alpha version, that also will allow me to use the plugin that requires fulltext in my tables that run InnoDB.

I'd like to know from anyone who knows more than me if is it safe to use this Percona 5.6 version. Nowadays I am running Percona 5.5 in my server that runs only WordPress, and I don't want to stop using Percona, but don't want something that can make me lose any data (is it possible as Percona 5.6 is new?).

Thank you all for the attention.

Big Percona fan here.  I'm really excited about a lot of 5.6 features (although many seem to be things Percona already provided).  At the same time there's no way i'm rolling out an alpha into production and no way I'm going back to stock and loosing thing I get from percona 5.5  Largely I suppose those "things" are related to information schema (which is just for my DBA satisfaction; not performance implications) as well as knowing I have a trusted xtrabackup situation going on w/ percona builds.

I'm personally waiting for Percona's 5.6 GA before even deploying to QA for evaluation.

As for the fulltext feature you're wanting I'm of the opinion search like that is not the role of a relational database and should be delegated to a lucene solution such as an in house elastic search install, or searchify for a cloud based solution if you don't have the resources to manage your own search service.

My opinion on your implementation isn't really an answer though, so to get to that more directly: Do not deploy alphas to production.  Alpha is the disclaimer saying "play around if you like but we're not sure we trust what we've built yet ourselves"


SQL server Service Accounts permission I will be changing the existing SQL Server Service accounts from the default to Domain accounts from the SSCM which i believe will set all the required permission. One thing I am not sure whether the service account needs to be added as database logins and if so what are the minimum permission needed.

If you're changing the SQL Server Agent Service, make sure you observe this orientation about SQL Server Role Membership from the doc:

  The account that the SQL Server Agent service runs as must be a member
  of the following SQL Server roles:

  The account must be a member of the sysadmin fixed server role.
  To use multiserver job processing, the account must be a member of    the msdb database role TargetServersRole on the master server.

Also, the Configure Windows Service Accounts and Permissions doc should give you information about each SQL Server Service.

You already mentioned you're going to use SSCM to do the change, but since it could be useful to others, I believe it's worth mentioning that it's recommended to use the SQL Server Configuration Manager to Change the Service Startup Account instead of services.msc. Pinal Dave's article explain a few reasons for Why to Use SQL Server Configuration Manager Over Services applet (services.msc)?


Can set unique constraint in table have data not unique SQL Server? I have a table with has many rows, I want to set unique constraint for this table, but the column that must be unique, has data is not unique. Is any way to set unique constraint for this table for this column or unique constraint for any insert or update next time?

I can't delete data because is real

You can use a filtered unique undex to apply a uniqueness constraint to a filtered set of data. For example this: http://sqlmag.com/blog/whats-good-use-unique-filtered-index

For example you could exclude your non unique rows from the index and make it unique

But you really need to fix your data or it's going to cause you problems in future.


Is it a good practice to apply Vertical Partition to a newly created model? Suppose you are modeling the ER model for a new Object Oriented Web Application.

There is a table with 20 columns, and there are going to be a lot of INSERTs populating 10 columns, and a lot of UPDATEs populating the remaning columns.

The amount of INSERT and UPDATE statements on this table may reach thounds per second.

Since there are a lot of locks occuring all the time (caused by the INSERTs and UPDATEs), there is a concern that performance may be a problem, since response time has to be a reasonable one for a Web Application.

Someone suggests that the table should be split into 2 other tables: One containing the columns that are inserted first, and the other one contaning the remaning columns, that would be updated.

Keep in mind that this would require a more complex Object-relational mapping, since the OO Model and all the documents consider it to be 1 single entity, not 2.

My questions:

Is it a good suggestion? Would you consider it? Is there a better way of doing it? is it going to help at all?

or

Is this manly a hardware problem? Should I model my application database ignoring this kind of optimization and focusing on getting the entities right? Should I just tell the hardware guys that I need better I/O?

Please let me know if any information is missing and thanks in advance.

Yes, it is a valid physical design modification to accommodate more flexible locking behavior.

If you join the two tables and expose it to your ORM with a view, I don't think the ORM will know the difference (unless it attempts to look at the view metadata like looking for primary key or something during code generation).

Your view should be updatable, and even if it isn't due to some peculiarity of updatable view limitations can always be made updatable using INSTEAD OF triggers.

And yes, I would certainly consider it.  Whether I would consider it before actually doing load testing on a single table design, I'm not sure.

Here's an example in SQL Server:

CREATE TABLE main
( ID INT IDENTITY NOT NULL
 ,DATA VARCHAR(255)
 ,CONSTRAINT PK_MAIN PRIMARY KEY CLUSTERED (ID ASC)
 );

CREATE TABLE aux
( ID INT NOT NULL
 ,MOREDATA VARCHAR(255)
 ,CONSTRAINT PK_AUX PRIMARY KEY CLUSTERED (ID ASC)
 );

GO

CREATE VIEW unified
AS
  SELECT main.*, aux.MOREDATA
  FROM main
  INNER JOIN aux
    ON aux.ID = main.ID;
GO

INSERT INTO main (DATA) VALUES ('somedata');

INSERT INTO aux (ID, MOREDATA) VALUES (SCOPE_IDENTITY(), 'some more data');

Then:

BEGIN TRANSACTION;

SELECT dm_tran_locks.request_session_id,
       dm_tran_locks.resource_database_id,
       DB_NAME(dm_tran_locks.resource_database_id) AS dbname,
       CASE
           WHEN resource_type = 'object'
               THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id)
           ELSE OBJECT_NAME(partitions.OBJECT_ID)
       END AS ObjectName,
       partitions.index_id,
       indexes.name AS index_name,
       dm_tran_locks.resource_type,
       dm_tran_locks.resource_description,
       dm_tran_locks.resource_associated_entity_id,
       dm_tran_locks.request_mode,
       dm_tran_locks.request_status
FROM sys.dm_tran_locks
LEFT JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id
LEFT JOIN sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id
WHERE resource_associated_entity_id > 0
  AND resource_database_id = DB_ID()
ORDER BY request_session_id, resource_associated_entity_id;

SELECT *
FROM unified;

UPDATE unified
SET MOREDATA = 'changed data'
WHERE ID = 1;

SELECT *
FROM unified;

SELECT dm_tran_locks.request_session_id,
       dm_tran_locks.resource_database_id,
       DB_NAME(dm_tran_locks.resource_database_id) AS dbname,
       CASE
           WHEN resource_type = 'object'
               THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id)
           ELSE OBJECT_NAME(partitions.OBJECT_ID)
       END AS ObjectName,
       partitions.index_id,
       indexes.name AS index_name,
       dm_tran_locks.resource_type,
       dm_tran_locks.resource_description,
       dm_tran_locks.resource_associated_entity_id,
       dm_tran_locks.request_mode,
       dm_tran_locks.request_status
FROM sys.dm_tran_locks
LEFT JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id
LEFT JOIN sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id
WHERE resource_associated_entity_id > 0
  AND resource_database_id = DB_ID()
ORDER BY request_session_id, resource_associated_entity_id;

COMMIT TRANSACTION;


Unknown column in 'where clause' when using an alias for a subquery in MySQL This simple query:
select exam.id as exam_id,
(select count(*) from dataset where dataset.id = exam.id) as n_dataset
from exam
where n_dataset = 0

returns the following error:
ERROR 1054 (42S22) at line 1: Unknown column 'n_dataset' in 'where clause'

Why?

Because the order of operations with the WHERE clause is applied first before the SELECT operator. So n_dataset doesn't exist yet when the WHERE clause is being applied.
You would need to use COUNT() as a window function (version of MySQL depending, you should update your tags to include the version) to get the results you're looking for.
Or you can create your COUNT() query in a CTE first, then re-join to your table later on.
But interpreting your logic further, it sounds like your actual end goal is to get all exams that don't have any correlating datasets. You can accomplish that a lot simpler with a LEFT JOIN this way:
select distinct exam.id as exam_id, 0 as n_dataset
from exam
left join dataset
    on exam.id = dataset.id
where dataset.id is null


SQL query to display names of customers who have purchased all the DVD These are the tables I have created and inserted the values accordingly:

CREATE TABLE Customer
(Customer_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 Customer_Name VARCHAR(30) NOT NULL
)

CREATE TABLE DVD
(DVD_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 DVD_Name VARCHAR(30)
)

CREATE TABLE DVD_Purchase
(DVD_Purchase_No INTEGER IDENTITY (1,1) PRIMARY KEY,
 DVD_No INTEGER NOT NULL,
 Customer_No INTEGER NOT NULL
 )

INSERT INTO Customer (Customer_Name)
VALUES('Daman')
INSERT INTO Customer (Customer_Name)
VALUES('Saif')
INSERT INTO Customer (Customer_Name)
VALUES('Gurung')
INSERT INTO Customer (Customer_Name)
VALUES('Upendra')

INSERT INTO DVD (DVD_Name)
VALUES('Bleach')
INSERT INTO DVD (DVD_Name)
VALUES('Gintama')
INSERT INTO DVD (DVD_Name)
VALUES('Tokyo Ghoul')
INSERT INTO DVD (DVD_Name)
VALUES('Death Note')

INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (4,1)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,2)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (2,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (3,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (4,3)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (1,4)
INSERT INTO DVD_Purchase (DVD_No,Customer_No)
VALUES (2,4)

In order to display names of customers who have purchased all the DVD, this is the query that I have tried out:

SELECT Customer_Name
FROM Customer
WHERE
EXISTS (SELECT Customer_Name,DVD_Name
FROM Customer,DVD,DVD_Purchase
WHERE Customer.Customer_No = DVD_Purchase.Customer_No AND DVD.DVD_No = DVD_Purchase.DVD_No)

But unfortunately, after executing this query it is displaying all the customer names. But I want to display a specific customer name who have purchased all the DVD.

Can someone provide me the correct query ?

You can try something like:

select c.Customer_Name
from Customer c
join (  select Customer_No
        from DVD_Purchase
        group by Customer_No
        having count(distinct DVD_No) = (select count(*) from DVD)
        ) d on c.Customer_No = d.Customer_No

Explaination: first we need to know the number of DVDs available.

select count(*)
from DVD

Then we need to get the number of distinct DVDs purchased by the customers, and to compare it with the total we got previously.

select Customer_No
from DVD_Purchase
group by Customer_No
having count(distinct DVD_No) = <number of DVDs available>

And finally, we just need to join with customers table to retrieve the names.


Change default collation when provisioning a SQL Server VM in the Azure portal Is there a way to change the default SQL collation directly from the "Create virtual machine" wizard in Azure ? The SQL VM images from the gallery are set for SQL_Latin1_General_CP1_CI_AS.

Browsing through the wizard's SQL Server settings, there's nothing on collation. I've also searched through the parameters (listed below) in the generated templates, but again nothing on collation. Am I missing some hidden way of doing this ? Using SQL 2016 right now, but seems to be the same for other versions.

    "sqlConnectivityType": {
        "type": "string"
    },
    "sqlPortNumber": {
        "type": "int"
    },
    "sqlStorageDisksCount": {
        "type": "int"
    },
    "sqlStorageWorkloadType": {
        "type": "string"
    },
    "sqlStorageDisksConfigurationType": {
        "type": "string"
    },
    "sqlStorageStartingDeviceId": {
        "type": "int"
    },
    "sqlStorageDeploymentToken": {
        "type": "int"
    },
    "sqlAutopatchingDayOfWeek": {
        "type": "string"
    },
    "sqlAutopatchingStartHour": {
        "type": "string"
    },
    "sqlAutopatchingWindowDuration": {
        "type": "string"
    },

Later edit October 2018: Checked again and still no collation options in the wizard or template.

If you are going to utilize the images from Azure marketplace for building your SQL Server instances, I'm not aware of any options to change the server collation. Since the image has SQL Server installed already your only option will probably be to just follow the process to change the default server collation after the VM is provisioned. Which requires rebuilding the system databases. You could add this into your build so it executes the needed commands, but you would have to have the installation media available for that version of SQL Server.


Server version error where restoring backup with SQL Server Management Studio I have SSMS v17.9.1 freshly installed. I believe the only later versions as of this post's date and time are only preview versions.

I am attempting to restore a .bak file. I get the error that: The database was backed up on a server running version 12.00.452. That version is incompatible with this server, which is running version 11.00.2100

I have also installed the latest preview version and attempted restore with that. The error still says Im running version 11.00.2100 and is incompatible.

What is it that I need to install / upgrade to get my 'server' version to 12.00.452 (or 12 to be generic)?

The version of SSMS is wholly irrelevant here - the error is referring to SQL Server itself.

In this case you will need to upgrade your SQL Server from 2012 to 2014.

How can I upgrade SQL Server from 2012 to 2014?

As this is Express edition and likely not a production server, I would recommend an inplace upgrade. You will need to download the installation media for SQL 2014 Express here.

Once downloaded, run it and choose the option to Upgrade from a previous version of SQL Server and follow the Wizard from there.

I would advise to take full backups of any databases you can't lose before doing this.

Are there any other ways to do this with less downtime?

You could also perform a side-by-side install of 2014 with 2012, and then migrate the databases over by backing them up then restoring them.

Please note this could impact how people connect to this instance - you can't create the new instance with the same name as the old instance, so if this is how users are connecting you may need to set up new connection strings.

As an aside the version of SQL 2014 you are running has several known issues and security problems - it's 2014 SP1. There have been 2 SP's released since then - I would recommend you speak to whoever is in charge of this box and look to get SQL server upgraded to the latest SP if at all possible.


How to start Neo4j on Azure I'm running Neo4j v4.x on an Azure VM (Linux Ubunto 16.04). Everything was running fine until my project required a resizing for more storage. This seemingly went well as I proceeded to load data, but then a glitch of unknown type caused the Neo4j server to stop. I could no longer access it from the Neo4j broweser or from python code that queries it. I've restarted the VM. No effect. Even redeployed the VM without effect. From Putty:
neo4j status
Neo4j is not running

I've tried numerous commands, but this one comes closest to trying to do something:
sudo ssh {myusername}@{vm ip} "systemctl restart neo4j V4_0.service"

It asked for my password and accepted it (rejected errors) and then gave me

Failed to restart neo4j.service: Interactive authentication required.
See system logs and 'systemctl status neo4j.service' for details.
Failed to restart V4_0.service: Interactive authentication required.
See system logs and 'systemctl status V4_0.service' for details.

I thought I'd authenicated. In debugging, i tried entering bad username or password and was immediately rejected as expected.
Another set of info generated by the command
root@neo4jVM:/etc/init.d# neo4j start

result:

Directories in use:
home:         /var/lib/neo4j
config:  /etc/neo4j
logs:         /var/log/neo4j
plugins:   /var/lib/neo4 j/plugins
import:       /var/lib/neo4j/import
data:   /var/lib/neo4j/data
certificates: /var/lib/neo4j/certificates
run: /var/run/neo4j Starting Neo4j.
WARNING: Max 1024 open files allowed,minimum of 40000 recommended. See the Neo4j manual. Started neo4j (pid> 71995). It is available at http://0.0.0.0:7474/ There may be a shortdelay until the server is ready. See /var/log/neo4j/neo4j.log for current status.

Despite the promising message, it never started and Http://0.0.0.0:7474 did not connect.
for clarification, I tried variations on the one command in Putty: sudo ssh dastumpf@104.43.228.191 "systemctl restart neo4j" sudo ssh dastumpf@104.43.228.191 "systemctl restart neo4j.service" etc.
Exploring the Neo4j log, I found this ....LifecycleManagingDatabaseService@78116659' was successfully initialized, but failed to start.
What am I missing?

The problem has been solved with help from Neo4j support. Hopefully they will fix the bug so you will not face this issue. For some reason one line was not in the template used to install the Neo4j VM.
The problem was there was not a listener for all the Neo4j ports in the neo4j.conf file. Make sure they are there for all three ports (7473, 7474 and 7687). The format is:
dbms.connector.bolt.listen_address=0.0.0.0:7687

You might also need a default listener:
dbms.default_listen_address=0.0.0.0

Other insights learned in debugging this:

When you restart the VM, it may take several minutes for the Neo4j
Browser and python code capabilities to recycle and come up.
In Putty, the command "neo4j status" will return "neo4j is not running"
even when it is.


SA permissions issues with many nested objects I have a broker application that's relatively complicated.

Today, after I made some changes, I started getting the error:

  The server principal 'sa' is not able to access the database 'XYZ' under the current security context.

The whole scenario up to the point of the error is:

(In Database ABC)

A broker message gets submitted into a queue
The queue reader picks it up
The queue reader updates a table to indicate the work is starting
This table has an update trigger on it.  The trigger

Checks a table in database XYZ to make sure the inserted value for this field is valid
The table is accessed via a synonym

The check in the trigger I believe is what is causing the issue.

If I run the update manually, it works fine.  I have also used EXECUTE AS to run the update manually as sa which works fine.

Other relevant facts:

sa is the owner of both database ABC and database XYZ
there's no funny business with the sa account - it's db_owner role in both DBs as well

Is there some sort of strange scoping happening because all this is running in the context of broker?

Updates

Some more info:

DB ownership chaining is on at the server level but not in the DBs.  I turned it on and it made no difference.
Using a three part name instead of a synonym didn't make a difference
Profiler trace shows that the statements are running as SA and not another login
Setting both databases TRUSTWORTHY to ON didn't make a difference
If I run the queue activation proc manually, it processes correctly (under my credentials).

Can tell you solution I came up with in situation like yours (its quite complicated, but still love it):

I have database DB1 with Service Broker an DB2 where data has to be updated as well (I do have triggers in DB2 as well).

I am using certificate to create server login + users in each db. I am signing procedures and triggers to Grant rights. You can read everything you need here.

-- Master DB:
CREATE CERTIFICATE MyCertificate
WITH SUBJECT = 'Example'
Go
BACKUP CERTIFICATE MyCertificate TO FILE = 'c:\Cert.cer'
    WITH PRIVATE KEY (
    FILE = 'c:\CertKey.key',
    ENCRYPTION BY PASSWORD = 'pwd');
Go
Create Login CertLogin From Certificate MyCertificate

-- DB1:
CREATE CERTIFICATE MyCertificate
FROM FILE = N'c:\Cert.cer'
WITH PRIVATE KEY
    (
        FILE = N'c:\CertKey.key',
        DECRYPTION BY PASSWORD = N'pwd'
    );

Create User CertUser From Login CertLogin;

-- DB2:
CREATE CERTIFICATE MyCertificate
FROM FILE = N'c:\Cert.cer'
WITH PRIVATE KEY
    (
        FILE = N'c:\CertKey.key',
        DECRYPTION BY PASSWORD = N'pwd'
    );
Go
CREATE USER [CertUser] FROM Login CertLogin;

To grant rights (In my case no need for Select/Insert/Update/Delete rights because all tables and procedures in DB2 is in the same schema. So ownership chaining in DB2):

-- DB1:
Grant Exec On dbo.usp_MyProc_DB1 To SomeUser -- User that calls procedure
ADD SIGNATURE TO dbo.usp_MyProc_DB1 BY CERTIFICATE MyCertificate;
-- DB2:
ADD COUNTER SIGNATURE TO dbo.usp_MyProc_DB2 BY CERTIFICATE MyCertificate;
ADD COUNTER SIGNATURE TO tr_MyTrigger_DB2 BY CERTIFICATE MyCertificate;
Grant Exec On usp_CheckPatientDetailedRole To CertUser;


Does CREATE TABLE AS lock the table? In Postgres, does this query lock films for reads and/or writes?

CREATE TABLE films2 AS
  TABLE films;

No it does not create a read or write lock on films table as this creates ACCESS SHARE LOCK;

to test this  open two sessions

in session one  run this command

  begin;
  create table films2 as
     table films;

then in the second session run this command

Select * from films;
Update films set something = 0

If you need to lock films table you have to issue a Lock like so

begin ;
  Lock table films in exclusive mode;
  create table films2 as
     table films;
commit;

this will block the table from updates but not reads...

NOTE  Access Share Locks block any transaction that try to acquire an ACCESS EXCLUSIVE locks such as DROP Films etc...


Designing database containing code metadata I'm working on a program that disassembles programs and analyses the results.  I decided to use SQLite to store the data, but I'm having trouble deciding on how to organize the data.

The design I've thought of so far involves placing the code data (comment, line number, opcode, etc) in one table, and then placing data about the code like symbolic names for memory addresses in other separate tables.  There could potentially be millions of lines of code, so I might need to handle millions of columns in the disassembly table.

The program using this database will be a simple text view (for now).  It will fetch enough lines of code from the code table to fit the screen, and the program will look up data about the code from the other tables.  Say that I have a memory address referenced at some point.  My program will recognize that, and look it up in the symbol name table to see if we could find a human readable name for it.  In the future, I might also add editing for individual lines, which could invalidate data stored about a line of code.

Does this seem somewhat sane?  How else might I go about it?  Assuming this rough idea makes sense, what things should I keep in mind for making it fast?  Any suggestions or resources to look at for designing this database would be much obliged.

"Millions of columns"? Or millions of rows? For single user applications and just a few megabytes of data, sqlite can be perfectly sufficient, so it will really depend on how many lines of code/rows of data you'll be dealing with, and the complexity of the queries that you'll require. You may want to consider postgresql or mysql from the get go, to avoid getting bogged down or finding sqlite is too limited when you've invested a lot of code and time on it.


How to COUNT co-tags from a tag_map table in mysql? I have a simple tag_map table

CREATE TABLE TagMap
(
TagID mediumint(7) unsigned,
ArticleID int(11) unsigned,
FOREIGN KEY(TagID) REFERENCES Tags(TagID) ON DELETE CASCADE,
FOREIGN KEY(ArticleID) REFERENCES Articles(ArticleID) ON DELETE CASCADE,
PRIMARY KEY(TagID,ArticleID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci KEY_BLOCK_SIZE=1

I get the co-tags (tags, which appear along with a specific tag):

SELECT TagID AS TagID2,COUNT(*) FROM TagMap WHERE ArticleID IN(
SELECT ArticleID FROM TagMap WHERE TagID=1 // This is TagID1
)
GROUP BY TagID

How can I do this query for all tags to get

TagID1,TagID2,COUNT(*)

The table is huge (10-50M rows) and each article has tens of tags. Thus, performance is critical.

SELECT t1.TagID AS TagID1
    ,t2.TagID AS TagID2
    ,COUNT(1)
FROM TagMap AS t1
JOIN TagMap AS t2 ON t1.ArticleID = t2.ArticleID AND t1.TagID <> t2.TagID
GROUP BY t1.TagID, t2.TagID

UPDATED: index on ArticleID may help to improve query performance


How to Convert List of values datatype in oracle to equivalent or valid datatype in sql server? I want to recreate the table below in  SQL server I have tried using SSIS but it does not know how to handle the lOV Data type how can I recreate this table in SQL server and migrate the data to it.

create Table Listing
(
 NAME                    VARCHAR2(30),
 VALUES                  NUMBER,
 LIST_OF_VALUES          LOV,
 id                      VARCHAR2(30)
);

I am new at trying to converting this type of data or data type, in general, your help would be appreciated.

User Defined Types (UDT) in Oracle

Many 3rd party software cannot handle UDTs.  These include Oracle APEX, and SSIS.

This is only one, of many, problems with using a UDT for a Column's data type.

MS SQL

I don't know if MS-SQL has the same problem with UDTs.  I'm guessing "yes".

I'm guessing LOV is a TABLE OF ... data type.  If so, create a child table of the type's attributes along with a foreign key to the Listing table.

SSIS

To enable SSIS to see the data, I recommend you CREATE VIEW.

create or replace
view Listing_LOVs
as
select a.id, b.attribute_1, b.attribute_2, ...
from Listing a, a.List_of_values b;

Then:

Transfer all Listing columns (except List_of_Values) to the MS-SQL Listing table.
Transfer all the Listing_LOVs values the the child table.

XML Version

Per Laughing Vergil's comment:  XML Document could be another method.

create or replace
view Listing_VIEW
as
select a.id, a.values, a.id
  ,SYS_XMLGEN( a.List_of_values).GetCLOBVal() List_of_Values_XML
from Listing a;

The column List_of_Values_XML will appear as a CLOB to 3rd party apps (eg SSIS)


why MyISAM uses 3 files per table? Why MyISAM uses 3 files per table?

I know one of the file contains table definition, another contains primary key and all other indexes, third the data.

What is unclear for me, what is the benefit to keep the data in 3 files, instead in one, or in many - every index in separate file like dbase.

As Phil already said 'It was a design decision'. This is the main difference between InnoDB Engine. Because InnoDB Engine is store all databases in one file. And each MyISAM table is stored on disk in three files (if it is not partitioned). The files have names that begin with the table name and have an extension to indicate the file type. MySQL uses a .frm file to store the definition of the table, but this file is not a part of the MyISAM engine; instead it is a part of the server.
The data file has a .MYD (MYData) extension. The index file has a .MYI (MYIndex) extension.

for your ref https://en.wikipedia.org/wiki/MyISAM

Even RolandoMySQLDBA and ypercube has well defined InnoDB Engine with respect to MyISAM engine Why does InnoDB store all databases in one file?


how to optimize grouped select from an already grouped select query I already had a specific query to get a number of shops, but I couldn't sum the cost field, I kept on getting an error on the sum function, but it worked when I did this;

select tab.shop_id, tab.shop_name, tab.payment_schedule, SUM(tab.num_clicks)
as num_clicks, SUM(tab.cost) as cost
from (select c.shop_id as shop_id, s.shop_name as shop_name,
      s.pay_type as payment_schedule,
      count(c.id) as num_clicks,
      count(c.id)*click_value as cost
      FROM Clicktracker c
        INNER JOIN Shop AS s ON c.shop_id=s.shop_id
      where c.domain='mobi.mysite.org'
      GROUP BY s.shop_id, click_value) as tab
GROUP BY tab.shop_id;

I'm not really good at all at sql; Is there a better way to write this query?

descr of shop table;

shop_id     int(10)
shop_name   varchar(20)
pay_type    float(11,2)

description of the clicktracker table;

id              int(10)
shop_id         int(10)
product_name    varchar(384)
shop_name       varchar(384)
price           float(11,2)
domain          varchar(255)
click_value     float(11,2)

to say the least, that is the desc of the tables (took me a lot of editting)

Simpler, just remove the click_value from the inner group and SUM it. No need for the outer sum:

SELECT s.shop_id as shop_id, s.shop_name as shop_name,
      s.pay_type as payment_schedule,
      res.num_clicks, res.cost
FROM Shop AS s
  INNER JOIN (
      SELECT c.shop_id
            count(c.id) as num_clicks,
            SUM(click_value) as cost
      FROM Clicktracker c
        INNER JOIN Shop AS s ON c.shop_id=s.shop_id
      WHERE c.domain='mobi.mysite.org'
      GROUP BY s.shop_id, c.domain
      ) AS res ON s.shop_id=res.shop_id

just something extra, you have "shop_name" and id on the Shop table. Check if you really need the shop_name in your clicktracker table or if it is redundant.


Restoring database from a server that uses data compression In our environment our production server is using MS SQL Server 2008 Enterprise Edition.  We have a development and test server that runs MS SQL Server Standard Edition and the table structure between all three environments is exactly the same.

We frequently refresh our dev and test environments from our production database.

I'm looking into turning on data compression in our production environment, but reading this technet article it sounds like we will no longer be able to perform these refreshes.  Am I reading this correctly?

Quote from article:

Databases with compressed tables or indexes cannot be restored, attached, or in any way used on other editions.

Will I be forced to upgrade our dev/test DB to Enterprise Edition to continue to refresh our data?

Correct, an error will be raised when attempting to RESTORE.

Same thing goes if other Enterprise features, such as table partitioning, are used. For those finding this answer in search, usage of Enterprise features are listed in sys.dm_db_persisted_sku_features.

What I would recommend is to use Developer Edition in your development and testing environments, if you don't support Standard in production (it sounds like your app is in-house only). This edition includes the Enterprise engine; it just isn't licenced to be used in production, and will be able to restore the Enterprise backups just fine.


Inserting BLOBs through PHP are always 0 bytes I'm making a site that allows users to upload small thumbnails for books they post. I want to store them as a BLOB.

The first row there was inserted manually through phpMyAdmin. The second row was uploaded through a PHP script. The two string values are evidently going through, but the blob I'm uploading isn't.

error_reporting(E_ALL);
ini_set("display_errors", 1);

include './getConnection.php';
include './getTokenData.php';

$conn = getConnection();

$image = addslashes(file_get_contents($_FILES['pic']['tmp_name']));
$name = $_POST['name'];
$desc = $_POST['desc'];

$SQL = "INSERT INTO  `db_bookclub`.`t_book` (
`c_name` ,
`c_description`,
`c_image`
)
VALUES (
?,?,?
);
";

echo $image;

$stmt = $conn->prepare($SQL);
$stmt->bind_param('ssb', $name, $desc, $image);
$stmt->execute();

When I echo $image, the image data (a whole bunch of characters) are sent to to the client, showing that the image is making it to the PHP script unmolested.

I have a feeling this is because of a settings file I haven't heard of. I've also tried mysql_real_escape_string in place of addslashes.

After a lot of searching, I found the keyword, send_long_data.

$stmt = $conn->prepare($SQL);
$stmt->bind_param('ssb', $name, $desc, $image);
$stmt->send_long_data(2, $image);

$stmt->execute();

A simple fix if you know the magic word.


Does Microsoft release its SQL Query Hash Algorithm..? We use SQL Spotlight in our environment. it's pretty handy.
we use particularly the output of sys.dm_exec_requests and sys.dm_exec_query_stats.

Spotlight pulls the query plan from the plan cache for you using the hash which is nice.

Problem is, unless you're experienced with the code base, it's quite difficult to know where that query came from.

I had an idea that if I could:

Parse the codebase
Pull the SQL queries
Hash them the same way microsoft does

This way I could do quick matches of the hash to be able see where in the codebase a particular query came from.

Alternatively I'd have to do some very slow regex...

Does Microsoft release its SQL Query Hash Algorithm..?

No, Microsoft does not release the hashing algorithm. Additionally, hashing happens at a different layer than original query text - so even if you had the algorithm, you'd still need to normalize like SQL Server does, first.

  This way I could do quick matches of the hash to be able see where in the codebase a particular query came from.

This assumes there also isn't anything like dynamic sql, SMO, etc., where there isn't an actual plain text query.


Composite foreign key that is optional I have a table with a composite foreign key like so:

CREATE TABLE a
(
  a1 INTEGER,
  a2 INTEGER,
  a3 INTEGER,
  a4 INTEGER,
  -- other columns
  FOREIGN KEY (a1, a2, a3, a4) REFERENCES b
);

The foreign key may or may not exists, therefore a1 to a4 may be NULL, however if so, they should be NULL all at the same time. Is there a better way to specify such an "optional composite foreign key" than to add a (rather verbose) constraint like so?

  CHECK ((a1 IS NULL) = (a2 IS NULL) AND
         (a3 IS NULL) = (a4 IS NULL) AND
         (a1 IS NULL) = (a3 IS NULL))

You can try with:

CREATE TABLE a
(
  a1 INTEGER,
  a2 INTEGER,
  a3 INTEGER,
  a4 INTEGER,
  -- other columns
  FOREIGN KEY (a1, a2, a3, a4) REFERENCES b MATCH FULL
);

From the manual:

  A value inserted into the referencing column(s) is matched against the values of
  the referenced table and referenced columns using the given match type. There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE (which is the default). MATCH FULL will not allow one column of a multicolumn foreign key to be null unless all foreign key columns are null; if they are all null, the row is not required to have a match in the referenced table.


Guid vs INT - Which is better as a primary key? I've being reading around reasons to use or not Guid and int.

int is smaller, faster, easy to remember, keeps a chronological sequence. And as for Guid, the only advantage I found is that it is unique. In which case a Guid would be better than and int and why?

From what I've seen, int has no flaws except by the number limit, which in many cases are irrelevant.

Why exactly was Guid created? I actually think it has a purpose other than serving as primary key of a simple table. (Any example of a real application using Guid for something?)

( Guid = UniqueIdentifier ) type on SQL Server

This has been asked in Stack Overflow here and here.
Jeff's post explains a lot about pros and cons of using GUID.

###GUID Pros

Unique across every table, every database and every server
Allows easy merging of records from different databases
Allows easy distribution of databases across  multiple servers
You can generate IDs anywhere, instead of having to  roundtrip to the database, unless partial sequentiality is needed (i.e. with newsequentialid())
Most replication scenarios require GUID  columns anyway

###GUID Cons

It is a whopping 4 times larger than the traditional 4-byte index
value; this can have serious
performance and storage implications
if you're not careful
Cumbersome to debug (where userid='{BAE7DF4-DDF-3RG-5TY3E3RF456AS10}')
The generated GUIDs should be partially sequential for best
performance (eg, newsequentialid() on
SQL Server 2005+) and to enable use of
clustered indexes

If you are certain about performance and you are not planning to replicate or merge records, then use int, and set it auto increment (identity seed in SQL Server).


Mysql - How number of columns effect the database performance I have a table to store social media information for users; I have 42 columns to store and will have 100000 entries per day to store. I am planning to have MyIsam engine for it and will use horizontal partioning for the table but my questions are

should I also do vertical partioning and divide the table into 2 tables ?
If we select only 4 columns from a table in query then does the presense of other columns affect the query even if those are not in column list ?

If you have 42 columns to store and you are selecting only 4 columns, then yes the other columns can affect the query since it increases the amount of disk I/O the server uses to select the wider table.

If you primarily need the 4 columns, doing a vertical partition would give a smaller I/O overhead and could result in better performance for that query.

Likewise, a covering index for the 4 columns could also be processed with less I/O than the wider 42 column select.

Will it be faster?  It should be, but it all depends on what else is involved in the process but not explicitly mentioned in your question.


TRANSACTION ISOLATION LEVEL SNAPSHOT vs. TRUNCATE? I'm hoping someone can shed some light on this behavior that I was not expecting regarding SNAPSHOT isolation vs. TRUNCATE.

Database: Allow Snapshot Isolation = True; Is Read Committed Snapshot On = False.

Procedure1 (Replaces content of table foo from a long-running complex SELECT with lots of joins):

BEGIN TRAN;
TRUNCATE TABLE foo;
INSERT INTO foo SELECT...;
COMMIT;

Procedure2 (Reads from table foo):

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
SELECT * FROM foo;

If Procedure1 is running while Procedure2 is executed, Procedure2 is held up with a LCK_M_SCH_S wait (according to sp_WhoIsActive) until Procedure1 finishes. And when Procedure2 does complete, it raises this exception:

  Snapshot isolation transaction failed in database 'DatabaseName'
  because the object accessed by the statement has been modified by a
  DDL statement in another concurrent transaction since the start of
  this transaction. It is disallowed because the metadata is not
  versioned. A concurrent update to metadata can lead to inconsistency
  if mixed with snapshot isolation.

However, Microsoft does not list TRUNCATE as a DDL statement not permitted under SNAPSHOT isolation: http://msdn.microsoft.com/en-us/library/bb933783.aspx

Clearly I'm not understanding something correctly, as I would have expected a best case of Procedure2 immediately returning the most recently committed data from the table before the TRUNCATE or a worst case of being held up by Procedure1 and then returning the new content of the table. Can you help?

The list of 'DDL' operations listed is not comprehensive (and TRUNCATE TABLE is not the only omission from that list). Whether TRUNCATE TABLE is DML or DDL is a fraught question in SQL Server, with persuasive examples on both sides of the debate, and entries both ways in Books Online.

From the point of view of a snapshot isolation transaction, truncate has the essential quality of taking a Sch-M lock, which explains the blocking ( because RCSI and SI still acquire Sch-S locks); and it also bumps the internal metadata version (for internal reasons*) resulting in error 3961.

So, the behaviour you are seeing is expected, just not documented very well.

* The current implementation of TRUNCATE TABLE does not generate row versions. Bumping the metadata version is the simplest way to ensure correct behaviour.


Does the order of columns in a PK index matter? I have a few very large tables with the same basic strucure.  Each one has a RowNumber (bigint) and DataDate (date) column.  Data is loaded using SQLBulkImport every night, and no "new" data is ever loaded - its a historical record (SQL Standard, not Enterprise, so no partitioning).

Because each bit of data needs to be tied back to other systems, and each RowNumber/DataDate combination is unique, that is my Primary Key.

I notice that due to the way I defined the PK in SSMS Table Designer, RowNumber is listed first and DataDate second.

I also notice that my fragmentation is always VERY high ~99%.

Now, because each DataDate only appears once, I would expect the indexer to just add to the pages each day, but I wonder if it actually is indexing based on RowNumber first, and hence having to shift everything else around?

Rownumber is not an identity column, it's an int generated by an external system (sadly). It resets at the start of each DataDate.

Example Data

RowNumber | DataDate | a | b | c.....
   1      |2013-08-01| x | y | z
   2      |2013-08-01| x | y | z
...
   1      |2013-08-02| x | y | z
   2      |2013-08-02| x | y | z
...

Data is being loaded in RowNumber order, one DataDate per load.

Import process is bcp - I have tried loading to a temp table and then selecting in order from there (ORDER BY RowNumber, DataDate) but still comes out high fragmentation.

Does the order of columns in a PK index matter?

Yes it does.

By default, the primary key constraint is enforced in SQL Server by a unique clustered index. The clustered index defines the logical order of rows in the table. There may be a number of extra index pages added to represent the upper levels of the b-tree index, but the lowest (leaf) level of a clustered index is simply the logical order of the data itself.

To be clear about it, rows on a page are not necessarily physically stored in clustered index key order. There is a separate indirection structure within the page that stores a pointer to each row. This structure is sorted by the clustered index keys. Also, each page has a pointer to the previous and next page at the same level in clustered index key order.

With a clustered primary key of (RowNumber, DataDate), the rows are logically sorted first by RowNumber and then by DataDate - so all rows where RowNumber = 1 are logically grouped together, then rows where RowNumber = 2 and so on.

When you add new data (with RowNumbers from 1 to n) the new rows logically belong inside the existing pages, so SQL Server will likely have to do a lot of work splitting pages to make room. All this activity generates a lot of extra work (including logging the changes) for no gain.

Split pages also start off about 50% empty, so excessive splitting can result in low page density (fewer rows than optimal per page) as well. Not only is this bad news for reading from disk (lower density = more pages to read), the lower-density pages also take up more room in memory when cached.

Changing the clustered index to (DataDate, RowNumber) means that new data (with, presumably, higher DataDates than currently stored) is appended to the logical end of the clustered index on fresh pages. This will remove the unnecessary overheads of splitting pages and result in faster load times. Less fragmented data also means that read-ahead activity (reading pages from disk just before they are needed for an in-progress query) can be more efficient.

If nothing else, your queries are much more likely to search on DataDate than RowNumber. A clustered index on (DataDate, RowNumber) supports index seeks on DataDate (and then RowNumber). The existing arrangement only supports seeks on RowNumber (and only then, perhaps, on DataDate). You might well be able to drop the existing nonclustered index on DataDate once the primary key is changed. The clustered index will be wider than the nonclustered index it replaces, so you should test to ensure that performance remains acceptable.

When importing new data with bcp, you may get higher performance if the data within the import file is sorted by the clustered index keys (ideally (DataDate, RowNumber)) and you specify the bcp option:

-h "ORDER(DataDate,RowNumber), TABLOCK"

For best data loading performance, you might try to achieve minimally-logged inserts. For more information, see:

SQL Server Index Basics by Robert Sheldon
Effective Clustered Indexes by Michelle Ufford
Bulk Inserts Via TSQL by Robert Sheldon
Minimal Logging with INSERT…SELECT into Empty Clustered Tables by me
Minimal Logging with INSERT…SELECT and Fast Load Context by me


Should I not use camelCase in my column names? I am creating a GraphQL API using Node.js, which foces me to return all the field names in camelCase.

In my PostgreSQL database, I currently have all my columns named following a camelCase convention, but I am thinking: is that the best idea?

Should I use snake_case in the database columns and convert them in the back-end?

Use lower case in Postgres. Postgres folds identifiers to lowercase, unless you double-quote your identifiers. To make Postgres operations easier, use lower_snake. If you need to bind to an API, etc, you can create a view with “CamelCase” aliases. For example, we do this with data stored in Postgres that is pulled by Domo.


Can't we make a non-primary key or non-unique key in a table as a foreign key in another table? I have got 4 tables named Blood_Bank,Consumer,Employee and Donor.Blood_Bank has SB as primary key which is a dummy attribute.I did not take bank_id as primary key since it can be repeated.To identify each repeated values,I have added one more column called SB.I took bank_id(neither primary nor unique)as the foreign key.These are my tables:

CREATE TABLE Blood_Bank(
    SB varchar(10) PRIMARY KEY,
    bank_id varchar(10) UNIQUE,
    bank_name char(25) NOT NULL,
    bank_add varchar(50) NOT NULL,
    bank_phone varchar(12) NOT NULL,
    b_id varchar(5) NOT NULL,
    b_grp varchar(5) NOT NULL,
    b_quantity varchar(4)NOT NULL,
    b_RHfactor varchar(5)NOT NULL);

CREATE TABLE Consumer(
    c_id varchar(20)PRIMARY KEY,
    c_name char(20) NOT NULL,
    c_add char(50) NOT NULL,
    c_blood varchar(10) NOT NULL,
    c_phone varchar(12) NOT NULL,
    bank_id varchar(10) REFERENCES Blood_Bank(bank_id));

CREATE TABLE Employee(
    emp_id int PRIMARY KEY,
    emp_name varchar(30) NOT NULL,
    emp_dept varchar(30) NOT NULL,
    emp_salary varchar(30) NOT NULL,
    emp_desgn varchar(40) NOT NULL,
    bank_id varchar(10) REFERENCES Blood_Bank(bank_id));

CREATE TABLE Donor(
    d_id varchar(20)PRIMARY KEY,
    d_name char(20)NOT NULL,
    d_blood varchar(15)NOT NULL,
    d_phone varchar(20) NOT NULL,
    d_gender char(20) NOT NULL,
    d_age varchar(15) NOT NULL,
    bank_id varchar(10) REFERENCES Blood_Bank(bank_id));

But,it is giving me the following error for the tables Consumer,Employee and Donor.

  ERROR:  there is no unique constraint matching given keys for referenced table "blood_bank".Please help.

First don't use varchar(x) in PostgreSQL, just use text. Second, all of your char(x) are likely wrong. In SQL, that means store whitespace padding. This is wasteful and a bad idea. You likely do not want char(x). Also, your id columns should probably be int (if you're designing this schema new).

You just need to drop your tables and try again. Your code works fine.


I have tried to make it using GROUP BY in MYSQL, Hope this helps!

SELECT
    channelid,
    deviceid,
    pointtime,
    SUBSTRING_INDEX(GROUP_CONCAT(pointvalue ORDER BY pointid ASC SEPARATOR '^'),'^',1) AS pointid3_1,
    SUBSTRING_INDEX(GROUP_CONCAT(pointvalue ORDER BY pointid ASC SEPARATOR '^'),'^',-1) AS pointid3_11
FROM
    Think.pointhistory
GROUP BY
    pointtime,
    channelid;


GRANT permission to access to respective creator Does the following command:

GRANT SELECT
ON EVENT
TO STUDENT;

grant student access to whole EVENT table? If yes, how to only give STUDENT access to EVENT(s) record created by respective STUDENT?

NOTE: When I wrote the answer (below), I did not notice that your question is a MySQL question. Please understand that my answer is for the MS SQL Server platform. I have never used MySQL (and probably never will) so I'm not sure it the permissions model is the same. None-the-less, my "solution" would work in MySQL as long as MySQL provides functions to return the current "user" to match against the saved user information.

Records are not "objects" that can be granted (or denied or revoked) permissions within the SQL Server security system. Only database objects (i.e. tables, views, stored procedures, functions, etc.) can have permissions modified with the GRANT statement. The only way to control access to records created by a given user is to build the database in such a way as to record the "owner" of the records within the data. You could add a column to the table "creator" or some such name and have the user's login saved in the column (you could also save this data in another table. For example (RecordID, UserID) or something to that affect. Then comes your challenge: how to actually control the access to the records. Well, the only way you're going to do that is to ensure that ALL access to the table is given through the use of stored procedures and, within the stored procedures that access the table, you build the logic that checks the current user against the user that saved the record and decide to allow or deny access. Alternatively, you could control the access through the use of a view (that uses a where clause against the user column and a function that returns the current user) but, me personally, I would go the stored procedure route (because I think that all access should be through SPs to begin with)...


Need to find the employees who have switched jobs atleast twice I am trying the following query on the tables which are described below:

SELECT * FROM job_history INNER JOIN employees
ON job_history.employee_id = employees.employee_id
WHERE employee_ID IN
    (SELECT employee_id FROM

        (SELECT employee_id, COUNT(employee_id) AS cnt
         FROM job_history
         GROUP BY employee_id
         HAVING cnt >= 2) INNER
    )
    OUTER;

But I get an error in MySQL, that every derived table must have its own alias

We have a table job_history(employee_id, start_date, end_date, job_id, department_id) containing the following data:

'102', '1993-01-13', '1998-06-24', 'IT_PROG', '60'
'101', '1989-09-21', '1993-10-27', 'AC_ACCOUNT', '110'
'101', '1993-10-28', '1997-03-15', 'AC_MGR', '110'
'201', '1996-02-17', '1999-12-19', 'MK_REP', '20'
'114', '1998-03-24', '1999-12-31', 'ST_CLERK', '50'
'122', '1999-01-01', '1999-12-31', 'ST_CLERK', '50'
'200', '1987-09-17', '1993-06-17', 'AD_ASST', '90'
'176', '1998-03-24', '1998-12-31', 'SA_REP', '80'
'176', '1999-01-01', '1999-12-31', 'SA_MAN', '80'
'200', '1994-07-01', '1998-12-31', 'AC_ACCOUNT', '90'

We have another table employees(employee_id int,
first_name varchar(20),
last_name varchar(25) not null,
email varchar(25) not null,
phone_number varchar(20),
hire_date date not null,
job_id varchar(10) not null,
salary float,
commission_pct float,
manager_id int,
department_id int,
constraint emp_pk primary key(employee_id)
)

Your Query should be like:

SELECT * FROM job_history INNER JOIN employees
ON job_history.employee_id = employees.employee_id
WHERE job_history.employee_ID IN
    (SELECT employee_id
         FROM job_history
         GROUP BY employee_id
         HAVING COUNT(employee_id) >= 2
    );

so you missed the alias before the id "WHERE job_history.employee_ID IN..", and the where in change it to what above...


Ways to clean data (trim unneccesary space & chars) - MySQL I am already familiar with methods of cleaning such as, pasting into excel, and running a TrimClean Macro.

example.

Cell contains text "Cognac                   "
I want the result to be "Cognac"

Is there an UPDATE query which can perform something similar on MySQL?

You can use TRIM():

SELECT TRIM('  cognac    ');
-> 'cognac'

Or:

SELECT TRIM(BOTH 'x' FROM 'xxxxcognacxxxx')
-> 'cognac'

Or cascade multiple characters:

SELECT TRIM(BOTH 'c' FROM (TRIM(BOTH 'x' FROM 'xxxxcognacxxxx')))
-> 'ogna'

You can also look at this question for REGEX replace using a function: How to do a regular expression replace in MySQL?


How To Export-Import Database in Oracle 11g(Application Express Edition)? I am beginner in Oracle 11g. I have created a database in oracle 11g(Application Express).

I want to export this database with all data(whole workspace) and import this to another computer where I already have installed Oracle.Is there any way to export-import the whole workspace in oracle 11g application express?

You have lots of different options for doing this:

http://docs.oracle.com/cd/B25329_01/doc/admin.102/b25107/impexp.htm

I would recommend using Oracle Datapump as this is the most powerful solution, and would represent the best opportunity for instruction on how this is done in an enterprise environment.


Unable to access OEM 11g grid control after upgrading to Firefox ver 39.0 Previously, I have encountered the similar issue after upgrading Firefox to version 37.0.1.

Here's the link of the question posted: Unable to access OEM 11g after upgrading to Firefox ver 37.0.1

I applied the suggested workaround Set Firefox's about:config setting security.tls.version.fallback-limit to '0'. It worked for Firefox version 37.0.1 however, yesterday my firefox was updated automatically to verson 39.0 (I have since disabled the auto update). Everything broken again.

Working around no longer fix. Not sure what Firefox has done with the new release.

Well, I have finally figured it out. I clicked on

Opened Help Menu (?)
Troubleshooting Information
Refresh Firefox

This would reset the default Firefox configuration. You will lose all your add-ons. Beware!

Next, enter following in browser URL:

about:config

Change the following settings
security.tls.version.fallback-limit=1

Restart firefox.

Viola! OEM loads flawlessly.


can any tweaks improve full table scan time in Postgres/Cloud SQL? I have a table with 150 million rows in a Postgres database hosted in Cloud SQL (in Google Cloud Platform).
Typically I obviously try to avoid scanning the table, but occasionally I will manually want to perform a query that I think will always produce a table scan. For example, SELECT COUNT(*) FROM table WHERE col1 IS NOT NULL. Most recently I was running that query out of curiosity, to see if I had set any values yet, but it essentially ran forever.
Fortunately the only time I run into this need is speculative manual queries like that, but it got me curious if there's anything I can do to improve the performance, or if I'm basically stuck with a table that I can't perform certain queries on due to its size.
Right now I have 4 cores, 500GB storage, 15GB RAM, but it seems unlikely that boosting CPU or RAM would help (as-is, that query only consumes 1-2 cores). I might benefit from greater IO capacity, but at least with Cloud SQL hosting, 500GB storage maxes out the associated IO capacity of a 4 core box. You need to go to something like 14 cores to have a shot at any higher IO specs.
As far as I can tell, partitioning wouldn't help here either. It seems like that would help if I could somehow cause each partition to get its own physical SSD and parallelize reads across more hardware that way, but my sense is that partitioning is more about finding ways to reduce the total number of operations that are needed, than about taking advantage of separate parallel SSDs.
Just curious if there's anything I might be missing here.

Obviously faster storage and more RAM for caching can improve performance of a sequential scan.
Other than that, you can increase max_parallel_workers_per_gather on recent PostgreSQL versions to have more processes work on the query. That reduces the execution time at the expense of more load on the database.


How to determine if a mysql binary log file is empty? In order to determine whether a database backup is needed or not, I'm trying to see whether a mysql binlog file has any data in it. (During a backup, we make a fresh binlog file and record the filename. Then later, we can check the contents of that binlog to see if anything has happened since the last backup.)

Is there any way to know when a binlog file is actually empty? Perhaps using the mysqlbinlog command?

I would check the position from SHOW MASTER STATUS, but the position seems to vary with the mysql version... is it constant within a version? My version of mysql (5.6.11) starts a new binary log at position 120.

An alternative approach would be to record the time that the binlog was created at and check to see if there were any changes made after that, but it makes me nervous relying on the speed of the backup process like that.

When it comes to an empty binary log, it is version specific:

120 in MySQL 5.6
107 in MySQL 5.5
106 in MySQL 5.1
98 in MySQL 4.1/5.0

I have discussed this many times in the DBA StackExchange

Nov 19, 2014 : Question on having a MySQL 5.1 replicate from a MySQL 5.6 DB
Jun 26, 2013 : Is it possible to convert mysql binlog from statement format to row format?
Oct 20, 2011 : How to handle the My SQL DB Master to Master replication when there was a disk space outage
Feb 04, 2011 : MySQL master binlog corruption
All my posts where I discuss this

In your case, you need to run just one command

SHOW MASTER STATUS;

This tells you the current binary log mysqld is writing to and the postition. It just so happens, the log position is also the filesize.

Here is an example how to check for an empty binlog before launching a backup

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#
# Find out the MySQL Version
#
SQL="SELECT VERSION()"
VERS=`mysql ${MYSQL_CONN} -ANe$"${SQL}"`
MYSQL_VERSION=`echo "${VERS}" | sed 's/\./ /g' | sed 's/ /./' | awk '{print $1}'`
case "${MYSQL_VERSION}" in
    "5.0") EMPTY_BINLOG_SIZE=98  ;;
    "5.1") EMPTY_BINLOG_SIZE=106 ;;
    "5.5") EMPTY_BINLOG_SIZE=107 ;;
    "5.6") EMPTY_BINLOG_SIZE=120 ;;
esac
#
# Get the Current Master Log's Position
#
SQL="SHOW MASTER STATUS"
LOGPOS=`mysql ${MYSQL_CONN} -ANe$"${SQL}" | awk '{print $2}'`
#
# Compare log position to an empty log size
#
OK_TO_BACKUP=0
if [ ${LOGPOS} -eq ${EMPTY_BINLOG_SIZE} ]; then OK_TO_BACKUP=1 ; fi
if [ ${OK_TO_BACKUP} -eq 1 ]; then
   ...
fi

GIVE IT A TRY !!!

SUGGESTION

If all your tables are using the InnoDB Storage Engine, you can dump the data at a point-in-time, capture the log file and position, and still allow INSERTs, UPDATEs, and DELETEs all at the same time (please do not do any DDL during the backup). When you use mysqldump with the options --single-transaction and --master-data=2, it will write the log file/position on line 23.

I just mentioned this 6 days ago : Master/Master/Slave Replication

That way, there is no need to wait for an empty binary log with no application downtime.


Bulk insert statement fails in SSMS with Operating system error code 5 (Access is denied.) This issue was discussed in several places and it seems that general consensus is that if Windows Authentication is used then SSMS will access source file using Windows Domain user credentials.

However, Process Monitor shows otherwise. When I log into SSMS as a Windows Domain user, SSMS still uses SQL Server service account to access file in question, which sits on a network share. SQL service account does not have any access rights to the share, hence statement fails.

My understanding after reading quite a few answers for similar questions is that this should happen only if SQL Server Authentication is used when logging into SSMS, in which case SSMS uses SQL Server service account to access files. Whereas for Windows Authentication SSMS is supposed to use relevant Windows Domain account under which SQL statement is executed.

I wonder why SSMS uses SQL Server service account even when user uses Windows Authentication? Any thoughts/comments on this please?

The statement I'm attempting is:

bulk insert [database].[schema].[table]
FROM '\\server\share\file_name.csv' with( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )

This query shows I'm connected to the server via NTLM authentication:

SELECT dec.auth_scheme
FROM sys.dm_exec_connections dec
WHERE dec.session_id = @@SPID;

Thank you.

Microsoft's Documentation for BULK INSERT says this about security:

If a user uses a SQL Server login, the security profile of the SQL Server process account is used. A login using SQL Server authentication cannot be authenticated outside of the Database Engine. Therefore, when a BULK INSERT command is initiated by a login using SQL Server authentication, the connection to the data is made using the security context of the SQL Server process account (the account used by the SQL Server Database Engine service). To successfully read the source data you must grant the account used by the SQL Server Database Engine, access to the source data.In contrast, if a SQL Server user logs on by using Windows Authentication, the user can read only those files that can be accessed by the user account, regardless of the security profile of the SQL Server process.
When executing the BULK INSERT statement by using sqlcmd or osql, from one computer, inserting data into SQL Server on a second computer, and specifying a data_file on third computer by using a UNC path, you may receive a 4861 error.
To resolve this error, use SQL Server Authentication and specify a SQL Server login that uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about how to enable a user account to be trusted for delegation, see Windows Help.
For more information about this and other security considerations for using BULK INSERT, see Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) (SQL Server).

The implication is you need to have Kerberos authentication working in order to allow impersonation.  SQL Server needs valid Kerberos SPNs setup for this to work.  I wrote a blog post about how to confirm that Kerberos SPNs are correctly setup over at SQL Server Science.
You'll also need to enable Delegation for the SQL Server Service Account.  This post I wrote shows how to get that set up, and provides minimally complete and verifiable example code you can use to test the setup.


Merits of inserting to temp table and renaming versus just adding/removing columns If I add or drop columns from a SQL Server table I presume I get page splits or gaps. Since the size of the row has changed.

When I use RedGate SQL Compare to create conversion scripts its strategy is to create a temporary table, copy all the data into that table, drop the old table, and then rename the temporary table.

I assume this cleans up the pages as all the rows were "perfectly" sequentially inserted.

I recently had a DBA tell me that this "copy and rename" approach is inefficient, expensive and unnecessary.

What are the merits of these two approaches?

I recommend you read SQL Server table columns under the hood. You'll see that many column DDL operations result in having 'ghost' columns in the table, physical columns that are existing in the table but not visible to the user. A rebuild will remove all these ghost columns.

Most times this is benign, but there are some dark areas that can lead to the issues described in KB2504090:

  This issue occurs because the accessor that SQL Server uses to insert data into different partitions recognizes the metadata changes incorrectly. When data is inserted into the new partition that is created after a column is dropped, the number of the maximum nullable columns in the new partition may be one fewer than the number of the maximum nullable columns in the old partition.

I for one shun all schema compare tools and comparison diff based deployment/upgrades. There simply is no one-size-fits-all correct approach with regard to changing schema. Just like Henrik mentions, I've been burned by the 500GB table 'copy', tyvm, no more diffs for me. Instead I recommend migrations, coded as SQL scripts and tested on relevant data size before being deployed. See Version Control and your Database. Rails ActiveRecord Migrations really grok this.


Can Availability Groups cross domains? Is SQL Server 2014 capable of having Availability Groups set up across multiple domains?  If not, is there a third party tool you can recommend to mirror a 13TB (and growing) database across domains?

No, not in SQL Server 2014.

You can have Availability Groups across multiple subnets, but they all must reside on the same domain (even if they span data centers, continents, etc). This official document states as much for Windows Server 2008 R2, but I can assure you the same remains true for more recently released versions of Windows (emphasis mine):

  Windows Server 2008 R2 requires that all the cluster servers must be in the same Active Directory domain. Therefore, SQL Server multi-subnet failover cluster requires that all the cluster nodes be in the same Active Directory domain even if they are in different subnets.

And from this document (emphasis theirs):

  Availability replicas must be hosted by different nodes of one WSFC cluster:  For a given availability group, availability replicas must be hosted by server instances running on different nodes of the same WSFC cluster.

This is enforced by the underlying Windows Server Failover Cluster (WSFC), not AGs directly. You can't have a Windows cluster traverse domains, and since AGs need to all be part of a single WSFC, there is no way to have them span domains.

For newer versions of SQL Server and Windows there is a new concept called a Domain Independent Availability Group which you can read about here. And for SQL Server 2017 there was a requirement to support Linux, which obviously doesn't have Windows domains, and you can read about that here.

Klaus Aschenbrenner talks about setting up a Basic Availability Group in SQL Server 2016 Standard Edition and Windows Server 2016, on a simple workgroup (no domain at all):

How to create a SQL Server Availability Group WITHOUT an Active Directory Domain


MYSQL food ordering portal database design I'm trying to create a database for a restaurant ordering portal, I've created the tables excluding the payment table. I'm a bit stuck on whether its the correct structure.  If i could get any feedback it would be greatly appreciated! so the database should allow the following:

allow the customer to order
restaurant can update/add to menu
restaurant can confirm/deny an order

(i havent added Pkeys or Fkeys yet)
CREATE TABLE Customer (
customerID INT(11) NOT NULL,
Fname VARCHAR(100) NOT NULL,
Lname VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL,
phone INT(14) NOT NULL,
address VARCHAR(255) NOT NULL,
postcode VARCHAR(20) NOT NULL

CREATE TABLE CusOrder (
orderID INT(11) NOT NULL,
prodname VARCHAR(55) NOT NULL,
quantity INT(11) NOT NULL,
price FLOAT(10 , 2 ) NOT NULL,
totalprice FLOAT(10 , 2 ) NOT NULL,
menuID INT(11) NOT NULL,
deliveryfee FLOAT(10 , 2 ) NOT NULL,
customerID INT(11) NOT NULL

CREATE TABLE resturantMenu (
menuID INT(11) NOT NULL,
deliveryfee FLOAT(10 , 2 ) NOT NULL,
productName VARCHAR(255) NOT NULL,
productPrice FLOAT(10 , 2 ) NOT NULL

CREATE TABLE resturantLogin (
resturantUserID INT(11) NOT NULL,
menuID INT(11) NOT NULL,
username VARCHAR(255) NOT NULL,
pass VARCHAR(255) NOT NULL,
restDetails VARCHAR(255) NOT NULL,
restPhone INT(14) NOT NULL,
restAddress VARCHAR(255) NOT NULL,
restPostcode VARCHAR(20) NOT NULL

CREATE TABLE orderConfirm (
orderID INT(11) NOT NULL,
resturantUserID INT(11) NOT NULL,
status ENUM('yes','no') NOT NULL

not afraid of criticism so please fire away :)

First: don't code, draw.  It's much easier to make mistakes in diagrams than it is to try and visualize relations, keys, etc. in code.  At this point in design column types are irrelevant.  Focus on keys and relations.
This may be a good starting point*:

Here we have customers who can place orders for restaurants.  Those orders cannot contain items not offered by that restaurant.  Those orders can include the same item at different prices (buy 1, get 1 50% off, etc).  Those orders can include fees, including delivery fees which can be looked up from a schedule.
Questions not answered by this diagram:

How do we determine if a OrderLine is a menu item or a charge and enforce that?
How is the status of an Order defined?
What exactly encompasses OrderLineCharge?
Should the order request a menu item/quantity which is then becomes an OrderLine?
When an MenuItem is requested but can't be fulfilled, what happens?
How are price changes stored?
Should we track types of cuisine or categories of menu items?
How is delivery tracked?
How are payments tracked?
How do we track quantity discounts ($2 each, 3 for $5, etc)?
Should it be 'OrderId' or 'OrderNumber'

That is a lot of questions we won't solve with lines of code or table definitions, at least not yet.
The next steps I'd recommend are:

Familiarize yourself with ERDs, specifically IDEF1X which are easily read and understood
Start thinking about business rules and how they might shape your data model - what problems can you solve through constraints/relations?  Which problems will require the transactions to contain rules?

*I'm leaving a lot out because this isn't something that can be answered in one step - I don't know if this is a homework assignment, hobby project, or actual work.


msdb.dbo.sp_start_job got stuck in stored procedure I have a stored procedure USP_A with logic like following:

......
BEGIN TRY
exec dbo.usp_Log 'Start to run job job1'
Exec @intErrorCode = msdb.dbo.sp_start_job 'job1'
  IF @intErrorCode <> 0 Goto errorHandling
exec dbo.usp_Log 'End to run job job1'
Return @intErrorCode
END TRY
BEGIN CATCH
  exec dbo.usp_Log 'Error......'
END CATCH
......

But when I run this stored procedure, it got stuck and when I check log I can only see message 'Start to run job job1'. And no error being caught in CATCH block. Also in the SQL Server Agent job monitor I can not see this job get triggered.

But if I manually run

Exec @intErrorCode = msdb.dbo.sp_start_job 'job1'

it works fine.

The SQL Server is Microsoft SQL Server 2005 Enterprise Edition (version 9.00.5000.00)

Update

It seems that the sp is crashed rather than hungup. Because I used sp_WhoIsActive to find which query is waiting and no record returned.

I also tried to comment the start job logic and just

Return @intErrorCode

However the SP still hangs (or crashes).

It turns out to be some kind of SQL Server bug (Maybe the SQL Server 2005 is quite old). The stored procedure just ended unexpectedly and does not return any code.

So the problem is solved by moving all the logic before this msdb.dbo.sp_start_job procedure into a separate stored procedure.

Hope it can help anyone who got this same issue.


Authorization dbo maps to a user with an old machine name I am trying to run a command that looks like this:

Create ASSEMBLY [AssemblyName]
AUTHORIZATION [dbo]
From ...

When this runs, it returns an error message: Could not obtain information about Windows NT group/user 'oldMachineName\username', error code 0x534.

In this case oldMachineName is the old machine name for my computer. I changed the computer's machine name to newMachineName before every installing Sql Server, but there are obviously some vestiges of it hanging around.

When I log into Sql Server using SSMS, it correctly lists the logged-in user as newMachineName\username. Yet when running this command with Authorization dbo it is trying to use oldMachineName.

So, how can I convince Sql Server that oldMachineName doesn't exist anymore and teach it the correct group/user of the current dbo (ie: my logged in user)?

It seems the database was created/restored/attached by a Windows account user that no longer exists. Change the database owner to a valid existing login using ALTER AUTHORIZATION

ALTER AUTHORIZATION ON DATABASE::YourDatabase TO SomeValidLogin;

Consider creating a login, perhaps one per database, without CONNECT permissions for database ownership purposes. That will avoid this sort of problem going forward.