How can I change default backup location for oracle11g using RMAN? I want to change to another drive due to lack of disk space.

How can I change the default location of backup files used by oracle to another drive using RMAN?

By default it's in the folder ORACLE_HOME from variables.

edit:

Found some links with problems like mine here, but they didn't help.

My script looks like this one:

run {
    allocate channel d1 type disk;
    allocate channel d2 type disk;
    backup
    format 'd:/backups/%U.bkp'
    database plus archivelog;
    release channel d1;
    release channel d2;
}

This backup never finishes.

Some things which might help:

Don't backup into a single file.

allocate CHANNEL d1 DEVICE TYPE disk maxpiecesize 32768M format some_backup_path

Use as many channels as you can stand.

allocate CHANNEL d2 DEVICE TYPE disk maxpiecesize 32768M format some_backup_path

allocate CHANNEL d3 DEVICE TYPE disk maxpiecesize 32768M format some_backup_path

allocate CHANNEL d4 DEVICE TYPE disk maxpiecesize 32768M format some_backup_path

RMAN parallelizes based on the number of channels you allocate; each channel takes part of the work to do.

Have faster disks - both on the system being backed up AND on the system to which backups are being written.  Are you backing up the database over the network?  If so, you're limited by the transmission speed of the network and competing network traffic - probably database-related activity.


SQL 2005 Permission Precedence Very simple question -
I have two database roles (Basic, Admin).  Say I explicitly deny the Basic role from deleting from table A and grant that to the Admin role.  If I am a user who is in both roles, can I delete from table A?

Never a simple answer...

For a direct DELETE, a user in both roles won't be able to DELETEDENY always has precedence when permissions are checked

For indirect via a stored procedure, the permissions may not be checked if both table and proc have the same owner. So both GRANT and DENY will be ignored. This is called "ownership chaining"

Personally, I don't really use DENY. Here's why:

In your case, you only need to GRANT DELETE to the Admin role. The Basic role needs neither DENY nor GRANT (but run REVOKE DELETE to remove the Basic DELETE permission)

The lack of a DELETE permission (by REVOKE) means a DELETE will fail for a Basic only user. DENY isn't needed to prevent this.
An Admin user (whether in both roles or not) has a GRANT so the DELETE will succeed. No DENY to block them


Postgresql on external HD I have a set of .csv files many times larger than my hard drive which I would like to load into database tables using postgresql.  (I must keep it locally on an external hard drive due to Data Use Agreement restrictions.)

I would like to tell Postgresql to store the tables I load on the external drive, due to size constraints.

I think I can do this with initdb -D '/Volumes/My Book/2020/', where /Volumes/My Book/2020/ is the path to the folder on the external drive.

I get the error: FATAL:  data directory "/Volumes/My Book/2020" has invalid permissions 2021-01-23 11:21:58.864 EST [4390] DETAIL:  Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).

But ls -l "/Volumes/My Book/" returns (among other lines) drwxrwxrwx@ 1 my_username  staff  1048576 Jan 19 13:29 2020 which looks like the right set of permissions to me.

I tried sudo chown my_username "/Volumes/My Book/2020/", but I get the same error as before.

I tried sudo chmod a+rwx "/Volumes/My Book/2020/", and still get the same error.

Can anyone advise me about how to set the permissions correctly?  (Or failing that: maybe I am taking the wrong approach to this problem?)
Thanks!

I'm the OP.  Thanks for the help from those above.  Here is what I ended up doing to solve this problem.  I think this is all of the steps but something could be missing.  (This is via Mac on Catalina.)  Happy to accept any corrections if I forgot a step.

Format the external drive AFPS or GUID via diskutility (using the "view all" command).  (This will delete everything on the drive, so you need a backup.)

After formatting, sudo chmod -R 0750 "/Volumes/your_external_hd_name/  from the command line - this will set the correct permissions.

initdb -D '/Volumes/your_external_hd_name/database/'     # creates a "database cluster"

pg_ctl -D /Volumes/your_external_hd_name/database/ start   # seems to run the cluster

NB: at the above step, if you get an error including the line "Is another postmaster already running on port 5432?", then run ps -ef | grep postmaster | awk '{print $2}' on the command line, followed by kill <pid1> ... <pidN> for any PIDs which show up.

psql postgres

CREATE DATABASE <database_name>;

\l                                                 # list databases

\c <your_database>                                 # connect to the database

\dt                                                # list tables

CREATE DATABASE <name>;                            # creates w/in the cluster

CREATE TABLE <table>;                              # include some columns

\dt                                                # to check that it was created,  should appear

If you need to undo/correct: DROP TABLE <name>;

\copy <table_name> FROM 'path/to/file';

\q # exit

 pg_ctl stop -D /Volumes/your_external_hd_name/  # seems to be important to prevent database from getting damaged, but I have never disconnected w/out it so I am not 100% sure.


What is the use of the transaction log buffer in SQL Server? I have a question like why do we use in transaction log buffer.

Like SQL Server puts the transaction log in the Transaction log buffer, when the checkpoint runs it transfers the dirty pages(committed) as well as logs to the disk (.mdf/.ldf).

So there must be no active log in the actual transaction log file, all the transactions are waiting for backup process.

Thanks in Advance.

Imagine a modification, for instance INSERT of a row.

SQL Server finds the page(s) that needed to be modified and brings them into memory (if not there already).

Then it constructs log records to reflect the modifications that is about to be performed. These are not written directly to the physical ldf file, for performance reasons. They are cached, in "log buffers".

Then the page(s) are modified, also in memory.

When the transaction is about to commit, SQL Server need to first "harden" the log recods. I.e., physically write the log buffers to the ldf file, before the transation is committed. I.e., a synchonous write, seen from the user's perspective.

The actual data pages will be written at a later time. For instance when checkpoint occurs.


Are triggers and their results visible in same order as original operations? I've created simple trigger

CREATE TABLE data (
    id int,
    v1 text,
    v2 text
);

CREATE TABLE events (
    id serial primary key,
    v1 text
);

CREATE OR REPLACE FUNCTION emit_event_for_v1()
RETURNS trigger AS '
    BEGIN
        IF OLD.v1 != NEW.v1 THEN
            INSERT INTO events (v1)
            VALUES (NEW.v1);
        END IF;
        RETURN NULL;
    END
' LANGUAGE 'plpgsql';

CREATE TRIGGER event_v1 AFTER INSERT OR UPDATE ON data
FOR EACH ROW EXECUTE PROCEDURE emit_event_for_v1();

INSERT INTO data VALUES (1, '', '');

After that, I've launched many clients (25) doing many updates (500 each) on the
data.

UPDATE data SET v1 = $1 WHERE id = 1

with $1 being $WORKER_ID : $ITER_ID. In the end I've verified that last
inserted event (SELECT id, v1 FROM events ORDER BY id DESC) matches current
state of data (SELECT v1 FROM data WHERE id = 1).

I've also checked that id of last event is equal to number of updates
(25 * 500). And it is.

My question is, is this guaranteed to work? Are results of inserts in triggers
always visible in same order as the original update that triggered the trigger?
Or did I just get lucky during my testing (like 50 times in a row)?

Yes, that is guaranteed, because there is no chance that the trigger function for two updates of the same row will be executed at concurrently.

The UPDATE will get an exclusive lock on the row, and the trigger function will run after the update. The transaction (that holds the exclusive lock) will not complete until the trigger has run, so no concurrent UPDATE can get the lock and start running until the previous one is done.

An exception would be if you had defined the sequence with the CACHE clause, so that backends can cache sequence values, but you didn't do that.


MySQL Server :: My C Code can remotely create a database, but not "INSERT INTO" it I’ve written a C program which does the following when it starts:

Connects to a remote Linux MySQL server over the network
Creates a new, uniquely-named database
Sends a “USE _____” command to specify we want to use that database
Creates a single table within the database

Here’s examples of the actual commands my code is sending:

(no command sent to initiate connection)
"CREATE DATABASE IF NOT EXISTS myDB_2019424_205718;"
“USE myDB_2019424_205718;”
“CREATE TABLE IF NOT EXISTS myTable ( timestamp VARCHAR(15) ,
Column1 VARCHAR(8) , Column2 VARCHAR(4) , Column3 VARCHAR(4) );”

All of the above works great.  The trouble is, once the database is set up and ready, I need the C program to crunch a lot of numbers on-the-fly, and periodically send “INSERT INTO” commands to the server.  Here’s the first “INSERT INTO” command my code sends:

INSERT INTO myTable VALUES ( '1556139439', NULL, NULL, NULL );

When my code sends its first “INSERT INTO” command, the server immediately sends back an error and my code reports “****** MySQL server has gone away ******”

I Googled that error, of course, and I see from posts like this that the two possible culprits are a low timeout or small packet threshold.  So I’ve reset those to maximum on my server:

mysql> SELECT @@wait_timeout;
+----------------+
| @@wait_timeout |
+----------------+
|          28800 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT @@max_allowed_packet;
+----------------------+
| @@max_allowed_packet |
+----------------------+
|            107373568 |
+----------------------+
1 row in set (0.00 sec)

mysql>

But still no luck.  Worse, when I examine /var/log/mysql/error.log after a failed session, I see no error message recorded.

One more clue…  I’ve tried manually logging into the MySQL server and pasting these commands in one-by-one.  All commands are accepted on the manual interface.  There’s something buggy when my code sends the “INSERT INTO” command, and I can’t figure out what.  Has anyone seen this before?

Some system information…  My C code is running on an Ubuntu container, version 16.04.3 LTS.  I’ve installed the latest mysql library.  The SQL server is running on an identical container; the actual version of MySQL Server is Ver 14.14 Distrib 5.7.25.

Sooo… what could be the problem here?  I’ve included an excerpt of my C code below.  (The full code is too long and confusing to include here.)  Any suggestions or advice will be appreciated.

User error, my apologies.  I had put a "close_connection()" line in my code for troubleshooting purposes, then forgot about it.  This wasn't a problem until I added an "INSERT INTO" command after the "close_connection()".  So of course the connection was closed when my code tried to INSERT.

The online documentation for the "**** MySQL server has gone away ****" error assumes that the sending code wasn't closed the connection, which is what confused me.  Now I know...


Trouble setting up mirroring on SQL Server 2016 I've setup a test SQL Server 2016 server.  I've installed 2 instances, restored a backup on the primary.  Then restored a backup on the secondary with NO RECOVERY, then restored the transactional log on the secondary, also with NO RECOVERY.

I then followed the prompts in the Mirroring Wizard off the Database Properties page and ended up getting an error:  Database 'Ares' cannot be open.  It is in the middle of a restore.

What am I missing?

For information, I had the same issue.

This is a bug in SSMS 2016, the easiest solution if to connect using an older version of SSMS (Like 2014) to run the mirroring wizard and it works like a charm.

You can also set it up using command line but it's more complicated.


Organize repeating timestamped values in schema I am creating models to hold some financial data. The information frequently is split into timestamped values, e.g:

Some value, now  |  as of Jan 1st  |  as of Jun 1st  | as of Sep 1st

