Is there a way to avoid having chained/nested “replace()” items when using an ISO 8601 timezone offset value in a generated column? I am using MySQL 5.7 and have a table that has the following columns that are being used to store some data that is source from an Apache common log formatted access log; details extracted from a MySQL schema export:

`timestamp` timestamp NULL DEFAULT NULL,
`offset` varchar(5) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL
`date` date GENERATED ALWAYS AS ((`timestamp` + interval replace(replace(replace(`offset`,'-0','-'),'+0','+'),'00','') hour)) VIRTUAL
`time` time GENERATED ALWAYS AS ((`timestamp` + interval replace(replace(replace(`offset`,'-0','-'),'+0','+'),'00','') hour)) VIRTUAL
`hour` int(2) GENERATED ALWAYS AS (hour((`timestamp` + interval replace(replace(replace(`offset`,'-0','-'),'+0','+'),'00','') hour))) VIRTUAL

As you can see I am storing timestamp (0000-00-00 00:00:00) and offset (+00:00) and then using generated columns to calculate, date (0000-00-00), time (00:00:00) and hour (0) values. And this is working well: I get to store the timestamp as UTC and then—by storing the offset value—I can dynamically get other info in non-UTC form.

But I don’t think the chained/nested REPLACE items are too hot. The goal is to be able to take an ISO 8601 timezone offset value like -0400 or +1000 as part of an interval [some value] hour calculation.

So is there a better way to approach this? I might be able to adjust the way that the timezone offset is stored initially so it is—essentially—exactly what I need for column calculations, but that seems messy and non-intuitive so I would rather use that +/-[for digit] format if possible.

After reading up on CONVERT_TZ, it seems like it would be the cleaner method of handling a case like this since I can cut down three (!!!) REPLACE statements with one like this:

CONVERT_TZ(`timestamp`,'+00:00',REPLACE(`offset`,'00',':00'));

It still seems a bit sloppy—since I am still dealing with having to convert offset values like -0400 to -04:00—but does work and is cleaner/easier to read and understand.

That said, the above method will fail for offset values such as +0530, +1000 and +0000. So instead of that, using the following method—which uses the INSERT() string function where colon (:) insertion is based on an offset value from the right calculated by the length of the string itself—is cleaner and works for a variety of different offsets:

CONVERT_TZ(`timestamp`,'+00:00',INSERT(`offset`,LENGTH(`offset`)-1,0,':'));

And these are the results using that with the following sample timestamps and related offsets:

SELECT CONVERT_TZ('2018-05-28 02:34:58','+00:00',INSERT('+0300',LENGTH('+0300')-1,0,':'));
SELECT CONVERT_TZ('2018-05-28 07:50:12','+00:00',INSERT('+0400',LENGTH('+0400')-1,0,':'));
SELECT CONVERT_TZ('2018-05-28 09:23:34','+00:00',INSERT('+0530',LENGTH('+0530')-1,0,':'));
SELECT CONVERT_TZ('2018-05-28 12:16:56','+00:00',INSERT('+1000',LENGTH('+1000')-1,0,':'));
SELECT CONVERT_TZ('2018-05-28 16:07:17','+00:00',INSERT('-0200',LENGTH('-0200')-1,0,':'));
SELECT CONVERT_TZ('2018-05-28 20:02:05','+00:00',INSERT('-0700',LENGTH('-0700')-1,0,':'));
SELECT CONVERT_TZ('2018-05-28 23:33:03','+00:00',INSERT('-1000',LENGTH('-1000')-1,0,':'));
SELECT CONVERT_TZ('2018-05-28 23:33:03','+00:00',INSERT('-0000',LENGTH('-0000')-1,0,':'));

And here are the results:

2018-05-28 02:34:58 with an offset of +0300 becomes: 2018-05-28 05:34:58
2018-05-28 07:50:12 with an offset of +0400 becomes: 2018-05-28 11:50:12
2018-05-28 09:23:34 with an offset of +0530 becomes: 2018-05-28 14:53:34
2018-05-28 12:16:56 with an offset of +1000 becomes: 2018-05-28 22:16:56
2018-05-28 16:07:17 with an offset of -0200 becomes: 2018-05-28 14:07:17
2018-05-28 20:02:05 with an offset of -0700 becomes: 2018-05-28 13:02:05
2018-05-28 23:33:03 with an offset of -1000 becomes: 2018-05-28 13:33:03
2018-05-28 23:33:03 with an offset of -0000 becomes: 2018-05-28 23:33:03

I have completely revised my long (and mostly irrelevant - thanks for pointing that out!) answer.

I did however come to one valid conclusion in that previous draft of this answer: that the best approach was to make use of CONVERT_TZ and you've made use of it!

However, your own answer (with REPLACE):

CONVERT_TZ(`timestamp`,'+00:00',REPLACE(`offset`,'00',':00')) -- S1/Statement_1

which you have marked as correct is, in fact, incorrect! It won't work in all circumstances, some of which are very important!

My answer now is:

CONVERT_TZ  -- multi-line here for legibility, single line in SQL
(
  time_utc,
  '+00:00',
  CONCAT
  (
    SUBSTRING(offset, 1, 3),
    ':',
    SUBSTRING(offset, 4, 5)
  )
) AS "Vérace's d_time", -- S2/Statement_2

And it does work under all circumstances.

The OP has come up with what I think is the optimal solution to this problem which makes use of the [INSERT][1] function (kicking myself for not having spotted it!). In my notation, this would give:

CONVERT_TZ(time_utc, '+00:00', INSERT(offset, LENGTH(offset) -1, 0,' :'));

which also gives the correct result under all cicumstances.
To illustrate this, I did the following:

Created a table:

CREATE TABLE time_test
(
  time_utc    TIMESTAMP      NOT NULL,
  offset VARCHAR(5) NOT NULL
);

Added sample data:

INSERT INTO time_test
VALUES
('2018-05-28 02:34:58', '+0300'),
('2018-05-28 07:50:12', '+0400'),
('2018-05-28 09:23:34', '+0530'), -- S1 fails because of 0530
('2018-05-28 12:16:56', '+1000'), -- S1 fails because of +1000
('2018-05-28 16:07:17', '-0200'),
('2018-05-28 20:02:05', '-0700'),
('2018-05-28 23:33:03', '-1000'), -- S1 fails because of -1000
('2018-05-28 23:33:03', '-0000'); -- S1 fails because of 0000

Then ran a query incorporating the REPLACE answer and the CONCAT(SUBSTRING( answer (which works for every offset). Both answers are  compared in the query result.

SELECT
  time_utc,
  REPLACE(offset,'00',':00') AS "OP's offset", -- from S1
  CONVERT_TZ(time_utc, '+00:00', REPLACE( offset, '00', ':00')) AS "OP's d_time",
  CONCAT(SUBSTRING(offset, 1, 3), ':', SUBSTRING(offset, 4, 5)) AS "Vérace's offset",  -- from S2
  CONVERT_TZ(time_utc, '+00:00', CONCAT(SUBSTRING(offset, 1, 3), ':', SUBSTRING(offset, 4, 5))) AS "Vérace's d_time",
  TIMESTAMPDIFF(HOUR, CONVERT_TZ(time_utc, '+00:00', REPLACE( offset, '00', ':00')), CONVERT_TZ(time_utc, '+00:00', CONCAT(SUBSTRING(offset, 1, 3), ':', SUBSTRING(offset, 4, 5)))) AS "OP diff Vérace"

  -- `TIMESTAMPDIFF` is the comparison. Anything other than 0 is a fail!
FROM
  time_test;

And the result is (see the fiddle here):

           time_utc  OP's offset          OP's d_time  Vérace's offset      Vérace's d_time  OP diff Vérace
2018-05-28 02:34:58       +03:00  2018-05-28 05:34:58           +03:00  2018-05-28 05:34:58               0
2018-05-28 07:50:12       +04:00  2018-05-28 11:50:12           +04:00  2018-05-28 11:50:12               0
2018-05-28 09:23:34        +0530                 null           +05:30  2018-05-28 14:53:34            null
2018-05-28 12:16:56       +1:000  2018-05-28 13:16:56           +10:00  2018-05-28 22:16:56               9
2018-05-28 16:07:17       -02:00  2018-05-28 14:07:17           -02:00  2018-05-28 14:07:17               0
2018-05-28 20:02:05       -07:00  2018-05-28 13:02:00           -07:00  2018-05-28 13:02:05               0
2018-05-28 23:33:03       -1:000  2018-05-28 22:33:03           -10:00  2018-05-28 13:33:03              -9
2018-05-28 23:33:03      -:00:00                 null           -00:00  2018-05-28 23:33:03            null

Failing records:

no. 3 - offset = '+0530' - no '00' string for the REPLACE to work on, hence the REPLACE just returns '0530 which in turn causes CONVERT_TZ to return NULL (as per the MySQL documentation - reference below. See below also for countries with timezones not on the hour),
no. 4 - offset = +1000' - the first '00' string is replaced by ':00', hence the REPLACE offset = '+1:00' which is read by CONVERT_TZ as '+01:00' (and not '+10:00'), hence a discrepancy of +9 hours,
no. 7 - offset = '-1000' - same as for record no. 4 except for the sign,
no. 8 - offset = '-00:00' - the first '00' is replaced with ':00' which gives the REPLACE answer an offset of ':00:00' which causes CONVERT_TZ to return NULL.

With respect to the '-00:00' (same answer with + also), the following countries use UTC (also erroneously called GMT) for part of the year (from here):

Greenland, Ireland, Iceland (all year), Britain.

A number of West African countries use UTC all year round:

Burkina Faso, Cote d'Ivoire (Ivory Coast), Gambia, Ghana, Guinea, Guinea-Bissau, Liberia, Mali, Mauritania, Saint Helena, Sao Tome and Principe, Senegal, Sierra Leone, Togo.

Furthermore, there are a number of time zones that have an offset of UTC+0 (i.e. = UTC):

AZOST – Azores Summer Time, EGST – Eastern Greenland Summer Time, WET – Western European Time, WT – Western Sahara Standard Time, Z – Zulu Time Zone

One would imagine that any app worth its salt should work for Western Europe?

MySQL CONVERT_TZ documentation (from here):

  CONVERT_TZ(dt,from_tz,to_tz)

  CONVERT_TZ() converts a datetime value dt from the time zone given by
  from_tz to the time zone given by to_tz and returns the resulting
  value. Time zones are specified as described in Section 5.1.12, “MySQL
  Server Time Zone Support”. This function returns NULL if the arguments
  are invalid.

Places that have non-integer time zone offsets (from here).

  Some governments make local time zones decisions that deviate from the
  norm. Note that India is +5½ from UTC, while Myanmar (Burma) is +6½,
  Iran is +4½, Iraq is +3½, Nepal is +5¾ and Central Australia is +9½.
  Venezuela is -4 1/2, the Canadian island of Newfoundland is -3½ hours
  from UTC, and some smaller islands in French Polynesia are -9½, while
  the Pitcairn Islands are -8½.

Finally, I would strongly urge you not to use SQL keyword identifiers like DATE, HOUR, TIME and TIMESTAMP for database and/or table and/or field names. There are two reasons why doing this is a bad idea:

It makes your SQL difficult to read and debug. Say you get a message about a problem with a TIMESTAMP - is that the data type or your variable?
Your application will be difficult to port should you ever decide to do so! Use lower_case identifiers with_words_separated_by_underscores and keep your SQL keywords and functions in UPPER CASE and always use singular names. This makes your SQL easy to read and debug (caveat: this is my opinion - others may not concur!).


Rebuild Windows cluster for Alwayson. I am setting up Alwayson,somehow my cluster is gone ( get-clusternode shows down,down,down and Joining). SQL Server is working fine. What Should I do? Create new cluster or build new server? Any suggestions, best practice or Whitepaper will help.

Thank you,
Coorsktm

Questions to ask yourself start with:
How fast do you need it up?  Is it costing you money per hour being down?  Are you out of SLA?  If you don't have SLA's yet for this service can you get them real quick or have at least a good idea if it's OK to be down?

Typically you would want to learn why it is down then decide but sometimes time doesn't allow that.  Other's it could be within maintenance hours so if you know how long it takes to rebuild the cluster you know how much you have to troubleshoot.

Thus my answer would be: figure it out as long as possible while giving yourself enough time to rebuild and test it while keeping the business need it serves in mind as priority.

Personally I try to manage a process and not servers.  I wrote a lot of Powershell automation to build up a brand new FCI from scratch, build out a read only replica AG node, test, open network, test network, etc. etc.  Something like this is great if you're rebuilding and you can start your automation that way if you haven't already.  I can give you some reference on how to begin if you'd like.

Let us know if you need more info by editing your question to give us more background and clarity. Thanks!


Worry to delete Read replica RDS I created a read replica from RDS , and I do want to delete it.

I am so worry about affecting the origin RDS which is in PROD .

Is it recommended to take snapshots before deletion of read replica instance ?
Or it is a safe action , known that AWS console shows the screen when I click on delete?

Taking a snapshot is never a bad idea when making changes in a production environment.

That said, it should be safe to delete a replica in RDS as long as you don't have any service connected to it.


How to avoid Forced reboots using unattended upgrades to SQL Server 2014 Upgrading seven instances from SQL Server 2008R2 to 2014 via the ADMINISTRATOR command line using a configuration file. The upgrade runs fine but when I move on to upgrading the next instance I get the following error.

From : C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log\20150105_152519\Summary.txt

Error result: -2067919934
Result facility code: 1214
Result error code: 3010

Exception type: Microsoft.SqlServer.Configuration.RulesEngineExtension.RulesEngineRuleFailureException
    Message:
        A computer restart is required. You must restart this computer before installing SQL Server.
    HResult : 0x84be0bc2
        FacilityCode : 1214 (4be)
        ErrorCode : 3010 (0bc2)
    Data:
      SQL.Setup.FailureCategory = RuleViolationFailure
      DisableWatson = true
    Stack:
        at Microsoft.SqlServer.Configuration.RulesEngineExtension.RunRulesAction.ExecuteAction(String actionId)
        at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)
        at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.<>c__DisplayClasse.<ExecuteActionWithRetryHelper>b__b()
        at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(ActionWorker workerDelegate)

I am able to continue after restarting, but something is very wrong if I have to restart after every upgrade. Can someone help me figure out, why I have to restart after upgrading every instance?

Here is the configuration file I'm using

INSTANCENAME="SOME_INSTANCE"
SQMREPORTING="False"
INSTANCEID="SOME_INSTANCE"
IACCEPTSQLSERVERLICENSETERMS="True"
ACTION="Upgrade"
ENU="True"
QUIET="True"
UpdateEnabled="True"
ERRORREPORTING="False"
USEMICROSOFTUPDATE="False"
UpdateSource="MU"
HELP="False"
INDICATEPROGRESS="False"
X86="False"
FAILOVERCLUSTERROLLOWNERSHIP="2"

Here is the command being used to upgrade :

setup.exe  /ConfigurationFile="Confguration_Upgrade.ini"

Update

More information from detail.txt

# (01) 2015-01-05 15:45:00 Slp: File operations are pending for "SQLCTR100.DLL"
# (01) 2015-01-05 15:45:00 Slp: File operations are pending for "SQLAGENTCTR100.DLL"
# (01) 2015-01-05 15:45:00 Slp: Rule 'RebootRequiredCheck' results: IsRebootNotRequired=False
# (01) 2015-01-05 15:45:00 Slp: Evaluating rule : RebootRequiredCheck
# (01) 2015-01-05 15:45:00 Slp: Rule running on machine: G06SQL001
# (01) 2015-01-05 15:45:00 Slp: Rule evaluation done : Failed
# (01) 2015-01-05 15:45:00 Slp: Rule evaluation message: A computer restart is required. You must restart this computer before installing SQL Server.

Hi Craig,

I may have a solution for you.

You should go to the registry and delete the registry key below:
HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager\PendingFileRenameOperations

I've created PS scripts for upgrading SQL Server Instances.

Here an example:

# Select the source path and the Instance name.
# Launch the script on a PowerShell console as Administrator.

$SourcePath = "\\MYNAS\Sources\SQL Server 2014"
$InstanceName = "MYINSTANCE1"

Set-Location $SourcePath

$exists = Get-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager"  -Name "PendingFileRenameOperations"  -ErrorAction SilentlyContinue
    If (($exists -ne $null) -and ($exists.Length -ne 0)) {
        Remove-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager"  -Name "PendingFileRenameOperations"
    }

.\setup.exe /ACTION="Upgrade" /Q /INDICATEPROGRESS="True" /INSTANCENAME=$InstanceName /IACCEPTSQLSERVERLICENSETERMS

$InstanceName = "MYINSTANCE2"

Set-Location $SourcePath

$exists = Get-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager"  -Name "PendingFileRenameOperations"  -ErrorAction SilentlyContinue
    If (($exists -ne $null) -and ($exists.Length -ne 0)) {
        Remove-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager"  -Name "PendingFileRenameOperations"
    }

.\setup.exe /ACTION="Upgrade" /Q /INDICATEPROGRESS="True" /INSTANCENAME=$InstanceName /IACCEPTSQLSERVERLICENSETERMS

I hope this will help.

Max.


Mysql - Grouping and getting the Max(col1) row variables I have this table:

CREATE TABLE `table1` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `col1` VARCHAR(30) NULL DEFAULT NULL,
    `col2` VARCHAR(30) NULL DEFAULT NULL,
    `col3` VARCHAR(30) NULL DEFAULT NULL,
    `riskLevel` VARCHAR(30) NULL DEFAULT NULL
    PRIMARY KEY (`id`)
)COLLATE='utf8_general_ci' ENGINE=InnoDB;

What i want to do is group this table by col1, and get the row with the highest riskLevel for each col1 group values.

When using this query:

SELECT id,col1,col2,col3, MAX(riskLevel)
FROM table1
GROUP BY col1

You get the max riskLevel, but the other values are random, not fittings to the correct row.

I know one solution:

SELECT id,col1,col2,col3, riskLvel
FROM table1 JOIN
(SELECT col1, MAX(riskLevel) as riskLevel
FROM table1
GROUP BY col1) x ON x.col1 = table1.col1 AND x.riskLevel = table1.riskLevel

This solution doesn't seem optimal to me.

Is there another way of getting the described behavior?

SELECT id,col1,col2,col3, riskLvel
FROM table1
where risklevel IN ( SELECT MAX(riskLevel)
                     FROM table1
                     GROUP BY col1 )

IF This query is not correct then you can try this query

SELECT id,col1,col2,col3, riskLvel
    FROM table1
    where col1 IN ( SELECT MAX(col1)
                         FROM table1
                         GROUP BY col1 )


MySQL replication fails due to duplicate keys after master is restarted I'm new to MySQL load balancing, I want to try the master-slave concept.

Whenever I setup master-slave for the first time, and both have up-to-date fresh data, that works perfectly fine. However,  whenever the master goes down, or I stop and then restart it, the slave starts generating errors because of duplicate keys.

Why would this be happening and how can I prevent this?

My application has a lot of update queries something like 5 SELECTs then 3 UPDATEs or INSERTs

My data will grow with time and I'm expecting quite a high load in the future.

Don't stop a master while any slaves are connected to it. If you need to stop it, then stop the slaves first and the master at the end. This way, you won't have any errors like the one you described.

Firstly, check the slave status with:

 show slave status;

If the slave is reporting something like ”Last_Error: Error Duplicate entry.....”, this can be fixed by the following:

stop slave;
set global sql_slave_skip_counter=1;
start slave;
show slave status;

It will take some time after issuing those commands for the slave to rebuild itself from the master, but you can check the progress by querying the db to see what record we are up to, for example:

SELECT * FROM `table_name` WHERE 1 ORDER BY id DESC LIMIT 10;


To get the first author of the first book, you can use

select ExtractValue(xml_doc, '//book[1]//author[1]');

To get a real distinct list you would have to write a stored function that circles through all books and all authors.

You can try this.

SELECT
  SUBSTRING_INDEX(
    ExtractValue(
      REPLACE(artists, '</author>', '+</author>'),
      '//author[1]'
    ), '+', 1
  ) AS Author
FROM music.catalogs
ORDER BY author;


Should I repeat UNIQUE constraint on similar multi-index? The index are on tuples, ordered sets, of columns, but the UNIQUE constraint or qualifiant for an index is on (unordered) sets of columns.

I have MySQL/MariaDB in mind but my questions certainly applies to other DBMS.

If I have a table T with two columns id1, id2 and two index on this two columns:

INDEX idx_id1_id2 (id1, id2)
INDEX idx_id2_id1 (id2, id1)

and I want to declare to the DBMS that the multi-set on all records of the table T of all couples (value(id1), value(id2)), or equivalently all couples (value(id2), value(id1)), is "UNIQUE" (this multi-set is a set),

I can change

INDEX idx_id1_id2 (id1, id2)
INDEX idx_id2_id1 (id2, id1)

for

UNIQUE INDEX idx_id1_id2 (id1, id2)
INDEX idx_id2_id1 (id2, id1)

But I can also declare :

UNIQUE INDEX idx_id1_id2 (id1, id2)
UNIQUE INDEX idx_id2_id1 (id2, id1)

Is there some DBMS that deduce from

UNIQUE INDEX idx_id1_id2 (id1, id2)
INDEX idx_id2_id1 (id2, id1)

that the second index is also unique?

Is there some DBMS that deduce from

UNIQUE INDEX idx_id1_id2 (id1, id2)
UNIQUE INDEX idx_id2_id1 (id2, id1)

that they don't have to do twice the unicity check on inserting/updating value in the table ?

From a declarative point of view, I prefer

UNIQUE INDEX idx_id1_id2 (id1, id2)
UNIQUE INDEX idx_id2_id1 (id2, id1)

but however there may be a performance penalty on insert/update.

If there is a performance penalty for declaring UNIQUE on many indexes, will it be significant ?

Is there any (significant) performance boost for some queries with the second UNIQUE qualifiant added ?

Case 1  If you need to avoid both (1,2) and (2,1), then nothing in the Question will prevent that form of "dup".

To do that, always store the LESSER() value in id1 and the GREATER() in id2.  Then UNIQUE(id1, id2) prevents dups.

Meanwhile, do a similar sort of the two values when doing a lookup.

Then you have only the one UNIQUE key -- which may as well be the PRIMARY KEY.  Now you have

Uniqueness constraint that works
Fewer indexes
Only need to do one lookup, not two

etc

Case 2  If (1,2) and (2,1) can coexist, then the order of the ids presented to the table is important.  Probably all you need is

PRIMARY KEY(id1, id2)

If you have something like WHERE id1 BETWEEN .. AND .. AND id2 = 7, then you would also need

INDEX(id2, id1)

My Cookbook covers most of these simply possibilities.

Yes you should. Index for unique data need qualifier UNIQUE because that determine which kind of tree structure will be used. Sure you can achieve uniqness of fields combination by single index of UNIQUE type but unique index itself is somehow faster than non-unique one. If an index can be unique or non-unique - make it unique.


Convert date yyyy-mm-dd to integer YYYYMM How can I convert @dateb:

SET @dateb = dateadd(month, datediff(month, 0, getdate()) - 3, 0)

that returns 2014-04-04 as date to an integer of 201404

Thanks

On version 2012 or higher you can use the format function to get just year and month, then cast it as an int.

On versions prior to 2012 you can do the formatting with the convert function, then cast as int.

declare @dateb datetime
set @dateb = getdate()

select cast(format(@dateb,'yyyyMM') as int) --2012 or higher
select cast(convert(varchar(6),@dateb,112) as int) -- all versions

Could this do the trick for you?

set @dateb = cast(convert(varchar, dateadd(month, datediff(month, 0, getdate()) - 3, 0), 112) as int)

Ah sorry, I forgot, you'll need a substring too.
So it's:

select cast(substring(convert(varchar, dateadd(month, datediff(month, 0, getdate()) - 3, 0), 112), 0, 7) as int)


RESTORE failing with OS error 32, file being used by another process I am writing a set of procedures for (partially) automating DB deployment for a customer, which mostly works fine, but sometimes a RESTORE command is failing with the OS error 32, about a file being in use by another process (details below).

I have googled this extensively but have found little that applies to my specific case.  I suspect that there's something that I am overlooking, but I just can't seem to find it.

Here's the command:

RESTORE DATABASE [NBBC_Logistics] FROM DISK = '\\wpdboardq01\Shares\DbCopy\DevBackups\NBBC_Logistics_140916112310.bak'
    WITH FILE=1, NOUNLOAD, STATS=10,
     MOVE 'NBBC_Logistics' TO 'D:\MSSQL2K12\MSSQL11.MSSQLSERVER\MSSQL\DATA\NBBC_Logistics.mdf',
     MOVE 'NBBC_Logistics_log' TO 'D:\MSSQL2K12\MSSQL11.MSSQLSERVER\MSSQL\DATA\NBBC_Logistics_log.ldf',
     REPLACE
    ;

This results in the following error message:

Msg 3634, Level 16, State 1, Line 11
The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'D:\MSSQL2K12\MSSQL11.DEV\MSSQL\DATA\NBBC_Logistics_log.ldf'.
Msg 3156, Level 16, State 8, Line 11
File 'NBCC_Logistics_Model2_log' cannot be restored to 'D:\MSSQL2K12\MSSQL11.DEV\MSSQL\DATA\NBBC_Logistics_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 11
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 11
RESTORE DATABASE is terminating abnormally.

Some things to note:

This only happens on some of the receiving SQL Instances most of them are executing very similar commands with no problem.
The instance that is failing is one where there are multiple SQL instances on the same box (DEV and QA) and it is trying to restore a DB backup from DEV into the QA version of the same database.
Other DBs in this same instance can execute their corresponding RESTORE command without any problems.
It may be relevant that the source logical file names (shown in the error) are different from the DBs existing ones (named in the command), however, I beleive that I have cases where this does work.
Also, note carefully, that the file path reported by the error is NOT what I am specifying in the MOVE, but rather the original file path (which does still have the original DB files in use by the DEV instance).

So it appears that it is trying to first RESTORE the DB files to their original path locations, and only then MOVE them to the path I tell it to.  That is in contrast to what the doc says it does, and obviously would be compeltely unworkable in general, as someone RESTOREing a DB copy has no control over where the original files were and could not gaurantee that such paths existed and were not already in use.

Any help would be greatly appreciated.

Just to head off some of the automatic responses that don't apply:

The target database is not currently in use by me or anyone else.
Nor does it show up in sp_lock
The command specifies REPLACE, it is supposed to overwrite the existing database
The files specified are not open by any other windows processes
however, the file mentioned in the error are open by the other Sql Instance on the box
the BAK file location on a share has nothing to do with it, copying it locally changes nothing, nor does renaming it.
the db files and paths specified are the correct ones for the target database

When using the backup files please make sure to use the logical filename. When using multiple backupsets within the same backup files, please make sure to check that the file being used is correct.

In this instance:

  File 'NBCC_Logistics_Model2_log' cannot be restored to 'D:\MSSQL2K12\MSSQL11.DEV\MSSQL\DATA\NBBC_Logistics_log.ldf'. Use WITH MOVE to identify a valid location for the file.

It looks as though you have the wrong logical file name or the wrong backup inside a backupset. I know you pointed this out, but it would make sense to interrogate the information in the backupset and double check.

This worked for me for the 32 error .. another process is using it.

https://support.microsoft.com/en-in/help/3153836/operating-system-error-32-when-you-restore-a-database-in-sql-server-20

If you're restoring from a striped backup set that contains FILESTREAM data in SQL Server 2012 SP3, 2014, or 2016, you can get this error. The fix is to install the Cumulative Update listed for your version in the above link (or a later SP or CU).


AWS RDS same file_guids Getting this error on AWS RDS with MS SQL

  Database XXX cannot be restored because there is already an existing
  database with the same file_guids on the instance.

I've imported from S3 once, it worked. Deleted the database and tried to import a more recent version FULL DB and it broke.

I've created a new instance also but still get the error. I can import other DBs from S3 fine.

Anyone have any ideas that can help?


SS2012 Export Wizard Access is denied When a user exports to a flat file she gets an access denied error.  The wizard allows her to preview the data so she has access on the db side.  From Windows she can create a new file in the target destination so she seems to have network permissions.  She is logged into her computer with a network username and into SS2012 with a SS login.  Everyone else can run the export wizard to this location successfully.  I am stumped!

T.H.  Everyone has a their own logins.  I love your idea to try someone elses SS login from her machine.  Unfortunately the export was successful from her machine with my SS login and also a success from my machine with her SS login.  I say unfortunately because it now works from her machine with her own SS login.  The issue has resolved itself but I don't know why! :-(


Oracle RESTRICTED SESSION error I tried to install Oracle database 12c on my machine following the documentation:

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/Windows_DB_Install_OBE/Installing_Oracle_Db12c_Windows.html

I have successfully installed the database itself and can login to the database pdborcl using:

sqlplus sys/Oracle_1@pdborcl as sysdba

I unlocked the hr user following the instruction using:

alter user hr identified by hr account unlock;

However, when I tried to connect to the database using the newly unlocked user: hr, it gives an error message:

SQL> connect hr/hr@pdborcl
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege.

Warning: You are no longer connected to ORACLE.

Can someone let me know how to solve this problem? Is this something with licensing?

UPDATE:

I actually tried:

alter system disable restricted session;

What I get is:

ERROR at line 1:
ORA-65144: ALTER SYSTEM DISABLE RESTRICTED SESSION is not permitted.

The query: select logins from v$instance returns:

LOGINS
---------
ALLOWED

If alter system disable restricted session doesn't work it means that there are patch violations

Check the status with below command:

select * from PDB_PLUG_IN_VIOLATIONS;

If post steps did not complete properly then it will keep PBD's in restricted mode. Run the below steps:

sqlplus / as sysdba

shutdown immediate
startup upgrade
alter pluggable database all open upgrade;
quit
cd $ORACLE_HOME/OPatch
./datapatch -verbose

check the log, should be no errors

sql> shutdown immediate
sql>startup
sql> alter pluggable database all open;

grant connect to user;

grant all privileges to user;


Only one simultaneous connection per user? I'm currently experiencing some trouble, seemingly because my application refuses to connect to the database if there is already another connection open from the same user (but in a separate process).

Based on my observations, it doesn't seem to matter how many or few other users are connected, and @@MAX_CONNECTIONS is set to 0 (unlimited).

Is there any way such a connections-per-user limitation may have been defined on the database, and if so, how can I turn it off?

Here is my connection string:

[db]
sql=MS SQL
provider=SQLOLEDB.1
data source=foo\bar
connection=Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=foobar

As you might be able to discern from the above, we are connecting to the database using Microsoft OLE ADO. The application is an old VB6 legacy program.

By "refuses to connect" I mean that the method Open on the ADODB.Connection object fails.

Sadly, there is currently no log of the error output from this method in the production environment. I'm currently waiting for a patch to the application to be applied, to see if I can get extended information here.

Is there any way such a connections-per-user limitation may have been defined on the database, and if so, how can I turn it off?

You are using Microsoft SQL-Server. You can limit the Resources with the Resource Governor. Have a look at the Microsoft Documents. There you can see which options might be set and you should check. If there is a limitation, you may find it there.

Documentation: https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor

I think you may be able to turn MARS off to resolve this.  I had the same scenario with an application, and turning MARS off disallowed starting a new query before finishing the old one.

Try Adding this to your ConnString and resetting the connection:

MultipleActiveResultSets=false

