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

• Submitted: 5 Nov 2008 1:18
• Modified: 20 Jan 22:14
• Reporter: Elena Stepanova
• Status: Closed
• Category: Server: Partition
• Severity: S3 (Non-critical)
• Version: 5.1.26,5.1.29
• OS: Any
• Assigned to: Mattias Jonsson
• Target Version: 5.1+
• Triage: Triaged: D2 (Serious)

[5 Nov 2008 1: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 16: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 10: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 10:46] Mattias Jonsson

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

[15 Jan 7: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 17: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 19: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 12: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 14: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 14:57] Jon Stephens

Modified changelog entry per developer comment.

Set status to NDI pending merge to 6.0.

[19 Jan 17: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 18:04] Jon Stephens

Set back to NDI pending merge to 6.0.

[20 Jan 19: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 22:14] Jon Stephens

Fix also noted in 6.0.10 changelog; closed.