Order by is ignored if inside a derived table of a update (only in MYSQL 5.7)

A derived table in SQL is a set, and a set by definition has no row order. Your attempt to use an imperative calculation (@rownum := @rownum + 1) within a set was wrong to begin with, and while it may have worked for a while, as you now realized, there is no guarantee for that. Most database engines will (correctly) raise an error if you try to use a similar query.

The need that you are describing was addressed with window ranking functions which are supported as of MySQL 8. If you can upgrade to that, your solution will be easy and elegant with ROW_NUMBER () OVER(PARTITION BY ImageID ORDER BY Position).

If you can't use version 8, your only guaranteed solution is to use a correlated subquery, something along the lines of:

Disclaimer - Since you didn't provide DDL and sample data, the solution below isn't tested

UPDATE  Image as t1
SET     Position = (
                    SELECT COUNT(*)
                    FROM Image AS t2
                    WHERE t1.ImageID = t2.ImageID
                          AND
                          t1.Position <= t2.position
                    );

Note that for large tables, this solution may not perform well, but it is guaranteed to work regardless of version as it follows SQL set rules.

This is valid ANSI SQL syntax. MySQL specifically may require some adjustments.


PostgreSQL error: remote query result rowtype does not match the specified FROM clause rowtype, on remote function call

To understand what's going on, first check what is being done on the remote:

SELECT _test1();
 _test1
────────
 (1,2)

This does return a record, not two integers.  When the dblink() call reports

ERROR:  remote query result rowtype does not match the specified FROM clause rowtype

it's because locally you defined the row type as x(a int, b int), but that doesn't match the record on the remote.  The reason is that dblink() itself doesn't know about (as it itself has a SETOF record return type), therefore it cannot push this information to the remote.  So what you have to do is the following:

SELECT *
  FROM dblink('local', 'SELECT * FROM _test1() AS x(a int, b int)') AS t(a int, b int);

 a │ b
───┼───
 1 │ 2

Specifying the record type twice seems superfluous, but if you omitted the local one, it would result in another error:

