Using schema + Filegroups or entirely separate database(same instance) to partition some big tables? I have the following challange:

I have a staging database which holds a few big tables (on SSD) but I start to run out of space on the SSDs. I use SQL server 2014 BI-Edition (= Standard Edition for the relational engine) so table partitioning out of the box is not available. My approach would be the splitting of the data into separate tables and joining them together in a view.

The data itself is (mostly) written once and then only read from.

What would be a better solution of the two suggested ones below? Why?

Solution A: Use Filegroups to move data to different disks:

create a two new filegroup on a separate disks (non-SSD, RAID5)
create several tables (e.g. one per Quarter Per Year) and move the old data to those tables on some rules (e.g. move data older thant 6 months)
one filegroup is readonly and the other one is read write. More current data resides in the read write filegroup and the older data is on the readonly filegroup
create the same index (same columns) for all tables
use a view to have transparent access to all data

Solution B:Use a database to hold the historic data

Implemented in more or less the same way but addng a database (in the same instance) as an additional layer

Thanks for sharing your ideas and advice.

Your problem is apparently all about storage space.

Your SSD array is not large enough to hold all your history on SSD.

Creating another filegroup is a good way to separate the use of storage space. That way you control what goes onto the historic data disk array. (When creating objects on a filegroup other than default, you must be sure to specify the filegroup you are using of course. )

Depending on how massive the historic data is, you may not need to split the data by quarters, etc.

For example, you were running well on the SSDs until space became a problem.  So, remembering that space is the problem, you could ask yourself questions like:

Are my indexes well-designed for this workload?
Do my queries require scanning all of the historic data?  (Apparently not, according to your comments above.)
Is the overhead of maintaining quarterly databases going to buy you significant query improvements?  (Especially if you will need data from more than one database.)

Making things simpler and well-designed should be a first goal.  But, if that is not sufficient then you should add layers as they are needed. Keeping performance statistics will help you measure the benefits.

There are plenty of web sites with help.  Here is one set of queries by Glenn Berry: http://www.sqlskills.com/blogs/glenn/category/dmv-queries/


Rman controlfile and rotation I got an Oracle 10g server with a bash script running every night which extract each datafile and create an rman script, which is launched from the bash script itself and make backups.

CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/opt/oracle/archivelogs/autobackup_control_file%F';
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
CROSSCHECK ARCHIVELOG ALL;
CROSSCHECK BACKUP;
ALLOCATE CHANNEL RMAN_BACK_CH01 TYPE DISK;
COPY LEVEL 0
  DATAFILE 1 TO '/opt/oracle/backup/datafile1.dbf'
, DATAFILE 2 TO '/opt/oracle/backup/datafile2.dbf'
...
, DATAFILE N TO '/opt/oracle/backup/datafileN.dbf'
;
SQL 'alter system archive log current';
BACKUP FORMAT '/opt/oracle/archivelogs/archive_logs_%d_%s-%t.bck' ARCHIVELOG ALL DELETE INPUT;
BACKUP CURRENT CONTROLFILE FORMAT '/opt/oracle/archivelogs/controlfile_%d_%s-%t.bck';
RELEASE CHANNEL RMAN_BACK_CH01;
}
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT BACKUP OF ARCHIVELOG UNTIL TIME='sysdate-6';

As you can see from the script the backups are made via datafile copy and archivelogs backup.
I got two questions:

on the archivelogs directory the script creates two controlfile backups (autobackup_control_filec-* and controlfile_SID_) which have quite the same size (6.1MB vs 6.2MB) but not exactly the same.
controlfile_SID_ file creation is clear from the syntax, but when the autobackup_control_filec-* is created? Perhaps during BACKUP ARCHIVELOG ALL command?
And why this different size? If they are both controlfile backup I expect they have the same size.
Datafile backup rotation seems to work fine, every day I found in /opt/oracle/backup only the backup files from the latest backup schedule, but in logs I don't find any trace of old backup deletion or messages on overwrite.
I suppose COPY LEVEL 0 command overwrite without any warning, is it right?

http://docs.oracle.com/cd/B19306_01/backup.102/b14191/rcmconc1.htm#BRADV111

If CONFIGURE CONTROLFILE AUTOBACKUP is ON, then RMAN automatically
backs up the control file and the current server parameter file (if
used to start up the database) in one of two circumstances: when a
successful backup must be recorded in the RMAN repository, and when a
structural change to the database affects the contents of the control
file which therefore must be backed up.
This means that the control file is backed up in the following
situations:

After every BACKUP command issued at the RMAN prompt.
At the end of a RUN block, if the last command in the block was BACKUP.
Whenever a BACKUP command within a RUN block is followed by a command that is not BACKUP.

In your case, the backup is created before RELEASE CHANNEL. The autobackup would happen after COPY, BACKUP ... ARCHIVELOG and BACKUP ... CONTROLFILE, but since these are in the same RUN block, why perform the work of creating the autobackup 3 times unnecessarily, when it is enough to perform the autobackup after the last BACKUP command?
The size is different because the autobackup contains the SPFILE also.
The autobackup finishes after the controlfile backup, so the autobackup contains the entry pointing to the previous controlfile backup, but the previous controlfile backup doesn't know about the autobackup, that is another difference between them.
When the controlfile grows, it gets more space allocated than it immediately needs, leaving some empty space in it that can be used later. Because of this, the controlfile and the controlfile autobackup can be the same size (when no SPFILE in use), but their content will be still different. Outdated entries can also be reused in the controlfile to prevent the controlfile growing too big.

COPY overwrites the copies of datafiles. DELETE NOPROMPT BACKUP OF ARCHIVELOG UNTIL TIME='sysdate-6' deletes archivelog backups older than 6 days.


Storing email inbox information I am working on a webmail client. I want to store the remote mailboxes information in a Postgres database. As a general rule of thumb, most IMAP servers will have a similar default folder structure like so:

INBOX

Drafts

Outbox

Sent Messages

Trash

Junk Mail

etc, etc.

As I add users to my system, this information will be repeated across most users. So here's my question:

Considering these are small-ish strings, am I better off to just store all the mailbox information in one row (ie: mailbox name, attributes, delimiter, parentId, hasChildren) or should I store the mailbox names in a separate table and join on a foreign key?

Right now I'm thinking the former as the cost of storing the extra information is probably less than the cost of throwing more computing power at the database to maintain the same performance. But I am a novice when it comes to database design and I want my application to scale well.

Joining the mailbox table to a "FolderNames" table using a foreign key will be problematic if any particular user wants to change the name of any given folder, without changing that name for everyone else.  You could get around this by having a nullable CustomFolderName column in the mailbox table.

Depending on the expected number of accounts, storing the name of the mailbox in the mailbox table is probably not a deal-breaker for performance, unless perhaps there are details you haven't shared with us.  When you say these are "smallish strings", don't specify the column as having a maximum length of 1000 characters if your app will limit the folder names to 15 characters.

Overthinking optimization early on can be burdensome to the development effort.  Focus on the database, just don't go entirely overboard at the very earliest stages of development.  You're quite likely to change the structure as development progresses anyway.  Ensuring you use the correct data types as appropriate is going to be far more important.  For instance, please don't store dates in string columns.  Use a date column.  If there is going to be a time element, use a date/time column.  Don't use 128 bit integers where 32 bit will suffice.


Does after insert trigger cause delay? MySQL 5.7 innoDB

My plan: Create an after insert trigger on one table, that copies some of the inserted data to another table. Create an before insert trigger on second table that includes some evaluation (collect data from another table) before doing the insert in second table or depend on the evaluation doesn't do the insert on the second table at all.

Will the planned triggers cause any locks on the tables/records they are created? Especially is there a possibility that the before insert trigger on second table will cause a lock or insert delay on the first table?

Some background: I have a heavy loaded table with actually 120GB data (growing) in it. About 15-40 inserts/sec.

In some occasions client connections raise up explosive and cause the applications to stall.

Not 100% sure but what I see is: Some queries in some occasions on that huge table does locks and delay the inserts. That causes the client connections to raise up.

The plan above, will put some work that actually clients do into the database itself. Is there a chance that my plan does eliminate that problem.

For doing a test I need to copy that huge table on a sandbox environment and simulate the inserts. Uff. So I ask before, is there is a chance that my plan works before occupy the net with all that data load.

The question is not the same, but my answer is:  https://dba.stackexchange.com/a/159012/1876

Don't use Triggers; instead batch the data and batch the processing.  It is a lot more efficient than doing everything one row at a time, especially when a spike occurs.

Another issue... If you let "too many" connections get started, yes, the entire system can stall.  This is sometimes because MySQL (and the OS) are carefully parceling out resources (CPU, I/O, etc) to more clients than the resources (cores, etc) can handle.  Throughput may be fine, but latency suffers terribly.


Do I need to create tablespace and also add all datafiles before importing a schema using datapump? Do I need to create tablespace and also add all datafiles before importing a schema using datapump? Or does impdp add all data files itself first and then unloads data into it?

impdp is able to create the tablespaces. You can even change the location of the datafiles if needed. Check the documentation for more information since there are many parameters with this tool (REMAP_DATAFILES and REUSE_DATAFILES for example).


Use ENUM in array of objects Is it possible to setup an ARRAY column with objects/JSON on a TABLE, forcing a parameter of the objects in the ARRAY to adhere to an ENUM, while keeping that object parameter unique?

Data examples:

ENUM val1, val2, val3

[{p1: val1, p2: 'something'}, {p1: val2, p2: 'something'}] <-- valid
[{p1: val1, p2: 'something'}, {p1: val4, p2: 'something'}] <-- not valid, val4 not an ENUM value
[{p1: val1, p2: 'something'}, {p1: val1, p2: 'something else'}] <-- not valid, p1 not unique

If it is possible, using PostgreSQL and sequelize, how would I go about it, setting up the column?

You have a few options,

You can just use CHECK conditions if it's on one table's jsonb column
CREATE TABLE with the possible values and set it as an fkey.
Reduce that jsonb to something more logical in the app, or in the db. Create a function such that f(jsonb)=>k and store k on the table, or possibly as an ENUM


Restoring a mysql database in windows 10 I am trying to restore a mysql database in windows 10 using the command below.

mysql -u root mydatabase < mydatabase.

My computer is quite fast but this is using about 99% of my disk space when I looked through my task manager. Sometime it takes about 2 hours to restore a database.

What can I do to make this faster?

Mysqldump is single threaded so you’re bound by the speed that 1 core can run at to play all your SQL commands in serialised fashion. There are some config variables that can be changed to reduce the amount of disk flushes needed and permit larger memory buffers for speed.

Innodb (if that’s what engine you use) variables (plus others)

