InfluxDB with Zabbix or Telefraf? I'm considering the use of InfluxDB to store thousands of measurements per second from routers in a network.  Each measurement is lightweight (between 10-20 bytes).  Now, I'm wondering what is the best strategy: InfluxDB + Zabbix? Or Telegraf? Or another alternative?

InfluxDB + Zabbix: according to

https://www.zabbix.com/integrations/influxdb
there is a possible integration. However, it seems the integration involves taking data that was collected in a Zabbix database and moving it to an InfluxDB to take advantage of InfuxDB using much less storage. So they meant it to be for archiving large amounts of data.
However, we believe Zabbix itself is too slow, no? The integration above would not work for online data collection, right? We need fast storage, and we do not want Zabbix to be a bottleneck. Are there known benchmarks and recommendations about Zabbix + InfluxDB integration, for online collection + storage?

InfluxDB + Telegraf: according to
https://www.influxdata.com/blog/monitoring-openwrt-with-telegraf/

there is also the possibility of integrating InfluxDB + Telegraf for data collection.  Are there best practices and/or benchmarks about data collection with Telegraf + storage with InfluxDB?

Are there other alternatives and/or suggestions for large scale data collection and integration with InfluxDB?

You might want to look into using Spark, and if are able to introduce AWS into the architecture, Kinesis Data Firehouse.
Spark: https://medium.com/analytics-vidhya/processing-time-series-data-in-real-time-with-influxdb-and-structured-streaming-d1864154cf8b
Another article on Spark: https://www.influxdata.com/blog/zeppelin-spark-and-influxdb-for-big-data-time-series-scenarios/
Kinesis Data Firehose: https://aws.amazon.com/kinesis/data-firehose/?kinesis-blogs.sort-by=item.additionalFields.createdDate&kinesis-blogs.sort-order=desc
Spark is fairly widely used these days and integrates with InfluxDB, and Kinesis Data Firehose is used for heterogeneously ingesting large amounts of data in real time.


Mysql enable remote connection in centos,my.cnf does not have bind-address to comment out I'm trying to make my MySQL Server running on CentOS to allow remote database connections. I'm reading this article that says I should comment out bind-address but bind address is not available in the my.conf, this line is available at /etc/my.cnf.

How should I enable remote connections now that bind-address is missing?

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
event_scheduler=ON
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
event_scheduler=ON
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

The MySQL server listens on a single network socket for TCP/IP connections. This socket is bound to a single address, but it is possible for an address to map onto multiple network interfaces. The default address is 0.0.0.0.

If the address is 0.0.0.0, the server accepts TCP/IP connections on all server host IPv4 interfaces.
If the address is ::, the server accepts TCP/IP connections on all server host IPv4 and IPv6 interfaces. Use this address to permit both IPv4 and IPv6 connections on all server interfaces.

Source: http://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_bind-address

You can check your current bind address by executing:

$ mysql -b -e "SHOW GLOBAL VARIABLES like 'bind_address'"
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| bind_address  | 127.0.0.1 |
+---------------+-----------+

on the command line, or SHOW GLOBAL VARIABLES like 'bind_address'; on a mysql client.


Microsoft SQL Server how can i detach localdb to run on any computer I am working on a personal project and have implemented a database system using the Entity Framework code first tutorial on msdn, link below,

http://msdn.microsoft.com/en-gb/data/jj193542

I am running Visual Studio 2012 and the database has been created using Microsoft SQL Server and localdb. However i wish to run my application on any computer and this current setup will not allow me to do so.

I am a bit of a novice when it comes to databases, and would like some advice/resources that outline how i can change my database setup so that it is available on any computer and also retain the data currently in my database.

Any help would be greatly appreciated,
Thanks in advance!

For LocalDB you'll need to install SQL Server Express on all the machines where you want to run your application. If you don't want to do that, have a look on SQL CE. That allows you to have only DB file moved about and all the required libraries can be included in your project.


How to set up a user who doesn't have `admin` database access but use it as authentication database? I want to setup a user in MongoDB. This user will not have admin database access. But it uses admin as authentication database. It will fail to connect the mongoDB by this command mongo --host localhost admin. Instead, it can use this command to connect to test database: mongo --host localhost --authenticationDatabases admin test.  How can I restrict the permission in this case?

I tried below command to create an user:

db.createUser({user: 'testUser', pwd: '123456', roles: [{role:'readWrite', db: 'SampleCollections'}]})

when I use that user account to login mongo shell, I am able to list the collections under admin database. How can I restrict the user only on SampleCollections database not admin?

Not need. You just give user needed rights to the wanted non-admin database. User can use admin database as authentication database even user doesn't have read/write access to admin database.

use products
db.grantRolesToUser("productsUser",[ "readWrite" ])

grantRolesToUser documentation.

UPDATE

Let's create mongodb instance with --auth. Login as admin, create user what can readWrite ONLY test -db, authenticate with that user, check can use list admin database collections and what this user can do with test database.

#> mlaunch init --single --auth
launching: mongod on port 27017
Username "user", password "password"
#> mongo -u user -p password admin
MongoDB shell version v3.4.6
connecting to: mongodb://127.0.0.1:27017/admin
MongoDB server version: 3.4.6
Mongo> db.createUser({user:"test", pwd:"testpwd", roles:[{role:"readWrite", db:"test"}]})
Successfully added user: {
    "user" : "test",
    "roles" : [
        {
            "role" : "readWrite",
            "db" : "test"
        }
    ]
}
Mongo> db.auth("test","testpwd")
1
Mongo> db
admin
Mongo> show collections
2017-09-11T19:06:22.001+0300 E QUERY    [thread1] Error: listCollections failed: {
    "ok" : 0,
    "errmsg" : "not authorized on admin to execute command { listCollections: 1.0, filter: {} }",
    "code" : 13,
    "codeName" : "Unauthorized"
} :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
DB.prototype._getCollectionInfosCommand@src/mongo/shell/db.js:807:1
DB.prototype.getCollectionInfos@src/mongo/shell/db.js:819:19
DB.prototype.getCollectionNames@src/mongo/shell/db.js:830:16
shellHelper.show@src/mongo/shell/utils.js:762:9
shellHelper@src/mongo/shell/utils.js:659:15
@(shellhelp2):1:1
Mongo> use test
switched to db test
Mongo> db.coll.insert({})
WriteResult({ "nInserted" : 1 })
Mongo> show collections
coll
Mongo>

And same from outside:

#> mongo -u test -p testpwd --authenticationDatabase test
MongoDB shell version v3.4.6
connecting to: mongodb://127.0.0.1:27017
MongoDB server version: 3.4.6
2017-09-11T19:17:48.614+0300 E QUERY    [thread1] Error: Authentication failed. :
DB.prototype._authOrThrow@src/mongo/shell/db.js:1461:20
@(auth):6:1
@(auth):1:2
exception: login failed
#> mongo -u test -p testpwd --authenticationDatabase admin test
MongoDB shell version v3.4.6
connecting to: mongodb://127.0.0.1:27017/test
MongoDB server version: 3.4.6
Mongo>


Problem to select not in I want to display the values that are between 0 and 10 and dont exist in the current table

For example :

Field
1
2
3
5
9
8

Result expected:

7
4

The problem is that the column field is a varchar, so i have first convert to number and then make the validations

SELECT TO_NUMBER(yy.ent_sub_dealer_code,'9') FROM a_entidades yy
where TO_NUMBER(yy.ent_sub_dealer_code,'9') not in (SELECT LEVEL n
                                                   FROM DUAL
                                                   CONNECT BY LEVEL <= 9     )

Any suggestions?

create table t1 (x varchar2(2));

insert into t1 (x) values ('1');
insert into t1 (x) values ('2');
insert into t1 (x) values ('3');
insert into t1 (x) values ('5');
insert into t1 (x) values ('6');
insert into t1 (x) values ('8');
insert into t1 (x) values ('9');
insert into t1 (x) values ('10');
insert into t1 (x) values ('11');
commit;

SQL>  select level as x
  2     from dual
  3  connect by level <= 9
  4    minus
  5   select to_number(x)
  6     from t1;

         X
----------
         4
         7

2 rows selected.


Optimization for 100 GB Real-time info table I have a table of device information feed which is filled by data from remote device units.

There will be like 1000 units each sending an update per minute which will be stored as a row in that table. So each minute there will be like 1000 new rows added so 60,000 rows per hour and 1 440 000 rows per day. And I need to store minimum 3 weeks data.

As I am new to DB design therefore I am out of ideas to how to maintain such big flow of data.

There will be INSERT queries mostly on this table and SELECT queries, no UPDATEs or other queries will not be needed.

Table strucure: There will be 28 columns mostly FLOAT and TINYINT(4) and 1 DATE type. No other types.
The table is in 3rd normal form and each row will be unique per device unit. So, columns can't be less then this.

I am using MySQL.

I have other tables but they are not so huge so maintainable.
Looking for expert suggestions to how to maintain this database.

Assuming you have 300 Bytes per row, that makes a whopping 95GB per three weeks - that's not very much in today's terms - a 1TB disk would last 30 weeks - that's almost 1/2 of a year. If you compressed this data, I'm fairly sure that you could store at least a couple of years (possibly a lot more) on a single 1TB disk. I would keep the "live" data on one machine (or even disk) and the compressed data (> 3wks) on another machine (or disk) for analysis. How about the Archive engine for older data? Or maybe you could consider InfiniDB for analysis (or Infobright)?  These are columnar based stores optimised for data analysis.

[Edit] You might also want to look at this - link to data compression. I am implicitly assuming that you're on MySQL since you used TINYINT.


Suppress repeated performance condition alerts I have set SQL Server performance condition alert with details as follows:

Object           : SQL Server:Databases
Counter          : Percent Log Used
Instance         : My database name
Alert If counter : rises above >25
Delay between responses 10 minutes.

Please let me know what action I should take so that I do not get this alert again.

The hard drive where the transaction log gets saved has enough space, and recovery model is FULL.

What I want to ask is How can I find out why I started getting alert suddenly. My database full backup schedule on Sunday ,differential backup  scheduled daily and transaction log backup daily after 10 min. Where should I check to find out the cause of the issue. Auto growth property is set to 5120 MB and restricted to 86000 MB. All backups like Full,daily,transaction log backup are taking place correctly . My transaction log backup size is 1.08 MB ,Differential backup size around 8 GB  . From last Sunday I started getting alert . Till that there were not alert.
One more thing I would like to mention that I am also taking backup for TempDB. TempDB log file  size  is 5 GB. Is it the reason the  alert

You basically created an alert that will get fired when the log space used percent is above 25%.  That's pretty low, in my opinion.

  Please let me know what action should I take so that I did not get this alert again Hard drive where transaction log get saved have enough space.

