MySQL Bugs: #49891: View DDL breaks REPEATABLE READ

Bug #49891	View DDL breaks REPEATABLE READ
Submitted:	23 Dec 2009 11:03	Modified:	4 Aug 2010 23:25
Reporter:	Philip Stoev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S2 (Serious)
Version:	5.1, next-4284, 6.0-codebase-bugfixing, next-mr	OS:	Any
Assigned to:	Jon Olav Hauglid	CPU Architecture:	Any

Description:
Executing a View DDL would cause REPEATABLE READ to break -- the same SELECT issued twice within the same transaction will return two different results.

How to repeat:
A test case will be uploaded shortly.

Grammar for bug 49891

Attachment: bug49891.yy (application/octet-stream, text), 695 bytes.

To reproduce with the RQG:

$ perl runall.pl \
  --mysqld=--loose-innodb-lock-wait-timeout=1 \
  --mysqld=--table-lock-wait-timeout=1 \
  --mysqld=--skip-safemalloc \
  --grammar=conf/bug49891.yy \
  --gendata=conf/metadata_stability.zz \
  --validator=SelectStability \
  --engine=Innodb \
  --mysqld=--innodb \
  --mysqld=--default-storage-engine=Innodb \
  --mysqld=--transaction-isolation=SERIALIZABLE \
  --mysqld=--innodb-flush-log-at-trx-commit=2 \
  --mysqld=--table-lock-wait-timeout=1 \
  --mysqld=--innodb-lock-wait-timeout=1 \
  --mysqld=--log-output=file \
  --queries=1M \
  --duration=600 \
  --reporters=Deadlock,ErrorLog,Backtrace,Shutdown \
  --basedir=/build/bzr/mysql-next-4284/

What happens in this test is that View DDL will be run concurrently with SELECTs in a transaction. The SelectStability RQG module will run each SELECT several times, looking for holes in the REPEATABLE READ. Shortly after takeoff, a hole will be reported:

# 13:02:22 Query: SELECT /* QUERY_ID 2902528 */ * FROM  view_2; returns different result when executed after a delay of 0 seconds.
# 13:02:22 --- /tmp//randgen26512-1261566142-server0.dump       2009-12-23 13:02:22.000000000 +0200
# 13:02:22 +++ /tmp//randgen26512-1261566142-server1.dump       2009-12-23 13:02:22.000000000 +0200
# 13:02:22 @@ -1,10 +1,10 @@
# 13:02:22 -71
# 13:02:22 -71
# 13:02:22 -71
# 13:02:22 -71
# 13:02:22 -71
# 13:02:22 -71
# 13:02:22 -71
# 13:02:22 -71
# 13:02:22 -71
# 13:02:22 -71
# 13:02:22 +160
# 13:02:22 +160
# 13:02:22 +160
# 13:02:22 +160
# 13:02:22 +160
# 13:02:22 +160
# 13:02:22 +160
# 13:02:22 +160
# 13:02:22 +160
# 13:02:22 +160

For convenience, each SELECT is labelled with an ID that can then be grepped in the query log. Here is what is seen there:

13 Query     ALTER ALGORITHM = MERGE VIEW view_2 AS SELECT 71 FROM `view_1`
...
9 Query     SELECT /* QUERY_ID 2902528 */ * FROM  view_2
...
17 Query     CREATE OR REPLACE ALGORITHM = UNDEFINED VIEW view_2 AS SELECT 160 FROM `table10_innodb`
...
9 Query     SELECT /* QUERY_ID 2902528 */ * FROM  view_2

Connection 9 does not execute any other statements between the two SELECTs. The the 71 and the 160 from the diff come from the two different view definitions.

Please pull fresh from the RQG tree before trying to reproduce this bug. You will notice that on the connection that is issuing the SELECTs, SET AUTOCOMMIT=OFF was in effect and a transaction has been started with START TRANSACTION prior to the two SELECTs in question.

Note that an MTR test case would not catch the issue:

--source include/have_innodb.inc

--connect (con1, localhost, root,,)
--connect (con2, localhost, root,,)

--connection con1
USE test;

--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

CREATE TABLE t1 ( f1 INTEGER ) ENGINE = INNODB;
CREATE VIEW v1 AS SELECT 1 FROM t1 LIMIT 1;
INSERT INTO t1 VALUES (1);

SET AUTOCOMMIT = OFF;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM v1;

--connection con2
USE test;
--send CREATE OR REPLACE VIEW v1 AS SELECT 2 FROM t1 LIMIT 1;

--connection con1
--sleep 1

SELECT * FROM v1;
COMMIT;
DROP TABLE t1;
DROP VIEW v1;

--disconnect con2
--disconnect con1

the view DDL properly hang waiting for the transaction to complete.

MTR test case:

--source include/have_innodb.inc

--disable_warnings
DROP TABLE IF EXISTS t1;
DROP VIEW IF EXISTS v1, v2;
--enable_warnings

CREATE TABLE t1 ( f1 INTEGER ) ENGINE = innodb;
CREATE VIEW v1 AS SELECT 1 FROM t1;
CREATE VIEW v2 AS SELECT 2 FROM v1;

connect (con2, localhost, root);
connect (con3, localhost, root);

--echo # Connection default
connection default;
START TRANSACTION;
SELECT * FROM v1;

--echo # Connection con3
connection con3;
--send ALTER VIEW v1 AS SELECT 4 FROM t1

