MySQL returns floats rounded > CREATE TABLE test(foo FLOAT);
> INSERT INTO test VALUES(1.2899999);
> SELECT * FROM test;
+------+
| foo  |
+------+
| 1.29 |
+------+

While it's obvious that FLOAT is not a precise data type, it is still rounding this data too arbitrarily. Let's check if it's actually able to store it:

> ALTER TABLE test MODIFY foo DOUBLE;
> SELECT * FROM test;
+--------------------+
| foo                |
+--------------------+
| 1.2899998426437378 |
+--------------------+

Whoops, only the 7th digit got corrupted due format limitations. Is there a way to prevent overzealous rounding in this situation, short of converting the column to DOUBLE?

I hate precision-based issues. I dealt with one before: Data Truncated for Column

You may have do the ALTER TABLE manually

CREATE TABLE test_new LIKE test;
ALTER TABLE test_new MODIFY foo DOUBLE;
INSERT INTO test_new (foo) SELECT CONVERT(foo,DOUBLE) FROM test;
RENAME TABLE test TO test_old,test_new TO test;
DROP TABLE test_old;

or

CREATE TABLE test_new LIKE test;
ALTER TABLE test_new MODIFY foo DOUBLE;
INSERT INTO test_new (foo) SELECT CONVERT(foo,DECIMAL(10,7)) FROM test;
RENAME TABLE test TO test_old,test_new TO test;
DROP TABLE test_old;

or

CREATE TABLE test_new LIKE test;
ALTER TABLE test_new MODIFY foo DOUBLE;
INSERT INTO test_new (foo) SELECT FORMAT(foo,7) FROM test;
RENAME TABLE test TO test_old,test_new TO test;
DROP TABLE test_old;

Not sure what will happen, but give it a try and see what happens.


Need help in putting the single quote and constant timestamp in plsql Please help me in appending the Single quotes and constant timestamp to my query output. Given below is the expected output from my param file.

Note: Query is executed through sqlplus

Expected output in spool file(params.txt):

'12-DEC-2014'
'15-DEC-2014 23:59:59'

Script:

spool C:\output\params.txt
SET SERVEROUTPUT ON;
variable from_date varchar2(30);
variable to_date varchar2(30);
set verify off
set feedback off
set trimspool on
set linesize 1000
set term off
set colsep ,
set pagesize 0
set heading on
set underline off

begin
  :From_date := '&1';
  :To_date := '&2';
end; /

select to_date(:From_date,'dd-mon-yyyy') from Scehma.table1;
select to_date(:To_date, 'dd-mon-yyyy') from Scehma.table1;
exit 0;
exit

To add single quotes in a string literal, just double it, or use the chr function.