If what I'm understanding from you is correct, you're saying that the volume your transaction log sits on has plenty of space, and you think you may be getting false alerts?  If that's correct, this perfmon counter isn't looking at that at all.  It could be a 2 GB log file, living on a 500 GB volume.  Even if that 500 GB volume has 400 GB of free space, if your 2 GB log file has 500+ MB space used you'll get that alert.  Volume free space and database file free space are two different things.

You need to determine what you care about to be alerted on.  It's a great strategy to alert on log file space used if it is getting high (in my opinion, the upper bounds would be 70% - 80%, but you will need to tailor that to your environment), but I think you may be confused with the above two points.

If that hasn't answered your question, please clarify either through comments here or editing your own question.

Edit after your edit

You need to see what your log space used percent is.  By your alert, if it is greater than 25%, then you should be alerted.  You can find that out a couple of ways.

The quickest is to run the following:

dbcc sqlperf(logspace);

That will give you a rundown of all log space used for every database.  If you want to break that down for a particular database (for whatever reason) it could be accomplished this way:

use YourDatabase;
go

;with file_cte as
(
    select
        name,
        size,
        fileproperty(name, 'SpaceUsed') as space_used
    from sys.database_files
    where type_desc = 'log'
)
select
    name,
    size,
    space_used,
    convert(decimal(5, 2), space_used * 1.0 / size * 100) as space_used_percent
from file_cte;

The reason why you didn't get alerted after that is simple:  Because your log space used didn't breach 25%.  That could have been through log reuse due to a transaction log backup, or maybe somebody grew the file making that 25% space used a larger number.  As for tempdb's database file sizes, that shouldn't affect when you get alerted, unless you are being alerted specifically for tempdb log file space used (in your question it says "my database name" so I'm assuming you're not alerting off of tempdb with this alert).


How to get table columns with datatypes (and lengths) using PowerShell? I'm trying to get column names with data types (and lengths) for SQL Server instance using PowerShell. I got this far:

#Load PSSnapin
Add-PSSnapin *SQL*

#Get column names
$colNames = dir 'SQLSERVER:\SQL\MYCOMPUTER\MYSQLINSTANCE\Databases\MYDATABASE\Tables' |
    Where-Object {$_.DisplayName -match "dbo.MYTABLE"} |
        ForEach-Object {$_.Columns} |
            Select-Object Name, DataType
$colNames

How to get also datatype lengths for columns?

The lengths are found at <Column>.Properties['Length'].Value, so you can select it like:

