bzr commit into mysql-5.1 branch (mattias.jonsson:2733) Bug#38804

[Mattias Jonsson]

#At file:///Users/mattiasj/clones/bzrroot/b33479-51-bugteam/

 2733 Mattias Jonsson	2008-09-03
      Bug#38804: Query deadlock causes all tables to be inaccessible.
      Bug-33479: auto_increment failures in partitioning
      
      fix for update of auto_increment column
      
      (Same bug exists in non partitioned InnoDB, bug-39207)
modified:
  mysql-test/suite/parts/inc/partition_auto_increment.inc
  mysql-test/suite/parts/r/partition_auto_increment_blackhole.result
  mysql-test/suite/parts/r/partition_auto_increment_innodb.result
  mysql-test/suite/parts/r/partition_auto_increment_memory.result
  mysql-test/suite/parts/r/partition_auto_increment_myisam.result
  mysql-test/suite/parts/r/partition_auto_increment_ndb.result
  mysql-test/suite/parts/t/partition_auto_increment_archive.test
  sql/ha_partition.cc
  sql/ha_partition.h

per-file messages:
  mysql-test/suite/parts/inc/partition_auto_increment.inc
    Bug#38804: Query deadlock causes all tables to be inaccessible.
    Bug-33479: auto_increment failures in partitioning
    
    Added tests for update.
  mysql-test/suite/parts/r/partition_auto_increment_blackhole.result
    Bug#38804: Query deadlock causes all tables to be inaccessible.
    Bug-33479: auto_increment failures in partitioning
    
    Updated results
  mysql-test/suite/parts/r/partition_auto_increment_innodb.result
    Bug#38804: Query deadlock causes all tables to be inaccessible.
    Bug-33479: auto_increment failures in partitioning
    
    Updated results
    NOTE: strange behavior of non partitioned table, reported as
    bug-39207
  mysql-test/suite/parts/r/partition_auto_increment_memory.result
    Bug#38804: Query deadlock causes all tables to be inaccessible.
    Bug-33479: auto_increment failures in partitioning
    
    Updated results
  mysql-test/suite/parts/r/partition_auto_increment_myisam.result
    Bug#38804: Query deadlock causes all tables to be inaccessible.
    Bug-33479: auto_increment failures in partitioning
    
    Updated results
  mysql-test/suite/parts/r/partition_auto_increment_ndb.result
    Bug#38804: Query deadlock causes all tables to be inaccessible.
    Bug-33479: auto_increment failures in partitioning
    
    Updated results
  mysql-test/suite/parts/t/partition_auto_increment_archive.test
    Bug#38804: Query deadlock causes all tables to be inaccessible.
    Bug-33479: auto_increment failures in partitioning
    
    Added $skip_update, since archive does not support update
  sql/ha_partition.cc
    Bug#38804: Query deadlock causes all tables to be inaccessible.
    Bug-33479: auto_increment failures in partitioning
    
    Removed comment from ha_example.cc, since it is not correct anymore
    Using found_next_number_field instead of next_number_field, which
    is not set in mysql_update (used in this way in ha_ndbcluster.cc)
  sql/ha_partition.h
    Bug#38804: Query deadlock causes all tables to be inaccessible.
    Bug-33479: auto_increment failures in partitioning
    
    Using found_next_number_field if next_number_field is not set,
    (not set in mysql_update)
=== modified file 'mysql-test/suite/parts/inc/partition_auto_increment.inc'
--- a/mysql-test/suite/parts/inc/partition_auto_increment.inc	2008-09-02 13:28:23 +0000
+++ b/mysql-test/suite/parts/inc/partition_auto_increment.inc	2008-09-03 14:48:21 +0000
@@ -41,6 +41,19 @@ if ($mysql_errno)
 INSERT INTO t1 VALUES (NULL);
 SET INSERT_ID = 30;
 INSERT INTO t1 VALUES (NULL);
+if (!$skip_update)
+{
+  UPDATE t1 SET c1 = 50 WHERE c1 = 17;
+  UPDATE t1 SET c1 = 51 WHERE c1 = 19;
+  -- error 0, ER_BAD_NULL_ERROR
+  UPDATE t1 SET c1 = NULL WHERE c1 = 4;
+if (!$mysql_errno)
+{
+  echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
+}
+  INSERT INTO t1 VALUES (NULL);
+  INSERT INTO t1 VALUES (NULL);
+}
 SELECT * FROM t1 ORDER BY c1;
 DROP TABLE t1;
 eval CREATE TABLE t1 (
@@ -167,6 +180,19 @@ if ($mysql_errno)
 }
 INSERT INTO t1 VALUES (NULL), (90);
 INSERT INTO t1 VALUES (NULL);
