changing TEMPORARY TABLE default collation

The CREATE TABLE syntax allows for specifying the character set and collation

The MySQL Documentation for CREATE TABLE shows this syntax:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

table_options:
    table_option [[,] table_option] ...

table_option:
    ENGINE [=] engine_name
  | AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name <<------ (YOU NEED THIS OPTION)
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name <<------ (YOU NEED THIS OPTION)
  | COMMENT [=] 'string'
  | CONNECTION [=] 'connect_string'
  | DATA DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | INDEX DIRECTORY [=] 'absolute path to directory'
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
  | STATS_AUTO_RECALC [=] {DEFAULT|0|1}
  | STATS_PERSISTENT [=] {DEFAULT|0|1}
  | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
  | UNION [=] (tbl_name[,tbl_name]...)

Therefore, your syntax should be something like this:

CREATE TEMPORARY TABLE tblname
(
) ENGINE=MyISAM CHARACTER SET '...' COLLATE '...';

Give it a Try !!!

If you want to manipulate the default options for collation, you have the following:

collation_connection
collation_database
collation_server

These variables can be changed at the Global or Session Level.

If you want to set the collation permamnently, you could put it in /etc/my.cnf

[mysqld]
collation_connection = 'collation_connection'

and restart mysql

If you only want this for an individual BB Connection, you would run this in the session

SET collation_connection = 'collation_connection ';

See my recent post Why are there different levels of MySQL collation/charsets? to see installed character sets and collations.


clone collection in mongoDB in the same db

Another way would be to use the copyTo() function.

NOTE: That will only work for MongoDB 4.0 and earlier.
Starting 4.2 it'll be deprecated. The dump/restore way is the proper way.


DatabaseIntegrityCheck Fails on Large Database Every Day

I would recommend check space on data file of your database and extended in case there is only 5% free of space.


Hint and gcgrace(you can set in table level) are two different parameters in Cassandra. irrespective of gcgrace seconds a hint will be stored in system.hints table for the >max_hint_window_in_ms< period mentioned in cassandra.yaml and will be removed after that. max_hint_window_in_ms is your SLA to bring the down node back online and run repair or the deleted data will comeback as a zombie.


how to get collection based on number of duplicate foreign keys?

SELECT b.* FROM
    batch b LEFT JOIN
    item i ON i.batch_id = b.batch_id
GROUP BY b.batch_id HAVING COUNT (i.batch_id) > 100

To get the count by batch_id
SELECT b.batch_id, COUNT(i.batch_id)
FROM
    batch b LEFT JOIN
    item i ON i.batch_id = b.batch_id
GROUP BY b.batch_id HAVING COUNT (i.batch_id) > 100


Try this:

Declare @T table (name varchar(10), Pattern varchar(10))
insert into @t (name, Pattern) values
('Peter', 'DRF'),
('Peter', 'DRF'),
('Peter', 'BBB'),
('Peter', 'DRF'),
('Peter', 'CCC'),
('Peter', 'CCC'),
('Sue', 'DRF'),
('Sue', 'CCC'),
('Sue', 'DRF'),
('Sue', 'VVV'),
('Sue', 'SSS')

SELECT NAME
    ,count(DISTINCT pattern) AS DistinctChanges
FROM @t
GROUP BY NAME

| name  | DistinctChanges |
|-------|----------------|
| Peter | 3              |
| Sue   | 4              |

Note: There is a difference between distinct values and the number of times a value changed assuming some kind of timeline.

For example:
AAA, BBB, AAA, BBB, AAA, BBB, AAA would be 2 distinct changes, but 5 total times the value changed assuming some sort of order.

Which variation are you looking for?


Perhaps you should look into using tombstone tables.

Here is what I mean: Suppose you had this table

CREATE TABLE mytable
(
    id INT NOT NULL AUTO_INCREMENT,
    ... other columns ....
    PRIMARY KEY (id),
    ... other indexes ...
);

Now, create a table with the IDs that are marked for deletion

CREATE TABLE mytable_deleted
(
    id INT NOT NULL,
    PRIMARY KEY (id)
);

So instead of doing

DELETE FROM mytable WHERE MOD(id,100) = 0;

You would add the ids to mytable_deleted

INSERT IGNORE INTO mytable_deleted
SELECT id FROM mytable WHERE MOD(id,100) = 0;

From here, you simply have to add the mytable_delete to all queries

To see all rows

SELECT * FROM mytable;

To see non-deleted rows

SELECT * FROM mytable A LEFT JOIN mytable_deleted B USING (id) WHERE B.id IS NULL;

To see deleted rows

SELECT * FROM mytable A LEFT JOIN mytable_deleted B USING (id) WHERE B.id IS NOT NULL;

or

SELECT * FROM mytable A INNER JOIN mytable_deleted B USING (id);

To perform the actual deletion

DELETE A.* FROM mytable A INNER JOIN mytable_deleted B USING (id);
TRUNCATE TABLE mytable_deleted;

CAVEAT

This will means that lots of SELECT queries must incorporate the tombstone table.

I have discussed this before : Tombstone Table vs Deleted Flag in database syncronization & soft-delete scenarios. You should read the accepted answer from Leigh Riffel instead of mine for a more honest critique as to which method is better in your case.


Should we use SQL Server partitions for a multi-tenant database design?

There are lots of ways for you to manage your data volume, but what you haven't really described here is the end result that you desire. Is it performance? Is it manageability? Is it security? Is it scalability? Is it your ability to add in new clients quickly and easily over time?

When you say 'manage the data volume', I am thinking you are looking to administer the data in a way that allows for decent performance. That makes me think of partitioning, but I don't know enough about the data to say that this is the right thing to do. Partitioning by client ID could be fine, but if you have thousands of clients, it may not be the right choice.

I think you should find the real pain points you are trying to solve and then look to architect a solution that mitigates those pain points.


How to merge data sets without including redundant rows?

The issue of records arriving late makes duplicate-removal more complex, but it is not impossible. Using a view (as proposed in your other question) to dynamically remove duplicates is workable, but queries against that view can produce complex and/or inefficient query plans.

An alternative design is to keep duplicated records in a separate table, in case they are needed to properly process a future late-arriving record. This does add a little complexity to the data import process, but each step is not too hard, and the result is a nice clean duplicate-free History table:

Tables

-- Original table
CREATE TABLE dbo.History
(
    Effective   datetime NOT NULL,      -- When the value became current
    Product     integer NOT NULL,       -- The product
    Kind        tinyint NOT NULL,       -- The price kind (RRP, special, etc)
    Price       smallmoney NOT NULL,    -- The new price

    CONSTRAINT [PK dbo.History Effective, Product, Kind]
        PRIMARY KEY CLUSTERED
            (Effective, Product, Kind)
);

-- Holding area for duplicates that may be needed in future
CREATE TABLE dbo.HistoryDuplicates
(
    Effective   datetime NOT NULL,
    Product     integer NOT NULL,
    Kind        tinyint NOT NULL,
    Price       smallmoney NOT NULL,

    CONSTRAINT [PK dbo.HistoryDuplicates Product, Kind, Effective]
        PRIMARY KEY CLUSTERED
            (Product, Kind, Effective)
);

Initial Data

CREATE TABLE #NewRows
(
    Effective   datetime NOT NULL,
    Product     integer NOT NULL,
    Kind        tinyint NOT NULL,
    Price       smallmoney NOT NULL,
    [Action]    char(1) NOT NULL DEFAULT 'X',
);

CREATE UNIQUE CLUSTERED INDEX cuq
ON #NewRows (Product, Kind, Effective);

INSERT #NewRows
    (Effective, Product, Kind, Price)
VALUES
    ('2013-04-23T00:23:00', 1234, 1, 1.00),
    ('2013-04-24T00:24:00', 1234, 1, 1.00),
    ('2013-04-25T00:25:00', 1234, 1, 1.50),
    ('2013-04-25T00:25:00', 1234, 2, 2.00),
    ('2013-04-26T00:26:00', 1234, 1, 2.00),
    ('2013-04-27T00:27:00', 1234, 1, 2.00),
    ('2013-04-28T00:28:00', 1234, 1, 1.00);

The first step is to remove any redundancies in the input data, storing the removed data in the new holding table:

-- Remove redundancies in the input set
-- and save in the duplicates table
DELETE NR
OUTPUT
    DELETED.Effective,
    DELETED.Product,
    DELETED.Kind,
    DELETED.Price
INTO dbo.HistoryDuplicates
    (Effective, Product, Kind, Price)
FROM #NewRows AS NR
OUTER APPLY
(
    SELECT TOP (1)
        NR2.Price
    FROM #NewRows AS NR2
    WHERE
        NR2.Product = NR.Product
        AND NR2.Kind = NR.Kind
        AND NR2.Effective < NR.Effective
    ORDER BY
        NR2.Effective DESC
) AS X
WHERE
    EXISTS (SELECT X.Price INTERSECT SELECT NR.Price);

Classifying input rows

The next step is to decide whether each row in the input table is redundant (w.r.t the History table) or not. The following query sets the Action column of the input set data appropriately:

-- Decide what to do with each row
UPDATE NewRows
SET [Action] =
    CASE
        WHEN NOT EXISTS (SELECT ExistingRow.Price INTERSECT SELECT NewRows.Price)
            THEN 'I' -- Insert
        WHEN ExistingRow.Price = NewRows.Price
            THEN 'D' -- Duplicate
        ELSE 'X'
    END
FROM #NewRows AS NewRows
OUTER APPLY
(
    SELECT TOP (1)
        H.Price,
        H.Effective
    FROM dbo.History AS H
    WHERE
        H.Product = NewRows.Product
        AND H.Kind = NewRows.Kind
        AND H.Effective <= NewRows.Effective
    ORDER BY
        H.Effective DESC
) AS ExistingRow;

CREATE UNIQUE CLUSTERED INDEX cuq
ON #NewRows
    ([Action], Product, Kind, Effective)
WITH (DROP_EXISTING = ON);

Store Redundant Rows

Now we store the rows identified as redundant to the holding table:

-- Store duplicates
WITH Duplicates AS
(
    SELECT NR.*
    FROM #NewRows AS NR
    WHERE NR.[Action] = 'D'
)
MERGE dbo.HistoryDuplicates AS HD
USING Duplicates AS NR
    ON NR.Product = HD.Product
    AND NR.Kind = HD.Kind
    AND NR.Effective = HD.Effective
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Product, Kind, Effective, Price)
    VALUES (Product, Kind, Effective, Price);

New History rows

The non-redundant rows are added to the History table:

-- Inserts
WITH Inserts AS
(
    SELECT NR.*
    FROM #NewRows AS NR
    WHERE NR.[Action] = 'I'
)
MERGE dbo.History AS H
USING Inserts AS NR
    ON NR.Product = H.Product
    AND NR.Kind = H.Kind
    AND NR.Effective = H.Effective
    AND NR.Price = H.Price
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Effective, Product, Kind, Price)
    VALUES (Effective, Product, Kind, Price);

Reinstating redundant records

Adding new records can result in redundant rows needing to be reinstated. The following query identifies qualifying redundant rows and moves them to the History table:

DELETE NextDuplicate
OUTPUT DELETED.Product,
       DELETED.Kind,
       DELETED.Price,
       DELETED.Effective
INTO dbo.History
    (Product, Kind, Price, Effective)