Data Source=MSSQL; Initial Catalog=AdventureWorks; Integrated Security=SSPI; MultipleActiveResultSets=false

Link to article on MARS


mysql slave restore database ERROR 3079 Master A mysql 8
Master B mysql 5.7
Master C mysql 5.7
Slave mysql 8

i create 4 channels on my slave and i can not restore backup from master.

[root@CentOS-76-64-minimal hex]# gunzip < db.gz | mysql -u root -p
Enter password:
ERROR 3079 (HY000) at line 22: Multiple channels exist on the slave. Please provide channel name as an argument.

Try this

mysql> create database mydb;
mysql> use mydb;
mysql> source db_backup.dump;


SQL Server Configuration manager doesn't show the IP Sorry for stupid question.
I'm trying to set up the remote connection to the SQL Server.
In all tutorials I watched people have their IPs shown in the TCP/IP properties window(under SQL Server Network Configuration-->Protocols for MSSQLSERVER). Please see the picture.
But I have only the port.
Where can I see the IP? What should I do to see it?

What the version of SQL Server are you using?
Please make sure you open the same version of SQL Server Configuration Manager if you have multiple different version SQL Server instances.
You can use the following query to view the raw data with your network protocols:
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE registry_key LIKE '%SuperSocketNetLib%'

See this for more details.
Regards,
Amelia

Can you go to Registry Editor to see all IPs: HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLXX.[Instance Name]\MSSQLServer\SuperSocketNetLib\Tcp\ .
Ensure that the version of SSCM used is the same as that of SQL Server.
There may be several reasons why you cannot see the IP:
1.Related keys in registry is corrupt
2.Components of SQL Server Configuration Manager(SSCM) is corrupt


View for a MANY_MANY_MANY relationship Let's say I have 3 main tables:
category, tag, url

category and tag have a MANY_MANY relationship (category_tag).

tag and url have a MANY_MANY relationship (url_tag).

I would like for a given category to retrieve all the linked urls.

What is the easiest, most efficient way to do this?

Could I maybe build a "VIEW"? What would be the query?

I am using MySQL.

Encapsulating the queries into Views or not, is your choice. For every one of the below queries, you can create a view:

CREATE VIEW category_url AS
  ( Query
  ) ;

If you want only the IDs of the categories and URLs, the simplest (joining only 2 tables) is this - no need for joining the tag table, as long as the foreign key constraints are defined:

    SELECT DISTINCT
        ct.category_id,
        ut.url_id
    FROM
        category_tag AS ct
      JOIN
        url_tag AS ut
          ON ut.tag_id = ct.tag_id

or (the equivalent) which can be easily extended to include the number of tags relating a category with a url:

    SELECT
        ct.category_id,
        ut.url_id,
        COUNT(*) AS number_of_joining_tags         --- optional
    FROM
        category_tag AS ct
      JOIN
        url_tag AS ut
          ON ut.tag_id = ct.tag_id
    GROUP BY
        ct.category_id,
        ut.url_id

If you also want columns from the category and url tables as well (which is very probable), you need to join those tables, too:

    SELECT
        ct.category_id,
        ut.url_id,
        COUNT(*) AS number_of_joining_tags,        --- optional

        c.*,                                       --- the columns from `category`
        u.*                                        --- and `url` that you need
    FROM
        category_tag AS ct
      JOIN
        url_tag AS ut
          ON ut.tag_id = ct.tag_id

      JOIN
        category AS c
          ON c.id = ct.category_id
      JOIN
        url AS u
          ON u.id = ut.url_id

    GROUP BY
        ct.category_id,
        ut.url_id

I created the following VIEW:

CREATE VIEW category_url AS
(SELECT DISTINCT category.id AS category_id, url.id AS url_id
FROM category
JOIN category_tag ON category_tag.category_id=category.id
JOIN url_tag ON url_tag.tag_id = category_tag.tag_id
JOIN url ON url.id = url_tag.url_id)


db_owner Member Can't Delete on Tables SQL server 2008 R2.

A user is part of an AD group. That AD group has a SQL account in this instance. That group has the following access to a Database:

db_datareader
db_datawriter
db_ddladmin
db_executor
db_owner
MSReplPal_7_1
public

When that user runs "Delete" on this database, it throws an error:

  The DELETE permission was denied on the object '', database '', schema 'dbo'.

User belongs to some other groups but none of those groups has permission to this DB in question.

This group does not have explicitly deny on the table in question.

What could the reason the user cannot delete on tables?

Also, I don't understand how this group has given this many access on this database. Doesn't having "db_owner" imply the group has maximum access to the database and other permission don't need to be added?? such as db_datareader, db_datawriter, etc.

Thanks for your time.

Unless the users is mapped to the sysadmin fixed server role (where they will connect to the database as the user dbo) then they can be limited by security on the database.  (i.e. A member of db_owner will receive a security check where dbo will not.)

A quick test to see if it is a permissions issue is to temporarily grant a user sysadmin role and then see if they can delete data....  if not then I'd start looking for constraints on tables.

If they can then run the following against the database (you may get more you may need to add DELETE to your where clause):

select * from sys.database_permissions
where state_desc = 'DENY'

If permissions are set it will give you something like:

class class_desc                                                   major_id    minor_id    grantee_principal_id grantor_principal_id type permission_name                                                                                state state_desc
----- ------------------------------------------------------------ ----------- ----------- -------------------- -------------------- ---- -------------------------------------------------------------------------------------------------------------------------------- ----- ------------------------------------------------------------
0     DATABASE                                                     0           0           10                   1                    DL   DELETE                                                                                D     DENY
1     OBJECT_OR_COLUMN                                             1501607433  0           10                   1                    DL   DELETE                                                                                D     DENY

(2 row(s) affected)

If deny delete is set at the DATABASE level then well then there is your problem.  Adjust permissions so required users are not actually DENYed access.

check groups this user is part of. In this scenario user should be part of some group which has been specifically denied access for dml operations. i have seen this many times.


Is it possible to set auto-increment-increment by 10 for only one table? Is it possible to add auto-increment-increment by 10 for only one table in mysql?

I have a testing db that has a lot of tables that do not abide by the auto-increment-increment of 10.  I won't want to mess up my testing rig.

The production server has auto-increment-increment of 10.

According to the MySQL documentation, you can't set custom auto-increment value for a specific table.

  It is not possible to restrict the effects of these two variables to a single table; these variables control the behavior of all AUTO_INCREMENT columns in all tables on the MySQL server.

However, this value has both global and session level effect. You may set it for session level and can perform required tests. And the changes will not persist after restarting your database server.

Reference:
auto_increment_increment

You can set auto-increment in Microsoft SQL but I don't think MySQL allows such, they auto-increment for you. You can only set your starting value of the auto-increment but not the common difference.
You should use Microsoft SQL if you really need auto-increment by. This feature is aided by IDENTITY(2,5) where the first value is 2 and the subsequent values increase by 5 in this example.
https://www.w3schools.com/sql/sql_autoincrement.asp
The link is for Microsoft SQL


Delete using id's not found in loop - MySQL I have a stored procedure where I loop through an array passed as an argument, update the values provided, then delete the missing values using an id passed in the given array.

    /* edit the purchase items */
        WHILE items_index<items_length DO
            UPDATE PurchaseOrderItems SET quantity=JSON_EXTRACT(p_purchaseitems, CONCAT('$[',items_index,'].quantity')) ,products_productid=JSON_EXTRACT(p_purchaseitems, CONCAT('$[',items_index,'].productid')) WHERE itemid=JSON_EXTRACT(p_purchaseitems, CONCAT('$[',items_index,'].purchaseitemid'));
            IF items_index+1=items_length THEN
                /* condition will only be true in last iteration*/
                SET item_separator='';
            END IF;
            SET save_list = CONCAT(save_list,JSON_EXTRACT(p_purchaseitems, CONCAT('$[',items_index,'].purchaseitemid')),item_separator);
            SET items_index=items_index+1;
        END WHILE;
        /* delete all other items that were not in the list */
        DELETE FROM PurchaseOrderItems WHERE purchaseorders_purchaseorderid=p_purchaseorderid AND itemid not in (save_list);

The delete command however deletes all the records except for the first item in the save_list variable. I have confirmed that the variable save_list has the id's to be skipped so where are things going wrong?

Maybe im wrong but the  SET item_separator='' looks empty. I could imagine that your ID´s you build there are not right and therefore he does not ignore them.
I'm going to rebuild a scenario like this and try to help you. But first see if the output of save_list is really a string of ID´s.


Crow's Foot notation and identifying -vs- non-identifying relationships Is it possible to distinguish between identifying and non-identifying relationships using the Crow's Foot notation?

Case in point: In table Book, part of the book's Primary Key is a FK to the Author table. In other words I embed the author's identifier in the book's identifier. Also, a book has a FK to a Book_Type (containing values like "hardcover", "papercover") (this is just an example, I understand that I would probably need a "book edition" table for that).

The thing is that this latter FK relationship isn't nearly really as important as the former one (the FK to Book_Type is not part of Book's Primary Key), yet my understanding is that in the Crow's Foot diagram shown below, both these relationships are rendered identically:

Have I misunderstood something or is there a way to signify the identifying versus non-identifying relationship distinction using the Crow's Foot notation?

Information Engineering methodology placed a big "I" on the relationship to show the foreign key was part of the primary key:

In your diagram, you are showing both entities as solid lines indicating identifying relationships.  Non-identifying relationships are shown with dashed lines.


Why is Primary Key not present at the end of Unique Key in MySQL? I have a table with following definition.

