What to check before attempting a read-scale replica log backup? We have a read-scale replica that we take our backups from. The network connection is not the best and sometimes we lose sync. It comes back in a few minutes, and for our purposes we can live with it. However, during that time-frame, sometimes the log backups fail. I read this which finally explained why that might be happening:

http://www.centinosystems.com/blog/sql/designing-for-offloaded-backups-in-alwayson-availability-groups/

Basically it sounds as if LSNs get too far apart and then backups will fail. So I think it would be a good idea to check those LSNs with sys.dm_hadr_database_replica_states and take appropriate action. My question is what LSNs should I compare to delay a backup until it will not fail? There are 9 of them, and I have trouble going from the blog post to the DMV.

My goal is to delay the backup until it will not fail, so if this is not the best approach I am open to something better.

Thanks.

Log backups are important.  If they are scheduled to be run every 15 minutes, for example, that means they need to run every 15 minutes in order to meet you RPO.  Although it's nice to have the secondary running log backups, it is not nice if the log backups fail, and then you have a catastrophe at the primary site and you don't have recent log backups--you will not meet your RTO.

I would recommend that you run the log backups on the primary if possible, and have them copied to another location at the primary site, and also to the secondary's site.  This way you have the log backups available at the primary site, and this covers you in case you have a problem with the primary server, but the site survives.  If the entire site goes down, you have the latest log backups possible, given the network instability, at the secondary site.

I would inform management that due to the network instability, you cannot guarantee the RPO at the secondary site, and have them approve whatever plan you implement.


Restore PostgreSQL database (or db names) to new version, from files? Scenario in short:

a development laptop broke
old HDD is still readable
new laptop has PostgreSQL 9.4 instead of 9.1
both laptops use a flavor of Ubuntu Linux

Question 1: Is my understanding correct that in order to restore the old data, I would need a server with the same major+minor version as the one that wrote the old data directory? Since no 9.1 packages are available for my distribution, I would have to compile a v9.1.x server from source, copy over the old data, start the server and perform a normal pg_dump, which could then be restored to the new cluster?

Question 2: There were around 10-15 databases on the old laptop, but since it was a development machine, in theory all of the data should be replacable, apart from some local experiments. I'm thinking of just scrapping the old data, but I can't remember with 100% certainty what those databases were. Is there a way to extract some basic information (such as database names, maybe even sizes or timestamps) from the old data directory without running a 9.1 server?

Looks like you've figured out question 1 for yourself already (short answer: yes, use the latest 9.1.x release, and make sure the compile-time options are the same between the version on the old and new machine to be sure the data directory, and the machines should ideally be as similar as possible in order to be binary-compatible, e.g. both x86-64, similar glibc versions, etc.).

But about question 2:

  Is there a way to extract some basic information (such as database names, maybe even sizes or timestamps)

You can look under the "base" subdirectory of the data directory, and you should see something like this:

/datadir/base $ du -sh *
6.5M    1
6.1M    12292
6.5M    12297
 39M    3237152
6.3M    371336
 10M    4049006
 41M    4481732
6.7M    4691247
 10M    4927721
7.2M    4927722
7.4M    58068
  0B    pgsql_tmp

Each of those directories with an integer as the directory name represents a database inside my PostgreSQL cluster. The integers (OIDs) in the directory name match the oid you would see from a query like:

 SELECT oid, datname FROM pg_database;

if you had the server running. I don't know of a trivial way to determine the database name from those OIDs without having the server running, but at least you know how many databases there are and how big they should be.  And you should be able to figure out creation time too from checking stat. In my case, "1" was for "template1", "12292" was "template0", and the rest were various other databases.


If A is next to B, how to automatically assert that B is next to A I am working in Access 2010.

Let's say I have a database that tracks where packages sit in a warehouse.

Table: Packages
PackageID (PK)
PackageHeight
PackageWidth
PackageWeight
PackageOwner
(etc)

Now I want to show what packages each package is next to.

For example, A is next to B and M.

Table: PackageNextTo
ID (PK), PackageID (Packages.PackageID, FK), NextToPackageID(Packages.PackageID, FK)
1, A, B
2, A, M

So when I look up A, I find out it is next to B and M.

But when I look up B, I also want to find out it is next to A.... and when I look up M, I want to find out it is next to A and also, for example, next to T.  (but M and B are not necessarily next to each other).  Do I have to define these relationships explicity by adding records as follows:

Table: PackageNextTo
ID (PK, AutoIncrement), PackageID, NextToPackageID
1, A, B
2, A, M
3, B, A
4, M, A
5, M, T
6, T, M

OR is there a way to automatically say if x is next to y then y is next to x.

(OR a better way to structure this data altogether!!)

In human terms I see redundancy between records 1 and 3 above, and between 5 and 6 above, and so on.... but I don't know if this is redundant to a database or if the database needs this explicitly called out.

If the database requires this explicit, is there a way to run a macro or query that will automatically create (B,A) when (A,B) is defined?

In your proposed PackageNextTo table, "ID (PK)" appears to be a surrogate key, presumably an autonumber. While I'm not a surrogate key advocate myself, I know folk who are who avoid them in a relationship tables (junction tables, linking tables, whatever). You would certainly need to enforce the candidate key (compound) of (PackageID, NextToPackageID) anyhow and the presence of the ID seems to have confused at least one person answering here. Therefore, I suggest you omit it.

To ensure the 'next to' relationship are stored uni-directional, add a CHECK constraint (or Validation Rule) e.g.

CREATE TABLE PackageNextTo
(
 PackageID CHAR(1) NOT NULL
    REFERENCES Packages (PackageID)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
 NextToPackageID CHAR(1) NOT NULL
    REFERENCES Packages (PackageID)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
 CHECK (PackageID < NextToPackageID),
 UNIQUE (PackageID, NextToPackageID)
);

To show all 'next to' relationships as bi-directional, expand them using a VIEW (or create a soted Query object in your usual way):

CREATE VIEW AdjacentPackages
AS
SELECT PackageID AS PackageID_1,
       NextToPackageID AS PackageID_2
  FROM PackageNextTo
UNION
SELECT NextToPackageID AS PackageID_1,
       PackageID AS PackageID_2
  FROM PackageNextTo;

Note that CHECK and CREATE VIEW require ANSI-92 Query Mode.


Postgres cannot rename table because of AccessExcusiveLock I'm trying to rename table. But it had error:

LOG:  sending cancel to blocking autovacuum PID 11911
Detail:  Process 10377 waits for AccessExclusiveLock on database 21819191 relation 24338310
Statement:  ALTER TABLE member_orderinfo2 RENAME TO member_orderinfo

Please tell me how to fix it ?
Thanks you !

It is not error - it is log message so your request sent (and maybe still waits) cancel request to autovacuum. It is usually not any issue. Vacuum should be finished in next iteration.


SELECT inside trigger gives wrong result (it ignores the WHERE clause) When I run this code inside an after UPDATE trigger:

SELECT * FROM `stock_totals` WHERE `product_id` = 2 AND `stock_place_id` = 3 INTO OUTFILE 'query3.csv'
        FIELDS TERMINATED BY ','
        ENCLOSED BY '"'
        LINES TERMINATED BY '\n';

I get this into query3.csv: (field order: product_id, stock_place_id, date, quantity)

"1","3","2020-01-01","42"

Notice how the resulting row does not match the WHERE clause: the stock_place_id is correct, but the product_id is wrong (I got 1, but it should be 2).

When the same query is ran alone, I get an empty table which is the expected result given my data:

+------------+----------------+------------+----------+
| product_id | stock_place_id | date       | quantity |
+------------+----------------+------------+----------+
|          1 |              1 | 2020-01-01 |      -42 |
|          1 |              3 | 2020-01-01 |       42 |
+------------+----------------+------------+----------+

I'm running MariaDB version 10.4.12 and innodb_version 10.4.12 on Arch Linux.

The full query for the stock_totals table is:

CREATE TABLE `stock_totals` (
 `product_id` int(11) NOT NULL,
 `stock_place_id` int(11) NOT NULL,
 `date` date NOT NULL,
 `quantity` int(11) NOT NULL,
 PRIMARY KEY (`product_id`,`stock_place_id`,`date`),
 KEY `fk_stock_totals_stock_place1_idx` (`stock_place_id`),
 CONSTRAINT `fk_stock_totals_product1` FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
 CONSTRAINT `fk_stock_totals_stock_place1` FOREIGN KEY (`stock_place_id`) REFERENCES `stock_place` (`place_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

I've just tested this with MariaDB 10.4.12 with innodb_version 10.4.12 on Fedora Linux, but I don't see the problem you have - I get an empty query3.csv file. The only difference I have (besides the Linux distro) is that I don't have your foreign key constraints since I don't have those tables.

Are you sure you're looking at the correct output file?

Note also that you will get a warning when you run the query or create a trigger with that query:

SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

So you could try moving the FROM and WHERE clauses to the end of the statement. I get the same result with this, just without the warning.

You can also try enumerating the columns in the SELECT instead of SELECT *, just to see if it changes anything.

This is my trigger:

CREATE TRIGGER trigger1 AFTER UPDATE ON stock_totals
  FOR EACH ROW
    SELECT * FROM `stock_totals`
    WHERE `product_id` = 2 AND `stock_place_id` = 3
    INTO OUTFILE '/tmp/query10.csv'
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n';


SQL Join / union question [beginer] I have two tables, shown below, with the following columns:

[table #Completed]
Client_ID, ClientName, FirstDeliveryStatus, FirstDeliveryDate

[table #Planned]
Client_ID, ClientName, NextDeliveryStatus, NextDeliveryDate

As you can see clients have some completed & planned deliveries. I'm trying to merge both tables to show for each patient :

Client_ID, ClientName, FirstDeliveryStatus, FirstDeliveryDate, NextDeliveryStatus, NextDeliveryDate

I have tried to select data from table Completed and outer/inner join table Planned but it does not realy work for me. Main issue is row 6[Ashton] who have never had any completed deliveries & once I join details about his planned delivery - i'm getting nulls on Client_ID & ClientName

I would expect to see output as per below visualization

Any help would be appreciated

You have to first decide: Which table do I want to return all records for?

The answer for you sounds like "Client". So start with your client table:

SELECT ClientID, ClientName
FROM Client

Then ask: Which tables do I want to add?

The answer for you sounds like "Completed" and "Planned". So join them to Client:

SELECT c.ClientID, c.ClientName
FROM Client AS c
JOIN Completed AS comp ON c.ClientID = comp.ClientID
JOIN Planned AS p ON c.ClientID = p.ClientID

Now ask: Do I want to return all fields from my Client table, or only those that have matching records in both tables?

The answer for you appears to be: "All records from client and any matching from other tables". So for this, outer join to the tables from your base and return the columns you want:

SELECT c.ClientID, c.ClientName, comp.FirstDeliveryStatus, comp.FirstDeliveryDate, p.NextDeliveryStatus, p.NextDeliveryDate
FROM Client AS c
LEFT OUTER JOIN Completed AS comp ON c.ClientID = comp.ClientID
LEFT OUTER JOIN Planned AS p ON c.ClientID = p.ClientID

Now if you only wish to have records which have a record in Completed or Planned tables, add in a filter:

SELECT c.ClientID, c.ClientName, comp.FirstDeliveryStatus, comp.FirstDeliveryDate, p.NextDeliveryStatus, p.NextDeliveryDate
FROM Client AS c
LEFT OUTER JOIN Completed AS comp ON c.ClientID = comp.ClientID
LEFT OUTER JOIN Planned AS p ON c.ClientID = p.ClientID
WHERE p.ClientID IS NOT NULL OR comp.ClientID IS NOT NULL


What are the pros and cons of using the Sphinx Storage Engine? What are the pros/cons of using the Sphinx Storage Engine in MySQL (doc) over the normal api?

Pros

If there is no Sphinx API for the language you are using SphinxSE allows you to do searches through MySQL.
If you are doing background processing and storing the search results in your database SphinxSE allows you to move the data from the searchd service directly into MySQL instead of going through an application layer.

Cons

You have to compile SphinxSE into MySQL.
Using SphinxSE requires some additional setup besides the basic searchd and indexer configuration.
(This one is more my opinion) Searching through the API is more intuitive than using the SphinxSE SQL syntax.


Getting 30 minute slots between time I have a Start Datetime and a End Datetime.

Eg: 10:00 am - 12:00 pm

I have to create 4 slots between the, each ranging for 30 minutes.

Eg:
10:00 am-10:30 am
10:30 am-11:00 am
11:00 am-11:30 am
11:30 am-12:00 pm

This 30 minutes can vary and is not a constant. I have tried a few things but they don't seem to work. Can someone please help. Thank you.

I have tried this but i only get the slot difference not the  slotfrom-slotto

with cte as
(
select   convert(datetime,CONVERT(VARCHAR(15), getdate(), 112)) as DATE
union all
select   c.DATE+'00:20:00' as QuarterTime
from cte c
where c.date<dateadd(minute,1422,convert(datetime,CONVERT(VARCHAR(15), getdate(), 112)))
)
select * from cte

My idea is not far from yours.

DECLARE @dtStart AS DATETIME ='20151028 10:00:00'
        ,@dtEnd AS DATETIME = '20151028 12:00:00'
        ,@iInterval AS INT = 30;  --30 min interval

WITH aCTE
AS(
    SELECT
        @dtStart AS StartDateTime,
        DATEADD(MINUTE,@iInterval,@dtStart) AS EndDateTime
    UNION ALL
    SELECT
        DATEADD(MINUTE,@iInterval,StartDateTime),
        DATEADD(MINUTE,@iInterval,EndDateTime)
    FROM aCTE
    WHERE
        DATEADD(MINUTE,@iInterval,EndDateTime) <= @dtEnd
)

SELECT
    -- 10:00:00 AM
    CONVERT(VARCHAR(10),StartDateTime,108)
    + ' ' + RIGHT(CONVERT(VARCHAR(30), StartDateTime, 9), 2)
    + ' - ' +
    -- 10:30:00 AM
    CONVERT(VARCHAR(10),EndDateTime,108)
    + ' ' + RIGHT(CONVERT(VARCHAR(30), EndDateTime, 9), 2) AS Result
FROM aCTE

And the output is:

Result
10:00:00 AM - 10:30:00 AM
10:30:00 AM - 11:00:00 AM
11:00:00 AM - 11:30:00 AM
11:30:00 AM - 12:00:00 PM


SQL Server keep reverting to inefficient plan (Clustered index Scan) once a week I have a very simple query:

INSERT INTO #tmptbl
SELECT TOP 50 CommentID --this is primary key
FROM Comments WITH(NOLOCK)
WHERE UserID=@UserID
ORDER BY CommentID DESC

against this table:

CREATE TABLE [dbo].[Comments] (
    [CommentID] int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
    [CommentDate] datetime NOT NULL DEFAULT (getdate()),
    [UserID] int NULL ,
    [Body] nvarchar(max) NOT NULL,
--a couple of other int and bit cols, no indexes on them
)

I have a simple index on the UserID column (no cols included) and everything works just fine and super-fast.

But once every 5-8 days I see timeouts in that part of the application. So I go to investigate in the Query Store and I see that the server stops using my index and reverts to a stupid "clustered scan". Removing the temp table doesn't help.

In order to resolve this - I reset the plan cache for this particular query (just for the record here's how I do this)

select plan_handle FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt
where text like '%SELECT TOP 50 CommentID FROM hdComments%'
--blahblahblah skipped some code
DBCC FREEPROCCACHE (@plan_handle)

And then starts working normally again.

Execution plans: slow fast

I've been scratching my head for days now... Any ideas?

Should SQLRaptor's answer not work out for you, one other drastic thing you can try is using the query hint FORCESEEK. This essentially forces the optimizer to always use a plan that does an index seek instead of an index scan (if possible).

One reason it's not a first go-to is because it limits the number of query plans that the optimizer can choose to use and in certain cases will error out by saying no plan available for that query hint. Typically query hints are more of a last resort bandaid fix (except in specific edge cases) but arguably less drastic than running the DBCC FREEPROCCACHE command and probably less drastic than always recompiling the query with the OPTION RECOMPILE hint from KumarHarsh's answer too.

(This ended up being the best solution for one particular scenario I recently ran into with a table that had billions of records and the optimizer was always trying to use a clustered index scan but there was a nonclustered index that was more applicable to the query and was actually always faster as a seek.)

See the FORCESEEK section of the Microsoft doc for more information: https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15


SQL Server 2008: How to send an email when a step in a SQL Server agent job fails, but overall job succeeds I have a SQL Server job with 6 steps.  Steps 5 and 6 must be run regardless of any failures in the first four steps, so these first four jobs are set to skip to step 5 if they fail.

However, if steps 5 and 6 then succeed, the whole job is regarded as a success. I have an email notification set up for the job failure, but I do not receive an email if any of the first four steps fail due to the overall job being considered a success. I would like this to happen.

It would not be ideal to split out the first four steps into a separate job, as they must be completed before steps 5 and 6 begin.

Please can anyone give me advice to solve this problem so that:

Steps 5 and 6 run even when any of steps 1-4 fail.
Steps 5 and 6 begin strictly not before steps 1-4 complete.
When any of steps 1-4 fail, an email notification is sent indicating the step that failed.

Thanks very much in advance for your help.

You could create a Stored Proc for steps 1-4, with error handling that notifies you via email on failure.

Something like:

CREATE PROCEDURE TestEmailOnFail
AS
BEGIN
    BEGIN TRY
        /*
            Perform some action that might fail
        */
        SELECT 0/0; --THIS WILL FAIL
    END TRY
    BEGIN CATCH
        DECLARE @subject nvarchar(max) = 'Job Failure Notification';
        DECLARE @body nvarchar(max) = 'TestEmailOnFail Job Failed'
            + CHAR(10) + CHAR(13) + 'Error Number:  ' + CAST(ERROR_NUMBER() AS nvarchar(max))
            + CHAR(10) + CHAR(13) + 'Error Message: ' + ERROR_MESSAGE();
        DECLARE @to nvarchar(max) = 'somebody@email.com';
        DECLARE @profile_name sysname = 'SQLMailProfileName';
        EXEC msdb.dbo.sp_send_dbmail @profile_name = @profile_name,
            @recipients = @to, @subject = @subject, @body = @body;
    END CATCH
END

I just tested this using my SQL Mail Profile, and my email address and got the following email:

TestEmailOnFail Job Failed
Error Number:  8134
Error Message: Divide by zero error encountered.


Like UNPIVOT in SQL! How to change the way that cols orders it self?

If we have a table like this

a        b        c
1        xx       xy
2        xy       yx
3        yy       xx

Since both of b and c are same type I would like to aggregate them in a one col call E respectively

a        E
1        xx
1        xy
2        xy
2        yx
3        yy
3        xx

Is that possible using MYSQL 5.7 ?

You can just do it with a UNION ALL (you may or may not need the ORDER BY part, depending on your use-case):

SELECT
    a, b as `E`
FROM
    a
UNION ALL
SELECT
    a, c as `E`
FROM
    a
ORDER BY
    a ;

You can check it here.


how to use index to speed up sorting in postgres I am using postgres 9.4.

The messages has the following schema:
messages belongs to feed_id, and has posted_at, also messages can have a parent message (in case of replies).

                    Table "public.messages"
            Column            |            Type             | Modifiers
------------------------------+-----------------------------+-----------
 message_id                   | character varying(255)      | not null
 feed_id                      | integer                     |
 parent_id                    | character varying(255)      |
 posted_at                    | timestamp without time zone |
 share_count                  | integer                     |
Indexes:
    "messages_pkey" PRIMARY KEY, btree (message_id)
    "index_messages_on_feed_id_posted_at" btree (feed_id, posted_at DESC NULLS LAST)

I want to return all messages ordered by share_count, but for each parent_id, I only want to return one message. ie, if multiple messages have the same parent_id, then only the latest one (posted_at) is returned. The parent_id can be null, messages with null parent_id should all return.

The query I used is:

WITH filtered_messages AS (SELECT *
                           FROM messages
                           WHERE feed_id IN (7)
                           AND (posted_at >= '2015-01-01 04:00:00.000000')
                           AND (posted_at < '2015-04-28 04:00:00.000000'))
    SELECT *
    FROM (SELECT DISTINCT ON(COALESCE(parent_id, message_id)) parent_id,
                          message_id,
                          posted_at,
                          share_count
          FROM filtered_messages
          ORDER BY COALESCE(parent_id, message_id), posted_at DESC NULLS LAST
         ) messages
    ORDER BY share_count DESC NULLS LAST, posted_at DESC NULLS LAST;

Here's the http://sqlfiddle.com/#!15/588e5/1/0, in the SQL Fiddle, I have defined the schema, the exact query, and the expected result.

But the performance of the query is slow once the messages table gets big. I tried add multiple sorting indexes, but it does not seem to use the index.
Here's the explain: http://explain.depesz.com/s/Sv2

How can I create a correct index?

Query
This query should be substantially faster in any case:
SELECT parent_id, message_id, posted_at, share_count
FROM   messages
WHERE  feed_id = 7
AND    posted_at >= '2015-01-01 4:0:0'
AND    posted_at <  '2015-04-28 4:0:0'
AND    parent_id IS NULL  -- match index condition
UNION ALL
(
SELECT DISTINCT ON(parent_id)
       parent_id, message_id, posted_at, share_count
FROM   messages
WHERE  feed_id = 7
AND    posted_at >= '2015-01-01 4:0:0'
AND    posted_at <  '2015-04-28 4:0:0'
AND    parent_id IS NOT NULL  -- match index condition
ORDER  BY parent_id, posted_at DESC NULLS LAST
)
ORDER  BY share_count DESC NULLS LAST, posted_at DESC NULLS LAST;

The CTE does nothing here that a plain subquery could not deliver also. And a CTE introduces an optimization barrier since it is executed separately and its result is materialized.

You have one more subquery-level than you actually need.

The expression (COALESCE(parent_id, message_id) is not compatible with a plain index, you would need an index on that expression. But that may not be very useful either, depending on data distribution. Follow my links below for detailed information.

Splitting the simple case of parent_id IS NULL into a separate SELECT may or may not deliver the optimum. Especially not, if that's a rare case anyway, in which case a combined query with an index on (COALESCE(parent_id, message_id) may perform better. Other considerations apply ...

Indices
Especially when supported with these indices:
CREATE INDEX messages_idx_null ON messages (
  feed_id
, posted_at DESC NULLS LAST
, share_count DESC NULLS LAST
, parent_id, message_id
)
WHERE parent_id IS NULL;

CREATE INDEX messages_idx_notnull ON messages (
  feed_id
, posted_at DESC NULLS LAST
, share_count DESC NULLS LAST
, parent_id, message_id
)
WHERE parent_id IS NOT NULL;

The two partial indices cover the whole table together and are about the same size together as a single total index.
The last two columns parent_id, message_id only make sense if you get index-only scans out of it. Else remove them from both indices.
SQL Fiddle.
Depending on missing details, DISTINCT ON may or may not be the best query technique for the purpose. Reade detailed explanation here:

Select first row in each GROUP BY group?

And possibly faster alternatives here:

Optimize GROUP BY query to retrieve latest record per user


MySQL : How to know if Trigger was fired Someone can help me please , I want to log error about triggers in MySQL 5.5
I'll like to know if it's possible to know if a trigger was'nt fired ?

You can do it the hard way by enabling MySQL logging (all SQL queries will be logged). I'm doing it when I'm trying to debug & understand complex code base with big amount of SQL queries, with SQL procedures, events & triggers. It's ugly, but it works.

https://support.plesk.com/hc/en-us/articles/213374189-How-to-enable-MySQL-logging-

How to Log All mysql queries into log file?

Debian-based systems:
/etc/mysql/my.cnf

[mysqld]
# Set Slow Query Log
long_query_time = 1
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
log_queries_not_using_indexes = 1

# Set General Log
general_log = on
general_log_file = /var/log/mysql/mysql-general.log


Not Able to Increase InnoDB Buffer Pool Size in Mysql I am facing a very rare problem with my DB. I have Windows Server with 64 GB RAM but I am not able to increase innodb buffer pool size in mysql.

Current InnoDb buffer pool Size : 1.6G

When I increase it and restart Mysql then it throws Error :

161026  0:52:30 [Note] Plugin 'FEDERATED' is disabled.
161026  0:52:31  InnoDB: Initializing buffer pool, size = 2.6G
161026  0:52:31  InnoDB: Error: cannot allocate 2816491520 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 22604620 bytes. Operating system errno: 8
InnoDB: Check if you should increase the swap file or
InnoDB: ulimits of your operating system.
InnoDB: On FreeBSD check you have compiled the OS with
InnoDB: a big enough maximum process size.
InnoDB: Note that in most 32-bit computers the process
InnoDB: memory space is limited to 2 GB or 4 GB.
InnoDB: We keep retrying the allocation for 60 seconds...
161026  0:53:31InnoDB: Fatal error: cannot allocate the memory for the buffer pool
161026  0:53:31 [ERROR] Plugin 'InnoDB' init function returned error.
161026  0:53:31 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
161026  0:53:31 [ERROR] Unknown/unsupported table type: INNODB
161026  0:53:31 [ERROR] Aborting

I dont have 32 bit OS but my MYSQL is 32 bit .  I have 64 bit OS on my Windows server.

Memory space is limited to 2 GB or 4 GB. For 32 bit Mysql but I am not able to make it 2 GB, my Innodb is only 1.6 GB  .

Increasing size of Innodb is very necessary because i am getting following error in my error log :

MySql.Data.MySqlClient.MySqlException: Can't create a new thread (errno 12); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug

What I have Already Tried :

I confirmed that My my.ini has no issue .
I restrted My mysql many times but innodb size not increasing.
At last I got from somewhere that Memory allocation to Mysql in my OS is very less thats why OS is not increasing its innodb size.
I increased virtual memory of windows. It was 8GB before and I increased to to 16 GB. ( Did this change just now and dont know its result because it requires server restart and before 7 hours I cant restart my Server) - Do you think this can solve my problem ?

MySQL Version : 5.1

If anyone know the solution then let me know, i will be very thank ful to you.

Since the OS you are using is 64 bit, you need to make sure MySQL version you are using is 64 bit not 32 bit, how to get those information:

show variables like 'version_compile_machine';

If MySQL version is 32 bit then you will have results like

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| version_compile_machine | i386  |
+-------------------------+-------+
1 row in set (0.00 sec)

Note MySQL 32 bit has limitation, you can not increase the memory to more than 1.8 to 2G.

If MySQl version is 64 bit then you will have the following results

    +-------------------------+---------+
    | Variable_name           | Value   |
    +-------------------------+---------+
    | version_compile_machine | x86_64  |
    +-------------------------+---------+
    1 row in set (0.00 sec)


Unknown System SPID blocking user SPIDs We are performing a migration of 3rd party data and it is hanging.

When we investigate we see SPID 38 (a system/background SPID) is the blocking user SPIDs trying to do the migration. Unfortunately, the SQL_Text from sp_whoisactive (or even activity monitor) is NULL for SPID 38. The wait_info is PREEMPTIVE_OS_GENERICOPS.

Is there anyway to tell what 38 is trying to do?

It's using a Windows-based tool the 3rd party has given us. I took a backup of the data, restored it on this new server (so the database and its contents are accessible and working). Then we run this tool to do the upgrade to their new version of the software and that is where we are getting the blocks.

sp_WhoIsActive @get_locks = 1 returns a really long XML (4k characters too long to post).

I decided to look at the sys.sysprocesses table and found that FT_CRAWL (looking in the CMD field) was running on the blocking spid.

I guess a restored database starts crawling right away? Anyways, I shut off the Full Text Daemon and the blocking ceased. We finished the upgrade and then turned the Daemon back on and all was well in the world.

I have emailed Adam Mechanic to see if the CMD field is used in his sp_WhoIsActive, as I think that would be very beneficial in this type of situation.


Best Indexing for Performance - MySQL I have a question related performance DB MySQL. I have querying like this with big data inside :

SELECT
       *
FROM
       tableA a
       INNER JOIN tableB b ON a.id = b.id AND a.code = b.code
       INNER JOIN tableC c ON a.uniq = c.uniq
WHERE
       a.date = '2020-01-01'
       AND a.type = 'X'
       AND b.merk =  'New';

My question is, what is better using single index or multi-column index?

Ex :

Index in tableA (Example1) :

idx1 (merk, code, date, type)
idx2 (date)
idx3 (merk)
idx4 (code)
idx5 (type)

Index in tableA (Example2) :

idx1 (merk, code, date, type)
idx2 (merk, code)
idx3 (date, type)

What is better? Example1 or Example2? And please explain it.

Notes : maybe tableA will got varian WHERE on another query for future

Thank you

The engine will only be able to make use of one index per table so while the other indexes may help you in the future with other queries, their benefit will be limited in assisting you with this particular query.

Given what we know about your query and limited knowledge of the table and the insert pattern, I would suggest the following.

Table A - (Date, Type)
Table B - (merk, ID, Code)
Table C - (Uniq)

Of course, if you were able to limit the result set columns (getting rid of the SELECT *) then further columns in the index could be of benefit.

These indexes above will allow the engine to seek quickly and get quickly to the rows that it wants.

I do have one question about the "merk" on Table B.  If that is something like a status that changes then I would not index on it, leaving just an index on ID and Code.  If the value of the field changes frequently then it is usually a poor index candidate as it will cause much fragmentation.


.mdf equivalent for mysql (TOAD) What is 'SQL Server Database Primary Data File (.mdf)' equivalent for mysql.

I am using 'Toad for mysql' (Vesion 7.7) tool

MYSQL usually comes with MyISAM/INNODB databases.

For MyISAM the files are:

.MYD for data files
.MYI for index files
.frm for table format files

InnoDB uses a single container file for the data and .frm for the definition and .ibd for the indexes.

Are you trying to load data from SQL Server .MDF into MySQL DB? In simple words are you tring to migrate from SQL server to MYSql?

Regards.


postGIS schema qualifier - best practice There are a number of cases where the EXTENSION for postGIS is created iwth a schema qualifier.

CREATE SCHEMA postgis;
CREATE EXTENSION PostGIS WITH SCHEMA postgis;

why is that?  what are the tradeoffs in using the schema?

A schema is like a namespace in programming. It lets you have different things with the same name.

For example, you might have a function foo() in your public schema. If PostGIS has a function foo() as well, that wouldn't work without name qualification.


mySQL running on 100% CPU both on RDS and EC2 How do we troubleshoot an issue with Amazon wherein my database queries (PHP/MySQL) are eating 100% CPU most of the day?

Traffic=200 connections average
mySQL version = 5.5.40

The following specific query seems to be the culprit with each query being sorted. (Most of the time, the status is Sorting Result).
SELECT reports . * , domains.name AS dom_name, publisher.pub_name
FROM reports, domains, publisher
WHERE reports.domain_id = domains.dom_id
AND publisher.pub_id = reports.pub_id
AND reports.rep_type =  'N'
AND reports.pub_id
IN ( 3 )
AND reports.rep_status =  'Y'
ORDER BY reports.rep_pub_date DESC
LIMIT 1200 , 100

The database contains roughly 100 tables, and the biggest table is reports with roughly 3 GB size and 2,45,00 rows.
Based on the developers demands, we downgraded from being on MySQL 5.6 on RDS to mySQL 5.5 on EC2, situation remains the same. (4 CPU/15 GB RAM)
How do we troubleshoot the issue? Is it related to queries or is it inherently slow on EC2 and thereby we need to upgrade the hardware?

Your question is about troubleshooting, not about 'fixing' the CPU issue.

Nearly all CPU issues can be traced to bad/missing indexes or poorly written queries.

Do you have the slowlog turned on?  With long_query_time = 2?  Can you run pt-query-digest?  Well, probably you said no to all of those.  Pursue them for future use.

Can you do SHOW FULL PROCESSLIST?  Probably.  Look at the queries that don't say "Sleep" and don't look like they have something to do with replication.  They will probably be the naughty queries.

Show us the naughtiest query, together with SHOW CREATE TABLE for the table(s) that it uses.  Also provide EXPLAIN SELECT ... so we can see how the query is being performed.

If you are lucky, we will say "add INDEX(...)" or "change the WHERE clause in this way...".  If you are less lucky, the solution could be more complex.

(None of the specs you mentioned -- ram, cores, #tables, table size, etc., are important to the question.  The version might be important because the solution might involve a 'new' feature.)

Since you have provided the SELECT, I can make some guesses and proceed without the rest of the data.

Keeping junk in the table and filtering them out hurts performance (='N', ='Y'), but I will live with that for the moment.

INDEX(rep_type, rep_status, pub_id, rep_pub_date) might make things run a lot faster.  But...  The speedup will only work (I think) when the IN clause has only one item.

OFFSET 1200 means that 1200 rows have to be fetched, then tossed.  Let's work on decreasing that overhead...

(Note that JOIN...ON is a better way to write JOINs.)

Oops, I need the PRIMARY KEY of reports; I'll assume it is rep_id.

SELECT  r2.* , d.name AS dom_name, p.pub_name
    FROM
      ( SELECT  rep_id
            FROM  reports
            WHERE  rep_type = 'N'
              AND  pub_id IN ( 3 )
              AND  rep_status = 'Y'
            ORDER BY  rep_pub_date DESC
            LIMIT  1200 , 100
      ) r1
    JOIN  reports AS r2  USING(rep_id)
    JOIN  domains AS d   ON d.dom_id = r2.domain_id
    JOIN  publisher AS p ON p.pub_id = r2.pub_id;

Some notes:
* The subquery looks only at reports, and does not have to haul around 1200 rows to be thrown away.
* Once the subquery is finished, the JOINs are doing only 100 rows each.
* This format will benefit from the above index.
* When the IN has multiple items, the index will work even better for the subquery than the original query.  But still not 'perfectly'.
* My index works well only because it handles all of the WHERE clause, plus all of the ORDER BY.

To get even better performance for "pagination" than you can get through OFFSET, see my blog: mysql.rjweb.org.  That blog also points out two bugs you possibly don't know you currently have.

Another issue... Do users really go as far as the 13th page?  Or is this a search engine crawler causing you grief?


Remote mysql only works from my computer, but no others I need a direct connection to a MySQL server on a remote web server. So I've added to cpanel access hosts list an IP of the computer I wish to connect to the remote web server. But I can't connect, tried: telnet remote IP 3306, but no luck. I've tried on several computers with no luck. Only from my computer, it works. Am I missing something?

UPDATE:

I've contacted hosting company and they confirmed that they have firewall on, but how on Earth I could connect from my computer then? I think it will remain a mystery...

you must be have user with host % and grant level like root user
You can test telnet or remote mysql from cpanel server if success networking or firewall issue is solved because your mysql server can access from other client. if not you must be check from networking level and firewall level probably default port mysql is blocked.


Merge & remove duplicates We have a few MySQL (InnoDB) tables with repeated data and I want to clean them up. Consider the following:

Table user_clubs

| id | user_id | name                   | function                 | description         | school_grade |
|----|---------|------------------------|--------------------------|---------------------|--------------|
| 1  | 10      | World Cultures Club    |                          | Cultural activities | Freshman     |
| 2  | 10      | World Cultures Club    |                          | Cultural activities | Sophomore    |
| 3  | 10      | World Cultures Club    |                          | Cultural activities | Senior       |
| 4  | 20      | National Honor Society |                          | Vounteer            | Second Year  |
| 5  | 30      | National Honor Society | Public Relations Officer | School Society      | 11th         |
| 6  | 30      | National Honor Society | Public Relations Officer | School Society      | 12th         |
| 7  | 40      | Student Council        | Sophomore Secretary      |                     | Sophomore    |
| 8  | 40      | Student Council        | Junior Secretary         |                     | Junior       |

We have duplicated rows for each school_grade. What I want to end up with is:

Remove the school_grade column, having all the school_grade values in a user_clubs_meta table
Remove unnecessary duplicates in the user_clubs table, keeping only the record which has a related user_clubs_meta record

Example:

record id 1,2 & 3 need to get reduced to a single row and a new user_clubs_meta row with meta_key=school_grades and meta_value=["Freshman","Sophomore","Senior"] needs to get added (id column omitted for simplicity)
id 4 can be left as-is
id's 5 & 6 needs to get consolidated & meta added
id's 7 & 8 can be left as-is

Where I need help:

Populate the user_clubs_meta table. I have the following query:

INSERT INTO user_clubs_meta (user_clubs_id, meta_key, meta_value)
SELECT id, 'school_grades', CONCAT('[', GROUP_CONCAT(CONCAT('"', school_grade, '"')), ']')
FROM user_clubs
GROUP BY user_id, name, function, description
HAVING COUNT(*) > 1;

Data looks good to me, but I'm not 100% positive if this gives me the correct result. If someone could confirm.
Remove the duplicate records from user_clubs. This is where I'm completely stuck. I guess basically do the opposite/diff of

SELECT id
FROM user_clubs
GROUP BY user_id, name, function, description
HAVING COUNT(*) > 1;

where the query would give me only a list of id's which I can remove from user_clubs unless the record has a related record in user_clubs_meta.

Any suggestion is highly appreciated.

The query for populating the users_club_meta table looks good to me except for two issues:

I am not entirely convinced about HAVING COUNT(*) > 1. If you want to create entries for all combinations of (user_id, name, function, description), remove that condition.

If you want to omit the groups consisting only of empty school_grade values, you can use this condition instead:

HAVING COUNT(school_grade) > 0

That is assuming that by "empty" you mean NULL. If you mean to exclude empty strings ('') as well, you could modify the above condition like this:

HAVING COUNT(NULLIF(school_grade, '')) > 0

You are selecting id in that query but the column is not in GROUP BY. It is true that MySQL allows you to do that but the manual also discourages that you do that in cases where the non-aggregated non-GROUP BY column has multiple values per group.

If you want predictable results, use an aggregate function, for instance MIN():

INSERT INTO
  user_clubs_meta (user_clubs_id, meta_key, meta_value)
SELECT
  MIN(id),
  'school_grades',
  CONCAT('[', GROUP_CONCAT(CONCAT('"', school_grade, '"')), ']')
FROM
  user_clubs
GROUP BY
  user_id, name, function, description
HAVING
  COUNT(school_grade) > 0
;

For the DELETE query, take the final draft of the INSERT statement's SELECT query keeping only MIN(id) in the SELECT clause and removing the HAVING clause:

SELECT
  MIN(id)
FROM
  user_clubs
GROUP BY
  user_id, name, function, description
;

That gives you the first ID of every unique combination of (user_id, name, function, description). That will be the rows you want to keep. All the others you want to remove. This is an anti-join, and there are various ways to implement it. In this case you can go with:

NOT IN:

DELETE FROM
  user_clubs
WHERE
  id NOT IN
  (
    SELECT MIN(id)
    FROM user_clubs
    GROUP BY user_id, name, function, description
  )
;

LEFT JOIN + WHERE IS NULL:

DELETE
  uc
FROM
  user_clubs AS uc
  LEFT JOIN
  (
    SELECT MIN(id) AS min_id
    FROM user_clubs
    GROUP BY user_id, name, function, description
  ) AS filter
  ON uc.id = filter.min_id
WHERE
  filter.min_id IS NULL
;

The left join method is known to work fast in MySQL but you should test for yourself in your environment to choose what works better for you.


Two different MYSQl instances on same machine? I have MySQL 5.6 installed (Linux).

/var/lib/mysql
/etc/my.cnf

I have a cold backup that I am recovering:

/var/lib/mysql_backup
/etc/mybackup.cnf

In April I was able to get the backup started and it had been running fine until I had to reboot my machine 2 days ago to release an unmount that wouldn't succeed.

Now, I attempt to restart that second instance and I get an error that doesn't make sense given that it was working for nearly 6 months nonstop before and simultaneous/in parallel with the other instance.

root@VENUS:/var/lib/mysql_backup# mysqld --defaults-file=/etc/mysql/mybackup.cnf --pid-file=/var/run/mysqld/mysqld_backup.pid
2013-10-19 15:48:07 22026 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Can't find file: 'plugin' (errno: 2 - No such file or directory)
2013-10-19 15:48:07 22026 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2013-10-19 15:48:07 22026 [Note] InnoDB: The InnoDB memory heap is disabled
2013-10-19 15:48:07 22026 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2013-10-19 15:48:07 22026 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-10-19 15:48:07 22026 [Note] InnoDB: CPU does not support crc32 instructions
2013-10-19 15:48:07 22026 [Note] InnoDB: Using Linux native AIO
2013-10-19 15:48:07 22026 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2013-10-19 15:48:07 22026 [Note] InnoDB: Completed initialization of buffer pool
2013-10-19 15:48:07 22026 [Note] InnoDB: Highest supported file format is Barracuda.
2013-10-19 15:48:07 22026 [Note] InnoDB: 128 rollback segment(s) are active.
2013-10-19 15:48:07 22026 [Note] InnoDB: Waiting for purge to start
2013-10-19 15:48:07 22026 [Note] InnoDB: 1.2.10 started; log sequence number 1642375387
2013-10-19 15:48:07 22026 [Note] Recovering after a crash using /var/log/mysql_backup/mysql-bin
2013-10-19 15:48:07 22026 [Note] Starting crash recovery...
2013-10-19 15:48:07 22026 [Note] Crash recovery finished.
2013-10-19 15:48:07 22026 [Note] Server hostname (bind-address): '192.168.1.70'; port: 3310
2013-10-19 15:48:07 22026 [Note]   - '192.168.1.70' resolves to '192.168.1.70';
2013-10-19 15:48:07 22026 [Note] Server socket created on IP: '192.168.1.70'.
2013-10-19 15:48:07 22026 [ERROR] Fatal error: Can't open and lock privilege tables: Can't find file: 'user' (errno: 2 - No such file or directory)

What am I missing here? Nothing has change from the first time it was running.

Since I am doing partial recovery on a damaged database (I did an iso image cold backup, but did not give the iso maker the proper options and ended up with DOS 8.1 filenames, when they should have been Joliet Long names) I must have previously used the option --skip-grant .

i used the command
    mysqld_safe --defaults-file=/etc/mysql/mybackup.cnf --skip-grant
and the database started up fine.

Now to continue recovering what I can.


Multi Replications Plan on 2 servers I need some advice about our new database deployment plan.

We have 6GB of total mysql data(all innoDB) and innoDB_buffer_pool is set to be 16GB
Ans we have 2 database servers.(plus 1 server for the nginx and php). Mysql Version is 5.6.20

Server #1 - 1 Qual Core CPU , 48GB ram
Server #2 - 2 Quad core CPUs, 96GB ram

We currently have 1 master for server #1, and 1 slave for server #2 and using mysqlnd_ms plugin to do real/write splitting for better performance.

But since we do have plenty of ram spaces we are seeking some ideas to boost our database performance up.

Our question is if we have plenty of ram space like this, would it be beneficial if we have multiple mysql replications each server and use mysqlnd_ms plugin to distribute traffics?

For instance,

for Server #1 - 1 replication slave and 1 master.
for Server #2 - 4 replication slaves
Plus load balance using mysqlnd_ms plugin.

We just assume it could bring some performace advantage, but this is unser production so we can't tweak these servers freely.

If this idea is meaningless, could you provide some ideas?

Let me give you a very broad piece of advise: I think you are trying optimisation in the wrong way. You are doing "We have spare memory, let's use it for something else" (which is otherwise very lucky and many people will envy you) instead of "let's identify our bottleneck and fix that with our resources".

I am not telling you that your idea is wrong, but in general, scaling out with slaves does not provide a better latency (and in many other cases, clustering it reduces it), but a better throughput (which if you were under high load, can sometimes mean better latency). I do not know if that is a problem that must be solved in your case. While having multiple mysql servers in the same machine is common, for example, in the hosting industry, they do not do it for performance, but for a cost-saving reason (consolidation). Also have into account that you may be more than doubling your maintenance/administration costs.

My real advice would be to identify your bottleneck: latency, throughput on where? Memory? Disk? Network? And try to use the extra resources in the best way- Are temporary tables being created? Can we use ramdisk for that? Are many queries too slow even if they are executed on memory? Can we setup a on-memory key-value cache system (memcache, by the way, integrated on MySQL 5.6-InnoDB) for caching results of frequent queries? Your write throughput is maximised? Maybe you need sharding. Your read throughput is maximised while your write throughput, network and disk bandwidth is low? Maybe your idea is ok. But you need metrics in order to support such an important architecture decision.


PostgreSQL equivalent of mysqld --bootstrap or mysqld --init-file I'm trying to automate initializing a postgresql data directory, with a user and a default database pre-created. I know I can do this by running postgresql backgrounded and using createdb and createuser commands, but I really want to avoid that as this is for a docker container and I just want postgres to be running in the foreground once done.

In MySQL I use --init-file /path/to/bootstrap.sql, which runs the statements in the file before the server accepts connections. You can also use --bootstrap, which temporarily spins up mysqld and runs statements from STDIN.

I can't find an equally straightforward way to do this in Postgres. I can see a --boot option to postgres, but when I try and use it I just get "invalid command line arguments", so I must be doing something wrong.

echo "CREATE USER bob WITH PASSWORD 'secure';" | postgres --boot postgres -D data
postgres: invalid command-line arguments

You probably want the single user mode: --single, as described in the documentation:

  When invoked in single-user mode from the shell, the user can enter
  queries and the results will be printed to the screen, but in a form
  that is more useful for developers than end users. In the single-user
  mode, the session user will be set to the user with ID 1, and implicit
  superuser powers are granted to this user. This user does not actually
  have to exist, so the single-user mode can be used to manually recover
  from certain kinds of accidental damage to the system catalogs

Note that initdb has be run to initialize an empty data directory before creating databases or users. That would be the equivalent of mysql_install_db if you prefer to look at  this from the MySQL analogy angle.


sys.dm_exec_sql_text returns NULL in dbid, objectid, and number columns, but not text This is something that's bugged me though has never caused any real issues as I can generally locate this information by other means, but can someone explain why NULL may be returned in the dbid, objectid, and number columns from the sys.dm_exec_sql_text DMV, yet yield results in the text column?  I understand why output would return inversely (e.g. all columns but text would yield data), but I've seen this behavior a number of times across a number of SQL Server versions and the documentation doesn't seem to imply this should be possible, or I'm just reading it wrong.

Here's an example of the query I'm running:

SELECT TOP 100
      t.*
    , s.*
    , c.*
FROM    sys.dm_exec_query_stats s
        LEFT JOIN sys.dm_exec_connections c
               ON c.most_recent_sql_handle = s.sql_handle
        CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t
WHERE s.execution_count > 1 AND DATEDIFF (second, creation_time, GETDATE()) > 0
        AND t.dbid IS NULL

Here's a sample of one of the results from the text column which implies this isn't related to temporary objects which is what I would normally guess to be the cause.

select * from [dbo].[Map_ProviderSpecialty]

What situation is occurring where these columns are returning NULL values?

This is by design as of now. There is a connect item about this problem which is closed as of now.

sys.dm_exec_query_stats DBID column NULL for dynamic SQL - by Theo Ekelmans

But at the end there is a comment:

  Thank you for the feedback! We will consider it for the next release
  of SQL Server.

There is also some workaround in the comment section and also as mentioned by @sp_BiltzErik in the comment above.


Mysql: how to get unique values in column that can have a value or 0 Mysql

on a simple table (all integers)
A B C

I want to query all rows where C is a distinct number or 0
for example:

0 1 4
4 5 6
6 5 0
5 6 0
7 5 4
8 5 6
9 5 0

Should give:

0 1 4
4 5 6
6 5 0
5 6 0
9 5 0

rows:

7 5 4
8 5 6

didnt match because in col C  4 and 6 already appear.
All the C=0 columns should appear.

Whats the best way to do a query that does distinct C or C=0 ?
the results order doesn't matter.


How to avoid using Merge query when upserting multiple data using xml parameter? I am trying to update a table with an array of values. Each item in the array contains information that matches a row in a table in the SQL Server database. If the row already exists in the table, we update that row with the information in the given array. Else, we insert a new row in the table. I have basically described upsert.

Now, I am trying to achieve this in a stored procedure that takes an XML parameter. The reason I am using XML and not table-valued param is because, doing the latter, I will have to create custom type in SQL and associate this type with the stored procedure. If I ever changed something in my stored procedure or my db schema down the road, I would have to redo both the stored procedure and the custom type. I want to avoid this situation. Besides, the superiority that TVP has over XML is not useful for my situation because, my data array size will never exceed 1000. This means I cannot use the solution proposed here: How to insert multiple records using XML in SQL server 2008

Also, a similar discussion here (UPSERT - Is there a better alternative to MERGE or @@rowcount?) is different from what I am asking because, I am trying to upsert multiple rows to a table.

I was hoping that I would simply use the following set of queries to upsert the values from the xml. But this is not going to work. This approach is just supposed to work when the input is a single row.

begin tran
   update table with (serializable) set select * from xml_param
   where key = @key

   if @@rowcount = 0
   begin
      insert table (key, ...) values (@key,..)
   end
commit tran

Next alternative is to use an exhaustive IF EXISTS or one of its variations of the following form. But, I reject this on the ground of being of sub-optimal efficiency:

IF (SELECT COUNT ... ) > 0
    UPDATE
ELSE
    INSERT

The next option was using Merge statement as described here: http://www.databasejournal.com/features/mssql/using-the-merge-statement-to-perform-an-upsert.html. But, then I read about issues  with Merge query here: http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/.  For this reason, I am trying to avoid Merge.

So, now my question is: is there any other option or a better way to achieve multiple upsert using XML parameter in SQL Server 2008 stored procedure?

Please note that the data in the XML parameter may contain some records that should not be UPSERTed due to being older than the current record. There is a ModifiedDate field in both the XML and the destination table that needs to be compared in order to determine if the record should be updated or discarded.

Whether the source is XML or a TVP does not make a huge difference. The overall operation is essentially:

UPDATE existing rows
INSERT missing rows

You do it in that order because if you INSERT first, then all rows exist to get the UPDATE and you will do repeated work for any rows that were just inserted.

Beyond that there are different ways to accomplish this and various ways to tweak some additional efficiency out of it.

Let's start with the bare minimum. Since extracting the XML is likely to be one of the more expensive parts of this operation (if not the most expensive), we don't want to have to do that twice (as we have two operations to perform). So, we create a temp table and extract the data out of the XML into it:

CREATE TABLE #TempImport
(
  Field1 DataType1,
  Field2 DataType2,
  ...
);

INSERT INTO #TempImport (Field1, Field2, ...)
  SELECT tab.col.value('XQueryForField1', 'DataType') AS [Field1],
         tab.col.value('XQueryForField2', 'DataType') AS [Field2],
         ...
  FROM   @XmlInputParam.nodes('XQuery') tab(col);

From there we do the UPDATE and then the INSERT:

UPDATE tab
SET    tab.Field1 = tmp.Field1,
       tab.Field2 = tmp.Field2,
       ...
FROM   [SchemaName].[TableName] tab
INNER JOIN #TempImport tmp
        ON tmp.IDField = tab.IDField
        ... -- more fields if PK or alternate key is composite

INSERT INTO [SchemaName].[TableName]
  (Field1, Field2, ...)
  SELECT tmp.Field1, tmp.Field2, ...
  FROM   #TempImport tmp
  WHERE  NOT EXISTS (
                       SELECT  *
                       FROM    [SchemaName].[TableName] tab
                       WHERE   tab.IDField = tmp.IDField
                       ... -- more fields if PK or alternate key is composite
                     );

Now that we have the basic operation down, we can do a few things to optimize:

capture @@ROWCOUNT of insert into temp table and compare to @@ROWCOUNT of the UPDATE. If they are the same then we can skip the INSERT
capture the ID values updated via the OUTPUT clause and DELETE those from the temp table. Then the INSERT doesn't need the WHERE NOT EXISTS(...)
IF there are any rows in the incoming data that should not be synced (i.e. neither inserted nor updated), then those records should be removed prior to doing the UPDATE

CREATE TABLE #TempImport
(
  Field1 DataType1,
  Field2 DataType2,
  ...
);

DECLARE @ImportRows INT;
DECLARE @UpdatedIDs TABLE ([IDField] INT NOT NULL);

BEGIN TRY

  INSERT INTO #TempImport (Field1, Field2, ...)
    SELECT tab.col.value('XQueryForField1', 'DataType') AS [Field1],
           tab.col.value('XQueryForField2', 'DataType') AS [Field2],
           ...
    FROM   @XmlInputParam.nodes('XQuery') tab(col);

  SET @ImportRows = @@ROWCOUNT;

  IF (@ImportRows = 0)
  BEGIN
    RAISERROR('Seriously?', 16, 1); -- no rows to import
  END;

  -- optional: test to see if it helps or hurts
  -- ALTER TABLE #TempImport
  --   ADD CONSTRAINT [PK_#TempImport]
  --   PRIMARY KEY CLUSTERED (PKField ASC)
  --   WITH FILLFACTOR = 100;

  -- optional: remove any records that should not be synced
  DELETE tmp
  FROM   #TempImport tmp
  INNER JOIN [SchemaName].[TableName] tab
          ON tab.IDField = tmp.IDField
          ... -- more fields if PK or alternate key is composite
  WHERE  tmp.ModifiedDate < tab.ModifiedDate;

  BEGIN TRAN;

  UPDATE tab
  SET    tab.Field1 = tmp.Field1,
         tab.Field2 = tmp.Field2,
         ...
  OUTPUT INSERTED.IDField
  INTO   @UpdatedIDs ([IDField]) -- capture IDs that are updated
  FROM   [SchemaName].[TableName] tab
  INNER JOIN #TempImport tmp
          ON tmp.IDField = tab.IDField
          ... -- more fields if PK or alternate key is composite

  IF (@@ROWCOUNT < @ImportRows) -- if all rows were updates then skip, else insert remaining
  BEGIN
    -- get rid of rows that were updates, leaving only the ones to insert
    DELETE tmp
    FROM   #TempImport tmp
    INNER JOIN @UpdatedIDs del
            ON del.[IDField] = tmp.[IDField];

    -- OR, rather than the DELETE, maybe add a column to #TempImport for:
    -- [IsUpdate] BIT NOT NULL DEFAULT (0)
    -- Then UPDATE #TempImport SET [IsUpdate] = 1 JOIN @UpdatedIDs ON [IDField]
    -- Then, in below INSERT, add:  WHERE [IsUpdate] = 0

    INSERT INTO [SchemaName].[TableName]
      (Field1, Field2, ...)
      SELECT tmp.Field1, tmp.Field2, ...
      FROM   #TempImport tmp
  END;

  COMMIT TRAN;

END TRY
BEGIN CATCH
  IF (@@TRANCOUNT > 0)
  BEGIN
    ROLLBACK;
  END;

  -- THROW; -- if using SQL 2012 or newer, use this and remove the following 3 lines
  DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
  RAISERROR(@ErrorMessage, 16, 1);
  RETURN;
END CATCH;

I have used this model several times on Imports / ETLs that either have well over 1000 rows or maybe 500 in a batch out of a total set of 20k - over a million rows. However, I have not tested the performance difference between the DELETE of the updated rows out of the temp table vs just updating the [IsUpdate] field.

Please note regarding the decision to use XML over TVP due to there being, at most, 1000 rows to import at a time (mentioned in the question):

If this is being called a few times here and there, then quite possibly the minor performance gain in TVP might not be worth the additional maintenance cost (needing to drop the proc before changing the User-Defined Table Type, app code changes, etc). But if you are importing 4 million rows, sending 1000 at a time, that is 4000 executions (and 4 million rows of XML to parse no matter how it is broken up), and even a minor performance difference when executed only a few times will add up to a noticeable difference.

That being said, the method as I have described does not change outside of replacing the SELECT FROM @XmlInputParam to be SELECT FROM @TVP. Since TVPs are read-only, you wouldn't be able to delete from them. I guess you could simply add a WHERE NOT EXISTS(SELECT * FROM @UpdateIDs ids WHERE ids.IDField = tmp.IDField) to that final SELECT (tied to the INSERT) instead of the simple WHERE IsUpdate = 0. If you were to use the @UpdateIDs table variable in this manner, then you could even get away with not dumping the incoming rows into the temp table.


mariadb-galera-server with Aria? With Galera now somewhat supporting MyISAM replication, does that also mean that it will replicate Aria databases with storage engine (which is more or less feature complete to MyISAM, and a drop in replacement). Are there any special configs I need to watch out for to make this work (such as wsrep_replicate_myisam=ON) ?

Apparently not, based on this recent post which suggests that they didn't open it up to non-transactional engines, but specifically for MyISAM... and even as such, it's still experimental (and still seems like a horrible idea except for the extremely limited case of the DML manipulation of the tables in the `mysql` database).
The article "5 Tips for migrating your MySQL server to a Galera Cluster" is over a year old by now, but it seems unlikely that this statement will ever be modified by much, because of the nature of how the internals operate:

Please be adviced that this is an experimental feature, and as explained above, will never really be able to work as well and perform as well as using InnoDB tables. It's dangerous in many other ways too: for example non-deterministic functions are not protected for in any way (ie it is even more dangerous than SBR in MySQL classic replication). Still, this feature has turned out to be helpful for some users, for example if applications do occasional updates on some of the MyISAM tables in mysql.* database, which of course can't (yet?) be migrated to InnoDB.
(emphasis added)

So apparently the real motivation for tying in to MyISAM wasn't because it was a good idea, but because that's the storage engine of the mysql grant tables, which does make quite a bit of sense depending on how you access them.
Yes, Aria seems generally a safer place to put your data than MyISAM but it's still not transactional like InnoDB/XtraDB, and I fail to understand why anybody wanting the resilience of a cluster would avoid InnoDB, which is, you know, sort of exactly what Galera is actually for... other than the one or two things MyISAM and Aria have that InnoDB doesn't... which are spatial and fulltext (pre-5.6) indexes.


Thwarting some awful parameter sniffing I've got this fairly simple reporting procedure that searches through inventory transaction data (stuff being received, moving around the warehouse, etc). The whole thing is listed below - I've only anonymized the procedure name. It's pulling data from Dynamics GP (explaining the awful table/field names), and an internal data warehouse.

CREATE PROCEDURE InvTrans
    @strItem varchar(20) = NULL,
    @strSN varchar(20) = NULL,
    @strStartDate date,
    @strEndDate date
WITH RECOMPILE
AS

SET @strItem = ISNULL(@strItem, '') + '%'
SET @strSN = ISNULL(@strSN, '') + '%'

SELECT TOP 10000
    d.unitcost AS cost,
    d.unitprice AS price,
    d.qty AS quantity,
    RTRIM(d.itemnmbr) as item,
    RTRIM(iv.ITEMDESC) AS itemdesc,
    d.txdate,
    RTRIM(d.serltnum) AS serltnum,
    d.doctype,
    RTRIM(d.docnumbr) AS docnumbr,
    RTRIM(d.refnumbr) AS refnumbr,
    d.glpostdt,
    RTRIM(d.username) AS username,
    d.lnseqnbr,
    RTRIM(d.from_place) AS from_place,
    RTRIM(d.to_place) AS to_place
FROM dw..dw_invtrans d
    INNER JOIN IV00101 iv
        ON d.itemnmbr = iv.ITEMNMBR
WHERE d.itemnmbr LIKE @strItem
    AND d.serltnum LIKE @strSN
    AND d.txdate BETWEEN CAST(@strStartDate AS datetime) AND CAST(@strEndDate AS datetime)
ORDER BY d.itemnmbr, d.txdate

There's obviously parameter sniffing going on, that much I can tell. The dw_invtrans table has 4,711,806 rows. There are non-clustered indexes on (itemnmbr, txdate) and (serltnum, txdate).

If I execute the procedure like this, it takes 17-20 seconds:

EXEC InvTrans @strItem = '', @strSN = '23-A', @strStartDate = '1/1/1999', @strEndDate = '2/21/2012'

If I execute it like this, it take less than 1 second, and returns the same results:

EXEC InvTrans @strItem = NULL, @strSN = '23-A', @strStartDate = '1/1/1999', @strEndDate = '2/21/2012'

As far as I can tell, when SQL Server sees one of the two item or serial number parameters is null, it generates an execution plan that ignores that filter completely (since the query uses it for a LIKE, it's assuming it won't match any rows, but the procedure turns it into '%'). If either of those parameters is an empty string, it considers that filter (which will end up matching every row, since it turns into '%'), thus hash-matching the small number of rows with a matching serial number to a totally unfiltered dw_invtrans (4.7 million rows).

What would be the best trick to have the execution plan ignore either of the two filters if it's null or a blank string? Bear in mind that I'm going to bind this to Reporting Services - normally I'd just have it dynamically select which query to run inside the procedure, but that often screws up Reporting Services' output column enumeration since it runs the procedure with FMTONLY on.

The sniff comes from the NULL or the empty string. The addition of % is ignored.
You can fix this one of 2 ways:

Use parameter masking, by using another variable internally, say @IstrItem
SET @IstrItem = ISNULL(@strItem, '') + '%'
Add OPTIMIZE FOR UNKNOWN hint to the query

Other observations:

Why not an index on all 3 columns? One of these is probably best
serltnum, txdate, itemnmbr or txdate, serltnum, itemnmbr
Do datatypes and length match? eg txdate is datetime, itemnmbr is varchar(20) etc
If not, indexes will be ignored
So many non-key columns will require a key lookup, or the indexes are being ignored
See Simple Talk's covering indexes article for more


Replication/log shipping to a standby site I need to replicate a SQL Server database on my standby site. Regarding Log Shipping, I have the following concerns:

Is there any data type or primary key dependency exists if I use log shipping?
Why I have to use network folders/directories. Why can I not use local folders to capture log files?
What about the password between the two databases i.e primary and standby as primary will connect to standby?
Is there any method whereby I can open my standby database in read only mode and logs are applied without interrupting or disconnecting my users?
I have a very large database i.e of 45 TB. So for disaster recovery solution what method do you suggest?
Please also mention the difference between "Log shipping" & "Always ON".

1. Is there any data type or primary key dependency exists if I use log shipping?

Log shipping works on database log backups. Any DML operation is captured in logs, so it is applied to secondary. No question of datatype or primary key dependency for logshipping to work.

  2. Why I have to use network folders/directories. Why can I not use local folders to capture log files?

Logshipping uses copy, restore jobs. Copy job copies log backups from primary to secondary and restore job restores them. So, in this case, assuming log shipping is between different instances of SQL, how can one job have visibility if it is local path?

  3. What about the password between the two databases i.e primary and standby as primary will connect to standby?

Copy job of primary should have write access and restore job should have read access.

  4. Is there any method whereby I can open my standby database in read only mode and logs are applied without interrupting or disconnecting my users?

No, it's not possible in log shipping. AlwaysOn does this by providing read only replicas. Further quote from MSDN on two options for log shipping:

From Using Secondary Servers for Query Processing

    There are two options for configuration when you place the secondary database in standby mode:

    You can choose to have database users disconnected when transaction log backups are being restored. If you choose this option, users will be disconnected from the database each time the log shipping restore job attempts to restore a transaction log to the secondary database. Disconnection will happen on the schedule you set for the restore job.

    You can choose not to disconnect users. In this case, the restore job cannot restore transaction log backups to the secondary database if there are users connected to that database. Transaction log backups will accumulate until there are no user connections to the database.

  5. I have a very large database i.e of 45 TB. So for disaster recovery solution what method do you suggest?

First you should have valid backups that are tested routinely. Then you need to ask yourself:

How much downtime can I afford?
Budget my business can spend?
Do I need secondary databases for reporting?

Based on above questions,you can see what are the total HA solutions available and what is best for me.

  6. Please also mention the difference between "Log shipping" & "Always ON".

There are lot of differences you can you see below.

What is better to use - database mirroring or log shipping for SQL Server?


performance tuning queries - practice I would like to find some materials and real life practice about performance tuning queries. Can anyone recommend some resources to read and study?

You can begin from reading on MSDN Query Tuning.

Initially to Begin with , find the Queries which needs to be tuned on basis of how bad they perform in terms of resource consumption or time they are taking to execute and then analyse their execution plans accordingly.

There are many free tools available out there apart from SQL server inbuilt tools like DTA etc, which makes the analysis go easy.

The best that I feel comfortable while doing query tuning is using SQL Sentry Plan Explorer.

You can use SQL Sentry’s Plan Explorer to get faster performance and tuning information from SQL Server query plans.

Also please read this excellent article Finding the causes of poor performance in SQL Server

Initially above should get you started. Apart from that you can always refer to SQL server Books Online


fn_dblog not showing up deleted table data I unintentionally deleted 4 rows from a table. I tried to fetch back the deleted data using a stored procedure (got from internet). However, I was able to see my data in the result of stored procedure. Then I updated the original schema of the table and remove the primary key before restoring the deleted data and again tried to run the stored procedure and found that fn_dblog is no more showing my deleted rows of that table.

All other deleted data from other tables are present in fn_dblog. How can I now fetch my deleted rows without using any external tool?
I am using SQL Server 2012.

Edited:
I have attached a snapshot as well of ApexSQL. The last 4 rows are my data but i am unable to recover, as the redo script says:

  00000088:0000011B:0002: ROW DATA NOT AVAILABLE FOR DELETE ON
  [UNKNOWN].[UNKNOWN(1394104007)]

I also asked at stack overflow the same question. No responses.

From Thomas LaRock at http://logicalread.solarwinds.com/sql-server-dbcc-log-command-tl01/#.VXbj_DYpDVk

"The fn_dblog() function (formerly known as the DBCC command) is one of several undocumented functions for SQL Server; it allows you to view the transaction log records in the active part of the transaction log file for the current database."

The fact that you can no longer find your data suggests that the desired part of the log is no longer in the active part of the transaction log file.

One way to get these 4 rows back would be to restore a copy of the database to a temporary database name (e.g. DbName_Disaster) at a point in time that contained these 4 rows. Then insert the 4 rows back into your production database and drop the DbName_Disaster database.

Or you could look for some database recovery tool that fits your need.  The prices and features vary.


how to GRANT EXECUTE on MySQL I updated the timezone on an AWS MySql instance, and tech support told me I also had to runt his command for each MySql user:

GRANT EXECUTE ON PyupROCEDURE `mysql`.`store_time_zone` TO 'some_user'@'localhost';

I tried running without selecting a database and got this error:

ERROR 1046 (3D000): No database selected

So then I ran:

use db2;
GRANT EXECUTE ON PyupROCEDURE `mysql`.`store_time_zone` TO 'some_user'@'localhpst';

And I got this error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON PyupROCEDURE `mysql`.`store_time_zone` TO 'some_user'@'localhost'' at line 1

I looked at the docs (http://dev.mysql.com/doc/refman/5.6/en/grant.html) and I don't see what would be wrong - here I want to grant access to a procedure and that seems to correspond to the docs:

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_specification [, user_specification] ...
    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
    [WITH {GRANT OPTION | resource_option} ...]

object_type: {
    TABLE
  | FUNCTION
  | PROCEDURE
}

And also this is how we created the procedure:

DELIMITER |
CREATE PROCEDURE mysql.store_time_zone ()
IF NOT (POSITION('rdsadmin@' IN CURRENT_USER()) = 1) THEN
SET SESSION time_zone = 'America/New_York';
END IF |
DELIMITER ;

Where did I go wrong? Thanks for any suggestions.

It is impossible to run this command:

GRANT EXECUTE ON `mysql`.`store_time_zone` TO 'user'@'%';

Here is why:

The EXECUTE grant exists at the global level
The EXECUTE grant exists at the database level
The EXECUTE grant does not exist at the table level
The EXECUTE grant does not exist at the column level

Here is how you can prove it:

User grants for MySQL exist in four(4) MyISAM tables

mysql.user (Global grants)
mysql.db (Database level grants)
mysql.tables_priv (Table level grants)
mysql.columns_priv (Column level grants)

If you run this query

mysql> select concat(table_schema,'.',table_name)
    -> from information_schema.columns
    -> where table_schema='mysql'
    -> and column_name='execute_priv';
+-------------------------------------+
| concat(table_schema,'.',table_name) |
+-------------------------------------+
| mysql.db                            |
| mysql.user                          |
+-------------------------------------+
2 rows in set (0.03 sec)

mysql>

Only mysql.user and mysql.db have columns in the mysql schema representing the EXECUTE privilege.

Therefore, these commands would work

# Global level EXECUTE privilege
GRANT EXECUTE ON *.* TO 'user'@'%';
# Database level EXECUTE privilege
GRANT EXECUTE ON `mysql`.* TO 'user'@'%';


How to convert minutes to hours using MYSQL? Personal I have this query that is returning the time in minutes for me. But I want this query to return in hours and minutes rather than in minutes. It's already tried using  FLOOR() function, but to no avail.

SQL

SELECT
   vencido.tickets_id,
   Tickets.name,
   Tickets.due_date,
   Tickets.solvedate,
   Cat.completename,
   TIMESTAMPDIFF(hour, Tickets.date, Tickets.solvedate) as Tempo
FROM
   glpi_vencidos as vencido
   INNER JOIN
      glpi_tickets as Tickets
      ON (Tickets.id = vencido.tickets_id)
   INNER JOIN
      glpi_itilcategories as Cat
      ON (Tickets.itilcategories_id = Cat.id)
WHERE
   Tickets.solvedate between STR_TO_DATE('01/06/2017', '%d/%m/%Y') and STR_TO_DATE('01/07/2017', '%d/%m/%Y')
   and vencido.situacao = 1

My result in minutes

You need DIV and % operators.

DIV is the integer division and % is the modulo function, returning the reminder:

SELECT 12345 DIV 60 AS hrs, 12345 % 60 as min;

UPDATE

You have to change your query such way:

SELECT
   . . . . .
   TIMESTAMPDIFF(MINUTE, Tickets.date, Tickets.solvedate) DIV 60 AS TempoH,
   TIMESTAMPDIFF(MINUTE, Tickets.date, Tickets.solvedate) % 60 AS TempoM
   . . . . .


Calculate Difference in Overlapping Time in PostgreSQL / SSRS I'm using PostgreSQL 9.2 and SSRS 3.0.  I'm trying to calculate the difference in non-overlapping time entries in a timesheet.
My data looks like this:

I would like this to return:

I can get the overlapping minutes 635, but am having trouble with the not overlapping, 605 minutes.

Working with this setup:

CREATE TABLE tbl (
   tbl_id     serial PRIMARY KEY
,  user_id    text NOT NULL
,  start_time timestamp NOT NULL  -- if values could be NULL ...
,  end_time   timestamp NOT NULL  -- ... it would be more complicated
);

Total duration without redundancy

Your numbers indicate you just want the total duration without counting same ranges of time multiple times. Not trivial, but can be done with pure SQL rather efficiently:

SELECT sum(duration) AS total_duration_without_redundancy
FROM  (
   SELECT island, max(end_time) - min(start_time) AS duration
   FROM  (
      SELECT start_time, end_time
           , count(gap) OVER (ORDER BY rn) AS island
      FROM  (
         SELECT start_time, end_time
              , (start_time > max(end_time) OVER w) OR NULL AS gap
              , row_number() OVER w AS rn
         FROM   tbl
         -- WHERE  user_id = 'User1'  -- just for a given user?
         WINDOW w AS (ORDER BY start_time, end_time DESC  -- longest range 1st
                      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
         ) sub1
      ) sub2
   GROUP  BY island
   ) sub3;

Explanation:

Beginning at subquery sub1, first order rows by start_time. While being at it, I actually use ORDER BY start_time, end_time DESC hoping to help performance a bit. But that's unimportant.
The custom frame definition (ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) allows to compute the maximum end_time for the previous row with the window aggregate function max() with . If the current start_time is later, we have a gap.
In sub2 count the gaps to form islands. (All rows with the same count of gaps live on the same island (can be aggregated to one gap-less range).
The rest is simple, in sub3 compute the size of each island (duration), and get the total in the outer SELECT.

Related:

Check if contiguous date interval exists

Only ranges without overlap

non-overlapping time entries like you wrote, can be interpreted in various ways. Excluding all ranges that overlap (consider only ranges of time that are covered exactly once) is hard to solve with pure SQL.

The special difficulty is that the "difference" of two ranges can produce two disjoint ranges if the one is a sub-range of the other, like the Postgres manual states for range functions:

  The union and difference operators will fail if the resulting range
  would need to contain two disjoint sub-ranges, as such a range cannot
  be represented.

I would therefore solve the problem procedurally. Iterate though rows ordered by start_time And compare each range to the remaining hopeful range from the last round.

This plpgsql function sums all non-overlapping time ranges (or parts of ranges):

CREATE OR REPLACE FUNCTION f_sum_no_overlap(OUT nol interval) AS
$func$
DECLARE
   a1 timestamp;  -- start and ...
   z1 timestamp;  -- ... end of current row
   a0 timestamp;  -- remaining candidate range ...
   z0 timestamp;  -- ... from previous row
   _id int;
BEGIN
nol := '0:0';     -- init!

FOR _id, a1, z1 IN
   SELECT tbl_id, start_time, end_time
   FROM   tbl
-- WHERE  user_id = 'User1'  -- or just for a given user?
   ORDER  BY start_time, end_time DESC  -- better take longest range first
LOOP
   IF    a1 >= z0 THEN               -- range starts later
   --       a0------z0
   --                  a1--z1
      nol := nol + (z0 - a0);
      a0 := a1;
      z0 := z1;

   ELSIF a1 >= a0 AND z1 >= z0 THEN  -- range overlaps
   --       a0------z0
   --            a1--z1
      nol := nol + (a1 - a0);
      a0 := z0;
      z0 := z1;

   ELSIF a1 >= a0 THEN               -- AND z1 < z0 - follows logically: range included
   --       a0-------z0
   --        a1--z1
      nol := nol + (a1 - a0);
      a0 := z1;  -- z0 unchaged

   ELSIF z1 <= a0 THEN               -- ignore redundant rest
   --       a0-------z0
   -- a1--z1
      -- do nothing

   ELSIF z1 <= z0 THEN               -- shrink remaining rest
   --       a0-------z0
   --     a1--z1
      a0 := z1;  -- z0 unchaged
   ELSIF z1 > z0 THEN                -- new remaining rest
   --       a0-------z0
   --     a1------------z1
      a0 := z0;
      z0 := z1; -- z0 unchaged

   ELSE                              -- only for 1st iteration: a0 & z0 are NULL
      a0 := a1;  z0 := z1;
   END IF;

   RAISE NOTICE '_id: %, nol: %, a0: %, z0:%', _id, nol, a0, z0;  -- to debug
END LOOP;

nol := nol + (z0 - a0);  -- rest from last row

END
$func$  LANGUAGE plpgsql;

SQL Fiddle
db<>fiddle here

Should be fast, too, because a single scan of the table (or index) is enough.

I added some basic ASCII art to visualize IF cases. That should help to understand.


multiple rows with the same maximum value When I run the following query,

SELECT engines, manufacturer, model, MAX(seats)
FROM   planes
GROUP BY engines;

I am getting the correct engines and seats results but not the correct manufacturer, model combination. In addition, there are multiple rows with the same maximum value for seats number which I need but just getting one result by engines/seats. I have viewed other Stack Exchange posts and elsewhere but cannot seem to find a good solution to fix the query. Any advice?

Yours appears to be a "greatest N per group" problem. What you can do is get the maximum seats per engine results:

SELECT
  engines,
  MAX(seats) AS max_seats
FROM
  planes
GROUP BY
  engines

and, using them as a derived table, join them back to the source to get the rows matching the maximums:

SELECT
  p.engines,
  p.manufacturer,
  p.model,
  es.max_seats
FROM
  planes AS p
  INNER JOIN
  (
    SELECT
      engines,
      MAX(seats) AS max_seats
    FROM
      planes
    GROUP BY
      engines
  ) AS es
  ON p.engines = es.engines
  AND p.seats = es.max_seats
;


Unstructured to Structured Data This is highly a research based question about converting the unstructured textual data to structured data. Are there any systems or tools perform automated process of converting the unstructured data  without human interventions to structured data sets? The unstructured data flows from different resources can be directed to a tool/software to be extracted automatically.

Depends on the data. Stanford's NER (GPL license) can pull out date/time, names of individuals and companies, locations, monetary amounts, in popular human languages.

A lot of data and work went into building the models, and if you need to build a custom model then you'll need a lot of data and work.


how much blob data can mysql handle? I want to create a table with 6 columns : 1 blob, 5 text the number of entries can exceed 150,000 rows. Blob data will have size near 2Kb.
Is it a good idea to create this table? Would there be any performance issues?
What different methods should I use ?

That's less that a gigabyte?  MySQL can handle a terabyte or more.  MySQL could handle 10 blobs in each of 10 million rows.  Or more.  "Blob" is not the issue.

As for "performance issues", that's a question of what indexes you have, what queries you have, etc.  Please provide more details:  SHOW CREATE TABLE; some sample queries; what type of data is involved (eg images); etc.


Why does SQLPackage include my table-valued function in deployments when it hasn't changed? I'm using SQLPackage in TFS to automate an SSDT project/DACPAC deployment with SQL Server 2014. I have a table-valued function that appears in the deployment report and script with every deployment, even though the source code and compiled rarely change.  Example; I can do a new build and deployment with no source code changes, and my deployment report will look like this (the corresponding SQL script will have the definition matching what's already in the DB):
<Operations>
  <Operation Name="Alter">
    <Item Type="SqlMultiStatementTableValuedFunction" Value="[stuff].[MyTVF]">
  </Operation>
</Operations>

I would expect the deployment to have nothing in it.  It's the only object for which this happens, in a project containing thousands of objects. Anybody had an experience like this with SQLPackage/TFS/DACPAC before?

My suggestion is to review the ignore list in the publish options.
Things like case sensitive/insensitive or columns order in tables can generate this kind of thiing.
Verify with visual studio compare (if it is possible) with the same publishing options what is happening to highlight codes differences.
What happens if you deploy your dacpac a second time? The TVF alter should disappear.
And after 24 hours? If it returned that means that an application or a script have altered or dropped an recreated it with maybe a simple space or carriage return as a difference and then your deploy is going to find again that an alter is needed.
If you can use visual studio for compare, eventually try to compare with notepad++ getting the alter script from the instance and the alter script from the dacpac apply script generated with the sqlpackage.


Migrate heroku database to Amazon RDS with minimum downtime I have a heroku postgres database and want to migrate it to Amazon RDS to save cost. What's a way to do so with minimum downtime? Usually this involves replicating database in real time and then promoting the replicated DB as the main DB.

I know I can use a follower database to migrate DB within heroku, and I can use read replica database to migrate DB within Amazon RDS. Is there a similar method to create database replication of heroku DB that lives in my own Amazon RDS?

Pretty sure you are going to have to perform a good old-fashioned dump-and-restore here. Amazon doesn't seem to offer any other import options than loading in a dump:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html

and even if they did, AFAICT the only export options offered by Heroku are pg_dumps (i.e. not basebackups or the ability to set up an external hot standby node):

https://devcenter.heroku.com/articles/heroku-postgres-import-export

If you're lucky, you're using a Postgres version new enough to support both parallelized pg_dump (9.3+) and parallelized pg_restore (8.4+), which will make your job a bit quicker. And hopefully you can afford to take a maintenance window during which you lock out writes to your site while the switchover is underway...


ERROR: 42803: column "wage_histories.wage" must appear in the GROUP BY clause or be used in an aggregate function I'm trying to migrate an application from SQLite to PostgreSQL, and now I have reached the point of re-expressing the queries that are valid in one and invalid in the other.

I have a table:

CREATE TABLE wage_histories (
  uid INTEGER REFERENCES users ( id ) ON DELETE CASCADE NOT NULL,
   start TEXT NOT NULL,
   wage INTEGER NOT NULL,
   PRIMARY KEY ( uid, start )
);

It represents the wage of a user, over time. So, the wage of a user on a particular date is the value of the wage column in the row with our chosen uid and the highest start that is less than our chosen date.

I was able to express this in SQLite with the following query:

SELECT wage, MAX(start) FROM wage_histories WHERE start <= ? AND uid = ?

However in PostgreSQL this gives me the following error:

ERROR:  42803: column "wage_histories.wage" must appear in the GROUP BY clause or be used in an aggregate function

Is there something more succinct than the following?

SELECT wage FROM wage_histories WHERE uid = ? AND start = (SELECT MAX(start) FROM wage_histories WHERE uid = ? AND start <= ?)

If you just need one row:

SELECT wage
FROM wage_histories
WHERE uid = ?
  AND start <= ?
ORDER BY start DESC
LIMIT 1


Why does the scipt executes only once the sqlscript the following script is pretty simple, but it works not as expected. I searched for an answer but I am stuck.

#!/bin/bash

db_con=user/pw@db
bname='filename'

for f in $(seq 1 10);
do
    sqlplus -S $db_con @$bname$f
    sleep 2
done

In the *.sql file are statements like:

update db.table_name
set column = bla
where column=foo;
commit;

I expect, that it will run filename1.sql to filename10.sql but sqlplus output shows me only the result of filename1.sql.

As you haven't shown us the sql files I'm going to take a guess that you haven't included the exit statement. This means that SQL*Plus will be invoked, run the commands/sql in the first file and then sit there waiting for the next SQL command. This means the 'sleep 2' command is never reached.

Try this in your sql file instead

 update db.table_name set column = bla where column=foo;
 commit;
 exit


Is it possible to convert a .mysql file to a .sql file? I am facing an issue with a MySql dump file (i.e mydump.mysql) generated by a Drupal export module. I am trying to import it on a local Drupal installation via Acquia Dev Desktop.

When browsing for my dump file, this application filters for .sql, .sql.gz and .mysql files. I thought it would load my .mysql easily, but I get an unsupported file type .mysql error.

Is there a way to convert my .mysql file into a .sql file, knowing that I don't have access to the original database used to generate my mydump.mysql? I would like to try to feed a .sql file to Acquia Dev Desktop instead.

P.S.: I am on Windows 7 if this is relevant. Thanks.

For the records, I finally managed to load my dump into Acquia Dev Desktop after renaming the file to .sql and specifying/using exactly the same database name as the original database name.

I don't know what caused the original issue or what solved it, since I don't believe the database name should be an issue at all.


renaming result of query i have two joined table in my postgres db

tb_items and tb_colors

id_item | group |id colors
--------------------------
1        a         1
2        b         1
3        b         2
4        c         4
5        c         5

id_color | color| subcolor
---------------------------------
1          black        var1
2          red          type1
3          red          type2
4          blue         aaa1
5          blue         xxx3
6          red          hex1

that mean in my query i have

id_item | group | color | subcolor
----------------------------------
1         a       black    var1
2         b       red      type1
3         b       red      type2
4         c       blue     aa1
5         c       red      hex1

basically what i want is renaming color name based on numers of color item in a groups  or

 id_item | group | color
----------------------------------
 1         a       black
 2         b       red
 3         b       red-1
 4         c       blue
 5         c       red

i dont' really know whato to do..

WITH
cte1 AS ( SELECT t1.id_item,
                 t1."group",
                 t2.id_color,
                 t2.color,
                 t2.subcolor
          FROM tb_items t1
          NATURAL JOIN tb_colors t2 ),
cte2 AS ( SELECT color, COUNT(DISTINCT subcolor) subcolors
          FROM cte1
          GROUP BY color )
SELECT cte1.id_item,
       cte1."group",
       CASE cte2.subcolors
       WHEN 1 THEN cte1.color
              ELSE cte1.color || '-' || ROW_NUMBER() OVER (PARTITION BY cte1.color
                                                           ORDER BY tb_colors.subcolor)
       END color
FROM tb_colors
NATURAL JOIN cte1
NATURAL JOIN cte2
ORDER BY cte1.id_item;

fiddle


Rename interval partitioning system generated partition names I have an existing table which I partitioned (interval partitioning on a daily basis) using dbms_redefnition.

PARTITION BY RANGE ("TIME_ID") INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(PARTITION "p1_1"  VALUES LESS THAN (TO_DATE(' 01-jan-2013','DD-MON-YYYY' )))

This gives me a new system generated partition names every day (like SYS41, SYS42, ...).

I have two questions:

I want a script which should run daily and change this system generated name to custom name (like "01-jan-2013" and so on for every date). This script should be run daily through dbms_scheduler. I have a script which give me the name of latest created partition.

SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS A
WHERE PARTITION_POSITION = (
  SELECT MAX(PARTITION_POSITION)
  FROM USER_TAB_PARTITIONS B
  WHERE A.TABLE_NAME = B.TABLE_NAME
    AND B.TABLE_NAME = 'IPART'
);

This may help you to give the answer.
I want a script which should delete the old partitions (say 3 months) automatically. This should also run daily. And that 3 months time should be configurable.

How can I achieve this?

There's a trick you can use, at least for date-range partitions: the HIGH_VALUE field in user_tab_partitions is a valid date expression. If you "eval" that, you'll get a date object for the cutoff point for that partition. You can then use that to build your partition name from.

Sample PL/SQL to try out (generates a string from the high_value column):

declare
  hv varchar2(9);
begin
  for x in (select partition_name, high_value
              from user_tab_partitions
              where table_name = 'FOO' and partition_name not like 'PART_%')
  loop
    execute immediate 'select to_char('||x.high_value||'-1,''YYYYMMDD'') from dual' into hv;
    dbms_output.put_line('alter table foo rename partition '||x.partition_name
                       ||' to PART_'||hv);
  end loop;
end;
/

(Replace the put_line with execute immediate once you've verified that it actually does what you want. The -1 in the date select is to make it more "human friendly", i.e. partition name to match the date it contains.)

As for dropping old partitions, you can use the same trick. Same type of loop to get a date object for each partition, and generate an alter table ... drop partition ... statement for the partitions you don't want to keep.

Simplistic example where you would drop partitions older than 10 days (be vary wary of off-by-one errors, there probably is one below):

declare
  dt date;
begin
  for x in (select partition_name, high_value
              from user_tab_partitions
              where table_name = 'FOO')
  loop
    execute immediate 'select '||x.high_value||' from dual' into dt;
    if dt < sysdate - 10 then
      dbms_output.put_line('to drop: '||x.partition_name);
    end if;
  end loop;
end;
/

You're then left with taking care of index maintenance if necessary.


What is an Effective Way to Count the Number of Pages in Oracle? We've implemented paging in our PL/SQL procedures by building a dynamic SQL query and then selection only a portion of results.

We implement paging by putting the original query inside a SELECT that fetches only certain rownums from given query, like this:

SELECT * FROM (
      SELECT ROWNUM rnum, d.* FROM (
      ' ||
      pSql ||
      '
        [[filter]]
        [[sort]]
      ) d
      WHERE rownum < [[end]])
      WHERE rnum> [[start]]

The only way to implement page counting I could think of was to do a count of all records in pSql. This, however, turned out to be very resource heavy and would needed to be done quite often, as we ofer filtering of data, which in return creates a whole new query in pSql.

Is there a more efficient way of doing a record count?

If the typical use pattern is to page through all the data, then all the records will eventually need to be sent to the client.  In this scenario re-querying the database for every page is in-efficient.  You should consider running the query once and paging through a cache on the client side.

If on the other hand the typical use pattern is to page through a very small amount of the data, then this method may be appropriate.  Nick Chammas' comment on using ROW_NUMBER() is fleshed out by Tom Kyte in a Oracle Magazine article.

SELECT * FROM (
   SELECT /*+ first_rows(25) */
      your_columns,
      Row_Number() Over (Order By something unique) rn
   FROM your_tables
   )
WHERE rn BETWEEN :n AND :m
ORDER BY rn;

I recommend you read the whole article.  Note in particular the warning to sort by something unique so that the results are consistent between pages (given static data):

  You need to order by something unique for these pagination queries, so
  that ROW_NUMBER is assigned deterministically to the rows each and
  every time.