Bug #40515 Query on a partitioned table does not return 'lock wait timeout exceeded'

• Submitted: 5 Nov 2008 0:18
• Modified: 20 Jan 2009 21:14
• Reporter: Elena Stepanova
• Status: Closed
• Category: MySQL Server: Partitions
• Severity: S3 (Non-critical)
• Version: 5.1.26,5.1.29
• OS: Any
• Assigned to: Mattias Jonsson
• CPU Architecture: Any

[5 Nov 2008 0:18] Elena Stepanova

Description:
If two updates are performed concurrently on a partitioned InnoDB table not using indexes, the second update quits peacefully when timeout is exceeded, not returning an error and not updating anything.

Thus, an application or logic that relies on a correct update in case no error occurred, might work wrongly.

It does not happen if there is no partitions -- the second update returns the ERROR 1205 after timeout is exceeded -- so I'm setting the category to 'Partitioning'.

It works the same way in 5.1.26.

How to repeat:
# The manual test case with comments is below

use test;
drop table if exists t;
create table t ( i int, n int ) engine = innodb partition by key (i) partitions 2;

insert into t values ( 1, 1 ), ( 2, 2 ), ( 3, 3 ), ( 4, 4 ); 

# ---------------
# CONNECTION 1:
# ---------------
use test;
set autocommit = 0;
update t set n = n+10 where n = 1;
# Query OK, 1 row affected (0.01 sec) 

# ---------------
# CONNECTION 2:
# ---------------
use test;
update t set n = n+100 where n = 3;
# it starts waiting and waits for the configured timeout, 
# after which (ATTENTION!) it says
# Query OK, 0 rows affected (51.95 sec)
# Rows matched: 0  Changed: 0  Warnings: 0
#
# -- no error and no rows matched, 
# even although the following select returns the row:

select * from t where n = 3;
# +------+------+
# | i    | n    |
# +------+------+
# |    3 |    3 |
# +------+------+
# 1 row in set (0.00 sec)

Suggested fix:
To return ERROR 1205 if lock wait timeout is exceeded, just as it's done for tables without partitioning.

[3 Dec 2008 15:11] Mattias Jonsson

The return value from the partitions rnd_next was masked at the exit of ha_partition::rnd_next, solved by this patch (will commit soon):
=== modified file 'sql/ha_partition.cc'
--- sql/ha_partition.cc	2008-11-24 16:24:03 +0000
+++ sql/ha_partition.cc	2008-12-03 15:05:28 +0000
@@ -3431,7 +3431,7 @@
   
   while (TRUE)
   {
-    int result= file->rnd_next(buf);
+    result= file->rnd_next(buf);
     if (!result)
     {
       m_last_part= part_id;

[4 Dec 2008 9:48] Bugs System

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

2727 Mattias Jonsson	2008-12-04
      Bug#40515: Query on a partitioned table does not return
      'lock wait timeout exceeded'
      
      Problem was a bug in the implementation of scan in partitioning
      which masked the error code from the partition's handler.
      
      Fixed by returning the value from the underlying handler.

[10 Dec 2008 9:46] Mattias Jonsson

pushed into mysql-5.1-bugteam and mysql-6.0-bugteam

[15 Jan 2009 6:34] Bugs System

Pushed into 5.1.31 (revid:[e-mail]-20090115053147-tx1oapthnzgvs1ro) (version source revid:[e-mail]-20081230114838-cn52tu180wcrvh0h) (merge vers: 5.1.31) (pib:6)

[15 Jan 2009 16:38] Jon Stephens

Documented in the 5.1.31 changelog as follows:

        A concurrent query that timed out when run against a partitioned 
        table having no column indexes could fail silently, rather than 
        returning Lock wait timeout exceeded.

Set back to PQ status pending merge to 6.0 tree.

[15 Jan 2009 18:56] Mattias Jonsson

For Doc:

The bug was not limited to 'having no column indexes'.

Any error message in rnd_next() (i.e. scanning a partitioned table) was silently ignored.

[19 Jan 2009 11:27] Bugs System

Pushed into 5.1.31-ndb-6.2.17 (revid:[e-mail]-20090119095303-uwwvxiibtr38djii) (version source revid:[e-mail]-20090115073240-1wanl85vlvw2she1) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)

[19 Jan 2009 13:05] Bugs System

Pushed into 5.1.31-ndb-6.3.21 (revid:[e-mail]-20090119104956-guxz190n2kh31fxl) (version source revid:[e-mail]-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)

[19 Jan 2009 13:57] Jon Stephens

Modified changelog entry per developer comment.

Set status to NDI pending merge to 6.0.

[19 Jan 2009 16:10] Bugs System

Pushed into 5.1.31-ndb-6.4.1 (revid:[e-mail]-20090119144033-4aylstx5czzz88i5) (version source revid:[e-mail]-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)

[19 Jan 2009 17:04] Jon Stephens

Set back to NDI pending merge to 6.0.

[20 Jan 2009 18:57] Bugs System

Pushed into 6.0.10-alpha (revid:[e-mail]-20090119171328-2hemf2ndc1dxl0et) (version source revid:[e-mail]-20081230114916-c290n83z25wkt6e4) (merge vers: 6.0.9-alpha) (pib:6)

[20 Jan 2009 21:14] Jon Stephens

Fix also noted in 6.0.10 changelog; closed.