CREATE TABLE `test` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  `e` int(11) DEFAULT NULL,
  `f` int(11) DEFAULT NULL,
  `g` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `h` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `b_UNIQUE` (`b`),
  KEY `single` (`c`),
  KEY `double` (`d`,`e`),
  KEY `triple` (`f`,`g`,`h`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

For some queries when I check optimizer trace I notice key parts for indices.

For example:

{
  "index": "single",
  "usable": true,
  "key_parts": [
    "c",
    "a"
  ]
},
{
  "index": "double",
  "usable": true,
  "key_parts": [
    "d",
    "e",
    "a"
  ]
}

Notice for indices single and double the key part contains the Primary Key a at the end. That is what InnoDB says. So thats good.

{
  "index": "b_UNIQUE",
  "usable": true,
  "key_parts": [
    "b"
  ]
}

But as seen in above snippet, the b_Unique key does not contain primary key a as the final key part. Is it really not present and pointer to record is present? What implications does this have with respect to query optimization and Page reorganisation?

EDIT 1: It seems to be present in MySQL 8.0 version. It is highly likely to be present in MySQL version 5.7 as well. So require a way to check its presence in this version.

But as seen in above snippet, the b_Unique key does not contain primary key a as the final key part.

It is a snippet problem.

show index from test

Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression
:---- | ---------: | :------- | -----------: | :---------- | :-------- | ----------: | -------: | :----- | :--- | :--------- | :------ | :------------ | :------ | :---------
test  |          0 | PRIMARY  |            1 | a           | A         |           0 |     null | null   |      | BTREE      |         |               | YES     | null
test  |          0 | b_UNIQUE |            1 | b           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | single   |            1 | c           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | double   |            1 | d           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | double   |            2 | e           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | triple   |            1 | f           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | triple   |            2 | g           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | triple   |            3 | h           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null

show extended index from test

Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression
:---- | ---------: | :------- | -----------: | :---------- | :-------- | ----------: | -------: | :----- | :--- | :--------- | :------ | :------------ | :------ | :---------
test  |          0 | PRIMARY  |            1 | a           | A         |           0 |     null | null   |      | BTREE      |         |               | YES     | null
test  |          0 | PRIMARY  |            2 | DB_TRX_ID   | A         |        null |     null | null   |      | BTREE      |         |               | YES     | null
test  |          0 | PRIMARY  |            3 | DB_ROLL_PTR | A         |        null |     null | null   |      | BTREE      |         |               | YES     | null
test  |          0 | PRIMARY  |            4 | b           | A         |        null |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          0 | PRIMARY  |            5 | c           | A         |        null |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          0 | PRIMARY  |            6 | d           | A         |        null |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          0 | PRIMARY  |            7 | e           | A         |        null |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          0 | PRIMARY  |            8 | f           | A         |        null |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          0 | PRIMARY  |            9 | g           | A         |        null |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          0 | PRIMARY  |           10 | h           | A         |        null |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          0 | b_UNIQUE |            1 | b           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          0 | b_UNIQUE |            2 | a           | A         |        null |     null | null   |      | BTREE      |         |               | YES     | null
test  |          1 | single   |            1 | c           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | single   |            2 | a           | A         |        null |     null | null   |      | BTREE      |         |               | YES     | null
test  |          1 | double   |            1 | d           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | double   |            2 | e           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | double   |            3 | a           | A         |        null |     null | null   |      | BTREE      |         |               | YES     | null
test  |          1 | triple   |            1 | f           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | triple   |            2 | g           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | triple   |            3 | h           | A         |           0 |     null | null   | YES  | BTREE      |         |               | YES     | null
test  |          1 | triple   |            4 | a           | A         |        null |     null | null   |      | BTREE      |         |               | YES     | null

Any Unique Key you create has no need of a PRIMARY key.

Over 8.5 years ago, I answered the post Why do primary keys have names of their own?. I described how a Primary Key is really no different from a Unique Key because all Unique Keys (properly known in Database Theory as Candidate Keys) define a level of uniqueness for each row in a table.

For example, an employee table could have an Employee ID number and a Social Security Number. Each value unique identifies an employee. What makes a Primary Key different from a Unique Key is strictly a matter of choice via application use. The Primary Key is just the default Candidate key to reference and identify table rows. Secondary Indexes will go by PRIMARY Key to determine each row it retrieves.

Unique Keys could also dictate the strictness of data being entered into a table. Suppose you wanted to make sure three columns in a table were unique to a table row. Once you entered the data into the table, would you always look up rows in your table with three columns (from the Unique Key) or just one column (the Primary Key) ? Naturally, using the fewest columns make sense.

With regard to your table, you could have easily designed it as

CREATE TABLE `test` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  `e` int(11) DEFAULT NULL,
  `f` int(11) DEFAULT NULL,
  `g` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `h` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`b`),
  UNIQUE KEY `a_UNIQUE` (`a`),
  KEY `single` (`c`),
  KEY `double` (`d`,`e`),
  KEY `triple` (`f`,`g`,`h`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

and all SELECT queries would mechanically look up data the same way within the Query Optimizer.

WORD OF CAUTION: Avoid multiple column Primary Keys as much as possible because bigger Primary Keys will bloat all Secondary Keys. Please read MySQL Docs on Clustered Indexes for more information.


Select Items that has one item but not the other I have three tables:

Table1:

ID      Name
1       Item1
2       Item2
3       Item3

Table2:

ID      Name         Type
1     Logistics       One
2     Human Resource  Two
3     Finance         Three

Table3 (Many to Many Table):

ID     Table1ID      Table2ID
1         1             1
2         1             2
3         2             1
4         3             1
5         3             2
5         3             3

I want to select all the items from table1 that exist in many to many table for just type one from table2 and not to the type two. the type three is not concerned. So all the items from table one that has a record in table3 for only type one and not two.

You can use NOT EXISTS to eliminate rows linked to undesirable types (like @a_horse already suggested).

But since multiple rows can match any row in table1 also use EXISTS to qualify rows linked to desirable types:

SELECT t1.*
FROM   table1 t1
WHERE  EXISTS (
   SELECT 1
   FROM   table2 t2
   JOIN   table3 t3 ON t3.table2_id = t2.id
   WHERE  t2.type = 'one'
   AND    t3.table1_id = t1.id
   )
AND    NOT EXISTS (
   SELECT 1
   FROM   table2 t2
   JOIN   table3 t3 ON t3.table2_id = t2.id
   WHERE  t2.type = 'two'
   AND    t3.table1_id = t1.id
   );

With joins like a_horse demonstrated, you would multiply rows and be forced to add an aggregation or DISTINCT step to get distinct rows again. Don't.

Also, you can use the same subquery twice, making the query simpler to understand and maintain.

I would see this as special case of relational-division, btw. Related:

Select rows which are not present in other table
How to filter SQL results in a has-many-through relation

It sounds like you want the following.
Join the tables, and filter. The group by columns should include all columns from table1

Select t1.* from table1 as t1
Join table3 t3 on t1.id = t3.table1id
Join table2 t2 on t2.id = t3. Table2id
Where t2.type = 'two'
Group by t1.{column 1}, t1.{column 2}, t1. {Column n...}


SSMS crashes when attempting to create a database diagram I'm currently using the latest version of SSMS (17.8.1) on windows 10 and everytime I try to create a database diagram it crashes without giving me any error message. I've already tried to re-install SSMS but the problem still occurs.

I've found that the database diagram tool seems to crash if you modify things outside of the diagram.
Diagrams appear to remain stable if:

The database diagram tool is used to do all of your modifications, thus retaining all metadata of the process within the diagram.
Your database structure is modified outside of the diagram which is then deleted and recreated each time.
The entire database is built, ground up, to a point that you are satisfied and feel that it won't change.  The database should then be diagrammed post-beta.

Whichever method is used, this is a known issue.
To confirm, I have this issue while using SSMS 18.4 against a SQL 2016 Express instance.
Current Status - 10/03/2021 - SSMS Version 18.7.1
I still have issues with the diagram tool. It is slightly more stable, though, if you modify anything outside of a current diagram, the editor complains about metadata and you lose your work. In fact, I found that I had to also close and reopen SSMS to ensure I could edit or create a diagrams.  So, while SSMS remains a little more stable (that's debatable), whatever has been done is not a solution.
Roll on version 37.n - let's hope it's fixed by then!
Further Notes From a Small Island (12/03/2021)
There also appears to be an issue with putting your PC to sleep, particularly if you're working over a VPN. I found that when you sleep your computer, then attempt to modify a diagram that was left open, the editor errors with various meta-data related faults (there has been more than one fault). The remedy to this was to reconnect the VPN, then refresh the database diagrams folder in SSMS...

This should allow you to save the diagram.

Luckily, database diagrams won't be available in future versions of SSMS starting from 18.0, as it's a deprecated feature. Source


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

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

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

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

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

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

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

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

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

DISTINCT

GROUP BY

HAVING

LIMIT

UNION or UNION ALL

Subquery in the select list

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

[src]

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

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

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

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

i think views are the predefined structure (no data) for merging tables into one to overcome from multiple table query, that can be used from real data for quick relational query's ...


Dynamic contacts database I'm trying to create a database for contacts. I must have many categories for every contact, like Home, Office, Emails, Phones. In every category one can add more than one value. Also the categories can be dynamic. How can this be designed in a database?

Check out relational database design.  It's an art and a science, and something that really can't be explained to the fullest in a post.  But what you should do is create relationships between contacts and all possible data that will be related to it.  This is most prevalent through the use of primary and foreign key relationships.

You should look at Universal Data Models for Party Contact Mechanism, which allows you to store as many contacts and category of contacts that you want.


SQL Server Space Reclaim We have a sizeable SQL Server 2016 database (running on Windows 2016 Datacenter). A table was growing exponentially due to a bad SQL process.  The bad SQL process has been resolved, but the specific table is still large, and the database instance grabbed a lot of space on the server.  That is, the database instance's physical size is very large.

We need to reduce the physical size and "shrink database" is not recommended by many because of the fragmentation it causes.  To process this while keeping everything on the same server, is the best thing to:

Deal with specific table

a) Create a script that will build a new table and copy records from original table.  But not copy excessive records created by bad SQL process.
b) Then delete the original table and rename the new table.
Deal with general database

c) Create a script to clone the database schema and use this to create a new database.
d) Then script the data in chunks from the current database to the new database (because data is extensive).
e) Drop the current database and rename the new one.

However, if space is an issue on the server to achieve this, could I:

f) Script the entire database WITH SCHEMA and DATA
g) Drop the current database
h) Use script to rebuild the entire database

Delete the extra data out of regular business hours, then shrink the database. Don't overcomplicate things.

Andy Mallon explains how in the post How to shrink a database in 4 easy steps:

First you'll get the file sizes for the databases on your instance, using the sys.database_files system view.
Then using DBCC SHRINKFILE, you'll shrink each file in question.
Finally, you'll rebuild all your indexes to ensure any fragmentation is handled.

Here is an alternative I suggest.

DELETE the unwanted records in a chunk (5k, 10k, 20k) not to cause significant blocking.
Then, take a maintenance window to shrink the database in a chunk to an acceptable size by slowly increasing the chunk size.


Trying to return empty rows even if data does not exsist in SQL I am trying to return the last changed time for a list of users, but i would like to return a row even if the a last changed time does not exist

select  user_id,
        last_changed_time = MAX(ISNULL(change_date_time,0))
from audit_log_tx
where user_id in (users)
group by user_id
order by user_id asc

SELECT u.user_id,
  column_name = COALESCE(MAX(change_date_time), '19000101')
  FROM dbo.users AS u
  LEFT OUTER JOIN dbo.audit_log_tx AS o
  ON u.user_id = o.user_id
  GROUP BY u.user_id;