SELECT * FROM dblink('local', 'SELECT * FROM _test1() AS x(a int, b int)') AS t;
ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM dblink('local', 'SELECT * FROM _test1() AS x(a...
                      ^

Omitting the remote record definition:

SELECT * FROM dblink('local', 'SELECT * FROM _test1() ') AS t(a int, b int);
ERROR:  a column definition list is required for functions returning "record"
CONTEXT:  Error occurred on dblink connection named "local": could not execute query.

(The difference in the error message shows this time the problem is on the remote side.)

One ore thing that is different in my version is SELECT * FROM _test().  See what happens when you call the function in the SELECT list instead of the FROM clause:

SELECT * FROM dblink('local', 'SELECT _test1() AS x(a int, b int)') AS t(a int, b int);
ERROR:  syntax error at or near "("
CONTEXT:  Error occurred on dblink connection named "local": could not execute query.

You can make this nicer if you created the remote function as RETURNS TABLE (or, equivalently, define OUT arguments there):

-- on the remote
CREATE FUNCTION bla()
    RETURNS TABLE (a int, b int) LANGUAGE SQL AS $$
    SELECT 1, 2
$$;

SELECT * FROM dblink('local', 'SELECT * FROM bla() ') AS t(a int, b int);
 a │ b
───┼───
 1 │ 2

The record type is designed to be flexible, but it comes at a price.  You cannot avoid paying this price with dblink(), though, as it has to be able to accommodate any return type.


SQL Server 2008 Mirroring Pauses without warning

If you query the sys.database_mirroring catalog view you should find out the status of database mirroring from the mirroring_state field.  Try this:

select
    DB_NAME(database_id),
    mirroring_state
from sys.database_mirroring

When it "pauses", what does the mirroring_state show?  Here is a legend:

0 = Suspended

1 = Disconnected from the other partner

2 = Synchronizing

3 = Pending Failover

4 = Synchronized

5 = The partners are not synchronized. Failover is not possible now.

6 = The partners are synchronized. Failover is potentially possible. For information about the requirements for failover see, Synchronous Database Mirroring (High-Safety Mode).

NULL = Database is inaccessible or is not mirrored.


But the restore never completes. using SP_WHOISACTIVE the complete_percentage is null ( for like, 10 minutes ).

10 minutes is not nearly enough time to qualify for using the word "never"!

  What can cause this problem? the database is 140GB.

If you don't have instant file initialisation (see here and here amongst other places) turned on then it will be trying to zero 140Gb+ of disk space ready to restore the data - this could easily take longer than ten minutes[*] and during that part of the process the percent done will not count (this only counts as the pages are restored from the backup file to the data files, not while the data files are being created).

  [*]: ~14Gb in ~10 minutes is about 240MByte/second - how fast can your I/O subsystem write and what other activity is going on at the same time?

To answer the question in the title:

  Can I restore a database, from the same version SQL server, with different updates?

Yes, you should be able to, even if the updates that differ are service packs, though I generally recommended that you only restore upwards where possible just in case.


It is enough to specify the schemas parameter.

expdp \'/ as sysdba\' schemas=siddhu

If you want to export only the tables, you can also add include=table:

expdp \'/ as sysdba\' schemas=siddhu include=table

The above exports the tables with indexes and triggers, but it skips procedures, sequences and other objects not directly related to tables.


Pb connection from client to postgresql server

I found the solution to my problem.
I've added ODBC to the client machine with IP server + allowing port 5432 and every thing ran fine.


Here's what we found:

Even though the SQL LOG claimed an ETA of 24 hours, the DB recovered in 12 hours. So the numbers were grossly over-estimated.

Even though SQL LOG was showing percentage recovery completion, it didn't go all the way up to 100%. At around 27%, SQL log said everything okay and recovery complete.

In our case 2 things caused this issue:

Large number of VLFs (697 in our case, for a 70GB log. Although, 697 doesn't seem that large to me)
In our case 800,000+ transactions were required to roll forward. So that could have contributed significantly too.

The answer to my original question: It seems the phase 1 is the slowest; 2 and 3 went very quickly.

We got an answer from Microsoft. Here are some notes from them:

  There are 3 phases of the recovery process

  Analysis: In this phase the transaction log is analyzed and records the information about the last checkpoint and creates on Dirty Page Table, this might capture all the dirty pages details.

  Redo: It’s a roll forward phase. When this phase completes database becomes online so the database was online

  Undo: It’s rollback phase. It holds the list of the active transaction from the analysis phase basically undoing the transactions.

  In Summary:
  This is normal for SQL Server when there is an unexpected shutdown when there is an execution of long running transaction. A database goes through 3 phases of recovery and then comes online – Enterprise edition allows the database to come online after second phase.  This means that there is a rollback running at the moment; need to wait for this to complete. Let the server finish the rollback process if a long-running transaction was terminated. Terminating the server process during the rollback of long-running transaction results in long recovery time.

This might look counteractive; I'm going to post it anyway. If I have to refer in the future, or for folks who might be a victim of this scenario.

It took 13 hours to recover the db. Here are the rough numbers.

Total Recovery time (about 13 hours) = 11 hours analysis + 1 hour redo + 0 hours undo


Reporting Services buttons show up one under the other instead of side-by-side

I found this self-answered question on Stack Overflow - maybe it will help you.

SQL Server Report Manager report showing export, refresh, data feed buttons on separate lines


Generate a new Sub Plan/Sequence ID in SQL 2005/8 Business Intelligence Development Studio

Look at BIDSHelper. Fantastic, free tool everyone developing SSIS and probably SSAS should be using.

Reset GUIDs

"This feature resets the IDs for all tasks, connection managers, configurations, event handlers, variables, and the package ID itself. When you copy a package, objects in both packages end up with the same IDs. This Reset GUIDs feature ensures that the IDs in the current package are unique."


The error message is the generic "I can't reach the SQL instance you've specified."  There are no components to "uninstall/reinstall", mixed mode/Windows mode are simply configuration settings.  I generally recommend you run in mixed mode.

Two things to check:

If this is a SQL Express instance, you should make sure your connecting to [Computer Name]\SQLEXPRESS.  SQL Express installs as a named instance, so as such needs to be connected to as a named instance.
By default, SQL Server installs without TCP protocols enabled, which would prevent most connections.  Open your SQL Server Configuration Manager, browse to SQL Server Network Configuration, and then Protocols for SQLEXPRESS.  Select that and look for TCP/IP and make sure it's enabled.

As for your file being larger than 10 GB, you'll need to use Standard Edition or higher, nothing you can do to get around that.  You can have a separate instance running alongside the Express instance, but if you want you can simply uninstall your Express instance as you would any other Windows program.


Why is this MySQL proc using cursors failing to retrieve results?

Try tagging all local variables with something like my_

DELIMITER @@

DROP PROCEDURE IF EXISTS import_members@@
CREATE PROCEDURE import_members ()
BEGIN
    -- Declare loop constructs --
    DECLARE done INT DEFAULT FALSE;

    -- Declare Person variables --
    DECLARE my_person_id INT;
    DECLARE my_era_username VARCHAR(100);
    DECLARE my_last_name VARCHAR(50);
    DECLARE my_first_name VARCHAR(50);
    DECLARE my_email VARCHAR(100);
    DECLARE my_email_primary VARCHAR(50);
    DECLARE my_degree_id_1 INT;
    DECLARE my_degree_id_2 INT;
    DECLARE my_member_status INT;

    DECLARE my_user_id INT;
    DECLARE my_user_email VARCHAR(100);

    -- Declare Cursor --
    DECLARE member_cursor CURSOR FOR
        SELECT person_id, era_username, last_name, first_name,
              TRIM(email), TRIM(email_primary), degree_id_1, degree_id_2,
              member_status FROM z_data_person
        WHERE member_status IS NOT NULL;

    -- Declare Continue Handler --
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN member_cursor;

    read_loop: LOOP

        -- Fetch data from cursor --
        FETCH member_cursor
        INTO my_person_id, my_era_username, my_last_name, my_first_name,
              my_email, my_email_primary, my_degree_id_1, my_degree_id_2,
              my_member_status;

        -- Exit loop if finished --
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Create User --
        SET my_user_id = SELECT (MAX(uid) + 1) FROM users;
        IF my_email_primary IS NOT NULL AND my_email_primary NOT LIKE '%null%' THEN
            SET my_user_email = my_email_primary;
        ELSE
            SET my_user_email = my_email;
        END IF;

        INSERT INTO `users` (`uid`, `name`, `pass`, `mail`, `created`)
        VALUES (my_user_id, my_era_username, SHA1(RAND()), my_user_email, UNIX_TIMESTAMP());

        -- Create Member --
        INSERT INTO `members` (`uid`, `first_name`, `last_name`, `phone`,
              `member_category_id`, `subscription_weekly_email`,
              `subscription_monthly_newsletter`)
        VALUES (my_user_id, my_first_name, my_last_name, my_phone_num,
              my_member_status, my_weekly_com, my_monthly_com);

        -- Add Degrees --
        IF degree_id_1 IS NOT NULL THEN
            INSERT INTO member_degrees_held
            VALUES (my_user_id, my_degree_id_1);
        END IF;

        IF degree_id_2 IS NOT NULL THEN
            INSERT INTO member_degrees_held
            VALUES (my_user_id, my_degree_id_2);
        END IF;

        -- Add Areas of Expertise --
        INSERT INTO `member_expertises_held` (`uid`, `specialty_id`)
        SELECT (SELECT MAX(uid) FROM users), z.specialty_id
        FROM z_map_person_specialty AS z
        WHERE z.person_id = my_person_id;

    END LOOP read_loop;

    CLOSE member_cursor;

END; @@

DELIMITER ;
CALL import_members();

In the book MySQL Stored Procedure Programming, all cursor examples give the local variable a unique name from its corresponding table column (if you have the book, see page 108 Example 5-15).

Sidenote : Where are weekly_com and monthly_com coming from ???


a couple of notes, seems Rob Farley would also write something similar (I just read his comment now). Secondly I missed your 10 minute rule - I'm going to leave some of the fun for you too
/*
create table #t (msgtime datetime2, userid int)

insert #t values ('2020-12-14 10:07:49.358739',11)
insert #t values ('2020-12-14 10:08:16.156553',11)
insert #t values ('2020-12-14 10:08:32.195645',11)
insert #t values ('2020-12-14 13:36:16.103253',12)
insert #t values ('2020-12-14 13:46:43.871769',12)
insert #t values ('2020-12-14 13:50:42.867178',12)
insert #t values ('2020-12-14 13:55:30.530697',12)
insert #t values ('2020-12-14 13:56:42.472823',12)
insert #t values ('2020-12-14 13:57:05.880580',12)
insert #t values ('2020-12-14 13:57:40.377021',4)
insert #t values ('2020-12-14 13:58:12.723215',12)
insert #t values ('2020-12-14 14:34:30.633401',4)
insert #t values ('2020-12-15 10:16:18.188387',4)
insert #t values ('2020-12-15 10:16:30.735493',4)
insert #t values ('2020-12-15 10:18:03.501372',4)
insert #t values ('2020-12-15 12:50:46.953427',4)
insert #t values ('2020-12-15 12:51:11.772787',4)
insert #t values ('2020-12-15 12:59:34.264237',11)
insert #t values ('2020-12-15 13:02:10.622184',12)
insert #t values ('2020-12-14 10:28:27.489880',12)
insert #t values ('2020-12-14 10:28:43.793308',12)
insert #t values ('2020-12-14 19:29:56.208375',12)
insert #t values ('2020-12-14 19:45:34.401379',12)

select * from #t order by userid, msgtime

*/

;with times as
(select t1.userid, t1.msgtime as prevmsgtime ,
    LEAD(msgtime, 1, NULL) OVER
    (
       Partition By UserID
        ORDER BY
            msgtime
    ) AS nextmsgtime
from #t t1
)
, seq as
(
select
userid,
prevmsgtime ,
nextmsgtime ,
datediff(mi, prevmsgtime , nextmsgtime ) as timediff,
-- If nextmsgtime is null wwe have changed userid
-- or it the time difference is more than 2 mins - a new group
case when (nextmsgtime  is null) or (datediff(mi, prevmsgtime , nextmsgtime ) > 2)
then convert(int,1)
else convert(int,0)
end as newseq
from times
where datediff(mi, prevmsgtime , nextmsgtime ) is not null

)
,
grouper as
(
select
userid,
prevmsgtime  as prevmsgtime ,
nextmsgtime  as nextmsgtime ,
timediff,
newseq,
-- we can calculate a column to group by by summing up by user the number of times we start a new group
sum(newseq) over (partition by userid order by prevmsgtime ) as groupcol
from seq
)
-- get the min and max msgtime when we have a group
select
userid,
min(prevmsgtime ) as prevmsgtime ,
max(nextmsgtime ) as nextmsgtime ,
count(*)+1 as nrows
from grouper
where newseq = 0
group by userid,groupcol
union all
-- just get the values if there isn't a group
select
userid,
prevmsgtime ,
nextmsgtime ,
1 as nrows
from grouper where newseq = 1
order by userid, prevmsgtime


How to Audit Successful Logins for certain logins only?

You can do this with audit objects with SQL Server 2012 and later by using a filter to specify the principals you want to audit.  The only downside is that you can't specify a group or a role, you have to include each login.

USE [master]
GO

/****** Object:  Audit [sysadmin_successful_logon] ******/
CREATE SERVER AUDIT [developer_successful_logon]
TO APPLICATION_LOG
WHERE ([server_principal_name]='domain\dev01' OR
    [server_principal_name]='domain\dev02' OR
    [server_principal_name]='domain\dev03')
ALTER SERVER AUDIT [developer_successful_logon] WITH (STATE = ON)
GO

CREATE SERVER AUDIT SPECIFICATION [developer_successful_logon_spec]
FOR SERVER AUDIT [developer_successful_logon]
ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE = ON)
GO


Index is seeked and scanned - is there a risk for seek to fail after modification?

I would start by adding a clustered index to the table. Since the primary key is unique I would just update that from nonclustered to clustered. To do that you will have to drop and recreate the constraint so if you have foreign keys defined you will need to drop those constraints first then put them back once you have the primary key put back on.

A few references on clustered indexes

Clustered indexes vs heaps

RID lookups

If all of your queries against the table include acatalid and acategid in the join or where clause then adding acatalid to the existing index may help.

However if the queries don't always include both columns in the where clause you may see a benefit in creating another index on acatalid.

I say these changes may help as the statistics that SQL server has on the table could be generating an execution plan with the scan because it sees the cost of scanning through the table as less than doing a seek.


How to reduce identity values to avoid integer overflow?

You could try simply dropping and re-adding the Identity column without copying the entire table. This has the same effect as what you are attempting with the code in the question. Neither approach really handles keeping existing rows as per the "some old values may stay forever" statement from the question, but for whatever situations work with the code in the question, the following should also work.

You should also consider starting the identity range at the min-value. Starting at 1 gives you just over 2.1 billion values, so starting at the minimum value (or close to it) gives you 4.2 billion values. Hence, less likely you will be needing such a reseed operation in the future.

SETUP

-- DROP TABLE dbo.IdentityTest;
CREATE TABLE dbo.IdentityTest
(
  ID INT IDENTITY(10203, 7) NOT NULL CONSTRAINT [PK_IdentityTest] PRIMARY KEY,
  SomethingElse UNIQUEIDENTIFIER NOT NULL,
  SomeName NVARCHAR(256) NOT NULL
);

INSERT INTO dbo.IdentityTest ([SomethingElse], [SomeName])
  SELECT NEWID(), so1.[name] + N'~' + so2.[name]
  FROM   master.sys.all_columns so1
  CROSS JOIN master.sys.objects so2;
-- 782,880 rows

SELECT * FROM dbo.IdentityTest;

Drop and re-Create Identity PK

BEGIN TRY
  BEGIN TRAN;

  ALTER TABLE dbo.IdentityTest
    DROP CONSTRAINT [PK_IdentityTest];

  ALTER TABLE dbo.IdentityTest
    DROP COLUMN [ID];

  ALTER TABLE dbo.IdentityTest
    ADD [ID] INT
    IDENTITY(-2140000000, 1)
    NOT NULL
    CONSTRAINT [PK_IdentityTest]
      PRIMARY KEY;

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

  THROW;
END CATCH;

SELECT * FROM dbo.IdentityTest;


SQL query to count number of payments made in a time period with data from two different table

Here is a query that should give you what you are looking for.  This is Oracle SQL syntax, but may work in other databases as well.

SELECT dau.datetime, count(*) FROM DAU
WHERE EXISTS (select 1 from Payer
   WHERE Payer.user_id=DAU.user_id
   AND Payer.payment_timestamp BETWEEN trunc(dau.datetime)-90 AND trunc(DAU.datetime))
GROUP BY dau.datetime
ORDER BY DateTime;

Data to demonstrate:

create table Payer as (select 1001 user_id, to_date('06/01/2015 07:00','MM/DD/YYYY HH24:MI') payment_timestamp from dual);
insert into Payer values (1001, to_date('06/01/2015 07:30','MM/DD/YYYY HH24:MI'));
insert into Payer values (1002, to_date('06/01/2015 10:00','MM/DD/YYYY HH24:MI'));
insert into Payer values (1001, to_date('06/02/2015 13:50','MM/DD/YYYY HH24:MI'));
insert into Payer values (1002, to_date('06/02/2015 08:00','MM/DD/YYYY HH24:MI'));
insert into Payer values (1004, to_date('06/02/2015 09:00','MM/DD/YYYY HH24:MI'));
insert into Payer values (1003, to_date('06/03/2015 10:00','MM/DD/YYYY HH24:MI'));
insert into Payer values (1005, to_date('06/03/2015 08:30','MM/DD/YYYY HH24:MI'));

create table DAU as (select 1001 user_id, to_date('06/01/2015','MM/DD/YYYY') datetime from dual);
insert into DAU values (1002, to_date('06/01/2015','MM/DD/YYYY'));
insert into DAU values (1003, to_date('06/01/2015','MM/DD/YYYY'));
insert into DAU values (1001, to_date('06/02/2015','MM/DD/YYYY'));
insert into DAU values (1002, to_date('06/02/2015','MM/DD/YYYY'));
insert into DAU values (1004, to_date('06/02/2015','MM/DD/YYYY'));
insert into DAU values (1002, to_date('06/03/2015','MM/DD/YYYY'));
insert into DAU values (1003, to_date('06/03/2015','MM/DD/YYYY'));
insert into DAU values (1005, to_date('06/03/2015','MM/DD/YYYY'));


SQL Server - Show Content of a partition

This is a pretty basic query I use to get rows ( lines? ) per object on my partitioned schemes:

SELECT  DISTINCT ObjectName = so.name, ds.name,
        sp.partition_number, sp.rows
FROM    sys.objects so
INNER JOIN sys.partitions sp
    ON  so.object_id = sp.object_id
INNER JOIN sys.destination_data_spaces dds
    ON  sp.partition_number = dds.destination_id
INNER JOIN sys.data_spaces ds
    ON  dds.data_space_id = ds.data_space_id
WHERE   so.is_ms_shipped = 0
    AND sp.rows > 0
    AND ds.name <> 'PRIMARY'
ORDER BY so.name, ds.name;

You could add the name of your table and the partition you're interested in to the predicate:

    AND so.name = 'MyTable'
    AND sp.partition_number = 2

For a little more detailed information ( per index, compression stats, etc ), I use this little guy:

SELECT  DatabaseName = DB_NAME(), SchemaName = ss.name,
        TableName = OBJECT_NAME( so.object_id ),
        IndexID = p.index_id, IndexName = CASE
            WHEN p.index_id = 0
              THEN 'HEAP'
            ELSE i.name
        END, [Compression] = sp.data_compression_desc, PartitionNumber = p.partition_number,
        LowerBoundary = prv_left.value, UpperBoundary = prv_right.value,
        FileGroupName = CASE
            WHEN fg.name IS NULL
              THEN ds.name
            ELSE fg.name
        END, UsedPagesInMB = CONVERT( NUMERIC( 18, 2 ), p.used_page_count * 0.0078125 ),
        DataPagesInMB = CONVERT( NUMERIC( 18, 2 ), p.in_row_data_page_count * 0.0078125 ),
        ReservedPagesInMB = CONVERT( NUMERIC( 18, 2 ), p.reserved_page_count * 0.0078125 ),
        [RowCount] = CASE
            WHEN p.index_id IN ( 0, 1 )
              THEN p.row_count
            ELSE 0
        END, [Type] = CASE
            WHEN p.index_id IN ( 0, 1 )
              THEN 'DATA'
            ELSE 'INDEX'
        END
FROM sys.dm_db_partition_stats p
INNER JOIN sys.objects so
    ON  p.object_id = so.object_id
INNER JOIN sys.partitions sp
    ON  so.object_id = sp.object_id
    AND p.partition_id = sp.partition_id
    AND p.partition_number = sp.partition_number
INNER JOIN sys.schemas ss
    ON  so.schema_id = ss.schema_id
INNER JOIN sys.indexes i
    ON  i.object_id = p.object_id
    AND i.index_id = p.index_id
INNER JOIN sys.data_spaces ds
    ON  ds.data_space_id = i.data_space_id
LEFT OUTER JOIN sys.partition_schemes ps
    ON  ps.data_space_id = i.data_space_id
LEFT OUTER JOIN sys.destination_data_spaces dds
    ON  dds.partition_scheme_id = ps.data_space_id
    AND dds.destination_id = p.partition_number
LEFT OUTER JOIN sys.filegroups fg
    ON  fg.data_space_id = dds.data_space_id
LEFT OUTER JOIN sys.partition_range_values prv_right
    ON  prv_right.function_id = ps.function_id
    AND prv_right.boundary_id = p.partition_number
LEFT OUTER JOIN sys.partition_range_values prv_left
    ON  prv_left.function_id = ps.function_id
    AND prv_left.boundary_id = p.partition_number - 1
WHERE   OBJECTPROPERTY( so.object_id, 'ISMSSHipped' ) = 0;


Cross join table row with generate_series

Use a lateral join:
SELECT x, plans.name
FROM plans
CROSS JOIN LATERAL
   generate_series(timestamp '2021-01-01 06:00',
                   timestamp '2021-01-07 22:00',
                   plans.duration::interval) t(x)


There are two basic types of corruption that can occur with CDX indexes: Logical corruption or physical corruption. Based on the OP description that says it is not detected for a while, it sounds like logical corruption.  A common type of logical corruption is when keys either don't  exist for records, or they exist but have incorrect key values.  Physical corruption in CDX indexes typically manifests itself with index pages that are simply invalid (e.g., mismatch between key count and actual keys) or have pointers to other pages that do not exist.

Physical corruption is usually detected sooner because the application will often produce an error when attempting to process a physically corrupt page.  Logical corruption can go undetected for a longer period of time because it is quite possible to use a logically corrupt index without any errors being reported (e.g., not finding a key in a seek operation is a "normal" situation).

Assuming it is logical corruption being described in the OP, one possible reason for this is if Advantage Database Server is using a different collation than the FoxPro application with which it is sharing the data. The older style CDX indexes do not contain any information in them that defines the collation.  One quick check you might use to see if this is the case is to download the checkindex utility.  It is a simple utility that will scan a table and verify that the keys and records are in agreement with each other.  If you reindex a table with FoxPro, you could then run this utility (which will use Advantage) and determine if they are using the same collation. More information about collations here.


Go to Model -> Diagram Properties and Size and increase the number of pages for the model
Select Arrange -> AutoLayout which evenly spreads out the tables in the model.

You can repeat the above as you add more pages to get the best fit


postgres_fdw and postgres version upgrade

Join and aggregate pushdowns are supposed to go live on Postgres 10, not Postgres 9.6 which is why remote queries are not being sent the way I expected it to.


PostgreSQL's CTE's materialize results - they run the CTE term once and cache the output for the duration of the query. So it's already doing what you want.

CTE results cannot be cached between queries. If you want that, you should instead CREATE TEMPORARY TABLE AS SELECT ....


How to get online the database which has missing/corrupted FILESTREAM files?

I finally found how to do it. I've found some very old backups. The data changed meanwhile, and it was important to have an up-to-date version of this data, which meant that it wasn't possible to just restore the old backup. Instead, I did the following, thanks to Michael Eklöf on ServerFault:

Copy the current .mdf and .ldf files.
List the filegroups of the old backup:

use [master]
go

restore filelistonly
from disk = 'E:\Database backups\Hello_backup_2012_03_10_202359_9203520.bak'
go

Restore the database from the old backup, with the names of filegroups from the previous result:

restore database [Hello]
from disk = 'E:\Database backups\Hello_backup_2012_03_10_202359_9203520.bak'
with move 'Hello' to 'D:\Database\Hello.mdf',
move 'Hello_log' to 'D:\Database\Hello_log.ldf',
move 'Hello_files' to 'D:\Database\Hello_files'
go

Set to offline the filegroup (in my case, the FILESTREAM) which is missing in the new version of the database:

alter database [Hello]
modify file (name = 'Hello_files', offline)
go

Take the database offline.
Replace the .mdf and .ldf files created by the server at step 3 by the files copied at step 1.
Take the database online.

There are still consequences of the missing filegroup. Some operations require the database to be complete; on of those critical operations is the backup. In other words, full backup is impossible to do with a database restored through the six steps above.

In order to get the backup ability back, I've copied the structure of the database (through Tasks → Generate Scripts...) excluding the tables which relied on filestream and then copied the data itself.


How do I get the corresponding row for a RowsetID?

That looks like a partition_id to me, not "a row." You can start to get an idea of what values might be stored in that partition through the following:

SELECT partition_number
  FROM sys.partitions
  WHERE partition_id = 72057600379912192;

Then you'll have to look at the partition function / scheme to tell where the boundaries are for that specific partition.

I don't think sys.sysrowsets is going to tell you anything that sys.partitions won't. It's unfortunate that the product interchanges words like partition_id, hobt_id, and RowsetId so carelessly.


Getting Data Back after Wrong Data Restore in SQL Server

The database has log and data files that are stored on the file system. If the sys admin has a process of regularly backing up the location where the database files are stored then you might be able to get some of the data back. Assuming that you can get these files from before you overwrote them - then create a new database and attach the db and log file to it. You will most likely still have some problems but you might get some data back.


What is the damage to the database if it uses high memory storage? Are there consequences for this use? Does it affect performance?

To add on to danblack's point, it's generally good for your server's memory to be mostly consumed by the database system because accessing data from memory is faster than disk, but if you don't have enough memory to support your database system's workload then you can run into performance issues when it needs to pull from disk or compensate in other ways.
If you updated your question with the specific issue you're trying to solve then we can provide more specific information, but if you're just generally asking then you don't have to worry too much about this until you run into performance issues. There's a lot of other things that can equally or more likely affect performance before memory provision does.


You most likely have a .my.cnf file in your home directory, which is specifying a password.

The clue here is that when you did not specify a password (sudo -u root), the error message is still saying that you did.


CONCAT() between different alphabet echoes out '&' ampersand

You can use COLLATE :

concat(COL1 COLLATE latin1_general_ci,COL2 COLLATE latin1_general_ci)

latin1_general_ci is an example, try to figure it out what collation do u have.

But i think this should work, you could try latin1_general_cs too.


Oracle (as well as any other RDMS) doesn't scan "rows". It operates with blocks (other vendors may use different terminology , e.g. page) , and it doesn't know which blocks have or don't have rows. Also, sequential read is much faster than random , and it's way cheaper to read more than needed into memory...  A good graphical explanation of HWM : http://chandu208.blogspot.ca/2012/02/high-water-mark-hwm.html


Can't create database even with all privileges

Before the changes actually take action, you need to run

FLUSH PRIVILEGES

EDIT:
Just saw that you tried to grant Privileges on a database which doesn't exists. You need to give them global privileges to create databases.


SQL Server Frozen Ghost Cleanup workaround needed

Finally, MS has recognized the issue as a bug: http://support.microsoft.com/kb/2622823

Briefly: It is fixed in

Sql Server 2008 SP3 CU4
Sql Server 2008 R2 CU10
Sql Server 2008 R2 SP1 CU4

In Sql Server 2012 SP1 I'm not experiencing the issue for more than year of runtime.


Merge multiple rows containing different values into one row

So, I want to thank all for your answers. The code that I came up with looks like this:
/* This Code section is to take different prefernces (in multiple rows) and put them in one row (with multiple columns)*/
SELECT account_id, A[1] IS_ID1, B[1] IS_TYPE1, C[1] IS_TITLE1, D[1] IS_QUERY1, A[2] IS_ID2, B[2] IS_TYPE2, C[2] IS_TITLE2, D[2] IS_QUERY2,
 A[3] IS_ID3, B[3] IS_TYPE3, C[3] IS_TITLE3, D[3] IS_QUERY3, A[4] IS_ID4, B[4] IS_TYPE4, C[4] IS_TITLE4, D[4] IS_QUERY4, A[5] IS_ID5, B[5] IS_TYPE5, C[5] IS_TITLE5, D[5] IS_QUERY5
from (
    select account_id, array_agg(id) A, array_agg(type) B, array_agg(title) C, array_agg(query) D
    from temp01
    group by 1
    order by 1) z
/* This is the end of this section*/


Is the syntax of Oracle 10g and MySQL the same?

They're vastly different when it comes to other features, such as security, programming languages, data types, tools, end user support, requirements, etc.

However, they're both compliant with basic SQL92 (and perhaps other SQL standards), so one can do the same basic schema creation and data querying and manipulation. You should be able to do the basics from java applications without noticing where you're connected to (and this applies to several other databases).


SQL Server data, logs, backups files in mounted foldersin C:\

Well, you shouldn't encounter any errors from SQL Server, if that is what you are asking about.  SQL Server doesn't care where the files are located.  That being said, there are good reasons for having different file types on different drives.

Performance - Having different files on different drives means that there are different sets of drive heads reading the data.  Your throughput for simultaneous read/write operations goes up.  Especially if you are backing up to the same drive that your data files are on, this will suck.

Maintenance - What happens when your drive fills up because the part of your script that cleans out old backups has failed and now your drive is full?  If you had separate drives then just the one then your damage would be limited.

What happens to your backups when your primary drive fails?  Are they being taken off to tape somewhere else?

If this is a production server then I strongly encourage you to separate out to at least separate Data and Log drives and backup to a different machine.


I think it is a bad idea to rob space in RAM for things other than innodb_buffer_pool_size.

Furthermore, your example is flawed.  An ALTER that needs to copy works this way:

Create tmp table and populate it;
"Move" the table into where the original table is.

Step 2 is the issue:

If the tmp and the original are in the same filesystem, these are fast operations involving the directory.
If they are in different filesystems (and tmpfs is definitely different), the "move" is really a more-costly "copy".


Can't create table in database with db_owner role (SQL Server 2012)

The problem solved. In the Database options - Permissions tab public Database role was added and it was Deny permission for Create database option. After I removed Deny permissions it is OK now. User can create table.


Limit the degree of parallelism (DOP) available to any query

Parallel Server Sets:  PARALLEL_DEGREE_LIMIT limits the degree of parallelism, but if your query is sorting or grouping the number of parallel processes can be twice as much (two server sets to enable inter-process parallelism).  That explains why you will see 48 parallel processes even with a limit of 24.  This also happens if you use resource manager to limit the DOP.

Parallel hints:  PARALLEL_DEGREE_LIMIT only applies to statements that use the auto degree of parallelism.  Any statements that use a hard-coded degree, or even any type of object-level parallel hint, will ignore the limit.  If you have those hints, that could explain why you see 96 some times.

Calibrate IO:  Maybe automatic DOP is not being used, and thus the limit is not being followed, because the IO was not calibrated.  This query will tell you if the IO was calibrated:

select * from V$IO_CALIBRATION_STATUS;

I've seen this cause problems before, but my current system is not calibrated and automatic DOP seems to work fine.  You can tell if this is really an issue by looking at the Notes section of the explain plan.  If you see something like - automatic DOP: Computed Degree of Parallelism is 2 you're fine, but you don't want to see automatic DOP: skipped because of IO calibrate statistics are missing.

Increase PARALLEL_MAX_SERVERS:  Instead of worrying about running out of parallel servers, I would recommend that you significantly increase PARALLEL_MAX_SERVERS.
You should at least try to go back to the default value, PARALLEL_THREADS_PER_CPU x CPU_COUNT x concurrent_parallel_users x 5, between 240 and 960 depending on your memory settings.

Those high numbers sound ridiculous to many DBAs, but they actually make a lot of sense for the following reasons:

Oracle parallel servers are more light weight than most people assume.  (And hardly anybody ever tests it, they just find one situation where a large DOP causes
a problem and assume that high DOP is always bad.)
It's common to run an adhoc query in a GUI tool that only retrieves the first 50 rows, but still use dozens of parallel servers.  These queries are NOT consuming any significant resources, unless PARALLEL_MAX_SERVERS is too low.  Then people get yelled at for running
perfectly reasonable queries, which can lead to some ugly situations.
A very large DOP for a single query is not always bad.  Everyone assumes that if you keep increasing the DOP, the overhead will get too high and the performance will drop significantly.  But on many systems I've found that even a ridiculously high DOP will lead to better performance, although there are definitely diminishing returns, and it can be very unfair to other sessions.  But don't just guess, test it; take a query
and run it with all kinds of DOPs, up to a 1000.  You may be surprised.
Yes, too much parallelism can be bad.  But what's worse for the system, having slightly more than the optimum number of sessions, or forcing
a query to serial and basically killing an important job?  You should monitor the system before introducing arbitrary limits.


Resolving ORA-01033 "initialization or shutdown in progress"

First:

sqlplus /nolog

Then:

connect / @bdlocal as sysdba

(Alternatively, use sqlplus / as sysdba instead of the above two steps.)

Check the status (I guess it will tell you anything but STARTUP):

select status from v$instance;

Then turn it completly off:

shutdown immediate;

Then start it:

startup;

If you get errors starting up, check the alert log and your favorite search engine (searching for the ORA-xxxx alert number is a good place to start). If you can't figure it out, you can ask a question about that here (including the relevant part of the alert log or any other errors you see).


How to use mysql live update on a mysql table between localhost and remote server?

Check this : Link

That is Below one :

Can't you use Triggers?

  22.5.5:  Is it possible for a trigger to update tables on a remote server?
  Yes. A table on a remote server could
  be updated using the FEDERATED storage
  engine.

From : http://dev.mysql.com/doc/refman/5.0/en/faqs-triggers.html#qandaitem-22-5-1-5

I wouldn't recommend this because you will be consuming bandwidth for every small change you do. You can try a scheduled job instead.


Avoid downtime of Microsoft SQL Server during restarts

If you have a running query or an open transaction, SQL Server doesn't have a way to move that query or transaction over to another server.
When your SQL Server instance (we'll call Instance A) restarts:

Running queries will be aborted
Open, uncommitted transactions will be rolled back
New queries will get a connection timeout

We can't fix #1 and #2, but we can help #3 in a couple different ways.
If the application needs to do writes, you can lower the amount of downtime by using a high availability feature like failover clustering, Always On Availability Groups, or database mirroring. This lets you patch the standby server (Instance B) first, ahead of time. When it's time to patch Instance A...you don't. You just do a failover from Instance A (unpatched) to Instance B (patched). Your users experience a short 5-60 second outage, depending on server size, running transactions, server tuning, etc. Then, you can take your time patching Instance A, and assuming the servers are the same size, you don't even need to fail back. You can keep running on Instance B until it's the next time for patching.
If the application only needs to do reads, you can use scale-out features like read-only replicas on Always On Availability Groups. Depending on how you configure the AG and your application, connection string changes may be required, like pointing to the AG listener and specifying read-only intent rather than specifying Instance A's name by itself.
If the application does a mix of reads and writes, and you're willing to put in some work on the application side, you can stay online during failovers using the techniques Stack Overflow uses.


Can't create indexed view against table with masked columns

As far as I know, this is not supported, and you can only use tables in this case.

As found in following article which is using Row level security which is not the same as Dynamic Data masking, but it might be linked to each other.

https://www.mssqltips.com/sqlservertip/4005/sql-server-2016-row-level-security-limitations-performance-and-troubleshooting/ by Aaron Bertrand:

Indexed Views

Row-Level Security is not compatible with tables that have indexed views. If you try to create an indexed view against a table with a security policy, you will get the following error:

  Msg 33266, Level 16 The index on the view 'view' cannot be created
  because the view is  referencing table 'table' that is referenced by a
  security policy.

And no, you can't chicken-and-egg this to work around it; if a table is referenced by an indexed view, a security policy cannot be applied:

  Msg 33265, Level 16 The security policy 'policy' cannot have a
  predicate on table 'table' because this table is referenced by the
  indexed view 'view'.

Note: You may come across the same types of problems if trying to apply a security policy to a table that is reference by a partitioned view.

As Dynamic Data Masking & Row Level Security were released in the same release maybe the limitations are linked to each other.

The Error

The error also clearly says that it is not allowed to have an index on a view linked to a table with a masked column. In the documentation however nothing can be found about it.

So my guess is that it is just not supported.


How do I define the frequency of my mongo backup

MongoDB doesn't have an automatic backup feature built-in -- you have to choose an appropriate backup strategy using command-line or third party tools.

The backup procedure will vary depending on your deployment type (standalone, replica set, sharded cluster), disk/dbpath configuration, and backup requirements.

Unless you are using a third party tool, it's best to follow a supported procedure from the MongoDB manual.


Word for describing how normal (technical term) a database is

I've only ever heard "normalized" and "denormalized".  "Sorry, you'll need to update the data in a bunch of places; it's kind of denormalized."  So you might hear that a database is "fully normalized", "somewhat denormalized", or "extensively denormalized".


Different types of result for coulmnName = nulll and coumnName IS NULL in PostgreSQL

As per SQL standard, comparisons (and boolean operations) use 3-Value logic (true, false, unknown), with unknown being represented by NULL. The following expression returns unknown (represented by NULL):

party_type = null

Whereas

party_type IS NULL

will return a pure boolean (true, false).

You probably want a "NULL-safe comparison predicate":

party_type IS NOT DISTINCT FROM null

See Comparison Predicates


How to find the active transactions in VLF?

LOG_REUSE_WAIT_DESC will show LOG_BACKUP any time there has been at least one transaction that has not been backed up (when the database is in Full Recovery mode).

You can see active sessions, requests, and transaction count for each session, by looking at the results of this query:

SELECT SessionID = s.session_id
    , HostName = s.host_name
    , ProgramName = s.program_name
    , SessionElapsedTime = s.total_elapsed_time
    , RequestCommand = r.command
    , DatabaseName = d.name
    , StatementExecuting = case when r.statement_end_offset > 0 THEN
            SUBSTRING(t.text, r.statement_start_offset, r.statement_end_offset -
            r.statement_start_offset)
            ELSE t.text END
    , tst.open_transaction_count
FROM sys.dm_exec_sessions s
    LEFT JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
    LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
    LEFT JOIN sys.databases d ON r.database_id = d.database_id
    LEFT JOIN sys.dm_tran_session_transactions tst ON s.session_id = tst.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t;

If I wrap that query in a transaction, you can see the open_transaction_count field shows 1, which is expected since I have no other activity ongoing on my Tablet.


SQL Server 2014 EE not using all available processors

You need to download the right ISO/EXE from your licensing site. (Could be MSDN, for example.) The one you want is Core-based, and it'll have the right installation key embedded in it. (You may also be able to check with your license admins to get it.)

Then, you can do an edition change (Books Online). For more practical details, check out Kendra Little's post on changing editions.

For folks who stumble across this question and just wonder what's going on, Aaron Bertrand has a great blog post with more background on the 20/40-core limit.


If you cannot use the RDS CLI, set proper DB Parameters or other means to set slave-skip-errors=1062, you are better off just creating a read replica to make a new Slave from the Master then dropping the old Slave that had the 1062 error.

I have mentioned MySQL RDS Read Replicas in the past

May 21, 2013 : Best strategies to have a backup of an RDS MySQL database
Jul 25, 2012 : Scaling Percona datacenters: setup and replication

After creating read replica, you should make sure you are using INSERT IGNORE or INSERT ... ON DUPLICATE KEY commands on the Master to prevent duplicate keys from replicating to Slaves.


Determine ANSI_DEFAULTS setting ON or OFF?

ANSI_DEFAULTS isn't really a distinct option, but a logical grouping of several other session-based options:

IMPLICIT_TRANSACTIONS
CURSOR_CLOSE_ON_COMMIT
ANSI_WARNINGS
ANSI_PADDING
ANSI_NULLS
QUOTED_IDENTIFIER
ANSI_NULL_DFLT_ON

You can use the @@OPTIONS configuration function to get the bit-masked value of the current Session settings, the values of which are noted on the Configure the user options Server Configuration Option MSDN page.

Putting those elements together gets you the following query which determines the current session setting of the "ansi_defaults" group of options:

SELECT @@OPTIONS AS [user_options],
       CASE WHEN @@OPTIONS & 2 = 2 THEN 'ON' ELSE 'OFF' END AS [implicit_transactions],
       CASE WHEN @@OPTIONS & 4 = 4 THEN 'ON' ELSE 'OFF' END AS [cursor_close_on_commit],
       CASE WHEN @@OPTIONS & 8 = 8 THEN 'ON' ELSE 'OFF' END AS [ansi_warnings],
       CASE WHEN @@OPTIONS & 16 = 16 THEN 'ON' ELSE 'OFF' END AS [ansi_padding],
       CASE WHEN @@OPTIONS & 32 = 32 THEN 'ON' ELSE 'OFF' END AS [ansi_nulls],
       CASE WHEN @@OPTIONS & 256 = 256 THEN 'ON' ELSE 'OFF' END AS [quoted_identifier],
       CASE WHEN @@OPTIONS & 1024 = 1024 THEN 'ON' ELSE 'OFF' END AS [ansi_null_dflt_on],
       -- all above options combined
       CASE WHEN @@OPTIONS & 1342 = 1342 THEN 'ON' ELSE 'OFF' END AS [ansi_defaults]

When all of the base options are set to ON, then ANSI_DEFAULTS will be shown as being ON. If any of those options is OFF, then ANSI_DEFAULTS will also be OFF. You can see this behavior via the following test:

SET ANSI_DEFAULTS ON;

DBCC USEROPTIONS; -- ansi_defaults is SET

SET ANSI_NULLS OFF;

DBCC USEROPTIONS; -- ansi_nulls AND ansi_defaults are missing

SET ANSI_NULLS ON;

DBCC USEROPTIONS; -- ansi_nulls AND ansi_defaults are back

Setting ANSI_DEFAULTS to ON will set all of those options to ON. Likewise, setting ANSI_DEFAULTS to OFF will set all of those options to OFF.


Can a TVF use multiple CTE statements internally?

Using the following cobbled-together-example, I'm able to utilize 3 different CTE's to work against the output table variable.  Perhaps you could identify the difference between your process and this simple example?

IF EXISTS (SELECT *
           FROM   sys.objects
           WHERE  object_id = OBJECT_ID(N'[dbo].[TEST_TVF_CTE]')
                  AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
  DROP FUNCTION [dbo].[TEST_TVF_CTE]

GO
CREATE FUNCTION [dbo].[TEST_TVF_CTE] (@sInputList VARCHAR(8000))
RETURNS @OutTable TABLE (item VARCHAR(8000))

BEGIN
    WITH FIRSTCTE
    AS (
        SELECT TOP 1 TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLES
        ORDER BY TABLE_NAME
        )
    INSERT INTO @OutTable
    SELECT *
    FROM FIRSTCTE;

    WITH SECONDCTE
    AS (
        SELECT TOP 1 TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLES
        ORDER BY TABLE_NAME DESC
        )
    INSERT INTO @OutTable
    SELECT *
    FROM SECONDCTE;

    WITH FINALCTE
    AS (
        SELECT TOP 1 'XYZ' AS TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLES
        ORDER BY TABLE_NAME DESC
        )
    UPDATE a
    SET a.item = b.table_name
    FROM @OutTable a
    JOIN FINALCTE b ON 1 = 1

    RETURN
END
GO

SELECT *
FROM DBO.TEST_tvf_cte('...')


SQL Server DeleteFolder Job ran successfully but it doesn't deleted the folder

After 2 days of my hard times, I found that I have problem in "Permission". Since I am using "xp_cmdshell" which use window log in permission to delete the folder.
I ask my server guy to give me "Modify permission" which can delete the folder. It solve my whole problem. Thanks guys


SQL query to find all parent records where child records are a specific set

The problem we are trying to solve is called exact relational division. It requires an extra condition from the relational division and there are still a lot (too many actually) ways to solve this kind of problem.

Check this related SO question where you will find more than 10 different ways to do this in Postgres and performance tests. The problem discussed there is (simple, not exact) relational division so you'll have to adjust the answers:

How to filter SQL results in a has-many-through relation

Here is another one:

select p.*
from item p
  join
    ( select item_id from item_detail
      where (type, value) = (10, 1000)
      intersect
      select item_id from item_detail
      where (type, value) = (20, 2000)
      except
      select item_id from item_detail
      where (type, value) not in ((10, 1000), (20, 2000))
    ) as c
  on c.item_id = p.id ;

I suggest an index on (type, value, item_id).


Deleting the MySQL 'root' user on purpose

You can rename the 'root' user.

To rename the administrator’s username, use the rename command in the MySQL console:

mysql> RENAME USER root TO new_user;

The MySQL “RENAME USER” command first appeared in MySQL version 5.0.2. If you use an older version of MySQL, you can use other commands to rename a user:

mysql> use mysql;

mysql> update user set user=”new_user” where user=”root”;

mysql> flush privileges;

From here.


How to get rid of using temporary when using DISTINCT in MySQL?

Do you have performance issues because this sounds like premature optimization?

The only good single query what uses indexes is an UNION (which will also filter out duplicates).

If you run EXPLAIN on this query, using temporary won't pop up in the explain..

SELECT
 a
FROM test
WHERE b = 'b1' AND
date > NOW() - INTERVAL 1 MONTH

UNION

SELECT
 a
FROM test
WHERE b = 'b2' AND
date > NOW() - INTERVAL 1 MONTH
;

Edited 2019-09-01

  Demo is not valid anymore as sqlfiddle does not run the old MySQL version anymore
  and because MySQL source code was updated...

  I would suggest looking into db-fiddle and switch between MySQL versions 5.5/5.6/5.7, then the problem should be visible between 5.5 and 5.6

By looking into source code file sql/union.cc (C++ code) of MySQL version 5.7.2 m12 (most likely (all) lower versions also using this code, but not sure about that), we can see how UNION works in MySQL. You will discover that the EXPLAIN output sometimes tells you lies, because the source code (in one of the input parameters) indicates that:

  is_union_distinct if set, the temporary table will eliminate
  duplicates on insert

This makes sense because duplicated records are filtered out. Source code snapshot is below:

/*
  Create a temporary table to store the result of select_union.

  SYNOPSIS
    select_union::create_result_table()
      thd                thread handle
      column_types       a list of items used to define columns of the
                         temporary table
      is_union_distinct  if set, the temporary table will eliminate
                         duplicates on insert
      options            create options
      table_alias        name of the temporary table
      bit_fields_as_long convert bit fields to ulonglong

  DESCRIPTION
    Create a temporary table that is used to store the result of a UNION,
    derived table, or a materialized cursor.

  RETURN VALUE
    0                    The table has been created successfully.
    1                    create_tmp_table failed.
*/

bool
select_union::create_result_table(THD *thd_arg, List<Item> *column_types,
                                  bool is_union_distinct, ulonglong options,
                                  const char *table_alias,
                                  bool bit_fields_as_long, bool create_table)
{
  DBUG_ASSERT(table == 0);
  tmp_table_param.init();
  count_field_types(thd_arg->lex->current_select(), &tmp_table_param,
                    *column_types, false, true);
  tmp_table_param.skip_create_table= !create_table;
  tmp_table_param.bit_fields_as_long= bit_fields_as_long;

  if (! (table= create_tmp_table(thd_arg, &tmp_table_param, *column_types,
                                 (ORDER*) 0, is_union_distinct, 1,
                                 options, HA_POS_ERROR, (char*) table_alias)))
    return TRUE;
  if (create_table)
  {
    table->file->extra(HA_EXTRA_WRITE_CACHE);
    table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
  }
  return FALSE;

}

There is a function that is called create_tmp_table, and thus MySQL is always creating a temporary table when the function create_result_table is called


Postgres evaluates syntax error as boolean in Update statement

Yet postgresql happily accepted the UPDATE as valid and it evaluated the code:
null AND dt = '2018-01-01'

after the second equal sign as a boolean and therefore updated data based on bad logic.

Bad things happen where there is bad code. In this case, the code was not equivalent to the logic you wanted, and had replaced the WHERE with AND. This kind - of bad substitution - often results in errors. The rare times that it doesn't result in error you get this - bad - results where you get different/unexpected results from a query or - even catastrophic - you update the whole table where you wanted to update a few rows.

I have tried this in other SQL flavors and it is not allowed, they want a WHERE.

I don't know of any DBMS that requires WHERE in an UPDATE statement. SQL Server, Oracle, DB2, SQLite, MySQL, neither of them requires it. That is the job of the database developers, code reviewers and testing - to assure that code aligns with business logic and requirements.

I'm not a Postgres person but it does seem like a very odd syntax quirk, or is it a bug?

It's definitely not a bug. The code is parsed and executed - as you correctly assumed - as:
UPDATE
    testing
SET
    num = 2,
    istrue = ( null AND (dt = '2018-01-01') )
;

resulting in updating the whole table. istrue will be updated with a value of NULL for the rows where dt is NULL or equal to '2018-01-01' and with a value of FALSE for the rest of the rows.


This query is generated from midnightcowboycoder.com. Don't forget to replace backtick with a single quote.

DB::select(`rt.id`,`rt.type`,`rt.total_no_of_rooms`)
        ->addSelect(DB::raw(`SUM(r.no_of_rooms) as reserved rooms`))
        ->selectSub(`rt.total_no_of_rooms`, `rooms available`)
        ->selectSub(`-`, `rooms available`)
        ->selectSub(`SUM`, `rooms available`)
        ->from(`Roomtypes as rt`)
        ->join(`Reservations as r`, function($join) {
            $join->on(`r.roomtype_id`, `=`, `rt.id`)
                ->on(`2018-04-11`, `>=`, `r.check_in`)
                ->on(`2018-04-12`, `<=`, `r.check_out`);
            })
        ->groupBy(`rt.id`)
        ->groupBy(`rt.type`)
        ->groupBy(`rt.total_no_of_rooms`)
        ->get();


Get ALL information about spatial index using SQL, including CELLS_PER_OBJECT and USING GEOGRAPHY_AUTO_GRID

This information can be found in the system tables, sys.spatial_indexes and sys.spatial_index_tessellations. More info for these system tables at https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/spatial-data-catalog-views?view=sql-server-ver15.


The problem was that i was doing a classic  backup using windows server utility  (nothing to have with DB backup) for some folder . I was doing backup for only 1 folder in the root of the C:\ drive  .

But somehow when this backup starts it freezes the I/O on each database, then it resumes the I/O of all databases and finally all DB are backed up .


Use CTE to fetch most recent row for each ID

You can try using a window function like ROW_NUMBER() or DENSE_RANK() to generate an ID that represents the latest rows per your sort criteria. Here's an example:
;WITH HFCTE (DIdentifier, CachedHFFWVersion, CurrentHFFWVersionn, HFFWMessage, HFFWMessageDate, SortId) AS
(
    --Get all HF Firmware Messages for each device
    SELECT  udp.DIdentifier,
        LEFT(RIGHT(UDPMessage,7),3),
         RIGHT(UDPMessage, 3),
         UDPMessage,
         DateRecorded,
         DENSE_RANK() OVER (PARTITION BY udp.DIdentifier ORDER BY DateRecorded DESC) AS SortId
    FROM UDPTable udp WITH (NOLOCK)
        INNER JOIN FirmwareInfoTable fw ON udp.DIdentifier = fw.DIdentifier
    WHERE UDPMessage LIKE '%FWMessage:%'
        AND UDPMessage NOT LIKE '%FWMessage:LF%'
        AND fw.Status = 'HF Pending'
)

SELECT cte.DIdentifier,
    cte.CachedHFFWVersion,
    cte.CurrentHFFWVersion,
    cte.HFFWMessage,
    cte.HFFWMessageDate
FROM HFCTE cte
WHERE cte.SortId = 1
ORDER BY cte.DIdentifier DESC

This eliminates the need to join your CTE to itself in a subquery and further upstreams the logic for determining the latest records by date. Also the window functions tend to be rather performant for this kind of problem.


Select Statement with Temp Column driven by Bitmask

SQL Server has bitwise operations that you can use.
The & symbol provides an AND operation comparing bit to bit. Source: https://msdn.microsoft.com/en-us/library/ms174965.aspx

Here is code (not extended to all role cases) that should work:

CREATE TABLE #t
([Name] varchar(30),
[Role] int)

INSERT INTO #t
VALUES ('Jon',77)

SELECT [Name],
    CASE
    WHEN [Role] & 1 = 1 THEN 'One '
    ELSE ''
    END
    +
    CASE
    WHEN [Role] & 2 >= 2 THEN 'Two '
    ELSE ''
    END
    +
    CASE
    WHEN [Role] & 4 >= 4 THEN 'Three '
    ELSE ''
    END
    +
    CASE
    WHEN [Role] & 8 >= 8 THEN 'Four '
    ELSE ''
    END
FROM #t

The comparison only occurs where binary digits exist for both numbers, so we only have to care about the leading 1 of the result, thus the inequality.


This will insert new Name fields that were not previously in the Matrix table.

INSERT IGNORE
INTO AdminAccounts (Name)
SELECT Name
FROM Matrix;

If you do not trust INSERT IGNORE, there is an alternative where you can manifest the new Name values before inserting them:

CREATE TABLE NewName SELECT Name FROM Matrix WHERE 1=2;
INSERT INTO NewName
    SELECT Name FROM AdminAccounts A
    LEFT JOIN Matrix B USING (Name)
    WHERE B.Name IS NULL;
INSERT INTO Matrix (Name) SELECT Name FROM NewNames;

The table NewName collects only those tables in AdminAccounts that are not in Matrix at present. This can give you a chance to look over the new Names. Afterwards, you can INSERT everything in NewName into Matrix.


Mail alert from MySQL when databases are edited

MySQL has no particular SMTP setup, mechanisms, or drivers whatsoever built in.

However, there are two basic things you can do the kind of monitoring you want.

Option 1 : You Could Monitor the Binary Logs

If binary logs are enabled, you could write a shell script to call mysql and do SHOW MASTER STATUS; If either the filename or filesize changes, something changed. Once detected, you could send out an email expressing that something changed !!!

Try something like this:

FIRST_READ=1
while [ 1 -eq 1 ]
do
    mysql -h... -u... -p... --skip-column-names -A -e"SHOW MASTER STATUS" > /tmp/ms.txt
    currfile=`cat /tmp/ms.txt | awk '{print $1}'`
    currsize=`cat /tmp/ms.txt | awk '{print $2}'`
    if [ ${FIRST_READ} -eq 0 ]
    then
        SOMETHING_CHANGED=2
        if [ "${prevfile}" == "${currfile}" ] ; then (( SOMETHING_CHANGED-- )) ; fi
        if [ "${prevsize}" == "${currsize}" ] ; then (( SOMETHING_CHANGED-- )) ; fi
        if [ ${SOMETHING_CHANGED} -gt 0 ]
        then
            echo "Something Changed" | mail -s "Something Changed Subject" abc@xyz.com
        fi
    fi
    FIRST_READ=0
    prevfile=${currfile}
    prevsize=${currsize}
    sleep 10
done

Option 2 : You Could Monitor information_schema.tables

You could loop through every table and check its UPDATE_TIME column in information_schema.tables

First collect all table names prepended with database. Then, loop through all the table names and check that entry in information_schema.tables.

Try the following (Any table that changed in the last 10 minutes):

mysql -h... -u... -p... --skip-column-names -A -e"SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql') AND engine IS NOT NULL" > /tmp/TableNamesToPoll.txt

while [ 1 -eq 1 ]
do
    for DBTB in `cat /tmp/TableNamesToPoll.txt`
    do
        DB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $1}'`
        TB=`echo ${DBTB} | sed 's/\./ /g' | awk '{print $2}'`
        NEWUPDATE=`mysql -h... -u... -p... --skip-column-names -A -e"SELECT IFNULL(update_time,NOW() - INTERVAL 100 YEAR) > (NOW() - INTERVAL 10 MINUTE) UpdatedRecently FROM information_schema.tables WHERE table_schema='${DB}' AND table_name='${TB}'"`
        if [ ${NEWUPDATE} -eq 1 ]
        then
            echo "Something Changed in ${DBTB}" | mail -s "Something Changed Subject" abc@xyz.com
        fi
    done
    sleep 5
done

These are just skeleton scripts to detect changes. For option 1, you can do mysqlbinlog against the current binary log and see the SQL that was executed in whatever timeframe you need. For option 2, you can change the SQL to retrieve the datetime stamp of the last update for a given table.

UPDATE 2011-06-29 06:30 EDT

Option 3 : You Could Monitor the general log

Interestingly, you could activate the general log. What's even more intriguing is that you can activate it a MySQL Table. The template to the general log as a table already exists in /var/lib/mysql/mysql as general_log.CSV. Here are the steps:

Step 01) Add these to /etc/my.cnf

[mysqld]
log-output=TABLE
log

Step 02) service mysql restart (general_log is a CSV table after restart)

