Does dm_exec_procedure_stats.total_worker_time include signal waits? Does sys.dm_exec_procedure_stats.total_worker_time include signal waits or only time spent by procedure on scheduler?

The reason of question - when number of cores was increased on the server signal waits decreased as expected, but total_worker_time/execution_count has dropped too.

Neal Hambly in his post at SQL Server SOS_SCHEDULER_YIELD Wait Type includes this comment (highlights are mine):

If you have long running queries, especially when in conjunction with
high signal wait times, it may be an indication of CPU pressure.
Reviewing the system performance will show if the CPUs are showing
signs of CPU pressure, and reducing workload through performance
tuning or other steps can help reduce the workloads being performed by
SQL Server.

Certainly adding more cores will change the amount of pressure each CPU must face.  So, it seems quite reasonable that more processors will reduce the pressure on each CPU, which in turn provides better CPU throughput.


MySQL commits taking time We are facing an issue, where at certain times there are number of commits that takes a more than a second. The time interval for this is not fixed. Our application sends thousands of commits but at some instance the commits with exact same timestamp gets slow.

This is extracted from MySQL slow query logs

There isn't anyother query in the slow query log at the same time. Any pointers on how it can be further investigated?

We are using MySQL 5.7.16 on Amazon RDS.

Since your commits are stalling, it's pretty safe to assume you're running with innodb_flush_log_at_trx_commit=1 (see #4 here), so one obvious change you could do is switch to innodb_flush_log_at_trx_commit=0, however that may not be the right choice for you - see the previous link.

In any case, COMMITs don't usualy stall due to other activity on the server (like queries), unless that other activity locks some kind of mutex for far too long.

If I had to guess without any further evidence, I would say that your writes are so intensive, that MySQL is unable to flush fast enough using adaptive checkpointing, so it exceeds 75% of the log capacity and starts sharp checkpointing (which blocks server activity for a short period).

There are few quick ways to make MySQL handle writes better (besides one already mentioned above):

Increase innodb log files (innodb_log_file_size) - you can go up to 4GB or so for your logs (esimating based on zero information given on the amount of writes),
Increase pIOPS capacity for the data drive
Increase innodb_io_capacity (up to ~70-80% of pIOPS capacity)

Otherwise it would be really useful to see disk activity from AWS, amount of data server currently writes (you can calculate it using Log sequence number delta between two consecutive show engine innodb status\G runs)

There are other ways too, but for that, I'd need a lot more details on the actual workload.


How to select all Mondays (or any day combo like MTW, WTTh) between two given dates in MySQL? What I see over the internet is just counting how many days occur whenever you input the day of the week you need. Like between May 1 to May 31, how many mondays are there in it. What I need is to display the dates where it is monday between two given dates. I don't have any code for this now since I don't really know where to start as I've scanned already the MySQL docu but I haven't seen anything useful besides

WEEKDAY('1998-02-03 22:23:00')

or

DAYOFWEEK('1998-02-03')

which shows the day index of a certain date. (but this time, of course, I don't know which dates fall on Mondays., i need the reverse thing)

I need is when I need mondays and tuesdays in february, it will return the following:

2015-02-02
2015-02-03
2015-02-09
2015-02-10
2015-02-16
2015-02-17
2015-02-23
2015-02-24

I dont have any code to display since my progresses in mysql on our proj isn't directly related to this one I'm needing. This is another independent mysql query of our proj I'm trying to get done..

From here, I modified the query slightly to get

select adddate('2015-02-01', numlist.id) as `my_date`,
       weekday(adddate('2015-02-01', numlist.id)) as day_no,
       dayname(adddate('2015-02-01', numlist.id)) as `day_name`
from
(SELECT n1.i + n10.i*10 + n100.i*100 AS id
   FROM num n1 cross join num as n10 cross join num as n100) as numlist
where adddate('2015-02-01', numlist.id) <= '2015-02-28'
and dayname(adddate('2015-02-01', numlist.id)) in( 'Monday', 'Tuesday');

which gives

+------------+--------+----------+
| my_date    | day_no | day_name |
+------------+--------+----------+
| 2015-02-02 |      0 | Monday   |
| 2015-02-03 |      1 | Tuesday  |
| 2015-02-09 |      0 | Monday   |
| 2015-02-10 |      1 | Tuesday  |
| 2015-02-16 |      0 | Monday   |
| 2015-02-17 |      1 | Tuesday  |
| 2015-02-23 |      0 | Monday   |
| 2015-02-24 |      1 | Tuesday  |
+------------+--------+----------+
8 rows in set (0.01 sec)

or from here

select * from
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2015-02-01' and '2015-02-28'
and dayname(selected_date) in ('Monday', 'Tuesday');

result

+---------------+
| selected_date |
+---------------+
| 2015-02-02    |
| 2015-02-03    |
| 2015-02-09    |
| 2015-02-10    |
| 2015-02-16    |
| 2015-02-17    |
| 2015-02-23    |
| 2015-02-24    |
+---------------+
8 rows in set (0.23 sec)

CREATE TABLE mydate( blah date);

INSERT INTO mydate (blah)
select adddate('2015-02-01', numlist.id) as `my_date`
--       weekday(adddate('2015-02-01', numlist.id)) as day_no,
--       dayname(adddate('2015-02-01', numlist.id)) as `day_name`
from
(SELECT n1.i + n10.i*10 + n100.i*100 AS id
   FROM num n1 cross join num as n10 cross join num as n100) as numlist
where adddate('2015-02-01', numlist.id) <= '2015-05-28'
and dayname(adddate('2015-02-01', numlist.id)) in( 'Monday', 'Tuesday');

(need a table num)

mysql> select * from mydate;
+------------+
| blah       |
+------------+
| 2015-02-02 |
| 2015-02-03 |
| 2015-02-09 |
| 2015-02-10 |
| 2015-02-16 |
| 2015-02-17 |
| 2015-02-23 |
| 2015-02-24 |
| 2015-03-02 |

OR

insert into mydate (blah)
select * from
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2015-02-01' and '2015-05-28'
and dayname(selected_date) in ('Monday', 'Tuesday');

No table necessary. Same result as above.


Cannot import database exported by phpPgAdmin I have exported database with phpPgAdmin tool - format sql, structure and data.

When I try to import this sql using:

psql command \i filename.sql

After some time (file is 2GB) I get following error:

ERROR:  unterminated quoted string at or near ".. , '\x25504... "

with "arrow" pointing to \x.

Looks like \ is making a problem here...

Looking at this tutorial, seems like phpPgAdmin make a call for pg_dump to export the data.

To process plain files with psql, you must use the -foption, eg:

psql \
  -U postgres  \
  -d database_name \
  -f phppgadmin_dump_file.sql

I strongly recommend using pg_dump manually with the option --format=custom to make the restore more flexible (and use the tool pg_restore).

Please, take a look at this URL to restore de file using phpPgAdmin itself:

http://www.inmotionhosting.com/support/website/databases/postgresql/import-database


OCIEnvCreate failed to create environment handle after installing oracle_fdw I have installed oracle_fdw successfully. After I create extension oracle_fdw and
foreign table it shows the following error. Is this a server configuration problem?

--env
Oracle :    10.2.0.1
PostgreSQL:  9.1.3

--create foreign server
skytf=#   CREATE SERVER oracle_srv
skytf-#   FOREIGN DATA WRAPPER oracle_fdw
skytf-#   OPTIONS (dbserver '//192.168.1.30:1521/MANUA');
CREATE SERVER

skytf=# grant usage on foreign server oracle_srv to skytf;
GRANT

--create mapping user
skytf=# CREATE USER MAPPING FOR skytf
skytf-# SERVER oracle_srv
skytf-# OPTIONS (user 'read_only', password 'read_only');
CREATE USER MAPPING

--create foreign table
skytf=# CREATE FOREIGN TABLE ft_test_1 (
skytf(#  id       integer,
skytf(#  name     character varying(20)
skytf(#  ) SERVER oracle_srv
skytf-# OPTIONS (schema 'ocp', table 'test_1');
CREATE FOREIGN TABLE

skytf=# \c skytf skytf
skytf=> select * from ft_test_1;
ERROR:  error connecting to Oracle: OCIEnvCreate failed to create environment handle
DETAIL:

You have to set the environment variables LD_LIBRARY_PATH and ORACLE_HOME for the user postgresql. Then you have to set the service to conncet in the file tnsname.ora, the you have to create a foreign server with dbserver=Service (you should have defined the Service in the file `tnsname.ora

I send you my files configurations in Debian:

I added to /etc/profile (apply for all system user) but you can use .bashrc or another for only one user.

Oracle

ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/client
export ORACLE_HOME
LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH
PATH=$PATH:$ORACLE_HOME/bin
export TNS_ADMIN=$ORACLE_HOME/network/admin

Postgresql Path

PATH=$PATH:/usr/local/pgsql/bin
export PATH
MANPATH=$MANPATH:/usr/local/pgsql/man
export MANPATH

The variable TNS_ADMIN will be a directory where you install a tnsnames.ora

tnsnames.ora:

ORA11 =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = ORA11)
 )
)

Then create a foreign server for oracle_fdw with option dbserver=ORA11


Postgres's equivalent for Oracle's datafile recovery In oracle, one can do a recovery from datafiles.
Does Postgres have an equivalent procedure?
I am already aware of the WAL files in Postgres, but I believe this is different. Datafiles in Oracle are the actual files on disk.

Data files are the operating system files that store the data within the database. The data is written to these files in an Oracle proprietary format that cannot be read by other programs

Does something like this exist for Postgres?

PostgreSQL does not have datafiles in the same way as Oracle. PostgreSQL stores its contents in a directory (show data_directory;) and not in a tablespace that consists of 1 or more datafiles. You have no control over the naming and/or size of the files in there.


why I can't find locked table? I locked 3 innodb tables with write lock

lock tables hive.NEXT_COMPACTION_QUEUE_ID write;
lock tables hive.NEXT_LOCK_ID write;
lock tables hive.NEXT_TXN_ID write;

I find out which table is locked through SQL

SHOW OPEN TABLES WHERE `Database` LIKE 'hive' AND In_use > 0;

but only one table (last table I locked) ,why ？

+--------------------+----------------------------------------------+--------+-------------+
| Database           | Table                                        | In_use | Name_locked |
+--------------------+----------------------------------------------+--------+-------------+
| hive               | NEXT_TXN_ID                                  |      1 |           0 |

but when I want to show the first table which I thought isn't locked,it locks!why? is show open tables not correct?

 show create table hive.NEXT_COMPACTION_QUEUE_ID;
ERROR 1100 (HY000): Table 'NEXT_COMPACTION_QUEUE_ID' was not locked with LOCK TABLES

Your queries are equivalent to the following:

UNLOCK TABLES;
lock tables hive.NEXT_COMPACTION_QUEUE_ID write;
UNLOCK TABLES;
lock tables hive.NEXT_LOCK_ID write;
UNLOCK TABLES;
lock tables hive.NEXT_TXN_ID write;

  LOCK TABLES implicitly releases any table locks held by the current session before acquiring new locks.

  — http://dev.mysql.com/doc/refman/5.7/en/lock-tables.html

The correct way to lock multiple tables is in a single statement:

lock tables hive.NEXT_COMPACTION_QUEUE_ID write,
            hive.NEXT_LOCK_ID write,
            hive.NEXT_TXN_ID write;


Identity column re-seed: when it is necessary? During one of the last lessons at university (I'm a student), lecturer asked us to develop a database (MySQL Server if it matters) and tiny client app that would consume the database as data source.

One of requirements was that the identity column (which is the PK in every table) must be sequential, because it is a good practice (as per lecturer words). That is, when table row is deleted, it's PK must be reused in subsequent inserts. I have average knowledge in RDBMS, PKs and identity columns. From what I understand, that identity column is just a way to let DB to auto-generate PKs when inserting rows and nothing more. And identity column value shall not be related to row attributes in any way (as long as it is not natural key).

This requirement (strictly sequential identity column) was suspicious to me. I tried to ask the lecturer what is wrong if identity is not sequential (with gaps caused by deletions), but got very abstract answer like "it is convenient for users and useful for DB administrators who maintain the database". No specific examples. The argument "convenient for users" sounds silly, because it doesn't have any meaning in business domain.

Therefore I'm curious if these reasons are real? I can think only of one case when identity column reseed is required -- when identity space is exhausted. But this is more design issue when identity column type was chosen incorrectly, say simple int instead of bigint or uniqueidentifier when table contains billion rows. Suppose, an identity column is a clustered index: can gaps in identity column affect index performance? Maybe there are other real-world reasons for automatic identity column re-seed after each delete I'm not aware of?

Thanks in advance!

That is, when table row is deleted, it's PK must be reused in subsequent inserts.

What universe is your lecturer from??

That is grossly inefficient.  If you try to do that, you will cut your performance prospects down by a factor of 10.

If you need gapless numbers for auditing reasons, build them explicitly, not directly from database tools.  And never delete rows, but flag them as "deleted".  This will add to the messiness of queries, since they will have to ignore such rows.

In MySQL, InnoDB requires the existence of a unique PRIMARY KEY for each table.  But that is the extent of the requirement.  The key can even be a string.

Gaps are a convenience to the users and DBAs, not an inconvenience.

I can think of one case where gapless would be convenient -- chunking into groups of 100 rows at a time.  But there is a simple workaround using LIMIT 100,1.

Gaps have zero impact on performance.  That includes non-numeric indexes.  And non-unique indexes.  And composite indexes.

Sure, you can run out of ids.  I think I have seen it happen twice in nearly 2 decades of using MySQL.  I may as well worry about being struck by an asteroid.  It's low on my things-that-keep-me-awake-at-night list.

Gaps occur from (at least):  INSERT IGNORE, IODKU, REPLACE, DELETE, ROLLBACK (explicit, or due to crash), Multi-master replication (including Galera and Group Replication).  Do you really want to come up with workarounds for those?!

Feel free to have us sanity-check anything else that lecturer says that is suspicious.


PgAdmin III - How to connect to database when password is empty? I have installed PostgreSQL 9.1 on my PC (Win 7). I have a small Java application connecting successfully to it with login=sa and password="". The connection works.

However, it is refused from PgAdmin III itself. I get:

Error connecting to the server: fe_sendauth: no password supplied

How do I connect to my database from PgAdmin III with an empty password?

EDIT

This is just a test, not production code.

I found this answer elsewhere.

If your DB is on the local host, try leaving the host field blank in the connection as opposed to using “localhost” or “127.0.0.1″. This tells PgAdmin to connect via the local unix socket instead of TCP.


Matching empty string is not working I have the following query

SELECT Ice.IceId, Ice.Code, Box.Ice, LEN(Box.Ice) AS IceCharLength,
       DATALENGTH(Box.Ice) AS DataLength, Box.Ice, ASCII(Box.Ice) AS ASCII,
       CAST(Box.ice AS VARBINARY) AS VarBinary
FROM dbo.tblESPShipBox Box
LEFT JOIN ReferenceManual.IceType Ice
       ON Ice.Code = CASE
                        WHEN Box.ICE IS NULL THEN 'N'
                        WHEN RTRIM(Box.ICE) = '' THEN 'N'
                        WHEN Box.Ice IN ('', ' ', '/') THEN 'N'
                        ELSE Box.ICE
                      END
WHERE BOX_SEQ_NUM = '000023' AND BOX_TYPE_ID = 0

It returns the following results:

IceId       Code Ice  IceCharLength DataLength  Ice  ASCII       VarBinary
----------- ---- ---- ------------- ----------- ---- ----------- -------------
2           N         0             1                32          0x20
NULL        NULL      1             1                0           0x00

The weird thing is that Box.ICE for the first row is ' ' (Single Space String).  The second row seems to be '' (No space string).  (The way I see this is by copying the cell out and pasting in into other text to see if it makes a space or not.)

The Box.ICE column is a char(1) so I don't really see how a '' (no space string) got in there.

But the weird part is that my case statement should still match on a '' (no space) char.  But clearly it does not.

How can I figure out what is really in this field and make it match my case statement?

Things I have tried:

• Used SSMSBoost to do a special char included copy of the cell into NotePad++ (with show all chars on).  I did this to make sure there are no unplayable unicode chars.

Run this query:

 SELECT ASCII(' ') AS [SingleSpaceString]
      , ASCII('') AS [NoSpaceString]

You will note the the Value for [SingleSpaceString] is '32', while [NoSpaceString] is NULL.

I'd suggest adding ASCII(Box.Ice) to your select statement to confirm that a true space is the value, vs. an odd character.


Is it generally faster to select into a temp table than selecting into an actual table? I thought I once read somewhere that writing to tempdb is faster than an actual table not in tempdb. Is this true in any capacity?
I thought I recall it saying something special about tempdb and storing the data in memory?

writing to tempdb is faster than an actual table not in tempdb

It's true.  There are two IO enhancements in TempDb.

Writes to a table in a user database must have their log records flushed to disk on commit, or if a minimally-logged insert (like SELECT ... INTO), must have the database pages flushed to disk on commit.  The way the minimal logging works in a user database is that the database pages are written directly to disk.  By the time the SELECT ... INTO completes, the new pages have all been written to the physical files.

TempDb writes don't have to be flushed to disk on commit since TempDb is never recovered after a failure.  So they simply aren't.  Your changes generate log records, but the log buffer is flushed to disk only when it's full, not for every commit.

And since SQL Server 2014 the minimally-logged inserts in TempDb aren't always written to disk either.  If you load a small, short-lived temp table it may never be written to disk at all.  The log will have a few records about the page allocations and metadata entries for the table, but that's it.

EG run the following batch in tempdb, a full recovery database and a simple recovery database to see the differences.

drop table if exists foo
go
declare @data bigint
declare @log bigint
select @log = sum(case when type_desc = 'LOG' then num_of_bytes_written end)
      ,@data =  sum(case when type_desc = 'ROWS' then num_of_bytes_written end)
from sys.database_files f
cross apply sys.dm_io_virtual_file_stats(db_id(),f.file_id) fs

select *
into foo
from sys.objects

select -@log + sum(case when type_desc = 'LOG' then num_of_bytes_written end)  log_bytes
      ,-@data +  sum(case when type_desc = 'ROWS' then num_of_bytes_written end) data_bytes
      , (select recovery_model_desc from sys.databases where database_id = db_id()) recovery_model
from sys.database_files f
cross apply sys.dm_io_virtual_file_stats(db_id(),f.file_id) fs

and you'll see something like:

For simple recovery:

log_bytes            data_bytes           recovery_model
-------------------- -------------------- ---------------
24576                16384                SIMPLE

for full recovery:

log_bytes            data_bytes           recovery_model
-------------------- -------------------- ---------------
36864                0                    FULL

and for tempdb:

log_bytes            data_bytes           recovery_model
-------------------- -------------------- ---------------
0                    0                    SIMPLE

Sometimes for tempdb you'll see the log buffer flushed:

log_bytes            data_bytes           recovery_model
-------------------- -------------------- ---------------
61440                0                    SIMPLE


Impact of CLUSTER on performance I'm trying to optimize my Postgres 9.2 database to speed up queries with date restrictions.

I have a timestamp column, but mostly I'm asking for some day, so I have created an index with timestamp to date parsing:

CREATE INDEX foo_my_timestamp_idx
ON foo
USING btree
((my_timestamp::date) DESC);

Now, to increase a performance I CLUSTER foo table using index above:

CLUSTER foo USING foo_my_timestamp_idx;

According to the manual on SQL-CLUSTER, the table

  is physically reordered based on the index information

I wonder whether there is an impact on performance for other queries using a PK of table (let say id_foo). Are there any downsides?

Yes, there can be downsides. If another query looks at a different data segment not determined by the date, it might take a performance hit if rows are spread out over more data pages now. Just the same way as your first query profits. That completely depends on information not in your question.

  other queries using a PK of table (let say id_foo)

That could be anything. It depends on what you have and what you query exactly. Querying a single row is not affected either way, but multiple rows might be.

Be aware that CLUSTER rewrites the table in pristine condition like VACUUM FULL does (removes dead tuples, compacts the physical size of the table, rewrites indexes) So you might see an immediate positive effect on read performance independent of the sort order. (Much like you would get with VACUUM FULL.)
After CLUSTER, you may want to run a plain VACUUM on the table to update the visibility map, too - which may allow index-only scans.

All benefits of CLUSTER shrink with the write frequency.

Also, if you have many updates to the table, CLUSTER can actually hurt write performance by removing "wiggle room" for HOT updates on the same data page. You might be able to counter that effect with a FILLFACTOR setting below 100. Again, depends on locality of updated rows, etc.

Related:

Index optimization with dates

Either way, I would probably not index and cluster on my_timestamp::date, but on my_timestamp directly. Nothing lost, something gained. The cast is very cheap, but it's still cheaper not to cast at all. And the index can support more queries.

CREATE INDEX foo_my_timestamp_idx ON foo (my_timestamp);

Even though a date occupies only 4 bytes on disk and a timestamp occupies 8 bytes, the difference is typically lost to alignment padding for your case, and both indexes have exactly the same size.

The order of multiple rows on the same day resulting from your expression index is arbitrary. There can still be two identical timestamps, but with 6 fractional digits that's normally very unlikely. Aside from that you get a deterministic order of rows, which can have various advantages.

I also dropped the DESC key word since Postgres can read indexes backwards virtually as fast a forwards. (Sort order matters for multicolumn indexes, though!) More:

Optimizing queries on a range of timestamps (two columns)

Instead of:

SELECT * FROM foo
WHERE my_timestamp::date = '2016-07-25';

You would now use:

SELECT * FROM foo
WHERE  my_timestamp >= '2016-07-25'  -- this is a timestamp literal now
WHERE  my_timestamp <  '2016-07-26';

Same performance.

If you don't need the time component of the column at all, convert the column to date ...

How to roll back CLUSTER?

CLUSTER on a single table can be rolled back with ROLLBACK like any other regular command as long as the transaction has not been committed.

However, I quote the manual:

  CLUSTER without any parameter reclusters all the previously-clustered
  tables in the current database that the calling user owns, or all such
  tables if called by a superuser. This form of CLUSTER cannot be
  executed inside a transaction block.

You can always run CLUSTER with a different index to change the physical order of rows once more.


How to read and increment a number in text file using TSQL I have a text file C:\abc.txt with a number 5 in the first line. I need to increment it into 6.

Please help me with the T_SQL syntax.

I have tried the following query to read the value but not able to update it.

DROP TABLE #UserLocation

CREATE TABLE #UserLocation(UserId numeric(3,0))

BULK INSERT #UserLocation
    FROM 'C:\ABC\ABC.txt'
    -- Path to the file.If this file is on server,be sure this file exists on the server.
    WITH
    (
        ROWTERMINATOR ='\n',
        --  New Line Feed (\n) automatically adds Carrige Return (\r)
        FIELDTERMINATOR = '\t',
        --delimiter
        FIRSTROW = 1
    )

PRINT 'File data copied to Temp table'

SELECT * FROM #UserLocation

The simplest way to read and increment a numeric value in a text file would be to not do it in SQL Server, though you can still use SQL Server to cause it to happen.

You can create a CMD script as follows that accepts a single input parameter for the file name and then increments the value inside of that file by 1. If the file name does not exist, it will be created with an initial value of 0.

Just create a new text file in Windows Explorer and replace the entire name, including the extension, with:  IncrementValue.cmd.

Then edit the file and paste in the following:

@ECHO OFF

SETLOCAL ENABLEDELAYEDEXPANSION

IF NOT EXIST %1 ECHO 0 > %1

FOR /F %%A IN ('TYPE %1') DO (
    SET /A NewTempValue=%%A+1
    ECHO !NewTempValue! > %1
)

REM Uncomment the following line to get the value back in xp_cmdshell
REM ECHO !NewTempValue!

Then just call it in SQL Server, either from a Command-step in a SQL Server Agent job, or via xp_cmdshell like so:

-- remove the ", NO_OUTPUT" if you need to get the new value back
EXEC xp_cmdshell N'IncrementValue.cmd MyFile.txt', NO_OUTPUT;

Or, this same operation could be done in a PowerShell script.


MySQL not using primary key on inner join I have two tables:

Table 1:

CREATE TABLE `lk_transaction_types` (
  `transactionTypeID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `isActive` tinyint(2) unsigned NOT NULL,
  `code` varchar(8) NOT NULL,
  `description` varchar(150) NOT NULL,
  `isInbound` tinyint(2) unsigned NOT NULL DEFAULT '1',
  `isOutbound` tinyint(2) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`transactionTypeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Table 2:

CREATE TABLE `ediLoad` (
  `loadID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `processID` int(10) unsigned NOT NULL,
  `success` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `transactionTypeID` tinyint(2) unsigned DEFAULT NULL,
  `escoID` int(10) unsigned DEFAULT NULL,
  `ldcID` int(10) unsigned DEFAULT NULL,
  `commodityType` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `filename` varchar(150) NOT NULL,
  `loadDate` datetime NOT NULL,
  `processed` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `processedDate` datetime DEFAULT NULL,
  `dataApplied` tinyint(2) unsigned NOT NULL DEFAULT '0',
  `dataAppliedDate` datetime DEFAULT NULL,
  `errorID` tinyint(3) unsigned DEFAULT NULL,
  `error` tinyint(2) unsigned DEFAULT '0',
  `warning` tinyint(2) unsigned DEFAULT '0',
  PRIMARY KEY (`loadID`),
  KEY `idx_processID` (`processID`,`transactionTypeID`,`escoID`),
  KEY `idx_escoID` (`escoID`),
  KEY `idx_filename` (`success`,`filename`),
  KEY `idx_bulk` (`processed`,`loadDate`),
  KEY `idx_loadDate` (`loadDate`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When trying to run a simple query it is not using the Primary Key on the lk_transaction_types table:

SELECT COUNT(0)
FROM edi.ediLoad l
INNER JOIN edi.lk_transaction_types lk
  ON  lk.transactionTypeID = l.transactionTypeID
WHERE l.escoID = 2
AND lk.isActive = 1
AND lk.isInbound = 1;

The Query is very slow. So I run explain and get this:

+----+-------------+-------+------------+------+-----------------------------------+------------------------+---------+--------------------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys                     | key                    | key_len | ref                      | rows | filtered | Extra                              |
+----+-------------+-------+------------+------+-----------------------------------+------------------------+---------+--------------------------+------+----------+------------------------------------+
|  1 | SIMPLE      | lk    | NULL       | ALL  | PRIMARY                           | NULL                   | NULL    | NULL                     |   31 |     3.23 | Using where                        |
|  1 | SIMPLE      | l     | NULL       | ref  | idx_escoID,idx_ transactionTypeID | idx_ transactionTypeID | 2       | edi.lk.transactionTypeID | 7158 |    50.00 | Using index condition; Using where |
+----+-------------+-------+------------+------+-----------------------------------+------------------------+---------+--------------------------+------+----------+------------------------------------+

This seems like a simple query with one join on a primary key. Why is it not using the primary key? I've even tried adding 'FORCE INDEX FOR JOIN (PRIMARY)' to the join and it still doesn't use the primary key. Any help would be great. Thanks!

Notes

A FOREIGN KEY adds a constraint; it does not add an index if there is already one there.
Datatypes should match, but what you had was "close enough" for JOINing.
What was wrong -- The columns of an INDEX are looked at left-to-right.  Since there was no mention of processID (the first column), the index was not useful.
When JOINing, MySQL usually starts with one table, then repeatedly reaches into the other.  It picked lk.  Why?...
When practical, MySQL will start with the table that it can do some filtering on.  But neither table had any useful indexes.  So?...
As a fallback, MySQL starts with the 'smaller' table, lk_transaction_types and did a full table scan ("ALL").

To make the query more efficient, usually the filtering (WHERE) is tackled first:

WHERE l.escoID = 2
  AND lk.isActive = 1
  AND lk.isInbound = 1;

To help the Optimizer picks l as the first table, have

INDEX(escoID, ...)

Or, to help it pick lk, have

INDEX(isActive, isInbound, ...)  -- in either order

The ... is optionally more columns.

Without understanding the distribution of the data values, let me proceed with deriving the optimal indexes for each case.

SELECT  COUNT(0)
    FROM  edi.ediLoad l
    INNER JOIN  edi.lk_transaction_types lk
        ON lk.transactionTypeID =
            l.transactionTypeID
    WHERE   l.escoID = 2
      AND  lk.isActive = 1
      AND  lk.isInbound = 1;

Case:  Start with l:

l:   INDEX(escoID,             -- first, so as to satisfy `WHERE`
           transactionTypeID)  -- added to make the index "covering"

A "Covering index" contains all the columns (of one table) needed to satisfy the entire query.  This lets the processing work only in the index BTree, and ignore the Data BTree.

lk:  INDEX(isActive, isInbound, transactionTypeID)  -- in any order

Again, this is 'covering'.

Case:  Start with lk:

lk:  INDEX(isActive, isInbound,  -- first, in either order
           transactionTypeID)
l:   INDEX(escoID, transactionTypeID) -- in either order

In neither case was either PRIMARY KEY useful.  So, I claim your original question was ill-advised.  Instead, you should have asked "how can I make this query more efficient".  That is the question I have been answering.

One more thing.  Let's combine the two cases to minimize the number of indexes.

lk:  INDEX(isActive, isInbound,  -- first, in either order
           transactionTypeID)
l:   INDEX(escoID, transactionTypeID)  -- in this order

With InnoDB, the PK is "clustered" with the data. PRIMARY KEY(transactionTypeID) is sufficient for the second case, but not the first.

More on creating indexes.


MySQL dies when trying to access a specific database For some reason, whatever I do I cannot access one of my databases (through command line or through something like phpmyadmin). However, the application that depends on this database is not having any issues and is working perfectly.

I've tried a few things to have it connect

$ mysql -u root -p

$ use my_database

$ show tables

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: my_database

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111)
ERROR:
Can't connect to the server

I really am only trying to get the dump of the database

$ mysqldump -u root -p my_database | gzip > my_database.sql.gz

this gives me the following error

mysqldump: Got error: 2013: Lost connection to MySQL server during query when using LOCK TABLES

I've also tried

mysqlcheck -u root -p -A

It goes through all the tables until it tries to access the tables on my_database where it fails and throws this error

mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'CHECK TABLE ... '

I just can't figure out what's wrong. If there is any other way of backing this database up please let me know or how to fix these errors.

I frequently use innodb force recovery in this case, and it often resolves the issue. On Mac and Windows installs, this is done by editing my.ini

Search for innodb_force in the text, uncomment that line, set the value to 2. Restart the MySQL service.
Undo your edits ( comment out that line in my.ini)
Restart the service and test. You can safely do level 3 in the same manner, but research more about it before attempting level 4 or higher.

Hope this helps.


recover a single mysql database on a busy master-slave replicated system Looking for a strategy or tool to deal with recovering a single database to a point-in-time in a busy replicated system.

I have 12 databases running on 2 MySQL 5.0.77 servers in master-slave replicated configuration. A full dump is taken of the read-only slave daily, and there are incremental SQL dumps available, with these backups off-site and the replication status is monitored.

Edit: Tables are a mixtures of InnoDB and myISAM, hence engine specific solutions are not available.

So given a complete failure of the master server, I can break the replication and promote the slave server, I also have the option of rebuilding a new server and configuring from the offside FULL backup, and then apply the differentials taken hourly from the slave.

However I am concerned how to deal with partial failure, or failure of a single database. I can think of 2 scenarios that are quite likely;

database 7 (for example) becomes corrupted, continues to serve some requests until someone notices that it is broken, or alerts from the log files...
Some query like drop database, drop table, "update where..." type query borks a single database, or some subset therein.

At the moment I have a bunch of FULL dumps as FULL-$DATE-all-databases.sql.gz files , and differentials that can be applied to the FULL dumps as DIFF-$DATE-all-databases.sql.gz

To restore database 7 to some point-in-time would require a grep through the FULL and DIFF files, and manual application of that sql.

How should I proceed in order to make it possible to be able to recover to one of the previous DIFF dumps to the master database?

DO I need to backup to individual database files, ie

mysqldump --databases "database1" | gzip > database1.sql.gz
mysqldump --databases "database2" | gzip > database2.sql.gz
mysqldump --databases "database3" | gzip > database3.sql.gz

rather than..

mysqldump --master-data --lock--all-databases --all-databases | gzip > all-databases.sql.gz

If I go for separate mysqldump files, what happens to the master data binary log, and should I even be setting --master-data for the master server recovery dumps?

If all your database use InnoDB only, I have some good news.

You should be to dump all the database in parallel from a slave.

In fact, you can force all the databases into the same point-in-time.

First thing to remember about a the Slave is that it is not not required to have binary logging enabled if it is not a Master for other Slaves.

You cannot use --master-data option for parallel dumps because each dump will have a different position written at line 22 of each dump file. It is better to record the Master's last log file and position the Slave executed using SHOW SLAVE STATUS\G. That way, all the databases have the same point-in-time position.

You can collect all databases and script the parallel dump of all the database.

DBLIST=/tmp/ListOfDatabasesToParallelDump.txt
BACKUP_BASE=/backups
BACKUP_DATE=`date +"%Y%m%d_%H%M%S"`
BACKUP_HOME=${BACKUP_BASE}/${BACKUP_DATE}
mkdir ${BACKUP_HOME}
cd ${BACKUP_HOME}

mysql -h... -u... -p... -e"STOP SLAVE;"
mysql -h... -u... -p... -e"SHOW SLAVE STATUS\G" > ${SSS}
LOGFIL=`cat ${SSS} | grep "Relay_Master_Log_File" | awk '{print $2}'`
LOGPOS=`cat ${SSS} | grep "Exec_Master_Log_Pos"   | awk '{print $2}'`
echo "Master was at ${LOGFIL} Position ${LOGPOS} for this Backup" > Master_Log_FilePos.txt

mysql -h... -u... -p... -AN -e"SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','mysql','performance_schema')" > ${DBLIST}

for DB in `cat ${DBLIST}`
do
    mysqldump -h... -u... -p... --hex-blob --routines --triggers ${DB} | gzip > ${DB}.sql.gz &
done
wait

mysql -h... -u... -p... -e"START SLAVE;"

If there are simply too many databases, dump them 10 or 20 at a time as follows:

DBLIST=/tmp/ListOfDatabasesToParallelDump.txt
SSS=/tmp/ShowSlaveStatusDisplay.txt
BACKUP_BASE=/backups
BACKUP_DATE=`date +"%Y%m%d_%H%M%S"`
BACKUP_HOME=${BACKUP_BASE}/${BACKUP_DATE}
mkdir ${BACKUP_HOME}
cd ${BACKUP_HOME}

mysql -h... -u... -p... -e"STOP SLAVE;"
mysql -h... -u... -p... -e"SHOW SLAVE STATUS\G" > ${SSS}
LOGFIL=`cat ${SSS} | grep "Relay_Master_Log_File" | awk '{print $2}'`
LOGPOS=`cat ${SSS} | grep "Exec_Master_Log_Pos"   | awk '{print $2}'`
echo "Master was at ${LOGFIL} Position ${LOGPOS} for this Backup" > Master_Log_FilePos.txt

mysql -h... -u... -p... -AN -e"SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','mysql','performance_schema')" > ${DBLIST}

COMMIT_LIMIT=20
COMMIT_COUNT=0
for DB in `cat ${DBLIST}`
do
    mysqldump -h... -u... -p... --hex-blob --routines --triggers ${DB} | gzip > ${DB}.sql.gz &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
wait
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

mysql -h... -u... -p... -e"START SLAVE;"

If you need to recover a single table, you can parallel dump tables 20 at a time in size order.

Try this:

TBLIST=/tmp/ListOfTablesToParallelDump.txt
SSS=/tmp/ShowSlaveStatusDisplay.txt
BACKUP_BASE=/backups
BACKUP_DATE=`date +"%Y%m%d_%H%M%S"`
BACKUP_HOME=${BACKUP_BASE}/${BACKUP_DATE}
mkdir ${BACKUP_HOME}
cd ${BACKUP_HOME}

mysql -h... -u... -p... -e"STOP SLAVE;"
mysql -h... -u... -p... -e"SHOW SLAVE STATUS\G" > ${SSS}
LOGFIL=`cat ${SSS} | grep "Relay_Master_Log_File" | awk '{print $2}'`
LOGPOS=`cat ${SSS} | grep "Exec_Master_Log_Pos"   | awk '{print $2}'`
echo "Master was at ${LOGFIL} Position ${LOGPOS} for this Backup" > Master_Log_FilePos.txt

mysql -h... -u... -p... -AN -e"SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql','performance_schema') ORDER BY data_length" > ${DBLIST}

COMMIT_LIMIT=20
COMMIT_COUNT=0
for DBTB in `cat ${TBLIST}`
do
    DB=`echo "${DBTB}" | sed 's/\./ /g' | awk '{print $1}'`
    TB=`echo "${DBTB}" | sed 's/\./ /g' | awk '{print $2}'`
    DUMPFILE=$DB-{DB}-TBL-${TB}.sql.gz
    mysqldump -h... -u... -p... --hex-blob --routines --triggers ${DB} ${TB} | gzip >  ${DUMPFILE} &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
wait
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

mysql -h... -u... -p... -e"START SLAVE;"

Now that you have scripts to dump databases or individual tables, you can load that data at your discretion. If you need to get SQL executed from the binary logs on the master, you can use mysqlbinlog and give it the position ot datetime and output the SQL to other text files. You just have to perform due diligence to find the amount of data you need from whatever timestamps the bnary logs have. Just remember that every binary log's timestamp in the OS represents that last time it was written.


copy backup from azure blob has anyone a sql script that allow us copy the last full backup files from azure blob container to an ftp server or simply to the machine from which we run the script (sql server machine)?

Thanks in advance

You can use AZCopy to move files from blob to Filesystems or eitherwise..

Below is the syntax..

AzCopy
/Source:https://myaccount.blob.core.windows.net/mycontainer
/Dest:C:\myfolder
/SourceKey:key
/Pattern:a
/S

use the pattern option to specify name (this will work like SQL like query)

From SQLServer 2014,you can also restore from Blob directly..

Restoring From Backups Stored in Microsoft Azure


Preventing mysql deadlocks in your php application that uses SELECT… LOCK IN SHARE MODE If I understand SELECT ... LOCK IN SHARE MODE correctly, you can place it into a mysql transaction to select the rows you will be working with during that transaction.

This is done in order to "lock out" those selected rows from other session's writing/deleting actions (but other sessions can still read the rows) until your transaction completes.

From that point, the rows that were locked with the SELECT LOCK IN SHARE MODE statement are released so other sessions can access them for writing, deleting, etc.

This is exactly what I want for my comments table. Whenever a comment is added to a post on my site, I need to lock all the comment rows tied to that post while I update some meta data on all the locked rows. Additionally, if two comments get submitted at the same time, I don't want them to both have access to the relevant comment rows at the same time because they will basically screw each other (and the meta data) up.

So, I want to incorporate SELECT LOCK IN SHARE MODE into the comment upload script so the first session to run the lock in query gets complete control over the comment rows until it finishes the entire transaction (and the script that was slightly slower has to wait until the entire transaction from the first script executes).

I am a but concerned about creating deadlocks where script A locks data that script B needs, and script B locks data that script A needs.

How can I get around this in my application?
I am using only innodb in my website database, so do I need to worry about table locks?

OMG I spent hours thinking through this issue.

I answered three very tough questions addressing this exact issue.

SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.

Here are three DBA Stack Exchanges questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

Jun 14, 2011 : Reasons for occasionally slow queries?
Jun 08, 2011 : Will these two queries result in a deadlock if executed in sequence?
Jun 06, 2011 : Trouble deciphering a deadlock in an innodb status log

Please read them carefully so you can relive my episode of philosophical InnoDB, right or wrong.


Deduplicate Data Using Having with Subquery I've inherited a DB that was lacking a unique constraint so data was/is duplicated. I'm now trying to remove the duplicate records, then add a constraint to block this going forward.

I have this query:

SELECT count(*) as dacount, substr(group_concat(id), (locate(',', group_concat(id))+ 1))
FROM `game`
group by matchid, ordinal
having dacount > 1
order by dacount desc

which correctly gives me the ids of the rows I need to delete. The problem with this though is that I can't use this as a subquery on the DELETE because of the dacount with the having parameter. Is there another way to do this?

Here was my plan:

DELETE FROM game WHERE id IN (SELECT count(*) as dacount, substr(group_concat(id), (locate(',', group_concat(id))+ 1))
FROM `game`
GROUP BY matchid, ordinal
HAVING dacount > 1)

There are restrictions regarding self-joins in MySQL's implementation of UPDATE and DELETE. The issue is usually solvable by using joins:

DELETE g.*
FROM game AS g
  JOIN
   ( SELECT matchid, ordinal, min(id) AS id
     FROM game
     GROUP BY matchid, ordinal
     -- HAVING count(*) > 1
   ) AS d
   ON  g.matchid = d.matchid
   AND g.ordinal = d.ordinal
   AND g.id > d.id ;

Test at dbfiddle.uk.

Another way would be using a LEFT JOIN / IS NULL construction:

DELETE g.*
FROM game AS g
  LEFT JOIN
   ( SELECT min(id) AS id
     FROM game
     GROUP BY matchid, ordinal
   ) AS d
   ON  g.id = d.id
WHERE d.id IS NULL ;

or an EXISTS subquery rewritten as a JOIN
(delete all games that there exists another game with same matchid, ordinal and smaller id):

DELETE g.*
FROM game AS g
  JOIN
     game AS d
   ON  g.matchid = d.matchid
   AND g.ordinal = d.ordinal
   AND g.id > d.id
 ;


One vs. Many Tables for Frequently but Asynchronously Updated Columns? As part of my Postgres 10 schema, I have two tables; current and historic -- both have the same columns.

current has about 20 (+/-) columns, and holds the most up-to-date information for accounts. I need to keep a timestamp showing when the information came in, but each of the 20 columns gets updated at a different time. So, per account current will have numberOfColumns rows (as opposed to one row for one account) -- as new data comes in for one column, the row holding the information for just that column will get updated.

This is so I can quickly access the current information for the account.

However, I also need to store the history; so every time current is updated, the row (containing a single entry -- aside from metadata) will be 'moved' to the historic table. This table has the same number of columns.

So, I will end up with a very sparse table of 20 or so columns, and with each row containing a timestamp, one column's worth of 'data', and some metadata (e.g., account_id).

The current table will be read from frequently. Both INSERTs and SELECTs must be very fast.

The historic table will likely generally be used for offline analytics, and space is a priority SELECT performance, but INSERTs must be fast (because they will be done while updating current.

So, my questions are:

Is this a bad design? What is the usual solution for this type of situation (where a number of things like account details are asynchronously updated)?
Should I break it up so I have 20 tables, with 1 data column plus the metadata I need per table? It seems that would waste space.
What are the pros and cons of each approach?

Thanks in advance.

Edit(s):

In response to @JonathanFite:

Adding the schema and sample queries would use up a lot of space; but,

The 'data' columns are a number of decimal and int values and one text value, with no constraints.
There are 4 additional values with not null constraints (bigserial, text, text, and timestamptz) that will be a part of each insertion.
Each insertion consists of those 4, plus a decimal or int (or text in one case).
All 20 columns could be updated within the same second, or minutes apart.
The selects will request the results to be ordered by time, and could be for anywhere from a single data column to nearly all of them.

Does that help clarify things?

You should use one table and views for Current and History data.

Reasons:

This is easier to update (just add a new data row with a newer timestamp)
easier to maintain on tables changes
does not need move logic which can fail
the views can easily be changed to view full history (with current data) without table joins


How to query based on related table without returning records from related table Table_A

^ID^Name^
|1 |Joe |
|2 |Jane|

Table_B

^ID^Foreign_ID^Company  ^Specialty^
|1 |1         |Microsoft|SQL      |
|2 |1         |Microsoft|PHP      |
|3 |2         |Oracle   |SQL      |
|4 |2         |Oracle   |HTML5    |

The query:

SELECT Name, Company
FROM   Table_A
         LEFT JOIN Table_B
         ON Table_A.ID = Table_B.Foreign_ID
WHERE  Table_B = 'Microsoft'

Say I'm formatting a report, and I'm only interested in employee's that work at Microsoft and I only want 1 record per employee, how do I prevent the Speciality column in Table_B from creating multiple rows?

You don't need a join, since you have no columns from table_B in the output. You could use a semi-join, i.e an EXISTS subquery:

SELECT a.Name, 'Microsoft' AS Company
FROM Table_A AS a
WHERE EXISTS
      ( SELECT *
        FROM Table_B AS b
        WHERE a.ID = b.Foreign_ID
          AND b.Company = 'Microsoft'
      ) ;

If you did want some other column from table_B (say the Specialty) and still only 1 row, you could use either a derived table with a window function, or CROSS APPLY with TOP 1. CROSS APPLY is a special join where we can reference previous tables. Note that it's similar to INNER JOIN (you had LEFT JOIN while you need INNER JOIN):

SELECT a.Name, 'Microsoft' AS Company, b.Specialty
FROM Table_A AS a
  CROSS APPLY
    ( SELECT TOP (1) bi.Specialty
      FROM Table_B AS bi
      WHERE a.ID = bi.Foreign_ID
        AND b.Company = 'Microsoft'
      ORDER BY Specialty
    ) AS b ;


View images as query results I've done lots of research on the topic but I'm drawing a blank.  Are their any database engines that can return rendered images in the query response?

For example:

Select images from fruit where type = "apple";

Would return rows of the apple type as rendered images, png, jpg, etc.

It is not up to the database engine to render an image or provide any visualisations - that work is performed by the client application receiving the data returned by the database engine.

What you want to look for is an IDE or query tool that has in-built visualisation capabilities. SSMS does not do this natively for SQL Server, but there are plug-ins and alternative tools that can do this (see this question).

For Oracle, SQL Developer is apparently capable of rendering BLOBs in the IDE. For other RDBMS you will need to research.


This query takes anywhere from 1 - 30 seconds depending on server load... How can it be optimized? SELECT id FROM watch_list WHERE item_id = '16837' AND user_id = '2582' LIMIT 1;
# Time: 171025 20:41:38
# User@Host: dylanw68_actmgr[dylanw68_actmgr] @ localhost []  Id: 70246342
# Schema: dylanw68_tauction2  Last_errno: 0  Killed: 0
# Query_time: 1.268647  Lock_time: 0.000031  Rows_sent: 0  Rows_examined: 14760  Rows_affected: 0
# Bytes_sent: 91
SET timestamp=1508982098;

Each row only has 4 values so I am really confused on why this simple query takes so long.

Assuming that id is the PRIMARY KEY of the watch_list table, the following should make the query very fast.

ALTER TABLE `watch_list`
ADD INDEX `AppropriateIndexName` (`item_id`,`user_id`)


Classic ASP Connection to SQL Server 2014 without TLS 1.0 using ADODB.Connection With TLS 1.0 being disabled in the name of PCI compliance, I'm unable to get a 32-bit Classic ASP application working.

Per MS / Stack Exchange recommendations, I've installed:

SQL Server 2014 SP1 CU1
.NET Framework 4.6

This has gotten our ASP.NET applications / SSMS running. But our Classic ASP application, which uses an ADODB.Connection object is not working.

I've tried a connection string which uses Provider=SQLNCLI11;, but that doesn't seem to help either. The Shared Memory provider is still complaining about nothing being on the other end of the pipe. Error message:

  Microsoft SQL Server Native Client 11.0 error '80004005'

  Shared Memory Provider: No process is on the other end of the pipe.

I also tried using Named Pipes with the connection string Provider=SQLNCLI11;Server=np:\\.\pipe\MSSQL$SQLEXPRESS\sql\query;Database=northwind;Trusted_Connection=Yes; and received this error message:

  Microsoft SQL Server Native Client 11.0 error '80004005'

  Named Pipes Provider: No process is on the other end of the pipe.

Is there a patch for ADODB that I should be aware of?
Should I consider using Named Pipes in some other way or the like instead? (although I'm a little baffled as to why shared memory doesn't work, regardless of the TLS settings)

Updates from comments:

The web server and the SQL Server are on the same box.

It's a SQLExpress named instance. The connection string working with TLS 1.0 enabled is:

"Driver={SQL Server}; Server=.\SQLExpress; Database=northwind; Trusted_Connection=Yes; Integrated_Security=True;"

I've also tried:

"Provider=SQLNCLI11; Server=.\SQLExpress; Database=northwind; Trusted_Connection=Yes;"

Neither work once TLS 1.0 is disabled. I know Trusted_Connection / Integrated_Security is redundant, but messing around with these doesn't seem to help either.

I've toggled enabling, disabling, and order for both TCP and named pipes. The name of the pipe used in the connection string above is directly from the Server Configuration for that instance. I've also tried disabling Shared Memory to make sure named pipes is working. I haven't tried TCP (as if Shared Memory doesn't work because of TLS, why would TCP?) Shared Memory and Named Pipes both work with TLS 1.0 enabled. As soon as I flip the registry key to disable TLS 1.0 (and reboot), the above error messages occur. We're likely just going to put this machine behind a proxy.

SQL server service won’t start after disabling TLS 1.0 and SSL 3.0

The issue described in that link is addressed by CU1 in my posed question above. The SQL Server instance DOES start with TLS 1.0 disabled (thanks to the MS update). .NET based applications work just fine with the SQL instance (after update to 4.6). This question is specific to a Classic ASP (32-bit) application. My understanding is that the native client is also patched by CU1, hence my confusion as to why this isn't working.

You might possibly be trying to use 32 bit legacy drivers.

Try running C:\Windows\SysWOW64\odbcad32.exe to create a DSN that connects to your SQL Server database using the 32 bit SQL Server driver. First test the connectivity using the GUI.

Then try specifying your DSN in your connection string like so:

DSN=myDsn;Uid=myUsername;Pwd=;

The 64-bit version is located in:
C:\Windows\System32\odbcad32.exe

P.S. I've not tried this but the provider name for the 32-bit SQL Server is actually "SQL Server". It'd be nice if all you had to do was replace SQLNCLI11 with SQL Server. Here's my file DSN I created with the 32 bit version--notice the driver...

[ODBC]
DRIVER=SQL Server
UID=sa
WSID=P13-0000
APP=Microsoft® Windows® Operating System
SERVER=mcdba1


postgresql, select sum of rows for multiple ids I have a query with sums I'd like to repeat with variables which are day and consumption_sector.name. I don't know how to simplify my question enough not to lose the point I need. Programatically I'd like to have variables and a loop all into one only select result.
Where I am is with having one result line, I'd like to customize and do multiple for having for example all month of year of give date, or all results for a given date for multiple consumption_sector.name
Result

Query I reached
set search_path to dev;
SELECT
    (SELECT SUM(total)
        FROM daily_unit_big_int
        INNER JOIN measuring_point on measuring_point.id = daily_unit_big_int.measuring_point_id
        INNER JOIN consumption_sector on consumption_sector.id = measuring_point.consumption_sector_id
        WHERE
        measuring_point.type='devices_logger' AND -- an enumerate
        consumption_sector.name like '%gnacio%' AND
        day = '2020-10-25') AS daily_total, -- a date I'd like to give as parameter/variable
    (SELECT SUM(total)
        FROM daily_unit_big_int
        INNER JOIN measuring_point ON measuring_point.id = daily_unit_big_int.measuring_point_id
        INNER JOIN consumption_sector ON consumption_sector.id = measuring_point.consumption_sector_id
        WHERE
        measuring_point.type='devices_logger' AND
        consumption_sector.name like '%gnacio%' AND
        EXTRACT(MONTH FROM day) = '10' AND
        EXTRACT(YEAR FROM day) = '2020'
        ) AS monthly_total,
    (SELECT SUM(total)
        FROM daily_unit_big_int
        INNER JOIN measuring_point ON measuring_point.id = daily_unit_big_int.measuring_point_id
        INNER JOIN consumption_sector ON consumption_sector.id = measuring_point.consumption_sector_id
        WHERE
        measuring_point.type='devices_logger' AND
        consumption_sector.name like '%gnacio%' AND
        EXTRACT(YEAR FROM day) = '2020'
        ) AS yearly_total
    ;

Minimal example with pseudo code to understand the point
$variable_date='2020-10-25'
for $consumption_sector_id in (12,13,46,72) do
    SELECT consumption_sector.id AS consumption_sector_id, SUM(total) AS daily_total
            FROM daily_unit_big_int
            INNER JOIN measuring_point on measuring_point.id = daily_unit_big_int.measuring_point_id
            INNER JOIN consumption_sector on consumption_sector.id = measuring_point.consumption_sector_id
            WHERE
            measuring_point.type='devices_logger' AND -- an enumerate
            consumption_sector.id = $consumption_sector_id AND
            day = $variable_date -- a date I'd like to give as parameter/variable
end--for

Result
|consumption_sector_id|daily_total|
|12|100|
|13|1000|
|46|1000|
|72|15468|

Here a fiddle for the example where I retreive all data for a particular day and a particular consumption_sector_id

As you can see, i remodelled your query(only with two to get you an idea how to do it
W
What you make you make for every SELECT that you have a seperate subquery and join them with cs.id, as you only want certain ids

SELECT *
FROM
    (SELECT cs.id,SUM(du.total) AS daily_total
            FROM daily_unit_big_int as du
            INNER JOIN measuring_point as mp on mp.id = du.measuring_point_id
            INNER JOIN consumption_sector as cs on cs.id = mp.consumption_sector_id
            WHERE
            mp.type='devices_logger' AND -- an enumerate
            cs.id in (1,2) AND
            du.day = '2020-01-01' -- a date I'd like to give as parameter/variable
     GROUP BY cs.id) dayly
     INNER JOIN
     (SELECT cs.id,SUM(total) monthly
        FROM daily_unit_big_int du
        INNER JOIN measuring_point mp ON mp.id = du.measuring_point_id
        INNER JOIN consumption_sector cs ON cs.id = mp.consumption_sector_id
        WHERE
        mp.type='devices_logger' AND
        cs.id in (1,2) AND
        EXTRACT(MONTH FROM du.day) = '01' AND
        EXTRACT(YEAR FROM du.day) = '2020'
        GROUP BY cs.id
        ) monthly ON dayly.id = monthly.id

id | daily_total | id | monthly
-: | ----------: | -: | ------:
 1 |        1555 |  1 |   31665
 2 |        2000 |  2 |    6000

db<>fiddle here


Updating an indexed column performance impact in SQL server I am trying to understand is there any performance impact if I am trying to update a column which is the key column of a non-clustered index in SQL server 2008? For e,g

I have a column IsActive in the table TestTable. I have created a non clustered index on column IsActive. Now I am trying to update the value of the column IsActive through an update query like

UPDATE TestTable SET IsActive= 1 WHERE IsActive = 0

I need to understand will this perform better or not having the index on it will be better. I am asking his as reindexing takes place when index vales are updated. So will this reindexing have a impact on the query performance? Note : The table has excess of 100 million rows.

Modifying the value of a field used in an index will certainly take more time than modifying the value of a field not involved in an index.  Having said that, the WHERE clause in your UPDATE statement could be used to speed the search for matching rows when the field in question is part of an index.  In this example, where you have a boolean field present in 100 million rows means the selectivity of the field is so poor that a table scan will likely be required anyway.  I would estimate that updating this particular field would be quicker if there was no index on it.

However, to answer your question of whether or not there will be performance impact on your system is really only answerable by you since there are a many other variables to consider such as storage speed, RAM availability, etc.


String parsing: Extracting the filename from a URL This is a link I have in a column of a table:

http://earthexplorer.usgs.gov/browse/etm/1/11/2000/LE70010112000130KIS00.jpg

There are like 1000's of rows like this.

Is it possible to extract the FileName like LE70010112000130KIS00 (without the path and filename extension) and put it in another column?

I am new to SQL Server.

I used this code to extract the FileName with Extension.

UPDATE TableName set ColumnName= right(Image_Link, CHARINDEX('/', REVERSE(ColumnName) + '/') - 1)

And this code to extract and update only FileName

UPDATE TableName  SET ColumnName= REVERSE(SUBSTRING(REVERSE(ColumnName),CHARINDEX('.', REVERSE(ColumnName)) + 1, 999))


Trouble installing percona 5.6 on Google compute engine debian I've been trying to install percona server on a fresh Google compute engine instance.

I haven't installed mysql server previously.
I followed the instructions from here.

I get this when I use:

apt-get install percona-server-server-5.6 percona-server-client-5.6

Setting up percona-server-common-5.6 (5.6.15-rel63.0-519.wheezy) ...
Setting up percona-server-client-5.6 (5.6.15-rel63.0-519.wheezy) ...
Setting up percona-server-server-5.6 (5.6.15-rel63.0-519.wheezy) ...
[ ok ] Stopping MySQL (Percona Server): mysqld.
2014-01-22 04:54:28 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated.     Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-01-22 04:54:28 21278 [Note] Plugin 'FEDERATED' is disabled.
2014-01-22 04:54:28 21278 [Note] InnoDB: The InnoDB memory heap is disabled
2014-01-22 04:54:28 21278 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-01-22 04:54:28 21278 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-01-22 04:54:28 21278 [Note] InnoDB: Using Linux native AIO
2014-01-22 04:54:28 21278 [Note] InnoDB: Using CPU crc32 instructions
2014-01-22 04:54:28 21278 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-01-22 04:54:28 21278 [Note] InnoDB: Completed initialization of buffer pool
2014-01-22 04:54:28 21278 [Note] InnoDB: Highest supported file format is Barracuda.
2014-01-22 04:54:29 21278 [Note] InnoDB: 128 rollback segment(s) are active.
2014-01-22 04:54:29 21278 [Note] InnoDB: Waiting for purge to start
2014-01-22 04:54:29 21278 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.15-    rel63.0 started; log sequence number 1625987
2014-01-22 04:54:29 21278 [Note] RSA private key file not found:     /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2014-01-22 04:54:29 21278 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
2014-01-22 04:54:29 21278 [Note] Binlog end
2014-01-22 04:54:29 21278 [Note] InnoDB: FTS optimize thread exiting.
2014-01-22 04:54:29 21278 [Note] InnoDB: Starting shutdown...
2014-01-22 04:54:30 21278 [Note] InnoDB: Shutdown completed; log sequence number 1625997

 * Percona Server is distributed with several useful UDF (User Defined Function) from Percona Toolkit.
 * Run the following commands to create these functions:

    mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"
    mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"
    mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"

 * See http://www.percona.com/doc/percona-server/5.5/management/udf_percona_toolkit.html for more details

[FAIL] Starting MySQL (Percona Server) database server: mysqld . . . . . . . . . . . . . . failed!
invoke-rc.d: initscript mysql, action "start" failed.

What am I doing wrong?

When I type /etc/init.d/mysql start
I get

[....] Starting MySQL (Percona Server) database server: mysqld/etc/init.d/mysql: fork: Cannot allocate memory
/etc/init.d/mysql: line 48: /usr/bin/tail: Cannot allocate memory
/etc/init.d/mysql: line 47: /bin/grep: Cannot allocate memory
/etc/init.d/mysql: line 46: /usr/bin/tr: Cannot allocate memory
/etc/init.d/mysql: line 45: /usr/sbin/mysqld: Cannot allocate memory
/lib/lsb/init-functions: fork: Cannot allocate memory

I was having the exact same problem on an AWS t1.micro that has about .6GB of memory. It would go through the entire setup process then fail on initializing mysqld. As it turns out, that instance did not have enough memory to get percona server 5.6 going. I brought up a faster instance running 3.75GB of memory and it started without issue.

If I were to speculate, I would guess you are on the f1-micro instance which is why you are getting the "Cannot allocate memory" errors. Try the g1-small instance and see if that is enough to get mysql running.

Update:
I was/am using Percona's default configuration and I was able to successfully install on a Digital Ocean droplet with 1GB of memory. So, it seems as though the magic number of needed memory is between .6GB and 1GB.


ON DELETE CASCADE has no effect I've been searching SE forums in order to solve that, however I wasn't able to find out where I was wrong.

In my DB I want to link in a strong way a parent table to a child table : to do so, I want all the child table rows that reference the parent to be deleted when the parent is deleted.

My app is not in production, it uses MariaDB, and PHP mysqli-like functions. All the tables use InnoDB engine. The keys involved (parent primary, and foreign key) belong to the same type.

Here is how the install script creates the tables (simplified). The rows are simply concatenated in order to produce a cleaner view of the query. PHP $ are not shown here.

Parent table :

       "CREATE TABLE Campagne( ".
       "id_campagne INT(3) NOT NULL AUTO_INCREMENT, ".
       "nom VARCHAR(100) NOT NULL, ".
                ...
       "PRIMARY KEY (id_campagne),".
       "FOREIGN KEY (utili_num) REFERENCES Utilisateur (utili_num)) ENGINE=INNODB;"

Child table :

       "CREATE TABLE ChildTable ( ".
       "id_child INT(9) NOT NULL AUTO_INCREMENT, ".
                ...
       "id_campagne INT(3) NOT NULL, ".
       "CONSTRAINT `FK_campagne_id_campagne` FOREIGN KEY (`id_campagne`) REFERENCES Campagne (`id_campagne`) ON DELETE CASCADE ,".
       "PRIMARY KEY (id_donnee)) ENGINE=INNODB;"

First of all, I can't use PHP my admin to make a quick delete of a Campagne row (no row is affected when I click on delete). When I use the SQL console, to do something like "DELETE FROM Campagne WHERE id_campagne=3" the corresponding row is properly deleted, however this has no effect on the child rows from the child table that have the matching foreign key.

What am I doing wrong ? Thanks for reading

What version of MySQL are you using? I tested the following with 10.1.16-MariaDB, and it appears to be working:

CREATE TABLE Campagne
( id_campagne INT NOT NULL AUTO_INCREMENT
, PRIMARY KEY (id_campagne) ) ENGINE=INNODB;

CREATE TABLE ChildTable
( id_child INT(9) NOT NULL AUTO_INCREMENT
, id_campagne INT NOT NULL
, PRIMARY KEY (id_child)
, CONSTRAINT FK_campagne_id_campagne
      FOREIGN KEY (id_campagne)
      REFERENCES Campagne (id_campagne) ON DELETE CASCADE
) ENGINE=INNODB;

insert into Campagne (id_campagne) values (DEFAULT);
insert into ChildTable (id_child, id_campagne) values (DEFAULT, 0);
select * from Campagne;
+-------------+
| id_campagne |
+-------------+
|           0 |
+-------------+

select * from ChildTable;
+----------+-------------+
| id_child | id_campagne |
+----------+-------------+
|        0 |           0 |
+----------+-------------+

delete from Campagne where id_campagne = 0;
select * from ChildTable;
Empty set (0.00 sec)


Oracle DB what is the difference between ALL, USER and DBA Objects? As the title suggests, I am trying to understand the structure of our Oracle DBs a little more and a little confused by the ALL, DBA & USER Objects.

A quick SQL

select count(*) ALL_OBJS from all_objects where owner = 'XXXX';
select count(*) DBA_OBJS from dba_objects where owner = 'XXXX';
select count(*) USER_OBJS from USER_OBJECTS; (logged on as XXXX)

Returns:

  ALL_OBJS
----------
      1591

  DBA_OBJS
----------
      1632

 USER_OBJS
----------
      1632

As I presume our user has DBA rights and the USER objects and DBA objects are the same? But what are ALL objects?
If you are looking to clear out a User's SCHEMA which would you remove from or would you drop objects from all three?

USER_OBJECTS are your objects, i.e. the objects where the current user is the owner
ALL_OBJECTS are all objects which you can access. These are of course your own objects from above plus objects from other schemas where access is granted to you.
DBA_OBJECTS are all objects in the database. As a DBA you have access to all objects, thus ALL_OBJECTS and DBA_OBJECTS should be the same if you are connected as DBA.

If you want to remove all objects from a user then the best command would be DROP USER xyz CASCADE;

Do not delete anything from XXX_OBJECTS view. Use DROP ... commands to removed those objects.


Client Encoding Mismatch when creating new database I'm upgrading a program for work that uses postgres 9.2.4. When the program is run for the first time, it creates a production database, using the existing template database. The program used to run with postgres 8, which uses sql_ascii for its client encoding. Postgres 9 uses UTF8.

I know how to fix this on the command line by changing the template database's encoding to sql_ascii, but ideally I'd like to figure out how to get the program to create the new database using the new UTF8 encoding.

I found this line in the program

sqlString = "CREATE DATABASE " & m_sProdDBName & " WITH OWNER=" & _
m_s4 & " TEMPLATE=" & m_s5 & " ENCODING='sql_ascii' LOCATION= DEFAULT;"

And I changed sql_ascii to UTF8.

However, the 'Client encoding mismatch' error gets thrown before that line ever gets executed. It happens when the connection to the template DB is being created. I got a message box to pop up with the connection string right before the connection opens. For some reason, it is hitting the messagebox code twice, even though it should only hit it once. The first time, no connection attempt is made. The second time the program goes on and attempts to open the connection, which is when the error gets thrown. Here are the two connection strings:

DRIVER=postgresql;SERVER=Stephane-PC;PORT=5432;UID=postgres;DATABASE=remindex;PWD=

DRIVER=postgresql;SERVER=Stephane-PC;PORT=5432;UID=postgres;DATABASE=template1;PWD=

(I know that no password is needed so that isn't the problem).
Remindex is the database to be created and template1 is the template DB.

In case you need some additional information, the connection object is an ADODB.Connection object and the Provider is MSDASQL.

I'm not all that good with database stuff, so I apologize for my lack of knowledge.

Any suggestions would be greatly appreciated!

Client Encoding Mismatch is an error message from old versions of the PostgreSQL ODBC driver.

You should go here: http://www.postgresql.org/ftp/odbc/versions/msi/
to get the most recent version and update your system.

Also there are in fact two driver flavors that get installed: ANSI and Unicode. Presumably you need Unicode if you decided to switch to utf-8.


ER vs database schema diagrams When I search the web for "ER diagram" and "database schema", these 3 diagram types are found, but all of them are referred to as "ER diagrams". I want to know:

What is a database schema diagram and what is an ER diagram?
Why all are named "ER diagram"?

ER diagramming grew out of ER modeling.  ER models were intended to be application neutral, so that ER modeling could be applied to a given situation whether the ultimate implementation was to use relational databases or some other technology, such as the (then current) hierarchical databases or the CODASYL style network databases.  Since that time, hierarchical and network databases have been all but eclipsed by relational databases, at least as far as new work is concerned.  At the same time, various post relational data schemes have come up for fitting data management into an object oriented framework or an unstructured enviroment.

ER diagrams were, initially, depictions of an ER model.  Eventually, this mode of diagramming became so widespread that it began to be used for diagramming schemas designed under a relational model as well.  ERD actually works quite well in this manner.  The only confusion that results is when a person builds a relational model, and expects the same benefits out of it that you get from an ER model, or vice versa.

A relational model and an ER model differ in a few important details.  The use of foreign keys and junction tables are peculiar to relational models, and do not appear in ER models.  Nevertheless, they do appear in most of the ER diagrams you will see today.  That is the best clue that what you are really looking at is an ERD picture of a relational schema.

For years, I looked at ER models as though they were "relational lite".  It turns out that this is a misconception.  The two models are really looking at reality from different levels of abstraction.  Each model has its strengths and weaknesses.

As far as good references go, I would start with Wikipedia.  There are good articles in there about the ER model, the relational model, SQL, and some of the major DBMS products like Oracle, SQL Server, and many many others.

Once you understand the models and their uses, you'll be in good shape to tackle the diagramming conventions.


Tools for cnf management, generation? Does anyone know of any good tools for cnf configuration management?  It's often the case I have a group of machines that has a very similar config except for a few differences such as server id or buffer pools to match their specific hardware.

I'm looking for something where I can setup a general template, tell it I want to generate some configs for that machine from that template, have it prompt me for the variables that need to be filled in and write out the .cnfs for me.

Percona just built a tool to do just that called the Configuration Wizard. I tested it out once just to see what it would return and the results were pretty darn close to what we were using on our servers, whose cnf's were put together by highly trained mysql certified dba's.


Huge database - challenges I have recently started a new job that will include lots of db related tasks. There is no document describing the various db tables/columns. I will need to do some BI and I am extremely stressed out by the thought that nobody can help me find my way around the database. There is no  db dictionary available. The db is huge, with ~1000 tables, some of them have ~800 columns.
I already have 5 tasks, and I have no idea where to start. There is 1 contractor that works on optimizing the db, but has no knowledge of the business.
Please throw at me some coping strategies. How can I create this db dictionary? Maybe there are tools that I can use?
Thank you!

Get Visual Studio if you don't have it already.
Get Oracle SQL Developer 4.1.3.
Install the SSDT add-in for visual studio.
Install a local instance of GIT for version control.
Install the MS GIT Plugin for Visual Studio.
Create a SQL Server Database project in Visual Studio for your database.
Make all database modifications using the SQL DB Project going forward.
Use the Datamodeler tool that comes with Oracle SQL Developer to build a relational model of your MS SQL Database so that you can visualize it - yes, this will work, and it's free.

Other Notes:

Don't include security roles or users in your SQL Database Project.
Do include Linked Servers, and Schema definitions.
Getting a handle on this is not going to happen over night, so don't beat yourself up.
It would be a lot better to have a remote git repo with a local copy that you replicate - but a local repo is better than nothing, and I suggested it because it's faster to get up and running.


Normalization: Should I do this in 2NF or 3NF? Consider a database storing the data of invoices, and each invoice takes the following form:

Invoice_Num: 1480
Handled By: John Keaton
Staff Num: 263
Staff Position: Trainee

Item_Num    Item_Desc         Unit_Price   Qty
========    ==============    ==========   ===
 15003      Blue Ballpen         2.00       5
 15012      5ml Gluestick        0.80       3

Assumptions:

Each invoice is handled by 1 and only 1 staff.
Each item in the same invoice is unique.

So, in 1NF, the data can be represented as:

Invoice [ Invoice_Num, Staff_Num, Staff_Name, Staff_Pos, Item_Num, Item_Desc, Unit_Price, Qty ]

where Invoice_Num and Item_Num form the primary key.

Now we know that Invoice_Num -> Staff_Num, and Staff_Num -> Staff_Pos, and Staff_Num -> Staff_Name.

But then when the database is normalized to 2NF, should we move the Staff_Pos and Staff_Name to another table? If we use the transitive property, we have Invoice_Num -> Staff_Pos, and Invoice_Num -> Staff_Name, then we should move them to another table to eliminate partial dependency. However, with the use of transitive property, should these 2 dependencies be considered transitive dependency instead, and hence moving Staff_Pos and Staff_Name to another table should be done in 3NF?

If's a design choice, and you have to consider the trade-off:

If you use 2NF, there will be some duplication, which can potentially be detrimental to integrity - you can change a value in one place and not in the other.

So, if you decide to go with 2NF, you have to be more careful about integrity.

If you use 3NF, there will be no duplication. But you might end up with too many tables, and have hard time keeping track of them.


SSIS OLE DB Source Editor Data Access Mode: "SQL command" vs "Table or view" In BIDS, I'm running a Data Flow Task & I'm wondering what the difference is behind the scenes between using "Table or view" vs "SQL Command" for the Data Access Mode for an OLE DB Source.
For example, suppose I have a table called mytable with 5 columns (column1, column2, column3, column4, column5) but I'm only interested in column1. I see these 2 options:

I can either use "Table or view" and select "mytable" and then only
check "column 1" from the "Columns" pane.
Or I can use "SQL Command"
and use this query "select column1 from mytable".

When I ran a test, the second option seemed to run much faster but I'd like confirmation of what's going on behind-the-scenes when I use "Table or view" and then only select certain columns.  I'd appreciate any insight anyone can provide.

(In my particular use case, mytable is an offsite Azure Database table and column2 has large amounts of xml within it. My guess is that all of the columns were coming over the wire when I used "Table or view")

I will make a small experiment. i will use SQL profiler to see what is going in the background while using an OLEDB Source in the two cases:

I have a Table named dbo.Table_1 that contains 3 columns (ID,name,department)

I used SQL profiler to Tune the database containing this Table and i used the 2 access mode, below the results:

Table or View - selecting only ID column

The profiler shows that the following command is executed

SELECT * FROM [dbo].[Table_1]

Even if you only select one column, the OLEDB Source reads all data then filters columns after reading them all.

SQL COMMAND

The profiler shows that the following command is executed

SELECT [ID] FROM [dbo].[Table_1]

Recently I published an article that contains more details, you can check it on the following link:

SQLShack - SSIS OLE DB Source: SQL Command vs Table or View


Changing two publications at the same time causes deadlock in distribution database I have transactional replication setup.  Let's say three databases on server A replicate to corresponding three databases on Server B.  The databases are identical (same structure for different clients).  Server B also acts as a distributor.  Everything is working fine.  Now I need to change one of the tables (which is also a publication article).  I need to do it in all databases at the same time.  I follow this process (it is scripted):

Drop article for the table I need to modify from the publication
Make the change to my table (it has to be dropped and re-created)
Add article back
Generate snapshot (it possible to have partial snapshot for just the modified article, see good explanation here)

This is where I run into deadlocks.  The deadlock takes place in the distribution database when this stored procedure is executed: sp_MSdrop_subscription.  I don't call this procedure directly, but apparently it gets executed when I run sp_dropsubscription (see step #1 above).  Once again, I modify all publication databases at the same time and they all use the same distribution database, so this is where the problem lies.  The two conflicting statements are captured by the deadlock trace (1222) and apparently come from inside sp_MSdrop_subscription.

First connection runs:

if not exists (select * from dbo.MSsubscriptions    where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
(publication_id = @publication_id or @publication_id is NULL or @independent_agent = 0 ) and
independent_agent = @independent_agent and
subscriber_id = @subscriber_id and
subscriber_db = @subscriber_db and
subscription_type = @subscription_type)

Second connection runs:

delete from dbo.MSsubscriptions where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
(publication_id = @publication_id or
@publication_id is NULL) and
(@article_id is NULL or
article_id = @article_id) and
Message
((subscriber_id = @subscriber_id and
(subscriber_db = @subscriber_db or @subscriber_id = @virtual)) or
-- Delete virtual anonymous subscription
-- if deleting virtual subscription for a anonymous publication
(@subscriber_id = @virtual and subscriber_id = @virtual_anonymous))

and here is the resource list of what's being locked:

resource-list

keylock hobtid=72057594039959552 dbid=34 objectname=distribution.dbo.MSsubscriptions indexname=ucMSsubscriptions id=lock13563b300 mode=X associatedObjectId=72057594039959552

owner-list
owner id=process2d7241498 mode=X
waiter-list
waiter id=process212b58188 mode=S requestType=wait

keylock hobtid=72057594040025088 dbid=34 objectname=distribution.dbo.MSsubscriptions indexname=iMSsubscriptions id=lock17ff3af80 mode=X associatedObjectId=72057594040025088

owner-list
owner id=process212b58188 mode=X
waiter-list
waiter id=process2d7241498 mode=U requestType=wait

I appreciate any pointers on how to avoid this.  The only work-around I can think of right now is to have separate distribution databases per publication database, but I can't believe that replication doesn't support modifying multiple publishers at the same time.

Thank you!

It has been quite a while since I personally used replication, so I am not current on practical problems. A publisher with multiple subscribers is pretty common. However, Microsoft suggests using multiple Distribution databases when you have multiple Publishers.  The following post has direction:

http://technet.microsoft.com/en-us/library/ms183524.aspx

"However, if multiple Publishers use a single Distributor, consider creating a distribution database for each Publisher. Doing so ensures that the data flowing through each distribution database is distinct."

It sounds like a recommendation to me.


What type of database would you suggest for a large scale delivery company? RDBMS or NoSQL? I've been learning the fundamentals of RDBMS and NoSQL systems. To start I thought NoSQL would be the way to go because of how large scale it could become, but understanding the importance of RDBMS ACID properties I'm now thinking this would correct because of regular customer transactions.
Can anyone give me any food for thought? I assume large companies may even use both types for different purposes

Transactional data that is highly structured is a perfect fit for relational databases.  It's not a matter of scale, it's a matter of what fits your data.  NoSQL databases are for unstructured (or highly variable) data.


MySQL least privileges to get database/tables size I'm using centreon_plugins to monitor some mysql databases. As a best practice I create a dedicated user for monitoring and I give it the least possible privileges.

For simple monitoring I usually create a monitor user with only 2 global privileges:

mysql> create user 'monitor'@'%' identified by 'monitorpassword';
mysql> grant SHOW DATABASES, REPLICATION CLIENT on *.* to 'monitor'@'%';
mysql> flush privileges;

Now I'm required to check database size. To access DB size I need that the user monitor can access information_schema.tables, but I cannot give it direct select privileges to that table.

Obviously the root user I'm using has the GRANT privilege.

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> grant SELECT on information_schema.tables to 'monitor'@'%';
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

The access denied is due to the particular use that mysql reserves to the information_schema DB; the access to that DB is limited by the granted access to all other mysql objects.
I could achive the result giving the SELECT privilege on *.* to monitor user, but I don't want to do that because the monitor user is not protected enough (i.e. it's password is stored in cleartext in the monitoring software) and so it must not be able to see application data.

I tried various combination of privileges on different DBs and tables and none of them works.
(ref. https://dev.mysql.com/doc/refman/5.7/en/grant.html)

Did someone had the same need? How did he/she resolved it?

For completeness, the queries executed by the monitoring plugin are:

show variables like 'innodb_file_per_table'
SELECT table_schema, table_name, engine, data_free, data_length+index_length as data_used, (DATA_FREE / (DATA_LENGTH+INDEX_LENGTH)) as TAUX_FRAG FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND engine IN ('InnoDB', 'MyISAM')

Thanks in advance.
Flavio

This is a little confusing but the minimum privileges you need to check a table size is to have the privileges to SELECT that table. So if you want to know the whole DB size you need privileges to SELECT every single table in that DB.

This is why:

There is no way to read a DB size without reading its tables' sizes
The command to read a table size (no querying information_schema) is SHOW TABLE STATUS table_name
SHOW TABLE STATUS derives from SHOW TABLES, so the privileges and rules are similar.
Finally, the official documentation about SHOW TABLE:
https://dev.mysql.com/doc/refman/8.0/en/show-tables.html

  If you have no privileges for a base table or view, it does not show
  up in the output from SHOW TABLES or mysqlshow db_name.

So the minimum access to check DB sizes is pretty invasive because you need to grant SELECT privileges to all tables.


AlwaysOn AG - CPU limitations I’ve been trying to find out if the same CPU limitations database mirroring has apply to Availability Groups. My guess is that the same limitations exists, given that Availability Groups was built on the same technology as Mirroring, so the following would still apply.

Principal server: 1 global thread and 2 threads per each of the mirrored databases.
Mirror server:
– 64 bit architectures: 1 global thread, 2 threads per mirrored databases and one additional thread for each mirrored database for every 4 processor cores.

Is this correct?
Thank you!

This is documented here on BOL: Thread Usage By Availability Groups.  For redundancy purposes, here is the breakdown:

3 to 10 shared threads (but can increase depending on primary replica workload)
(primary replica) 1 log capture thread per primary database
(primary replica) 1 log send thread per secondary database
(secondary replica) 1 redo thread for each secondary database
(secondary replica) during a backup, holds a thread on the primary replica for the duration of the backup

Other points to consider:

There is a shared pool of threads for hadr, and this is the max number of threads except 40 (so hadr worker thread pool could take a significant amount of threads)
Idle threads (about 15 seconds of idle-ness) can be released back to the pool

Other references:

AlwaysON - HADRON Learning Series: Worker Pool Usage for HADRON Enabled Databases

Monitoring SQL Server 2012 AlwaysOn Availability Groups Worker Thread Consumption


Need for backup with Active Dataguard When exploring the features of Oracle Active Dataguard, I found that one of them is block change tracking to allow the standby db to take the load of backups instead of the primary database doing them. Is there a need for taking backups of the primary DB in the first place, when the standby database can be used for recovery directly?

Depends on your requirements.
A standby database is typically a copy of the primary database. Meaning you can not restore and recover a past state of the database. If something goes wrong in the primary database, that change depending on configuration may be immediately replicated in the standby database. This can be handled by using flashback or configuring a standby to lag behind on purpose.
With backup, a database can be recovered to any point of time in the past within the recovery window or redundancy retention policy, which could be days, weeks or even more. It may be required to create a copy of a past state of the production database in a test environment. Some companies may have regulations so they need to preserve past states of the database for a specific amount of time, or they need to be able to restore and recover their database as of in any past point in time in a specific period.
Yes, I have seen customers with Data Guard who did not make database backups, but it is uncommon.


Specify different log file locations with Hallengren scripts I appreciate Ola Hallengren scripts.  Being new to SQL Server, they've made my life so much easier.  Thank you Mr. Hallengren.

I have 5 user databases that I need to back up at separate times, so instead of using USER_DATABASES, I'm providing the database name.  When I created the stored procedures with the script, I specified the Output File Directory as something like G:\Logs\Backups. The log files are there, but it would be great to either have the database name in the logfile name, or pass the logfile path as a parameter.

I'm also logging to dbo.CommandLog. It would be nice to identify the log file that I'm looking for without looking inside.

Is there a way to do this, without changing maintenance_solution.sql?

I don't like the idea of modifying because when an update comes out, those would be gone.  To me, the scripts are quite complicated, as I'm coming up-to-speed with MS SQL and T-SQL.

Any insights are appreciated.

Sherrie

Assuming you have separate jobs to backup each database, your best bet is to edit that manually.

Unfortunately, the way these tokens are implemented, you can only get the database name when it's raised in an alert.

All of the tokens that start with "A-" can't be called the way general information tokens can.

Token   Description
(A-DBN) Database name. If the job is run by an alert, the database name value automatically replaces this token in the job step.
(A-SVR) Server name. If the job is run by an alert, the server name value automatically replaces this token in the job step.
(A-ERR) Error number. If the job is run by an alert, the error number value automatically replaces this token in the job step.
(A-SEV) Error severity. If the job is run by an alert, the error severity value automatically replaces this token in the job step.
(A-MSG) Message text. If the job is run by an alert, the message text value automatically replaces this token in the job step.

If they could, I'm sure they'd end up in Ola's code here:

  BEGIN
    SET @TokenServer = '$' + '(SRVR)'
    SET @TokenJobID = '$' + '(JOBID)'
    SET @TokenStepID = '$' + '(STEPID)'
    SET @TokenDate = '$' + '(STRTDT)'
    SET @TokenTime = '$' + '(STRTTM)'
  END

If you try to tack A-DBN on to the string, the job will fail.

$(ESCAPE_SQUOTE(SQLLOGDIR))\DatabaseBackup_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM))_$(ESCAPE_SQUOTE(A-DBN)).txt

  Message
  Unable to start execution of step 1 (reason: Variable A-DBN not found).  The step failed.

Hope this helps!


Calculate percentage of false records to total records within a aggregate function I'm looking for a solution to calculate a percentage rate of a boolean value:

Model:
Products 1 <-> n Components
Components.essential is a boolean attribute

What I want to calculate is something like:
SELECT products.*, count(comp_a.essential)/count(comp_b.essential) AS essential_percentage
From products
INNER JOIN components AS comp_a ON products.id = comp_a.product_id
INNER JOIN components AS comp_b ON products.id = comp_b.product_id
WHERE comp_a.essential = 1
GROUP BY products.id

Unfortunately this statement calculates way too many components as there are two INNER JOIN on the components model...
Any help on this?
Markus

Note the standard GROUP BY clause.

This gives "true" percent because I assume that's what you mean (not false)

SELECT
  p.*,
  count(CASE WHEN c.essential = 1 THEN 1 END) / count(*) AS essential_percentage
From
  products p
  LEFT OUTER JOIN
  components c ON p.id = c.product_id
GROUP BY
  p.*;

or

  count(nullif(c.essential, 0)) / count(*) AS essential_percentage


How to convert Extended Event action mdmget_TimeStampUTC to datetime? SQL Server Extended Events can include an action of mdmtargetpkg.mdmget_TimeStampUTC. It returns a value such as 132085458320550473. How can that be converted to an actual date?

It doesn't appear to be a valid unix timestamp, even using microseconds.

Maybe it's the TimeStamp is actually RowVersion issue-- but this explicitly has "UTC" in it, implying it's a real time.

@@VERSION:

Microsoft SQL Azure (RTM) - 12.0.2000.8
    Jul  3 2019 10:02:53
    Copyright (C) 2019 Microsoft Corporation

The value 132085458320550473 is the number of ticks since January 1, 1601. Use the below function to convert it to datetime:

CREATE FUNCTION dbo.ConvertTicksToDateTime ( @Ticks bigint )
  RETURNS datetime
AS
BEGIN
    DECLARE @DateTime datetime2 = '16010101';
    SET @DateTime = DATEADD( DAY, @Ticks / 864000000000, @DateTime );
    SET @DateTime = DATEADD( SECOND, ( @Ticks % 864000000000) / 10000000, @DateTime );
    RETURN DATEADD( NANOSECOND, ( @Ticks % 10000000 ) * 100, @DateTime );
END