Backup with mongoexport or mongodump? For production backup, MongoDB recommends mongodump instead of mongoexport for accuracy of data. However, I would need to "scrub" data off a MongoDB database before backing it up. I am not aware of any server-side data scrubbing options other than mongoexport. Two questions:

Does mongoexport access MongoDB cache in RAM? Would it alter the working set in RAM like mongodump does?
mongodump command has this --query option. "Provides a JSON document as a query that optionally limits the documents included in the output of mongodump." Does it take a query to exclude certain fields in a document?

(sorry, dunno)
--query will select or exclude entire documents, not fields.

It would be a good idea though :

--query '{datetime:{$gt:ISODate("2014-01-01T00:00:00.000Z")}},{_id:0,name:1,address:1,interests:1}'* )

Mongodump uses bson file structure and preserves the data types. Mongoexport will lose data type of the values. Such as NumberLong("1431677405876") would be converted to just 1431677405876. That's why mongodump is advised if you need to import back to MongoDB.


Is it possible to prevent an insert without rolling it back? In MySQL 5.5, I know I can prevent an insert by using SIGNAL, which rolls back the current transaction of my INSERT.

The fact is I want to use a trigger to insert a row in another table,
but I'd like that to replace the original insertion. If I use SIGNAL in the trigger the new insert statement is also cancelled.

Example:

If I do

INSERT INTO table_a VALUES('a','b');

I want to have a new row in a table_b but none in the table_a.

(I want to replace the original insertion only in a few cases.)

Is it possible in MySQL?
If yes, how can I do this?

I am not readily availble to provide exact details but I believe 2 more ways are possible:

Using blackhole engine on table_a
Using proxysql

Using Blackhole engine

It seems to be the simplest solution.

  The BLACKHOLE storage engine acts as a “black hole” that accepts data but throws it away and does not store it.

See MySQL 5.5 doc for more details.

First, if you need to keep the table_a you need to create a copy of it with the blackhole engine ;

CREATE TABLE table_a(
    id    INT unsigned auto_increment primary key,
    field CHAR(5)
)Engine=InnoDB;

CREATE TABLE table_blackhole(
    id    INT unsigned auto_increment primary key,
    field CHAR(5)
)Engine=BLACKHOLE;

Then, create the trigger on table_blackhole.
Inside the trigger, use

an INSERT on table_a (for example) for the normal treatment (we don't prevent the insertion)
or an INSERTon table_b for the specific treatment (we "prevent" the normal insertion but there is no rollback).

Instead of inserting rows in table_a, do your INSERT on table_blackhole.


Recursive Manager - Oracle 10g x 11g We have a problem adapting a recursive SQL from 11g to 10g.

Oracle 11g Query ( Working as expected )

WITH ADRECURSIVEUSERLEADER (CDLEADER,CDUSER,NMUSER,FGUSERENABLED)
AS
(
    SELECT US.CDLEADER AS CDLEADER
          ,US.CDUSER AS CDUSER
          ,US.NMUSER
          ,US.FGUSERENABLED
      FROM ADUSER US
     WHERE 1 = 1
     UNION ALL
    SELECT RS.CDLEADER
          ,US.CDUSER
          ,US.NMUSER
          ,US.FGUSERENABLED
      FROM ADRECURSIVEUSERLEADER RS
           INNER JOIN ADUSER US
               ON RS.CDUSER = US.CDLEADER
     WHERE 1 = 1
)
SELECT *
  FROM ADRECURSIVEUSERLEADER T
 WHERE 1 = 1;

Oracle 10g Query ( What we are trying )

WITH ADRECURSIVEUSERLEADER
AS
(
    SELECT US.CDLEADER AS CDLEADER
          ,US.CDUSER AS CDUSER
          ,US.NMUSER
          ,US.FGUSERENABLED
      FROM ADUSER US
     START WITH US.CDLEADER IS NULL         -- parent
   CONNECT BY PRIOR US.CDUSER = US.CDLEADER -- child / parent
)
SELECT *
  FROM ADRECURSIVEUSERLEADER T
 WHERE 1 = 1;

What is the problem?

We have the following leader structure:

User code (CDUSER)    5 is the CEO
User code (CDUSER)   21 is the Manager   (Leaded by 5)
User code (CDUSER)  937 is the Leader    (Leaded by 21)
User code (CDUSER) 1276 is the Developer (Leaded by 937)

If I search for a leader on 10g and 11g :

AND T.CDLEADER = 21

11g shows me two records:

One for  937 ( Leader )
One for 1276 ( Developer )

10g shows me only one record:

One for  937 ( Leader )

SQL Fiddle Example

DDL for Oracle:

CREATE TABLE ADUSER (CDUSER          NUMBER(10)
                    ,CDLEADER        NUMBER(10)
                    ,FGUSERENABLED   NUMBER(2)
                    ,NMUSER          VARCHAR2(255) );

INSERT INTO ADUSER VALUES (   5, NULL, 1, 'CEO Name');
INSERT INTO ADUSER VALUES (  21,    5, 1, 'Manager Name');
INSERT INTO ADUSER VALUES ( 937,   21, 1, 'Leader Name');
INSERT INTO ADUSER VALUES (1276,  937, 1, 'Developer Name');

What I need?

I need to know everyone under the leader I search and the filter must me apllied on the (ADRECURSIVEUSERLEADER T) because it's going to be a view . Is that possible on Oracle 10g?

Thanks a lot

If you want to find the people "under" CDLEADER = 21, then why do use START WITH US.CDLEADER IS NULL in your CONNECT BY? Just simply use START WITH US.CDLEADER = 21:

WITH ADRECURSIVEUSERLEADER
AS
(
    SELECT US.CDLEADER AS CDLEADER
          ,US.CDUSER AS CDUSER
          ,US.NMUSER
          ,US.FGUSERENABLED
      FROM ADUSER US
     START WITH US.CDLEADER = 21         -- parent
   CONNECT BY PRIOR US.CDUSER = US.CDLEADER -- child / parent
)
SELECT *
  FROM ADRECURSIVEUSERLEADER T
 WHERE 1 = 1;

SQL Fiddle


How do I create a non localhost connection with MySQL? I'm currently hosting a small website on my college domain where people can fill out a form and post it to database.  I'm trying to figure out how I can set up/edit my MySQL so that its not localhost.

This is the current setting:

http://i.imgur.com/WP0KIml.png

Obviously when people fill out the form right now it fails since my MySQL is on localhost.  I tried setting the host name to my IP but that didn't work as well.

If I have understand you, what you want is to connect to your mysql server, from another PC different of which is running your mysql service.
In that case, what you need to enter in the Hostname, is the IP address of your mysql server.


Potential corruption in sys.sysdercv, latch errors from ghost cleanup process We're running SQL Enterprise 2016 (13.0.4411).  I have a database that was using service broker to handle some workflow, long story short, it was designed incorrectly and none of the conversations were closed correctly.  The sys.sysdercv system table blew up to about 270,000,000 rows and 140GB in size.

I created an agent job that grabbed the top 50,000 conversation handles from sys.conversation_endpoints in DI or DO state, and closed them with cleanup.  This agent job ran for a couple of weeks, and finally got the majority of the table cleaned out.  However, now it appears that there may be a corrupted page in sys.sysdercv.

The ghost cleanup process (spid 596 in this case) has been chugging along for about 37 hours now.  Checking it with sp_whoisactive shows that it's trying to get a lock on sys.sysdercv.  I'm also seeing the following error in the SQL log:

A time-out occurred while waiting for buffer latch -- type 3, bp 000000FA1CA98B80, page 1:21335899, stat 0x10b, database id: 13, allocation unit Id: 281474981494784/4784128, task 0x000000AF954AACA8 : 0, waittime 300 seconds, flags 0x100000001a, owning task 0x000001B98D8D04E8. Not continuing to wait.

The allocation unit ID corresponds to the clustered index on sys.sysdercv.  The owning task address corresponds to the ghost cleanup process.  I tried to run the following:

DBCC PAGE (13, 1, 21335899, 0) WITH TABLERESULTS;

This just hangs and doesn't return anything (sp_whoisactive shows that it's blocked by the ghost cleanup process).  I also tried to run this:

DBCC CHECKTABLE ('sys.sysdercv') WITH TABLOCK, ALL_ERRORMSGS;

Same deal, it hangs, then eventually times out with a latch error complaining about the same page.

At this point, I'm unsure of what to do.  There is no data I currently care about in sys.sysdercv or sys.sysdesend (no messages queued at the moment), so I was hoping I could just truncate these tables, which would (ideally) get rid of the offending page.  I can't truncate system tables though.  I can't kill the ghost process in order to do DBCC CHECKTABLE.

I thought about stopping the SQL service, enabling trace flag 661 on startup to prevent the ghost cleanup process from running, and then doing the table check.  However, I'm also seeing this error message:

One or more recovery units belonging to database 'xxx' failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.

So I don't know if the database will even come back online if I restart the SQL service.  My differential backups have been failing due to this issue, and DBCC OPENTRAN shows that the ghost cleanup is preventing the log space from being reused.

