Memory speed vs. amount I just got talking to DBA as he asked me: What's best, memory speed or memory amount, e.g. 512GB @ 1866MHz or 768GB @ 1333MHz, when running the database in-memory?

We've both been looking for stats, benchmarks and experiences all over the internet, but with no luck, so I thought I'd try my luck here.

There is a good bit of "it depends" here due to the question being asked about both size and speed. I'm going to take the size out of it, either you have enough memory or you don't.

Talking just speed from now on and assuming DDR3 for the memory type.

The difference in transfer amount (theoretical) between the two is as follows:

DDR3-1333: 667MHz * 2 bits/clock (rise and fall) * 64-bit wide channel = 10.67 GB/Sec
DDR3-1866: 933 * 2 * 64 = 14.93 GB/Sec

This means there is (theoretically) 14.93-10.67 = 4.26 GB/Sec difference. Unfortunately that's just the memory part.

Continuing our way down the pipeline, we're going to hit our memory controller, of which almost all are integrated into the processor. Thus we'll have to look at the processor (and motherboard layout) to see the number of possible memory channels and throughput of the controller.

You can continue to do this for your specific hardware and figure out the theoretical difference.

Since you've specifically asked about SQL Server In Memory technology, having the faster memory will almost-always be better. That's not the whole case, though, since the way IMOLTP works is by using a specific assembly instruction (cmpxchg16b), thus having faster memory may or may not help if the bottleneck is the CPU speed (think 2.1 GHz [high density VM cpu] vs 3.2 GHz [low density high throughput cpu]).

Assuming the same processor and that the CPU is not a bottleneck, there is dual or triple channel memory, all memory slots are balanced, etc., etc., then yes having the faster memory would generally be better.


How do you submit a List of items to a Stored Procedure to limit results? Possible Duplicate:
  Passing array parameters to a stored procedure

Lets say you want to filter results by a List of uniqueidentifiers such that your SQL statement would read:

WHERE CustomerID in (@CustomerIDs)

Is this possible?

Edit:

To clarify and answer Nick's question, I'd like to pass in a List of values from C# to SQL Server and be able to do a select similar to the SQL I posted above.  This would be preferable to getting more data than needed and having to search through it since there is an enormous amount of data.

If you are using SQL 2008 or higher you can use a table valued parameter which would allow you to pass in what is basically an array from the .NET side then use the table variable as an in clause an Nick has said.

If you aren't using SQL 2008 or higher then you'll want to pass in the values as an XML document, then use xquery or OPENXML to parse the XML document and put the values into a temp table (or table variable depending on how many values you expect).  Then use the temp table as part of an IN within the WHERE clause.


count distinct values over columns I struggle to get the result for
here is my dataset

A
B
C
cnt

2
1
2

1
5
5

2
1
4

1
1
1

result expected

A
B
C
cnt

2
1
2
2

1
5
5
2

2
1
4
3

1
1
1
1

PG 9.5

A correlated subquery against a VALUES table should do the trick:
SELECT
    t.A, t.B, t.C,
    (SELECT COUNT(DISTINCT v.n) FROM (VALUES(t.A),(t.B),(t.C)) AS v(n)) AS cnt
FROM table t

If you wanted separate calculations on the values such as both counting and summing, you could use a lateral join (a.k.a APPLY) over VALUES instead:
SELECT A, B, C ,D, E, v.countDistinct, v.countNotNull, v.Total
FROM  test,
LATERAL (
    SELECT COUNT(DISTINCT v.n), COUNT(v.n), SUM(v.n)
    FROM (VALUES (A),(B),(C), (D), (E)) AS v(n)
) AS v(countDistinct, countNotNull, Total)


How to connect to remote PostgreSQL server I wrote a web app, using java + spring+ PostgreSQL, which I have up and running.

I have a tester for my system and I want him to be able to connect to my database directly to check the result of interaction with application. As always, the web is littered with articles on how to connect, but one simple thing they don't mention is whether a client should install some app to connect to the database? I get the bit about making correct settings for the database, but if a mere mortal enters the following in their windows console:

psql -h <host> -p <port> -u <database>

they will get 'psql' is not recognized as an internal or external command,
operable program or batch file. I understand I don't get that error because I operate via shell and my home path is set to psql.exe, but again the client doesn't need to install PostgreSQL to access remote a PostgreSQL database. It just doesn't click in my head.

How can a client connect to database via command prompt without installing anything?

I suppose the simplest way is to let the tester use ssh to login to your server. Then the tester's execution environment is whatever your server exposes through the shell. (I presume that would include psql.)

As far as I know, all modern Unix derivatives install ssh by default. That would include Linux, Solaris, OS/X (Apple), and BSD. I believe that Windows does not ship with ssh, but Cygwin and PuTTY are commonly used to remedy that problem.


Running queries on a mirrored database I have a Web forms asp.net site running with a database on a VM (VM1). I also have a MVC 5 Web app that has its own membership database on another VM (VM2). I want to retrieve customer information from VM1 to create a login on VM2.

My thoughts were I could create a backup database(or mirror) for VM1 to run member queries on that to retrieve information. Is this plausible? What methods would you recommend?

One method that I think I am able to obtain, would be creating a webservice on VM1, but it just seems the VM2 would be constantly using this webservice and I really want to obtain the best performance possible. Any help is much appreciated.

With database mirroring, you have to create a snapshot of the database in order to run queries against it.

A few problems there:

Snapshots are an Enterprise Edition feature, which put it out of the price range of most shops
Running queries against the snapshot mean the server is in use, so you have to pay full Enterprise Edition pricing on it (it's not a free standby server at that point)
The queries are only up to date as of when the snapshot was taken
To refresh the snapshot, you have to drop it and take another snapshot (which means getting all users out of the snapshot to drop it)

Because of those, running queries against a mirror isn't usually cost-effective.

If you're willing to stomach the Enterprise Edition price tag, SQL 2012 added Always On Availability Groups, which let you read directly from the secondary with nearly real-time data at a much lower management cost. Still has the EE price tag, though - readable secondaries are an EE-only feature.

Generally speaking, performance tuning is easier than licensing more EE cores ($7k/core USD.)


DataBase Performance Issues with Indexes I'm reviewing a production database with over 400 tables and 658 indexes. I have determined that only 440 of these indexes are being used. Of these, many have fragmentation approaching 100% so I will be rebuilding and reorganizing them as needs require. Many reports are crawling, etc.

One thing I noticed on querying the system views is that all indexes have a fill factor of 0. So my question is twofold: should I aim for a fill factor of 90% and monitor it, and should I adjust the fill factor BEFORE rebuilding indexes?

Fill factor will leave free space on your index pages. If you know that updates on index keys will need aditional space, then it may be ok to leave that 10% free. But if there will be no changes on index keys, it is better to have no free space on index (remember that all pages will generate logical reads on querys, so More pages = More logical reads = slower execution).

If you change fill factor, immediatly you will be asked to do rebuild


Killing idle pooled connections On SQL Server, I'm trying to prevent connection from staying open. From here I understand that Connection Lifetime allows a connection to stay alive for a certain period of time since it was opened.

Quote: When a connection is returned to the pool, its creation time is compared with the current time...

Is there a way to do something similar, but with connections who are idle? When they return to the pool they'd be allowed to live for X seconds until killing them, unless they're used again.

The connection pool will automatically kill connections which haven't been used in a while.  There's no way to identify these from the SQL Server side with 100% accuracy without running the risk of the client application throwing error messages when it attempts to use the connection from the pool which it things should be open put is really closed.

What problem are you trying to solve for?


Can't select 2 tables at the same time I have a pesId (peopleId) column at pessoas (people) where is stored data from medics and patient. Also I have a table that stores medical consult information like Date, patient id, medic id and diagnostic.

I got to select the conDate, Patient Name (from table 'ppl'), Medic Name (also from 'ppl') and Diagnostic but I am having trouble for hours doing this. I am very noob at MySQL but I am trying to learn. May anyone help me with this logic problem?

I tried many methods.

select conDat, pa.pesId = pacientes.pacId, me.pesId = medicos.medId, conDiag, case when null then 'Indefinido' end
    from consultas
    inner join pessoas as pa on pa.pesId = pacientes.pacId
    inner join pessoas as me on me.pesId = medicos.medId;

Thank you very much

This is my creating tables code:

create table consultas (

conId int primary key auto_increment,
conDat date not null,
pacId int not null,
medId int not null,
conDiag varchar (30) );

create table pessoas (

pesId int primary key auto_increment,
pesNome varchar (50) not null);

create table pacientes (

pacId int primary key default 1,
pacTel varchar (20) not null,
pacAtiv bool);

create table medicos (

medId int primary key default 1,
medCPF varchar (20) not null,
medAtiv bool);

If I understand your specifications well, and although I haven't tested this query in the server yet, it should work:

SELECT CO.conDat,
       PA.pesNome                         AS paciente,
       ME.pesNome                         AS medico,
       COALESCE(CO.conDiag, 'indefinido') AS condiag
    FROM  consultas    CO
    INNER JOIN pessoas PA
    ON    PA.pesId   = CO.pacId
    INNER JOIN pessoas ME
    ON    ME.pesId   = CO.medId;

Since this query JOINS the table consultas with pessoas twice in order to retrieve the pessoas.pesNome column values related to medicos and pacientes respectively, it is important that you look with special attention to the aliases given to the PA.pesNome and ME.pesNome columns (by way of the AS keyword in the SELECT statement), because such aliases are used for the purpose of displaying different column headers in the obtained result set, and thus, ambiguities are avoided, since the meaning of each column, paciente or medico, is clear.

For handling the cases when consultas.conDiag contains NULL, the above query takes advantage of the COALESCE() function which, in this particular case, substitutes NULL values in said column with your custom defined string ‘indefinido’.

Alternately, you can try this other query, which shows a different method for replacing the consultas.conDiag column value when it holds NULL, via the IFNULL() function:

SELECT CO.conDat,
       PA.pesNome                       AS paciente,
       ME.pesNome                       AS medico,
       IFNULL(CO.conDiag, 'indefinido') AS conDiag
    FROM  consultas    CO
    INNER JOIN pessoas PA
    ON    PA.pesId   = CO.pacId
    INNER JOIN pessoas ME
    ON    ME.pesId   = CO.medId;


Exclude IP addresses of Vulnerability Scanner I am working with the SQL team to see if there is a way to globally whitelist IP addresses that are causing alerts within sp_Blitz? Would like to have them exclude 5 IP addresses.

sp_Blitz doesn't have parameters to skip IP addresses since SQL Server doesn't store IP addresses for most of the activities.

However, you can skip specific checks altogether if you want. To do that, create a table like this:

CREATE TABLE dbo.SkipChecks (ServerName NVARCHAR(128), DatabaseName NVARCHAR(128), CheckID INT);

Then, you can add rows in there for the checks you want to skip. In the far right column of sp_Blitz's output, the CheckID is listed. (For a comprehensive list of checks and IDs, see the documentation.)

ServerName = ‘MyServer, DatabaseName = NULL, CheckId = NULL – will not check anything on servername MyServer
ServerName = NULL, DatabaseName = ‘MyDB’, CheckId = NULL – will not check MyDB on any server
ServerName = ‘MyServer’, DatabaseName = ‘MyDB’, CheckId = NULL – will not check MyDB on MyServer
ServerName = NULL, DatabaseName = NULL, CheckId = 5 – will skip CheckId 5 on all databases
ServerName = NULL, DatabaseName = ‘MyDB’, CheckId = 5 – will skip CheckId 5 on MyDB


MySQL - terrible performance for simple query on large table I'm having huge troubles with the performance of a very simple indexed query on a quite large table.
Configuration:

48 cores, NVMe Raid, 376GB RAM (144GB used)
Mysql 8 (Percona server)
Server dedicated to DB
Application with low concurrent users
InnoDB
Overall database size: ~1TB
Table size: ~35GB
Table structure:
primary_key (int),
indexed_field (int),
value (int),
plus a few other ints, dates and a varchar

The query: SELECT AVG(value) WHERE indexed_field=[somevalue] => takes 220s for ~2M rows
Here is the associated explain

I tried the following other queries to try and understand what was going on:

SELECT COUNT(*) WHERE indexed_field=[somevalue] => 1s (~2M rows)
SELECT AVG(primary_key) WHERE indexed_field=[somevalue] => 1s (~2M rows)
SELECT AVG(value) WHERE indexed_field=[anothervalue] => 0s (~15k rows)

I understand the first two use only the index without needed access to the actual data, which makes them super fast ; and the third one is also super fast thanks to the low amount of rows selected by the indexed_field.
What I can't figure out is why the initial query is so slow - MySQL is perfectly capable of processing millions of rows almost instantly (I just tried an AVG(random_field) on a 3M rows table and got the result in 3 seconds), I do provide it with the appropriate index to find them, so why does it take so long?
Here are also a few values out of the mysql config:

sort_buffer_size = 4M
key_buffer_size=48G
innodb_buffer_pool_size=96G
max_heap_table_size=12G
tmp_table_size=12G

key_buffer_size=48G  -- lower to 50M unless you are using MyISAM tables
innodb_buffer_pool_size=96G  -- why not 300G
max_heap_table_size=12G  -- limit to 1% of RAM
tmp_table_size=12G       -- ditto

Queries 2 and 3 are "covering", as you paraphrased with "use only the index without needed access to the actual data".
Queries 1 and 4 are the same, except for the number of rows involved.
Did you run the first query twice?  Probably the first time, the necessary data blocks were not cached in the buffer_pool and needed to be fetched from disk.  In an extreme case, that could be 2M disk reads.  Even NVMe probably takes more than 220s to do 2M reads.
If you have a lot of "average" and similar queries, consider building and maintaining "summary tables".  They can make things significantly faster for huge tables.  http://mysql.rjweb.org/doc.php/summarytables


Error When Trying to Run MDW Data Collector I have SQL Server Management Data Warehouse (MDW) set up across multiple instances, all in the same way with the same accounts and permissions.

On one server I get an error when trying to run the data collectors. The error states that SSIS packages for the data collectors are missing. True enough, when I check SELECT * FROM msdb.dbo.sysssispackages, 8 packages relating to collecting and uploading are missing.

In addition, system logs showed the following error:

  The application-specific permission settings do not grant Local
  Activation permission for the COM Server application with CLSID
  {FDC3723D-1588-4BA3-92D4-42C430735D7D}  and APPID
  {83B33982-693D-4824-B42E-7196AE61BB05}

Does anyone know a fix for this? It is really odd, since all my other instances are running the data collectors without issues. I am running SQL Server 2012 and my host MDW database is on a remote server. But again, all other MDW instances collect and upload without error.

I saw SQL Server 2012 Integration Services and DCOM Permissions by John Veazey, which has a common fix for people but it didn't work for me. The sys log error went away, but the error when running data collection persists.

So apparently network packet size setting in sp_configure was changed up to 32kb when it needed to be under 16kb. Once I reconfigured the setting, the jobs ran successfully. Who would have thought...


how to install mssql on to ubuntu Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Can't open lib '/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1' : file not found.
pugazhl@pugazhl-LIFEBOOK-LH532:~$ # sqlcmd -S localhost -U SA
pugazhl@pugazhl-LIFEBOOK-LH532:~$ # sqlcmd -S localhost -U SA
pugazhl@pugazhl-LIFEBOOK-LH532:~$  sqlcmd -S localhost -U SA
Password:
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Can't open lib '/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1' : file not found.

To configure SQL Server on Ubuntu, run the following commands in a terminal to install the mssql-server package.

Import the public repository GPG keys:

wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo
apt-key add -

Register the Microsoft SQL Server Ubuntu repository:

sudo add-apt-repository "$(wget -qO-
https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list)"