Step 03) Run these commands to convert the general_Log from CSV to MyISAM

SET GLOBAL general_log = 'OFF';
ALTER TABLE mysql.general_log ENGINE = MyISAM;
ALTER TABLE mysql.general_log ADD INDEX (event_time);

Step 04) Move the general_log file to a huge disk volume that can accommodate a fast growing log table

Example: If you have the following disk layout

[root@iml-db10 ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg1-root  117G  3.2G  108G   3% /
/dev/mapper/vg2-data01
                      1.7T  688G  877G  44% /data
/dev/sdc1             3.6T   36G  3.4T   2% /backup
/dev/sda1              99M   18M   77M  19% /boot
tmpfs                  95G     0   95G   0% /dev/shm
none                   16G   51M   16G   1% /var/tmpfs

Perform these steps to move the general log table:

mkdir /backup/general_log
mv /var/lib/mysql/mysql/general_log.MY* /backup/general_log/.
chown -R mysql:mysql /backup/general_log
ln -s /backup/general_log/general_log.MYD /var/lib/mysql/mysql/general_log.MYD
ln -s /backup/general_log/general_log.MYI /var/lib/mysql/mysql/general_log.MYI

When do make sure the symlinks exist

[root@db1]# ls -l /var/lib/mysql/mysql/general*
-rw-rw---- 1 mysql mysql 8776 Jun 25 15:53 /var/lib/mysql/mysql/general_log.frm
lrwxrwxrwx 1 root  root    35 Jun 25 18:33 /var/lib/mysql/mysql/general_log.MYD -> /backup/general_log/general_log.MYD
lrwxrwxrwx 1 root  root    35 Jun 25 18:32 /var/lib/mysql/mysql/general_log.MYI -> /backup/general_log/general_log.MYI

Step 05) Run this SQL command