Though I really don't like the use of token dates for missing data (especially 0).

use any sql function below

IFNULL(change_date_time,0) or
COALESCE(change_date_time,0) or
ISNULL(change_date_time,0).
http://www.w3schools.com/sql/sql_isnull.asp [use this link for more details][1]

if users is a tablename this is a clear syntax error.
use

select  user_id,
    last_changed_time = MAX(ISNULL(change_date_time,0))
from audit_log_tx
where user_id in (select distinct user_id from users)
group by user_id
order by user_id asc


How to generate list of dates --- ie every day for the past 2 years? We're using a business intelligence system and need to load a list of dates so we can flag them as 'last week' or 'last 12 months' or some dynamic value.

I'm wondering what's the simplest way to virtually generate a table that simply lists dates in one column, literally every date from '2014-01-01' to the current date (the other columns I can use a formula from there). Actually even appending future dates on for a year would probably be useful as well.

Now yes I can grab distinct dates from another random fact table that has thousands of entries, but that seems sloppy and is creating a dependency where there really shouldn't be one.

Using pure T-SQL, you can do the following:

;WITH cte AS
(
  SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1 AS [Incrementor]
  FROM   [master].[sys].[columns] sc1
  CROSS JOIN [master].[sys].[columns] sc2
)
SELECT DATEADD(DAY, cte.[Incrementor], '2014-01-01')
FROM   cte
WHERE  DATEADD(DAY, cte.[Incrementor], '2014-01-01') < GETDATE();

Or, I also wrote a SQLCLR function to make this a little easier, which is available in the Free version of the SQL# library:

SELECT [DatetimeVal]
FROM   [SQL#].[Util_GenerateDateTimeRange]('2014-01-01', GETDATE(), 1, N'day');

If you want the ending date to be in the future, just replace the GETDATE() in either query.

Updated Code for Generating dates for next 20 days from today

;WITH cte AS
(
  SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 1 AS [Incrementor]
  FROM   [master].[sys].[columns] sc1
  CROSS JOIN [master].[sys].[columns] sc2
)
SELECT convert(VARCHAR,DATEADD(DAY, cte.[Incrementor], GETDATE()+2),103) as [Date]
FROM   cte
WHERE  DATEADD(DAY, cte.[Incrementor], GETDATE()-20) < GETDATE();


MySQL any way to import a huge (32 GB) sql dump faster? I have this huge 32 GB SQL dump that I need to import into MySQL. I haven't had to import such a huge SQL dump before. I did the usual:

mysql -uroot dbname < dbname.sql

It is taking too long. There is a table with around 300 million rows, it's gotten to 1.5 million in around 3 hours. So, it seems that the whole thing would take 600 hours (that's 24 days) and is impractical. So my question is, is there a faster way to do this?

Further Info/Findings

The tables are all InnoDB and there are no foreign keys defined. There are, however, many indexes.
I do not have access to the original server and DB so I cannot make a new back up or do a "hot" copy etc.
Setting innodb_flush_log_at_trx_commit = 2 as suggested here seems to make no (clearly visible/exponential) improvement.
Server stats during the import (from MySQL Workbench): https://imgflip.com/gif/ed0c8.
MySQL version is 5.6.20 community.
innodb_buffer_pool_size = 16M and innodb_log_buffer_size = 8M. Do I need to increase these?

Percona's Vadim Tkachenko made this fine Pictorial Representation of InnoDB

You definitely need to change the following

innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0

Why these settings ?

innodb_buffer_pool_size will cache frequently read data
innodb_log_buffer_size : Larger buffer reduces write I/O to Transaction Logs
innodb_log_file_size : Larger log file reduces checkpointing and write I/O
innodb_write_io_threads : Service Write Operations to .ibd files. According to MySQL Documentation on Configuring the Number of Background InnoDB I/O Threads, each thread can handle up to 256 pending I/O requests. Default for MySQL is 4, 8 for Percona Server. Max is 64.
innodb_flush_log_at_trx_commit

In the event of a crash, both 0 and 2 can lose once second of data.
The tradeoff is that both 0 and 2 increase write performance.
I choose 0 over 2 because 0 flushes the InnoDB Log Buffer to the Transaction Logs (ib_logfile0, ib_logfile1) once per second, with or without a commit. Setting 2 flushes the InnoDB Log Buffer only on commit. There are other advantages to setting 0 mentioned by @jynus, a former Percona instructor.

Restart mysql like this

service mysql restart --innodb-doublewrite=0

This disables the InnoDB Double Write Buffer

Import your data. When done, restart mysql normally

service mysql restart

This reenables the InnoDB Double Write Buffer

Give it a Try !!!

SIDE NOTE : You should upgrade to 5.6.21 for latest security patches.

This will do:

mysql --init-command="SET SESSION FOREIGN_KEY_CHECKS=0;SET UNIQUE_CHECKS=0;" -u root -p < Backup_Database.mysql


InnoDB page_cleaner error message: The settings might not be optimal My server is in idle state but I often get this error:

Note: InnoDB: page_cleaner: 1000ms intended loop took 35165ms.
The settings might not be optimal.
flushed=0 and evicted=0, during the time

How can I fix this issue?

mysql> set global innodb_lru_scan_depth=256;


ORACLE- Database unable to START/OPEN When I'm trying to start a database (after a server patch was applied), I get the following error and I am unable to open the database.

SQL> startup;
ORACLE instance started.

Total System Global Area 7465926656 bytes
Fixed Size                  2267744 bytes
Variable Size            1241515424 bytes
Database Buffers         6207569920 bytes
Redo Buffers               14573568 bytes
Database mounted.
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1:
'/u00/oracle/app/oracle/product/oradata/XRO/sy_XRO.dbf

NOTE:

SQL> select database_role from v$database;

DATABASE_ROLE
------------------------------------------------
PHYSICAL STANDBY

SQL> select status from v$instance;

STATUS
------------------------------------
MOUNTED

Generally you can open standby databases as read only. Specifically you can open data guard standby databases as read only. Did you check to see if all of the data files exist? That a database is mounted, means that the control files are where they are supposed to be, not corrupted and your init/spfile is valid.

Did you patch the Oracle Home for the standby database with the same patch as the parent? Can you force database recovery on the child to get the databases in sync.


Run a script for all databases I need help  what statement or function i can use to run a query in all database using one script , i want to run the below script at the same time to retrieve output in all databases

   select
      @@SERVERNAME as ServerName,
      DB_NAME() as DatabaseName,
      Object_ID = (id),
      OBJECT_NAME (id) as StoredProc,
      Text
    from syscomments
    where text like '%Janice\SQL%DTS%'.

The solution heavily depends on how much work you are willing to put in your initial query and what is going to consume the data (and how).

-- Option 1 --

If this is a one-off requirement, you can use variations of sp_MSForEachDB to retrieve a separate data set for each database.

Personally, I don't like sp_MsForEachDB for a number of reasons, mainly because it uses a wrongly configured cursor to enumerate the databases, which can make it skip databases at times. I coded my own alternative to it some years ago and blogged about it here

The main issue with this solution is that it doesn't return a single data set, which I suppose is what you want. In this case, you should create a temporary table upfront and use it to store data returned from your query. All executions against different databases will populate the temporary table with the data you need.

Here's an example:

DECLARE @sql nvarchar(max);

SET @sql = '
select
      @@SERVERNAME as ServerName,
      DB_NAME() as DatabaseName,
      Object_ID = (id),
      OBJECT_NAME (id) as StoredProc,
      Text
    from syscomments
    where text like ''%Janice\SQL%DTS%''
';

CREATE TABLE #results (
    [ServerName] [nvarchar](128) NULL,
    [DatabaseName] [nvarchar](128) NULL,
    [Object_ID] [int] NOT NULL,
    [StoredProc] [nvarchar](128) NULL,
    [Text] [nvarchar](4000) NULL
);

DECLARE @statement nvarchar(max);

SET @statement = (
SELECT 'EXEC ' + QUOTENAME(name) + '.sys.sp_executesql @sql; '
FROM sys.databases
WHERE DATABASEPROPERTY(name, 'IsSingleUser') = 0
      AND HAS_DBACCESS(name) = 1
      AND state_desc = 'ONLINE'
FOR XML PATH(''), TYPE
).value('.','nvarchar(max)');

INSERT #results
EXEC sp_executesql @statement, N'@sql nvarchar(max)', @sql;

SELECT *
FROM #results;

-- Option 2 --

This solution requires that you put your sql statement in a string variable and you know the output of the query. If this is not acceptable for you, some time ago I coded a stored procedure that can take the SELECT statement inside a view and use it to execute against all databases (or a subset of your databases). In this case, you just need to create a view with the statement you want to run and pass the name to that stored procedure.
You can find the code here.

-- Option 3 --

You could also UNION ALL the results that come from your query, something like this:

DECLARE @sql nvarchar(max)

SET @sql = '
select
      @@SERVERNAME as ServerName,
      DB_NAME() as DatabaseName,
      Object_ID = (id),
      OBJECT_NAME (id) as StoredProc,
      Text
    from <dbname>.dbo.syscomments
    where text like ''%Janice\SQL%DTS%''
';

DECLARE @statement nvarchar(max);

SET @statement = STUFF((
SELECT 'UNION ALL ' + REPLACE(@sql,'<dbname>',QUOTENAME(name))
FROM sys.databases
WHERE DATABASEPROPERTY(name, 'IsSingleUser') = 0
      AND HAS_DBACCESS(name) = 1
      AND state_desc = 'ONLINE'
FOR XML PATH(''), TYPE
).value('.','nvarchar(max)'),1,9,SPACE(0));

EXEC sp_executesql @statement

If you use this approach, you also have to take care of everything that could go wrong in the UNION ALL. Collation conflicts between databases, for instance, have to be resolved in the query.

Hope this helps.

BTW, syscomments is deprecated, so if you're using SQL Server 2005 and above, use sys.sql_modules instead.

EXECUTE master.sys.sp_MSforeachdb 'USE [?];
BEGIN
select
      @@SERVERNAME as ServerName,
      DB_NAME() as DatabaseName,
      Object_ID = (id),
      OBJECT_NAME (id) as StoredProc,
      Text
    from syscomments
    where text like '%Janice\SQL%DTS%'.
END


How to speed up audit trigger? The task: write a trigger to log all updates within tbl1 table for future audit users' actions.

The tbl1 table:

id INT(11)
y SMALLINT(6)
country SMALLINT(6)
-- really here ~20 fields but only some of them have to be monitored

