If two entities are engaged in a many-to-many relationship with both entities participating totally, how many tables are needed? I was thinking three tables, one for entity A, second for entity B and third for the relationship and describe the total participation in the third table by importing primary keys from first and second table and terming them as not NULL.
Am I on right track?

Consider a relation between students and courses, each student must be associated with at least one course (can be more than one too) and each course must be taken by at least one student (can be taken by more than one students too). This would result in a many to many relationship with total participation from both sides.

CREATE TABLE student ( sid INTEGER, age INTEGER, PRIMARY KEY (sid) )
CREATE TABLE COURSE ( cid INTEGER, timings DATETIME, PRIMARY KEY (cid) )
CREATE TABLE TAKES ( sid INTEGER, cid INTEGER, FOREIGN KEY (sid),
    FOREIGN KEY (cid), instructor CHAR(12), PRIMARY KEY (sid,cid) )

Using a third table to hold links between two independent tables is the correct method when designing a simple many-to-many relationship. You don't specifically need flag the fields as NOT NULL because they form the Primary Key and so cannot be NULL anyway.

One note, however, you've stated in your example that a Student record MUST be associated with at least one Course record, and vice versa. Your table design would not enforce this requirement, it would only allow the possibility that Student and Course records could be linked - not that they must have at least one linked record.

Your example would not enforce total participation on both sides, and I am not sure that you even could enforce it. It raises the question of which comes first, the Student or Course record given that each of those record types requires at least one link to the other record type.

Without a Student record, no Course could be created because it doesn't have a linked record, and vice versa - it's the classic "which came first" chicken or egg scenario!


Why does SSDT deploy inline FK Constraint as WITH NOCHECK? Given the following Foreign Key Constraint definition inline of a Create Table definition in a SSDT project:

CREATE TABLE A
(...Columns...),
CONSTRAINT [FK_O] FOREIGN KEY ([OID]) REFERENCES [dbo].[O] ([ID]) ON DELETE CASCADE NOT FOR REPLICATION,
(...)

and the following quote from MSDN

  WITH CHECK | WITH NOCHECK
  Specifies whether the data in the table is or is not validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint. If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK is assumed for re-enabled constraints.

, I wonder why the FIRST deployment result of this NEW table does look like that using a constraint WITH NOCHECK option as follows:

ALTER TABLE [dbo].[A]  WITH NOCHECK ADD CONSTRAINT [FK_O] FOREIGN KEY([OID])
REFERENCES [dbo].[O] ([ID])
ON DELETE CASCADE
NOT FOR REPLICATION
GO

ALTER TABLE [dbo].[A] CHECK CONSTRAINT [FK_O]
GO

So the question is, shouldn't the inline Constraint definition that does not allow to define "with Nocheck" paired with the rule from the MSDB page result into a constraint using the WITH CHECK option on this NEW table? Why do we get a WITH NOCHECK Constraint here?

There is only one kind of foreign key constraint (though it can be trusted, or untrusted).

If it was a new table, both code paths are essentially the same. But they do it this way because SSDT is taking into account it might need to tweak the DDL to add to an existing table.

If the table existed and it altered the table with check, the deployment will fail and will not put a check constraint on the table.
If the table existed and it altered the table with nocheck, leaving the check for afterwards, you'll get an error if old data doesn't meet the criteria, but at least the schema change will be in place.

What they've done is generate a consistent set of code that covers both non-existing and existing possibilities. They haven't tried to optimise for every use case.

Essentially it has no further inherent meaning. It's just an artefact of how they output the code.


convert date in string format to date datatype sql I have values in a column named Date as a nvarachar data type in the form of mmddyy and want to convert the values to a date datatype in the form of yyyy-mm-dd. What sql colde can I use to convert the value?
example 02121955 -> 1955-02-12

You can use SUBSTRING to pull apart the source. Then DATEFROMPARTS will stitch them back together.
A few notes: dates are not held internally in that format, or any other date-like format. The question says the source is 6 characters but the example shows 8. If you need to find the century you'll have to decide on rules for that.


Non-Clustered-Index on a temporary table I am working on various query optimization techniques. I reduced the query execution time from 1 minute to 12 seconds just by adding a Non-Clustered-Index on a table including one column (which is used in multiple where conditions) but DBA is very picky about adding indexes.

I would like to know if it makes any difference by adding NCI on a temp table instead of actual table. If YES, how? If NO, why?

Temporary tables comply to the same rules as permanent tables when it comes down to indexing. The only difference is in the storage location, which is Tempdb for temporary tables. However, if you are adding an index to a table that is heavily written, you have to take into account the write vs. read tradeoff.

Since the temporary table is probably used in a procedure or in a script, it's your code that controls how hard you're hitting the table with writes.
INSERTs are faster without indexes in place: if you're inserting lots of data in multiple statements, you probably want to create the index after fully populating the table.
UPDATEs and DELETEs have to find the row(s) to modify first, so they could highly benefit from proper indexing.

If your DBA wants to pay (a lot) more reads + CPU + elapsed time vs. some writes, I think (s)he should clarify his point.

Long story short, if your code runs faster with a NCI, go on and add it.


varbinary to varchar or nvarchar When retrieving a text value, which has been stored in a varbinary field, and converting back to text, is there a way to determine whether it was stored as a VARCHAR or NVARCHAR string?  If it is unknown, telling the CONVERT function it is a NVARCHAR string, when it is in fact VARCHAR will return gibberish.  Run the below for an example:

IF OBJECT_ID(N'dbo.Temp') IS NOT NULL
  DROP TABLE dbo.Temp;

CREATE TABLE Temp(
   ID INT IDENTITY(0,1) NOT NULL
  ,Test VARBINARY(50) NULL
);

INSERT INTO dbo.Temp (Test) VALUES( CONVERT(VARBINARY(50), 'varchar test string'));
INSERT INTO dbo.Temp (Test) VALUES( CONVERT(VARBINARY(50), N'nvarchar test string'));

SELECT CONVERT(VARCHAR, Test) AS [VARCHAR], CONVERT(NVARCHAR, Test) AS [NVARCHAR]
FROM dbo.Temp;

This isn't really possible in general.

The full range from 0x00 to 0xFF is valid for varchar and from 0x0000 to 0xFFFF for nvarchar so it is impossible to distinguish between two bytes that represent 2 varchar characters or a single nvarchar character.

0x4141 could be 'AA' or N'䅁'

If DATALENGTH is odd then you know it is varchar though.

Also if the text is primarily standard ASCII as in your example then it will contain a lot of 0x00 when stored as binary if nvarchar.

This is probably not a character you expect to reasonably find at all in varchar strings so the presence of that could be taken as evidence that the string is nvarchar.


Set Parameter in SPFILE still outputs the same error for the installation of a SAP IDM system, I need to know how to use the command alter system set "_ORACLE_SCRIPT"=true SCOPE=SPFILE; command correctly.
The installation creates a user and gives the error ORA-65096: "invalid common user or role name". During the researches I found the command alter session set "_ORACLE_SCRIPT"=true. This command allows to create users temporarly.
At the next session or in a separate terminal, this is no longer possible.
In the $ORACLE_HOME/dbs/spfile.ora the parameter *_oracle_script=true was in there, but it gives me the same error again.
Thank you in advance.
-Sruscht

Digging further: Your error indicates that you are using a container/pluggable database (CDB/PDB) architecture for your Oracle Database. The SAP IDM installation guide specifically says not to do this:

When installing the Identity Management database on Oracle, note the following prerequisites and recommendations:...
...make sure the Create as Container database option is not selected

https://help.sap.com/viewer/d77277f42c0b469db8794645abd954ea/8.0/en-US/571119cc37ce465b93de726fc0408427.html
Based on this, you will most likely need to recreate your database from scratch and make sure it is configured as a traditional instance, and not as container/pluggable database architecture.


Fix transaction log file after database update I have a databases that are regularly (monthly) updated. An update includes a lot of new or changed T-SQP procedures, functions, indexes creation, etc.

A database X has transaction log file with the following settings:

unlimited growth
initial size 128 MB
growth with 128 MB

with space used - maximum 5-10% of it (a backup of the transaction log file is made every 15 minutes).

So, the above settings are specific for each database but in common, the log file size is enough and not growing. After UPDATE is made, the size of the transaction log file is increased with 1 or 2 or 3 GB. Since I do not need such big file for regular use of the database, I am shrinking them back like this:

DBCC SHRINKFILE(2,TRUNCATEONLY)
ALTER DATABASE X MODIFY FILE (NAME = N'x_Log', SIZE = 128MB)

I want to execute the code above after each update, but sometimes I need to execute the command above many times in order to reduce the database tlog file (each time I have to wait 15 minutes for the tlog backup to complete).

The database are in FULL recovery mode with Always ON, end the tlog backups are made on the secondary databases.

Could anyone tell if the idea for regular shrink is bad and why sometimes I need to executed the shrink several times?

Short answer:

Just leave the log file as big as it typically needs to be, and stop worrying about it.

Longer answer:

First, why do you want to keep shrinking the file? If it's just going to grow again (and keep in mind that shrink and grow operations are expensive, especially for the log), then what did you gain? What did you use all that freed up space for in the meantime? Please read this page, in full, this article, and this blog post, and then come back with further questions. You're not seeing 8 million people on the Internet tell you not to shrink for no reason: it's a really bad and pointless exercise in almost all cases. There are exceptions, but this does not sound like one of them.

  Sometimes I need to execute the shrink multiple times

I've addressed the primary reason for this in this answer. Essentially, SQL Server has to have two checkpoints or two log backups in order to wrap around to the reusable portion of the log.  (This has to do with the number and arrangement of the VLFs in the log file, and where the current LSN points. We could post a book here but the easy answer is to just do what you're doing or run an additional log backup before starting.)

Another possibility is that the log can't be reused for a variety of reasons. Next time you try to shrink the file and it doesn't shrink on first try, check sys.databases.log_reuse_wait_desc. For example, if you're also using replication on this database, this can be caused by that activity, an un-replicated transaction, etc.

But usually it's just the wrap-around thing. So next time, issue two manual log backups - I don't see why you need to wait for the 15 minute interval (unless additional, smaller log backups in between will throw off any manual processes you have around your AGs). Or (as per above) just stop performing this pointless work - especially since that work is done over and over on every replica. Once again, this is useless effort you're putting in with the net result of: you freed up a little disk space for a little while.


Upgrade SQL Server 2005 to SQL Server 2012 I have a test environment set up with the following:

Virtual Machine (Hyper-V)
Windows Server 2008 R2 SP1(x64-bit)
Windows SQL Server 2005 Developer Edition SP4 (x64-bit)(default instance name)

1 Database with Merge Replication set up... 3 publications with 2 subscribers each.