Run the following commands to install SQL Server:

sudo apt-get update sudo
apt-get install -y mssql-server

After the package installation finishes, run mssql-conf setup and follow the prompts to set the SA password and choose your edition.

sudo /opt/mssql/bin/mssql-conf setup

Make sure to specify a strong password for the SA account (Minimum length 8 characters, including uppercase and lowercase letters, base 10 digits and/or non-alphanumeric symbols).

Once the configuration is done, verify that the service is running:

systemctl status mssql-server

If you plan to connect remotely, you might also need to open the SQL Server TCP port (default 1433) on your firewall.

At this point, SQL Server is running on your Ubuntu machine and is ready to use!
You can read Microsoft Documentation Here


Would it make security sense to split up tables into different databases and or servers? What I am asking is would it be more secure if I had a customer table in one database server and the transactional data in another database server? This way if the database is compromised they are only getting one piece of the pie? What kind of service hit would this take on the application?

Just a thought on design.

Not knowing what system you are using I can only make a couple of general observations.  If you have multiple tables in a database then the administrator of the database will have access to all of them but otherwise you can control the table level security.  If you have multiple databases on a server then the administrator of the server will have access to all of them but otherwise you can control the security as you desire.  The best reason to split the data up between servers is to limit the access of an administrator on the server.

At least in MS SQL Server (the DB I know the most about) you can locate tables across multiple databases on the same instance with no performance issues.  Once you are dealing with multiple instances on the same server you have a distributed transaction and may or may not take a performance hit.  If the instances are on separate servers in the same location then you add in performance degradation caused by the LAN. And of course if you have servers in different locations you are dealing with WAN performance.


Should you run DBCC CHECKCONSTRAINTS regularly? In my database, no one is adding, removing, or disabling constraints outside of patches and releases. All constraints are enabled and trusted. There is a regular check for untrusted or disabled constraints in case one were to be accidentally introduced. DBCC CHECKDB is also run on a regular basis to check for corruption.

Is there a benefit to running DBCC CHECKCONSTRAINTS just as regularly as DBCC CHECKDB? Or am I causing unnecessary work? When should DBCC CHECKCONSTRAINTS usually be run?

From Microsoft documentation:

  Checks the integrity of a specified constraint or all constraints on a
  specified table in the current database.

You only urn this after repairing a database and against the objects with constraints to confirm those constraints are still valid.

Quote from The Accidental DBA (Day 13 of 30): Consistency Checking by Erin Stellato

  CHECKCONSTRAINTS is a command to verify that data in a column or table
  adheres to the defined constraints. This command should be run any
  time you run CHECKDB with the REPAIR_ALLOW_DATA_LOSS option. Repair in
  DBCC will fix corruption, and it doesn’t take constraints into
  consideration; it just alters data structures as needed so that data
  can be read and modified. As such, after running repair, constraint
  violations can exist, and you need to run CHECKCONSTRAINTS for the
  entire database to find them.


What is "SAP on Oracle" OK, total newb question.

What is "SAP on Oracle", exactly.  This link is talking about how SAP can run on Oracle (I think).

I'm not understanding how this is working.

My understanding:

SAP is a company that provides ERP
Oracle is a database system.

I thought they were competitors!  So, is SAP on Oracle running the ERP system on Oracle?  What is running here and what would a DBA be supporting in this scenario?  (Or would this even be DBA territory?)

I'm asking because I've applied for a job that prefers "SAP" experience and I ran into this "SAP on Oracle" in my googling.

SAP AG (Often just called SAP) is a company which has a product called SAP ERP (Enterprise Resource Planning).  Since Oracle also makes ERP software they are indeed competitors.  In fact they are the top two ERP vendors.