I'm going to store both old & new values, one row for each field that was changed. The place for log (simplified for this example):

 CREATE TABLE z_log(
   id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
   table_name CHAR(20) NOT NULL,
   row_id INT(11) UNSIGNED NOT NULL,
   field CHAR(20) NOT NULL,
   old_val VARCHAR(255) NOT NULL DEFAULT '',
   new_val VARCHAR(255) NOT NULL DEFAULT '',
   PRIMARY KEY (id)
 )

The trigger I have created:

CREATE TRIGGER trigger1
    AFTER UPDATE
    ON tbl1
    FOR EACH ROW
BEGIN
  -- I'm interested in two fields only and if they were changed only
  IF new.y != old.y THEN
    SET @y = "('','tbl1',new.id,'y',old.y,new.y)";
  END IF;

  IF new.country != old.country THEN
    SET @country = "('','tbl1',new.id,'country',old.country,new.country)";
  END IF;

  if @y != '' or @country != '' THEN
    set @my_sql = concat('insert into z_lot values ', concat_ws(',', @y, @country), ';');
    prepare stmt1 from @my_sql;
    EXECUTE stmt1;
  end if;

END

But I got error "dynamic sql is not allowed in stored function or trigger".
I can avoid 'execute'  by using a lot separated inserts: one for field 'y', one for field 'country' and ~10 inserts more for other fields but there will be performance penalty.

Is there any other fast way to log changes only?
Thank you.

You could alter the trigger with brute force INSERTs as follows:

CREATE TRIGGER trigger1
    AFTER UPDATE
    ON tbl1
    FOR EACH ROW
BEGIN
  DECLARE insert_style INT;

  -- I'm interested in two fields only and if they were changed only

  SET insert_style = 0;
  IF new.y != old.y THEN
      SET insert_style = 1;
  END IF;
  IF new.country != old.country THEN
      SET insert_style = insert_style + 2;
  END IF;

  CASE insert_style
      WHEN 1 THEN
          insert into z_lot values
          ('','tbl1',new.id,'y',old.y,new.y);
      WHEN 2 THEN
          insert into z_lot values
          ('','tbl1',new.id,'country',old.country,new.country);
      WHEN 3 THEN
          insert into z_lot values
          ('','tbl1',new.id,'y',old.y,new.y),
          ('','tbl1',new.id,'country',old.country,new.country);
  END CASE;

END

I have spent a few days to come up with a Stored Procedure to automatically/dynamically create UPDATE / DELETE triggers in MariaDB (Works with v 10.1.9) auditing all changes on updates and deletions. The solution uses the INFORMATION_SCHEMA to automatically build an audit trigger for each of your tables. On Update only changed columns are audited, whilst on delete all the history is retained in the audit.

The Stored Procedure will give you a CREATE TRIGGER script which you can execute separately.

In the example below we create a test database with two tables, tb_company and tb_auditdetail which will hold our audit log.

    -- Dynamic Automated Update / Delete Triggers in MariaDB
    -- Leonard Tonna 19/05/2016 - www.ilabmalta.com

    CREATE DATABASE db_ilabmalta_test;

    USE db_ilabmalta_test;

    CREATE TABLE tb_auditDetail(
        audit_pk int(9) NOT NULL PRIMARY KEY AUTO_INCREMENT,
        type varchar(1) NOT NULL,
        tablename varchar(128) NULL,
        pk varchar(128) NULL,
        fieldname varchar(128) NULL,
        oldvalue varchar(1000) NULL,
        newvalue varchar(1000) NULL,
        updatedate datetime NULL,
        username varchar(128) NULL,
        dbusername varchar(128) NULL,
        machinename varchar(128) NULL);

    CREATE TABLE tb_company(
        cmp_pk int(9) NOT NULL PRIMARY KEY AUTO_INCREMENT,
        cmp_name varchar(100) NOT NULL,
        cmp_no varchar(16) NULL,
        cmp_status smallint NOT NULL DEFAULT 1,
        cmp_created datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
        cmp_createdby varchar(10) NOT NULL,
        cmp_updated datetime NULL,
        cmp_updatedby varchar(10) NULL,
        cmp_record_version int(9) NOT NULL DEFAULT 1 ) ;

    -- We now create sp_maketrigger which is the stored procedure
    -- which will give us our trigger scripts

    DELIMITER $$

    DROP PROCEDURE IF EXISTS sp_maketrigger;

    CREATE PROCEDURE sp_maketrigger (IN s_tablename CHAR(30), OUT u_trigger_out VARCHAR(65500) CHARACTER SET ascii,OUT d_trigger_out VARCHAR(65500) CHARACTER SET ascii)
    BEGIN
        DECLARE s_fieldname VARCHAR(50);
        DECLARE u_trigger VARCHAR(65500) CHARACTER SET ascii;
        DECLARE d_trigger VARCHAR(65500) CHARACTER SET ascii;
        DECLARE s_key VARCHAR(50);
        DECLARE s_updatedby VARCHAR(50);
        DECLARE s_updated VARCHAR(50);
        DECLARE s_recversion VARCHAR(50);
        DECLARE done INT DEFAULT 0;
        DECLARE cursor_end CONDITION FOR SQLSTATE '02000';
        DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME FROM test_prepare_vw;
        DECLARE pri_cursor CURSOR FOR SELECT COLUMN_NAME FROM test_prepare_vw2;
        DECLARE upd_cursor CURSOR FOR SELECT COLUMN_NAME FROM test_prepare_vw3;
        DECLARE rec_cursor CURSOR FOR SELECT COLUMN_NAME FROM test_prepare_vw4;
        DECLARE CONTINUE HANDLER FOR cursor_end SET done = 1;

        DROP VIEW IF EXISTS test_prepare_vw;
        DROP VIEW IF EXISTS test_prepare_vw2;
        DROP VIEW IF EXISTS test_prepare_vw3;
        DROP VIEW IF EXISTS test_prepare_vw4;

        SET u_trigger = '';
        SET u_trigger = CONCAT('DELIMITER $$ \nDROP TRIGGER IF EXISTS tra_',s_tablename,'_update;\n');
        SET u_trigger = CONCAT(u_trigger,'CREATE TRIGGER tra_',s_tablename,'_update AFTER UPDATE ON ',s_tablename,' FOR EACH ROW \n');
        SET u_trigger = CONCAT(u_trigger,'BEGIN \n');
        SET u_trigger = CONCAT(u_trigger,'DECLARE msg VARCHAR(255); \n');

        SET d_trigger = '';
        SET d_trigger = CONCAT('DELIMITER $$ \nDROP TRIGGER IF EXISTS tra_',s_tablename,'_delete;\n');
        SET d_trigger = CONCAT(d_trigger,'CREATE TRIGGER tra_',s_tablename,'_delete AFTER DELETE ON ',s_tablename,' FOR EACH ROW \n');
        SET d_trigger = CONCAT(d_trigger,'BEGIN \n');

        SET @query = CONCAT('CREATE VIEW test_prepare_vw2 as SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = \'', s_tablename, '\' AND table_schema = \'db_diers\' AND COLUMN_NAME NOT LIKE \'%updated%\' AND COLUMN_KEY = \'PRI\' ORDER BY ORDINAL_POSITION');
        PREPARE stmt from @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        OPEN pri_cursor;
        FETCH pri_cursor INTO s_key;
        CLOSE pri_cursor;
        DROP VIEW test_prepare_vw2;

        SET @query = CONCAT('CREATE VIEW test_prepare_vw3 as SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = \'', s_tablename, '\' AND table_schema = \'db_diers\' AND COLUMN_NAME LIKE \'%updatedby%\' AND COLUMN_KEY <> \'PRI\' ORDER BY ORDINAL_POSITION');
        PREPARE stmt from @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        OPEN upd_cursor;
        FETCH upd_cursor INTO s_updatedby;
        CLOSE upd_cursor;
        DROP VIEW test_prepare_vw3;
        SET s_updated = LEFT(s_updatedby,(LENGTH(RTRIM(s_updatedby)))-2);

        SET @query = CONCAT('CREATE VIEW test_prepare_vw4 as SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = \'', s_tablename, '\' AND table_schema = \'db_diers\' AND COLUMN_NAME LIKE \'%record_version%\' AND COLUMN_KEY <> \'PRI\' ORDER BY ORDINAL_POSITION');
        PREPARE stmt from @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        OPEN rec_cursor;
        FETCH rec_cursor INTO s_recversion;
        CLOSE rec_cursor;
        DROP VIEW test_prepare_vw4;

        SET @query = CONCAT('CREATE VIEW test_prepare_vw as SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = \'', s_tablename, '\' AND table_schema = \'db_diers\' AND COLUMN_KEY <> \'PRI\' ORDER BY ORDINAL_POSITION');
        PREPARE stmt from @query;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        SET u_trigger = CONCAT(u_trigger,'   IF (ISNULL(NEW.',s_recversion,') OR OLD.',s_recversion,' >= NEW.',s_recversion,' OR ISNULL(NEW.',s_updatedby,') OR NEW.',s_updatedby,' = \'\' OR ISNULL(NEW.',s_updated,') OR NEW.',s_updated,' = OLD.',s_updated,') THEN \n');
        SET u_trigger = CONCAT(u_trigger,'      set msg = \'Cannot update record without specifying updated/updatedby by columns and without incrementing the record version.\'; \n');
        SET u_trigger = CONCAT(u_trigger,'      SIGNAL SQLSTATE \'45000\' SET MESSAGE_TEXT = msg; \n');
        SET u_trigger = CONCAT(u_trigger,'   END IF;     \n');

        OPEN col_cursor;

        FETCH col_cursor INTO s_fieldname;
        WHILE done = 0 DO
            SET u_trigger = CONCAT(u_trigger,'   IF (IFNULL(OLD.',s_fieldname,',\'\') <> IFNULL(NEW.',s_fieldname,',\'\') ) THEN\n');
            SET u_trigger = CONCAT(u_trigger,'     INSERT INTO tb_auditdetail (type, tablename, pk, fieldname, oldvalue, newvalue, updatedate, username, dbusername, machinename) \n');
            SET u_trigger = CONCAT(u_trigger,'     VALUES (\'U\', \'',s_tablename,'\', OLD.',s_key,', \'',s_fieldname,'\', OLD.',s_fieldname,', NEW.',s_fieldname,', CURRENT_TIMESTAMP,NEW.',s_updatedby,',CURRENT_USER(),@@hostname);\n');
            SET u_trigger = CONCAT(u_trigger,'   END IF;\n');

            SET d_trigger = CONCAT(d_trigger,'     INSERT INTO tb_auditdetail (type, tablename, pk, fieldname, oldvalue, newvalue, updatedate, username, dbusername, machinename) \n');
            SET d_trigger = CONCAT(d_trigger,'     VALUES (\'D\', \'',s_tablename,'\', OLD.',s_key,', \'',s_fieldname,'\', OLD.',s_fieldname,',NULL, CURRENT_TIMESTAMP,NULL,CURRENT_USER(),@@hostname);\n');

            FETCH col_cursor INTO s_fieldname;
        END WHILE;
        CLOSE col_cursor;

        DROP VIEW test_prepare_vw;

        SET u_trigger = CONCAT(u_trigger,'END;$$ \nDELIMITER ; \n');
        SET d_trigger = CONCAT(d_trigger,'END;$$ \nDELIMITER ; \n');
        SELECT u_trigger INTO u_trigger_out;
        SELECT d_trigger INTO d_trigger_out;

    END; $$

    DELIMITER ;

    -- And finally, to extract the Trigger Scripts

    call sp_maketrigger('tb_company',@s_line1,@d_line1);

    SELECT CONCAT(@s_line1,@d_line1)

    -- You just need to copy, paste and execute the trigger script, and
    -- voila, your audit is in place.