We are testing the procedure for upgrading to SQL Server 2012 Developer Edition (sp1)...

I have downloaded the iso for 64bit SQL Server 2012 Dev. (sp1) from MSDN (full licensed copy) and have started going down the upgrade path. At one of the final stages, where it begins a check for different criteria, we run into a bizarre issue as outlined below:

Rule "Upgrade architecture mismatch" failed. The CPU architectures of upgrading feature(s) are different. To upgrade these features, Setup architecture must be the same as the features being installed.

Furthermore, in the upgrade log file, I found the following (more verbose) output:

(03) 2012-12-28 14:02:45 Slp: Init rule target object: Microsoft.SqlServer.Configuration.SetupExtension.BlockMixedArchitecture
(03) 2012-12-28 14:02:45 Slp: Rule 'BlockMixedArchitectureUpgrade' detection result: IsMixedArchitectureInstall= True
(03) 2012-12-28 14:02:45 Slp: Evaluating rule        : BlockMixedArchitectureUpgrade
(03) 2012-12-28 14:02:45 Slp: Rule running on machine: REPLICATIONTEST
(03) 2012-12-28 14:02:45 Slp: Rule evaluation done   : Failed
(03) 2012-12-28 14:02:45 Slp: Rule evaluation message: The CPU architectures of upgrading feature(s) and this installation program are different. To upgrade these features, Setup architecture must be the same as the features being installed.

I have made sure that we are on an x64 system and not an ia64 and I see no reason why a mismatch would be occurring. Something to note: We cannot simply install SQL Server 2012, and reattach the databases to it as we have over 20 live databases (in production) with over 40 publications set up... this would be an absolute nightmare if we had to re-set up replication.

Here is the version info for the SQL Server 2005 setup:

Microsoft SQL Server 2005 - 9.00.5000.00 (X64)
Dec 10 2010 10:38:40
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

9.00.5000.00 indicated SP4.

I will be calling Microsoft support about this tomorrow.

Could either tools (SSMS etc) or SSIS have been installed separately to the main install as 32bit?


Postgresql - Hot standby vs Streaming I'm trying to understand the various PostgreSQL replication technologies. But still I am unable to understand difference between Hot Standby and Streaming Replication.

In this Blog the Hot Standby and Streaming replication is same.  Can anyone help me understand this?

The blog post is using the terms incorrectly.

Those are two different things.

"Hot Standby" is the state the replica is in and means it is available for read-only queries. A replica can also "just" be a standby, but then it does not allow queries, it just sits there and waits to take over.

A replica needs to have exactly the same data as the master and the way the data is sent from the master to the replica is what "streaming replication" refers to.

A hot standby can also be setup using log shipping (sometimes also referred to as "continuous WAL archiving") or even streaming replication together with log shipping.

Quote from the manual

  In standby mode, the server continuously applies WAL received from the master server. The standby server can read WAL from a WAL archive or directly from the master over a TCP connection (streaming replication).


insert with multiple row return through select I want to insert multiple rows return from select statement with different uid,
lets select statement return (1,2,4,5);

INSERT INTO `notifications`( `Text`, `uid`, `read_status`)
VALUES ("abc",(select uid from users where status=1),0)

How can I insert this from one insert query?

Output

 Text,    uid,    read_status
 abc,     1,    0
 abc,     2,    0
 abc,     4,    0
 abc,     5,    0

Almost. You need to remove the values clause and just put the select straight after the table and column names of the insert clause:

INSERT INTO notifications(text, uid, read_status)
SELECT "abc", uid, 0 FROM users WHERE status = 1;

Then the output of the select will go to the insert.


Failover Cluster, Availability Group, and Disaster Recovery We have an active/passive Windows Failover Cluster setup as an HA solution. Here is an example of what that might look like:

Cluster1: [Node1 'active', Node2 'passive']

We're looking to set up a DR solution by making use of Availability Groups in SQL Server 2016. To do this, we plan to have the following setup:

Cluster1: PRIMARY[Node1 'active', Node2 'passive'], SECONDARY[Node3 'standalone']

Node1 and Node2 can have resources failover to each other. They even share a storage. Node3 will be part of the same Windows Failover Cluster because it has to be in order for High Availability to work. However, Node3 will NOT share resources with Node1/Node2. Node3 will host the secondary replica, the primary replica will be on the instance that is shared between Node1 and Node2.

Is our understanding that all nodes need to be on the same Windows Failover Cluster for Availability Groups to work correct? We read it here but aren't fully sure of what's being said.
Is it possible to set up HA in this manner? Where Node1/Node2 share resources but Node3 has a separate instance which cannot ever be a part of Node1/Node2.

Is our understanding that all nodes need to be on the same Windows Failover Cluster for Availability Groups to work correct? We read it here but aren't fully sure of what's being said.

Yes this is correct. Although starting from SQL Server 2016 you have Distributed Availability Groups in which replicas can reside in different WSFC

  Is it possible to set up HA in this manner? Where Node1/Node2 share resources but Node3 has a separate instance which cannot ever be a part of Node1/Node2.

Yes this is very much possible and I have this in my environment. Please note that in this case you would not be able to leverage automatic failover of replicas from SQL Server FCI to standalone node 3. You would have to reply on manual failover. This is because the AG replica resides on FCI which has its own failover.


What should I index when joining on a table with conditions? I have a query like this:

SELECT "main_table".*
FROM "main_table"
INNER JOIN "other_table" ON "other_table"."deleted_at" IS NULL
AND "other_table"."id" = "main_table"."appointment_id"
WHERE "main_table"."user_id" = 1
  AND (other_table.date BETWEEN '2020-02-26 23:29:42.678693' AND '2020-02-27 01:29:42.678739')

You probably know everything you need to about the schema from the query, but let me know any questions.

What are the optimal indexes to make for this?

main_table is easy (right?)

CREATE INDEX ON main_table (user_id);

For other_table, I'm tempted to think "first postgres will 'do the join', then it will 'filter by date'". Which suggests this index:

CREATE INDEX ON other_table (id, date) where deleted_at IS NULL;

But, without creating the indexes and running EXPLAIN, is it unknowable how postgres will go about doing the query, and it might think that it's better to filter by date first, then 'do the join'?

this select will typically match 1–10 rows
for a given date range, there are thousands of rows in other_table
for a given user_id there are thousands of rows in main_table
the results of this query will never have two main_table rows correlating with one other_table row, or vice versa. It's always 1-1.
other_table has about 2/3 of its rows with deleted_at not null

Given your information about the distribution of the data, the following execution plan will be the best:

Use an index scan to get the 1 to 10 rows from other table.
Perform a nested loop join with main_table as inner table, which will be fast if the join condition is indexed.

So the ideal index on other_table would be

CREATE INDEX ON other_table (date) WHERE deleted_at IS NULL;

If deleted_at is usually NULL, you can omit the WHERE clause without losing much.

