MySQL Bugs: #48315: Metadata lock is not taken for merged views that use an INFORMATION

Bug #48315	Metadata lock is not taken for merged views that use an INFORMATION_SCHEMA table
Submitted:	26 Oct 2009 15:16	Modified:	7 Mar 2010 19:57
Reporter:	Jon Olav Hauglid	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	mysql-6.0-codebase	OS:	Any
Assigned to:	Jon Olav Hauglid	CPU Architecture:	Any
Tags:	mdl

Description:
From Kostja's review of the patch for Bug#47313:

Apparently, we won't take a metadata lock for a view that was merged with an INFORMATION_SCHEMA table.
The scenario would be: create a view that refers to an information schema table, start executing the statement, replace the view with a different one while the statement is executed. This should lead to master/slave becoming out of sync.

How to repeat:
MTR test case:

--source include/have_debug_sync.inc
--source include/have_binlog_format_mixed.inc
--source include/count_sessions.inc

--disable_warnings
DROP TABLE IF EXISTS t1;
DROP VIEW IF EXISTS v1;
DROP PROCEDURE IF EXISTS p1;
--enable_warnings

connect (con2, localhost, root);

--echo # Connection 1
connection default;

CREATE VIEW v1 AS SELECT schema_name FROM information_schema.schemata;
CREATE TABLE t1 (str VARCHAR(50));
CREATE PROCEDURE p1() INSERT INTO t1 SELECT * FROM v1;
CALL p1();
RESET MASTER;

--echo # Start to insert values from IS.schemata...
SET DEBUG_SYNC= 'after_lock_tables_takes_lock SIGNAL locked WAIT_FOR view_recreated';
--send CALL p1()

--echo # Connection 2
connection con2;
SET DEBUG_SYNC= 'now WAIT_FOR locked';
--echo # ... then drop and recreate the view
DROP VIEW v1;
CREATE VIEW v1 AS SELECT engine FROM information_schema.engines;
--echo # Now complete the insert
SET DEBUG_SYNC= 'now SIGNAL view_recreated';

--echo # Connection 1
connection default;
--reap

--echo # What ended up in t1?
SELECT * FROM t1;

--echo # Now check the binlog.
let $VERSION= 'select version()';
source include/show_binlog_events.inc;

--echo # Cleanup
DROP PROCEDURE p1;
DROP TABLE t1;
DROP VIEW v1;
SET DEBUG_SYNC= 'RESET';
disconnect con2;

--source include/wait_until_count_sessions.inc

t1 ends up with 2x SELECT schema_name FROM information_schema.schemata

But in the binlog INSERT comes after the view has been recreated:
Log_name        Pos     Event_type      Server_id       End_log_pos     Info
master-bin.000001       #       Query   #       #       use `test`; DROP VIEW v1
master-bin.000001       #       Query   #       #       use `test`; CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS SELECT engine FROM information_schema.engines
master-bin.000001       #       Query   #       #       use `test`; INSERT INTO t1 SELECT * FROM v1

Suggested fix:
Make sure v1 is locked so that DROP VIEW v1 waits until the INSERT completes - similar to what is already done for views that do not reference IS tables.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/98210

3055 Jon Olav Hauglid	2010-01-26
      Bug #48315 Metadata lock is not taken for merged views that use
                 an INFORMATION_SCHEMA table
      
      The problem was that after an information schema table had been merged
      into a view, a metadata lock would not be taken to protect the view 
      definition. This meant that it was possible for concurrent view DDL to
      execute, thereby breaking the binary log. For example, it would be
      possible for DROP VIEW to appear in the binary log before a query using
      the view.
      
      The view is opened normally and merged at prepare or first time a stored
      procedure is executed. This bug would only be triggered after merging - 
      i.e. execution of a prepared statement or second invocation of a stored
      procedure.
      
      This patch resolves the problem by making sure a metadata lock is taken
      for merged views that use information schema tables.
      
      Test cased added to bug48315.test. This is a temporary file, the test case
      will be merged into sp-sync.test before push.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/100521

3102 Jon Olav Hauglid	2010-02-16
      Bug #48315 Metadata lock is not taken for merged views that use
                 an INFORMATION_SCHEMA table
      
      The problem was that after an information schema table had been merged
      into a view, a metadata lock would not be taken to protect the view 
      definition. This meant that it was possible for concurrent view DDL to
      execute, thereby breaking the binary log. For example, it would be
      possible for DROP VIEW to appear in the binary log before a query using
      the view.
      
      The view is opened normally and merged at prepare or first time a stored
      procedure is executed. This bug would only be triggered after merging - 
      i.e. execution of a prepared statement or second invocation of a stored
      procedure.
      
      This patch resolves the problem by making sure a metadata lock is taken
      for merged views that use information schema tables.
      
      Test cased added to view.test.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/100756

3103 Jon Olav Hauglid	2010-02-18
      Bug #48315 Metadata lock is not taken for merged views that use
                 an INFORMATION_SCHEMA table
      
      When a prepared statement using a merged view containing an information
      schema table was executed, a metadata lock of the view was not taken.
      This meant that it was possible for concurrent view DDL to execute,
      thereby breaking the binary log. For example, it was possible
      for DROP VIEW to appear in the binary log before a query using the view.
      This also happened when a statement in a stored routine was executed a
      second time.
      
      For such views, the information schema table is merged into the view
      during the prepare phase (or first execution of a statement in a routine).
      The problem was that we took a short cut and were not executing full-blown
      view opening during subsequent executions of the statement. As a result,
      a metadata lock on the view was not taken to protect the view definition.
      
      This patch resolves the problem by making sure a metadata lock is taken
      for views even after information schema tables are merged into them.
      
      Test cased added to view.test.

Pushed to mysql-next-4284.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100225194420-p60r4u90tszj8q2x) (version source revid:alik@sun.com-20100223155607-rq7rqm3ghgag75b7) (merge vers: 6.0.14-alpha) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100225194305-h49uyjrlfl3mwo60) (version source revid:wlad@sun.com-20100223223036-f9tvi5gqrtj91r5o) (pib:16)

Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:alik@sun.com-20100225195857-farb6yvy8x06bylj) (merge vers: 5.5.99-m3) (pib:16)

Noted in 5.5.3, 6.0.14 changelog.

If a prepared statement using a merged view referencing an
INFORMATION_SCHEMA table was executed, no metadata lock of the view
was taken. Consequently, it was possible for concurrent DDL
statements on the view to execute and cause statements to be written
in the wrong order to the binary log.

Correction: Not present in any 5.5.x release. 5.5.3 changelog entry removed.