FROM #NewRows AS NR
CROSS APPLY
(
    SELECT TOP (1)
        H.Effective,
        H.Price
    FROM dbo.History AS H
    WHERE
        H.Product = NR.Product
        AND H.Kind = NR.Kind
        AND H.Effective > NR.Effective
    ORDER BY
        H.Effective ASC
) AS NextHistory
CROSS APPLY
(
    SELECT TOP (1)
        HD.Effective,
        HD.Product,
        HD.Kind,
        HD.Price
    FROM dbo.HistoryDuplicates AS HD
    WHERE
        HD.Product = NR.Product
        AND HD.Kind = NR.Kind
        AND HD.Effective > NR.Effective
        AND HD.Effective < NextHistory.Effective
    ORDER BY
        HD.Effective ASC
) AS NextDuplicate
WHERE
    NR.[Action] = 'I'
    AND NOT EXISTS (SELECT NextDuplicate.Price INTERSECT SELECT NR.Price);

Results

History table

╔═════════════════════════╦═════════╦══════╦═══════╗
║        Effective        ║ Product ║ Kind ║ Price ║
╠═════════════════════════╬═════════╬══════╬═══════╣
║ 2013-04-23 00:23:00.000 ║    1234 ║    1 ║ 1.00  ║
║ 2013-04-25 00:25:00.000 ║    1234 ║    1 ║ 1.50  ║
║ 2013-04-25 00:25:00.000 ║    1234 ║    2 ║ 2.00  ║
║ 2013-04-26 00:26:00.000 ║    1234 ║    1 ║ 2.00  ║
║ 2013-04-28 00:28:00.000 ║    1234 ║    1 ║ 1.00  ║
╚═════════════════════════╩═════════╩══════╩═══════╝

History Duplicates table

╔═════════════════════════╦═════════╦══════╦═══════╗
║        Effective        ║ Product ║ Kind ║ Price ║
╠═════════════════════════╬═════════╬══════╬═══════╣
║ 2013-04-24 00:24:00.000 ║    1234 ║    1 ║ 1.00  ║
║ 2013-04-27 00:27:00.000 ║    1234 ║    1 ║ 2.00  ║
╚═════════════════════════╩═════════╩══════╩═══════╝

Processing new data

The preceding steps are quite general. We can process a new batch of rows using exactly the same code. The next script loads the input table with two sample rows, one of which is a duplicate, and the other an example of needing to reinstate a previously-redundant row:

-- If needed
DROP TABLE #NewRows;

CREATE TABLE #NewRows
(
    Effective   datetime NOT NULL,
    Product     integer NOT NULL,
    Kind        tinyint NOT NULL,
    Price       smallmoney NOT NULL,
    [Action]    char(1) NOT NULL DEFAULT 'X',
);

CREATE UNIQUE CLUSTERED INDEX cuq
ON #NewRows (Product, Kind, Effective);

INSERT #NewRows
    (Effective, Product, Kind, Price)
VALUES
    ('2013-04-24 00:24:01.000', 1234, 1, 1.00), -- New duplicate
    ('2013-04-26 00:26:30.000', 1234, 1, 5.00); -- Complex new row

Running the rest of the general script produces this final state:

History:

╔═════════════════════════╦═════════╦══════╦═══════╗
║        Effective        ║ Product ║ Kind ║ Price ║
╠═════════════════════════╬═════════╬══════╬═══════╣
║ 2013-04-23 00:23:00.000 ║    1234 ║    1 ║ 1.00  ║
║ 2013-04-25 00:25:00.000 ║    1234 ║    1 ║ 1.50  ║
║ 2013-04-25 00:25:00.000 ║    1234 ║    2 ║ 2.00  ║
║ 2013-04-26 00:26:00.000 ║    1234 ║    1 ║ 2.00  ║
║ 2013-04-26 00:26:30.000 ║    1234 ║    1 ║ 5.00  ║ <- New
║ 2013-04-27 00:27:00.000 ║    1234 ║    1 ║ 2.00  ║ <- Recovered
║ 2013-04-28 00:28:00.000 ║    1234 ║    1 ║ 1.00  ║
╚═════════════════════════╩═════════╩══════╩═══════╝

History Duplicate:

╔═════════════════════════╦═════════╦══════╦═══════╗
║        Effective        ║ Product ║ Kind ║ Price ║
╠═════════════════════════╬═════════╬══════╬═══════╣
║ 2013-04-24 00:24:00.000 ║    1234 ║    1 ║ 1.00  ║
║ 2013-04-24 00:24:01.000 ║    1234 ║    1 ║ 1.00  ║ <- New duplicate
╚═════════════════════════╩═════════╩══════╩═══════╝


Can I check if a Mirror database was synchronized when the principal died?

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

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

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


Strange issue with mysql index cardiality

1) cardiality in index of innodb table is  An estimate of the number of unique values in the index, MySQL Doc shows the details of it : https://dev.mysql.com/doc/refman/5.7/en/show-index.html

Change the innodb_stats_transient_sample_pages may get more accurate values, and https://dev.mysql.com/doc/refman/5.7/en/innodb-statistics-estimation.html may help you.

2) rows in explain shows the the number of rows MySQL believes it must examine to execute the query, it's also a approximation. So the value only means MySQL believes is should examine 577 rows, not examine 577 rows exactly.


Has anyone connected to Oracle from SSIS using Oracle Driver ( 12.1.0 ) from VS 2017 Data Tools SSIS

Visual Studio and SSDT are 32bit programs, so they can't use the 64bit OleDB provider.  You can run your SSIS package in 64bit mode, but the designers don't work unless you also have the 32bit OleDB provider, and are running visual studio using a Path that allows it to load the 32bit OCI.DLL.


If you really need to run multiple threads at the same time you can enable the ignore_dup_key option on the primary key.

This will just give a warning instead of an error when an insert would result in a duplicate key violation. But instead of failing it will discard the row(s) that if inserted would cause the uniqueness violation.

CREATE TABLE [dbo].[DeleteMe](
[id] [uniqueidentifier] NOT NULL,
[Value] [varchar](max) NULL,
CONSTRAINT [PK_DeleteMe]
PRIMARY KEY ([id] ASC)
WITH (IGNORE_DUP_KEY = ON));

Example on sqlfiddle

Paul White's detailed explanation on the IGNORE_DUP_KEY option. Thanks Paul.


The problem was that all oracle linked tables in msaccess database, have the link description of an odbc of the previous database version (DBA=W;APA=T;EXC=F;FEN=T;QTO=F;). One of this parameters (Enable LOBs) was diferent and causes the error.
To solve the problem have to relink all the tables of de msaccess database. It's important to relink all, because masccess make the connection to oracle for all the linked table in the first access of any table.


How get in SQL pairs of connections in a FW Logs

SELECT DISTINCT orig_h , resp_h
from gustavo.conn
WHERE orig_h = '15.214.135.70' AND resp_h = '15.214.159.100'


Is there any reason not to use Percona 'innodb_fast_checksum'?

The wording you pasted is very specific:

  The original algorithm is checked after the new one, so you can have data pages with old checksums and data pages with new checksums. However in this case, you may experience slow reads from pages having old checksums.

This means that it can still read the old checksum, but from now on in, only Percona Server will be able to read your data.  This is later noted here:

  Once enabled, turning it off will require table re-creation as well, since it will fail to start on data files created when innodb_fast_checksums was enabled.

You've got to put it in context - InnoDB only verifies the checksums when it reads a page from the block storage device, and updates it before flushing it back to the storage device.  While in memory, the checksum is not maintained.

For many years, an IO to a disk has taken something on the order of 5-10ms (1ms = 1/1000th of a second).  Computing a checksum probably takes somewhere around 50us (1us = 1/1000000th of a second).  I don't have the actual data what it is in InnoDB's case, but if you Google "Numbers everyone should know", you'll hopefully agree I'm correct within an order of magnitude.

Enter an era now where we have things like Fusion-io flash devices, which on paper have a ~20-30us access time, and you can see that reevaluating the checksum makes sense.

My general advice: Don't ruin your backwards compatibility with MySQL releases unless you really need it.  Most people do not need it yet.


How can i return the max of a sum by each team?

If am not wrong this is what you need

select team,max(Total)
from
(
SELECT team,
       Sum (total) AS Total
FROM   campaign
WHERE  Year(time) = Year(Now())
       AND Month(time) = 12
GROUP  BY campaign_identi,
          team
) A
Group by team


Using DNS per application in ConnectionStrings

What would be the drawbacks of telling my developers to use a DNS entry to point to their respective databases in their ConnectionStrings?

Technically, now, one of the drawbacks is that they aren't going to change their connections strings... when, say, they should be adding in connection specific keywords such as MultiSubnetFailover.

You'll also have a ton of DNS items to regulate, which sounds easy - and in general, it is. I do, though, tend to see it lead to network name duplicates though.

  This way, when I migrate databases, I can do it one application at a time, making sure everything works before moving on. No code recompile needed, nor any configuration files to handle. I simply have to copy the database over and update the application's DNS entry.

This is actually pretty common, especially in environments that use tons of 3rd party applications where the connection string information isn't known or licensing code prevents the renaming of a connection (ugh).

  Because it sounds too easy and beneficial not to be a very standardized practice, yet I have never come across this suggestion ever.

See above. Many of the companies I know have an alias policy for that reason. It's also how AGs work for the listener as it is a network layer indirection.

  Would Kerberos be an issue?

If you're using a CNAME, it'll just point to the actual host so you won't have any issues there. If you put in a new A record then it will cause an issue and you'd need to setup the SPN for the new A record. Otherwise, with a CNAME, the SPN should be the name of the host.


You can do a UNION between the two tables and modify the WHERE clause based on the parameter passed (i.e., SELECT from both tables and let the parameter decide whether to return any rows).
Or you can use dynamic SQL, i.e., build a string and execute that string using sp_executesql. Required reading before using dynamic SQL is http://www.sommarskog.se/dynamic_sql.html. Erland even has a section about this particular requirement.
And, you can of course have an IF and do two separate SELECTS, which one depends on the contents of the variable.
To be honest, above "solutions" sucks. I suggest you take a step back and look at the broader picture.
And to make it clear: You can not have a variable or parameter for the table name in a DML command.


How can I create a Windows Firewall rule to allow only one IP address to connect MS SQL 2017 Express database in Windows 2016 server?

Seems follow-up question of this. However, following are the recommended actions in your case which could be quick release from the cause:

Define static custom port (other than 1433) for the SQL server service

Service restart required here, and the custom port number must be explicitly mentioned in connection string of application. i.e. (DataSource = DatabaseServerIP,PortNumber)

Create new login with sysadmin permissions
Disable the login sa

Also, consider applying security benchmark recommendations particularly the sections Surface Area Reduction and Authentication and Authorization.

Most importantly, do not let the database server expose to public network. Usually, the app server only exposing to public.

  Firewall rule to allow only one IP address (such as 45.23.56.12)

Start Windows firewall (you can use wf.msc from run command), select entry that related to SQL server from the In-bond Rules list (if not existed create new rule)

To Allow specific IP
Right click rule → Properties → Scope → from "Remote IP Address" section select These IP Addresses option and Add desired IP Addresses that only needs to be connect SQL Server

To change port numbers
Right click rule → Properties → Protocols and Ports → Enter the custom port (that SQL service listening on - configured in above steps) at Local port section.


ORA-15041: diskgroup space exhausted
Cause:  At least one disk in the diskgroup is out of space.
Action: If all disks are evenly balanced, add more disks to the diskgroup.

According to the log provided in the question, i found that it was unable to complete the archiving operation as the asm diskgroup '+FRA_DATA' is full. The DML operation on large table might have caused this error.
You can check if there are extra files - including orphaned archivelogs - that may be in the FRA_DATA.