SET GLOBAL general_log = 'ON';

That's it. You should have the general_log as a MyISAM table

mysql> show create table mysql.general_log\G
*************************** 1. row ***************************
       Table: general_log
Create Table: CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL,
  KEY `event_time` (`event_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='General log' DATA DIRECTORY='/backup/general_log/' INDEX DIRECTORY='/backup/general_log/'
1 row in set (0.00 sec)

All you need to do is poll the general_log table every 15 minutes via crontab running this query

SELECT COUNT(1) UpdateCount FROM mysql.general_log
WHERE LOCATE('UPDATE',argument) > 0
AND event_time >= (NOW() - INTERVAL 15 MINUTE);

SELECT COUNT(1) DeleteCount FROM mysql.general_log
WHERE LOCATE('DELETE',argument) > 0
AND event_time >= (NOW() - INTERVAL 15 MINUTE);

WARNING : Entries will pile up quickly. Delete all events keeping the last 3 days. Run this in a crontab every night a midnight

SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
DELETE FROM mysql.general_log WHERE event_time < NOW() - INTERVAL 3 DAY;
SET GLOBAL slow_query_log = @old_log_state;

Give this a Try !!!


Importing a .bacpac from SQL Azure into a local SQL Server 2016 server gives errors

Please download the latest version of SSMS to import the dacpac.

Download it from here.

Hope this helps.

Regards,

Alberto Morillo


Why is my SQL Server reporting CLR_AUTO_EVENT waits when I don't even use SQL CLR?

SQL Server uses CLR internally for several features, and these waits track CLR background threads just sitting around waiting on a ManualResetEvent or a AutoResetEvent.  According to High waits on CLR_MANUAL_EVENT and CLR_AUTO_EVENT these are background waits (ie the wait time is not happening to user sessions), and can safely be ignored unless you have unsafe CLR code using these events directly.


Storing the correct answer to check against

How about adding 2 Columns in Question_Option entity, one as IsCorrectAnswer and HasMultiAnswers ? You can letter check the counts of answers where HasMultiAnswers is set to true.


Why can't I alter a security policy conditionally?

Statements go through three processing steps - parsing, binding and execution, in that order. This batch is failing at binding since it cannot find an object to match the typed words. The work-around is to use dynamic SQL.

See a related question here.


Should I turn off GTID to fix this error?

Short answer: Yes, you can disable GTID without risk if you're not replicating from that Server.

Long answer:

The GTID is "a unique identifier created and associated with each transaction committed on the server of origin (master)." This helps when you have some slaves; so you can always track how is your slave (which statements have replicated, from which Server, and some further information; but if you're using the server as a Standalone, shouldn't have problems, you won't use your binlogs.

You should lookup for all the cnf files, (maybe in /usr...), there should be one configuring gtid, since it's disabled by default (https://dev.mysql.com/doc/refman/5.6/en/replication-options-gtids.html#sysvar_enforce_gtid_consistency); it's important to find it so you don't have problems if someday your DB restars; otherwise it'll be ON again.

For disabling it, you'll need to restart; the link you found for disabling it without restart applies for MySQL 5.7 and further.

Finally, what it'll mean for you to disable it, you'll have your binlogs without a clear "identifier", but you'll still have your binlogs in case you need them.


Is it allowed to for two tables to have two types of relationships between them?

This might sound a bit crazy, but as noted by ypercube in the answer linked in the comments, it is best to consider this a separate relation: for which we need a new table Question_CorrectAnswer, and it's PK is QuestionID.
This means that we only need to update a single row to change the correct answer.
Our only issue is that to prevent update anomalies, whereby someone could enter an Answer as being the correct one for a different Question, we must redefine Answer's primary key as being AnswerID, QuestionID, and FK on those two columns together.
So we define our tables like this:
(please don't take this as the best way to write CREATE TABLE, I've left out constraint names for example)
CREATE TABLE Question (
    QuestionId int IDENTITY PRIMARY KEY,
    Title varchar(100) NOT NULL
)

CREATE TABLE Answer (
    AnswerId int IDENTITY,
    QuestionId int NOT NULL REFERENCES Question (QuestionID),
    Body varchar(1000) NOT NULL,
    PRIMARY KEY (AnswerID, QuestionID)
)

CREATE TABLE Question_CorrectAnswer (
    QuestionId int NOT NULL PRIMARY KEY,
    AnswerId int NOT NULL,
    FOREIGN KEY (AnswerId, QuestionID) REFERENCES Answer (AnswerID, QuestionID)
)

It does have the downside of requiring an extra join, though.


Update: Tested all 5 queries in SQLfiddle with 100K rows (and 2 separate cases, one with few (25) distinct values and another with lots (around 25K values).

A very simple query would be to use UNION DISTINCT. I think it would be most efficient if there is a separate index on each of the four columns It would be efficient with a separate index on each of the four columns, if Postgres had implemented Loose Index Scan optimization, which it hasn't. So this query will not be efficient as it requires 4 scans of the table (and no index is used):

-- Query 1. (334 ms, 368ms)
SELECT a AS abcd FROM tablename
UNION                           -- means UNION DISTINCT
SELECT b FROM tablename
UNION
SELECT c FROM tablename
UNION
SELECT d FROM tablename ;

Another would be to first UNION ALL and then use DISTINCT. This will also require 4 table scans (and no use of indexes). Not bad efficiency when the values are few, and with more values becomes the fastest in my (not extensive) test:

-- Query 2. (87 ms, 117 ms)
SELECT DISTINCT a AS abcd
FROM
  ( SELECT a FROM tablename
    UNION ALL
    SELECT b FROM tablename
    UNION ALL
    SELECT c FROM tablename
    UNION ALL
    SELECT d FROM tablename
  ) AS x ;

The other answers have provided with more options using array functions or the LATERAL syntax. Jack's query (187 ms, 261 ms) has reasonable performance but AndriyM's query seems more efficient (125 ms, 155 ms). Both of them do one sequential scan of the table and do not use any index.

Actually Jack's query results are a bit better than shown above (if we remove the order by) and can be further improved by removing the 4 internal distinct and leaving only the external one.

Finally, if - and only if - the distinct values  of the 4 columns are relatively few, you can use the WITH RECURSIVE hack/optimization described in the above Loose Index Scan page and use all 4 indexes, with remarkably fast result! Tested with the same 100K rows and approximately 25 distinct values spread across the 4 columns (runs in only 2 ms!) while with 25K distinct values it's the slowest with 368 ms:

-- Query 3.  (2 ms, 368ms)
WITH RECURSIVE
    da AS (
       SELECT min(a) AS n  FROM observations
       UNION ALL
       SELECT (SELECT min(a) FROM observations
               WHERE  a > s.n)
       FROM   da AS s  WHERE s.n IS NOT NULL  ),
    db AS (
       SELECT min(b) AS n  FROM observations
       UNION ALL
       SELECT (SELECT min(b) FROM observations
               WHERE  b > s.n)
       FROM   db AS s  WHERE s.n IS NOT NULL  ),
   dc AS (
       SELECT min(c) AS n  FROM observations
       UNION ALL
       SELECT (SELECT min(c) FROM observations
               WHERE  c > s.n)
       FROM   dc AS s  WHERE s.n IS NOT NULL  ),
   dd AS (
       SELECT min(d) AS n  FROM observations
       UNION ALL
       SELECT (SELECT min(d) FROM observations
               WHERE  d > s.n)
       FROM   db AS s  WHERE s.n IS NOT NULL  )
SELECT n
FROM
( TABLE da  UNION
  TABLE db  UNION
  TABLE dc  UNION
  TABLE dd
) AS x
WHERE n IS NOT NULL ;

SQLfiddle

To summarize, when the distinct values are few, the recursive query is the absolute winner while with lots of values, my 2nd one, Jack's (improved version below) and AndriyM's queries are the best performers.

Late additions, a variation on the 1st query which despite the extra distinct operations, performs much better than the original 1st and only slightly worse than the 2nd:

-- Query 1b.  (85 ms, 149 ms)
SELECT DISTINCT a AS n FROM observations
UNION
SELECT DISTINCT b FROM observations
UNION
SELECT DISTINCT c FROM observations
UNION
SELECT DISTINCT d FROM observations ;

and Jack's improved:

-- Query 4b.  (104 ms, 128 ms)
select distinct unnest( array_agg(a)||
                        array_agg(b)||
                        array_agg(c)||
                        array_agg(d) )
from t ;


Forums about getting SQL Server 2000 Administration help?

This forum or ServerFault.

In the 13 years i've been working with SQL Server since 6.5 I've frequented several (SQL Server Central, SQL Server Performance etc) but it's hard to discern quality answers on these.

The reputation system on the Stack Exchange sites is a powerful tool to ensure higher quality answers as well weeding out the poor ones.

I can't understand your worries about upgrading: unless you have some code that (ab)uses sp_OA% or such you should be OK. The Upgrade Advisor will assist you here


Dynamic where condition in a stored procedure

You'll need to execute your SQL statement. Use execute immediate for this. And because you're selecting a value you'll need to execute immediate ... into it, something like this (replacing 3 > 2 with your own where condition:

CREATE OR REPLACE PROCEDURE test(p_table IN varchar2) IS
  v_sqltxt varchar2(4000);
  v_count integer;
BEGIN
  v_sqltxt:='select count(*) from '||p_table||' where 3 > 2';

  execute immediate v_sqltxt into v_count;

  dbms_output.put_line(v_count);
END;
/


Multiple Instances and Availability Groups

This can happen due to the TCP ports configuration of your instances and listeners

  If one of the instances of SQL Server is already listening on TCP port
  1433 via the instance listener and there are no other services
  (including additional instances of SQL Server) on the computer
  listening on port 1433, this will not cause a port conflict with the
  availability group listener. This is because the availability group
  listener can share the same TCP port inside the same service process.
  However multiple instances of SQL Server (side-by-side) should not be
  configured to listen on the same port.

Source

In short, you can have both your listeners listening on port 1433, as long as you change the tcp ports for the instances to different ports like 1533, 1633.

You could also use different port numbers than 1433 but then you need to specify these in the connection string(s).

In the case of "one instance, one listener" you could be using the same port for both.


How to restore database using old full backup and current log file

It was essential that your database was in the Full recovery model and no transaction log backups were created in the last 15 days. That provided that all transactions that happened after the full database backup were still in your LDF file. Otherwise, there would have been a gap between your LDF and full database backup transactions and you would not be able to recover everything successfully (maybe that was the case)

There's another tool that can be used in the same scenario to read the transactions from the LDF file and create a redo script for them - ApexSQL Log.


The default Pg config is almost certainly sub-optimal, it's a conservative default intended for "will run anywhere" not "will be fast".

There's lots to be said on that; see http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server, http://wiki.postgresql.org/wiki/Performance_Optimization, and Greg Smith's "PostgreSQL 9.0 High Performance". (By way of disclaimer, I now work with Greg at 2ndQuadrant, but I was recommending his book before I moved to 2ndQuadrant).

I won't repeat what's already covered in those references.

I advise that you run pg_test_fsync to see whether fsync() performance is acceptable on your I/O subsystem, as this is critical for decent transaciton-processing throughput. It doesn't matter as much for read-mostly workloads. If it's poor, consider getting a couple of high quality SSDs with proper built-in power-fail protection or a RAID controller with a battery-backed write cache. By way of comparison, my single cheap SSD laptop tends to hit about 5000 ops/sec in pg_test_fsync.

Also use sysbench to get some general idea of what disk seq and random throughput are like. This will be useful when you're adjusting random_page_size and seq_page_size.

Most importantly of all, use a connection pooler like PgBouncer instead of raising max_connections above a couple of hundred.


SELECT DISTINCT ON, ordered by another column

This is a classic greatest-n-per-group problem. They frequently arise in a whole host of areas and, like Analytic functions (see below) are well worth studying.

Nowadays, it is typically solved by using Analytic (aka Window) functions - see the fiddle here.

You can use this query -

WITH cte AS
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS rn,
    col1, col2, col3
  FROM test
  ORDER BY col3 DESC
)
SELECT * FROM cte
WHERE rn = 1

Result -

rn  col1    col2    col3
1   2   xyz     2015-09-13
1   1   abc     2015-09-11
1   3   tcs     2015-01-18

Analytic functions are well worth getting to know - they are very powerful and you will find that they will repay you many times  for any effort you put into learning them. Run the inner query on its own - experiment, it's how I learnt. BTW, it's always worth tagging your question with the version of PostgreSQL that you are using!

A more traditional method of doing this would be

SELECT x, y, mc FROM
(
  SELECT col1 AS x, col2 AS y, MAX(col3) AS mc
  FROM test
  GROUP BY col1, col2
) AS tab
ORDER BY mc

Same result - also on the fiddle.


Is it possible to validate DML statements?

I can only speak for SQL Server, but the only way this could be done is through a DML trigger (as far as I know).  Take the below for example:

use TestDB;
go

create table dbo.TestTriggerTable
(
    id int identity(1, 1) not null,
    some_int int not null
        default 1
);
go

insert into dbo.TestTriggerTable
default values;
go 1000

create trigger DeleteMaxRows
on dbo.TestTriggerTable
after delete
as

    if (select count(*) from deleted) > 10
    begin
        raiserror('Cannot delete more than 10 rows', 16, 1);
        rollback;
    end
go

select *
from dbo.TestTriggerTable;

-- this fails and rolls back
delete from dbo.TestTriggerTable
where id < 20;
go

-- this is successful
delete from dbo.TestTriggerTable
where id < 8;
go

There are a few problems with this approach though, and I don't believe this to be a good design.  First off, you have hard-coded requirements within the trigger (whether you're doing a set row count, or a percentage of rows...it's still hard-coded).  An application would have to be designed accordingly to ensure retry with different logic.

What this really comes down to is having a correctly designed database, and correctly constructed DML statements to ensure that the data that is being modified is the data you want, instead of having to check a row count of the deleted rows.

In my opinion, you should be spending design time in figuring out how to redesign so you won't need this check.


In MySQL 5.5: can I capture the output of SHOW ERRORS?

Use

SELECT *
FROM sys.statements_with_errors_or_warnings
WHERE last_seen > NOW() - INTERVAL ??? MINUTE;

Set a reasonable time limit.

Or query to performance_schema, tables events_statements_current, events_statements_history and events_statements_history_long.


How to avoid tons of lower(column) in WHERE clause for several LIKE comparisons?

I would use join here instead of old writing way
Regarding "lower" are you sure that this is needed?
Did you check collation of new_value?
If it has CI(case insensitive) in collation name it you can avoid using lower.

If you do, you can write something like this

WITH cte
    AS ( SELECT history.date
               ,history.new_value
               ,res.resource_name
               ,LOWER(history.new_value) AS new_value
         FROM   item_history history
         JOIN   owned_resource res
                ON (   history.attr_name = 'resource_contact'
                       OR history.attr_name = 'Kontakt-UUID' )
                   AND res.inactive = 0
                   AND history.com_par_id = res.own_resource_uuid )
SELECT *
FROM   cte
WHERE  new_value NOT LIKE '%tp für%'
       AND new_value NOT LIKE '%rücklieferung%'
       AND new_value NOT LIKE '%rückläufer%'
       AND new_value NOT LIKE '%stoerreserve%'
       AND new_value NOT LIKE '%zentrallager%'
       AND new_value NOT LIKE '%bhs-pool%'
       AND new_value NOT LIKE '%lager halle%'
       AND new_value NOT LIKE '%lager logistik%'
       AND new_value NOT LIKE 'reserve %'
       AND new_value NOT LIKE '%igeko%bhs%'
       AND new_value NOT LIKE '%service%ecg%'
       AND new_value NOT LIKE '%multifunktionsdrucker%'
       AND new_value NOT LIKE 'nn%gisa%raum%'
       AND new_value NOT LIKE '%citrix%admins%'
       AND new_value NOT LIKE '%personalwesen%'
       AND new_value NOT LIKE '%etagendrucker%'
       AND new_value NOT LIKE '%schulungsraum%'
       AND new_value NOT LIKE '%team%raum%'
       AND new_value NOT LIKE '%beratungsraum%'
       AND new_value != 'reserve';


AWS - MySQL server "Service CPU Usage" stuck at 50% / being throttled?

This is probably neither throttling nor a misconfiguration, nor anything related to AWS. Consider these points:

A c4.large instance has 2 cores.
Any given query running in MySQL runs entirely within a single thread, and
a single thread within a process can only run on 1 core at any moment in time (though presumably the Windows process scheduler can move it between the cores at will), and
100% utilization of 1 core by 1 thread would show up as 50% CPU utilization.

What you appear to have here, rather than the server really being "under load," is a single CPU-bound query running.

Any user with the PROCESS privilege can run the SHOW FULL PROCESSLIST; query in MySQL and see what all the session (connection) threads in MySQL Server are doing at the moment.  Run this and you should find your CPU hog.  If you run another instance of the same query, you should find that the CPU utilization hits 100% with two CPU expensive queries running concurrently.  (Then you'll want to fix/optimize that query.) The TIME column tells you how long the connection has been in its current state.


Failed to start service, error 2. TNS-12560: TNS:protocol adapter error TNS-00530: Protocol adapter error

Check the tnsnames.ora file in below navigation directory:

Drive_Name:\OraHome_1\network\ADMIN

Check the file property with installed oracle service details

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10..)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PLSExtProc)
    )
  )

Also check the tnslistener along with it's service.


Access denied for user 'myuser'@10.% to database 'mydb' - Mysql Import SQL Script

The message itself looks like an authentication issue.

Here is what you should do: If you can connect to the hosting company's mysql database from the mysql client, run this command:

SELECT USER() RequestedUserLogin,CURRENT_USER() AllowedUserLogin;

What will these functions give you ?

USER() reports how you attempted to authenticate in MySQL
CURRENT_USER() reports how you were allowed to authenticate in MySQL

It's the second function you need to be concerned with because it says you have the same user permissons as AllowedUserLogin.

@Abdul's answer reveals a deeper issue: You created the mysqldump which has the command to create the database. When you mysqldump with the --databases option, the create database mydb; is inserted before the use mydb;. You can hide the create database mydb; by doing

mysqldump -u... -p... --no-data --databases mydb > mydbschema.sql
mysqldump -u... -p... --no-create-info      mydb > mydbdata.sql

This will place the database and table creation statements in mydbschema.sql while the bulk INSERTs are placed in mydbdata.sql.

If you cannot run mydbschema.sql then have the hosting company create the database for you. You could also use whatever DB tools they have setup for you to create the database yourself.

You could then load the data using

mysql -umyuser -pmypassword -h(IP-of-DBServer) -Dmydb < mydbdata.sql

I also noticed the user mysql was expecting 'myuser'@'10.%' as the user to authenticate. You might need 'myuser'@'IP-of-DBServer' defined on the database mydb with this:

GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'IP-of-DBServer';

Give it a Try !!!


Migration Wizard Offline SQL Server to Oracle, before Move Data, directory Data is empty

I didn't see a thing

After you executed Move Data, remember that Migration is between SQL Server to Oracle, the SQL Developer generate some scripts and one of that scripts generated, call MicrosoftSQLServer_data.bat, you must run that script and it'll bring the data to you complete the Migration


REVOKE SELECT ON v_catalog.users FROM public;
That should work. If you want more details check this: https://www.techonthenet.com/sql_server/grant_revoke.php


Ec2Snitch is specifically designed for Cassandra being installed in AWS EC2 instances. Please confirm if your installation is in AWS? In case you are, check whether listen address in cassandra.yaml is set to the private ip of the node (instead of localhost)

SimpleSnitch should work fine for Single datacenter. Its recommended to use GossipingPropertyFileSnitch for production.


"on duplicate key update" all the non-key fields mentioned in the insert

You can use the VALUES(column) function:

insert into tablename set keyname = 'keyvalue',
    fieldname = 'fieldvalue'
    . . .
    on duplicate key update
        updatecounter = updatecounter + 1
        fieldname = VALUES(fieldname)
        . . .;


Okay, I think that I'm beginning to understand the nuance of your question.
Given that space efficiency is a high priority, I think that you can strike the varchar(max) option from your list. Since your minimum file size is 11K, nothing is going to fit on a single page. All the files will be stored as BLOBs. SQL Server won't compress that (aside from compressing the backup), so you're not going to save any space with that approach.
Next, I'm going to make the assumption that processing time can be traded for space efficiency. In that case, I would recommend that you compress the files yourself before storing them and decompress before displaying them. This goes for any solution you end up going with. You should end up with better compression than letting the file system handle it (which applies if you use a Filestream).
So I think that your question boils down to Filestream (and possibly FileTable) or VARBINARY(MAX). The standard answer provided by Microsoft is:

When to Use FILESTREAM
In SQL Server, BLOBs can be standard
varbinary(max) data that stores the data in tables, or FILESTREAM
varbinary(max) objects that store the data in the file system. The
size and use of the data determines whether you should use database
storage or file system storage. If the following conditions are true,
you should consider using FILESTREAM:

Objects that are being stored are, on average, larger than 1 MB.
Fast read access is important.
You are developing applications that use a middle tier for application logic.

For smaller objects, storing varbinary(max) BLOBs in the
database often provides better streaming performance.

I think that you will need to reassess your figures using compressed file sizes, and then make a decision based off of that.


Extract some columns data from one table and insert into multiple tables

Assuming that you are preserving the id assocaited with each name and lastname, run this:

INSERT INTO tbl_2 (id,name) SELECT id,name FROM tbl_1;
INSERT INTO tbl_3 (id,lastname) SELECT id,lastname FROM tbl_1;

If tabless tbl_2 and tbl_3 already have data and you wish to overwrite it, run this:

REPLACE INTO tbl_2 (id,name) SELECT id,name FROM tbl_1;
REPLACE INTO tbl_3 (id,lastname) SELECT id,lastname FROM tbl_1;

If you are not preserving the ID, then run this:

INSERT INTO tbl_2 (name) SELECT name FROM tbl_1;
INSERT INTO tbl_3 (lastname) SELECT lastname FROM tbl_1;


Oracle 11g Express Edition - missing or invalid schema authorization identifier

CREATE SCHEMA

  Purpose

  Use the CREATE SCHEMA statement to create multiple tables and views and perform multiple grants in your own schema in a single transaction.

Well you have multiple issues. First of all you need to have CREATE SESSION system privilege granted to the newly created user. Secondly your data types are incompatible in Oracle(int should be converted to number and text to varchar2).

In order to use CREATE SCHEMA statement you need to be connected to database as the user which you have specified on the statement.

The problem is you are not connected to database as xauthority by altering the session.

SQL> conn sys/password
Connected.

SQL> create user XAuthority identified by mysecret;

User created.

SQL> alter session set current_schema = XAuthority;

Session altered.

SQL> create schema authorization XAuthority
create table CUSTOMER(ID number, CUSTOMER_name varchar2(20))
create view CUSTOMER_VIEW as select * from CUSTOMER
grant select on CUSTOMER_VIEW to xareadonly;  2    3    4
create schema authorization XAuthority
*
ERROR at line 1:
ORA-02421: missing or invalid schema authorization identifier

SQL> show user
USER is "SYS"

The user should be xauthority but it is still SYS.

You need create session system privilege to connect to the database.

SQL> grant create session to xauthority;

Grant succeeded.

You need quota on tablespace to create schema objects such as table.

SQL> alter user xauthority quota unlimited on orapdb1_tbs1;

User altered.

SQL> conn xauthority/mysecret
Connected.

SQL> conn xauthority/mysecret
Connected.
SQL> create schema authorization XAuthority
create table CUSTOMER(ID number, CUSTOMER_name varchar2(20))
create view CUSTOMER_VIEW as select * from CUSTOMER;

Schema created.


By all means, remove them. Those are just temporary tables mysqld had written to disk.

In all likelihood, mysqld probably crashed when those temp tables were made.

Look at the timestamps:

Max is 2012-10-09
Min is 2012-08-21

The sum of these files are over 1G. They are just taking up room.

I know they are temp tables because

MySQL uses MyISAM as the Storage Engine for Temp Tables
Temp Tables have no .frm, which prohibit DB sessions from accessing them directly in SQL

You can remove the corresponding .MYI files as well. Those are the index file components of teach temporary MyISAM tables. They are never populated because temp tables are never indexed.


SQL Server 2016 SP1 CU2 AlwaysOn AG Secondary always disconnected

CREATE AVAILABILITY GROUP [AG_SQL]
FOR
--DATABASE SetupAG

If this is what you're using (copied from your question) then I would expect it to say disconnected. You've created an empty availability group (no databases, or database only on the primary) so there is nothing to send or receive other than some metadata about the AG.

The fact that it works when you choose automatic seeding means you're adding a database into the AG, it's now no longer empty and should show connected (if it truly is connected).

  On the secondary dm_hadr_database_replica_states is empty and the database doesn't show up in sys.databases. When I look in SSMS the database is shown in 'Availability Databases'

This is because there is no database on the secondary server, which completely falls inline with what you've posted. The "database" shows up under the Availability Groups folder on the secondary because that's the metadata of the Availability Group, not because the database is on the secondary.

The reason it "works" with automatic seeding is because automatic seeding creates the database and seeds it on the secondary for you versus what you're doing now which is creating the AG but not preparing the database on the secondary. Thus, no database on secondary.

  Is this a bug in SQL Server 2016 SP1 Standard CU2 or is there something wrong with my T-SQL statement?

Neither, it's a process issue where the secondary wasn't prepared properly.


SQL Server 2012 Express : How do you limit a database's size and retention policy?

You can set the database's max file size to 9GB, but you will need to do the purging yourself.  This is not something that SQL Server supports.  You could get notified of the limit being reached using extended events, but there is no way for SQL Server to know what records to delete without you telling it.  SQL Server does not log or otherwise keep track of how old a row in a table is.  You have to build it into the table schema and maintain your own aging logic.

To set the file size in SQL Server Management Studio:

Right-click the database and get properties
Click the Files link in the menu on the left
Click the ... button under Autogrowth / maxsize and set the properties accordingly

T-SQL to set the file size:

ALTER DATABASE MyDb
MODIFY FILE
(
    NAME=N'MyDataFile',
    MAXSIZE=9000MB
)

Note: MyDataFile is the logical name of your data file, not the physical file name on the filesystem.  You can find the logical file names of your database files with the sys.database_files system view:

USE MyDb
SELECT name
FROM sys.database_files
WHERE type_desc = 'ROWS'

The value in the name column is the logical name of the file.


Unix timestamp vs timestamptz index performance

To answer your basic question: No. timestamptz is stored as 64 bit integer quantity internally (same as int8). An index on it performs identically to one on  a bigint (int8) column - when used correctly. Related:

Ignoring time zones altogether in Rails and PostgreSQL

If in doubt, go with timestamptz. It's built for the purpose. The only argument in favor of Unix timestamps would be that you already have them and use them elsewhere.
As for the query plan added later. Consider a multicolumn index like bbaird suggested. See:

Multicolumn index and performance

You might rewrite the table physically to help this particular query. CLUSTER takes an exclusive lock. To deal with concurrent write load consider pg_rewrite or pg_squeeze. Either requires excess storage and time for a big table. See:

Configuring PostgreSQL for read performance


As a general rule every table ought to have a natural key because it usually isn't a good idea to duplicate information in a table. Only add a surrogate key if and when you find there is a reason to do so.


SQL Server 2012 Not Using Parallel Cores for Parallel Connections - Scheduler Problem?

First, inserting data into a table variable is single-threaded (not parallel). You can learn more about that in Paul White's epic post Fording a Parallel Execution Plan.

Second, when you're running multiple queries, you can't predict which queries will end up on which cores. An incoming query gets assigned to a worker, which is pinned to a scheduler, which runs on a CPU core. SQL Server doesn't know in advance how complex the query (or queries) will be, so it can't predictively load balance. If you truly want to get the CPU to 100%, you'll want to run a lot more than 3 queries, or you'll want to check DMVs to abort your queries if they end up on the same scheduler.

Or do what I would do - stop using table variables and switch to a temp table instead.