Bug#40176: update as first partitioning statement

breaks auto increment

The auto_increment value was not initialized if
the first statement after opening a table was
an 'UPDATE'.

solution was to check initialize if it was not,
before trying to increase it in update.

mysql-test/suite/parts/inc/partition_auto_increment.inc:
  Bug#40176: update as first partitioning statement
  breaks auto increment
  
  Added tests for verifying the bug and show some more
  auto_increment flaws
mysql-test/suite/parts/r/partition_auto_increment_archive.result:
  Bug#40176: update as first partitioning statement
  breaks auto increment
  
  Updated test results, due to added tests
mysql-test/suite/parts/r/partition_auto_increment_blackhole.result:
  Bug#40176: update as first partitioning statement
  breaks auto increment
  
  Updated test results, due to added tests
mysql-test/suite/parts/r/partition_auto_increment_innodb.result:
  Bug#40176: update as first partitioning statement
  breaks auto increment
  
  Updated test results, due to added tests
mysql-test/suite/parts/r/partition_auto_increment_memory.result:
  Bug#40176: update as first partitioning statement
  breaks auto increment
  
  Updated test results, due to added tests
mysql-test/suite/parts/r/partition_auto_increment_myisam.result:
  Bug#40176: update as first partitioning statement
  breaks auto increment
  
  Updated test results, due to added tests
mysql-test/suite/parts/r/partition_auto_increment_ndb.result:
  Bug#40176: update as first partitioning statement
  breaks auto increment
  
  Updated test results, due to added tests
sql/ha_partition.cc:
  Bug#40176: update as first partitioning statement
  breaks auto increment
  
  make sure that the auto_increment value is initialized
  before updating it.
  
  (missed initializing in mysql_update_row).
sql/ha_partition.h:
  Bug#40176: update as first partitioning statement
  breaks auto increment
  
  Assert that it is initialized, before updating
  the auto_increment value

1 files changed, 28 insertions, 0 deletions

diff --git a/mysql-test/suite/parts/inc/partition_auto_increment.inc b/mysql-test/suite/parts/inc/partition_auto_increment.inc
index 341bf7ab860..5e9ad47aa2e 100644
--- a/mysql-test/suite/parts/inc/partition_auto_increment.inc
+++ b/mysql-test/suite/parts/inc/partition_auto_increment.inc
@@ -43,8 +43,13 @@ SET INSERT_ID = 30;
 INSERT INTO t1 VALUES (NULL);
 if (!$skip_update)
 {
+# InnoDB Does not handle this correctly, see bug#14793, bug#21641
   UPDATE t1 SET c1 = 50 WHERE c1 = 17;
   UPDATE t1 SET c1 = 51 WHERE c1 = 19;
+  FLUSH TABLES;
+  UPDATE t1 SET c1 = 40 WHERE c1 = 50;
+  SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+  AND TABLE_NAME='t1';
   -- error 0, ER_BAD_NULL_ERROR
   UPDATE t1 SET c1 = NULL WHERE c1 = 4;
 if (!$mysql_errno)
@@ -83,6 +88,16 @@ TRUNCATE TABLE t1;
 INSERT INTO t1 VALUES (NULL);
 SHOW CREATE TABLE t1;
 SELECT * FROM t1 ORDER BY c1;
+INSERT INTO t1 VALUES (100);
+INSERT INTO t1 VALUES (NULL);
+if (!$skip_delete)
+{
+DELETE FROM t1 WHERE c1 >= 100;
+}
+# InnoDB does reset auto_increment on OPTIMIZE, Bug#18274
+# Archive does reset auto_increment on OPTIMIZE, Bug#40216
+OPTIMIZE TABLE t1;
+SHOW CREATE TABLE t1;
 DROP TABLE t1;
 
 -- echo # Simple test with NULL
@@ -184,6 +199,10 @@ if (!$skip_update)
 {
   UPDATE t1 SET c1 = 150 WHERE c1 = 17;
   UPDATE t1 SET c1 = 151 WHERE c1 = 19;
+  FLUSH TABLES;
+  UPDATE t1 SET c1 = 140 WHERE c1 = 150;
+  SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+  AND TABLE_NAME='t1';
   -- error 0, ER_BAD_NULL_ERROR
   UPDATE t1 SET c1 = NULL WHERE c1 = 4;
 if (!$mysql_errno)
@@ -295,6 +314,15 @@ TRUNCATE TABLE t1;
 INSERT INTO t1 VALUES (NULL);
 SHOW CREATE TABLE t1;
 SELECT * FROM t1 ORDER BY c1;
+INSERT INTO t1 VALUES (100);
+INSERT INTO t1 VALUES (NULL);
+if (!$skip_delete)
+{
+DELETE FROM t1 WHERE c1 >= 100;
+}
+# InnoDB does reset auto_increment on OPTIMIZE, Bug#18274
+OPTIMIZE TABLE t1;
+SHOW CREATE TABLE t1;
 DROP TABLE t1;
 
 -- echo # Test with two threads