If you want to get an index only scan on other_table (which probably isn't necessary, since you only fetch very few rows), you could instead

CREATE INDEX ON other_table (date) INCLUDING (id) WHERE deleted_at IS NULL;

The ideal index on main_table would be

CREATE INDEX ON main_table (user_id, appointment_id);

The order of the columns doesn't matter in this case, because you will scan for both columns with the = operator.

In the real world, you will try to pick indexes that can be useful for as many queries as possible, because having too many indexes hurts performance and wastes space.


Disk page size for MySQL and MySQL cluster As I know database reads data from disk in some constant page size (even if one byte is needed, whole page will be copied from disk to memory).
What are disk page sizes for MySQL and MySQL Cluster?
Is this value configurable or is fixed and depends on db engine/operating system?


Mysql throwing table full error when adding new column I keep getting table full error when adding a new column to 30gb table on innodb mysql database.
ibdata1 size : 844mb
Current Settings
innodb_data_file_path : ibdata1:12M:autoextend
tmp_table_size : 256M (increased from 64M)
max_heap_table_size : 256M (increased from 64M)
Changed settings above and restarted server. Still throwing error.

tmp_table_size and max_heap_table_size are useful for certain complex SELECTs, not for ALTER.  It is not wise to raise either of those above 1% of RAM.
ALTER works in one of two ways, depending on version and the specifics of the query.

Copy the table over, making the change as it goes.  This seems to be what is happening.
Certain optimizations that avoid needing to copy the table.  It seems that this option was either not available due to using an old version or the complexity of the ALTER.

(Please provide the version of MySQL and the text of the ALTER.)
The "copy" method needs disk space to make a full copy of the table, then it does something like RENAME TABLE to swap it into place.  In your case, there needed to be about 30GB free on disk to perform the Alter.  Without the space, you get that error.
It may be that that space needs to be in the "tmpdir".  Could it be that that is a separate disk partition, and that partition is too small?
At the time of running the Alter, what was the value of innodb_file_per_table?  If it was OFF (or 0), then you have a nasty problem -- ibdata1 would be expanded by the 30GB, and not shrunk back.  You may have that table so larger that the disk is full.  Ouch.
Since you mentioned 844MB and 3TB, I would guess that that setting was ON (or 1).  That is, your 30GB table was probably a .ibd file on disk.  And another 30GB file was being built, but disk space ran out.
Please provide more info (as requested above); we may be able to help you find a not-too-painful way out of your predicament.
(As for SO vs dba.SE -- There are a zillion and one questions on SO; no human can even glance at all the questions, especially those that have only one tag.  No traction => no responses.)


Need to upgrade SQL Server from 2012 to 2017 on a Server 2008 box I have been asked to upgrade SQL Server from 2012 to 2017, it is currently installed on a server with Windows 2008 OS.
My thinking from what I've researched is this:

Take a snapshot of the server
Upgrade OS first from 2008 > 2012R2
Run SQL Server data migration assistant to make sure nothing is incompatible with new SQL version
Take a snapshot of the server
Backup all DBS, script out all jobs/logins/linked servers, etc using PowerShell
Upgrade SQL Server in place to 2017

From this thread I have found that 2017 should be compatible with 2012 OS.
And here it says I should be able to upgrade SQL Server directly from 2008 SP4 to 2017.
The only thing I'm not sure about is the initial OS upgrade, will this cause any issues with the current SQL install?  Am I doing this in the correct order?
If anyone has done this kind of upgrade I would be grateful if you could advise me if I'm going about this the correct way or if there's a better solution I'm not seeing.

This will be somewhat an opinion answer, so likely to be downvoted.
First up, in-place upgrades are fraught with issues. So many things can go wrong along the way and you have no fallback position except a restore. This means you must first ensure that at every step you have a restore option, for the databases and the OS. However, I recognize that not everyone can do a side-by-side upgrade.
Yes, you're doing this in the correct order. Get the OS upgraded first and ensure it's stable. Then do the SQL Server upgrade. Both these are fairly standard upgrades and under normal circumstances should go off without a hitch. However, you're missing steps on the upgrade.
While I understand that you can't do a side-by-side upgrade, I'd still do side-by-side testing. Get a second server, even if it's a VM on a dev laptop, and run through this whole process. Validate that it works. Don't trust some idiot on the internet. Most of your application code should work just fine. Some may not. Find that early, before you've committed to a difficult to undo upgrade.
I'd also add the step of recording standard behavior of the system now using Extended Events and then again after the upgrade so you have data to understand how the upgrade affected performance and behavior.
Because you're crossing the 2014 threshold, moving from 2012 to 2017, you have to deal with the new cardinality estimation engine introduced in 2014. For most queries, no difference will be apparent. For a few queries, you may see a performance enhancement. For a minority of queries, you may see horrific degradation of performance.
After you restore your databases to 2017, leave the compatibility mode in place, at 2012. Enable Query Store on your databases. Run your normal load for a period of time. It's on you to figure out how long. Depends on the system. Some could be a day, some a month, somewhere in between. I don't know. After running the load for this period, change the compatibility level to the new version. When you see regressed queries, use Plan Forcing to make them run the old way (the old plans will be in Query Store).
That should cover you better.


SQL Server full safety mirror commit overhead less then Time to send? How is it possible that the time to send value is greater then the mirror commit overhead? The mirror operates under full safety so this means that the commit is only done @ principal when the commit is successful @ mirror. When the time to send is 2 seconds, this should mean that that overhead is at least 2 seconds. Or am I missing something?

Database Mirroring Monitor (Status Page):

  Time to send log (estimated)
  Approximate amount of time the principal server instance requires to send the log that is currently in the send queue to the mirror server instance (the send rate). Because the rate of incoming transactions can vary significantly, the time to send log is an estimate. However, the send rate can be useful for roughly estimating the time required for a manual failover.

The time to send measures the time to send all the pending log. It only applies if there is a pending log (eg. the principal has run exposed and mirror is in 'synchronizing' state). Notice that in this state the principal does not wait for commits to be acknowledged by mirror, hence the commit overhead is significantly lower (1-5 ms) than when running in 'synchronized' state (20-40-80ms).


Get the Time of Last update of a column This command gives the date of the last update for a table

SELECT UPDATE_TIME
FROM   information_schema.tables
WHERE  TABLE_SCHEMA = 'MyDB'
AND TABLE_NAME = 'MyTable'

But I want to find the time of last update of a particular column of a table.
I can't use triggers because I want to know the time of last update of a specific column from a system table.

I hope I explained well my problem.

None of the system tables (that is, nothing in the INFORMATION_SCHEMA database) exist that has that kind of information recorded anywhere. In other words, there is no native mechanism to put any timestamps on column changes. Any time that

one or more columns change in any row
a new row is added
an old is deleted
an ALTER TABLE of any kind

the UPDATE_TIME column in INFORMATION_SCHEMA.TABLES is updated.

SUGGESTION

This may be a bitter pill to swallow, but you must do the following:

Create a custom table that records

primary key
column name
the old value of that column
the new value of that column
timestamp of the change

Create BEFORE UPDATE trigger to compare old and new columns and record it if they do not match

EPILOGUE

This may not be so bad if you are customizing for a few tables and a few columns.


How to find functional dependancies from simply looking at a relational schema? If only given a schema (table header, with primary key indicated), how do I find functional dependencies?

My problem is that functional dependencies are determined in terms of the data stored in the relevant table, and so you could not determine all, but only some functional dependencies by looking at the schema only?

Consider this example:

How do I go about finding the applicable functional dependencies?

You can't tell the functional dependencies just from the schema.

If you knew that the schema was in third normal form (3NF) or higher and you knew that there are no other candidate keys other than the primary key, then you would know that every non-key attribute was fully functionally dependent on the primary key.

Except for this particular situation, there is nothing about a schema that conveys functional dependency.  The purpose of a schema is to convey table structure, not functional dependencies.


How do I make SQL Server database accessible to PC's on the LAN? We are using Microsoft SQL Server 2008 R2 Express on server machine with instance name abc12. This instance contains two databases:

alarm
report

but when we connect to the abc12 instance from other PC's on the local area network, it shows only one database alarm in this instance. It doesn't show the report database.

How do I make that report database accessible to other PC's on the LAN?

Please help me

Sounds like you have an issue with permissions - if one database is visible they've clearly connected to the instance. I'd suggest you run SSMS as yourself from the other PC and see what happens.


What is the "Data partitioned, Shared-Nothing, Parallel Database" solution for PostgreSQL ? I was looking for a open source parallel database solution. It seems Postgres-XC is a viable solution. However, I don't need the replication. In this case, is there any better solution towards read optimized parallel architecture ?

There is pl/proxy, which provides a basic federation capability, but it's not an engine optimised for this sort of work.  Greenplum is a commercial product that does have a shared nothing engine that might do what you want.  However, it's not cheap - less expensive than Oracle but more expensive than SQL Server.

There are no pure open-source engines of that type for PostgreSQL.  However, you might get what you want from PL/Proxy.


Schema designing for handling multiple payment gateways This is more of a question that requires feedback.
I'm designing a database that handles multiple payment gateways. A payment gateway mostly requires a table for order details before making the payment(this is common for all PGs), and a table for transaction details, for storing response after making the payment.

Now to handle multiple payment gateways, I can either keep a single transaction table, stuffing it with all the fields available from all the payment gateways and a field that says which PG that row is from;
Or, I can create separate transaction tables for each of the PG with prefix like paypal_ or bank_ etc, each having the fields each of them need.

I'm just not sure which is the more optimum way of doing it. Also need to learn it for similar scenarios I might come across in future.

It depends on how different the data is between the payment types.

For the sites I support at work, we have one table that stores data for all payment types.  That works for us because our payment types are basically 4 kinds of credit cards and company purchase order.  Most of our customers pay with credit cards, so there isn't a whole lot of deviation in the data.  Of course, queries for those credit card customers always yield NULL values in the PONumber field.  Likewise, queries for PO customers yield NULLs in all the credit card related fields.

If there are a lot of different fields in your data, you could try one master transaction table with individual tables for each payment gateway.  Each payment gateway type table would have a foreign key of transaction_id, which would link back to the master transaction table.

On the other hand if your payment gateway types all have similar fields, then I would stick with one transaction table.


List Active Directory Users My boss sent to request me that: I want to a SQL query, that lists all domain users of a specific group. I began to search query. I found several solutions using Linked Server.

EXEC master.dbo.sp_addlinkedserver
@server = N'ADSI',
@srvproduct=N'Active Directory Service Interfaces',
@provider=N'ADSDSOObject',
@datasrc=N'adsdatasource'

then

EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'ADSI',
@useself=N'False',
@locallogin=NULL,
@rmtuser=N'DOMAIN\USER',
@rmtpassword='*********'

I replaced username and password with a domain admin user's information but I got this error

  Cannot create an instance of OLE DB provider "ADsDSOObject" for linked server "ADSI".

Our domain name is akilus.local, but I didn't replace the data source.

What could the problem be?

Why is SQL Server required to do this task?  This isn't the objective of an RDBMS.  You would have to go through hoops to get SQL Server to find this data.

The quickest/easiest/most natural way to do this?  The Active Directory PowerShell module.  It's a one-liner:

Get-ADGroupMember "administrators" | Select-Object Name, DistinguishedName, ObjectClass

In short, keep logic like this away from SQL Server and put it in the OS where it belongs.


DB2: Database alias already exists when cataloging SQL1005N  The database alias "XYZ" already exists in either the local database directory or system database directory

I am an accidental DB2 LUW on linux administrator; primary background in MSSQL and Oracle. We have an application that has an integrated DB2 Enterprise server as part of the stack. Within that, the application creates databases based on user defined workloads. When those workloads are dropped, the databases are removed as well. Unfortunately, I've reached a scenario where i need to reuse database names. For example, the databases are named like WORKOB0[0-9][0-9] and we've exceeded 99, historically.

If I manually create database WORKOB01, the create is successful. However, when I attempt to catalog it, I get the error message above. The databases do not exist in the user directory, no question about that. However, there is a twist. The application creates databases AT NODE 'HOMEDB01'. When I use commands like list database directory, no objects are present. If I use list node directory, it states that "the node directory cannot be found".

I'm lost. I've spent several hours on this and I'm in a position to say: I have no idea what I'm doing!

If this is a development environment I suggest you turn off the directory cache:

db2 update dbm cfg using DIR_CACHE NO
db2 stop force; db2start

I don't really understand why you need to create new databases though, can't you cope with a new schema in an existing database?


Postgres Error: could not read block 167593 in file... SQL state: XX001 When creating a new column on a table, I'm getting the following error:

ERROR: could not read block 167593 in file "pg_tblspc/16384/PG_9.4_201409291/16386/157223695.1": read only 0 of 8192 bytes
SQL state: XX001

Obviously not good, as SQL state XX001 means corrupt data. I'm not too bothered as this is not critical data and can relatively easily be re-loaded from the source.

However, after searching for similar issues, all I can see from other people is this error for 'of relation base', not 'in file'. How is 'in file' different, and what does this mean? Is it more likely that I have a hardware issue?

Also, how can I check to see for certain that the data is corrupted?

However, after searching for similar issues, all I can see from other
  people is this error for 'of relation base', not 'in file'. How is 'in
  file' different, and what does this mean? Is it more likely that I
  have a hardware issue?

No, it's the same issue. The error message used to refer to the relation in older versions of PostgreSQL, but it was changed at some point (version 9.0, probably) to refer to the corresponding file instead.

I think the switch to the new message format happened here:

  commit 23dc89d2c385e8e362cb4b8186b4d4ad02242ac0
  Author: Heikki Linnakangas
  Date:   Wed Aug 5 18:01:54 2009 +0000

Improve error messages in md.c. When a filesystem operation like open() or
fsync() fails, say "file" rather than "relation" when printing the filename.

This makes messages that display block numbers a bit confusing. For example,
in message 'could not read block 150000 of file "base/1234/5678.1"', 150000
is the block number from the beginning of the relation, ie. segment 0, not
150000th block within that segment. Per discussion, users aren't usually
interested in the exact location within the file, so we can live with that.

To ease constructing error messages, add FilePathName(File) function to
return the pathname of a virtual fd.


msdb.dbo.sysjobs and last run date and time I know that the last_run_date and last_run_time are stored as Int, but when coverted over to datetime (of which I have examples of), is the time GMT, or what? When converted it doesn't match the same time as what the GUI log history displays.

Can anyone provide guidance on this?

Here is the t-sql code I have that converts the value to a datetime value:

'last run literal' = dateadd(millisecond,
    sjs.last_run_time,
    convert(datetime,cast(nullif(sjs.last_run_date,0) as nvarchar(10))))

The time is not stored as number of milliseconds.  It is a numeric representation of the actual time.  For instance, 12:06:59 is represented as 120659.  1:02:04 pm is presented as 130204.  1:23:45 am would be 12345.

Instead of calculating that all yourself, simply use the dbo.agent_datetime function.

Something like:

SELECT dbo.agent_datetime(last_run_date, last_run_time);

The above function uses this code to create a DATETIME value:

CONVERT(DATETIME,
      CONVERT(NVARCHAR(4),@date / 10000) + N'-' +
      CONVERT(NVARCHAR(2),(@date % 10000)/100)  + N'-' +
      CONVERT(NVARCHAR(2),@date % 100) + N' ' +
      CONVERT(NVARCHAR(2),@time / 10000) + N':' +
      CONVERT(NVARCHAR(2),(@time % 10000)/100) + N':' +
      CONVERT(NVARCHAR(2),@time % 100),
120)

If you can stand adding a function to the msdb, you can create a table-valued-function that will perform very well, as:

CREATE FUNCTION dbo.get_agent_datetime(@date INT, @time INT)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN (
SELECT agent_date_time =
    CONVERT(DATETIME,
          CONVERT(NVARCHAR(4),@date / 10000) + N'-' +
          CONVERT(NVARCHAR(2),(@date % 10000)/100)  + N'-' +
          CONVERT(NVARCHAR(2),@date % 100) + N' ' +
          CONVERT(NVARCHAR(2),@time / 10000) + N':' +
          CONVERT(NVARCHAR(2),(@time % 10000)/100) + N':' +
          CONVERT(NVARCHAR(2),@time % 100),
    120)
);

To get the last 7 days of Agent Job History, you'd use this:

SELECT JobName = sj.name
    , RunDate = dt.agent_date_time
    , sjh.*
FROM dbo.sysjobs sj
    INNER JOIN dbo.sysjobhistory sjh ON sj.job_id = sjh.job_id
CROSS APPLY dbo.get_agent_datetime(sjh.run_date, sjh.run_time) dt
WHERE dt.agent_date_time >= DATEADD(DAY, -7, GETDATE());

Dates/times presented are stored in the local time of the server.


Mongodb sharding - set shard size I am very new to MongoDB and am trying to do database sharding.

I have created two MongoDB shards and inserted 10000 records in it. I have used shard key as "no" which increments by 1 in every document in ascending order.

mongos>sh.addShard("localhost:27004",{"no":1})
mongos>sh.addShard("localhost:27005",{"no":1})

Here, when I insert the records, the shard 1 gets only 6 records and shard 2 gets the rest of the records.

The output of sh.shatus() is:

 databases:
    {  "_id" : "shard_test_2",  "primary" : "shard0000",  "partitioned" : true }
            shard_test_2.test
                    shard key: { "no" : 1 }
                    unique: false
                    balancing: true
                    chunks:
                            shard0000       2
                            shard0001       1
                    { "no" : { "$minKey" : 1 } } -->> { "no" : 1 } on : shard0000 Timestamp(2, 1)
                    { "no" : 1 } -->> { "no" : 6 } on : shard0000 Timestamp(1, 2)
                    { "no" : 6 } -->> { "no" : { "$maxKey" : 1 } } on : shard0001 Timestamp(2, 0)

Now, I want my shard one to store more documents than 6.

I read that how many documents will go to which shard is the function of balancer. So, how do I configure the balancer?

Or is there any other way of setting how much data should go to which shard?
No resource online is helping much in this.

Mongo can't determine key ranges for sharding if you use increments.
Here's an example:

You have a collection with keys 10, 20, 30, 40, 50, 60.

If mongo have to create two shards, it may assume the keys range as: [10,30] and [31,60] (or similar).

By continuing to write bigger keys, they will always go to the second range.

Mongo will adjust the ranges, but it can never know what the next key will be, and it will always put it into the last range.

Try to use some key with good distribution.

More details here: https://docs.mongodb.com/manual/core/sharding-shard-key/#monotonically-changing-shard-keys


Finding count of objects within a specified time window in SQL (Postgresql DB) I need to find out all the users who registered for my Postgresql 9.3 backed website in the 24 hour window 2 days ago. Currently, I'm doing that via running the following queries, and then manually subtracting the difference:

select count (*) from auth_user where date_joined > now() - interval'24 hours';
select count (*) from auth_user where date_joined > now() - interval'48 hours';

How do I do everything in the same SQL query, including the subtraction? Thanks in advance!

If I do select count (*) from auth_user where date_joined > (now() - interval'48 hours') - (now() - interval'24 hours');, I get:

  No operator matches the given name and argument type(s). You might
  need to add explicit type casts.

Use conditional aggregation:

select count(*) filter (where date_joined > now() - interval'24 hours') as last_24_hours,
       count(*) filter (where date_joined > now() - interval'48 hours') as last_48_hours
from auth_user

The filter clause is available since Postgres 9.4, for older versions you need to use a case statement:

select count(case when date_joined > now() - interval'24 hours' then 1 end) as last_24_hours,
       count(case when date_joined > now() - interval'48 hours' then 1 end) as last_48_hours
from auth_user


Disable/enable PostgreSQL trigger inside function for the same table I have a trigger that protects updating some column. I also have a trigger that should update that same column after insert on that table. I've used triggers like this and it works if I need to update column on some other table. I just disable that protecting trigger before update and enable it again after the update inside function that do all the work. In this case, it doesn't work, since I need to disable the trigger for the same table.

  ERROR:  cannot ALTER TABLE "Table1" because it is being used by active queries in this session

Is there another way to do it? Is there way to disable all updates to that column except from within this function?

It's PostgreSQL 9.4.

One option would be to:

Jack up the security on the table so normal users can't directly access it at all, and remove the protecting trigger.
Create an updatable view on the table which is accessible by normal users, and add an INSTEAD OF trigger to protect the column from updates (assuming you want it still visible; if not, simply omit it completely from the view definition.)
Create a function with definer's rights which bypasses the view and directly updates the column in the base table.


What are the other types of database architectures? Oracle, for example has a typical client server architecture, and a memory cache (SGA) with some background processes.

This is one way of designing a database. Are there databases out there which have been designed fundamentally in different ways than this?

Most databases are client/server, although some embedded databases can be used purely like libraries, and share the application's address-space.

The server architecture can vary between databases, and differ from what you described too.  MySQL runs entirely as a single process (with multiple threads).  It does not require any OS shared memory segments or inter-process communication.


What does "Heap Block" in "Bitmap Heap Scan" mean? I have this query:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM test
WHERE
    timestamp_range @> '2015-01-22 23:00:00'::timestamp
    AND data_int_array @> '{49, 61}'::integer[];

Which outputs:

Bitmap Heap Scan on test  (cost=16.74..20.75 rows=1 width=113) (actual time=0.364..0.367 rows=2 loops=1)
  Recheck Cond: ((timestamp_range @> '2015-01-22 23:00:00'::timestamp without time zone) AND (data_int_array @> '{49,61}'::integer[]))
  Heap Blocks: exact=1
  Buffers: shared hit=8
  ->  BitmapAnd  (cost=16.74..16.74 rows=1 width=0) (actual time=0.351..0.351 rows=0 loops=1)
        Buffers: shared hit=7
        ->  Bitmap Index Scan on ix_test_interval  (cost=0.00..4.40 rows=17 width=0) (actual time=0.130..0.130 rows=12 loops=1)
              Index Cond: (timestamp_range @> '2015-01-22 23:00:00'::timestamp without time zone)
              Buffers: shared hit=2
        ->  Bitmap Index Scan on ix_test_data_int_array_data_json  (cost=0.00..12.08 rows=11 width=0) (actual time=0.211..0.211 rows=6 loops=1)
              Index Cond: (data_int_array @> '{49,61}'::integer[])
              Buffers: shared hit=5
Planning time: 0.396 ms
Execution time: 0.484 ms

I have read the documentation "Using Explain", but I didn't find any reference to Heap Block.

Please, could you tell me what Heap Bock means and how it relates to Buffers in a Bitmap Heap Scan?

I'm running: "PostgreSQL 9.4.5, compiled by Visual C++ build 1800, 64-bit"

Bitmaps can either store a bitmap of rows, or if that becomes too large to fit in work_mem it can "go lossy" by storing a bitmap of blocks.  It can do this selectively, so some blocks can be converted lossy while others not.

If it goes lossy, then the Heap Scan must recheck every row in every lossy block which it visits, because it no longer has information about which particular rows in that block met the search criteria.

"Heap Blocks: exact=1" means that it only visited one block, and that it was not lossy.  (So both of yours rows returned were in the same block)

This agrees with the Buffers data.  The Bitmap Index Scans and BitmapAnd in total hit 7 blocks.  The Heap Scan hit one more, bringing it up to 8.


MySQL customized service script for zip installation We are currently using MySQL 5.7.20 on CentOS and are using zip installation.
Since it is a zip installation we do not have service created in /etc/init.d/.

Is there any other method for auto-startup and auto-shutdown as we usually do with service.

We can startup using /etc/rc.local but what about if we want to do auto-shutdown or service status? any sample service script

Please suggest.

Thanks.

SHUTDOWN

MySQL 5.7 now features the SHUTDOWN command, which requiresthe  SHUTDOWN privilege.

SHUTDOWN METHOD #1: Within mysql client

mysql> SHUTDOWN;

SHUTDOWN METHOD #2: From OS using mysql client

MYSQL_HOST=127.0.0.1
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -P3306 --protocol=tcp"
mysql ${MYSQL_CONN} -ANe"SHUTDONW"

SHUTDOWN METHOD #3: Using mysqladmin

This can be done with any MySQL 5.x version

MYSQL_HOST=127.0.0.1
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -P3306 --protocol=tcp"
mysqladmin ${MYSQL_CONN} shutdown

STATUS

You can use mysqladmin

For example, to see if mysqld is alive, run this:

MYSQL_HOST=127.0.0.1
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS} -P3306 --protocol=tcp"
mysqladmin ${MYSQL_CONN} ping 2>/dev/null | grep "mysqld is alive"

You can also do one of the following:

mysqladmin ${MYSQL_CONN} status
mysqladmin ${MYSQL_CONN} variables
mysqladmin ${MYSQL_CONN} uptime
mysqladmin ${MYSQL_CONN} processlist

AUTO START

Make sure you have chkconfig mysqld on or  chkconfig mysql on enabled

There are no mysql utility programs to auto-startup except for mysqld_safe, which is launched by the mysqld (or mysql) service. This is usually issued by sudo service mysqld start (MySQL 5.7) or sudo service mysql start.

If you would like to attempt to write a custom mysqld_safe script, please see my old posts for ideas

Sep 30, 2011 : Running multiple instances on the same host
Sep 17, 2012 : mysqlservice command syntax


Internal reason for killing process taking up long time in mysql I copied a big table's structure with (it is an InnoDB table btw)

CREATE TABLE tempTbl LIKE realTbl

Then I changed an index, and filled it up so I could run some test.
Filling it was done using:

INSERT INTO  `tmpTbl`
SELECT *
FROM `realTbl`

This took too long, so I wanted to stop this test.1

I killed the process while it was in a "Sending data" state: it is now "killed", and still in the state "Sending data".

I know some killed processes need to revert changes and so could take (equally?) long to kill compared to how long they were running, but I can't imagine why this would be the case now: The whole table needs to be emptied.