There are four timeframes per value; for each of them values will differ, but conceptually mean the same thing. I have around 30 of different 'timeframed' values to store. Timeframes in each case are usually the same, but technically can change. There is also some not timestamped stuff to store in addition to the above.

How to model this into db schema in order to keep it clean, elegant and efficient? Right now it's hard to anticipate the way this data will be used and what type of queries will be most frequent.

I'm considering flat schema to make querying as easy as possible (no joins), so the columns would be:

id  |  value_now  | value_now_date  | value_previous  |  ...

... but the model gets huge and I'm tempted to add separate table instead, having columns:

id  |  FK to main model  |  value_name  | date_frame

Are there significant benefits of such approach? Or the previous one? Are there other options I should consider?

I'd go with the 2nd approach.
Don't be afraid of Join(t)s.
Here are some reasons

Fast enough. Chances are extremly low that you'd rely on the additional speed you gain from a flat structure, if there is any. It's not worth such an ugly hacky design
More flexible. You can add a 5th timeframe per value later and don't end up with empty columns
Less redundancy. (see following paragraph)

You should consider taking the Value names and addition value
   properties (unit, measurement device, measurement method, etc.) into
   a 3rd table and link both it with main model over the timeframe and
   the value.
This is because the value name is a relatively huge string that is stored for every timeframe. Storing only a 4 byte integer (FK) is more efficient and flexible.


Merge Replication customize synchronization cycle and its performance I'm trying to implement Merge Replication between just two servers, a publisher and one Subscriber.
Assume these tables :
--Main table in Publisher
Ticket (id, userId, reqId, blockedDate, usedDate, returnDate, transferDate, ...)

--On Subscriber (The fields are in Publication)
Ticket (id, userId, blockedDate, usedDate)

We must assign current date to transferDate whenever a row goes to Subscriber, and assign to returnDate whenever it comes back from Subscriber. As we HAVE to consider a file syncing (transfer/return data to/from subscriber with a USB stick!) these fields must be assigned on syncing either on file syncing or on replication.
I know you can add your custom business logic for Conflict Resolution (when a conflict occurs). I'm looking for something like triggers for replication events, which gain me the ability to manipulate some fields out of Publication (or even another table whenever needed).
Questions