SAP can be used with quite a few different database systems, but according to one source I found Oracle is dominant comprising around 80% of SAP systems.  Oracle recently won a law suit against SAP for intellectual-property theft. (http://news.cnet.com/8301-1001_3-20029601-92.html#ixzz1CWy7UiKR)

Here is some SAP on Oracle documentation.

It does seem like a strange scenario for a product to depend so heavily on a competitors product, but it is not unheard of.  To some extent this pattern exists for Oracle with their database product running on Microsoft Windows servers.


Fix Linked Server Without Restarting SQL Server Instance Problem:
Occasionally a nightly job will fail, and when I look into it, it is the result of a query across a linked server failing. But for some reason that query seems to remain 'open' in some way and cause subsequent failures.

Bad Solution:
The only way I have found to fix this is to restart the instance which seems to clear everything up. But when it happens in production, obviously it is a bit of a hassle.

Question:
Does anyone have strategies for resetting a linked server which is stuck open? I am not even really sure what is happening under the hood.

Other Checks made:
 -

Tested the query on the server linked to. And I can select the data fine on that side
Tested everything else on the server w/ the link. Everything works fine there it is just that when it tries to query across that link it fails.

Is the only solution to this to really restart the instance?

Depending on the statement you are running and how you have your linked servers configured, you could be running into a situation where you have an orphaned distributed transaction sitting out there that you need to kill.  To identify if this is the case or not, first run the following query:

SELECT  DISTINCT request_owner_guid
FROM    sys.dm_tran_locks
WHERE   request_session_id = -2

If this returns records, chances are good that you are in fact running into this very situation.  To kill these orphaned transactions, you have to run the following statement for each record returned by the query above:

KILL 'UOW'

UOW is the request_owner_guid returned in the above query.

I've run into this situation with both linked server issues and jobs that are making calls externally from the SQL Server process.  In either case, I would find that an external process would interfere (e.g. network drop, process is killed or errors unexpectedly, etc.) and then the transaction wouldn't properly terminate at the SQL Server.  SQL Server won't know that the transaction has actually failed and things will just sit out there until you either walk through this process or bounce the server as you are already doing now.

Hope that helps.


Rate of transactions reduced when mirror is synchronized I'm currently managing a database of ~ 40 giga in a HA/mirrored set up with primary, mirror and witness nodes.

When the system is fully synchronized i noticed a decrease in rate of transactions which has a negative impact on my production environment (general slowness and UI latency).
When the mirror is temporarily suspended everything works as it should.
Principal and mirror are built on the same server model, same OS(SQL server 2012) and same hardware specs.

Is there anything i should check to match the performance when the mirror is suspended?

There are two modes for database mirroring:

High safety (synchronous) - every delete/update/insert (DUI) operation is written to the primary's transaction log, sent across the network to the mirror, written to the mirror's transaction log, and then confirmation is sent back to the primary. Transactions are inherently slower, but in a perfect world, you won't lose data when you fail over from the primary to the mirror.
High performance (asynchronous) - DUI operations are only written to the primary's transaction log, then the transaction is immediately committed. Later, the primary sends transactions to the mirror - but that traffic doesn't slow down your transactions. However, if you fail over, you can lose data.

The key is in the names: high safety is safe (but slow), and high performance is faster (but less safe.)

You've noticed that when you suspend the mirror, things get faster - that's because your mirroring is set up with high safety.

If your business is willing to lose data when the primary fails, then you can keep doing mirroring, but switch it from high safety to high performance. (You can change this at the database level at any time.)

If your business is NOT willing to lose data, then it's time to start troubleshooting which layer of mirroring is causing you problems:

Slow log file writes on the mirror, or
Slow network connection between the primary and the mirror, or
Lots of tiny transactions in the app rather than doing larger batches

Troubleshooting that is kinda beyond the scope of what I can do in an answer here though.


Same query same data different server different query plan I have a query that hits a view, which references other views etc...several levels down. The query plan is a bit complex.

I run the query on 2 different servers. On both servers, the number of rows in referenced is roughly the same and the query returns 3 rows in both environments..

In production, sql server chooses a query plan that looks to create a resultset of one of the underlying views without applying the join clause first. this results in 8x more page reads.

In QA environment, sql server applies the join clause first and minimizes the rows returned by that subtree. the subtree in prod returns 42k actual rows and in QA 2 actual rows.

What would cause sql server to choose a less optimal plan?

QA is sql 2012 sp3
Prod is sql 2012 sp1
no physical reads in either case.
prod stats io shows 42k logical reads on worktable. I am assuming this is the resultset from that subtree.
QA does not show this.

Since you are running SP3 in QA and SP1 in production (assuming you are running with Trace Flag 4199) you are missing out on a lot of performance related fixes in your production environment.

My first suggestion would be to test the query and compare the generated plan on servers of the same build number.

Without details of the query and the underlying tables (which may be complex to post if you're talking about views referencing views, joining views) it's hard to tell which issue you are running into exactly, but for example these have been fixed since SP1:

FIX: Suboptimal execution plan is generated when you run a query in SQL Server 2012 or SQL Server 2014
FIX: Performance of a SELECT statement that contains a LIKE operator and an ESCAPE clause is low in SQL Server 2008 R2 or in SQL Server 2012
FIX: Poor performance when you run a query against a table in an RCSI-enabled database in SQL Server 2012 or 2014

But the list goes on and on.

Other than that it could be any configuration difference (MaxDOP, memory in the server, activated trace flags) or it could be statistics that are different.

In any case it's hard to compare between builds that are so far apart (SP1 and SP3)

I would suggest running the same build in QA as prod anyway because you want your QA-users to be testing the exact situation as they will be running in prod.


Adding an Organization schema to existing person/relationship schema I'm not a database programmer. I really don't plan on becoming one; I dabble. My skills and work lie elsewhere, and these keep me busy enough. Nevertheless, I find myself needing, and begging, for some quick help at times in an area in which I have no time or need to learn fluently. I have officially attempted some trials at building a couple of new tables for this, but to no avail. I do most of my learning in a hands-on setting, and will know how this particular schema works once I've received some direction (which is all I'm asking for, I think) and implemented it.

That said, the following was suggested by @RolandoMySQLDBA four years ago, and I've found it terrific for a project I'm building.

I'm hoping he will see this and that he, or someone, can help me extend this to include a table of "Organizations" along with a table of Organization rel_types, to be associated with any of the persons...

CREATE TABLE person
(
    person_id INT NOT NULL AUTO_INCREMENT,
    ...
    PRIMARY KEY (person_id)
);
CREATE TABLE relationship
(
    rel_id INT NOT NULL AUTO_INCREMENT,
    person_id1 INT NOT NULL,
    person_id2 INT NOT NULL,
    reltype_id TINYINT,
    PRIMARY KEY (rel_id),
    UNIQUE KEY outer_affinity (reltype_id,person_id1,person_id2),
    UNIQUE KEY inner_affinity (reltype_id,person_id2,person_id1),
    KEY has_relationship_to (person1_id,reltype_id),
    KEY has_relationship_by (person2_id,reltype_id)
);
CREATE TABLE relation
(
    reltype_id TINYINT NOT NULL AUTO_INCREMENT,
    rel_name VARCHAR(20),
    PRIMARY KEY (reltype_id),
    UNIQUE KEY (rel_name)
);
INSERT INTO relation (relation_name) VALUES
('friend'),('follower'),('foe'),
('forgotabout'),('forsaken'),('fixed');

I will be, and am, grateful to anyone who can assist me with this.

If you want to keep things simple, it may be enough to add
organization(organization_id PK, ...) table and person_organization_relationship (person_org_relationship_id (PK) , person_id,organization_id ,reltype_id , date_from, date_thru). Depends on requirements you may or may not add unique constraint, on , for instance, (person_id, organization_id,reltype_id).  I would reuse the same relation table which is used for describing relationships between persons.  In many cases such model will be working just fine.

If more general model is needed, concept of Party can be added.  Party can be a person, or organization, or something else that interacts with the system .  For instance if RDBMS supports enums, you can have

PARTY (party_id PK, party_type enum('person','organization'), [common attributes]);

Then you either add 2 tables, PERSON and ORGANIZATION (which both have party_id as a primary key and foreign key to PARTY) and store their attributes separately, or store all attributes in PARTY table.

Concept of Party lets you store any type of relationship between person and organization,or 2 organizations , or 2 persons in one table.  Moving forward, it'll make other things easier to implement. For example, various contact information such as address(physical or electronic), phone, etc can stored in one place .
On the other hands, it has its price. The more universal model you are implementing, the more time you'll need to spend in the beginning.

So I'd recommend to use simplest model that satisfies requirements .

Some notes regarding current physical implementation of relationship table:

     UNIQUE KEY outer_affinity (reltype_id,person_id1,person_id2),
     UNIQUE KEY inner_affinity (reltype_id,person_id2,person_id1),
     KEY has_relationship_to (person1_id,reltype_id),
     KEY has_relationship_by (person2_id,reltype_id)

It's better not to have the least selective column (reltype_id)  as a leading column in outer_affinity and inner_affinity
For enforcing uniqueness it's enough to have one of outer_affinity, inner_affinity
I'd start with just two indexes UNIQUE KEY UQ_relationship_person_1_person_2 (person_id1, person_id2, reltype_id) and INDEX IDX_relationship_person_2(person_id2) (depends on typical queries it might be useful to person_id1 as second column to this index).
The table seems to miss date_from (and maybe date_thru) attribute .


Could not add Replica Set to MongoDB Shard I want to deploy a MongoDB Cluster for testing on 3 Virtual Machines using Docker.

On the first Virtual Machine I have the Router Instance and a Replica Set of the 3 Config Servers.

The second Virtual Machine is hosting a Replica Set of three MongoDB Server. They where successfully initiated and elected a Primary.

The third Virtual Machine is hosting a Replica Set of three MongoDB Server. They where successfully initiated and elected an Primary.

When I start my Container which contains the Router it connects successfully to the Replicaset of the Config Server.

If I now want to add my 2 Replica Sets as Shards with the following Command:

sh.addShard("rep1/rep1-node1:27018")

rep1 is the name of the Replica Set. rep1-node1 is the hostname for the Container with the Primary Node. (Host Name resolution works fine.)

After a short time I get the Following Error:

{
    "code" : 133,
    "ok" : 0,
    "errmsg" : "could not find host matching read preference { mode: \"primary\" } for set rep1"
}

I Have found another question here with the same topic but I don't get any errors with missing Authentication Key. None of my Mongo Instance uses any authentication because it's only a testing environment.

Adding normal Mongo Servers works without any problems. Only Replicasets won't work. I'm using MongoDB version 3.4.

So i have found my mistake. I had to add the Servernames and IP Addresses to the hosts file of the docker container in which the config Servers runs. After that i could add my Replica Set as a Shard. I will edit this answer this or next week and provide a link to a Blogpost in which i will describe how to make a MongoDB Sharding with Docker on Virtual Machines.


PostgreSQL full-text search retrival from id gap I have a full-text search column on one of my tables in PostgreSQL 9.3 implemented based on this tutorial (without the South part).
There is a postgres function where I

SELECT concat_ws(' ', a."my_column1") INTO c1;
SELECT concat_ws(' ', a."my_column2") INTO c2;
RETURN setweight(to_tsvector('pg_catalog.simple', c1), 'C') ||
       setweight(to_tsvector('pg_catalog.english', c2), 'D');

This is being written into a column called fts_document in the same table. I have INSERT and UPDATE triggers on this table and a trigger function as in the tutorial.

Everything has worked fine at some point.

After a dump from another db with identical structure and a restore to an empty db I got my table with a gap of the field id from 7306 to 21431. The main problem is that now the full-text-search retrieves documents only from this missing id range and the my_column1 and my_column2 values are very valid ones. I have deleted the index on fts_document column and rebuilt it, but no result.

Where does postgres retrieve these weird ("not existing") rows from? How can I fix the search to retrieve also the existing rows and postgres to show the missing docs?

Found my stupid mistake...
instead of running the query as my_table.objects.raw(sql_query) I was creating a connection to another db with

conn = psycopg2.connect("dbname='wrong_db' user='my_user' password='my_password'")
cur = conn.cursor()
cur.execute(sql_query)

so lesson learnt... use the Django built-in capabilities to eliminate the possibility of a connection to a wrong db.

The coincidence that I was getting ids from my gap range was caused by the fact that at some point I had tried to import (unsuccessfully) the entries from wrong_db to my db and this created the gap in ids.


PAGEIOLATCH wait and Page Life Expectancy very low Training for SQL Server, has following scenario:
SQL Server on Azure VM, DS-series machine, it has 32 GB of RAM
Server has below performance conditions:
- very high PAGELATCH_IO waits
- average Page Life Expectancy is 30
- nothing else is known

What should be done in order to decrease PAGELATCH_IO waits ?
Choose correct answer (need to pick just one):

Add more tempdb files
Enable large-page support
Enable lock pages in memory
Configure buffer pool extensions

Training says that the correct answer is 1) add more tempdb files
I think correct answer is 4) Configure buffer pool extensions, based on:
- PLE is a number of seconds a page will stay in the buffer pool without removing
- PLE = 30 is too low, means high turnover in the buffer pool
- DS-series support Premium SSD drives, can place buffer pool extension on Premium SSD

