How to merge data sets without including redundant rows? I think this must be a fairly common thing to do, but I haven't been able to find an example. It involves merging data that involves dates/times (or more generally any sequential values) without including redundant records. By redundant I mean records that can be implied by other records.

E.g. if the price yesterday was $1, and the price today is $1, and there were no other price changes between yesterday and today, then the price today can be inferred from the price yesterday without storing a price for both days.

Here's the scenario I'm trying to solve. We have a table of historical prices for items. New prices are imported periodically into the table. The import file can contain dates from any time, we don't know that they are always "after" the data in the history table.

To avoid data bloat I only want to insert records if they give new information. So if a new record can be inferred from an old record then I don't want to insert the new record, and if an old record can be inferred from a new record then I want to remove the old record and insert the new record.

Some concrete examples might help, here are the two problem scenarios:

An incoming record can be inferred from an existing record, so the incoming record is redundant.
E.g.

old record: 2013-04-23 1.00

new record: 2013-04-24 1.00 <-- this is implied by the existing record, don't insert it
An existing record can be inferred from a new record, so the existing record is redundant.
E.g.

new record: 2013-04-23 1.00

old record: 2013-04-24 1.00 <-- this is implied by the new record, delete it
This just shows an example of when a value is not redundant.
E.g.

old record: 2013-04-23 1.00

old record: 2013-04-24 1.20

new record: 2013-04-25 1.00 <-- not redundant, the price changed since it was last 1.00

There is a more detailed example here http://sqlfiddle.com/#!3/2ef87/2

Currently I'm leaning towards a multi-step approach of:

Delete from incoming where there is an existing record that has the same price with an earlier date (scenario 1 above).
Delete from existing where there is an incoming record that has the same price with an earlier date (scenario 2 above).
Insert the remaining incoming records.

There must be a nicer way, maybe using MERGE, but it's doing my head in trying to work out how to do it.

How do I efficiently "merge" the existing and incoming records?
Thanks

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
╚═════════════════════════╩═════════╩══════╩═══════╝


`show procedure status` doesn't list all of the procedures? mysql-server-5.5.28-1.el5.remi

I'm pulling my hair with this problem: only EMS SQL Manager can list all of my store procedures (1763). mysql client, mysql-workbench, Navicat, ... only see some of them.

mysql> show procedure status where db='reportingdb';

...
| reportingdb | 7k_cronjob_log                             | PROCEDURE | user@host | 2012-10-17 10:10:55 | 2012-10-17 10:10:55 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
+-------------+--------------------------------------------+-----------+----------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
146 rows in set (0.05 sec)

As you can see 7k_cronjob_log is the last procedure and there is only 146 rows. But let's see what happens:

mysql> show create procedure reportingdb.warningserver_getmaxtime_v2\G
*************************** 1. row ***************************
           Procedure: warningserver_getmaxtime_v2
            sql_mode: NO_AUTO_VALUE_ON_ZERO
    Create Procedure: CREATE DEFINER=`user`@`host` PROCEDURE `warningserver_getmaxtime_v2`()
    READS SQL DATA
    DETERMINISTIC
BEGIN
    SELECT `LastTimeCalc` FROM `000_sys_params_v3`;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

Any thoughts?

Only thing I can think of is that your mysql.proc database might be corrupt. Try with:

ANALYZE TABLE mysql.proc;

If corrupt, you can try to repair it:

REPAIR TABLE mysql.proc;


Database schema design help needed I am developing a PHP application expecting millions of records both parent and children. My goal is to design an optimized database design to achieve high speed and performance.

This application will have 1M users, each user will have their own dashboard where they can create their own users/roles, pages and posts.

I am thinking about two possible solutions

Use only 1 table for all users, pages and posts and they will have a foreign key of the owner
Use separate tables for all users, like user1_pages, user1_posts, user1_users

I Think with #1 the query will be slow as all users will be sharing one table, second one also don't seems a perfect solution as number of tables will increase exponentially.

Please suggest me a best possible solution and share tips to design an optimized MySQL schema.

You should use one table per object of interest.  That means one table for users, one table for pages, one table for posts, etc.  Use a normalized database (See database normalization) for transactional data.  This is precisely what relational database management systems are built to do.  Don't presume you are going to have performance problems because you have many rows.  Most systems perform better with many rows than with just a few.


Column name naming conventions and best practices I would like some expert opinion on best practices when it comes to column naming.

The background is that according to Wikipedia, the following syntax,

SELECT ... FROM Employees JOIN Timesheets USING (EmployeeID);

is more efficient than

SELECT ... FROM Employees JOIN Timesheets ON (Employees.EmployeeID = Timesheets.EmployeeID);

However, the JOIN ... USING syntax only works of all primary key columns have globally unique names. Thus I wonder if this is considered The Right Thing to do.

Personally, I always used to create tables with PK column id, and foreign key column othertable_id. But that way it's not possible to use USING or NATURAL JOIN.

Any links to design styles or best practice guides for table design would be appreciated, too!

This has been asked before on SO.

Where you have common and very ambiguous names, then prefix with table name. That is, anything you're liable to have to alias in almost every query.

So for an Employee table I'd have

EmployeeID
EmployeeName
Comment
Salary
StartDate
EndDate
InsertedDateTime
...

And Wikipedia actually says:

  The USING construct is more than mere syntactic sugar, however, since the result set differs from the result set of the version with the explicit predicate. Specifically, any columns mentioned in the USING list will appear only once, with an unqualified name, rather than once for each table in the join.

That is one less column. You'd never use SELECT * anyway so the point is moot...


Copying View in Management Studio I want to copy a VIEW from one server to another. I could copy tables but I can't copy views. Is there a way to do so?
Thanks

The easiest way if you just want to do a quick CREATE VIEW on your destination server would be to script it out:

Right-click your view
Script View as -> CREATE to -> (select your choice here)

The possible choices as a destination for the CREATE VIEW script can be a new query window, a file, the clipboard, or an agent job.  If you need to run this create script at a later date, the file destination is probably your best bet.  But if you want to run it immediately, and you have access to the server, just script it to a new query window and connect to your destination instance/database and run the CREATE VIEW script.


Merge replication throws PRIMARY KEY violation during the initialization of Subscriber I am using SQL Server 2014 SP1 to set up merge replication on Windows 2008. The databases (Publisher & Subscriber) are restored from the production back up of SQL Server 2005 publisher database.

The merge replication set up on SQL Server 2005 works fine. However when I set up the merge replication on SQL server 2014, I am getting the following error.

If I add new tables, there replication seems working fine. The issue is occurring only for the existing tables with production data.

Error messages:

  The merge process was unable to deliver the snapshot to the
  Subscriber. If using Web synchronization, the merge process may have
  been unable to create or write to the message file. When
  troubleshooting, restart the synchronization with verbose history
  logging and specify an output file to which to write. (Source:
  MSSQL_REPL, Error number: MSSQL_REPL-2147201001)

  The process could not bulk copy into table '"dbo"."FiscalVisitType"'.
  (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)

  Batch send failed (Source: MSSQLServer, Error number: 0)

  Violation of PRIMARY KEY constraint 'PK_FiscalVisitType'. Cannot
  insert duplicate key in object 'dbo.FiscalVisitType'. The duplicate
  key value is (1). (Source: MSSQLServer, Error number: 2627)

  To obtain an error file with details on the errors encountered when
  initializing the subscribing table, execute the bcp command that
  appears below. Consult the BOL for more information on the bcp utility
  and its supported options. (Source: MSSQLServer, Error number: 20253)

  bcp "SIPG_Subscriber"."dbo"."FiscalVisitType" in "C:\Program
  Files\Microsoft SQL
  Server\MSSQL12.OGIS\MSSQL\ReplData\unc\PC39606-ORG-D-P$OGIS_SIPG_PUBLISHER_OGIS_PUBLICATION\20150624142806\FiscalVisitType_2.bcp" -e "errorfile" -t"" -r"" -m10000 -SPC39606-ORG-D-P\OGIS -T -w (Source: MSSQLServer, Error number: 20253)

Have seen this on numerous occasions.  Try these steps to resolve your issue.

Remove table from replication
Reinitialize subscription
Add table to replication
Reinitialize subscription


Unable to start mysql I am using Oracle LINUX5. I installed MySQL 5.1.73 community edition through RPM command and it was working. Now, I stop mysql by running below command:

$ mysqladmin -u root -ptmpassword shutdown

I did not get any message after executing the above command. When I run below command to start the service:

$ /etc/rc.d/init.d/mysql start

I got below message:
Starting MySQL.Manager of pid-file quit without updating fi[FAILED]

Now, I am unable to connect MySQL database.

How can I start MySQL again?
What is the reason behind not starting the service?

I found my answer and I want to laugh on myself... What a silly mistake I did!
Sharing this solution so that others can save their time.
I just prefix sudo command in the below statement and mysql started.

$ sudo /etc/rc.d/init.d/mysql start

Input password:
Starting MySQL.
I checked and found that MySQL is running by issuing below command:

$ mysqladmin -u root -pjoomla1 ping

mysqld is alive


Indexed columns in SQL Server do not appear to work for basic queries according to execution plan Disclaimer: I'm not a DBA. I have picked up a few things from this board in the past that I'm building from.
I have a table of google analytics session start times. I have an index on each column. I want to filter for all sessions that were started between two dates. Screenshot below shows the query, and the index.

The query runs quickly but I do not believe it's using the index based on the Execution plan which both says that there's a missing index and shows a table scan rather than an index scan:

Why?
Is it because of something about the way I'm searching through the datetime? If instead of looking between dates, I set it equal to a date, the execution plan shows it using the index:

But it's not just this table or datetime. Here's a different table with an index on a varchar column:

And a simple query on this one also tells me I'm missing the index:

I'm stumped.

If you want SQL Server to use that index for that specific query, you need to include the session_id column.  Otherwise, for each row it finds it will have to go do a key lookup into the base table.  It will choose to do this for small result sets, but once you get above a certain number of rows ("the tipping point"), SQL Server thinks it's more efficient to just scan the whole base table.
You can use the INCLUDE clause of the CREATE INDEX syntax to accomplish this:
CREATE NONCLUSTERED INDEX IX_Start_Time
ON GoogleAnalytics.SessionStartTimes (session_start_time)
INCLUDE (session_id);