RMAN>crosscheck archivelog all;

If there is no more space available in FRA_DATA then add disks to diskgroup using ASMCA or ASMCMD utilities.
After adding disks to the diskgroup also check the value of db_recovery_file_dest_size initialization parameter.
The error 15041 possibly relates to the disks in that particular diskgroup.
This can happen if the Diskgroup contains disks of unequal sizes.
See Oracle Support Document ORA-15041 IN A DISKGROUP ALTHOUGH FREE_MB REPORTS SUFFICIENT SPACE (Doc ID 460155.1)


Can I get a tree-structure from a self-referenced (hierarchical) table?

Add a "path" field and sort by that similar to a file path. As ypercube mentioned, the sorting is overly simplistic in this example and just happens to work but for simplicity's sake I'll leave as is. Most of the time when I use this pattern I sort by name rather than ID anyway.

IF OBJECT_ID('[dbo].[btree]', 'U') IS NOT NULL
    DROP TABLE [dbo].[btree];
GO

CREATE TABLE [dbo].[btree]
(
  id INT PRIMARY KEY
, parent_id INT REFERENCES [dbo].[btree] ([id])
, name NVARCHAR(20)
);
GO

INSERT INTO [btree] VALUES (1, null, '1 Root');
INSERT INTO [btree] VALUES (2,    1, '1.1 Group');
INSERT INTO [btree] VALUES (3,    1, '1.2 Group');
INSERT INTO [btree] VALUES (4,    2, '1.1.1 Group');
INSERT INTO [btree] VALUES (5,    2, '1.1.2 Group');
INSERT INTO [btree] VALUES (6,    3, '1.2.1 Group');
INSERT INTO [btree] VALUES (7,    3, '1.2.2 Group');
INSERT INTO [btree] VALUES (8,    4, '1.1.1.1 Items');
INSERT INTO [btree] VALUES (9,    4, '1.1.1.2 Items');
INSERT INTO [btree] VALUES (10,   5, '1.1.2.1 Items');
INSERT INTO [btree] VALUES (11,   5, '1.1.2.2 Items');
INSERT INTO [btree] VALUES (12,   6, '1.2.1.1 Items');
INSERT INTO [btree] VALUES (13,   6, '1.2.1.2 Items');
INSERT INTO [btree] VALUES (14,   7, '1.2.2.1 Items');

;WITH tree AS
(
    SELECT c1.id, c1.parent_id, c1.name, [level] = 1, path = cast('root' as varchar(100))
    FROM dbo.[btree] c1
    WHERE c1.parent_id IS NULL
    UNION ALL
    SELECT c2.id, c2.parent_id, c2.name, [level] = tree.[level] + 1,
           Path = Cast(tree.path+'/'+right('000000000' + cast(c2.id as varchar(10)),10) as varchar(100))
    FROM dbo.[btree] c2 INNER JOIN tree ON tree.id = c2.parent_id
)
SELECT tree.path, tree.id, parent_id, REPLICATE('  ', tree.level - 1) + tree.name AS description
FROM tree
Order by path
OPTION (MAXRECURSION 0)
;

Here a rextester


Is there any performance difference in using a unique index on primary key?

A PRIMARY KEY is, by definition, UNIQUE and NOT NULL, so by adding a second UNIQUE keyword on it you are actually creating a separate index. This does not only make things less efficient but, in some cases, it will make your queries less performant.

You can check that two index were created by doing:

mysql> SHOW CREATE TABLE test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `c` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Both in MyISAM and InnoDB, it will require additional space, it will make the writes slower (because the index has to be updated synchronously at write time), and it may waste optimizer (even if microseconds) time deciding which index is the best one. MySQL would usually select the PRIMARY key in MyISAM, but if for any reason both indexes were used, you will also waste memory.

If you do not believe me, believe MySQL:

mysql> INSERT test1 SELECT * from test;
Query OK, 1691648 rows affected (6.84 sec)
Records: 1691648  Duplicates: 0  Warnings: 0

mysql> create table test2 like test1;
Query OK, 0 rows affected (0.04 sec)

mysql> ALTER TABLE test2 add unique(id);
Query OK, 0 rows affected, 1 warning (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> INSERT test2 SELECT * from test;
Query OK, 1691648 rows affected (10.94 sec)
Records: 1691648  Duplicates: 0  Warnings: 0

A run of pt-duplicate-key-checker is usually enough to detect those duplicates.

If you mean using UNIQUE NOT NULL instead of PRIMARY KEY, I would not expect a difference on MyISAM in most cases (although some tools work better with a PK, like row-based replication); it is not the case with InnoDB, in which the PK is fundamental on its structure (clustered rows around it).


Decryption not working when SQL Server database copied to new server

The sequence of steps is entirely depended on you current (old db) encryption hierarchy. There is no cookie-cutter recipe that can be applied w/o knowing your encryption hierarchy, which you did not present. It cannot be deduce from the code you posted, steps like creating a new certificate after the database copy is attached make not much sense. Normally after you copy the file and attach it to the new server all you have to do is open the master key and add the server master key encryption:

USE [MyDatabase];
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = '...';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO

Assuming a typical data->symmetric key->certificate->database master key->service master key encryption hierarchy the steps above are sufficient.


Journey.CustomerIDis a foreign key to Customer.CustomerID. Journey.TrainNo is a foreign key to TrainRide.TrainNo.

The relationship here is many to many. A TrainRide can have many customers. A customer could ride many TrainRides. The table Journey is used to associate the two. You can see the same customer more than once in this table such as 834 or the same train more than once such as Amtrak 333.


Make one table for all surveys. Don't make a new table for each survey.

Also, make sure you hash and salt your passwords with bcrypt.


Why is “Access this computer from the network” permission required for proxy credential?

Because if the account doesn't have the "Access this computer from the network" privilege your job step will fail with

Message Unable to start execution of step 1 (reason: Error
authenticating proxy xxxx\xxx, system error: Logon failure: the user
has not been granted the requested logon type at this computer.
('Access this computer from network')).  The step failed.

Ok, followup question, why is that?  I don't know, but either SQL Agent is performing the impersonation using a call to LogonUser with a dwLogonType that requires this privilege, or SQL Agent is checking the privilege directly and generating this error message, instead of just trying to perform the job step and reporting a failure.
In both cases, this suggests a security feature in SQL Agent.
IE if you don't have the privilege to connect remotely to this computer, but you do have the ability to connect to to SQL Server and create a job, then you could bypass the bar on remote authentication when SQL Agent logs you on locally to run your job step.


Looks like it's the write to the directory that's failing, but at the OS level.

Does the OS user runing impdp have OS permissions to write to the directory?


Why does adding an index on a column containing a char version of a UUID decrease performance?

The EXPLAINs show that, with the index, it used the index.  That lead to jumping around:  Pick the next alphabetical UUID, reach into the table in random location for the rows to find the amounts to SUM up.
Without the index, it simply plowed through straight through the table, collecting data, then sorting in order to finish the task.
3.5M random lookups apparently takes longer than sorthing 3.5M rows.
Converging the UUID to BINARY(16) would shrink it, thereby helping a little.
Having INDEX(uuid, amount) would significantly speed up this query.  But still, the random nature of uuids is a performance drag.
Some related discussion:  http://mysql.rjweb.org/doc.php/uuid


SQL Server transaction log growing without control

The only way to "solve" it somehow is backing up

Because that is the only way to truncate the log. See How to Shrink the SQL Server log for an explanation on what happens. Normally log backups are scheduled very frequently (think 10 minutes) and they truncate the log. You want a frequent log backup not only because it eliminates the growth problem, but primarily because you want to minimize the amount of transactions lost in case of disaster (crash).

If you want a different behavior, change the recovery model to SIMPLE. In case of failure you will loose all the transactions since your last full backup, but there is no free lunch, is there?


With psql you could \echo text (or \qecho if using \o), for example executing with psql -f a file with contents:

select 'Straight';
\echo BETWEEN
select 'the lines.';

outputs:

 ?column?
----------
 Straight
(1 row)

BETWEEN
  ?column?
------------
 the lines.
(1 row)


Table 1 and Table 2 have matching columns. How do I join the 9th column from table 2 to table 1 based on the matches?

SELECT *
FROM tbl1
JOIN tbl2
  USING (
    acct_number,
    system,
    source,
    object,
    group,
    code,
    detail,
    routing,
    complexity
  );

But this is insanity. You should be reducing this to a sane primary key. Even if you're primary key is a natural key, this many columns is highly indicative of bad practice.


Multi master mysql with automerge data if node disconnected

Galera has the principle that a "quorum" in needed to declare that the cluster is alive.

For example if 'a' disconnects from 'b' and 'c', but 'b' and 'c' are still talking to each other, then

b and c have a quorum (more than half the servers), so they declare they are the cluster.
'a' does not have a quorum, so it refuses to accept any writes.
If the network gets repaired, 'a' will ask b & c for the latest updates and get back in sync.

That is Galera.  If you have plain MySQL multi-master, you have a nightmare when the network breaks.

NDB Cluster works a different way -- every node can be disconnected.  However, you must provide rules for "eventual consistency" of the data.


mysql user creation with % to host , can only log on from remote host

When connecting to your localhost it's going you use sockets instead of ports to connect.  Those grants need a specific 'localhost' grant.  To use your % grant locally connect specifying you want to connect via TCP

mysql -u user -p --proto=TCP

Further reading


myql performance : extremely slow inserts on a faster box

Do not insert one row at a time; batch them.  That is, have 1000 rows being inserted by each INSERT statement.

That will help both servers

To find out why there is a difference, ...  Compare the CREATE TABLE.  Compare SHOW VARIABLES LIKE 'innodb%';.  If those don't point out anything, compare all the VARIABLES.  The likely suspect is innodb_flush_log_at_trx_commit.  Note:  Don't simply look at my.cnf; some defaults are different between versions.


Entity or attribute if only one field beside key and attribute for other entity?

Its has become its own Entity. You now have a 1:N relationship between Person and Email.


I would implement this using a stored procedure instead of a trigger.  Use a separate key table to store the last used evidence number for each case.

I mocked up a minimally viable complete example.

Drop the objects from tempdb if they already exist, so we can modify the code as required.

USE tempdb;

IF OBJECT_ID(N'dbo.AddCase', N'P') IS NOT NULL
DROP PROCEDURE dbo.AddCase;
IF OBJECT_ID(N'dbo.AddEvidence', N'P') IS NOT NULL
DROP PROCEDURE dbo.AddEvidence;
IF OBJECT_ID(N'dbo.EvidenceKeys', N'U') IS NOT NULL
DROP TABLE dbo.EvidenceKeys;
IF OBJECT_ID(N'dbo.Evidence', N'U') IS NOT NULL
DROP TABLE dbo.Evidence;
IF OBJECT_ID(N'dbo.Cases', N'U') IS NOT NULL
DROP TABLE dbo.Cases;
GO

Create a Cases and Evidence table, along with an EvidenceKey table to store the incrementing Evidence Number.

CREATE TABLE dbo.Cases
(
    CaseID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_Cases
        PRIMARY KEY CLUSTERED
) ON [PRIMARY];

CREATE TABLE dbo.Evidence
(
    EvidenceID int NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_Evidence
        PRIMARY KEY CLUSTERED
    , CaseID int NOT NULL
        CONSTRAINT FK_Evidence_CaseID
        FOREIGN KEY
        REFERENCES dbo.Cases(CaseID)
    , EvidenceNum int NOT NULL
    , CONSTRAINT UQ_EvidenceNum
        UNIQUE (CaseID, EvidenceNum)
);

CREATE TABLE dbo.EvidenceKeys
(
    CaseID int NOT NULL
        CONSTRAINT PK_EvidenceKeys
        PRIMARY KEY CLUSTERED
    , MaxEvidenceNum int NOT NULL
);
GO

Create a procedure used to add a new Case.  You'd need to add parameters to this such as the Case Name, date, etc.

CREATE PROCEDURE dbo.AddCase
(
    @CaseID int OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Cases TABLE
    (
        CaseID int NOT NULL
    );
    INSERT INTO dbo.Cases
    OUTPUT inserted.CaseID
    INTO @Cases (CaseID)
    DEFAULT VALUES;
    SELECT @CaseID = CaseID
    FROM @Cases;
END
GO

Create a procedure to add Evidence.  Again, this is only a proof-of-concept, so you'd need to add parameters to deal with the actual evidence item details.

CREATE PROCEDURE dbo.AddEvidence
(
    @CaseID int
    , @EvidenceID int OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @MaxEvidences TABLE
    (
        MaxEvidenceNum int NOT NULL
    );
    SET @EvidenceID = NULL;
    UPDATE dbo.EvidenceKeys
    SET MaxEvidenceNum += 1
    OUTPUT inserted.MaxEvidenceNum
    INTO @MaxEvidences(MaxEvidenceNum)
    WHERE dbo.EvidenceKeys.CaseID = @CaseID;
    SELECT @EvidenceID = MaxEvidenceNum
    FROM @MaxEvidences;
    IF @EvidenceID IS NULL
    BEGIN
        INSERT INTO dbo.EvidenceKeys (CaseID, MaxEvidenceNum)
        VALUES (@CaseID, 1);
        SET @EvidenceID = 1;
    END
    INSERT INTO dbo.Evidence (CaseID, EvidenceNum)
    VALUES (@CaseID, @EvidenceID);
END;
GO

Insert some sample data:

DECLARE @CaseID int;
DECLARE @EvidenceID int;

EXEC dbo.AddCase @CaseID OUT;
EXEC dbo.AddEvidence @CaseID, @EvidenceID OUT;
SELECT @EvidenceID;
EXEC dbo.AddEvidence @CaseID, @EvidenceID OUT;
SELECT @EvidenceID;
EXEC dbo.AddEvidence @CaseID, @EvidenceID OUT;
SELECT @EvidenceID;

EXEC dbo.AddCase @CaseID OUT;
EXEC dbo.AddEvidence @CaseID, @EvidenceID OUT;
SELECT @EvidenceID;
EXEC dbo.AddEvidence @CaseID, @EvidenceID OUT;
SELECT @EvidenceID;
EXEC dbo.AddEvidence @CaseID, @EvidenceID OUT;
SELECT @EvidenceID;

Each execution of dbo.AddEvidence will increment the value in the dbo.EvidenceKeys table for the given @CaseID in a single atomic operation, reducing the chance for locking to become a problem.

SELECT *
FROM dbo.Cases c
    INNER JOIN dbo.Evidence e ON c.CaseID = e.CaseID

Results from the select above:

╔════════╦════════════╦════════╦═════════════╗
║ CaseID ║ EvidenceID ║ CaseID ║ EvidenceNum ║
╠════════╬════════════╬════════╬═════════════╣
║      1 ║          1 ║      1 ║           1 ║
║      1 ║          2 ║      1 ║           2 ║
║      1 ║          3 ║      1 ║           3 ║
║      2 ║          4 ║      2 ║           1 ║
║      2 ║          5 ║      2 ║           2 ║
║      2 ║          6 ║      2 ║           3 ║
╚════════╩════════════╩════════╩═════════════╝

Since obtaining the maximum EvidenceKey value for any given CaseID, and updating the dbo.EvidenceKeys table, occurs in a single atomic statement, the opportunity for deadlocks is vastly reduced, without the need for locking hints.

To test this design, I ran the following code.  The first piece creates 100 "cases", each with 3 rows of "Evidence".  Then, in 3 separate sessions, the 2nd piece of code inserts 100,000 rows into the Evidence table, randomly assigning each evidence row to a randomly chosen case.  No deadlocks occurred, and the process took under 1 minute on my old, slow, dev workstation.

DECLARE @loop int = 0;
DECLARE @CaseID int;
DECLARE @EvidenceID int;
WHILE @loop < 100
BEGIN
    EXEC dbo.AddCase @CaseID OUT;
    EXEC dbo.AddEvidence @CaseID, @EvidenceID OUT;
    EXEC dbo.AddEvidence @CaseID, @EvidenceID OUT;
    EXEC dbo.AddEvidence @CaseID, @EvidenceID OUT;
    SET @loop += 1;
END
GO

This piece should run in 3 (or more) separate sessions:

DECLARE @loop int = 0;
DECLARE @CaseID int;
DECLARE @EvidenceID int;
WHILE @loop < 100000
BEGIN
    SET @CaseID = (SELECT TOP (1) CaseID FROM dbo.Cases ORDER BY CRYPT_GEN_RANDOM(10));
    EXEC dbo.AddEvidence @CaseID, @EvidenceID OUT;
    SET @loop += 1;
END


It's definitely SQL Azure. You can easily recognize it as the icon is azure, unlike the green one for a regular SQL Server instance

You can see the the video by Thomas LaRock here

How To Connect to SQL Azure database using SSMS


How to find missing data from one table that is a join of two other tables

You need all combinations from the two tables, so a CROSS JOIN and then remove the ones in the 3rd table, using either NOT EXISTS or EXCEPT:

SELECT
    s.stockcode, l.locno
FROM
    dbo.stock AS s
  CROSS JOIN
    dbo.location AS l

EXCEPT

SELECT
    si.fkstockcode, si.fklocno
FROM
    dbo.stock_loc_info AS si ;


Besides the overview shown by .help, the is no more information in the shell.

You have to use the on-line documentation of the shell or the SQL language.


How to pass, specific day month and year in the below query?

First create a table for as calendar in your database and fill it with few years data.

CREATE TABLE `calendar` (
`date_field` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Then prepare your query  like :

set @dy='6',@mnth='2',@yr='2016';

select c.date_field,IFNULL(t.val,0) val
from calendar c
  left outer join
   (SELECT date_field,val FROM school) t
    USING (date_field)
 where if(@dy='',1,day(c.date_field)=@dy)
   and if(@mnth='',1,month(c.date_field)=@mnth)
   and if(@yr='',1,year(c.date_field=@yr);


This explanation does not provide a complete answer to the question, but it does address critical considerations in coming up with a good solution.  So it is an answer, just not a complete one.

Since I was the one who discouraged "flags", I'll further qualify my previous answer. The bad flags I mentioned where used to keep track of related database rows that could otherwise be handled by proper, normalized table and relationship design. Such bad flags do not tell you anything about the state of the data objects that you want to store, rather where only for database organizational purposes. Boolean (yes/no) flags may be okay and even necessary for recording an existing, valid state of the object(s) you are storing. That's the first test for determining if the flags are okay.

The next consideration, for any type of flag, is whether or not the chosen database schema allows contradictory states to be stored. The answer to how you handle this situation can be complicated, but

you might be able to change/augment/refactor the database schema to avoid or eliminate contradictory states, or
be prepared to write all queries and updates to consider and handle such possible contradictions. Sometimes this is necessary if the real-world scenario itself allows such contradictions. For instance, perhaps you assign an employee to two positions, thinking that perhaps they can handle 3 positions, so you leave is_indeterminate checked, but then after a week on the job they are overwhelmed and you realize that they could not handle more work, so their is_indeterminate value is unchecked. In that case, there was nothing of an automated fashion that could've fixed that scenario.

On the other hand, what if that employee were now given a 3rd position, but the is_indeterminate checkbox was not properly updated. Now the system has inaccurate data. Is that tolerable? Will the workplace have someone that runs reports to periodically audit and fix bad data? Will your update routines handle possible bad data, so that, for example, the employee isn't underpaid because it says they need more work? Etc. etc.

Per #1 suggestion above, a possible change would be to replace a basic Boolean flag column with a position counter column. A query could simply indicate if the position has the correct number of employees assigned and flag shortages or over-staffing... but this time the "flag" is dynamically determined in the report, not stored in the database where it can become outdated.

Regarding the pseudo-code...  We're back to using a bad flag like staffing_stadus_id = 1 == complete.  I'll admit that sometimes such a flag can be useful for efficiency or simplicity reasons... like a final stamp that means "it's all done and don't ever bother me with that position again".  Maybe that's okay.  BUT, on the other hand, simply by adding a record to the [Position_Employee] table should be sufficient to determine if that particular position is complete.  In other words, a query looks for all related records and if there are sufficient employees assigned to the position, then it is dynamically determined to be complete... no need to update a separate column with specialized codes that will certainly become outdated either by bugs or people not updating the status appropriately.  If the schema/app is designed properly, the only way to mark the position as complete is to actually assign employees properly to the correct table... No accidentally updating the status, but forget to actually add the employee that was put in the position.

But once again, it could be necessary to have a "status flag", for instance if you want to maintain a history of positions, but perhaps a position is not yet filled but also determined to be obsolete.  In that case, you would indeed want a flag that you could use to mark it as obsolete.

This might all seem subtle and even contradictory, but go back to the original 2 points and consider why you are adding each flag and consider if it can be implemented in a more natural, relational-database-esque manner.

Also, why are there both employee_id and position_id FK columns in both the [Staffing] table AND the [Position_Employee] tables?  Once again, this sets up possible contradictory columns that could be different/outdated between the two tables.  Once an employee is chosen for the position, then a record is simply added to the [Position_Employee] table. Why add the employee_id to the [Staffing] table to then just copy it into the [Position_Employee]?

If there is another legitimate business reason for having the employee_id in the [Staffing] table, then schema needs to consider that requirement separately.  For instance, perhaps you want to store a potential employee for manual approval, and they are not put into the position until final approval is obtained.  Then a better relational-database-esque schema would be another table, perhaps a [Staffing_potential] table with a staffing_id column and employee_id column.  Once they are approved, then move (atomic transaction of copy/insert then delete) from the [Staffing_potential] to the [Position_Employee] table.  Once again, the staffing status is implied in what tables the rows exist rather than messing with flag columns.

Update

It's always difficult to judge from the questions and the context the reason for each particular detail, despite the attempt to describe those details.  It is now apparent that your design and requirements are mature enough that not only are there probably many ways to accomplish what you need, but there is really no answer to "how I can do this best?" without a full iterative analysis.  That can't be done on these forums.  In fact, I started with Stack Exchange long ago enough that I previously would have rejected this question as "too broad".  But the recent push for being "nice and open" has lead me to provide more wordy and specific answers.  I won't take back the advise I posted, but I'll wrap up this answer with...

Especially for historical or archival reasons, it may indeed be necessary to store duplicate data or even have supplemental "flag" fields.  As long as there is a documented purpose and reason for this, database normalization rules are not necessarily "broken".  I ran into that often with financial data where it was critical to record unalterable total transaction amounts along with constituent rows, even though the total could have been obtained from the detail rows.  BUT the key is that either documentation or column names or some other feature distinguish between the purposes of the fields, not to be mistaken for non-normalized data.  It's up to the developer(s) to ensure that the purpose of such fields are not eventually used for reasons that break stable, normalized design.  (FYI, it often good to have separate tables where such historical rows are kept, which are separate from the "working data".)

The fact that you are conscious and aware of the purpose of individual features of your schema is critical.  I don't have any further advise about handling the indeterminate employment cases.


How to get the top 20 percent records with a single query?

The LIMIT clause parameters are always literals. So You cannot use the value calculated by some statement in it.

The solution can be in prepared statement use.

Variant 1:

SET @sql := CONCAT( 'SELECT start_coins FROM coins ORDER BY start_coins DESC LIMIT '
                  , (SELECT ROUND(COUNT(*) * 0.2) FROM coins));
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;

Variant 2:

SELECT COUNT(*) * 0.2 INTO @cnt FROM coins;
PREPARE stmt FROM 'SELECT start_coins FROM coins ORDER BY start_coins DESC LIMIT ?;';
EXECUTE stmt USING @cnt;
DROP PREPARE stmt;

In both variants You MUST to execute all 4 statements in the same connection to database. The best vairant is the connector You use allows multi-statements (sending all statements as 1 command).


You have mentioned the two common solutions to this use case - point-in-time capture and history tracking.

Of the two, point-in-time capture is the more robust and easier for audit purposes. You capture exactly what was presented to the purchaser at the time they agreed to the sales contract. There is no argument about subsequent data creep. I have worked on a couple of enterprise implementations which adopted this approach. As a mental sop you can justify the duplication of values by saying the two tables have different semantics - the source is what is available and the destination is an event capture.

With history tracking you have to be absolutely rigorous ensuring every update or insert by every user, application, utility, ETL job, batch load and whatever, always, for the entire life of the system, sets the "from" and "to" dates, and nothing ever, ever deletes anything. This is hard (although temporal tables make it easier).


No, it does not remove backups. They are just missing from the controlfile, and you can catalog them.
When the T database was refreshed yesterday from P, a new incarnation of T started. But your UNTIL TIME clause refers to a point of time before that happened, when the previous incarnation was active.
After restoring the controlfile on C, mount the database, then you can use the following command to view the incarnations:

RMAN> list incarnation;

You will also see the times when they started. Revert to the previous incarnation, e.g:

RMAN> reset database to incarnation 1;

Catalog the backups:

RMAN> catalog start with '/path/to/the/backups';

Then continue with restore and recover as you normally would.


Data Schema For Stock Control / Multi Source Inventory

You can check out the book Data Model Patterns by David Hay.  It has inventory and product modeling patterns.
It's hard to judge the logical correctness of model without more business details, but I think you should have someone with more experience in data modeling review the ERD so it meets business requirements.  You enumerated 1:many relationships, but you modeled many:many which more than meets what's stated.
From a technical perspective, you should not define the product table with PK column id char(36); define the column as product_id int or product_id raw(16) if you really think you have to.  RAW is an Oracle data type, use the appropriate data type for your DB platform.  This goes for all the tables.  Personally, I would not put id in all those "link" tables, it's not necessary.


How can I reset a mysql table auto-increment to 1 in phpMyAdmin?

phpmyadmin
Perhaps you could just select the phpMyAdmin Operations tab:

In phpMyAdmin, click on the table you want to reset or change the AUTO_INCREMENT value
Click on the Operations Tab
In the Table Options box find the auto_increment field.
Enter the new auto_increment starting value
Click on the Go button for the Table Options box.

Since this one of the most frequently asked questions for phpmyadmin, you can learn more about this in this blog : http://trebleclick.blogspot.com/2009/01/mysql-set-auto-increment-in-phpmyadmin.html
Supplemental Info
For an empty table, another way to reset the auto_increment attribute is to run
TRUNCATE TABLE mydb.tablename;

Don't run this if you have data in it. If you want to hose the data, then be my guest.
In phpmyadmin, just click the SQL tab, enter the command, and run it.
For a nonempty table, you may want to adjust the auto_increment attribute to the highest existing id in use in case higher entries were deleted.
First, optimize the table
OPTIMIZE TABLE mydb.mytable;

Next, locate the highest value for the auto_increment column (say it is id)
SELECT MAX(id) maxid FROM mydb.mytable;

Suppose the answer returns 27. Goto the Operations tab and enter 28.


DB Design - Different tables that share a common table - each has their own values

Is there another approach?

The alternative approach is to use the "Table-per-Hierarchy" design, and store all types of Persons in a single table, eg

create table Persons
(
  Id int primary key,
  PersonType char(6) check PersonType in ('Clerk','Client'),
  LocationId int references Locations,
  . . .
)


PostgreSQL table lock for certain select wheres

I think this could be achieved with an advisory lock

Before doing the insert every transaction would acquire an advisory lock e.g. for the user_id/make_id combination, so any subsequent transaction that tries to do something for the same user/make, would have to wait:

-- this waits until the lock can be granted
select pg_advisory_lock(2,1);

SELECT COUNT(*)
FROM purchase
WHERE user_id = 2
  AND make_id = 1;

INSERT INTO purchase (user_id, make_id) VALUES (2, 1);

You can also use pg_try_advisory_lock() which returns immediately with a boolean result whether the lock could be granted or not, if you don't want the session to wait for the lock (but show an error message "already locked" or something similar).

The lock is automatically released when you commit (or rollback) your transaction (so you have to make sure to turn off autocommit and wrap all statements in a single transaction)

As deszo pointed out: it's important that all involved parts of the code stick the to the contract that an advisory lock is required. The approach using an advisory lock does not guarantee in any way that conflicts may occur.


How to use prepared statements within MySQL stored procedures?

The solution was to SET the variable in the prepared statement itself as in:

SET @sql = CONCAT('SET @curid = SELECT id
                                FROM coupon_operations
                                WHERE user_id = ?
                                ORDER BY id ASC
                                LIMIT ?, 1');


Why is the subquery apparently correlated? How to avoid it?

Your suppositions are correct, MySQL versions up to 5.5 only can execute those kind of subqueries (semi-joins) in that particular way. The subquery is not correlated in reality (there is no real dependency), but the DEPENDENT SUBQUERY that you may get from explain means that it is executing it as if it was one.

Please note that in some scenarios, that execution plan could be the most optimal one (checking the existence of the rows one by one), but in many others, like yours, it will probably not. This is a very well known query plan limitation of MySQL <= 5.5, as you can see on these slides I created about query optimization:

Query optimization with MySQL 5.6: Old and new tricks - Slide 54 (That subquery in 5.5)
Query optimization with MySQL 5.7 and MariaDB 10: Even newer tricks - Slide 64 (A common 5.5 performance problem)

You can either upgrade to 5.6 (or use >= MariaDB 5.3) where there are more options available for its execution; or manually convert the query to a JOIN or a scalar subquery if you want to stick with MySQL <=5.5. The 5.6 and the current in-development 5.7 contain a great number of optimizations that make the life easier for the developer.


Vertica DB user permissions becomes active only after connection is closed

To enable the newly granted role you need to issue the command SET ROLE mynewrole in that user's session. If you need this role to be effective every time you may want to change the user's default role: ALTER USER ... DEFAULT ROLE ....

References:

Enabling roles
Changing default role


This is a perfectly acceptable way to implement pessimistic locking without affecting concurrency.

You need to ensure, at the logical level, that whenever one session edits a record (which can translate to multiple rows in multiple tables at the physical level), no other session can modify any components of that logical record -- this is necessary to ensure consistency of your business data. To enable this you need to place some sort of a lock on all affected table rows. This, however, presents a problem in an interactive application, because humans are inherently slower than computers, they tend to look out of the window, make phone calls, go for a smoke or out for lunch before finishing the edit, which potentially leaves records locked for long time.

If you were to rely on physical U or X row locks in a pessimistic locking scenario, you sometimes might prevent other concurrent transactions from reading (or even reading past) the locked rows, thus affecting overall application performance. A safer, from the concurrency point of view, alternative is to logically mark certain rows as being edited by some session (by setting the InUse flag in your example) without placing physical locks on them.

The cost of these extra updates to InUse in an interactive application is negligible, since, as we noted, the human component in it is still much slower.

This, of course, only works if all applications accessing the database know about and honour these logical locks.


Since your DC is on VM, I suspect you are experiencing poor disk performance. Poor disk performance can result in slower log-write times on the secondary which can result in a slower acknowledgment back to the primary replica from the secondary replica (exhausting worker threads).

Disk latency on Secondary Replica can cause an increase in HADR Sync Commit process, resulting in the Primary holding open threads while waiting for the Secondary to acknowledge the transaction.

Please check the error log for Deadlocked Schedulers and collect some IO metrics from PerfMon to see the disk latency and the disk queue length.


1) Is that the correct code to run?

Well, kind of. It would probably look a bit more like this:

RESTORE DATABASE [YourMom] WITH RECOVERY

  2) Does that automatically restore any transaction logs that have shipped since the last restore Agent Job was ran?

No, it's your job to make sure that Log Shipping is caught up, and if possible (if the primary is still available), take a Tail of the Log (sounds like a bar, I know) backup, which will grab any remaining transactions, and put the database on the primary in a restoring state (will cut off any current users). The TOL backup is a usually a manual step, unless you have third party tools (like Quest LiteSpeed to help).

  3) Once the restore Agent Job runs, do I need to keep the .trn files?

This isn't a yes or no question. You should keep all backups according to your data retention policy.

Hope this helps!


Restoring a SQL Server 2008 R2 backup on SQL Server 2005

You can't restore a 2008 backup on SQL Server 2005. Simply not possible.

You'll need to either:

(a) Restore it on 2008, then use Import/Export wizard, Generate Scripts Wizard, Copy Database Wizard, SSIS, 3rd party tools like SQL Compare, etc. to move the structure and data to the 2005 instance, or

(b) Upgrade. Developer Edition is $50 or so.


A request that is being blocked does no work.  Here's an example:

Create a blocking query

use AdventureWorks2012;
go

begin tran;
    update HumanResources.Department
    set Name = 'testing 1 2 3'
    where DepartmentID = 7;
--rollback tran;

Run a query that will be blocked by the above session

use AdventureWorks2012;
go

select *
from HumanResources.Department;

Now look at any "work" this blocked request could be doing (query sys.dm_exec_requests for this blocked session, and then wait some time and query again to compare/contrast):

select
    start_time,
    status,
    total_elapsed_time_sec =
        total_elapsed_time / 1000,
    cpu_time,
    reads,
    logical_reads,
    writes
from sys.dm_exec_requests
where session_id = 52;  -- my blocked session's spid

waitfor delay '00:00:10';

select
    start_time,
    status,
    total_elapsed_time_sec =
        total_elapsed_time / 1000,
    cpu_time,
    reads,
    logical_reads,
    writes
from sys.dm_exec_requests
where session_id = 52;  -- my blocked session's spid

What you see here is a request that is doing no work, as it is not in the running state.  When it is suspended then it is not on a processor doing any work.


I was not able to reproduce your error (although I only tested with one table in the mix).

An OLE DB Destination has the ability to disable trigger and other check constraints when it loads but the OLE DB Command does not provide a similar facility.

Set up

I created a simplistic table: a key, a value and an empty field that will be populated, via trigger, when the row is updated. I created a trigger that will update the empty field for the modified row. Finally, I created a stored procedure to handle actually performing the UPDATE statement.

CREATE TABLE dbo.dba_128344
(
    Foo int NOT NULL
,   Bar char(1) NOT NULL
,   Blee datetime NULL
);
GO
CREATE TRIGGER tr_dba_128344_update
ON dbo.dba_128344
FOR UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE
        D
    SET
        Blee = CURRENT_TIMESTAMP
    FROM
        Inserted AS I
        INNER JOIN
            dbo.dba_128344 AS D
            ON D.Foo = I.Foo;
END
GO
CREATE PROCEDURE dbo.dba_128344Update
(
    @Foo int
,   @bar char(1)
)
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE
        DBA
    SET
        DBA.Bar = @bar
    FROM
        dbo.dba_128344 AS DBA
    WHERE
        DBA.Foo = @Foo;

    -- Induce a 1 second delay
    -- Allows us to ensure we aren't cheating
    WAITFOR DELAY '00:00:01';
END
GO

My package is simplistic. A data flow with an OLE DB Source and an OLE DB Command.

My OLE DB Source uses the following query which just calculates the next value of Bar in the ASCII table and also brings in the original value. A will become B, B becomes C, C becomes D.

SELECT
    D.Foo
,   D.Bar AS OldBar
,   CHAR(ASCII(D.Bar) + 1) AS NewBar
FROM
    dbo.dba_128344 AS D;

The OLE DB Command uses the following statement.

EXECUTE dbo.dba_128344Update ?, ?;

OLE DB Connection Managers require the use of the ? as an zero ordinal based replacement system. Here I mapped column Foo to the parameter @Foo and NewBar to the parameter @bar because I'd hate to be consistent in my casing...

After execution, as expected I saw my dates populated with ~ 1 second delay between each row to commensurate with the row-by-row nature of the OLE DB Command.

Foo Bar Blee
10  B   2016-02-04 22:53:18.043
20  C   2016-02-04 22:53:19.050
30  D   2016-02-04 22:53:20.053

Biml

If you'd like to confirm you're seeing the same behaviour on your system, you're in luck. Biml, the Business Intelligence Markup Language, is an XML dialect that can be fed into a translator to generate SSIS packages. It's pretty slick. All you need to do is download and install the free add-in BIDS Helper

Once installed, right click on an SSIS project and select Add new Biml File
Double click BimlScript.biml and paste the following content
1, Adjust line 3 to make that point to a valid server and database where you ran the above DDL
Save and then right click on BimlScript.biml and select "Generate SSIS Packages". Whoosh Out comes dba_128344.dtsx

GIVE IT A TRY!!!

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="CM_OLE" ConnectionString="Data Source=localhost\dev2014;Initial Catalog=tempdb;Provider=SQLNCLI11.0;Integrated Security=SSPI;"/>
    </Connections>
    <Packages>
        <Package Name="dba_128344">
            <Tasks>
                <Dataflow Name="DFT Demo Trigger">
                    <Transformations>
                        <OleDbSource
                            ConnectionName="CM_OLE"
                            Name="OLESRC Demo Data">
                            <DirectInput><![CDATA[-- SourceQuery
SELECT
    D.Foo
,   D.Bar AS OldBar
,   CHAR(ASCII(D.Bar) + 1) AS NewBar
FROM
    dbo.dba_128344 AS D;
]]></DirectInput>
                        </OleDbSource>

                        <OleDbCommand
                            ConnectionName="CM_OLE"
                            Name="Trigger Tester">
                            <DirectInput><![CDATA[EXECUTE dbo.dba_128344Update ?, ?;]]></DirectInput>
                            <Parameters>
                                <Parameter SourceColumn="Foo" TargetColumn="@Foo" DataType="Int32" />
                                <Parameter SourceColumn="NewBar" TargetColumn="@bar" DataType="AnsiString" Length="1"/>
                            </Parameters>
                        </OleDbCommand>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>


How to Export MS SQL DB from Docker-Linux to Access format

Follow step by step guidance using Microsoft SQL Server Management tool.
Invoke context menu on your database Tasks -> Export Data..., where you can choose proper file name, encoding, delimiters.


Calculating the optimal size for the oracle undo tablepsace

You may use the calculation mentioned in this site:

https://taliphakanozturken.wordpress.com/tag/actual-undo-size/


Delete records in Peer to Peer Replication in SQL Server 2008 R2

Peer to Peer is transactional. So the log reader will re-generate the result of the delete, row by row based on the transaction log. The there will be 8+ million delete operations performed on each of the other nodes. And, yes, you only run the procedure on one node. But as you probably understand, having row-by-row as the end result is far from optimal.

An option is to include the procedure in your publication. This mean that SL server will, for you, execute that procedure on the other nodes (instead of sniffing the log).

Above is for transaction replication. I can't say how peer to peer changes things, if they do. Make sure to test thoroughly.

https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/publishing-stored-procedure-execution-in-transactional-replication?view=sql-server-2017


Is pgtune still valid for newer PostgreSQL versions?

There is an active fork of PgTune maintained here. That said, I don't know of anyone that uses it.


First, look at the EXPLAIN plan again.

The first line says that the Query Optimizer will do the following:

Full table scan of at least 7,706,280 rows without using any keys (thus the root cause of the Sending data query state)
Each row in passes through from esp_game will do a lookup to esp_gameplayer and gameTypeMap

I would like you to try the following:

Refactor Your Query

Your original query is:

SELECT
    g.gameID, g.gameType, g.mapID,
    g.createDate, gt.gameTypePortugueseDesc
FROM
    esp_game g
    INNER JOIN esp_gameplayer gp ON g.gameID = gp.gameID and gp.summonerID=401129
    INNER JOIN gameTypeMap gt ON g.gameType = gt.gameTypeID
ORDER BY g.createDate DESC LIMIT 10;

If the summonerID drives which gameIDs to retrieve, then the query needs to have esp_gameplayer be the lead table in the query

SELECT
    g.gameID, g.gameType, g.mapID,
    g.createDate, gt.gameTypePortugueseDesc
FROM
    (SELECT gameID FROM esp_gameplayer WHERE summonerID=401129) gp
    INNER JOIN esp_game g ON g.gameID = gp.gameID
    INNER JOIN gameTypeMap gt ON g.gameType = gt.gameTypeID
ORDER BY g.createDate DESC LIMIT 10;

Change the esp_gameplayer PRIMARY KEY

CREATE TABLE IF NOT EXISTS `esp_gameplayer` (
  `gameID` int(11) unsigned NOT NULL,
  `summonerID` int(11) unsigned NOT NULL,
  `championID` int(11) NOT NULL,
  `teamID` int(11) NOT NULL,
  `isUpdated` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`summonerID`,`gameID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

To reverse the PRIMARY KEY do the following:

CREATE TABLE IF NOT EXISTS `esp_gameplayer_new` (
  `gameID` int(11) unsigned NOT NULL,
  `summonerID` int(11) unsigned NOT NULL,
  `championID` int(11) NOT NULL,
  `teamID` int(11) NOT NULL,
  `isUpdated` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`summonerID`,`gameID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO esp_gameplayer_new SELECT * FROM esp_gameplayer;
ALTER TABLE esp_gameplayer RENAME esp_gameplayer_old;
ALTER TABLE esp_gameplayer_new RENAME esp_gameplayer;

Since you asked for a specific summonerID, reversing the PRIMARY KEY gathers all the corresponding gameIDs faster for summoner 401129

Make the changes, rerun the EXPLAIN plan. It should improve a great deal.

Give it a Try !!!


Sql server database synchronization on 2 different server

You can go in three ways:

Use replication. This is a large area of database engineering. I recommend you get started with types of replication.
Use log shipping. This is usually used to create an emergency database (hot backup). Pay attention: The secondary server will read-only.
The easiest option. Run backup on the primary server and restore this file on the secondary server. it is a good idea If the restore procedure takes a little time. There are paid cloud services like SqlBak and IBM Cloud Backup that will do it for you.


Differentiating tables and views in INFORMATION_SCHEMA.COLUMNS

You will need to do a left outer join to INFORMATION_SCHEMA.VIEWS to see if they are views or not.

select CASE WHEN b.TABLE_NAME is not null then 'view' else 'table' end OBJECT_TYPE,
    a.*
from INFORMATION_SCHEMA.COLUMNS a
LEFT OUTER JOIN INFORMATION_SCHEMA.VIEWS b ON a.TABLE_CATALOG = b.TABLE_CATALOG
    AND a.TABLE_SCHEMA = b.TABLE_SCHEMA
    AND a.TABLE_NAME = b.TABLE_NAME


ERROR: text search configuration name "english" must be schema-qualified

Double quotes are for case sensitive identifiers or one's that have whitespace in them. Single quotes are for strings.

Try this:

UPDATE "EMAIL" SET PostText = to_tsvector('english', 'TEXT');

You can also remove the quotes around "EMAIL" for a case insensitive match on the field name as well:

UPDATE email SET PostText = to_tsvector('english', 'TEXT');


PL/SQL block fails in job but succeeds in SQL*Plus

What happened is that I had all the permissions and privileges set right except I forgot to create the directory on the second node too.

So whenever the job ran on the second instance (which it by chance did) the error was raised that the file could not be written.

Of course it had to be as simple as that but I want to post it as an answer anyway.

When working with an Oracle RAC your mantra should always be "Keep in mind the other instances."


Extended events vs SQL Audit - performance implications

One benefit of the Audit that comes to mind is that it will automatically record who turns it on and off, XE won't do that out of the box (though you might find an event that tracks XE stop/start). You may also find that the two capture different data, depending on exactly what you want.

Regarding doing some testing, you would need to have a database backup, capture a trace of the application under load, and then restore the copy while doing a replay / replay with audit / replace with XE and comparing performance data.

Which performance data? It's up to you. For some ideas - Linchi Shea did a comparison between Audit and Trace by focusing on Transactions/sec, while Kehayias did a comparison between Trace and XE by focusing on batches/sec and overall replay runtime.

http://sqlblog.com/blogs/linchi_shea/archive/2012/01/24/performance-impact-sql2008-r2-audit-and-trace.aspx
http://sqlperformance.com/2012/10/sql-trace/observer-overhead-trace-extended-events

I'd encourage you to read both and their comments because you should know that no matter what you do it will be open to interpretation. It's difficult to get an apples for apples comparison. Also, a trace/replay can fail to simulate load properly - for example when your application is doing lots of bulk loads from disk files that no longer exist.

But the important thing is that you try at least one thing, so you can justify your decisions, and also blog about it for the rest of us.


Vacuuming database termination for postgreSQL

I am worried whether any database corruption will occur

No database corruption will occur due to an interrupted VACUUM, whether it was whole-database or just one table, and whether it was a an ordinary VACUUM or a VACUUM FULL. VACUUM is completely safe to abort at any time. At worst it'll leave some extra table and index bloat that a later VACUUM run (that completes) will clean up.

In fact, it's also crash-safe so long as the underlying storage subsystem respects fsync(), which is a basic requirement for PostgreSQL reliability. It wouldn't even matter if PostgreSQL crashed, or if the machine was reset suddenly. In that case PostgreSQL would just recover from transaction logs.


How to dump PostgreSQL database whose owner has no password?

See this part of the pg_dump manpage:

   -W, --password
       Force pg_dump to prompt for a password before connecting to a database.
       This option is never essential, since pg_dump will automatically prompt
       for a password if the server demands password authentication.

Don't use -W at all. In your case, it's just confusing.

Also, you need to know that the fact that the server asks for a password or not is not driven by the existence  of this password.

It's driven by the server-side pg_hba.conf file that you need to study and possibly modify according to your needs (don't forget to reload the server after modifying it).

EDIT: reviewing your pg_hba.conf. The relevant lines are:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

The 1st line concerns the postgres user. It's irrelevant for your pg_dump command since you're using the santa user with -U santa

The 2nd line concerns any other connection through Unix domain sockets (TYPE column is local). From the client, it means when you do not use -h localhost. It says that if the OS user is the same name than the db user, he doesn't need a password.

The 3rd line says that if -h localhost is used (IPv4 TCP connection), a password will always be asked to the client.  The 4th line says the same with IPv6.

Based on this, this command run by the santa OS user should not ask or need  a password:

 pg_dump --no-owner myapp_db > myapp_db_backup.sql

-U santa is optional because the db username is taken as the OS user by default.


What MySQL data types REQUIRE a max length (size) declaration in a create table statement?

MySQL Perspective

@DerickDowney discussed this in his post INT(5) vs SMALLINT(5). He quoted from the MySQL Docs:

  The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column display width from being displayed correctly. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range permitted by three digits are displayed in full using more than three digits.

Given this statement, if you create a table with a column type INT it will appear as INT(11). Why 11 ? INT has a range of -2147483648 and 2147483647. It takes 11 spaces to display the smallest signed INT -2147483648. Nothing more, nothing less.

Character data is entirely different story. Defining a VARCHAR requires the table header knowing the maximum length in advance. Rather than go into a lengthy explanation, please read the following posts:

Fine Tuning The Table Definition (Percona)
InnoDB Physical Row Structure (MySQL Documentation)

From the information in these posts, I would confidently say that you have no choice but to define a length for a VARCHAR. If you not sure, just define all VARCHARs as VARCHAR(255). No wasted is there. Alter loading data you could always run the following

SELECT varcharcolumnname FROM yourtable PROCEDURE ANALYSE();

and let it tell you the proper VARCHAR size.


First a sample table

mysql> drop database if exists ali;
Query OK, 1 row affected (0.10 sec)

mysql> create database ali;
Query OK, 1 row affected (0.00 sec)

mysql> use ali;
Database changed
mysql> CREATE TABLE test
    -> (
    -> id int(11) unsigned NOT NULL AUTO_INCREMENT,
    -> external_id int(11),
    -> number smallint(5),
    -> value varchar(255),
    -> UNIQUE INDEX (external_id, number),
    -> PRIMARY KEY(id)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.09 sec)

mysql>

Let's insert an initial row and SELECT it back

mysql> INSERT INTO test (external_id,number,value)
    -> VALUES (200,15,'Y')
    -> ON DUPLICATE KEY UPDATE number = number + 1;SELECT * FROM test;
Query OK, 1 row affected (0.06 sec)

+----+-------------+--------+-------+
| id | external_id | number | value |
+----+-------------+--------+-------+
|  1 |         200 |     15 | Y     |
+----+-------------+--------+-------+
1 row in set (0.00 sec)

mysql>

Let's insert the same thing again

mysql> INSERT INTO test (external_id,number,value)
    -> VALUES (200,15,'Y')
    -> ON DUPLICATE KEY UPDATE number = number + 1;SELECT * FROM test;
Query OK, 2 rows affected (0.10 sec)

+----+-------------+--------+-------+
| id | external_id | number | value |
+----+-------------+--------+-------+
|  1 |         200 |     16 | Y     |
+----+-------------+--------+-------+
1 row in set (0.00 sec)

mysql>

OK let's try the value column at the same time

mysql> INSERT INTO test (external_id,number,value)
    -> VALUES (201,15,'X')
    -> ON DUPLICATE KEY UPDATE
    -> number = number + 1,value = VALUES(value);
Query OK, 1 row affected (0.07 sec)

mysql> SELECT * FROM test;
+----+-------------+--------+-------+
| id | external_id | number | value |
+----+-------------+--------+-------+
|  1 |         200 |     16 | Y     |
|  5 |         201 |     15 | X     |
+----+-------------+--------+-------+
2 rows in set (0.00 sec)

mysql> INSERT INTO test (external_id,number,value)
    -> VALUES (201,15,'X')
    -> ON DUPLICATE KEY UPDATE
    -> number = number + 1,value = VALUES(value);
Query OK, 2 rows affected (0.06 sec)

mysql> SELECT * FROM test;
+----+-------------+--------+-------+
| id | external_id | number | value |
+----+-------------+--------+-------+
|  1 |         200 |     16 | Y     |
|  5 |         201 |     16 | X     |
+----+-------------+--------+-------+
2 rows in set (0.00 sec)

mysql>

This time change value and number

mysql> INSERT INTO test (external_id,number,value)
    -> VALUES (202,15,'Z')
    -> ON DUPLICATE KEY UPDATE
    -> number = number + 1,
    -> value = VALUES(value);
Query OK, 1 row affected (0.07 sec)

mysql> SELECT * FROM test;
+----+-------------+--------+-------+
| id | external_id | number | value |
+----+-------------+--------+-------+
|  1 |         200 |     16 | Y     |
|  5 |         201 |     16 | X     |
|  8 |         202 |     15 | Z     |
+----+-------------+--------+-------+
3 rows in set (0.00 sec)

mysql> INSERT INTO test (external_id,number,value)
    -> VALUES (202,15,'A')
    -> ON DUPLICATE KEY UPDATE
    -> number = number + 1,
    -> value = VALUES(value);
Query OK, 2 rows affected (0.07 sec)

mysql> SELECT * FROM test;
+----+-------------+--------+-------+
| id | external_id | number | value |
+----+-------------+--------+-------+
|  1 |         200 |     16 | Y     |
|  5 |         201 |     16 | X     |
|  8 |         202 |     16 | A     |
+----+-------------+--------+-------+
3 rows in set (0.00 sec)

mysql>

Hope this helps.

Give it a Try !!!


Combine two child tables based on parent

You may change your code to replace join with left join since you need all the rows from left table, code could be written as:
SELECT        Parent.*, Child_1.*, Child_2.*
FROM          Lead AS Parent
          LEFT JOIN
              Child_1 ON Parent.Child1_Id = Child1.Id
          LEFT JOIN
              Child_2 ON Parent.Child2_Id = Child_2.Id
;

Left Join - The LEFT JOIN clause allows you to query data from multiple tables. It returns all rows from the left table and the matching rows from the right table. If no matching rows found in the right table, NULL are used.


Join Multiple Derived Tables, Correctly in MySQL?

Regarding your FULL OUTER JOIN question (not working in MySQL):  you can probably achieve a result similar to that of a FULL OUTER join by coding a UNION of a LEFT and RIGHT join.  However, looking at your raw data and your "Desired Output", it may be better to use pivoting.

Consider the following - just for generating a test table that resembles your "data" table (using MySQL version 5.1):

create table user( userid int );
create table question( qid int, answer varchar(128));

insert into user values (0),(4),(5),(6),(7),(8),(9),(10);
insert into question (qid) values (44),(45),(46),(47),(48),(49);

create table qa
as select * from user, question;

-- Add some dummy answers to the table, leave some gaps.
update qa
set answer = concat(concat('answer', userid), qid)
where mod( (userid*qid), 2 ) = 0 ;

We get a table that looks like this: (middle section not displayed)

mysql> select * from qa;
+--------+------+------------+
| userid | qid  | answer     |
+--------+------+------------+
|      0 |   44 | answer044  |
|      0 |   45 | answer045  |
|      0 |   46 | answer046  |
|      0 |   47 | answer047  |
|      0 |   48 | answer048  |
|      0 |   49 | answer049  |
|      4 |   44 | answer444  |
|      4 |   45 | answer445  |
|      4 |   46 | answer446  |
 ...
|      8 |   49 | answer849  |
|      9 |   44 | answer944  |
|      9 |   45 | NULL       |
|      9 |   46 | answer946  |
|      9 |   47 | NULL       |
|      9 |   48 | answer948  |
|      9 |   49 | NULL       |
|     10 |   44 | answer1044 |
|     10 |   45 | answer1045 |
|     10 |   46 | answer1046 |
|     10 |   47 | answer1047 |
|     10 |   48 | answer1048 |
|     10 |   49 | answer1049 |
+--------+------+------------+
48 rows in set (0.00 sec)

If we now "extend" and "pivot" (see http://stratosprovatopoulos.com/web-development/mysql/pivot-a-table-in-mysql/ ) and use MySQL's group_concat() function ( see https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html ), we get a result that may be useful for you.

create view qa_extended as
select
  userid
, case when qid = 44 then answer end as q44
, case when qid = 45 then answer end as q45
, case when qid = 46 then answer end as q46
, case when qid = 47 then answer end as q47
, case when qid = 48 then answer end as q48
, case when qid = 49 then answer end as q49
from qa
;

create view qa_pivot as
select
  userid
, group_concat(q44) as q44
, group_concat(q45) as q45
, group_concat(q46) as q46
, group_concat(q47) as q47
, group_concat(q48) as q48
, group_concat(q49) as q49
from qa_extended
group by userid
;

Output of   select * from qa_pivot;

mysql> select * from qa_pivot;
+--------+------------+------------+------------+------------+------------+------------+
| userid | q44        | q45        | q46        | q47        | q48        | q49        |
+--------+------------+------------+------------+------------+------------+------------+
|      0 | answer044  | answer045  | answer046  | answer047  | answer048  | answer049  |
|      4 | answer444  | answer445  | answer446  | answer447  | answer448  | answer449  |
|      5 | answer544  | NULL       | answer546  | NULL       | answer548  | NULL       |
|      6 | answer644  | answer645  | answer646  | answer647  | answer648  | answer649  |
|      7 | answer744  | NULL       | answer746  | NULL       | answer748  | NULL       |
|      8 | answer844  | answer845  | answer846  | answer847  | answer848  | answer849  |
|      9 | answer944  | NULL       | answer946  | NULL       | answer948  | NULL       |
|     10 | answer1044 | answer1045 | answer1046 | answer1047 | answer1048 | answer1049 |
+--------+------------+------------+------------+------------+------------+------------+
8 rows in set (0.00 sec)


Script component source won't load when click "Edit Script..."

I stumbled across this blog post, which while the author didn't have the exact same problem as me, our issues seemed similar enough that his fix might work for me to. I downloaded the SSDT-BI 2012 package and installed it, and lo and behold, things work fine again. I'll just not even consider that somehow things worked fine for a year or more without downloading and installing SSDT-BI 2012, so I might be able to retain some portion of my sanity.


Does a filtered index improve searching on the opposite condition?

nope. You'll probably even get an error if you try to force your 'IS NULL' query to use your filtered index with an index hint.


Most likely the issue is with the first dynamic query, the one you are storing into @sql0:

set @sql0=CONCAT('select count(*) into ',@i,' from ',tab1);

You are concatenating the value of @i into the query, but @i has not been defined yet at this point – so it is null. Concatenating a null with other string literals gives you a null as well. So @sql0 ends up to be a null and that is what you are attempting to execute subsequently and getting the error mentioned.

My guess is you meant the @i to be part of the dynamic query, so it should probably go like this:

set @sql0=CONCAT('select count(*) into @i from ',tab1);

that is, you need to concatenate the name of @i, not the value. That way the dynamic query will be storing the result of count(*) into @i.

Note, though, that you may also need to declare the variable explicitly before the dynamic query. Otherwise the dynamic query will probably implicitly declare the variable at its own, nested, level, and the variable will go out of scope once the query is completed, and so the @i reference further in your query will still evaluate to null.


SELECT in trigger function in two tables

You have an unresolved naming conflict.

You must be using an old version of Postgres without declaring it. Or you are operating with non-default configuration setting.

Here you declare a variable named measurement_id:

DECLARE
    measurement_id              INTEGER;

It's a folly to use ambiguous variable names to begin with. If you do it anyway, you must know what you are doing. I make it a habbit to prepend variable names with an underscore unlike column names, like _measurement_id.

The later SELECT statement is ambiguous:

ORDER BY measurement_id

This would raise an error message in modern PostgreSQL with default configuration. Per the documentation:

  By default, PL/pgSQL will report an error if a name in a SQL statement
  could refer to either a variable or a table column.

And:

  To change this behavior on a system-wide basis, set the configuration
  parameter plpgsql.variable_conflict to one of error, use_variable, or
  use_column (where error is the factory default). This parameter
  affects subsequent compilations of statements in PL/pgSQL functions,
  but not statements already compiled in the current session. Because
  changing this setting can cause unexpected changes in the behavior of
  PL/pgSQL functions, it can only be changed by a superuser.

In Postgres older than 9.0 this would be resolved to mean the variable. Per the documentation

  In such cases you can specify that PL/pgSQL should resolve ambiguous
  references as the variable (which is compatible with PL/pgSQL's
  behavior before PostgreSQL 9.0)

Bold emphasis mine.
This would result in arbitrary results, since the sort order is now undetermined.

Audited Function

CREATE OR REPLACE FUNCTION insaft_function()
   RETURNS TRIGGER AS
$func$
DECLARE
   _measurement_id          integer;
   _file_header_index_start integer := TG_ARGV[0]::int;
   _file_header_index_end   integer := TG_ARGV[1]::int;
BEGIN

   SELECT a.measurement_id   INTO _measurement_id
   FROM   measurements a
   ORDER  BY a.measurement_id DESC  -- you had ambiguity here!
   LIMIT  1;

   IF TG_OP = 'INSERT' THEN  -- noise if only used in AFTER INSERT trigger
      INSERT INTO file_headers (measurement_id, file_header_index_start
                                              , file_header_index_end)
      VALUES (_measurement_id, _file_header_index_start, _file_header_index_end);
   END IF;

   RETURN NULL; -- result is ignored since this is an AFTER trigger
END
$func$ LANGUAGE plpgsql;

Note how I named it insaft_function(), since this is only to be used in an AFTER INSERT trigger.

Trigger:

CREATE TRIGGER insaft_measurement_ids
AFTER INSERT ON measurements
FOR EACH ROW EXECUTE PROCEDURE insaft_function(1, 666);

But for the provided setup, you can radically simplify the function:

CREATE OR REPLACE FUNCTION insaft_function()
   RETURNS TRIGGER AS
$func$
BEGIN
   INSERT INTO file_headers (measurement_id, file_header_index_start
                                           , file_header_index_end)
   VALUES (NEW.measurement_id, TG_ARGV[0]::int, TG_ARGV[1]::int);

   RETURN NULL;  -- result ignored since this is an AFTER trigger
END
$func$ LANGUAGE plpgsql;


How to make the SQL query more efficient?

Some notes:

No need for the derived tables. Most, if not all MySQL versions will try to materialize the subresults of the derived tables and the subsequent join operations will not be able to use any indexes. Check the EXPLAIN output to verify this. Moreover, if the resulting tables do not fit in memory, they will be saved temporarily into disk, causing more delay.
I think that two indexes, on (activity_type, advertiser_id, activity_date) and on (advertiser_id, activity_date, activity_type) will be more useful than those you have. The first will be used for the bid_change and the second for joining the post_funnel.
If the message column is not some huge varchar or text datatype, including it in the 1st (only) above index will further improve efficiency.
I strongly suggest you don't use BETWEEN for date and datetime comparisons but inclusive-exclusive ranges (qt >= start_datetime AND qt < end_datetime).
(detail) No need to use CASE bid_change.activity_type WHEN 11 in the SELECT when you have WHERE bid_change.activity_type = 11 in the query.
(detail) You can rewrite the other CASE using either COUNT() instead of SUM() or using the SUM(condition) mysql trick which makes them a bit simpler:

In all, the query can be rewritten:

SELECT
    bid_change.advertiser_id,
    bid_change.activity_date,
    IF(bid_change.message LIKE '%Raised%', 1, -1) AS bid,
    SUM(change_type.activity_type = 11)           AS update_ad_bid,
    SUM(change_type.activity_type = 12)           AS update_ad_budget,
    SUM(change_type.activity_type = 14)           AS creative
FROM
    post_funnel AS bid_change
  LEFT JOIN
    post_funnel AS change_type
      ON  change_type.advertiser_id = bid_change.advertiser_id
      AND change_type.activity_date >= bid_change.activity_date - INTERVAL 1 DAY
      AND change_type.activity_date < bid_change.activity_date
WHERE
    bid_change.activity_type = 11
GROUP BY
    bid_change.advertiser_id,
    bid_change.activity_date ;


I have tried to recreate your scenario on a test system using a simple stored procedure. The way I have approached this problem is as follows:

Create a 'StartBackgroundProcess' stored procedure that starts a job named 'BackgroundProcess'.
Create a test stored procedure to be called by the 'BackgroundProcess' job.
Create a 'BackgroundProcess' job that continuously loops, checking for jobs completed and then executes the 'TestSP' stored procedure.
Configure the 'StartBackgroundProcess' stored procedure to be called whenever SQL Server starts.

You can configure the BackgroundProcess to run at any interval within the code and it will check for completed jobs since the last run (ex. check every 30 seconds, 5 minutes, etc.).  It then fires a test stored procedure that you can replace with whatever one you were wanting to use.

/*
1. Create a 'StartBackgroundProcess' stored procedure that starts a job named 'BackgroundProcess'.
2. Create a test stored procedure to be called by the 'BackgroundProcess' job.
3. Create a 'BackgroundProcess' job that continuously loops, checking for jobs completed and then executing the 'TestSP' stored procedure.
4. Configure the 'StartBackgroundProcess' stored procedure to be called whenever SQL Server starts.
*/

-- 1. Create the 'StartBackgroundProcess' stored procedure that starts a job named 'BackgroundProcess'.

USE master
GO

IF EXISTS (SELECT * FROM master.dbo.sysobjects o WHERE o.xtype IN ('P') AND o.id = object_id('master.dbo.StartBackgroundProcess'))
DROP PROC StartBackgroundProcess
GO

CREATE PROCEDURE dbo.StartBackgroundProcess
AS
BEGIN
    EXEC msdb.dbo.sp_start_job 'BackgroundProcess'
END
GO

-- 2. Create a test stored procedure to be called by the 'BackgroundProcess' job.

USE Tempdb
GO

CREATE PROCEDURE [dbo].[TestSP]
AS
BEGIN
    SELECT 'Running Test', GETDATE()
END
GO

-- 3. Create the 'BackgroundProcess' job that continuously loops, checking for jobs completed and then executing a specified stored procedure.
-- ACTION REQUIRED: Copy/Paste the commented SQL below (up until Step 3) into a new job named 'BackgroundProcess' as a SQL Step and Save. No need to configure a schedule.
-- ACTION REQUIRED: Remove the beginning/ending comment tags from the job!
/*
USE master
GO

DECLARE @whilecounter AS INT
DECLARE @i AS INT
DECLARE @ScriptToRun varchar(250)
DECLARE @LastRun datetime
DECLARE @StatementToRun VARCHAR(5000)

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects o WHERE o.xtype IN ('U') AND o.id = object_id('Tempdb.dbo.BackgroundProcess'))
DROP TABLE Tempdb.dbo.BackgroundProcess

CREATE TABLE Tempdb.dbo.BackgroundProcess
(
    CommandRun VARCHAR(100),
    TimeExecuted DATETIME
)

DECLARE @TimeMarker DATETIME

DECLARE @cmdQueue TABLE
(
    cmd VARCHAR(5000) NOT NULL
)

DECLARE @Runcommand TABLE
(
    cmd VARCHAR(5000) NOT NULL
)

SET @TimeMarker = (SELECT start_execution_date FROM msdb.dbo.sysjobactivity sja INNER JOIN msdb.dbo.sysjobs sj on sja.job_id = sj.job_id WHERE sj.enabled = 1 AND start_execution_date IS NOT NULL AND name = 'BackgroundProcess')

WHILE 1=1
BEGIN
        INSERT @cmdQueue
            SELECT 'EXEC ' + 'TempDB.dbo.TestSP' + ' ' + name + ', ' + @@SERVERNAME FROM msdb.dbo.sysjobactivity sja INNER JOIN msdb.dbo.sysjobs sj on sja.job_id = sj.job_id WHERE sj.enabled = 1 AND start_execution_date IS NOT NULL AND stop_execution_date >= @TimeMarker
        SET @whilecounter = (SELECT COUNT(*) FROM @cmdQueue)
        SET @i = 1
        WHILE @i <= @whilecounter
        BEGIN
            DELETE TOP(1) FROM @cmdQueue
            OUTPUT DELETED.cmd INTO @RunCommand
            INSERT Tempdb.dbo.BackgroundProcess
                SELECT cmd, GETDATE() FROM @RunCommand
            SET @StatementToRun = (SELECT TOP(1) cmd FROM @RunCommand)
            EXEC (@StatementToRun)
            DELETE FROM @RunCommand
            SET @i = @i + 1
        END
SET @TimeMarker = GETDATE()
WAITFOR DELAY '000:0:30.000' --Configure this to be the proper frequency for your situation. Ensure it is runs often enough to catch all executions of other jobs.
END
*/

-- 4. Configure the 'StartBackgroundProcess' stored procedure to be called whenever SQL Server starts.

USE master
GO

EXEC sp_procoption 'master.dbo.StartBackgroundProcess', 'startup', 'true'
GO


Reviewing all new MySql queries on staging environment

These are some tools suggested by MySQL Performance blog post:
http://www.mysqlperformanceblog.com/tools/
pt-query-digest can be useful to review queries as well
It can review a slow log, saving results to the test.query_review table in a MySQL server running on host1. See --review for more on reviewing queries:

pt-query-digest --review h=host1,D=test,t=query_review /path/to/slow.log
Using Show Profiles also you can review query performance

http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html

SET PROFILING = 1;
SET profiling_history_size = 0;
SET profiling_history_size = 15;
<Your own query>;
SHOW PROFILES;
select state, round(sum(duration),5) as `duration (summed) in sec` from information_schema.profiling where query_id = 11 group by state order by `duration (summed) in sec` desc;
SET PROFILING = 0;

EXPLAIN EXTENDED [Your own query];

When EXPLAIN is used with the EXTENDED keyword, the output includes a filtered column not otherwise displayed. This column indicates the estimated percentage of table rows that will be filtered by the table condition. In addition, the statement produces extra information that can be viewed by issuing a SHOW WARNINGS statement following the EXPLAIN statement. The Message value in SHOW WARNINGS output displays how the optimizer qualifies table and column names in the SELECT statement, what the SELECT looks like after the application of rewriting and optimization rules, and possibly other notes about the optimization process.


Creating Excel files in SSIS based with a predefined format

If your SSIS package runs with the appropriate file system permissions, you can create a template Excel file with the desired formatting and copy that file to a new one (using a file system or script task) where the data will be exported. If the sheet will contain a variable number of cells that must be formatted, you can use conditional formatting in the template and have Excel apply the format based on actual column values or a formatting flag, say in a hidden column, which could be populated from the data.

See this article for an example of using a template. This article may also be helpful; it mentions an ingenious way to update specific cells.