I'm curious as to what is happening that would take stopping/killing a simple query like this very long. To give you some numbers: the insert was running for an hour or 3, the kill is closer to 5 7 now. It almost looks like it runs a DELETE for every INSERT it did, and the delete takes longer then the insert did? Would that even be logical?

(And if anyone knows how to kick my test-server back into shape that would be nice too, as it's eating some resources, but that's not really important at this moment :) )

1)  I don't know yet why (it's a big table, 10M rows, but it should take that long?), but that's another thing / not part of this question :). It might be that my test could have been smarter or quicker, but that is also not the question now :D

The reason the kill takes so long is most likely due to the rollbacks issued by the innodb transaction. From InnoDB performance tips:

  Beware of big rollbacks of mass inserts: InnoDB uses the insert buffer to save disk I/O in inserts, but no such mechanism is used in a corresponding rollback. A disk-bound rollback can take 30 times as long to perform as the corresponding insert. Killing the database process does not help because the rollback starts again on server startup.

Edit:
The innodb force recovery methods might be of  use to you (glad you did this on a test environment)

  You can kill the mysqld process and set innodb_force_recovery to 3 to bring the database up without the rollback, then DROP the table that is causing the runaway rollback.

And next time, insert a small subset of data each time. 10 million rows shouldn't take that long to insert, but there could be multiple reasons it does. Without knowing your environment I can't offer advice on that.


Get top 5 on 2 distinct where clause On postgres I have data like:

id | device_model | device_type
1  | "samsung"    | 1
2  | "iphone 4"   | 1
3  | "samsung"    | 1
4  | "ipad"       | 0
5  | "ipad"       | 0

And I'm looking to get the top 5 of device_model per device_type.

for the moment I'm doing :

SELECT device_model, COUNT(*)
FROM devices
GROUP BY device_model
ORDER BY COUNT(*) DESC
limit 5;

But it doesn't render top5 for each device_type. What can I use to do this? Seems I need to do a UNION.

Can I do something like :

SELECT device_model, COUNT(*)
FROM devices
WHERE device_type = 1
GROUP BY device_model
ORDER BY COUNT(*) DESC
LIMIT 5
UNION
SELECT device_model, COUNT(*)
FROM devices
WHERE device_type = 0
GROUP BY device_model
ORDER BY COUNT(*) DESC
LIMIT 5;

ERROR:  syntax error at or near "UNION"
LINE 7: UNION ALL
        ^
Query failed
PostgreSQL said: syntax error at or near "UNION"

This is the right query:

WITH counts AS (
    SELECT device_model, device_type, COUNT(id) AS cnt, rank() OVER (PARTITION BY device_type ORDER BY COUNT(id) DESC) AS model_rank
    FROM devices
    GROUP BY device_model, device_type
)
SELECT device_model, device_type, cnt, model_rank
FROM counts
WHERE model_rank <= 5
ORDER BY device_type, model_rank

It handles models that has the same rank, displaying more than 5 rows per device type just in case.

In order to test it, create a table and fill it with data:

CREATE TABLE devices (
    id serial,
    device_model text,
    device_type integer
);

INSERT INTO devices (device_model, device_type)
    VALUES
        ('samsung', 1),
        ('iphone4', 1),
        ('samsung', 1),
        ('iphone4', 1),
        ('samsung', 1),
        ('samsung', 1),
        ('samsung', 1),
        ('samsung', 1),
        ('ipad', 0),
        ('ipad', 0),
        ('ipad', 0),
        ('ipad', 0),
        ('ipad', 0),
        ('iphone6', 1),
        ('iphone3', 1),
        ('iphone3', 1),
        ('iphone3', 1),
        ('iphone2', 1),
        ('iphone1', 1),
        ('iphone1', 1),
        ('iphone1', 1),
        ('iphone1', 1),
        ('iphone1', 1),
        ('iphone1', 1),
        ('kindle', 0),
        ('kindle', 0),
        ('kindle', 0),
        ('kindle', 0)
;

The output of the initial query is the following:

 device_model | device_type | cnt | model_rank
--------------+-------------+-----+------------
 ipad         |           0 |   5 |          1
 kindle       |           0 |   4 |          2
 iphone1      |           1 |   6 |          1
 samsung      |           1 |   6 |          1
 iphone3      |           1 |   3 |          3
 iphone4      |           1 |   2 |          4
 iphone6      |           1 |   1 |          5
 iphone2      |           1 |   1 |          5
(8 rows)

Feel free to ask questions about the logic behind this solution.


How to check if feature (JSON) exists, and/or version This is a cross-posting from an original question at How to check version is greater than some base value?

When calling MySQL SELECT @@VERSION; or SELECT VERSION();, I get for instance '5.7.11-log'. Is there a "well-known" way to check if the version is greater (or smaller) than some major.minor.patch version? If not a well-known way, is there a way without using a temporary table or a user-defined function (there probably is, but for a reason or another currently eludes me)? I would use this to check if there's support for JSON type type that was introduced in version 5.7.8.

Upon researching this more, it appears this is a bit tougher nut for my skills to crack. For instance, I could write something like

SELECT
    SUBSTRING_INDEX(@@VERSION, '.', 1) AS major,
    SUBSTRING_INDEX(SUBSTRING_INDEX(@@VERSION,'.', 2), '.', -1) AS minor,
    SUBSTRING_INDEX(SUBSTRING_INDEX(@@VERSION,'.', -2), '.', -1) AS patch;

but that isn't entirely satisfactory (e.g. see -log) and it doesn't directly check the existence of the feature.

Is anyone aware of better ways?

<edit: To amend Rick's answer a bit, the comments syntax can be used to recognize constructs too, not just versions. For instance, in this particular case like this SELECT /*!JSON_TYPE*/('["a", "b", 1]');. Here's a link to the documentation: http://dev.mysql.com/doc/refman/5.7/en/comments.html.

/*!50700 ... */ says:  If you are running a version older than 5.7.0, this is a comment.  Else it is sql.  It can be entire statements or fragments of statements.

Example (taken from mysqldump output):

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

Presumably, @@TIME_ZONE was implemented in 4.1.3.

(Note:  I think the two spaces are part of the syntax.)


Database size - MDF too large? I'm maintaining a SQL Server 2005 database which hosts approximately 2.9Tb of data (2 x 1.45Tb - I have a RAW schema and an ANALYSIS schema so basically two copies of the data ingested). The recovery model is SIMPLE and the .ldf is at 6Gb.

For whatever reason, the .mdf is 7.5Tb. Now, there are only maybe 2-3 additional columns in ANALYSIS tables and not many NVARCHAR(MAX) columns which, from what I (may have mistakenly understood - please correct me if I'm wrong) may be causing additional space allocation. That's after shrinking the database just now - it was at ~9Tb prior to that. Any thoughts?

And, please, let me know if you have additional questions - I'm very new to database administration and optimization efforts (I usually don't do this side of the job :)).

Many thanks!

Andrija

In your size estimates, have you taken into account the amount of space taken by indexes? Also if you have text fields that are set as multi-byte (N[VAR]CHAR rather than [VAR]CHAR) and the input files are UTF-8 or plain one-byte-per-character then that will push your storage requirements up by up to a factor of two. Furthermore remember that if you have a clustered key/index on a table the size of this affects all other indexes on the table because they include the clustered key value for every row (so to give an extreme example if a table has an NCHAR(10) key where an INT would do and that is your clustered key/index you are not only using an extra 16 bytes per row in the data pages you also waste 16 bytes per row in every other index on that table).

Also, some space will be allocated but unused, either because the DB engine has left some space allocated after deletes so that it can be used again quickly for new data in that table or because the pattern of inserts and deletes has left many pages only part full.

You can run:

SELECT o.name
     , SUM(ps.reserved_page_count)/128.0 AS ReservedMB
     , SUM(ps.used_page_count)/128.0 AS UsedMB
     , SUM(ps.reserved_page_count-ps.used_page_count)/128.0 AS DiffMB
FROM sys.objects o
JOIN sys.dm_db_partition_stats ps ON o.object_id = ps.object_id
WHERE OBJECTPROPERTYEX(o.object_id, 'IsMSShipped') = 0
GROUP BY o.name
ORDER BY SUM(ps.reserved_page_count) DESC

to get a quick look at what tables are taking up space.

Also EXEC sp_spaceused run within that DB will return two result sets. The first lists the total space allocated in the filesystem for the data files and how much of that is unallocated, the second lists how much of the allocated space is used for data pages, for index pages, or is currently unused.

sp_spaceused will return the space used by a given object too, so you can loop this to build a table for analysis:

-- TEMP TABLES FOR ANALYSIS
CREATE TABLE #tTables (sName NVARCHAR(MAX), iRows BIGINT, iReservedKB BIGINT, iDataKB BIGINT, iIndexKB BIGINT, iUnusedKB BIGINT)
CREATE TABLE #tTmp (sName NVARCHAR(MAX), iRows BIGINT, sReservedKB NVARCHAR(MAX), sDataKB NVARCHAR(MAX), sIndexKB NVARCHAR(MAX), sUnusedKB NVARCHAR(MAX))
-- COLLECT SPACE USE PER TABLE
EXEC sp_msforeachtable 'INSERT #tTmp EXEC sp_spaceused [?];'
-- CONVERT NUMBER-AS-TEXT COLUMNS TO NUMBER TYPES FOR EASIER ANALYSIS
INSERT #tTables SELECT sName, iRows
                     , CAST(REPLACE(sReservedKB, ' KB', '') AS BIGINT)
                     , CAST(REPLACE(sDataKB    , ' KB', '') AS BIGINT)
                     , CAST(REPLACE(sIndexKB   , ' KB', '') AS BIGINT)
                     , CAST(REPLACE(sUnusedKB  , ' KB', '') AS BIGINT)
                FROM #tTmp