But could be also this 3) Enable lock pages in memory based on this
https://sqlperformance.com/2014/06/io-subsystem/knee-jerk-waits-pageiolatch-sh
Who is right - me, Training material, or you (your option) ?
By the way, not sure what they mean under PAGELATCH_IO, that could be PAGEIOLATCH or PAGELATCH, I guess answer depends on exactly which type we have

It seems that you haven't reproduced the question from the training materials here with complete accuracy.
Assuming that the question was about PAGELATCH_** waits, and that the latched pages are in the tempdb database, then those waits can be a common sign of tempdb allocation contention.  And the most common solution to that is to increase the number of tempdb files.
See this Microsoft Support article:
Recommendations to reduce allocation contention in SQL Server tempdb database

To improve the concurrency of tempdb, try the following methods:

Increase the number of data files in tempdb to maximize disk bandwidth and reduce contention in allocation structures. As a general rule, if the number of logical processors is less than or equal to eight (8), use the same number of data files as logical processors. If the number of logical processors is greater than eight (8), use eight data files. If contention continues, increase the number of data files by multiples of four (4) up to the number of logical processors until the contention is reduced to acceptable levels. Alternatively, make changes to the workload or code.

If we already suspect tempdb contention (because of the latch waits), then PLE = 30 could be caused by the heavy tempdb usage (if you think of lots of tempdb pages being loaded into memory and released repeatedly, driving down PLE).
Thus option 1 would most likely be the correct answer, although more information would be needed to confirm (for example, knowing that the latch waits are in fact in tempdb).


Implementing an encrypted table in SQL Azure I'm moving an App to Azure and need to achieve PCI compliance for a couple columns in a single table. I realize I can encrypt the data before I store it, but I was wondering if there was a feature in SQL Azure that can manage part of this for me. I've done some reading but I'm not sure what is; or is not; supported in SQL Azure.

I've read about patterns using SQL Server (and not SQL Azure) where data encryption & decryption was applied behind a View. In that case, all clients accessed data via a View and consequently were abstracted from the Encryption/Descryption process. With that said, it seems a bit dangerous.

I'm open to any and all suggestions on how to deal with this situation.

Short answer: as of April 20, 2012 column encryption is not supported in SQL Azure, see: http://msdn.microsoft.com/en-us/library/windowsazure/ee336253.aspx and search for ENCRYPT.  You will see that all of the encryption functions are listed as unsupported.

If you need to encrypt your data at rest and have to support SQL Azure then you are going to have to do your encryption at the application level.  This is more difficult to get right as lots of the good encryption practices (using nonces as initialization vectors for each cell, key management for tracking which keys encrypt which columns, integrity verification, layered protection of encryption keys with role based permissions) you will have to implement yourself.  I strongly recommend getting a security professional to work with you on the encryption as it is challenging to get right and if you miss something then you have a false sense of security that could really burn you later.

You could look at http://securentity.codeplex.com, which claims to be a solution for managing encrypted data and supports SQL Azure.  I have not personally used it, nor can I vouch for it's correctness, but it is a potential option.

Other information:

In SQL Server column level encryption is easy and very secure (even with a view) because you can use roles to manage who can access the encryption keys.  Even if a user can select from a view, if they are not granted access to the key that encrypts the underlying data then they will get back NULLs for those encrypted columns.  I have a set of sample code for SQL Server 2005 and higher that demonstrates the encryption capabilities of SQL Server here: http://sqlcrypto.codeplex.com


Where are the username and password stored in postgresql? I'm using the PostgreSQL database. In it I create users. So I want to know

Where are the User name and Password stored?
Whether the Password is stored as plain text or encrypted data?
Whether root user is able to read the password or not?

In pg_authid
By default encrypted, see here
no, not possible (if encrypted)


tmp_table_size with mostly InnoDB tables I use MySQL version 5.6.25-0ubuntu0.15.04.1 on 64-bit Ubuntu 15.04. I have 2GB RAM and the disk is SSD (so, in my mind, writing to disk isn't that costly). I currently have databases for just two small Wordpress blogs. I wasn't trying to get too deep into optimizing this but I encountered what I consider to be strange behavior.

When I run MySQLTuner 1.5.0, I get the following output:

[OK] Currently running supported MySQL version 5.6.25-0ubuntu0.15.04.1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics-------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in InnoDB tables: 7M (Tables: 30)
[!!] Total fragmented tables: 3

-------- Performance Metrics -----------------------------------------
[--] Up for: 10d 13h 0m 49s (86K q [0.095 qps], 3K conn, TX: 218M, RX: 26M)
[--] Reads / Writes: 73% / 27%
[--] Binary logging is disabled
[--] Total buffers: 296.0M global + 1.1M per thread (75 max threads)
[OK] Maximum reached memory usage: 302.8M (15.20% of installed RAM)
[OK] Maximum possible memory usage: 380.4M (19.10% of installed RAM)
[OK] Slow queries: 0% (0/86K)
[OK] Highest usage of available connections: 8% (6/75)
[OK] Aborted connections: 0.34%  (11/3243)
[!!] Key buffer used: 18.2% (1M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/109.0K
[OK] Read Key buffer hit rate: 96.7% (30 cached / 1 reads)
[OK] Query cache efficiency: 45.3% (27K cached / 60K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
[!!] Temporary tables created on disk: 81% (2K on disk / 2K total)
[OK] Thread cache hit rate: 99% (6 created / 3K connections)
[OK] Table cache hit rate: 76% (115 open / 150 opened)
[OK] Open file limit used: 0% (48/16K)
[OK] Table locks acquired immediately: 100% (41K immediate / 41K locks)

-------- InnoDB Metrics ----------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/7.4M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 15.19% (1244 used/ 8191 total)
[OK] InnoDB Read buffer efficiency: 99.93% (1295836 hits/ 1296742 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 12926 writes)

-------- Recommendations ---------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    tmp_table_size (> 128M)
    max_heap_table_size (> 128M)

My problem is the tmp_table_size (and max_heap_table_size). Every week I've been increasing it and MySQLTuner keeps suggesting larger. (As you can see, it now proposes >128M.) I can see from the above output that it's writing tables to disk, which I assume is the reason for the suggestion. However, in my understanding, this only relates to the MyISAM engine, while all of my Wordpress tables are using the InnoDB engine. I assume there are some internal MySQL tables that may be using MyISAM, but I still don't know why MySQLTuner thinks I need such a massive table size just for that minimal stuff.

So, the question: Why does MySQLTuner think I need such large temp tables, when I use only InnoDB?

Because your mysql is still creating disk tables:
[!!] Temporary tables created on disk: 81% (2K on disk / 2K total)

Looking at the source of tuner-script

$mycalc{'pct_temp_disk'} = int(($mystat{'Created_tmp_disk_tables'} / $mystat{'Created_tmp_tables'}) * 100);

*snip*

if ($mycalc{'pct_temp_disk'} > 25 && $mycalc{'max_tmp_table_size'} < 256*1024*1024) {
    badprint "Temporary tables created on disk: $mycalc{'pct_temp_disk'}% (".hr_num($mystat{'Created_tmp_disk_tables'})." on disk / ".hr_num($mystat{'Created_tmp_tables'})." total)\n";
    push(@adjvars,"tmp_table_size (> ".hr_bytes_rnd($myvar{'tmp_table_size'}).")");
    push(@adjvars,"max_heap_table_size (> ".hr_bytes_rnd($myvar{'max_heap_table_size'}).")");
    push(@generalrec,"When making adjustments, make tmp_table_size/max_heap_table_size equal");
    push(@generalrec,"Reduce your SELECT DISTINCT queries without LIMIT clauses");
} elsif ($mycalc{'pct_temp_disk'} > 25 && $mycalc{'max_tmp_table_size'} >= 256*1024*1024) {
    badprint "Temporary tables created on disk: $mycalc{'pct_temp_disk'}% (".hr_num($mystat{'Created_tmp_disk_tables'})." on disk / ".hr_num($mystat{'Created_tmp_tables'})." total)\n";
    push(@generalrec,"Temporary table size is already large - reduce result set size");
    push(@generalrec,"Reduce your SELECT DISTINCT queries without LIMIT clauses");

So there's tuner's logic, if the (Created_tmp_disk_tables/Created_tmp_tables)*100 is more than 25(%) then increase your tmp-table size (upto max 256M).

Again, mysqltuner is for reference, if you don't see  performance issues you don't need to blindly follow the suggestions.

Also note that tmp table has nothing to do with InnoDB vs MyISAM (if you mean that in your last line). You might want to read about internal temporary tables.


Get new ID for inserted ID that comes from another table? Simple question but I'm a newbie. I have existing orders table 'Orders'(table1)  and it has an OrderNumber as a column and the orders are number up 156.
I have a existing table called 'orders'(table 2) from another website. I have to insert these orders from table 2 into table one and the OrderNumber for the inserted new order has to be 157....and so on for all orders coming in. I have asked this question and get answers that make no sense. Anyone?

http://sqlfiddle.com/#!6/d423d/11/0

I got it to work but why did it increment with 50? It went from 156 to 206. I only want it to take the next number in sequence.

It incremented by 50 because you added "ordID" to @idmax and ordID = 50.

Maybe you wanted an incrementing number using row_number?

DECLARE @idmax INT
SELECT @idmax = MAX(OrderNumber) FROM OrderNumbers

INSERT INTO OrderNumbers
(OrderNumber,
 OrderGUID)

 SELECT
 row_number() over(order by ordID) +@idmax,
 NEWID()

 FROM orders

 SELECT * FROM OrderNumbers


how many 'text' column a table could have in mysql? I have this error “Row size too large (> 8126).” I'm working on a application which is kinda complex to explain how it works. But this is the situation I'm in,

------------------------------------------------------------------------------
FIELD1 (TEXT) FIELD2(TEXT) FIELD3(TEXT) .........................FIELD70(TEXT)
------------------------------------------------------------------------------

POSSIBLE DATA SIZE FOR A SINGLE FIELD: around 500 characters.

I have around 20 tables in a single database like this.

I know it's too bad idea to have this much column in a single table. But I have to go with this since It's went too far and can not modify number of columns.

Now I got some error like, "Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline."

WHAT I HAVE TRIED:

I altered the table as ROW_FORMAT=COMPRESSED. = didn't work out
I altered the table as ROW_FORMAT=DYNAMIC. = didn't work out
innodb_file_format = Barracuda = didn't work out
I altered the column to VARCHAR(500) = didn't work out
I altered the column to TEXT,BLOG,MEDIUMBLOG = didn't work out

What could be the actual error? I've seen plenty of the answers for this problem bur they are telling these above methods that I've tried and failed. PLEASE HELP ME TO SOLVE THIS

The problem is the row size limit for InnoDB tables, in this links you can find some approaches to solve this:

http://www.mysqlperformanceblog.com/2011/04/07/innodb-row-size-limitation/
InnoDB create table error: "Row size too large"


mongodb master slave replication I have set up master slave replication in mongo. It works fine for a few months, then suddenly replication fails. I have started it again with initial sync but during sync after a specific point it will again start resync and this process goes on and on, and slaves have not complete their initial sync.

The log file contains initial sync pending and again start resync again and again.

One reason could be that your oplog is too small.

On the master:

db.getReplicationInfo()

If timeDiffHours is shorter than the time that your initial sync takes, then the initial sync will fail and start again...

Resizing the oplog is quite easy, if this is indeed your problem. The documentation is here:
http://docs.mongodb.org/manual/tutorial/change-oplog-size/


Reclaim Database Wasted Space In our company we have a test server on which Oracle RDBMS 11g is installed.
My question is related to reclaiming wasted space.

I ran the following query:

select
   table_name,round((blocks*8),2) "size (kb)" ,
   round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
   (round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)"
from
   dba_tables
where
   (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
order by 4 desc;

...which shows me the total wasted space for each table. So we have approx. 84 GB of wasted space overall in our database.

I want to know how to reclaim this wasted space without being able to use Enterprise Manager, because I don't have access to EM.

I have also noticed that the indexes on the database were last rebuilt in 2010. Could rebuilding the indexes solve the wasted space problem?

This is all covered in the documentation, but basically:

Enable row movement for each table using ALTER TABLE ... ENABLE ROW MOVEMENT;
Shrink the object (TABLE/MATERIALISED VIEW/etc) with ALTER <object type> <object name> SHRINK SPACE;
Resize the tablespace datafiles with ALTER DATABASE DATAFILE '/path/to/your/datafile.dbf' RESIZE 500M;

Tim Hall covers this in great length, with every conceivable option, in this article - a great resource.


capability and performance of tags implemented as array vs text vs full text In terms of search capability and performance, what are the pros & cons of implementing tags as an array or text or full text field?

I am seeing limitations of capability in choosing to implement tags with an array, because it seems to me that if an array contains ['juicy fruit'] then someone searching for 'fruit' will not find that record (for example, tags && ARRAY['fruit'] will not find it). The person who created that record would have had to enter something more like ['juicy fruit', 'juicy', 'fruit'] to have their record found with just a search for 'fruit' or 'juicy'. Whereas if I implement tags as text, then a search for 'fruit' will find 'juicy fruit', and further still, if I implement tags as full text then I will find 'juicy fruit' when searching with the string 'fruits' (plural). Furthermore, I think there will be no performance loss in going for full text search. Thoughts?

But maybe the whole point of tags is exact match.

You can get search functionality with arrays by using the ANY/SOME or generate_subscripts functions. However, the PostgreSQL documentation notes the following:

  Tip: Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements.

So it looks like you're on the right track, because you're correct about not losing performance (you would more likely gain it) and the other methods allow greater flexibility. Tags themselves are about exact matches, but most sites have a way to search tags too (for instance, this very site).

If you implement tag storage as full text, that doesn't preclude matching, including regex, with simpler expressions such as LIKE/ILIKE. A common scenario is to store documents (the chunks of text being searched) as text or varying character and store the tsvector type necessary for full-text search in a separate column. See the PostgreSQL documentation for more on creating a full text index.

The pros and cons should then be evident: using an array limits scalability and search options because it lacks text indexing, stemming and inflection support, but is easy to use when an exact match is desired. Using a text datatype is more flexible when searching for strings. Using a text datatype with a generated tsvector column provides the most flexibility and good performance in all scenarios, but requires more storage.


Migration of MariaDB/MySQL to another server without downtime System : MariaDB (MySQL 5.5)

Parameters : innodb_file_per_table=1

The database I have now is approaching its full capacity (94% full now, 6.4TB out of 6.9TB)

I can't run OPTIMIZE TABLE to reclaim space as there is insufficient space to hold the temporary tables.

I have prepared another new server to migrate the bulk data over. However, I do not wish to have any downtime during this period.

Hence, I attempted to do the following :

1) In the most utilized and huge tables, I began to delete old/unnecessary records to withstand the incoming insertions (since by DELETING records, MySQL will reuse space)

2) Started migrating of the data over by running a script that queries the records and insert them into the new server by doing batch inserts (every 5000). The reason why I am running a script is because the database schema different in the new server (I sharded the table).

Problems encountered:

1) The insertion rate of the incoming records is faster than the deletion rate, which means my .ibd table is still growing

2) The batch insert migration script is still too slow and it definitely can't finish in time before the original database server gets full (almost 4TB+ data to migrate)

I can most probably run more migration scripts in parallel, and see if I can delete the old records at a faster rate.

However, is there any alternative approaches I can take or improvements to do?

Edit : The application currently points to the old and new server, and after every a batch of complete migration of records, I'll update the application such that it'll connect to the new server instead. The storage is not a SAN, but directly connected. As we ran out of disks ports, we can't add more to the local storage. Also, we do not have regular backups (a huge mistake at our end).

Are you using LVM on Linux servers? In that case, you can have a minimal downtime solution:

Switch the database to master, stop the server just a few seconds to make a LVM snapshot, start the master again, mount the snapshot in a temp directory to copy the data to the new (bigger) server and then configure a slave with that. Then you can start the slave, let it sync with the master until is up to date and finally promote the slave as master.

Detailed howto: https://www.percona.com/blog/2006/08/21/using-lvm-for-mysql-backup-and-replication-setup/


PostgreSQL indeterminate ordering of results When I have this query:

SELECT DISTINCT "spree_products".id FROM "spree_products"
INNER JOIN "spree_variants"
   ON "spree_variants"."product_id" = "spree_products"."id"
  AND "spree_variants"."is_master" = 't'
INNER JOIN "spree_prices"
   ON "spree_prices"."variant_id" = "spree_variants"."id"
INNER JOIN "spree_products_taxons"
   ON "spree_products_taxons"."product_id" = "spree_products"."id"
INNER JOIN "spree_taxons"
   ON "spree_taxons"."id" = "spree_products_taxons"."taxon_id"
WHERE "spree_taxons"."id" IN (13)
AND "spree_prices"."currency" = 'USD'
AND ("spree_products".deleted_at IS NULL
  or "spree_products".deleted_at >= '2013-08-30 06:23:45.881488')
AND ("spree_products".available_on <= '2013-08-30 06:23:45.881974')
AND (spree_prices.amount IS NOT NULL) LIMIT 12 OFFSET 36

I get some records returned in different offsets. For example, I see a record with ID=53 in both OFFSET 24 and OFFSET 36.

If I add an ORDER BY spree_products.id clause to this statement, it makes the order determinate and I never see the same record returned in different offsets.

That's because the SQL standard doesn't require PostgreSQL to return rows in any particular order without an ORDER BY clause. It can return them however it feels like. See the manual for SELECT. In particular:

  If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce.

and

  When using LIMIT, it is a good idea to use an ORDER BY clause that constrains the result rows into a unique order. Otherwise you will get an unpredictable subset of the query's rows — you might be asking for the tenth through twentieth rows, but tenth through twentieth in what ordering? You don't know what ordering unless you specify ORDER BY.

This makes LIMIT and/or OFFSET without an ORDER BY unsafe in almost all cases. It's permitted in case you've forced the ordering in an inner query, as a bit of a performance hack, but it's rarely a good idea.

You must add an ORDER BY clause to make your statement deterministic.

In this case, with the DISTINCT there it's probably doing hash aggregation - which tends to return results in an inconsistent order from execution to execution. Even if you were doing an index scan that usually returns results in order, you cannot assume that it will unless you tell PostgreSQL you require the rows in a particular order.

This is closely related to people who ask how to select rows in the "natural" order of a table; tables and result sets don't have a natural order. They're sets not lists. If you want an ordering you must specify one on an operation by operation basis.


SQL Server 2008 R2 Auditing I am new to database administration, and I need to implement auditing of all users of multiple databases.

Can we audit multiple users of a multiple database by creating just one audit specification?

I tried to create the audit specification, but I get the error message in the screenshot below.

The error message is telling you exactly what the problem is:

  You can only create audit actions on objects in the current database.
  (Microsoft SQL Server, Error: 33221)

The audit can only be on objects in the current database. Your screenshot shows that you are trying to audit two different databases: audit_test and db_aiudit

You will need to create two database audit specificiations - one for each of the two databases listed above a the respective database level


Can a complex primary key made up of 4 fields have only one of this fields set to AUTO INCREMENT? I have a complex primary key, I wonder if what I write in the title is possible:

I have a look up table with 4 fields (they should compose the primary key of the table):

1) an INT column;

2) Another INT column;

3) A YEAR column;

4) another INT column which should be AUTO_INCREMENT.

The 1) and 2) fields are also FOREIGN KEYS of another table, and I need them to be so.

How can I handle this?

You can do this:

CREATE TABLE mytable
(
    `ID` MEDIUMINT NOT NULL AUTO_INCREMENT,
    `C1` MEDIUMINT NOT NULL,
    `C2` MEDIUMINT NOT NULL,
    `Year` MEDIUMINT NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (C1, C2)
            REFERENCES othertable(pkcol1, pkcol2)
);

ALTER TABLE mytable ADD UNIQUE INDEX(`C1`, `C2`, `Year`, `ID`);

Adding the ID column to the unique index will guarantee that you can have the same values for C1, C2 and Year in your table. If you don't want that, remove ID from the ALTER TABLE statement.

The syntax of the FOREIGN KEY is for a multiple column key in another table called othertable. pkcol1 and pkcol2 would be the corresponding columns in that table.

ID would be your fourth column in the question example code you presented.


What happens if data gets inserted in the middle of a transaction? I'm new to database and I'm lost with transaction. For example
in a situation where I have a table sensor_log that receives INSERT constantly and I want to move data to two other tables in one transaction.

BEGIN;

INSERT INTO sensor_log_a
SELECT id, location
FROM sensor_log
INNER JOIN sensor_location_to_insert USING (location);

INSERT INTO sensor_log_b
SELECT id, location
FROM sensor_log
INNER JOIN sensor_location_to_insert USING (location);

COMMIT;

Is there a risk that data is different between sensor_log_a and sensor_log_b if data gets inserted during the transaction or the BEGIN;..COMMIT; prevent this?

A statement sees a consistent view of the "world" based on the data that was there when the statement started. So the select statements in your two insert statements will not see new rows while they are running.

However, if the table is changed between running the two statement (or after the first one started), each select statement could see different data e.g the first one sees 100 rows, the second one 200.

If you want to ensure that the whole transaction sees a consistent view of the data, use a higher isolation level. In your case using repeatable read would be enough.

begin transaction isolation level repeatable read;

...
commit;

Another option would be to do this in a single statement using a data modifying cte:

with to_insert as (
  SELECT id, location
  FROM sensor_log
  INNER JOIN sensor_location_to_insert USING (location)
), insert_a as (
  insert into sensor_log_a
  select *
  from to_insert
)
insert into sensor_log_b
select *
from to_insert;


How knowing queryid get query string in Postgresql? I try use postgres_exporter.

It monitors postgres and sends metrics to prometheus.

It has a request
https://github.com/wrouesnel/postgres_exporter/blob/master/queries.yaml#L137

The output will be a queryid table and its metrics.

How knowing queryid get query string in Postgresql?

Thanks!

This is managed by pg_stat_statements extension in the view that has the same name.
To link queryid and query you need to query the view pg_stat_statements:

postgres=# \d pg_stat_statements;
                    View "public.pg_stat_statements"
       Column        |       Type       | Collation | Nullable | Default
---------------------+------------------+-----------+----------+---------
 userid              | oid              |           |          |
 dbid                | oid              |           |          |
 queryid             | bigint           |           |          |
 query               | text             |           |          |
 calls               | bigint           |           |          |
 total_time          | double precision |           |          |
 min_time            | double precision |           |          |
 max_time            | double precision |           |          |
 mean_time           | double precision |           |          |
 stddev_time         | double precision |           |          |
 rows                | bigint           |           |          |
 shared_blks_hit     | bigint           |           |          |
 shared_blks_read    | bigint           |           |          |
 shared_blks_dirtied | bigint           |           |          |
 shared_blks_written | bigint           |           |          |
 local_blks_hit      | bigint           |           |          |
 local_blks_read     | bigint           |           |          |
 local_blks_dirtied  | bigint           |           |          |
 local_blks_written  | bigint           |           |          |
 temp_blks_read      | bigint           |           |          |
 temp_blks_written   | bigint           |           |          |
 blk_read_time       | double precision |           |          |
 blk_write_time      | double precision |           |          |

postgres=#

For example:

select queryid, query from pg_stat_statements;


Group by age range and gender with aggregation function on some columns In Mysql, I have a patient table with id, birth_year and gender. Next table is transactions in which I have id, patient_id [FK from patient] and checkup_date. I need to select rows such that I get number of checkups for all males and females in the range of ages.

How do I query to achieve the following tabular result ?

under 20, male: 10 times, female: 15 times

21 -  30, male: 20 times, female: 35 times

31 -  40, male: 30 times, female: 45 times

....      .......        .........      .......     ........    ..........

81 -  90, male: 10 times, female: 6 times

above 90, male : 0 times, female: 4 times

Thank you.

Two options.

a) Use sub query

select
  concat(10*floor(age/10), '-', 10*floor(age/10) + 10) as `range`,
  gender,
  count(*) as count
from (
  select
    *,
    TIMESTAMPDIFF(YEAR,birth_year,CURDATE()) AS age
  from
    transaction left join patient on patient_id = patient.id
) as t group by `range`, gender;

Gives

+-------+--------+-------+
| range | gender | count |
+-------+--------+-------+
| 0-10  | m      |     2 |
| 10-20 | m      |     1 |
| 20-30 | m      |     3 |
| 30-40 | f      |     2 |
| 30-40 | m      |     2 |
| 50-60 | f      |     1 |
| 50-60 | m      |     1 |
| 60-70 | f      |     5 |
| 60-70 | m      |     3 |
| 70-80 | f      |     1 |
| 70-80 | m      |     6 |
+-------+--------+-------+

b) Group by age first and then group in range in application.

When using ORMs it's not easy to do sub queries, so this might be a good idea.

Performance wise: Considering that average humans live for 100 years, there can be at max 200 records (100 male and 100 female) which can be easily looped in an application.

select
  gender,
  TIMESTAMPDIFF(YEAR,birth_year,CURDATE()) AS age,
  count(*)
from
  transaction left join patient on patient_id = patient.id
group by age, gender

Gives

+--------+------+----------+
| gender | age  | count(*) |
+--------+------+----------+
| m      |    4 |        2 |
| m      |   14 |        1 |
| m      |   24 |        3 |
| f      |   34 |        2 |
| m      |   34 |        2 |
| f      |   54 |        1 |
| m      |   54 |        1 |
| f      |   64 |        5 |
| m      |   64 |        3 |
| f      |   74 |        1 |
| m      |   74 |        6 |
+--------+------+----------+


Export image file from bytea column I am trying to export an image file from a PostgreSQL database.The users_data_circulation table has a photo (bytea) column.

My command:

copy (select encode(photo,'hex') from users_data_circulation limit 1)
    TO '/tmp/imagetest.hext';

In operating system:

$> xxd -p -r /tmp/imagetest.hex > /tmp/imagetest.jpg
$> file /tmp/imagetest.jpg
/tmp/imagetest.jpg: ASCII TEXT

I can't open the jpg file. How can I convert this file to jpg?

devinim@devinimpostgresql:~$ hexdump -C /tmp/image.hex | more
00000000  5c 5c 33 37 37 5c 5c 33  33 30 5c 5c 33 37 37 5c  |\\377\\330\\377\|
00000010  5c 33 34 30 5c 5c 30 30  30 10 4a 46 49 46 5c 5c  |\340\\000.JFIF\\|
00000020  30 30 30 01 02 5c 5c 30  30 30 5c 5c 30 30 30 01  |000..\\000\\000.|
00000030  5c 5c 30 30 30 01 5c 5c  30 30 30 5c 5c 30 30 30  |\\000.\\000\\000|
00000040  5c 5c 33 37 37 5c 5c 33  34 31 5c 5c 30 30 30 5c  |\\377\\341\\000\|
00000050  76 50 49 43 5c 5c 30 30  30 02 5c 6e 5c 6e 01 5c  |vPIC\\000.\n\n.\|
00000060  5c 30 30 30 5c 5c 33 37  37 5c 5c 33 37 36 5c 5c  |\000\\377\\376\\|
00000070  30 30 30 21 50 69 63 74  75 72 65 20 45 6c 65 6d  |000!Picture Elem|
00000080  65 6e 74 73 2c 20 49 6e  63 2e 20 49 53 45 2f 53  |ents, Inc. ISE/S|
... continues like that

It's a horrible idea to store jpegs in the database.

That said, if you want to get it out of the database, you can use psql. This gets it out as hex.

psql -t -A -o "/tmp/imagetest.jpg" -c \
  "SELECT photo FROM users_data_circulation LIMIT 1";

You may also want to check out the large object.


Fetch just inserted data in master/slave model? Say I have MySQL master DB(used for updates) and corresponding Slave DB(used for reads). Now user inserts the data into MASTER through application(java), just after insertion he tries to retrieve the data which will go slave. I am sure there be some time lag in replication from master to slave(may be of few Millis) and it won't be instantaneous.

My question is how to deal this scenario in master slave model where user will definitely expect the latest data inserted by him down the line.

Does it need to be handled at application level i.e. wherever transaction reads/fetches the data with any CUD, it should be done from Master so that latest data inserted by same transaction is retrieved or are there some other better ways too?

For example:- User first created the account . Insert goes to Master but when he fires the query to retrieve the user details like user_id, name call will go to slave and he may not be getting any data ?

This is often handled by the application actively choosing to read from the master based on context.

A common example I've seen mentioned is a database of blog posts.  *Viewing, a blog post would obviously be done from a replica, but previewing an edit to a blog post -- which may be the same code, since it's rendering the post -- might be something you do by reading from the master.

Depending on the application, you might also read from the replica and then fall back to reading from the master if you don't find what you expect.

There are also solutions, like Maxscale -- designed by MariaDB but works with MySQL too -- that are essentially a proxy between your app and both MySQL servers.  Whenever you are in a transaction, you're talking to the master, whenever you're not in a transaction and issue a SELECT, the queries go to the replica... so you can easily force a read from the master just by doing it in a transaction.  There are other rules in play as well that send certain statements to both servers, such as USE and SET statements.  If the replica lag goes beyond a certain threshold, Maxscale sends everything to the master.


SSRS 2012 Report variables scope I have a report that uses data from several datasets. I chose to do it this way because the alternative would be to join about 12 tables.

From what I can gather, I have to use the dataset name to define the scope of the variable when using it in expressions. I have tried to do this, but I still get an error.

The expression I'm using is:

=(Fields!PropertyDescription.Value, "Application")

And the error I get is:

  System.Web.Services.Protocols.SoapException: The Value expression for the text box ‘Textbox11’ refers directly to the field ‘PropertyDescription’ without specifying a dataset aggregate. When the report contains multiple datasets, field references outside of a data region must be contained within aggregate functions which specify a dataset scope.

Even if I use an expression such as

=First(Fields!PropertyDescription.Value, "Application")

I get the same error.

Can someone perhaps help me with this error or suggest another way of combining the data?

After some more research I realised that what I wanted was data regions. I'm now using different lists and tablixes to bind to the different datasets.


How I develop Union Report Views for Multiple Single Tenant Databases? We have a database for each customer, over 20+ databases. All database schemas  are the same. Now we want to create a Consolidated Reporting View for All Databases, for total database analysis. How would I dynamically generate views for tables in the database, which combine the data? The databases reside on the same server instance.

Many db professionals prefer single tenant databases.

Single Tenant Database Discussion

Code provided below:
CustomerTemplateDb contains all the tables for single tenant database.

It will list other single tenant databases around it, and create one consolidated view in reporting database.

(Feel free to code review, or make modifications as needed. Works on my end)

declare @DatabaseTemplate nvarchar(max) = 'CustomerTemplate'
declare @DatabaseDestination nvarchar(max) = 'ReportingDb'
declare @TableSchema nvarchar(max)
declare @TableName nvarchar(max)

declare @DatabaseCurrent nvarchar(max)
declare @DatabaseCounter int = 1

declare @TableListGenerateCode nvarchar(max) = ''
declare @TableCounter int = 1

declare @DropView nvarchar(255) = ''

declare @TableSelectFromCurrentDatabaseCode nvarchar(max) = ''
declare @TableSelectFromAllDatabaseCode nvarchar(max) = ''

declare @ColumnSelectFromCurrentTableCode nvarchar(max) = ''

IF OBJECT_ID('tempdb..#DatabaseList') IS NOT NULL drop table #DatabaseList
create table #DatabaseList (DatabaseId int identity(1,1), DatabaseName varchar(255))

IF OBJECT_ID('tempdb..#TableList') IS NOT NULL drop table #TableList
create table #TableList (TableId int identity(1,1), TableSchema varchar(255), TableName varchar(255))