innodb_buffer_pool_size
Innodb_log_file_size
innodb_flush_log_at_transaction_commit
Sync_binlog

Should get your research and tuning off to a start to make things faster.


High Page Faults/sec low Page Reads/sec I currently have Page Faults/sec avergaing 1269 while my Page Reads/sec is averaging 0.3.

I am just trying to interpret what this means.  I assume I am getting lots of soft page faults in this case and the issue the user is experiencing with slowness is due to the application having to wait on pages to become free in memory?

In which case should I be tracking locking and blocking?

You are getting soft faults and this is a non-issue. Soft faults are part of the normal page lifetime as the process working set gets continuously trimmed.

If you also experience application problems then you need to investigate the cause. Read How to analyse SQL Server performance for a starting point.


Return values as 1 or 0 together with percent I need a SELECT that will result in a percentage and columns with 1 and 0 for exist or not exist.

The goal is to generate data for a usage meter (Percentage 0.0-1.0) and each measurable value as 1 or 0.
It also has to show a row for every user in a users table, even if that user has no items at all.

Scenario:

In one table, a user can have many items, thet all have a specific ItemType.
Like type A, B, C, D or E
If a user has items in all types, the result should be (5/5 100%):
Percentage  A  B  C  D  E
1.0, 1, 1, 1, 1, 1
If a user has none of the types (0/5 0%):
Percentage  A  B  C  D  E
0.0, 0, 0, 0, 0, 0
If a user has some of the types (3/5 60%):
Percentage  A  B  C  D  E
0.6, 1, 1, 1, 0, 0
SAMPLE:

Use a CTE to calculate A,B,C,D and E using CASE or IIF and then calculate final percentatge.

;WITH tbl AS
(
SELECT
    u.id,
    u.name,
    CASE WHEN EXISTS(SELECT 1 FROM items WHERE user_id = u.id AND itemtype = 'A')
         THEN 1 ELSE 0 END AS [A],
    CASE WHEN EXISTS(SELECT 1 FROM items WHERE user_id = u.id AND itemtype = 'B')
         THEN 1 ELSE 0 END AS [B],
    CASE WHEN EXISTS(SELECT 1 FROM items WHERE user_id = u.id AND itemtype = 'C')
         THEN 1 ELSE 0 END AS [C],
    CASE WHEN EXISTS(SELECT 1 FROM items WHERE user_id = u.id AND itemtype = 'D')
         THEN 1 ELSE 0 END AS [D],
    CASE WHEN EXISTS(SELECT 1 FROM items WHERE user_id = u.id AND itemtype = 'E')
         THEN 1 ELSE 0 END AS [E]
FROM
    users u
)
SELECT
    id,
    name,
    CAST((A+B+C+D+E) / 5.0 as decimal(10,1)) AS Percentatge,
    A,
    B,
    C,
    D,
    E
FROM
    tbl;
GO

id | name  | Percentatge |  A |  B |  C |  D |  E
-: | :---- | :---------- | -: | -: | -: | -: | -:
 1 | user1 | 1.0         |  1 |  1 |  1 |  1 |  1
 2 | user2 | 0.4         |  1 |  1 |  0 |  0 |  0
 3 | user3 | 0.2         |  0 |  1 |  0 |  0 |  0
 4 | user4 | 0.0         |  0 |  0 |  0 |  0 |  0
 5 | user5 | 0.0         |  0 |  0 |  0 |  0 |  0

db<>fiddle here


Is there a way to add a column to a table without affecting performance? How do you add a column to a table in production without affecting its performance?  Or is this impossible?  I'm asking about Postgres.

For Postgres, adding a column without a default value is essentially a no-op as only the catalog tables get rewritten.

Apart from the short exclusive(!) lock there is no performance impact.

Although that lock is only held for a very short time, getting the lock on a busy system might be a problem because all open transactions and queries need to be finished before this lock can be acquired. And of course you need to make sure that the DDL gets committed immediately, because every access (including read-only selects) will be blocked until the alter table is committed.

Adding a column with a default value is a different thing though - that will affect performance because it is updating all existing rows with the default value.

If I'm not mistaken MySQL always rewrites the full table (and all indexes) if you add (or drop) a column (but it's been a while since I used MySQL so things could have changed).

Oracle works the same as Postgres.

I don't know about SQL Server, but I would be surprised if it did it differently.


Integrate Full cast and crew in a Movie Database I'd like to display full cast and crew from my movie database and was wondering if the structure below is OK. Basically, I want something like this https://www.themoviedb.org/movie/284052-doctor-strange/cast

[movies]
id,
name

[people]
id,
name

[jobs]
id,
name (ex: Director, Writer)

[casts]
person_id,
movie_id,
character_name

[crew]
person_id,
movie_id,
job_id

This looks good to me.

You have correctly defined 3 entities: Movies, Jobs, and People

You have correctly defined 2 associative entities: Cast & Crew

Your entities are independent of each other and the associative entities handle the many-to-many relationship...ie many People can work on many Movies.


Are there known parallelism issues with Sql Server 2016? Looking for anything such as known bugs in Sql Server 2016 SP1, CU4 and CU5 (both have same issue) on windows 2012 R2 on vmware. Esp. if where parallel threads wait on each other.

Issue:  After going production we have one specific query in an SSIS dataflow that was very poorly performing in 2016 (was fine in 2012).  So I found some indexes needed to be added so added them which cut down on runtime by about 80%. Then it started randomly freezing (say once or twice a day) never returning any rows. Doesn't seem to correlate to how busy the server is, locks, or data volume. We have very high cxpacket waits but who doesn't?  I'm running maxdop 8 with 12 virtual cores.

Activity monitor very often shows session id is blocked by same session id on 2016.  I never looked on 2012 to see if this happens. It also shows this when a query hangs.  No deadlocks.  I'm running with option recompile and default server maxdop 8 (database is maxdop 0).

Any ideas greatly appreciated.

Problem was due to use of the Vmware CPU Hot Plug feature in combination with Sql Server 2016.

This feature disables vNUMA and is not recommended by Microsoft or Vmware (see document Architecting Microsoft Sql Server on Vmware vSphere).

Switching this feature off and then rebooting the server resolved this issue and it resulted in average query performance of over 50% as well as showing improved utilization of CPUs.

Since we were running this Vmware setting when using Sql Server 2012 and did not experience freezing/hanging queries, my guess is something in 2016 makes this environmental setup more problematic. It could be the new default "soft Numa" configuration in 2016.

Btw...the query plan did not seem affected by this Vmware setting.  You can however see a difference when you run select * from sys.dm_os_memory_nodes;  With Numa switched off there is an added "Node 1" and the virtual address space reserved in node 0 goes from 100% of max mem with no Numa to 50% of total max mem when Numa awareness in vmware is turned on.


Why cannot I execute dbms_space_admin.drop_empty_segments despite granted? Did i miss something in the documentation about granting execute on DBMS_% or SYS packages?

I have:

GRANT EXECUTE ON sys.dbms_space_admin TO DEVELOPER_ROLE;
GRANT DEVELOPER_ROLE TO my_user;
ALTER USER my_user DEFAULT ROLE ALL;

But when my_user tries this:

EXEC SYS.DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS()

I get:

ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_SPACE_ADMIN' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

However, if only I grant directly - it works perfectly fine:

GRANT EXECUTE ON sys.dbms_space_admin TO my_user;
EXEC SYS.DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS();

What did I miss?

As stated by @Gary in the comment, here is a scenario where a user is still being unable to use the role granted to it even the user has been altered to active the role by default. The user called JAY is already connected to the database.

SQL> conn jay@oracdbpdb
Enter password:
Connected.

The SYS user performs the following operations to grant EXECUTE on a package to the user via a role.

SQL> CREATE ROLE developer_role;

Role created.

SQL> GRANT EXECUTE ON sys.dbms_space_admin TO developer_role;

Grant succeeded.

SQL> GRANT developer_roleTO jay;

Grant succeeded.

SQL> ALTER USER jay DEFAULT ROLE ALL;

User altered.

However, the user is unable to execute the PLSQL package/procedure.

SQL> show user
USER is "JAY"

SQL> EXEC SYS.DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS();
BEGIN SYS.DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS(); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_SPACE_ADMIN' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

If I grant the privilege directly to the user then the user is able to execute the PLSQL package.

This is expected behavior because the ALTER statement sets the default role when the user's session starts. Since the user has not been reconnected it was unable to execute the package. However, the user will be able to use the privilege as soon as we grant the privilege directly to the user.

The solution is that the user either should reconnect to the database or explicitly set the role as shown below.

SQL> set role role_name;

Role set.


How SQL server database reorganize memory I am using [sys].[master_files] table to determine the size of the database, however i didn't get a sensible value yet. If i am inserting 100 rows into a table ideally db size should increase while i am getting the same size even after inserting 1000 rows , sometimes it may decrease unexpectedly. Then i drop all the tables from the database and after that it shows 188 MB from 180 , size increased after dropping all tables. This is also not good.

When i am using DBCC SHINKDATABASE ('dbName') , i am getting a good value. It reduced to 22 MB. My question is how i can determine the size of a particular DB , which is showing sensible changes. Any help will be appreciated , thanks.

SQL Server does its own database file management - it does grow your .mdf file every time you need a few bytes more space.

Instead, for each database file, you've defined an initial size and how it will grow - by percent or by a fixed size. You can see these values by issuing this query:

SELECT
    name, physical_name,
    size, max_size, growth, is_percent_growth
FROM sys.database_files

If your database file is set to e.g. 180 MB in size, and the growth is set to 50 MB, then as soon as the space in the allocated 180 MB is no longer sufficient, the file will be grown by 50 MB (to 230 MB).

By default, SQL Server also never shrinks data files - what for? Just to grow them again later on? It keeps track of unused space inside the file, and will reuse that space, until again, the file size isn't sufficient anymore, at which point it will be grown again (by that settings that's defined per file).


Large update and delete based on another table I have a very active PostgreSQL 10.4 DB, dealt with through SQLAlchemy, so SQL is not my forte.

Among its tables, there are master with a primary key id and some nonindexed string column value, and slave with a foreign key mid towards master.id. The table master is just a bit short of 100M rows and is very frequently accessed by the app (usually several times per second, but always just a few rows at a time), while slave has roughly double that and is accessed in a similar manner.

I want to do two things:

Delete from slave and master:

delete from slave
    using master
    where
        slave.mid = master.id
        and master.value in ('some', 'set', 'of', 'values')
delete from master
    where value in ('the', 'same', 'set', 'of', 'values')

SQLAlchemy didn't create any cascade delete rules for master, so I cannot just run the latter of the two queries, as it causes a foreign key constraint violation.
Update master:

update master
    set value=<case expression>
    where value in ('a', 'different', 'set', 'of', 'values')

The number of rows to be deleted is estimated to be few millions (maybe a mil or 2 mils; certainly not much more).

I have another constraint that I'd like to keep (but could ditch it if absolutely necessary): a dry run is done the same way as the live execution, except that I call rollback instead of commit. So, for example, doing the update top would end up in an infinite loop if I kept rolling back after each call.

The above codes work nicely on a small DB used to just test if the code works as intended, but they are super-slow on a copy of the production DB. So, my first issue here is the performance.

My second issue would be locking, because the production is hit a lot and it would be nice to be able to do this seamlessly, without causing any downtime and/or timeouts for the app.

This got me to start thinking about doing deletes/updates in batches, but this goes beyond my knowledge of PostgreSQL (or SQL in general). How can I get these to work the way I want them to?

Edit

After some consideration, I altered the slave tables (there is two of them) to delete on cascade, which solved the deletion problem. There is still an issue of efficient updating, most likely in chunks.

A delete with an IN condition might be faster than the JOIN:

delete from slave
where mid in (select master.id
              from master
              where master.value in ('some', 'set', 'of', 'values'));

delete from master
    where value in ('the', 'same', 'set', 'of', 'values');

Another option that might be faster is to delete both tables in a single statement. This can also be used to delete multiple slaves.

with removed_master (id) as (
  delete from master
  where value in ('some', 'set', 'of', 'values')
  returning id
), remove_slave1 as (
  delete from slave_one
  where mid in (select id from removed_master)
)
delete from slave_two
where mid in (select id from removed_master);

This works as the foreign key constraints are evaluated when the statement is finished, not when each individual row is deleted. This has the additional benefit that you have to specify the "master values" only once.


MySQL SSL error: Unable to get private key Getting an error while trying to enable encryption:

[ERROR] SSL error: Unable to get private key from '/etc/mysql/ssl/nginx.key'
[Warning] Failed to set up SSL because of the following SSL library error: Unable to get private key

/etc/mysql/conf.d/encrypt.cnf:

[mysqld]
ssl-ca=/etc/mysql/ssl/nginx.ca
ssl-cert=/etc/mysql/ssl/nginx.crt
ssl-key=/etc/mysql/ssl/nginx.key

The key is generated like that:

openssl req -newkey rsa:4096 -nodes -keyout nginx.key -subj "/C=US/ST=California/L=Sacramento/O=MyOrg/OU=MyDev/CN=MyApp"

The openssl checks it fine.