DROP TABLE #tTmp
-- DO SOME ANALYSIS
SELECT sName='TOTALS', iRows=SUM(iRows), iReservedKB=SUM(iReservedKB), iDataKB=SUM(iDataKB),  iIndexKB=SUM(iIndexKB), iUnusedKB=SUM(iUnusedKB) FROM #tTables ORDER BY sName
SELECT * FROM #tTables ORDER BY iReservedKB DESC
-- CLEAN UP
DROP TABLE #tTables

The above code will output all the table sizes in one list, plus a single row for the totals. If needed you can use the various system views (like sys.objects and sys.dm_db_partition_stats used in the first query above, see http://technet.microsoft.com/en-us/library/ms177862.aspx for much more detail) to get more details such as the space used by each index.

There are three classes of unused space in a data file:

That which is not allocated to anything (this shows in the first resultset from sp_spaceused with no object specified)
That which is allocated to an object (reserved) but not currently used (this shows in the "unused" count in sp_spaceused's output.
That locked in part-used pages (this will look to be used as everything is allocated in single page chunks, one page being 8,192 bytes long). This is harder to detect/calculate. It is due to a mix of two factors:

Split pages. As data gets added you often end up with part empty pages (the storage engine could always normalise page contents, but this would be very inefficient), and as rows are deleted page contents are not automatically packed (again they could be, but the extra I/O load is generally far from worth it).
The storage engine won't split a row over multiple pages (this along with the page size where the 8,192 byte-per-row limit comes from). If your rows are fixed size and take 1,100 bytes each then you are going to "waste" at least 492 bytes of each data block allocated to that table (7 rows take 7,700 bytes and an 8th won't fit so the remaining bytes won't be used). The wider the rows, the worse this may be. Tables/indexes with variable length rows (which are far more common than completely fixed length ones) generally fair better (but are less easy to calculate the matter for).
Another caveat here is large objects (TEXT columns, [N]VARCHAR(MAX) values above a certain size and so on) as they do get placed off-page, just taking 8 bytes in the main row data to hold a pointer to the data elsewhere) so can break the 8,192 bytes-per-row-limit.

tl;dr: Estimating expected database sizes can be a lot more involved than it is natural to initially assume.


How do we express a weak entity to be dependent on another weak entity? Perhaps I am looking at my entities as a hierarchy rather than a set of relationships; for sake of an example I have three tables:

Authors
Books
Content

Where Authors writes Books and Books contain content.

The rules for these entities is as follows:

Content cannot stand alone without association to a Book
a Book cannot stand alone without association to an Author

See below the following partial implementation:

CREATE TABLE authors(
   author_id integer,
   author_name varchar,
   author_age int,

   PRIMARY KEY (author_id)
);

CREATE TABLE books(
   book_id integer,
   author_id integer,
   book_title varchar,
   book_published date,

   PRIMARY KEY (book_id, author_id),
   FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

From here I believe I have created a relationship where Books relies on Authors, should it decide to exist.

Arguably some may say that Content can simply just be part of Books, but say that Content has a hefty amount of columns in association to it and that a person would prefer to keep the more general Book data separate from its Content data for simplicity purposes.

In the event of the latter, how can I write a create statement that will make Content dependent on Books?

Using you syntax, I think the following might be what you're looking for:

CREATE TABLE authors(
   author_id integer,
   author_name varchar(100),
   author_age int,

   PRIMARY KEY (author_id)
);

CREATE TABLE books(
   book_id integer,
   book_title varchar(100),
   book_published date,
   author_id integer,

   PRIMARY KEY (book_id),
   FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

CREATE TABLE chapters(
   chapter_id integer,
   chapter_title varchar(100),
   book_id integer,
   content longblob,

   PRIMARY KEY (chapter_id),
   FOREIGN KEY (book_id) REFERENCES books(book_id)
);

Each table has a unique id column as its primary key and a foreign key relationship to the table "above" it.  These don't need to be part of the primary keys.  For instance, book_id should be unique in and of itself, no need to include author_id as part of the primary key (assuming books only have one author).  I added a table for chapters with basically the same relationship to books as books are to authors.

It might be helpful to think about the relationships a little differently.  You say for instance, "a Book cannot stand alone without association to an Author" or "I have created a relationship where Books relies on Authors".  It might be better to think of authors simply as a look-up [-Who wrote this book? -Author 37 did (details on file elsewhere).] and the foreign-key constraint as simply requiring that look-up to be a legitimate author (i.e. present in the authors table).

To illustrate the non-hierarchy further, imagine that your books table not only has a look-up to authors, but also to publishers and copyright holders and so on (a "star" structure) and that another table, called editions, has multiple rows associated with each book (using the books table as a foreign-key look-up) and each edition also has a look-up to the company that printed that edition and so on and so on.

Of course you would need a completely different structure in the first place if, say, books could have more than one author, or each chapter is a short story with it's own author or authors, etc.

I hope this helps a bit.


Read only postgres table, except by trigger Suppose the following situation:

I have a product_stock table and a stock_transaction_log table. Currently whenever there is an increase/decrease in a product's stock, I update the product_stock table increasing or decreasing the quantity and also insert an entry in the transaction log table, for auditing. This is done in the application code.

I am considering changing the update of the product_stock table to be done by a trigger on the transaction log table (instead of doing it directly in my application code), but I would like to add a reinforcement that the product_stock table would never be updated directly, and only the trigger would be authorized to update it.

Is there anyway to do this in postgresql (currently using 9.1)

The solution proposed by Ziggy will work, but if you don't want to go that deep by having to create a privileged user just to execute the trigger, there's another solution you can try: Create a validation trigger on the product_stock table that checks if the update is coming from the stock_transaction_log trigger. To do that, we can create a temporary table to serve as a "global variable".

CREATE OR REPLACE FUNCTION validate_product_stock() RETURNS trigger AS $$
DECLARE
    count_ int;
BEGIN
    BEGIN
        SELECT COUNT(1) INTO count_ FROM __inside_stl_trigger WHERE note = 'inside trigger';
    EXCEPTION WHEN undefined_table THEN
        count_ := 0;
    END;

    IF count_ = 0 THEN
        RAISE EXCEPTION 'This table is automatically updated by stock_transaction_log'
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER validate_product_stock_trigger
    BEFORE INSERT OR UPDATE ON product_stock
    FOR EACH ROW
    EXECUTE PROCEDURE validate_product_stock();

CREATE OR REPLACE FUNCTION update_product_stock() RETURNS trigger AS $$
BEGIN
    CREATE TEMPORARY TABLE __inside_stl_trigger (note text NOT NULL);
    INSERT INTO __inside_stl_trigger (note) VALUES ('inside trigger');

    -- Update the product_stock table here

    DROP TABLE __inside_stl_trigger;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_product_stock_trigger
    BEFORE INSERT OR UPDATE ON stock_transaction_log
    FOR EACH ROW
    EXECUTE PROCEDURE update_product_stock();

Obviously, one could create that temporary table by hand, insert a row on it and do the update, but at least you will make it harder.


Running out of disk space mid-restore A colleague of mine reported that an hour after starting a restore the operation failed because the server ran out of disk space.  He mentioned that since we are using filestream the size isn't included in the backup size.

Does this sound right?  I thought the first thing sql server did up on restoring was to check the amount of disk necessary [including filestream objects] then reserve the space on disk, start initializing the allocated disk space and finally write out the data.  I didn't think it was possible to run out of disk space mid-restore.

Does any of this sound right?  Does anyone know of a reason the restore would fail halfway through?

I have not tested it, but I would think the filestream file should be included in the size.

  For example, you can use all backup and recovery models with FILESTREAM data, and the FILESTREAM data is backed up with the structured data in the database.

Source

In addition, it sounds like you have emtpy pages.

  Note that the backup size does not include empty pages, but when you actually perform the restore, the data and log files will be over 200 GB, because it has to restore exactly what the source system had (including a 200+ GB log file, regardless of how full it was).

Your example may not be 200+GB, but it was in the case for this person when their backup was pretty small.

Source


Mongodb update conditional set operator ($set only when it is insert ) Is there anything in Mongo update where $set when will work only when an insert is done.

example is  I have a collection which has these fields:

Username
FirstOccurrence
Count

What i want that i will run a update operation like this.

coll.update{ q: {username} ,u: {$set:{FirstOccurrence},$inc:count} }

But condition will be if UserName is already existing then it wont run the $set it will just increment the count.

Is it possible to do it I am running Mongo 2.6.4 with Java driver 2.13.0

Regards
Viren

It will work for conditional $set update operation

http://docs.mongodb.org/manual/reference/operator/update/setOnInsert/

db.products.update(
  { _id: 1 },
  {
    $set: { item: "apple" },
    $setOnInsert: { defaultQty: 100 }
  },
  {upsert: true }
)

Here setOnInsert will set defaultQty only when a new record is inserted.
Both $set and $setOnInsert can be used together.


Postgresql Streaming replication - monitoring I have setup streaming replication for my postgres servers. I am using PostgreSQL 9.2.6. Replication seems to be working fine. I am monitoring this with two ways using nagios:

Log_delay and
Byte_lag

I am very frequently getting critical alert for log_delay and in the same time byte_lag is not throwing up any alert. log_delay will be OK after 1 or 2 minutes. Can any one please suggest me If I am missing something in my setup?

Queries are given below.

Log_delay

SELECT
    CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location()
    THEN 0
    ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp())
    END AS log_delay;

Byte_lag

SELECT sent_offset - ( replay_offset - (sent_xlog - replay_xlog) * 255 * 16 ^ 6 )
       AS byte_lag
FROM (
    SELECT client_addr,
           ('x' || lpad(split_part(sent_location,   '/', 1), 8, '0'))::bit(32)::bigint
           AS sent_xlog,
           ('x' || lpad(split_part(replay_location, '/', 1), 8, '0'))::bit(32)::bigint
           AS replay_xlog,
           ('x' || lpad(split_part(sent_location,   '/', 2), 8, '0'))::bit(32)::bigint
           AS sent_offset,
           ('x' || lpad(split_part(replay_location, '/', 2), 8, '0'))::bit(32)::bigint
           AS replay_offset
FROM pg_stat_replication ) AS s;

Since you're using 9.2, it would probably be better to use pg_xlog_location_diff instead of your byte_lag function. Something like this is much clearer, less error prone, and should give you what you are looking for.

SELECT client_hostname,
       client_addr,
       pg_xlog_location_diff(sent_location, replay_location) AS difference_in_bytes
FROM pg_stat_replication;

Alternatively, there is always the check_postgres.pl Nagios plugin. It has a huge number of implemented checks that can definitely help keep your PostgreSQL installation happy and healthy.

The particular function you would want for your needs is hot_standby_delay.

Once you have it installed, and the symlinks made, you could simply do something like this from your primary:

check_postgres_hot_standby_delay --dbport=5432,5432  --dbuser=postgres,postgres --dbhost=,10.0.1.2 --warning="1048576 and 2min" --critical="16777216 and 10min" -v

And get alerts if the bytes and the time falls out of your set thresholds.


Indexing a string by a subset of its characters (i.e.: the Kanji ones) I have a database in PostgreSQL modelling a Japanese->English dictionary. The tables I am interested in are listed as follows, where bold represents PK and italic FK:

entries(entry_id int, keyword varchar, reading varchar, meaning varchar)
entries_kanji(kanji char, entry_id int) where entry_id references entries.entry_id

The idea is that entries.keyword is a string containing several kanji. For each of these kanji, there will be an appropriate row in entries_kanji linking back to the entry. This allows me to quickly find all the entries whose keywords contain specific kanji, and by computing the intersection of several queries on this table, identify a word by its kanji alone.

At present, I am using a script external to the database to generate the entries_kanji table by iterating over every row in entries, extracting the kanji from each keyword and inserting a row into entries_kanji. While this works, it is inconvenient to have to run this script every time a new entry is added to the dictionary.

My question is: is there a way to achieve the above using an index alone? I know PostgreSQL allows for indexes to be created based on expressions, but I don't know how to achieve what I described above using this. Or is there some other feature of PostgreSQL that will allow me to do away with the script?

There is a way of implementing what you want by using Regular Expressions and a couple of triggers.

To set the stage, I assume your tables are created this way:

CREATE TABLE entries
(
    entry_id int PRIMARY KEY,
    keyword varchar,
    reading varchar,
    meaning varchar
) ;

CREATE TABLE  entries_kanji
(
    kanji nchar,
    entry_id int REFERENCES entries (entry_id)
) ;

I assume that your definition of Kanji can be translated to a Regular Expression. To begin with, I use the definition of Kanji found on Localizing Japan. That is, we can define one funcion to check whether a character is a Kanji by doing this:

CREATE OR REPLACE FUNCTION is_kanji(IN a_char character)
RETURNS boolean AS
$BODY$
    SELECT
        a_char ~ '[\x3400-\x4DB5\x4E00-\x9FCB\xF900-\xFA6A]'
$BODY$
LANGUAGE sql
IMMUTABLE
STRICT;

The ~ operator means text_on_the_left matches the regular expression on the right. See Pattern Matching in PostgreSQL.

The result of executing the following query

SELECT
    is_kanji('齢');

is true.

Next step is, given a sentence (a string where there are several Kanji chars), we SELECT all of them. This can be done with the following query:

SELECT
    unnest(regexp_matches('答 answer 電 electricity 教 teach',
      '[\x3400-\x4DB5\x4E00-\x9FCB\xF900-\xFA6A]', 'gi')) AS kanji

The function regexp_matches returns an array containing all the parts of the passed string that match the regular expression. This array is converted into a set of rows by means of the unnest function.

Once we have this, we create one trigger function to be associated with INSERTS, UPDATES and DELETES into the entries table.

This function would take care:

CREATE FUNCTION trigger_entry()
RETURNS trigger AS $$
BEGIN
    if TG_OP = 'DELETE' or TG_OP = 'UPDATE' then
        -- When we delete or update rows, we delete all
        -- corresponding entries_kanji.
        DELETE FROM
            entries_kanji
        WHERE
            entry_id = OLD.entry_id ;
    end if ;
    if TG_OP = 'INSERT' or TG_OP = 'UPDATE' then
        -- When we insert or update rows, we insert all
        -- corresponding entries_kanji
        INSERT INTO
            entries_kanji (kanji, entry_id)
        SELECT
             unnest(regexp_matches(NEW.keyword,
                 '[\x3400-\x4DB5\x4E00-\x9FCB\xF900-\xFA6A]', 'g')) AS kanji,
             new.entry_id ;
        -- If case we use a 'before' trigger... we return NEW row (untouched)
        return new ;
    end if ;

    -- In case we use a 'before' trigger, ... we return OLD row (untouched).
    return old ;
END $$
LANGUAGE plpgsql
VOLATILE ;

And we associate this trigger function with one trigger (I have chosen it to be an AFTER trigger, in case some other BEFORE triggers do some data manipulation; otherwise, it could also be a BEFORE trigger):

CREATE TRIGGER trigger_make_entries_kanji
    AFTER INSERT OR UPDATE OF entry_id, keyword OR DELETE
    ON entries
    FOR EACH ROW
    EXECUTE PROCEDURE trigger_entry();

With this setup, you can now insert data into entries, and get the kanji in entries_kanji:

INSERT INTO
    entries (entry_id, keyword, reading, meaning)
VALUES
    (1, 'One sentence with one kanji(朗) symbol', 'reading_1', 'meaning_1'),
    (2, '答 answer 電 electricity 教 teach', 'reading_2', 'meaning_2');

and see that you get the proper information in the entries_kanji table:

SELECT
    *
FROM
    entries_kanji
ORDER BY
    entry_id, kanji ;

You get

+-------+----------+
| kanji | entry_id |
+-------+----------+
| 朗    |        1 |
| 教    |        2 |
| 答    |        2 |
| 電    |        2 |
+-------+----------+

You can also check that the trigger takes care when you UPDATE or DELETE entries.

The advantages of triggers versus materialized views are that

They change the information in real time, without need for refreshing
They are available also in older versions of PostgreSQL (which might be important if version is out of your control)

The disadvantages are:

You (or someone in your team) need to have some knowledge on how to program triggers. Triggers can have some nasty side-effects if not used properly.
You carry some extra cost when inserting, updating or deleting your table's data.

Depending on your use case, you can decide which solution is more appropriate.


How can I minimize SQL Server data loss in AWS in the event of failure? If I am running a SQL Server in AWS. What is a low cost solution to minimize my risk of data loss in the event of failure at the database level?

Lets assume I am performing backups every 6 hours so worst case I can have my servers back up in a couple hours but depending of the time of the failure I could lose 6 hours of data.

Lets assume I am ok with the 2 hours to bring the servers back up but I am not ok with 6 hours of data loss.

Is there a way to narrow my dataloss gap to around 15 mins or less without getting into replication etc.

I am open to any way to host SQL Server in AWS

Additional to your Full backups you can take transaction log backups every 15 minutes. A transaction log backup contains a list of the changes that happened. During the restore those changes get re-applied.

In the case of a disaster you would restore the newest full backup and then all of the log backups that were taken since that full backup in chronological order. With that you would have a maximum of 15 minutes of data loss. The additional storage is dependent on the amount of changes in your system. Usually it is significantly smaller than another full backup.

You could also add differential backups into the mix: Full backup every 6 hours, differential every 2 hours, log backup every 15 minutes. Most likely that wont safe storage, but it could make the restore a lot faster, depending on your situation of course.

No matter which solution you end up taking, make sure you practice the restore regularly, as it is not completely trivial and a disaster is not the right time to try to figure out how it works.


Query databases on different servers How can I create a query that will find all the databases on different servers?

I have four servers,  PRDSVR, UATSVR, DEVSVR, TRNSVR; with either copies of the same database on each server or standalone databases that may only be on one or two servers.

I would like to create a query that searches for all databases, listing the server, databases on that server, and the size of each of the databases.  How can I achieve this?

Thanks in advance for any suggestions.

Four part naming convention.

Add a linked server that will define the security context for connecting, then refer to PRDSVR.master.sys.databases and the like.


Simplest way to log in as Oracle user different from schema user and still see all tables,... of schema? What is the simplest way to log in as user X and see all tables,... of schema Y as if they belonged to X?

With an Oracle 11g database, I would like to log into the database using user X but all the data (tables, views, procedures, ...) would be stored in Schema Y.

All the code that accesses the tables is guaranteed to use unqualified names (TBL_NAME, not Y.TBL_NAME)

Searching for "schema alias" points to CREATE SYNONYM, but that would have to be done for each DML object, right? And since I only have unqualified calls, it seems a login trigger with ALTER SESSION SET CURRENT_SCHEMA = ...; should do the trick?

Any other options?

ALTER SESSION SET CURRENT_SCHEMA

will work, but you still need to do all of the grants.

–  Phil Oct 29 at 23:33


Count Rows that exist before and not after and after but not before I have an Azure SQL Database, one Table that has 3 Relevant Columns: Brand, ID and Month/Year and a Temporary Table that simply lists the Month/Year with an ID.
The table shows a list of all IDs that were active for that given month.
Month/Year is a datestamp column, with the date as the first of the Month. The ID Column is just an ID.
The goal is to have an output that for each month will show the count of all the records that were active in this month, but not active in the next month (Churn) and IDs that weren't active in the previous month but where active in this month (New) and have these grouped by brand and Month so:
Brand | Churn | New | Month
1 | 10 | 12 | 2019-12-01
2 | 11 | 9 | 2019-12-01
I should also note that Table1 has 19 million rows.
I tried the below code:
Declare @Id int
declare @my date

set @Id = 300
set @my = '2019-12-01'

select t1.[brand],
count(c.[ID]) as [churn],
count(n.[ID]) as [new],
t1.[month/year]
from Table1 T1

left join Table1 c on
c.[ID] = t1.[ID] and c.[ID] not in (select [ID] from Table1 where [Month/Year] = (select [month/year] from Temp_Date where id = (@Id +1)))
left join Table1 n on
n.[ID] = T1.[ID] and n.[ID] not in (select [ID] from Table1 where [Month/Year] = (select [month/year] from Temp_Date where id = (@Id -1)))

where t1.[Month/Year] = @my
group by t1.[Brand], t1.[Month/Year]

Which although it produced an output, I don't think it was right and it took ages, probably due to my liberal use of Sub-Queries in Joins.
My question is 2-fold - can someone assist in a better way to count the number of rows and join them and is there a better way to do this without so many sub-queries?

In general you should include the scripts that generate your Table schemas and populates them with data. (Also for performance questions, you should include the execution plan too.) I couldn't do a lot of testing without real data, but I believe this query should improve the performance you're seeing:
DECLARE @my DATE = '2019-12-01'
DECLARE @nextMonthYear DATE = DATEADD(MONTH, 1, @my)
DECLARE @prevMonthYear DATE = DATEADD(MONTH, -1, @my)

SELECT CurrentMonth.Brand, SUM(CASE WHEN NextMonth.ID IS NULL THEN 1 ELSE 0 END) AS Churn, SUM(CASE WHEN PreviousMonth.ID IS NULL THEN 1 ELSE 0 END) AS New, CurrentMonth.MonthYear
FROM Table1 AS CurrentMonth
LEFT JOIN Table1 AS NextMonth
    ON NextMonth.MonthYear = @nextMonthYear
    AND CurrentMonth.ID = NextMonth.ID
LEFT JOIN Table1 AS PreviousMonth
    ON PreviousMonth.MonthYear = @prevMonthYear
    AND CurrentMonth.ID = PreviousMonth.ID
WHERE CurrentMonth.MonthYear = @my
GROUP BY CurrentMonth.Brand, CurrentMonth.MonthYear

This also is a lot simpler (both for the developer to read and for SQL Server to parse) then a bunch of sub-queries. You're likely right that the unneeded additional joining to the sub-queries is a source of your performance issues (but I can't verify that without seeing the execution plan).
What the above query does is use a LEFT JOIN to Table1 on itself twice (once for the PreviousMonth and once for the NextMonth) by ID to get all the records that match and don't match to the CurrentMonth. Then using the SUM() aggregate function with a CASE statement inside of it, we can sum up only the NULLs (cases where the ID of the CurrentMonth did not exist in the PreviousMonth and NextMonth) to get the counts for the New and Churn, grouped by the Brand and MonthYear of the CurrentMonth.


Restore large MySQL dump thorugh EasyPHP I am trying to upload large files on easyphp but as there is limitation on this software and large databases cannot be uploaded. Is there some other software that allows to import large databases? Please help.

You can use MySql Workbench for managing your remote database

here is the link for downloading MySql Workbench

http://dev.mysql.com/downloads/tools/workbench/


Database Design - One table from many source(One to One) I have a 'Text' table, the text table may came from different source, like Tweet, SMS, Email, etc. and each source have their own log, and Text is came from the source. so i came with some solution, but i don't know which is the best.

Solution 1 :

Text(
text_id PK
content
source_type (tweet, sms, email, etc)
source_id (can't be FK since it may contain sms_id, tweet_id, or email_id)
)

Tweet(
tweet_id PK
tweet_text
tweet_username
etc...
)

Sms(
sms_id PK
sms_text
sender_number
etc...
)

Email(
email_id PK
email_text
email_sender
etc...
)

Solution 2 :

Text(
text_id PK
content
sms_id FK
tweet_id FK
email_id FK
)

Tweet(
tweet_id PK
tweet_text
tweet_username
etc...
)

Sms(
sms_id PK
sms_text
sender_number
etc...
)

Email(
email_id PK
email_text
email_sender
etc...
)

Please note that one 'Text' only came from one source.

so if I use solution 2, let say the source is came from sms then the data will look like this

Text(
12
'the text'
333
null
null
)

Sms(
333
'the text'
0818833733664
etc...
)

so which is the best approach ?

UPDATE :
I miss a big part on my question, and that's are may fault, sorry for that. In my case the Text also may came from a csv(excell) file. which mean one file may have many text, because the file is a dump for many text(coma or row separated). SMS, Tweet, and Email relation with Text is one to one, but File with Text is One to Many
so the big missing part in my question is the File Table

File(
file_id PK
file_name
etc..
)

I do not like both of your solutions.

Solution 1: Storing different things at the same place is a bad idea. Storing  foreign key to different tables in the same field is worse.
Solution 2: this is also no a good idea. You get a lot of NULL values.
The best thing is to draw a picture.

My prefered solution is that of @Joel Brown (with some minor changes) Instead of 'text' i woul call the supertype 'message' and it should not only hold the content but the all fields (and references) common to all message types.

message(
  message_id PK
  content
  common attributes ...
)

Tweet(
  message_id PK, FK
  tweet_username
  tweet attributes
)

Sms(
  message_id PK, FK
  sender_number
  sms attributes ...
)

Email(
  message_id PK, FK
  email_sender
  email attributes ...
)

The implementation is straight forward: each entity is a table. the superentity has a primary key. this primary key is used for the subentities too.
If you want model your system with an entity text that has relationships to entities sms, email and tweet as described in your post, you get the following EER diagram

I don't like this model in your special case but there are other situation where such a model is appropriate.

There is a standard way to transform such 1:1-entities  to tables. we have three relations sends, mails, tweets which have an optional entity (SMS,Email,Tweet) and a mandatory Entity (Text). In this case the foreign key is stored in the table of the optional entity. (You did it the wrong way) So you get the following tables

text(
  text_id PK
  content
  other attributes ...
)

Tweet(
  tweet_id PK
  text_id FK
  tweet_username
  tweet attributes
)

Sms(
  sms_id PK
  text_id FK
  sender_number
  sms attributes ...
)

Email(
  email_id PK
  text_id FK
  email_sender
  email attributes ...
)

Using Extended Entity Relationship Diagrams (and maybe any other modelling method) makes it almost
 impossible to create without cheating such solutions that you have proposed. Diagrams can be drawn with pencil and paper. The diagrams
in this post  are created with DIA. The Extended Entity Relationship Model and how to transfer it into tables can be found in  T.J.Teorey, D.Yang,J.P.Fry:
A Logical Design Methodology for Relational Databases Using the Extended Entity-Relationship Model


MySQL How to calculate checkpoint age? According to the Percona article MySQL checkpoint age may freeze it:
https://www.percona.com/blog/2011/04/04/innodb-flushing-theory-and-solutions/
How can I calculate it?
How can I ensure that innodb_log_file_size of 134 mb is enough for me or I need to increase it?
This is the section LOG from SHOW ENGINE INNODB STATUS, but what is the correct formula?
( and how do I get megabytes to compare with innodb_log_file_size )
---
LOG
---
Log sequence number          26361002986468
Log buffer assigned up to    26361002986468
Log buffer completed up to   26361002986148
Log written up to            26361002986148
Log flushed up to            26361002902700
Added dirty pages up to      26361002986148
Pages flushed up to          26360959010673
Last checkpoint at           26360958543063
7934296064 log i/o's done, 669.00 log i/o's/second

Thanks.
P.S  We are thinking should we increase innodb_capacity_io from default 200 to, lets say, 500 or 1000. ( we have enough iops available )

Those sequence numbers are necessarily large, monotonically increasing, numbers.  They are not byte offsets into the log.  You may find the number in the log, of you have fun with hex dumps.  (I don't.)
That Percona blog, while good, is a decade old.  A large log no longer delays restart as much as it once did.
Consider this for judging whether to increase (or decrease) the log_file_size:
Minutes between InnoDB log rotations = Uptime / 60 * innodb_log_file_size / Innodb_os_log_written
60 minutes is a good target, but it is not a precise target.
Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.
innodb_log_files_in_group traditionally has been 2.  I suggest you use 2 unless you are on a new version of MariaDB, where it is no longer needed.


Efficient approach to Query DB with data in text file I've got an oracle DB set up and want to Query it after I receive data in a text file. Number of product-names present in text file are around a million.

After Searching a lot I've found 2 ways of doing same but I'm not sure which'll be more efficient.

Use SQL loader to load data in a table then query using where in or a join
Query DB for each product name in loop.

The second option is a consideration just because I think insert(using loader)+select will be more expensive than only selects.

P.S.: I would prefer less load on DB server rather than less time.

File contents are something like:

ABC
BCD
FGS
LTB
.
.
NHL

I've to query db as

Option 1:

Run a sqlloader on file products.txt and load contents into TMPTABLE.
SELECT PRICE FROM PRICEMAP WHERE PRODUCT_NAME IN (SELECT PRODUCT_NAME FROM TMPTABLE);

Option 2:

Foreach $productname in products.txt:
    SELECT PRICE FROM PRICEMAP WHERE PRODUCT_NAME IN=$productname

Every server is different, but you might be surprised how quickly you can query through a million rows. Are you inserting the data into a table, or are you just trying to query against it?

If you're just trying to query against the file but don't need to store the data in the database, you could insert it into a Temp Table and run your queries against it.

If you're inserting the file into a table I would go with your first option. One query is going to cause less load on the server than running 1 million queries (if I understood you correctly). Without knowing your query I can't help with performance/load. It may not be applicable, but a UNION is typically faster than a JOIN. Just something else to keep in mind.

(Sorry for the answer and not a comment, I don't have the rep yet)


"Could not find stored procedure' even though the stored procedure have been created in MS SQL Server Management Studio I have created a table testtable inside the database testbase that have the following structure:

product_no (int, not null)
product_name (varchar(30), not null)
price (money, null)
expire_date (date, null)
expire_time (time(7), null)

which I used the Microsoft SQL Server 2008 Management Studio.

I created a stored procedure testtable_pricesmaller as follows

use testbase
go
create procedure testtable_pricesmaller
    @pricelimit money
as
select * from testtable where price = @pricelimit;
go

and are able to view the Stored Procedures on the Object Explorer of the Microsoft SQL Server Management Studio. (It is listed in the following tree structure of the Object Explorer)

Databases
    + testbase
        + Tables
            + dbo.testtable
        + Programmability
            + Stored Procedures
                + dbo.testtable_pricesmaller

I find it very strange when I receive the following error:

Could not find the stored procedure 'dbo.testtable_pricesmaller'.

when I execute the following SQL statement:

execute dbo.testtable_pricesmaller 50

What could it be missing?

IntelliSense Refresh local Cache should fix it


Diagnosing the error in a Postgresql to Redshift migration through psycopg2 I am running a job class that contains the following:

A Postgresql connection that can issue SQL statements
A Redshift connection that can do the same
an S3 connection to function as an intermediary between the two

The current process I am taking is dealing with the columns with redshifts differences on a per-case basis. The types of columns used (disregarding duplicate types, can give the rest if needed) are:

 id                             integer          NOT NULL,
 client_id                      integer          NOT NULL,
 manual_dt                      date,
 scheduled_at                   timestamp,
 some_other_id                  varchar(255),
 is_good                        integer          DEFAULT 0 NOT NULL,
 url                            varchar(4096)    NOT NULL,
 image_width                    integer,
 image_dim_ratio                float8,
 invalid_reason                 varchar(256)     DEFAULT NULL::character varying

There are a total of ~2.3 million records that need to be copied in this initial load. I am using this as a SELECT and COPY on postgresql, uploading it to S3, then using Redshift's COPY to get it from the s3 source. All of this can be posted if needed too.

This works on loads of 100, 1000, 10000, 100000, and 1000000 records. But, if I go to the entire set or a limit of the exact number of records, I receive the following trace:

psycopg2.extensions.TransactionRollbackError: canceling statement due to conflict with recovery

DETAIL:  User query might have needed to see row versions that must be removed.

Is this the source of what is stopping the copy, or am I on the wrong track and it is another issue?

This is because you are querying a postgresql replica that doesn't allow queries to run for very long.  Your only solution is to ask the database administrator to increase the query limit on that replica, or hit production instead.