Bug #73213 strange warning message when start transaction WITH CONSISTENT SNAPSHOT

• Submitted: 6 Jul 2014 5:02
• Modified: 7 Jul 2014 6:46
• Reporter: zhai weixiang (OCA)
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.7.4, 5.6.20
• OS: Any
• CPU Architecture: Any

[6 Jul 2014 5:02] zhai weixiang

Description:

strange warning message when start transaction WITH CONSISTENT SNAPSHOT:

mysql> show session variables like '%iso%';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)

mysql> start transaction WITH CONSISTENT SNAPSHOT;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                 |
+---------+------+-------------------------------------------------------------------------------------------------------------------------+
| Warning |  138 | InnoDB: WITH CONSISTENT SNAPSHOT was ignored because this phrase can only be used with REPEATABLE READ isolation level. |
+---------+------+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> set session tx_isolation = 'REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction WITH CONSISTENT SNAPSHOT;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                 |
+---------+------+-------------------------------------------------------------------------------------------------------------------------+
| Warning |  138 | InnoDB: WITH CONSISTENT SNAPSHOT was ignored because this phrase can only be used with REPEATABLE READ isolation level. |
+---------+------+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show session variables like '%iso%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

How to repeat:
described above

Suggested fix:
correct it.

[7 Jul 2014 6:46] MySQL Verification Team

Hello Zhai,

Thank you for the report.
Verified as described.

Thanks,
Umesh

[7 Jul 2014 6:50] MySQL Verification Team

// 5.6.

mysql> set session tx_isolation = 'REPEATABLE-READ';
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction WITH CONSISTENT SNAPSHOT;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                 |
+---------+------+-------------------------------------------------------------------------------------------------------------------------+
| Warning |  138 | InnoDB: WITH CONSISTENT SNAPSHOT was ignored because this phrase can only be used with REPEATABLE READ isolation level. |
+---------+------+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show session variables like '%iso%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.6.20-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql>