City Distance Map Database Design Well I am just a db beginner, I want to design something like this

I want to keep simple as possible, well I will enter all values[Cities, Distance etc.,] manually, because my cities are limited.
And also I want to Query like 'oneCity' Between 'AnotherCity', will yield the cities between them.

Any Advice, solution, concepts, technique or tutorials are appreciated. I googled a lot, but no solutions are appropriate :(

And also I don't know this is the right place to post, if this is a wrong place please ignore my mistake.

Thanks.

I've never tried this but here's some ideas I have:

You'll need to keep track of all cities, so you will need a City table.
You also need to know the distance between two cities that are directly connected to each other, so you will need a cities_distances table like this:

cities_distances
----------------
  From_City_ID
  To_City_ID
  Distance

This will let you keep track of how much distance it is from one city to another. Note that this is directional - The distance from A to B might not be the same as from B to A if the highway between them has a lane for each direction, and one lane has a detour around some natural feature (like a small lake, or difficult parts of terrain). Within a city, this is also important because you'll have one-way streets.

If you don't care about the direction of the distance between the two cities, you can just rename those two columsn CityA_ID and CityB_ID.

As for writing a query that gets the distance between any two cities, it might be easier to do that in code using Dijkstra's algorithm (because this is really just a graphing problem) than in query, but it might be possible using recursive queries. Or, you could generate a pre-calculated table that stores all this information and just look the data up in that.


Memory Table and Disk Table I'm running a pretty big (or maybe I should call it SELECT & INSERT-intensive) database.

Now I've heard here and there that a MEMORY-based table is better for my situation (since I now run on a RAID1 HDD array consisting of 2 HDDs and can't afford SSDs).  But I know that using the MEMORY engine is not good in case of a power outage (as the data on there will be lost). And since I'm running the server in my own house without UPS... well... you see where this is going.

So, how can I have 2 tables, 1 on the disk (InnoDB for example) and 1 in the memory, then have the memory table sync with the on-disk table once every 5 minutes so the data won't get lost completely in case of a power outage? (I realise that the data since the last sync will be gone.)

At the moment, I can use about 2GB of RAM for the database (which should not be a big deal because at the moment the table is about 300MB big with InnoDB).

EDIT:
you can find the code of the slowest page here (link to pastebin) and the page where it is running here

Big Query Cache == Big Trouble.

It takes a long time to purge the QC of table entries every time you write to the table.
I robs RAM from other useful tasks.
With 1GB for QC and 835 for buffer_pool, you are very probably swapping.  Swapping is terrible for performance; MySQL assumes its caches are not swapped!

Do not make the QC bigger than 50M.  In most production system it is better to turn off the QC.

With 2GB of RAM, you probably should not set innodb_buffer_pool_size bigger than 500M.

Listing the indexes does not help without seeing the SELECTs.  Please also provide SHOW CREATE TABLE.

50 SELECTs/sec -- Swapping and/or poor indexes and/or poor formulation of queries.  5000/sec may be possible with your hardware.

Abandon the discussion of MEMORY until you work through my comments.  We may (but probably won't) need to come back to MEMORY.


Postgresql select columns in join query say that i do inner join in query... the result will give columns from both table.
Is there a way to specify that i want only columns from one of the tables without specifying all columns names manualy?

Use a table alias:

select t1.*
from table_1 t1
  join table_2 t2 on t1.id = t2.id1;


Issues installing SQL Server 2008 on Windows Server 2008 R2 I am trying to install SQL Server 2008 on Windows Server 2008 R2 Cluster. While installing the first node, I keep on getting the following error:

  TITLE: Microsoft SQL Server 2008 Setup
  The following error has occurred:
  Attempted to perform an unauthorized operation.
  Click 'Retry' to retry the failed action, or click 'Cancel' to cancel this action and continue setup.

Has anyone seen this issue before? I have uninstalled it multiple times and tried re-installing it but same results.

Thanks Aaron B. for the help. The issue seems to be with the server itself. I had SA reimage the server and now SQL installed without any issues.


Unpivot a table in PostgreSQL I need to unpivot a table in PostgreSQL. The first dataset is what I have, the second is what I want:

Below is my schema with sample dataset:

http://www.sqlfiddle.com/#!17/3f45c/1

My understanding is that you may have to use JSON functions to accomplish this in PostgreSQL, as it doesn't have an unpivot function as some other SQL variants do. I'm open to other solutions, though.

Unpivot can be done using an array and unnest:

select s.state, s.city, 'item'||t.i as items, t.item
from state1 s
  cross join lateral unnest(array[item1, item2, item3]) with ordinality as t(item,i);

Online example: http://rextester.com/VPDDN96018


Database for store images Actualy I'm using MongoDB with embedded images next to other data in same documents.
Images count actualy are more than 14000 and each image size is less than 1MB. Images are readed frequently and updated rarely.

I want to separate images from data and store it in different database than data to use it as microservice and for scalability. I've read people saying that the best solution to store images is on file system, but you have limitations about number of files in folder and in a lot of CloudComputing services you don't have access to the file system. In MongoDB official documentation, they say that GridFS is faster than file system, but I've understood that the most case to use GridFS is when you have files with size more than 16MB.

Do you know if there are some specific database to store files?
If not, what conventional database is more efficient to store files?
Is MongoDB a good solution to store files? If yes, what is better for the size of my images, storing as normal documents in collection or as GridFS?

Thanks

You could do it using GridFS.

This is best approach.


Store server password in table for reading with CURSOR I would like to connect to multiple servers using Linked Server do download table and do queries.  I want to store server IP in a table and go down the list using CURSOR to connect to each.  I would like to store the password on the table as well but I don't know if this is bad idea.  Is this ok or what is better alternative?
Is there a version of sp_addlinkedsrvlogin that doesn't need password..?

It's definitely a bad practice and a bad idea to store any kind of passwords in plain text in the database, let alone the specific passwords for access to your other servers. Database servers are frequent targets of attacks, and storing your password in an unobfuscated and unencrypted manor makes them so much easier of a target.
I've seen a couple of your questions on here, and it makes me think that you'd best be served by centralizing your data into one server. A couple options to do that you can look into are Replication and SSIS. (AlwaysOn Availability Groups is another feature you can use to synchronize data across servers as well, but might be overkill for your use cases.)
Otherwise if you want to continue to query through Linked Servers you should only have to set them up once on your primary server, and not need to store or use the password to access them. You can follow either this guide or the Microsoft Docs on how to setup a Linked Server appropriately.
After they're setup, you can access entities from them using the four part naming scheme like so: SELECT SomeField FROM LinkedServerName.DatabaseName.SchemaName.TableName


Can't install a OLE DB Provider for IBM DB2 ISeries on SQL Servers higher than 2008 version I've currently had a SQL Server 2008 server, which we now need to migrate to a newer edition.

We have a couple of linked servers which I have not set up personally which utilizes a provider called IBMDASQL in SSMS. It appears as if the previous collegue responsible for this set up has used the link here in order to set up these connections.

However, when I try to run the Setup.exe from the installation I get an immediate error telling me that I have a Standard edition which is not supported, and I can see from the documentation that only SQL Server 2008 is supported.

How can I set up a OLE DB Provider for IBMDASQL (I need to get this working with a ISerie Connection)?

EDIT

I've tried to install OleDB provider for DB2 from Microsoft, and it installed successfully. It was even included in my Providers list in Management Studio, but when I tried to set up a Connection using the same name for provider etc. as the previous Connection had, I got the error that The OLD DB provider "DB2OLEDB" for linked server abc reported an error. [....] cannot initialize data source object of OLE DB provider DB2OLEDB for linked server abc. The same server used IBMDASQL earlier. I thought it was "included" in the DB2 OLE DB provider

On my previous installation I had the following settings:

Provider: IBM DB2 UDB for iSeries IBMDASQL OLE DB Provider
Product name: ISeries
Data Source: [an IP number]
Provider string:
Location:
Catalog: foo

the only difference I have in the new Connection is that

Provider: Microsoft OLE DB Provider for DB2

is used instead. Do I miss any steps?

If you want to use the Microsoft provider, you'd need to use the Microsoft provider's name and not IBM's provider name.

The IBM provider is/was part of Client Access for Windows.  You don't mention what version you are trying to install, but the latest is v7r1 and it supports up to Windows 8.1.

IBM has replaced client Access for Windows with the new Access Client Solution (ACS).  ACS is split into two packages, an OS independent package and a OS dependent one.

The OLEDB driver is part of the ACS Windows Application Package.

Both the Client Access for Windows and the ACS Windows application package are licensed software and available only through IBM's Entitled Software Support website (ESS).  You'll need your IBM i folks to download it for you, or help you get access.


Debug Connection Pool Error I have been getting the below error.

    System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> System.InvalidOperationException: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)

I know that this is a question already asked by some users and i have gone through most of those and i learned the usual root causes and the fixes.
I learning are below:

systems is unable to open new connections because connection pool max
is reached.(default is 100 and it is set in the connection string)
Opened connections are not closed, called connection leaking
More than 100 concurrent connections are being used. Increase the max
pool size in the connection string if we have more than 100
simultaneous users.
Slow queries or open transactions blocks new connections.
When the connections are being held open by slow query execution/open transactions and instead of being reusing connections the new connection are open and eventually reaches the connection pool maximum.

When i did my research i learned that it connection pool is something which the application maintains.I talked to the application team and they say that it has never occurred earlier(for the last 4 or 5 years) and so it must be something with database server.
Our SQL server version earlier was SQL 2008 R2 and we migrated to SQL Server 2016 as the data became huge like 1.5 TB and the performance was bad.
After migrating to SQL Server 2016,and fixing some performance issues we were stable,but getting this connection pool error now and then.

How do i go about this connection pool error from the database side.

Thanks

I talked to the application team and they say that it has never occurred earlier...so it must be something with database server

Sounds about right =P

  Our SQL server version earlier was SQL 2008 R2 and we migrated to SQL Server 2016

It's likely that, since your SQL Server upgrade, the performance of some of your queries has changed.  This can lead to queries being blocked, or just running for longer (which in turn can cause this connection pooling problem, as you discovered in your investigation).

Have the developers, or users, notify you when they start getting these connection pool errors.  This will give you a time range to look into.

If you have a monitoring tool, use it to look at the queries that were running during that time.  This will give you the queries that need to be tuned.

If you don't have a monitoring tool, try logging sp_WhoIsActive to a table.  There are good step-by-step instructions by Tara Kizer here: Logging Activity Using sp_WhoIsActive – Take 2


How do I create a user invitation model? I am developing an application which allows 'users' to create 'lists'. I have a many-to-many relationship between 'users' and 'lists' (this might not be right). Additionally 'Lists' have many 'Tasks'.

What I would like to do is extend this model to include the idea of 'invitations'. I would like users to be able to invite one another to other lists. A user can create many invitations. A user can create an 'invitation' and the invitation would have one or many 'invitees' which, in turn, would be 'Users'. So am confused about how this should be organized in a relational db.

I think the core problem is: users 'own' invitations but also can be the recipient of an invitation. Clear as mud? :)

I was hoping someone could offer some advice on how this might be accomplished. Any example ERDs would be useful. If my question requires further clarification I can provide it.

Thanks!

You could have a pretty simple schema for this with a "trick": treat all user -> list associations as "invitations", including the ownership relation.

You'd have one table for the users (with say a userid column).
Then a table for the lists (with a listid).
The relation between the users and lists tables could be:

userid | listid    | role
----------------------------
bob    | dbas      | owner
alice  | dbas      | invitee
alice  | sysadmins | owner
bob    | sysadmins | invitee
eve    | sysadmins | invitee

An invitation is simply another line in that table.
This allows you to have different levels of access to the lists. A user could "invite" another to have full-control of a list, just read access, or something in between - just define the appropriate set of roles for your use-case.

You might want to add things like "creator" information, last modification information to the lists themselves.


Get maximum from aggregated values in a query I have a table details it has a column kills and a column player_slot and a column match_id. I want to find the maximum difference between the SUM of kills for player_slot 0-4 and 128-132. Can someone give me a hint?

Thats my table details:

id,
kills,
match_id,
player_id,
player_slot

Thats what I got so far, it's not much:

SELECT SUM(m."kills") AS max_kills
FROM details m
INNER JOIN matches ON matches.id = m.match_id
WHERE m.player_slot IN (0,1,2,3,4)
GROUP BY m."match_id"

SELECT SUM(m."kills") AS max_kills
FROM details m
INNER JOIN matches ON matches.id = m.match_id
WHERE m.player_slot IN (128,129,130,131,132)
GROUP BY m."match_id"

SELECT *
FROM  (
   SELECT match_id
         ,SUM(CASE WHEN d.player_slot BETWEEN   0 AND   4
                   THEN d.kills ELSE 0 END) AS team1_kills
         ,SUM(CASE WHEN d.player_slot BETWEEN 128 AND 132
                   THEN d.kills ELSE 0 END) AS team2_kills
   FROM   details d
   WHERE (d.player_slot BETWEEN   0 AND   4
      OR  d.player_slot BETWEEN 128 AND 132) -- parens optional
   GROUP  BY d.match_id
   ) sub
ORDER  BY @(team1_kills - team2_kills) DESC
LIMIT  1;

The subquery is only for convenience.

The WHERE can might improve performance if a matching index is available. Redundant otherwise.

The CASE statements sum only kills from qualified players.

The @- operator produces the absolute value of the score difference. This is used to sort by (descending), then we only need to pick the first.

Don't use m as table alias for details, when a second table called matches is involved. That's confusing. Using d instead.

More generally, why do you join to matches at all? It is not used in the query. Are there details without associated match to exclude? (Then it would make sense.)

Based on comments
The query could probably be simpler / faster this way:
SELECT *
FROM  (
   SELECT match_id
         ,SUM(CASE WHEN p.team_id = ? THEN d.kills ELSE 0 END) AS team1_kills -- team1
         ,SUM(CASE WHEN p.team_id = ? THEN d.kills ELSE 0 END) AS team2_kills -- team2
   FROM   details d
   JOIN   players p ON p.id = d.player_slot  -- guessing from comment
   WHERE  p.team_id  IN (?, ?)               -- teams to compare go here.
   GROUP  BY d.match_id
   ) sub
ORDER  BY @(team1_kills - team2_kills) DESC
LIMIT  1;


Check integrity between servers I have three Database servers, running PostgreSQL 9.2:

  master01 (Own data center USA)

  slave01 (Own data center USA)

  slave02 (AWS Australia)

Both slaves use streaming replication + wal_files (delivered by master01 via ssh).

The postgres process on the slave02 server has been recently stopped, due a maintenance on the slave02's postgres disk volume(/var/lib/pgsql).

Postgres is running on: /var/lib/pgsql

Wal files are shipped to this volume: /var/lib/walfiles

Once the volume was restored, I started postgres and got this log:

cp: cannot stat '/var/lib/walfiles/0000000500008AA800000069': No such file or directory
cp: cannot stat '/var/lib/walfiles/0000000500008AA80000006A': No such file or directory
consistent recovery state reached at 8AA8/8AD22F70
record with zero length at 8AA8/8AD22F70
database system is ready to accept read only connections
streaming replication successfully connected to primary

I have a very large table, that is constantly updated. I ran this query on slave01 and slave02 server:

select max(id) from ids_table;

SLAVE01:

   max
----------
 45148099
(1 row)

SLAVE02:

   max
----------
 45148095
(1 row)

Can you see the results are different? And that shouldn't be different!

Question:

How can I check the data between servers? How can I see if there is something wrong with slave02 due to those No such file or directory errors?

UPDATE 1:

As far as I could see, checksum is only available on Postgres 9.3+ ? So can't use that!

UPDATE 2: Anwsering Sahap Asci's questions

On the master:

synchronous_commit = on
#synchronous_standby_names = ''

First of all your replication is not synchronized. That means there can be always slight differences between master and slave. if you change configuration like below your master won't return commit until the replication processes.

  synchronous_standby_names = '*'

In your case, synchronized replication can be problematic because your slave is quite distant from the master so that the commits get slower.

Still with synchronized commit running a query 2 times in different transactions can get different results because of data changes.

I recommend you to read the blog; Evolution of Fault Tolerance in PostgreSQL: Synchronous Commit.


Deleting an Oracle database without connecting to it? I have two databases which were created using dbca.
I made some changes in the second database, and I can't login to that database anymore, and, of course, I can't delete it by using dbca.

I am using Centos 5 and Oracle 10g Release 2.

How can I delete a database manually without impacting another dababase?

From René Nyffenegger's blog:

  Since 10g, It is possible to drop a database in Oracle 10g with the new SQL statement drop database…

  The command gets rid of datafiles online redo log files, controlfiles and spfile…

shutdown abort;
startup mount exclusive restrict;
drop database;
exit

  Requirements:

  . database must be closed
    . exclusively mounted
    . restricted

If you do not have a password, you should still be able to connect from the console, eg:

sqlplus / as sysdba


update matching column value in anywhere in database sqlserver I have assetid column in multiple table in my Database and Asset(main) table

I want to update all occurences of tables in all occurences of inserts in database assetid column where value has 1020 to 1

update AdminInterests set assetid = 1 where 1020

I just want to how to apply this update for entire database all tables with one query

One possible approach is to generate and execute a dynamic statement using information from system catalog views. It's important to note, that such an update may fail, if assetid column is a primary key or has constraints in some of the tables.

DECLARE @stm nvarchar(max)

SELECT @stm = (
    SELECT CONCAT(
        ' UPDATE ', QUOTENAME(sch.[name]), '.', QUOTENAME(tab.[name]),
        ' SET ', QUOTENAME(col.[name]), ' = 1',
        ' WHERE ', QUOTENAME(col.[name]), ' = 1020;'
    )
    FROM sys.columns col
    JOIN sys.tables tab ON col.object_id = tab.object_id
    JOIN sys.schemas sch ON tab.schema_id = sch.schema_id
    WHERE col.[name] = 'assetid'
    FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)')

PRINT @stm
EXEC sp_executesql @stm

SQL Server (and most other relational database systems) store information about the tables, views, procedures etc. that have been created in a set of internal tables. These are collectively known as the catalog. Specifically there are ones for schemas, tables and columns. We are allowed to read the catalog from code we write.

When a client passes SQL to a server the query is just a text string. So if we can generate a string of the correct format we can pass it to the server and it will be executed. The SELECT statement above generates such a string using the information available in the catalog.

Rather than write a loop that processes one table each pass the code constructs a single string containing all required statements. Each statement is terminated by the semicolon at the end of the literal ' = 1020;'. The statements are concatenated using a trick of how FOR XML constructs its output. In newer versions of SQL Server STRING_AGG could have been used instead.

The print statement is just to aid debugging.

Finally the exec submits the generated string to SQL Server for execution.


How to run SQL services on NT SERVICE\MSSQLSERVER account if it is running earlier on LocalSystem Recently just for experimental puprose, I have relocated the default (C:...) data\log file paths in server settings to new location (G:\DBA).

Then I've moved the previously existing db's data\log files to the new location (G:\DBA) and restarted the SQL services. From then, whenever I'm creating DB, it is working as expected i.e., data\log files are getting created in new location(cool...).

But the problem is, I see all my previously existing DBs are in RecoveryPending state also got an error like Access denied.

Thinking that service account on which SQL is running (NT SERVICE\MSSQLSERVER) is not having access to new location(G:\DBA), I changed the logon account as Local System for SQL services in Computer Management console. Now I see all db's are good.

But, I want to run my sql services on NT SERVICE\MSSQLSERVER (default instance) service account. I tried by giving full access to G:\DBA\ for NT SERVICE\MSSQLSERVER, but no use. Please help me what all I have to do if I want to run my SQL services back on NT SERVICE\MSSQLSERVER service account. I'm beginner in this field. Please help me out.

Thanks in advance. . .

You have to change the service account in SQL Server Configuration Manager in order to have all required permissions applied to the service account.

Open Configuration Manager, select SQL Server Services in the treeview on the left, then open the properties window of the desired SQL Server instance.

Set the service account as follows:

Then open file explorer, navigate to the SQL Server data root and change the permissions on the properties window. Click the Advanced button:

Click the "Change permissions" button:

Then add the service account using the add button. If your computer is in a domain, make sure you select the local computer and enter NT SERVICE\MSSQLSERVER:

Make sure you check the "replace permissions on child objects" checkbox.


Linked Server Security under SQL Agent Job Context I have two SQL servers V1 and V2. Both 2012.
V1 has linked server connection to V2.
There are two Windows accounts: one running SQL server, the other
running the Agent service. The two accounts are common to V1 and V2.
I have a SQL Agent job that is owned by a non-sysadmin login, an SQL Server account. This account is also present on both servers with the same name and password.
On Linked Server Properties, Security tab, there are no explicit mappings. The default then is set to "Be made using the login's current security context"

Here's the problem.

I run SQL statement against V2 from V1 in SSMS as the non-sysadmin login and it works. However, when I create a job owned by the login with the same query, it fails with:

  Executed as user: NONSYSADMIN_USER.
  Linked servers cannot be used under impersonation without a mapping for the impersonated login.
  [SQLSTATE 42000] (Error 7437).  The step failed.

Then I run in SSMS with EXECUTE AS which results in pretty much the same:

  Msg 7437, Level 16, State 1...
  Linked servers cannot be used under impersonation without a mapping for the impersonated login.

I then schedule another job which is owned by sysadmin, running the same query and that works.

From my understanding, if the job is owned by sysadmin, there is no impersonation, the job runs (and steps as long as there is no run as or proxy set) as the Agent service account.

So, my question is, shouldn't the sysadmin owned job also fail since it's using Windows account, and as far as I know there isn't any delegation configured for the account. Or does the fact that it works indicate that the delegation was configured?

My ultimate goal is to have a job that is owned by a non-sysadmin login, which is a windows account, that runs queries against the linked server. Will this work?

According to the Microsoft's KB article, it would not without employing workarounds. Can anyone confirm it?

Here are some of the references I've used (among others):

SQL Server Agent jobs and user contexts, Tibor Karaszi's post on sqlblog.com
Linked Server Properties (Security Page) on TechNet

Put NT AUTHORITY\SYSTEM user in sysadmin server role
Add this user into linked server login mappings with remote user name and pwd


Windows ODBC Driver versions for Netezza I am looking for information regarding the Netezza windows ODBC driver versions.

What are the supported/preferred windows ODBC driver versions for Netezza 7.2, Netezza 6.1 and Netezza 6.0.

Any information is very much appreciated.

Based on the current release driver release compatibility matrix, found here, if you want to support Netezza NPS v6.0, v6.1, and v7.2 with the same ODBC driver, you should pick the most recent patch of the 7.1 ODBC driver.

If you don't need to have the same driver support all three server levels, then you would likely be better off going with the most recent patch of the v7.1 ODBC driver for Netezza v6.0 and v6.1, and going with the most recent patch of the v7.2 ODBC driver for Netezza v7.2.


Running two DELETE statements causes a syntax error DELETE `member`
    FROM `member`
    WHERE `member`.`type_id` = 1;

    DELETE `subscriptions`
    FROM `subscriptions`
    WHERE `subscriptions`.`type_id` =  1;

  Table: subscriptions Columns: id  int(11) PK AI type_id   int(11)

  Table: member Columns: id int(11) PK AI type_id   int(11)

With the above I seem to be getting the following:

 Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELETE `subscriptions`
        FROM `subscriptions`
        WHERE `subscriptions' at line 5:

I think the problem that I am having is that I am trying to run both queries at the same time. I have also put this through a syntax checker and it gives the following: screenshot.

On a side note notice that both tables contain the attribute type_id. I did try to condense this by using an inner join:

DELETE FROM member, subscriptions
USING member
INNER JOIN subscriptions ON type.id = type_id
WHERE type.id= 1;

But I believe the above inner join isn't correct

You can't* combine multiple queries like this.  You need to execute them separately.

When you see the error ...the right syntax to use near... that's telling you that MySQL's parser (lexer?) can't come up with any valid reason why it found whatever it's complaining about in the specific place within the statement where it was encountered... in this case, the beginning of a second query.

You'll need to send them to the server separately.

If these two deletions need to occur together, atomically, that should be handled in a transaction.

*"can't" -- actually, with some libraries, you can enable multi-statement execution, but don't do that. It opens up a whole new world of sql injection vulnerabilities.


TSQL Delete All dates and stores data This might sound like a stupid question but I can't seem to find a better way.
It's a posting system where stores send us info on a daily basis (can be for multiple days). That info is placed in TempTable. For every day found in TempTable it is always for a full day, and the TempTable is good for all stores found in it. So If TempTable has 2 different Stores and 2 different Days such as Day1-Store1 and Day2-Store2, It would mean that nothing happened on Day1 for Store2 and Day2 for Store1.

So, I want to delete any record that BigTable has (for a Specific Day and Store) that is not in the tempTable (for the Days found and Stores Found in the TempTable)
In other words, If Temptable has an entry for Day1-Store1 and Day2-Store2, I need to delete from the Big Table Day1-Store2 and Day2-Store1 and any other entry that is not in the temp Table...

This is what, I'm using right now (it works) but it takes way too long.  I'm looking for a way to minimize the number of joins on the TempTable.
Matching Clustered indexes exist on both BigTable and TempTable (Date/Store/SKU_id)

Delete X
From BigTable X
join (Select Distinct postingDate from TempTable) D on X.PostingDate = D.PostingDate
Join (Select Distinct postingStore from TempTable) S on X.Store = S.PostingStore
Left join TempTable T on X.PostingDate = T.PostingDate
    and X.Store = T.PostingStore and X.SKU_id = T.SKU_id
Where T.Sku_id is null

I'm sure this isn't the best way to do this.
Thanking you all in advance

[MORE INFO]
Since I've noticed that the suggestions given posted are to delete everything in BigTable that is not in TempTable, i thought that I should add the following point:

BigTable contains All days for all stores that have already been posted.
TempTable contains the Posting info of specific Days and Stores found in TempTable.  I want to delete from BigTable the Stores and Days found in TempTable but without deleting the good entries that are already in BigTable.

If Big Table has the following entries:

Day1 Store1 Sku123 ...
Day1 Store2 Sku124 ...
Day1 Store3 Sku123 ...
Day2 Store1 Sku124 ...
Day2 Store2 Sku123 ...
Day2 Store3 Sku123 ...

And TmpTable has the following entries:

Day1 Store1 Sku124 ...
Day2 Store2 Sku123 ...

I would want to delete from BigTable any record for Day1 and Day2 for Store1 and Store2 that is not in TempTable... So I would want to Delete the Following Records from BigTable:

Day1 Store1 Sku123 ...
Day1 Store2 Sku124 ...
Day2 Store1 Sku124 ...

If I understand it right, these queries should work:

DELETE X
From BigTableX X
Left join TempTableX T on X.PostingDate = T.PostingDate
    and X.Store = T.PostingStore and X.SKU_id = T.SKU_id
Where T.Sku_id is null
AND EXISTS (Select 1 from TempTableX WHERE postingDate = X.PostingDate)
AND EXISTS (Select 1 from TempTableX WHERE PostingStore = X.Store)

DELETE X
From BigTableX X
CROSS APPLY (Select TOP(1) p = 1 from TempTableX WHERE postingDate = X.PostingDate) P
CROSS APPLY (Select TOP(1) s = 1 from TempTableX WHERE postingStore = X.Store) S
Left join TempTableX T on X.PostingDate = T.PostingDate
    and X.Store = T.PostingStore and X.SKU_id = T.SKU_id
Where T.Sku_id is null

Either of these 2 queries will delete the same rows.

On my test sample they are more efficient but my test tables don't match your data and you will have to test it.
They all do 4 table scan but the new query does not sort data (no DISTINCT).

Can you please provide both execution plans (old and new query) for further analysis.

It may help to add an index on postingDate and on postingStore or on postingDate/postingStore/sku_id. If CLUSTERED INDEX on TempTable (Date/Store/SKU_id) is not narrow enough (3 varchar...), it may be better to remove it.


Why does key "concatenation" help to bring an unnormalized table to 1NF? This question is about the proposed technique to bringing an unnormalized table to first normal form (1NF) that my textbook "A Guide To MySQL" presents. It gives an example of an unnormalized table with repeating groups:

ORDERS (ORDER_NUM, ORDER_DATE, (PART_NUM, NUM_ORDERED)).

There are multiple entries in the PART_NUM column because a customer can order many parts at once (associated with a single order). The solution is to create a composite key (ORDER_NUM, PART_NUM), so now there are multiple rows in the ORDERS table with the same ORDER_NUM but different PART_NUM that represent a customer ordering multiple parts.

My question is how does that solution make repeating groups go away? Couldn't I have a composite key with ORDER_NUM and PART_NUM but with PART_NUM still having multiple entries? I'm confused. Unnormalized table:

Normalized to 1NF table:

Using the schema shown, you are GOING to have multiple instances of the same PART_NUM, but the ORDER_NUM will be different for each of those. The key is the unique combination of ORDER_NUM and PART_NUM.

The composite key will permit one record per unique combination of order and part. A specific part can be associated with any order. Presumably the NUM_ORDERED column specifies the quantity of that particular part on that order.

Personally, I might use a surrogate key with a somewhat different schema. But that isn’t what your textbook is trying to illustrate in this case.

OP's Comment

  I realized the uniqueness gained by order and part making up the primary key. I guess I'm still wondering if/how that actually gets rid of repeating groups (the whole point of 1NF). I definitely see an improvement in the design, but it feels like my book is leaving out part of the explanation.

Comments incorporated into the answer

I dislike the design in your textbook. I would use 3 tables: orders, parts, and orders_parts (a many-to-many join table). The order and the part are defined one time each, then referenced from the join table to put parts on orders.

There is more to a part and an order than just the id. All of that definition data is in the parts and orders tables, specified just once. The ids are then referenced in the orders_parts join table, where they are foreign keys. The join table is just keys. It lets you put multiple parts on an order, and put the same part on multiple orders, without any redundant data.

Don't let the repetition of keys throw you. The data is not repeated. You don't define any part more than once. You don't define any order more than once. But you do reference the same parts and orders multiple times. In the example just below, Part 10 is referenced on Orders 1 and 2. Part 11 is referenced on Orders 1 and 3, etc.

Order | Part
--------------
  1      10
  1      11
  1      12
  2      10
  2      12
  3      11

The two foreign keys combined are also a composite key (unique and/or primary), if the design calls for it. This is what makes the database relational.


Is there a difference in performance between @date and getdate()? Usually I use the getdate() function in my where clauses to go back in time.
Something like:

 DOC.DATUM >= DATEADD(DD,-1*SSN_SDO.DANA_ZA_POVRAT,GETDATE())

Will SQL Server 2008R2 perform faster queries if I first declare a date parameter and use that in my queries instead?

declare @dateNow date = getdate()
...
where
     DOC.DATUM >= DATEADD(DD,-1*SSN_SDO.DANA_ZA_POVRAT,@dateNow )

The answer is - you have to test it to find out.
I did a test of my own on a table which has ~8,000,000 rows

DECLARE @date DATETIME
SET @date = GETDATE()
;
SELECT T.DateCol, DATEADD(dd,-100,@date)
FROM dbo.TableName AS T
WHERE T.DateCol > DATEADD(dd,-100,@date)
;
SELECT T.DateCol, DATEADD(dd,-100,GETDATE())
FROM dbo.TableName AS T
WHERE T.DateCol > DATEADD(dd,-100,GETDATE())
;

In my case the use of the variable caused SQL Server to estimate an expensive plan:

the reason is that SQL Server builds the query plan during compilation,
at that time the value of the variable is not yet known and SQL Server can not use the statistics.
I suggest you read about parameterization

There are other cases where the use of the variable will cost less then calling a function many times,
so here it is again - TEST TEST, and TEST :)


Database consistency checker in postgresql Are there any DBCC(Database consistency checker) commands in PostgreSQL? I could find SQL server DBCC commands but not for Postgres? I read that postgresql has inbuilt functionality of perfomance tuning and no DBCC commands are available for postgres. Is it true?

There is no built-in consistency check command or tool in PostgreSQL.

The general view is that one should not be necessary, as corruption and inconsistency should not be possible on a quality hardware/software stack. If problems do arise, there's no guarantee any kind of consistency check would find them, so it'd just create a false sense of security. I don't agree with that sentiment, but it's what seems to come out when this is periodically discussed on pgsql-hackers.

As usual, the underlying problem is that nobody particularly needs a consistency checker tool to meet their immediate needs, so nobody's spending the time to write one to scratch an itch and nobody's funding the development of one on a commercial contract or in-house basis. Volunteering? :p

PostgreSQL (until 9.3) didn't support block level checksums. So one of the main things that you're used to verifying did not exist and could thus not be verified. A tool to scan all relations and validate checksums does not exist in PostgreSQL 9.3, but would be desirable to add and may appear in a future version.  In the mean time all you can do is SELECT * from each relation individually - but due to the fact that PostgreSQL uses operating system buffer cache for reads there's no gurantee that'll actually force a read of the underlying disk block anyway. A new tool would be needed to do this.

PostgreSQL tends to avoid redundantly storing information where possible, so there's often nothing to check against, just a single authorative source. A consistency checker can't do much unless the same information appears, or can be derived from, multiple different places.

It's also very hard to do any kind of useful checking concurrently, on a database that's still busy and active. Most installations aren't going to be willing to lock the entire database, or at least several major relations at a time, to run some kind of consistency check. So the checker would need to be able to operate on a database that's subject to concurrent modifications, making it even harder to write and able to detect fewer problems reliably.

There's still plenty that a validator tool could do if one were written, especially if it was allowed to take multiple relation exclusive locks:

Check that all tablespaces exist on disk.
Check that each pg_class entry has file(s) corresponding to its relfilenode in the correct tablespace.
Inspect visibility maps, free-space maps, etc, ensuring they're present when they should be, readable, and appear to match the relation they're associated with.
Report orphan on-disk file nodes. (These are normal due to transactional DDL and lazy unlinking, but a checker could force eager unlinking and lock all relations before running the check).
Read every block of each relation and look for obvious problems. For heap relations that'd be things like:

an xmin greater than xmax (after considering xid wrap-around)
Tuples created by transactions in the future
broken HOT chains / broken ctid chains
tuple structures that fail to match the table attributes
any Datum that doesn't round-trip its _in and _out functions unchanged or throws an error
NULL bitmap fields set on NOT NULL table attributes
Re-execution of CHECK constraints fails

Re-check foreign key and exclusion constraints after locking all tables involved

... and probably lots more I don't know enough about Pg's guts to figure out, such as attempts to detect torn pages, b-tree structure validation, sanity checking GIN and GiST indexes, sanity checking pg_control, and more I wouldn't know where to even start with.

If you're keen to have such a tool, the best thing to do is learn enough to come up with a concrete proposal about how it should work - and make the time to work on it, or to fund others to spend time on its development.

Personally I'd be really quite happy to have something that could check a stopped database cluster using a special startup mode for the postgres backend, so I could (somewhat) validate physical database copies taken with pg_basebackup, with pg_start_backup(), rsync and pg_stop_backup, with file-system level atomic snapshots, etc.

Alternately, you could do what most others do: Ensure your hardware and software stack is robust and properly configured, keep good backups, and monitor your logs. There's no substitute for proper testing of the whole stack before commissioning a server - and for good backups, both physical (streaming / PITR) and logical (dumps). Do plug-pull testing on a loaded database - repeatedly - before going live to make sure your supposedly reliable I/O subsystem really is. Use multiple forms of backup.


When upgrading config servers to use WireTiger, the running config server with wiredtiger does not authenticate the existing pass and user? In order to upgrade mongodb to 3.0.0, I am upgrading my config servers to use WireTiger. I followed the subsection of Change Config Server to Use WiredTiger. There are 18 steps. I run mongodump --port 27019 --username user --password pass --authenticationDatabase admin --out /var/data/config1/mongodump-15-03-16/ to bump the data. When I went to step 6, I tried to use "mongorestore --port 27019 --username user --password pass --authenticationDatabase admin --authenticationMechanism MONGODB-CR /var/data/config1/mongodump-15-03-16/" to restore the data.
The error is

  2015-03-17T17:37:51.963Z I NETWORK  [conn14] end connection 127.0.0.1:56093 (5 connections now open)

  2015-03-17T17:38:01.685Z I NETWORK  [initandlisten] connection accepted from 127.0.0.1:56095 #15 (6 connections now open)

  2015-03-17T17:38:01.687Z I ACCESS   [conn15]  authenticate db: admin { authenticate: 1, nonce: "xxx", user: "user", key: "xxx" }

  2015-03-17T17:38:01.687Z I ACCESS   [conn15] Failed to authenticate user@admin with mechanism MONGODB-CR: AuthenticationFailed UserNotFound Could not find user user@admin

  2015-03-17T17:38:01.687Z I NETWORK  [conn15] end connection 127.0.0.1:56095 (5 connections now open)

I have no idea. But when I start the config server with MMAP storage engine, no errors. My config file is

storage:
    dbPath: "/var/data/config1/db"
    engine: "wiredTiger"
    directoryPerDB: true
    journal:
        enabled: true
systemLog:
    destination: file
    path: "/var/data/config1/log/config1.log"
    logAppend: true
    timeStampFormat: iso8601-utc
processManagement:
    fork: true
net:
    bindIp: 0.0.0.0
    port : 27019
    wireObjectCheck : false
    unixDomainSocket:
        enabled : true
security:
    keyFile: "/var/data/config1/keys/mongodb-keyfile"
    authorization: "enabled"
sharding:
    clusterRole: "configsvr"

Any idea? Should I upgrade the authentication before upgrade the config servers to user WiredTiger?  In addition, I used the same binary /usr/bin/mongod to generate the config server process and the data server process. Does this matter?

It looks like the current MongoDB 3.0 upgrade instructions are missing mention of two important parameters for backing up and restoring users and roles:

mongodump --dumpDbUsersAndRoles (see also: Required Access to Backup User Data).
mongorestore --restoreDbUsersAndRoles (see also: Required Access to Restore User Data)

I can think of several approaches to fix:

If you don't have many user accounts on the config servers, recreate the administrator & user accounts. This isn't ideal, but is probably the fastest approach.
Export the users from your mmap database. This is more involved, but saves you recreating the users & roles. I've described steps for this below.
Redo the config server migration with the user & role information included. I expect this is the least desirable option.

Exporting the users

Assuming you have already upgraded all of your config servers to WiredTiger, here are some steps to add the user information:

Disable the balancer
Stop the last config server listed in your mongos' configDB setting (will call that config3 for the purpose of these steps). This will ensure your sharded cluster metadata remains read-only for the following steps.
Re-start config2 using the mmap data directory

At this stage you should have:

config1 (running WiredTiger)
config2 (running mmap with user/role data)
config3 (stopped)

Export the data from config2:

  mongodump --db config --dumpDbUsersAndRoles --username .. --password ..

Add any other parameters needed, eg --authenticationDatabase .. if you need to auth against another database.
If you have users in the admin database on your config server, you will also want to dump that as well.
(optional) Remove files from your dump except for the user/role information. If you are certain nothing has changed since you did the original migration from mmap to WiredTiger you could skip this step, however it would be safer to not overwrite any existing data.

Preview the files to remove:

  find ./dump -type f -not -name "\$admin.system*"

WARNING: removing files, make sure you have previewed to confirm:

  find ./dump -type f -not -name "\$admin.system*" | xargs rm

Re-start config2 using the wiredTiger storage engine
Run: mongorestore --db config --restoreDbUsersAndRoles dump/config/

You should see messages about restoring users & roles, for example:

  2015-03-18T02:41:34.887+1100  restoring users from dump/config/$admin.system.users.bson

  2015-03-18T02:41:34.887+1100  restoring roles from dump/config/$admin.system.roles.bson

Login to config2 and confirm the users are correctly setup (i.e. auth with admin account, use db.getUsers() to check).

At this stage you should have:

config1 (running WiredTiger)
config2 (running WiredTiger with user/role data)
config3 (stopped)

Copy the dump directory to config1 and repeat the mongorestore step.
Shutdown config2 (to keep the sharded cluster metadata readonly for the next step).

At this stage you should have:

config1 (running WiredTiger with user/role data)
config2 (stopped)
config3 (stopped)

Start config3. Copy the dump directory to config3, and repeat the mongorestore step.

At this stage you should have:

config1 (running WiredTiger with user/role data)
config2 (stopped)
config3 (running WiredTiger with user/role data)

Start config2. At this point all config servers should be online with the user information.
Re-enable the balancer so normal balancing activity & chunk migration can resume.


How to insert multiple values in a specific field in a table using mysqli? I am working on a project and I I have a scenario like this: I have many field in my table :

table_name : transaction_tbl
-id
-name
-description
-ref_number : text(datatype)

In my inserting here is my code:

 $sql = "INSERT INTO transaction_tbl (`name`,`description`,`ref_number`) VALUES ('$name','$desccription',$ref_number)";

if ($conn->query($sql) === false){
                    trigger_error('Wrong SQL: ' . $sql . 'Error: ' . $conn->error , E_USER_ERROR);
                }else {
                    echo "Successful ! Data is inserted in database ^__^" ;

                }

As the name itself ref_number or reference number, so there will be a time that I will have a lot of reference number,how can I let it insert if it will have multiple values? Thanks :)

UPDATES :

I want something like this :

name    description            ref_number
bag     to be use               10359435846
                               05438547656
                               035848576

No.  Don't put multiple values in a column; make a new table:

Mapping table
-id -- same as the `id` in transaction_tbl
-ref_number
PRIMARY KEY(id, ref_number)

For your example, there will be 3 rows in this table for the 1 row in transaction_tbl.

INSERT INTO transaction_tbl (name, descripton) VALUES (..., ...)
$id = LAST_INSERT_ID()
INSERT INTO mapping (id, ref_number) VALUE ($id, '10359435846')
INSERT INTO mapping (id, ref_number) VALUE ($id, '05438547656')
INSERT INTO mapping (id, ref_number) VALUE ($id, '035848576')

One way of getting the output:

SELECT t.id, t.name, t.description,
       GROUP_CONCAT(m.ref_number) AS ref_numbers
    FROM transaction_tbl t
    JOIN mapping m  ON m.id = t.id
    GROUP BY t.id;


Self-join example review in Books Online Here in this example it states that performs a self-join, but it joins Sales.SalesPerson to Sales.SalesTerritory. I knew that self join means joining the same table to itself but here aren't Sales.SalesPerson and Sales.SalesTerritory different tables? So is this considered self join though the tables are different? or is there a mistake?

source link

Looks like you are right in all observations and it is a mistake.

They are two separate tables (not views) and a self-join means a table joined to itself (and even a view or derived table or CTE joined to itself). You might even consider a join between a table and a view derived from that table to be a self-join but it is not the case here either.

I suggest you submit a bug report.

Link: list of AdventureWorks tables.


Is it possible to create a foreign key with a condition? I have a table site that has a field categoryid_root and a table category which has the fields id and parentid I want to create a relationship between:

site.categoryid_root and category.id but with the condition that category.parentid is null is this at all possible in MySQL?

It is not possible create a foreign key with a condition as you like. But you might use an intermediate table to simulate the condition. I call this table category_root. The category_root table will be updated only by triggers on category table. So you can create the foreign key between site table and category_root tables. No users must be have grants to update category_root to prevent inconsistencies. See diagram to see how the tables are related to each other.

create table category (
  id int auto_increment, parent_id int, name varchar(45),
  primary key (id),
  constraint fk_parent foreign key (parent_id) references category (id)
  on delete no action on update no action
) engine=innodb;

create table category_root (
  category_id int not null,
  primary key (category_id),
  constraint fk_category_id foreign key (category_id) references category (id)
  on delete cascade on update no action
) engine=innodb;

create table site (
  id int auto_increment, category_root_id int not null, name varchar(45),
  primary key (id),
  constraint fk_category_root foreign key (category_root_id) references category_root (category_id)
  on delete no action on update no action
) engine=innodb;

The trigger for insert is defined so when an new category without parent_id is inserted into category a new row is insert also into category_root table:

delimiter $$
create trigger t_category_ins after insert on category for each row
begin
  if NEW.parent_id is NULL then
    insert into category_root(category_id) values (NEW.id);
  end if;
end
$$
delimiter ;

The trigger for update is a little more complex:

delimiter $$
create trigger t_category_upd after update on category for each row
begin
  set @new_parent_id = NEW.parent_id;
  set @old_parent_id = OLD.parent_id;

  if (@new_parent_id is NULL) then
    if (@old_parent_id is not NULL) then
      insert into category_root(category_id) values (NEW.id);
    end if;
  else
    if @old_parent_id is NULL then
      delete from category_root where category_id = NEW.id;
    end if;
  end if;
end
$$
delimiter ;

Let try to insert some values:

insert into category(name) values('root1');
insert into category(name, parent_id) values('a', last_insert_id()), ('b', last_insert_id());
insert into site(category_root_id, name) values (1, "site1");

select * from category;
+----+-----------+-------+
| id | parent_id | name  |
+----+-----------+-------+
|  1 |      NULL | root1 |
|  2 |         1 | a     |
|  3 |         1 | b     |
+----+-----------+-------+

select * from category_root;
+-------------+
| category_id |
+-------------+
|           1 |
+-------------+

select * from site;
+----+------------------+-------+
| id | category_root_id | name  |
+----+------------------+-------+
|  1 |                1 | site1 |
+----+------------------+-------+

If we try to add a site with a category_id with parent_id not null we got an error:

insert into site(category_root_id, name) values (2, "site1");

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`test`.`site`, CONSTRAINT `fk_category_root` FOREIGN KEY (`category_root_id`)
REFERENCES `category_root` (`category_id`) ON DELETE NO ACTION ON UPDATE NO ACTION)


how will nvarchar(max) store data in database will it be fast if some data is less then 4000 characters? I have to develop a CMS which will support two Language English, Arabic. This CMS will be a sort of Article Publishing site. While designing & analysis i found that some articles are more than 8000 characters in length. My table has some column as

PageID int,
PageTitleEnglish nvarchar(200),
PageTitleArabic nvarchar(200),
PageDescEnglish nvarchar(500),
PageDescArabic nvarchar(500),
PageBodyEnglish nvarchar(max)
PageBodyArabic nvarchar(max)

If i keep PageBody as nvarchar(4000) then i a limited to 4000 characters and if i have to store Arabic version then i need 16000 bytes (As Arabic is Unicode and take 3 time more space then ASCII).

So i am only left with option of defining PageBody as nVarchar(max), This will have it downside from performance point of view. My actual question is if some data in PageBody column is less than 4000 characters will it MS SQL Store than data in inline column or separately in the database.

I looked for this on Google also but didn't find any relevant answer and how i can improve performance in such scenario.

Any suggestions for best practice for such design of multilingual CMS are welcome.

I need to Support Only two languages Arabic & English

An nvarchar(max) value will be stored "in-row" if it is short enough.

The default behaviour can be modified using sp_tableoption, "large value types out of row" option. I wouldn't bother. The DB engine will manage this efficiently by itself.

As for design, there are several ways of doing this based on your model:

Will you always have both English and Arabic?
Can one be optional? If so, will one always be mandatory?
Do you expect more languages later?

1. Separate tables

That is, you can split off the separate languages into different tables. This allows table level collations rather than column level ones

It allows allows more rows per page and more chance of in-row LOB storage

PageParent

PageID int,
PageOtherInfo...

PageEnglish (note varchar may be OK here)

PageID int,
PageTitleEnglish varchar(200),
PageDescEnglish varchar(500),
PageBodyEnglish varchar(max)

PageArabic

PageID int,
PageTitleArabic nvarchar(200),
PageDescArabic nvarchar(500),
PageBodyArabic nvarchar(max)

2. Separate rows

Or have a languageID column to support several languages.This has the drawback that collation will be fixed for all languages which means poor sorting/filtering

PageParent

PageID int,
PageOtherInfo..

Page

PageID int,
LanguageCode,
PageTitle nvarchar(200),
PageDesc nvarchar(500),
PageBody nvarchar(max)


Is index like key(timetime) usable when we query: SELECT * ....... ORDER BY timetime DESC When we index a timestamp field like timetime. Can we use it for ORDER BY timetime DESC or is it just usable for ORDER BY timetime ASC OR ORDER BY timetime? Does mySQL use this index when doing vise verse ordering?

Yes, it does. MySQL can use the index and use it for scanning/seeking in either direction. You can read in MySQL documentation: How MySQL Uses Indexes

  To sort or group a table if the sorting or grouping is done on a leftmost prefix of a usable key (for example, ORDER BY key_part1, key_part2). If all key parts are followed by DESC, the key is read in reverse order. See Section 8.13.9, “ORDER BY Optimization”, and Section 8.13.10, “GROUP BY Optimization”.


Can I tell SQL Server to "do something" when a connection is closed? Can I tell SQL Server to "do something" (e.g. release an application-level lock) when the connection is closed for whatever reason?

Background: I want to lock a record on the application level, not on the database level. However, I'm having trouble releasing the lock when the application crashes. Basically, I am implementing the following algorithm in my application:

function editRecord(recordId):
    begin transaction
    if (select lockedBy from myTable where id = recordId) is not empty:
        commit
        show "Sorry, record already in use by ..."
    else
        update myTable set lockedBy = current_user() where id = recordId
        commit
        show UI window to let user edit and update record
        update myTablet set lockedBy = empty where id = recordId

I'm trying to find out if there is an easy way to get rid of the application-level lock when the connection crashes.

SQL Server provides a built-in mechanism to support application locking. The locks supported are Shared, Update, IntentShared, IntentExclusive, and Exclusive. Deadlock detection is also built-in, though application lock deadlocks do not terminate the active transaction - you need to check the result code returned from the system procedure call. Books Online documentation:

APPLOCK_MODE
APPLOCK_TEST
sp_getapplock
sp_releaseapplock

Locks associated with the session are automatically released when the session is logged out.

Community Wiki answer generated from a question comment by @JonSeigel


Large discrepancy between information_schema database size vs mysqldump database size We currently have a database on our server that seems to be excessively large. We run the following query to see all the databases sizes on our server -

SELECT Round(Sum(data_length + index_length) / 1024 / 1024, 1) 'DB Size in MB' FROM information_schema.tables where table_schema like 'some_db' GROUP BY table_schema;

It returns the following -

+---------------+
| DB Size in MB |
+---------------+
| 23663.4       |
+---------------+

However, we also take nightly backups. Looking at this nightly backup for the specific database, it is only 6.5 GB, rather than the 23 GB that the query is reporting. Every other database that we are checking for appears to be roughly the same.

I saw this - https://stackoverflow.com/questions/4709521/mysql-database-size, however we arent using phpmyadmin to takle the backups.  The backups are done inside of a RHEL7 server running MariaDB, and are taken using mysqldump.

The command being run when taking the backup is -

mysqldump -uroot -p --single-transaction some_db > /tmp/somedb_backup.sql

A BIGINT takes 8 bytes (plus some overhead) on disk, but after going through mysqldump, a value of '1' will take only 2 bytes: INSERT ... VALUES(...,1,...).  That's a 4:1 shrinkage.  (Pro tip: 90+% of BIGINT columns don't need to be 'big'.)

On the other hand, a 1-byte TINYINT with 100 in it expands 1:4.

BLOBs tend to incur a 1:2 expansion, maybe more, due to escaping the bytes to encode in the dump.

INDEXes are not dumped.

"Free" space (and other fragmentation) is not dumped.  This might account for 2:1 shrinkage in the typical dump.

But the dump spells out the column names at least twice -- once for the CREATE TABLE, and at least once for the INSERTs.  On disk, the column names occur only once -- in the .frm file.  (That is, not included in the SELECT you did.)

And there are many other cases of expansion/shrinkages.

Since you are seeing 23 : 6.5 shrinkage, I would guess that you have some over-sized integer columns and a lot of indexes.  (Pro tip: It is folly to "index every column.")


How is every binary relation BCNF? So, as part of my assignment, I have to prove that any relation with two attributes is in BCNF.

As per my understanding, if for a relation we have 3rd normal form and one non key attribute functionally determine key attribute, it violates the BCNF.

Say my relation consists of two attributes A1,A2

Scenario1(only one functional dependency)

A1 -> A2 (so A1 is the key, and A2 does not FD A1 : so no violation)

same applies for

A2 -> A1

But what if

A1->A2 and A2->A1

Here key can be either A1, A2. And the other non key attribute functionally determines the key.

A relation is in BCNF when, for all FDs that apply to the relation, the left hand side is a superkey. That is, it has to contain all the attributes of at least one key. Your relation must have at least one candidate key (that is, you can't have repeating rows in the relation), else it wouldn't even meet the conditions for 1NF.

Given this, your relation can have zero, one or two FDs. The case for zero FDs is trivial (ab->ab). For one FD (a->b) the left hand side attribute is clearly the key.

In the case where you have FDs in both directions (a->b and b->a) you simply have two different keys, and so the left hand side of each FD remains a superkey.


Query the definition of a materialized view in Postgres I'm wondering how to query the definition of a materialized view in Postgres. For reference, what I hoped to do is very similar to what you can do for a regular view:

SELECT * FROM information_schema.views WHERE table_name = 'some_view';

which gives you the following columns:

table_catalog
table_schema
table_name
view_definition
check_option
is_updatable
is_insertable_into
is_trigger_updatable
is_trigger_deletable
is_trigger_insertable_into

Is this possible for materialized views?

From my research so far, it appears that materialized views are deliberately excluded from information_schema, because

  The information_schema can only show objects that exist in the SQL standard.

(http://www.postgresql.org/message-id/3794.1412980686@sss.pgh.pa.us)

Since they appear to being entirely excluded from information_schema, I'm not sure how to go about this, but what I'd like to do is twofold:

Query whether a particular materialized view exists. (So far the only way I've found to do this is try creating a mat view with the same name and see if it blows up.)
And then query the definition of the materialized view (similar to the view_definition column on information_schema.views).

Looks like 9.3 and up you can do:

select * from pg_matviews;
select * from pg_matviews where matviewname = 'view_name';

More info found here: https://stackoverflow.com/questions/29297296/postgres-see-query-used-to-create-materialized-view


Tablix cells width issue in SSRS I have a weird issue in the reports that I have created in SSRS 2008.

On the report I have got a number of tablixes defined, that should be displayed attached together vertically. Imagine they form a big table which each row (or set of rows) of this imaginary table has got different number of columns.

Using the alignment and width icons, I make these tablixes width the same. In design mode all these tablixes look nicely attached to each other with the same width, however when I run the report, the width of them appear unequal! I don't have any margin or padding set for them and can't understand why what I see in design mode is not consistent with what is displayed in run (review) mode!

Any idea?

Thank you.

As it looked ok in design mode and the issue was happening at run time, it would be helpful to investigate what elements displayed in the Tablix can grow or expand at run time.

I resolved this issue by following the steps below:

1- There was an image in one of the tablixe cells, I checked to see what is the selected option for displaying the image, if its size is bigger than the cell's size. The image had set to accept the growth, so it was increasing the width of the cell and consequently the Tablix size.

2- The padding for all cells of the row needs to be checked, especially it can be simply overlooked when an image has been assigned to a cell in the Tablix. If there is a padding, it would affect the cell size and eventually the row size in the Tablix.

I hope this description helps anyone else experience this issue.


SSAS Multidimentional Drillthrough with Many-to-Many Relationships I was browsing through the AdventureWorks DW 2012 sample, along with the accompanying SSAS cube sample, and noticed some interesting behavior related to drillthrough when a many-to-many relationship existed.

When looking at the Internet Sales Amount measure for a particular date (ie. July 1, 2005), and then performing a drillthrough to see the related lines, I'd notice that sums of the sales amount on the drillthrough lines didn't match the reported total sales amount. Looking at the base fact table in the warehouse, I found either lines were missing or sometimes were duplicated.

If I did a query combining the FactInternetSales table with the FactInternetSalesReason table, then I'd receive the same results that I got when looking at the drillthrough. Conversely, if I removed the Sales Reason dimension from the set of returned columns for the drillthrough, then I'd see "all" the rows and the totals would match up.

So I'm left with 2 questions:

Is there a way to have drillthrough show "unresolved" rows when a
many-to-many relationship exists, but the source row doesn't have a
matching attribute?
How do I remove duplicate rows in the drillthrough when a source row
has multiple matches in the many-to-many relationship?

If your drillthrough command includes a dimension attribute from a many-to-many dimension then it behaves as described: showing a row zero to many times.

The easiest solution is to create a drillthrough action and mark that action as the default drillthrough action and specify the columns you want to include in the drillthrough results. If you don't include the many-to-many dimensions then it will stop skipping or duplicating rows.


Does Hasura (GraphQL) support multiple databases? Hi I am setting up Hasura, the normal setup looks like this:

docker run -d --net=host \
-e HASURA_GRAPHQL_DATABASE_URL=postgres://username:password@hostname:port/dbname \
-e HASURA_GRAPHQL_ENABLE_CONSOLE=true \
hasura/graphql-engine:latest

I couldn't find in the docs a way to use multiple databases within the same instance, is it even possible?

Nope, It's not supported at the moment. You would have to create as many Hasura instances as the number of databases you have. Then use one as the main endpoint and add the others as remote schemas.


Creating a database with only MySQL command line? The linux server on which I'm attempting to create a MySQL database has many difficult to work with permissions. My supervisor has tried to install MySQL Workbench on the server, but has been unable to "build" the correct install (he knows much more than me about linux command line and the server we're using than I do, and we are running on a relatively short schedule). Thus, the only tool I have with which to create a MySQL relational database is the built-in MySQL Command Line.

Is it possible (practically speaking) to create a functioning
relational database using only SQL code in the command line, or will
I eventually need a tool such as MySQL Workbench?

If it is possible, could you please direct me to a good
resource/reference on how to do so?

I have minimal experience with MySQL, but am familiar with databases and am generally computer-savvy. Thanks.

Yes, most linux system administrators only use the command lines client (or other cli tools to work with the database). Maybe the only other thing you will need for design is a text editor (for writing easily before executing), like vi(m), nano or emacs, and pen and paper.

Here it is a first crash course:

Create a database:

CREATE DATABASE your_database_name;

Change the current database:

use your_database_name

Create your first table:

 CREATE TABLE your_table (
     id bigint unsigned PRIMARY KEY auto_increment,
     name varchar(50) NOT NULL,
     description text,
     modified_on timestamp
 ) ENGINE = InnoDB;

Populate your table:

 INSERT INTO your_table (name, description) VALUES ('pheidlauf', 'You are awesome');

Select your table:

 SELECT * FROM your_table;

Learn about MySQL SQL variant, in particular DDL commands, DCL and about using the command line client. There are many resources (I have just linked to wikibooks and the official MySQL documentation), but the command line client also has integrated help:

mysql> help create table

Another quick startup guide.


In [Percona] MySQL, is the Adaptive Hash Index included in the Total memory allocated count? I'm debugging a memory problem with MySQL 5.6 (I'm using the Percona distribution), but I can't figure out, searching around the web, if the Adaptive Hash Index amount is part of the Total memory allocated, or if it's separated.

Current output:

BUFFER POOL AND MEMORY
----------------------
Total memory allocated 109890764800; in additional pool allocated 0
Total memory allocated by read views 15776
Internal hash tables (constant factor + variable factor)
    Adaptive hash index 10365924496   (1699980328 + 8665944168)
    Page hash           13281976 (buffer pool 0 only)
    Dictionary cache    427147426   (424996592 + 2150834)
    File system         84040   (82672 + 1368)
    Lock system         295705056   (295674296 + 30760)
    Recovery system     0   (0 + 0)

Just going by the MySQL Documentation, I would say yes.

According to the MySQL 5.6 Documentation on innodb_adaptive_hash_index

  Disabling the adaptive hash index empties the hash table immediately. Normal operations can continue while the hash table is emptied, and executing queries that were using the hash table access the index B-trees directly instead. When the adaptive hash index is re-enabled, the hash table is populated again during normal operation.

If you really want to know for sure, login as root@localhost, run this

SET GLOBAL innodb_adaptive_hash_index = 'OFF';

and look at the memory stats again. They should be zero and memory counts should reflect that.


What is the best design for entities in order to represent dynamic organizations? I'm trying to design entities for dynamic organizations for future use-cases;

Pattern1

.
└── headquarters (the largest unit)
    └── division
        └── department
            └── personnel (the smallest unit)

Pattern2

.
└── headquarters (the largest unit)
    └── division
        └── branch
            └── personnel (the smallest unit)

Pattern3

.
└── headquarters
    └── division
        └── department
            └── foo
                └── bar
                    └── personnel

Question

What is the best design for entities in order to represent dynamic organizations?
I think some geniuses must have already designed such organization patterns with SQL, I'd like to know that the design pattern if it exists.

Assumption

There should be some units between the largest unit and the smallest unit

Numbers of that unit is variable

For example, I'd like to know a personnel's name or division

Thank you

There are two different approaches to achieve this.

Solution 1: The hierarchy is in the employees data

Add a reportsto field to your employees table

Each employee has an ID. This can be a unique identifier or an employee number that is given to each employee when she/he starts at the company. Each employee will have to report to another person (e.g. manager, team manager, ...) Here is some example data for such an EMPLOYEE table with a REPORTSTO column

Example EMPLOYEE table:

 UID   | REPORTSTO | NAME      | DEPT
-------+-----------+-----------+---------------
 UID1  |           | Smith     | CEO
 UID2  | UID1      | Vernon    | CFO
 UID10 | UID2      | White     | CFO1
 UID23 | UID2      | Rutzky    | IT1
 UID22 | UID2      | V.        | MN1
 UID6  | UID23     | M.        | IT12
 UID9  | UID23     | Nets      | IT22

Pros and Cons

The benefit of implementing this solution is that the departments and sub-departments are irrelevant. Everybody has to report to somebody. You can now query the hierarchy with a script to return a lovely tree view. The disadvantage is, that you have to change the reportsto each time somebody leaves the company.

Solution 2: The hierarchy is in a separate table.

Separate the hierarchy from the employees data

The employee has a link to a hierarchy/department table. The hierarchy is not stored together with the employee data, but in a separate table

Example of EMPLOYEE table:

 UID   | HIER_ID   | NAME      |
-------+-----------+-----------+---------------
 UID1  | 1         | Smith     |
 UID2  | 2         | Vernon    |
 UID10 | 7         | White     |
 UID23 | 14        | Rutzky    |
 UID22 | 17        | V.        |
 UID6  | 210       | M.        |
 UID9  | 213       | Nets      |

Example of HIERARCHY table:

 HIER_ID    | BELONGSTO    | DEPT_SHORT     | DEPT_DESC
------------+--------------+----------------+-------------------------
 1          |              | CEO            | Chief Executive Office
 2          | 1            | CFO            | Chief Financial Officer Office
 7          | 2            | CFO1           | Assistant CFO
 14         | 2            | IT1            | Head of IT
 17         | 2            | MN1            | Manufacturing Lead Manager
 210        | 14           | IT12           | Assistant Head of IT
 213        | 14           | IT22           | Database Administration

Pros and Cons

The benefit of implementing this solution is that the hierarchy is not linked to the employees, but rather the employee is linked to a hierarchy. Likewise you can have an infinite number of sub-departments / sub-structures and don't have to think about limitations. The disadvantage is if the structure changes inside the company (e.g. IT1 (14) doesn't belong to CFO (2) but reports directly to the CEO (1)), because it requires a separate user interface to modify the hierarchy.


Error creating server with pgadmin I´m using pgadmin and I´ve created a server, when trying to connect I get the following error:

Have you ever seen this problem?

Many thanks

It might be that pgAdmin is trying to connect to the database pruebas expecting it to be encoded as UTF-8. If you think of your database as a file, the client is trying to decode all the byte sequences in it using "the rules of UTF-8" as it were, it comes across the byte sequence 0xF3 which it cannot interpret and therefore fails.

This may be due to the fact that the encoding that you are using on the host computer (the computer where you created the database) is not UTF-8 and when you created the database pruebas you didn't explicitly specify which encoding to use and the creation process used your local setting (whatever it may be) as the default.

The documentation on the supported character sets (specific to version 9.6) says that:

  Each database's character set must be compatible with the database's LC_CTYPE (character classification) and LC_COLLATE (string sort order) locale settings. For C or POSIX locale, any character set is allowed, but for other locales there is only one character set that will work correctly. (On Windows, however, UTF-8 encoding can be used with any locale.)

I don't know the details of your OS, but on Linux, I can run the command:

locale

to see all the locale specific information which tells me that my LC_TYPE (which stands for character classification and case conversion) is en_GB.UTF-8 and I can also connect to my DB using the client psql and run:

SHOW server_encoding;

to see the encoding of my database, e.g.:

 server_encoding
-----------------
 UTF8
(1 row)

If the database pruebas is a small play database you created to play around with and you don't mind deleting it, you could drop it and create it again setting the encoding to UTF-8 and then see if you can connect to it.

I once didn't check the locale on the host (Linux) prior to initialising a database, went ahead and created the database only to find out that the process set up the database using the encoding on the host as default (which wasn't what I wanted). After digging around in the documentation, I found out that the only way to set the locale of the DB to what I wanted was to start all over.


Is a SQL query still called a "query" when doing a deletion, update or insertion? I'm not sure what to call a query that... isn't really a query; is "statement" a better word to use in place? Or how about "script"?

Or would something else be more appropriate?

Note: I've seen someone call it a "DELETE query" once

I typically call them DML statements or just statements. Query is commonly used for convenience.

After all, you're issuing a query against the database - if you say...

DELETE dbo.table WHERE id = 5;

...isn't at least part of that a query? What about...

INSERT dbo.table1(col) SELECT col FROM dbo.table2 WHERE ...

?

And no, I would not call them "scripts" - that implies something else altogether (usually a collection of statements, in a wide variety of languages, and commonly saved to a file).


PATH settings for Oracle database I have a laptop with Centos 7. I installed Oracle 12C database on it.The oracle user is oracle and the sid is orcl.
How can a user that is not oracle user run the sqlplus program directly ?

The .bash_profile for the oracle user is

if [ -f ~/.bashrc ] ; then
    . ~/.bashrc
fi

export TMP
export TMPDIR=$TMP
export ORACLE_HOSTNAME=servera.localdomain
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1
export ORACLE_SID=orcl
export TNS_ADMIN=$ORACLE_HOME/network/admin

export PATH=$PATH:$HOME/. local/bin:$HOME/bin
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/lib64
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

question: Do I need to have these variable exports for every user in their .bash_profile file ?

This is the minimum:

export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=orcl

Or use oraenv. Add the below in /etc/oratab if it is not already there:

orcl:/u01/app/oracle/product/12.1.0/db_1:N

Then interactively:

. oraenv
ORACLE_SID = [oracle] ? orcl
The Oracle base has been set to /u01/app/oracle

Or from a script:

export PATH=/usr/local/bin:$PATH # default path for oraenv, in case your PATH does not include it
export ORAENV_ASK=NO
export ORACLE_SID=orcl
. oraenv

It is not mandatory to set these in the profile, users can set themselves it when needed.


MySQL - Boolean Full-text Search I'm using MySQL's boolean full-text search for my search engine: https://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html.

Assume I have a table with the following columns:

PRODUCTS
--------
id (PK)
color_id (FK to COLORS table)
name
description

When given a search term, I want to search the product's table's name and  description columns, as well as the product's color name. The color's name is in a separate table.

Should I create a new column in the product's table and put all the text I want indexed in there? For example, I add a products.full_text_index column, and dump a concatenation of name, description, and the color's name in that column? Then do full-text search against that column?

UPDATE: Or, instead of a column in an existing table, maybe create a new table to contain all the text that should be used for the full-text search. That table can just have a FK to the products table.

Yes, that is a good way to use FULLTEXT, especially if you need to 'cleanse' the data in any way, or gather text from related tables.

There is probably not much difference between having the new column in the same table or in a separate table.

The redundancy is wasteful.  However, there is no way to have any kind (FULLTEXT or other) of index that spans multiple tables.  Might it be better to have all the columns in the same table, and do FULLTEXT(this, that, the_other)?

And the redundancy is potentially error-prone.  This could be tempered by having a Stored Procedure that users must call when inserting or manipulating any of the text involved in the issue.  This SP would always make the copy for you.

When using a FT index with other things, such as

WHERE price > 50
  AND MATCH(stuff) AGAINST(...)

the FT test will be done first.  Hopefully, that will select only a small number of rows.  Then the other tests will be applied to further filter the results.  When the situation is reversed (price is more selective than MATCH), well, too bad.

If you always have the MATCH, there is no need for indexing price, the index won't be used.


Mongo DB Can't Start We have had a mongodb running fine for a few weeks and all of sudden it has gone down.

We are unable to start it back up as it is complaining that the dbpath (/data/db/) does not exist. I've tried routing through the documentation and have seen others suggest using the dbpath flag, however still no luck.

We are starting it with sudo service mongodb start and the config file dbpath config points to a directory with our db in.

Any advice would be greatly appreciated.

Since you are running out of space for the journal files, here is what the docs says:
journal

journal Default: (on 64-bit systems) true
Default: (on 32-bit systems) false
Set to true to enable operation journaling to ensure write durability
and data consistency.
Set to false to prevent the overhead of journaling in situations where
durability is not required. To reduce the impact of the journaling on
disk usage, you can leave journal enabled, and set smallfiles to true
to reduce the size of the data and journal files.
Note You must use nojournal to disable journaling on 64-bit systems.

journalCommitInterval

Default: 100 or 30
Set this value to specify the maximum amount of time for mongod to
allow between journal operations. Lower values increase the durability
of the journal, at the possible expense of disk performance.
The default journal commit interval is 100 milliseconds if a single
block device (e.g. physical volume, RAID device, or LVM volume)
contains both the journal and the data files.
If different block devices provide the journal and data files the
default journal commit interval is 30 milliseconds.
This option accepts values between 2 and 300 milliseconds.
To force mongod to commit to the journal more frequently, you can
specify j:true. When a write operation with j:true is pending, mongod
will reduce journalCommitInterval to a third of the set value.

For more clarification, please read Recover MongoDB Data following Unexpected Shutdown. You may need to run validate() on your data collections to verify integrity.
You should also read about Journaling
SUGGESTIONS

Looking back at this phrase If different block devices provide the journal and data files the default journal commit interval is 30 milliseconds.
You should make store the MongoDB journals on a different disk from the Data.
You may also want to shrink the journaling files if you haven't already done so.
You should see if the journal files are RAM-based. If they are and if you ran out of memory, that may explain your crash.
See Documentation on --smallfiles


Is there anyway to find the closest matching JSON entry inside a database Scenario: suppose I have a list of numbers written in something like JSON stored in a database field. Something like this:

ids{1,2,3}

Let's suppose the database is filled with rows that each have a an IDs JSON field like this

entry1: someUniqueID, ids{1,2,4}

entry2: someUniqueID, ids{1,2,5}

entry3: someUniqueID, ids{1,2,3}

Is it possible with any type of database (sql, nosql, graph etc.) to efficiently structure the database for finding the entry with the closest match? As in
looking for the field ids inside a database that most closely resembles

ids{1,2,3}

entry1 has 2 numbers inside that match which are 1 and 2. therefore 66,6% match

entry2 has 2 numbers inside that match which are 1 and 2. therefore 66,6% match

entry3 has 3 numbers inside that match which are 1,2 and 3. therefore 100% match

the Query would return entry3

I know it's of course possible to read each row and compare the values using code and find the closest match. Of course if you have a large database with many thousands or even millions of rows each holding the ids field. Getting result would take ages.

Is there any database technology that would allow for fast, sub 1 seconds result of this kind, even if ids holds much more than 3 values.

By the way the values don't have to be stored using JSON.  Any Direction to for me to look into would be appreciated.

Just a rough sketch on how you could do that with Postgres arrays.

There are two array operators that you might be interested in. The "overlaps" operator that returns true if some of the elements are the same and the "contains" operator that checks if all elements of one array are contained in the other - that would be your 100% match:

Assuming this table:

create table idlist (ids int[]);

You can create an index on that column:

create index on idlist using gin (ids);

A query to find the 100% match looks like this:

select *
from idlist
where ids @> array[1,2,3,4,5,6]

That will return only rows where ids contains all the values 1,2,3,4,5,6 (but it might contain more).

On a table with 2 million rows and arrays from 5 to 100 elements this takes about 20ms on a pretty old test server (the actual runtime depends on the total amount of matches found)

The query to find rows that contain at least one of the values of the parameter uses the && operator and is substantially slower.

select *
from idlist
where ids && array[1,2,3,4,5,6];

That takes about 300ms on my test server. But it also returns 500.000 rows! If the condition only returns very few rows (less than thousand) then obviously this is going to be a lot faster.

How you proceed from there is a matter on how restrictive the conditions are that you use. If the && operator only returns a few rows (not half a million) then maybe processing them in your application might be more efficient.

Getting the information if the input parameter is completely contained, could be done like this:

select ids,
       ids @> array[1,2,3,4,5,6] as full_match
from idlist
where ids && array[1,2,3,4,5,6];

Another option would be to write your own function that calculates the match, or maybe use the intarray extension to efficiently calculate the intersection between two arrays.


Why can't a differential backup specify its base? This is my first DBA.SE post, so please inform me of any mistakes, thanks!
I am a new DBA (not an IT pro, just no one else in the company to do it), so the more basic the explanation the better. I have been reading about database backup strategies (or, as I have learned to call them, "restore strategies").  I understand what Full, Differential, and Transaction Log backups do, but I want to know why a differential backup can only be based on the most recent full backup.
If a differential backup is everything that has changed since the last full backup, then why can't the differential be based off of any backup of my choosing?  To be more clear, I'm asking about specifying the base when the backup is taken, not when restoring. I am assuming that when restoring you would choose the correct base and corresponding differential to perform the restore (not using a differential made from base B to restore from base A).
What is the reason that prevents this functionality from being possible?  I figure that there must be a reason, I just don't know what it is.
Note: I understand that the base cannot be specified, but my question is why not?  (I'm also not interested in discussion about "why would you?")
Analogy
Here's an analogy for how I understand a differential backup:
I have an Excel file with some data in cells.
On day 1, I make a copy of this file and store it somewhere else (the "full backup").
On day 2, I look at the file and compare it to the backup copy that I made on day 1, and I note all the cells that have changed and what their new values are (a "differential backup").  I am not noting every change made to a cell, only what its final value is. If cell A1 started as "Alfred", changed to "Betty", "Charlie", then "Dave", I would only note that "A1 is now Dave".
On day 3, I compare the current file with the backup file again and note the changes (another "differential backup" with the same base as day 2).  Again, only noting final values per cell at the time observed, not all values that the cell has been throughout the day.
On day 4, I compare again and note changes again. Continuing with cell A1, now it says "Sarah", even if it was 10 other names throughout the day, and all I note is "Now A1 is Sarah".
On day 5, my file gets messed up; so, I look at the backup copy that I made on day 1, then the final states noted on day 4, and I apply the changes noted to the backup copy and now I have the file "restored" to how it was on day 4.  So, I look at the backup made on day 1, see that on day 4 cell A1 ended as "Sarah", and change the backup cell A1 to be "Sarah".
Why would it matter if I had made another backup copy ("full") of the file on day 2?  Why wouldn't it still be possible to compare (read, "take a differential backup of") the file on day 3 or 4 with the copy made on day 1?  As I understand it, SQL Server would require me to compare (when taking another differential backup) to a full backup made on day 2 (if one had been made)- no other option.

A differential backup uses what is called the differential change map to build a list of pages that have been modified since the last full backup.  This list is a "differential" list, hence the name of the backup type, and the reason the backup can only ever be restored over top of the associated full backup.

Performing a full backup resets the differential change map.  From that point forward, any page that is modified is recorded in the map.  If you then take a differential, that backup only contains pages that have been modified since the last full backup, and recorded in the map.

In your analogy, the two full backups, which serve as a base for the entire restore process would likely have different contents, and therefore different differential maps.  If you restore a diff based on the first backup over the 2nd backup, the database would likely be corrupted.  In fact, SQL Server prevents the restore of a diff backup over anything except the original full backup it is based upon.

When you ask SQL Server to take a differential backup, the only "base" for the differential is the single differential change map present in the database at the time the differential backup starts.  This is why you cannot specify the base for the differential backup.

In response to a comment from @MartinSmith - you might be able to use COPY_ONLY backups to restore a differential backup over a number of full backups.  Consider the following scenario:

BACKUP DATABASE xyz TO DISK = 'path_to_backup.bak';
BACKUP DATABASE xyz TO DISK = 'path_to_backup_2.bak' WITH COPY_ONLY;
BACKUP DATABASE xyz TO DISK = 'path_to_backup_3.bak' WITH COPY_ONLY;
BACKUP DATABASE xyz TO DISK = 'path_to_backup_4.bak' WITH COPY_ONLY;
BACKUP DATABASE xyz TO DISK = 'path_to_backup_diff.bak' WITH DIFFERENTIAL;

The differential backup in step 5 should be capable of being restored over any of the backups taken in steps 1 to 4, since the differential change map is only cleared when the full backup in step 1 occurs.  The COPY_ONLY backups in steps 2, 3, and 4, do not reset the change map. Since the differential change map accumulates changes made since the full backup, each of the successive COPY_ONLY backups contains enough information for the differential backup to work against any of the previous 4 backups.

Although it seems like it should work, in practice, restoring a differential over top of a copy_only backup results in the following error:

  Msg 3136, Level 16, State 1, Line 1
  This differential backup cannot be restored because the database has not been restored to the correct earlier state.
  Msg 3013, Level 16, State 1, Line 1
  RESTORE DATABASE is terminating abnormally.

I've created a SQL Server 2012 platform repro for testing differential and copy_only restores, and saved the file on gist.github.com - WARNING the script will drop any database named RestoreTest as its first step.


PostgreSQL WHERE clause with two OR'ed conditions, priority to first I'm storing user data (username, email, password hash) in PostgreSQL, and I can only enforce uniqueness on username. I'd like to let users provide either their username or their email when they login. However, when matching their input against usernames and email addresses I want to ensure that a username match gets priority over an email match.

For example, in the following contrived instance:

id | username        | email address    | pw_hash
-------------------------------------------------
1    john_doe          john@domain.com    x1j34
2    john@domain.com   john@domain.com    x1j34

if both users enter their username, I want to guarantee that user 2 gets logged in.

If I use:

SELECT * FROM member WHERE (username='john@domain.com' OR email='john@domain.com') AND pw_hash='x1j34';

this matches both users but provide no guarantee on order.

Is there a way to have to conditions, and only check the second condition if the first isn't satisfied? Can this be done in a single query?

You have two conditions to check during the authentication:

There is a user with matching username; do not need to match by email.
There are no users with matching usernames; need to match by email.

So the query is:

WITH matching_username AS (SELECT * FROM member WHERE username = 'john@domain.com')
SELECT * FROM matching_username WHERE (SELECT COUNT(*) FROM matching_username) = 1
                                   AND pw_hash = 'x1j34'
UNION
SELECT * FROM member            WHERE (SELECT COUNT(*) FROM matching_username) = 0
                                   AND email = 'john@domain.com'
                                   AND pw_hash='x1j34';

On the other hand, there is a problem with your design of the table.

What if two users have have the same email, but neither of them have email as their username? If one of them tries to authenticate with an email, you will not be able to tell, which user record to authenticate against.


ON CONFLICT ON CONSTRAINT fail postgresql INSERT INTO O_USER (ID, NAME, LASTNAME, EMAIL, USERNAME, PASSWD, DATE_REG, DATE_UPD, DATE_DEL, IS_DELETE, TIPO, FTP)
VALUES (%s,%s,%s,%s,%s,%s,DEFAULT,NULL,NULL,%s,%s,%s)
ON CONFLICT ON CONSTRAINT ux DO NOTHING;

ux constraint:

CREATE UNIQUE INDEX ux ON public.o_user USING btree (email,username);

but in multiple insert have this error:

psycopg2.IntegrityError: duplicate key value violates unique constraint "o_user_username_key"
DETAIL:  Key (username)=(jferraro) already exists.

what is the problem?

update add table information:

CREATE TABLE O_USER (
    ID          INTEGER         PRIMARY KEY,
    NAME        VARCHAR(30)     NOT NULL,
    LASTNAME    VARCHAR(30)     NOT NULL,
    EMAIL       VARCHAR(50)     NOT NULL UNIQUE,
    USERNAME    VARCHAR(30)     NOT NULL UNIQUE,
    PASSWD      VARCHAR(30)     NOT NULL,
    DATE_REG    DATE            DEFAULT NOW(),
    DATE_UPD    DATE,
    DATE_DEL    DATE,
    IS_DELETE   NUMERIC         NOT NULL,
    TIPO        VARCHAR(10)     NOT NULL,
    FTP         VARCHAR(30)     NOT NULL,
    CONSTRAINT  DEL             CHECK       (IS_DELETE IN ('0','1')),
    CONSTRAINT  FK_FTP          FOREIGN KEY (FTP) REFERENCES FTP_USERS(FTP_USER),
    CONSTRAINT  UX              UNIQUE      (EMAIL,USERNAME),
    CONSTRAINT  TYP             CHECK       (TIPO IN ('ADMIN','STANDARD'))
);

update resolve the problem:
the problem was that i declared two different mode for make unique email and username, for resolve this problem i delete unique near the collums and use only unique constraint "UX" for email and username.
thanks all for the support.

This:

EMAIL       VARCHAR(50)     NOT NULL UNIQUE,
USERNAME    VARCHAR(30)     NOT NULL UNIQUE,

makes both EMAIL and USERNAME individually unique, in addition to your other constraint. If only a combination of EMAIL and USERNAME is to be unique, remove UNIQUE from the column definitions.