So...all that said, is there anything at all I can do to fix this problem?  Is there some magical trace flag that would allow me to truncate system tables?  Can I safely restart SQL without the ghost cleanup process in order to run DBCC CHECKTABLE (as in, will my database come back online since it hasn't been able to checkpoint in the past day and a half)?

Any help would be greatly appreciated.

UPDATE: The problem has been resolved. Took a lot of screwing around, but we were able to take the instance down, bring it back up, put the database in emergency mode, and ran a DBCC CHECKTABLE with REPAIR_ALLOW_DATA_LOSS (we were able to fix it in a test restore with REPAIR_REBUILD, but emergency mode won't let you use that option, apparently). That fixed the corruption in sys.sysdercv, and everything is back to normal.  Still not sure what ended up causing it, but backups are working again, DBCC CHECKDB has been coming back clean.  Chalking it up to "stuff happens", I guess.


DB2 - cannot invalidate a view I work on something and I need to check some views. I use this code:

SELECT SUBSTR(T.TABNAME,1,32) AS TABLE, SUBSTR(T.TABSCHEMA,1,32) AS SCHEMA, T.STATUS, T.TYPE FROM SYSCAT.TABLES T WHERE T.TABNAME LIKE 'TEMP_T%'

To check if it works I:

added a table,
created a view of "SELECT * FROM TEMP_T",
altered the table by adding a new column,
and check the status again.

From what I understand (did a bit of research) this should invalidate the view, but it didn't. I also removed the initial column of the table (after creating the view), but the view still appears to be valid. In the end I even droped the table, but the view still is in STATUS='N'.

Can you guys help me understand why is this happening? I assume it's a bug in DB2, but as I'm new to DB I'd like to hear some other opinions.

Here is the log:

CREATE TABLE DB2INST1.TEMP_T ( COL_A  CHAR(5) )
DB20000I  The SQL command completed successfully.

CREATE VIEW DB2INST1.TEMP_V AS   SELECT * FROM DB2INST1.TEMP_T
DB20000I  The SQL command completed successfully.

SELECT SUBSTR(T.TABNAME,1,32) AS TABLE, SUBSTR(T.TABSCHEMA,1,32) AS SCHEMA, T.STATUS, T.TYPE FROM SYSCAT.TABLES T WHERE T.TABNAME LIKE 'TEMP_%'

TABLE                            SCHEMA                           STATUS TYPE
-------------------------------- -------------------------------- ------ ----
TEMP_T                           DB2INST1                         N      T
TEMP_V                           DB2INST1                         N      V

  2 record(s) selected.

ALTER TABLE DB2INST1.TEMP_T ADD   COLUMN COL_B VARCHAR(20)
DB20000I  The SQL command completed successfully.

ALTER TABLE DB2INST1.TEMP_T DROP  COLUMN COL_A
DB20000I  The SQL command completed successfully.

REORG TABLE DB2INST1.TEMP_T
DB20000I  The REORG command completed successfully.

SELECT SUBSTR(T.TABNAME,1,32) AS TABLE, SUBSTR(T.TABSCHEMA,1,32) AS SCHEMA, T.STATUS, T.TYPE FROM SYSCAT.TABLES T WHERE T.TABNAME LIKE 'TEMP_%'

TABLE                            SCHEMA                           STATUS TYPE
-------------------------------- -------------------------------- ------ ----
TEMP_T                           DB2INST1                         N      T
TEMP_V                           DB2INST1                         N      V

  2 record(s) selected.

DROP TABLE DB2INST1.TEMP_T
DB20000I  The SQL command completed successfully.

SELECT SUBSTR(T.TABNAME,1,32) AS TABLE, SUBSTR(T.TABSCHEMA,1,32) AS SCHEMA, T.STATUS, T.TYPE FROM SYSCAT.TABLES T WHERE T.TABNAME LIKE 'TEMP_%'

TABLE                            SCHEMA                           STATUS TYPE
-------------------------------- -------------------------------- ------ ----
TEMP_V                           DB2INST1                         N      V

  1 record(s) selected.

As for the view being valid after dropping a column: The view is defined as SELECT * so the view is still valid.

Regarding the validity of the view after dropping the table just use field VALID from SYSCAT.VIEWS, see below.

db2 => DROP TABLE DB2INST1.TEMP_T
DB20000I  The SQL command completed successfully.
db2 => SELECT SUBSTR(VIEWNAME,1,32) AS TABLE, VALID FROM SYSCAT.VIEWS WHERE VIEWNAME='TEMP_V'

TABLE                            VALID
-------------------------------- -----
TEMP_V                           N

  1 record(s) selected.

db2 => CREATE TABLE DB2INST1.TEMP_T ( COL_A  CHAR(5) )
DB20000I  The SQL command completed successfully.
db2 => SELECT SUBSTR(VIEWNAME,1,32) AS TABLE, VALID FROM SYSCAT.VIEWS WHERE VIEWNAME='TEMP_V'

TABLE                            VALID
-------------------------------- -----
TEMP_V                           N

  1 record(s) selected.

db2 => SELECT * FROM DB2INST1.TEMP_V

COL_A
-----

  0 record(s) selected.

db2 => SELECT SUBSTR(VIEWNAME,1,32) AS TABLE, VALID FROM SYSCAT.VIEWS WHERE VIEWNAME='TEMP_V'

TABLE                            VALID
-------------------------------- -----
TEMP_V                           Y

  1 record(s) selected.


Altering table on SQL Server 2012 AON High Availability Databases In a production site, the current configuration I am using is three SQL Server 2012 servers, with some databases configured with AON High Availability. I understand that between the AON High Availability databases, data would be replicated, but I was wondering if this remains true for the table definition. By my understanding, I believe that table structures not to be handled manually. CMIIW.

Assuming that is correct, what is the best way for me to deploy changes in table structure with minimal downtime?

Update:

It seems I missed out a similar question but with different wording, but nevertheless it answers my question: Do schema changes "break" SQL Server 2012 AlwaysOn or are they handled transparently?

Even though it's a duplicate question, but I think I'll keep this just for those who is searching it with a different wordings.

It turns out that changes in table structure (schema) is automatically handled, so changes in the primary database should be also reflected in the secondary ones.

  Schema changes and data changes are essentially the same. It works like traditional mirroring today : what happened in the log on the primary happens on the secondary. Not everything that happens in Vegas has to stay in Vegas. :-)

Source: Do schema changes "break" SQL Server 2012 AlwaysOn or are they handled transparently?


HADR_THROTTLE_LOG_RATE_SEND_RECV_QUEUE_SIZE wait type While uploading (bulk insert via BCP) a large amounts of data to Azure SQL Database I observe a long HADR_THROTTLE_LOG_RATE_SEND_RECV_QUEUE_SIZE waits.

select session_id,
    start_time,
    s1.text as sql_text,
    wait_type,
    last_wait_type,
    open_transaction_count,
    total_elapsed_time,
    reads,
    writes,
    logical_reads,
    row_count,
    database_id
from sys.dm_exec_requests r
    cross apply sys.dm_exec_sql_text(r.sql_handle) s1

Does anybody have an idea what it can be related to? I can't find any information about this wait type on the Internet.

Thanks a lot in advance.

Since you are uploading to Azure SQL Database, this is likely to do with how fast they will let you push log records to the replicas you have. You can't configure those or decrease any throttling they're going to do; depending on service tier you're likely just going to have to resign yourself to a certain threshold on speed for bulk data transfer, whether you take this speed hit from their throttling, or changes you make yourself by injecting batching / delays to indirectly reduce log writes per time frame.

In other words, the wait isn't documented, and there's nothing you're going to be able to do about it anyway, except maybe move up to a better service tier. But even that is no guarantee, since I am sure there is throttling across the board, and it might even always be the same regardless of tier.


Delete duplicates from a table with all the columns duplicated I needed to remove duplicates from a table where every column was duplicated and there was no Prime Key.  The ID column was also an Identity column.

[It perplexes me why the Identity column wasn't made a PK when the table was created.  I'm going to look at doing that and test to ensure I don't break anything.]

The table isn't huge--just a few hundred rows.  In terms of performance, it doesn't matter if this is the most efficient method.  However, if there is a better way, I'm interested in hearing.

USE DBMGMT
GO

If(OBJECT_ID('tempdb..#Temp1') Is Not Null) Drop Table #Temp1

SELECT DISTINCT
    [ID]
    ,[Load_Name]
    ,[Remote_File_Name]
    ,[Local_File_Name]
    ,[Status]
    ,[Return_Code]
    ,[Group_Name]
    ,[Priority_Level]
    ,[Enabled]
    ,[Table_Name]
INTO #Temp1
FROM [dbo].[monthly_prd04_ftp_list]

SET IDENTITY_INSERT [dbo].[monthly_prd04_ftp_list] ON
GO

DELETE FROM [dbo].[monthly_prd04_ftp_list]
GO

INSERT INTO dbo.monthly_prd04_ftp_list
    (
    [ID]
    , [Load_Name]
    , [Remote_File_Name]
    , [Local_File_Name]
    , [Status]
    , [Return_Code]
    , [Group_Name]
    , [Priority_Level]
    , [Enabled]
    , [Table_Name]
    )
SELECT
    [ID]
    , [Load_Name]
    , [Remote_File_Name]
    , [Local_File_Name]
    , [Status]
    , [Return_Code]
    , [Group_Name]
    , [Priority_Level]
    , [Enabled]
    , [Table_Name]
FROM #Temp1

GO

SET IDENTITY_INSERT [dbo].[monthly_prd04_ftp_list] OFF
GO

If you can, add an UNIQUE INDEX (or PRIMARY KEY) for [ID].

And you could change:

DELETE FROM [dbo].[monthly_prd04_ftp_list]
GO

by

TRUNCATE TABLE [dbo].[monthly_prd04_ftp_list]
GO

It's faster and it will reseed the identity column to 1 (or it initial value.)

If you don't want to use a temporary table, and there isn't FOREIGN KEYS pointing to this table, you could rename it and insert the DISTINCT values to the final table directly. Then you could DROP the old table.


Find changed columns for near-identical rows across tables I run PostgreSQL 9.3.5 on Windows 7, 64-bit.
My data arrives quarterly, in multiple tables (table1, ..., tableN) that are linked, intra-period, by cross-table constraints based on key identifiers. Among other columns, each table has identifiers that persist over time: pfi - persistent feature identifier and ufi - universal feature identifier.
pfi is unique per table (it's exceedingly rare that table1.pfi = table2.pfi.
ufi is unique across all tables and across all time. It's not a hash of the row data, but you could think of it as such.
Each period, in each table, some new pfiare brought into being and some old pfi are retired. Some pfi change attributes. ufi tracks any change to any attribute for a given pfi(row), so to fetch changed (and new) rows for table1 it's simply a matter of:
-- 1st query
select a.*
into vm201512.property_d
from vm201512.property a
where not exists (select 1 from vm201412.property where ufi = a.ufi);

This selects all rows which are either new (new pfi) or changed in at least one column.
About 96% of each table remains unchanged in every respect. Accordingly, in analysing the cross-period changes I build a table that only includes changed and new data. This reduces the table size from ~3.5m rows to ~225k rows: that's a BIG reduction if you subsequently do spatial comparisons with relatively-complex polygons and multiple (spatial and non-spatial) JOINs.
The property table has relatively few columns, so I can identify which elements of the data have changes as follows:
-- 2nd query
create table vm201512.property_d_changes as
select pfi,
   case when a.view_pfi=b.view_pfi then 0::int else 1::INT end as view_pfi,
   case when a.status=b.status then 0::int else 1::INT end as status,
   case when a.property_type=b.property_type then 0::int else 1::INT end as property_type,
   -- ... more columns
from vm201512.property_d a -- table created with first query
join vm201412.property b using (pfi);

This gives me a nice table where I can determine precisely what changes happened to a changed (not new) row. I can figure out that pfi 123456 had changes to its propnum and its status; I can figure out how many pfi had changes to their view_pfi - that sort of thing.
Several of the other tables have >50 columns, which makes the case statement unwieldy (I realise it only has to be coded once, but what if the data structure changes?)
Question
With two rows in 2 different tables new.table1, old.table1 where new.table1.pfi = old.table1.pfi and one or more columns different, is there a parsimonious, elegant PostgreSQL statement to figure out the changed columns? Or am I stuck with CASE?
I realise I could write a dynamic function to loop through all columns for a given table, and build the query with CASE statements.

Clarifications
Your comment needs addressing first:

numeric data almost always takes 0 (and text types take '')

The key word here is "almost". As long as it's not "never" (like in "never ever!"), you need to take NULL into account anyway.

no risk of testing NULL=NULL, which would return 1 inappropriately

No it wouldn't. Anything compared to NULL is always NULL even NULL=NULL. Try it. You need to understand NULL comparison.

I think I just need to change sum(col1) to sum(col1::int) to get the number of rows where col1 changed.

If you want to count every case of a.col1 IS DISTINCT FROM b.col1, then you need to work with NULL-safe comparison to begin with. Apart from that, your expression would work. There are many alternatives, depending on the situation:

For absolute performance, is SUM faster or COUNT?

You use select a.* into vm201512 ... in your 1st query. Don't. SELECT INTO .. is discouraged. Use the superior CREATE TABLE AS ... like in your 2nd query.

Creating temporary tables in SQL

Also, Postgres provides pivot functionality in the tablefunc module, but this is not a "pivot" problem at all. Nothing is pivoted here.
The core problem is the dynamic nature of the query due to varying input tables.
Solution
Assuming no NULL values. Where NULL values are possible, use IS NOT DISTINCT FROM instead of =.
Tested in Postgres 9.5. Should work for Postgres 9.1 or later.
You can build your queries like this:
CREATE OR REPLACE FUNCTION f_build_query(_t1 regclass
                                       , _t2 regclass
                                       , _join_col text = 'pfi')
  RETURNS text AS
$func$
SELECT format('SELECT %I, %s FROM %s a JOIN %s b USING (%1$I);'
            , _join_col
            , string_agg(format ('a.%1$I = b.%1$I AS %1$I', attname), ', ' ORDER BY attnum)
            , _t1, _t2)
FROM   pg_attribute
WHERE  attrelid = _t1        -- compare all columns from 1st table
AND    NOT attisdropped      -- no dropped (dead) columns
AND    attnum > 0            -- no system columns
AND    attname <> _join_col  -- exclude "pfi"
$func$  LANGUAGE sql;

Call:
SELECT f_build_query('vm201512.property_d', 'vm201412.property');

Returns a query like this (which you can execute in turn):
SELECT pfi, a.a = b.a AS a, a."weird NaMe" = b."weird NaMe" AS "weird NaMe" -- more ...
FROM vm201512.property_d a JOIN vm201412.property b USING (pfi);

Result:
 pfi | a | b | weird NaMe
-----+---+---+------------
   1 | t | f | t
   2 | f | t | f

Works for arbitrary input tables, and deals with identifiers safely. You can provide table names schema-qualified or not, as you like.

Table name as a PostgreSQL function parameter

Simple dynamic solution
The difficulty is to return varying row types. SQL demands to know the return type at call time. To avoid difficulties, you could return a simple array instead. You get values in the original order of columns, but you don't get column names like in the first query:
CREATE OR REPLACE FUNCTION f_diff_matrix(_t1 regclass
                                       , _t2 regclass
                                       , _join_col text = 'pfi')
  RETURNS TABLE (pfi int, change_matrix bool[]) AS   -- Adapt data type of pfi to your needs!
$func$
BEGIN
   RETURN QUERY EXECUTE (
   SELECT format('SELECT %I, ARRAY[%s] FROM %s a JOIN %s b USING (%1$I)'
               , _join_col
               , string_agg(format ('a.%1$I = b.%1$I', attname), ', ' ORDER BY attnum)
               , _t1, _t2)
   FROM   pg_attribute
   WHERE  attrelid = _t1        -- compare all columns from 1st table
   AND    NOT attisdropped      -- no dropped (dead) columns
   AND    attnum > 0            -- no system columns
   AND    attname <> _join_col  -- exclude "pfi"
   );
END
$func$  LANGUAGE plpgsql;

Call (note the difference!):
SELECT * FROM f_diff_matrix('vm201512.property_d', 'vm201412.property');

Result:
 pfi | change_matrix
-----+---------------
   1 | {t,f,t}  -- one element per column
   2 | {f,t,f}

SQL Fiddle.

You could even make the same function return a dynamic result set for various tables, but I doubt it's worth the complication:

Refactor a PL/pgSQL function to return the output of various SELECT queries

If your really need dynamic pivot functionality (not in this case):

Dynamic alternative to pivot with CASE and GROUP BY


do user_scans and no user_seeks mean the index is useless? I'm digging into sys.dm_db_index_usage_stats and finding that a number of our indexes are used exclusively in user_scans, not user_seeks.  Does this imply that the index is useless and should be redesigned?

Thanks!

Not useless, but maybe sub-optimal.  Scanning a 10 MB index is a hell of a lot faster than scanning a 1 GB table, though a seek would be even better.  Can you tell us more about the indices?  How are the tables being used?


Cleaning up archives in Postgres after using systemd I'm using Postgres 9.3 and I have a master database and a hot standby one that is following the master.
The operating system is CentOS 7.

I've changed the way of starting Posgres from SysV to systemd.
I've used the standard /usr/lib/systemd/system/postgresql-9.3.service, and it's much more simplified than the old SysV script.

After the change, I've observed that it continues to work ok.  The only downside is that the archives in the hot standby are never deleted.  It is like the archive_cleanup_command in recovery.conf were ignored.

In the .service, Postgres is started in this way:

pg_ctl start -D ${PGDATA} -s -w -t 300

whereas in SysV in this other way:

postmaster -p $PGPORT -D $PGDATA

I think both of them are quite equivalent.

If I change back to SysV, then archives are not accumulated any more.

Why my archives aren't cleaned up with systemd?

The problem is that the PATH environment is different.

I had this line in recovery.conf:

archive_cleanup_command = 'pg_archivecleanup /var/lib/pgsql/9.3/standby/archive %r'

And Postgres doesn't find pg_archivecleanup and silently ignores it.

Solution is to change the line to this:

archive_cleanup_command = '/usr/pgsql-9.3/bin/pg_archivecleanup /var/lib/pgsql/9.3/standby/archive %r'


select items with one or more specified tags I have two tables.
One table holds tags:

CREATE TABLE `tags` (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(30) NOT NULL
) COMMENT='';
INSERT INTO `tags` (`id`, `name`) VALUES
(1, 'tag one'),
(2, 'tag two'),
(3, 'tag three'),
(4, 'tag four'),
(5, 'tag five');

Other table holds items, where each item has zero or more tags (their IDs) separated by comma:

CREATE TABLE `items` (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(30) NOT NULL,
  `tags` text NOT NULL
) COMMENT='';
INSERT INTO `items` (`id`, `name`, `tags`) VALUES
(1, 'first item',   '2,4'),
(2, 'second item',  ''),
(3, 'third item',   '1'),
(4, 'fourth item',  '1,3,4');

How do I select only those items which have at least one of the wanted, say (2,3) tags?

Just use FIND_IN_SET function

SELECT * FROM items
JOIN tags
ON FIND_IN_SET(tags.id,items.tags)
WHERE tags.id IN(2,3)

Fiddle


Need some help with this query or some ideas for change it to better model I've a dilemma in my application and I don't get how to solve it at DB level. The idea is that a user can be subscribed to one or more services but services are show depending on user type. This can sound tricky but it does not. So I have a form where I pick which type of user I'm registering but then from services table I need to show only the services allowed for that type of user. I made this table:

CREATE TABLE "nomencla"."service" (
    "id" int4 NOT NULL,
    "name" varchar(80) COLLATE "default" NOT NULL,
    "active" bool,
    "cedulabenefsigesp" varchar(10) COLLATE "default" NOT NULL,
    "user_type" text COLLATE "default",
    CONSTRAINT "tipo_servicio_pkey" PRIMARY KEY ("id")
) WITH (OIDS=FALSE);

ALTER TABLE "nomenclator"."service" OWNER TO "postgres";
COMMENT ON COLUMN "nomenclator"."service"."user_type" IS '(DC2Type:array)';

In user_type column, I stored, as serialized values, the allowed user type for each service, ex:

1   Service1    t   0000000039  a:1:{i:0;s:1:"1";}
2   Service2    t   0000000040  a:3:{i:0;s:1:"2";i:2;s:1:"3";i:3;s:1:"4";}

For row 1 it means: Service1 will be available for users of type 1 and Service2 will be available for users of type 2,3 and 4 only so in register form for users of type 1 I should only show the Service1 option and for users of type 2,3 or 4 then Service2 will be showed, I need some help building a query for get that data or help to change my model with better solution to that problem, any?

It's not a good idea to encode access rules in text fields.  Even more generally, it violates normal form to encode anything in any field.

Instead, create a new table:

ServiceAllowedUserTypes(
    service_id int foreign key references service,
    user_type int);

If there's a row for the service's id and the user's type, then the user is allowed to order that service.


What is a real-world use case for a marked transaction? I have read BOL about marked transactions and I can see contrived examples, but what is a real world use case for MARK'ed TRANSACTIONS?

After reading this I was thinking I would implement marked transactions henceforth in our large batch jobs that run over night. We already use TRANSACTIONS and log backups, but I was hoping to get a use case where this saved time for someone as opposed to just adding it because I just learned about it.

Marked transactions make it easy to do point-in-time restores without worrying about actual time. Instead, one restores to logical event.

As a practical example, consider an update that needs to be undone. Now, the update was such that a database restore is needed as wrong value was set to all the columns and original data is lost. Maybe a business analyst set VAT to 24% for all the products instead of a subset. We don't know what the previous VAT values were, so the update cannot be undone.

Restoring the database is simple, but how much of the transaction logs would be applied? At which point-in-time would one stop the log restores? Does the business analyst keep a detailed journal that records the beginning of the update? Maybe all that is remembered is that "it was done Monday afternoon". If there are lots of changes in the database, recovery to right spot is trial and error.

Should the update be done within marked transaction, the DBA will simply query msdb.dbo.LogMarkHistory table and pick symbolic name for the transaction. Log restore is then done with STOPBEFOREMARK = 'VAT update' parameter to stop at the precise point before marked transaction was started.


How to resolve MDF and LDF files remaining in use? I am attempting to copy off approximately 220 gigs of database MDF and LDF files from a MS SQL server 2000 install in order to transfer ownership to a third party.

The error I get when I try to stop the database using the enterprise manager is

  Error 1051: A Stop Control Has Been Sent To A Service Which Other Running Services Are Dependent On

Going to the service and looking at the dependencies tab does not show any other services running in relation to it. If I stop the actual Windows service, and try to copy it tells me that the databases are still in use. I have changed the service from automatic to manual and rebooted, and it still insists the files are in use.

I don't particularly care about copying the log files, but how can I resolve the error to do a clean shutdown and be able to copy off at least the MDF files?

The Restart-Service cmdlet sends a stop message and then a start message to the Windows Service Controller for a specified service. If you specify -force parameter, it means override restrictions that prevent the command from succeeding,  e.g. stop and restart a service that has dependent services.

However, Windows Server manager launched via services.msc you mentioned is not running with force parameter when you click restart button there.

Thus if you want to do that, either stop the dependent services first, then retry or use the PowerShell command line to restart.


Databases for training (MariaDB) I'm practicing for enrich my knowledge, and i would like to have more databases than actually i have (2).

Where i can found free databases for practice? If it's possible, big databases.

I'm traing to looking for some kind of comunity where people upload their databases for practice, If exist.

There is a wikipedia database you can download from the following link:

Wikipedia:Database download

  Wikipedia offers free copies of all available content to interested users. These databases can be used for mirroring, personal use, informal backups, offline use or database queries (such as for Wikipedia:Maintenance). All text content is multi-licensed under the Creative Commons Attribution-ShareAlike 3.0 License (CC-BY-SA) and the GNU Free Documentation License (GFDL). Images and other files are available under different terms, as detailed on their description pages. For our advice about complying with these licenses, see Wikipedia:Copyrights.


How to represent the optionality of a many-to-one mandatory relationship from the Parent to its two Child entity types in an ERD? I have a Parent entity type with two Child entities, with a relationship cardinality of many-to-one (or M:1) from Parent to Child.

The business rules say that:

It is mandatory that the Parent entity type be connected to either of Child A or Child B in any given process, but also that…
The Parent cannot be connected to both nor can it be connected to neither during that process.

I am stumped on how to represent the optionality here. It is a mandatory relationship on the Parent side, but not mandatory in the same way on the Child side. It is necessary that at least one Child be present in the relationship at a given time, but I do not feel it is correct to call this mandatory from the Child side, nor do I feel it is correct to represent both Child entities in optional relationships with the Parent.

So:

Is each Child entity in a mandatory or optional relationship with Parent?

This is a theoretical question in my head more-so, but I've ran with the "Who is the parent's favourite child?" example in my diagram as it matches: a child can have many parents favouring them, the parent can have one of either child as their current favourite, and the parent can't have no preference toward one child at a given moment. Cruel, but it works for my example!

I haven't got as far as DB design, though I was assuming that you can set up a column in the Parent table that can accept a Foreign Key from either of Child_A or Child_B but has a Not Null constraint. Or is my assumption off, and as such would this then mean the ERD above can't be implemented like so in the database?

It looks to me as though child_1 and child_2 are two instances of a single entity type.

If so, then you have a (second) relationship such that

Every parent must have one and only one favourite child
Every child may be the favourite of zero or more parents.

The (first) relationship being

A parent may have zero or more children (unless, of course you have defined "parent" such that a child is a pre-requisite of being a parent ;-))
A child must have exactly two parents (under basic human biology ;-))

Your first relationship may, of course, be different depending on the context.


Why more (and varying number of) logical reads with read ahead (prefetch)? After creating tpch database in my SQL Server, I tried below query:

    set statistics io on
    DBCC DROPCLEANBUFFERS;
    select top 100 * from dbo.lineitem order by l_partkey;

The table lineitem has a non-clustered index on l_partkey. I issued the above queries for several times and found out that the logical reads vary each time:

    Table 'lineitem'. Scan count 1, logical reads 1019, physical reads 4, read-ahead reads 1760, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'lineitem'. Scan count 1, logical reads 1007, physical reads 4, read-ahead reads 1720, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'lineitem'. Scan count 1, logical reads 1030, physical reads 4, read-ahead reads 1792, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

From the post here: Logical read count varies, I know it could be caused by read ahead behavior.

BUT exactly why read ahead could cause more logical reads? How does it change the SQL Server behavior? Like SQL Server may read more index page since it's in cache anyway?

Anyway, I disabled read ahead and issue the above query again. Now it reports the same amount of logical reads each time. BUT the logical reads is much smaller!!

    Table 'lineitem'. Scan count 1, logical reads 404, physical reads 160, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So my question is, why the read ahead feature could cause many more and various logical reads count?

Out of curiosity, I tried another query without the "order by":

    select top 100 * from dbo.lineitem

Here's the result without read ahead:

    Table 'lineitem'. Scan count 1, logical reads 5, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Here's the result with read ahead:

    Table 'lineitem'. Scan count 1, logical reads 15, physical reads 2, read-ahead reads 3416, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The one with read ahead still has more logical reads. So, why?

The query plan for the ORDER BY l_partkey example almost certainly reads the nonclustered index in order (with read-ahead), followed by a Key Lookup to retrieve the uncovered columns.

The Nested Loops Join operator above the Lookup probably uses additional prefetching (WithOrderedPrefetch) for the Lookup. See the following article I wrote for full details:

Nested Loops Prefetching

Also, as I mentioned in answer to the linked Q & A, the number of read-ahead reads depends on timing and storage subsystem characteristics. The same sort of considerations apply to Lookup prefetching at the Nested Loops Join.

Note that SQL Server issues read-ahead for pages that might be needed by the index scan, but this is not limited by the TOP specification in the query. The TOP is a query processor element, whereas the read ahead is controlled by the Storage Engine.

The activities are quite separate: read-ahead (and prefetching) issue asynchronous I/O for pages that may be needed by the Scan (or Lookup).

Depending on the order in which I/Os actually complete and make rows available to the query processor (among other things), the number of pages actually touched (logical reads) or physically read may vary. Note in particular that Lookup delayed prefetch also contributes to the logical reads when it checks to see if a page needed for the Lookup is already in memory or not.

So, it all comes down to the detailed timing of overlapping operations: The query processor will start to shut the query execution pipeline down as soon as the required number of rows (100) has been seen at the Top iterator. Quite how many asynchronous I/Os (read-ahead or prefetch) will have been issued or completed at that point is essentially non-deterministic.

You can disable Nested Loops Join prefetching with trace flag 8744 to explore this further. This will remove the WithOrderedPrefetch property from the Nested Loops Join. I usually use OPTION (QUERYTRACEON 8744) on the query itself. In any case, you need to be sure you're not reusing a cached plan that has the prefetch. Clear the plan cache each time or force a query recompilation with OPTION (RECOMPILE).

Logical reads is a simple measure of the number of cache pages touched on behalf of the query. Where read-ahead (and/or prefetching) is enabled, more (and different!) index and data pages may be touched in order to issue that read-ahead or as part of the prefetching activity.


show extended index equivalent for MySQL 5.7 If you look at this documentation for show index in version 8.0, it says

  SHOW [EXTENDED] {INDEX | INDEXES | KEYS}
   {FROM | IN} tbl_name
   [{FROM | IN} db_name]
   [WHERE expr]

  The optional EXTENDED keyword causes the output to include information about hidden indexes that MySQL uses internally and are not accessible by users.

Without the optional EXTENDED keyword, only the normal index parts are visible, the primary key at the end is not visible (innodb). I want to see the extended version in 5.7. How do I see it?

I tried checking in information_schema tables like TABLE_CONSTRAINTS, KEY_COLUMN_USAGE etc. The extended info is also not there.

But it’s safe to assume PK is indeed present ?

Yes.  (The following applies only to InnoDB; other engines probably differ.)

A copy of the PK column(s) is tacked on the end of regular indexes.
FULLTEXT and SPATIAL work differently, so this discussion may not apply.
There has been some chatter about whether the FK columns are redundantly added, or only if not present in the declared index.
The benefit of the PK columns being tacked onto an index is for "covering".  This provides a (perhaps) 2x speedup.  Example:  Given PRIMARY KEY(id), if you also have INDEX(x), then SELECT id FROM t WHERE x=1 (without any other columns mentioned), then the query can be performed in the BTree for the index since it is effectively (x,id).
UNIQUE and non-UNIQUE work the same except for the uniqueness constraint.  This constraint applies only to the columns specified.  That is, given PRIMARY KEY(id), if you also have UNIQUE(uuid), then uuid is checked for uniqueness, even though the BTree containing that index has (uuid, id).
The PRIMARY KEY is a UNIQUE index, but it is different because it is clustered with the data.  That is it is in the same BTree and that BTree is ordered according to the PK.
Hence a "point query" using the PK, is a simple drill-down of the PK+data's BTree.
But a "point query" using a secondary index needs to first drill down the index's BTree to find the PK, then drill down the PK to find the row.  (Exception:  "Covering index", see above, avoids this second drill-down.)


Make two different updates with two different where conditions Although this question is very similar, I don't think that it is also the best approach for my case, though it seemed to work. Consider the following example:
CREATE TABLE messages (
  writer VARCHAR(5),
  recipient VARCHAR(5),
  show_for_writer TINYINT(1), -- 0 for hidden, 1 for display
  show_for_recipient TINYINT(1) -- idem
);

I now want to create ONE update query which hides all messages of user x for him; i.e. which sets show_for_writer equal to 0 where the writer is x, AND which sets show_for_recipient equal to 0 where the recipient is x. Note that I want to make this as efficiently as possible, as this table may contain millions of records (one per message) for countless writer-recipient combinations (composite, non-unique index).
What I've tried to make, according to the linked post, is:
UPDATE messages
SET show_for_writer =
    CASE
    WHEN writer = 'x'
    THEN 0
    ELSE show_for_writer -- or even better, do nothing; only update if necessary
    END
    ,
    show_for_recipient =
    CASE WHEN recipient = 'x'
    THEN 0
    ELSE display_receiver -- or even better, do nothing; only update if necessary
    END
WHERE writer = 'x' OR recipient = 'x';

I however feel that this is not ideal AT ALL, because it goes through all the records of the table, and this query may not be done at a regular scale, but not rarely either. What's the best solution for this? I'm open for any solution, even structural column modification propositions; I'm using MariaDB 10.3.
An Idea I had was maybe to edit the structure as follows:
CREATE TABLE messages (
 participants VARCHAR(10) FULLTEXT,
 writer VARCHAR(5) NOT NULL,
 recipient VARCHAR(5) NOT NULL,
 show JSON NOT NULL
);

And do something like:
UPDATE messages
SET show = JSON_REPLACE(messages,'$.x',0)
WHERE MATCH(participants) AGAINST('x');

The problem here is that I query messages if they shall be shown for 'x' upon init. Hence, setting show to a JSON data type won't make it possible to index this column, hence to query in f(show) efficiently. That's why I thought this idea may not be ideal, but just to show you that I'm basically open even to modifications at the structural level.
Otherwise I could simply fire two subsequent UPDATE statements, like so:
UPDATE messages
SET show_for_writer = 0
WHERE writer = 'x';

UPDATE messages
SET show_for_recipient = 0
WHERE recipient = 'x';

But I really prefer to stick to using one query per action. Help please!
Check the example here, with the update query to be otpimized to avoid going over all records of the table:
http://www.sqlfiddle.com/#!9/f6830d/1

Do two UPDATEs.
And have a PRIMARY KEY
And two secondary indexes:
INDEX(recipient),
INDEX(writer)

If you are concerned about ACIT integrity, make a transaction:
BEGIN;
UPDATE ... WHERE writer...
UPDATE ... WHERE recipient...
COMMIT;


How to write "and" queries for a lookup table when you can't use the AND keyword? I'm building a practice website that allows users to search hotels based off of the amenities that the hotel provides. From the FE, the user will select a checkbox for however many amenities they want, and the amenity keys will be sent to the backend. On the backend, I've got these three tables:
          hotels
| id | name         | vacancies
| ---| ------------ | -------- |
|   1| Marriott     |        0 |
|   2| Best Western |       10 |
|   3| Sheraton     |        3 |
 ------------------------------

          amenities
| id | name                  | key                   |
| ---| --------------------- | --------------------- |
|   1| Cafe                  | cafe                  |
|   2| Wheelchair Accessible | wheelchair_accessible |
|   3| Wifi                  | wifi                  |
 ----------------------------------------------------

    hotels_amenities_lookup
| id | amenity_id | hotel_id |
| ---| ---------- | -------- |
|   1|          1 |        3 |
|   2|          2 |        1 |
|   3|          2 |        2 |
|   4|          2 |        3 |
|   5|          3 |        2 |
|   6|          3 |        1 |
 ----------------------------

To search for one amenity, such as wheelchair_accessible, I would do something like this:
WITH hotels_with_amenity as (
    SELECT ha.hotel_id
      FROM hotels_amenities_lookup ha
      JOIN (
            SELECT id from amenities a
            WHERE  a.key = 'wheelchair_accessible'
        ) amenity ON ha.amenity_id = amenity.id
)

SELECT  h.name,
        h.vacancies
   FROM hotels h, hotels_with_amenity hwa
  WHERE h.id = hwa.hotel_id;

Returns all three hotels.
The question is: if the user selects multiple amenities, wheelchair_accessible and wifi for example, how would I query for hotels that have both? With this current set up, I couldn't do
WHERE a.key = 'wheelchair_accessible AND a.key = 'wifi'
Is there a better way of setting up these tables to make this query easier?
I'm new to relational databases and it's likely I'm missing something obvious here.

A classical case of relational-division!
One of the most efficient queries techniques for what you ask:
SELECT h.name,
       h.vacancies
FROM   hotels_amenities_lookup ha1
JOIN   hotels_amenities_lookup ha2 USING (hotel_id)
-- more?
JOIN   hotels h ON h.id = ha1.hotel_id
WHERE  ha1.amenity_id = (SELECT id FROM amenities WHERE key = 'wheelchair_accessible')
AND    ha2.amenity_id = (SELECT id FROM amenities WHERE key = 'wifi')
-- more?

db<>fiddle here (reusing McNets fiddle, thanks!)
These indices help:

amenity(key, id)  -- ①
hotel_amenity(amenity_id, hotel_id)
hotel_amenity(hotel_id, amenity_id) -- ②
hotel(id) -- PK

① Maybe in the form of UNIQUE (key) INCLUDE (id). Related:

Do covering indexes in PostgreSQL help JOIN columns?

② Yes, another multi-column index with reversed columns. See:

Is a composite index also good for queries on the first field?

But there is a plethora of other ways:

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

Aside 1: The above is using AND, but it's not really about avoiding the keyword, is it?
Aside 2: Just hotels_amenities instead of hotels_amenities_lookup?
Database design
But, as Laurenz commented, the first question is about the right database design. And that heavily depends on exact (undisclosed) specifications and requirements.
Your fully normalized many-to-many implementation is a "catch-all" solution. You can combine as many hotels and as many amenities as you like, rename, add, drop hotels and amenities easily, add any amount of additional information in each table, and indexing with plain btree indexes is simple. Robust against heavy concurrent write load with many updates in any or all tables. But the disk footprint is high. And optimizing your query with many amenities needs some work.
For combining just a hand full of amenities the presented query is fast and optimized by the query planner automatically (using MCV lists in the column statistics). For more than a few amenities (more than join_collapse_limit) you'll have to switch to a smarter solution like outlined in the related answer you found:

Using same column multiple times in WHERE clause

The linchpin of the solution is to filter on rare amenities first.
About join_collapse_limit:

Postgres JOIN conditions vs WHERE conditions
Are implicit joins as efficient as explicit joins in Postgres?

OTOH, for a small, stable set of amenities a simple design with boolean flags like Laurenz suggested has a much smaller disk footprint. Maybe add one partial index per flag.
Or an array of amenities for more than a few options (optimized with "char"[]?) with a GIN index. May even be combined with your current design in the form of a MATERIALIZED VIEW.
Each with a matching query style.
If storage space is important (it always is in some way):

Should I use the PostgreSQL bit string?

As always: it depends.


Alternatives to MySQL Partitioning, so we can have a Unique Index I have a DBA vs Developer Issue here, and am wondering if there are any alternatives to partitioning that I can employ here.

I have a table as follows:

CREATE TABLE `tracking` (
  `Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `CustId` int(10) unsigned DEFAULT NULL,
  `SourceName` varchar(32) DEFAULT NULL,
  `SourceId` int(10) unsigned DEFAULT NULL,
  `Created` datetime NOT NULL,
  PRIMARY KEY (`Id`,`Created`),
  KEY `Cust_Source`(`CustId`,`SourceName`,`SourceId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( TO_DAYS(Created))
(PARTITION `201903` VALUES LESS THAN (737515) ENGINE = InnoDB,
 PARTITION `201904` VALUES LESS THAN (737545) ENGINE = InnoDB,
 PARTITION `201905` VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;

As you can see, I like to partition my tables, as we only ever keep 2 months data in the live system, and this way I can automatically DROP the oldest partition each month.

However, my developer has asked if I can change the Cust_Source Index to be a UNIQUE Index, to prevent duplicate data being added by mistake. But so doing means I need to lose the partitioning. As:

"Error Code: 1503. A UNIQUE INDEX must include all columns in the table's partitioning function"

And of course, if I include the created field in the Index, we could get Duplicate data, created at different times.

Any suggestions?

This needs to work on MySQL 5.5 and 5.7 (although we'll be retiring 5.5 soon, hopefully).

Since you won't have even a million rows in the table, don't bother to PARTITION it.  Then you won't have any trouble with the UNIQUEness constraint.
Change the indexing:

Plan A:

PRIMARY KEY(Id),   -- notice: `Created` was removed
INDEX(Created),    -- for purging
UNIQUE(`CustId`,`SourceName`,`SourceId`)  -- as desired

Plan B (this assumes you have no real use for id):

PRIMARY KEY(`CustId`,`SourceName`,`SourceId`)  -- Unique
INDEX(Created)    -- for purging

Purge daily, not monthly.  This is because DELETE is much slower than DROP PARTITION.  If you still run into performance troubles:  Using Plan B, write a loop to delete the rows for each CustId one at a time.

(I prefer Plan B for your situation.)

(My comments apply to all versions.)

Usually, each partition has 4M-7M of "free" space.  Hence a partitioned table is inherently bigger than the non-partitioned equivalent.


Restoring a MySQL database to a failed master I have a master-master configuration in MySQL with two servers. One server should stay live on the network to serve requests (call it server A) and the other should be taken offline to push new code changes (server B).

My idea originally was that after running STOP SLAVE on both servers, that server B could be shut down, updated, and even have a new database schema put in. After this, I thought that I could simply START SLAVE on server B and have the entire database from server A replicated/mirrored back over to server B. However, this is not the case. Restarting the slave and doing a CHANGE MASTER TO (...) and syncing up the log files does not replicate old changes like I want it to: it only replicates new writes from that point on.

I am looking for a way to bring server B up to speed with the latest database from server A, and then have server B continue to replicate changes in a master-master setup. Then I can continue the sequence of server upgrades by doing the same process but keeping server B online only.

Any solutions which require locking the tables will not work since I need to do this change without any downtime. Is this possible?

Slow ALTER in Master-Master

In Master-Master, each server is acting both as a Master and a Slave to the other server.

STOP SLAVE -- When you later do START SLAVE, the stuff that was performed on the Master will be applied to the Slave.  If you did, say, a big ALTER on a big table, that ALTER will run, taking a long time.  This is probably the "downtime" you are worried about?

The alternative is to do the lengthy process twice.

Stop client usage of B.
On B, turn off writing to the binlog
On B, do the ALTER(s), etc.
On B, turn on writing to the binlog
Wait for replication to catch up.  (It was probably blocked by the ALTER.)
Flip -- that is stop talking to A and start talking to B.
On A, (steps 2,3,4,5) binlog off, ALTER, binlog on, catch up

Alternatives

In Galera, this is "RSU" (as opposed to "TOI").

See also pt-online-schema-change -- this tool (percona.com) allows for doing time consuming maintenance on a table with essentially no downtime.

5.7 can now do many ALTERs with ALGORITHM=INPLACE, hence little or no downtime.

Putting the burden on the Code

Keep in mind that some code changes involve schema changes.  Sometimes they can be done in a "backward compatible" manner, thereby obviating stuff like this.

In the extreme, you might need to double up the code.  That is, have it capable of handing the old schema and the new schema at the same time.  This complicates the code, but eliminates coordination problems between code and schema.  (The code should eventually be cleaned up to throw out the 'old' variant.)

That last way becomes especially important when you have Sharding, multiple clients, or other cases of "lots of servers to update".


How do I normalize a database with lots of many-to-many relationships? I am building a database that is based around a business project, in this case called a "Program." Each Program will have multiple Reports, Clients, and Partners.

My first thought was to place each Program, Report, Client, and Partner into their own table and then use a linking table for the Report, Client, and Partner tables to the Program table's primary key.

Can I link all of them with a single table by just making repeat entries? I am not sure if this even makes sense, I am kind of lost as to how one descirbes an ERD in a post like this.

ProgramTable

ProgramID

ReportTable

ReportID

ClientTable

ClientID

PartnerTable

PartnerID

LinkTable

ProgramID (FK)
ReportID
ClientID
PartnerID

My concern is that because a Program may have multiple Reports, Clients and Partners, that I cannot do this in one link table and istead need 3 link tables, one for each. Any help would be great... sorry if I am not to clear on this.

It is very clear but you should probably first read some "introduction to relational databases" before you proceed, because from the way you are asking I feel it would be beneficial for you to get some basics first. No offence, it may save you lot of time later.

To give you few fast rules of a thumb

You never ever connect more then two tables with one link. Try to connect tables step by step. Always take a pair of them and try to join them. Do not try to solve the whole thing at once.
you only need the link table if there is so called many to many relationships, i.e. if a client can have many programs and at same time one program can have many clients. It seems to me that it might not be needed in your case.
In all other cases you just place "anothertableID" to one of the tables. In your example it would be good to start with putting ProgramID to ReportTable, ClientTable, PartnerTable. Maybe it is what you need.

To give an more elaborated answer you should give more information about structure of your problem.


insert results from store procedure with parameters into table I have a stored procedure that I want to insert into a table, the stored procedure however has parameters I have to pass, how can I do this?

DECLARE @RC int
DECLARE @startweek varchar(20)
DECLARE @endweek varchar(20)
DECLARE @payroll varchar(30)
DECLARE @job varchar(25)
DECLARE @job_to varchar(25)
DECLARE @manager varchar(30)
DECLARE @office varchar(100)
DECLARE @pu varchar(6)
DECLARE @pu_to varchar(6)
DECLARE @task varchar(25)
DECLARE @task_to varchar(25)
DECLARE @Prj_pu varchar(6)
DECLARE @Prj_pu_to varchar(6)

-- TODO: Set parameter values here.
SET @endweek = dateadd(d, -((datepart(weekday, getdate()) + 1 + @@DATEFIRST) % 7), getdate());
SET @startweek = DATEADD(WEEK, -25, @endweek)

EXECUTE @RC = [dbo].[TIME_lynx_extract]
   @startweek
  ,@endweek
  ,@payroll
  ,@job
  ,@job_to
  ,@manager
  ,@office
  ,@pu
  ,@pu_to
  ,@task
  ,@task_to
  ,@Prj_pu
  ,@Prj_pu_to
GO

I want to insert the result of this (the calling of the stored procedure) into a table, I have created the table with the columns, but if there were a way with temp table even better.

Here is an example of inserting the results of a store procedure call into a table variable (of course,you can use a real table). The stored procedure takes 2 parameters and returns their values as a result set.

--demo setup
drop procedure if exists [dbo].[sp1]
go
CREATE PROCEDURE [dbo].[sp1] @P1 INT
    ,@P2 INT
AS
BEGIN
    SELECT @p1 AS p1
        ,@p2 AS p2
END
go

--create table variable to hold results of SP call
Declare @T table (P1 int, P2 int)

--insert into the table variable the results of the SP call
Declare @P1 int = 1, @P2 int = 2
insert into @T
exec [dbo].[sp1] @P1, @P2

--select from the table variable to verify
select * from @T

P1  P2
1   2


Non-priviledge way to restrict UPD/DEL to user knowing certificate pass I am assigned to researching column-level encryption, with the additional requirement of the sa not being able to read the encryted column(s).

The idea as I've read in this DBSE post is to create a password-protected certificate, a bunch of symmetric keys under it, and the client should open these key(s) with the password only they are aware.

This seems to ensure that indeed noone other than the client can read the encrypted data. However, they can still modify them (delete them or update=corrupt them). As I understand it, using ENC/DECRYPTBYKEY is just a scalar function, independent of tables. That means that whether someone uses these functions on a specific column does not "inform" the database that these columns hold encrypted data. Thus, the only way to "protect" the data is the old school priviledge one.

Have I missed something? Is there a way to restrict a column to only accept updates or deletes if a specific key is open? Maybe even, in the case of update, require that the inserted. value is always the output of the EncryptByKey function?

Is there a way to restrict a column to only accept updates or deletes if a specific key is open?

No, a sysadmin or someone with sufficient permissions could change the data regardless of encryption.

Should the question or investigation change to be something where it doesn't matter if the data could change but that it was encrypted or otherwise unavailable to a sysadmin or someone with sufficient permissions, then yes that is possible but would require the keys to be stored outside the database system or for secret passwords to be known (less secure).


Incorrect result when you execute a query that uses WITH RECOMPILE option in SQL Server 2012 I just read the Brent Ozar Weekly Links email (July 28th Edition), and noticed the "Kendra's Link" talking about "Fix: Incorrect result when you execute a query that uses WITH RECOMPILE option in SQL Server 2012"

I replied to Brent Ozar's email, and Brent suggested that I post my question here instead of asking him to reply to me privately.

I had just finished patching all of our SQL Server 2012 servers in July to apply SQL Server 2012 SP2 plus the hotfix for the "data loss during online index rebuilds", so my servers are now at version 11.00.5522.0.

I did a little bit of research on this new hotfix.  I came to the conclusion that there is not a way to patch our systems with this new fix yet.

Here is the email I just sent to my manager. Do you guys agree with what I've pasted below:

Now that we’ve upgraded to SQL Server 2012 SP2 already, we don’t have a way to apply this new hotfix fix as of right now.

The fix was released in SQL Server 2012 SP1 CU11 (version 11.00.3449.0). As of this month, we are now at SQL Server 2012 SP2 plus the hotfix for the data loss during online index rebuild problem (version 11.00.5522.0).

You’d think that since we are at a version higher than the fix, we’d be covered. You know how they say that all the service packs include the previous service pack plus all the cumulative updates for the previous service packs, and all versions include everything from the versions below that version. Well, that’s not entirely true. Microsoft continues releasing CU’s for the previous service pack even after the new service pack is locked in QA. So they then have to play “catch-up” with the new service pack.

In reality, this is how it worked this time for the transition from SP1 to SP2:

• SQL Server 2012 SP2 (what I just patched all our SQL Server 2012 servers to) – includes SP1 plus CU1-CU9. It does not include SP1 CU10 or SP1 CU11.

• SQL Server 2012 SP2 CU1 was just released (last week I think) – that includes SP1 plus CU1-CU10. It does not include SP1 CU11 .

• That’s as far as MS has gotten with SP2. They haven’t released a CU for SP2 that includes the fixes from SP1 CU11 yet.

I assume there will be an SP2 CU2 coming out soon that will contain this fix, and we’ll probably want to talk about doing another round of SQL patching when that does get released.

I believe your question is, "Is the hotfix in https://support.microsoft.com/kb/2965069 included in Service Pack 2?" The answer as of July 28, 2014 is no. Watch that KB article to see when it changes - I use http://www.WatchThatPage.com for stuff like this.


Migrating a database from SQL Server 2012 to the cloud I have a sample SQL Server database which is running on my computer. I have a cloud database account on AWS (free account). I want to migrate the database, with all tables, to the cloud.

How can I do that? Are there any open-source or free tools?

Have a look at Importing Data Into SQLServer on Amazon RDS.

Also, from codeplex SQL Database Migration Wizard with a reference video as well.


sql backup restore converts createdAt to local TZ I have a remote MySQL DB in the cloud on JAWSDB.
I have createdAt and updatedAt timestamps for records that are in UTC TZ.
I'll backup these DBs using mysqldump -h HOST -u USER -p > backup.sql. I then restore these to a local MySQL Server using
mysql -u USERNAME -p DB_NAME < backup.sql
However, upon inspecting the new local backup...I notice that the createdAt timestamps are for my local TZ (EST and so currently 5hrs before the UTC version that are actually in JAWSDB). The updatedAt timestamps are still in UTC though.
Here's how I created those timestamp fields btw.
 `createdAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 `updatedAt` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

So it seems like the restore of the dump is converting from UTC to EST just for the createdAt field. Could this be due to an engine or mysql version mismatch between JawsDB and my local server?
Any insights? Thanks!

From MySQL documentation

MySQL converts TIMESTAMP values from the current time zone to UTC for
storage, and back from UTC to the current time zone for retrieval.
(This does not occur for other types such as DATETIME.) By default,
the current time zone for each connection is the server's time. The
time zone can be set on a per-connection basis. As long as the time
zone setting remains constant, you get back the same value you store.
If you store a TIMESTAMP value, and then change the time zone and
retrieve the value, the retrieved value is different from the value
you stored. This occurs because the same time zone was not used for
conversion in both directions. The current time zone is available as
the value of the time_zone system variable. For more information, see
Section 5.1.15, “MySQL Server Time Zone Support”.


Storing file into oracle db I'm developing a java web-app  for the acquisition of contracts, and that have to receive some file from an external system. For each new contract i recieve some information about the new customer and a audio file. Now I could save the file on db(I'm using Oracle 11g) or save just the link on db and then maintain the file on a server.

In your opinion What is the better solution?
What are prod and cons of the both the ways described above?

My strong advice (regardless of the RDBMS): store the files separately. This makes a much smaller database, important when you backup, migrate, replicate etc. Additionally, you can separate db and files on different disks, having so much more control over storage locations (e.g. disk subsystems with different fail-over strategies).

This means however, you need a way to serve the files to your clients, which requires extra work (but also extra opportunities, as you are not bound to the DB communication protocol).

A pro for storing in the db is obviously the simple way to return the data to the client.


How to creare a second login for a second database in Azure? How to deny a user access to one of the databases? I have an Azure database and I have created another one on the same server. After that, I have created a user and a corresponding login (see the script below) with the intention to use it for the second database only. After running the script I have googled (below), the second user can access both databases. How to deny the second user access to the first database? There are DENY and REVOKE, that deny specific rights, but it is not obvious to me which one should be used.

Bonus question: was it possible to create a user that can access the second database only?

IF NOT EXISTS (SELECT 1 FROM master.sys.databases WHERE ('[' + name + ']' = '[Elmah5]'))
    CREATE DATABASE Elmah5;
GO

IF NOT EXISTS (SELECT 1 FROM sys.sql_logins  WHERE name = 'ElmahAdmin')
    CREATE LOGIN ElmahAdmin WITH PASSWORD = ''
GO

IF NOT EXISTS (SELECT 1 FROM sys.database_principals  WHERE name = 'ElmahAdmin')
    CREATE USER ElmahAdmin FOR LOGIN ElmahAdmin WITH DEFAULT_SCHEMA=[dbo]
GO

HOW IT SHOULD HAVE BEEN DONE

Connect using your primary account.
Switch your current database to MASTER (circled in, just type in MASTER and hit ENTER) and run

IF NOT EXISTS (SELECT 1 FROM master.sys.databases WHERE ('[' + name + ']' = '[SecondDB]'))
    CREATE DATABASE SecondDB;
GO
IF NOT EXISTS (SELECT 1 FROM sys.sql_logins  WHERE name = 'SecondDBAdmin')
    CREATE LOGIN SecondDBAdmin WITH PASSWORD = ''
GO

Still under your first login, switch to SecondDB and run

CREATE USER SecondDBAdmin FOR LOGIN SecondDBAdmin WITH DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember @rolename = 'db_owner', @membername = 'SecondDBAdmin'
GO

If you don't want the user to have access to the database at all make sure your user does not have system administrator privileges which would allow them access to every database. Baring that, on the database you don't want the 2nd user to have access to, run the following:

USE [1stDatabase]
DROP USER [2ndUser]
GO

To create an exclusive user for the second database:

USE MASTER
CREATE LOGIN [login_name here] WITH PASSWORD = '[password here]'
GO

USE [2ndDatabase]
CREATE USER [login name here] FOR LOGIN [login name here]
GO


Harvester query to identify databases where the ID has reset I need a MySQL query to identify if a database has a table where the auto-incremented ID has reset back to 0. This can result in non-unique rows for those IDs. I'm not sure how to write this. Thanks in advance for any help!

A_I does not go back to zero; it hits the max for the datatype and then is stuck there.  This leads to errors on INSERT that you should be checking for.

This will get you the SELECTs necessary to do the investigation:

SELECT  CONCAT("SELECT  MAX(", column_name, ") AS max_id, '", data_type, "' AS type, ", (column_type LIKE '%unsigned%'), " AS 'unsigned'  FROM  ", table_schema, ".", table_name, ";"
              ) AS stmts
    FROM information_schema.`COLUMNS`
    WHERE extra LIKE '%auto_increment%' ;

Sample row:

 SELECT MAX(AId) AS max_id, 'int' AS type, 1 AS 'unsigned'
 FROM alerts.action;

Running that sample gets:

+--------+------+----------+
| max_id | type | unsigned |
+--------+------+----------+
|      4 | int  |        1 |
+--------+------+----------+

The next step is to understand that int unsigned has a max of 2^32-1, and compare that to the 4.

All of this could be done in a long stored procedure, or in some application code (I leave that as an exercise to the reader).


Peer review database design for various reports I'm currently trying to redesign a poorly thought out database for a peer review system that we use internally at my workplace. Employees can log on and submit a review about another employee who worked on the same team for a specified project.

I was initially tasked to just remove one of the questions (from 4 to 3) but they are stored as columns in a review table instead of as their own table. I'm attempting to redesign the database with possible additions of questions in mind, and here's what I've got so far:

---------------
| category    | // the questions you are asked to rate an employee by
---------------
| id (PK)     |
| name        |
| description |
---------------

--------------------
| score            | // the score you give to a category
--------------------
| id (PK)          |
| score            | // ENUM (1,2,3)
| category_id (FK) | // category.id
| review_id (FK)   | // review.id
--------------------

--------------------
| review           |
--------------------
| id (PK)          |
| date             |
| for_month        |
| reviewee_id (FK) | // employee.id
| reviewer_id (FK) | // employee.id
| project_id (FK)  | // project.id
| comments         |
--------------------

--------------
| department |
--------------
| id (PK)    |
| name       |
--------------

----------------------
| employee           |
----------------------
| id (PK)            |
| first_name         |
| last_name          |
| username           |
| department_id (FK) | // department.id
| start_date         |
| end_date           |
| is_admin           |
----------------------

------------
| project  |
------------
| id (PK)  |
| name     |
| end_date |
------------

--------------------
| project_employee | // maps employees to projects
--------------------
| project_id (FK)  | // project.id
| employee_id (FK) | // employee.id
--------------------

It seems like I'm going in the right direction, at least for storing reviews. However, retrieving data about reviews for managers feels overly complicated, involving lots of joins. I want to provide multiple views of the data, such as:

List of employees with year average scores
Reviews for individual employees
List of departments with year average scores
Reviews for individual departments
List of projects with average scores
Reviews for individual projects
Reviews by month

Things like showing average yearly scores for a department get fairly convoluted, since there is no direct link between scores and departments, or even reviews and departments.

Would it be overkill to add foreign keys to the score table for project, reviewer, reviewee, and department? That way I can very directly query the score table to get scores for departments. Or maybe I should keep what's in review and just add department? Or should I suck it up and keep writing long queries that join multiple tables from score to review to employee to department to get that data? Or am I just doing this wrong?

EDIT: After thinking about it some more, I'm leaning towards adding department_id to the review table, since that should give me everything I need to lookup reviews and grab scores. It also solves another unrelated issue, that of what to do when an employee changes departments, since the original reviews should still count towards the department they were in at the time. My question of if I'm doing this right in general still stands...

I think you are doing well going for the 4th normal form and exploding your data. Now, as you found out, you need to de-normalise to support for historical data support: if the review will always account for a particular department's score whatever the current department of the reviewee_id is, then yes, you definitely need to add a department at the review table and make sure you know what/why it is there for.

Push the logic further: what if there is a 4th and 5th category added in a year? How do you calculate past and future averages then? On 3 or 5 categories? Then another year later, the first category gets removed? Will you delete all scores for that category, or change yet again the average's calculation based on the number of "category" at the time of the "date" of the "score"?

Sometime, too much normalisation is not the best course of action. You have a good core here. Time to custom-fit now. Take a step back, plan for the future, ask the end-users to make sure you support all their foreseeable needs, and look at the impacts of adding/removing/updating departments, employees and categories on historical reporting.

As for reporting, if you are using powerful data tools like IBM Cognos, it will be easy to generate data cubes, then let managers do their own reports. If you are using tools like Microsoft Excel with "external data", then the easiest course of action would be to pre-create Views or Stored Procedures that crunch up the data. The caveat here is that you will have to create a new View/SP for every manager that comes up with a new request/idea/variant.


Why MySQL Database is Bigger After Dump/Import? I have 2 VPS and plan to migrate the DB from first to second. The original DB is taking around 15GB of storage and I manage to dump out a 7GB SQL. However, when I imported the SQL to second VPS, it takes up 2x of storage becoming 32GB.

My questions are:

What might cause my DB from 15GB to become 32GB by just dump+import?
How can I avoid this?

Below are some further detail:

Both VPS are not exactly same and so do the MySQL version. The source DB is version 5.5.47. The receiving DB is version 5.7.13.
The receiving DB is newly install and empty.
The command I used to dump out SQL is: mysqldump -u <username> -p --all-databases --add-drop-database --max_allowed_packet=16777216 --single-transaction > full_dump.sql. The reason I am setting max_allowed_packet is to have the same value compare to receiving DB
Not sure if this is relevant. If I do not specify single-transaction in my mysqldump command, it will error mysqldump: Got error: 2006: MySQL server has gone away when using LOCK TABLES
The source DB storage distribution is different from the receiving DB:

  14858M    /var/lib/mysql

  14483M    /var/lib/mysql/ibdata1

  365M  /var/lib/mysql/wordpress

This is the receiving DB storage distribution:

  31558M    /var/lib/mysql

  31306M    /var/lib/mysql/wordpress

  337M  /var/lib/mysql/wordpress/wp_wfLeechers.ibd

  101M  /var/lib/mysql/wordpress/wp_1059_posts.ibd

I've tried to run table optimisation, but no impact

The first thing that caught my eye was the versions of MySQL you are using.
You said

The source DB is version 5.5.47
The receiving DB is version 5.7.13

These two versions of MySQL use different default values for innodb_file_format

MySQL 5.5 has Antelope as the default
MySQL 5.7 has Barracuda as the default

MySQL 5.7 has new ways of storing InnoDB as mentioned in the Documentation. Please note:

The Barracuda file format is required to use Compressed or Dynamic row formats and associated features such as compression, off-page storage for large variable-length columns, and large index key prefixes (see innodb_large_prefix). This restriction does not apply to tables stored in general tablespaces.

If your data has large variable-length columns, the result of reloading could be the storage of data and index info outside of your BTree Indexes and extra splitting of pages. See DYNAMIC and COMPRESSED Row Formats for more details on this. Many of the new InnoDB file format features are deprecated in MySQL 5.7 and will eventually disappear in future releases.
SUGGESTION #1
Even though deprecated, you could set innodb_file_format to Antelope on the MySQL 5.7 server, restart MySQL, and reload the mysqldump.
SUGGESTION #2
Use MySQL 5.5/5.6 as the Master instead of MySQL 5.7. Restart MySQL on the Master, and reload mysqldump into the Master.
SUGGESTION #3
Run ALTER TABLE innodbtable ROW_FORMAT=COMPRESSED; on all the MySQL 5.7 InnoDB tables.
GIVE IT A TRY !!!


Quiz database design I'm currently working on a quiz system and am trying to make the database design first. I've read some threads about different quizzes but they are all unique on some parts.

The system will use a MySQL database with PHP.

The goals of this quiz system:

Be able to have different "quizzes" inside it
Timetrack on the full quiz and for each question (thinking ahead for a challenge part between people)
Be able to handle a lot of participants in the quiz
Have a good base so it can be developed further with new, more complex functions without the need to rebuild the whole system (the database design that is).

Image of the database design so far. I've written the field on each "relationship".

I'm quite sure most of it is mostly self-explanatory, but I've added notes on the less obvious ones.

In the start at least it won't be any "live" contest. More likely that you do the quiz, get the time for it and then you can send a link to a friend and compete. But I'm still thinking a bit for that part.

I realise that with this design it's a bit more work to get the complete results etc than a basic quiz design where you put everything together in a table or two. But I try to divide it into different tables to be able to scale it better and make it more complex for later.

I'm thinking about adding some kind of support so a question can be used in different quizzes as well.  Just thinking about how I should do it best.

A key feature in this is also to be able to load everything fast.

Any tip is welcome!

wp_quiz (Here we store all the different quizzes)

quiz_id
quiz_name
quiz_text
quiz_site (internal thingy, nothing important for the quiz itself)
quiz_difficulty

wp_quiz_questions

quiz_question_id
quiz_id
quiz_question
quiz_question_difficulty
quiz_question_multianswer (In case it's a special question that has more than one correct answer, then we use for example selects intead of radiobuttons).

wp_quiz_question_answers

quiz_question_answer_id
quiz_question_id
quiz_question_answer
quiz_question_answer_correct

wp_quiz_participants

quiz_participant_id
quiz_id
quiz_participant_name
quiz_participant_email
quiz_participant_userid
quiz_participant_time_start
quiz_participant_time_end

wp_quiz_participant_answers

quiz_participant_answers_id
quiz_id
quiz_question_id
quiz_participants_id
quiz_question_answer_id
quiz_question_time_start
quiz_question_time_end

Pretty good overall, only a few minor changes I would suggest:

quiz_id is redundant in the wp_quiz_participant_answers table, you
can get it via JOINs to either the wp_quiz_questions or
wp_quiz_participants tables
If you have any plans to allow something resembling a login or ability to keep taking quizzes as the same user you should probably break Users into their own table that wp_quiz_participants references. Kinda looks like you might be thinking along those lines with the quiz_participant_userid?

Performance is going to be based around your queries, pay close attention to the performance and add indexing when necessary.


I think you're getting this wrong. ST_Distance_Sphere calculates the ST_Distance and returns the result after converting the radians to units of radius on a sphere of radius.

  Calculations use a spherical earth and a configurable radius. The optional radius argument should be given in meters. If omitted, the default radius is 6,370,986 meters. An ER_WRONG_ARGUMENTS error occurs if the radius argument is present but not positive.

As a matter of trivia, if you're wondering where that radius comes from -- it's pretty amusing. So let's play

d(A,B) = R a /180

So...

a = ST_Distance( POINT(0,0), POINT(0,1) ). This means a=1.
r = 6370986 meters
pi = 3.141593 ish... Using pi() here

So your call to ST_Distance_Sphere( Point(0,0), Point(0,1) ); is the same as,

SELECT ((6370986 * 1 * pi()) / 180);
+------------------------------+
| ((6370986 * 1 * pi()) / 180) |
+------------------------------+
|            111194.6822984635 |
+------------------------------+
1 row in set (0.00 sec)

So as you can see ST_Distance_Sphere is a Cartesian distance using basic geometry to get to a sphereacle distance. That makes this not a bug. in fact, PostGIS will return the same results.

SELECT ST_DistanceSphere( ST_MakePoint(0,0), ST_MakePoint(0,1) );
 st_distance_sphere
--------------------
    111195.07973463
(1 row)

PostGIS calculates on a sphere using the average mean radius of the WGS84 reference ellipsoid. MySQL uses a totally random value that originates from a constant in PostgreSQL 20 years ago.

In MySQL, this isn't a GIS function. It's a geometry function, you could argue MySQL doesn't support GIS at all as it does not even use Spatial Reference Systems in its calculations. It makes no assumption about an ellipsoidal projection or spatial reference system.


MySQL restore bd from directory copy MySQL restore bd from directory copy.
Hello, I recently lost the external storage where my MySQL data directory was located, but I have copied the entire data directory in another location, the problem is that I copied it without stopping mysql before, I would like to know how to get mysql from the directory data where information is saved before the crash

Sorry, you are probably SOL.

Case 1:  Copying the disk while MySQL is actively writing data.  Very likely to be in an inconsistent state -- some things written to disk and copied; some not.

Case 2:  MySQL up during the copy, but not actively writing (UPDATE, INSERT, etc).  This might work since it is identical to a power failure.


How to add / create table in the current database in mysql? mySQL 5.6.24: Could anyone guide me on that "How to create a base view and table for a specific plugin?"

After uploading the plug-in, I'm getting following error:

  "There has been an error processing your request SQLSTATE[42S02]: Base
  table or view not found: 1146 Table
  'yuvakxkb_mage800.mgjm_manthan_marketplace_seller' doesn't exist,

query was:

SELECT `main_table`.*
FROM `mgjm_manthan_marketplace_seller` AS `main_table`
WHERE (`user_id` = '1')"

How can I create a base view and table for the above error in MySQL?

Is this Magento?  I had a similar issue where the plugin I was using wasn't compatible with the version of Magento that was installed.

Check your plugin compatibility then if you're running this on a hosted platform, use phpMyAdmin to verify that the table actually exists.

Also, have you contacted the Magento forums to see if they can help?


Problem with new MySQL Workbench connection I'm working on Debian 8 and wanted to create database using MySQL Workbench. I did everything in this tutorial, but  when I created a new MySQL Connection, the program returned:

  Failed to Connect to MySQL at 127.0.0.1:3306 with user root, Access denied for user 'root'@'localhost' (using password: YES).

What am I doing wrong? I typed the correct password.

Since you get an "access denied" the actual connection to the server worked. You just have used a user that is not allowed to connect either because it connects from a machine that is not enabled in the user settings and/or the user does not exist or the password is wrong.

Is it the correct server you connected to?
Are you 100% sure to have used the right user/pw?
Is the IP address of the user allowed to connect at all?


Clear all tables with one DELETE Is it possible to clear all tables with one request?
I have tried the following with no result.

DELETE a,b,c,d,e,f,g,h,i,j,k,l,m,n,o
FROM jos_bet_details as a, jos_bet_1x2 as b, jos_bet_1x2_best as c, jos_bet_1x2_prev as d,
     jos_bet_1x3 as e, jos_bet_1x3_best as f, jos_bet_1x3_prev g, jos_bet_hcp as h,
     jos_bet_hcp_best i, jos_bet_hcp_prev j, jos_bet_ou k,jos_bet_ou_best l,
     jos_bet_ou_prev m, jos_bet_debug n, jos_bet_deleted o

Here is your solution

SELECT CONCAT('DELETE ',delete_list,' FROM ',table_list)
INTO @mass_delete_table_command
FROM
(
    SELECT GROUP_CONCAT(table_name,'.*') delete_list,
    GROUP_CONCAT(table_schema,'.',table_name) table_list
    FROM
    (
        SELECT table_schema,table_name
        FROM information_schema.tables
        WHERE table_schema=database() AND
        table_name IN
        ('jos_bet_details','jos_bet_1x2','jos_bet_1x2_best','jos_bet_1x2_prev',
         'jos_bet_1x3','jos_bet_1x3_best','jos_bet_1x3_prev','jos_bet_hcp',
         'jos_bet_hcp_best','jos_bet_hcp_prev','jos_bet_ou','jos_bet_ou_best',
         'jos_bet_ou_prev','jos_bet_debug','jos_bet_deleted')
    ) ListOfTables
) DeleteParameters;
PREPARE s1 FROM @mass_delete_table_command;
EXECUTE s1;
DEALLOCATE PREPARE s1;

Here is the principle behind this: You want to use information_schema.tables to build the DELETE query dynamically for you.

First collect the names of the tables in a variable called 'mass_delete_table_command' as a delete list and a table list. Then, execute it as a prepared statement.

Here is sample code from creating 4 tables, adding records them, and zapping the records:

use test
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
DROP TABLE IF EXISTS t4;
CREATE TABLE t1 (n int NOT NULL);
CREATE TABLE t2 like t1;
CREATE TABLE t2 like t1;
CREATE TABLE t3 like t1;
CREATE TABLE t4 like t1;
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7);
INSERT INTO t2 SELECT * FROM t1;
INSERT INTO t3 SELECT * FROM t1;
INSERT INTO t4 SELECT * FROM t1;
SELECT A.*,B.*,C.*,D.* FROM
(SELECT COUNT(1) t1count FROM t1) A,
(SELECT COUNT(1) t2count FROM t2) B,
(SELECT COUNT(1) t3count FROM t3) C,
(SELECT COUNT(1) t4count FROM t4) D;
SELECT CONCAT('DELETE ',delete_list,' FROM ',table_list)
INTO @mass_delete_table_command
FROM
(
    SELECT GROUP_CONCAT(table_name,'.*') delete_list,
    GROUP_CONCAT(table_schema,'.',table_name) table_list
    FROM
    (
        SELECT table_schema,table_name
        FROM information_schema.tables
        WHERE table_schema=database() AND
        table_name IN ('t1','t2','t3','t4')
    ) ListOfTables
) DeleteParameters;
SELECT @mass_delete_table_command;
PREPARE s1 FROM @mass_delete_table_command;
EXECUTE s1;
DEALLOCATE PREPARE s1;
SELECT A.*,B.*,C.*,D.* FROM
(SELECT COUNT(1) t1count FROM t1) A,
(SELECT COUNT(1) t2count FROM t2) B,
(SELECT COUNT(1) t3count FROM t3) C,
(SELECT COUNT(1) t4count FROM t4) D;

I ran this in MySQL 5.5.12 on my PC. Here is the output:

mysql> use test
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
Database changed
mysql> DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (n int NOT NULL);
CREATE TABLE t2 like t1;
CREATE TABLE t2 like t1;
CREATE TABLE t3 like t1;
CREATE TABLE t4 like t1;
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7);
INSERT INTO t2 SELECT * FROM t1;
INSERT INTO t3 SELECT * FROM t1;
INSERT INTO t4 SELECT * FROM t1;
SELECT A.*,B.*,C.*,D.* FROM
(SELECT COUNT(1) t1count FROM t1) A,
(SELECT COUNT(1) t2count FROM t2) B,
(SELECT COUNT(1) t3count FROM t3) C,
(SELECT COUNT(1) t4count FROM t4) D;
SELECT CONCAT('DELETE ',delete_list,' FROM ',table_list)
INTO @mass_delete_table_command
FROM
(
    SELECT GROUP_CONCAT(table_name,'.*') delete_list,
    GROUP_CONCAT(table_schema,'.',table_name) table_list
    FROM
    (
        SELECT table_schema,table_name
        FROM information_schema.tables
        WHERE table_schema=database() AND
        table_name IN ('t1','t2','t3','t4')
    ) ListOfTables
) DeleteParameters;
SELECT @mass_delete_table_command;
PREPARE s1 FROM @mass_delete_table_command;
EXECUTE s1;
DEALLOCATE PREPARE s1;
SELECT A.*,B.*,C.*,D.* FROM
(SELECT COUNT(1) t1count FROM t1) A,
(SELECT COUNT(1) t2count FROM t2) B,
(SELECT COUNT(1) t3count FROM t3) C,
(SELECT COUNT(1) t4count FROM t4) D;
Query OK, 0 rows affected (0.04 sec)

mysql> DROP TABLE IF EXISTS t2;
Query OK, 0 rows affected (0.03 sec)

mysql> DROP TABLE IF EXISTS t3;
Query OK, 0 rows affected (0.03 sec)

mysql> DROP TABLE IF EXISTS t4;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE t1 (n int NOT NULL);
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE t2 like t1;
Query OK, 0 rows affected (0.10 sec)

mysql> CREATE TABLE t2 like t1;
ERROR 1050 (42S01): Table 't2' already exists
mysql> CREATE TABLE t3 like t1;
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE t4 like t1;
Query OK, 0 rows affected (0.25 sec)

mysql> INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7);
Query OK, 7 rows affected (0.08 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t2 SELECT * FROM t1;
Query OK, 7 rows affected (0.05 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t3 SELECT * FROM t1;
Query OK, 7 rows affected (0.07 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t4 SELECT * FROM t1;
Query OK, 7 rows affected (0.06 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT A.*,B.*,C.*,D.* FROM
    -> (SELECT COUNT(1) t1count FROM t1) A,
    -> (SELECT COUNT(1) t2count FROM t2) B,
    -> (SELECT COUNT(1) t3count FROM t3) C,
    -> (SELECT COUNT(1) t4count FROM t4) D;
+---------+---------+---------+---------+
| t1count | t2count | t3count | t4count |
+---------+---------+---------+---------+
|       7 |       7 |       7 |       7 |
+---------+---------+---------+---------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT('DELETE ',delete_list,' FROM ',table_list)
    -> INTO @mass_delete_table_command
    -> FROM
    -> (
    ->     SELECT GROUP_CONCAT(table_name,'.*') delete_list,
    ->     GROUP_CONCAT(table_schema,'.',table_name) table_list
    ->     FROM
    ->     (
    ->         SELECT table_schema,table_name
    ->         FROM information_schema.tables
    ->         WHERE table_schema=database() AND
    ->         table_name IN ('t1','t2','t3','t4')
    ->     ) ListOfTables
    -> ) DeleteParameters;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @mass_delete_table_command;
+-----------------------------------------------------------------+
| @mass_delete_table_command                                      |
+-----------------------------------------------------------------+
| DELETE t1.*,t2.*,t3.*,t4.* FROM test.t1,test.t2,test.t3,test.t4 |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> PREPARE s1 FROM @mass_delete_table_command;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE s1;
Query OK, 28 rows affected (0.08 sec)

mysql> DEALLOCATE PREPARE s1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT A.*,B.*,C.*,D.* FROM
    -> (SELECT COUNT(1) t1count FROM t1) A,
    -> (SELECT COUNT(1) t2count FROM t2) B,
    -> (SELECT COUNT(1) t3count FROM t3) C,
    -> (SELECT COUNT(1) t4count FROM t4) D;
+---------+---------+---------+---------+
| t1count | t2count | t3count | t4count |
+---------+---------+---------+---------+
|       0 |       0 |       0 |       0 |
+---------+---------+---------+---------+
1 row in set (0.00 sec)

mysql>

This will definitely work for you as long as

you are standing in the correct database at the time
you suppply the list of tables you want dropped to the subqueries

Give it a Try !!!


How to atomically replace table data in PostgreSQL I want to replace the entire contents of a table, without affecting any incoming SELECT statements during the process.

The use case is to have a table which stores mailbox information that is regularly extracted, and needs to be stored in a PostgreSQL table. There are many clients using an application that is constantly querying that same table.

Normally, I would do something like (pseudocode incoming)...

BEGIN TRANSACTION
TRUNCATE TABLE
INSERT INTO
COMMIT

But unfortunately the table cannot be read during this process; due to the time it takes INSERT INTO to complete. The table is locked.

In MySQL, I would have used their atomic RENAME TABLE command to avoid these issues...

CREATE TABLE table_new LIKE table;
INSERT INTO table_new;
RENAME TABLE table TO table_old, table_new TO table; *atomic operation*
DROP TABLE table_old;

How could I achieve this in PostgreSQL?

For the purposes of this question, you can assume I am not using foreign keys.

Right, the TRUNCATE TABLE command you are performing "... acquires an ACCESS EXCLUSIVE lock on each table it operates on", so in the first SQL block you posted, any other clients attempting to access the table after that time will be blocked until your INSERT finishes and you COMMIT.

You can use the same workaround as in your MySQL-specific code; Postgres supports roughly the same syntax and will have similar locking behavior. To wit:

BEGIN;
-- You probably want to make sure that no one else is
-- INSERT / UPDATE / DELETE'ing from the original table, otherwise
-- those changes may be lost during this switchover process. One way
-- to do that would be via:
-- LOCK TABLE "table" IN SHARE ROW EXCLUSIVE mode;
CREATE TABLE "table_new" (LIKE "table");
INSERT INTO "table_new" ...;

-- The ALTER TABLE ... RENAME TO command takes an Access Exclusive lock on "table",
-- but these final few statements should be fast.
ALTER TABLE "table" RENAME TO "table_old";
ALTER TABLE "table_new" RENAME TO "table";
DROP TABLE "table_old";

COMMIT;

Extra bonus: Postgres actually supports transactional DDL, unlike MySQL, so in case you need to ROLLBACK the above transaction, you may safely do so.