#Get column names
$colNames = dir 'SQLSERVER:\SQL\MYCOMPUTER\MYSQLINSTANCE\Databases\MYDATABASE\Tables' |
    Where-Object {$_.DisplayName -match "dbo.MYTABLE"} |
        ForEach-Object {$_.Columns} |
            Select-Object Name, DataType, `
                @{Name='Length'; Expression = {$_.Properties['Length'].Value}}
$colNames


New SQL Server from backup; application has successful login on 1 database, but fails on another: 0x80040e4d Our SQL Server crashed and burned, the hard drives destroyed. No hope of recovery.

We need to get a very important application working again; this is on a different server.

We've set up a new SQL Server using SQL Server Express 2014 and attached the .MDF files from the most recent backup.
We created a user and gave access to all the databases.
We changed the connection string in the application to use the new SQL Server and the new user. Connection string:
Provider=sqloledb;Data Source=<IP-address>;Network Library=DBMSSOCN;Initial Catalog=<DB>;User ID=<UID>;Password=<PWD>;
The application successfully connects and reads data from one of the databases, but fails with error: "Login failed for user 'goa'." on another database.
The SQL Server log files shows the following:
Login failed for user 'goa'. Reason: Password did not match that for the login provided. [CLIENT: <IP Address>] and Error: 18456, Severity: 14, State: 8.

The only difference between the connection string that succeeds and the one that fails is the database.

The user does have access to both databases, and we are able to log in with that user in SQL Server Management Studio and do anything with either of the databases. Both databases are on the same SQL Server instance.

What puzzles me is that it claims wrong password when the same password worked fine less than a second before, when it connected to the other database.

Does this have anything to do with the limitations of SQL Server Express?

Application log file

Microsoft OLE DB Provider for SQL Server
Description = Login failed for user 'goa'.
HelpFileContext = 0
Error 0
Number = 0x80040e4d
Source = Microsoft OLE DB Provider for SQL Server
Description = Login failed for user 'goa'.
HelpFileContext = 0
SQL State = 42000
NativeError = 18456

Some attempts

We've tried changing the order of the connections, so that database B goes first, but it still fails at B; so it is NOT a matter of a limit to the number or frequency of connections.
We've made sure the user has access to both databases under *User Mapping* and by right clicking the databases themselves, going to Properties >> Permissions, and granting everything. It still can't connect.
We've tried changing the passwords: in which case neither connection worked. This gives me the impression that there is some encryption problem(?), and that the SQL Server tries to decrypt the password with the wrong key or something, and by chance uses the correct key in certain circumstances.
Adding Integrated Security=SSPI;, to the connection strings, did not help. Nor did Integrated Security=yes; or Trusted_connection=yes;.

Here is a summarization of our efforts to make this work:

We set up a new SQL Server using SQL Server Express 2014 and attached the .MDF files from the most recent backup.
We created a user and gave access to all the databases; by giving ownership and db_datareader and db_datawriter to the user for all the relevant databases, and by granting rights under User Mapping.
In the application we needed to get back online: The initial connection string, that worked fine with the old SQL server before it crashed, was: DSN=<SQL_Server>;UID=<User Id>;PWD=<Password>;Database=<Catalogue>;

Our initial attempt was to replace <SQL_Server> with the IP-address to the new SQL Server (with and without port).
This did not work; so we changed DNS=.. to Server= (with and without port); this did not work either.
All these attempts gave errors that the connection string was invalid. So we looked up different connection strings for IP-Addresses using OLE DB and found this: Provider=sqloledb;Data Source=<IP-address>;Network Library=DBMSSOCN;Initial Catalog=<Database>;User ID=<User>;Password=<Password>;. This worked, but only for one of the connections, for database A, while database B still failed.

The application successfully connects and reads data from database A, but fails with error: "Login failed for user 'goa'." on database B.

The SQL Server log files shows the following:
Login failed for user 'goa'. Reason: Password did not match that for the login provided. [CLIENT: <IP Address>] and Error: 18456, Severity: 14, State: 8.

We tried changing the passwords, and then neither connection worked. This gives me the impression that there were some encryption problem (because I recalled a mention of encryption in one of the forums I had browsed, looking for answers), and that the SQL Server tried to decrypt the password with the wrong key or something, and by chance uses the correct key in certain circumstances.

To solve this potential encryption problem; we added Integrated Security=SSPI; to the connection strings, this did not help. Nor did Integrated Security=yes; or Trusted_connection=yes;.

To make sure this was not a matter of the number of connections, or a limit in the number of open connections at a time: we changed the order of the connections so that database B went first, and A went second; and this time it was still the database B that failed.
We tried ressurecting the users from the original SQL server, by creating those users and executing: EXEC sp_change_users_login 'Auto_Fix', '<user>', NULL, '<Password>'. This gave a positive result: The number of orphaned users fixed by adding a new logins and then updating users was 1.. Thanks to @SQLPRODDBA and @Chad-Mattox

This is what worked for us:

Changing the connection string to:
Driver={SQL Server};Server=<IP-Address>;UID=<User Id>;PWD=<Password>;Database=<Catalogue>;That is going back to the initial format and adding Driver=.. to the string. For this connection we did not use a ressurected user from the old SQL server.


Microsoft Access Move Multiple Rows In Table1 To A Single Row In Table2 I'm new to Microsoft Access so my terminolgy and understanding are limited, please bear with me. I am interfacing a piece of lab equipment via Serial/ASTM/ODBC to MS Access for Office 365 MSO 16.0.12.x.
The equipment and ASTM transmit different parameters per line via the same field names. So I end up with 23 different rows in Access and each row has a different parameter and result. I use Table1 as a queue. I need to move the captured data from multiple rows in Table1 to multiple columns in a single row in Table2. I've created a greatly simplifed version of the database to try to understand how to do this properly.  Table1 is a simplified example of captured data. Table2 is a simulated example of what I am trying to achieve. Any help would be greatly appreciated.

With help from Jonathan Fite who pointed me in the correct direction.
Via SQL:
TRANSFORM FIRST(RESULTS)
SELECT RECORDNUM
FROM Table1
GROUP BY RECORDNUM
PIVOT TYPE

Via GUI choose Totals and Crosstab then:


no free memory but low total memory I have SQL Server 2012 SP2 that has a max memory of 25.4GB but SQL Server is only using 15GB. When I check free memory with the PerfMon SQLServer:Memory Manger Free Memory (KB) counter only 40MB is free. The server itself has 32GB.

I think that the server is only using 15GB because it only needs that much for loading all the used pages. But what I do not understand is why the free memory is only 40MB.

Can someone please explain this?

There are no other services on the system and my PLE is 122000 seconds.

The counter Memory Manager: Free Memory (KB) tells you below. Read the BOL document

  Free Memory (KB): Specifies the amount of committed memory currently not used by the server.

So what this means is of all committed memory SQL Server is not using 40 MB of the memory. This is not telling you free memory on the SQL Server system. A committed memory is one that is backed by physical RAM. When a process starts it can address any physical memory address in its VAS but memory will only be committed if that VAS region is backed by physical memory.


Psql hangs mid way through script How do you diagnose why an instance of psql is hanging mid-way through executing a SQL script?

I have a sanitation script with a bunch of UPDATE statements like:

UPDATE table1 SET column1 = CONCAT('stuff', id);
UPDATE table2 SET column2 = CONCAT('stuff', id);
...

The script is part of an automated routine to prepare a development database. However, this is blocking me because the script hangs indefinitely.

There's about a hundred statements like this in the file, yet, about half way through, the script hangs. When I re-run the process from a parent bash script, it hangs at a different point, so it's not a specific statement that's causing the issue. Moreover, when I run the psql command manually, it runs perfectly without hanging.

The call I'm running is:

psql --user=myuser --no-password --host=localhost -d mydatabase -a -f sanitize.sql

Since it non-deterministically fails when run from inside Bash, but succeeds when I run it directly in a shell, that's likely a part of the problem, but I'm not sure what to investigate, since no error's being thrown. Where do I begin investigating this?

The problem ended up being some obscure bug in Bash. I was calling psql from a Bash script, which was being called from another Bash script, which was being run from a Cron job, which was redirecting all output to a log file. When I changed it so that Cron directly ran the psql call, and redirected the output, the "hanging" stopped.


Partitioning increases CPU usage in mariadb I'm using mariadb 10.2, and I had a table with a structure similar to the one below, currently with 15gb, where I insert a few thousand records per minute all the time, each batch for a specific account (AccountId1).

AccountId1 bigint PK,
AccountId2 bigint PK,
ActionType int,
ActionDate timestamp

Looking at the process list, multiple inserts were waiting in line for this table, and they were always one insert running at a time, so I decided to partition this table by hash on the AccountId1, and created 32 partitions.

The inserts now jumped from 1 or 2 a second to 8 during peak times, and that worked great. The issue is that the CPU usage jumped from 15% to 40% after the change, and even stopping the insert process completely for a few hours, the CPU won't go down.

There are a few places where we select from this table that could span all partitions, but these are processes that run every few hours and don't take that long. Other frequent queries always use the AccountId1 field and are indeed querying a single partition by looking at the query plan.

Is there any overhead associated with partitioning, even if no sql is touching that table? What could be the reason for this increase in CPU?

Note: there are around 2000 concurrent connections at any time, but the # of connections before and after the partitioning were the same.

OVERHEAD
What needs to be understood is a partitioned table's physical implementation.
For example, in my old post (Aug 31, 2014 : How does subpartitioning actually work and what physical files are created?), I displayed what a partitioned table looks like:
C:\>cd \MySQL_5.6.15\data\test

C:\MySQL_5.6.15\data\test>dir nums_comp*
 Volume in drive C is TI10665200H
 Volume Serial Number is A273-2EFF

 Directory of C:\MySQL_5.6.15\data\test

08/31/2014  04:09 PM            98,304 nums_composite#p#p0#sp#p0sp0.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p0#sp#p0sp1.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p1#sp#p1sp0.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p1#sp#p1sp1.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p2#sp#p2sp0.ibd
08/31/2014  04:09 PM            98,304 nums_composite#p#p2#sp#p2sp1.ibd
08/31/2014  04:09 PM             8,594 nums_composite.frm
08/31/2014  04:09 PM                96 nums_composite.par
               8 File(s)        598,514 bytes
               0 Dir(s)  686,691,409,920 bytes free

C:\MySQL_5.6.15\data\test>

I have another example in Feb 16, 2015 : MySQL Create Table with Partition Slow on server machine
Each partition is treated as a separate InnoDB table with a distinct tablespace.
What overhead exists ?

Memory Usage for the Information Schema (Adding new tables -- memory usage increases)
Index Stats Separated By Partition (Do I need to index a key that I partition by?)
Open File Handles (In your case, 33 [1 File Handle for Each .ibd file, 1 for .frm])

While these things help explain the overhead, you must still be wondering where the CPU activity is coming from ???
CPU
There will still be some CPU activity based on the open file handles. How ?
The option innodb_open_files places a limit on the number of open file handles against all InnoDB tables through the MySQL Instance.
If table info needs to be examined by monitoring software, more queries make more CPU.
If there are any queries that do not access disk (such as SHOW PROCESSLIST; or SHOW GLOBAL STATUS;) or reads from INFORMATION_SCHEMA, more CPU. (See my old ServerFault post from 2011-08-10 1 billion mysql queries in 24 days? Can something be wrong?)
Opening and closing files handles to access certain tables and read 16K pages into the InnoDB Buffer or marking pages in the InnoDB Buffer Pool as old.


Depicting permissions to access table via E -R diagram I am designing a database schema for a web application. Its a first time I am designing a database with a commercial aim. I have started with drawing E-R diagrams after going through requirements phase.

My question is regarding conditions in schema.

I have a table category and sub category with one to many relationship between them. One category has many sub categories.

Category Table

====================
|  Category Name   |
====================
|  category1       |
|  category2       |
====================

Sub category Table

===========================================
| Owner Category   |  Sub category name   |
===========================================
|   category1      |  subc1               |
|   category1      |  subc2               |
|   category2      |  subc3               |
|   category2      |  subc4               |
===========================================

Now a user has access to subc1 if he has access to category1.

Is there any way to depict this scenario with E-R diagram so that by looking up diagram one can know this constraint?

Basically I want to know how this access constraint is propagated to next phase of designs.

Entity-Relationship diagrams show entities and relationships, including the cardinality of the relationships.

Some people and tools expand this to show attributes, primary keys and foreign keys.  I've even seen some attempts at visually representing some contraints like attribute nullability and basic data domains.  Some of the classic graphical conventions, like IDEF1X also include information about the nature of the relationships (whether they are determinants of the children or not).

I'm not aware of any graphical conventions for showing access rules in an ER diagram.

I would argue that trying to pack too much information into a visual representation of the schema will make the diagram so difficult to understand that it becomes unhelpful.  I would say that this applies to something like access rules.

In order to show access rules, you really need to show the accessors (i.e. the users) since different accessors will generally have different access rules.  There is no established convention for including users in an ER diagram so I wouldn't try to do it that way.

As an alternative, I would suggest using tables or charts to indicate which users have access to which entities.  You could include a column that shows data-level rules that expand on the table-level rules.  A data level rule could be something like "users can only access sub categories which belong to categories that they own".

It won't result in a pretty picture, but I believe that understandable documentation will beat a pretty picture when it comes to trying to explain your design to a developer or customer.


Find rows that have the same values in another column I have the following table.

User  Permission
John  1
Mary  1
Anne  1
Joe   2
Marie 2
Nick  3

I want to select and group users with the same permission number. Is this actually possible? I have millions of different permission values.

Expected output:

(John, Mary, Anne), (Joe, Marie), (Nick)

It's probably best to do this in the application, after all it's just presentation. Something like:

SELECT user, permission
FROM T
ORDER BY permission

then keep track of when permission changes.

However (untested), there seems to be a GROUP_CONCAT function that can do what you want:

SELECT GROUP_CONCAT(user), permission
FROM T
GROUP BY permission
ORDER BY permission


Using TOP in INSERT INTO statement I am reading a tutorial about TOP keyword in T-SQL. But I don't really understand the examples given in the tutorial.

Example 1:

INSERT TOP n INTO table_name1 (Col1, Col2)
SELECT Col3, Col4 FROM table_name2;

The tutorial mentions that the TOP here doesn't really achieve anything since the database doesn't have a concept of order, which I kind of get.

And is it correct to assume that the n rows that are chosen from table_name2 are completely random records?

Example 2:

The tutorial gives another example that improves the first statement.

INSERT INTO table_name1 (Col1, Col2)
  SELECT TOP n  Col3, Col4
  FROM table_name2
Order By Col1;

Here is the confusing part. Putting the TOP keyword inside the SELECT statement is supposed to give us a better control over what rows get chosen and inserted through the ORDER BY clause. So I would assume the ORDER BY clause is part of the SELECT statement in the second example. But Col1 isn't part of table_name2.

This confused the hell out of me.
Thanks for any insight.

And is it correct to assume that the n rows that are chosen from
  table_name2 are completely random records?

When an order isn't specified, it will usually use the sort fields and directions from the design of the primary key of table_name2, but is not guaranteed to.  As Aaron Bertrand said

  It doesn't really matter what the clustered index is. SQL Server may
  still return the order based on some other index based on a wide
  variety of factors. Creating a primary key on some column DOES NOT
  guarantee that selects with no order by will suddenly come back
  ordered by that column always. Might you observe that most of the
  time? Sure. But that is not the same as a guarantee. I've never seen a
  polar bear on my street but there is no polar bear forcefield that
  prevents it from happening. – Aaron Bertrand♦ Sep 14 '11 at 20:41

It certainly wouldn't hurt to add your order clause in, even if you want them to return in the index order of the PK.

From the tutorial example

INSERT INTO table_name1 (Col1, Col2)
  SELECT TOP n Col3, Col4
  FROM table_name2
Order By Col1;

Gaaaah.  That's horrible coding. That will order by Col1 ASC of table_name2, even though your SELECT clause isn't explicitly returning a Col1 from the second table. It is NOT ordered by Col3 of the 2nd table aka Col1 of the 1st table. The MS docs for INSERT INTO do not list an ORDER BY clause - the ORDER BY is part of the inner SELECT clause.

It's always better to explicitly specify which table you are inserting into and selecting from.  E.g. this would be a slightly better example.

INSERT INTO
  table_name1 (Col1, Col2)
  SELECT TOP 3
    t2.Col3
    ,t2.Col4
  FROM
    table_name2 AS t2
  ORDER BY
    t2.Col1 ASC, t2.Col3 ASC, t2.Col4 ASC

After this alteration there's no confusion about what Col1 means (nor the sort order of it). I'd still recommend throwing in a sort order of Col3 and Col4 if Col1 isn't unique for all entries in table_name2, as demonstrated above. You cannot alias the table that records are inserted into (e.g. table_name1 AS t1 (t1.Col1, t1.Col2) as that is not legal syntax for existing versions of SQL Server.

Opinion-based recommendation: avoid the tutorial website that you found.


RAID10 vs. RAID5 for data files We are trying to evaluate the potential performance improvements that could be gained by switching an existing applications .mdf files to a RAID10 LUN vs. the existing RAID5 LUN they are living on right now. The transaction log already lives on a RAID10 LUN.

So the question is, if we move data files to RAID10 would the end user see performance improvements in the GUI? or would it be just faster writes to the data files when a checkpoint occurs and the changes get written to the data file but no improvement would be seen at the end user level?

There are two types of activity to consider here: reads and writes.

As you correctly point out, MDF/NDF writes are done through the checkpoint process, and users shouldn't have their transaction time affected.

Reads happen when the data that is needed is not yet in RAM (in the buffer cache). So ideally, user activity shouldn't be affected often. But when it is, the data can be fetched quite quickly whether RAID 10 or 5. The pain point for RAID 5 is with writes not reads.

But if you lose a disk - what then?

With RAID 10 there's no loss of performance. When a replacement disk is put in, the mirror is copied over it. With RAID 5, every read requires some maths to be done. And when a new disk comes, it has to read a lot and write a lot.

But RAID 5 is cheaper. 1TB of disk will get you 800GB of space (depending on your setup), but RAID 10 will only get you 500GB. Disk activity is slightly more expensive though.

It's "fine" to put data files (not logs) on RAID 5. But if disk is cheap enough, RAID 10 is better.


Same query, same workstation, different execution plans I ran the same query on the same workstation against two different databases that yielded completely different execution plans.  The structure and indexes are the same. The only difference is the data.  The strange thing is the data in “database1” is much less than the data in “database2” and the performance is worse (database2 takes ½ the time as database1).  You’ll notice an obvious design flaw in the ItemProperties table and we plan on changing the structure of this poorly designed table, however before that takes place I’m looking for optimization ideas.  Here is the info for both databases.

Database1:
[Actual Execution Plan] https://www.brentozar.com/pastetheplan/?id=HyVv5-PE-

Database2:
[Actual Execution Plan] https://www.brentozar.com/pastetheplan/?id=rJrzjbDVW

Differences noticed:

The slow plan uses version 120 cardinality estimator
The fast plan uses version 70 cardinality estimator

You could try using query trace flag 9481 to force use of version 70 - just append OPTION (QUERYTRACEON 9481).

Next, the ItemProperties table is handled differently by both cases. The fast plan assumes that there will be at most one property in each join, while the slow plan assumes that there will be two. Assuming that there is in fact only one property value for all cases in question, you could replace the left joins with inner queries. This should result in identical plans on both servers.

EDIT Another optimization opportunity involves using PIVOT operator to fetch all properties at once, like so:

SELECT
vw_Items.ID
,vw_Items.ResolvedEntityID
,vw_Items.ResolvedDescription
,vw_Items.ItemProductNumber
,vw_Items.ItemDescription
,vw_Items.FilteringType
,vw_Items.ItemCategory
,vw_Items.IsDefault
,vw_Items.IsInventoryType
,vw_Items.Active
,vw_Items.PlantFloorVisible
,[dbo].f_ParseDistanceValue([Length]) AS 'Length'
,[dbo].f_ParseDistanceValue(Width) AS Width
,ISNULL(MaterialDescription.[Description], '(none)') AS 'Material'
,ISNULL(GradeDescription.[Description], '(none)') AS 'Grade'
,ISNULL(MillingDescription.[Description], '(none)') AS 'Milling'
,ISNULL(StateDescription.[Description], '(none)') AS 'State'
FROM
vw_Items
-- Join to get properties
left join (
    select [ItemEntityID],[Property],[Value] from [dbo].[ItemProperties]) p
    PIVOT (MAX(VALUE) for [Property] in (Material,Grade,Milling,State,Length,Width))properties on vw_Items.[EntityID] = properties.[ItemEntityID]
LEFT JOIN DescriptionEntity  AS MaterialDescription ON properties.Material = MaterialDescription.EntityID
LEFT JOIN DescriptionEntity  AS GradeDescription ON properties.Grade = GradeDescription.EntityID
LEFT JOIN DescriptionEntity  AS MillingDescription ON properties.Milling = MillingDescription.EntityID
LEFT JOIN #DescriptionEntity  AS StateDescription ON properties.[State] = StateDescription.EntityID

With PIVOT the server will access the ItemProperties table only once, which will likely result in reduced IO.


Bulk SQL INSERT into Azure SQL Database using spark causes blocking/contention? I am running the following code using microsoft's sql sparkconnector to write a 1-2 Billion dataframe into Azure SQL Database.
df.write \
.format("com.microsoft.sqlserver.jdbc.spark") \
.mode("append") \
.option("url", secrets.db.url) \
.option("dbtable", 'tableName') \
.option("user", secrets.db.user) \
.option("password", secrets.db.password) \
.option("batchsize", 1048576) \
.option("schemaCheckEnabled", "false") \
.option("BulkCopyTimeout", 3600) \
.save()

This is a snapshot of the DB Utilization graph
And These are the first few rows from the following query using sp_whoisactive
EXEC sp_WhoIsActive
@find_block_leaders = 1,
@sort_order = '[blocked_session_count] DESC'

The wait_info column's value is Resource_Semaphore.
Configs:
My dataframe is partitioned over 2100 partitions on a cluster of 900 cores
My database is 14 vcores in General Purpose tier on Azure.
My query is incredibly slow because of this blocking. It's almost like it's running one bulk insert from my cluster at a time. Any suggestions on what to change to speed it up? or any insights into why it's blocking?

You could be hitting the Resource Governance limits for the Azure SQL DB.   Query the sys.dm_db_resource_stats while running the insert and you will see more detail on what is happening.
Possible workarounds could be to load into TempDB first of all and then process into your main DB (TempDB has higher resource governor limits) or scale up for the bulk load then scale back down afterwards.   Also consider partitioning of the table you are inserting into and consider the tips here


Index is not used when joining two tables in a stored procedure In MySQL 5.7, I have two tables:

CREATE TABLE `periodholdings ` (
   `stkcode` varchar(255) NOT NULL,
   `participantid` varchar(255) DEFAULT NULL,
   `participantname` varchar(255) DEFAULT NULL,
   `holdingdate` date DEFAULT NULL,
   `shares` bigint(20) DEFAULT NULL,
   `percentage` decimal(5,4) DEFAULT NULL,
   KEY `sp` (`stkcode`,`participantname`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `pholdings` (
   `stkcode` varchar(255) NOT NULL,
   `participantid` varchar(255) DEFAULT NULL,
   `participantname` varchar(255) DEFAULT NULL,
   `holdingdate` date DEFAULT NULL,
   `shares` bigint(20) DEFAULT NULL,
   `percentage` decimal(5,4) DEFAULT NULL,
   KEY `sp` (`stkcode`,`participantname`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `choldings` (
   `stkcode` varchar(255) NOT NULL,
   `participantid` varchar(255) DEFAULT NULL,
   `participantname` varchar(255) DEFAULT NULL,
   `holdingdate` date DEFAULT NULL,
   `shares` bigint(20) DEFAULT NULL,
   `percentage` decimal(5,4) DEFAULT NULL,
   KEY `sp` (`stkcode`,`participantname`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

In the SP, I called the following:

    drop table if exists pholdings;
    create table pholdings (index sp(stkcode, participantname)) as select * from periodholdings where holdingdate = '2016-09-01';

    drop table if exists choldings;
    create table choldings (index sp(stkcode, participantname)) as select * from periodholdings where holdingdate = '2016-09-21';

   explain
    (select p1.stkcode, p1.participantid, p1.participantname, if(c1.shares is null, -p1.shares/100, (c1.shares - p1.shares)/100) as averagediff from pholdings p1 use index(sp) left join choldings c1 use index(sp) on p1.stkcode = c1.stkcode and p1.participantname = c1.participantname)
    union
    (select c1.stkcode, c1.participantid, c1.participantname, if(p1.shares is null, c1.shares/100, (c1.shares - p1.shares)/100) as averagediff from pholdings p1 use index(sp) right join choldings c1 use index(sp) on c1.stkcode = p1.stkcode and c1.participantname = p1.participantname);

However, the index is not used at all.

1   PRIMARY p1      ALL                 407340  100.00
1   PRIMARY c1      ALL sp              412439  100.00  Using where; Using join buffer (Block Nested Loop)
2   UNION   c1      ALL                 412439  100.00
2   UNION   p1      ref sp  sp  1535    ccass.c1.stkcode,ccass.c1.participantname   70012   100.00
    UNION RESULT    <union1,2>      ALL                         Using temporary

If I just put the last EXPLAIN statement into the query and modify a little (like exchange the c1 and p1 around the =), the index works again.

Using where; Using join buffer (Block Nested Loop) is perhaps better than using an index.  It loaded the entire table in RAM so it can quickly probe it.

You seem to be simulating a FULL OUTER JOIN?  That can be made more efficient...

Create only one tmp table; then use a combination of the original and the tmp table.
Don't include the "intersection" in both parts of the UNION, thereby necessitating UNION DISTINCT (which is what you have), but rather the faster UNION ALL.
periodholdings LEFT JOIN tmp gives you the intersection and NULLs on one side
tmp LEFT JOIN periodholdings ... IS NULL gives you the other lobe of the Venn diagram.

Other issues...

If (stkcode,participantname) is unique, make it the PRIMARY KEY; that will speed things up.
Why have a name and id in the same table?  Shouldn't this be "normalized" into another table?
Don't blindly use (255); pick something smaller, but sufficiently big.  This will speed up any tmp tables needed.
decimal(5,4) - You are not going over 9.9999?

Please provide EXPLAIN FORMAT=JSON SELECT ...; it may provide some more details.


foreach loop in sql scripts? In powershell I use foreach to run a function through a list of targets.

I was wondering that if there is any thing similar to foreach in sql, so I can run the same query through multiple DBs I chose and save the results to one csv file?

Absolutely.  We definitely have FOR loops.

https://www.techonthenet.com/sql_server/loops/for_loop.php

From the site:

DECLARE @cnt INT = 0;

WHILE @cnt < cnt_total
BEGIN
   {...statements...}
   SET @cnt = @cnt + 1;
END;

There is also a "FOREACHDB" functionality and even foreachTable

https://www.mssqltips.com/sqlservertip/1414/run-same-command-on-all-sql-server-databases-without-cursors/

https://www.sqlservercentral.com/Forums/Topic271576-5-1.aspx
https://stackoverflow.com/questions/26496864/how-to-loop-through-all-sql-tables

But quite a few articles on why it (foreachdb) may be unreliable.

Here's an Aaron Bertrand version, which is much better:
https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/448

This is the direct link.
https://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/


How important is drive speed for a MySql server I'm trying to invest in a self managed server for MySQL. My use case will involve querying a full text index of anywhere from 5m to 20m records. About 5 thousand full text searches per hour. Usually the searches will each query multiple match against statements as I will need to search multiple columns in the table index. The only write queries will be to update a single field of a single record approximately 10 to 20 times per minute.
My question is this... How beneficial would it be to setup an 8 drive NVMe raid 0? I know indexes are usually queried from RAM as this is usually significantly faster than storage drives. But with the newest PCIe 4.0 NVMe SSDs and the crazy speeds of these drives, I could probably setup raid0 that has about twice throughout as the fastest DDR4 ram.
Or does none if this even matter? Is throughout the factor that even matters? Can you even force indexes to be queried from the storage drive?
Thanks in advance for the advice.

How many GB in the tables?  It sounds like the RAM might be big enough to cache everything all the time?  If that is the case, the disk is irrelevant.
Even if RAM is not big enough, FULLTEXT does not need to hit the disk that much.  Think of a query as being controlled by how many disk hits are needed, minus how many were avoided by caching.
A Rule of Thumb is that a disk hit on HDD is 10ms; on SSD is 1ms.  If the query needs only a "few" hits, the response time on even HDD will be adequate.
I'm saying that FT 5K searches per minute is probably feasible with your proposed configuration!  Even if there were 20 updates per second.
Every action involving the disk actually occurs in the "buffer_pool" in RAM.  So, if the disk must be hit, the time taken for the query is necessarily slower regardless of disk speed.
On the other hand, NVMe is getting cheap, so why not!


Check if a primary key value is referred in any other table I am using oracle database.

Is there a way I can check if the primary key value in one table is being used as a foreign key value (have any associations) in any other table?

Example: if I have the Student_id as primary key in STUDENT table. And there are other tables in the schema like CLASS, MARKS, SPORTS etc.

I have student_id=100 with me. I need to find out all the references of this student_id value '100' in the entire schema wherever it is linked as a foreign key from the parent table STUDENT.

You can check dba_constraints:

Get the primary key constraint name:

select constraint_name
from dba_constraints
where owner = '<SCHEMA_NAME>' and table_name = 'STUDENT' and constraint_type = 'P';

Then check where that constraint is used:

select owner, table_name, constraint_name
from dba_constraints
where r_constraint_name = '<result from previous query>' and constraint_type = 'R';

To get the column name of the referential constraint, you can use dba_cons_columns:

select owner, table_name, column_name
from  dba_cons_columns
where constraint_name = '<constraint name from previous query>';

Plugging all of this together, you can do something like:

  SELECT fk.owner, fk.table_name, col.column_name
    FROM dba_constraints pk
    JOIN dba_constraints fk
      ON pk.constraint_name = fk.r_constraint_name
     AND fk.constraint_type = 'R'
    JOIN dba_cons_columns col
      ON fk.constraint_name = col.constraint_name
   WHERE pk.owner = :owner AND pk.table_name = 'STUDENT' AND pk.constraint_type = 'P';


PostgreSQL. Is using random tag in dollar quoting without escaping input safe? Is that safe to run such query without escaping input? Assume noone can guess the random string I generate.

SELECT * FROM foo WHERE bar = $long random tag$ THIS NOT ESCAPED $long random tag$

What drawbacks are?
For example if the tag will change for each transaction, will it have performance penalties?

EDIT: Doing escaping correctly is also sometimes tricky - remember PHP mysql_escape and mysql_real_escape and sometimes can be fooled or just forgotten. Proposed approach can be automatic.

EDIT 2:
Any ideas if that can be made on pure Postgres SQL? Remember that dollar quoting can be nested.

Postgres doesn't cache ad-hoc SQL query plans; only if your host environment is using prepare will the query plan be cached. But if prepare is used, then parameterisation is also being used so dollar quoting won't be necessary.

It follows, then, if you're not using prepare that each query will be parsed. In that case, you're probably best off accepting the 2 * (32 + 2) = 68 byte penalty and use UUIDs inside dollar quoting.

You could write code to generate a random short string -- eg $a$ and test if that appears in the input, and loop making that string longer if it appears. But the cost of such code might exceed just generating a UUID and using that.


How does paging work with ROW_NUMBER in SQL Server? I have an Employee table that has one million records. I have following SQL for paging data in a web application. It is working fine. However what I see as an issue is - the derived table tblEmployee selects all records in the Employee table (to create the  MyRowNumber values).

I think, this causes selection of all records in the Employee table.

Does it really work so? Or is SQL Server optimized to select only the 5 records from the original Employee table too?

DECLARE @Index INT;
DECLARE @PageSize INT;

SET @Index = 3;
SET @PageSize = 5;

SELECT *  FROM
  (SELECT  ROW_NUMBER() OVER (ORDER BY EmpID asc) as MyRowNumber,*
  FROM Employee) tblEmployee
WHERE MyRowNumber BETWEEN ( ((@Index - 1) * @PageSize )+ 1) AND @Index*@PageSize

An alternative to test might be:

;WITH x AS (SELECT EmpID, k = ROW_NUMBER() OVER (ORDER BY EmpID) FROM dbo.Emp)
SELECT e.columns
FROM x INNER JOIN dbo.Emp AS e
ON x.EmpID = e.EmpID
WHERE x.k BETWEEN (((@Index - 1) * @PageSize) + 1) AND @Index * @PageSize
ORDER BY ...;

Yes, you hit the table twice, but in the CTE where you scan the whole table you are only grabbing the key, not ALL of the data. But you really should look at this article:

http://www.sqlservercentral.com/articles/T-SQL/66030/

And the follow-up discussion:

http://www.sqlservercentral.com/Forums/Topic672980-329-1.aspx

In SQL Server 2012 of course you can use the new OFFSET / FETCH NEXT syntax:

;WITH x AS
(
  SELECT EmpID FROM dbo.Emp
    ORDER BY EmpID
    OFFSET  @PageSize * (@Index - 1) ROWS
    FETCH NEXT @PageSize ROWS ONLY
)
SELECT e.columns
FROM x INNER JOIN dbo.Emp AS e
ON x.EmpID = e.EmpID
ORDER BY ...;

I also blogged about this in more detail here:

SQL Server v.Next (Denali) : Using the OFFSET clause (paging)
Pagination with OFFSET / FETCH : A better way


unable to add data to table because related record is required I have an access database with my work that I am trying to insert into but I keep getting.

  'You cannot add or change a record because a related record is required in table 'Projects'.'

I'm running this query: INSERT INTO Tasks (Assigned,Project,Description) VALUES (@assign,@project,@description)

On this Structure:

With this code in C# with an OleDb... commands and connections Which are working fine for other query's:

//the assign id is already known and is of type integer.
            string query = "SELECT Project_ID FROM Projects WHERE Project_Name = @project";
            OleDbConnection con = new OleDbConnection(con_string);
            OleDbCommand cmd = new OleDbCommand(query, con);
            cmd.Parameters.AddWithValue("@project", projectComboBox.Text);
            con.Open();
            OleDbDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                project_id = Convert.ToInt16(reader[0]);
                Console.WriteLine(project_id);
            }

            con.Close();

            Console.WriteLine("submit: " + project_id + " " + employee_id + " " + descriptionTextBox.Text + " " + monthCalendar1.SelectionStart.ToString("MM/DD/YYYY"));
            Console.WriteLine(monthCalendar1.SelectionStart);
            query = "INSERT INTO Tasks (Assigned,Project,Description) VALUES (@assign,@project,@description)";
            con = new OleDbConnection(con_string);
            cmd = new OleDbCommand(query, con);
            cmd.Parameters.AddWithValue("@project", project_id);
            cmd.Parameters.AddWithValue("@assign", employee_id);
            cmd.Parameters.AddWithValue("@description", descriptionTextBox.Text.ToString());
            //cmd.Parameters.AddWithValue("@deadline", monthCalendar1.SelectionStart);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            this.Close();

I have tried looking at other examples of this problem and I don't understand why I'm getting this error. @project has a valid id number of the primary key for a Project, @assign has a valid employee id as well and @description is string of text. Thanks for any help.

The answer is that I was adding the parameters in the wrong order. All of your parameters have to line up in order.


SQL Server sp_addrolemember in stored procedure for different database I have a stored procedure to restore the latest backup of a prod database to an acceptance database. Once it's restored I need to grant a user read access to the database.

Use statements are not allowed in stored procedures, and both ALTER ROLE (it's a SQL Server 2012 box) and sp_addrolemember don't take a target database parameter.

Ideas?

EXEC('USE mydb;ALTER ROLE myRole ADD MEMBER myMember;');

But I am not quite sure what you are trying to achieve. After restoring a database to a different server, you need to fix the link between users and logins. For that you can execute this in your database:

ALTER USER myUser WITH LOGIN myLogin;

Or use the same EXEC('USE...') method shown above with that ALTER USER statement. That should get you where you need to be. All the database related permissions are stored in the database itself, so they get restored with the database.


Building Dynamic Oracle Where Clause I'm working on application that uses dynamic query to do a select statement based on user input, after discussing security with DBAs they want me to convert my dynamic select statement into Stored Procedure.

I have built dynamic sql using MSSQL but I can not figure out how to convert it to Oracle SQL.

CREATE PROCEDURE GetCustomer
@FirstN nvarchar(20) = NULL,
@LastN nvarchar(20) = NULL,
@CUserName nvarchar(10) = NULL,
@CID nvarchar(15) = NULL as
DECLARE @sql nvarchar(4000),
SELECT @sql = 'C_FirstName, C_LastName, C_UserName, C_UserID ' +
'FROM CUSTOMER ' +
'WHERE 1=1 ' +

IF @FirstN  IS NOT NULL
SELECT @sql = @sql + ' AND C_FirstName like @FirstN '
IF @LastN  IS NOT NULL
SELECT @sql = @sql + ' AND C_LastName like @LastN '
IF @CUserName IS NOT NULL
SELECT @sql = @sql + ' AND C_UserName like @CUserName '
IF @CID IS NOT NULL
SELECT @sql = @sql + ' AND C_UserID like @CID '
EXEC sp_executesql @sql, N'@C_FirstName nvarchar(20), @C_LastName nvarchar(20), @CUserName nvarchar(10), @CID nvarchar(15)',
                   @FirstN, @LastN, @CUserName, @CID

*please note that I want to prevent SQL injection I do not want to just add string together

**i have built a separate class for creating this dynamic query for my application in .net I have almost 1000 lines of code to handle everything and prevent sql injection, but DBAs have told me that they want stored procedures so they can control input and output.

This might give you an idea:

create table Customer (
  c_firstname varchar2(50),
  c_lastname  varchar2(50),
  c_userid    varchar2(50)
);

insert into Customer values ('Micky' , 'Mouse', 'mm');
insert into Customer values ('Donald', 'Duck' , 'dd');
insert into Customer values ('Peter' , 'Pan'  , 'pp');

create or replace function GetCustomer(
  FirstN    varchar2 := null,
  LastN     varchar2 := null,
  CID       varchar2 := null
) return sys_refcursor
as
  stmt varchar2(4000);
  ret sys_refcursor;
begin
  stmt := 'select * from Customer where 1=1';

  if  FirstN is not null then
      stmt := stmt || ' and c_firstname like ''%' || FirstN || '%''';
  end if;

  if  LastN is not null then
      stmt := stmt || ' and c_lastname like ''%' || LastN  || '%''';
  end if;

  if  CID is not null then
      stmt := stmt || ' and c_userid like ''%' || CID || '%''';
  end if;

  dbms_output.put_line(stmt);

  open ret for stmt;
  return ret;
end;
/

Later, in SQL*Plus:

set serveroutput on size 100000 format wrapped

declare
  c sys_refcursor;
  fn Customer.c_firstname%type;
  ln Customer.c_lastname %type;
  id Customer.c_userid   %type;
begin
  c := GetCustomer(LastN => 'u');

  fetch c into fn, ln, id;
  while  c%found loop
      dbms_output.put_line('First Name: ' || fn);
      dbms_output.put_line('Last Name:  ' || ln);
      dbms_output.put_line('user id:    ' || id);

      fetch c into fn, ln, id;
  end loop;

  close c;
end;
/

Edit: The comment is right, and the procedure is subject to SQL injection. So, in order to prevent that, you could go with bind variables such as in this modified procedure:

create or replace function GetCustomer(
  FirstN    varchar2 := null,
  LastN     varchar2 := null,
  CID       varchar2 := null
) return sys_refcursor
as
  stmt varchar2(4000);
  ret  sys_refcursor;

  type parameter_t is table of varchar2(50);
  parameters parameter_t := parameter_t();
begin
  stmt := 'select * from Customer where 1=1';

  if  FirstN is not null then
      parameters.extend;
      parameters(parameters.count) := '%' || FirstN || '%';
      stmt := stmt || ' and c_firstname like :' || parameters.count;
  end if;

  if  LastN is not null then
      parameters.extend;
      parameters(parameters.count) := '%' || LastN || '%';
      stmt := stmt || ' and c_lastname like :' || parameters.count;
  end if;

  if  CID is not null then
      parameters.extend;
      parameters(parameters.count) := '%' || CID || '%';
      stmt := stmt || ' and c_userid like :' || parameters.count;
  end if;

  if    parameters.count = 0 then
        open ret for stmt;
  elsif parameters.count = 1 then
        open ret for stmt using parameters(1);
  elsif parameters.count = 2 then
        open ret for stmt using parameters(1), parameters(2);
  elsif parameters.count = 3 then
        open ret for stmt using parameters(1), parameters(2), parameters(3);
  else  raise_application_error(-20800, 'Too many parameters');
  end   if;

  return ret;
end;
/

Note, that now, whatever the input, the select statement becomes something like select ... from ... where 1=1 and col1 like :1 and col2 :2 ... which is obviously much safer.


Query agent jobs for a given notification operator name Is there a good way to query sql agent jobs for success / failure notification to a given operator name?
One of the operators is no longer with the company and need to fix up all agent jobs sending failure notifications to that person.

Please try following T-SQL query:
 USE [msdb]
GO

   SELECT j.[name] AS [JobName]
    FROM [dbo].[sysjobs] j
    LEFT JOIN [dbo].[sysoperators] o ON (j.[notify_email_operator_id] =o.[id] )
    WHERE o.[id]=1 ----or o.[name]='your operator name'
     and j.[enabled] = 1
        AND j.[notify_level_email] IN (1,2)

This should be able to query the jobs for success or failure notification to a specified operator.


High I/O for part of query I have a large query that I'm trying to tune. I write lots of queries but haven't done a lot of tuning. I've included a screenshot from SQL Sentry Plan Explorer Free (SSPEF):

In the above part of the plan the table pb_WorkRquestLog contains 229,001 rows. However, the query plan is showing approx. 348 million rows (229,001 x 1,520 iterations):

There is no where clause so the query is using a Clustered Index Scan. I've rebuild all indexes with FULLSCAN and updated all statistics.

The code that this part of the plan is executing is:

select distinct
            wrs.ServiceKey
            , owner.DepartmentName AS GroupName
            , owner.UserName AS UserId
            , owner.WRLCreateDateTime
            , owner.WRLNotes
            , wrx.CreatedDate as WRCreateDateTime
            , wrx.Id
            , wrx.Description
            , cast(wrx.Notes as varchar(2500)) as Notes
        from
            prism72ext.dbo.pb_WorkRequestService wrs
            Join prism72ext.dbo.pb_WorkRequest wrx on (wrs.WorkRequestId = wrx.Id and wrx.Status = 'Incomplete')
            left join (
                select
                    wl.WorkRequestId
                    ,   d.Name AS DepartmentName
                    ,   u.UserName
                    , wl.CreatedDate as WRLCreateDateTime
                    , cast(wl.Notes as varchar(2500)) as WRLNotes
                from
                    (
                        SELECT
                            MAX( Id ) AS Id
                        FROM
                                prism72ext.dbo.pb_WorkRequestLog WITH(INDEX(0))
                        GROUP BY
                            WorkRequestId
                    ) mwl
                    join prism72ext.dbo.pb_WorkRequestLog wl on mwl.Id = wl.Id
                    join prism72Ext.dbo.pb_Department d ON wl.DepartmentId = d.DepartmentId
                    left join prism72Ext.dbo.pb_User u on wl.UserId = u.UserId
                ) owner on wrs.WorkRequestId = owner.WorkRequestId

SSPEF is reporting the Actual Data Size is nearly 5GB, with 3 million Logical Reads! SSMS reports the table to be only 16 MB in size.

I've tuned the query down from 4m28s to 1m35s, but now I'm stuck. I'd be grateful if anyone could point me in the right direction to deal with this.

Edit: Someone suggested trying "OPTION (HASH JOIN, MERGE JOIN)". This made a dramatic difference. I/O was massively reduced and the query ran in 13 seconds.

Does anyone see any issues with this solution?

If you don't already have one, create a composite nonclustered index on prism72ext.dbo.pb_WorkRequestLog(WorkRequestId, Id DESC) and run the query without the index hint.

Also, have your inner select for the owner derived table return mwl.WorkRequestId instead of ml.WorkRequestId.


Migrating from SQL Server to MySQL using MySQL Workbench tool I'm trying to migrate few tables from SQL Server to MySQL using MySQL Workbench migration wizard. All work fine for structure migrations but when I go to the data migration section it throws an error for one table:

  ERROR: dbo.Documents:SQLExecDirect(SELECT [DocumentID],
  [CategoryID], CAST([DocumentName] as NVARCHAR(255)) as [DocumentName],
  [Active], [NavigatorID], CAST([DocumentText] as NTEXT) as
  [DocumentText], [UseSubtitle], CAST([DocumentSubtitle] as
  NVARCHAR(255)) as [DocumentSubtitle], CAST([DocumentPlainText] as
  NTEXT) as [DocumentPlainText], [DocumentType], CAST([DocumentLink] as
  NVARCHAR(255)) as [DocumentLink], [Sitemap], CAST([SubtitleImage] as
  NVARCHAR(255)) as [SubtitleImage], CAST([MetaTags] as NVARCHAR(8000))
  as [MetaTags], CAST([MetaDescription] as NVARCHAR(8000)) as
  [MetaDescription], [AccessLevel] FROM [ctool_test].[dbo].[Documents]):
  42000:1131:[Microsoft][ODBC SQL Server Driver][SQL Server]The size
  (8000) given to the convert specification 'nvarchar' exceeds the
  maximum allowed for any data type (4000).

  2131:[Microsoft][ODBC SQL Server Driver][SQL Server]The size (8000)
  given to the convert specification 'nvarchar' exceeds the maximum
  allowed for any data type (4000).

Based on that what I can understand it limits columns with nvarchar data to max size of 4000 when MySQL can handle 65535.

Any clue how I can get this to work?

Thanks

Well, since you have data currently stored in SQL Server, and it's already in an NVARCHAR column, then either it's an NVARCHAR <= 4000 (in which case you can't lose any data, and should just change all instances of NVARCHAR(8000) to NVARCHAR(4000)), or it's an NVARCHAR(MAX) column, in which case you change all instances of NVARCHAR(8000) to NVARCHAR(MAX). Or just leave out those CASTs - do you really need them?

As an aside, you should probably change as NTEXT to as NVARCHAR(MAX) as well.


Select from a table if conditions met in joined table I have two tables

members

+-----------+
|id   | name|
+-----------+-
| 1   | john |
| 2   | adam |
| 3   | sara |
+-----------+-

keys

+-----------+
|id   | color|
+-----------+-
| 1   | blue |
| 2   | green|
| 3   | red  |
+-----------+-

and a third joined table

members_keys

+-----------+---------
| member_id | key_id |
+-----------+---------
| 1         | 1      |
| 1         | 3      |
| 2         | 1      |
+-----------+--------

both rows are assigned as primary key and foreign key

What I need is to get john's keys (keys 1 and 3 in this example)

This is what I am doing

select * from keys join members_keys on keys.id = members_keys.key_id where members_keys.member_id = 1;

from my limited testing I got the required rows, but I feel there is a better way to do it and this solution looks like wasting a lot of resources (comparing each row in keys with all rows in members_keys)

Comparing each row in keys with all rows in members_keys is not how the join is actually implemented.

This is the query plan without any indexes:

create table members(id int, name text);
create table keys(id int, color text);
create table members_keys(member_id int, key_id int);
explain select * from keys join members_keys on keys.id = members_keys.key_id where members_keys.member_id = 1;
                                QUERY PLAN
--------------------------------------------------------------------------
 Hash Join  (cost=38.39..66.55 rows=70 width=44)
   Hash Cond: (keys.id = members_keys.key_id)
   ->  Seq Scan on keys  (cost=0.00..22.70 rows=1270 width=36)
   ->  Hash  (cost=38.25..38.25 rows=11 width=8)
         ->  Seq Scan on members_keys  (cost=0.00..38.25 rows=11 width=8)
               Filter: (member_id = 1)

The database first goes through all rows of the members_keys table, and only those with the matching member_id are inserted into a hash table. It then goes through all rows in the keys table (once), and searches for matching entries in the hash table. (Hash lookups are very fast; their speed is ideally independent of the size of the hash table.)

If we add primary keys with their implicit indexes, then the join works pretty much the same, except that the member_key = 1 check can be sped up with index lookups:

alter table members add primary key(id);
alter table keys add primary key(id);
alter table members_keys add primary key(member_id, key_id);
explain select * from keys join members_keys on keys.id = members_keys.key_id where members_keys.member_id = 1;
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Hash Join  (cost=15.05..41.09 rows=11 width=44)
   Hash Cond: (keys.id = members_keys.key_id)
   ->  Seq Scan on keys  (cost=0.00..22.70 rows=1270 width=4)
   ->  Hash  (cost=14.91..14.91 rows=11 width=4)
         ->  Bitmap Heap Scan on members_keys  (cost=4.24..14.91 rows=11 width=4)
               Recheck Cond: (member_id = 1)
               ->  Bitmap Index Scan on members_keys_pkey  (cost=0.00..4.24 rows=11 width=0)
                     Index Cond: (member_id = 1)

The database did rewrite your query into something like the following (except that select * returns columns from both tables):

select * from keys where id in (select key_id from members_keys where member_id = 1);

And depending on database version, options, and statistics, the database might do something entirely different. But it will not do an actual cross join if it can avoid it.


Materialized view: compilation_error When executing the following code on the SCOTT schema I receive a compilation_error.
What am I doing wrong here? I doesn't even tell me what the error is.

SQL> create materialized view dept_emp_mv as (
  2      select dname, job, hiredate, count(*) nbr
  3                  from emp e
  4                  join dept d on e.deptno = d.deptno
  5                  group by dname,hiredate,job);
Materialized view created.
SQL>
SQL> select staleness from user_mviews where lower(mview_name) = 'dept_emp_mv';
STALENESS
-------------------
FRESH
SQL>
SQL> insert into emp values (8003, 'TEST', 'TEST', 7902, to_date('20131127','YYYYMMDD'), 2500, null, 20);
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select staleness from user_mviews where lower(mview_name) = 'dept_emp_mv';
STALENESS
-------------------
NEEDS_COMPILE
SQL>
SQL> execute DBMS_SNAPSHOT.REFRESH('DEPT_EMP_MV','C', parallelism => 1);
PL/SQL procedure successfully completed.
SQL>
SQL> select staleness from user_mviews where lower(mview_name) = 'dept_emp_mv';
STALENESS
-------------------
COMPILATION_ERROR

Any help is appreciated.

Update

With Oracle 12c this issue seems to be resolved.

It seems that Oracle doesn't like ANSI-style joins in the materialized view definition when refereshing...

Changing the definition to

create materialized view dept_emp_mv as
   select dname, job, hiredate, count(*) as nbr
               from emp e, dept d
               where e.deptno = d.deptno
               group by dname,hiredate,job;

makes it work for me.

See http://sqlfiddle.com/#!4/f706b/1

Changing the definition in the SQL Fiddle back to the one included in the question will cause the staleness to be COMPILATION_ERROR.

Personally, I would classify this as a work-around rather than an answer.


Do varchar/text/longtext columns in MySQL occupy full size in filesystem even for partially filled cells? Do varchar/text/longtext columns in MySQL occupy full length size in filesystem even for incomplete cells?

Referring to this blog article for example I have the following columns and i have put in a given string in it.

CHAR (72) - can store upto 72 characters - i put in a string "abcd"
VARCHAR (72) - can store upto 72 characters - i put in a string "abcd"
TINYTEXT - can store upto 255 characters - i put in a string "abcd"
TEXT - can store upto 65,535 characters - i put in a string "abcd"
LONGTEXT - can store upto 4,294,967,295 characters - i put in a string "abcd"

How much actual physical space will be occupied by each of these columns for a row? The full size upto which each column is capable of storing? Or only as much as required for storing "abcd"?

'abcd' in CHAR(72) CHARACTER SET ascii occupies 72 bytes on disk.
'abcd' in CHAR(72) CHARACTER SET utf8 occupies 3*72 bytes on disk.
'abcd' in CHAR(72) CHARACTER SET utf8mb4 occupies 4*72 bytes on disk.
'abcd' in VARCHAR(72) occupies 1+4 bytes on disk.
'abcd' in TINYTEXT occupies 1+4 bytes on disk.
'abcd' in TEXT occupies 2+4 bytes on disk.
'abcd' in LONGTEXT occupies 4+4 bytes on disk.

Update:  In some versions, InnoDB will store only 4 bytes for the CHAR cases.

Notes:
The 1,2,4 is for length; VARCHAR could be 2 in some situations.
The CHARACTER SET is important in all the cases, but does not impact the space occupied by 'abcd' except for CHAR.

@akuzminsky -- You are wrong about the *3.  CHAR(N) and VARCHAR(N) can hold up to N characters in the declared CHARACTER SET.  That will become up to 3*N bytes for CHAR or 1+3*N for VARCHAR.

TEXT is limited to 65535 bytes (plus 2-byte length).

There is overhead on top of the lengths described above.  In InnoDB, there are record flags, transaction ids, blocking factors, pointers to overflow areas, etc, etc.  So, if you use this kind of arithmetic, it will underestimate the disk space used.

MyISAM is more frugal; it has at least 1 byte of overhead per record; in some cases, only 1.


How to handle temporary tables with mutliple connections? In Is there a way to hint to query optimizer to MySQL which constraints should be done first? I created a subquery.

The subquery is often not optimized.

I think a better way is to create a temporary table and then query that temporary table.

The problem is say I create a temporary table and name that "BusinessesWithinABox" table, which are businesses in the area I am interested in, say 10 by 10 km squares.

Say I have 300 users.

All of which create this temporary tables named BusinessWithinABox. Yes I know that the tables are temporary.

Then when I do query, which tables go which?

Actually some tutorial detail on how to create temporary tables and run and use that in PhP would be fine.

A temporary table is only visible to the connection which created it. There could be 300 temporary tables, all by the same name, and non interrupting or conflicting with each other. In fact, you cannot access temporary tables created by other connections.

So when you query, you get results from the temporary table created by the same connection.

If you close a connection, the temporary table gets dropped. So, moral of the story, create a temporary table then query it, all within the same connection.


How to get the average value of each second? MySQL I wonder how to output the average data of each second.
My data set is in millisecond and I can out them in millisecond by

SELECT * FROM some_table
where RealTime > '2019-09-10 23:00:00' LIMIT 200;

Below is the Data Format

AcX       RealTime
-15836   2019-09-05 15:02:37.502
-16666   2019-09-05 15:02:37.508
-17645   2019-09-05 15:02:37.512
...

But how can I get the average data in each second? Thx

SELECT  LEFT(RealTime, 19) AS to_the_second,
        ROUND(AVG(AcX))  AS Average_AcX
    FROM some_table
    GROUP BY 1;

Notes:

Often it is practical to treat a datetime (or timestamp) as a string, as I am doing with the LEFT.
You may want to add the number of decimal places to the ROUND() function call.
The 1 in GROUP BY 1 is shorthand for 1st column (the LEFT...).


EC2 - How to correctly back up PostgreSQL data? Here is the setup: 1 small Amazon Linux (EBS-backed) EC2 instance with 3 additional volumes. This is both a web server and database server. One volume for code, one for the PostgreSQL (8.4) data directory, and one volume to store WAL files from PostgreSQL.

(1) The volume with WAL files will also have a base backup of the data directory, which is copied over after doing a pg_start_backup(). Then it will store the continuous archive output from PostgreSQL (WAL files). To snapshot this volume, is there any point in issuing a sync and freezing the filesystem (using xfs_freeze if it's XFS or dmsetup if it's EXT4)? Or can I just take a live snapshot? WAL files will be shipping at a rate of one per minute. Is it possible that a snapshot could be initiated while a single WAL file is being copied over and resulting in corrupt data?

(2) The volume that contains the live PostgreSQL data directory will also be backed up for good measure (daily). Before doing a snapshot of this volume, I issue a pg_dump and the resulting SQL file is kept in the data directory. Is there any point in taking precautions to ensure the actual database data is consistent? Would it be correct to assume that taking a live snapshot will properly (a) backup config files (postgresql.conf, pg_hba.conf, pg_ident.conf) and (b) backup the SQL dump file. Backing up those two things, sql dump file and config files, would be the main point of snapshotting this volume. The DB is not very big so I don't mind the fact that data files will bloat this snapshot. And in that case, I can just do a live snapshot -- correct?

(2a) Would it just be better to keep the data directory on the root volume, and have a backup script that copies the sql dump file as well as config files onto another volume, and snapshot that volume once the copy is done?

(3) As for the volume with code on it, again is there any point in sync'ing and freezing the filesystem? Or can just a live snapshot be taken? This data should be fairly "static".

(4) Is this a solid backup scheme? The root volume is not backed up on a regular basis since I will just keep a machine image after it is set up and configured.

Thanks

See the fine manual. If my advice conflicts with its' in any way, it's right.

A sync isn't a bad idea, unless your copy tool fsync()s each WAL file it writes and the directory it's in before copying the next one. An incomplete last WAL file doesn't matter much; at worst, you just delete it. Pg will generally choke on an incomplete WAL - though there's no checksumming done, so you could be really unlucky and have it try to apply garbage data that by sheer insane chance happened to look like real WAL records. In your position I'd be syncing the volume before a snapshot to make sure any unwritten dirty buffers in RAM hit the file system image on disk. A freeze would help avoid messy but non-fatal partially written WALs, so it's not a terrible idea but not vital.  What's vital is to have an undamaged timeline up until the point of recovery. Personally, I write my WALs to a temporary file name and rename them to their final name only once fully copied; if you do this, you don't need to freeze.
Sounds correct. A live snapshot is just like doing a plug pull test on a live system with write-through caching. Your database should recover fine when restored from a live snapshot, same as after plug-pull. I'd recommend that you automate tests of restores from snapshots. (Note: A snapshot restore test is not a complete substitute for plug pull testing because it doesn't account for possible disk, raid controller, etc write caching). Not only the config files and the dump, but the database its self should be fine after your snapshot. Consider syncing the volume before the snapshot to make sure all the dump data etc has actually hit disk.
2a. Might save some disk space. Little difference otherwise. You'll get to keep the snapshots a lot longer without all the churn of the live database on them.
Why even snapshot your code volume? A plain file level copy may well be just fine. Certainly a live snapshot should be.
This is not a solid backup scheme. It fails in one critical area: There is no restore testing and validation being performed. You should always test your backups on a regular basis to make sure you can really restore them.

Personally, I recommend that you use WAL shipping, or send database dumps, to a different host, preferably one not on Amazon EC2 or at least in a different region. This host should perform automated restore tests, send reports to you of the results, and should also be checked manually.

While your snapshots (containing dumps) will be on S3, and will be safe there, that doesn't mean they'll be accessible when you need them urgently. Amazon's durability claims are reassuring, but your data can still be safe and completely inaccessible to you during a badly timed outage of the S3 service.


How to generate a unique barcode without using identity or sequence in SQL Server 2012 We're currently building an application where customers need to have 1 unique barcode. Each time a new customer has been added to the system, a new unique barcode needs to be generated for that customer.

We're using 12 digits for this barcode, but the first 6 digits are fixed, leaving me with only 6 digits left to generate a unique number.

At first I wanted to do this based on the primary key of the table customer, which is an identity column. And this would have worked prior to SQL Server 2012 because apparently, whenever the SQL Server 2012 service gets (forcefully) restarted, the identity column jumps by 1000 or 10000 (depending on the datatype of your column), which would skyrocket the amount of digits being used, risking to exceed the 6 digit limit. Same with the Sequence functionality.

Can anyone point me out how you would do it? I've been thinking of storing a number myself in a column with a unique constraint and add 1 to it each time a customer has been added but this makes me feel very uncomfortable.

Thanks for reading this far, any advice is appreciated.

Zeep

Why do you feel uncomfortable storing the id yourself?
This is a valid approach for this problem.

You could also use -T272 as a startup parameter to prevent the gaps during id generation.
I read that this could have impact on the performance of the id generation, but I never had no problems in my tests.
See this link for further information: http://www.dfarber.com/computer-consulting-blog/2014/2/13/how-to-solve-identity-problem-in-sql-2012.aspx

Please consider that there are many different types of barcodes. If you use the common GS1-128 for example, you can create alpha-numeric codes to increase the possible combinations and prevent an overflow.


Slow update query in mysql (mariadb) I have 2 tables:

Create table Files (
   FileId BigInt unsigned not NULL auto_increment,
   FileName varchar(255) not NULL,
   PRIMARY KEY (FileID),
   UNIQUE KEY FileName (FileName)
);
Create table FileRevisions(
   fileRevId BigInt unsigned not NULL auto_increment,
   FileID BigInt unsigned not NULL,
   RevisionName varchar(255) Not NULL,
   deletionFlag boolean default 0,
   PRIMARY KEY (FileID),
   KEY FileRev_FileID_FK_idx(FileId),
   CONSTRAINT FileRev_FileId_FK FOREIGN KEY (FileID)
     REFERENCE Files(FileID)
     ON DELETE CASCADE
     ON UPDATE CASCADE
);

I have the following code in ADD_FILE(File_Name, Revision_Name) procedure:

Create Procedure Add_File(File_Name varchar(255), Revision_Name varchar(255))
BEGIN
    declare file_id;
    Insert into files(FileName) values (File_Name)
                  ON DUPLICATE KEY UPDATE FileId=LAST_INSERT_ID(FileId);
    set file_id = LAST_INSERT_ID();
    set @rowNum = 0;
    update FileRevisions set deletionFlag=true where fileRevId in
    (
     SELECT fileRevId from
     (
       select @rowNum:=@rowNum+1 as rowNumber, fileRevId from FileRevisions
       Where FileId = file_id and deletionFlag=false order by fileRevId
     ) tmp where rowNumber>=5
    );
    Insert Into FileRevisions (fileId,RevisionName) values (file_id,Revision_Name);
END;

When I call the procedure and have approx. million records, it take about 1 second to run.
from tests I figured the update is the slowly parts, (the inserts are fast)
I tried to run the inner select queries and it was also fast, the updtae was the problem, even where the inner select return no rows...

Why is the update so slow?
is there a faster way to do the update?

Don't use IN ( SELECT ... ).  But it seems to be necessary due to the @row_num computation.

Don't use @row_num computation if you can do the equivalent with ORDER BY ... LIMIT ....

What are you trying to do?  Set delete_flag=true for all but the latest 5 rows?  "Latest" is defined by fileRevId?

"Million records"?  In which table?  Or do you mean that you need to call the proc a million times?

In checking for 5 rows, are there cases where you have thousands?  I ask because 5+ rows can possibly be optimized one way, but 1000+ rows may need a different optimization.

Please provide SHOW CREATE TABLE FileRevisions, I need to check what composite indexes you have.

Update

The schema, as you have it, does not allow for more than one revision per FileId -- because of PRIMARY KEY(FileId) on FileRevisions.  Also AUTO_INCREMENT demands to be the first column in some index.  So, I recommend

 PRIMARY KEY (FileID),
 KEY FileRev_FileID_FK_idx(FileId),

-->

 PRIMARY KEY (FileID, fileRevId),
 INDEX(fileRevId)


Backing up & restoring 10-20 SQL Server databases to a ~synchronous state? I need to backup up 10-20 SQL Server 2008 R2 databases with sizes between 10-50 GB, while they are online and used simultaneously by a single enterprise app. I also need to restore them to a state that is largely synchronized across all databases (I can afford up to a few seconds of desync between databases). The purpose is to capture production data for QA/DEV environments.

I would strongly like to not demand databases run in full recovery and to come up with a backup method that is dedicated to capturing data for QA environments and remains independent of a main backup process which is not under my control.

For my customers, it will take 1-2 hours to capture 20 full backups at ~30 GB each. This makes taking full backups sequentially unacceptable as the databases would be too desynchronized when running in simple recovery.

I'm looking for an idea better than these:

IDEA 1: SAN-level snapshot of VM disks. xcopy MDFs/LDFs from snapshot.

Once the copied files are attached to a different server instance, its recovery process should produce consistent databases that are snapshot pretty much simultaneously.
Googling around convinced me this is a bad idea, at least because I may get desync vs. master/msdb/etc.

IDEA 2: Orchestrate a complex backup & sync-restore across all databases

This requires me demanding databases run in full recovery, which I don't want. Start parallel backups for all databases well before the deadline (T0). Once T0 is reached, backup all logs (should take at most a few minutes). Take the resulting myriad of backups and try to restore them & roll logs forward/back to obtain a somewhat consistent state across databases, relative to T0.
This requires a lot of planning & scripting to have it used reliably so I would go to great lengths to avoid it.

Am I missing some other solution?

P.S.1: I would've loved being able to use db snapshots. The idea was to initiate a snapshot on each db (which should be over in seconds), then fully backup each one sequentially over the following minutes/hours. Then restore all of them on a different server and revert each one to the snapshot. AFAIK this scenario is not possible because snapshots can't be backed up along with the database. They can only be rolled back in place, on the server where they were created. In addition, they require Enterprise Edition which I don't have for all customers.

P.S.2: If you know of a 3rd party solution capable of producing cross-db synchronized backups please mention it.

I need to backup up 10-20 SQL Server dbs used simultaneously by a single enterprise app, while they are online, in such a way as to restore them to a state that is largely synchronized across all dbs

What you are looking for is a consistent backup across all your customer databases, you should use FULL backups along with Marked Transactions (emphasis in bold added):

  When you make related updates to two or more databases, related databases, you can use transaction marks to recover them to a logically consistent point. However, this recovery loses any transaction that is committed after the mark that was used as the recovery point. Marking transactions is suitable only when you are testing related databases or when you are willing to lose recently committed transactions.

Make sure that you take adhoc transaction log backup with COPY_ONLY, else your recovery will be a pain, since any adhoc transaction log backup without COPY_ONLY will break the log chain. As a precaution, you can restrict users to use only COPY_ONLY backups.

  I need a solution for SQL Server versions 2008 R2 and later. Db sizes is up to 50 GB per db and the time to backup all of them is likely over 1-2 hours.

Marked transactions will work for your situation. The only thing to make parallel backups is to  STRIPE them, but then you end up making sure that you don't lose your stripes of backup. To make them faster, you can play with BUFFERCOUNT and MAXTRANSFERSIZE.

You should use backup compression as well as enable Instant file initialization.

Refer to

Using Marked Transactions (Full Recovery Model)
How to increase SQL Database Full Backup speed using compression and Solid State Disks ?


Best practice for multiple 0,1 to many relationships I'm an application developer without a ton of database design experience and I'm wondering if someone can suggest a design/implementation for relating one table to one or more other tables.

For example, if I have Account, Policy and Claim tables, each may have 0 or more Contacts.

I could define multiple foreign key fields in the Contacts table, but there must be a better way.

i.e.

varchar(50) contact_name
varchar(100) contact_address
bigint Account_ID
bigint Policy_ID
bigint Claim_ID

This will really depend on many factors, beginning with your choice of RDBMS, and followed by your application language and framework. The choice will be very different if you use some form of RAILS and mysql (where database design is driven by class design in your application), than PHP and PostgreSQL (where you can choose to use create a CONTACT composite type, and keep contacts as an array of contacts in a column on each of your databases, without a separate table for it).


How to avoid Repetition in the Database Design? I have currently the following database structure,

            Retailers
-----------------------------------------------------------------
ID    Name    XXX    XXXX    XXXX    XXXX    XXXX    XXXX

            ProductTypes
-----------------------------------------------------------------
ID    Name    RetailerID    XXXX    XXXX    XXXX    XXXX    XXXX

            Products
-----------------------------------------------------------------
ID    Name    ProductTypeID    RetailerID    XXX    XXXX

            Attributes
-----------------------------------------------------------------
ID    Name    ProductTypeID    RetailerID    GroupID    XXXX

            AttributesGroups
-----------------------------------------------------------------
ID    Name    ProductTypeID    RetailerID    XXXX    XXXX    XXXX

            AttributesValues
-----------------------------------------------------------------
ID    Value    RetailerID    ProductID    AttributeID    XXXX

Let say if you have a product called HP ProBook 4530s 15.6" Notebook then it may contain a Product Type called Laptop, it may belongs to Retailer1. It may contains Battery, Power, Manufacturer, Processor Name, etc attributes.  May be Manufacturer and Processor are belongs to General attribute group. These attributes values are saved in AttributesValues table.

This database schema works very fine. But now I am facing an issue. Some products are needed to be completely shared between other retailers. Some products are needed to be completely shared except for few attributes between some retailers because some retailers need to show additional attributes of the same product and some retailers need to remove some attributes of the same product and some wants to update some attributes.

Currently, the above schema works with a flaw that each product(and it's related) things are repeated for retailer which uses this product. How to avoid this situation and design the database so that it works smoothly as well as no or limited repeatation.

Track attributes against a product (always) and optionally against a retailer.  If the attibute value is used by all retailers then the retailer foreign key would be NULL.

When you join attributes to a product include an OR clause in either your join or where (depending on how you do your joining) so that you select out attribute values where the retailer FK is as given or where the retailer FK IS NULL.


Excluding rows by lots of ids I need to exclude some rows that the user has already seen. Currently I am doing this by simply comparing the ids that the user has seen to the row's ids by running this query (there are also other fields to filter by that I left out):

SELECT * FROM table WHERE NOT (id IN (ids here)) LIMIT 18;

However, since I have about 150,000 rows in my table, and the amount of ids can be even up to 30,000, this is unbearably slow.

I haven't worked a lot with databases before, so I'm probably doing this completely wrong. Is there any better way to achieve this?

I'm running PostgreSQL 9.3.9

Any help is greatly appreciated!

NOT IN can have slower performance than a left join for some data sets. You can see an example here that compares different ways of excluding items from your selection. Ideally you could identify the IDs you want to include, and this would definitely gain better performance.

So first you could try one of the two query changes depending on your data:

SELECT *
FROM Table
WHERE ID IN (SELECT IDs FROM UserViewingList WHERE UserID = @UserID)

If you're using an app of some sort, you may be able to update a viewing table while the user chews through the first set of data, and then have that available for a quicker response when they request the next set.

Or if you don't have that option/data try replacing NOT IN:

SELECT BaseTable.*
FROM BaseTable
LEFT JOIN ViewedIDs
     ON BaseTable.ID = ViewedIDs.ID
WHERE ViewedIDs.ID IS NULL

The other question though is can you cache this set in an App or is there another way to update the list? You might be able to add pagination or some other tool in your UI that can handle the selection of IDs in memory on a per user basis.

In addition to the query changes, you can add indexing to your User IDs column, if not already indexed (on your source table - BaseTable in the example above).