IF OBJECT_ID('tempdb..#ColumnList') IS NOT NULL drop table #ColumnList
create table #ColumnList (ColumnId int identity(1,1), ColumnName varchar(255))

insert into #DatabaseList (databasename)
select db.name
from sys.databases db
where
database_id > 4
and db_Name(database_id) <> @DatabaseTemplate and db_Name(database_id) <> 'reportingdb' and db_Name(database_id) <> 'electronics' and db_Name(database_id) <> 'mytestdb'
and state = 0;

set @TableListGenerateCode=
'
Insert into #TableList (TableSchema, TableName)
SELECT TABLE_SCHEMA, TABLE_NAME
FROM ' + @DatabaseTemplate + '.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE'' AND table_catalog = ' + '''' + @DatabaseTemplate + ''''

exec (@TableListGenerateCode)

while @TableCounter <= (select count(*) from #TableList)
begin

    set @TableSchema = (select TableSchema from #TableList where Tableid = @TableCounter)
    set @TableName = (select TableName from #TableList where Tableid = @TableCounter)
    set @DatabaseCounter = 1

    while @DatabaseCounter <= (select count(*) from #DatabaseList)
    begin

        set @DatabaseCurrent = (select DatabaseName from #DatabaseList where DatabaseId = @DatabaseCounter)

        truncate table #ColumnList
        set @ColumnSelectFromCurrentTableCode = 'insert into #ColumnList   select STUFF((
            SELECT '',
        '' + QUOTENAME(c.name)
            FROM ' + @DatabaseCurrent + '.sys.columns c where c.object_id = object_id(''' +  @DatabaseCurrent + '.' + @TableSchema + '.' + @Tablename +''')
            FOR XML PATH(''''), TYPE).value(''.'',''nvarchar(max)''),1,2,'''')'

        exec (@ColumnSelectFromCurrentTableCode)

        set @TableSelectFromCurrentDatabaseCode =
    '
    UNION ALL

    SELECT ' + (select top 1 ColumnName from #ColumnList) +
            '
    FROM ' + @DatabaseCurrent + '.' + @TableSchema + '.' +  @Tablename + CHAR(13)+CHAR(10) + '
            '
        set @TableSelectFromAllDatabaseCode = @TableSelectFromCurrentDatabaseCode + @TableSelectFromAllDatabaseCode

        set @DatabaseCounter = @DatabaseCounter + 1

    end

        set @TableSelectFromAllDatabaseCode = N' EXEC ' + @DatabaseDestination + '.sys.sp_executesql ' + 'N''Create View ' + @Tablename + 'Vw as
        ' + RIGHT(@TableSelectFromAllDatabaseCode, LEN(@TableSelectFromAllDatabaseCode) - 12)+''''

        PRINT @TableSelectFromAllDatabaseCode

        --PRINT ('Create View ' + @Tablename + 'Vw as
        --' + RIGHT(@TableSelectFromAllDatabaseCode, LEN(@TableSelectFromAllDatabaseCode) - 13)+'''');

        set @DropView = N' EXEC ' + @DatabaseDestination + '.sys.sp_executesql ' + 'N''IF OBJECT_ID(''''' + @TableName + 'Vw'''', ''''V'''') IS NOT NULL   DROP VIEW ' + @Tablename + 'Vw' + ''''

        exec (@DropView)
        exec (@TableSelectFromAllDatabaseCode)
        set @TableCounter = @TableCounter + 1

        set @DropView = ''
        set @TableSelectFromCurrentDatabaseCode = ''
        set @TableSelectFromAllDatabaseCode = ''

end


Performance of SQL Server Triggers I'm coming from an Oracle/PostgreSQL background and have a hard time coping with the limits of SQL Server's trigger implementation.

I want to update two different datetime columns if the value of a different column changes. This is to record two specific status changes of the row.

Now SQL Server does neither have row level triggers, nor before update triggers. So my understanding is that I need to join insert and deleted to find out if and which status change occurred and then run a "regular" update agains the table being updated.

My trigger currently looks like this:

create trigger ord_status_history_trigger
  on jobs
AFTER UPDATE
AS
  IF ( UPDATE(order_status) )
  BEGIN
    update jobs
      set  transfercomplete = case
                                when o.order_status = 'initial' and n.order_status = 'sent' then current_timestamp
                                else n.transfercomplete
                              end,
           installcomplete = case
                               when o.order_status = 'delivered' n.order_status = 'installed' then current_timestamp
                               else n.installcomplete
                            end
      from inserted n
        join deleted o on o.jobid = n.jobid
        join jobs aj on aj.jobid = n.jobid;
  END

My question is: does this trigger effectively result in two updates on the table?

The first one being the triggering update and the second one being the one executed by the trigger?

Or is SQL Server smart enough to merge that into a single update to the base table (essentially the way it happens with a e.g. a BEFORE UPDATE trigger in PostgreSQL)

Do I create a potential performance bottleneck here?

We could set those timestamps from within the application as well, but it would be nicer if we could delegate this to the database, so we can be sure this is never forgotten.

Your trigger runs after the updates have occurred on the table. If you update the table again in the trigger a new update is performed. See also the discussion about RECURSIVE_TRIGGERS.

If you want to run code before the update you will have to create an INSTEAD OF trigger.

Whether having to do two updates instead of one will become the bottleneck is entirely subject to the efficiency of the update in your trigger, and of course whether this UPDATE is already on the critical path or not. You probably have a primary key on job id, the pages are hot in buffer pool and you cannot have lock conflicts. So basically is only subject to a) how fast can your log accept writes and b) whether the version store (which serves the deleted and inserted pseudo-tables) can keep up.

If you ask me: such business logic status housekeeping belongs in the application, not in a trigger...


Use "LEN" function in "WHERE" clause in "CREATE UNIQUE INDEX" I have this table:

CREATE TABLE Table01 (column01 nvarchar(100));

And I want to create a unique index on column01 with this condition LEN(column01) >= 5

I tried:

CREATE UNIQUE INDEX UIX_01 ON Table01(column01) WHERE LEN(column01) >= 5;

I got:

  Incorrect WHERE clause for filtered index 'UIX_01' on table 'Table01'.

And :

ALTER TABLE Table01 ADD column01_length AS (LEN(column01));
CREATE UNIQUE INDEX UIX_01 ON Table01(column01) WHERE column01_length >= 5;

Produces:

  Filtered index 'UIX_01' cannot be created on table 'Table01' because the column 'column01_length' in the filter expression is a computed column. Rewrite the filter expression so that it does not include this column.

One method to workaround the filtered index restriction is with an indexed view:

CREATE TABLE dbo.Table01 (
  Column01 NVARCHAR(100)
);
GO

CREATE VIEW dbo.vw_Table01_Column01_LenOver5Unique
WITH SCHEMABINDING AS
SELECT Column01
FROM dbo.Table01
WHERE LEN(Column01) >= 5;
GO

CREATE UNIQUE CLUSTERED INDEX cdx
    ON dbo.vw_Table01_Column01_LenOver5Unique(Column01);
GO

INSERT INTO dbo.Table01 VALUES('1'); --success
INSERT INTO dbo.Table01 VALUES('1'); --success
INSERT INTO dbo.Table01 VALUES('55555'); --success
INSERT INTO dbo.Table01 VALUES('55555'); --duplicate key error
GO

EDIT:

  How should I define the view if I have two columns in the index?
      CREATE UNIQUE INDEX UIX_01 ON Table01(column01, column02) WHERE LEN(column01)>=5

The indexed view approach can be extended for a composite key by adding other key columns to the view definition and index. The same filter is applied in the view definition but uniqueness of qualifying rows enforced by the composite key rather than the single column value:

CREATE TABLE dbo.Table01 (
   Column01 NVARCHAR(100)
  ,Column02 NVARCHAR(100)
);
GO

CREATE VIEW dbo.vw_Table01_Column01_LenOver5Unique
WITH SCHEMABINDING AS
SELECT Column01, Column02
FROM dbo.Table01
WHERE LEN(Column01) >= 5;
GO

CREATE UNIQUE CLUSTERED INDEX cdx
    ON dbo.vw_Table01_Column01_LenOver5Unique(Column01, Column02)
GO

INSERT INTO dbo.Table01 VALUES('1','A'); --success
INSERT INTO dbo.Table01 VALUES('1','A'); --success
INSERT INTO dbo.Table01 VALUES('55555','A'); --success
INSERT INTO dbo.Table01 VALUES('55555','B'); --success
INSERT INTO dbo.Table01 VALUES('55555','B'); --duplicate key error
GO


Does combining columns into 1 column helps to optimize a large table? I have user, items and user_items tables, and as usual user has many items.
Tables contains:
User: ~50K
Items: ~3.5M
UserItems: ~5M

I am using MySQL and most of my queries are selecting items of a user.
So I wonder If it helps to keep data on UserItems table as follows,

user_id item_ids
1       1,22,23,45,66...

Then after getting ids, simply parse to get item ids.
Any suggestion/experience?

This is a bad idea.

Putting more than one data element into a single field invariably leads to headaches.

You will be much better off making a new table to store this data, like:

Table UserItems

UserID int
ItemId int

And then one row per item per user.  You can query this as needed and update it without any issues.

Think about adding one additional item to your proposed structure - it would be a challenge since you need to concatenate another space and another id to the end of the existing string.

It will also be a pain to check for a single item in that structure since you need to do a string search for every record.


What is the default value of ANSI Padding Enabled, and what should value should I set it to? When creating a new database, ANSI Padding Enabled is set to False by default.

According to this however, the default is ON (which I assume is the same as True). They also recommend to set it to ON and mention that in the future this setting will be deprecated and always set to ON.

I'm quite confused by all of this, actually my question comes from a ticket I have opened that is related to this setting.

So, what is the actual default value, and do I need to change any settings?

Per @MartinSmith's comment, the database setting is overwritten by the connection setting most of the times.

ADO.NET, ODBC, OLE DB and SSMS all set this to ON. Additional libraries/tools and settings are listed here.


SQLSERVER FullText search troubleshooting I have a table with nchar(32) column, with FullText search and auto change tracking activated.

Data set:

Name, SuperID
Item1, (LO}(bJ$
Item2, (LO}(bJ$
Item3, (LO}(bJ$

I run two queries:

select Name, SuperID from Asset A WHERE A.SuperID LIKE
N'%(LO}(bJ$%'

and

select Name, SuperID from Asset A WHERE (   CONTAINS
(A.SuperID ,N'"(LO}(bJ$"')  )

The first one returns 3 rows, and the second returns only 1 row.

Why is it happening? How can I analyse the contents of the Full Text search index? The column values in the first three results are identical. I have tried to update indexes/stats but that doesn't change the outcome.

UPDATE:
I have tried removing quotes around the CONTAINS query but I get an error message:

  "Syntax error near '(' in the full-text search condition

UPDATE2:

I should have phrased my question in a different way, I think! Have a look at the SuperID column values for the three rows that I would like to return. I have converted them to VARBINARY to ensure that there is no hidden trailing space trickery going on and they are actually the same:

SuperIDasBinary
0x28004C004F007D00280062004A0024002..
0x28004C004F007D00280062004A0024002..
0x28004C004F007D00280062004A0024002..

So why does the CONTAINS query above return only one row instead of three?

Whilst trying to re-create the problem with a fresh table in a separate DB, I ended up comparing the Full-Text catalogue settings. In the DB where I saw the erroneous behaviour the Table Full-Text Pending Changes value was 21219 (!).

I have then checked the FT log and found that the Asset table hasn't been updated for a long time:

  An internal query to load data for a crawl on database '-' and table
  'Asset' failed with error code 1205. Check the sql error code for more
  information about the condition causing this failure. The crawl needs
  to be restarted after this condition is removed.

and also

  Error: 30059, Severity: 16, State: 1
  A fatal error occurred during a full-text population and caused the population to be cancelled.
  Population type is: AUTO; database name is Media (id: 6); catalog name
  is - (id: 6); table name Asset (id: 1076966963). Fix
  the errors that are logged in the full-text crawl log. Then, resume
  the population. The basic Transact-SQL syntax for this is: ALTER
  FULLTEXT INDEX ON table_name RESUME POPULATION."

I had to manually resume the FT updates. After that the search has returned 3 items!


How to connect an oracle DB with MySQL workbench? I am a bit confused:
I want to connect an Oracle DB with the application "MySQL Workbench". In order its from Oracle as well I throught that this is possible. But until now Im not able to do so.

With the "Oracle SQL Developer" is it working, so my credentials have to be correct.

Is it just impossible to connect an oracle db with mysql workbench?

kind regards
mario

to answer your question:

"Thanks for your answers. I thought not that oracle own this product they made it able to connect to a oracle database. Is there a semilar tool like workbench for oracle dbs?" from comments,

you can use toad for oracle or even sql developer.


Efficient way to compute unique values using a PostgreSQL GIN index Say I have a JSONB column, with a GIN index on it.

I can use jsonb_object_keys() to fetch the keys for each row. But is there an efficient way of getting the unique set of keys, across all rows?

Surely the GIN index must have this information already, right?

NB: I know the "classic" solution: split the single column into a many-to-many relationship.

This can't be done.  Of the two built-in GIN operators for indexing JSONB, one of them only stores hashed values, so you wouldn't be able to reverse them, and other one stores flattened keys, irrespective of what level of the JSONB they were at.  That second one also hashes the values if they exceed a certain length.

Both of the methods can lead to false positives, so both rely on "rechecking" the full JSONB in the tabe to make sure they meet the criteria.

For example, with the "jsonb_ops" operator, the value {"ABCD": {"EFGH": "IJKL"}} will initially match the query @> '{"EFGH":{}}', because the value does have "EFGH" as a key.  It would then fail the recheck, because that key occurs at the wrong depth.


Apostrophe in EXECUTE postgres I need to print an empty string using EXECUTE command in Postgres. So, how to circle with quotes the string str in order for Postgres to realize my intention?

CREATE OR REPLACE FUNCTION foo() RETURNS void AS
$$
DECLARE
empty_str text='';
BEGIN
EXECUTE 'SELECT '||str||'';
END;
$$
LANGUAGE plpgsql;

Table name is a variable in my query.

I want to print nothing at all (not even a carriage return and/or newline). It's a simplified example of my real code where I have to substitute NULL values with empty strings. I use empty string as an option value in NULL 'null_string' in COPY function.

Adding to Andriy's answer, there is a neater way around called dollar quoting.  See what the documentation tells us about it:

  While the standard syntax for specifying string constants is usually
  convenient, it can be difficult to understand when the desired string
  contains many single quotes or backslashes, since each of those must
  be doubled. To allow more readable queries in such situations,
  PostgreSQL provides another way, called "dollar quoting", to write
  string constants. A dollar-quoted string constant consists of a dollar
  sign ($), an optional "tag" of zero or more characters, another dollar
  sign, an arbitrary sequence of characters that makes up the string
  content, a dollar sign, the same tag that began this dollar quote, and
  a dollar sign.

Using this technique simplifies your life and makes the statement much more readable:

CREATE OR REPLACE FUNCTION foo() RETURNS void AS
$$
DECLARE
    empty_str text := '';
BEGIN
    EXECUTE $sql$ SELECT ' $sql$ || empty_str || $sql$ ' $sql$;
END;
$$
LANGUAGE plpgsql;

I've added an extra space after and before $sql$ to make things more clear.  Here the tag that the documentation mentioned is 'sql'.  Note that you are already using dollar quoting: it's the pair of $$s around the function body.

Upon a hint from ypercube, I have to say that in the general case one should use

EXECUTE 'SELECT quote_nullable($1)' USING empty_str;

Read more about quoting in dynamic queries at https://www.postgresql.org/docs/9.6/static/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE


MySQL: How to ignore /etc/mysql/my.cnf for mysqld --initialize I compile a 5.7 MySQL database from source into a custom directory and use mysqld --initialize to initialize the database.

This works fine as long as there is no MySQL installed on the main system, or when I use mv /etc/mysql/my.cnf /etc/mysql/my.cnf_backup to get rid of the system-wide file, but of course that is a terrible workaround that breaks the system-wide mysql installation.

If a /etc/mysql/my.cnf is present, the initialization fails obviously as the paths are all wrong.

mysqld --initialize always seems to prefer /etc/mysql/my.cnf and ignores any file I specify on the command line with --defaults-file. It does however use single parameters that are specified on the command line (so it may work if I translate my complete my.cnf file into command line arguments, but that again is not a good solution and creates other problems)

Of course the whole point why I build from source into a custom directory is because I want it independent from any things that are installed on the main system.

Is there a way to let mysqld --initialize ignore /etc/mysql/my.cnf?

The manual mentions that the --defaults-file option has to be specified first, if you want to use it.
Otherwise the option is ignored.


Why is a port needed for DBMS? I always wondered, why a port must be assigned in order to use a DBMS? And what difference does it make using TCP or UDP (and even static and dynamic)?
Is it for communication purposes?
I know this is very basic question, but still I could not find a straight forward answer.

A port number is not necessary to use DBMS.
Individual programs could write directly to the DBMS's files.  But the rules of a DBMS have become very complex, and an immense amount of logic is needed to respect those rules and to respect the actions of other programs using the DB.  I've known that situation with Oracle, you could compile your programs to access the DB directly.
To avoid having even the most simple program becoming a huge blob, the DB authors came up with the DB server.  A single program had access to the DB, others wanting to use the DB, had to "talk" with that program, which executed their requests and sent back the results.  That single program has become a complete library, you can't count the processes of a single Oracle DB on your fingers and toes.
Now, "talking" between programs is inter-process communication.  One possibility is a "socket", no port needed, on the unixes it is presented as a file to which the program can write a request, the server reads it, executes, and writes the result, which the program can read.   Another possibility is TCP/IP, for which you need to know the port number.  The program sends its requests to that port, on which the DB server is listening, etc.  TCP is much more secure than UDP, slower, but no packets can get lost. You can't afford that with a DB.  Another big advantage: programs running on other systems can use the DB.


In SQL Server, what are the typical symptoms of parameter sniffing? There is a wealth of information on the DBA.SE about what parameter sniffing is and how it relates to individual query performance circumstances. I was hoping that someone could provide some sort of canonical answer as to what the symptoms of parameter sniffing are. So that's the question:

  What are the typical symptoms of parameter sniffing that would lead to its diagnosis as a potential culprit in performance?

It's commonly a potential cause when a stored procedure takes wildly different times to complete with different parameters.

Or when a stored procedure is seen taking a less than brilliant approach to getting data with certain parameters.

These would indicate to me that SQL Server had based it's query plan on the first parameters it had seen, but that these parameters weren't really indicative for all the possible parameter values.


Prevent unique constraint violations caused by concurrent inserts I have a situation where I need to insert a unique record into the database:

SELECT user FROM users WHERE id = 'some_randomly_generated_id'
If user does not exist, INSERT INTO users WHERE id = 'some_randomly_generated_id'

The problem is, a matching record can be added after the SELECT query but before the INSERT query, causing a unique constraint error. How would I prevent/go about this? These are my current thoughts after some research:

Run these queries under a transaction and use SELECT user FROM users WHERE ... FOR SHARE to lock the row so no new record could be inserted (I might be wrong on this, please correct me).
Handle the unique constraint error in the application and pretend that the record is inserted successfully. (No transaction, could be better since it allows greater concurrency and is trivial to implement)
Run only the INSERT IGNORE query instead, which silently ignores the unique constraint error. (Similar to the 2nd approach in my opinion, just that the error is not handled in the application).

Thanks in advance. My dialect is MySQL and isolation level is REPEATABLE READS if that helps make answering this question easier.

Failure to wrap in a transaction is causing your problem.
do
    compute random number
    BEGIN;
    SELECT ... FOR UPDATE;
    if row exists, then start loop over
    INSERT ...;
    COMMIT;
until success

And don't worry about transaction isolation mode.
Or...
do
    compute random number
    INSERT ...;
until success

with autocommit = ON; no worry about isolation mode.
Or...  Use an AUTO_INCREMENT column -- MySQL will deal with guaranteeing a unique id every time.  (Caveat: A little more code is needed on multi-master topologies.)


Why does my table keep getting corrupted after removing large amounts of data? I have a database that is about 400 gigs. The majority of the space is consumed by images of utility bills stored in string form as varchar(max) in a single table. It's a poor database design that I inherited. I have already exported the utility bills as pdfs to a network folder. Now I am trying to replace the images in the database with an empty string. I have been updating in small batches of 40k to 80k records at a time. The problem is that after a few batches, the table gets corrupted. I'll get a message that the update failed. When I run CHECKDB I get

Msg 8909, Level 16, State 1, Line 1 Table error

It's happened twice so far and I have to restore the database from a backup.
I need to know why this is happening and if there is a way to avoid it. I'm doing this in a test environment and I need to get this problem figured out before I can work on the production database.
Thank you for your help.
Edit: Changed binary to string. One of the images is stored as a continuous string of 65,535 characters.
Edit: This is happening on MSSQL 2014 SP3 (Version 12.0.6024.0)

Did you really mean varbinary(max)? Seems strange to have non-text data in a text-type column.
Anyhow, corruptions shouldn't occur. Your first step would be to make sure that you are up to date within the version of SQL Server you are using. I.e., (service pack and) CU.
If it persists, then I'd suspect a bug in SQL Server or that a corruption already existed before your updates. I.e., do a checkdb first and see if it is clean. If it is clean and gets corrupted by an update, then you either have some storage/machine issue (less likely if it is repeatable) or a bug in SQL server.