Is it possible to handle replication events(push and pull) manually?**
(customize or override PULL/PUSH's. Not conflict resolver)**

Can you give me a comparison between implementing a customized merge replication and syncing with help of a distributed transaction SP?

I need synchronization to be scheduled (at midnight when there's no load on DB server), BUT it is highly needed we'll be able to sync manually (from within our web application).
In second question I need their performance, reliability, and of course their maintainability. These factors especially the first two, are a big concern. Suppose Publisher may have about millions of records, and Subscriber may have about several ten thousand. So with each synchronization, about several ten thousand records will be transferred.
Merge replication checks rows; if there's a change and there's not a conflict, the row merges. if there's a conflict, a conflict resolver (could be your custom stored procedure resolver) handles the conflict. I want to write my own logic in the first case (data changed without conflict) instead of SQL's built-in merge mechanism. Clearly I want to customize it.
There's onlyid, userId, blockedDate, usedDate columns in the article. I want to fill returnDate whenever a Ticket comes back to Publisher (on a pull) and fill transferDate whenever a Ticket goes to Subscriber (on a push).

To customize the merge process, you'll need to implement a custom business logic handler (requires .NET code - cannot be implemented using TSQL). See Execute Business Logic During Merge Synchronization in the documentation. It is available in SQL Server 2012 and later.

Override methods Update, Insert and Commit to transform changed rows, and conflict resolver method to modify conflict logic. Can't use T-SQL, must be done with .NET code. The main limitation is that each overridable method is called once for every row (like SSIS row transformations), to the best of my knowledge there isn't a method called on all changed rows.

There are some workarounds to handle your case, but this the general solution.

This is the BOL C# template:

using System;
using System.Text;
using System.Data;
using System.Data.Common;
using Microsoft.SqlServer.Replication.BusinessLogicSupport;
using Microsoft.Samples.SqlServer.BusinessLogicHandler;

namespace Microsoft.Samples.SqlServer.BusinessLogicHandler
{
public class OrderEntryBusinessLogicHandler :
  Microsoft.SqlServer.Replication.BusinessLogicSupport.BusinessLogicModule
{
    // Variables to hold server names.
    private string publisherName;
    private string subscriberName;

    public OrderEntryBusinessLogicHandler()
    {
    }

    // Implement the Initialize method to get publication
    // and subscription information.
    public override void Initialize(
        string publisher,
        string subscriber,
        string distributor,
        string publisherDB,
        string subscriberDB,
        string articleName)
    {
        // Set the Publisher and Subscriber names.
        publisherName = publisher;
        subscriberName = subscriber;
    }

    // Declare what types of row changes, conflicts, or errors to handle.
    override public ChangeStates HandledChangeStates
    {
        get
        {
            // Handle Subscriber inserts, updates and deletes.
            return ChangeStates.SubscriberInserts |
              ChangeStates.SubscriberUpdates | ChangeStates.SubscriberDeletes;
        }
    }

    public override ActionOnDataChange InsertHandler(SourceIdentifier insertSource,
      DataSet insertedDataSet, ref DataSet customDataSet, ref int historyLogLevel,
      ref string historyLogMessage)
    {
        // custom logic for updates
    }

    public override ActionOnDataChange UpdateHandler(SourceIdentifier updateSource,
      DataSet updatedDataSet, ref DataSet customDataSet, ref int historyLogLevel,
      ref string historyLogMessage)
    {
 // custom logic for updates
  }
    public override ActionOnDataDelete DeleteHandler(SourceIdentifier deleteSource,
      DataSet deletedDataSet, ref int historyLogLevel, ref string historyLogMessage)
    {
     // custom logic for deletes
}
}


Export SQL Server varbinary field to string using Visual Basic script in SSIS? I'm using SSIS to export data from a set of tables to flat files. The export is running great except for one table with a varbinary field. The export is running a Visual Basic script task to load the data into a DataTable object, then spinning through each row and writing the values out to a flat file. Here's the loop that gets the data from the DataTable and writes it out:

For Each row As DataRow In dataTable.Rows
    delim = ""
    builder = New System.Text.StringBuilder
    For Each col As DataColumn In dataTable.Columns
        ' By default, bit fields are exported as boolean (True or False).
        ' Cast them to int to avoid errors on import.
        If col.DataType Is GetType(Boolean) And Not row.IsNull(col.ColumnName) Then
            builder.Append(delim).Append(IIf(row(col.ColumnName), 1, 0))
        ElseIf col.DataType Is GetType(Byte) And Not row.IsNull(col.ColumnName) Then
            If isUnicode Then
                builder.Append(delim).Append(New UnicodeEncoding().GetString(row(col.ColumnName)))
            Else
                builder.Append(delim).Append(New ASCIIEncoding().GetString(row(col.ColumnName)))
            End If
        Else
            builder.Append(delim).Append(row(col.ColumnName))
        End If
        delim = delimiter
        Next
    writer.WriteLine(builder.ToString())
    Next

When I run my SSIS package, I get this error:

  System.InvalidCastException: Unable to cast object of type
  'System.Byte' to type 'System.Byte[]'

I thought that Visual Basic interprets the varbinary field as a Byte array, so why am I getting a cast error?

On line 9, instead of GetType(Byte) you need GetType(Byte()).

Encoding.GetString only takes a byte array; never a single byte, so regardless of the type provided by the framework, what's there is logically not going to work.

If there is another field in the table that comes back as a Byte, you'll need to add another branch to the If statement to handle the encoding (or convert the single byte to a byte array of length 1 and use common encoding code).


PostgreSQL - VACUUM FULL does not free space back to the OS On a Postgres 9.1 installation, I'm trying to reduce the amount of Postgres' disk usage by dropping records from tables and then running VACUUM FULL on those tables. My understanding of VACUUM FULL is that it should give disk space back to the OS, but I'm finding that this doesn't consistently happen. For one of the tables, the DELETEs and VACUUM FULL ran successfully, and the pg_database table reports a smaller size, but the disk space actually increased slightly. I can see why disk usage goes up temporarily during a VACUUM FULL operation, but the disk usage doesn't go back down afterward.

My question is, why is the disk space not being freed back to the OS after the VACUUM FULL completes? Is there something I'm missing?

edit (for posterity) This is running on CentOS 6.3, and WAL archiving is enabled.

Craig's comment was spot on--the WAL files were where the space was going. An earlier-than-usual rm gave us the space back.


Why are breakpoints not being hit in SQL Server Management Studio? SQL Server Management Studio allows debugging of SQL (breakpoints and stepping through the SQL). It is possible to step in to a stored proc being called and to see a call stack. However I cannot determine how to set a breakpoint on a stored proc that will be called from those statements, that is, I can set a breakpoint on the top level SQL statements that I run that call on to sub-procs, but any breakpoints in the sub-procs are not hit.

UPDATE:

When debugging the 'breakpoints' view shows a warning symbol against the problematic breakpoints with a tooltip text of 'The breakpoint will not currently be hit. Unable to bind SQL breakpoint at this time. Object containing the breakpoint not loaded'.

If however I step into a stored proc with the debugger and place breakpoints on the view that it has opened then the breakpoints are bound correctly. So my question is, how do I get to to a view that allows me to bind breakpoints to stored procs correctly? It seems that right clicking on a stored proc and selecting 'Modify' gets you to a view that is not bound to the debugger.

It is because you need to create the breakpoint using the database name and object id rather than setting a breakpoint in a random SQL file which is what you get by choosing modify.

See:

https://sqlserverfunctions.wordpress.com/2014/09/12/setting-breakpoints-in-ssms/

Ed


PostgreSQL 10 Clean Up After Restore I run postgres server (10.6) on my pc just for some custom programs. Problem is every time I restore a backup file of 600mb - the pg_wal directory increases in size for 600mb. Is there a way to easily restore + get rid of the wal files?

I have read about pg_archivecleanup utility but I don't think it helps in my case.

EDIT:
Alternatively, is it possible at all to restore without recreating wal files as I don't need them. What will be the config entries for this.

The files should go away on their own after a checkpoint or two (if there is little activity, it might take longer for the files to go away.  If they were renamed already in anticipation of being recycled back when the server was very active, they won't go away until they get re-used).  If you don't want it to use up to 1GB of WAL, then you should lower "max_wal_size".  Otherwise, it might do what you gave it permission to do.


Query to return all Oracle Deadlocks in 12c Is there an object I can query in 12c to return all ORA-60 Deadlock alerts?

I just want to do a weekly report that outputs the number of deadlocks that have occurred and what schema they occurred on.

My Oracle knowledge is not great, and I have drawn a blank when searching for resources on this online.

Thanks

Sure:

select
  ss.value
from
  v$sysstat ss
  join v$statname sn using (statistic#)
where
  sn.name = 'enqueue deadlocks'
;

I did not test it extensively though.


How to extract pipe-delimited data using BCP? I tried the following command

USE <DBNAME>
SELECT 'exec master..xp_cmdshell'
+ ' '''
+ 'bcp'
+ ' ' + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME
+ ' out'
+ ' D:\'
+ TABLE_NAME + '.csv'
+ ' -c'
+ ' -t,'
+ ' -T'
+ ' -S' + @@SERVERNAME
+ ''''
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

I want to get the data in following format.

Format will be Pipe delimited text file, with double quote text qualifiers and no header row. How to modify or write the BCP command to get the required result.

My dataset is just a customer table.

CustomerID  CustomerName         ContactName   Address        City    PostalCode  Country
1           Alfreds Futterkiste  Maria Anders  Obere Str. 57  Berlin  12209       Germany

Required data format is

1|"Alfreds Futterkiste"|"Maria Anders"|"Obere Str. 57 Berlin"|12209|"Germany"

Now that I better understand that you are trying to extract data, here is a new answer. This just simply extracts data from a table that I created in tempdb.

use tempdb

-- drop table mytable

create table mytable
(id int, customer_name varchar(55), cityname varchar(55), statename varchar(55))

insert into mytable
values (1, 'a', 'a city', 'a state')
     , (2, 'b', 'b city', 'b state')
     , (3, 'c', 'c city', 'c state')
     , (4, 'd', 'd city', 'd state')

select cast(id as varchar(10)), customer_name, cityname, statename from mytable

Which results in the following bcp command

bcp "select cast(id as varchar(10)), customer_name, cityname, statename from mytable" queryout c:\temp\myTable.csv /S.\instance_name /d tempdb /c /t"|" -T

Update This bcp command will add double quotes

exec Master..xp_Cmdshell 'bcp "select cast(id as varchar(10)), quotename(customer_name,char(34)), quotename(cityname,char(34)), quotename(statename,char(34)) from mytable" queryout "c:\temp\myTable.csv" /S.\instance_name /d tempdb /c /t"|" /T'

or just with bcp

bcp "select cast(id as varchar(10)), quotename(customer_name,char(34)), quotename(cityname,char(34)), quotename(statename,char(34)) from mytable" queryout "c:\temp\myTable.csv" /S.\instance_name /d tempdb /c /t"|" /T

This bcp command uses the quotename function. Which allows you to delimit your output using whatever characters you like.


MS SQL Server 2008 Transaction log file back up failure I've done some reading around this subject here and on StackOverflow but nothing really answers my question enough for me to go forward.

I'll explain we have a MS SQL 2008 server running, the transaction log file backup has been failing every night, now I've had a look at some of the other errors we've had, long and the short of it, it looks like this could be disk or I/O or so I'm informed, as this is a hosted machine we've initiated a change but that takes time and Sods law means the closer you are to making a change the more likely something catastrophic will happen. ie I need to make sure we're safe.

I'm not a DBA (If I was I probably wouldn't be asking questions).

Now as I understand it, if the transaction log backup fails when you are on a Full recovery model, then you're stuffed.

So I've changed to a simple recovery model (I appreciate the implications interms of point in time better we have a backup than one that will fail on restore) as I understand it a Simple Model writes everything to the database during the backup, what I'm not sure of is, what this really means in terms of the transaction log. ie if the database died this moment and I had to restore from a backup, but the transaction log backup fails what this means to me. We have a backup plan for the DB and the logs. We know the logs backup fails, but if it's in simple recovery mode and the database is backed up but the log back up fails would I be able to recover the database? By recover lets get this straight, I'm talking about getting the thing working again by simple restoring from my last back up?

Now to the 2nd part, if we assume there is a problem with a sector on the disk (Appreciate I need to move as soon as I can, but new machine etc etc are out of my sphere of influence), then it occurs to me that deleting and recreating a log file won't work as the file is likely to be in the same part of the disk. So wouldn't creating new log file and removing the old log file (but leaving the old file on the disk) be the thing to do? As that would effectively stop the bad sector being used (Different partition would also solve this, just this is a C: and D: affair and space is tight on C:.

So if so, how do I do this? I guess I'm really asking for step by step here as I'm unsure of all implications, I've read elsewhere that you will be in a whole world of pain if you just delete a log file by say stopping the service deleting the file and recreating new file same name and starting the service. So what do I do?

Really appreciate all comments

Thanks in adavance

Have you tried running a full backup followed by another transaction log backup?  What error messages are you getting?

  We know the logs backup fails, but if it's in simple recovery mode and
  the database is backed up but the log back up fails would I be able to
  recover the database? By recover lets get this straight, I'm talking
  about getting the thing working again by simple restoring from my last
  back up?

If the database fails, you can recover the database with the last full backup regardless of whether it's set to full or simple recovery.  Simple recovery doesn't support log backups at all.  So basically, you'd lose everything from your last full backup forward.

  So wouldn't creating new log file and removing the old log file (but
  leaving the old file on the disk) be the thing to do?

NO.  DO NOT DELETE YOUR LOG FILE.  You'll probably kill your database and the symptom you're trying to fix is that it won't back up properly.

I'm going to recommend this article, "A beginner’s guide to SQL Server transaction logs."  Especially this bit:

  Can SQL Server database work without a transaction log?

  No, that is not possible due to the SQL Server design and ACID
  compliance. ACID stands for atomicity, consistency, isolation, and
  durability.

Also this article on Full vs. Simple recovery.

To crib from this article:

Run dbcc checkdb on the database
Stop all user activity in the database
Switch to the SIMPLE recovery model (breaking the log backup chain and removing the requirement that the damaged portion of log must be backed up) (which you've done, yes)
Switch to the FULL recovery model
Take a full database backup (thus starting a new log backup chain)
Start taking log backups


Accessing a SQL Server Denali CTP1 database with Management Studio 2012 SP1 Express I had Management Studio 2011 Denali CTP1 installed before but I have uninstalled it.

Now I have installed the Management Studio 2012 SP1 Express without any database instances.

I have a webserver with a few SQL server instances, one of them was installed by the Denali CTP1 installer. I'm able to connect to it wothout problems with a Denali CTP 1 SSMS but not with the 2012SP1.

When I connect with SSMS2012SP1 I immediately get the error:

  Invalid object name 'sys.database_filestream_options'. (Microsoft SQL Server, Error: 208)

Although I'm connected to the database (and able to create or modify users for example), nearly every item throws some error when I expand it.

For example expanding "Databases" throws:

  Invalid column name 'synchronization_state'. (Microsoft SQL Server, Error: 207)

"Some authorization problem" - I thought. But after creating a new user and setting every possible right to it and trying to logging in with it, there is no difference.

I'm not able to figure out the problem.

I can access the database through code just fine, here is my connectionstring:

server\web11;Initial Catalog=a_database;Persist Security Info=True;Trusted_Connection=True;

So I figured that this is something Management Studio does.

You'll want to upgrade the database to the full version of SQL Server 2012.  I'm not surprised that the beta database engine doesn't work with the full release of the management tool.  There's no guarantee that beta and RTM products will work together.


MariaDB CONNECT storage engine I have a MariaDB running on one server that contains a particular table that I want somehow to have access to on another server on the same private network.The second server is also running MariaDB. Can this be achieved with the CONNECT storage engine? If so, can you point out any examples of how to do it? Thank you!

Yes, this is possible:

First install the package. On Redhat and friends, run this command:

sudo yum install MariaDB-connect-engine

Or on Debian/Ubuntu and friends:

sudo apt-get install mariadb-plugin-connect-engine

(Or should that say 'mariadb-plugin-connect'?)

Then log in to MariaDB and install the plugin:

INSTALL SONAME 'ha_connect';

Now create the CONNECT table locally:

CREATE TABLE my_connect_table (
   col1 type1 -- same columns as in your remote table
   ...
)
ENGINE=CONNECT TABLE_TYPE=MYSQL dbname=the_db_name tabname=the_table_name
CONNECTION='mysql://the_username:the_password@the_hostname';

Now you can SELECT, INSERT, UPDATE, DELETE etc. with that table as you wish. If you DROP this table, then you only DROP the local CONNECT table, not your remote table.

For reference, see:
CONNECT MYSQL Table Type: Accessing MySQL/MariaDB Tables

You might also want to consider the MariaDB Spider storage engine. The Connect engine can use any remote database system (Oracle, MSSQL, PostgreSQL, ...) if you install the ODBC driver, or it can even be e.g. a CSV file instead of an actual database system. The Spider engine is only for remote MariaDB databases. However, the advantage of Spider is that it has sharding features (i.e. allows distributing the data in a table across multiple servers) and supports XA transactions.


Mysql Forcing close of thread 946 user My mysql box keeping shutting down and up. Below is snippet of the log file. There is quite a number of places I notice this Forcing close of thread 946  user:

130426 12:36:28 [Note] Event Scheduler: Purging the queue. 0 events
130426 12:36:28  InnoDB: Starting shutdown...
130426 12:36:31  InnoDB: Shutdown completed; log sequence number 0 1111309
130426 12:36:31 [Note] /usr/libexec/mysqld: Shutdown complete

130426 12:36:31 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
130426 12:36:32 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130426 12:36:32  InnoDB: Initializing buffer pool, size = 8.0M
130426 12:36:32  InnoDB: Completed initialization of buffer pool
130426 12:36:32  InnoDB: Started; log sequence number 0 1111309
130426 12:36:32 [Note] Event Scheduler: Loaded 0 events
130426 12:36:32 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.67'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
130426 21:05:19 [Note] /usr/libexec/mysqld: Normal shutdown

130426 21:05:19 [Note] Event Scheduler: Purging the queue. 0 events
130426 21:05:21 [Warning] /usr/libexec/mysqld: Forcing close of thread 946  user: ''

130426 21:05:22  InnoDB: Starting shutdown...
130426 21:05:23  InnoDB: Shutdown completed; log sequence number 0 1111309
130426 21:05:23 [Note] /usr/libexec/mysqld: Shutdown complete

130426 21:05:23 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
130426 21:05:25 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
130426 21:05:25  InnoDB: Initializing buffer pool, size = 8.0M
130426 21:05:25  InnoDB: Completed initialization of buffer pool
130426 21:05:25  InnoDB: Started; log sequence number 0 1111309
130426 21:05:25 [Note] Event Scheduler: Loaded 0 events
130426 21:05:25 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.67'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
130428  0:47:12 [Note] /usr/libexec/mysqld: Normal shutdown

130428  0:47:12 [Note] Event Scheduler: Purging the queue. 0 events
130428  0:47:12  InnoDB: Starting shutdown...
130428  0:47:16  InnoDB: Shutdown completed; log sequence number 0 1111309
130428  0:47:16 [Note] /usr/libexec/mysqld: Shutdown complete

This seems to be one of those errors that won't go away. In this case, it may be a case of human error.
First of all, here are a series of links with similar error messages on Shutdowns

http://www.webhostingtalk.com/showthread.php?t=518682
http://bugs.mysql.com/bug.php?id=7403
http://bugs.mysql.com/bug.php?id=48879
http://lists.mysql.com/mysql/206094
http://dev.mysql.com/doc/refman/5.6/en/error-log.html (See User Comments)
http://www.mail-archive.com/mysql@lists.mysql.com/msg12324.html

They all have seem to have one thing in common: MySQL for Windows.
It was claimed that Bug 7403 (the second link) was fixed and patch implemented July 14, 2005: http://lists.mysql.com/internals/27091
The versions of MySQL that this fix should be in 4.0, 5.0.40, 5.1.42. Looking in the error log you posted, I see you are using MySQL 5.1.67. Apparently, no one knows how to fix this issue or wants to (http://bugs.mysql.com/bug.php?id=7403) I also noted that the error messages indicate that you are using MySQL in Linux already.
I have three(3) suggestions that range from the most passive to the most aggressive...
SUGGESTION #1
Try setting log_warnings to 0, if you don't mind flying blind on warnings.
SUGGESTION #2
Upgrade to MySQL 5.6.11. Hopefully, there is should be more updates and bug fixes for this.
SUGGESTION #3
Move the Database to Linux. There should be more stability for this problem away on a better machine with the most updated kernel for the OS.
Give it a Try !!!
UPDATE 2013-04-30 11:20 EDT
Here is the fastest way to check for table corruption


Check Constraints on Decimals In SQL Server 2012 I have the following check constraint on an NVARCHAR(255) column:

([weekReviewWk10]='A' OR [weekReviewWk10]>=(0) AND [weekReviewWk10]<=(25))

However, when someone enters, say, 23.5, SQL Server rejects the input. How can I fix this? I know it is because it is testing things as ints, but how do I get it to test the entry as a decimal?

The column has to be NVARCHAR since, as you can see, I also need my users to be able to enter 'A'.

You could use TRY_CAST to cast the column to a decimal format before comparing.

weekReviewWk10='A' OR
TRY_CAST(weekReviewWk10 AS decimal(3, 1)) BETWEEN 0.0 AND 25.0

If the cast fails, the result of TRY_CAST will be NULL and the comparison is false, but it won't crash. TRY_CAST requires SQL Server 2012 or newer.

It's worth mentioning that you're potentially opening up a pit of bad things when you allow text values in a numeric column, but judging from the question, you already know that. :)


Is db_executor a standard role in SQL Server 2012 R2? In trying to figure out the exact permissions I need to give a user to execute all stored procedures I ran across many pages saying to create a role of "db_executer" and give that role the GRANT EXECUTE permission, then assign users to that role. When I when to my sever to make that role I found out it already exists. Since I inherited this server I am not sure if it was made by the last admin, a program, or is a built in. Searching Google I am not finding any relevant hits newer then 2010 and those all talk about how to make the role. Can anyone tell me if this is a default role in 2012 or if someone/thing else made it?

db_executor is not a default role (MS has a list of default roles), it would have been created by somebody.

There's also no SQL Server 2012 R2. Potentially you are getting confused with Windows Server 2012 R2.


What is the underlying representation of BLOB data when I export it? When I export a blob object to an SQL file, I get some thing like this for a blob field:

0x4f3a383a227374644.....,

Questions

I would like to know how the BLOB is actually converted to the above format?
What is its underlying representation?

If you use mysqldump, you can specify the hexadecimal format for BLOB data

mysqldump -u... -p... --hex-blob --all-databases ...

Here is the help on hex-blob in mysqldump

$ mysqldump --help | grep -A 1 "blob" | head -2
  --hex-blob          Dump binary strings (BINARY, VARBINARY, BLOB) in
                      hexadecimal format.


Can a maintenance workload after a SQL Server reboot cause SQL outage or issue when application load resumes? Configuration:

Hardware: HP ProLiant DL980 G7
CPU: 4 Xeon X6550 @ 2.00GHz
Memory: 393,206 MB
SQL Version/Ed, OS: SQL Server 2008 Enterprise Ed (SP1) - 10.0.2757.0 (X64)
Windows: Microsoft Windows Server 2008 R2 Enterprise

Recently our primary SQL Server (on a cluster) experienced an outage on a Monday morning. Over the weekend work had been done (to upgrade NIC drivers & firmware). The server failed over and rebooted and failed over again.

Everything seemed to be working just fine through Sunday; Clean logs for the light load of work & maintenance that is performed.  However, on Monday morning, as application and DB activity ramped up the database experienced some blocking that caused connections to spike to the thousands. As the connections spiked there was memory pressure, the blocking got worse, and connections to the database started to fail and timeout. I was engaged while the blocking was peaking and tried to kill some SPIDs, but we decided to fail over to return to service more quickly.

My question is: could the RE-INDEX maintenance workload after the reboot fill the query plan cache and create memory pressure and blocking when the application workload resumes on Monday morning?

Our application service windows reflect operating hours, so we have a flood of activity as customer systems come online M-F 7am to 8pm. If the application timeouts lead to aggressive log-in attempts and a spike in database connections could we be creating a self-inflicted Denial-Of-Service on the database which leads to more memory pressure, lack of threads to do work and eventually bringing down the database?

Has anyone seen behavior like this before in large memory systems where application workload occurs in very specific windows?

The only two errors found occur 30 minutes after the application server reported connections failing:

Message: Error: 18056, Severity: 20, State: 29.
The client was unable to reuse a session with SPID XXX,
which had been reset for connection pooling. The failure ID is 29.
This error may have been caused by an earlier operation failing.
Check the error logs for failed operations immediately before this error message.

If the maintenance tasks were complete, then the resources would be free. If not, then you'll have blocking (unless you have online index rebuilds etc)

My random guess is some client command timeout, which issues an ABORT, which left locks and transactions. Your node failover is the same as closing all connections. The timeout could have been caused by cold cache, no cached query plans etc

Saying that, I have seen brain dead polling process overload a database by opening and closing connection 1000s of times per second. And similar. Or one client needs rebooted , normally indicated by ASYNC_NETWORK_IO waits.

Can you reproduce it though? Which will be tricky on a live system

See SQL Server Transaction Timeout for more (both answers)


Manipulate SQL statement before execution on server Our databases are on SQL Server 2000. We are migrating to SQL Server 2008 R2.
We want to update the compatibility level to 100.

But the join operator \*= and =\* are no more supported with that compatibility level.
The advised solution is to update our source code and change the operators to OUTER JOINS. But we cannot afford the modification of thousand .exe and .dll. So we cannot update our source code; there are too many projects.

What I want to accomplish, in SQL Server 2008 R2, is the following:

Catch the SQL statement before its execution ?
Update the statement (i.e., replace *= by LEFT OUTER JOIN) ?
Give back the modified statement to the SQL engine for execution ?

This artifice is really not advised, but it will allow us to set the compatibility level to 100 event to our program still use the old syntax.

Thanks

I posted the same question on stackoverflow before I found this specific site.
https://stackoverflow.com/questions/27320554/manipulate-sql-statement-before-execution-on-server

So you need to make a choice:

Change to Compatibility Level = 100 and break your code.
Set Compatibility Level = 80 and your code will live a little longer.
Find some way to globally edit your code, dynamically or statically, so that you can switch to Compatibility Level = 100.

It would be interesting to understand why you want Level = 100 when you do not want the code to change.

If you install SQL Server 2008 R2 the SQL Server features available for the system (such as compressed backups) will work without regard to any database's compatibility level.  So, keep your database (a little while longer) in compatibility level 80.

EDIT: Responding to your comment on Mirroring.  Mirroring is a server feature first introduced (for production use) with SQL Server 2005 SP1.  Since this is a feature of the server, it will work for all compatibility levels running on your SQL Server 2008 R2 server.

Therefore, keep your compatibility level at 80 (SQL Server 2000) level and use mirroring on the databases where you need it.

There is a lot of documentation on mirroring, but since this is new to you, you should start by reading Ron Talmage's white paper at: http://technet.microsoft.com/en-us/library/cc917680.aspx

Please note, however, that SQL Server 2008 R2 is the last version to support SQL Server 2000 (80) compatibility. And SQL Server 2008 R2 is no longer under mainstream support since 7/8/2014, though the limited extended support will continue into 2019.

This means that for your SQL Server next upgrade you must upgrade your T-SQL code.

END OF EDIT

Since you mention DDLs and EXEs, you seem to be indicating that your SQL queries are in the client not existing as stored procedures, functions, etc in the SQL Server. If you decide to write a wrapper code for use in your EXEs and DLLs and include it in all the locations.  That sounds as disruptive as just changing the SQL Queries.

Since you are using the *= syntax your code should look like this:

SELECT a.IdColumn, b.Description
   FROM EntityMaster a, EntityDescription b
   WHERE a.IdColumn *= b.IdColumn

So, there is more than a single edit needed to change the code to:

SELECT a.IdColumn, b.Description
   FROM EntityMaster a LEFT OUTER JOIN EntityDescription b
        ON a.IdColumn = b.IdColumn

And I expect you have much more complex code than that.


What's a good use case for SELECT * in production code? Out of habit, I never use SELECT * in production code (I only use it with ad-hoc scrap queries, typically when learning the schema of an object).
But I ran across a case now where I'm tempted to use it but would feel cheap if I did.

My use case is inside a stored procedure where a local temp table is created that should always match the underlying table used to create it, whenever the stored procedure runs. The temp table is populated much later on, so a quick hack to create the temp table without being verbose would be SELECT * INTO #TempTable FROM RealTable WHERE 1 = 0 especially for a table with hundreds of columns.

If the consumer of my stored procedure is agnostic to dynamic result sets, then are there any issues with me selling my services to SELECT *?

I generally abhor SELECT * in production code, and I've been in a situation where its use led to massive amounts of rework later.  Your case does look like a fair use of it though.

The place where I find SELECT * to be a must - and its evil cousin "INSERT INTO tbl" without a column list - is in an archiving situation, where rows are being moved to another table that must have the same structure.

INSERT INTO SalesOrderArchive  -- Note no column list
SELECT *
  FROM SalesOrder
 WHERE OrderDate < @OneYearAgo

DELETE FROM SalesOrder
 WHERE OrderDate < @OneYearAgo

If a new column is added to SalesOrder in the future, but not to SalesOrderArchive, the INSERT will fail.  Which sounds bad, but it's actually a really good thing!  Because the alternative is much worse.  If all the columns were listed on the INSERT and the SELECT, then the INSERT would succeed, and so would the following DELETE (which is effectively "DELETE *").  Production code that succeeds doesn't get any attention, and it may be a long time before someone notices that the new column is not being archived, but being silently deleted altogether.


arbiter bring down the three node mongodb replica set I have a typical 3 node mongodb replica set. One primary, one secondary and one arbiter. I thought the replica set would survive one node offline but it didn't. Please help me understand what went wrong.

Here is what I observed. Are they supposed to work like this?

When I take the arbiter offline (killing the instance), both two data nodes become secondary (they were primary and secondary).
When I take the primary offline (killing the instance), the secondary and arbiter remains secondary and  arbiter. There is no voting to select the new primary.

Thanks!

Here is the config

{
  "_id": "0",
  "version": 8,
  "protocolVersion": NumberLong(1),
  "writeConcernMajorityJournalDefault": true,
  "members": [
    {
      "_id": 3,
      "host": ":27017",
      "arbiterOnly": true,
      "buildIndexes": true,
      "hidden": false,
      "priority": 0,
      "tags": {

      },
      "slaveDelay": NumberLong(0),
      "votes": 1
    },
    {
      "_id": 4,
      "host": ":27017",
      "arbiterOnly": false,
      "buildIndexes": true,
      "hidden": false,
      "priority": 0,
      "tags": {

      },
      "slaveDelay": NumberLong(0),
      "votes": 0
    },
    {
      "_id": 5,
      "host": ":27017",
      "arbiterOnly": false,
      "buildIndexes": true,
      "hidden": false,
      "priority": 3,
      "tags": {

      },
      "slaveDelay": NumberLong(0),
      "votes": 1
    }
  ],
  "settings": {
    "chainingAllowed": true,
    "heartbeatIntervalMillis": 2000,
    "heartbeatTimeoutSecs": 10,
    "electionTimeoutMillis": 10000,
    "catchUpTimeoutMillis": 60000,
    "catchUpTakeoverDelayMillis": 30000,
    "getLastErrorModes": {

    },
    "getLastErrorDefaults": {
      "w": 1,
      "wtimeout": 0
    },
    "replicaSetId": ObjectId("")
  }
}

When I take the arbiter offline (killing the instance), both two data
  nodes become secondary (they were primary and secondary).

In this case, when the arbiter(_id = 3) goes down, The member with _id equals to 4 has Priority and votes as 0, so it cannot become primary and cannot vote in the election and the Majority(2 votes) cannot be established. So both the nodes becomes secondary.

  When I take the primary offline (killing the instance), the secondary
  and arbiter remain secondary and arbiter. There is no voting to select
  the new primary.

In this case, when the Primary(_id = 5) goes down, The member with _id equals to 4 has Priority, so it cannot become primary. Because of this the secondary
and arbiter remains as secondary and arbiter.

How to design this replica set to work better?

Make the Priority and votes of the node(_id = 4) to 1, it will fix both the scenarios.


Find and remove gaps in sequence across two different columns I've tried to solve this problem using a set-based approach.  However, since I need to look at each row I think I must use a cursor; please correct me if I'm wrong.

The table:

Project, item, method, start, end

The table contains multiple projects, multiple items, but I've reduced it here to a single project, one item, for simplicity:

The data looks like this:

ABC, widget1, XY, 1000, 1033
ABC, widget1, XY, 1033, 1062
ABC, widget1, XY, 1062, 1112
ABC, widget1, XY, 1112, 1163
ABC, widget1, WW, 1163, 1223
ABC, widget1, WW, 1223, 1288
ABC, widget1, WW, 1288, 1334
ABC, widget1, XY, 1334, 1383
ABC, widget1, XY, 1383, 1425

I'd like to write a query that returns this result:

ABC, widget1, XY, 1000, 1163
ABC, widget1, WW, 1163, 1334
ABC, widget1, XY, 1334, 1425

What is the best way to do this?

You can store pre-calculated gaps, and use constraints to make sure that your pre-calcualted data is always up-to-date:

Here is the table and the first interval

CREATE TABLE dbo.IntegerSettings(SettingID INT NOT NULL,

  IntValue INT NOT NULL,

  StartedAt DATETIME NOT NULL,

  FinishedAt DATETIME NOT NULL,

  PreviousFinishedAt DATETIME NULL,

  CONSTRAINT PK_IntegerSettings_SettingID_FinishedAt PRIMARY KEY(SettingID, FinishedAt),

  CONSTRAINT UNQ_IntegerSettings_SettingID_PreviousFinishedAt UNIQUE(SettingID, PreviousFinishedAt),

  CONSTRAINT FK_IntegerSettings_SettingID_PreviousFinishedAt

    FOREIGN KEY(SettingID, PreviousFinishedAt)

    REFERENCES dbo.IntegerSettings(SettingID, FinishedAt),

  CONSTRAINT CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt CHECK(PreviousFinishedAt <= StartedAt),

  CONSTRAINT CHK_IntegerSettings_StartedAt_Before_FinishedAt CHECK(StartedAt < FinishedAt)

);

GO

INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt)

  VALUES(1, 1, '20070101', '20070103', NULL);

It has five constraints which work together to implement the business rule. Let me demonstrate how the more complex ones work. Of course, some constraints are simple and as such do not need any explanations.

**

There can be only one first interval for a setting

**

The constraint UNQ_IntegerSettings_SettingID_PreviousFinishedAt ensures exactly that. The first interval does not have a previous one, which means that PreviousFinishedAt IS NULL. The UNIQUE constraint  guarantees that there can be only one such row per setting. See for yourself:

INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt)

  VALUES(1, 1, '20070104', '20070105', NULL);

/*

Server: Msg 2627, Level 14, State 2, Line 1

Violation of UNIQUE KEY constraint 'UNQ_IntegerSettings_SettingID_PreviousFinishedAt'. Cannot insert duplicate key in object 'dbo.IntegerSettings'.

The statement has been terminated.

*/

**

Next window must begin after the end of the previous one.

**

The constraint CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt guarantees exactly that. See for yourself:

INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt)

  VALUES(1, 2, '20070104', '20070109', '20070105')

/*

Server: Msg 547, Level 16, State 1, Line 1

INSERT statement conflicted with TABLE CHECK constraint 'CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt'. The conflict occurred in database 'RiskCenter', table 'IntegerSettings'.

The statement has been terminated.

*/

**

Two different windows cannot refer to one and the same window as
their previous one.

**

Again, the same constraint UNQ_IntegerSettings_SettingID_PreviousFinishedAt guarantees precisely that, as demonstrated below:

INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt)

  VALUES(1, 3, '20070104', '20070115', '20070103')

Msg 2627, Level 14, State 1, Line 1

Violation of UNIQUE KEY constraint 'UNQ_IntegerSettings_SettingID_PreviousFinishedAt'. Cannot insert duplicate key in object 'dbo.IntegerSettings'.

The statement has been terminated.

This means that there can be no overlaps.

As you have seen, for every time window, there can be at most one preceding it, and at most one following it. The following interval cannot begin before the end of its previous one. Together these two statements mean that there can be no overlaps.

**

Working with gaps.

**

You can prohibit gaps altogether, just replace the following constraint:

  CONSTRAINT CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt CHECK(PreviousFinishedAt <= StartedAt),

With a stricter one, as follows:

 CONSTRAINT CHK_IntegerSettings_PreviousFinishedAt_EqualTo_StartedAt CHECK(PreviousFinishedAt = StartedAt),

But if you allow gaps, the query to retrieve them is very simple and performant, as follows:

SELECT PreviousFinishedAt AS GapStart, StartedAt AS GapEnd
  FROM dbo.IntegerSettings
  WHERE StartedAt > PreviousFinishedAt;


View information on historical deadlocks I've been tasked with investigating an issue where an application's job has failed due to deadlocks. I'm aware of using traces to capture deadlocks, however as this is something that occurred 16 hours ago I wanted to know if it was possible to see what caused the deadlock?

Thanks

Start with sp_BlitzLock. It's a free open source stored procedure written by Erik Darling that examines the built-in System Health Extended Events session in SQL Server 2012 & newer.
You can download it from the First Responder Kit Github repository.
To install it, just run sp_BlitzLock.sql to install the stored procedure, then run:
EXEC sp_BlitzLock

The first result set is a set of deadlock details, and the second is analysis that looks at which apps, users, and tables are most commonly involved in your deadlocks. It also gives you parameters to use for sp_BlitzCache to analyze their query plans, and sp_BlitzIndex to analyze over-indexing and under-indexing issues on the tables that may be contributing.


at the moment mysqldump (22 minutes exec time, 5 GB Dump), when to use xtrabackup? We use the Puppet code for deploying MariaDB. Those are the backup options.

mysql::backup::mysqldump: Implements mysqldump backups.
mysql::backup::mysqlbackup: Implements backups with Oracle MySQL
Enterprise Backup.
mysql::backup::xtrabackup: Implements backups with
XtraBackup from Percona.

We don't own MySQL Enterprise Backup licenses.

Here the backup script. The big problem at the moment, we don't notice when backup is failing.

#!/bin/bash
#
# MySQL Backup Script
#  Dumps mysql databases to a file for another backup tool to pick up.
#
# MySQL code:
# GRANT SELECT, RELOAD, LOCK TABLES ON *.* TO 'user'@'localhost'
# IDENTIFIED BY 'password';
# FLUSH PRIVILEGES;
#
##### START CONFIG ###################################################

USER=backup
DIR=/var/lib/mysql/backup
ROTATE=6

# Use a pwd file. See https://dev.mysql.com/doc/refman/5.1/en/password-security-user.html
PWD_FILE=$(mktemp ${DIR}/backup.cnf.XXXX)
echo "
[client]
password=sirugrwiuwiuwguisrkogos
" >> $PWD_FILE

PREFIX=mysql_backup_

ADDITIONAL_OPTIONS="--ignore-table=mysql.event"

##### STOP CONFIG ####################################################
PATH=/usr/bin:/usr/sbin:/bin:/sbin

set -o pipefail

cleanup()
{
    find "${DIR}/" -maxdepth 1 -type f -name "${PREFIX}*.sql*" -mtime +${ROTATE} -print0 | xargs -0 -r rm -f
}

mysqldump --defaults-file=${PWD_FILE} -u${USER} --opt --flush-logs --single-transaction \
 ${ADDITIONAL_OPTIONS} \
 --all-databases | bzcat -zc > ${DIR}/${PREFIX}`date +%Y%m%d-%H%M%S`.sql.bz2

if [ $? -eq 0 ] ; then
    cleanup
fi

rm -rf $PWD_FILE

backup time

# time /usr/local/sbin/mysqlbackup.sh

real    21m35.647s
user    20m47.059s
sys     0m7.843s

backup size

# du -sh mysql_backup_20160223-123920.sql
4.9G    mysql_backup_20160223-123920.sql

Is it in our use case better to use xtrabackup? When to use xtrabackup?

Can't you test this on a test machine? I don't mean to be rude, but only you can really tell if XtraBackup is better  for you. What I do know is that  Percona is a quality outfit and that their offerings are always worth a look.

I'm guessing about your scenario, but any backup will depend more on your disk config than on a particular programme (RAID config &c.). There are four things to be considered in any IT setup - hardware, networking, OS and software (and maybe 5 - people :-) ).

Have you also looked at MyLVMBackup? Zmanda? If you're considering XtraBackup, you must be on Linux - these also work on that platform.


Does STATISTICS IO output include Version Store reads? SQL Server has an option SET STATISTICS IO ON that shows the number of logical and physical page reads for a query. Do these stats include reads of the version store for SNAPSHOT and RCSI queries?

STATISTICS IO does not include Version Store reads, at least for the version store in tempdb.

Here's a demo for proof:

--setup script
USE master
GO

CREATE DATABASE TestDB
GO

ALTER DATABASE TestDB
SET ALLOW_SNAPSHOT_ISOLATION ON
GO

USE TestDB
GO

DROP TABLE IF EXISTS dbo.Test
GO

CREATE TABLE dbo.Test (ID int identity PRIMARY KEY, junk int)

INSERT dbo.Test
SELECT TOP (100000) 1
FROM master.dbo.spt_values a
CROSS JOIN master.dbo.spt_values b

Start a 30s update loop in one SSMS tab

--UPDATE loop
SET NOCOUNT ON
DECLARE @stop datetime = DATEADD(SECOND, 30, GETDATE())

WHILE GETDATE() < @stop
BEGIN
    BEGIN TRAN

    UPDATE dbo.Test
    SET junk += 1

    COMMIT
END

UPDATE dbo.Test
SET junk = 1

And while the loop is going, run two identical queries in SNAPSHOT with STATISTICS IO ON, separated by 15s to allow versions to accumulate.

USE TestDB
SET STATISTICS IO ON
GO

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN

SELECT MAX(junk)
FROM dbo.Test

WAITFOR DELAY '00:00:15'

SELECT MAX(junk)
FROM dbo.Test

COMMIT

The IO stats show identical reads:

But the actual execution plan shows the scan for the second query taking far more time, due to reading the version store.

To prove to yourself that this query resulted in tempdb reads, you can use this Extended Events session (which is obviously better than Profiler), filtered to the session where the read queries are running:

CREATE EVENT SESSION [file_reads] ON SERVER
ADD EVENT sqlserver.file_read_completed(
    ACTION(sqlserver.session_id,sqlserver.sql_text)
    WHERE ([sqlserver].[session_id]=(52)))
ADD TARGET package0.event_file(SET filename=N'file_reads')
GO

Viewing the "live data" for that XE session during the demo, you can see reads against database id 2 (tempdb), and it captures the query text of our read query as well:

Special thanks to Paul White for bringing up this issue with STATISTICS IO.


Can I check if a Mirror database was synchronized when the principal died? having 2 machines, one being the principal and the second one as a mirror in case of failure of the 1st one. without witness but in High Safety mode
Is any way to check if the mirror DB was synchronised when the Principal has failed?
I need this because I'm concerned if:

1-the mirror goes down
2-the Principal does a lot of work (while mirror is down)
3-the mirror come up, and starts synchronising, while synchronizing the principal goes down

in this situation, can I detect the mirror is not up to date without a witness??
So...is there a way to check if the Mirror database is up to date while the Principal is down?
Edit:
I tried to use a Event Notification, to keep track of mirroring changes, but theses notifications are asynchronous, so there is a gap I cant cover.
Is any other way?

You can check the mirroring_state_desc in sys.database_mirroring.  It will tell you if the databases are in sync, this does depend on if you're running in High Safety or High Performance mode.  In High Performance mode as long as the mirroring process is running it will show state of SYNCHRONIZED while the actual state of the database can be behind as the log records are still being applied.

In this disaster type scenario, I believe the mirroring would be broken and it would be best to know the various ways to get a tail of the log backup combined with the normal course of database and transaction log backups to restore to the point of failure.

Tables 5-7 in this TechNet article cover the various scenarios against the statues reported.


Finding the name of the supplier that supplies the most parts I have the following schema:

Suppliers(sid: integer, sname: string, address: string)
Parts(pid: integer, pname: string, color: string)
Catalog(sid: integer, pid: integer, cost: real)

Now what I'm trying to do is get the name of the supplier that has the most items for sale in the Catalog. This is an example instance:

What I have so far is:

SELECT S.sid, COUNT(*) AS NUM
FROM Catalog C, Suppliers S
WHERE C.sid=S.sid
GROUP BY S.sid

Which returns each supplier id and the number of parts they supply, which sounds like a good start; however, I'm not sure where to go from there.

I tried:

SELECT Y.sid, MAX(Y.Num)
FROM (SELECT S.sid, COUNT(*) AS NUM
      FROM Catalog C, Suppliers S
      WHERE C.sid=S.sid
      GROUP BY S.sid) Y

But that returned:

| sid | MAX(Y.NUM) |
+-----+------------+
| 1   |     5      |

...which is wrong.

I really feel like I'm over complicating this and that there is a simple solution that I'm missing. Any help is appreciated.

You didn't specify your DBMS, so this is ANSI SQL:

select sid,
       num_products
from (
  select sid,
         count(*) as num_products,
         dense_rank() over (order by count(*) desc) as rnk
  from catalog
  group by sid
) t
where rnk = 1;

If you need the information from the supplier table as well, you need to join it:

select s.sname,
       t.num_products
from (
  select sid,
         count(*) as num_products,
         dense_rank() over (order by count(*) desc) as rnk
  from catalog
  group by sid
) t
  join suppliers s on s.sid = t.sid
where t.rnk = 1;

Online example: http://rextester.com/HGL10937


Role db_ddladmin not working properly on SQL Server 2008 I want to allow certain user to create/modify objects on a database(tables, views, sp's, functions, schemas etc) and don't want to give db_owner.

I thought I could do it with the db_ddladmin role, but somehow that doesn't wort as expected. The error messages i get is that are like this:

Your are not loggend on as the database owner or system administrator.
You might not beable to sache changes to tables that you do not own.

Table dbo.MyTable is set to read only,
user doesn't have enough right on this table.

etc

Any idea how i can achieve my goal?

EDIT:

I re-read your question and I may have got it incorrect before. Looks like you are running the scripts using SSMS instead of using T-SQL scripts. I verified that it works when using T-SQL scripts and doesn't work when using SSMS GUI. To get around this issue, you may want to use T-SQL scripts.

This is a known issue and looks like this is fixed in DENALI CTP3 and I can confirm that.

Ref: https://connect.microsoft.com/SQLServer/feedback/details/467582/db-ddladmin-role-doesnt-allow-use-of-design-functions-in-ssms

Just verified the (correct) behavior also works in SQL Server 2008 R2. I don't have a SQL Server 2008 instance to verify unfortunately.


In creating view use SQL Security definer or invoker? CREATE VIEW syntax is

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

For SECURITY you can set DEFINER or INVOKER.  When does it make sense to select "definer"?  As far as I understand that means check the security permissions of me, the person who is creating the view.  If I have full permissions across our database, then I just created a view that gives any invoker full access.  That sounds wrong.  Wouldn't you always want the security to be the person who is actually using the view?

Quotes taken from the documentation.

  If you have the SUPER privilege, you can specify any syntactically legal account name. If the account does not actually exist, a warning is generated.

Being able to specify security accounts other than the view's creator is a key piece of using DEFINER, which you seem to have missed.

  When a view has been referenced, privileges for objects accessed by the view are checked against the privileges held by the view DEFINER account...

This means that you can set the DEFINER to use a security account which has access to the underlying objects, but deny access to those objects to users at large, thus maintaining tighter security.

For example, say you have some table PrivateData, which users should not be able to generally access, but you want to create an internal report which tracks metadata on this table. You deny permissions on PrivateData to users, but create an account PrivateDataReader which has read-only access to the table. You can then create a view which presents the metadata information, specifying PrivateDataReader as the DEFINER.

If you always want to use the security of the user accessing the view, then you should specify INVOKER.


How to optimize a large join to create a WHERE IN The following sqlite database query takes about 40 seconds on my Core i7.

SELECT t.a
FROM titles t
WHERE t.a IN (
    SELECT e.id
    FROM erpubtbl e
    JOIN abstract_mesh am ON am.pmid = e.pmid
    JOIN mesh_descriptor md ON md.id = am.mesh_id
    WHERE md.id = 100
    ORDER BY e.journal_year DESC)
AND t MATCH 'test';

I'm pretty sure the slow part is the IN.  The abstract_mesh table is a million plus rows.  Any idea how to speed this up, or suggestions for how to alter the schema to be more efficient?

Here's the schema:

CREATE TABLE `abstract_mesh` (
  `mesh_id` integer NOT NULL
,  `major_topic` varchar(1) DEFAULT NULL
,  `pmid` integer DEFAULT NULL
);

CREATE TABLE `erpubtbl` (
  `id` integer NOT NULL
,  `journal_id` integer  NOT NULL DEFAULT '0'
,  `pmid` integer NOT NULL DEFAULT '0'
,  `title` text
,  `author` varchar(50) DEFAULT NULL
,  `journal_month` varchar(25) DEFAULT NULL
,  `journal_year` integer DEFAULT NULL
,  `journal_page` varchar(50) DEFAULT NULL
,  `volume` varchar(100) DEFAULT NULL
,  `issue` varchar(50) DEFAULT NULL
,  `pubmed_entry_year` varchar(50) DEFAULT NULL
,  `pubmed_entry_month` varchar(50) DEFAULT NULL
,  `medline_pgn` varchar(50) DEFAULT NULL
,  `no_abstract` integer DEFAULT NULL
,  `publication_type` varchar(250) DEFAULT NULL
,  `country` varchar(50) DEFAULT NULL
,  `citation_subset` varchar(50) DEFAULT NULL
,  PRIMARY KEY (`id`,`journal_id`,`pmid`)
,  UNIQUE (`id`,`pmid`)
);

CREATE TABLE `mesh_descriptor` (
  `id` integer  NOT NULL PRIMARY KEY AUTOINCREMENT
,  `name` varchar(250) DEFAULT NULL
,  UNIQUE (`id`)
,  UNIQUE (`name`)
);

CREATE INDEX mesh_descriptor_name_idx on mesh_descriptor (name);
CREATE INDEX erpubtbl_year_idx on erpubtbl (journal_year);
CREATE VIRTUAL TABLE titles USING fts4(a NUMBER, t TEXT, tokenize=porter);

The abstract_mesh table has no index.
As this EXPLAIN QUERY PLAN output (from SQLite 3.8.10.2) shows, searching in this table is so slow that it is worthwhile to create a temporay index as part of the execution of the query:

0|0|0|SCAN TABLE titles AS t VIRTUAL TABLE INDEX 3:
0|0|0|EXECUTE LIST SUBQUERY 1
1|0|2|SEARCH TABLE mesh_descriptor AS md USING INTEGER PRIMARY KEY (rowid=?)
1|1|0|SCAN TABLE erpubtbl AS e USING INDEX erpubtbl_year_idx
1|2|1|SEARCH TABLE abstract_mesh AS am USING AUTOMATIC COVERING INDEX (pmid=? AND mesh_id=?)

You should create such an index explicitly so that the query does not need to:

CREATE INDEX abstract_mesh_pmid_mesh_id_idx ON abstract_mesh(pmid, mesh_id);

However, if you create some other indexes:

CREATE INDEX abstract_mesh_meshid_pmid ON abstract_mesh(mesh_id, pmid);
CREATE INDEX e_pmid ON erpubtbl(pmid);

this allows the database to use a different execution plan, which might or might not be faster for your data:

0|0|0|SCAN TABLE titles AS t VIRTUAL TABLE INDEX 3:
0|0|0|EXECUTE LIST SUBQUERY 1
1|0|2|SEARCH TABLE mesh_descriptor AS md USING INTEGER PRIMARY KEY (rowid=?)
1|1|1|SEARCH TABLE abstract_mesh AS am USING COVERING INDEX abstract_mesh_meshid_pmid (mesh_id=?)
1|2|0|SEARCH TABLE erpubtbl AS e USING INDEX e_pmid (pmid=?)
1|0|0|USE TEMP B-TREE FOR ORDER BY


DB client tool -- To get the error message when open toad oracle client I have a serious problem with toad oracle client. I got the error message as following when I open toad client (installed and then opened it):

  Error: No valid Oracle clients found. You need at least one 64-bit
  client properly configured

Please do me a favor to give me how to deal with this issue or give me any slim clue.

Download from toad official website with 64-bit like as following picture

Depending on YouTube video from @Philᵀᴹ, I have done this problem and I will try to list all I know and every step as following content, Hopefully I can help someone who has the same issues.

Download and Install Toad for Oracle v12.10

Open it you should can not use like as below error message

Download Instant Client 12.2

Unzip it and move it to anywhere you like (for example C:\app\instantclient_12_2)

Go to Control Panel\All Control Panel Items\System-->Environment Variable to "Add" TNS_ADMIN as like below picture

The same path to "Edit" Path like as following picture

OK done with toad client

To move your old tnsnames.ora to C:\app\instantclient_12_2 (for example)

Done all


Can changes to cardinality optimization be made without taking users offline? I have an old SQL Server database that has been migrated a few times since < 2005. Some queries are running very slowly. I have been advised that the following changes could help performance:

Increase the database compatibility level from 90 (2005) to highest possible (the software itself does support the highest).
Set Legacy Cardinality Estimation to ON.
Set Query Optimizer Fixes to ON.

Can these changes be made without taking users offline?

Yes the changes can be done without taking users offline. But please note that this is ALTER DATABASE command which will lock the database so if any schema changes are happening it would be blocked. It would be better to run this query when load is relatively very less, just so that it finishes very quickly.

Also note that when you enable legacy cardinality estimation the procedure and plan cache will be flushed and new queries would be recompiled using old CE. This will force SQl Server to do some extra work and you might see slowness for a very small period.


How to apply patches in MySQL in any version How to apply patches in MySQL 5.1.67 or any version.

because  we need to fix so many bugs in our version without any upgrading.

please suggest me .

The concept of "applying patches" is not meaningful in MySQL unless you are building from source, and even then, it's not really a question that has an answer, since they don't release "patches."

What are you trying to accomplish?  What version are you running, now?  When you say "applying patches in MySQL 5.1.67," do you mean applying fixes found in 5.1.67 to a server of a previous version?

I ask that, because, at the time of this writing, 5.1.67 is still a very recent release and I would contend that "so many bugs" is not a phrase that describes this release at all.  It (5.1.67) is very stable and is, in fact, my current production release of choice for everything based on 5.1.

The fixes present in 5.1.67 cumulative from the beginning of 5.1.  To have these fixes, you have to have 5.1.67 because changes made to 5.1.67 would often depend on other changes made to intermediate versions.  Hence, you upgrade.

Within a release series (e.g., 5.1.x) the functionality of the server should not change after the series reaches General Availability, and the release notes will document any incompatible changes that are introduced.

Upgrading should not be a problem within a release series (although you should always have backups, of course).  It also should not be a problem going from series to series either, as long as you follow the instructions and don't try to skip over a major release series.


Tables with mixed ON DELETE CASCADE and ON DELETE RESTRICT, rule set with recursion? Not sure if this is a generic question, but unless not, this is MySQL 5.6.

We have a database consisting of ~300 tables, INNODB, all connected with proper foreign keys. One of these tables can be seen as the parent. My desired end result is that I can execute one delete on this table, and have all related information in all other 300 tables deleted as well.

However, the declared foreign keys are a mix of RESTRICT and CASCADE for ON DELETE and I'm trying to understand the rule set when these are applied in recursion.

I've been trying to find information on this, but no luck. Testing appears to indicate that RESTRICT blocks a CASCADE, but not necessarily. Consider this example:

  Tables: A, B, C
  B -> A (ON DELETE CASCADE)
  C -> A (ON DELETE CASCADE)
  C -> B (ON DELETE RESTRICT)

In this example, rows from B and C are deleted when A is deleted. However, since C -> B has a RESTRICT one could think that matching rows would block a CASCADE. But this is not the case. However, if I add an additional table D like this:

  Tables: A, B, C, D
  B -> A (ON DELETE CASCADE)
  C -> A (ON DELETE CASCADE)
  C -> B (ON DELETE RESTRICT)
  D -> C (ON DELETE RESTRICT)

A delete from A is blocked by the RESTRICT from D to C.

A simple approach would be to simply use CASCADE everywhere, but I'm curious about the rule set. Or if the behaviour is either implementation dependent or simply undefined.

Is it based on level of depth of the CASCADING? That is, the shortest path determines the effective ON DELETE rule to use? What if multiple paths have the same length, with different rules?

I have managed to create a test case that at least for MySQL 5.6 demonstrates, for all intents and purposes, that the behaviour is undefined. Consider these 3 examples creating a "diamond"-shape between A, B, C and D.

Example 1

CREATE TABLE `a` (id INT UNSIGNED NOT NULL,
    PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE `b` (
    id INT UNSIGNED NOT NULL,
    a INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (a) REFERENCES a (id) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE `c` (
    id INT UNSIGNED NOT NULL,
    a INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (a) REFERENCES a (id) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE `d` (
    id INT UNSIGNED NOT NULL,
    b INT UNSIGNED NOT NULL,
    c INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (id) REFERENCES b (id) ON DELETE CASCADE,
    FOREIGN KEY (id) REFERENCES c (id) ON DELETE CASCADE
) ENGINE = INNODB;

INSERT INTO a VALUES (1);
INSERT INTO b VALUES (1, 1);
INSERT INTO c VALUES (1, 1);
INSERT INTO d VALUES (1, 1, 1);

DELETE FROM a;

Delete from a is successful as expected, as all keys are CASCADE.

Example 2

CREATE TABLE `a` (
    id INT UNSIGNED NOT NULL,
    PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE `b` (
    id INT UNSIGNED NOT NULL,
    a INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (a) REFERENCES a (id) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE `c` (
    id INT UNSIGNED NOT NULL,
    a INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (a) REFERENCES a (id) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE `d` (
    id INT UNSIGNED NOT NULL,
    b INT UNSIGNED NOT NULL,
    c INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (id) REFERENCES b (id) ON DELETE CASCADE,
    FOREIGN KEY (id) REFERENCES c (id) ON DELETE RESTRICT
) ENGINE = INNODB;

INSERT INTO a VALUES (1);
INSERT INTO b VALUES (1, 1);
INSERT INTO c VALUES (1, 1);
INSERT INTO d VALUES (1, 1, 1);

DELETE FROM a;

Notice that d now has a RESTRICT to c. I would consider this case ambiguous as it forces you to consider which foreign key "wins". CASCADE or RESTICT, what is picked?

The result is that all rows are deleted.

Example 3

CREATE TABLE `a` (
    id INT UNSIGNED NOT NULL,
    PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE `b` (
    id INT UNSIGNED NOT NULL,
    a INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (a) REFERENCES a (id) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE `c` (
    id INT UNSIGNED NOT NULL,
    a INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (a) REFERENCES a (id) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE `d` (
    id INT UNSIGNED NOT NULL,
    b INT UNSIGNED NOT NULL,
    c INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (id) REFERENCES b (id) ON DELETE RESTRICT,
    FOREIGN KEY (id) REFERENCES c (id) ON DELETE CASCADE
) ENGINE = INNODB;

INSERT INTO a VALUES (1);
INSERT INTO b VALUES (1, 1);
INSERT INTO c VALUES (1, 1);
INSERT INTO d VALUES (1, 1, 1);

DELETE FROM a;

Notice that the only difference is the change of what foreign key is RESTRICT from d. This example however, fails with

  Error Code: 1451
  Cannot delete or update a parent row: a foreign key constraint fails (hello.d, CONSTRAINT d_ibfk_1 FOREIGN KEY (id) REFERENCES b (id))

While logically, it's the same as Example 2. Without having looked at the source code of MySQL, I strongly suspect that the foreign keys are "applied" in lexical order based on their name. This is also the order the foreign keys are listed by mysqldump.

Without knowing whether what I'm testing here has a defined behaviour or not based on a higher logic, it's hard to know if this demonstrates either a bug, or simply the behaviour in MySQL.

Regardless, as it stands, this example shows that in practical terms I'd say that for all intents and purposes, when having mixed CASCADE and RESTRICT, the behaviour is undefined as it's not in any way sound that you should depend on the names given to foreign keys.


How can I add a column to multiple tables at once in MySQL Workbench? I have a MySQL Workbench model (diagram) that belongs to a Ruby on Rails application. I now need to add the created_at and updated_at timestamp columns to each of the tables. What is a quick and easy way to do this (instead of doing it manually)? My client does not want to use Rails migrations and uses the Workbench as the authorative database schema so I'm stuck with doing it in Workbench.

You can use this Python Script in the Scripting Shell of MySQL Workbench. It is an improved version of the script found here: http://mysqlworkbench.org/2012/06/mysql-workbench-script-for-adding-columns-to-all-tables-in-a-model/

# define your columns and types here
columns_types = {"created_at" : "DATETIME", "updated_at" : "DATETIME"}

# tables you want to skip
skip_tables = ["main_defs", "main_menu_items", "delayed_jobs", "delayed_job_workers", "delayed_job_logs"]

# get a reference to the schema in the model. This will get the 1st schema in it.
schema = grt.root.wb.doc.physicalModels[0].catalog.schemata[0]

# iterate through all tables
for table in schema.tables:

    # skip the current table if it is in skip_tables
    if table.name in skip_tables:
        continue

    # iterate through all columns to be added
    for column_name, type in columns_types.items():

        # skip this column_name if there is already a column with this name
        column_names = [x.name for x in table.columns]
        if column_name in column_names:
            continue

        # create a new column object and set its name
        column = grt.classes.db_mysql_Column()
        column.name = column_name
        # add it to the table
        table.addColumn(column)
        # set the datatype of the column
        column.setParseType(type, None)


Mysql query runs longer than long_query_time variable I have this query in picture attached that runs longer than my long_query_time. Please can anyone recommend what index or indexes to create to speed it up?

Just looking at the query you have you are missing indexes for:

is_template
archived
market_segment
template_type

I am assuming the indexes you currently have are used for other queries correct?

I am thinking you probably need an index on the other tables name column.

ALTER TABLE `projects` ADD INDEX project_ix1( is_template , archived, market_segment, template_type );
ALTER TABLE `template_types` ADD INDEX templatetypes_ix1( alias );
ALTER TABLE `market_segments` ADD INDEX marketsegments_ix1( name );

You might want to break up the multi-column index, since this is only for the query you asked about, and I don't know what other queries you might have in the pipe.  Let me know the results.


prepare stmt is not working set @sqlt = concat("select", @rowname_date," from  syslog_data.syslogs_index_1 ");
prepare stmt from @sqlt;
execute stmt;
deallocate prepare stmt;

gives me a eeror 1064 in the prepare statement?
Any advise please.
Thanks

Assuming that you have a valid column name or a properly escaped literal in @rowname_date and you don't have leading spaces in it then you have to have a space after SELECT clause

set @sqlt = concat("select", @rowname_date," from syslog_data.syslogs_index_1 ");
                         ^^

Here is SQLFiddle demo


How does the sys.objects Catalog View pull from itself? I'm using this stored procedure called OBJECT_DEFINITION as was suggested. It's helping me wrap my head around the SQL Server system. However, there is one area that I can't yet figure out. On this one table sys.objects, you'll see it pulls from itself.

1> SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects'))
2> GO

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE VIEW sys.objects AS
    SELECT name,
      .. stuff.
    FROM sys.objects$

Now I'm totally lost how is sys.objects selecting from sys.objects$ what is sys.objects$. Thinking there may be a table of the same name, I first tried to create a table and a view by the same name, but I can't do that. I get (f is the name I picked). You can't do this in any database I've ever used.

Msg 2714, Level 16, State 3, Server x230, Procedure f, Line 1
There is already an object named 'f' in the database.

Microsoft docs sys.objects only as a Catalog View, it's not listed as a Base Table

The counterpart Catalog View, sys.system_objects pulls from the Base Table sys.sysschobjs, and OBJECT_DEFINITION clearly shows that.

My guess is that the SQL here isn't the actually SQL the view is executing, but some kind of comment or description on it: meta-data of sort, and that it's bugged. But, I could be totally off.

sys.objects is not a table, it is a system catalog view. The definition shown in the question even shows Create VIEW .... Also, sys.objects is not the same thing as sys.objects$:

You cannot select from sys.objects$ on a regular connection. You need to be on a DAC connection, though not necessarily in Single-User mode, to SELECT from it.
They don't have the same number of columns. Just connect to the DAC connection and run the following:

SELECT TOP (1) * FROM sys.objects;
GO
SELECT TOP (1) * FROM sys.objects$;
GO

sys.objects$ must be an internal catalog view since the underlying fields in sys.objects (no trailing $) come from these tables: sysschobjs, syssingleobjrefs, syspalnames.


Supertypes and subtypes I am trying to design a database that has supertype/subtypes. I have the following users:
- basic user
- business user
- administrator (super user).

All three users share a common table (users) where fields like name, email, password, etc are stored. However, the business users have a separate table (business) where the business fields like business_name, business_license, business_email, etc are stored. My problem is that my business users are themselves split into 5 or more categories.
My business users are split like this:

painters
vehicle detailers
lot attendants
service technicians:

engine technicians
transmission technicians
computer technicians
electrical technicians

sales representatives

physical location sales representative
internet sales representative
sales manager

I currently designed this database but I not really happy with it. Is there a better approach?

EDIT 1 I need to mention that the business types and subtype do not have any different fields. They business table holds all I need. The business types and subtype are more like an ENUM, but held in a table.

EDIT 2
Example of SELECTs that I will need. More exactly, how a result should look like:

username       | user_type   | business_name    | business_type      | business_subtype

John Doe       | business    | AUDI Dealership  | service technician | engine mechanic
Mary Liu       | basic user  | NULL             | NULL               | NULL
George Vangh   | super user  | NULL             | NULL               | NULL
Martha Ayla    | business    | AUDI Dealership  | sales              | sales representative
Carlos M       | business    | Ford & Lincoln   | sales              | sales manager

UPDATE 3
Found this example online and I thought I'll give it a try. Is this acceptable?

Yes the code in Update 3 will work and you will be able to select the information you need. If you are comfortable with that then go for it.  Keep in mind that means you will have to either duplicate your Painter type in both the sub and super type, or leave Painter as a sub type without a super type.  Either way this is a little bit awkward but workable.

Assuming the tables really are as small as you say,and your requirements as simple as they seem you might want to consider compressing the business data down into one table.  It won't look as sexy as other solutions but CRUD operations will be dead simple. I don't know MySql very well so please excuse any silly syntax errors:

CREATE TABLE users (user_id INT NOT NULL, user_type_id INT NOT NULL, busniess_id INT, etc);
CREATE TABLE user_type (user_type_id INT NOT NULL, name VARCHAR(50) );
CREATE TABLE business (business_id INT NOT NULL, business_name VARCHAR(50) NOT NULL, business_type_id INT NOT NULL, business_subtype_id INT, etc);
CREATE TABLE business_type (business_type_id INT NOT NULL, name VARCHAR(50) );
CREATE TABLE business_sub_type (business_sub_type_id INT NOT NULL, name VARCHAR(50) );

Like I said that isn't sexy but it will get the job done.  It is a bummer that MySql doesn't offer check constraints because they could offer a touch of protection.

If your requirements get any more involved I would seriously reconsider @MDCCL's post.  My gut says this is the best solution out of the mostly ho-hum solutions I can envision.  To quote another answer to that question :

  The Answer by MDCCL is fascinating, educational, and presumably correct (though above my pay-grade).


Help writing query for MS SQL Server I need to write a MS SQL Server query statement that will generate the result set table name SUMMARY from the DETAIL table

Server name: Development
Database name: Test

                  DETAIL
STATE     Total BadEggs  GoodEggs   Unknown
Kansas      5      2        2         1
Texas       10     5        4         1
Texas       2      0        2         0
Georgia    20      5        5        10
Maine      25     15        5         5
Florida     5      1        2         2
Texas       8      4        2         2
Georgia     9      3        4         2
Maine       5      2        1         2
Florida    11      1        5         5
Texas       2      2        0         0
Florida     5      2        1         2
New York   20      5        5         10
New York    6      2        2         2
Alabama    10      3        4         3
Alabama    30     12       15         3
Alabama     4      1        1         2
Alabama     8      3        2         3
       185

                  SUMMARY
STATE     Total  BadEggs   GoodEggs Unknown
Kansas      5        2          2   1
Texas      22       11          8   3
Georgia    29        8          9   12
Maine      30       17          6   7
Florida    21        4          8   9
New York   26        7          7   12
Alabama    52       19          22  11
Total     185       68          62  55

SELECT ISNULL([State],'Total'), SUM(Total), SUM(GoodEggs), SUM(BadEggs)
FROM Detail
GROUP BY
GROUPING SETS (([State]), ());

Although you should probably just do GROUP BY [State] and provide the total row and right column in your application.


Authentification failure for PostgreSQL server (9.3) I can currently not connect to my Postgresql server (9.3) from pgadmin (Running on Ubuntu 14.04). The server according to pg_lsclusters is on port 5433.

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

I can connect to the 9.1 server with pgadmin, but when I try to access the 9.3 server I get the error that the password can't be authenticated:

    An error has occurred:

    Error connecting to the server: FATAL:  Passwort-Authentifizierung für Benutzer „postgres“ fehlgeschlagen
    FATAL:  Passwort-Authentifizierung für Benutzer „postgres“ fehlgeschlagen

The 9.1 server has the following lines in pg_hba.conf:

    # Database administrative login by Unix domain socket
    local   all             postgres                                peer

    # TYPE  DATABASE        USER            ADDRESS                 METHOD

    # "local" is for Unix domain socket connections only
    local   all             all                                     peer
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            md5
    # IPv6 local connections:
    host    all             all             ::1/128                 md5
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    #local   replication     postgres                                peer
    #host    replication     postgres        127.0.0.1/32            md5
    #host    replication     postgres        ::1/128                 md5

and the 9.3 server has the following in the pg_hba.conf:

    # Database administrative login by Unix domain socket
    local   all             postgres                                md5

    # TYPE  DATABASE        USER            ADDRESS                 METHOD

    # "local" is for Unix domain socket connections only
    local   all             all                                     md5
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            md5
    # IPv6 local connections:
    host    all             all             ::1/128                 md5
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    #local   replication     postgres                                peer
    #host    replication     postgres        127.0.0.1/32            md5
    #host    replication     postgres        ::1/128                 md5

The difference is the "peer" or "md5" in the first two active lines. I am going to try switching those to peer next. Am I on the right track to a solution? (Edit: No, this did not help).

Password

I tried changing the default password of the server using:

    $ sudo -u postgres psql
    psql (9.3.4, Server 9.1.13)

    postgres=#  \password

I have no idea why this didn't work. Maybe the command only affects the 9.1 server and not the 9.3 server (Hopefully I can kill the old one now and properly set up the 9.3 server).

Related questions:

-https://stackoverflow.com/questions/18664074/getting-error-peer-authentication-failed-for-user-postgres-when-trying-to-ge

-https://stackoverflow.com/questions/2942485/psql-fatal-ident-authentication-failed-for-user-postgres?rq=1

I found a temporary solution to my problem. I edited the pg_hba.conf of the 9.3 server to say trust in the first two active lines. And after restarting (sudo service postgresql restart) I can connect to the server again using pgadmin. The downside is that the server might now not be password protected (I will open a separate question if I encounter any problems with that).

    # Database administrative login by Unix domain socket
    local   all             postgres                                trust

    # TYPE  DATABASE        USER            ADDRESS                 METHOD

    # "local" is for Unix domain socket connections only
    local   all             all                                     trust
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            md5
    # IPv6 local connections:
    host    all             all             ::1/128                 md5
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    #local   replication     postgres                                peer
    #host    replication     postgres        127.0.0.1/32            md5
    #host    replication     postgres        ::1/128                 md5


sysjobhistory "sql_severity" codes Where can I find out what the different "sql_severity" codes mean in the sysjobhistory table?

Eg what does 0 mean?

I've inherited a report that excludes ones with a 0 as "0 is not a real error"?

I googled and looked at MSDN etc etc.. but no real luck.

From here, the sql_severity column is the "severity of any SQL Server error."

When this column has the value 0, that means either no message was returned (and it's therefore a magic value), or a message with severity 0 was returned. I would argue this is a bad table design, as the column does not allow NULL.

It's almost the same thing with the sql_message_id column. This column will contain 0 when no message was returned -- in this case, though, it's purely a magic value, as there's no message id 0 (run SELECT * FROM sys.messages WHERE message_id = 0;).

You may want to clarify the intent of the report query you've inherited, because only comparing the sql_severity column may not do exactly what the author thought. (Severity 0 errors being raised are undoubtedly rare, but hey, you asked.)

If it's supposed to look for steps that returned a message, then it should check for sql_message_id > 0. As mentioned, it's completely possible that a job step can have a sql_severity > 0 and/or sql_message_id > 0 while still succeed. **
If the query is supposed to look for failed steps, it should be looking at the run_status column instead.

** You can try this yourself by creating and running a job with a T-SQL step that simply calls RAISERROR.