+if (!$skip_update)
+{
+  UPDATE t1 SET c1 = 150 WHERE c1 = 17;
+  UPDATE t1 SET c1 = 151 WHERE c1 = 19;
+  -- error 0, ER_BAD_NULL_ERROR
+  UPDATE t1 SET c1 = NULL WHERE c1 = 4;
+if (!$mysql_errno)
+{
+  echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
+}
+  INSERT INTO t1 VALUES (NULL);
+  INSERT INTO t1 VALUES (NULL);
+}
 SELECT * FROM t1 ORDER BY c1;
 DROP TABLE t1;
 

=== modified file 'mysql-test/suite/parts/r/partition_auto_increment_blackhole.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_blackhole.result	2008-09-02
13:28:23 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_blackhole.result	2008-09-03
14:48:21 +0000
@@ -34,6 +34,12 @@ INSERT INTO t1 VALUES (NULL), (10), (NUL
 INSERT INTO t1 VALUES (NULL);
 SET INSERT_ID = 30;
 INSERT INTO t1 VALUES (NULL);
+UPDATE t1 SET c1 = 50 WHERE c1 = 17;
+UPDATE t1 SET c1 = 51 WHERE c1 = 19;
+UPDATE t1 SET c1 = NULL WHERE c1 = 4;
+# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
 SELECT * FROM t1 ORDER BY c1;
 c1
 DROP TABLE t1;
@@ -157,6 +163,12 @@ INSERT INTO t1 VALUES (NULL), (9);
 INSERT INTO t1 VALUES (59), (55);
 INSERT INTO t1 VALUES (NULL), (90);
 INSERT INTO t1 VALUES (NULL);
+UPDATE t1 SET c1 = 150 WHERE c1 = 17;
+UPDATE t1 SET c1 = 151 WHERE c1 = 19;
+UPDATE t1 SET c1 = NULL WHERE c1 = 4;
+# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
 SELECT * FROM t1 ORDER BY c1;
 c1
 DROP TABLE t1;

=== modified file 'mysql-test/suite/parts/r/partition_auto_increment_innodb.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_innodb.result	2008-09-02 13:28:23
+0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_innodb.result	2008-09-03 14:48:21
+0000
@@ -33,6 +33,11 @@ INSERT INTO t1 VALUES (NULL), (10), (NUL
 INSERT INTO t1 VALUES (NULL);
 SET INSERT_ID = 30;
 INSERT INTO t1 VALUES (NULL);
+UPDATE t1 SET c1 = 50 WHERE c1 = 17;
+UPDATE t1 SET c1 = 51 WHERE c1 = 19;
+UPDATE t1 SET c1 = NULL WHERE c1 = 4;
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
 SELECT * FROM t1 ORDER BY c1;
 c1
 2
@@ -40,13 +45,15 @@ c1
 5
 6
 10
-17
-19
 20
 22
 23
 25
 30
+31
+32
+50
+51
 DROP TABLE t1;
 CREATE TABLE t1 (
 c1 INT NOT NULL AUTO_INCREMENT,
@@ -178,6 +185,11 @@ INSERT INTO t1 VALUES (NULL), (9);
 INSERT INTO t1 VALUES (59), (55);
 INSERT INTO t1 VALUES (NULL), (90);
 INSERT INTO t1 VALUES (NULL);
+UPDATE t1 SET c1 = 150 WHERE c1 = 17;
+UPDATE t1 SET c1 = 151 WHERE c1 = 19;
+UPDATE t1 SET c1 = NULL WHERE c1 = 4;
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
 SELECT * FROM t1 ORDER BY c1;
 c1
 2
@@ -186,8 +198,6 @@ c1
 6
 9
 10
-17
-19
 20
 21
 22
@@ -197,6 +207,10 @@ c1
 60
 90
 91
+150
+151
+152
+153
 DROP TABLE t1;
 # Test with auto_increment_increment and auto_increment_offset.
 CREATE TABLE t1 (

=== modified file 'mysql-test/suite/parts/r/partition_auto_increment_memory.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_memory.result	2008-09-02 13:28:23
+0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_memory.result	2008-09-03 14:48:21
+0000
@@ -33,6 +33,11 @@ INSERT INTO t1 VALUES (NULL), (10), (NUL
 INSERT INTO t1 VALUES (NULL);
 SET INSERT_ID = 30;
 INSERT INTO t1 VALUES (NULL);
+UPDATE t1 SET c1 = 50 WHERE c1 = 17;
+UPDATE t1 SET c1 = 51 WHERE c1 = 19;
+UPDATE t1 SET c1 = NULL WHERE c1 = 4;
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
 SELECT * FROM t1 ORDER BY c1;
 c1
 2
@@ -40,13 +45,15 @@ c1
 5
 6
 10
-17
-19
 20
 21
 22
 23
 30
+50
+51
+52
+53
 DROP TABLE t1;
 CREATE TABLE t1 (
 c1 INT NOT NULL AUTO_INCREMENT,
@@ -178,6 +185,11 @@ INSERT INTO t1 VALUES (NULL), (9);
 INSERT INTO t1 VALUES (59), (55);
 INSERT INTO t1 VALUES (NULL), (90);
 INSERT INTO t1 VALUES (NULL);
+UPDATE t1 SET c1 = 150 WHERE c1 = 17;
+UPDATE t1 SET c1 = 151 WHERE c1 = 19;
+UPDATE t1 SET c1 = NULL WHERE c1 = 4;
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
 SELECT * FROM t1 ORDER BY c1;
 c1
 2
@@ -186,8 +198,6 @@ c1
 6
 9
 10
-17
-19
 20
 21
 22
@@ -197,6 +207,10 @@ c1
 60
 90
 91
+150
+151
+152
+153
 DROP TABLE t1;
 # Test with auto_increment_increment and auto_increment_offset.
 CREATE TABLE t1 (

=== modified file 'mysql-test/suite/parts/r/partition_auto_increment_myisam.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_myisam.result	2008-09-02 13:28:23
+0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_myisam.result	2008-09-03 14:48:21
+0000
@@ -33,6 +33,11 @@ INSERT INTO t1 VALUES (NULL), (10), (NUL
 INSERT INTO t1 VALUES (NULL);
 SET INSERT_ID = 30;
 INSERT INTO t1 VALUES (NULL);
+UPDATE t1 SET c1 = 50 WHERE c1 = 17;
+UPDATE t1 SET c1 = 51 WHERE c1 = 19;
+UPDATE t1 SET c1 = NULL WHERE c1 = 4;
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
 SELECT * FROM t1 ORDER BY c1;
 c1
 2
@@ -40,13 +45,15 @@ c1
 5
 6
 10
-17
-19
 20
 21
 22
 23
 30
+50
+51
+52
+53
 DROP TABLE t1;
 CREATE TABLE t1 (
 c1 INT NOT NULL AUTO_INCREMENT,
@@ -178,6 +185,11 @@ INSERT INTO t1 VALUES (NULL), (9);
 INSERT INTO t1 VALUES (59), (55);
 INSERT INTO t1 VALUES (NULL), (90);
 INSERT INTO t1 VALUES (NULL);
+UPDATE t1 SET c1 = 150 WHERE c1 = 17;
+UPDATE t1 SET c1 = 151 WHERE c1 = 19;
+UPDATE t1 SET c1 = NULL WHERE c1 = 4;
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
 SELECT * FROM t1 ORDER BY c1;
 c1
 2
@@ -186,8 +198,6 @@ c1
 6
 9
 10
-17
-19
 20
 21
 22
@@ -197,6 +207,10 @@ c1
 60
 90
 91
+150
+151
+152
+153
 DROP TABLE t1;
 # Test with auto_increment_increment and auto_increment_offset.
 CREATE TABLE t1 (

=== modified file 'mysql-test/suite/parts/r/partition_auto_increment_ndb.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_ndb.result	2008-09-02 13:28:23
+0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_ndb.result	2008-09-03 14:48:21
+0000
@@ -34,6 +34,11 @@ INSERT INTO t1 VALUES (NULL), (10), (NUL
 INSERT INTO t1 VALUES (NULL);
 SET INSERT_ID = 30;
 INSERT INTO t1 VALUES (NULL);
+UPDATE t1 SET c1 = 50 WHERE c1 = 17;
+UPDATE t1 SET c1 = 51 WHERE c1 = 19;
+UPDATE t1 SET c1 = NULL WHERE c1 = 4;
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
 SELECT * FROM t1 ORDER BY c1;
 c1
 2
@@ -41,13 +46,15 @@ c1
 5
 6
 10
-17
-19
 20
 21
 22
 23
 30
+50
+51
+52
+53
 DROP TABLE t1;
 CREATE TABLE t1 (
 c1 INT NOT NULL AUTO_INCREMENT,
@@ -179,6 +186,11 @@ INSERT INTO t1 VALUES (NULL), (9);
 INSERT INTO t1 VALUES (59), (55);
 INSERT INTO t1 VALUES (NULL), (90);
 INSERT INTO t1 VALUES (NULL);
+UPDATE t1 SET c1 = 150 WHERE c1 = 17;
+UPDATE t1 SET c1 = 151 WHERE c1 = 19;
+UPDATE t1 SET c1 = NULL WHERE c1 = 4;
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
 SELECT * FROM t1 ORDER BY c1;
 c1
 2
@@ -187,8 +199,6 @@ c1
 6
 9
 10
-17
-19
 20
 21
 22
@@ -198,6 +208,10 @@ c1
 60
 90
 91
+150
+151
+152
+153
 DROP TABLE t1;
 # Test with auto_increment_increment and auto_increment_offset.
 CREATE TABLE t1 (

=== modified file 'mysql-test/suite/parts/t/partition_auto_increment_archive.test'
--- a/mysql-test/suite/parts/t/partition_auto_increment_archive.test	2008-09-02 13:28:23
+0000
+++ b/mysql-test/suite/parts/t/partition_auto_increment_archive.test	2008-09-03 14:48:21
+0000
@@ -28,6 +28,7 @@
 # Archve does not support delete
 let $skip_delete= 1;
 let $skip_truncate= 1;
+let $skip_update= 1;
 let $only_ai_pk= 1;
 
 ##### Storage engine to be tested

=== modified file 'sql/ha_partition.cc'
--- a/sql/ha_partition.cc	2008-09-02 13:28:23 +0000
+++ b/sql/ha_partition.cc	2008-09-03 14:48:21 +0000
@@ -2947,13 +2947,6 @@ exit:
     Keep in mind that the server can do updates based on ordering if an
     ORDER BY clause was used. Consecutive ordering is not guarenteed.
 
-    Currently new_data will not have an updated auto_increament record, or
-    and updated timestamp field. You can do these for partition by doing these:
-    if (table->timestamp_field_type & TIMESTAMP_AUTO_SET_ON_UPDATE)
-      table->timestamp_field->set_time();
-    if (table->next_number_field && record == table->record[0])
-      update_auto_increment();
-
     Called from sql_select.cc, sql_acl.cc, sql_update.cc, and sql_insert.cc.
     new_data is always record[0]
     old_data is normally record[1] but may be anything
@@ -2996,8 +2989,10 @@ int ha_partition::update_row(const uchar
       table_share->ha_data->next_auto_inc_val if needed.
       (not to be used if auto_increment on secondary field in a multi-
       column index)
+      mysql_update does not set table->next_number_field, so we use
+      table->found_next_number_field instead.
     */
-    if (table->next_number_field && new_data == table->record[0] &&
+    if (table->found_next_number_field && new_data == table->record[0]
&&
         !table->s->next_number_keypart)
       set_auto_increment_if_higher();
     reenable_binlog(thd);
@@ -3009,7 +3004,7 @@ int ha_partition::update_row(const uchar
 			old_part_id, new_part_id));
     tmp_disable_binlog(thd); /* Do not replicate the low-level changes. */
     error= m_file[new_part_id]->ha_write_row(new_data);
-    if (table->next_number_field && new_data == table->record[0] &&
+    if (table->found_next_number_field && new_data == table->record[0]
&&
         !table->s->next_number_keypart)
       set_auto_increment_if_higher();
     reenable_binlog(thd);

=== modified file 'sql/ha_partition.h'
--- a/sql/ha_partition.h	2008-09-02 13:28:23 +0000
+++ b/sql/ha_partition.h	2008-09-03 14:48:21 +0000
@@ -879,7 +879,15 @@ private:
   }
   virtual void set_auto_increment_if_higher()
   {
-    ulonglong nr= table->next_number_field->val_int();
+    ulonglong nr;
+    /*
+      mysql_update does not set table->next_number_field, so we use
+      table->found_next_number_field.
+    */
+    if (table->next_number_field)
+      nr= table->next_number_field->val_int();
+    else
+      nr= table->found_next_number_field->val_int();
     HA_DATA_PARTITION *ha_data= (HA_DATA_PARTITION*) table_share->ha_data;
     lock_auto_increment();
     /* must check when the mutex is taken */