Check against multiple duplicate records I have data that i should insert into table.
But before inserting i need to check against duplicate records and report list of those records.

Table:

CREATE TABLE `test` (
  `A` varchar(19) NOT NULL,
  `B` varchar(9) NOT NULL,
  KEY `A` (`A`),
  KEY `B` (`B`)
) ENGINE=InnoDB;

I need to check for both columns:

Number of records to insert: ~1000

Rows in table: ~1.000.000

What is the efficient way of doing this.

Thanks in advance.

This would depend on the table's layout.

Suppose you have the following table

CREATE TABLE `mydata` (
    `A` varchar(19) NOT NULL,
    `B` varchar(9) NOT NULL,
    KEY `A` (`A`),
    KEY `B` (`B`)
) ENGINE=InnoDB;

Before you insert 1000 rows into mydata, you could do preload them into another table called mynewdata like this:

CREATE TABLE mynewdata LIKE mydata;
CREATE TABLE mynewdups LIKE mydata;
INSERT INTO mynewdata ... ;
INSERT INTO mynewdups SELECT * FROM mynewdata;

Next delete all rows in mynewdata that matches A or B in mydata

DELETE T1.* FROM mynewdata T1 INNER JOIN mydata T2 ON T1.A=T2.A OR T1.B=T2.B;

What's left in mydata are rows that do not have A or B matching

What about the rows that matched? Run this

DELETE T1.* FROM mynewdups T1 LEFT JOIN mydata T2
ON T1.A=T2.A OR T1.B=T2.B
WHERE T2.A IS NOT NULL;

What's left in mynewdata is data to import

What's left in mynewdups is data that had a dup key in mydata

Give it a Try !!!


MySQL boolean field search vs indexed varchar performance Will a select filtering on a boolean field be faster than a select filtering on an indexed varchar(255) field?
Specifically:
MySQL 5.7
In this case, records of a certain table should never be deleted.  Instead they should be marked as inactive if necessary.
A boolean 'is_active' field could be added to that table, or an existing 'status' indexed varchar field could be used (conceptually overloaded) to also store the value 'inactive' if necessary.  The varchar is specified as 255 max length, but realistically it is much smaller.
In this case, 90+% of the rows will be considered active, so the boolean is_active would be 0, or alternately the status would have some value other than 'inactive'; and most searches would be for is_active = 1 (ignoring inactive rows).
I have read that there is little value in indexing boolean fields, so whether the boolean option were indexed or not probably wouldn't be a factor (but if it is, please enlighten me!)

Short answer for bool vs varchar:  Probably not much diff, but it may depend.
Short answer for bool that is true 90% of the time:  The index is useless.
Long answer...
What percentage of the rows are "is_active"?  If it is more than about 20%, the index won't be used!.
Use reasonable limits on VARCHAR.  Even without that, 'inactive' is only 8 characters, not 255.  So, WHERE status = 'inactive' is not much slower than WHERE is_active.
Is that the entire WHERE clause?  I ask because Optimization requires looking at the entire WHERE clause.  For that matter, then entire SELECT can be important.
Often the query involves
WHERE is_active
  AND other-stuff

In which case, this may be very beneficial, even with 90% being active:
INDEX(is_active, other-column)

But, again, I need to see the details before giving a definitive answer.
And, no, the poor cardinality of is_active is not relevant in a composite index.


'+' operator with one operand! How does '+' operator behave in following statement?

select + 'taco';  --Result is 'taco'

Is it doing string concatenation with first string blank ('' + 'taco'), or does it mean something else?

How does '+' operator behave in following statement?

It is parsed as a unary plus, and ignored.

The following was given in answer to Connect item 718176 on the subject (Connect has since been retired, and no archive of this page is available):

    After some investigation, this behavior is by design since + is an unary operator. So the parser accepts "+ , and the '+' is simply ignored in this case.

    Changing this behavior has lot of backward compatibility implications so we don't intend to change it & the fix will introduce unnecessary changes for application code.

(Community Wiki answer generated from a comment on the question by Martin Smith)


mariadb ignoring force index I'm trying to tune a relatively simple query:

select bt.col1,bt.col2
from bigtable bt
join smalltable st on bt.smalltable_id = st.smalltable_id
where st.name = 'some name occuring only once in st'
limit 10

The number of matches in bigtable is relatively small compared to the overall size (< 1%)

Here's the explain plan:

+------+-------------+-------+--------+-----------------+---------+---------+-----------------+----------+-------------+
| id   | select_type | table | type   | possible_keys   | key     | key_len | ref             | rows     | Extra       |
+------+-------------+-------+--------+-----------------+---------+---------+-----------------+----------+-------------+
|    1 | PRIMARY     | bt    | ALL    | ix_smalltable_id| NULL    | NULL    | NULL            | 22709766 | Using where |
|    1 | PRIMARY     | st    | eq_ref | PRIMARY,ix_name | PRIMARY | 2       | bt.smalltable_id|        1 | Using where |
+------+-------------+-------+--------+-----------------+---------+---------+-----------------+----------+-------------+

Somehow, even though it would be easier to pickup the index, it doesn't.

I then tried to force the index:

select bt.col1,bt.col2
from bigtable bt force index (ix_smalltable_id)
join smalltable st on bt.smalltable_id = st.smalltable_id
where st.name = 'some name occuring only once in st'
limit 10

But the query plan and query time is the same. It doesn't want to use the index.

I tried doing where bt.smalltable_id in (select ...), but same query plan and time.

But if I fetch the smalltable_id first, and then embed it in the select, it's much faster.

The question

Can I force the index by enabling some flags? Is this a limitation of the query engine?

Table structure

CREATE TABLE `smalltable` (
  `smalltable_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`smalltable_id`),
  KEY `ix_name` (`name`(10))
) ENGINE=InnoDB AUTO_INCREMENT=5698 DEFAULT CHARSET=utf8

CREATE TABLE `bigtable` (
  `bigtable_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `col1` varchar(255) DEFAULT NULL,
  `col2` varchar(255) DEFAULT NULL,
  ...
  `smalltable_id` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`bigtable_id`),
  KEY `ix_smalltable_id` (`smalltable_id`)
) ENGINE=InnoDB AUTO_INCREMENT=23167374 DEFAULT CHARSET=utf8

I'm pretty sure the problem is the "partial" index:

CREATE TABLE `smalltable` (
  ...
  KEY `ix_name` (`name`(10))
)

Try running the query after adding an index on the full length of the column:

ALTER TABLE smalltable
    DROP INDEX ix_name
  , ADD INDEX ix_name_full (name) ;


How to create a partitioned table based on date? I'm trying to create a partitioned table where partitioning is determined by OrderDate. I'm trying to create a clustered index on OrderDate and a nonclustered primary key on OrderID. But I get an error saying:

  Msg 1908, Level 16, State 1, Line 1 Column 'OrderDate' is partitioning
  column of the index 'PK_OrderID'. Partition columns for a unique index
  must be a subset of the index key. Msg 1750, Level 16, State 0, Line 1
  Could not create constraint. See previous errors.

How to modify this to get it work:

CREATE TABLE dbo.Orders
    (
      OrderID INT NOT NULL ,
      Name NVARCHAR(20) NULL ,
      OrderDate DATE NOT NULL ,
    )
ON  PartSchemeOrders(OrderDate)

CREATE CLUSTERED INDEX IX_OrderDate
 on dbo.Orders (OrderDate)
 on PartSchemeOrders(OrderDate)

ALTER TABLE dbo.Orders
    ADD CONSTRAINT PK_OrderID PRIMARY KEY NONCLUSTERED  (OrderID)

As the error message says, any partition-aligned unique index has to include the partitioning key in the index key.  This requirement exists so the engine can enforce uniqueness on updates without checking every partition.

In your case, this means including OrderDate in the nonclustered index key, or having a non-aligned index.  Both are potentially valid choices, depending on your circumstances.  To preserve alignment, your table and index definitions would be:

CREATE TABLE dbo.Orders
(
    OrderID     integer NOT NULL,
    Name        nvarchar(20) NULL,
    OrderDate   date NOT NULL,

    CONSTRAINT PK__Orders_OrderID_OrderDate
        PRIMARY KEY NONCLUSTERED
            (OrderID, OrderDate)
        ON PS (OrderDate)
)
ON PS (OrderDate);
GO
CREATE CLUSTERED INDEX CX__Orders_OrderDate
ON dbo.Orders (OrderDate)
ON PS (OrderDate);

Of course this changes the uniqueness that the nonclustered index enforces.  Now, only the combination of OrderID and OrderDate is guaranteed to be unique.  It is theoretically possible to add duplicate OrderIDs, so long as the OrderDate is different.  Whether this change of semantic is acceptable to you depends on your circumstances, but it is something to be aware of.

The alternative is to have the nonclustered primary key non-aligned:

CREATE TABLE dbo.Orders
(
    OrderID     integer NOT NULL,
    Name        nvarchar(20) NULL,
    OrderDate   date NOT NULL,

    CONSTRAINT PK__Orders_OrderID
        PRIMARY KEY NONCLUSTERED
            (OrderID)
        ON [PRIMARY]
)
ON PS (OrderDate);
GO
CREATE CLUSTERED INDEX CX__Orders_OrderDate
ON dbo.Orders (OrderDate)
ON PS (OrderDate);

This preserves the uniqueness of OrderID alone, and has some benefits with queries that compute MIN or MAX aggregates using the index, but you lose the ability to SWITCH partitions in and out without dropping the primary key and recreating it after the switching operation.

You can read more about partitioning in this section of Books Online and more on the issues of aligned and non-aligned indexes in this excellent answer by Remus Rusanu.


Linked server in SQL Server 2005 using NT authentication impersonation I am trying to Link a server using an NT authentication as the impersonated connection, but this connection will be static to a specific user instead of passing the currently logged-in user's credentials.  Is there any way to do this without setting up a new SQL-server account on the server I am linking to and using that for an unlisted user.

If it was possible to connect to a SQL Server without setting up a security context on that server, that would be a serious security hole.  So, there must be a security context.

If you can ride on some existing rights that are already defined, then fine, but likely any existing security context was not tailored for your need. And since you want a specific account it is further complicated.

Perhaps you could have a SQL Agent job created on the RemoteServer that would periodically PUSH the data to a staging table on your LocalServer.  (This would also allow the RemoteServer administrators to control what happens, just in case that is an issue.)

Then you would grant rights on your LocalServer to allow the RemoteServer's job to insert data into the staging table on your LocalServer.

Note: When SQL Agent runs a job using a credential it is not impersonating the account, but should be viewed as logging in using the credential's account name and password.


SQL Trying to sum and group by distinct values I have a table

+-------+----------+------------+----------+---------+
| Plant | LineName | WorkCenter | Material | ProdQty |
+-------+----------+------------+----------+---------+
| x     | xl       | xl1        | y1       |       1 |
| x     | xl       | xl2        | y1       |       1 |
| x     | xl       | xl3        | y1       |       1 |
| x     | xl       | xl1        | y2       |       1 |
| x     | xl       | xl2        | y2       |       1 |
| x     | xl       | xl3        | y2       |       1 |
+-------+----------+------------+----------+---------+

