Make 3 separate tables, or 1 combined table for similar data? When designing a database's structure, if we have multiple tables with similar data, should these tables be combined, with an added ENUM field to denote the table type; or should 3 separate tables be created?

E.g.

VAT (sales tax) rates
National Minimum Wage rates
Currency exchange rates

The structure of the tables would be:

|============= VAT rates =============|
| id | rate |    from    |     to     |
|----|------|------------|------------|
| 1  | 17.5 | 1991-04-01 | 2008-11-30 |
| 2  | 15.0 | 2008-12-01 | 2009-12-31 |
| 3  | 17.5 | 2010-01-01 | 2011-01-03 |
| 4  | 20.0 | 2011-01-04 |    NULL    |
|=====================================|

All 3 tables are frequently referenced by queries.

The full keys to your data are more complex that you indicate.  Sales tax rates can vary by jurisdiction and goods can be categorised as luxury or exempt. Wage rates can vary by industry and public holiday / normal work day.  Exchange rates have to be keyed on the source and destination currencies.
While your values may all be fractional numbers they have different units, which makes them different things.  Tax is "percentage", wages are "dollars per hour", ForEx rates are dimensionless ratios.
Your data scope my be constrained to eliminate these considerations, of course.  Only you can say.
Joe Celko covers this topic in depth here.


Disaster Recovery Options for Sql Server I wanted to see what suggestions you might have for Disaster Recovery Options based on the following criteria.

Currently running SQL 2012 standard edition
We have 18000 databases (same schema across databases)- majority of databases are less than 2gb-- across 64 instances approximately
Recovery needs to happen within 1 hour (Not sure that this is realistic)
We are building a new data center and building dr from the ground up.

What I have looked into is:

Transactional Replication: Too Much Data Not viable
AlwaysOn Availability Groups (Need enterprise) Again too many databases and would have to upgrade all instances
Log Shipping is a viable option and the only one I can come up with that would work right now. Might be a management nightmare but with this many databases probably all options will be a nightmare.

Is there anything else that you can think of that I should look into?

Thanks in advance for any help provided.

During Hurricane Sandy (natural disaster) and recently when moving our data center (managed failover), I was in your situation.

I have implemented Logshipping and was able to efficiently failover during the above scenarios.

The technique that I used was reverse logshipping (swapping roles between primary and secondary) - which allows you to swap the log backups.

Below steps can be scripted with Powershell or TSQL script (I used sqlcmd and xp_cmdshell)

FAILOVER Process:

Kill all user connections on primary server and take a final log backup (WITH NO RECOVERY) putting the primary  into NO RECOVERY state.

a.This means that no users can now connect to the database and the database is ready for failover.
Ship the transaction log to the secondary server  and restore the log WITH RECOVERY bringing the secondary  up.

b.This means that the users can now connect to the secondary server as the database is successful failed-over.

FAILBACK Process:

Kill all user connections on the primary server (original secondary) and take a final log backup (WITH NO RECOVERY) putting the primary (original primary) into NO RECOVERY state.
Ship the transaction log to the primary server (original secondary) and restore the log WITH RECOVERY bringing the primary (original primary) up.

This will utilize less network bandwidth as it just has to ship small T-log backups.

  I do have a question about how log shipping handles Differential backups. Do I have to stop doing differentials for this to work?

A differential or FULL backup does not break LSN chain. ONLY an adhoc log backup will break the LSN chain.


mysql select count on same table slow subquery I've a small table which contains a call log history. Now I want to make a report from this, so I have to find the number of outgoing and incoming calls.

Company has a few extenstions ranging from 100 - 600. So In order to find out if it's an outgoing call, I check if the "dest" is not between 100 - 600 and I get the count using a sub query. (Meaning, two sub queries to get Outgoing and Incoming)

But the problem is, these two sub queries make the query a lot slow, It takes like 3 seconds. (without using these two sub queries it takes only 0.3s).

I read so many questions and answers and found out that the best approach would be to use a join instead of subqueries because of Mysql's low performance on subqueries.

But when I try to join the same table and try to get the same result, it always gives 0 rows. I can't understand how to get the same output using a join.

Here's my Query (with SQ)

SELECT SQL_CALC_FOUND_ROWS DATE(calldate) as clldate,
    clid as caller_id,
    src as source_ext,
    dst as dest_ext,
    (select count(clid) from cdr where src between 100 and 600 and clid = caller_id and DATE(calldate) = clldate) as outbound,
    (select count(clid) from cdr where dst not between 100 and 600 and clid = caller_id and DATE(calldate) = clldate) as inbound,
    sum(duration)/60 as call_duration
FROM cdr
group by DATE(calldate), clid
order by calldate LIMIT 0 , 10;

Here is the explain result:

id  select_typetable    type    possible_keys   key key_len ref rows    Extra
1   PRIMARY cdr ALL NULL    NULL    NULL    NULL    1765    "Using temporary; Using filesort"
3   "DEPENDENT SUBQUERY"    cdr ALL dst NULL    NULL    NULL    1765    "Using where"
2   "DEPENDENT SUBQUERY"    cdr ALL NULL    NULL    NULL    NULL    1765    "Using where"

Here is the show create table:

'CREATE TABLE `cdr` (
  `calldate` datetime NOT NULL DEFAULT ''0000-00-00 00:00:00'',
  `clid` varchar(80) NOT NULL DEFAULT '''',
  `src` varchar(80) NOT NULL DEFAULT '''',
  `dst` varchar(80) NOT NULL DEFAULT '''',
  `dcontext` varchar(80) NOT NULL DEFAULT '''',
  `channel` varchar(80) NOT NULL DEFAULT '''',
  `dstchannel` varchar(80) NOT NULL DEFAULT '''',
  `lastapp` varchar(80) NOT NULL DEFAULT '''',
  `lastdata` varchar(80) NOT NULL DEFAULT '''',
  `duration` int(11) NOT NULL DEFAULT ''0'',
  `billsec` int(11) NOT NULL DEFAULT ''0'',
  `disposition` varchar(45) NOT NULL DEFAULT '''',
  `amaflags` int(11) NOT NULL DEFAULT ''0'',
  `accountcode` varchar(20) NOT NULL DEFAULT '''',
  `uniqueid` varchar(32) NOT NULL DEFAULT '''',
  `userfield` varchar(255) NOT NULL DEFAULT '''',
  `did` varchar(50) NOT NULL DEFAULT '''',
  `recordingfile` varchar(255) NOT NULL DEFAULT '''',
  `cnum` varchar(40) NOT NULL DEFAULT '''',
  `cnam` varchar(40) NOT NULL DEFAULT '''',
  `outbound_cnum` varchar(40) NOT NULL DEFAULT '''',
  `outbound_cnam` varchar(40) NOT NULL DEFAULT '''',
  `dst_cnam` varchar(40) NOT NULL DEFAULT '''',
  KEY `calldate` (`calldate`),
  KEY `dst` (`dst`),
  KEY `accountcode` (`accountcode`),
  KEY `ClldateCid` (`calldate`,`clid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1'

Can anybody guide me on how to get this properly done without using sub queries.

Thank you all in advance :)

What is the datatype of clldate and calldate?  If they are both DATE, get rid of the function calls (DATE()) around them; it prevents use of indexes.

If wither or both is DATETIME (or TIMESTAMP), then we need to rephrase the query to make it more efficient.

Add compound index:
INDEX(caller_id, calldate)

If calldate is DATE, then also add
INDEX(calldate, caller_id)

How big is cdr?  How big will it eventually become?

Does the performance improve much is you get rid of SQL_CALC_FOUND_ROWS?  Consider whether you really need it.

Will you be purging 'old' data?

After you have answered my questions, I will consider whether PARTITIONing is worth doing.


Hard Disk is full of Sql server logs SQL server created a lot of SQLDump.txt files in log folder I'm using SQL server 2017, but I have two databases in My SQL server. I find out my problem isn't about to shrink Ldf file I checked it out. I researched about Error Logs, SQL log, and transaction log. I don't know how to find out which of them are my issues?

  Microsoft SQL Server 2017 (RTM-GDR) (KB4494351) - 14.0.2014.14 (X64)
  Apr  5 2019 09:18:51   Copyright (C) 2017 Microsoft Corporation
  Enterprise Edition (64-bit) on Windows Server 2016 Datacenter 10.0
   (Build 14393: ) (Hypervisor)

You have something seriously wrong going inside the SQL Server and that is why it is producing so many dumps and if you look closely dumps file SQLDumpxxx are getting dumped every minute of so this may be due to bug in SQL Server or sql server facing some exception because of queries running. I would start by looking whether the SQL Server is patched to latest SP and CU or not. If you run select @@version and add the output in the question I can tell you the SP level. You can also find SP/CU information about SQL Server 2017 on SQLServerbuild.blogspot.com.

Similar question discussed on This Thread

MS engineers are the best person to analyze these dumps and tell you what is causing this but before opening case with MS please make sure the SQL Server instance is patched to latest SP and CU so that you rule out all the possibility of bug. If you go to them without patched SQL Server they are most likely to ask you to update to latest SP.

Some links which show how to analyze dumps

SQL Server Diagnostics (Preview)

Looking deeper into SQL Server using Minidumps

Steps to Catch a Simple “Crash Dump” of a Crashing Process

Extracting Information from Crash and Hang Dumps

The first 2 links should be good if you want to try it yourself but my suggestion would be to make sure latest SP and CU is there and if that does not solves the issue raise case with MS.

  Microsoft SQL Server 2017 (RTM-GDR) (KB4494351) - 14.0.2014.14 (X64)

You can see the version is RTM you need to apply SQL Server 2017 CU15 and see if that resolves the issue


After Update Mysql 8.0.13 to 8.0.15 MySql Server Fails with error code = 1265 After Upgrading from MySql 8.0.13 (compiled from sourcecode) to MySql 8.0.15, the MySql Server can not start.

Log:

2019-02-12T10:09:52.275336Z 1 [ERROR] [MY-013178] [Server] Execution of server-side SQL statement 'INSERT INTO routines SELECT * FROM mysql.routines' failed with error code = 1265, error message = 'Daten abgeschnitten für Feld 'sql_mode' in Zeile 7'.
2019-02-12T10:09:52.302630Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2019-02-12T10:09:52.302856Z 0 [ERROR] [MY-010119] [Server] Aborting

"Daten abgeschnitten für Feld" means "Data truncated for field" - its a german version ...

table checker does not find any errors - how can I solve this problem ?

yours sincerely
Robert

I found the solution myself.
On the old mysql 8.0.13 server I corrected the field SQL_MODE of one of the procedures, what happened to be too long.

SELECT * FROM INFORMATION_SCHEMA.ROUTINES;

I found some (old) stored procedures I did not need anymore, and the entries in field SQL_MODE of one of that stored procedures were too long.
After deleting that stored procedure, starting the new 8.0.15 server and "mysql_upgrade" everything is fine, the server can now start without any errors.


Hourly Transaction Log files size not consistent - is this Differential backup file? I am new to DBA task here.
I am trying to restore database with transactional logs (in SQB file format)  that our company get 24 times each hour daily thru FTP site, and then I have to convert them to BAK file format using "RedGate's SQL Backup" application.
Then, I am planning to restore each transactional log with "NoRecovery" as we would have to restore each hour.
I found there is a pattern of file number "..04" which is really big compared to other hourly transactional logs.
I did not get specific instruction regards to this file "..04" from the vendor's documentation who provides data.

I am curious whether I should treat this particular file as a regular transactional log or is this like "differential backup file" that I have treat it differently?

It's more likely that the log backup occurs after some processes that generate a lot of log records, like index maintenance or any ETL-ish tasks.
You may find some benefit in increasing the frequency of your log backups to every 5-15 minutes so that internal truncation occurs more frequently, since that will likely make the backup size smaller.


pg_dump and pg_dumpall tools backup create zero size backup file from linux putty terminal I have installed postgres database version 9.6, 10 on OS Centos 7 64 bit. I'm trying to take a backup with this command:

-bash-4.2$ pg_dump -h localhost -Fc backupdb > /var/lib/pgsql/niger_pgbackup/nigerpg_bkp.sql

but the problem is the backup file size is zero. I have tried many times. Kindly help  where I have gone wrong.

-bash-4.2$ pg_dump -h localhost -Fc backupdb > /var/lib/pgsql/niger_pgbackup/nig erpg_bkp.sql
-bash-4.2$ cd niger_pgbackup/
-bash-4.2$ ls -ltr
total 0
-rwxrwxrwx 1 postgres postgres 0 Oct 7 23:38 nigerpg_bkp.sql

just after being a superuser of the shell, try with supplying superuser postgres as:

    pg_dumpall -f filename.sql -U postgres

or

    pg_dumpall -U postgres > filename.sql

you do not need $bash-rc, just be superuser and run the commands, more info is here


PostgreSql function slows down view I am rewriting Sybase view to postgresql and encountered such problem.
Is there any way to improve performance of view with functions inside?
For example we have complex function

create function func1(pid integer)
returns char(20)
as $$
begin
... do stuf
return 'aaaa';
end; $$ language plpgsql;

And create view with function

create view view_client
as
select code, name, func1(pid) from client;

Such view performs terrible slow, unless "func1" is removed from it. Postgresql probalby executes function for every row even if column is not used?
count(*) from view with 2M rows takes 2+ minutes, without "func1" it takes ~10sec.

As an answer,

  What exactly does the function do? Can you define it as stable or even better as immutable? – a_horse_with_no_name 12 mins ago

From the docs

  IMMUTABLE / STABLE / VOLATILE These attributes inform the query optimizer
  about the behavior of the function. At most one choice can be
  specified. If none of these appear, VOLATILE is the default
  assumption.

  IMMUTABLE indicates that the function cannot modify the database and
  always returns the same result when given the same argument values;
  that is, it does not do database lookups or otherwise use information
  not directly present in its argument list. If this option is given,
  any call of the function with all-constant arguments can be
  immediately replaced with the function value.
  STABLE indicates that the function cannot modify the database, and
  that within a single table scan it will consistently return the same
  result for the same argument values, but that its result could change
  across SQL statements. This is the appropriate selection for functions
  whose results depend on database lookups, parameter variables (such as
  the current time zone), etc. (It is inappropriate for AFTER triggers
  that wish to query rows modified by the current command.) Also note
  that the current_timestamp family of functions qualify as stable,
  since their values do not change within a transaction.
  VOLATILE indicates that the function value can change even within a
  single table scan, so no optimizations can be made. Relatively few
  database functions are volatile in this sense; some examples are
  random(), currval(), timeofday(). But note that any function that has
  side-effects must be classified volatile, even if its result is quite
  predictable, to prevent calls from being optimized away; an example is
  setval().

  For additional details see Section 36.6

The real question is essentially, can you optimize away f(x)

Over everything (mathematical purity)
Over the same scan
Never (uses IO or system calls)

A somewhat important note is that even in production applications you can sometimes lie and say IMMUTABLE and it's perfectly fine. Just be aware of the consequences.


Disabling TDE on AlwaysOn database Which are the correct steps to disable (not removing) TDE on a AlwaysOn database in an availability group?

I found steps for removing TDE but that involves removing the availability group and disabling the AlwaysOn relationship. I hope this is only for removing at all the TDE configuration.

To disable TDE you must first remove the candidate database from the Availability Group - the group itself however does not need to be dropped.

To do this, the following code is needed:

USE [master];
GO

ALTER DATABASE [YourDatabase] SET HADR OFF;
GO

Once this is complete, you may then disable TDE on the PRIMARY replica:

ALTER DATABASE [YourDatabase] SET ENCRYPTION OFF;

The process of decryption is asynchronous, and can be monitored by looking at the percent_complete column in the sys.dm_database_encryption_keys DMV

One thing to note, is that although the database itself is encrypted, if you aren't careful you can end up with part of the log file still encrypted - this is only a problem if you will be dropping the encryption certificates from the offending servers, or trying to take a backup and restore this database to a server that doesn't hold this encryption certificate.


How does postgres host based authentication work? This is a duplicate of: https://stackoverflow.com/questions/55804806/how-does-postgres-host-based-authentication-work
(I was asked to shift this query to DBA.Stackexchange)
I am installing DSPACE which needs Postgresql.
Please see this link: DSPACE INSTALL
It says:

Then tighten up security a bit by editing pg_hba.conf and adding this line:
host dspace dspace 127.0.0.1 255.255.255.255 md5.

I have read on the internet as to how the above line works. It needs a connection type followed by database name/user name followed by IP address and the authentication scheme.
My query is: Should this not be a local (in place of host) connection since the dspace user is running locally?
Can someone show me step by step as to what happens when a request comes in?
Where are the instructions that the dspace user will submit a request using md5?

A local connection uses a Unix-domain socket; a host connection uses TCP.
The linked page says that

  you need to enable TCP/IP connections (DSpace uses JDBC)

… because JDBC does not have built-in support for Unix-domain sockets.

As long as a connection is allowed only from localhost (as configured in pg_hba.conf) and only to localhost (as configued by listen_addresses), it is as secure as a local connection.

The md5 is just an option that specifies how the password is to be encrypted during the connection setup; this is handled automatically by the JDBC driver. For a local connection, or for a connection over SSL, this does not really matter.


SQLServer - how to move tables between instances? I'm not very familiar with SQLServer and, thus, I inadvertently created a database in a fashion I thought it would work.

In SQL Management Studio, I right clicked the instance and created a xpto database, so all my tables are xpto.tableName instead of dbo.tableName.

Should I expect performance issues?

Also, is there a somewhat safe way to move tables from xpto to dbo?

First of all if your object names are xpto.tablename you probably created a schema not a new database.

Schemas are the containers for objects such as tables.  Databases contain schemas.  So the naming format is databasename.schemaname.tablename.

Assuming that is correct it's very easy to change the schema that owns your tables using ALTER SCHEMA.

ALTER SCHEMA dbo TRANSFER schemaname.tablename

This will transfer TableName into the dbo schema.  Or a little closer to your specific case

ALTER SCHEMA dbo TRANSFER xpto.tablename

If in fact your tables are in the wrong database you can move them by using something like this:

USE correctDBName
SELECT * INTO xpto.dbo.TableName FROM tablename

You'll then want to generate any indexes, constraints, permissions etc and apply them in the correct database.

Edit

Here is a query you can use to construct the commands to transfer all the tables from the xpto schema to the dbo schema.

SELECT 'ALTER SCHEMA dbo TRANSFER xpto.' + quotename(tables.name)
FROM sys.tables
JOIN sys.schemas
    ON sys.tables.schema_id = sys.schemas.schema_id
WHERE sys.schemas.name = 'xpto'


Only show results greater than a certain number when summing two values together I have a query that sums up two values in the same table and then calculates one as a percentage of the other

select account_no,
  100*((sum(COMMISSION/100)) / (SUM(AMOUNT/100)))
from accounts etc

What I want to do is only return results where that value is greater than a certain amount (say 4 in this case). If I add something in the where clause such as:

where 100*((sum(COMMISSION/100)) / (SUM(AMOUNT/100))) > 4

I get the error:

  An aggregate may not appear in the WHERE clause...

I expect there's an easy way round this that I haven't learned yet.  Any ideas?

Try:

HAVING 100*((sum(COMMISSION/100)) / (SUM(AMOUNT/100))) > 4

However you will still probably find that integer math is preventing you from getting the result you want. So this might be better:

HAVING 100*((1.0*sum(COMMISSION/100)) / (1.0*SUM(AMOUNT/100))) > 4


Behavior of Varchar with spaces at the end When I use a Varchar with spaces it ignores the spaces at the end.

ex:

declare @X varchar(50)

This...

set  @X= 'John'

...is the same as...

set @X= 'John           '

It considers these to be equal.  How can I cause the system to recognize these as different?

Everything is just according the ANSI standard:

Trailing blanks explained:

  SQL Server follows the ANSI/ISO SQL-92 specification (Section 8.2,
  , General rules #3) on how to compare strings
  with spaces. The ANSI standard requires padding for the character
  strings used in comparisons so that their lengths match before
  comparing them. The padding directly affects the semantics of WHERE
  and HAVING clause predicates and other Transact-SQL string
  comparisons. For example, Transact-SQL considers the strings 'abc' and
  'abc ' to be equivalent for most comparison operations.

  The only exception to this rule is the LIKE predicate. When the right
  side of a LIKE predicate expression features a value with a trailing
  space, SQL Server does not pad the two values to the same length
  before the comparison occurs. Because the purpose of the LIKE
  predicate, by definition, is to facilitate pattern searches rather
  than simple string equality tests, this does not violate the section
  of the ANSI SQL-92 specification mentioned earlier.

Here's a well known example of all the cases mentioned above:

DECLARE @a VARCHAR(10)
DECLARE @b varchar(10)

SET @a = '1'
SET @b = '1 ' --with trailing blank

SELECT 1
WHERE
    @a = @b
AND @a NOT LIKE @b
AND @b LIKE @a

Here's some more detail about trailing blanks and the LIKE clause.

BUT if you want to differ them - you may decide to use DATALENGTH function instead of LEN, because

SELECT 1 WHERE LEN('John ') = LEN('John')

will put you 1 instead of

SELECT 1 WHERE DATALENGTH('John ') = DATALENGTH('John')

The solution is

to use DATALENGTH function to differ between strings
to cast the string into NVARCHAR type - may be better declare this type to parameter of SP


dropping a subscription in the subscriber first, how to fix it? I was looking for dropping a subscription, however, I made the mistake of dropping it in the subscribe, without dropping it at the publisher first, ignoring the warning from the picture below.

  TITLE: Microsoft SQL Server Management Studio

  This action will remove the selected subscription information, but not
  the previously replicated data, from database 'MY_DATABASE_Sub'.
  Removing the subscription information should be done only if the
  subscription has been deleted at the Publisher, MY_PUBLISHER, or if the
  subscription is otherwise defunct. Removing an active subscription
  will result in errors at the Publisher.

  Are you sure you want to remove the subscription information?

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.0.17119.0&EvtSrc=Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Replication.ReplicationMenuItem&EvtID=CleanUpPushSubscriberConfirmation&LinkId=20476

  ------------------------------ BUTTONS:

  &Yes &No

that is causing me the error below (in yellow), basically the subscription is showing up in the replication monitor, but it should not be there at all.

Question is:
How can I fix this error? Basically get rid of the ghost subscription showing up in the replication monitor and causing it become red flagged?

Always drop a subscription in the publisher first to avoid this error.

But if you happen to drop the subscription in the subscriber and get the error in the question above,
the way I sorted it out was to go to the publisher server and publisher database and run the script below, that generates a drop subscription command for each article still pointing to the wrong subscription.

this is the script to generate the script to run:

-- check the server and database
-- these should be the publisher server and publisher database

SELECT @@SERVERNAME
-- CTSTGDB1

SELECT DB_NAME()
-- APIASTG_Repl_Pub

-- go to the publication database
USE APIASTG_Repl_Pub
GO

--check how things are (works for before and after)
sp_helppublication

sp_helpsubscription

--script to generate the drop of all articles in the BANDIT subscription
USE APIASTG_Repl_Pub
GO

select
db_name() PublisherDB
, sp.name as PublisherName
, sa.name as TableName
, UPPER(srv.srvname) as SubscriberServerName
, s.dest_db
,SQL_='exec sp_dropsubscription ' + CHAR(13) +
      '     @publication=' + '''' + db_name() + '''' +  CHAR(13) +
      '    ,@article='  + '''' + SA.NAME + '''' +  CHAR(13) +
      '    ,@subscriber=' + '''' + UPPER(srv.srvname) + '''' +  CHAR(13) +
      '    ,@destination_db=' + '''' + s.dest_db + ''''
            +  CHAR(13) + CHAR(10)

from dbo.syspublications sp
join dbo.sysarticles sa on sp.pubid = sa.pubid
join dbo.syssubscriptions s on sa.artid = s.artid
join master.dbo.sysservers srv on s.srvid = srv.srvid
where s.dest_db = 'MY_WRONG_SUBSCRIPTION'

-- get the _SQL generated above and open a new query,
-- on the same server, same database, run it
-- problem should be solved!!!

Below is an example - a partial list of the script generated after running the script above.

exec sp_dropsubscription       @publication='MY_WRONG_SUBSCRIPTION'     ,@article='repl_Airport'     ,@subscriber='MY_SUBSCRIPTION_SERVER'     ,@destination_db='MY_WRONG_SUBSCRIPTION'
exec sp_dropsubscription       @publication='MY_WRONG_SUBSCRIPTION'     ,@article='repl_ArrivalCalendar'     ,@subscriber='MY_SUBSCRIPTION_SERVER'     ,@destination_db='MY_WRONG_SUBSCRIPTION'
exec sp_dropsubscription       @publication='MY_WRONG_SUBSCRIPTION'     ,@article='repl_Transportation'     ,@subscriber='MY_SUBSCRIPTION_SERVER'     ,@destination_db='MY_WRONG_SUBSCRIPTION'
exec sp_dropsubscription       @publication='MY_WRONG_SUBSCRIPTION'     ,@article='repl_Transportation_Details'     ,@subscriber='MY_SUBSCRIPTION_SERVER'     ,@destination_db='MY_WRONG_SUBSCRIPTION'

The script generated should be run in the publisher (server and database)

this is the picture from the replication monitor:
note on the right hand side before there were 2 lines, one with a red error cross on it, that was the subscription we wanted to delete and now it has been deleted:

there are still errors on this replication monitor, but they are unrelated to this subscriptions as they are on a different server.

I would like to include a link to the question below because it might be useful when you have problems deleting a publication

SQL Agent still attempting replication for deleted publication


Why was default of innodb_file_per_table changed? Is there a disadvantage in using file per table storage? I wonder why the default of the value innodb_file_per_table was changed with MySQL 5.7.

Documentation shows that the default value changed (to "OFF"), so there must be a reason:
https://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_file_per_table

Reference: Is innodb_file_per_table advisable?

The default value has been ON, since 5.6.6.

You're looking at the 5.5 documentation, which only refers to the defaults in 5.5 -- not 5.7.  In version 5.5.7, a decision was made to revert the defaults to match 5.1 in an effort to avoid a replication incompatibility.

  the default values for innodb_file_per_table and innodb_file_format are reverted to the MySQL 5.1 default values—that is, OFF and Antelope, respectively. (Bug #56318, Bug #11763590)

  https://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-7.html

In 5.6.6 (before 5.6 GA) the default value changed back to ON.

  The motivation for these changes is to provide better out-of-box performance and to reduce the need for database administrators to change settings manually.

  https://dev.mysql.com/doc/relnotes/mysql/5.6/en/news-5-6-6.html

In 5.7, the default is still ON.


How do you set the pagesize, codeset and table space when creating a database in DB2? I want to create a database in DB2.

I found some information on  this page

I want to use only some options which are explained on the page:

PAGESIZE
CODESET
TABLESPACE

Then, I want to add/configure the buffer pools.

How should the create SQL look ?

CRAETE DATABASE myDatabase
USING CODESET UTF-8 TERRITORY US
COLLATE USING SYSTEM PAGESIZE 8192

CATALOG TABLESPACE 16K  -- Is the 16K ok?
USER TABLESPACE 16K  -- Is the 16K ok?
TEMPORARY TABLESPACE 16K;  -- Is the 16K ok?

To answer your question, yes you would specify the codeset, territory, collation, and pagesize like you do above. (And as a note, your link is off, you probably mean something like this which is the page for DB2 LUW 9.7 as an example.). By what you are setting up, you are specifying a 8K default pagesize. If you wanted a 16K default pagesize you would need to specify that.

And yes, 16K tablespaces are fine; it just depends on what you are doing.

But I will note that you can't catalog tablespaces. I think you meant to say that you want the CREATE TABLESPACE statement. And yes, if you do not specify a pagesize in your create tablespace statement, then it takes the default as specified by your CREATE DATABASE command.

Personally, I do not think there is anything wrong with specifying exactly what options you want with any statement, even if they are the defaults. For the following two reasons:

You know exactly what settings you are choosing to use and (hopefully) why.
In case IBM ever changes the default behavior, you are not left wondering why your scripts broke. You can continue to use the settings you choose and migrate to IBM's new defaults when you choose to (again, by explicit settings).


In stored procedure %Rowtype is not working while deploying CREATE OR REPLACE PROCEDURE CAS.ROWTYPECHECK ( P_APPLICATION_REC  TAPPLICATION%ROWTYPE )
IS
BEGIN
  INSERT INTO "CAS"."VAPPLICATION"
    VALUES P_APPLICATION_REC;
END;

But I'm getting one error while deploying.

Deploy CAS.ROWTYPECHECK

  Running
  CAS.ROWTYPECHECK - Deploy started.
  Create stored procedure returns SQLCODE: -104, SQLSTATE: 42601.
  CAS.ROWTYPECHECK: 1: An unexpected token "%" was found following "ON_REC
  TAPPLICATION".  Expected tokens may include:  ",".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.16.53
  An unexpected token "%" was found following "ON_REC  TAPPLICATION".  Expected tokens may include:  ",".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.16.53
  CAS.ROWTYPECHECK - Deploy failed.
  CAS.ROWTYPECHECK - Roll back completed successfully.

DBMS is DB2 V9.1.

I tried using:

CREATE OR REPLACE PROCEDURE <Sechma>.<ProcedureName>
  (<Record> <table/VIEW>%ROWTYPE)

Db2 for LUW 9.1 did not have the Oracle compatibility feature, which was added since v9.7, so you cannot use the PL/SQL dialect. Moreover, the CREATE OR REPLACE syntax or row types were not supported either.

If you wish to use these features, upgrade to a supported version of Db2.


Why is it not possible to create indexes on temp tables in SNAPSHOT isolation? When I try to create indexes on a temporary table in SQL Server when using the SNAPSHOT transaction isolation, I get this error:

  Transaction failed because this DDL statement is not allowed inside a snapshot isolation transaction. Since metadata is not versioned, a metadata change can lead to inconsistency if mixed within snapshot isolation.

Why is it not allowed to create indexes on temporary tables in SQL Server when using snapshots?

I don't understand this, if I'm allowed to create temporary tables, why am I not allowed to add indexes to them?

In modern versions of SQL Server (2014+) you can create the indexes when you create the table, e.g.:
create table #t(id int primary key, a int, index ix_a nonclustered(a))

Also you can create the temp table before the snapshot transaction starts.
Almost all DDL is prohibited within a SNAPSHOT transaction.  ALTER TABLE and TRUNCATE TABLE are obviously not allowable.  CREATE TABLE is whitelisted.  CREATE INDEX could be whitelisted, but simply isn't.


Backup and restore Transparent Data Encrypted database Doing my first backup and restore of someone else's sql server database. I have already done a successful backup and restore of one of the databases, but the other two are locked behind Transparent Data Encryption.

Is there a simple way of finding the certificates' files and just copy/pasting them to the new server's filepath and then restore? What's the proper direction for restoration from backup here?

If I do:

select * FROM [master].[sys].[certificates]

I get a bunch of rows that have ## before them. Are these significant? There's only one cert from that output that doesnt have ## and I'm not sure if that applied to a db that I'm not interested in...

Is there a simple way of finding the certificates' files

On source server:

declare
    @SourceDb sysname = 'MyDb',
    @Thumbprint varbinary(20),
    @Cert sysname,
    @BackPath nvarchar(260) = 'C:\temp\Cert.bak',
    @KeyPath nvarchar(260) = 'C:\temp\Cert.key',
    @Pwd sysname = '&DoN0tUseTh1$',
    @SQL nvarchar(max)

select @Thumbprint = encryptor_thumbprint
from sys.dm_database_encryption_keys
where database_id = db_id(@SourceDb)

select @Cert = name from master.sys.certificates
where thumbprint = @Thumbprint

select @Cert '@Cert'

set @sql = 'BACKUP CERTIFICATE ' + quotename(@Cert) + ' TO FILE = '''+@BackPath+'''
WITH PRIVATE KEY (
FILE = '''+@KeyPath+''',
ENCRYPTION BY PASSWORD = '''+@Pwd+''');'
exec(@sql)

  and then restore

On destination server:

declare
    @DestDb sysname = 'MyDb',
    @Cert sysname = 'MyTDECert',
    @BackPath nvarchar(260) = 'C:\temp\Cert.bak',
    @KeyPath nvarchar(260) = 'C:\temp\Cert.key',
    @Pwd sysname = '&DoN0tUseTh1$',
    @SQL nvarchar(max)
set @sql = 'USE master;
    CREATE MASTER KEY ENCRYPTION BY PASSWORD  = '''+@Pwd+''';'
exec(@sql)
if @@error<>0 print
'You already have a DMK - do not drop it.
Instead, BACKUP MASTER KEY TO FILE...
and secure its backup.
Also BACKUP SERVICE MASTER KEY TO FILE...
and secure its backup.'

set @sql = 'USE master;
CREATE CERTIFICATE '+quotename(@Cert)+'
FROM FILE = '''+@BackPath+'''
WITH PRIVATE KEY
(FILE = '''+@KeyPath+''',
DECRYPTION BY PASSWORD = '''+@Pwd+''');'
exec(@sql)

  What's the proper direction for restoration from backup here?

Now restore the database.
After securing the keys and backups off of the 2 servers: Permanently shift+delete them (which in above examples were saved or copied to C:\temp).

  Are these significant? There's only one cert from that output that doesnt have ## and I'm not sure if that applied to a db that I'm not interested in...

They are significant - leave them be :). A cert's thumbprint is used (in above code on the source server) to find the cert which encrypted the db. More than likely it is the cert without the double hashes in the name, but checking  sys.dm_database_encryption_keys is better than guessing :).


Why does pre-sorted index not help me in window function? I am trying to speed up this query. Currently it takes ~6s.
It queries a TEMP TABLE providers_tam_with_offices_tmp.
select *,
  %(max_providers_to_target_per_territory)s as max_providers,
  case
    when call_point_rank = 1 then
        rank()
        over (partition by team_id, territory_name, call_point_rank order by office_tam desc, account_id, address_key, tam desc, id)
    else NULL
    end as cum_provider_count
from
(
  select *,
    -- We order by `account_id, address_key`, and then by `id` as a tie breaker for
    -- deterministic results.
      rank()
      over (partition by team_id, territory_name, id order by office_tam desc, account_id, address_key,
        tam desc, id ) as call_point_rank
  from
    providers_tam_with_offices_tmp
) a

As an experiment, I tried adding this index to providers_tam_with_offices_tmp.
create index providers_tam_with_offices_tmp_idx on providers_tam_with_offices_tmp
(team_id, territory_name, id, office_tam desc, account_id, address_key,
        tam desc, id );

But the query plan shows that this doesn't eliminate the need to sort the table first before calculating the first window function:

While the utility of this might be questionable, I am interested in the general case of why the optimiser wouldn't spot that there's already an index with exactly the sorting required.
Or have I misunderstood how window functions required the data to be organised?

You haven't shown that it doesn't spot the index, only that it doesn't use it.  If you set enable_sort = off; then what happens?
If the table is not well vacuumed and the correlation of team_id with the physical row ordering is low, then reading the index in order will mean hopping all over the table to check that each row from the index is actually visible, leading to the expectation of a lot of costly random IO.  This will be slower (or is expected to be slower) than a nice sequential scan followed by a sequential-IO-optimized sort.


First date in a week, not exceed from the month, start from specific day I want to get first date of week from date, but not exceed from first date of month. This is the query i've tried :

SET @date:='2014-10-03';
SELECT
IF(DATE_ADD(@date, INTERVAL(1-DAYOFWEEK(@date)) DAY)<DATE_FORMAT(@date ,'%Y-%m-01'),
DATE_FORMAT(@date ,'%Y-%m-01'),
DATE_ADD(@date, INTERVAL(1-DAYOFWEEK(@date)) DAY)) AS result

The result : 2014-10-01, because in DATE_ADD(@date, INTERVAL(1-DAYOFWEEK(@date)) DAY) , function DAYOFWEEK() start day of week  is from sunday by default.

What can i add into my query so i can set the start day of week ?

for eg. (Desired) result date :

if i set the start day of week is Thursday then result should be 2014-10-02
if i set the start day of week is Tuesday then result should be 2014-10-01

I hope to understand your question. You want translate a date to a customizable first day of week. I call it start_day_of_week. So if your start_day_of_week is greater than day_of_weeek you must subtract the difference otherwise you must first subtract seven day (i.e. translate to previous week) and then add the difference. For example:

if your date is Fri (day of week 6) and you want to start at Thu (day of week 5) you subtract 1 day
if your date is Fri (day of week 6) and you want to start at Sat (day of week 7) you must first subtract 7 day i.e. go to the previous week and then add 1 day.

I think it better to write a mysql function that takes as parameters a day, a customizable start_day_of_week and returns a date translated.

To begin, I write some test. I use a table with three columns. One for input date, and input start_day_of_week, and a column with the result date. So the date '2014-10-23' translates to date '2014-10-19' when the start_day_of_week is Sunday (1), but it translates to  '2014-10-17' when the start day is Friday (7).

use test;
-- (1 = Sunday, 2 = Monday, …, 7 = Saturday).
drop table if exists fixture;
create temporary table fixture (input date, result date, day_of_week tinyint);
insert into fixture values
('2014-10-01', '2014-10-01', 1),
('2014-10-03', '2014-10-01', 1),
('2014-10-09', '2014-10-05', 1),
('2014-10-16', '2014-10-12', 1),
('2014-10-23', '2014-10-19', 1),

('2014-10-01', '2014-10-01', 2),
('2014-10-03', '2014-10-01', 2),
('2014-10-09', '2014-10-06', 2),
('2014-10-16', '2014-10-13', 2),
('2014-10-23', '2014-10-20', 2),

('2014-10-01', '2014-10-01', 6),
('2014-10-03', '2014-10-03', 6),
('2014-10-09', '2014-10-03', 6),
('2014-10-16', '2014-10-10', 6),
('2014-10-23', '2014-10-17', 6);

The function is:

drop function if exists mydatef;
delimiter $$
create function mydatef (d date, my_start_day_of_week int) returns date deterministic
begin
  set @v := IF(my_start_day_of_week - DAYOFWEEK(d) <= 0,
               DATE_ADD(d, INTERVAL(my_start_day_of_week - DAYOFWEEK(d)) DAY),
               DATE_ADD(d, INTERVAL(my_start_day_of_week - DAYOFWEEK(d)  - 7) DAY));
  return
    IF(@v < DATE_FORMAT(d ,'%Y-%m-01'), DATE_FORMAT(d ,'%Y-%m-01'), @v);
END;
$$
delimiter ;

So to test the function I write a simple query:

mysql> select input, day_of_week, mydatef(input, day_of_week),
 if(result = mydatef(input, day_of_week), 'OK', 'ERR' ) as result from fixture;
+------------+-------------+-----------------------------+--------+
| input      | day_of_week | mydatef(input, day_of_week) | result |
+------------+-------------+-----------------------------+--------+
| 2014-10-01 |           1 | 2014-10-01                  | OK     |
| 2014-10-03 |           1 | 2014-10-01                  | OK     |
| 2014-10-09 |           1 | 2014-10-05                  | OK     |
| 2014-10-16 |           1 | 2014-10-12                  | OK     |
| 2014-10-23 |           1 | 2014-10-19                  | OK     |
| 2014-10-01 |           2 | 2014-10-01                  | OK     |
| 2014-10-03 |           2 | 2014-10-01                  | OK     |
| 2014-10-09 |           2 | 2014-10-06                  | OK     |
| 2014-10-16 |           2 | 2014-10-13                  | OK     |
| 2014-10-23 |           2 | 2014-10-20                  | OK     |
| 2014-10-01 |           6 | 2014-10-01                  | OK     |
| 2014-10-03 |           6 | 2014-10-03                  | OK     |
| 2014-10-09 |           6 | 2014-10-03                  | OK     |
| 2014-10-16 |           6 | 2014-10-10                  | OK     |
| 2014-10-23 |           6 | 2014-10-17                  | OK     |
+------------+-------------+-----------------------------+--------+
15 rows in set (0.00 sec)

So to respond you answer

SET @date:='2014-10-03';
-- 5 is Thursday
mysql> select mydatef(@date, 5);
+-------------------+
| mydatef(@date, 5) |
+-------------------+
| 2014-10-02        |
+-------------------+
1 row in set (0.00 sec)

-- 2 is Tuesday
mysql> select mydatef(@date, 2);
+-------------------+
| mydatef(@date, 2) |
+-------------------+
| 2014-10-01        |
+-------------------+
1 row in set (0.00 sec)


Schema-less data in a database? I'm wondering if there's any database technology where its structure or schema does not need to be defined upfront? For example it may start off with just one column or row and the user will keep adding more columns and rows to the table on the fly, when they see it necessary.

If you write your queries properly and avoid SELECT * then any relational database will allow the addition of further columns or tables without requiring adjustment to the application.  You do, of course, have to declare each column to the DBMS before referencing it in SQL.

I find the claim that NoSQL is "schema-less" to be slightly misleading.  Applications using NoSQL persistance do, indeed, have a schema.  The difference is that the schema is held in application code and it is the responsibility of every programmer who touches the code throughout the application's life to enforce that schema.  With relational databases the data structure is declared to the service and it then takes the responsibility for enforcing those rules for ever after.  The real flexibility of NoSQL is that two rows within the same "bucket" (the definition of which various depending on your DBMS) can have different structure.


MySQL Create View WITH CTE I have successfully created a VIEW in SQL Server as follows. But when I try the same in MySql, Error coming.

SQL SERVER Command:

CREATE VIEW [dbo].[vw_PurchParent] as

WITH cte AS
 (
  SELECT a._Id, a._parentId, a._name, a._IsLedger
  FROM tbl_ChartOfAcc a
  WHERE _Id = 1
  UNION ALL
  SELECT a._Id, a._parentid, a._Name, a._IsLedger
  FROM tbl_ChartOfAcc a JOIN cte c ON a._parentId = c._id
  )
  SELECT *
  FROM cte where _IsLedger = 'Y'
GO

Error in MySQL:

  1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
  near 'cte AS(
      SELECT
          a._Id,
          a._parentId,
          a._name,    ' at line 1

Server version: 5.6.41-84.1 - Percona Server (GPL), Release 84.1, Revision b308619

First of all, MySQL has been supporting CTEs since relatively recently, starting from version 8.0 (as has been mentioned by others). The syntax error you are getting seems to indicate that you are using an older version. Therefore, your database server requires an upgrade1.

Apart from that, there are issues with your CREATE VIEW statement that need to be addressed in order to make it work in MySQL.

Square bracket delimiters.

Use of square brackets to delimit names, like in the statement's first line:

CREATE VIEW [dbo].[vw_PurchParent] as

is specific to Transact-SQL. For MySQL, you need to replace them with either double quotes (") or backticks (`). Or, in this case, you can just omit them altogether, because the name contains only letters and an underscore, which are allowed to be used in names without delimiting.
Schema name.

In SQL Server, databases contain schemas, and schemas contain objects (tables, views, functions, stored procedures etc.). Therefore, the full name of an object consists of three parts:

database.schema.object

When it only has two parts, like in this case ([dbo].[vw_PurchParent]), then it is interpreted as

schema.object

However, MySQL does not support schemas in databases. When an object reference has two parts, it is interpreted as

database.object

Therefore, it is likely that you will want to drop the dbo. part of the view name when adjusting the script for MySQL.
Missing keyword RECURSIVE.

The CTE in this view's definition is a recursive CTE2. MySQL supports recursive CTEs but, unlike SQL Server, it requires that the RECURSIVE keyword be specified when one or more CTEs in the WITH clause are recursive.

Therefore, the WITH line of the definition will need to be rewritten as

WITH RECURSIVE cte AS

The GO keyword.

The GO keyword at the end of your script is specific to the SQL Server platform, although it is not part of the Transact-SQL dialect per se. Rather it is an instruction interpreted by some of SQL Server client tools (SSMS, sqlcmd, the now deprecated osql). It indicates the end of a batch of statements. In this case, it should simply be replaced with a semicolon (;).

Note that it is also a good practice in general to always end a statement with a semicolon, which is the standard statement delimiter in SQL.

1 Alternatively, you could also consider switching to MariaDB. MariaDB is a fork of MySQL, and as such it is compatible with MySQL in many respects, including the SQL dialect aspect. At the same time, though, MariaDB is known to often introduce advanced language features – which includes CTE, window functions and others – before MySQL does. For instance, as already mentioned, CTEs have been introduced to MySQL in version 8.0, which was released one year ago (in April 2018). In contrast, MariaDB has been supporting CTEs for about 2-3 years, as of writing this. (CTEs were first added to version 10.2 in 2016; the version became a stable release in 2017.)

2 It represents a UNION ALL query with a self-reference in the second leg, which is the specific structure that a recursive CTE is required to have.


Entity Framework Coupling with Database I am new to EF and Database Design. I have been studying about using Repository Pattern and Unit of Work a lot, and I have seen a lot of queries made with LINQ.

But I have one question. If, in future, we change the schema of the database, the repository layer is bound to change, because the entities themselves will change. In other words, the repository layer is tightly coupled with the database structure.

But, if we encapsulate most of the possible transactions using a stored procedure in the database itself, and then import them using EF, then change in the database design will not affect the repository layer so much. In other words, the layers would be like this:

DATABASE TABLES <--->  STORED PROCEDURES <---> EF <---> REPOSITORY <---> APPLICATION LOGIC

Why don't we follow this pattern, or is there any pitfall that I am overlooking? Doesn't this loosely couple the application with the database?

I thought that using LINQ also binds the schema with the application, which is my concern. If in future, I want to further normalise my database, it would mean rewriting the whole LINQ queries. It's one more reason I prefer views instead showing raw tables. It's like using properties instead of fields in classes. It adds a layer of encapsulation.

I create my DALs with LINQ. Personally I feel its a lot easier to debug queries using LINQ and I dont enjoy bouncing from MSSQL and VS so in a way It also keep things simple for me. In the past I've come around some very nasty bugs that took a while to point as a (for example) datatype error on a stored procedure, while It would have taken minutes to spot such error on the VS debugger (maybe wouldn't have even compile in the first place).

I do use stored procedures in cases when then query is just to complex and performance is worlds apart from using a LINQ. But this would account for about 10% of any given DAL class, its not really that much.

If you check some other sources you'll notice it mainly comes down to Security and Performance... There are other implications and I dig the argument of the refactoring, but if you refactor your database you still need to recreate the EF mapping and work around those changes on your stored procedures, views, etc.

More on the subject:

Linq2sql vs Stored Procedures

Why I Avoid Stored Procedures (And You Should Too)

In this answer there's an argument for readability that also makes a lot of sense from the software development side of the discussion:


Refresh SQL Server's service account security group membership Consider a SQL Server whose service account is a Managed Service Account in Active Directory. When this MSA is added to a security group, this group membership is not immediately going to take effect if SQL Server is already running as the MSA. (Restarting the SQL Service should have the effect of the MSA "logging on" again, and gaining the access of its new security group membership.)

Is there a way to safely "refresh" the service account's group membership without restarting the SQL Server?

You can use klist purge to do this.  See How do you force an update to a user's group membership in Windows 7? and Viewing and Purging Cached Kerberos Tickets.

I would test this a lot before doing it in production, and avoid doing it in production unless absolutely critical.  If a restart of SQL Server is completely impossible, a safer workaround would be to temporarily assign permissions to the necessary resource directly to the service account.  This would provide the access without a restart, and not require anything out of the ordinary that might cause further issues.  You would simply need to remove the temporary permission after the next service restart.


Run shell script on Oracle 11g I have an oracle database:

Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE   11.2.0.1.0  Production"
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

and I would like to run my script stored on db server.

path: /d01/oracle/scripts/super_skripta.sh

The content of my script (test script):

IME=$(date +"%s")

touch /d01/oracle/scripts/$IME

Can someone explain me (step by step) how can I run this script from a oracle database?

I tried to create an executable program like this:

begin
dbms_scheduler.create_program
(
program_name => 'SUPER_SKRIPTA',
program_type => 'EXECUTABLE',
program_action => '/d01/oracle/scripts/super_skripta.sh',
enabled => TRUE,
comments => 'super skripta'
);
end;
/

and then create a job:

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
            job_name => '"WOLFY"."JOB_SS"',
            program_name => '"WOLFY"."SUPER_SKRIPTA"',
            start_date => NULL,
            repeat_interval => NULL,
            end_date => NULL,
            enabled => FALSE,
            auto_drop => FALSE,
            comments => '',

            job_style => 'REGULAR');

    DBMS_SCHEDULER.SET_ATTRIBUTE(
             name => '"WOLFY"."JOB_SS"',
             attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);

    DBMS_SCHEDULER.enable(
             name => '"WOLFY"."JOB_SS"');
END;

but it does not work...

Environment:

[oracle@o61 ~]$ ls -l /home/oracle/myscript*
-rwxr-xr-x. 1 oracle oinstall 44 Mar 25 17:38 /home/oracle/myscript.sh
[oracle@o61 ~]$ cat /home/oracle/myscript.sh
#!/bin/bash
touch /home/oracle/myscript.out
[oracle@o61 ~]$

[oracle@o61 ~]$ ls -l $ORACLE_HOME/bin/extjob*
-rwsr-x---. 1 root   oinstall 1254574 Nov  1 21:01 /u01/app/oracle/product/11.2.0/dbhome_1/bin/extjob
-rwx------. 1 oracle oinstall 1254574 Nov  1 21:01 /u01/app/oracle/product/11.2.0/dbhome_1/bin/extjobo
-rwsr-x---. 1 root   oinstall 1254292 Oct 19 14:54 /u01/app/oracle/product/11.2.0/dbhome_1/bin/extjobO
-rw-------. 1 oracle oinstall 1254292 Oct 19 14:54 /u01/app/oracle/product/11.2.0/dbhome_1/bin/extjoboO

[oracle@o61 ~]$ grep -v "^#" $ORACLE_HOME/rdbms/admin/externaljob.ora

run_user = oracle
run_group = oinstall

Then create a job that is executed once then dropped:

[oracle@o61 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 25 17:42:13 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

begin
  dbms_scheduler.create_job
  (
    job_name => 'RUN_SCRIPT',
job_type => 'EXECUTABLE',
job_action => '/home/oracle/myscript.sh',
enabled => true,
auto_drop => true
  );
end;
 11  /

PL/SQL procedure successfully completed.

SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@o61 ~]$ ls -l /home/oracle/myscript.*
-rw-r--r--. 1 oracle oinstall  0 Mar 25 17:42 /home/oracle/myscript.out
-rwxr-xr-x. 1 oracle oinstall 44 Mar 25 17:38 /home/oracle/myscript.sh
[oracle@o61 ~]$


MAXDOP = 1, Query Hints and Cost Threshold For Parallelism If an instance has MAXDOP set at 1 and query hints are used to allow specific queries to go parallel, is the Cost Threshold For Parallelism value still used by SQL to decide whether or not to actually go parallel?

I haven’t been able to dig up this specific information although this link suggests that CTFP is ignored if MAXDOP is 1. This makes sense without query hints as no request, regardless of cost, will go parallel when MAXDOP is 1.

Can anyone let me know what the expected behaviour of these two requests will be?

Example 1:

Instance Maxdop: 1
CTFP: 50
Query hint: Maxdop=2
Query cost: 30

Example 2:

Instance Maxdop: 1
CTFP: 50
Query hint: Maxdop=2
Query cost: 70

If an instance has MAXDOP set at 1 and query hints are used to allow specific queries to go parallel, is the Cost Threshold For Parallelism value still used by SQL to decide whether or not to actually go parallel?

Simple answer: yes.

Details

There are a couple of separate things going on here, which it is important to separate:

What is the effective maximum degree of parallelism available to a query?

The contributors to this are (broadly in order of importance):

Resource Governor MAX_DOP setting
Query hint MAXDOP setting
The max degree of parallelism instance configuration option

The details are explained in Server’s “Max Degree of Parallelism” setting, Resource Governor’s MAX_DOP and query hint MAXDOP–which one should SQL Server use? by Jack Li, Senior Escalation Engineer for Microsoft SQL Server Customer Service and Support. The table below is reproduced from that link:

Will a query plan use parallelism?

The SQL Server query optimizer always finds a serial plan first*.

Then, if:

Further optimization is justified; and
The cost of the best serial plan exceeds the cost threshold for parallelism configuration value

...the optimizer will try to find a parallel plan.

Then, if:

A parallel plan is found (i.e. is possible); and
The cost of the parallel plan is less than the best serial plan

...a parallel plan will be produced.

Note: the cost threshold for parallelism only affects whether the optimizer looks for a parallel plan. Once a parallel plan is cached, it will execute using parallelism when it is reused (so long as threads are available) regardless of the CTFP setting.

Examples

For both examples, with instance maxdop 1 and query hint maxdop 2, the effective available DOP is 2. If a parallel plan is chosen, it will use DOP 2.

Example 1

Given CTFP of 50 and a cheapest serial plan found cost of 30, SQL Server will not try to find a parallel plan. A serial plan will be produced.

Example 2

Given CTFP of 50 and a cheapest serial plan found cost of 70, SQL Server will try to find a parallel plan. If this plan (if found) has a cost less than 70 (the serial plan cost) then a parallel plan will be produced.

The end result of query optimization is always a single cached plan: serial or parallel. The optimizer finds only a serial plan in search0 (TP) and search1 (QP) phases.

It may then (as described) re-run search1 with a requirement to produce a parallel plan. A choice is then made between serial and parallel based on best whole plan cost so far. That choice is binding in case optimization moves on to search2 (Full Optimization). Each phase of optimization considers many alternatives, but the output from a stage is always a single best plan, which is either serial or parallel.

I wrote about some of this in Myth: SQL Server Caches a Serial Plan with every Parallel Plan


Get products of category plus all child categories till leaf nodes We have a hierarchy which looks this:

Now the problem is that most products are only connected with 1 category, which is most of the time the lowest level.

I am trying to get ALL products below a given category. So If select one category, I want ALL products from current category and ALL categories below.
So this didn't work:

SELECT "category"."id"
    ,"products"."name"
FROM "products"
INNER JOIN "product_categories" ON "products"."id" = "product_categories"."product_id"
INNER JOIN "categories" ON "product_categories"."category_id" = "categories"."id"
WHERE "categories"."parent_id" = 0

Anyone who can help me out? BTW, we are using PostgreSQL

EDIT:

We are using "PostgreSQL 9.3.5"
And these are the tables for this case, you can see in my above query (and below ERD) which connections I would like to accomplish.

EDIT 2:
This was my query for mysql (here we use taxonomies and not categories):

SELECT T2.id, T2.taxonomy, T2.parent, T2.terms_id, terms.term
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parent FROM term_taxonomies WHERE id = _id) AS parent,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 1, @l := 0) vars,
        term_taxonomies m
    WHERE @r <> 0) T1
JOIN term_taxonomies T2
ON T1._id = T2.id
JOIN terms ON T2.terms_id = terms.id
ORDER BY T1.lvl DESC;

But this only works for mysql

If your data structure is fixed and out of your control, then the best way to do this is to use a recursive common table expression (CTE) like in this question.

If you can change the structure then there are ways to make such queries considerably more efficient. What you have there is often called a "naive tree" - while it allows easy construction and easy navigation up and down by one level at a time, more complex queries including those that need to consider arbitrary depth (like your question) can be a pain.

The common alternatives are:

Store the full path of each node as well as the parent relationship, and index it. This denormalises your data a little but makes "this node down" queries easy because you can search for WHERE tree_path LIKE current_node_path+'%' or to exlude the current node and list just those below it WHERE tree_path LIKE current_node_path+'/%'. Of course you have the extra work of updating the paths when nodes are moved - this is probably best done by trigger or if your application always uses stored procs for updating the tree instead of direct table access it is clear (and perhaps more efficient) to include the logic there.
Store a matrix of relationships, so for the tree 1->2, 1->3, 2->4, 2->5, 5->6 you would store:
n1  n2  depth
2   1      1
3   1      1
4   2      1
4   1      2
5   2      1
5   1      2
6   5      1
6   2      2
6   1      3
Again you have extra data to maintain, but it makes certain varieties of query more efficient and easer to write. For all under node 5 it is WHERE n1=5. Some would store the self relation (i.e. n1=5, n2=5, d=0) too to avoid unions when you want the node and all below - then "5 and below" is WHERE n1=5 and "below 5" is WHERE n1=5 AND depth>0.

There is a good chapter covering this in more detail in this book which I recommend anyone working with database has a read through - it has a good tone for the relatively inexperienced and the experienced will be reminded of important things they've forgotten! If you want to go into real depth there are whole books about handing hierarchical data in SQL and other relational systems. The usual recommendation is "Joe Celko's Trees and Hierarchies in SQL for Smarties".


Oracle 11g Sync Standby Database Currently, I synchronize the standby database from primary database by copying archive logs from primary server to the secondary server. I do this activity daily. I have heard that there are automated process for this are available.

But the problem is that, the primary data gets corrupted, then the same will be replicated to the secondary standby database. This will make the standby database of no use.

Is there any way of automated process so that only the data to be synchronized to the standby database which is not corrupted in the primary database ?

Thanks

No.  This is not possible.  There are various options that might help you recover from this situation or even prevent it from happening in the first place though.

Think about it like this: how do you determine when the "data gets corrupted"?

Imagine that you could decode and scan through the archived redo logs, before they get shipped to the standby.  How would you determine what caused any "corruption" without knowledge of the database structure, contents of the tables and such?

Some options are:

Set a delay on your standby receiving the data.  If a logical (data) corruption is discovered during this window, stop the redo being shipped to the standby so it doesn't get affected.
Set up flashback on either the primary or standby databases for an appropriate period.  If a logical corruption is detected during this time, you may be able to recover using flashback query or flashback database - but it won't be easy if it's discovered a long time after.
Set the database up so that logical corruptions are impossible/difficult to manage without intentionally introducing them.  e.g. referential integrity, correct data types and c.


ER Diagram - View Intergration/Centralised approach Could anyone enlighten me on the difference between a view integration and centralised approach when designing a database, and how the ER diagram can be used to illustrate upon this; can't seem to find any simple explanations on the topic

i understand ER diagrams

Centralized approach

Requirements for each  user view are merged into a single list of requirements for the new database system. A logical data model representing all user views is created during the database design stage. The centralized approach involves collating the requirements for different user views into a single list of requirements. A data model representing all user views is created in the database design stage.

view integration

Requirements for each user view remain as separate lists. Data models representing each user view are created and then merged later during the database design stage. The view integration approach involves leaving the requirements for each user view as separate lists of requirements. We create data models representing each user view. A data model that represents a single user view is called a local logical data model. We then merge the local data models to create a global logical data model representing all user views of the company


Does number of rows affect Update Statistics performance if you are sampling with n rows? For example, I have table A with 80 million rows and table B with 200 million rows.

Assume both tables have 30+ stat tables (all manually generated by SQL Server).
I randomly take the first 10 stat tables from each and run the two tasks separately:

Update Statistics A (stat1, stat2, ...) with sample 5000000 rows
Update Statistics B (stat1, stat2, ...) with sample 5000000 rows

Even though both are scanning 5000000 rows only, the second task is taking much longer to complete. Is this difference in performance solely from the difference in the contents of the tables, or does the number of rows in the table also play a part even if we are limiting the number of rows to scan?

Update statistics is a single threaded task (You can run multiple instances of it in parallell though) so duration of the statistics run will be correlated with the number of records the task has to scan in order to build the statistics and selecting the rows to sample.

Even if you are only sampling a fixed number of rows the population you are sampling from is larger therefore the scan will be more time consuming.

There are some interesting details about effects and use of tempdb in statistics scanning in Erin Stellato's post about statistics but almost all you want to know is contained in this Robert Sheldon's article


TSQL Generic Row number theoretical question To start, We have a pretty complicated system that's been a compilation of over 20 years of code... And the code needs to work with SQL2005 all the way to SQL2014 (can't use the offset Fetch Next)

one of the procedures that I'm trying to write is to be able to take parameters such as an action (add, delete, modify), a source(which is a query), a target (which is a table) and keys (which will be used to join the source and target to do the action)... and this for any source to any table within our application... This being said, I have been able to create the procedure. Everything worked fine until we hit a big source and target (15 million rows).

one update statement was too big and an update statement for every row was too long... So I've managed to get the update to work in chunks of 500K rows in a test environment which performs way better but in my test lab I knew the source and the target so I was using a specific field from the source and Target to make sure I take the next 500K rows.

pseudo-code of my tests look like this.

DECLARE @BatchSize INT = 500000, @ROWCNT INT, @ROWTOTAL INT
WHILE 1 = 1
BEGIN
   UPDATE  TOP (@BatchSize) PRI
                SET {List of columns to update}
                FROM ({Source}) BAT
                JOIN {Target} PRI ON {List of Keys}
                WHERE PRI.F1 <> BAT.F1 -- F1 is updated by the list of columns to update
    IF @@ROWCOUNT < @BatchSize BREAK
END

The problem I'm having is that I want to use similar code but since I don't know what fields exist in the list, I can't use the WHERE PRI.F1 <> BAT.F1 which means that My loop would always pickup the same 500K rows (top 500K).

To counter that problem, I would need to use the ROW_NUMBER function but in my eyes it would double the execution time since I would have to create a temporary table that includes the row_number value and logic that will take the next 500k and so on.

Can anyone think of any other way (using physical addresses or something else) to be able to pick up the next 500K rows without having to create a temp table?

your're input is greatly appreciated.
Thank you
JG

Since the update is produced by dynamic SQL (as was revealed in the comment discussion – A. M.), it would be fairly easy to change (the dynamic part) so it knows which columns are updated and use them in the where clause.
Another route might be to use the OUTPUT clause so you get the list of primary key values of the updated rows in a temp table/variable. Then get the maximum of them and use it the next iteration of the loop with something like: WHERE PRI.pk_column > @previous_max. (All these assuming that all the target tables have a single-column integer primary/unique key.) This could prove more efficient as that column would be indexed (while an arbitrary F1 column might not be).


Getting "invalid authorization specification" when creating linked server I have two databases. Database A is on DBServer\Instance1 and Database B is on DBServer\Instance2. I need to create a view inside Database B that looks into a table in Database A. I was told I need to add DBServer\Instance1 as a linked server to DBServer\Instance2 first. That's what I'm trying to do.

Just for testing, I created a user account named linked_server_acct and gave it all the roles on both servers (Under Security > Logins). I can log in to each instance using SQL Management Studio, so I know for a fact that the login works, but I'm not able to use it for some reason when creating the linked servers. It fails with "invalid authentication specification - 7399'.

I can't think of a reason why an account that is basically a super admin of a server can't authenticate to it. Any help would be appreciated.

DBServer\Instance1 linked server script:

/****** Object:  LinkedServer [DBSERVER\INSTANCE1]    Script Date:

06/17/2015 16:17:39 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'DBSERVER\INSTANCE1', @srvproduct=N'SQL Server'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DBSERVER\INSTANCE1',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DBSERVER\INSTANCE1',@useself=N'True',@locallogin=N'linked_server_acct',@rmtuser=NULL,@rmtpassword=NULL

GO

EXEC master.dbo.sp_serveroption @server=N'DBSERVER\INSTANCE1', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DBSERVER\INSTANCE1', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'DBSERVER\INSTANCE1', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DBSERVER\INSTANCE1', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DBSERVER\INSTANCE1', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DBSERVER\INSTANCE1', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DBSERVER\INSTANCE1', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DBSERVER\INSTANCE1', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'DBSERVER\INSTANCE1', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'DBSERVER\INSTANCE1', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'DBSERVER\INSTANCE1', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'DBSERVER\INSTANCE1', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'DBSERVER\INSTANCE1', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Figured out the problem. Even though the user I was adding to the Linked Server security section had permissions into the target database, the user I was logged into the SQL Server instance at the moment didn't. Since the error message is incredibly unhelpful (it doesn't say which user failed to log in), I discovered this entirely by accident.


What is the safest way to run SSIS on Multiple Named SQL Server Instances? I have an Active/Active SQL Server 2008 R2 cluster. There are two named instances on each node and no default instance.

I need to deploy SSIS packages to the different instances. To do this, I needed to modify the  MsDtsSrvr.ini.xml file to add the instance names so they would be seen by BIDS and SSMS. I put all 4 instance names in that XML file (on both nodes) and restarted SSIS on both nodes. I can now see the folder for each instance when connecting to any instance in SSMS through an Integration Services connection. However, clicking the 2 instance names that are on the other node generates a login failure since they are not active on the current node. Maybe this is just an inconvenience, but am I going to run into any issues with this setup? Or is there a way to do it that makes more sense?

I dont see anything wrong in your setup. To be fair, this is the correct way of setting up SSIS in a clustered environment.

Read up SSIS and clustering: What you should do instead

Also for the login failure, its worth to check  the Windows Server 2008 R2's firewall rules  to allow an inbound rule for either the msdtssrvr.exe executable or the DCOM port SSIS listens on.


Cluster command locking read only queries on replica I noticed that clustering can take a long time and lock the table entirely, even for reads.

Index Organized Tables and incremental clustering would be perfect, but sadly they aren't supported by PostgreSQL.

Now I realize that it also lock the replicas' tables, so apparently there isn't a way to cluster without having a lot of downtime?

Setting rds.logical_replication to 1 on both master and replica instances didn't help.

I would like to cluster four big tables every week for better performance.

A problem with letting queries operate against the table while it is being CLUSTERED (or VACUUM FULL) is that even queries which are select-only from the user's perspective can still write to the table in non-user-visible ways, to set hint bits and clean up after rolled-back transactions, for example.

You could set up two replicas, one which has replay paused just before the table cluster operation starts and so is open for readers during the CLUSTER, and another which is allowed to replay normally.  Then once the CLUSTER is done, everyone switches from the paused replica to the other one, so that the paused one can catch up.  Whether you can tolerate using a replica which has fallen behind by hours is another question, though.

There are projects that will do online clustering of tables, such as pg_reorg and pg_repack. They are not part of the PostgreSQL core, but they are probably worth a look.  I have not used them in production, but mostly because I don't have a production need for them.

Yet another option would be to partition the tables.  Then each partition can be reclustered separately, and you might not have to do clustering at all if the partition constraints accomplish the same thing as clustering does--generating more efficient IO patterns for partial scans.


Cast to date is sargable but is it a good idea? In SQL Server 2008 the date datatype was added.

Casting a datetime column to date is sargable and can use an index on the datetime column.

select *
from T
where cast(DateTimeCol as date) = '20130101';

The other option you have is to use a range instead.

select *
from T
where DateTimeCol >= '20130101' and
      DateTimeCol < '20130102'

Are these queries equally good or should one be preferred over the other?

The mechanism behind the sargability of casting to date is called dynamic seek.

SQL Server calls an internal function GetRangeThroughConvert to get the start and end of the range.

Somewhat surprisingly this is not the same range as your literal values.

Creating a table with a row per page and 1440 rows per day

CREATE TABLE T
  (
     DateTimeCol DATETIME PRIMARY KEY,
     Filler      CHAR(8000) DEFAULT 'X'
  );

WITH Nums(Num)
     AS (SELECT number
         FROM   spt_values
         WHERE  type = 'P'
                AND number BETWEEN 1 AND 1440),
     Dates(Date)
     AS (SELECT {d '2012-12-30'} UNION ALL
         SELECT {d '2012-12-31'} UNION ALL
         SELECT {d '2013-01-01'} UNION ALL
         SELECT {d '2013-01-02'} UNION ALL
         SELECT {d '2013-01-03'})
INSERT INTO T
            (DateTimeCol)
SELECT DISTINCT DATEADD(MINUTE, Num, Date)
FROM   Nums,
       Dates

Then running

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT *
FROM   T
WHERE  DateTimeCol >= '20130101'
       AND DateTimeCol < '20130102'

SELECT *
FROM   T
WHERE  CAST(DateTimeCol AS DATE) = '20130101';

The first query has 1443 reads and the second 2883 so it is reading an entire additional day then discarding it against a residual predicate.

The plan shows the seek predicate is

Seek Keys[1]: Start: DateTimeCol > Scalar Operator([Expr1006]),
               End: DateTimeCol < Scalar Operator([Expr1007])

So instead of >= '20130101' ... < '20130102' it reads > '20121231' ... < '20130102' then discards all the 2012-12-31 rows.

Another disadvantage of relying on it is that the cardinality estimates may not be as accurate as with the traditional range query. This can be seen in an amended version of your SQL Fiddle.

All 100 rows in the table now match the predicate (with datetimes 1 minute apart all on the same day).

The second (range) query correctly estimates that 100 will match and uses a clustered index scan. The CAST( AS DATE) query incorrectly estimates that only one row will match and produces a plan with key lookups.

The statistics aren't ignored completely. If all rows in the table have the same datetime and it matches the predicate (e.g. 20130101 00:00:00 or 20130101 01:00:00) then the plan shows a clustered index scan with an estimated 31.6228 rows.

100 ^ 0.75 = 31.6228

So in that case it appears the estimate is derived from this formula:

  The following table shows the number of conjuncts guessed and the resultant selectivity as a function of input table cardinality of N:

  | Conjuncts | Cardinality | Selectivity |
|-----------|-------------|-------------|
| 1         | N^(3/4)     | N^(-1/4)    |
| 2         | N^(11/16)   | N^(-5/16)   |
| 3         | N^(43/64)   | N^(-21/64)  |
| 4         | N^(171/256) | N^(-85/256) |
| 5         | N^(170/256) | N^(-86/256) |
| 6         | N^(169/256) | N^(-87/256) |
| 7         | N^(168/256) | N^(-88/256) |
| ...       |             |             |
| 175       | N^(0/256)   | N^(-1)      |

If all rows in the table have the same datetime and it doesn't match the predicate (e.g. 20130102 01:00:00) then it falls back to the estimated row count of 1 and the plan with lookups.

For the cases where the table has more than one DISTINCT value the estimated rows seems to be the same as if the query was looking for exactly 20130101 00:00:00.

If the statistics histogram happens to have a step at 2013-01-01 00:00:00.000 then the estimate will be based on the EQ_ROWS (i.e. not taking into account other times on that date). Otherwise if there is no step it looks as though it uses the AVG_RANGE_ROWS from the surrounding steps.

As datetime has a precision of approx 3ms in many systems there will be very few actual duplicate values and this number will be 1.


What happens when changing column type DATETIME to TIMESTAMP in MySQL? I want to convert a column data type from DATETIME to TIMESTAMP in a table with more than one million of lines.

Is it possible? Is it slow? Will I lose the old information or MySQL can convert this automatically? Can I just do an alter table or may I do something different?

Here is a test script:

use test
drop table if exists felipe_table;
create table felipe_table
(
    id int not null auto_increment,
    dt datetime not null,
    name varchar(25),
    primary key (id)
);
insert into felipe_table (dt,name) values
(NOW() + INTERVAL FLOOR(RAND()*10) DAY,'rolando'),
(NOW() + INTERVAL FLOOR(RAND()*100) DAY,'pamela'),
(NOW() + INTERVAL FLOOR(RAND()*1000) DAY,'dominique'),
(NOW() + INTERVAL FLOOR(RAND()*10000) DAY,'diamond');
select * from felipe_table;
SHOW CREATE TABLE felipe_table\G
ALTER TABLE felipe_table MODIFY dt TIMESTAMP;
select * from felipe_table;
SHOW CREATE TABLE felipe_table\G

Here is its execution (using MySQL 5.5.12-log for Windows):

mysql> use test
Database changed
mysql> drop table if exists felipe_table;
Query OK, 0 rows affected (0.03 sec)

mysql> create table felipe_table
    -> (
    ->     id int not null auto_increment,
    ->     dt datetime not null,
    ->     name varchar(25),
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> insert into felipe_table (dt,name) values
    -> (NOW() + INTERVAL FLOOR(RAND()*10) DAY,'rolando'),
    -> (NOW() + INTERVAL FLOOR(RAND()*100) DAY,'pamela'),
    -> (NOW() + INTERVAL FLOOR(RAND()*1000) DAY,'dominique'),
    -> (NOW() + INTERVAL FLOOR(RAND()*10000) DAY,'diamond');
Query OK, 4 rows affected (0.13 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from felipe_table;
+----+---------------------+-----------+
| id | dt                  | name      |
+----+---------------------+-----------+
|  1 | 2013-02-16 09:01:21 | rolando   |
|  2 | 2013-02-09 09:01:21 | pamela    |
|  3 | 2014-09-07 09:01:21 | dominique |
|  4 | 2036-03-07 09:01:21 | diamond   |
+----+---------------------+-----------+
4 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE felipe_table\G
*************************** 1. row ***************************
       Table: felipe_table
Create Table: CREATE TABLE `felipe_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dt` datetime NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> ALTER TABLE felipe_table MODIFY dt TIMESTAMP;
Query OK, 4 rows affected (0.31 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from felipe_table;
+----+---------------------+-----------+
| id | dt                  | name      |
+----+---------------------+-----------+
|  1 | 2013-02-16 09:01:21 | rolando   |
|  2 | 2013-02-09 09:01:21 | pamela    |
|  3 | 2014-09-07 09:01:21 | dominique |
|  4 | 2036-03-07 09:01:21 | diamond   |
+----+---------------------+-----------+
4 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE felipe_table\G
*************************** 1. row ***************************
       Table: felipe_table
Create Table: CREATE TABLE `felipe_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `name` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>

Looks like it works. If you do not trust this small sample, then make a copy of the table and perform this with the copy.

Give it a Try !!!


how to use mysql temporal cache tables I took this example from a book where visitors_today is the cache table from visitor_stored. The idea is to store all the visitors per day (visitors_stored) using a cache table.

Create table visitors_today(
today int unsigned not null default 0,
vcount bigint unsigned not null default 0
)
engine = Innodb;

create table visitors_stored(
v_count bigint unsigned not null default 0,
vcount_date DATE not null primary key
)
engine=MyIsam;

My questions:

Do I need to set up something?
A cache table is it just a regular table, the difference is the data will be delete after update the visitors_stored?, or just because the storage engine it will be faster?

If I understand your question correctly, your examples are just storing the vcount of visitors (visitors_stored) from today.

This means

You'll want a script to reset the visitors_today each day at midnight.
You'll want a trigger on visitors_stored to update the visitors_today table.

Why would you do it? If you regularly wanted to know how many visitors were from today, you would do a

SELECT vcount FROM visitors_today

instead of

SELECT v_count FROM visitors_stored WHERE vcount_date=NOW(); -- Probably wouldn't use NOW, but an actual static date!

It depends on how your server is tuned, but generally if you have an index on the vcount_date column in visitors_stored, it will suffice. If your visitors_stored table was overly large (>100GB say), the cache table might be of use.

If you start to see performance issues with computed columns, a cache column or table might be the way to go.


How to remove dbcreator server role? I was able to add the dbcreator server role to a user of a SQL Server DB, thanks to this nice question/answer: create-database-permission
How can I REMOVE that permission? Is there a stored procedure like sp_addsrvrolemember that works on the contrary and that I can use to drop that permission for the same user for which I granted it?
I tried using sp_droprolemember, but i got a "dbcreator is not a role" error message.
I need to do it programmatically.
Many thanks in advance!
(Sorry if a similar question was already asked, but I was not able to find it)

You can only add logins to server roles, not database users. To remove a login, you use ALTER SERVER ROLE ... DROP MEMBER:
ALTER SERVER ROLE dbcreator DROP MEMBER login_name;

Please stop using the stored procedures; they're all deprecated. For example, from the sp_addsrvrolemember documentation:

This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER SERVER ROLE instead.


Mysqldump from the Amazon RDS I have a Mysql database deployed on Amazon web services RDS. I am running a crontab which runs mysqldump command. The problem is it freezes the rds instance and my website will be down for more then half and hour almost. It will be inaccessible. Is there any way to stop the freeze during the dump and website will be accessible during the dump also.

Have you tried mysqldump using --single-transaction ? In this way it doesn't lock the table for threads doing SQL,DML operations provided no DDL statements should be issued and you will get the backups for conistent states only. This will be efficient  only for innodb tables. Below url might give some insights about the option.

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_single-transaction


SQL join on col1 if present else use col2 There are two tables T1(col1) and T2(col1, col2) which needs to be joined. But T2 may have col1 as null in which case col2 can be used as backup.
What I want is either join on T1.col1 = T2.col1 or T1.col1 = T2.col2 if col1 is NULL in T2.
I have already tried these:
select * from T2 left join T1
on T1.col1 = coalesce(T2.col1, T2.col2)

select * from T2 left join T1
on T1.col1 in (T2.col1, T2.col2)

select * from T2 left join T1
on T1.col1 = T2.col1
or T1.col1 = T2.col2)

Which result in the ETL job never ending.
Additional info:

Redshift DB
No indexes because columnar DB
T2.col1 and T2.col2 will be ditto. T2.col1 comes from upstream, whereas T2.col2 is derived from the ETL. Until upstream quality improves, we'll need fallback on col2.

The problem here is that using a function or or around your join predicate means that the database engine can not use any indexes that you might have defined on the relevant columns for seeking so it is forced to table scan. On a large table this can take a long time. Worse, if this is part of a larger query you might find the scanning must be done multiple times.
If you have useful indexes on T2.col1 and T2.col2 (and T1.col1) then the following is likely to be more efficient, assuming that what you actually want is an INNER JOIN:
-- find matches for col1
SELECT *
FROM T1
JOIN T2 ON T1.col1 = T2.col1
-- join these results together
UNION ALL
-- find matches for col2 except where will have already matched col1
SELECT *
FROM T1
JOIN T2 ON T1.col1 = T2.col2 AND T2.col1 IS NULL

Because each of the UNIONed SELECTs can avoid table scanning this will be more efficient than the single SELECT, assuming appropriate indexes exist for them to use.
If you do want a LEFT JOIN (i.e. you want to include rows from T1 that don't match those in T2 at all) then you need an extra clause for this:
-- find matches for col1
SELECT *
  FROM T1
  JOIN T2 ON T1.col1 = T2.col1
-- join these results together
UNION ALL
-- find matches for col2 except where will have already matched col1
SELECT *
  FROM T1
  JOIN T2 ON T1.col1 = T2.col2
          AND T2.col1 IS NULL -- this will match above and you'll get a duplicate
UNION ALL
-- find rows in T1 where there is no match in T2
SELECT *
  FROM T1
  LEFT JOIN
       T2 ON T1.col1 = T2.col2
 WHERE T2.col1 IS NULL
   AND NOT EXISTS (SELECT * FROM T2 WHERE T2.col2 = t1.col1)

If you were not using SELECT * (i.e. had a fixed column list instead of the wildcard *) then you might find that third clause easier to understand as:
   SELECT [columns-to-project]
  FROM T1
 WHERE NOT EXISTS (SELECT * FROM T2 WHERE T2.col1 = t1.col1)
   AND NOT EXISTS (SELECT * FROM T2 WHERE T2.col2 = t1.col1)

(whether this would be more or less efficient, or exactly the same, depends on your database's query planner and the indexes you have defined).
Note that the first SELECTs assume that you have no rows in T2 with a col2 that matches T1.col1 = T2.col2 and also have a col1 that matches T1.col1 = T2.col1, and that there are none with a valid T2.col2 but a T2.col1 whcih does not match any T1.col1. If such rows do exist then extra work is needed, I've not included that because your current queries would skip those matches just as my examples will.
Note also that any claims of efficiency of the above examples relies on appropriate indexes existing.
For a more concrete answer you will need to edit your question to provide:

The table structure, particularly what indexes are defined.
A more detailed description of your data, particularly with respect to what goes in T2.col1 & T2.col2 and when.
What database system you are using (there might be specific features in your DB that could help with this sort of query and table design), unless you particularly need a database-agnostic answer.


the database system to store any value associated with such external writes temporarily in a special relation in the database The text below is from the book Database System Concepts by Silberschatz.
Why the values associated with external writes need to be stored in a special relation in the database? Can't we behave with these values like other values?

We must be cautious when dealing with observable external writes, such as writes
to a user’s screen, or sending email. Once such a write has occurred, it cannot be
erased, since it may have been seen external to the database system. Most systems
allow such writes to take place only after the transaction has entered the committed
state. One way to implement such a scheme is for the database system to store any value
associated with such external writes temporarily in a special relation in the database,
and to performthe actual writes only after the transaction enters the committed state. If
the system should fail after the transaction has entered the committed state, but before
it could complete the external writes, the database system will carry out the external
writes (using the data in non-volatile storage) when the system is restarted.

Can't we behave with these values like other values?

That's exactly what is suggested in your quote: you store data to be written externally in a (special) relation, because they are special, transient entities.
Suppose you have a workflow system in which a task is assigned to a user by updating its user_id attribute ("internal write"). It also needs to send an email notification (an external write) to that user. These two happen asynchronously, but the external one must always happen after the internal one is persisted. You update the task record as usual (nothing "fake" about it) and create a new outbound_email entity with whatever information is relevant. When the task update is committed, you process outbound_email and delete the transient record.
This works pretty much like a write-ahead log for external actions: you first create a record for the action, then perform that action at an appropriate moment, then discard the log record as it is no longer needed. If the flow is interrupted, you replay the "log" to recover.


preferred order of columns in the key If I have a table like so:

table T ( account_id bigint, event_date datetime ... );

and later make queries like so:

select ...
from T
where account_id = 'ABC'
  and event_date >= '2016-01-01'

Does the order of columns in the index matter, ie:

key (`account_id`, `event_date`)

Or

key (`event_date`, `account_id`)

Thanks!

When building an index for a = constant AND b >= ..., put any and all '=' columns first; end with one 'range': INDEX(a, b).  In your case INDEX(account_id, event_date).

You do need a PRIMARY KEY on your table, but it is unclear whether this is suitable.  The PK must be unique.

For more discussion, see my cookbook. The info is the same for MySQL as for MariaDB.  If you prefer, see MySQL: Building the best INDEX for a given SELECT