--echo # Connection con2
connection con2;
START TRANSACTION;
--send SELECT * FROM v2

--echo # Connection default
connection default;
ALTER VIEW v2 AS SELECT 5 FROM t1;

--echo # Connection con2;
connection con2;
--reap
SELECT * FROM v2;
COMMIT;

--echo # Connection con3;
connection con3;
--reap

--echo # Connection default
connection default;
DROP TABLE t1;
DROP VIEW v1, v2;
disconnect con2;
disconnect con3;

Running this test case, I've observed three different results:
1) "ALTER VIEW v2 AS SELECT 5 FROM t1;" blocks trying to get an exclusive metadata lock on v2
2) Both "SELECT * FROM v2;" return 5.
3) First "SELECT * FROM v2;" returns 2, second returns 5.

Simplified and repeatable test case:

--source include/have_innodb.inc

--disable_warnings
DROP TABLE IF EXISTS t1, t2;
DROP VIEW IF EXISTS v2;
--enable_warnings

CREATE TABLE t1 ( f1 INTEGER ) ENGINE = innodb;
CREATE TABLE t2 ( f1 INTEGER );
CREATE VIEW v1 AS SELECT 1 FROM t1;

connect (con2, localhost, root);
connect (con3, localhost, root);

--echo # Connection con3
connection con3;
LOCK TABLE t1 WRITE;

--echo # Connection default
connection con2;
START TRANSACTION;
--send SELECT * FROM v1

--echo # Connection con2
connection default;
let $wait_condition=
  SELECT COUNT(*) = 1 FROM information_schema.processlist
  WHERE state = "Table lock" AND info = "SELECT * FROM v1";
--source include/wait_condition.inc
ALTER VIEW v1 AS SELECT 2 FROM t2;

--echo # Connection con3
connection con3;
UNLOCK TABLES;

--echo # Connection default;
connection con2;
--reap
SELECT * FROM v1;
COMMIT;

DROP TABLE t1, t2;
DROP VIEW v1;
disconnect con2;
disconnect con3;

The first SELECT returns 1 and the second returns 2 even if they are in the same transaction. With this test case, I've been able reproduce the bug both in 5.1 (5.1-bugteam) and 5.5 (next-mr-bugfixing).

Here is what happens:
1) connection 3 locks t1
2) connection default opens and locks v1, tries to open and lock t1, backs off.
3) connection 2 alters v1
4) connection 3 unlocks t1
5) connection default tries to reopen and lock v1. It notices that it has opened v1 before and skips doing it again. It therefore uses the old version of v1. 

This is a problem with the way views are handled.

Not repeatable in 5.1

Jon Olav,

thank you for the second test. Repeatable with 5.1 and Locked state.

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/112269

3064 Jon Olav Hauglid	2010-06-26
      Bug #49891 View DDL breaks REPEATABLE READ
      
      The problem was that if a query accessing a view was blocked due to
      conflicting locks on tables in the view definition, it would be possible
      for a different connection to alter the view definition before the view
      query completed. When the view query later resumed, it used the old view
      definition. This meant that if a the view query was later repeated inside
      the same transaction, the two executions of the query would give different
      results, thus breaking repeatable read. (The first query used the old
      view definition, the second used the new view definition).
      
      This bug is no longer repeatable with the recent changes to the metadata
      locking subsystem (revno: 3040). The view query will no longer back-off
      and release the lock on the view definiton. Instead it will wait for
      the conflicting lock(s) to go away while keeping the view definition lock.
      This means that it is no longer possible for a concurrent connection to
      alter the view definition. Instead, any such attempt will be blocked.
      
      In the case from the bug report where the same view query was executed
      twice inside the same transaction, any ALTER VIEW from other connections
      will now be blocked until the transaction has completed (or aborted).
      The view queries will therefore use the same view definition and we will
      have repeatable read.
      
      Test case added to innodb_mysql_lock.test.
      This patch contains no code changes.

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/112273

3064 Jon Olav Hauglid	2010-06-26
      Bug #49891 View DDL breaks REPEATABLE READ
      
      The problem was that if a query accessing a view was blocked due to
      conflicting locks on tables in the view definition, it would be possible
      for a different connection to alter the view definition before the view
      query completed. When the view query later resumed, it used the old view
      definition. This meant that if the view query was later repeated inside
      the same transaction, the two executions of the query would give different
      results, thus breaking repeatable read. (The first query used the old
      view definition, the second used the new view definition).
      
      This bug is no longer repeatable with the recent changes to the metadata
      locking subsystem (revno: 3040). The view query will no longer back-off
      and release the lock on the view definiton. Instead it will wait for
      the conflicting lock(s) to go away while keeping the view definition lock.
      This means that it is no longer possible for a concurrent connection to
      alter the view definition. Instead, any such attempt will be blocked.
      
      In the case from the bug report where the same view query was executed
      twice inside the same transaction, any ALTER VIEW from other connections
      will now be blocked until the transaction has completed (or aborted).
      The view queries will therefore use the same view definition and we will
      have repeatable read.
      
      Test case added to innodb_mysql_lock.test.
      This patch contains no code changes.

Pushed to mysql-trunk-runtime (5.5.6).

Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-next-mr (revid:alik@ibmvm-20100804081630-ntapn8bf9pko9vj3) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (pib:20)

Changes to test suite. No changelog entry needed.