SQL Server 2008: Row count for each hour in a Day and Row count for each day in a Month I am trying to get row count in a given week for each day as below, which works fine

SELECT
    COUNT(CASE WHEN DATENAME(dw, [created_at]) = 'Sunday' THEN 1 END) AS S,
    COUNT(CASE WHEN DATENAME(dw, [created_at]) = 'Monday' THEN 1 END) AS M,
    COUNT(CASE WHEN DATENAME(dw, [created_at]) = 'Tuesday' THEN 1 END) AS T,
    COUNT(CASE WHEN DATENAME(dw, [created_at]) = 'Wednesday' THEN 1 END) AS W,
    COUNT(CASE WHEN DATENAME(dw, [created_at]) = 'Thursday' THEN 1 END) AS R,
    COUNT(CASE WHEN DATENAME(dw, [created_at]) = 'Friday' THEN 1 END) AS F,
    COUNT(CASE WHEN DATENAME(dw, [created_at]) = 'Saturday' THEN 1 END) AS S,
    COUNT(*) AS TOTAL
FROM PARCEL
WHERE [created_at] BETWEEN '2017-03-26 00:00:00'/*Sunday, report start date*/ AND '2017-04-10 23:59:59'/* Saturday, report end date */

I am also trying to get row count for each hour in a given day as below

Hour1 Hour2 Hour3 Hour4 .............Hour24  Total
 33    544   55     77                 23    13444

Also row count for each day in a given month as below

 Day1  Day2  Day3   Day4 .............Day31  Total
 33    544   55     77                 23    13444

I am pretty new to sql programming, am not sure where to start on this for tha lateral 2 reports, can someone please help me with this?

You want to look into using a PIVOT table.  You can use the same solution for your hour as well as the day of week or even day in month just change a few things in the query.

Here is an example for you that gives you the Sunday, Monday, etc. table that you first asked for.  It should get you started.

;WITH CTE_Parcel AS
    (
    SELECT created_at
        , 1 AS ItemCount
        , DATENAME(WEEKDAY, created_at) AS DayOfOrder
        , DATEPART(HOUR, created_at) AS HourOfOrder
        , SundayOfWeek = DATEADD(DAY , 7-DATEPART(WEEKDAY,created_at),created_at)
    FROM PARCEL
    WHERE created_at BETWEEN '2017-03-26 00:00:00'/*Sunday, report start date*/ AND '2017-04-10 23:59:59'/* Saturday, report end date */
    )
SELECT SundayOfWeek
    , PT.Sunday
    , PT.Monday
    , PT.Tuesday
    , PT.Wednesday
    , PT.Thursday
    , PT.Friday
    , PT.Saturday
    , WeekTotal = PT.Sunday + PT.Monday + PT.Tuesday + PT.Wednesday + PT.Thursday + PT.Friday + PT.Saturday
FROM (SELECT ItemCount, DayOfOrder, HourOfOrder, SundayOfWeek FROM CTE_Parcel) AS P
    PIVOT (COUNT(ItemCount) FOR DayOfOrder IN ([Sunday], [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday])) AS PT
ORDER BY PT.SundayOfWeek


Update one column based on count of records of grouped value in another column Given a table

CREATE TABLE TABLE_ITEMS (
    serial_num int null,
    item_group int null
 );

INSERT INTO TABLE_ITEMS
VALUES
 (0,1), (1,1), (2,1), (3,1), (4,1),
 (0,2), (1,2), (2,2), (3,2), (4,2), (5,2),
 (0,3), (1,3), (2,3);

and so on...

I need to know how to run a query which will update the rows with 0 as the serial_num to be the next number in the serial_num sequence for the given item_group

The result will look like this:

   those were the zeroes
   ↓
(**5**,1) (1,1) (2,1) (3,1) (4,1)
(**6**,2) (1,2) (2,2) (3,2) (4,2) (5,2)
(**3**,3) (1,3) (2,3)

I want to update an item in serial_num=0 to be the last serial_num plus 1 (if the last one was 70, it needs to be 71).

How can I do it?

CREATE TABLE TABLE_ITEMS (
    serial_num int null,
    item_group int null
 )
 GO

INSERT INTO TABLE_ITEMS
VALUES
(0,1), (1,1), (2,1),(3,1) ,(4,1) ,(0,2) ,(1,2), (2,2), (3,2), (4,2), (5,2), (0,3) ,(1,3) ,(2,3)
GO

SELECT
    Item_group,
    ROW_NUMBER() OVER (PARTITION BY item_group order by item_group) AS AddNum
INTO #NewSerials
from TABLE_ITEMS
WHERE serial_num <> 0

SELECT * FROM TABLE_ITEMS