I've tried to change permissions, owners, file names, moving to different directories, disabling SELinux (it's already disabled), looking at AppArmor logs (it doesn't block it), changing 'BEGIN/END PRIVATE KEY' to 'BEGIN/END RSA PRIVATE KEY'.

What's wrong with it?

Ok, apparently just changing 'BEGIN PRIVATE KEY' to 'BEGIN RSA PRIVATE KEY' isn't enough.

It must be properly converted from PKCS#8 to PKCS#1 Reference

openssl rsa -in /etc/mysql/ssl/nginx.key -out ~/nginx.key.rsa


Postgres randomly fails silently on huge CSV import With Postgres 10.12 on Windows 10, I am importing at 67 GB CSV file into a database using a simple COPY query:

COPY table_name FROM 'c:/temp/data.csv' WITH (FORMAT csv, HEADER);

The import fails silently at random intervals, with the intervals ranging from 1.x minutes to over 10 minutes. This is the only process on the device with major resource use, so the time-difference cannot be explained by other processes competing for resources.

(Why 10.12? Postgres 11+ has a bug that blocks straightforward imports of large files. Yes, I know I could use the cat command with Postgres 12 (see third answer), and I actually did, and ran into the same problem.)

While the query runs, the Postgres process has sustained, high disk activity, generally 16 - 46 MB/s (running this on a Surface Pro 4 i7).

The response from the query always indicates success. The latest time I ran it, the response was Query returned successfully in 13 min 28 secs.

The resulting table is always empty but behaves strangely. If I count rows (SELECT COUNT(*) FROM table_name), I have to wait through about 45 seconds of very high disk activity before it returns 0. Viewing the table through Pgadmin 4 has a similar outcome: many seconds of high disk activity, then 0 rows returned.

There is data in there somewhere, however. If I go to C:\Program Files\PostgreSQL\10\data\base and check the size, there's over 11 GB of data in there, so some data is coming in. I inspected further and found several 1 GB files in C:\Program Files\PostgreSQL\10\data\base\16393.

I have segmented the first 45 lines of the CSV into a separate file. If I import that file instead, it runs fine, and I get 44 rows in the table (row 1 is a header and is not imported).

This ended up being a bug with either Postgres or pgAdmin. The problem is I was getting a success message when the query ended instead of a failure message. That threw me off. After checking the logs, I found the error, and it was easy to solve.


Procedure with "bind varibale" does not return the desired result set I have a table with below structure:
create table CUST_MODERN_CHANNEL_BRANCH
(
  customer_Num      NUMBER,
  channel_Code  NUMBER(5)
)

Some example data :
   Customer_Num       Channel_Code
  =============     ==============
       1                 5
       1                 4
       1                 6
       2                 3
       3                 5

I wrote a procedure which you can see the code below :
create or replace procedure query_test(res          out sys_refcursor,
                                       order_by     number,
                                       customer_num number) is
  qry varchar2(1000) := ' select * from cust_modern_channel_branch ';
begin

  if customer_num is not null then
    qry := qry || ' where customer_num = :cust ';
  end if;

  if order_by is not null then
    qry := qry || ' order by :ord ';
  end if;

        --******* Generate Result

  if customer_num is not null then
    if order_by is not null then
      open res for qry
        using customer_num, order_by;
    else
      open res for qry
        using order_by;
    end if;
  end if;
end;

I'm using bind variable in my procedure to protect it from injection and make it better in terms of performance. The problem is that when I TEST my procedure with input parameters customer_num=1 / Order_by=2 the final result is not sorted on the second column and it is like this:
  Customer_Num       Channel_Code
  =============     ==============
       1                 5
       1                 4
       1                 6

But when I write a query with order by, it gives me the output below:
select *
from CUST_MODERN_CHANNEL_BRANCH t
where t.customer_num = 1
order by 2

  Customer_Num       Channel_Code
  =============     ==============
       1                 4
       1                 5
       1                 6

Is that something wrong with my procedure? Or the way I used bind variables?
Thanks in advance

order by 2 does not equal to order by :ord where :ord = 2.
With order by 2, 2 represents a position, meaning the result must be sorted always by 2nd column in select list.
With order by :ord, :ord is an expression, which will be evaluated to a value, and sorting will be done by that value, not a column.
If you use order by :ord where :ord = 2, that is handled like like order by '2' (logically, ignore the type difference for now).
Do not bind the order by position.


Combining 3 select queries I am working with a MySQL database. I am suppose to combine three select queries, to "improve performance". Each select below is dependent on the previous ID retrieved.

Here is the workflow to get data references between the tables.

There are three tables:

pUserMatch  |  pGroupMatch  |  ProfileData
----------- |  -----------  |  ----------
* username
* GroupID .... GroupID
               ProfileID ....  ID
                               ParentUser

My three select queries...

# get the group ID for the specific username
group_id = select GroupID from Profiles.pUserMatch where username = "<username>";

# now, get the group match ID for the group ID
profile_id = select ProfileID from Profiles.pGroupMatch where GroupID = "<group_id>"

# now, get the profile data for this ID
profile = select * from Profiles.ProfileData where ID = "<profile_id>"

I know a little bit about using joins, but I thought that they were only used when multiple tables have matching data.

So far, I've tried the following...

# multiple select from tables
select usermatch.username, groupmatch.GroupID
  from pUserMatch as usermatch, pGroupMatch as groupmatch
  where usermatch.username = <"username">

# inner join...
select pUserMatch.GroupID, pGroupMatch.GroupID,
  from pUserMatch
    inner join pGroupMatch
      on pUserMatch.GroupID = pGroupMatch.GroupID

I need to select the pGroupMatch.GroupID and ProfileData.ID based on a username param. Is there a way to query this data in a single statement?

As far as I know you don't need foreign keys to make joins.

Also, you don't even need to use the "inner/outer join" syntax to make joins!

So, following your schema:

pUserMatch  |  pGroupMatch  |  ProfileData
----------- |  -----------  |  ----------
* username
* GroupID .... GroupID
               ProfileID ....  ID
                               ParentUser

The query would be:

select u.username, u.GroupID, g.ProfileID, p.ParentUser
from pUserMatch u, pGroupMatch g, ProfileData p
where u.GroupID = g.GroupID
  and g.ProfileID = p.ID
  and usermatch.username = <"username">

Anyway the "join" syntax would be the following:

select u.username, u.GroupID, g.ProfileID, p.ParentUser
from pUserMatch u
  inner join pGroupMatch g
    on u.GroupID = g.GroupID
  inner join ProfileData p
    on g.ProfileID = p.ID
 where usermatch.username = <"username">

Use "left outer join" instead of "inner join" if you want to see all the records of the first table (i.e. pUserMatch) even if there's no the linked record on the other one (i.e. pGroupMatch).


Statistics on filestream column When I try to create a statistics on a filestream column, I get the following error message:

  Column 'MyColumn' in table 'MyTable' is of a type
  that is invalid for use as a key column in an index or statistics.

This is documented in the BOL page for CREATE STATISTICS and it's not an issue.

However, when I run a query with a predicate on the column (IS NOT NULL, for the record), a statistics is auto-created. Why is this disallowed for manual creation but still possible for auto creation?

The reason why I want to create a statistics manually is that I want to mark it NORECOMPUTE in order to avoid having to scan several GBs of data. I know I can let SQL Server auto create it and then mark it NORECOMPUTE later, but auto creation is triggered with the default sample, which I could override to 0 ROWS in manual creation. I would also like to know if there is a reason for this.

If you have applied the latest update for SQL Server 2012 - Cumulative Update 2 for Service Pack 3 as at the time of writing - and the issue persists, you should open a support ticket, or report the issue on Connect.

The underlying issue shares some common features with an existing report:

String summary - slow statistics creation on VARBINARY(MAX) column

...which might give you some ideas for a workaround.

For example, there is a small chance that creating statistics on a computed column casting the column to varbinary(200) might work. That cast may also explain why the auto-stats succeeds while you cannot create statistics manually.


oracle 10g - ORA00257 is there any way to recover from a ORA-00257: archiver error?

The archiver disks broke, but DB continue operation 2 sequences more.
I have no access to the archiver disk anymore

 SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /drbak/arch/terdb/pro/va
Oldest online log sequence     46612
Next log sequence to archive   46612
Current log sequence           46614

SQL> show parameter db_recovery_file_dest;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 50000M

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;
NAME
--------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------

 5.2429E+10          0                 0               0

If your existing archive location is no longer available, change it to one that is:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = ('LOCATION=<new location>');

You'll want to take a Level 0 backup immediately afterwards. With missing archivelogs, your database is no longer recoverable.


What exactly is binding in DB2? I recently crossed over from being a Java developer to an actual DBA in our company. I am learning the ropes, so to speak, about being a DBA (which is actually somewhat of a new position for our company).

I have seen several scripts where we run the command DB2 BIND bind_file other_parameters.

I am puzzled by what these do. I have asked our other DBAs, but they were not able to explain it to me in a way that made sense. I have looked at IBM's Information Center for the BIND command, but it wasn't clear to me either.

I know binding is somehow important, because we are supposed to run REORGS, run STATS, and re BIND on our databases regularly to assist with performance.

Since I am still a n00b DBA, I was wondering if anyone can provide a "What is BINDing for Dummies?" explanation?

  EDIT: In edition to the answer below, I recently came across the following developerworks article: "DB2 packages: Concepts, examples,
  and common problems: Understanding DB2 system and user application
  packages". Very helpful. Especially for the system packages, which
  is what we were mostly running into.

  20130905 EDIT: This blog entry by DB2 DBA Ember Crooks is stellar with regards to binding and what it means. She also wrote a
  previous entry on packages not being found and when to up the
  CLIPKG number for the binds and what that means. These articles are
  very well explained. Basically like reading "DB2 Binding and Packages
  for Dummies" if such a thing existed.

I see your Info Center link goes to LUW 9.7, and you mention that you've programmed in Java, but most of the experience I have with binding is with DB2 on the Mainframe with COBOL.  So, you may need to adapt the explanation a bit (but generally, the concepts should be the same).

I believe that binding is only relevant when you are compiling programs that include embedded SQL which is precompiled (statically bound SQL).  If, for example, you're using JDBC, you aren't required to run a BIND.  The JDBC driver will PREPARE the statement dynamically.

When you run a program through a DB2 pre-compiler, PRECOMPILE runs through your program, and if it finds any embedded SQL (in COBOL, these are statement blocks that go from EXEC SQL to END-EXEC.), it carefully rips the SQL out, and replaces it with a call to the COBOL-DB2 interface.  After this, there are two outputs of the PRECOMPILE, the COBOL source that has had all the embedded SQL removed (A from now on), and a DBRM that contains all the SQL that was removed (B).

Precompile does do some basic syntax checking, but be aware that the checks are only based on your table declarations within the program.  It doesn't attach to DB2 to verify these!

These two files are completely separate, and when you run the COBOL program, it has to find an A and a B that were generated at the same time.

At this point, A is compiled and linked with the standard COBOL compiler into a load module and placed in a load library to be used later.

However, there is still a lot of work to be done with B, the DBRM.  This is where BIND comes in.  BIND is sort of like a compiler for the embedded SQL code, and the output of the "compile" is a package.

In order to BIND the SQL into an executable "package", the BIND process attaches to DB2 and does a few things:

Verifies that the current AuthID is authorized to perform a bind.
Checks the syntax of your SQL, with help from the data in the DB2 catalog.
Finally, and most importantly, the bind will optimize your SQL

During the last step, all of your SQL is run through the Optimizer, which takes into account all of the statistics and various paths that the DB2 engine could take to fetch your data.  It then chooses the path it came up with that has the lowest cost associated (with newer versions of DB2 [DB2 10 for z/OS], it may decide to take a "higher cost", but "lower risk" path).  Once the path is selected, it is compiled and becomes a package, which is stored in the catalog (you can see all of your current packages with SELECT * FROM SYSIBM.SYSPACKAGE (z/OS)).

Finally, there is a last piece that allows our programs to reunite with their packages, the PLAN.  You create a plan by doing another BIND (BIND PLAN).  A plan is a collection of packages that the program is allowed to look through to find the package that shares the same name.  With COBOL, you specify which plan the program should search in in your JCL.

In short, compiled code goes through these steps to generate a usable BIND PLAN:

Precompile -> Creates a DBRM (with C[++], the precompiler outputs the precompiled SQL to an HFS file, which can be sent through the command-line bind program) -> the DBRM is optimized and a set of access paths (a package) is created -> The package is added to a BIND PLAN, which is a group of packages that allow you to create a "search path" for your programs to look through.

Since these programs are statically bound, if your table statistics change drastically, then the access path the optimizer chose at bind-time might not be the best path anymore, and re-binding will allow it to re-evaluate the SQL and perhaps choose a better path.

Edit (update for comment): If you are using the command line processor, you can pass in either a single bind package (.bnd), or a list of bind filenames (.lst).  If you pass in a list, the filename has to be prepended with a @ (e.g. /path/to/@packages.lst).  Inside the .lst file, you can either put each package on an individual line, or you can separate them with +:

package1.bnd
package2.bnd
package3.bnd+package4.bnd+package5.bnd


Make database writeable (Azure MS Sql) I have a bacpac file from a read-only database hosted on Azure. When imported to a new server the database is also read-only.

I’ve tried looking for answers with no luck.

How can I change it to a writeable?

It doesn't look like there are any supported ways to change this.  However...

I noticed that the bacpac file is just a zip archive, so I unzipped it.  The basic structure of the folder looks like this:

I then used KDiff to diff two bacpacs of the exact same data - one that was read-write, and one that was read-only.  The main difference of substance was in model.xml:

So you should be able to remove those two lines* from the bacpac, and then import it, and it will be read-write! I attempted this on my local Developer Edition copy of SQL Server 2016, but got this error message:

  Could not load schema model from package. (Microsoft.SqlServer.Dac)

  The calculated checksum for model.xml in the package C:\Users\username\Desktop\my_database\my_database.bacpac is different from the saved checksum. (Microsoft.Data.Tools.Schema.Sql)

Which led me to this blog post: Azure SQL Database Support - Editing a .bacpac file

  There are times when for whatever reason you just wish you could make a change to a .bacpac file before you import it into Azure.

So I followed the instructions there, running this PowerShell script to generate a new checksum:

$modelXmlPath = Read-Host "model.xml file path"

$hasher = [System.Security.Cryptography.HashAlgorithm]::Create("System.Security.Cryptography.SHA256CryptoServiceProvider")

$fileStream = new-object System.IO.FileStream ` -ArgumentList @($modelXmlPath, [System.IO.FileMode]::Open)

$hash = $hasher.ComputeHash($fileStream)

$hashString = ""

Foreach ($b in $hash) { $hashString += $b.ToString("X2") }

$fileStream.Close()

$hashString

Then updating the Origin.xml checksum line at the end:

<Checksums>
    <Checksum Uri="/model.xml">long checksum number goes here</Checksum>
</Checksums>

Zipped all the files again (I used the "Deflate" algorithm in 7-zip), and then imported the database.  It came in perfectly as a normal, read-write database.

*Note: I didn't explicitly enable snapshot isolation, that happens automatically when you set a database to read-only.  Not even Paul White is sure why, so what hope do the rest of us have?  For more, see Snapshot_isolation_state_desc on a Read only/Stand by Database


How do I pull a backup of an oracle database so I can send it to a vendor First of all, I know squat about Oracle databases.

A vendor who is looking at converting our data from an older piece of software with an Oracle DB back-end wants a dump of our database to see what they need to do to bring that data over to our new software which is MS SQL Server based. I know in MS SQL Server, I right-click on the database and say backup, and I can create a backup of the database to a file and FTP that backup to the vendor. How would I do the equivalent in Oracle?

It appears like we might have version 10g of Oracle.

In the Two Day DBA Guide see  see the section called "Performing a Whole Database Backup".  It explains how to use Database Control to do a full backup.  If you have 11g see this version.  Assuming Database Control is working this would probably be the easiest for someone who knows nothing about Oracle.

On the other hand, the database may already have regular backups done on it in which case you can just send one of those.  If it doesn't or the information about them hasn't been retained, then it would seem that the data would not be important enough to convert.


For each implementation in joins deliveries Table has customer_id and the item_id they want to buy. items table has items with item_id as primary as below

 deliveries                                 items
id  customer_id  item_id               item_id       item_name
1    10           100                     100        Delicious
2    10           111                     111        Super Delicious
3    12           100

I want to see for every customer (10, 12) whether they bought the two items (Delicious and Super Delicious).

This I tried:

SELECT * FROM items LEFT JOIN deliveries ON items.item_id = deliveries.item_id

Here specifically, I want to see customer with id 12 has not bought item_id 111 as Null, which would be 4th row in the result.

To get the full domain you need a cartesian product (cross join in sql lingo) between the two sets:

select distinct d.customer_id, i.item_id
from deliveries d
cross join items i;

from this set you can subtract the actual deliveries:

select distinct d.customer_id, i.item_id
from deliveries d
cross join items i
where not exists (
    select 1 from deliveries d2
    where i.item_id = d2.item_id
    and d.customer_id = d2.customer_id
);

I'm not clear on how this null comes into play, but you can add a flag that says that this product has not been bought by this customer as:

select distinct d.customer_id, i.item_id, 'NOT BOUGHT' as status
from deliveries d
cross join items i
where not exists (
    select 1 from deliveries d2
    where i.item_id = d2.item_id
    and d.customer_id = d2.customer_id
);

Finally, you can add the deliveries table to this set as:

select distinct d.customer_id, i.item_id, 'NOT BOUGHT' as status
from deliveries d
cross join items i
where not exists (
    select 1 from deliveries d2
    where i.item_id = d2.item_id
    and d.customer_id = d2.customer_id
);
union all
select d.customer_id, d.item_id, 'BOUGHT' as status
from deliveries d;

Not sure if it answers your question, but I think it will give you a start.


text positioning for select list I have following issue, I have hierarchy table where every record has level, code and description and I need to have description to look as it is was one column.

Example code

CREATE TABLE #tmp (Value NVARCHAR(255))
INSERT INTO #tmp (Value) VALUES ('L1 [ABC]'),('    L2 [DEFGH]'),('        L3 [E]')

SELECT
Value + SPACE(50 - DATALENGTH(Value)) + 'Description text'
FROM #tmp

Output

But i need to have description to start at red line for every hierarchy record.
Any idea ?

You should use len() instead of datalength() to get the number of characters in Value. They are different because Value is a double byte string (nvarchar).

CREATE TABLE #tmp (Value NVARCHAR(255))
INSERT INTO #tmp (Value) VALUES ('L1 [ABC]'),('    L2 [DEFGH]'),('        L3 [E]')

SELECT
Value + SPACE(50 - DATALENGTH(Value)) + 'Description text'
FROM #tmp

SELECT
Value + SPACE(50 - LEN(Value)) + 'Description text'
FROM #tmp

Result:

----------------------------------------------------------------------------
L1 [ABC]                                  Description text
    L2 [DEFGH]                      Description text
        L3 [E]                      Description text

(3 row(s) affected)

----------------------------------------------------------------------------
L1 [ABC]                                          Description text
    L2 [DEFGH]                                    Description text
        L3 [E]                                    Description text


Stored Procedure Asking for Input While Processing So I am asking this to see if this is something that is even possible within SQL.

I am currently writing up some stored procedures for a VB.Net application I am working on to use to interact with the data within my database.

I am creating a procedure to delete a set of records, but want to know if it's possible for the Procedure itself to sort of ask for confirmation that the item is to be deleted.  Something like a message prompt or something where the procedure pauses to wait for further input.

If this isn't doable, I know how I can manage it on the VB.Net side, I am just trying to keep as much of the SQL and database stuff in the database itself as possible.

Short answer is that a stored procedure cannot prompt to accept an input to utilize in the transaction after it has been executed.

What you could do is use a stored procedure to obtain a count or the value you are looking to validate. Based on this, you can use a logic function in your code to then call the second stored procedure which performs the action.

Likewise this IF ELSE logic could be incorporated into the procedure. Let's say you want to delete all records from a table where the value = 1, you expect 50 deletes. You could call the stored procedure and input the value 50 and it would run:

SELECT COUNT(*) FROM TABLE WHERE Value = 1

You can do validation to say that count was greater, less, or equal to 50, and then it can either call another procedure and pass another parameter to it which could be a delete procedure, or it could just end the action.

I'm typically against daisy chaining stored procedures and I like to keep the business logic in the application though, so I would suggest handling it in VB.


MySQL - installed version 5.5 but mysql --version says 5.0.95 I'm running CentOS 5.2 and upgraded MySQL to 5.5. When I run mysql I get this:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.45 MySQL Community Server (GPL)

But when I run mysql --version I get this:

mysql  Ver 14.12 Distrib 5.0.95, for redhat-linux-gnu (x86_64) using readline 5.1

Since the mysqld is mysql55-mysqld I'm sure I'm running 5.5, but why am I getting the wrong version with the version command?

This problem may happen if you have multi MySQL instances on the same machine. There was pre-installed MySQL when the OS was installed, or using auto installation methods like yum: in this case when you type mysql it will point to the pre-installed one since the environment variable point there. you can test it by going to /mysql/install/path/bin use ./mysql --version this should show you the exact version of MySQL.


Copy Azure Hyperscale DB I need to copy a Hyperscale DB to another server in the same region/location, (the copy function is in coming soon state but not already available) does someone know how?

Database copy for Hyperscale is now in Public Preview as announced here on Sep 22, 2020.
If you don't like the current functionality, another option is creating a new database by restoring an existing backup.
When a copy is needed to another region use a geo-restore of the database. Create a SQL Database server in the target region if you do not already have an appropriate server there. This server should be owned by the same subscription as the original (source) server. Finally perform steps provided here.


How can we create a table from stored procedure? CREATE DEFINER=`root`@`localhost`
PROCEDURE `create_tb_one`(In tableName varchar(255))
BEGIN

    CREATE table tableName(
        ID INT      NOT NULL,
        SNAME VARCHAR(100)  NOT NULL,
        SSTATE VARCHAR(100) NOT NULL,
        TIME_STAMP VARCHAR(45) NOT NULL,
        constraint FK_SENSOR
            foreign key (SNAME)
            references sensorInfo(SName)
            on delete set null
    );END

Now, when I call this procedure by passing a String, it is not creating a table and showing this following error code:

  call create_tb_one(first) Error Code: 1054. Unknown column 'first' in 'field list'    0.00024 sec

I would do it with 2 steps:
I would create a table template allowing to create as many tables as you want based on that table. Advantage is you only need to modify the table template, you don't need to touch the SP.

Create a table template;

CREATE table tableName_template(
    ID INT NOT NULL,
    SNAME VARCHAR(100)  NOT NULL,
    SSTATE VARCHAR(100) NOT NULL,
    TIME_STAMP VARCHAR(45) NOT NULL,
    constraint FK_SENSOR
        foreign key (SNAME)
        references sensorInfo(SName)
        on delete set null
);

Use this code

DELIMITER $$
CREATE DEFINER=`xxx`@`localhost`  (do not use root !! Create dedicated account)
PROCEDURE `create_tb_one` (In tableName varchar(255))
BEGIN

    If not exists (select 1 FROM information_schema.tables
    WHERE table_schema=DATABASE()
    AND table_name=tableName )
    Then

        SET @sql = CONCAT('CREATE TABLE ',tableName,' LIKE  tableName_template');
        PREPARE s FROM @sql;
        EXECUTE s;
        DEALLOCATE PREPARE s;
    END IF;

END $$
DELIMITER ;


Database recovery pending on external drive I have database with mdf and ldf file residing on external drive. There was a long powercut and the database restarted. Now the database has gone for recovery pending. The database is around 600 gb.

I have tried the following commands

ALTER DATABASE ‘DATBASE NAME’ SET OFFLINE WITH ROLLBACK IMMEDIATE

ALTER DATABASE ‘DATBASE NAME’ SET ONLINE WITH ROLLBACK IMMEDIATE

but i am getting the following error

  Msg 5120, Level 16, State 101, Line 4 Unable to open the physical file
  "G:\SQL_DATA\TEST_NEW.mdf". Operating system error 5: "5(Access is
  denied.)". File activation failure. The physical file name
  "G:\SQL_DATA\TEST_NEW_log.ldf" may be incorrect. Msg 5181, Level 16,
  State 5, Line 4 Could not restart database "TEST_NEW". Reverting to
  the previous status. Msg 5069, Level 16, State 1, Line 4 ALTER
  DATABASE statement failed.

I have tried all options.

Do any body has a working solution. Please share it will save my day.

As previously mentioned, make sure the SQL service account has permissions on the files.
Here is a quick snippet to help you do that:

-- From SQL:
-- get full path for master
SELECT SUBSTRING(physical_name, 1,
CHARINDEX(N'master.mdf',
LOWER(physical_name)) - 1) DataFileLocation
FROM master.sys.master_files
WHERE database_id = 1 AND FILE_ID = 1

-- get the service accounts
SELECT servicename, service_account FROM sys.dm_server_services

# From Powershell (run as admin) and replace with values you got from the previous step

# replace with master db path
$masterDBLocation = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL1\MSSQL\DATA\'

# replace with SQL service account
$serviceAccount = 'DOMAIN\svc_DatabaseEngine'

$mdf = 'G:\SQL_DATA\TEST_NEW.mdf'
$ldf = 'G:\SQL_DATA\TEST_NEW_log.ldf'

$Acl = Get-Acl $masterDBLocation
$Ar = New-Object System.Security.AccessControl.FileSystemAccessRule($serviceAccount, "FullControl", "Allow")
$Acl.SetAccessRule($Ar)

Set-Acl -Path $mdf -AclObject $Acl
Set-Acl -Path $ldf -AclObject $Acl


Does varchar size matter in temporary tables? There is a debate at my wife's work about just using varchar(255) for all varchar fields in temporary tables in stored procedures.  Basically, one camp wants to use 255 because it will always work even if the definition changes, and the other camp wants to stick with the size in the source tables for potential performance improvements.

Is the performance camp right?  Are there other implications?  They are using SQL Server.

Depending on how you are using your temp tables, you could run into a data truncation issue.

This example is a bit contrived, but it illustrates my point.
Example:

Your user table column is varchar(50).
Your temp table column is varchar(255).
You have a record with 45 characters in that column in your user table.
In your procedure, you concatenate ' - for the win' to the end of that column, prior to merging that temp table into your user table.

The temp table would gladly accept the new varchar value with a length of 59.  However, your user table could not.  Depending on how you handle this in your procedure, this could result in truncation or an error.

Unless you document and account for these issues, your procedure could perform in an unexpected manner.

Personally, I do not think there is an answer to this question that is correct 100% of the time.  It really depends on how you are using those temp tables.

Hope this helps


Prevent COUNT() Subquery using window functions A developer colleague of mine has asked me to optimize statements for him. I have had a brief introduction to current level SQL technology and functionality and have the feeling that especially the window functions could help me.

The query in question is currently like this:

SELECT
T1.FIELD1,
T1.FIELD2
...
(SELECT COUNT (T2.FIELD1) FROM OUTER_TABLE_NAME T2 WHERE T2.FOREIGN_KEY = T1.PRIMARY_KEY) AS COMMENTS
FROM T1
;

This forces the database to run the subquery for each record from the primary table T1, which is generally slow. A better way would be if there was a window function that could express the same. However, it must not multiply the records from the primary table T1.

I thought about something like this:

SELECT
T1.FIELD1,
T2.FIELD2
...
COUNT(T2.FOREIGN_KEY) OVER (PARTITION BY T2.FOREIGN_KEY)
FROM T1
LEFT OUTER JOIN T2 ON T2.FOREIGN_KEY = T1.KEY
;

It needs to be an OUTER JOIN as to not filter records from the primary table T1. However, this way would multiply the records from the primary table T1 for every matching record from the secondary table T2.

Is there a way to express the count() using either window functions or any other non-proprietary methodology in Oracle 12.2?

SELECT
T1.FIELD1,
T2.FIELD2,
...
NVL(T2.COMMENTS, 0) AS COMMENTS
FROM T1
LEFT OUTER JOIN
(SELECT FOREIGN_KEY, COUNT (FIELD1) AS COMMENTS FROM OUTER_TABLE_NAME GROUP BY FOREIGN_KEY) T2
ON T2.FOREIGN_KEY = T1.PRIMARY_KEY
;


Need help about normalizing database to 3NF I am currently working on a city travel guide application which let users explore places and plan trip.

In the account module, the following functions are to be considered:

User can register by providing primary email and password.
Registered users can add their personal details like they can add full name, secondary email, country and city.
User can setup their profile like they can add profile picture, Display name, places they have visited etc.
When they open own/ other's profile, their profile info (like photo and Display name) and statistics like their 'trip' count, 'likes' count, 'reviews' count are to be shown.

I have designed database tables for account module as follows:

 1. User_Account_details(user_id, primary_email, secondary_email, password)
 2. User_Personal_details(user_id, first_name, last_name, country, city)
 3. User_Profile(user_id, profile_picture, display_name)
 4. User_Statistics(user_id, trips_count, likes_count, reviews_count)

There are 3 more tables for cities and countries so that when user add country , then the app retrieve it from database and according cities are displayed in suggestions for city textbox.

 1. country(country_id, country_name)
 2. city(city_id, city_name)
 3. country_city_association(country_id, city_id)

Can anyone tell me the database tables are properly normalized to 3NF or not? And if yes, one more confusion: Should I normalize tables upto 3NF or BCNF? sorry, I am a newbie in database-design. plz help.

You don't need four tables for the user account data. Each user only has one primary email, one secondary email, one first name, one profile picture, one count of trips etc. If there's only one of each entry per user, why not put them all in the same table?

You can set a lot of the fields to be nullable so that they don't all have to be filled in, and you might also want a "registration status" field to show if they're a guest or a registered user.

If you only want to show users certain parts of another user's profile, you don't need to store the publicly available information in a separate table, you can use a query like this:

select profile_picture, display_name, trips_count, likes_count, reviews_count
from user_table where user_id = @SelectedUser


Enterpise Manager 12c to Database 11g Does Oracle Enterpise Manager 12c can be install with Oracle Database 11g? Because the Oracle Enterprise Manager 11g is no longer available.

Yes it can. We are currently using it on a 11g RAC database.

Do you need any other details?


When to use views in MySQL? When creating tables from multiple joins for use in analysis, when is it preferred to use views versus creating a new table?

One reason that I would prefer to use views is that the database schema has been developed by our administrator from within Ruby, and I am not familiar with Ruby. I can request that tables be created, but requires an additional step and I would like more flexibility when developing / testing new joins.

I started using views following the answer to a related question on SO (When to use R, when to use SQL). The top-voted answer begins "do the data manipulations in SQL until the data is in a single table, and then do the rest in R."

I have started using views, but I have run into a few issues with views:

queries are much slower
Views do not get dumped from the production to backup database that I use for analysis.

Are views appropriate for this use? If so, should I expect a performance penalty? Is there a way to speed up queries on views?

Views in MySQL are handled using one of two different algorithms: MERGE or TEMPTABLE. MERGE is simply a query expansion with appropriate aliases. TEMPTABLE is just what it sounds like, the view puts the results into a temporary table before running the WHERE clause, and there are no indexes on it.
The 'third' option is UNDEFINED, which tells MySQL to select the appropriate algorithm. MySQL will attempt to use MERGE because it is more efficient. Main Caveat:

If the MERGE algorithm cannot be used, a temporary table must be used instead. MERGE cannot be used if the view contains any of the following constructs:

Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)

DISTINCT

GROUP BY

HAVING

LIMIT

UNION or UNION ALL

Subquery in the select list

Refers only to literal values (in this case, there is no underlying table)

[src]

I would venture to guess your VIEWS are requiring the TEMPTABLE algorithm, causing performance issues.
Here is a really old blog post on the performance of views in MySQL and it doesn't seem to have gotten better.
There might, however, be some light at the end of the tunnel on this issue of temporary tables not containing indexes (causing full table scans). In 5.6:

For cases when materialization is required for a subquery in the FROM clause, the optimizer may speed up access to the result by adding an index to the materialized table.
...
After adding the index, the optimizer can treat the materialized derived table the same as a usual table with an index, and it benefits similarly from the generated index. The overhead of index creation is negligible compared to the cost of query execution without the index.

As @ypercube points out, MariaDB 5.3 has added the same optimization. This article has an interesting overview of the process:

The optimization is applied then the derived table could not be merged into its parent SELECT which happens when the derived table doesn't meet criteria for mergeable VIEW


TOP X of SQL Server in MySQL Analog I am looking for SQL Server TOP (X) Analog in MySQL

In SQL Server:

SELECT
  TOP 5
  Z
...
FROM
ORDER BY Z DESC

How can this be done in MySQL?

ADDED:

I have found for other RDBMS: Returning TOP N Records

SELECT
...
FROM
ORDER BY
LIMIT 5 [OFFSET 10] --Use offset for paging.

Reference: http://dev.mysql.com/doc/refman/5.1/en/select.html


Transactional replication Issue I am getting this error when i try to create a new publicaton in dbserver-1 after reinstalling SQL Server in my database server 2.

Msg 18482, Level 14, State 1, Line 1
Could not connect to server 'dbserver-2' because 'dbserver-1' is not defined as a remote server. Verify that you have specified the correct server name. .

I had replication of some tables enabled from dbserver-1 to dbserver-2 and it was working good.

Both dbserver-1 and dbserver-2 are in the same domain.

Check out this Microsoft KB article:

You receive an "Error 18482" error message when you try to make a remote procedure call (RPC) from one server to another in SQL Server

Especially the very last part:

  You may receive this error message when installing replication because the installation process makes remote procedure calls between the servers involved in replication.

Since you mentioned that SQL Server was reinstalled, it's possible server names have gotten out of sync (depending on how the install was done).

I'd suggest following the troubleshooting steps in that KB.  In particular, make sure that srvid for the local server is "0" in sys.sysservers on both machines.


Dog Adoption Database Design Apologies if this isn't the norm, but I am seeking advice for the database design for 'dog rescue/adoption'.

I've done my normalization research and this is my first attempt at database design. Any tips/feedbacks/criticism?

The trickiest part was dealing with dogs/users/organisations and representing who owns a dog.

a dog can belong to an organisation.
An organisation can have many organisation users.
Think animal shelter/pound
a dog can belong to an organisation user. think a one man band with a large property housing multiple dogs at a time
a dog can belong to a single user, not attached to any organisation. think someone moving overseas and they need to find their dog a new owner

So, the dog table has to have either an organisation ID or a user ID attached to it, or both. Perhaps a trigger stating that one must be not null.

You can have organisation, user, and location all have a reference to a common table representing something that can be an owner to a dog. Then dog can have a single reference to this table. Here is a diagram explaining.

dog.owner_id references can_own_dog.can_own_dog_id, in case that isn't clear. Each of the can_own_dog foreign keys is unique in their table, ideally  unique across all three tables.

I'm not sure if you're familiar with object-oriented programming, but the idea is similar to multiple classes implementing a single interface.

On another note, your diagram says "Users can have multiple locations", and "Dogs can have multiple locations". I think these mean to say "Locations can have many users" and "Locations can have many dogs"?


Difference between Client Access Name in SQL server Fail over Cluster and Always on availability group We are migrating from SQL Server Fail over Instance to Always On Availability group. The current network name is ABC\ABCTEST where ABC is the SQL server Virtual Name  and ABCTEST is the named instance name. In always on availability group all the connection will make connection through the Listener name for example Listerner123 and it will not use any instance name and the replicas are standalone.

Now is there any way to have the name as VirtualName\InstanceName  or we will simply use the Listener name in the connection strings

There is a way: use alias on source machine. Anyway, if possible, I suggest you to correct connection strings.


Unsupported languages in Postgres Full text search My web application supports 3 languages, English, Traditional Chinese, Vietnamese. I understand that Traditional Chinese and Vietnamese is not supported in Postgres' Full Text Search.

If I were to go ahead and implement Full Text Search, what will happen if a text search is performed using an unsupported language?

There are Postgres extensions to handle Chinese. Have you tried http://pgxn.org/dist/zhparser/? You can then maybe port it to Vietnamese.


How can I select data and the total to cursors? I have a table like this:

 ID  | Name
------------
  1  | AA
  2  | AA
  3  | AA
  4  | BB

I want to select all rows where Name like 'AA' in a cursor and get the count from this result in another cursor. I also need to page the results.

Expected result (Skip = 0; Take = 2)

-- p_result

 ID  | Name
------------
  1  | AA
  2  | AA

-- p_count
   3

How can I do this?

An alternative to msi77's solution is the use of analytical functions. This might be a bit more efficient but you should run an explain plan to find out.

SELECT id,
       name,
       count(*) over () as total_count
FROM your_table
WHERE name = 'AA'


Forcing uncommitted read access via view? I have an application (which I'm only the user of, i.e. I cannot change it, but I do have control over the database) that performs relatively simple, but potentially very frequent concurrent look-ups on a table using prepared statements like the following on a DB2 LUW 10.5 system:

SELECT A, B, C FROM SCHEMA.TABLENAME WHERE D = ?

Particularly it does not seem to apply uncommitted read isolation. There are no updates on the table while the application reads from it and each read usually only returns a single row.

My questions are:

Can I assume there can be performance improvements by adding WITH UR to the statement?
If so: Can I expect similar performance improvements by creating a view like CREATE VIEW VIEWNAME AS SELECT * FROM SCHEMA.TABLENAME WITH UR and point the application at that view instead of the original table?

Or more generally: Can I use a view (or other DB-side facilities) to trick the application into using uncommitted read, provided that results in better performance in my scenario?

Readers should not block readers, so if no one writes to the table I don't think using UR instead of CS will make much of a a difference. There is a small overhead I guess for applying and releasing the share lock, but my guess is that it will be negligible. I guess only way to find out for sure is to try and see if it affects throughput.

I was unaware that you could add WITH UR in a view, but I tried and according to the plan the isolation level for the table actually changes from CS to UR. I find it a bit weird to be honest that the isolation level is lowered for the statement, without notifying the caller.

Since you are on 10.5 you might want to give CURRENTLY COMMITED a try. Keep an eye open on usage of the log buffer.


Domain Account or Windows account for Transactional Replication All the online resources say to use a Windows account for Agent accounts, i.e. Snapshot, Distributor and Log Reader to set up Replication.

See the following links as examples:

Tutorial: Prepare SQL Server for replication (publisher, distributor, subscriber)
Replication Security Best Practices

My question is: what is the problem with using domain accounts for the above, which is much easier to maintain and administer?

Short Answer: Nothing at all is wrong with a domain account. When all the computers participating are in the same domain, go that way. Easier to manage and maintain and secure.

Your confusion here lies in the way the documents you are looking at are structured. In the document you reference in the comments above you see this section:

  If your replication topology includes computers that are not in the
  same domain or are in domains that do not have trust relationships
  with each other, you can use Windows Authentication or SQL Server
  Authentication for the connections made by agents (For more
  information about domains, see the Windows documentation). It is
  recommended as a security best practice that you use Windows
  Authentication.

  To use Windows Authentication:

  Add a local Windows account (not a domain account) for each agent at
  the appropriate nodes (use the same name and password at each node).
  For example, the Distribution Agent for a push subscription runs at
  the Distributor and makes connections to the Distributor and
  Subscriber. The Windows account for the Distribution Agent should be
  added to the Distributor and Subscriber.

  Ensure that a given agent (for example the Distribution Agent for a
  subscription) runs under the same account at each computer.

The article isn't saying "if you want to use a Domain account, we suggest you use a local account" - but I totally see why anyone would read it that way.

The article is saying, instead, "Use a domain account where you can. IF your computers are not in the same domain, here's a little trick you can use."

That trick, sometimes referred to as pass through authentication has you create an account on each computer. Same name. Same password. By doing that - windows will pass the credentials through and, for all intents and purposes of this conversation, the apps (SQL and Replication objects) won't realize it isn't really a "domain account" - and it will just work.

If, however, the computers are on the same domain? Just use the domain account.

If you go back to that first link you commented with - you'll notice the "if your replication topology includes computers that are not in the same domain..." line isn't bulleted or indented. Really the bullet about the local to each should be indented off of that.


Oracle data pump import parallel configuration I am trying to track down a cause for a difference in oracle impdp processing, and I am not finding anything, so I am wondering if anyone here can explain the cause of the differences.

At times I have seen times where using the parallel=x parameter in oracle datapump will cause either multiple tables to be inserted at once, up to the value of 'x', or other times will use parallel threads to import a single table.

I have not been able to track down what might be causing the difference in performance, and I am wondering if anyone has an explanation, or even just a direction to point me to.  It would be helpful to determine why it runs in a given matter.

For instance right now I am monitoring a single table, data_only import that was started with parallel=8 in the command, but the import job is only using a single thread to do the import, as shown by the following hint the insert query /+ PARALLEL("XXXXX",1)+/.  If the process would use the maximum 8 threads specified to should run much faster.

Two common reasons:

Each worker thread uses a seperate dumpfile and requires exclusive access to it. If you have fewer dumpfiles than the degree of parallelism you provided, the parallel threads will not be fully utilized. If you have a single dump file, only 1 worker thread can be active.
Tables that contain Basicfile LOB columns are not be exported or imported in parallel. This is a known restriction and documented in: DataPump Export/Import Of LOBs Are Not Executed in Parallel (Doc ID 1467662.1)

Workarounds include:

seperate partitions can be imported in parallel
start multiple data pump jobs and distribute data between them using the QUERYparameter.


Changing the SQL Server Compatibility setting for individual tables/columns, which have retained older settings Is there a clear and methodological method to change the compatibility mode of a databases individual Tables & Columns?  A 3rd party database was upgraded and migrated to SQL 2005, the SQL Server Compatibility is at the correct setting, but there are tables/columns contained within the new db that have retained old compatibility settings.

Compatibility levels are set at the database, not at the table or column.

You might be thinking of collations, which are set at the column level.  You can't simply change the collation on those objects - you have to build new objects with the right collations and push the data into them.

You might also be thinking of deprecated datatypes like text, ntext, image, or vardecimal.  You don't have to change those yet, but you'll have to fix them before a future SQL Server version.


How to find SQL Server database is in use or not? What query do I need to write to know if the database is in use or not? I tried following query

SELECT
    name
FROM
    sys.databases
WHERE
    state != 0;

but it is not working.

I want to check there should not have a open connection with that database and should not executing a query against that DB

In order to use a database any session must first obtain a lock on it, usually a shared lock. So simply look for such locks:

select *
from sys.dm_tran_locks
where resource_type = 'DATABASE'
and resource_database_id = <dbid>;


How to recursively find gaps where 90 days passed, between rows This is a kind of trivial task in my C# homeworld, but I don't yet make it in SQL and would prefer to solve it set-based (without cursors). A resultset should come from a query like this.

SELECT SomeId, MyDate,
    dbo.udfLastHitRecursive(param1, param2, MyDate) as 'Qualifying'
FROM T

How should it work

  I send those three params into a UDF.
  The UDF internally use params to fetch related <= 90 days older rows, from a view.
  The UDF traverse 'MyDate' and return 1 if it should be included in a total calculation.
  If it should not, then it return 0.
  Named here as "qualifying".

What the udf will do

  List the rows in date order.
  Calculate the days between rows.
  First row in resultset defaults to Hit = 1.
  If the difference is up to 90,
   - then pass to next row until the sum of gaps is 90 days (90th day must pass)
  When reached, set Hit to 1 and reset gap to 0.
  It would also work to instead omit the row from result.

                                          |(column by udf, which not work yet)
Date              Calc_date     MaxDiff   | Qualifying
2014-01-01 11:00  2014-01-01    0         | 1
2014-01-03 10:00  2014-01-01    2         | 0
2014-01-04 09:30  2014-01-03    1         | 0
2014-04-01 10:00  2014-01-04    87        | 0
2014-05-01 11:00  2014-04-01    30        | 1

In the table above, MaxDiff column is the gap from date in previous line. The problem with my attempts so far is that I can't ignore second last row in the sample above.

[EDIT]
As per comment I add a tag and also paste the udf I have compiled just now. Though, is just a placeholder and won't give useful result.

;WITH cte (someid, otherkey, mydate, cost) AS
(
    SELECT someid, otherkey, mydate, cost
    FROM dbo.vGetVisits
    WHERE someid = @someid AND VisitCode = 3 AND otherkey = @otherkey
    AND CONVERT(Date,mydate) = @VisitDate

    UNION ALL

    SELECT top 1 e.someid, e.otherkey, e.mydate, e.cost
    FROM dbo.vGetVisits AS E
    WHERE CONVERT(date, e.mydate)
        BETWEEN DateAdd(dd,-90,CONVERT(Date,@VisitDate)) AND CONVERT(Date,@VisitDate)
        AND e.someid = @someid AND e.VisitCode = 3 AND e.otherkey = @otherkey
        AND CONVERT(Date,e.mydate) = @VisitDate
        order by e.mydate
)

I have another query which I define separately which is more close to what I need, but blocked with the fact I can't calculate on windowed columns. I also tried one similiar which give more or less same output just with a LAG() over MyDate, surrounded with a datediff.

SELECT
    t.Mydate, t.VisitCode, t.Cost, t.SomeId, t.otherkey, t.MaxDiff, t.DateDiff
FROM
(
    SELECT *,
        MaxDiff = LAST_VALUE(Diff.Diff)  OVER (
            ORDER BY Diff.Mydate ASC
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    FROM
    (
        SELECT *,
            Diff =  ISNULL(DATEDIFF(DAY, LAST_VALUE(r.Mydate) OVER (
                        ORDER BY r.Mydate ASC
                            ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
                                r.Mydate),0),
            DateDiff =  ISNULL(LAST_VALUE(r.Mydate) OVER (
                        ORDER BY r.Mydate ASC
                            ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING),
                                r.Mydate)
        FROM dbo.vGetVisits AS r
        WHERE r.VisitCode = 3 AND r.SomeId = @SomeID AND r.otherkey = @otherkey
    ) AS Diff
) AS t
WHERE t.VisitCode = 3 AND t.SomeId = @SomeId AND t.otherkey = @otherkey
    AND t.Diff <= 90
ORDER BY
    t.Mydate ASC;

As I read the question, the basic recursive algorithm required is:

Return the row with the earliest date in the set
Set that date as "current"
Find the row with the earliest date more than 90 days after the current date
Repeat from step 2 until no more rows are found

This is relatively easy to implement with a recursive common table expression.

For example, using the following sample data (based on the question):

DECLARE @T AS table (TheDate datetime PRIMARY KEY);

INSERT @T (TheDate)
VALUES
    ('2014-01-01 11:00'),
    ('2014-01-03 10:00'),
    ('2014-01-04 09:30'),
    ('2014-04-01 10:00'),
    ('2014-05-01 11:00'),
    ('2014-07-01 09:00'),
    ('2014-07-31 08:00');

The recursive code is:

WITH CTE AS
(
    -- Anchor:
    -- Start with the earliest date in the table
    SELECT TOP (1)
        T.TheDate
    FROM @T AS T
    ORDER BY
        T.TheDate

    UNION ALL

    -- Recursive part
    SELECT
        SQ1.TheDate
    FROM
    (
        -- Recursively find the earliest date that is
        -- more than 90 days after the "current" date
        -- and set the new date as "current".
        -- ROW_NUMBER + rn = 1 is a trick to get
        -- TOP in the recursive part of the CTE
        SELECT
            T.TheDate,
            rn = ROW_NUMBER() OVER (
                ORDER BY T.TheDate)
        FROM CTE
        JOIN @T AS T
            ON T.TheDate > DATEADD(DAY, 90, CTE.TheDate)
    ) AS SQ1
    WHERE
        SQ1.rn = 1
)
SELECT
    CTE.TheDate
FROM CTE
OPTION (MAXRECURSION 0);

The results are:

╔═════════════════════════╗
║         TheDate         ║
╠═════════════════════════╣
║ 2014-01-01 11:00:00.000 ║
║ 2014-05-01 11:00:00.000 ║
║ 2014-07-31 08:00:00.000 ║
╚═════════════════════════╝

With an index having TheDate as a leading key, the execution plan is very efficient:

You could choose to wrap this in a function and execute it directly against the view mentioned in the question, but my instincts are against it. Usually, performance is better when you select rows from a view into a temporary table, provide the appropriate index on the temporary table, then apply the logic above. The details depend on the details of the view, but this is my general experience.

For completeness (and prompted by ypercube's answer) I should mention that my other go-to solution for this type of problem (until T-SQL gets proper ordered set functions) is a SQLCLR cursor (see my answer here for an example of the technique). This performs much better than a T-SQL cursor, and is convenient for those with skills in .NET languages and the ability to run SQLCLR in their production environment. It may not offer much in this scenario over the recursive solution because the majority of the cost is the sort, but it is worth mentioning.


MySQL Upgrade on Windows I am having MySQL 5.1 installed on windows box. Now I would like to upgrade to latest MySQL 5.6 version.

I am planning to install MySQL 5.6 in the same server with same port 3306. After installation, I will map the new installation data directory to the older data directory which is used by MySQL 5.1.

After this, I will upgrade using mysql_upgrade.exe.

Will this plan work out or not ? Also let me know if any precautions to be taken care.

Edit: This is production server and the DB is of huge size 100 GB, so we are not able to go with backup and restore mechanism which is time consuming process.

Actually, this plan is kind of dangerous for the mysql schema. Why ?

You are upgrading two versions up. mysql_upgrade will not respond well to this. I have a post where someone upgraded without addressing mysql.user : Any known issues upgrading from MySQL 5.1.73 to 5.6.21?

I have old posts on how to upgrade two versions in one shot

Apr 11, 2013 : MySQL upgrade 5.0.88 to latest (Best Reference)
Feb 08, 2012 : will replication from 5.5.20 to 5.0.XX server work?
Jul 26, 2011 : Restoring an old backup to latest MySQL release

Here is an additional post on how to move the grants with pure SQL : Upgrading mysql myisam 5.1 to mysql 5.6: force innodb on restore?. That post is based on Linux.

To do it in Windows, here are the steps

STEP 01 : Run This

set MYSQL_USER=root
set MYSQL_PASS=rootpassword
set MYSQL_CONN=-u%MYSQL% -p%MYSQL_PASS%
set SQLSTMT="SELECT CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),';')
set SQLSTMT=%SQLSTMT% FROM mysql.user WHERE user<>''"
mysql %MYSQL_CONN% -ANe%SQLSTMT% > GetGrants.sql
echo "SET sql_log_bin = 0;" > MySQLUserGrants.sql
mysql %MYSQL_CONN% -AN < GetGrants.sql >> MySQLUserGrants.sql

STEP 02 : Append a semicolon to every line in MySQLUserGrants.sql

STEP 03 : Load MySQLUserGrants.sql into MySQL 5.6 setup


Is there a MySQL character set and encoding that will allow for both emojis and accents? I've got a database of terms that get added to by one group of users, and queried against by another.

I was running into problems when people would query for an emoji in the database and my React app would throw an error (oddly a CORS error, but that's a different issue). I eventually solved this by changing my table's character set to utf8mb4 with utf8mb4_unicode_ci collation.

Now I'm getting errors when adding new terms saying, for example, that a duplicate row for "beyoncé" already exists. However, when I query the db for "beyoncé", it returns the row with "beyonce" in it. Is there a combination of charset and collation that can handle this properly?

My DB is MySQL 5.6.40 running on Amazon RDS.

I was running into problems when people would query for an emoji in the database and my React app would throw an error

What was the exact error message? What were the character set and collation of the column before you changed it to utfmb4 and utf8mb4_unicode_ci? In MySQL, collation can be set at many levels, including the client connection.

That said, Unicode (utf8 being one of the Unicode encodings), supports all characters. If your character set truly is utf8mb4, then there is no need to change that.

You said:

  I'm getting errors when adding new terms saying for example that a duplicate row for "beyoncé" already exists, however when I query the db for "beyoncé", it returns the row with "beyonce" in it.

The MySQL documentation states:

  For nonbinary collation names that do not specify accent sensitivity, it is determined by case sensitivity. If a collation name does not contain _ai or _as, _ci in the name implies _ai and _cs in the name implies _as.

So, since your collation is utf8mb4_unicode_ci, then it is both case-insensitive and accent-insensitive. And this is why "beyoncé" matches "beyonce".

If you need "beyoncé" and "beyonce" to be considered different, then ideally you would use a case-sensitive (and either explicitly-stated or implied accent-sensitive) collation. However, it looks like this is not available in MySQL 5.6 (or even 5.7), while MySQL 8.0 does have utf8mb4_0900_as_cs, or even utf8mb4_0900_as_ci if you only want the accent to distinguish between the values while allowing "beyonce" and "Beyonce" to match.

So for now, it looks like you might need to use a binary collation, utf8mb4_bin, either by changing the collation of the column, or by adding COLLATE utf8mb4_bin to one or more queries.


Apply DB2 logs from production to DR Is it possible to Apply DB2 9.5 logs files from Production site to DR site? Or do I have to do Backup/Restore and rollforward ...?

I copied log files from production site to DR and issued the following command:

rollforward db DBNAME to end of logs and stop overflow log path (/home/db2inst1/logs) noretrieve

Got the error:

  DB is not in Rollforward Pending

As the error says, the database must be in the Rollforward pending state for you to use the rollforward command. However, while in this state the database is not accessible.

You don't explain what it is that you are trying to achieve so it's hard to provide a precise solution. If this is a one-time activity you will need to restore your DR database from a backup and run rollforward. If your intent is to keep the DR database up to date on an ongoing basis, consider setting up HADR, which automates the process of transferring and applying logged changes to the standby database.


"Access denied for user" error when creating a procedure When I request my database server (MySQL), with "show grants for current_user"

I notice that I have the grant to execute procedure:

SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK TABLES, EXECUTE

But when I execute a sql file that contains those instructions :

DROP FUNCTION IF EXISTS
DROP FUNCTION IF EXISTS
DROP PROCEDURE IF EXISTS
CREATE FUNCTION
CREATE PROCEDURE
DECLARE
CALL

I have this error :

ERROR 1044 (42000) at line 16: Access denied for user

The line 16 contains : CREATE FUNCTION function_name( .. )

Should I have other grants ?

From  MySQL Documention:

  The CREATE ROUTINE privilege is needed to create stored routines (procedures and functions).

Example:

GRANT CREATE ROUTINE, ALTER ROUTINE ON yourDB.* TO youUser;

Max


Elapsed time in SSMS is consistently higher than SQL Server Execution Time - connection issue? I have a fresh install of SQL Server 2012 Developer Edition on a pretty snappy laptop. I'm using it as a backend for an ASP.NET webapp. I noticed query timings that were consistently higher than I'd expect coming from a MySQL background.

For example, MiniProfiler shows select * from Comments is taking 30-50ms, when Comments has only one row in it. Simple things like that.

In investigating, I opened SQL Server Management Studio and issued a simple query:

set statistics io on
set statistics time on
go
select 1 as one;
go
set statistics io off
set statistics time off
go

This outputs what I'd expect:

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

But in the right sidebar of SSMS, 'Aggregate Status > Elapsed Time' shows 77ms. Running it multiple times shows timings varying between 50ms and 80ms.

I removed the timing commands and just ran select 1 as one; (again from SSMS). Profiler again shows 0ms execution times, but SSMS shows 55ms. My first question: Is it unreasonable to expect (exponentially) better performance? If SQL Server is just slow, I don't want to waste time on this. Have a hard time believing that, though.

At this point, I believe this is a connection issue, but I'm not sure where to start. I did disable McAfee's on-access protection, to no avail. I've also tried using SQL Server auth instead of Windows auth, also with no effect.

What are your thoughts? These 50ms hits for every query really add up on pageloads, and I believe we're also seeing this in production (I'm light on the details there).

If you're after a system that takes 0 ms to return and render results, good luck.

This doesn't mean that SQL Server is 'just slow', though I'm not sure what your expectations for grid rendering are.

The only way to see what your server is waiting on is to look at its wait stats. If you'd like a free script that does that, head over here and check out sp_BlitzFirst (full disclosure, I contribute to this open source project).

You can run it this way to look at your server's wait stats since it started up:

EXEC sp_BlitzFirst @SinceStartup = 1

Or this way to get a sampling during a window length of your choosing:

EXEC sp_BlitzFirst @Seconds = 30, @ExpertMode = 1

And if you find a system that returns and renders results in 0 ms, drop me a line! I'd love to hear about it.


Can I estimate the row count of a large mysql table using the disk space? Sysadmin here (not a developer by trade), so go easy please ;)

I have a mysql table, on a linux server that uses innodb_file_per_table.

When I try to do select count(foo) from table; It doesn't finish.  I gave it a good 5 minutes before interrupting.

The table.ibd file is about 43G (45176848384).  Since the count() seemed like it was never going to finish, I decided to try the following to get an estimate of the row count:

I dumped the first 10,000 rows of the table and imported it to a local database that also has innodb_file_per_table and I hope all else equal.

That file, after I imported it, (with only 10,000 rows) is about 2M (2097152)

So:

45176848384/2097152
21542
21542*10000
215420000

Then, is it correct to assume that the ~43G table is around 215 million rows?  Are there other considerations I'm overlooking?

Or is there a better way for me to determine how many rows are in this table?

You cannot make that kind of approximation work if the InnoDB table experiences DELETE and UPDATEs. With innodb_file_per_table enabled and a table called mydb.mytable, this is what you get

/var/lib/mysql/mydb/mytables.frm : Table Format Info
/var/lib/mysql/mydb/mytables.ibd : This file contains data pages and index pages making up the table and its indexes (Each page is 16KB).

There will be fragmentation amongst the data pages due to DELETEs and UPDATEs
There will be fragmentation amongst the index pages due to BTREE operations (node balancing, node splits, etc).

Even if you defragment the InnoDB with OPTIMIZE TABLE, there is no way of knowing if a single row occupies two or more pages.

ALTERNATIVE APPROACH

You could probably force the index statistics to give you a round figure. You could just run

SHOW INDEXES FROM mydb.mytable;

Look at the Cardinality column of the PRIMARY KEY. That will give you a very close guess at the rowcount. However, if you run SHOW INDEXES FROM mydb.mytable; multiple times on a large table, you get a different number each time. That's because InnoDB will scan the BTREE nodes and give a roundabout number. If you want to go by the Cardinality of the PRIMARY KEY as the rowcount, you should run this

SET GLOBAL innodb_stats_on_metadata = 0;

before running SHOW INDEXES FROM mydb.mytable;

Believe me, this is a much better guess that dumping a subset of the data and guessing from the filesize.


SSIS error Property BackupDirectory is not available for Settings I am trying to edit the SSIS Back Up Database Task And it gives the folowing error

  Property BackupDirectory is not available for Settings 'Microsoft.SqlServer.Management.Smo.Settings'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

This is a known issue with SSIS designer (although I cannot find the Connect article at this time).  Most likely the backup value key in the registry is not being updated correctly with the directory for this package.

To fix this (if you are on the default instance), launch Regedit and navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer.  Create a string value called "BackupDirectory" (no quotes) and in the value put the complete path of your backup directory.  Then restart and your issue should be resolved.

For a named instance, the Regedit path is:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\"name of your SQL instance"\MSSQLServer


How can I monitor a production database to see if there are table scans happening? How can I monitor a production database to see if there are table scans happening? And what indexes might fix them...

I'm told that we have adequate indexes but I can't see every code path, and we have too many people touching the codebase (hey, lots of developers can sometimes be a good thing) so what's the most comprehensive way to see how if we've missed indexes based on our production load?

SQL Server 2008 R2, C#/ASP.NET code, NHibernate are the key factors in use.

If you are looking for missing indexes inside your plan cache, you can try this:

;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT dec.usecounts, dec.refcounts, dec.objtype
      ,dec.cacheobjtype, des.dbid, des.text
      ,deq.query_plan
FROM sys.dm_exec_cached_plans AS dec
     CROSS APPLY sys.dm_exec_sql_text(dec.plan_handle) AS des
     CROSS APPLY sys.dm_exec_query_plan(dec.plan_handle) AS deq
WHERE
deq.query_plan.exist(N'/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup') <> 0
ORDER BY dec.usecounts DESC

You can find more info in the plan cache as well, I've listed a few examples here: http://sqlmag.com/database-performance-tuning/sql-server-plan-cache-junk-drawer-your-queries