I am trying to count the number of different materials, and sum the quantities along the LineName, so that I get something like this:

+----------+------+---------+
| LineName | Cmat | ProdQty |
+----------+------+---------+
| xl       |    2 |       2 |
+----------+------+---------+

Instead, when I use a combination of count distinct, sum and group by, I get an incorrect result:

+----------+------+---------+
| LineName | Cmat | ProdQty |
+----------+------+---------+
| xl       |    2 |       6 |
+----------+------+---------+

How should I do this correctly? I've tried with

SELECT LineName, COUNT(DISTINCT(Material) as Cmat, SUM(ProdQty) as ProdQty
FROM table
GROUP BY LineName

But it does not create the desired result. I've been looking on stack exchange for some similar topics, but they don't seem to match my question.

Following confirmation in the comments it seems that you need this

WITH Dist
     AS (SELECT DISTINCT LineName,
                         Material,
                         ProdQty
         FROM   [table])
SELECT LineName,
       COUNT(Material) AS Cmat,
       SUM(ProdQty)    AS ProdQty
FROM   Dist
GROUP  BY LineName


language detection column content I have a Postgresql table that has contents of a column either in English or Spanish Like so:

202707  Redness or swelling on the cheek.
202575  Preguntas acerca de medicamentos antivirales de la gripe.
202433  Sounds very sick or weak to the triager.
201505  El paciente quiere ser visto.

I would like to query such that I can get only the English rows or only the Spanish rows.

I have read a bit about PostgreSQL dictionaries, to_tsvector and to_tsquery and I feel like there should be a easy way to identify a string as a English/Spanish.

PostgreSQL doesn't have an in-core natural language identifier.  I'm not aware of direct PostgreSQL extensions to do it either.  But there are lot of language identifiers out there, and it shouldn't be too hard to map one of them into PostgreSQL.

I would do that using pl/perl or pl/perlu to wrap an existing Perl module, like in this answer.  But pl/pythonu would be another good choice, but one I have less personal experience with.

You will probably want to store the determined language in a separate database column, rather than computing it on the fly all the time.  Doing it that way also means you can do the detection in the app, rather than in the database.


sp_delete_backuphistory not working I am using sql Server 2008 R2.
We are using below query for reducing the msdb size.
use msdb exec sp_delete_backuphistory '01-MAR-2020'

When i execute this query in  server whose msdb size is 15GB no rows were deleted.
Some testing server with lesser rows it worked fine.
Why  is it  not working ? How to reduce the msdb size is there any other method?

Delete Old Backup History
You might want to have a look at this stored procedure I created some time back. Instead of entering a date it gets the current date and goes 1080 days (default) back in time and starts deleting the backup history in steps of 1 (default) up until 180 days (default).
With a large msdb database you might have a lot of info that takes a long time to delete. Entering a simple date using the sp_delete_backuphistory procedure can cause issues, because it has to go a long way back in time to delete the information from the various tables.
My stored procedure is just a wrapper which automates the task of deleting old backup history using the built-in sp_delete_backuphistory (Microsoft | SQL Docs) procedure. The advantage of my procedure is that you don't have to manually enter dates to clean up the history in large msdb database, but can use days instead and can turn on transactional handling.

    use msdb
    go

    CREATE PROCEDURE [dbo].[spdeletehistory]
    /*
    -- ==================================================================
    -- Author......:    hot2use
    -- Date........:    04-Jan-2006
    -- Version.....:    1.3
    -- Server......:    [server name here]
    -- Database....:    msdb
    -- Name........:    spdeletehistory
    -- Owner.......:    dbo
    -- Table.......:
    -- Type........:    Stored Procedure
    -- Description.:    Delete old backup history on a server
    -- History.....:    04-Jan-2006    1.0    First created
    --                  07-Dec-2005    1.1    Modified seperators
    --                  04-Jul-2006    1.2    Modified Text Output for return value
    --                                        Added Constants for defaults
    --                  25-Jan-2017    1.3    Slight modifications regarding comments
    --
    -- Editor......:    UltraEdit 11.10a (using Syntax Highlighting)
    --                    Tabstop Values = 4
    -- ==================================================================
    */

        /*input variables*/
        @iDaysBackToStart int = 0,
        @iDaysToKeep int = 0,
        @iDayStep int = 0,
        @iDebug int = 0

        /*output variables*/

    AS
    BEGIN
         /* Turn off double quotes for text strings */
        set quoted_identifier off
          /* Dont return the count for any statment */
        set nocount on
         /* debugging configuration */
        declare @debug int
         /* debug settings
        1 = turn on debug information
        2 = turn off all possible outputs
        4 = turn on transaction handling
         e.g.: Adding an @iDebug paramter of 6 will...
        ... turn on transaction handling (4)
        ... turn off all possible output information (2)
         e.g.: Adding an @iDebug value of 1 will turn on all debugging information
        */

        set @debug = @iDebug

        /* day constants */
        declare @iDaysBackToStart_CONST int
        declare @iDaysToKeep_CONST int
        declare @iDayStep_CONST int

        /* constant settings
        set the defaults here instead of in the input variables
        it makes for easier changing, when comparing the default values in the code
        */
        set @iDaysBackToStart_CONST = 1080
        set @iDaysToKeep_CONST = 180
        set @iDayStep_CONST = 1

        if @iDaysBackToStart = 0
            BEGIN
                set @iDaysBackToStart = @iDaysBackToStart_CONST
            END

        if @iDaysToKeep = 0
            BEGIN
                set @iDaysToKeep = @iDaysToKeep_CONST
            END

        if @iDayStep = 0
            BEGIN
                set @iDayStep = @iDayStep_CONST
            END
        /*
        return values
        0 Successful execution.
        1 Required parameter value not specified.
        2 Invalid parameter value specified.
        3 not defined
        4 not defined
        */
        declare @iRetVal int

        declare @dtDateToDelete datetime
        declare @dtCurrentDate datetime
        declare @dtStartDate datetime
        declare @dtStopDate datetime
        declare @vSQL nvarchar(2000)

        if @debug & 1 = 1  print 'Checking variables...'
        if (@iDaysToKeep < @iDaysToKeep_CONST)
            BEGIN
                PRINT 'Invalid parameter value specified (1)'
                PRINT 'e.g. spdeletehistory @iDaysBackToStart = 1080, @iDaysToKeep = 180, @iDayStep = 1 '
                PRINT 'The default values are the above values'
                PRINT '@iDaysBackToStart must be >= ' + convert(varchar(20), @iDaysBackToStart_CONST) + ''
                PRINT '@iDaysToKeep must be >= ' + convert(varchar(20), @iDaysToKeep_CONST) + ''
                PRINT '@iDayStep must be > ' + convert(varchar(20), @iDayStep_CONST - 1) + ''
                PRINT ''
                RETURN(2)
            END
        else if (@iDaysToKeep < @iDaysToKeep_CONST) or (@iDaysBackToStart < @iDaysBackToStart_CONST)
            BEGIN
                PRINT 'Invalid parameter value specified (2)'
                PRINT 'e.g. spdeletehistory @iDaysBackToStart = 1080, @iDaysToKeep = 180, @iDayStep = 1 '
                PRINT 'The default values are the above values'
                PRINT '@iDaysBackToStart must be >= ' + convert(varchar(20), @iDaysBackToStart_CONST) + ''
                PRINT '@iDaysToKeep must be >= ' + convert(varchar(20), @iDaysToKeep_CONST) + ''
                PRINT '@iDayStep must be > ' + convert(varchar(20), @iDayStep_CONST - 1) + ''
                PRINT ''
                RETURN(2)
            END
        else
            BEGIN
                if @debug & 1 = 1  print 'Checking if just defaults have been used...'
                if (@iDaysToKeep = @iDaysToKeep_CONST) and (@iDayStep = @iDayStep_CONST) and (@iDaysBackToStart = @iDaysBackToStart_CONST)
                    BEGIN
                        if @debug & 2 <> 2
                            BEGIN
                                PRINT ''
                                PRINT 'Running with default parameters'
                                PRINT ''
                                PRINT '@iDaysBackToStart ='  + convert(varchar(20), @iDaysBackToStart_CONST) + ''
                                PRINT '@iDaysToKeep = ' + convert(varchar(20), @iDaysToKeep_CONST) + ''
                                PRINT '@iDayStep = ' + convert(varchar(20), @iDayStep_CONST) + ''
                                PRINT ''
                            END
                    END
                -- if @debug & 1 = 1
                if @debug & 1 = 1  print 'Settings date variables...'
                select @dtCurrentDate = getdate()
                if @debug & 1 = 1  print '@dtCurrentDate..: ' + convert(varchar(60), @dtCurrentDate, 20)
                select @dtStartDate = @dtCurrentDate - @iDaysBackToStart
                if @debug & 1 = 1  print '@dtStartDate....: ' + convert(varchar(60), @dtStartDate, 20)
                select @dtStopDate = @dtCurrentDate - @iDaysToKeep
                if @debug & 1 = 1  print '@dtStopDate.....: ' + convert(varchar(60), @dtStopDate, 20)
                select @dtDateToDelete = @dtStartDate
                if @debug & 1 = 1  print '@dtDateToDelete.: ' + convert(varchar(60), @dtDateToDelete, 20)

                if @debug & 1 = 1  print 'Starting to loop...'
                while @dtDateToDelete < @dtStopDate begin

                    set @vSQL = 'msdb.dbo.sp_delete_backuphistory ''' + convert(varchar(60), @dtDateToDelete, 20) + ''''
                    if @debug & 1 = 1  print '@vSQL.........: ' + @vSQL
                    if @debug & 4 = 4
                        BEGIN
                            begin tran john
                        END
                    exec @iRetVal = sp_executesql @vSQL
                    if @iRetVal <> 0
                        BEGIN
                            /* Turn on double quotes for text strings */
                            set quoted_identifier on

                             /* Return the count for any statment */
                            set nocount off

                            /* If transactions have been turned on then rollback if failed */
                            if @debug & 4 = 4
                                BEGIN
                                    rollback tran john
                                END

                            /* If general output has not been turned off print output*/
                            if @debug & 2 <> 2
                                BEGIN
                                    PRINT 'Return Value: ' + convert(varchar(100), @iRetVal) + ' at current time: ' + convert(varchar(20),getdate())
                                end
                            RETURN(@iRetVal)
                        END

                    /* If transactions have been turned on then commit on success */
                    if @debug & 4 = 4
                        BEGIN
                            commit tran john
                        END

                    /* If general output has not been turned off print output*/
                    if @debug & 2 <> 2
                        BEGIN
                            PRINT 'Return Value: ' + convert(varchar(100), @iRetVal) + ' at current time: ' + convert(varchar(20),getdate())
                        end
                    /* Get next date to delte history data */
                    select @dtDateToDelete = @dtDateToDelete + @iDayStep
                end
            END
        /* Turn on double quotes for text strings */
        set quoted_identifier on
         /* Return the count for any statment */
        set nocount off
        /* If general output has not been turned off print output*/
        if @debug & 2 <> 2
            BEGIN
                print 'Finished.'
            END
        RETURN(0)
    END
    GO

Once you have the procedure in your msdb database, then run the query via:
msdb.dbo.spdeletehistory

The stored procedure will then run with the default values:
@iDaysBackToStart = 1080
@iDaysToKeep = 180
@iDayStep = 1

You can change these values to meet your requirements. For example:
spdeletehistory @iDaysBackToStart = 2000, @iDaysToKeep = 180, @iDayStep = 2

If you want lower values than the defaults, then you will have to modify the @i..._CONST values in the stored procedure, as the defaults are the lowest values possible.
If you want to see what happens, then set the @iDebug parameter (bitwise):
/* debug settings
1 = turn on debug information
2 = turn off all possible outputs
4 = turn on transaction handling
e.g.: Adding an @iDebug paramter of 6 will...
... turn on transaction handling (4)
... turn off all possible output information (2)
e.g.: Adding an @iDebug value of 1 will turn on all debugging information
*/

E.g. turning on @iDebug = 4 will delete the history in transactions which can reduce the overall impact on the msdb database.
Example Run
Running the following command:
msdb.dbo.spdeletehistory @iDaysBackToStart = 1080, @iDaysToKeep = 180, @iDayStep = 1, @iDebug = 7

Produces the following output:

Checking variables...
Checking if just defaults have been used...
Settings date variables...
@dtCurrentDate..: 2020-07-03 13:25:20
@dtStartDate....: 2017-07-19 13:25:20
@dtStopDate.....: 2020-01-05 13:25:20
@dtDateToDelete.: 2017-07-19 13:25:20
Starting to loop...
@vSQL.........: msdb.dbo.sp_delete_backuphistory '2017-07-19 13:25:20'
@vSQL.........: msdb.dbo.sp_delete_backuphistory '2017-07-20 13:25:20'
@vSQL.........: msdb.dbo.sp_delete_backuphistory '2017-07-21 13:25:20'
@vSQL.........: msdb.dbo.sp_delete_backuphistory '2017-07-22 13:25:20'
@vSQL.........: msdb.dbo.sp_delete_backuphistory '2017-07-23 13:25:20'
@vSQL.........: msdb.dbo.sp_delete_backuphistory '2017-07-24 13:25:20'
....
@vSQL.........: msdb.dbo.sp_delete_backuphistory '2020-01-04 13:25:20'

This should clear up old backup information in the msdb database.
However, it will not shrink the msdb database.  That's a different issue, which can be achieved with ...
DBCC SHRINKFILE
The DBCC SHRINKFILE (Microsoft | SQL Docs) command can be used to reclaim space that was once used in a database. It is not recommended for general use, as a database will normally reclaim the released space again in most cases, but seeing as you have a rather large msdb database....
USE [msdb]
GO
DBCC SHRINKFILE (N'MSDBData' , 0, TRUNCATEONLY) -- release unused space
GO

USE [msdb]
GO
DBCC SHRINKFILE (N'MSDBData' , 96) -- shrink mdf data file to 96 MB size
GO

Please be aware that if the msdb database is under pressure that you might not be able to actually shrink the database in one go.
You might not even be able to shrink the database down to 98 MB. If you right click the msdb database and then select: TASK... | Shrink ... | Files ... you will be presented with a dialog that will tell you the minimum size, that the database file(s) can be shrunk to. Take that value and add it to the second command:
USE [msdb]
GO
DBCC SHRINKFILE (N'MSDBData' , 1024) -- shrink mdf data file to 1 GB size
GO

Good luck.


Alter procedure in mysql I need to alter  existing procedure in MYSQL, so as per I know we don't have that option in MYSQL.  Every time we need to drop procedure and recreate,  so is there any way  we can  alter procedure  without dropping the existing one.

According to MySQL Docs, you can alter a procedure using ALTER PROCEDURE, unless you need to change the procedure's parameters or it body.

  This statement can be used to change the characteristics of a stored procedure. More than one change may be specified in an ALTER PROCEDURE statement. However, you cannot change the parameters or body of a stored procedure using this statement; to make such changes, you must drop and re-create the procedure using DROP PROCEDURE and CREATE PROCEDURE.


Daily restore from sql server transaction log Our client will ship a database full dump on day 1 and daily transaction log from day 2. We will restore the full dump on day 1 and plan to restore the transaction log daily since day 2. Is it possible to just restore the daily transaction log? Because I read in order to restore a transaction log backup, the database has to be in a restoring state which means that a full backup or a full backup and differential backup were first restored using the NORECOVERY option to allow additional backups. Does it mean on everyday in order to restore the daily transaction log we have to first restore the full dump and leave the db in NORECOVERY mode and restore all the transaction logs before current day and leave the db in NORECOVERY mode?

Is it possible to just restore the daily transaction log?

As you already pointed out, restoring just the transaction log is NOT possible you have to either restore full backup or full and differential backup to restore transaction log backups.

  Does it mean on everyday in order to restore the daily transaction log we have to first restore the full dump and leave the db in NORECOVERY mode and restore all the transaction logs before current day and leave the db in NORECOVERY mode?

Yes. Are you aware about transaction log shipping this might suit your requirement.


Multiple "model" DBs -- or db creation 'profiles' I know you can't actually have multiple 'model' DBs on the same server instance. BUT, I'd like to have something almost like it.  Basically, we have a business model where each customer is its own DB, and these DBs are all very similar in structure & definition (different in actual data/record content of course).  And then we have some 'global' DBs as well that are accessed and joined-to from each customer-DB.

So when we obtain a new customer, we need to create their DB.  We want the DB to be pre-populated with certain tables/procs, and certain pre-seeded data that is pretty consistent (one could say duplicated/redundant - yes, that'd be fair, but it's also very customizable and doesn't necessarily remain redundant after we start customizing that client's configuration).

Our initial solution was to build all of that into the [model] DB.  However, it's tailored to a particular kind of client - say, California.  Now we need to model, say, a Michigan client, and create a number of new DBs based on that.  So this is a problem because we need to kill our [model] and put all that CA model-data/structures somewhere else.

My thought was, let's just create a 'template' DB for each state, and when we need to create a new client DB, we just restore-from template's full-backup file (doing the Rename in the process of course).  Is this the thing to do?  Or is there a better way?

I agree I would create an example of each type and then back each up.  Once they are backed up you can drop the examples.  Then when you need a new "California" type you just run something like this:

RESTORE DATABASE <NewDBName> FROM DISK = 'x:\ModelDBs\California.bak'
WITH FILE = 1,
MOVE 'CaliforniaDataFile' TO
    'E:\Data Files\<NewDBName>.mdf',
MOVE 'CaliforniaLogFile' TO
    'F:\Log Files\<NewDBName>.ldf';

You could even create a template to generate most of the script for you.

The other option is to create "creation" scripts for each.  Create a blank DB and run your creation script for the appropriate type.  This can be automated to a large degree but I still like the backup/restore method better.


How do I add minutes to a time data type? I have a stored procedure which inserts two records into a table, the difference between the records is that the time column of the second record is @MinToAdd after the first:

CREATE PROCEDURE CreateEntry
    /*Other columns*/
    @StartTime time(2),
    @EndTime time(2),
    @MinutesToAdd smallint
    AS
BEGIN
    SET NOCOUNT ON;

    SET @MinutesToAdd = @MinutesToAdd % 1440;   --Prevent overflow if needed?
    IF (@MinutesToAdd > 0)
    BEGIN
    INSERT INTO ClientNotification (/*Other columns*/ startTime, endTime)
        OUTPUT inserted.id
        VALUES
               (/*Other columns*/ @StartTime, @EndTime),
               (/*Other columns*/ @StartTime + @MinutesToAdd, @EndTime + @MinutesToAdd);
    END
    ELSE
    BEGIN
        /*Whatever ELSE does.*/
    END
END

What is the correct way to add @MinutesToAdd minutes to @StartTime and @EndTime?
Please note I am using the time data type.

Update:
A correct answer should contain the following information:

How to add minutes to to a time data type.
That the proposed solution does not result in a loss of precision.
Issues or concerns to be aware of in the event that the minutes would be to too large to fit in a time variable, or risk of rolling the time variable over.  If there are no issues then please state so.

You can't use lazy shorthand arithmetic with the new types. Try:

DATEADD(MINUTE, @MinutesToAdd, @StartTime)

Note that even though you have protected your @MinutesToAdd from overflow, you haven't protected the result from overflow. This doesn't yield an error, however, just might not be the result you're expecting.

DECLARE @StartTime TIME(0) = '23:59';
DECLARE @MinutesToAdd INT = 20;

SELECT DATEADD(MINUTE, @MinutesToAdd, @StartTime);

Result:

00:19:00

I assume this must go through some type of internal conversion, because you couldn't get that result by saying:

DECLARE @StartTime TIME(0) = '24:19';

Result:

  Msg 241, Level 16, State 1, Line 1
  Conversion failed when converting date and/or time from character string.

You need to consider how you want to handle calculations that lead to either @EndTime or both @StartTime and @EndTime to be in the next day.

Also - to address another new requirement in your "ideal answer" - there is no loss of precision. As per the documentation, the return type of DATEADD is the same as the input:

  The return data type is the data type of the date argument, except for string literals.

Therefore, TIME in, TIME out.


Determine whether there is any blocking while deleting large number of records I would like to know whether there is any way to determine why it is taking too much time say 3 minutes for deleting 50K records from a database hosted in Sql Azure.
I have clustered index on primary key and a non-clustered index on ID which is being used in where condition in Delete Query

DECLARE @RowsDeleted INTEGER
SET @RowsDeleted = 1

WHILE (@RowsDeleted > 0)
BEGIN
    DELETE top(100) FROM table WHERE ScenarioID= @ID
    SET @RowsDeleted = @@ROWCOUNT
END

Is there any way to know whether there are any locks on this table

indexes:

ALTER TABLE [dbo].[ActCost] ADD  CONSTRAINT [PK_ActCost] PRIMARY KEY CLUSTERED
(
    [ActCostID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
GO

 Alter NONCLUSTERED INDEX [IX_ActCost_ScenarioID] ON [dbo].[ActCost] ([ScenarioID] ASC)
INCLUDE ( <list_all_other_columns_returned> );
GO

CREATE TABLE [dbo].[ActCost](
    [ActCostID] [int] IDENTITY(1,1) NOT NULL,
    [ActID] [int] NOT NULL,
    [ActCostTypeID] [int] NOT NULL,
    [Description] [nvarchar](200) NOT NULL,
    [Cost] [float] NOT NULL,
    [CostPerProductUnit] [float] NOT NULL,
    [CostPerEndProductUnit] [float] NOT NULL,
    [OtherValue] [float] NULL,
    [OtherID] [int] NULL,
    [Comment1] [nvarchar](200) NULL,
    [Comment2] [nvarchar](200) NULL,
    [OPerProductUnit] [float] NULL,
    [OPerHour] [float] NULL,
    [OCostPerUnit] [float] NULL,
    [OCostPerHour] [float] NULL,
    [PerfEnh_ProcessID] [int] NOT NULL,
    [PerfEnh_MillID] [int] NOT NULL,
    [ScenarioID] [int] NOT NULL,
 CONSTRAINT [PK_ActCost] PRIMARY KEY CLUSTERED
(
    [ActCostID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
)

GO

Azure Space :
Available 2 GB
Used :500MB

I have another database with same data but with more Available Size which works fine i mean it takes just 20-30 seconds to do the same job , Is it going to be a factor ?

Blocking inspection :

The title says blocking, but the question says locking. I'll deal with the former since the latter should be a given: yes, locks need to be taken in order to perform a delete.

To check for blocking, in the window where you're performing the deletes:

SELECT @@SPID; -- make note of this

In another window:

SELECT blocking_session_id, wait_type
  FROM sys.dm_exec_requests
  WHERE session_id = <spid from above>;

I know this DMV was available in the last two SQL Database refreshes, but not quite sure what version you're on.

Now, if you get a blocker, check what they are doing.

SELECT r.[status], r.command, r.blocking_session_id,
    r.wait_type, query = SUBSTRING(CONVERT(NVARCHAR(MAX),
    t.[text]), r.statement_start_offset/2,
    COALESCE(NULLIF(r.statement_end_offset,-1),
    LEN(CONVERT(NVARCHAR(MAX),t.[text]))*2)/2)
  FROM sys.dm_exec_requests AS r
  OUTER APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS t
  WHERE r.session_id = <blocking session>;

You can't run DBCC INPUTBUFFER in this case because in SQL Database this is somewhat protected - you can only run this against your own @@SPID.

It might also be that log file autogrowth is the problem - if the clustered index delete here actually has to go and delete rows from many indexes and/or indexed views, the problem could be a log bottleneck. My usual go-to here is the default trace, but I don't believe you have access to that in Azure. Thankfully in your scenario we can see that this is the case due to the wait type shown - your query is actually being slowed down intentionally by the system because you're generating too much log activity. I'm not sure exactly how this algorithm works, but if it is happening when you delete 100 rows, it may be time to migrate to an instance with better "guaranteed" performance (which may mean paying more). Simply spinning up a new instance on the same plan may even help, as you'll likely end up on newer generation hardware which should naturally perform log operations quicker and may help you avoid hitting the governor.

You should also be committing transactions at some level of frequency within the loop. Otherwise chunking doesn't really help because you're likely still holding the locks on the first 100 rows while you're trying to delete the last 100 rows. Committing smaller chunks more frequently will have less impact on the log and potentially on locking/blocking too.

DECLARE @RowsDeleted INT = 1, @counter INT = 1;

BEGIN TRANSACTION;

WHILE (@RowsDeleted > 0)
BEGIN
    DELETE top(100) FROM dbo.table WHERE ScenarioID = @ID;
    SELECT @RowsDeleted = @@ROWCOUNT, @counter += 1;
    IF @counter % 10 = 0
    BEGIN
      COMMIT TRANSACTION;
      BEGIN TRANSACTION;
    END
END

COMMIT TRANSACTION;

You can also put a waitfor in the loop - maybe pausing long enough will prevent tripping the log rate governor.


help me to migrate single myslq5.6 server to mysql5.6 Cluster, follow this error! I try to migrate a single myslq5.6 server to mysql5.6 Cluster. I do not understand why I get the follow this error:

  ERROR 1297 (HY000) at line 1 in file: 'load_dept_emp.dump': Got temporary error 233 'Out of operation records in transaction coordinator (increase MaxNoOfConcurrentOperations)' from NDBCLUSTER

Thanks for everyone, i have just found a way to slove my question. follow here:
1.you must configure the config.ini file in your MGM nodes
/opt/mysql-cluster/etc/config.ini
Setting this parameter of "MaxNoOfConcurrentTransactions" a bigger value
2.if you reconfigure config.ini, you must delete this file of ndb_2_config.bin.1, and then restart MGM Services
ndb_2_config.bin.1 is a cache and binary file
3.in fact, if you recofingure confing.ini, you can use "# ndb_mgmd --ndb_nodeid=1 --initial -f /var/lib/mysql-cluster/config.ini" for updating ndb_2_config.bin.1 effective.
try it!


Removing filestream from a database I am trying to remove filestream from my database, so I have to do the following

I would like to know how to remove the following:

ALTER TABLE AMGR_Documents_Tbl ADD UId uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT( NEWID() );
ALTER TABLE AMGR_Documents_Tbl ADD CONSTRAINT AMGR_Documents_Tbl_UId_Unique UNIQUE( UId );`

Then I need to Add a column with no filestream which probably is the following code:

ALTER TABLE AMGR_Letters_Tbl ADD TextCol1 varbinary( max );
Update [AMGR_Letters_Tbl]
SET TextCol1 = TextCol;
ALTER TABLE [AMGR_Letters_Tbl] DROP COLUMN TextCol;
/*Now i just rename the column*/

I also need to fix the FTS index (Which i do not understand) after which is the following:

    IF 0 != INDEXPROPERTY( OBJECT_ID( 'AMGR_Letters_Tbl' ), 'Letters_Record_Id', 'IsFulltextKey' ) BEGIN
                EXEC sp_fulltext_table AMGR_Letters_Tbl, 'drop'
            END

/****** I need to fix filestream on both tables:  ******/
/****** This is what the whole thing looks like (for 2 tables):  ******/
IF @@VERSION NOT LIKE 'Microsoft SQL Server 2005%' AND ISNULL(SERVERPROPERTY ('FilestreamEffectiveLevel'),0) > 0 BEGIN
    if not exists   ( select name from syscolumns where id IN
                ( select Id from sysobjects where id = object_id(N'[dbo].[AMGR_Letters_Tbl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
                ) AND name = 'UId' )
    BEGIN
        -- add unique ROWGUIDCOL column
        ALTER TABLE AMGR_Letters_Tbl ADD UId uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT( NEWID() );
        ALTER TABLE AMGR_Letters_Tbl ADD CONSTRAINT AMGR_Letters_Tbl_UId_Unique UNIQUE( UId );
        EXEC( 'ALTER TABLE AMGR_Letters_Tbl ADD TextCol1 varbinary( max ) FILESTREAM NULL' );
        -- will need to re-create FTS index after
        IF 0 != INDEXPROPERTY( OBJECT_ID( 'AMGR_Letters_Tbl' ), 'Letters_Record_Id', 'IsFulltextKey' ) BEGIN
            EXEC sp_fulltext_table AMGR_Letters_Tbl, 'drop'
        END
        EXEC( 'UPDATE AMGR_Letters_Tbl SET TextCol1 = TextCol;' );
        ALTER TABLE AMGR_Letters_Tbl DROP COLUMN TextCol;
        EXEC sp_rename 'AMGR_Letters_Tbl.TextCol1', 'TextCol', 'COLUMN';
        EXEC ('DBCC CLEANTABLE (0,''AMGR_Letters_Tbl'',1000) WITH NO_INFOMSGS;');
    END
END
GO
------------------------------------------------------------

IF @@VERSION NOT LIKE 'Microsoft SQL Server 2005%' AND ISNULL(SERVERPROPERTY ('FilestreamEffectiveLevel'),0) > 0 BEGIN
    if not exists   ( select name from syscolumns where id IN
                ( select Id from sysobjects where id = object_id(N'[dbo].[AMGR_Documents_Tbl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
                ) AND name = 'UId' )
    BEGIN
        -- add unique ROWGUIDCOL column
        ALTER TABLE AMGR_Documents_Tbl ADD UId uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT( NEWID() );
        ALTER TABLE AMGR_Documents_Tbl ADD CONSTRAINT AMGR_Documents_Tbl_UId_Unique UNIQUE( UId );
        EXEC( 'ALTER TABLE AMGR_Documents_Tbl ADD Data1 varbinary( max ) FILESTREAM NULL' );
        -- will need to re-create FTS index after
        IF 0 != INDEXPROPERTY( OBJECT_ID( 'AMGR_Documents_Tbl' ), 'AMGR_Documents_Record_Id', 'IsFulltextKey' ) BEGIN
            EXEC sp_fulltext_table AMGR_Documents_Tbl, 'drop'
        END
        EXEC( 'UPDATE AMGR_Documents_Tbl SET Data1 = Data;' );
        ALTER TABLE AMGR_Documents_Tbl DROP COLUMN Data;
        EXEC sp_rename 'AMGR_Documents_Tbl.Data1', 'Data', 'COLUMN';
        EXEC ('DBCC CLEANTABLE (0,''AMGR_Documents_Tbl'',1000) WITH NO_INFOMSGS;');
    END
END
GO

To completely remove FILESTREAM features from a database, you need to perform the following steps.

Delete all FILESTREAM columns from all tables
Disassociate tables from FILESTREAM filegroups
Remove all FILESTREAM Data Containers (filegroup files – you might have more than one of them)
Remove all FILESTREAM filegroup (there may be more than one of them)

Walkthrough Click Here and follow his steps

Another Note: if you have Data in your FileStream file you will not able able to delete it. sp_filestream_force_garbage_collection unfortunately this only works >= SQL Server 2012
For that fix you can See the Whole Thing Here

If that doesn't work. try to make a backup for it.
That is how I cleared my FileStream file.

When it is Cleared then you can Delete it FileStream File and FILESTREAM filegroup.

If your database in heavily indexed, you should check you Functions and View, some might go missing. (Have a Back up of that)


Inventory Database Design? Get historical data from DB for reporting & accounting Following is the design of my db:

Product:

ID
Name
Quantity in Hand (as on date - total across all format)
Reorder_point
Remarks

Warehouse:

ID
Name
Address
Remarks
is_default

Inventory:

ID
Product FK
Warehouse FK
Quantity
Purchased_on (This field is to be used to consider product ageing)
Purchase Rate
Tentative Sales Rate
MRP

Purchase Order/Sales Invoice Table (Both are similar)

ID
Total
Date
Warehouse FK
Vendor FK/Customer FK
Amount Paid/Amount Received

Line Item for Purchase/Sales

Product FK
Unit FK (We have a unit table to maintain conversion from one unit to other, eg: Dozen to Nos, etc)
Quantity
purchase_price/sales_price

Now, I wanted to know:

Is this a good DB design, or does it have an inherent fault somewhere?
Suppose two products, varying only in flavor - how would that be reflected here? (They may not need separate product ID/SKU)
Most importantly, I can always get the inventory in hand, but let's say, today is 2nd May & I need to know inventory details as on 15th April, how can this db design do it? Or is there a better DB design to handle that?

Edit

As of now let's assume purchase and sales happen immediately (as in, there's no purchase order & sales order in between).

Now, the issue with the inventory table is I can't calculate the historical value of the inventory. As in, lets say the value of the inventory in 3rd May s $500000, how can I find out the inventory value in 10th April from the data I have here?

Answer originally left in comments by Nick McDermaid

It depends on your requirements. In the current model you can't record the same order, different item taken from a warehouse on a different day - the date is at order level.
You could add a flavour attribute either to the Product table (in which case it gets a new id), or to the LineItem table.
Maybe. If the correct product IN date is in Inventory, and the correct product OUT date is in the purchase order header you can. But this doesn't appear to have the correct level of detail for inventory, more sales... depends.

By correct level of detail, what I mean is: Does PurchaseOrder.Date record when every widget left the warehouse? I don't think so. It most likely records when you a purchase order was raised. That PO might consist of 5 widgets. Three were picked and supplied from warehouse A on 1st May, and two were picked and supplied from warehouse B on 3rd May. I don't think your current set of tables have enough detail to describe when things left the warehouse (unless they all leave the same warehouse immediately that you get the PO, highly unlikely).

You don't have a 'left warehouse' transaction table so you don't know what date items left the warehouse so you can't work back and calculate inventory. warehouse db's usually have a 'warehouse transaction' table that records when items arrived and left the warehouse. You don't have that in your model. PurchaseOrder has a date but it doesn't really record when items left. Inventory has a date but it probably doesn't really record when the item arrived. You also need to periodically record stocktake levels.

To "find out the inventory value in 10th April from the data I have here" you take the current inventory value (today) and reverse all the inventory in transactions back to 10th April (from inventory table I guess), and also reverse all the sale (inventory out) transactions and you'll arrive at where you were on 10th April. As long as you are recording all inventory in and out transactions, and you know what you have today, you can work it out. But how do you know what you have today? Do you do a stocktake every day?

So if you have 10 cogs and 20 widgets today and over the last two weeks you had a total of 6 cogs in and 8 cogs out, you know that you effectively had -2 cogs over that time, so you must've had 10 - (-2) = 12 cogs two weeks ago. It's actually more realistic to calculate a stock balance based off a historical stock take and add forwards. You might have a stocktake every 3 months. From your stocktake in Jan you had 12 cogs, and from Jan to Apr you had 7 cogs in and sold 2 cogs so you know in April you had 12+7 - 2 = 17 cogs.

Notice a theme here? You need to understand your business. You need to analyse it. Back to your actual schema: it's not really stock focused, it's sales focused. You need a dedicated stock transaction table if you want to track stock levels. Lastly, there are many sample stock schemas online. Like this one: https://stackoverflow.com/questions/4380091/best-structure-for-inventory-database


Disallowing table creation in the public schema Postgres version: 9.3.16

Let's say we have two users, luser and editor. I'd like to make it so that luser (or any other non-superusers) cannot create any tables under the public schema, except editor. When I apply the following as the postgres user, I achieve this:

postgres=> select current_user;
 current_user
--------------
 postgres
(1 row)
postgres=# REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE
postgres=# GRANT ALL ON SCHEMA public TO editor WITH GRANT OPTION;
GRANT
postgres=# SET ROLE luser;
SET
postgres=> create table public.test (uid integer);
ERROR:  permission denied for schema public
postgres=> SET ROLE editor;
SET
postgres=> create table public.test (uid integer);
CREATE TABLE
postgres=> \d+
                   List of relations
 Schema | Name | Type  | Owner  |  Size   | Description
--------+------+-------+--------+---------+-------------
 public | test | table | editor | 0 bytes |
(1 row)

However, when if I try to run REVOKE ALL ON SCHEMA public FROM PUBLIC; as the editor user, this does not work (i.e. luser can create a table under the public schema):

postgres=> SET ROLE postgres;
SET
postgres=# GRANT ALL ON SCHEMA public TO editor WITH GRANT OPTION;
GRANT
postgres=# SET ROLE editor;
SET
postgres=> REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE
postgres=# SET ROLE luser;
SET
postgres=> create table public.test (uid integer); # I expect this to fail with `ERROR:  permission denied for schema public` but it works somehow!
CREATE TABLE
postgres=> \d+
                   List of relations
 Schema | Name | Type  | Owner |  Size   | Description
--------+------+-------+-------+---------+-------------
 public | test | table | luser | 0 bytes |
(1 row)

So, even though I can run REVOKE ALL ON SCHEMA public FROM PUBLIC; as the editor and it doesn't give any errors, luser can still create a table under the public schema. How can this happen?

Prior research:

From how do you revoke create table from a user on postgresql 9.4? :

  Also note the user/role executing the revoke also needs to be the
  owner of the schema (so you can check that as well).

Maybe this is the reason? But I'd still expect Postgres to give some kind of error when running REVOKE ALL ON SCHEMA public FROM PUBLIC; as the editor (it does, when I try to run this as the luser user)...

This will probably do what you want:

revoke create on schema public¹ from public²;

Note that the public² does not mean the schema public¹, but a group representation of all users of the database. So when you try to revoke your user from creating tables nothing happens because it probably does not have this permissions at start(except if you give it), but the group public² that your user belongs, is with this access by default - That's the default behavior for schema public.

Now you can give the create privilege for specific users:

grant create on schema public to specificuser;


postgres_fdw slower then copy + scp + copy (~12 times) foreign server 9.2
local server 9.5
table is 10GB
data transfer performed on same network interface as foreign server works
no indexes set on data
old way:

copy to - 2:36
scp - 08:17
copy from - 10:11

postgres_fdw:

by the time old way finished it has done 800MB of insert into ..select * from foreign_table

Question: Did I miss something in config (meaning I can improve it), or postgres_fdw is just not meant for bulk load (meaning I can't improve it)? (I use it for small data amount reconcile and it works fine. Idea of insert select from fdw instead of running bash commands looked so sweet)

Update I tried psql to remote server from local server and \copy table - six minutes - faster then over ssh

Update2 The fetch_size option, not available prior to 9.6, can be mocked up with dblink_fetch(CURSOR, fetch_size) - look my answer below for sample.

postgres_fdw is certainly not as optimized for bulk transfer as copy to, copy from, and scp are.  After all, bulk transfer is the main reason for the existence of those tools.

But that doesn't mean there is nothing you can do.  If you were running 9.6 on the local server, you could try increasing the fetch_size.


Is it possible to get rid of so-called 'Spurious Tuples' completely? Is it possible to get rid of so-called 'Spurious Tuples' completely?

For example: In this example from a textbook, there is an original table:

I don't see anything wrong with the two projections of it:

But their join is still generating spurious tuples:

(These figures from ToddEverett's answer.]

This is a great question.  Normalization beyond BCNF is extremely hard to understand.  Hopefully I can provide an answer that makes sense.  I struggled with these concepts for over 20 years before finally making sense of them thanks to Fabian Pascal's Practical Database Foundation Series.

The example provided is an EmpRoleProj R-table that looks like so:

It then goes on to show projections of the original EmpRoleProj R-table like so:

The reason you don't see anything wrong with the base tables Table 1 and Table 2 is that you aren't considering the dependency rules (in this case multivalued dependency (MVD) rules) defined in the business model describing the business rules. If we assume for the sake of example there are no MVDs defined in the business rules then EmpRoleProj is in 5NF despite the "appearance" of redundancies.  It appears for example that the information that Smith is a designer is stored redundantly.  It also appears that the information that a designer is needed on the Amazon project is stored redundantly.  While this appears to be the case, by learning that in fact these are not MVDs, what is actually the case is that Smith happens to be a designer on a few projects, but it is not a fact that Smith is a designer and thus that fact should not be inferred.  When table 1 and table 2 are joined, the result:

shows Jones to be a designer on the Nile project but we know that is not the case.

Let us assume instead the business model did say there were MVDs of empName-->>role and role-->>projName.  In this case, what those MVDs mean is that if an employee plays a role, and if that role is on a project, by definition that employee plays that role on that project.  In this example, that same EmpRoleProj table is now not in 5NF and now does suffer from redundancy.  Now, the facts that Smith is a designer and there is a need for a designer on the Amazon project are stored redundantly as those facts could be inferred from joining Table 1 and Table 2!  Likewise, taking the join of Table 1 and Table 2 now does not result in a spurious tuple as the inference that Jones is a designer on the Nile project is a fact now based upon the business rules defined by the MVDs.

This is why you cannot assess the normal form of any R-table without knowing the dependencies and the defined key.  Making any assumption, even one that seems to you to make sense, can be dangerous.  If you are ever asked what normal form an R-Table is in, you must ask for the dependencies to assess.  In addition to Fabian's series of papers, Chris Date's works provide the best information available on normalization theory.


SQL Server 2008 R2 recognizes a domain account as a SQL Server account After migrating from SQL Server 2000 to SQL Server 2008 R2, we have a few domain accounts that appear to the new server as SQL Server accounts. These accounts can SELECT from tables in the database they are assigned to, but they are not able to write to them as they could under SQL Server 2000. The SQL Server 2008 R2 log shows that at least one account failed at its attempt to INSERT into a table with this message (excerpt):

  Attempting to use NT account name with SQL Server
  Authentication....

This specific account had no problems doing its thing on SQL Server 2000, and nothing has changed at the account level, but the database it is assigned to "thinks" this account is a SQL Server account.  Has anybody experienced this issue and resolved it?

The question is rather vague, or at least to me it is.

You state you had new user accounts 'appear' when you migrated, but then state that 'they can only SELECT and can't write to the database like they used to in 2000'.  Did these same user accounts exist in the 2000 instance or did they appear only after creating 2008?  Were they created by someone or did they literally just appear, like the service users typically do (such as DOMAINNAME\NTAUTHORITY).

Also when you migrated over, did you create new domain controllers?  If so, your user SIDs no longer match.  Did you rename your domain?  If so, your sql server users are still referenced by the old domain which means you want to script their permissions out by right clicking on them, script user as, create.  Then change the user name to reference the new one.  Make sure you then include this user in all the databases it needs access to.

If you did in fact create a new domain, make sure you update your internal SQL Server name to reference the new Fully Qualified Host Name:
http://blogs.msdn.com/b/cindygross/archive/2010/02/11/how-to-rename-sql-server.aspx

There's a lot of unknowns, please further clarify. Thanks!


How can I generate scripts for all functions in pgAdmin? I am using pgAdmin version 1.10.5, and I've checked the properties and menu options but couldn't find anything on the subject.

For example, I just want to generate a script that includes the functions that I've created for my schema. If I right click on the database node and choose backup it has an option to create a script for the schema, but it creates all the objects. And I want only the functions.

There is no explicit switch to just export functions. I did some research since it's a nice question.

I can think of some ways to do this:

Using pgAdmin, you get the code that makes up the function when you right-click on the function, and choose "Scripts->Ceate". You could copy&paste the contens and put it into a sql file that you can import like any other file
Exporting the schema and importing only the functions you need (tedious, since you need the exact name and arguments of the function. This blog post explains it:

pg_dump -Fc -s | pg_restore -P 'funcname(args)'
Do some querying in the schema/pg_proc and export the results. This could be another function that just exports the functions...


Why would I ever prefer ALGORITHM=COPY to ALGORITHM=INPLACE? Since MySQL 5.6 introduced online DDL, the ALTER TABLE command can optionally have either ALGORITHM=INPLACE or ALGORITHM=COPY specified. The overview of online DDL notes that, by default, INPLACE is used wherever possible, and implies (without ever quite stating it) that the INPLACE algorithm is cheaper than the COPY one is.

So what reason would I ever have to specify ALGORITHM=COPY on an ALTER TABLE statement?

Yes, there are cases when you may specify COPY, but it would be for other reasons than performance.

It is important to understand that MySQL introduced new feature - Online DLL processing in version 5.6. It did not remove offline processing. So there is a need to differentiate between these 2 modes:

Some operations still work in Offline mode only. See Table 15.10, “Summary of Online Status for DDL Operations” for a list of the DDL operations that can or cannot be performed in-place.
Operations in Online and Offline modes have slightly different behavior, so you can choose "old" one for compatibility reasons.

Some examples (please suggest more):

InnoDB tables created before MySQL 5.6 do not support ALTER TABLE ... ALGORITHM=INPLACE for tables that include temporal columns (DATE, DATETIME or TIMESTAMP) and have not been rebuilt using ALTER TABLE ... ALGORITHM=COPY. In this case, an ALTER TABLE ... ALGORITHM=INPLACE operation returns error.
ADD PRIMARY KEY clause in COPY mode silently converts NULL to default values for that data type (0 for INT,  empty string for varchar), whereas IN_PLACE does not do that.

  With the ALGORITHM=COPY clause, the operation succeeds despite the
  presence of NULL values in the primary key columns; the data is
  silently changed, which could cause problems.

Another reason to prefer COPY:

  Operations for which you specify ALGORITHM=COPY or old_alter_table=1,
  to force the table-copying behavior if needed for precise
  backward-compatibility in specialized scenarios.

Although MySQL manual doesn't talk about actual scenarios, you can imagine some. E.g. developer relied on table being locked during ALTER INDEX operation so table is read-only or fully locked and there is a process that reads static table during index rebuild.


I have two tables and need to join them First table:

id | username | firstname |  surname  | address
---+----------+-----------+-----------+---------
1  | johnie   | john      | something | blabla
2  | alice    | something | something | blabla
3  | bar      | cola      | something | blabla
4  | whiskey  | whiskey   | something | blabla

Second table:

aincrement | id | user_id
-----------+----+---------
1          | 1  | 3
2          | 1  | 4
3          | 2  | 3

If I do

SELECT username, firstname, surname
FROM first_table
WHERE username != "johnie"

I get all the rows except johnie...

If I do:

SELECT DISTINCT T2.user_id
FROM second_table AS T1
JOIN second_table AS T2 ON T1.id = T2.id
WHERE T1.id = 1 AND T2.user_id <> 1

I get 3,4

But i would like to get 3 and 4 with all the others details (username, firstname, surname....)

My expected output

id | username | firstname |  surname  | address
---+----------+-----------+-----------+---------
3  | bar      | cola      | something | blabla
4  | whiskey  | whiskey   | something | blabla

I have to use where username="johnie" (first_table). That user has id 1. Then I need to obtain user_id(s) from second_table (3, 4) and in output I need only basic info for users with id 3 and 4.

You could use GROUP BY, if you only need the basic information from first_table:

SELECT
    T1.id,
    T1.username,
    T1.firstname,
    T1.surname,
    T1.address
FROM first_table AS T1
JOIN second_table AS T2 ON (T2.user_id=T1.id)
JOIN first_table AS T3 ON (T3.id=T2.id AND T3.id<>T2.user_id)
WHERE T3.username='johnie'
GROUP BY T1.id;

Try it in SQLFiddle.

You can also test it with T3.username='alice'.


I am getting error ORA-03113 at the time of startup database. I am getting error ORA-03113 at the time of startup database. I removed some old archive log files as per solution I got on google. But still I am unable to startup db. But, I am able to connect other database (dbname: CTS) on same server.
Please suggest solution on this

Please find below current OS and DB configuration details and received errors.

**DB version is : 11g
Linux version : Linux ctsvm.oraclexpert.com 2.6.18-194.el5PAE #1 SMP Mon Mar 29 20:19:03 EDT 2010 i686 i686 i386 GNU/Linux**

Error I received is as below:-
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@ctsvm dbs]$ cd
[oracle@ctsvm ~]$ export ORACLE_SID=DBAYOG
[oracle@ctsvm ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.2.0 Production on Sun Oct 27 00:52:39 2013
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup
ORACLE instance started.
Total System Global Area  422670336 bytes
Fixed Size                  1344616 bytes
Variable Size             339741592 bytes
Database Buffers           75497472 bytes
Redo Buffers                6086656 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 4293
Session ID: 1 Serial number: 5

In your case, it seems that database can not pass from mounted to open state, for any reason.
Please check alert log and any generated file in your trace directories.
EDIT: I removed comment about ORA-00600 (I wrongly read your post 1st time...)


How to execute a trigger procedure with an expression as an argument? How can I pass an expression as an argument when calling a trigger function?
create trigger my_trigger
after insert on my_table
for each row
execute function trigger_job(
    'job_name',
    (json_build_object(
       'foo', current_setting('foo', true),
       'bar', current_setting('bar', true)
    ))
);

I know how to call a "normal" function with an expression as an argument, but I can't get it to work calling a function from the trigger. I also know how to read the passed arguments from TG_ARGV[] in the trigger function, but that's beyond this question.
I also tried something simple like:
create trigger my_trigger
after insert on my_table
for each row
execute function trigger_job(
    'job_name',
    (true)
);

But I still get the error:

ERROR:  syntax error at or near "("
LINE 6:                 (true)
                        ^

Update: I am now using an intermediate function. The trigger calls the intermediate function with the single text parameter. That intermediate function builds the second argument and calls the final trigger_job function with both arguments. Simple.

It cannot be done.  From the docs:

The arguments are literal string constants


Set Default Maximum Rows for Export I am more of a Vertica database user than admin, so I'm not even sure if what I'm asking is absurd, but here goes nothing...

Could I, as an admin, put default restrictions on the number of rows that can be exported per export query? I would like to make sure most users can only export a certain number of rows at a time, and that they cannot change the setting which prevents a massive export. I realize there are probably be ways around this restriction
Would there also be a way to catch a user pulling small amounts of data incrementally from the same data set?

An answer to question (1) would suffice, but I would appreciate any input on question (2), as well.

You can create views using LIMIT and give those view names to the end users. If you're using a development tool such as DBVisualizer, you can set the number of records to limit.

As always, query labels allow for easy tracking of any requests.


ORA-13249: SDO_NN cannot be evaluated without using index I have two tables called my_nodes and restaurants that contain a name and geoloc data. I need to get a table that for each my_node returns nearest restaurant and distance from node to restaurant.

    select n.node_id, r.r_name , sdo_nn_distance(1) as min_distance from
        (select nodeid as node_id, geoloc  from my_nodes@my_server ) n,
        (select r_name, GEOLOC from restaurants) r
        where sdo_nn   (n.geoloc, r.GEOLOC,   'Unit = MILE sdo_num_res = 1', 1) = 'TRUE'

The query above returns:

    ORA-13249: SDO_NN cannot be evaluated without using index
    ORA-06512: at "MDSYS.MD", line 1723
    ORA-06512: at "MDSYS.MDERR", line 17
    ORA-06512: at "MDSYS.PRVT_IDX", line 9

How to fix it?

Got same error message, but without DB-Link. http://www.orafaq.com/forum/t/154844/ helped (replace c and s with your table aliases and spatial_index with your spatial index name):

/*+ LEADING(c) USE_NL(c s) INDEX(s spatial_index)*/


MySQL: Use index in background query I'm using MySQL 5.7.10
I'm checking a new query for an audit report.
I'll execute it in a simple background Unix process, whick invoke mysql from the console.
To check the query, I use a worksheet in HeidiSQL.

The table is:

CREATE TABLE `services` (
`assigned_id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`service_id` VARCHAR(10) NOT NULL,
`name` VARCHAR(50) NOT NULL,
...
`audit_insert` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
...
INDEX `idx_audit_insert` (`audit_insert`),
...

);

The simple worksheet is:

SET @numberOfMonths:=6;
SET @today:=CURRENT_TIMESTAMP();
SET @todaySubstractnumberOfMonths=TIMESTAMP( date_sub(@today, interval @numberOfMonths MONTH) );

EXPLAIN SELECT service_id from services where audit_insert between @todaySubstractnumberOfMonths and @today;

The explain output for that query is:
id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra
1,SIMPLE,services,[all partitions],ALL,idx_audit_insert,,,,47319735,21.05,Using where

So, index 'idx_audit_insert' is not used.

If I change the query to:

EXPLAIN SELECT service_id where audit_insert between '2020-01-01 00:00:00' and '2020-03-10 23:59:59';

The output is:
id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra
1,SIMPLE,tdom_transitos,[all partitions],range,idx_audit_insert,idx_audit_insert,4,,4257192,100.00,Using index condition

Now, the index is used and the rows value is dramatically reduced.

So, my questions are:

How can I force the variables to be timestamp? Is there any wrong in my worksheet?

or maybe

How can I use the index (trying to avoid hints like USE INDEX, FORCE INDEX...)?

(Excuse me if this question is in stackoverflow too. But this related web is more properly for DB questions)
Thanks a lot.

Well, maybe it's not the answer I thought I'd find, but it works perfectly.

I have splitted audit_insert field in another one, audit_insert_datetype, of DATE type. This field has a new index too.

I have changed the query to execute with this field, and I have tried to force the @... variables to be date type (with current_date and date).

The results: the new index is used and the execution time is dramatically reduced.
Maybe it's bad style, but it works as I need.


Alias summing in mysql My query looks like this

select employees.PK_worker,
       concat(employees.name,' ',employees.surname)                    AS name,

       (select coalesce(sum(employees_to_hand.cost),0)
        from   employees_to_hand
        where  employees_to_hand.PK_worker = employees.PK_worker
        and    employees_to_hand.reg_date between @dstart and @dend)   AS to_hand_cost,

       (select coalesce(sum(employees_delegations.cost),0)
        from   employees_delegations
        where  employees_delegations.PK_worker = employees.PK_worker
        and    employees_delegations.dstart between @dstart and @dend) AS delegation_cost
FROM employees

join (select  @dstart := str_to_date('10/01/2017','%m/%d/%Y'),
              @dend   := str_to_date('11/30/2017','%m/%d/%Y')) d

order by 1;

I am wondering if it is possible to do some basic summing for instance (to_hand_cost + delegation_cost) as simple_sum I assume it might be not possible but repeating the selects again to get the values seems overwhelming. Got any suggestions?

Mysql playground mcve : https://www.db-fiddle.com/f/5FtV24czfAfUiE1dj5EeYt/13

The two inner tables should have INDEX(PK_worker, reg_date, cost) and INDEX(PK_worker, dstart, cost).

Plan A:

The following may help, and it may depend on what version of MySQL you are running:

JOIN ( SELECT str_to_date('10/01/2017','%m/%d/%Y') AS start,
              str_to_date('11/30/2017','%m/%d/%Y') AS end ) AS d

and then use start and end instead of @start and @end.

Rationale:  @variables are sometimes not well optimized.

Plan B:  (But this requires 5.6, perhaps 5.7 - see the EXPLAIN for "auto-key".)

SELECT e.PK_worker,
   concat(e.name,' ',e.surname)   AS name,
    FROM employees AS e
    JOIN ( SELECT PK_worker,
                  coalesce(sum(cost),0) AS to_hand_cost
              FROM employees_to_hand
              WHERE ...
                AND reg_date BETWEEN start AND end
              GROUP BY 1 ) AS ethc
      ON e.PK_worker = ethc.PK_worker
    JOIN ( ... )  AS edc
      ON ...
    ORDER BY 1;

INDEX(reg_date, PK_worker, cost)

Rationale:  Calculating all the costs in one pass over the secondary tables is probably faster than one employee at a time.  Hopefully it is enough faster than the cost of automatically creating INDEX(PK_worker) after generating the 'derived' table.  The different index is a guess as to what would work better for this.

Thanks for dbfiddle, but in this situation, some of the optimizations depend on the size of the table, distribution of the values (esp reg_date, and the version of MySQL).


How to store historical records? What's the best practice approach to storing historical records?

I have an entity whose rows will constantly get updated. I need to keep track of each row's history. There will be about 40K "current" records at any given time and each of them will be updated on a daily basis for about 60 days, then the updates will stop.

I see two options here:

All current and historical records in one table - since each row will have an id that will allow me to identify which records are part of the same group.
Current and historical records in separate tables - each time the current record is updated, a copy of it pre-update is put into the history table. The history table would have a FK.

Is there another option I haven't considered? Which option is the more appropriate approach to what I am trying to do?

There are some Open Source Change Data Capture tools that support MySQL you can find some of them in this answer: Does MySQL have a version of Change Data Capture?

I would personally recommend option #2 if you choose to deal with it yourself. A few simple triggers can automatically copy the new/modified rows into the other table and you can add a few columns in the history table to help track what is going on without having to touch any of the queries that use the primary table. This solution is extensible and is easy to deal with from a querying point of view.


MySQL Master-Slave-Slave Configuration -error 1236 I currently have a Master (5.5) - Slave (5.6) set up right now and I would like to add another slave that will replicate from the existing slave.

On the existing slave:

1. STOP SLAVE;
2. SHOW SLAVE STATUS;
3. Note these values Master_Log_File: mysql-bin.018098 & Read_Master_Log_Pos: 185753950
4. FLUSH TABLES WITH READ LOCK;
5. Service mysql stop
4. Took snapshot of mysql data vol and restore it on new mysql slave server, also I changed the server-id in my.cnf on the new mysql slave.
5. service mysql start.
6. UNLOCK TABLES;

on the new mysql slave server (should replicate from the existing slave):

1. changed server-id on my.cnf
2. remove /mnt/data/mysql/auto.cnf
3. service mysql start --skip-slave-start

4.
CHANGE MASTER TO MASTER_HOST='existing slave',
MASTER_USER='xxx',
MASTER_PASSWORD='xxx',
MASTER_LOG_FILE='mysql-bin.018098',
MASTER_LOG_POS=185753950;

5. execute start slave

got an error:
2016-11-18 16:39:50 5736 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file (server_errno=1236)
2016-11-18 16:39:50 5736 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file', Error_code: 1236
2016-11-18 16:39:50 5736 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.018098', position 185753950

Thanks !!

If you want to replicate from the existing 5.6 slave to another 5.6 slave, you need to setup the first 5.6 slave to behave like a master

STEP 01

Add these three(3) lines to my.cnf (under [mysqld] group header) on the first 5.6 slave

[mysqld]
log-bin=mysql-bin
log-slave-updates
skip-slave-start

STEP 02

Restart mysql on the first 5.6 slave

service mysql restart

Now, the first 5.6 slave has its own binary logs

STEP 03

Reset Binary Logs on the first 5.6 slave

mysql> RESET MASTER;

STEP 04

On the second 5.6 Slave, setup replication to the first 5.6 slave as follows:

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='existing slave',
MASTER_USER='xxx',
MASTER_PASSWORD='xxx',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=4;
START SLAVE;

STEP 5

Start replication on the first 5.6 slave

mysql> START SLAVE;

STEP 6

Verify that Replication is running on both 5.6 Slaves

mysql> SHOW SLAVE STATUS\G

If you see Slave_IO_Running: Yes, Slave_SQL_Running: Yes, and Seconds_Behind_Master is a number (preferably 0), CONGRATULATIONS !!!

GIVE IT A TRY !!!


Best use of RAM for multiple SQL instances? I have a new Windows Server 2016 box with 32GB of RAM, it will be used for dev testing by one or two users max. There will be occasional use of VM/Hyper-V instances and a couple of IIS test sites running from time to time.

I also need to install all versions of SQL Server from 2008 through to 2016. These will each have many databases attached but very few will actually be active at a time unless testing calls for it.

Assuming there is no practical issue with installing several different SQL Server installations on the same box, what options do I have to configure the best use of RAM? I know that on a standard installation, SQL will try to hog all available RAM but I want to try to avoid this leaving around 12GB for other services and allocate a maximum of 24GB for all the SQL instances to share equally without fighting with each other over resources.

Is there a good way to achieve this?

PS I'm not an experienced DBA just a humble developer

You can allocate maximum amounts of memory that each SQL Server instance can use through the sp_configure stored procedure.  However, that won't be an absolute maximum, but a maximum amount of memory allocated to the storage of certain types of data, mainly data cached from the database.

If you're planning on running one instance of each version of SQL Server since 2008, you're looking at five instances of SQL Server on a 32MB server; you'll need to leave a few GB RAM available for the operating system and anything else on that server (SSIS?  SSRS?  SSAS?).  At a conservative estimate, you'll probably only have about 4GB RAM available for each instance, so you would run something like:

exec sp_configure 'show advanced options', 1;  -- ensure memory configuration option is available
GO
RECONFIGURE;
GO
exec sp_configure 'max server memory', 4096; -- maximum memory in MB
GO
RECONFIGURE;
GO

Note that difference versions of SQL Server interpret the configuration setting in different ways - more recent versions apply the setting to more types of memory allocation.  There's a table about half way down this sizing consideration document that shows what's changed.


Problem granting user privileges via roles in Oracle 12c I'm starting to learn Oracle 12c so please excuse my bad terminology. I wrote this basic script to set up a user and privileges:

CREATE USER wertyq_db_usr IDENTIFIED BY justatest ACCOUNT UNLOCK DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" PROFILE "DEFAULT";
ALTER USER wertyq_db_usr QUOTA 100M ON USERS;
GRANT RESOURCE, CONNECT TO wertyq_db_usr;
GRANT EXECUTE ON CTX_DDL TO wertyq_db_usr;
GRANT EXECUTE ON "CTXSYS"."CTX_DDL" TO wertyq_db_usr;
GRANT UNLIMITED TABLESPACE TO wertyq_db_usr;
CREATE ROLE wertyq_usr_role;
GRANT CREATE PROCEDURE TO wertyq_usr_role;
GRANT CREATE TRIGGER TO wertyq_usr_role;
GRANT CREATE SESSION TO wertyq_usr_role;
GRANT CREATE VIEW TO wertyq_usr_role;
GRANT wertyq_usr_role TO wertyq_db_usr;

The goal is to create a user and a role, then grant privileges to the role, then assign the role to the user so the user inherits the privileges. I should also mention that I can connect to the database as SYSTEM user.

However, a third party application I am also using, which should connect to my Oracle database using this user created above, it behaves like the user doesn't have any privileges and it won't create any tables/views/ etc in the database. So I grant the privileges directly to the user and the third party application functions correctly. So I remove the privileges from the user and grant the role to the user again, and the third party application again won't create tables/views/ etc.

So I am wondering if the third party application has a bug, or if an Oracle user doesn't inherit privileges from roles in the manner I thought it would?  I would appreciate your insights :)

Thank you!

You did not mention it, but this is quite typical when using PL/SQL.

Privileges granted through roles are disabled for named PL/SQL blocks that are defined to execute with definer's rights.

How Roles Work in PL/SQL Blocks

  All roles are disabled in any named PL/SQL block (stored procedure,
  function, or trigger) that executes with definer's rights. Roles are
  not used for privilege checking and you cannot set roles within a
  definer's rights procedure.

More details you can find in the documentation referenced above.

Possible workarounds involve granting the privileges directly (as you already did) or define the PL/SQL program unit to execute with invoker's rights.

Another possibility starting with 12c, you can grant roles to PL/SQL program units. So if you create tables with the procedure called p1 using dynamic SQL, you can grant the above role to it as:

grant wertyq_usr_role to procedure p1;

Choose the one that suits your needs.


Table design for conversation I want to create simple conversation module on my site. So I need a list of users who had conversation with current user. I need to show amount of new messages in each conversation.
I think about next tables

User
id
...

Conversation
id
user_id1
user_id2
cnt_new_msg
last_msg_id

Message
id
conversation_id
sender_id
type
is_read
created_at

For selecting data for list of conversation I can use next query:

SELECT *
FROM conversation c
LEFT JOIN message m ON m.id = c.last_msg_id l.last_ms
WHERE user_id1=[current_user_id] OR user_id2=[current_user_id]

When user send new message I will increment Conversation.cnt_new_msg and when user open conversation I will set Message.is_read to 1 and recalculate Conversation.cnt_new_msg again. Also to sync this value I can create cronjob.

But this approach has problems. I don't like user_id1=[current_user_id] OR user_id2=[current_user_id] for each query. My tables will be very denormalized but I think it will be faster.

Please advise best practices to design conversations tables.

Don't store functionally dependent data. If you should need that for performance, you can always add a materialize view later (see below). But it's often better (and sometimes even faster) to calculate derived values in queries on the fly.

In particular, you would create many dead rows, table bloat, more work for VACUUM and nasty concurrency issues in conversation if you UPDATE that row with every new message and with every status change.

I suggest a relational model like this:

CREATE TABLE users (user_id serial PRIMARY KEY, ...)

CREATE TABLE conversation (
  conversation_ id serial PRIMARY KEY
, user_id1         int NOT NULL REFERENCES users
, user_id2         int NOT NULL REFERENCES users
-- cnt_new_msg   -- redundant
-- last_msg_id
, created_at       timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE message (
message_id      serial PRIMARY KEY
conversation_id int  NOT NULL REFERENCES conversation
sender          "char" NOT NULL CHECK (sender IN ('1', '2')
message         text NOT NULL
type            ???
unread          boolean NOT NULL DEFAULT TRUE
created_at      timestamptz NOT NULL DEFAULT now()
);

Details for data types in the manual.
You can create a VIEW or MATERIALIZED VIEW to present conversation data like you had in mind:

CREATE VIEW conversation_plus_ct AS
SELECT c.*
     , count(m.unread OR NULL) AS cnt_new_msg
     , max(m.created_at) AS last_msg_id
FROM   conversation c
JOIN   message m USING (conversation_id);

And your query could look like this (not using the VIEW, but it could ..)

SELECT *
     , count(m.unread OR NULL) AS cnt_new_msg
     , max(m.created_at) AS last_msg_id
FROM   conversation c
LEFT   JOIN message m USING (conversation_id)
WHERE  [current_user_id] IN (user_id1, user_id2);


How to connect to SQL Server using sqlcmd on Linux? I have set up the Microsoft ODBC Driver 13 for SQL Server on an Ubuntu 16.04 machine. I am now trying to test a database connection, that includes both the server name and an instance name.

The following both work fine from a Windows machine:

sqlcmd -S "SERVERNAME\INSTANCENAME" -U User -P pwd -d DatabaseName -Q "SELECT TOP 5 Id FROM dbo.MyTable;"
sqlcmd -S SERVERNAME\INSTANCENAME -U User -P pwd -d DatabaseName -Q "SELECT TOP 5 Id FROM dbo.MyTable;"

However none of them work on Linux - they all return the error message below. The error implies the server cannot be found:

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : MAX_PROVS: Error Locating Server/Instance Specified [xFFFFFFFF]. .
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

I have also tried escaping the backslash with the same error (and with single and double quotes).

sqlcmd -S "SERVERNAME\\INSTANCENAME" -U User -P pwd -d DatabaseName -Q "SELECT TOP 5 Id FROM dbo.MyTable;"
sqlcmd -S SERVERNAME\\INSTANCENAME -U User -P pwd -d DatabaseName -Q "SELECT TOP 5 Id FROM dbo.MyTable;"

The driver is working however as I can connect to a different server with no instance name:

sqlcmd -S SERVERNAME -U User -P pwd -d DatabaseName -Q "SELECT TOP 5 Id FROM dbo.MyTable;"

I am fairly certain it is due to the backslash, but cannot find a way to get around this.

With the help of Dan Guzman's comment I got the connection working using a port.

I had a Windows machine with SQL Management Studio connected to the database in question, and used:

netstat -abn

Then I searched for ssms.exe for the connection details:

TCP    192.168.0.31:50777     192.168.0.78:49399     ESTABLISHED [Ssms.exe]

Alternatively log into the database server and look at the ports used by the instance in SQL Server Configuration Manager (SQL Server Network Configuration > Protocols for INSTANCENAME > TCP/IP > Properties > IPAll TCP Dynamic Ports).

The following then both worked:

sqlcmd -S 192.168.0.78,49399 -U User -P pwd -d DatabaseName -Q "SELECT TOP 5 Id FROM dbo.MyTable;"
sqlcmd -S SERVERNAME,49399 -U User -P pwd -d DatabaseName -Q "SELECT TOP 5 Id FROM dbo.MyTable;"


Questions about PostgreSQL backup/restores I've inherited a large PostgreSQL 8.4.7 database that is currently not being backup at all.  The database itself I believe is around 100GB, I've been reading about the different methods to backup this type of DB here

My questions are:

1) Typically how fast or slow is the pg_dump dump process?  I know its a subjective question and depends on hardware and the database, but roughly 100gb can I expect this to take 1 hour, 12 hours, 24+?  I'd like to set an expectation with management and users before I begin.

2) The restore instructions indicate the following:

Before restoring a SQL dump, all the users who own objects or were granted permissions on objects in the dumped database must already exist. If they do not, then the restore will fail to recreate the objects with the original ownership and/or permissions.

Is it possible to gather this information as part of the backup?  Or is there a separate backup process for users and permissions?  Because I'm inheriting this database, Im not sure about all the users and permissions yet and would like to just backup everything for offsite storage.

2) pg_dumpall is what you're looking for if you want all objects (globals, roles, etc.).

1) You noted that it is subjective. On my dev laptop (Win7 x64 .. i7 4 @ 2.2 ... 12 GB RAM ... normal, spinny disk @ 5400) I backup a 4GB pg cluster in about 15 seconds. I don't know if it scales linearly.

Finally, depending on your OS, I'd verify that it's not actually being backed up. I've seen pg on linux being backed up at the file system level using rsync. Without getting into a debate about the relative merits of these differing methods, I'd verify that this isn't already implemented.


dbcc failing with Cannot continue the execution because... alwayson having a change of state event for each db I got a job DatabaseIntegrityCheck - USER_DATABASES, failing with the following error:

  Date and time: 2018-10-29 02:34:13 Command: DBCC CHECKDB ([one of my
  databases]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY HResult
  0x254, Level 21, State 1 Cannot continue the execution because the
  session is in the kill state.

I get an alert email with the following error message:

  The client was unable to reuse a session with SPID 193, which had been
  reset for connection pooling. The failure ID is 46. This error may
  have been caused by an earlier operation failing. Check the error logs
  for failed operations immediately before this error message.

On the primary server I get this message:

  The availability group database "all databases" is changing roles from
  "PRIMARY" to "RESOLVING" because the mirroring session or availability
  group failed over due to role synchronization. This is an
  informational message only. No user action is required.

and on the secondary:

  The availability group database "each database" is changing roles from
  "SECONDARY" to "SECONDARY" because the mirroring session or
  availability group failed over due to role synchronization. This is an
  informational message only. No user action is required.

As I could not find out the cause of this error, I have escalated it to the sys admin friends, also because I suspect it is something to do with network.

question:
as nothing about this on the error log, where else can I find any information about this matter?

I have run sp_blitz with markdown

EXEC sp_Blitz @OutputType = ‘markdown’, @CheckServerInfo = 1

and the results are here

I have also run the following command on one of my small databases test1 which is part of the availability group:

DBCC CHECKDB ([test1]) WITH  ALL_ERRORMSGS, DATA_PURITY

and that gave me the results shown here and important to say, on this occasion it did not cause any failover or change of state in the availability group.

Update:

Yesterday I changed the schedule of the job DatabaseIntegrityCheck - USER_DATABASES from 2 am to 3 am and it upset the availability group at 3:43 am
It seems to be caused by the dbcc checkdb

I have experienced this behavior before, where the AGs "blip:"

from PRIMARY to RESOLVING and back to PRIMARY
from SECONDARY to SECONDARY

It's my understanding that, on a system with a small number of logical processors (1-4), and with multiple AGs (or AGs with many databases), some of these "blips" are unavoidable.  Especially if the server is a VM with even slightly noisy neighbors.

Availability Groups use a lot of worker threads baseline.  You can read about the thread requirements for AGs in detail here: Thread Usage by Availability Groups

I'd suggest that you avoid having other CPU-heavy scheduled tasks run at the same time, in order to reduce the risk of these events occurring.  In this case, it looks like index maintenance was running at the same time as CHECKDB.  If you have a big enough window, try to spread these out so they don't overlap.


Designing a database for Questions, Options and Answers A practice in trying to adhere to relational database techniques.
Can anyone give me a little bump as to what I'm doing wrong/how this should be?

In Short All Questions have 5 Possible Answers. That's the whole database act.

My idea/what I've thought to do so far was create three tables:

questions

CREATE TABLE `questions` (
 `question_id` int(11) NOT NULL AUTO_INCREMENT,
 `question` mediumtext NOT NULL,
 PRIMARY KEY (`question_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

options

CREATE TABLE `options` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 INDEX choice_index (id),
    FOREIGN KEY (`id`)
        REFERENCES questions(question_id),
 `answer_1` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `answer_2` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `answer_3` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `answer_4` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 `answer_5` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`choices_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Here's where I'm coming to a dead-end.

answers

CREATE TABLE `answers` (
 `question_id` int(11) NOT NULL AUTO_INCREMENT,
 ??`answer_id` int(11) NOT NULL
 //Foreign Key?
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

A foreign key has to be an integer value1 -- but my answer choices are all varchar(255).

What is the proper way to link these three tables up?

1 This is an assumption, since most if not all relational schemas I've seen are "binded" by integer values, so that is where this assumption is coming from.

The relationship between questions and options is one-to-one, so if you wanted to simplify your table structure, then these two tables could be merged into one.

Are you 100% certain that every question will have only 5 (or less) options? For flexibility reasons, it might make sense to have only one answer option per record. In that case, the options table must remain separate from the questions table, and it becomes a one-to-many relationship.

Which is the right answer option? You should probably indicate that somehow.

In the answers table, the answer_idis obviously the primary key. The question_id is a foreign key. And you seem to have forgotten a foreign key to options, as well as a foreign key to a users table to indicate who gave this answer.

A minor issue: In the questions table, the question column is a MEDIUMTEXT. This is up to 16 million bytes. Do you really need that many? A TEXT column can store up to 64K bytes, maybe that would be enough?

Also, you would want to use int unsigned for your auto_increment columns. The reason for this is that auto incrementing ints start at 1, so by allowing signed ints you're effectively wasting a bit and limiting the range of values you can use.

There may be other opportunities for improvements, these are just a few I noticed.


Referential Integrity in schema, cascading the renaming In phpMyAdmin, while renaming a column/table which is referenced by some foreign key, I get an error. I can not rename a column until I remove all its relations.

There should be facilitated integrity of schema, just like referential integrity i.e. cascading on delete and update column/table name.

User should be allowed to rename a column/table only if user has also permissions to the related tables/columns. Then, cascade this change throughout schema i.e. (foreign keys,triggers etc.)

I have tried to post above request on mysql here Link but get error This form is invalid or has expired, please try again shortly

Please guide me Is this a good request . No? then why? Yes? then where should I and how can i submit it?

Referential integrity is one, and is an attribute of the table data, but it is not an attribute of the table's schema. A table's schema shouldn't be changed in a cascading style, because it can break existing working code. A table's column is not used only in foreign keys, triggers, etc. It's used in scripts, maintenance plans, jobs, applications, dlls, even web pages etc. If you change an object's name you can create a mess, that's why it shouldn't be done automatically. This is something you want to control.

This is where refactoring tools can enter the scene. Eg for MS SQL Server there's Visual Studio db projects that can help here, but not by much. There may be tools that aid in refactoring the db objects and fix anything automatically, but they are not part of any standard db package.


Do I need to RUNSTATS after a REORG in DB2? The REORG documentation says:

  BUILD

Builds indexes. Updates index statistics.

But I have always heard REORG/RUNSTATS as if they should be paired.  Can anyone verify that "Updates index statistics" achieves the same thing as RUNSTATS?

REORG is used to help DB2 point to accurate data (ie, indexes should become aware of fresh data and no longer include deleted data), as well as "collapse" empty page space created by deletion of data and/or indexes. It can also help move data that is related closer to each other for more efficient access (especially true in the case of a cluster index).

RUNSTATS is used to help gather updated statistics on the volume and/or distribution of data within tables and indexes. This information is stored in the system tables and is used by many things including the optimizer to determine the optimal path to query the data. This is especially true as data grows. The data may distribute in a way that causes the optimizer to now include an index that it didn't before in its access path (or perhaps vice versa and choose not to use an index). RUNSTATS can also affect how a REORG runs - at least in older versions of DB2.

Based on what I have read from DB2 DBA's you generally want to run

RUNSTATS
REORG
RUNSTATS

The first RUNSTATS helps the REORG to work appropriately and efficiently. The RUNSTATS after is more to make sure that stats are now accurate given all the data movement around on pages. Depending on where you read you may see DBA's mention the last RUNSTATS is no longer needed with newer versions of DB2. Since we haven't seen definitive answers on that and since most DB2 DBA's I've heard of follow the above order, our company has chosen to use that order (for the record we are on DB2 LUW 9.7 FP4 and we plan on migrating to V 10.1 sometime next year).

Also, just for the sake of completeness, generally when you update the statistics and you affect the optimizer you want all applications that call into DB2 to make sure they are taking advantage of those new optimizations. So a REBIND of packages will make sure that static packages know of the updated statistics and optimized query paths, and a FLUSH PACKAGE CACHE DYNAMIC cleans out dynamic queries from things like Hibernate, so that way the queries will be rebound with the updated query paths. (NOTE: on z/OS I see you have BIND and REBIND options. Perhaps the FLUSH PACKAGE CACHE DYNAMIC is under those or has a different name on z.)

So ultimately I would do the following:

RUNSTATS
REORG
RUNSTATS
REBIND packages
FLUSH PACKAGE CACHE DYNAMIC (on LUW) or whatever the equivalent is on z/OS.