The above example takes it for granted that with each of your tables you have 5 columns: created, createdby, updated, updatedby, record_version.

However you can customise the Stored Procedure sp_maketrigger differently to suit your needs. The sp is also subject to enhancements and improvements.

Leonard Tonna

iLabMalta

www.ilabmalta.com


Can bad table-scan queries slow down nice index-covered queries at the same instance? Let's say we have an instance used by two clients: me and my friend Ivan. Each of us has his own database. Each of us is only reading.

I'm a bad guy, so I'm relentlessly shooting table scans at the db. My tables are also huge, so they don't fit into memory.
Ivan is a nice guy, so all of his queries are covered by an index, so he has no reasons to hit the disk at all.

Will he notice any problems?

AFAIK, I'll have a bunch of threads sitting there in SUSPENDED state waiting for disk, and not generating too much of a load otherwise, so Ivan should do just fine.

I can definitely say "Yes, your bad query can impact Ivan's query" due to resource competition at instance level.

I have multiple databases in one production sql server box, I have seen many times that a bad query (actually a SP) in one database that caused both CPU and I/O usage skyrocketed and impact all other user sessions connected to other databases.

Besides CPU and I/O usage, one particular case is the negative impact a bad query brings to TempDB performance, and when TempDB is heavily utilized, it is a direct hit to all other queries that need to use TempDB for their executions.

Isn't here where that the significant role of DBA come to picture by monitoring, analyzing and making corrective course of action to iron-out for the best use of resources?


EXCEPT operator vs NOT IN The EXCEPT operator was introduced in SQL Server 2005 but what is the difference between NOT IN and EXCEPT ?

Does it do the same? I would like a simple explanation with an example.

There are two key differences between EXCEPT and NOT IN.

EXCEPT

EXCEPT filters the DISTINCT values from the left-hand table that do not appear in the right-hand table. It's essentially the same as doing a NOT EXISTS with a DISTINCT clause.

It also expects the two tables (or subset of columns from the tables) to have the same number of columns in the left and right hand side of the query

For example, you cannot do:

SELECT ID, Name FROM TableA
EXCEPT
SELECT ID FROM TableB

This would result in the error:

  All queries combined using a UNION, INTERSECT or EXCEPT operator must
  have an equal number of expressions in their target lists.

NOT IN

NOT IN does not filter for DISTINCT values and returns all values from the left-hand table that do not appear in the right-hand table.

NOT IN requires you compare a single column from one table with a single column from another table or subquery.

For example, if your subquery was to return multiple columns:

SELECT * FROM TableA AS nc
WHERE ID NOT IN (SELECT ID, Name FROM TableB AS ec)

You'd get the following error:

  Only one expression can be
  specified in the select list when the subquery is not introduced with
  EXISTS.

However, if the right-hand table contains a NULL in the values being filtered by NOT IN, an empty result set is returned, potentially giving unexpected results.

EXAMPLE

CREATE TABLE #NewCustomers (ID INT);
CREATE TABLE #ExistingCustomers (ID INT);

INSERT INTO #NewCustomers
        ( ID )
VALUES
     (8), (9), (10), (1), (3), (8);

INSERT INTO #ExistingCustomers
        ( ID )
VALUES
        ( 1) , (2), (3), (4), (5);

-- EXCEPT filters for DISTINCT values
SELECT * FROM #NewCustomers AS nc
EXCEPT
SELECT * FROM #ExistingCustomers AS ec

-- NOT IN returns all values without filtering
SELECT * FROM #NewCustomers AS nc
WHERE ID NOT IN (SELECT ID FROM #ExistingCustomers AS ec)

From the above two queries, EXCEPT returns 3 rows from #NewCustomers, filtering out the 1 and 3 that match #ExistingCustomers and the duplicate 8.

NOT IN does not do this distinct filtering and returns 4 rows from #NewCustomers with the duplicate 8.

If we now add in a NULL to the #ExistingCustomers table, we see the same results returned by EXCEPT, however NOT IN will return an empty result set.

INSERT INTO #ExistingCustomers
        ( ID )
VALUES
        ( NULL );

-- With NULL values in the right-hand table, EXCEPT still returns the same results as above
SELECT * FROM #NewCustomers AS nc
EXCEPT
SELECT * FROM #ExistingCustomers AS ec

-- NOT IN now returns no results
SELECT * FROM #NewCustomers AS nc
WHERE ID NOT IN (SELECT ID FROM #ExistingCustomers AS ec)

DROP TABLE #NewCustomers;
DROP TABLE #ExistingCustomers;

Instead of NOT IN, you should really look at NOT EXISTS and there is a good comparison between the two on Gail Shaw's blog .

The NOT IN above fails because there needs to be a correlation between the predicates in the main query and the subquery. If you leave it out you get a UNCORRELATED subquery.

SELECT * FROM TableA AS nc
WHERE ID NOT IN (SELECT ID, Name FROM TableB AS ec where nc.ID = ec.ID)

EXCEPT is better and will handle any null rows without using IS NULL / IS NOT NULL predicates.


How do I place leading zeros for numbers less than 10 without affecting those 10+? Probably a newb question, but...

I have a table with a VARCHAR field containing numbers 1 through 300. I need to place a leading zero in front of any numbers less than 10 while retaining the original number if it is 10 or greater (i.e. 1=01, 10=10 and 300=300).

SELECT DISTINCT RIGHT('0'+CONVERT(VARCHAR,[FacilityCode]),3) FROM...

This returns 1=01, 10=010 and 300=300 (using the same examples as above)

EDIT: I'm trying to do this within a query so that I can do lookups without altering either table.

Can someone lend a brother a hand here? My brain just ain't working.

The safest way is probably to only add zeroes when the length of the column is 1 character:

UPDATE
   Table
SET
   MyCol = '0' + MyCol
WHERE
   LEN(MyCol) = 1;

This will cover all numbers under 10 and also ignore any that already have a leading 0.

EDIT

To just select the data try:

SELECT
   MyColPadded = CASE WHEN LEN(MyCol) = 1 THEN '0' + MyCol ELSE MyCol END
FROM
   MyTable;

declare @num varchar(50) = '7'
select case when @num<100
            then RIGHT('00'+ CONVERT(VARCHAR(50),@num),2)
            else CONVERT(varchar(50),@num) end


Take individual MySQL database offline MySQL does not have any SQL commands or internal mechanisms for

making an individual database unavailable / offline
moving an individual database

Thtis being the case, how can you take an individual database offline?

This may not be a suitable answer, but would achieve your goal...

What about, for the time you want it offline, renaming the database? This way, whatever application you have accessing the data would fail to connect to the database.

Moving the database can be achieved by

mysqldump -u user -p database -c > whatever.sql

Then import whatever.sql into another server, finally dropping the original DB.

This behavior could be achieved programmatically...

Create a table of databases and the status you would like them to be..  then your application can test the status of the database prior to use..   the application proceeds if the status is available and returns a message if unavailable.

You could also do this with applications in general too (same thing as described above but with application instead)...  You can then control applications use of the instance.


Mysql crashes in amazon linux We are trying to drop tables from mysql that is installed in amazon linux which the os is not able to handle and is very slow, mysql crashes as well or goes to the defunct mode. We have tried to execute the same on centos5.5 but the process was very fast and the mysql did not crash. The mysql configuration are the same on both the servers. Kindly tell us why this scenario happens ??.

Check the log file, where the error was written on disk or check in the event viewer log


Error ORA-12560 when trying to connect Windows SQL*Plus client to Oracle 11g Linux server I installed 11g on Centos VM on my mac.I have everything up and working on server.I have included following command I ran on cenos.

$lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 23-JUN-2014 14:49:41

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hayer)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                23-JUN-2014 14:03:58
Uptime                    0 days 0 hr. 45 min. 42 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/hayer/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hayer)(PORT=1522)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

My tnsnames.ora file is as follows:

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = hayer)(PORT = 1522))

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = hayer)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
      (SID = orcl)
    )
  )

~
~
~
~

On Centos server I can log in using sqlplus too. I changed tnsnames.ora on windows to same as server.I have added oracle host information in windows host file and I can ping oracle host and telnet host on port 1522.

I have disables service iptables so no type of firewall exists on centos.

But I still get following error on Windows client:

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jun 23 14:51:31 2014

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

Enter user-name: system as sysdba
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error

Enter user-name: system as sysdba
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error

On your Windows client, you need to specify the TNS alias @ORCL, or else it assumes you're trying to connect to a local database running on your Windows client, which there isn't one.  Also, system as sysdba generally isn't used, not even sure if that makes sense.  SYS as sysdba and just plain system are used.

Enter user-name: system@ORCL
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option

If you don't have tnsnames.ora set up on your Windows client, you can still connect via EZCONNECT string @"hayer:1522/orcl" instead of setting up tnsnames (yes, the quotes are needed).

Enter user-name: system@"hayer:1522/orcl"
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option

Try this:

Open Oracle Net Configuration Assistant
Select radio button option at "Local Net Service Name Configuration", then click "Next"
Select radio button option at "Reconfigure", then click "next"
Select the net service name you want to reconfigure, either ORACLR_CONNECTION_DATA or ORCL, then click "Next"
input your Service Name, if your service name of installed oracle as ORCL, then write ORCL to the field, then click "Next"
select the protocol you want to use, for example TCP, then click "Next"
input the hostname where your oracle database engine (service) is installed, for example your desktop or notebook.

Then fill in your port number (select option "Use the Standard port number" of 1521) or if use other port, fill in the port number.

Then click "next"
select "Yes", perform test, if necessary, change user logon

Then click "next".
if you don't want to configure another net service name, select "No" and then click "next"
Click "Finish"