;WITH CTE AS
(SELECT MAX(addnum) as addnum, item_group from #NewSerials group by item_group)
UPDATE ti
    SET ti.serial_num = CTE.AddNum
FROM TABLE_ITEMS ti
JOIN CTE ON CTE.item_group = ti.item_group
WHERE ti.serial_num = 0

SELECT * FROM TABLE_ITEMS

5   1
1   1
2   1
3   1
4   1
6   2
1   2
2   2
3   2
4   2
5   2
3   3
1   3
2   3


SQL Server DB Restore Unable to browse directory I have a problem with SQL Server 2008.
When I try to restore a backup (located in the default location). This error appears.

  Cannot access the specified path of file on the server. Verify that you have the necessary security privileges and tha the path or file exists.

I've tried to google about this error but nothing has been resolved, I've also tried to assign the folder Full Access to Everyone and the error still appears.

The SQL Server services (Engine and Agent) are running under the NetworkService system account.

However in the folder browser window I can browse through the C:\ drive

Edit:
The server is standalone, I have only one with Windows Server 2008 x86 as a domain controller.

First thing you need to check is that:

Has the SQL Server service account been added as a member of any local
groups on EACH server? Does the group in which it resides have read-write
permissions?

So please check if you're NW service account is admin on the box, along with R/W permissions for it to access the folder for backups.

Also, in case you are on cluster,

then you need to make sure and check for the dependency of that drive when in cluster for the service account its running under!


What is the correct/fastest way to negate @> in Postgres? I have a text[] column in Postgres 9.5
I am planning on running a lot of queries with @> against it
I have a GIN index on this column

Normally you need to use the correct Postgres operator so that an index is consulted for your query. There is no operator that is the "opposite" of  @> as far as I know.

I ran EXPLAIN ANALYZE on a query for this table and it takes 8 times as long to run WHERE NOT column @> array['thing'] as it does to run  WHERE column @> array['thing']. The planner reports that it does a filter for WHERE NOT instead of a bitmap index scan on my index.

What is the correct way to negate @> so that my index is consulted?

I'm willing to create new indexes for this column, including functional indexes if that's the answer.

Here are depesz links:

https://explain.depesz.com/s/uXOT
https://explain.depesz.com/s/nc7Q
https://explain.depesz.com/s/wC9m
https://explain.depesz.com/s/Eat

I don't think there is any highly effective way to index generic versions of that expression.  If you find a way to do so, I think you will be famous (well, famous within database circles, anyway).

If it is always exactly NOT column @> array['thing'] where 'thing' does not change, you could build special-purpose indexes, like

create index on foo (("column" @> array['thing']));

(or a partial index version of that)

Once 9.6 is released, you will be able to do the seq scan in parallel, and so get a boost that way.


This is an example of where Analytic functions can shine! In this case, you have a choice between the FIRST_VALUE() function (also its "mirror image" - LAST_VALUE()) and the ROW_NUMBER() function! This thread gave me the idea for using FIRST/LAST_VALUE to emulate  PostgreSQL's DISTINCT ON. See the fiddle here!

Create and populate the table:

CREATE TABLE my_tab
(
  title VARCHAR (25) NOT NULL,
  start_time TIMESTAMP NOT NULL,
  end_time   TIMESTAMP NOT NULL,
  location    VARCHAR (10) NOT NULL,
  CONSTRAINT my_tab_pk PRIMARY KEY (title, start_time, end_time, location),
  CONSTRAINT my_tab_st_lt_et_ck CHECK (start_time < end_time)
);

populate (sample):

INSERT INTO my_tab VALUES
(
  'Title - abc',
  TO_DATE('2020-03-18 10:00:00', 'yyyy-mm-dd hh24:mi:ss'),
  TO_DATE('2020-03-18 10:30:00', 'yyyy-mm-dd hh24:mi:ss'),
  'Loc1'
);

You have to put in a date as well as a time - Oracle doesn't have a TIME datatype - see here. This thread also proved helpful for final formatting of the answer using the TO_CHAR function.

And then (to check), run:

SELECT * FROM my_tab;
ORDER BY location, start_time, end_time, title;

Result:

TITLE           START_TIME                      END_TIME                      LOCATION
Title - abc     18-MAR-20 10.00.00.000000 AM    18-MAR-20 10.30.00.000000 AM    Loc1
Title - def     18-MAR-20 10.00.00.000000 AM    18-MAR-20 10.30.00.000000 AM    Loc1
Title - abc     18-MAR-20 11.00.00.000000 AM    18-MAR-20 11.30.00.000000 AM    Loc1
Title - def     18-MAR-20 11.00.00.000000 AM    18-MAR-20 11.30.00.000000 AM    Loc1
Title - ghi     18-MAR-20 12.00.00.000000 PM    18-MAR-20 12.30.00.000000 PM    Loc1
Title - jkl     18-MAR-20 01.00.00.000000 PM    18-MAR-20 01.30.00.000000 PM    Loc1
Title - mno     18-MAR-20 02.00.00.000000 PM    18-MAR-20 02.30.00.000000 PM    Loc1

We can see that abc and def overlap at 10:00 and 11:00.

FIRST_VALUE()

In the fiddle there is a first attempt which shows my train of thought - not included here.

The query:

SELECT
  DISTINCT
  FIRST_VALUE (t.title) OVER (PARTITION BY t.start_time ORDER BY t.start_time ASC) AS title,
  TO_CHAR(t.start_time, 'HH24:MI') AS "start",  -- be careful, both "start" and "end"
  TO_CHAR(t.end_time,   'HH24:MI') AS "end",     -- SQL keywords - fails if not quoted!
  t.location
FROM my_tab t
-- can have ORDER BY 2, 1, but I think actual names are more legible!
-- Again, the SQL keyword "start" must be quoted!
ORDER BY "start", title;

Result:

TITLE           start     end   LOCATION
Title - abc     10:00   10:30       Loc1
Title - abc     11:00   11:30       Loc1
Title - ghi     12:00   12:30       Loc1
Title - jkl     13:00   13:30       Loc1
Title - mno     14:00   14:30       Loc1

Et voilà - the desired result. If you want to eliminate abc instead of removing abc, you can use the LAST_VALUE() function or change the ORDER BY in the OVER() clause of the query (shown in the fiddle - not included here).

ROW_NUMBER()

I'll run the inner query of my final query first so that my train of thought can be easily followed:

SELECT
  m.title,
  m.start_time,
  m.end_time,
  m.location,
  ROW_NUMBER() OVER (PARTITION BY m.location, m.start_time ORDER BY m.title, m.start_time, m.location) AS rn
FROM my_tab m;

Result:

TITLE           START_TIME                      END_TIME                     LOCATION  RN
Title - abc     18-MAR-20 10.00.00.000000 AM    18-MAR-20 10.30.00.000000 AM    Loc1    1
Title - def     18-MAR-20 10.00.00.000000 AM    18-MAR-20 10.30.00.000000 AM    Loc1    2
Title - abc     18-MAR-20 11.00.00.000000 AM    18-MAR-20 11.30.00.000000 AM    Loc1    1
Title - def     18-MAR-20 11.00.00.000000 AM    18-MAR-20 11.30.00.000000 AM    Loc1    2
Title - ghi     18-MAR-20 12.00.00.000000 PM    18-MAR-20 12.30.00.000000 PM    Loc1    1
Title - jkl     18-MAR-20 01.00.00.000000 PM    18-MAR-20 01.30.00.000000 PM    Loc1    1
Title - mno     18-MAR-20 02.00.00.000000 PM    18-MAR-20 02.30.00.000000 PM    Loc1    1

We can see that we want to hold on to the records where rn = 1, so we wrap this in another query (since we can't put Analytic functions in WHERE clauses).

So, we run the following:

SELECT
  t.title,
  TO_CHAR(t.start_time, 'HH24:MI') AS "start",  -- be careful, both "start" and end are
  TO_CHAR(t.end_time,   'HH24:MI') AS "end",    -- SQL keywords - fails if not quoted!
  t.location
FROM
(
  SELECT
    m.title,
    m.start_time,
    m.end_time,
    m.location,
    ROW_NUMBER() OVER (PARTITION BY m.location, m.start_time ORDER BY m.title, m.start_time, m.location) AS rn
  FROM my_tab m
) t
WHERE rn = 1
ORDER BY t.title, t.location, t.start_time;

Result:

      TITLE     start     end  LOCATION
Title - abc     10:00   10:30      Loc1
Title - abc     11:00   11:30      Loc1
Title - ghi     12:00   12:30      Loc1
Title - jkl     13:00   13:30      Loc1
Title - mno     14:00   14:30      Loc1

Et voilà encore - the desired result again. If you have a preference for one title over another, you can vary the ORDER BY clause in the OVER part of the ROW_NUMBER() function.

Analytic (aka Window) functions are very powerful and will repay (many, many times over) any effort you put into learning them! As previously mentioned, this thread proved very helpful.

For future reference, henceforth, could you please include your table structure(s) as DDL (CREATE TABLE foo (f1 t1,....);) and your data as DML (INSERT INTO foo VALUES (v1, v2...);)? It's a help to have a single point of truth for the data and saves duplication of effort!

p.s. welcome to the forum!


timestamp change on Oracle dbf files I have an oracle database with no asm.  If I shut down the database, move some of the dbf files to a location where I cannot preserve the timestamp on the files, then move them back... Will the database start up without any problem?  I will not change anything in the contents of the dbf files nor the permissions.. only the OS timestamp will change.  is this ok?  Thanks!

Oracle does not care about the OS timestamp on data files.  When it opens files it looks at the scn in all of the file headers and compares them to what is recorded in the control file.  If all scn match, the database opens.  If there is a mismatch between the scn of a file and that in the control file, it will throw an appropriate error.  But none of this has to do with the OS time stamp on the file.


Does 'max_worker_processes' include background process from postmaster? I am wondering that max_worker_process include background process from postmaster? I'm going to upgrade Postgres from 9.3 to 9.6 and planning to use parallel query.

-bash-4.2$ ps auxf | grep postgres | grep -v grep
root      3848  0.0  0.2  89560  2440 pts/0    S    02:02   0:00                      \_ su - postgres
postgres  3849  0.0  0.2  15728  2584 pts/0    S    02:02   0:00                          \_ -bash
postgres  6168  0.0  0.1  51016  1880 pts/0    R+   02:34   0:00                              \_ ps auxf
postgres   977  0.0  1.5 257956 15296 ?        Ss   01:48   0:00 /usr/pgsql-9.6/bin/postmaster -D /var/lib/pgsql/9.6/data/
postgres   983  0.0  0.1 112960  1576 ?        Ss   01:48   0:00  \_ postgres: logger process
postgres   992  0.0  0.1 257956  1908 ?        Ss   01:48   0:00  \_ postgres: checkpointer process
postgres   993  0.0  0.2 257956  2708 ?        Ss   01:48   0:00  \_ postgres: writer process
postgres   994  0.0  0.1 257956  1684 ?        Ss   01:48   0:00  \_ postgres: wal writer process
postgres   995  0.0  0.2 258408  2808 ?        Ss   01:48   0:00  \_ postgres: autovacuum launcher process
postgres   996  0.0  0.1 112956  1900 ?        Ss   01:48   0:00  \_ postgres: stats collector process

If max_process_worker include those bg process from postmaster, it means that when default settings, backgroup process worker for parallel query can start background processes up to 2?

I believe that they are two different processes and max_process_workers does not include the bg process. Both of them are set and defined separately. If you are talking about backgroud WOKER process (bgw) it is a complete different process and is not included in the max_process_workers by default as it has to be supplied in separate code by user and is surely maintained by postgres process itself.


How can I determine the size of a dump file prior to dumping? How can I determine or estimate the size of the SQL dump file prior to using something like mysqldump?

Please run this query:

SELECT
    Data_BB / POWER(1024,1) Data_KB,
    Data_BB / POWER(1024,2) Data_MB,
    Data_BB / POWER(1024,3) Data_GB
FROM (SELECT SUM(data_length) Data_BB FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','performance_schema','mysql')) A;

This will give you a ballpark figure. The column index_length is not used because mysqldump does not dump indexes, only data. Just to be safe, you should always gzip it immediately:

mysqldump --all-databases --routines --triggers | gzip > MySQLData.sql.gz

Give it a Try !!!


Database design that handles data growth with time I have a big table that stores video rental data with the following columns:

  id, title, language, duration, owner, remarks, closing_date_for_loan

Assuming every day there are thousands of data inserts to this table, then within a year, I could have a million rows of data. The search on the data record involves range query on several columns and will always contain a WHERE closing_date_for_loan > NOW() condition.

To maintain this table, I could perform a query to transfer data with closing_date_for_loan < NOW() out of this big table periodically so that the table won't get too big causing excessive query times. I am looking for a more automated way of handling such data growth similar to how a log rotation works. Any ideas?

Additional Note:

I have tested a few composite index and the query time can range from a few seconds to 50s if the row gets to 5 million. Range queries can be hard to optimize, so I am looking for other ways like keeping the table to a manageable size.

Your first tactic should be good indexing. Somewhere down the road, you'll want to consider partitioning.

You always want to think about sargable expressions.


Is updating many columns in a MERGE statement bad for performance? I have a merge statement that either needs to insert about 65 rows worth of data, or update rows. At the moment I'm updating all columns when a match is found. But I don't need to.

Would there be a significant performance increase if i only updated a few columns on WHEN MATCHED THEN UPDATE vs updating all columns?

My first thought was: no, the whole row will get re-written whether you update one column or many, in both the log and data files. This should be true for simple un-indexed columns at least but there could be complications.

It may make a difference if the "extra" columns are indexed: will the engine be bright enough to know it doesn't need to make changes for what is effectively no change? Of course if the update results in the row moving to a different page because it is variable length and the update causes it to no longer fit forcing a page split, then all the indexes will need updating anyway. Another complication might be your own code in triggers if your database engine supports identifying which columns were altered (UPDATE() and COLUMNS_UPDATED() in SQL Server): if your code is dependent on that does the engine identify which columns really changed or just list those that were written to?

This will all be dependent on which database engine you are using, so you will need to specify that in your question text and/or tags to get less generic advice.


SSMS 2008 Object exploer not showing Has anyone seen this issue. I am using SSMS 2008 (x64 Version   10.0.5768.0) on Win 2008 R2 machine. Management studio crashed and then when I launch it, it doesn't show Object Explorer even when I hit F8 or select it from View. The only time it works is when I select full screen.

Thanks!

Nevermind.. I just fixed it... something might have been corrupt in my local profile setting. Renamed the SQL folder in Local and Roaming of App folder and it is working now.

Thanks!


postgres finding child tables for particular table I want to find all child tables corresponding to particular parent table.

SELECT *
FROM information_schema.table_constraints tc
  right JOIN information_schema.constraint_column_usage ccu
          ON tc.constraint_catalog=ccu.constraint_catalog
         AND tc.constraint_schema = ccu.constraint_schema
         AND tc.constraint_name = ccu.constraint_name
         and ccu.table_name in ('plugin')
 WHERE lower(tc.constraint_type) in ('foreign key');

Now this is working fine in public schema. but when I use 'a.plugin' instead of 'plugin' where a is my schema then it is not working. (i.e it is not working inside particular schema when I try to use table inside schema as a parent table).

The schema name of a table is not stored in the column table_name, it's stored in the column table_schema, so you need to change the condition:

and ccu.table_name in ('plugin')

to

and (ccu.table_schema, ccu.table_name) in (('a', 'plugin'))


How to UPDATE data without locking every record the search encounters? We have the following database specifications:
version: 10.1.47-MariaDB-0ubuntu0.18.04.1
innodb_version: 5.6.49-89.0
tx_isolation: READ-COMMITTED
innodb_strict_mode: ON
sql_mode: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
autocommit: ON

We have the problem that we lock too many data entries in the table, which causes deadlocks. Here is our example.
The first query we run:
UPDATE a SET col4 = 3 WHERE col1 = 1 AND col2 = 2 AND col3  = 3;
We have single column indexes on all the columns. According to the docs: this will set an exclusive next-key lock on every record the search encounters.
So the next UPDATE statement will be waiting for the previous one, although the matching final result records are not overlapping. This is because the index on col1.
UPDATE a SET col4 = 2 WHERE col1 = 1 AND col2 = 9999;
I would like to lock only the final result records and not "every record the search encounters". Splitting the query into a SELECT and an UPDATE by primary key gives me that result, but then a racing condition can happen between the SELECT and the UPDATE. Combining those in a transaction also doesn't help, as every SELECT is a consistent read.
How would you design a process like this, where I can run an UPDATE statement, without locking every record the search encounters?

UPDATE a SET col4 = 3 WHERE col1 = 1 AND col2 = 2 AND col3  = 3;

Needs this composite index:
INDEX(col1, col2, col3)  -- in any order

So, if you start it with col1, col2 (in either order), that index will also work nicely for
UPDATE a SET col4 = 2 WHERE col1 = 1 AND col2 = 9999;

If, by "We have indexes on all the columns" you mean single-column indexes, then those are wasted and in the way.  MySQL will use only one index.  It will pick among (col1), (col2), (col3) looking for the most selective.  Then it will scan all the rows with, say col1 = 1.  This is likely to be a lot more rows than you need to UPDATE.
With the composite index I recommend, it will go straight to the row(s) that match all of the WHERE clause.  This will be many fewer rows, thereby decreasing the likelihood of a deadlock.  Also, it will be much faster, which also decreases the chance of a deadlock.
Regardless, your could should check for errors and re-run any transaction that hits a deadlock.


Alter postgresql table with huge data I have a table in my database with few simple columns and one binary column. The primary key of that table is an integer type.

The problem I am facing is that auto-incremented primary key column had hit its maximum limit +2147483647. Means that table have around 2 billion records in it. Due to binary column in that table, it have around 255 GB size on disk.

Now all insertion to that table had stopped due to that data type limit. Logical solution is that we covert that primary key column to bigint. But due to huge size of the ALTER TABLE command always get failed and some times even database put into inconsistent state, where we can't access that table at all.

Statement used:

ALTER TABLE table_name ALTER COLUMN id SET DATA TYPE bigint ;

Any one have any suggestions to solve this issue.

One idea, without knowing the error you get, is that the operation times out.  This sometimes happens when the locks to be taken conflict with other operations or there are too many indexes and constraints to update and check. My answer below gives an idea for this scenario.  Other possibilities are that you ran out of disk space - add more disks to fix it, and consider the solution below to change the table structure.

Now that you cannot insert into your table anymore, you can just as well create a similar table with bigint instead of the integer PK column.  Copy over the existing data.  When done (and the necessary indexes built), you can simply rename both tables in the same transaction, so that the new one will in the end have the name of the present one.

With normal operation this would be a bit more complicated (has to duplicate the inserts to write to the new table and so on), but in your case it should work as described above.

Copying a table's data without the constraints and indexes will be possibly much faster than altering it in place (for most cases, at least).

Note: 9.1 will run out of support soon, please consider upgrading to a more recent version.


PostgreSQL: PANIC: heap2_redo: unknown op code 32 I was doing migrations while the server was still accessing the slave DB. I have a master / slave setup. After I restarted the server, I realized I couldn't connect to the SLAVE anymore. I went into the SLAVE and tried restarting Postgres, this is what it returned:

$ sudo /etc/init.d/postgresql start
 * Starting PostgreSQL 9.3 database server
 * The PostgreSQL server failed to start. Please check the log output:
2014-12-16 22:14:20 UTC LOG:  database system was interrupted while in recovery at log time 2014-12-16 20:57:20 UTC
2014-12-16 22:14:20 UTC HINT:  If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target.
2014-12-16 22:14:20 UTC LOG:  entering standby mode
2014-12-16 22:14:20 UTC LOG:  redo starts at 0/38620168
2014-12-16 22:14:20 UTC LOG:  consistent recovery state reached at 0/38680BF0
2014-12-16 22:14:20 UTC LOG:  database system is ready to accept read only connections
2014-12-16 22:14:20 UTC PANIC:  heap2_redo: unknown op code 32
2014-12-16 22:14:20 UTC CONTEXT:  xlog redo UNKNOWN
2014-12-16 22:14:21 UTC LOG:  startup process (PID 18357) was terminated by signal 6: Aborted
2014-12-16 22:14:21 UTC LOG:  terminating any other active server processes

Any idea on what could be causing this and what I can do to fix this? I'm going into a PANIC frenzy.

http://www.postgresql.org/docs/9.3/static/release-9-3-3.html

  Fixing this required changing the WAL record format for tuple
  freezing. While this is no issue for standalone servers, when using
  replication it means that standby servers must be upgraded to 9.3.3 or
  later before their masters are. An older standby will be unable to
  interpret freeze records generated by a newer master, and will fail
  with a PANIC message. (In such a case, upgrading the standby should be
  sufficient to let it resume execution.)


Which normalization rule violates the column-per-language approach? I've got the following table, which can store a title and content in German and English. I wonder, which normalization rule it violates?

I started going through the rules and stopped at NF2:

NF1: It's atomic.
NF2: Probably it breaks this rule, because the title and content are not directly dependent on the primary key. They depend on a key in the column name.

Is it correct, that this is against the NF2?

I would classify it as breaking NF2.  The language dependent columns are dependent on the Primary key and the Language.  I normally model these in a child table keyed on the Primary key of the parent table, and a language code (integer or ISO language code).

The disadvantage of having a child table is that it is difficult to ensure all the languages are provided.  This can be an advantage as you can save the record before you have all translations available.  In some cases, I have used language preferences to retrieve the best (by preference) alternate language for the description.  (HTTP has a header that allows language preferences to be specified.)


In MySQL, does the order of the columns in a WHERE clause affect query performance? I am having performance issues on certain database queries that have large possible result sets.

The query in question, I have three ANDs in the WHERE clause

Does the order of the clauses matter?

As in, if I put the ASI_EVENT_TIME clause first (since that would remove the most of the results out of any of the clauses.

Will that improve the run time on the query?

QUERY:

SELECT DISTINCT  activity_seismo_info.*
FROM `activity_seismo_info`
WHERE
    activity_seismo_info.ASI_ACTIVITY_ID IS NOT NULL  AND
    activity_seismo_info.ASI_SEISMO_ID IN (43,44,...,259) AND
    (
        activity_seismo_info.ASI_EVENT_TIME>='2011-03-10 00:00:00' AND
        activity_seismo_info.ASI_EVENT_TIME<='2011-03-17 23:59:59'
    )

ORDER BY activity_seismo_info.ASI_EVENT_TIME DESC

EXPLAIN of query:

+----+-------------+---------+-------+---------------------------+--------------+---------+------+-------+-----------------------------+
| id | select_type | table   | type  | possible_keys             | key          | key_len | ref  | rows  | Extra                       |
+----+-------------+---------+-------+---------------------------+--------------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | act...o | range | act...o_FI_1,act...o_FI_2 | act...o_FI_1 | 5       | NULL | 65412 | Using where; Using filesort |
+----+-------------+---------+-------+---------------------------+--------------+---------+------+-------+-----------------------------+

Using:

PHP 5.2

MySQL 5.0.51a-3ubuntu5.4

Propel 1.3

Symfony 1.2.5

I do not think so. The query optimizer should be clever enough.

You can try rearranging the WHERE clauses and see that EXPLAINS tells you the same in each case.

About what can be done to optimize this query: Is there an index on ASI_EVENT_TIME ? (this is the most crucial I think for this query as you also sort the results using it).

Are there indexes on the other two fields (ASI_SEISMO_ID and ASI_ACTIVITY_ID)?

It would be helpful if you posted the table structure.


SSIS File System Task (Copy File Operation) OverWriteDestination functionality I was unable to find any documentation on the SSIS File System Task component OverWriteDestination property functionality  for a Copy File Operation.

I understand what it is used for but how does it actually cause the component to behave when the OverWriteDestination is set to false?

If set to False, will the component simply skip the file and not try to copy it or will it copy it with a different name, perhaps a number appended to the end of the file name being copied (for example filename(1).text)?

If you have OverwriteDestination set to False and your destination file already exists, then you will get an error stating something along the lines of

[File System Task] Error: An error occurred with the following error message: "The file 'XXXXXXX' already exists.".

That is the default behavior if you do not chnage anything else.


SSIS:How retrieve and insert an XML Column I'm trying to Insert two columns from one table onto another. The first column is an int but the second column is an XML. I'm having issues inserting the xml column

In first SQL Task I'm doing a select of the the two columns and storing this information into an object.

In the for loop I'm passing on the information :

And in the Result set I'm declaring the variables :

I know that the Form_XML_Text is set to Varchar here but there is not XML option.

And running this insert statement:
INSERT INTO TEST
VALUES (?,?)

This is the error message I'm getting back:

This can be done by converting the XML column when initially selecting it then casting it back to an XML data type for the insert.  This is outlined in further detail below.

In the first Execute SQL Task, select the XML column by casting it to an NVARCHAR data type.  Keep the Form_XML_Text variable as a string data type.
In the Execute SQL Task within the Foreach Loop, set this variable to use the NVARCHAR data type on the Parameter Mapping pane.
Then in the SQL statement on the Execute SQL Task inside the loop cast it back to XML as done below.  This example assumes that the XML column is the second one in the destination table.
I would also recommend always explicitly listing the columns when inserting into a table as follows.  This will make debugging easier and prevent errors that can occur if additional columns are added to the table in future.

Insert Example:

INSERT INTO TEST (Column1, XMLColumn)
VALUES (?, CAST(? AS XML))


When was the syslogin's password last updated? In SQL Server, I see there's a field in syslogins titled "updatedate" which is a timestamp of the last time the login was updated.

What constitutes an "update"?
My goal is to only get a timestamp of the last time a password was changed.

SQL Server does not track this information by default, so you will not be able to look back and see when these happened in the past. The trace event is 107 and it is not in the default trace (and I don't see anything in the system_health extended event session either).

For the future, you can set up an audit specification (no idea if this is an option for you because you didn't specify version or edition). You may also be able to capture these events using a server-side trace or an event notification on ALTER LOGIN.

Server-Side Trace

Here is a script that will produce a server-side trace that captures just the Audit Login Change Password Event (and this works for both ALTER LOGIN and sp_password) - please be sure to update the path in the first EXEC line:

DECLARE @TraceID INT, @mfs BIGINT;
SET @mfs = 5;

EXEC sp_trace_create @TraceID OUTPUT, 0, N'C:\path\tracefile', @mfs, NULL;

EXEC sp_trace_setevent @TraceID,107,1, 1;
EXEC sp_trace_setevent @TraceID,107,11,1;
EXEC sp_trace_setevent @TraceID,107,8, 1;
EXEC sp_trace_setevent @TraceID,107,12,1;
EXEC sp_trace_setevent @TraceID,107,14,1;
EXEC sp_trace_setevent @TraceID,107,40,1;
EXEC sp_trace_setevent @TraceID,107,42,1;

EXEC sp_trace_setstatus @TraceID, 1;

Later when you want to review the events the trace captured, you can see the last update time for each login with the following query:

DECLARE @path NVARCHAR(260);

SELECT @path = [path]
FROM    sys.traces
WHERE  id = <traceID from above>;

SELECT TargetLoginName, LastTimeStamp = MAX(StartTime)
  FROM sys.fn_trace_gettable(@path, DEFAULT)
  WHERE EventClass = 107 -- in case you've added other events
  GROUP BY TargetLoginName;

You'll probably want to schedule this query periodically and snapshot the results somewhere, since the data won't hang around in the default trace forever.

Event Notifications

If you'd rather have an EVENT NOTIFICATION instead of a server-side trace running, you can do it this way. First, create a table in MSDB to track the login name and the time of the change (you may want to capture other information from the event as well, like I did with the trace above, but this is a bit more involved and I'm trying to maintain some brevity).

USE [msdb];
GO

CREATE TABLE dbo.PasswordChanges
(
    LoginName     SYSNAME,
    EventDateTime DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Now, we need a queue and a service for our notification:

CREATE QUEUE PasswordChangeQueue;
GO

CREATE SERVICE PasswordChangeService
    ON QUEUE PasswordChangeQueue
   ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO

CREATE EVENT NOTIFICATION PasswordChangeNotification
    ON SERVER WITH FAN_IN
    FOR AUDIT_LOGIN_CHANGE_PASSWORD_EVENT
    TO SERVICE 'PasswordChangeService', 'current database';
GO

And a procedure to actually do something when the event happens:

CREATE PROCEDURE dbo.LogPasswordChange
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @message_body XML;

   WHILE (1 = 1)
   BEGIN
       WAITFOR ( RECEIVE TOP(1) @message_body = message_body
          FROM dbo.PasswordChangeQueue), TIMEOUT 1000;

       IF (@@ROWCOUNT = 1)
       BEGIN
        INSERT dbo.PasswordChanges(LoginName)
          SELECT @message_body.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname');
       END
   END
END
GO

And finally set the notification to use this procedure.

ALTER QUEUE PasswordChangeQueue
WITH ACTIVATION
(
   STATUS = ON,
   PROCEDURE_NAME = dbo.LogPasswordChange,
   MAX_QUEUE_READERS = 1,
   EXECUTE AS OWNER
);
GO

Now change the password for a few logins, and you should see results from the following query:

SELECT LoginName, LastChange = MAX(EventDateTime)
  FROM dbo.PasswordChanges
  GROUP BY LoginName;


SQL Server Migration Plan - Alias Options for Multiple Apps Connecting We currently have a number of databases on a single SQL Server 2008R2 instance (server name: prdServer01; Win VM). We want to migrate to a SQL Server 2016 instance (server name: prdServer02; Win VM).
We have many applications that connect to the 2008R2 DB server. So ideally we would like to avoid having to update each connection string in each application.
From what I can tell, our options are

Add an alias to the SQL 2016 server(prdServer02). That alias will be the same name as the SQL 2008R2 server(i.e. prdServer01)
Add a DNS entry to the Windows server (I'm not entirely sure how this works)

The problem I see, is that we would have to take the 2008R2 server offline before the alias will work. And it is also going to be difficult to test.
Is there a better solution for this? Are we best to give the new 2016 server a completely different alias and then update our apps one by one to use this new alias in the connection strings?

you may eventually rename the old VM and the old instance (es: prdServer01_old) and assign the prodServer01 at the new one (both VM and instance)
prdServer01 --> prdServer01_old
prdServer02 --> prdServer01
here how to rename an instance:
https://docs.microsoft.com/en-us/sql/database-engine/install-windows/rename-a-computer-that-hosts-a-stand-alone-instance-of-sql-server?view=sql-server-ver15
As i'm supposing you are not using ip addresses in connection string, that's all.


How To Create A New bash_profile And Connect New Database To It In Oracle OS -> Linux

DB -> Oracle 11gR2

Normally my .bash_profile file looks like this:

TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_HOSTNAME=abcde.localdomain; export ORACLE_HOSTNAME
ORACLE_UNQNAME=DB11G; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME
ORACLE_SID=DB11G; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

But I need to do that:

ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/newdb

After that I have to create a new database in newdb.

I'm a newbie oracler and I couldn't find any clear solution. Should I create a directory as newdb and set a new .bash_profile file? If it's a clear solution, how can I do it and after that will my dbca and netca operations work fine? Or something else like this?

Thank you.

First, remove all that junk from .bash_profile. Use oraenv. Example:

$ grep u01 /etc/oratab
MIN112:/u01/app/oracle/product/11.2.0/dbhome_1:N
MIN121:/u01/app/oracle/product/12.1.0/dbhome_1:N

$ . oraenv
ORACLE_SID = [oracle] ? MIN112
The Oracle base has been set to /u01/app/oracle
$ env | grep ORA | sort
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
ORACLE_SID=MIN112

$ . oraenv
ORACLE_SID = [MIN112] ? MIN121
The Oracle base remains unchanged with value /u01/app/oracle
$ env | grep ORA | sort
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
ORACLE_SID=MIN121

Second, forget /u01/app/oracle/product/newdb.
Just run /u01/app/oracle/product/11.2.0/dbhome_1/bin/dbca and create your other database and provide the necesarry path for datafiles. You do not need another ORACLE_HOME for another database.

When you have multiple databases, just put them in /etc/oratab (dbca will do this automatically in 11.2), and use oraenv or just set ORACLE_SID in case of the same ORACLE_HOME for switching between them.


How would you design a table for approval system between User and Sports Club I am currently learning database modelling and struggling to model the following requirement. I have a user and sports_club table. User can only join a sports_club if three existing users of the club approve his request. If the number of members in club are less than three then all the existing members (whatever 2, 1 ) need to approve his request. My table looks something like this:

I am struggling to model this approval process in my database? Could anyone help me here? Sports club can have any number of users.

Add a self-reference to User. Call this reference "approved by". I'd suggest implementing this as a separate table but if you're absolutely sure you'll only ever need three approvers it can be colums in the user table.

As a current member approves an application count the number of approvals, compare it to the number of required approvals, and the number of current members and proceed accordingly. Remember to account for the case where the current applicant is the first to apply to a club.

I think it's an error to make a user exclusive to one club. Wouldn't it be good to know that the same person plays polo, frisbee, netball and Jai-Alai? It would be better to have an intersection table called Membership.


Copying MySQL Raw Data Files for Master-Slave Replication and GTIDs I have been developing and running a mysql-based web application (intranet-based) for some time now. I wish to establish a step-by-step sane sequence for creating a slave from the running MySQL master. I have decided I should start the slave server by copying MySQL raw data files. A couple dozen times, I've gone through the relevant section of the manual, and I'm still dizzy.

I once got replication going (years ago) using this same approach, but at present, I'm in a bit of a fix. I think the complexity for me has been increased by the following:

I am using version 5.6.23
I have just turned on GTID mode, when I never really mastered the "old" system yet!
Generally, most tutorials and help posts out there are currently either outdated or in "transition", given the recent changes and advances in replication functionality that came on board during the 5.6.xx series.

Currently, I have the following config declarations:

MASTER:

log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
binlog-format=MIXED
enforce-gtid-consistency
gtid-mode=ON
log-slave-updates

SLAVE:

log-bin=mysql-bin
server-id=2
read-only=ON
gtid-mode=ON
enforce-gtid-consistency
binlog_format=MIXED

In order to copy raw data files from the master, what should be my
step-by-step process?
Exactly what files within the data directory must I NOT copy, and
why?
On the slave, what are the gotchas, especially regarding the GTID
mode?

I'm sorry if aspects of my question sound vague. I'd be more than willing to elucidate as might be necessary.

Thank you in advance.

OBSERVATION #1

The slave needs log-slave-updates in my.ini

OBSERVATION #2

Both Master and Slave need binlog_gtid_simple_recovery in my.ini

MySQL Restart is required on both Master and Slave to include new options

SUGGESTION

Since DB is 5GB, just mysqldump the database on the Master. No need for raw copying

STEP 01 : On the Master, run this

RESET MASTER;
GRANT REPLICATION CLIENT,REPLICATION_SLAVE ON *.*
TO replicator@'%' IDENTIFIED BY 'repl1cat0r';

STEP 02 : On the Slave, run this

STOP SLAVE;
CHANGE MASTER TO
    MASTER_HOST='IP Address of Master',
    MASTER_PORT=3306,
    MASTER_USER='replicator',
    MASTER_PASSWORD='repl1cat0r',
    MASTER_HEARTBEAT_PERIOD=1,
    MASTER_AUTO_POSITION=1
;

STEP 03 : Dump the data on the Master

set mysql_user=root
set mysql_conn=-u%mysql_user% -p
set dump_options=--routines --triggers --events -A --apply-slave-statements
mysqldump -uroot -p %dump_options% > C:\mysqldata.sql

STEP 04 : Copy the dump to the Slave (leave dump in C:)

STEP 05 : Load dump into the Slave

mysql %mysql_conn% < C:\mysqldata.sql

STEP 06: Verify replication

Login to mysql on the Slave and run `SHOW SLAVE STATUS\G`

Give it  a Try !!!


Oracle database uses wrong parameter file When I try to startup my Oracle database I get the following error:

LRM-00109: could not open parameter file '/opt/app/oracle/product/11.2.0/db_1/dbs/initjicdbaex.ora'

The /dbs directory in my Oracle home contains the init.ora file I want to use, but I don't know how to point the database to read that file.

How do I set the correct parameter file path to init.ora?

You can not or at least you should not. The right pfile (or spfile) should contain ORACLE_SID in it's name.

Theoretically you  can start the database using:

SQL> startup pfile='<path to init.ora>'

But I won't recommend it.


What is the appropriate SQL method for "Safe to SUM"? In SQL Server 2008 R2+ we, unfortunately, have some nvarchar columns storing numeric data.

In some cases we want to SUM the data when it can be summed. The problem is that ISNUMERIC() returns 1 for a number of entries with are invalid parameters for SUM.... I understand what's going on here but I am looking for a workaround.

For example ISNUMERIC('10,1') = 1 but I cannot include that value in a SUM so I would like that value excluded from the SUM... and any data points, in fact, which cannot be parameters to SUM without being massaged...

So I'm wondering what is the preferred way to determine whether or not the contents of an alphanumeric column can be summed?

EDIT: It's worth noting that this is a take-off from this answer on SO:

https://stackoverflow.com/a/15833014/369775

but I am not convinced that the methods provided in that answer are optimal. I do agree with HLGEM:

https://stackoverflow.com/a/338292/369775

  The best solution would be to stop storing integers in a varchar column. Clearly there is a data issue where the data is interpretable as a numeric but cannot be cast as such.

The challenge with relying on ISNUMERIC() for this is that it can look at a lot of data that can't be summed and give it a pass. It isn't IS_ELIGIBLE_FOR_SUM() - it is merely returning true if the input could be converted to any of the numeric types. Commas are allowed because conversion to MONEY, for example, works successfully:

SELECT CONVERT(MONEY,'11,2');

So this also works:

SELECT SUM(CONVERT(MONEY,'11,2'));

Which probably isn't what you want. I think it would be safer to replace commas with periods before attempting to perform operations, or just exclude them and filter out rows this way:

DECLARE @t TABLE(n NVARCHAR(32));

INSERT @t VALUES(N'11,2'),(N'32'),(N'-32.4'),(N'32323'),(N'^'),(N'.'),(N'-');

SELECT SUM(CASE WHEN ISNUMERIC(n) = 1
    AND n NOT LIKE '%[^0-9.-]%' THEN CONVERT(MONEY,n) END)
  FROM @t
  WHERE ISNUMERIC(n) = 1 AND n NOT LIKE '%[^0-9.-]%';

The reason you have to perform a CASE expression in addition to the filter is that you can't control how the optimizer will process the statement - without CASE it might try to perform the conversion before the filter.

Of course, the smartest course of action would be to stop using the wrong data type in the first place. If you continue storing dirty data in ill-advised data types, you don't really get to complain about "optimal"...


Trying to query AD on a linked server error message is 7321 Trying to query AD on a linked server error message is 7321 error occurred while preparing the query.  Any suggestions?

Select Name, EmployeeID, Mail, SamAccountName
FROM OPENQUERY(ADSI, 'SELECT Name, EmployeeID, Mail
FROM ''LDAP://DC=mydomain,DC=com''
WHERE  objectClass=''user'' AND EmployeeID > 0 and SamAccountName = ''f.bower''')

At a minimum, in the outer query you want SamAccountName which you are using within the openquery as a filter but do not have in your select list there.

Whether that's the root issue or just a byproduct of you doctoring this up for the site remains to be seen.

Select Name, EmployeeID, Mail, SamAccountName
FROM OPENQUERY(ADSI, 'SELECT Name, EmployeeID, Mail, SamAccountName
FROM ''LDAP://DC=mydomain,DC=com''
WHERE  objectClass=''user'' AND EmployeeID > 0 and SamAccountName = ''f.bower''')


Amazon Aurora CREATE_TIME in INFORMATION_SCHEMA.TABLES is NULL We are running an Amazon Aurora database that emulates MySQL 5.6.10. The CREATE_TIME in SHOW TABLE STATUS and INFORMATION_SCHEMA.TABLES is consistently NULL for all our tables.

Is this a bug, a limitation of Aurora's architecture, or am I doing something wrong? Is this information available anyplace else?

db2 information_schema> SELECT DISTINCT TABLE_SCHEMA
FROM information_schema.TABLES WHERE CREATE_TIME IS NOT NULL;
+--------------------+
| TABLE_SCHEMA       |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set (9.00 sec)

Additional notes: We have several thousand tables across several hundred databases, on several different hosts; many of the tables were created with files-per-table on, and some with it off. We do not have access to the .frm files, and they may or may not exist in Aurora. This has always worked for us in MySQL.

Update: Amazon support told us, vaguely, this is a bug and "it will be corrected in a later release." Still interested in knowing if this is going to be retroactively available, or if there is another place to get this 'cause Amazon is not elaborating.

This was the response from AWS - they agree using the TABLE_COMMENT column is the way forward. I can also confirm we have successfully implemented this solution.

  Hello,

  Thank you for contacting AWS premium support. My name is Vidyadhar and
  I am glad to assist you on this case.

  From the case description, I understand that you noticed only null
  values in create_time column in information_schema.tables. You were
  removing old tables based on the values inside this column in MySQL.
  Now, you would like to know reason for not showing up creation time in
  Aurora and alternatives to achieve your requirement.

  Just to let you know, there is a known bug in MySQL related to the
  same where create_time will be null for partitioned columns. This has
  been fixed in MySQL 5.6.25 version. Please look into below link for
  reference.

  https://bugs.mysql.com/bug.php?id=69990

  In aurora this column will be NULL for all tables including
  partitioned tables. This has been already notified to the service
  team. Also, there is no workaround to this available in Aurora for
  now.

  It is best option to utilize the table comment session as a work
  around to note the time of table creation and proceed accordingly with
  table deletion. You can look into TABLE_COMMENT column in
  information_schema.tables. Please refer to below example which might
  be helpful.

MySQL [test]> create table S1(id int) comment='2017-09-21 15:53:51';
Query OK, 0 rows affected (0.02 sec)

MySQL [test]>  show create table S1\G
*************************** 1. row ***************************
       Table: S1 Create Table: CREATE TABLE `S1` (   `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1
COMMENT='2017-09-21 15:53:51' 1 row in set (0.00 sec)

MySQL [test]> select TABLE_SCHEMA,TABLE_NAME,TABLE_COMMENT from
information_schema.tables where table_schema='test' and
table_name='S1';
+--------------+------------+---------------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_COMMENT       |
+--------------+------------+---------------------+
| test         | S1         | 2017-09-21 15:53:51 |
+--------------+------------+---------------------+
1 row in set (0.01 sec)

  I truly apologize for the inconvenience caused. Aurora service team is
  working hard to add features and fixing bugs. As of now, I am unable
  to provide any ETA but definitely this will be fixed in future
  versions.

  I hope this information is helpful. Please feel free to reach me if
  you have any further questions or concerns.

  Have a great day!


Large update taking too long; looks like it is updating more records than I have I am trying to do an update on a table that has 4 million records. The main table is indexed on SerNum a unique field. I use a temp table to update from that also has a unique index on SerNum. I am told this should happen fast but it's taking hours. It's been running for 2 hours and if I add up the rows affected I get 5,260,000 which is weird because I only have 3,869,000 records in the table. The select into the temp table shows it has 3,390,954. Here is what I have so far can this be more efficient?

USE IMB_TraceData
GO

IF OBJECT_ID('tempdb.dbo.PRIMARY KEY (lname,') IS NOT NULL
    DROP TABLE #MaxDate;
GO

-- ******************************************
-- Create Work Table
-- ******************************************
SELECT * INTO #MaxDate
FROM
    (
    SELECT  *
    FROM    (
            SELECT  *,
            ROW_NUMBER() OVER (PARTITION BY TR_SerNum
            ORDER BY TR_Scan_Date DESC) AS rn
            FROM  Trace_Return_Table
            ) a
    WHERE   a.rn = 1
    ) AS n
GO
CREATE INDEX IDX_SerNum ON #MaxDate(TR_SerNum)
GO

DECLARE  @UPDATES TABLE (PK_ID int NOT NULL Primary KEY)
DECLARE @x INT

SET @x = 1
SET ROWCOUNT 10000

WHILE @x > 0
    BEGIN
        BEGIN TRANSACTION
        UPDATE TOP (10000) IMB_TBO_Data.dbo.The_Big_One
        SET TR_Scan_Date = n.TR_Scan_Date,
            TR_Facility_Id = n.TR_Facility_Id,
            TR_Operation_Code = n.TR_Operation_Code
        OUTPUT inserted.HT_SerNum INTO @Updates
        FROM IMB_TBO_Data.dbo.The_Big_One t
        INNER JOIN #MaxDate n
            ON t.HT_SerNum = n.TR_SerNum
        WHERE NOT EXISTS
        (SELECT * FROM @UPDATES AS Updates
        WHERE UPDates.PK_ID = t.HT_SerNum)
        SET @x = @@ROWCOUNT
        COMMIT TRANSACTION
    END

SET ROWCOUNT 0

Well, after a few iterations your @Updates table variable probably has become huge (since you're inserting data into it in every iteration ), and this is probably leaking memory. Plus, on every iteration, you're doing an EXISTS check for every single row in the tables which are a product of the join statement, against the @Updates table.

Might I suggest this approach here for the while part of your code, it works because the [rn] column is sequential, so you actually have a index you can just use for the chunk based update

--Keep this original code as is --
USE IMB_TraceData
GO

IF OBJECT_ID('tempdb.dbo.PRIMARY KEY (lname,') IS NOT NULL
    DROP TABLE #MaxDate;
GO

-- ******************************************
-- Create Work Table
-- ******************************************
SELECT * INTO #MaxDate
FROM
    (
    SELECT  RowNum = row_number() OVER (ORDER BY (SELECT 0)),*
    FROM    (
            SELECT  *,
            ROW_NUMBER() OVER (PARTITION BY TR_SerNum
            ORDER BY TR_Scan_Date DESC) AS rn
            FROM  Trace_Return_Table
            ) a
    WHERE   a.rn = 1
    ) AS n
GO
CREATE CLUSTERED INDEX IDX_RowNum ON #MaxDate(RowNum)
CREATE INDEX IDX_SerNum ON #MaxDate(TR_SerNum)
GO
-- original code ends --

-- modified code follows --
DECLARE @tRow BIGINT = 0
DECLARE @tMax BIGINT = (SELECT MAX(RowNum) FROM #MaxDate)

WHILE @tRow< @tMax
        BEGIN
          set @tRow = @tRow + 10000
            BEGIN TRANSACTION
            UPDATE t
            SET TR_Scan_Date = n.TR_Scan_Date,
                TR_Facility_Id = n.TR_Facility_Id,
                TR_Operation_Code = n.TR_Operation_Code
            FROM IMB_TBO_Data.dbo.The_Big_One t
            INNER JOIN #MaxDate n ON t.HT_SerNum = n.TR_SerNum
            WHERE n.RowNum between (@tRow - 10000) AND @tRow

            COMMIT TRANSACTION
        END

My bet is, this will run a lot faster.


Need help to understand how information_schema.TABLES get updated I created large innodb table

mysql> select count(*) from big_data;

+----------+
| count(*) |
+----------+
| 23822336 |
+----------+

checked the stats in informaion_schema.TABLES

mysql> select data_length, index_length, data_free
       from information_schema.TABLES
       where TABLE_NAME = 'big_data';

+-------------+--------------+-----------+
| data_length | index_length | data_free |
+-------------+--------------+-----------+
|  4385144832 |            0 |         0 |
+-------------+--------------+-----------+

Then deleted approx 4M rows.

Checked the count

mysql> select count(*) from big_data;

+----------+
| count(*) |
+----------+
| 19978240 |
+----------+

checked the information schema.TABLES

mysql> select data_length, index_length, data_free
       from information_schema.TABLES
       where TABLE_NAME = 'big_data';

+-------------+--------------+-----------+
| data_length | index_length | data_free |
+-------------+--------------+-----------+
|  4385144832 |            0 |         0 |
+-------------+--------------+-----------+

There is no change!!

Can somebody please explain to me what happened?

I'm using mysql 5.6.18

InnoDB is telling you that the data pages are the same size.
The DELETE you ran simply makred the rows delete within the associated data pages.
You have to run
OPTIMIZE TABLE big_data;

or
ALTER TABLE big_data ENGINE=InnoDB;

to shrink it.
Since you inserted data into a table with no indexes, that explains 0 for index_length.
The data_free column is really superfluous when innodb_file_per_table is 1 (Thta's the default for MySQL 5.6). The data_free column was used to give you the total space free inside ibdata1 when innodb_file_per_table = 0 and all data and indexes were inside ibdata1. Since innodb_file_per_table = 1 and you loaded data into MySQL from the its beginning, I believe 0 will always appear for data_free for every InnoDB table in INFORMATION_SCHEMA.TABLES.
Here is something else to consider: The INFORMATION_SCHEMA.TABLES does not properly update because of the Storage Engine's ineraction with it.
For example, I noticed you used
select count(*) from big_data;

Why did you not use
select data_length, index_length, data_free, table_rows
from information_schema.TABLES
where TABLE_NAME = 'big_data';

Using this Pictorial Representation of the InnoDB Architecture
InnoDB Architecture

try imagine what is happening when you delete 4 million rows:

Drag data pages for the 4M rows into the buffer pool
Capture those rows in the Rollback Segments
Mark the rows delete inside those memory-resident data pages
Write the changes into the Double Write Buffer
Migrate changed data pages through the Log Buffer to commit the changes back to the .ibd file
Write Redo Log Info into the Transaction Logs (ib_logfile0, ib_logfile1).
Clean up the Rollback Segments upon the completion of the DELETE.

In light of this, it would take a considerable amount of time for the INFORMATION_SCHEMA.TABLES to catch up with all these changes.
Please note that you could never reliably extract exact row counts from INFORMATION_SCHEMA.TABLES. The MySQL Query Optimizer always approximates row counts for InnoDB tables by scanning non-leaf BTREE nodes. I have written about this consistently in the DBA StackExchange for almost 3 years.

Mar 03, 2014 : The most efficient method to obtain the total entries into a mysql table
Feb 23, 2013 : How does mysql deal with queries that touches myisam and innodb tables? (See ASPECT #3)
Apr 23, 2012 : Why is a secondary index chosen over a clustered index for SELECT COUNT(*) ...?
Aug 04, 2011 : Optimizing InnoDB default settings
Jun 21, 2011 : From where does the MySQL Query Optimizer read index statistics?

Therefore, it is not surprising that other metadata aspects of InnoDB do not update column info in INFORMATION_SCHEMA.TABLES effectively .
What if the table was MyISAM ???
MyISAM does not have to jump through all these hoops like InnoDB. Notwithstanding, even if the big_data table was MyISAM, the .MYD file would remain the same size after the DELETE. You would still have to run OPTIMIZE TABLE big_data; to shrink the .MYD


Artificially increasing query time Very weird question:

Is there any way to artificially inflate the amount of time a query will take? An infinite loop would be great.

SELECT CLAIM.UNIT_PRICE FROM CLAIM WHERE CLAIM.DATE_PROCESSED>SYSDATE-10;

Needs to take enough time for our DBA to analyze something while it's running.

Create a table with a select * from a large table and where 1=0. Gather stats, save them using dbms_stats and then apply those stats to the large table. You will essentially tell Oracle that the large table has no data, which would favor full table scans. You can also try switching the optimizer mode to first_rows_1 or invalidating some indexes.


Can't create any objects within OLAP database Problem

I can't create any objects under my databases in my Analysis Server. My context menus on all the folders under this test database just have Refresh as an option.

Details

My Analysis Server is running locally. I am a local admin. I am running SSMS as Administrator.

I have tried running the Analysis Services as both Local System and my user.

During setup, I added my user to the list of users who can administer Analysis Services.

More Information

Use SQL Server Data Tolls (SSDT) to create the SSAS objects and deploy them to the SSAS server, where they can be managed through SSMS.  Once created, modifications can be made to cubes in SSDT with the changes deployed and accessible in SSMS.  The following items outline this further.  While the last link covers the deployment process, the third item (configuration) details the deployment server property and other deployment related settings.

SSDT
Creating SSAS Projects
Analysis Services Projects Configuration
Deploy SSAS Projects


slow autovacuum on table with binary data I have a table with about 4M rows, but each row has some binary data that may consume up to 1Mb.

The autovacuum process on this table is taking over 1.5 hours to complete, which seems way to long to me.

I understand that when Postgres updates or deletes a row it keeps a copy of the old row and then autovacuum cleans this up later.

If my rows have a lot of binary data and they are getting updated regularly, is this the cause of my very slow autovacuum? And is it trying to load all of that data into memory?

4M rows with each one up to 1MB means that the table is up to 4TB, not counting the obsolete rows themselves.  1.5 hours would be astonishingly fast to vacuum a table of that size.  Especially if you still have the default settings of "autovacuum_vacuum_cost_delay" and kin, which would throttle the autovacuum to about 8MB/S of read throughput, and less of write throughput.

All parts of the table not already having the visibility map set for it will have to be read into memory, but not all at the same time.  All the tables indexes will also have to be read, perhaps multiple times.


How to maintain customer addresses for Orders? The database I am designing is part of a project I am working on. I have come up with 3 simple "business rules":

Customers must either be Individuals or Businesses (a business belongs to a certain type).
A Customer must place at least one Order.
Orders must contain their historical shipping address (i.e. if a customer updates their address, any Orders associated with the Customer must reference the old address).

I have come up with the following design based on the above requirements:

However, there is a problem with normalization.
In order for the 3rd requirement to be met, I am saving the shipping address into the Order at the time of creation. Additionally, I am referencing an address in the Customer table (which is potentially the same).

In the bottom right of the image, I extracted the Address-related attributes into its own table as the first step in my attempt to normalise the design.

Over the past week, I have searched for hints without much luck.
I know about this post and this one, yet the implementation is rather ambiguous for my scenario/case.

The thought of making a single table with all addresses, then referencing each as required has crossed my mind but doesn't seem like the right (let alone best) way of achieving this goal.

My question is simple:

How do I meet the 3rd requirement of associating an address with a Customer and an Order historically, with normalisation in mind?

Any help is very much appreciated. Cheers.

You have addresses in 3 tables.  Shrink that down to 1.  Then have a customer_address_id in the Customers table and ship_address_id in the Orders table.  Then, ship_address_id can be nullable.  Do not make all the fields in Addresses Nullable.  (Perhaps region and/or post_code can be left blank -- but make them DEFAULT '' instead of NULLable.

If a Customer is 'shipping' to himself, simply copy the customer_address_id into Orders.ship_address_id.

One more thing...  In order to not lose "old addresses", be careful about when (if ever) you change any row in Addresses.  You may end up with unused entries, but that is a small price to pay for the data integrity you require.


Computing date from record fields In an SQLite DB I have a table with a station integer field and an at date field.

How can I combine these two values to obtain, in a WHERE clause a date which is at plus station months?

I.e. for a record with at = '2019-01-01' and station = 3, my WHERE clause should look like WHERE before <= '2019-04-01'

I have looked at several resources online, including https://www.sqlite.org/lang_datefunc.html but no one refers to using filed value to compute a date.

You can use the following:

 WHERE before <=  date(at, (station || ' months') )

Please note that according to the documentation:

  "±NNN months" works by rendering the original date into the YYYY-MM-DD format, adding the ±NNN to the MM month value, then normalizing the result. Thus, for example, the data 2001-03-31 modified by '+1 month' initially yields 2001-04-31, but April only has 30 days so the date is normalized to 2001-05-01. A similar effect occurs when the original date is February 29 of a leapyear and the modifier is ±N years where N is not a multiple of four.


ONLINE BACKUP is not performed from the CRON Job on LINUX server (manually it works) I made a script which executes successfully online backup when it is run manually by the instance owner.
Script is the following (two lines originally).

 #!/bin/bash
    /opt/ibm/db2/V9.7/bin/db2 backup database maxdb71 online to "/data/archiveBackup"
 WITH 2 BUFFERS BUFFER 1024 PARALLELISM 1 INCLUDE LOGS WITHOUT PROMPTING;

As I said when I execute it manually by the instance owner it works.
I put it in the instance owner crontab.
crontab -l lists this script, and date/time on the server is OK.

Why the script is not executing the online backup when it works OK when it is run manually?

Thank you

Chances are that the reason your script runs from the command line but fails from cron is that your DB2 environment is already initialized (i.e. via your .profile) for the interactive session, where it is not when called by cron.

Here's a suggested modification:

 #!/bin/bash
 #

 # Setup DB2 Environment
 . $HOME/sqllib/db2profile

 # Back up Database
 db2 -v "backup database maxdb71 online to /data/archiveBackup INCLUDE LOGS WITHOUT PROMPTING"

A few comments:

This will work if you're executing it via cron for the instance owner.  However, because it depends on the $HOME environment variable you would need to use the actual path to the instance owner's home directory if you wanted to run this via cron as a different user.
You should not reference binaries in /opt/ibm/db2/..., you should be using the binaries from the DB2 instance.  Because the script initializes the environment from the db2profile, the db2 command will be in the script's $PATH.
I removed some of the options from your backup command as these are not necessary.  DB2 is very intelligent when it comes to selecting run-time values for these, so you're likely to get worse backup performance if you override the defaults.
When you execute the script from cron, make sure you capture the output in a log file.  It will be very useful for debugging purposes. Example crontab:

00 23 * * * /path/to/script/backup.sh > /path/to/script/backup.log 2>&1


Sql Server output parameters Is there a way to use named parameters when getting the output parameters of a stored proc? Currently my knowledge of output parameters is very limited. It looks like I have to use them in order of their decleration in the stored proc. I.E if i did exec test @rich output,@bob output the call would blow up. How can i have the order be arbitary? Thank you

create procedure test
  @ID as INT output
 ,@mark as char(20) output
as

  select @ID = 5,@mark='test'
go
declare @bob as int
declare @rich as char(20)
exec test @bob output, @rich output
select @bob,@rich

There is nothing special about OUTPUT parameters when it comes to "named parameters" or "ordinal parameters". In SQL Server this terminology this applies to the EXEC call and how you specify parameters there: not direction

Ordinal = position must match and datatype must be compatible
Named = assign the local value to the stored proc parameter name. Only datatype must be compatible

This is ordinal

declare @bob as int, @rich as char(20)
--OK
exec test @bob output, @rich output
GO

declare @bob as int, @rich as char(20)
--Fail
exec test @rich output, @bob output

This is named

declare @bob as int, @rich as char(20)
--OK
exec test
      @ID = @bob output,
      @mark = @rich output
GO

declare @bob as int, @rich as char(20)
--OK
exec test
      @mark = @rich output,
      @ID = @bob output

If you had a stored proc with parameters with defaults ...

create procedure test2
  @ID as INT output
 ,@filler1 tinyint = 0 --has default
 ,@mark as char(20) output
 ,@filler2 tinyint = 0 --has default
as
     select @ID = 5,@mark='test'
go

.. then you need the DEFAULT keyword for ordinal parameters if you have subsequent mandatory parameters

exec test2 @bob output, DEFAULT, @rich output --don't need to do anything for @filler2

For named parameters, it can be DEFAULT keyword or ignored because order is irrelevant

exec test2
      @ID = @bob output,
      @mark = @rich output,
      @filler2 = DEFAULT

exec test2
      @mark = @rich output,
      @ID = @bob output

And the obligatory MSDN link


CTE Query to CONCAT text (over 10,000 rows) I am trying to construct a CTE query to loop through rows of data and concatenate text into one line for each SampleUserNumber.

Below is what I am working with:

CREATE TABLE #SampleTable
(
SampleRowID INT IDENTITY(1,1),
SampleUserNumber INT,
SampleLineNumber INT,
SampleTextLine VARCHAR(1000)
)

Here is my CTE Query:

Here is the Output:

The CTE Query works and the result is correct, but my main issue is the performance and the amount of records I have to process. Right now I am dealing with 100,000 records at a bare minimum.

Questions:

Have I written the query incorrectly to handle 100,000 records? Is there a more efficient way to write the CTE?
Is there another way more efficient and better way to do this? [looping would be out of the question unless I have no other choice]

Sorry for all the images. I figured it would be much easier to show everything rather than write it out here.

Thank you.

How does this approach do in comparison?

SELECT
  SampleUserNumber,
  CombinedMessage = STUFF(
    (SELECT N' ' + SampleTextLine FROM #SampleTable
       WHERE SampleUserNumber = t.SampleUserNumber
       ORDER BY SampleLineNumber
       FOR XML PATH, TYPE).value(N'.[1]',N'nvarchar(max)'),1,1,'')
FROM #SampleTable AS t
GROUP BY SampleUserNumber
ORDER BY SampleUserNumber;

It would probably be useful for your #temp table to have a clustered index on SampleUserNumber, SampleLineNumber or at least a non-clustered on those two columns that INCLUDEs SampleTextLine.

In SQL Server 2017 you would be able to use a much more straightforward approach, almost guaranteed to be faster:

SELECT SampleUserNumber,
  CombinedMessage = STRING_AGG(CONVERT(varchar(max),SampleTextLine), ' ')
                    WITHIN GROUP (ORDER BY SampleLineNumber)
FROM #SampleTable
GROUP BY SampleUserNumber
ORDER BY SampleUserNumber;

To avoid the CONVERT you could just make your #temp table column a MAX type.


Copy views from one database to other database I want to copy views from one database to other database I had near around 70-75 views.
How can it be possible to do?

select DB and Right Click

then this window will appear click Next

then select DB object which you want to generate script

select file path if you want to change

then Click Next 2 times and check path which you selected and pickup that script from there and run it


MySQL queries are 10 to 100 times slower after OS reboot I have a MySQL server running on Ubuntu. If I restart the MySQL, everything is fine. If I however restart the OS, my queries take anywhere from 10 times to 100 times as long. The only "solution" to the problem I have found is to run optimize on every table. Afterwards everything performs normal again. However, rebuilding the whole database after every OS reboot is obviously extremely painful and not a viable long term solution.

I reboot the OS by

stopping all programs querying the database
waiting for queries to finish executing
running stop mysql
making sure that the server stopped with ps -C mysql and ps -C mysqld
running reboot

If I leave out the OS reboot and just start up MySQL again, everything is fine.

Additional information:

The error log doesn't contain anything pointing to the issue, nor do I get any errors. Everything is just a magnitude or two slower.
All tables are affected. I am using InnoDB, but the very same problem existed when I was using MyISAM.
Usually MySQL uses 80% of the 8GB RAM, but after the OS reboot only about 1GB is used. CPU usage goes down from 30-80% of one core to about 1%.
The optimize query makes normal use of the full 80% RAM and CPU goes up to normal values, too.

Specs:

MySQL 5.5.35-0ubuntu0.12.04.2
Ubuntu 12.04.4 LTS
8GB RAM, 4 CPU cores

my.cnf file:

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]

local-infile=0

user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

innodb_file_per_table

innodb_autoinc_lock_mode = 0

innodb_thread_concurrency=8
innodb_buffer_pool_size=6G
innodb_log_file_size=1600M
innodb_additional_mem_pool_size=1M
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=0

innodb_read_io_threads=3000
innodb_write_io_threads=7000
innodb_io_capacity=10000

key_buffer_size     = 1024M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8

myisam-recover         = BACKUP

query_cache_limit   = 1M
query_cache_size        = 16M

log_error = /var/log/mysql/error.log

expire_logs_days    = 10
max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]

[isamchk]
key_buffer      = 16M

!includedir /etc/mysql/conf.d/

key_buffer_size is 1GB, that is about as much RAM being used after the reboot. Could that be connected?

Edit: When logging the query execution times per table, I have noticed that after repairing three of the four tables, sometimes the query times of the three repaired tables still go very high. So I am not completely sure that all tables break. Maybe it's just one table that is pulling down the performance of the whole server? However that doesn't really match the low % of used resources.

When you reboot the OS you remove all of the disk reads that have previously put into operating system disk cache (RAM). Once you've rebooted, the operating system will have to read the MySQL data from disk, which is several orders of magnitude slower than reading from cache (RAM).

Optimise "fixes" this as it causes MySQL to read all of the table data from disk, enabling the OS to cache the data.

Linux uses most free RAM as disk cache. You can see this using the free command:

[oracle@ora12c1 ~]$ free
             total       used       free     shared    buffers     cached
Mem:       4050844     840072    3210772          0      97268     349716
-/+ buffers/cache:     393088    3657756
Swap:      4063228          0    4063228
[oracle@ora12c1 ~]$

Restarting MySQL has no affect on this cache, as it is the operating system that has cached the data, not MySQL.

To "fix" your problem you'll have to force the OS to read the data from disk so that it will cache it. You can use the Unix dd tool to do this. For example:

dd if=/var/mysql/data/mytable.idb of=/dev/null

Tip: you can verify how much of a given file is in the disk cache with the vmtouch command. That command can also purge the file out of the filesystem cache, or force loading the file into the filesystem cache. See http://hoytech.com/vmtouch/