E.g. 'hello '' world' represents the string hello ' world. 'hello '||char(39)||' world' is the same. '''' is a string literal representing a single single-quote.

To get your fixed end time, just concatenate it.

select ''''||to_date(:From_date,'dd-mon-yyyy')||'''' from Scehma.table1;
select ''''||to_date(:To_date,  'dd-mon-yyyy')||' 23:59:59''' from Scehma.table1;


Insertion of data violates the foreign key constraint Consider I have two tables Employee and Department with the fields given below:

Employee:

Fname, Minit, Lname, Ssn Bdate, Address, Sex, Salary, Super_ssn, Dno

Department:

Dname, Dnumber, Mgr_ssn, Mgr_start_date

Italics and code block represent the primary key of table.

In above case, Super_Ssn references the SSN of employee, while Dno references the Department's Dnumber.
Creating the sql for them is pretty straightforward by adding columns first and altering them later when both tables are formed. However, while inserting the data like given below:

insert into employee values('James','E','Borg','888665555','1937-11-10','asda','M',55000,NULL,1);
ERROR:  insert or update on table "employee" violates foreign key constraint "dno_ref_dnum_dpt"
DETAIL:  Key (dno)=(1) is not present in table "department".

and

insert into sp0090.department values ('Headquarter',1,'888665555','1981-06-19');
ERROR:  insert or update on table "department" violates foreign key constraint "department_mgr_ssn_fkey"
DETAIL:  Key (mgr_ssn)=(888665555) is not present in table "employee".

I am getting the foreign key constraint violated. This can be traced because to insert Mgr_ssn in department, it has to exists in Employee and to insert dno in Employee it has to exists in Department. However, I am required to fill the tables with maintaining the schema as it is. How do I solve this circular referencing? Thanks.

In Postgres, there are two ways to circumvent the chicken-and-egg problem of inserting into tables with circular references.

a) Defer one of the constraints, either declaring it as DEFERRABLE INITIALLY DEFERRED when created or as DEFERRABLE as created and then defer it when needed.

For an example of using deferrable constraints, see my answer in this question:
  Implementation of a many-to-many relationship with total participation constraints in SQL.

b) Use a single statement to insert into the two (or more) tables, using modifying CTEs. Example for your case:

with
  insert_emp as
  ( insert into employee
        (Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
    values
        ('James', 'E', 'Borg', '888665555', '1937-11-10', 'asda', 'M', 55000, NULL, 1)
    returning
        *
  ),
  insert_dep
  ( insert into sp0090.department
        (Dname, Dnumber, Mgr_ssn, Mgr_start_date)
    values
        ('Headquarter', 1, '888665555', '1981-06-19')
    returning
        *
  )
select *
from insert_emp as e
  full join insert_dep as d
  on  e.dno = d.dnumber
  and e.ssn = d.mgr_ssn ;


HowTo parsed Stored Procs to get a list of tables and columns referenced in WHERE, GROUP BY, AND JOIN clauses Is there a tool, script, utility that can parse sys.sql_modules and return a list of tables and columns referenced in the WHERE, GROUP BY, and JOIN clauses of Stored Procedures?
Example:

CREATE PROC myProc
BEGIN
    SELECT a.col1, b.col2, count(a.col7) as MyCount
    FROM dbo.FirstTable as a
    JOIN dbo.SecondTable as b on (a.col1 = b.col1)
    WHERE b.col5 = 'xxx'
    GROUP BY a.col1,b.col2
END

Return a list such as:

FirstTable.col1
SecondTable.col2
SecondTable.col5
FirstTable.col1
SecondTable.col2

The goal is to use the result list to build some CREATE STATS commands based on the number of times a table.col pair is referenced.

sysdepends & sys.sql_modules have some known limitations... and you probably don't want to perform string-parsing gymnastics if you want to rely on your results with a high level of confidence. Although neither of these options is really something I would recommend as an answer to your problem.

  The goal is to use the result list to build some CREATE STATS commands based on the number of times a table.col pair is referenced.

Perhaps you've got a highly targeted need to do exactly this, but my first reaction is that you might want to take a more holistic approach to your stats maintenance than reviewing the text inside stored procedures. You may have an heavy ad-hoc query load that doesn't ever touch a stored procedure. You may have 10 stored procs that reference all the things but are never called and 1 stored proc that touches less but is called all the time. I recommend taking a step back and browsing some literature before taking the path you seem to be steering toward with this question.


Use of Non clustered Indexes I know the differences between clustered and non-clustered indexes. Can someone tell me the various scenarios where we actually prefer non clustered index over a clustered index ?

If a table is large and growing fast, clustered index might become too expensive to maintain since the DB server has to reshuffle all the data while rebalancing the tree, not only nodes with key values. It might significantly affect speed of data modification queries.
Clustered index stores table data right in the index (a tree node contains the whole record, not only the key). So when you rebalance the tree of this index, you also have to physically move records. In comparison, non-clustered indexes store trees separately from the main data and only use pointers to physical placement of corresponding records. So they are much cheaper to rebalance.

For most DBMS (TokuDB for MySQL/MariaDB/Percona) is an exception) there can be only one clustered index per table because it defines the physical data placement. So, if you need several indexes on a table, you have to use non-clustered ones.


How to restore a database with innodb tables from files? I am trying to restore a MariaDB database containing innodb tables from files. Blue-eyed as I am, I considered this an easy task: I just stopped the server, copied the files and restarted it as follows:
# Stop MariaDB server
systemctl stop mariadb.service
# Copied database
sudo rsync --numeric-ids -aHAEXv --rsh="ssh" user@mybackupserver:/xyz/mysql/owncloud /var/lib/
# Start MariaDB server
systemctl start mariadb.service

Unfortunately this does not work as expected. Although the database and all it's tables are there, I can't access any table. Trying to do so I get:

1932 - Table 'owncloud.oc_activity_mq' doesn't exist in engine.

I than tried to create an empty database of the same name before I repeated all steps above, but I got the same error.
Oddly in PHPMyAdmin all tables have the Collation  in use.
I checked permissions and ownership of the directory a dozen times, everything within my MariaDB data directory is owned by the user mysql and every file has the permission 0660 every directory 0700…
My question is, is it possible to restore a single database with innodb tables directly from an old data directory to a new one? And if it is, how?
I know this question came up before, for example here: How to restore MySQL database from Files
But I am asking here specifically for a database with innodb engine, as off what I read, I got the impression the problems I ran in, might have to do with the engine

With innodb files you need to restore the entire database server to a new instance.Use mysqldump oc_activity_mq to extract a SQL version of the table out of that backup. And then import this into your active instance.

This assumes you've made consistent backup. If mysql was running and being updated at the same time it was copied it might have some form of corruption.

Revist your backup strategy using innodbbackup, lvm, and/or mysqldump/pump.


Fill percentage of total field conditional on some other field with MS Access SQL This question is an extension of Fill percentage of total field with MS Access SQL.

I have the following 2013 MS Access SQL query:

UPDATE MyTable
SET F = f / DSUM("f", "MyTable") ;

This query fills field F with the percentage over the total of some field f, such that each one of the records F(i) in field F is given by:

F(i) = f(i)/SUM_i(f(i))

Both fields f and F belong to table MyTable.

Now, I would like, instead of simply computing the percentage over the whole table, to do it by Date, where Date is a 3rd field also to be found in table MyTable. I will make up an example for illustration purposes. Let's assume a simplified MyTable with 2 records per date; then that's what I would like MyTable to look like after executing my desired query... - see fields f and F:

|    Date    | Department |     f     |     F     |
|------------|------------|-----------|-----------|
|     t1     |     D1     |     2     |    40%    |
|     t1     |     D2     |     3     |    60%    |
|     t2     |     D1     |     1     |    20%    |
|     t2     |     D2     |     4     |    80%    |

... And this is how it is looking right now, after executing the query displayed above:

|    Date    | Department |     f     |     F     |
|------------|------------|-----------|-----------|
|     t1     |     D1     |     2     |    20%    |
|     t1     |     D2     |     3     |    30%    |
|     t2     |     D1     |     1     |    10%    |
|     t2     |     D2     |     4     |    40%    |

I have tried the following code, but none yielded what I wanted.

Try #1:

UPDATE MyTable
SET F = f / DSUM("f", "MyTable", "Date");

Try #2:

UPDATE MyTable
SET F = f / DSUM("f", "MyTable", "Date=Date");

Any ideas on how I could manage to do this?

Note: I have just called the two fields "f" and "F" here for illustration purposes, it is not how they are named in the actual DB.

I will add here further tests that haven't worked out:

Try #3:

UPDATE MyTable
SET F = f / DSUM("f", "MyTable", "Date=" & Date);

Try #4: this has worked but only for a single Date, the 1st one to be recorded - i.e. the one in the 1st row/record of the table MyTable - for the rest of dates the value for F is empty.

UPDATE MyTable
SET F = f / DSUM("f", "MyTable", "Date=#" & Date & "#");

Your last UPDATE statement should work. Access is often a bit crap though so although it should work, it probably won't. Thanks Microsoft Access dev team (probably more specifically the updates to JET).

Unfortunately, the only way I can recommend is to create a temp table based on a query which does a GROUP BY on the Date column you are attempting to aggregate. So create a query first like this:

SELECT MyTable.Date, Sum(MyTable.f) AS SumOfVal INTO temp_DateSums
FROM MyTable
GROUP BY MyTable.Date;

Then create another query which joins to this new table and performs its update based on it. This second query would look like:

UPDATE MyTable
INNER JOIN temp_DateSums ON MyTable.Date = temp_DateSums.DateDate
SET MyTable.F = [MyTable].[f]/[temp_DateSums].[SumOfVal];

This should update the column you need with the right value, albeit running 2 queries instead of one. I can't think of another way around it running a single query where you wouldn't get the dreaded "Operation must use an updatable query" option, that doesn't use VBA. Again, your DSUM should work, but DSUM doesn't seem to want to work the way it was designed anymore...


How can I ensure the user can insert and edit data while updating table I am designing schema. Any idea is welcome!
Right now I have tableA, tableA_cache, tableB, tableC, tableD. If any row insert to tableB tableC or tableD, the trigger will insert data to tableA.
For data integrity, periodically I have to delete all the rows in tableA then re-populate data from tableB, tableC...etc.
Normally, user query tableA_cache so that even if tableA has no data, user can query tableA_cache. However, during re-population, tableA will be keep writing. At the same time, if user insert a row in tableB (or C, D), the trigger should insert data to tableA. Since tableA is locked, the response time for insert will be extremely slow.
Is there a better way to handle this case? or a direction I can look into. (There is no guarantee that in specific timing no user will use the system.)

Get rid of all the complex design.

Plan A:  Get rid of A and A_cache and the trigger and the rebuild; run queries against B and C.  Perhaps you need better indexes?  Or "Summary tables"?

Plan B:  (More directly in line with your design.)  Get rid of A_cache. Do this, which will avoid A ever being unreachable:

Create A_new from B and C
RENAME TABLE A TO A_old, A_new TO A;  -- atomic and instantaneous
DROP TABLE A_old;


Connecting to database using Toad in windows I am trying to connect to a database using Toad on windows 7.

But I am not able to connect because I dont have SQLNET Editor, TNSNames Editor and LDAP Editor installed.

Please help me with this. I am not able to find out in which package these 3 come in.

I have Oracle network client, SQL Client 2008 and SQLite Expert Professional installed.

What extra I need to install?

It's not clear from your question what brand of database you are connecting to.  I will assume it's Oracle.
You need this information:

name of the server the database is on
name of the database  --sometimes called a service name
is the database parameter db_domain configured with a value?  (usually the domain name)
the port the database listener is using (usually port 1521)
a user and password, be good and don't connect as sys

Let's try the easiest way, connecting directly

open toad
select session/new connection from the menu
select the "Direct" tab from the right side and enter

User and password
Host = machine name of the database server
Port = 1521
Service Name = Name of the database

connect as normal if you are not connecting as sys
ensure that the Installed clients picks up the correct install of your Oracle client

and....press connect.

This is the easiest way.  More involved is adding the name of the database to the TNSNames in your oracle client.
Another good check is to log onto the server where the database is and start a DOS command line window.  Enter lsnrctl status  .  This will give you the database name and port and if the listener is ready to receive connections.


Summing a column based on uniqueness of another column without using temporary table I have a table like this:

CREATE TABLE `files` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `hash` char(40) DEFAULT NULL,
  `size` bigint(20) unsigned DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `ndx_hash` (`hash`),
  KEY `hash_and_size_ndx` (`hash`,`size`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

This table has files that contains same (hash, size) with different name in it.

It should be noted that two file records containing the same hash will have the same size.

What I'm looking for is the total size of unique files in this table. Uniqueness is based on hash column.

Here is a query that provides what I want:

SELECT sum(t.unique_size) FROM (SELECT MIN(size) as unique_size FROM files GROUP BY hash) AS t;

Output is:

+--------------------+
| sum(t.unique_size) |
+--------------------+
|           64233241 |
+--------------------+

The reason I don't like this query is because its slow. Its slow because it creates a temporary table. My table has more than 5m records in it.

I think it is a waste to create a giant temporary table since it is theoretically possible to allocate one integer field and increment it as the unique_size's (see my query) are generated.

Is there a way to get what I want without creating a temporary table?

Note: I've experimented WITH ROLLUP and found no use of it.

I would suggest normalizing data and splitting the table into the following two tables:

CREATE TABLE `file_data` (
`data_id` bigint(20) NOT NULL AUTO_INCREMENT,
`hash` char(40) DEFAULT NULL,
`size` bigint(20) unsigned DEFAULT '0',
PRIMARY KEY (`data_id`),
UNIQUE (`hash`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `file_names` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`data_id` bigint(20) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (data_id) REFERENCES file_data(data_id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

This eliminates the possibility of inconsistency between hash and size fields, and the total size of all (unique) files can be obtained with the query:

select sum(size) from file_data;

If you need the original (denormalized) structure, you can perform a simple join:

select id, name, hash, size
from file_names n join file_data d on d.data_id = n.data_id;


Case sensitive Oracle 11g At work we have 11g and the boss told me to make passwords case sensitive, but that's where I'm going, he doesn't want to set the SEC_CASE_SENSITIVE_LOGON to true in the production environment and he told me to find a way to make it through the profiles of the Oracle Database System or another way.

Is there actually a way to make case sensitive passwords through profile settings or another way?

What i believe is that if you don't switch the parameter of SEC_CASE_SENSITIVE_LOGON there is no way the database would let you make them, since this parameter is from the database system itself.

Also from what I've been searching the PROFILE doesn't have any of these functions.

Yep, it's really strange why SEC_CASE_SENSITIVE_LOGON should not be used for this if it's especially designed for the purpose.
I can suggest in this case maybe just use some external authorization method like Oracle Identity Management, e.g.. It's much more complex and require additional infrastructure installed/configured and maintained, but users' credentials will be stored and controlled (including case-sensitive passwords) outside of the database and becomes independent on the database parameter value.


MongoDB/Mongochef: import from JSON changed dates to string I have a huge JSON file (about 5 GB) with several fields in all rows.
this is what one document looks like:

{
    "_id" : ObjectId("55f0105346c5110dfc6b8760"),
    "patient" : "198",
    "concept" : "Ampicillin",
    "confidence" : NumberInt(100),
    "value" : 1.0,
    "time" : ISODate("2007-06-27T17:24:00.000+0000"),
    "stay" : "20220"
}

This is data from hospital stays.
For some reason, when I imported the JSON file to MongoDB (using mongoimport in linux command line), the 'time' field was saved not as ISO-date, but as a string.
I figured - "no problem" and using mongo-chef altered the field to be of "Date" type using the 'Edit Value' option. I then picked the override option for all documents matching criteria.

What happened then is that the entire collection got the exact same value in the "time" column.

So my question is - either - (A) how can I import it correctly, where mongoimport inserts the "time" as ISOdate, OR, (B) how can I correctly change it in mongochef, after the import.
This is time sensitive so PLEASE if anyone has any idea - help!

Thanks!

All right. After digging a LOT I found the correct formatting is:

time: {$date:"2013-11-20T23:32:18Z"}

So I used 'sed' to change it throughout the json file and imported it.

I'm leaving this answer for the benefit of anyone who might come across this problem.


As there is no unsigned int in SQL Server doesn't an Identity Seed of -2,147,483,648 make more sense for large tables? I just had a showerthought that the default Identity Seed is 1. I have some tables that I know will grow to the billions at a certain point. Wouldn't it make more sense to start on int.Min ( -2,147,483,648) for these tables?
This could just make the difference of migrating your key to bigint in 4 years or in 8 years. Can be relevant enough.
Is this common? It feels weird. Is there anything I am missing?

There is nothing at all wrong with starting at -2,147,483,648 as far as SQL Server is concerned. Starting at 2,147,483,647 and counting backwards with IDENTITY(2147483647,-1) is perfectly valid too.
Things that would make me be wary of doing so:

It might confuse people who don't expect to see negative values in such positions. It is unusual enough that it could easily look like bug. If nothing else you might get sick of explaining it.
If you ever pass the IDs to other services for any reason they may fail input validation do to that code not expecting to see negatives (an external system shouldn't care about your internal IDs like this, but there are many things that happen which shouldn't!). Furthermore, code in other layers of your application might use an unsigned int32 making the "down from int.max" option even more dangerous as you'll not hit the problem until dropping below 0.
People sometimes use negative magic numbers to have special meaning, and this could cause collisions which lead to difficult to explain bugs. For instance shortening WHERE (x.a<>y.a OR x.a IS NULL AND y.a IS NOT NULL OR x.a IS NOT NULL AND y.a IS NULL) to WHERE ISNULL(x.a,-1)<>ISNULL(x.a,-2) is something I've seen numerous times. Not likely for your PK as that'll never be NULL but might happen in comparing FK values in other tables. I've even seen someone use somevalue>-1 in place of somevalue IS NOT NULL (apparently there is a circumstance where that is more efficient, though he never explained to my satisfaction what the circumstance might be!). There might be that and other odd shenanigans in code outside the DB too.
Most importantly: scale, particularly unexpected scale. Software and data that doesn't die early often outlives their creators' vision, and grows more over time accordingly. Unless you are very constrained by storage or RAM (perhaps in an embedded system) then plan for at least one order of magnitude higher than you expect.
If I expect hitting ~400,000,000 is ever going to be likely in the lifetime of the data, far before the difference between 2 or 4 000,000,000 is a consideration, I'm already going to go for a larger key so doubling up by using negative values isn't going to make much difference.
You don't want to be making a change to something core like a PK in four years time, but you even less want to do it in eight years time. In both cases if the design lasts that long you've long forgotten details and many other bits & bobs have started to depend on the key so the changes needing to be made grow massively, and even that single table is going to be massive work to change (unless most of the data gets deleted after a time) as it contains that many rows, then you have all those that refer to it with FKs to work on also.


How would I move SQL MSSQL.1 and MSSQL.2 location? We have a SQL instance where the MSSQL.1 and MSSQL.2 were installed on a SAN drive. We are moving away from this SAN do to faults and we wish to move the files and folders to a new SAN. How would we do this?

All DB datafiles have been moved and are up and running again, I just want to know if it is necessary to move these over:

First shut down sql server

Then the best option is to robocopy everything from the disk to the new disk with the existing security.

when this is done, remove the old disk, and give the drive letter of that disk to the disk which you robocopied to. After that start SQL Server again.

This way SQL Server will not notice they are now on a new disk.


Associating on the ID of an association table Sorry about the title, I couldn't find anything better. Suggestions are welcome.

Say we have two tables, Suppliers and Products.
The same product can come from many different suppliers, so we create a third table, let's call it SuppliersAndProducts, with this schema

SuppliersAndProducts

  - id (autoincrement)
  - supplier_id
  - product_id
  - from_date
  - to_date

from_ and to_date are there to account for the fact that a given supplier might well stop selling a given product, but could also begin selling it again at some point in the future.

Now, we want to store the price we pay when we buy a Product from a given Supplier. Prices, of course, change over time, and we want to keep a track of that too.

So we introduce a table, let's call it SuppliersAndPrices, structured like so:

SuppliersAndPrices

  - id (autoincrement)
  - supplier_id (FK to the supplier id)
  - product_id (FK to the product id)
  - price
  - from_date
  - to_date

My question about this last table is more conceptual than anything else: should this table be like I described (meaning that the association in based on both the supplier and product IDs) or instead just reference the id from SuppliersAndProducts with a column that we could call suppliers_and_products_id?

The latter design is more normalized than the former; after all, the association between a Supplier and a Product is already stated in SuppliersAndProducts, so there would be little point in repeating that information. Still, at least to me, for some reason the former feels closer to the real world.

From the point of view of query complexity, to know the price I'm paying today for a given product, with the first design I would have to write (with some pseudo-SQL to remain db-agnostic) something like:

SELECT price
FROM   SuppliersAndPrices
WHERE  supplier_id = X
       AND product_id = Y
       AND today is between from_date and to_date

whereas the second design would entail a join, thus making the query look like:

SELECT price
FROM   SuppliersAndPrices
       INNER JOIN SuppliersAndProducts
               ON SuppliersAndPrices.suppliers_and_products_id = SuppliersAndProducts.id
WHERE  SuppliersAndProducts.supplier_id = X
       AND SuppliersAndProducts.product_id = Y
       AND today is between SuppliersAndPrices.from_date and SuppliersAndPrices.to_date

I'm writing this off the top of my head, sorry about my SQL.

Also, now that I think about it, with the second design I would have to add another condition to the WHERE clause to check the dates in SuppliersAndProducts too, to handle the case in which a Supplier will stop carrying a product and, at some point, begin selling it again. In that case the join condition would return more than one row, and that would be Not A Good Thing™.

So, which one would you choose? Associating on the id of what already is an association in the name of greater normalization but at the price of increasing query complexity? Denormalizing a tiny bit to make querying easier and, arguably, the database structure clearer for future maintainers?

The query for the second design would probably be hidden behind a view, but that's still code that has to be maintained and understood. What I really want to know is which one of the two design is "better", for some definition of "better".

Just to get this part out of the way, what is the intention of keeping the historical data? If it is for the app to allow users to interact with on some level, then ok, as that is still a transactional need. Else, if it is merely for reporting purposes, that could be tracked entirely in a separate server / database / schema as you are really talking about a Slowly Changing Dimension (SCD). If it only exists for reporting then no need to complicate your transactional model with anything but the current info.

That being said, I will assume the data is needed for transactional purposes, in which case there is no question (in my mind, at least :) that Option 2 (i.e. relating the Price info with the Supplier and Product relationship table) is the only way to go. If you relate the Price properties back to their respective sources then you allow for invalid combinations of Suppliers and Products that don't exist in the SuppliersAndProducts table. And while performance and maintainability are very important considerations when designing, they are secondary to data integrity as that is the primary responsibility of the database.

Some notes:

It is better to use the table name + "id" rather than the generic "id". This will make writing queries a lot easier as the field name will be the same between tables. Queries in general will be more readable.
While I also like naming tables as plural (it just sounds better), it does make things easier to use the singular. This way the "id" field of the table includes the table name without sounding odd, such as "SuppliersID" (as opposed to "SupplierID"). If you ever have to code automated processes against the tables, it is much easier to assume that the ID field is simply "{TableName}ID", else you might need to hit the database to do a look up. And for new people being trained it is just easier to know that any table will have a standard ID field name, which makes writing queries faster. This is why I no longer use plural table names :). So, for example, SuppliersAndProduct would be SupplierAndProduct or maybe even SupplierXProduct.
If it weren't for the historical values then SupplierAndProduct wouldn't need an auto-incrementing ID as the combination of SupplierID and ProductID would be the composite PK.
But since we do have that history we can do two things with that relationship table:

PK is the SupplierAndProductID field
Unique Index (representing an "Alternate Key") on the combination of the SupplierAndProductID, SupplierID, and ProductID fields

For the SupplierAndProductPrice (or SupplierXProductPrice) table we would have (just in terms of the Key fields here; no changes to the "price" and date, etc fields):

- supplier_and_product_price_id (autoincrement) -- I will stop PascalCasing now ;-)
- supplier_id (FK to supplier_and_product; could also FK to supplier)
- product_id (FK to supplier_and_product; could also FK to product)
- supplier_and_product_id (FK to supplier_and_product)

In this model, since the supplier_and_product_id field lacks any real meaning, we bring along the other two fields and the combination of all 3 will relate back to the Unique Index on supplier_and_product. I generally try to avoid FKs to Unique Indexes/Constraints but this is one situation where it makes sense to do it.

Putting all 3 fields together in a Unique Index in order to reference the FK to it allows for bringing over the two needed fields (supplier_id and product_id) while guaranteeing that the combination of those two is always a valid combination (as it is enforced by the FK).
I would recommend against combining these into a single table as it duplicates relationship info of Product and Supplier when the Price changes during the from and to dates of the Product-to-Supplier relationship. But, that is just for the transactional side. On the reporting side it is definitely a good idea to combine them as suggested by @JonofAllTrades.
Regarding the need for the extra WHERE condition to check the date against the from and to dates in supplier_and_product: this is not a bad idea, but is technically just a safe guard, assuming that the app doesn't allow a given Price entry to have dates that fall outside of the time-frame that the Product is being supplied by that Supplier. But if the dates are somewhat validate against such oddities, then you can now get away with the simplified query of Option 1 (since the product_id and supplier_id fields are there) while having the integrity of Option 2 (since those fields also FK back to the supplier_and_product relationship table). Which means you really should FK the product_id and supplier_id fields in the Price table back to both the relationship table and their respective parent tables since it will help with JOINs between Price and the two parent tables.


Query performance with primary keys of different data types Is there a significant difference in performance if i decide to use INT or BIGINT as data type of a primary key in a table? Of course if i use BIGINT the value will have twice it's size. But as the primary key has always an index to speed up queries, is there going to be a significant difference in performance?

i would not see this as a major issue. of course things are a little larger but if we talk about a single integer column things will most likely be lost in noise anyway. we got 24 bytes of tuple header ... then comes the data. if things are 4 bytes larger? what difference does it make in a 10 column table.

so, no worries ... make sure that you do what is good for your app.


How to make PostreSQL functions private (inaccessible to end users)? When writing a set of PostgreSQL functions with procedural languages, is it possible to make some of the supporting functions private and not accessible by end users?

I wanted to refactor some common patterns into SQL functions, but are concerned about introducing potential SQL injection in the function parameters. For example, I wanted to implement a copy_table(src regclass, target text) function for copying a table to new table (in a previous question), the second parameter can be subject to SQL-injection if unquoted. (Quoting the 2nd parameter has its own issues as discussed there). On the one hand, I wanted to write this function as a helper function for other public-facing functions; on the other, I don't want to expose it to everyone for security concerns.

Is there a way to hide the dangerous supporting functions so that they can only be called by my functions?

Just use REVOKE to control the permissions Postgres assigns to roles.
Documentation:

The REVOKE command revokes previously granted privileges from one or more roles. The key word PUBLIC refers to the implicitly defined group of all roles.
See the description of the GRANT command for the meaning of the privilege types.

CREATE FUNCTION nocando()
RETURNS void AS $$
  SELECT null::void
$$ LANGUAGE sql;

REVOKE ALL
  ON FUNCTION nocando ()
  FROM PUBLIC ;


How to create multiple azure sql database in an elastic pool using powershell script? I want to create a sql server and deploy the elastic pool on that server. Then create multiple azure sql database in that elastic pool using powershell script.

I tried to work on this question...I hope it helps who is looking for it.The solution script is as follows:

Set subscription

Set-AzContext -SubscriptionId $subscriptionId

Create a new resource group

$resourceGroup = New-AzResourceGroup -Name $resourceGroupName -Location $location

Create a new server with a system wide unique server name

$server = New-AzSqlServer -ResourceGroupName $resourceGroupName -ServerName $serverName -Location $location -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminSqlLogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))

Create a server firewall rule that allows access from the specified IP range

$serverFirewallRule = New-AzSqlServerFirewallRule -ResourceGroupName $resourceGroupName -ServerName $serverName -FirewallRuleName "AllowedIPs" -StartIpAddress $startIp -EndIpAddress $endIp

Create an elastic database pool:

$firstPool = New-AzSqlElasticPool -ResourceGroupName $resourceGroupName -ServerName $servername -ElasticPoolName $firstPoolName -Edition "Standard" -Dtu 50 -DatabaseDtuMin 10 -DatabaseDtuMax 20

Declare an array to store db names:

$databasename = @("DB1", "DB2")

Loop to create automatic multiple database as per requirement:

   For ($i=0; $i -lt $databasename.Length; $i++)
        {
        $resourcegroupname = "elas-rg"
        $location = "centralus"
        $servername = "sirurname"

            $azuresqldb= Get-AzSqlDatabase -ResourceGroupName $resourcegroupname -ServerName $servername -DatabaseName $databasename[$i] -ErrorAction SilentlyContinue

     if ( -not $azuresqldb )
     {
                "Creating Azure SQL Database " + $databasename[$i] + " in Server Name sirurname"

                New-AzSqlDatabase -ResourceGroupName $resourcegroupname -ServerName $servername -DatabaseName $databasename[$i] -ElasticPoolName "Mysecondpool"
            }
            else
            {
                "Azure SQL Database already exists " + $databasename[$i]
            }
        }


Has anyone ran into this replication breaking bug before? I've been having this issue where replication was breaking on colliding primary keys.  The annoying thing was these were auto increment generated.

I think my issues been tracked down to http://bugs.mysql.com/bug.php?id=45677 for a couple reasons

The slaves that were breaking were using triggers
Slaves with identical schemas sans triggers were not breaking
I was able to reproduce the problem following the reproduction steps in the ticket

What leaves a shadow of a doubt that's the exact bug that's biting me is the schema and application have been running for years with out issue.  It started happening shortly after upgrading mysql versions.  That might seem like a 'well duh' thing but it was an upgrade from 5.1.5 to 5.1.59.  The ticket indicates it's been around since 5.1.35, so should have bitten me much sooner, no?

If you've run into this what version were you running?

That bug is referenced in the MySQL Documentation for 5.5 and 5.1.40+ as that condition being unsafe.

Note the bottom of the bug report:

  [10 Dec 2010 3:34] Daogang Qu  Any statement that causes a trigger or
  function to update an AUTO_INCREMENT column is considered unsafe for
  statement-based replication after a patch of
  Bug#45827(http://lists.mysql.com/commits/9031). I had backported the
  patch to mysql-5.1-rep2 long ago. But I didn't see it in
  mysql-5.1-bugteam. The patch must be missed to merge to
  mysql-5.1-bugteam. I'm going to commit a patch to fix it in bug#45827.
  Thanks for the check.

  [10 Dec 2010 4:12] Daogang Qu  Sorry. The patch of bug#45827
  backported into mysql-5.1-rep2 should be merged into 5.5 instead of
  5.1. It's merged into 5.5 successfully.

  For 5.1, We mark all the statements that invoke a trigger or call a
  function that updated autoinc fields as unsafe and will switch to
  row-format in mixed mode. But in statement mode, we didn't emitted the
  warning.

  For 5.5, we emitted the warning in statement mode, because  a suppress
  warning feature is introduced.

Could a patch have been overlooked between minor releases of 5.1? That is the only probable cause. Now that Oracle is the grandfather of MySQL (via Sun), don't hold your breath for any fixes.

MySQL 5.1.59 was released Sept 16 2011 and this is still around?

It must have been an overlooked/forgotten patch.


SQL Server 2016 in-place upgrade time for large databases Is there any statistics on how long it will take to in place upgrade SQL Server 2014 to SQL Server 2016 on a server with 1000+ databases and 10TB of storage?
How long it will take to install subsequent 2016 service packs?
Is there a way to optimize those installation times by configuration?

The goal is to minimize maintenance windows

Update: Let me clarify a question. All production updates will be tested in QA environment, the question is not about it all.
I'm asking about known to you options to optimize upgrade duration. I'll search for those options myself by measuring upgrade time in my environment, but if there is a "previous art" - please share.

I'd really recommend not doing an in-place upgrade; certainly not for a very large system like yours.  The reasons for this recommendation are many, and have been covered extensively by Mike Walsh in his answer on a separate question.

If you really need to do an in-place upgrade, the only real way to determine the amount of time required is to do a dry run on separate, identical, hardware.  This would involve installing SQL Server, and using backup-and-restore to copy all 1,000+ databases to the test server, then performing an in-place upgrade.  I'd recommend doing this in the very minimum since it allows you to test SQL Server in your environment prior to committing to it in production.


"Will this query always be indexed AND fast as the rows in the table grow?"

Definitely not necessarily. If there are many records and/or the value of deleted is skewed in some way (i.e. it's (nearly) always one particular value, or split 50-50), then the optimiser may decide to ignore a given index. Googling "why does mysql not use an index" gives 3.5M results

[EDIT]

You said:

"Thank you for your answer. Is there a different type of query I can use to count the number of rows with a WHERE clause?"

No -  that's what COUNT() is for. See here. Basically, you can't get round problems like the one you outline - indexes are frequently helpful, but there's no "magic bullet" that will cope well with all data volumes and profiles.

To see how difficult this problem is, check here - that's > 600 pages on the fundamentals of how an RDBMS decides on the best plan for a query. It is far from a trivial problem!


Delete rows older than x days without locking table we have a quite big MS SQL database with millions of rows.
I created a simple script to delete rows older than 1 month, but this seems to lock the table and creates trouble for the application.
The table has an indexed "ID" PK, and also a "date" column which I will use for this task.
What is the best way to do this without causing the lock?
I was considering partitioning, but not sure if its the best way to go.
Thanks in advance.

You need to delete rows in batches to avoid lock escalation.  Lock escalation happens when SQL Server switches from row or page locks to locking the entire table.  Lock escalation conserves memory when SQL Server detects a large number of row or page locks have been taken, and more are needed to complete the operation. This may be why you're having blocking issues.  If I recall correctly, row locks escalate to table locks when more than 5,000 rows are involved.
If the ID column is also the clustered index you might benefit from using the ID column to perform the delete instead of using the date column.  To do that, obtain the ID value for the date you want to work with, then loop to delete fewer than 5,000 rows per batch, where the ID value is less than or equal to the desired ID.  This is only applicable assuming rows are inserted in date order, such that incrementing ID values match incrementing date values.
You can determine how many rows were affected by the DELETE by checking the @@rowcount variable, and you can set a maximum number of rows to be affected using SET ROWCOUNT, then loop until your DELETE no longer removes rows:
SET ROWCOUNT 4500;
DECLARE @i int;
SELECT @i = 1;

WHILE @i > 0
BEGIN
    Delete from dbo.my_table Where ID < 4356;
    SELECT @i = @@rowcount;
END

An alternate method that eliminates the use of the deprecated SET ROWCOUNT statement is to add a TOP clause to the DELETE query, as in:
DECLARE @i int = 1;
DECLARE @id int;
SET @id = COALESCE((
    SELECT TOP(1) mt.ID
    FROM dbo.my_table mt
    WHERE mt.date < '2021-01-04T00:00:00.000'
    ORDER BY mt.date DESC
    ), 0);
WHILE @i > 0
BEGIN
    DELETE TOP(5000) mt
    FROM dbo.my_table mt
    WHERE mt.ID <= @ID;
    SET @i = @@ROWCOUNT;
END

In the code above, I've added a query to obtain the maximum ID value for rows on or before January 4th, 2021, simply to illustrate how this works.
Both methods above result in the same outcome, all rows prior to the given @ID value are deleted from the table.  Locking should be reduced to using row-level locks, thereby preventing a table lock from causing blocking on other concurrent processes.
As an alternative, you could look at Read Committed Snapshot Isolation (RCSI), otherwise known as row-versioning, at the database level.  RCSI prevents writers from block readers, however there are many possible impacts to using row versioning. You should thoroughly test the applications that use the database if you decide to go this route. Read up carefully and consider the other impacts on your application before blindly enabling RCSI.


Trace flag is running, but not message when deadlock happens Trace flags are used to temporarily set specific server
  characteristics or to switch off a particular behavior

More information on the trace flags are here

DBCC TRACESTATUS

this above, is the command that shows all the trace flags that are currently running on my system.

I have 2 questions:

1) is there any other way to find the list of trace flags that are currently running, other than DBCC TRACESTATUS?

2) It can be seen on the picture above the trace flag 1222 is active but I am not receiving any email when a deadlock occurs. What else needs to be done?

The reason the server wasn’t sending emails is due to this flag not being set:

EXEC master..sp_altermessage 1205, 'WITH_LOG', TRUE;
GO

Even though it was logging in the event log via the trace flag, this also needs to set in order to trigger the emails.
You can see the table here:

select * from master.sys.messages
where text like '%deadlock%'


CAST SUBSTRING to NUMERIC SSIS I have a fixed width text file that I am bringing in through SSIS. As part of the input into SQL Server I am grabbing a string of characters and trying to CAST them as numeric in a derived column.
The data looks like this:

02      PR000000000000017943                    0287801709

I need to grab 17943 and convert it to 179.43

The expressions I have tried are:

(DT_NUMERIC,18,2)(SUBSTRING(EntireRow,10,18)
(DT_NUMERIC,18,2)TRIM(SUBSTRING(EntireRow,10,18))

I keep getting the following error:

  The ProcessInput method on component "Derived Column" (2) failed with
  error code 0xC0209029 while processing input "Derived Column Input" .

I have looked but have not been able to find a solution to this.

Casting a string of 000000000000017943 to decimal(18,2) is going to result in a value of 17943 The source doesn't have a decimal place, so why would the cast know you need a decimal there?

Also, given your source data, your substring formula is off. It's resulting in a source string of R00000000000001794

That said, I take one of 2 approaches. Either I use a string operation to insert the decimal place or I use math to divide by 100. If I'm already dealing with strings, I usually stick with that approach as I hate to keep casting values about.

Off to lunch, but here is the Biml I used to generate the package. Grab bidshelper and use that to generate the same SSIS package as me.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <FileFormats>
        <FlatFileFormat Name="FFF" IsUnicode="false" FlatFileType="Delimited">
            <Columns>
                <Column Name="EntireRow" DataType="AnsiString" Delimiter="CRLF" Length="58" />
            </Columns>
        </FlatFileFormat>
    </FileFormats>
    <Connections>
        <FlatFileConnection FilePath="C:\ssisdata\dba_89464.txt" FileFormat="FFF" Name="CM_FF" />
    </Connections>
    <Packages>
        <Package ConstraintMode="Linear" Name="dba_89464">
            <Tasks>
                <Dataflow Name="DFT Source">
                    <Transformations>
                        <FlatFileSource ConnectionName="CM_FF" Name="FF Source" />
                        <DerivedColumns Name="DER Split to row type">
                            <Columns>
                                <Column DataType="AnsiString" Name="Header" Length="2">SUBSTRING([EntireRow],1,2)</Column>
                            </Columns>
                        </DerivedColumns>
                        <ConditionalSplit Name="CSPL - Split on Header">
                            <OutputPaths>
                                <OutputPath Name="Type 2">
                                    <Expression>[Header] == "02"</Expression>
                                </OutputPath>
                            </OutputPaths>
                        </ConditionalSplit>
                        <Multicast Name="MC Demo approaches">
                            <InputPath OutputPathName="CSPL - Split on Header.Type 2" />
                            <OutputPaths>
                                <OutputPath Name="String Approach" />
                                <OutputPath Name="Math Approach" />
                            </OutputPaths>
                        </Multicast>
                        <!--
                        Generate our value as string and one without the decimal place but of a numeric type
                        -->
                        <DerivedColumns Name="DER Type 2 processing">
                            <InputPath OutputPathName="MC Demo approaches.String Approach" />
                            <Columns>
                                <Column DataType="AnsiString" Name="strTemporaryMyValue" Length="18">TRIM(SUBSTRING(EntireRow,11,18))</Column>
                            </Columns>
                        </DerivedColumns>
                        <!--
                            Add our decimal place, string operations
                            This is not concerned with empty strings, etc
                        -->
                        <DerivedColumns Name="DER Add decimal place">
                            <Columns>
                                <Column DataType="AnsiString" Name="withDecimalPlace"  Length="19">SUBSTRING([strTemporaryMyValue], 1, LEN([strTemporaryMyValue]) -2) + "." + RIGHT([strTemporaryMyValue], 2)</Column>
                            </Columns>
                        </DerivedColumns>

                        <DerivedColumns Name="DER Make string to decimal">
                            <Columns>
                                <Column DataType="Decimal" Name="MyValueViaString" Precision="18" Scale="2">(DT_NUMERIC, 18, 2)[withDecimalPlace]</Column>
                            </Columns>
                        </DerivedColumns>

                        <DerivedColumns Name="DER Dataviewer connector String" />

                        <DerivedColumns Name="DER slice string into numeric type">
                            <InputPath OutputPathName="MC Demo approaches.Math Approach" />
                            <Columns>
                                <Column DataType="Decimal" Name="decTemporaryMyValue" Precision="20" Scale="0">(DT_NUMERIC, 20, 0) TRIM(SUBSTRING(EntireRow,11,18))</Column>
                            </Columns>
                        </DerivedColumns>
                        <!--
                            Behold, the power of math
                        -->
                        <DerivedColumns Name="DER Divide approach">
                            <Columns>
                                <Column DataType="Decimal" Name="MyValue" Precision="18" Scale="2">(DT_NUMERIC, 18, 2)decTemporaryMyValue/100.0</Column>
                            </Columns>
                        </DerivedColumns>

                        <DerivedColumns Name="DER Dataviewer connector Math" />
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>


Comparing Agent Jobs on Two Servers I'm currently building a process in Powershell where we can copy SQL Server Agent jobs from one server to another server just by entering the server names.  The script uses the SMO library to obtain access to both job servers, scripts the jobs that don't exist on the "other" server, then runs those scripts (under a SqlConnection and SqlCommand).  The issue is how to check if a job exists on ServerOne compared to ServerTwo.

I've tried four different approaches: build an array with the jobs from ServerTwo (the compare server) and see if that array contains a job from ServerOne; I also tried the like operator with the same logic; and, using arrays, I've used Compare-Object after loading two arrays with job names from both servers and measuring which jobs don't exist in the other (produces everything too).  Finally, I looped through each job on ServerTwo and looped through each job on ServerOne to see if they equal each other (which is the below code).  This will produce everything because each job doesn't equal each job:

$ServerName = "S1\I"
$ComServer = "S2\I"
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $ServerName
$com = New-Object "Microsoft.SqlServer.Management.Smo.Server" $ComServer

foreach ($a in $com.JobServer.Jobs)
{
    $a = $a.Name.ToString()
    foreach ($j in $srv.JobServer.Jobs)
    {
        $j = $j.Name.ToString()

        if ($a -like $j)
        {
            Write-Host $j
        }
    }
}

Is there another approach to this where I can check if a job exists on one server vs. another (as a note, I already know how to script the job, execute it and so on; the part of making sure the job doesn't already exist is the problem here)?

I see from the comments that you've already given Compare-Object a try, but that really should be working.  Below is a working example showing this:

$SqlServer1Name = "SQL1"
$SqlServer2Name = "SQL2"

$SqlServer1 = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServer1Name)
$SqlServer2 = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServer2Name)

$SqlServer1JobListing = $SqlServer1.JobServer.Jobs |
    Select-Object -ExpandProperty Name

$SqlServer2JobListing = $SqlServer2.JobServer.Jobs |
    Select-Object -ExpandProperty Name

Compare-Object $SqlServer1JobListing $SqlServer2JobListing |
    Where-Object {$_.SideIndicator -eq "<="} |
    Select-Object -ExpandProperty InputObject

This will output all of the jobs that live on SQL1 and not on SQL2.  This comparison is by name only, not taking into consideration any other SQL Server Agent Job attributes.


What should I set DATE field as default value when NO_ZERO_DATE is enabled in MySQL? On shared hosting is not possible to change the NO_ZERO_DATE unless doing with a query but it seems more like a fast fix that the most correct one
Some of my tables has as default date "0000-00-00" what should be the correct setting when NO_ZERO_DATE is enabled? At this time I was thinking at a fixed date in the past like "1900-01-01" but not sure if is the most correct way to create the table

There is no date 0000-00-00 on any calendar. It's not a valid value for a date.
Don't use 0000-00-00 or 1900-01-01 or 9999-01-01 or any other supposedly "unused" date value to represent a missing value. This just trades one type of problem for some different problem. For example, you can no longer use MIN() or MAX() or AVG() on that column.
If you need some way to represent that the date is unknown or inapplicable on a given row, then use NULL. That's what NULL is for in SQL.


How to profile the PostgreSQL query planner diagnose what is causing it to take up large amounts of memory and time I'm fighting with a specific "generated" query (the SQL is generated from a proprietary system, and I won't be able to share it or the execution plan here), and running into simply massive memory usage and long query times. When running locally on my machine, i'm seeing 6.5gb of memory used during the query, and the query takes around 8 seconds to fully run. However the weird part is that almost all of the time is spent in query planning, and not much in the actual execution:

Planning time: 7453.148 ms
Execution time: 159.609 ms

Interestingly enough, PREPARE-ing the query and then EXECUTE-ing it later causes the memory usage to spike during planning, but not during execution.

I have never seen a query take that long to plan, and none of our other large queries get anywhere near that.

My question is how can I diagnose or debug what the query planner is doing here? What tools does PostgreSQL provide to allow me to profile and diagnose why this query in particular is taking so much time and memory in the query planning stage, and what we can do to reduce the memory usage from the query planner.

Things i've tried:

Disabling GEQO entirely - had no measurable effect on the memory used, but did marginally increase the planning time to ~9 seconds up from ~7.5 seconds.
Tweaking GEQO threshold - had no measurable effect whether set to 2 or 99999 or anything in between.
Tweaking from_collapse_limit and join_collapse_limit - these had a small impact on the query, taking the planning down from ~7 seconds to ~5 seconds but not changing the memory usage at all (and the memory usage is really my main concern here)

I want to dig into this more, but without knowing how to profile what the query planner is doing, I'm basically shooting in the dark...

Edit: this is in PostgreSQL 9.6. We should be able to upgrade to 10 fairly easily, but 11 and up will need more research into if it's feasible.

The first stop to investigating this be would the system tool "perf".  You can get the pid of the running backend (by using "top", for example--make sure you get the pid of the backend, not the client program) then do sudo perf top -p <pid>.  You might get more useful output by first installing the debug symbols. How you do that is going to depend on your OS and how you installed it.

But once you identify the bottleneck, I wouldn't hold out much hope that anyone will do anything about it, given the far-out nature of your query.  If you are willing to dig into the source code to propose our own solution, then I would suggest you start by compiling your own server with version 13dev.  You aren't going to get very far in your investigations using only packaged versions, and especially not older packaged versions.  (Also, the problem might already be fixed in 12 or 13dev).


Order questions depending on answer votes I'm really curious about how to create an SQL query to order questions depending the votes_up and votes_down. (The votes can be the sum of both, votes_up + votes_down.)

Question has_many answers
Answer belongs_to one question

The answer table is like:

Answer(id, answer, votes_up, votes_down)

Something like:

SELECT q.*, SUM(a.votes_up) - SUM(a.votes_down) AS TotalVotes
FROM Questions AS q
JOIN Answers AS a ON a.QuestionID = q.QuestionID
GROUP BY q.QuestionID, q.Question, q.AnyOtherFieldFromThatTable
ORDER BY TotalVotes DESC;

I would encourage you to include q.QuestionID (or whatever you call that field) in your GROUP BY clause, for performance reasons.


How can I reduce the Initial Size of a LOG file? I've inherited a SQL Server 2008r2 database; the Data file was created with an initial size of 1,000 mb, but the Transaction Log has an initial size of 28,016 mb.  No backups were being performed.

I've implemented full and transaction backups to match our recovery needs, and as a result the transaction file is always ~99.9% empty, never exceeding 30 or 40 mb used.  I'd like to reduce the initial size so I can shrink the log down to a more apprpriate size, 200 mb or so.

Does anyone know how I can redefine the Log file with an Init size of 200 mb?

Does anyone know how I can redefine the Log file with an Init size of 200 mb?

You have to use ALTER DATABASE [YOUR_DB_NAME] ... MODIFY FILE ... SIZE

ALTER DATABASE [your_db_name]
SET RECOVERY SIMPLE;

CHECKPOINT;

-- shrink the log file to 200MB
DBCC SHRINKFILE (your_db_name_log,200);

--- define the initial size to 200MB as well.. CHANGE it as per your needs!
ALTER DATABASE [your_db_name]
MODIFY FILE (NAME=your_db_name_LOGICAL_log,SIZE=200MB,MAXSIZE=UNLIMITED,FILEGROWTH=100MB);

--Optional if you want the database in full recovery mode
--for point in time recovery going forward
ALTER DATABASE [your_db_name]
SET RECOVERY FULL;
--- Take full backup to establish log chain
-- take log backups to help truncate the log file and keep it manageable

Suggestions:

Initial size of 200MB is very less. Depending on how busy your system is and how frequent you take log backups (if your db is in full recovery), I would suggest you to look into making an initial size and autogrowth more sensible.
Its always a good idea to enable Instant file initialization to help for data file autogrowth events (as well as database creation and restoring database). Refer Database defragmentation and autogrowth settings answer for more details.


Migrate MySQL tables to new server with same tables already on it? We migrated our web server to a newer server. We have the old and new both running while we do testing on the new one. The old one is still being used in production by our clients and internal staff so is more up-to-date than the new one which was current as of a week ago when took a snap shot and did the migration. So I need to migrate the new data on the old server to the new server. What is the best way to accomplish this? If I do a mysqldump and import into the new server, will it overwrite the data already in there? That is fine if so, just concerned about it duplicating records. Basically just want to take everything that is on the old server and dump it into the new one when no one is on the system.

So, you want to import the new data without overwriting and no duplicate keys, right? I'm no expert, but as I see it, you can either:

Remove the current data and import the whole old DB,
Use --ignore or --replace on mysqlimport - either won't duplicate rows (they'll either --replace existing rows or --ignore duplicates),
mysqldump without creating the database, the table and using the Insert Ignore option (--insert-ignore), so it'll insert the data and skip the duplicate keys,
Use --force when importing, to skip errors while inserting, this way if there's already one row with the key being inserted, it won't generate an error, just skip it and go to the next insert.

You can look up another already answered questions and try to find the best solution: here or here.

I tried with the --insert-ignore option on mysqldump and when importing I had no errors, the new data was inserted with no duplicates.


Delete and Update of Foreign keys I'm working on a school project. And I'm required to implement it using Java-EE and SQL Server. I want the design to withstand any kind of faulty user requests. My questions are how should I design the tables and the relationships such that deletions are safe.
In addition i want to know how to log these changes.

The trick is to not actually delete rows, but to set an "IsDeleted" column to 1. That way, referring records remain unorphaned.

Updates are more interesting. Do you need to have the referring record still refer to the old record, or the new version?


Backup Access Database Daily/Weekly We have a Microsoft Access database split into backend/frontend.  What I am looking for is to be able to automatically backup these files on a daily or weekly basis - what is the best way to go about this?  We don't want to have to worry about backups for the database, just know that it is occuring automatically on a schedule.

Thanks.

(Borrowing from my earlier answer here...)

re: actually performing the backup

Backing up a native Access database is simply a matter of copying the entire database file (.mdb for Access_2003 and earlier, .accdb for Access_2007 and later). Any scripting language would work, even a simple Windows batch file that does something like

copy /Y d:\apps\databases\mydatabase.accdb z:\backups\databases\*.*

re: automatic scheduling of the backup

The Task Scheduler in Windows could take care of that for you. Once you've created your script to copy the database file(s) you can create a scheduled task to run it periodically. See the MSDN article Using the Task Scheduler (Windows) for more information.


Read CSV and perform SQL in SQL Server Management Studio Is it possible in SQL Server Management Studio to read a CSV and perform an SQL update per line?

Example

data.csv

"column1","column2,"column3"
 "column1","column2,"column3"
 "column1","column2,"column3"
 "column1","column2,"column3"
 "column1","column2,"column3"

I want to run a SQL update based on the data in each line such as

UPDATE [data].[orders] set order_number = $column1, something_else = $column2 where order = $column3

Is this possible to do in SQL Server Management Studio?

You may use BULK INSERT for that:

CREATE TABLE #orders (
  Column1 int,
  Column2 nvarchar(max),
  Column3 datetimeoffset
)

BULK INSERT #orders
FROM 'X:\orders.csv'
WITH
(
  FIRSTROW = 1,
  DATAFILETYPE='widechar', -- UTF-16
  FIELDTERMINATOR = ';',
  ROWTERMINATOR = '\n',
  TABLOCK,
  KEEPNULLS -- Treat empty fields as NULLs.
)

If you exported the CSV from SQL Management Studio then some preparations are needed:

The file should be converted to UTF-16.
All NULLs should be replaced with empty strings.

Here is a Powershell script for that:

Get-Content .\orders_raw.csv -Encoding UTF8 | % { $_ -replace "NULL","" } | Out-File ".\orders.csv"


Can I check what the allocation unit size is on my drives via a query? Assuming that I do not have access to windows server, can I run a script from SSMS that would allow me to check whether allocation unit size is formatted with 64K?

Maybe this is one way:

EXEC master..xp_cmdshell 'fsutil fsinfo ntfsinfo "C:"';

Line 9 shows, e.g.:

Bytes Per Cluster :               4096

There are obviously more powerful ways to do this using, e.g. PowerShell, but as I suggested in my comment, it's hard to understand exactly what you're going to do with the output in SSMS, and what kind of effort you're willing to go to in order to use these methods from within SSMS... I'd say it's much better to get these values externally (which gives you so many more options), then update a table you can query from within SSMS.


pgAdmin-III - Option to load a favorite automatically when opening query window pgAdmin-III - Query window option

Does pgAdmin-III has any option to load a default favorite file automatically when opening new query window? This would be much useful to have commit/rollback statements by default when executing random sql statements.

Example:

BEGIN;
SAVEPOINT my_savepoint;
-- Todo Queries
-- Ex. UPDATE users SET firstname = 'new first name' WHERE lastname = 'lastname';

-- Either one below
-- COMMIT;
-- ROLLBACK TO my_savepoint;

I want this always to be loaded when opening a new query window from pgAdmin-III.
Any suggestions?

There is no direct option, but I use command line option to load my favorite sql automatically at starting pgAdmin.

for exmaple in windows :
start "" "...../pgAdmin3.exe" -q -f "favorite.sql"

-q options means that you launch query tool only.
-f "filepath" instructs sql file automatically opened in query tool editor.

detail of options are
http://www.pgadmin.org/docs/dev/commandline.html


Why did a query change its execution plan and how to anticipate the change? We have a job using a dynamic query in production for years now and its execution took 5-6 seconds up until our last code deploy. Now the same query takes days to finish. It's a relatively simple nested select that joins 5-6 tables, the largest of which have a few hundred thousand rows (others are mostly small lookup tables).

This same query runs on different database instances very quickly (with much more data and exactly the same codebase).

I'm well aware that tools like explain plan only show predicted execution plan, but the explain plan on this particular instance is very poor (for comparisson, it doesn't use an index which seems to be used on all other instances).

My question is - without changes in query, table structure the query uses or a significant change in the amount of data read, why has the execution plan changed so drastically and how can I anticipate for such a change?

added info
The query in question starts with a SELECT DISTINCT... We noticed that on this db instance, if we remove the distinct (which then doesn't necessarily give us the correct results) the execution plan is very similar to the original, much quicker one.

Edit:
Satistics were run straight after the last code deploy.

The Optimizer takes the parsed representation of SQL statement and Statistics to generate final execution plan with the lowest cost. During this process the Optimizer generates multiple plans and compares them. Execution plans may change as the Optimizer inputs(Parsed SQL Statement and Statistics) get changed.

  Why Execution Plans Change

  Execution plans can and do change as the underlying optimizer inputs change.
  EXPLAIN PLAN output shows how the database would run the SQL statement when
  the statement was explained. This plan can differ from the actual execution plan a SQL
  statement uses because of differences in the execution environment and explain plan
  environment.
  The Oracle Documentation clearly states that Execution plans can differ when we change Schemas and have changes in Costs.

If we run the same SQL statement in different database under different Schemas then also the resulting plan can be different. Even the schema and database is same but the Cost of the execution plan is different then also the optimizer can choose different execution plans. Bind variables, size of the data and its statistics, optimizer's parameters may influence the Cost.

Guessing why the Execution plan has been changed is difficult because we don't have the metadata(which are dynamic) of your database on which we have to query for further investigation and its time consuming task.

You can use SQL Tuning Adviser, SQLTXPLAN, SQL Trace etc to make it easier to find the elements affecting the execution plan.

For details:
Oracle Database SQL Tuning Guide


selecting records that are 3 days from expiring datetime I have a table that contains a datetime timestamp. What I am looking for is to select records that are exactly 3 days from the 30 day expiration based on the datetime timestamp.

Here is what I have so far although I don't think it is correct:

    SELECT jobs.*,occupations.title
    FROM jobs
    LEFT JOIN occupations on occupations.id = jobs.occupation_id
    WHERE DATE_ADD(jobs.`date_created`,INTERVAL 27 DAY) = DATE(NOW())

Help please :)

If date_created is a DATE, do this:

SELECT jobs.*,occupations.title
FROM jobs
LEFT JOIN occupations on occupations.id = jobs.occupation_id
WHERE jobs.`date_created` = DATE(NOW() - INTERVAL 27 DAY);

If date_created is DATETIME or TIMESTAMP and the time part is '00:00:00', do this:

SELECT jobs.*,occupations.title
FROM jobs
LEFT JOIN occupations on occupations.id = jobs.occupation_id
WHERE jobs.`date_created` >= DATE(NOW() - INTERVAL 27 DAY) + INTERVAL 0 SECOND;

These queries are better in terms of performance than the one in the question because the WHERE clauses of my queries will induce an index range scan, whereas the WHERE clause in the question will induce a full table scan (even if date_created is indexed).

If the jobs table does not have an index on date_created, then make one

ALTER TABLE jobs ADD INDEX (date_created);

Give it a Try !!


slow query with fulltext and left join I have 3 tables

about 250,000 records.
table1 is MyISAM, add fullindex with title and content, add index with pid.
table2 and table3 are InnoDB, add index with pid.

Only query table3, just cost 0.04 seconds.

select * from table3
WHERE MATCH (title,content)
AGAINST ('+words' IN BOOLEAN MODE)
ORDER BY pid

but query like this, cost 16.87 seconds.

SELECT *
FROM table1
INNER JOIN table2 ON table1.pid = table2.pid
LEFT JOIN table3 ON table1.pid = table3.pid
WHERE MATCH (table3.title, table3.content)
AGAINST ('+words' IN BOOLEAN MODE)
ORDER BY table3.pid

I make an EXPLAIN plan for the 2nd query, return:

id select_type  table     type  possible_keys    key    key_len    ref               rows      Extra
1  SIMPLE       table1    ALL    pid             NULL   NULL       NULL              201497    Using temporary; Using filesort
1  SIMPLE       table2    ref    pid             pid    32         mydb.table1.pid   1
1  SIMPLE       table3    ref    pid             pid    32         mydb.table2.pid   222309    Using where

Why is the 2nd query slow?
How can I optimize it?

Thanks.

Unfortunately, MySQL is behaving exactly as I expected.

The problem lies in the fact that MySQL's Query Optimizer gets very confused when used in conjunction with other tables in an EXPLAIN plan's compliation. I wrote about this before:

https://stackoverflow.com/a/6092216/491757 (May 23, 2011)
FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional (Oct 25, 2011)
Mysql fulltext search my.cnf optimization (Jan 26, 2012)
MySQL EXPLAIN doesn't show 'use index' for FULLTEXT (May 07, 2012)

My suggestion: Try Refactoring the Query By Getting the FULLTEXT Search to occur before JOINs

SELECT * FROM table1
INNER JOIN table2 ON table1.pid = table2.pid
LEFT JOIN
(
    select * from table3
    WHERE MATCH (title, content)
    AGAINST ('+words' IN BOOLEAN MODE )
    ORDER BY pid
) table3
ON table1.pid = table3.pid
;


PostgreSQL backup + gzip We have a need to send PostgreSQL DB backup to CRM developer.
We send them and get feedback that the backup can't be "uzipped", as archive is broken.
The backup script snippet looks like this:
pg_dump -c -O -U postgres $x -h localhost -p 5432 --disable-dollar-quoting -i | gzip > $BACKUPDIR/$DT-$x.gz

We chceck the backup process and in our opinion there is no any issue with backuping system.
btw. They used 7Zip to unzip.
Question: What would you suggest to me to say to CRM Development Team .... what could be the problem ?
btw.
In fact as so far we do not do any test for restoring, and I know must do this due to the security reason.
Question 2
We found that on Linux server all was properly backuped.
When we copy them with to SMB resource and get to Windows station then even 7zip is unpacking them correctly.
We found that the problem is with sending them to local FTP server.
We are sending this files by this BASH script:
#!/bin/sh
echo FTP file: $1 UPLOAD START
#pwd
ftp -n 192.168.1.205 <<END_SCRIPT
quote user userslogin
quote pass userspassw
cd /Backup
pwd
put $1
quit
quit
END_SCRIPT
exit 0

Could somebody say how to improve/fix our FTP bash script ?

You should tell them the way you generated the dump, since it is somewhat unusual. One obvious problem may be that they tried a decompression program that does not understand GNU zip.
I recommend to use the custom format:
pg_dump -F c -U postgres -f outfile.dmp $x

That can be restored with pg_restore, and it is automatically compressed.


Overcome LIKE character length limitation By reading this LIKE character length limitation here, it looks like I can't send a text longer than ~4000 characters in a LIKE clause.

I'm trying to fetch the query plan from query plan cache for a particular query.

SELECT *
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
where st.text like '%MY_QUERY_LONGER_THAN_4000_CHARS%' ESCAPE '?'

if the query inside the the LIKE is longer than 4000 chars then I get 0 results even if my query is in the cache plan. (I was expecting at least an erorr).

Is there a way to workaround this issue or to do it differently?
I have queries which can be > 10000 chars long and it look like I can't find them with the LIKE.

It does not appear that this can be solved in pure T-SQL since neither CHARINDEX nor PATINDEX allow for using more than 8000 bytes in the "to search for" string (i.e. max of 8000 VARCHAR or 4000 NVARCHAR characters). This can be see in the following tests:

SELECT 1 WHERE CHARINDEX(N'Z' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 7000),
                         N'Z' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 6000)) > 0

SELECT 1 WHERE PATINDEX(N'Z' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 7000),
                        N'Z' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 6000)) > 0

Both of those queries return the following error:

  Msg 8152, Level 16, State 10, Line xxxxx
  String or binary data would be truncated.

And, reducing the 7000 in either of those queries down to 3999 gets rid of the error. A value of 4000 in both cases will also error (due to the extra N'Z' character at the beginning).

HOWEVER, this can be accomplished using SQLCLR. It is fairly simple to create a scalar function that accepts two input parameters of type NVARCHAR(MAX).

The following example illustrates this ability using the Free version of the SQL# SQLCLR library (which I created, but String_Contains is again available in the Free version :-).

SETUP

-- DROP TABLE #ContainsData;
CREATE TABLE #ContainsData
(
  ContainsDataID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  Col1 NVARCHAR(MAX) NOT NULL
);

INSERT INTO #ContainsData ([Col1])
VALUES (N'Q' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 15000)),
       (N'W' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 20000)),
       (N'Z' + REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 70000));

-- verify the lengths being over 8000
SELECT tmp.[ContainsDataID], tmp.[Col1], DATALENGTH(tmp.[Col1])
FROM   #ContainsData tmp;

TESTS

SELECT tmp.[ContainsDataID], tmp.[Col1], DATALENGTH(tmp.[Col1])
FROM   #ContainsData tmp
WHERE  SQL#.String_Contains(tmp.[Col1], REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 15100)) = 1;
-- IDs returned: 2 and 3

SELECT tmp.[ContainsDataID], tmp.[Col1], DATALENGTH(tmp.[Col1])
FROM   #ContainsData tmp
WHERE  SQL#.String_Contains(tmp.[Col1], REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 26100)) = 1;
-- IDs returned: 3

Please keep in mind that String_Contains is using an everything-sensitive (case, accent, Kana, and width) comparison.


How can I update an hstore[] field? I have table products, in which there is an hstore array field countries_of_origin.

So a product that is assembled from multiple products might have multiple countries of origin:

[
  { "country" => "USA" },
  { "country" => "MEX" }
]

My only issue is, I have no idea whatsoever on how to write a SQL update. Our developers interact with the table via an ORM. But I sometimes have to do a mass update for expediency's sake, and I don't know to do it!

Thanks for any help you can offer.

Look at the examples for hstore in the PostgreSQL documentation.

UPDATE tab SET h = h || ARRAY['b => 2'::hstore,'c => 3'::hstore];


SQL Server Error, "Invalid usage of the option FIRST in the FETCH statement." From 2012 onward the SQL Server docs show they support OFFSET..FETCH which I'm trying to use instead of a LIMIT.

The following works fine in PostgreSQL to sample a result set,

SELECT *
FROM ( VALUES (1),(2),(3) ) AS t(x)
OFFSET 0 ROWS
FETCH NEXT 1 ROWS ONLY;

However, with SQL Server, I get

Msg 153, Level 15, State 2, Line 4
Invalid usage of the option FIRST in the FETCH statement.

What's going on here? Does SQL Server support the standardized OFFSET .. FETCH?

SQL Server has implemented the OFFSET and FETCH clauses as part of the ORDER BY clause, as pointed by the other answers and documented in their documentation.
The SQL standard on the other side, has both of these clauses as independent:
<query expression> ::=
[ <with clause> ] <query expression body>
[ <order by clause> ] [ <result offset clause> ] [ <fetch first clause> ]

If someone wants this feature implemented in full compliance with the standard, they can always make a request to the SQL Server team, through the Connect channel. In fact, MS has commented - in a different request about offset and fetch:

Connect item: SQL Denali: add total rows counter to SELECT statement - by Alexey Rokhin
Answer: Posted by Microsoft on 24/11/2010 at 11:34
The requirement that OFFSET/FETCH requires ORDER BY is a restriction in this release. In the ANSI SQL standard (SQL:2011) where the new OFFSET/FETCH clauses are proposed, ORDER BY is optional. The restriction in SQL Server has to do with limitation in our parser technology that cannot handle the optional syntax without making OFFSET a reserved keyword. We may remove it in the future.
Now with respect to ...

Until then, if one wants to use OFFSET and FETCH without a specific ORDER BY, a workaround is to add a "do nothing" order by clause. Example:
SELECT
...
ORDER BY (SELECT NULL)
OFFSET 0 ROWS
FETCH NEXT 1 ROWS ONLY;


Difficulty with Postgis extension on Ubuntu 14.04 I have a updated Ubuntu 14.04 running postgresql 9.3 (installed using the tutorial from https://wiki.postgresql.org/wiki/Apt).

When I access my database through pgadmin and go to the console and type:

-"create extension postgis;"

I get an error that the extension is missing in the directory:

 /usr/share/postgresql/9.1/extension/postgis.control

But that is 9.1. - Is pgadmin connected to the wrong postgresql? In my postgresql directory I also have a folder "9.1", "9.2" and "9.3". And the postgis extension files seem to only be in the 9.3 subfolder.

Do I need to change something in the pgadmin settings? I kind of ran out of options myself.

Packages:

postgis 2.1.2+dfsg-2
postgresql-9.3-postgis2.1
postgresql 9.3+154.pgdg14.04+1
pgadmin3 1.18.1-2
postgresql-contrib-9.3

Edit:

 dpkg -L postgresql-9.3-postgis2.1

returns:

 dpkg-query: Package »postgresql-9.3-postgis2.1« is not installed.

but this worked

 sudo dpkg -L postgresql-9.3-postgis-2.1 2.1.2+dfsg-2

    /.
    /usr
    /usr/lib
    /usr/lib/postgresql
    /usr/lib/postgresql/9.3
    /usr/lib/postgresql/9.3/lib
    /usr/lib/postgresql/9.3/lib/postgis-2.1.so
    /usr/lib/postgresql/9.3/lib/rtpostgis-2.1.so
    /usr/share
    /usr/share/doc
    /usr/share/doc/postgresql-9.3-postgis-2.1
    /usr/share/doc/postgresql-9.3-postgis-2.1/copyright
    /usr/share/doc/postgresql-9.3-postgis-2.1/changelog.Debian.gz

Edit 2:

The server status is also complaining about "The server lacks instrumentation functions" and the missing conbrib-package. Here too the installed package "postgresql-contrib-9.3" is ignored.

Edit 3:

Output of "pg_lsclusters"

    Ver Cluster Port Status Owner    Data directory               Log file
    9.1 main    5432 online postgres /var/lib/postgresql/9.1/main /var/log/postgresql/postgresql-9.1-main.log
    9.3 main    5433 online postgres /var/lib/postgresql/9.3/main /var/log/postgresql/postgresql-9.3-main.log

But that is 9.1. - Is pgadmin connected to the wrong postgresql?

Yes.

It appears that you're connected to your 9.1 server, suggesting that 9.1 is still running.

Do pg_lsclusters to see what PostgreSQL installs you have and their status.

Each runs on a different port. If you want to connect to 9.3 you need to check what port it runs on and connect PgAdmin to that port.


What's the best way to migrate from mariadb to mysql on ubuntu 18.04 I've got a server running on ubuntu 18.04.1 with mariadb 10.1. and I have to change to mysql. The reason is that mariadb isn't supported by a product I want to buy. Searching around the internet  I've just  found the opposite way.

Do I have to keep an eye on something special?

Current db-related software:

ispconfig
nextcloud

Any hint will be helpful.

Confluence officially supports a number of different database systems, including MySQL 5.6 and 5.7, but not 8.0, and also does not officially support MariaDB.

If you want to migrate an existing Confluence database running on MariaDB to MySQL, then mysqldump is a guaranteed route to success. Just dump the existing database to an SQL file, and then import this into MySQL using e.g. the mysql command-line client. This could be slow if it's a big database.

FWIW, the company I work for has a major Confluence installation on MariaDB 5.5, which is the default in RHEL7/CentOS7, and this has been running for several years with no known issues.


Do I need a database license for my application? I am developing an application for cemetery management. It is my first "big" project, so I'm not familiar with the "corporative" side of this.

First, some info about the app:

Has 3 local database files, one for authorizations (Login and permission purposes), another for option storing (each of these two has only one table) and another for the system itself (this is the main one, and has 5 tables).
In general, the system stores all the info of a small cemetery. This info includes deceased, graves and payments (3 tables). The other 2 tables are one for crossed information and another for vacant graves.
There's storing and retrieval of info between the C# GUI and the SQL.
The application is for academic purposes for now, but it may be a serious thing in the future (own business maybe?), so I'm a little worried about legal problems with M$ in the future.

Main Question: Do I need to pay for a license? Either for SQL or Visual Studio or anything else.

It does not sound like your database and application would be very big in current terms.

You could look into the freely available Microsoft tools for Visual Studio Express and SQL Server Express:

https://www.visualstudio.com/en-us/products/visual-studio-express-vs.aspx

http://www.microsoft.com/en-us/server-cloud/products/sql-server-editions/sql-server-express.aspx

Both of these are without charge and contain most features of the bigger products.  The limit is in database size, amount of memory, and number of processors that can be used.


Help on building a new Server I am in the process of building a new server to run our SQL Server databases (we have many TB's of data). I am looking for some help on what I should request for hard drives. My thought was to have 5 logical drives:

Drive 1: System drive
Drive 2: Database drive for DB's with a lot of read/writes
Drive 3: Database drive for other DB's and TempDB
Drive 4: Log files
Drive 5: Backups and other storage

Would this be a good setup? What type of disk (SSD or other) and RAID should I use for each drive? Keep in mind I'm trying to get the most bang for the buck. I don't have an unlimited budget, and I want to spend a lot on RAM.

If this is local storage, layout matters a lot more. If it's SAN storage, things are a little bit different.

Here's what I do for local storage:

Put big data files (mdf, ndf) for user databases on non-SSD storage
Put log files (ldf), and tempdb (all files) on SSD

Data files are typically around 80-90% reads to writes. You can mitigate the "slow" part of reading from them by, as you say, "spend[ing] a lot on RAM". RAID 5 or 6 is the usual choice here.

Log files and tempdb are different, and they need to absorb a lot of writes quickly. They're a much better fit for faster tiers of storage. RAID 10 is the usual choice here.

The system drive storage class isn't terribly important here, but I'd make a note that backups either shouldn't be done locally, or they should be copied to redundant storage. If you lose your backup drive, you, well... Don't have backups.

If this is SAN storage, what matters even more than drive layout and storage tiers is making sure you have adequate networking paths between the server and the SAN.

For more details why, see this Q&A: SQL Server has encountered occurences of I/O requests taking longer than 15 seconds.


Why not use a table instead of a materialized view? I'm new to Oracle databases. If I have understood correctly, materialized view is a view which result set is saved as a physical table in the database and this view/table is refreshed bases on some parameter. If view is saved as a  physical table, why not store the data into a table in the first place? So what is the benefit of using materialized view instead of a table?

One of the biggest benefit of using a materialized view is that Oracle takes care of keeping the data in sync.  If you have a separate aggregate table, you are responsible for keeping the data synchronized.  That generally requires a reasonable amount of code and a decent amount of testing and most organizations manage to make mistakes that leave holes that cause the aggregate table to get out of sync.  This is particularly true when you try to implement incremental refreshes of the aggregate table.

Another major benefit is that, depending on the settings, Oracle can use query rewrite to use materialized views when users issue queries against base tables.  So, for example, if you have a bunch of existing reports against a detail table that produce daily, monthly, and yearly aggregate results, you can create a materialized view on the base table that aggregates the data at a daily level and the optimizer can utilize that materialized view for all your existing queries.  This makes it much easier to optimize reporting workloads in a data warehouse without trying to go and rewrite dozens of reports to use your new aggregate table or to mess with DBMS_ADVANCED_REWRITE to force your own rewrites of the queries.


Stuck with self-referencing linked server - will replacing the primary server name be better? A vendor app uses 4-part names to reference objects.  I am pushing back on that, but the current version is what it is.  We installed the app using a SQL Alias for all the good reasons you do that, but the app had tons of errors because its four part references were starting with the alias instead of the local server name.  Defining the alias on the db server had no effect - the references were being made within the DBEngine.

To get around the errors, I added a second server definition (linked server) named after the alias that referred back to the same DBEngine.  I recently heard about the performance costs of linked servers (e.g. no caching) and about how bad an idea it is to define a linked server that refers to the local server.

I am stuck with this version of the app for now, so I cannot do anything about the four-part references and cannot reinstall without the alias (my client is not willing to pay for that work).

Would it help to delete the default server definition and replace it with the alias name instead of the machine name.

Comments? Warnings? Risks? Will doing that avoid the performance issues associated with self-referencing linked servers?

As a secondary question, I would be interested in a clear explanation of why four part names are a bad idea - something I can pass on to the vendor.

A linked server even if it links to itself is still a linked server with all of the pain points that entails (as you outlined in your question). I would either stand up a new server/instance with the name that you had installed and run the database there without the linked server, or get the app to reinstall with the actual server name.

Having an Alias does make things simpler - mainly that you can move the database around without the app knowing - but if it's going to do every query through a linked server then benefit is outweighed (in my opinion) by the cost.


HA SQL AlwaysOn backup using Copy Only + Logs from secondary replica I'm hoping to elicit some guidance from your collective DBA experiences.

Here's our problem:

Full backups of our larger databases (around 3TB) are consuming too much resources such that we are suffering lengthy timeouts. It appears to be mostly I/O bound.

Full backups are done once a week, with regular 15min transaction log backups in between. We have to take full backups at some point.

Options we are considering:

Upgrade from SQL 2016 Standard to 2016 Enterprise edition and configure the Resource Governor to limit I/O caused by backups. Basically choke the backups a little to allow servicing of application requests better.
Go the HA (High Availability) route and deploy a secondary node with SQL AlwaysOn perform backups from the secondary replicas. This would relieve the primary replica of backup duties and remain available for application requests, potentially resolving our timeouts issue.

Pro's & Con's:

The cost of Enterprise edition is not trivial and while it offers numerous other benefits, it's a hard pill to swallow at this time.

For a similar or even lesser cost, we can deploy a second SQL 2016 Standard edition server and benefit from a HADR-based database deployment. This option is very appealing to us as we currently only have a cold standby for recovery.

We would likely favor the benefits from HADR over Enterprise, provided we can resolve the timeouts issue during backups.

Some Lab Findings:

For Enterprise edition, some labs tests show we can control the backup resources reasonably well, but it doesn't feel ideal. We'll need to tweak the numbers to the hardware to find the "sweet spot" for achieving the required level of restriction for backups without adversely slowing it down too much.

For AlwaysOn, we've done some lab tests and found that we cannot perform typical "Full" backups on the secondary replica, but we can make COPY_ONLY "Full" backups. This makes sense as it preserves the log chain of the primary replica.

We are able to make transaction log backups on the secondary replica which too is fine since that log chain is actually a "slave" from the primary and should not cause any harm when made on the secondary replica.

Have confirmed we are able to restore from the CopyOnlyBackup+TransactionLogs, so this could be a viable data recovery strategy.

Findings and Questions:

Has anyone had a similar issue and resolved it with either option, confirming viability of the option for us?
In the "CopyOnlyBackup+TransactionLogs from the secondary replica" scenario, any full backup on the primary replica would break the log chain and invalidate the CopyOnlyBackup of the secondary replica for subsequent transaction log backups. The remediation is to make another CopyOnlyBackup on the secondary replica, or to not make full backups on the primary.
What is the implication of not making normal full backups on the primary and only ever making COPY_ONLY full backups. Is this OK in the long term?

Any guidance would be greatly appreciated. Hopefully we not barking up the wrong tree.

Im actually using a similar setup with 1TB of the database.

  Has anyone had a similar issue and resolved it with either option, confirming viability of the option for us?

Im using backup on secondary only.Even in few outages, I tried to use copy only full backups and log backups to recover them.

  No issues that using copy only backups.

The above statement will give a clarity for your 2 and 3'rd questions.


Better way to restore to existing pg database We want to restore to an existing 9.3 pg database. What we ended up doing was:

1) delete the database and recreate again,
2) use pg_restore to restore.
3) reboot the ubuntu server to allow rails app to pick up the new database.

We tried the following pg_restore command and it did not restore to an existing database:

$pg_restore --dbname=existing_database --verbose /home/myname/dbbackup.backup

There was no change of existing_database after pg_restore. Is there a command we can restore to an existing pg database without deleting it first as we did? We have to use command line. Many thanks.

The command you showed looks fine.

pg_restore -d target_database /path/to/the/backup.dbbackup

At a guess, there were errors during the restore but you didn't see them. Most likely you were running with a user ID that did not have the correct permissions to perform the restore.

If you are certain that there is no data of any value in the target database (which will be deleted by the following command) try:

sudo -u postgres pg_restore --create --clean -d postgres /path/to/dbbackup.sql

If you have issues, post the output in an edit to your question, then comment here.

Also: You do not need to restart the server. At most, you should restart your Rails application. Usually that means restarting Apache, but it depends a bit on how you're running Rails. Restarting the whole server is completely unnecessary.


How to optimize a delete query with a subselect? This query needs to delete over 17 million rows, from a table containing 20 million.

DELETE FROM statements
where agreement_id IN (
    select id FROM agreements
    WHERE created < DATE_SUB(CURDATE(), INTERVAL 6  MONTH)
);

DELETE FROM agreements
WHERE created < DATE_SUB(CURDATE(), INTERVAL 6  MONTH)

It takes hours to run, am I missing something that could speed things up a bit?

The subselect by itself takes a few seconds, I don't understand why the delete takes so long.

Use Multiple-Table Syntax of DELETE statement.

DELETE s.*, a.*
FROM statements s, agreements a
WHERE s.agreement_id = a.id
  AND a.created < CURDATE() - INTERVAL 6 MONTH