#At file:///Users/mattiasj/mysql-bzr/b57890-51-bt/ based on revid:georgi.kodinov@stripped
3553 Mattias Jonsson 2010-11-11
Bug#57890: Assertion failed: next_insert_id == 0
with on duplicate key update
There was a missed corner case in the partitioning
handler, which caused the next_insert_id to be changed
in the second level handlers (i.e the hander of a partition),
which caused this debug assertion.
The solution was to always ensure that only the partitioning
level generates auto_increment values, since if it was done
within a partition, it may fail to match the partition
function.
@ mysql-test/suite/parts/inc/partition_auto_increment.inc
Added tests
@ mysql-test/suite/parts/r/partition_auto_increment_blackhole.result
updated results
@ mysql-test/suite/parts/r/partition_auto_increment_innodb.result
updated results
@ mysql-test/suite/parts/r/partition_auto_increment_memory.result
updated results
@ mysql-test/suite/parts/r/partition_auto_increment_myisam.result
updated results
@ sql/ha_partition.cc
In <engine>::write_row the auto_inc value is generated
through handler::update_auto_increment (which calls <engine>::get_auto_increment() if needed).
If:
* INSERT_ID was set to 0
* it was updated to 0 by 'INSERT ... ON DUPLICATE KEY UPDATE' and changed partitions for the row
Then it would try to generate a auto_increment value in the
<engine for a specific partition>::write_row, which will
trigger the assert.
So the solution is to prevent this by,
in ha_partition::write_row set auto_inc_field_not_null and
add MODE_NO_AUTO_VALUE_ON_ZERO
in ha_partition::update_row (when changing partition) temporary
set table->next_number_field to NULL which calling the
partitions ::write_row().
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
sql/ha_partition.cc
=== modified file 'mysql-test/suite/parts/inc/partition_auto_increment.inc'
--- a/mysql-test/suite/parts/inc/partition_auto_increment.inc 2010-03-04 17:16:10 +0000
+++ b/mysql-test/suite/parts/inc/partition_auto_increment.inc 2010-11-11 10:34:55 +0000
@@ -105,6 +105,30 @@ OPTIMIZE TABLE t1;
SHOW CREATE TABLE t1;
DROP TABLE t1;
+if (!$skip_update)
+{
+eval CREATE TABLE t1
+(a INT NULL AUTO_INCREMENT,
+ UNIQUE KEY (a))
+ENGINE=$engine;
+SET LAST_INSERT_ID = 999;
+SET INSERT_ID = 0;
+INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+SELECT LAST_INSERT_ID();
+SELECT * FROM t1;
+INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+SELECT LAST_INSERT_ID();
+SELECT * FROM t1;
+UPDATE t1 SET a = 1 WHERE a IS NULL;
+SELECT LAST_INSERT_ID();
+SELECT * FROM t1;
+UPDATE t1 SET a = NULL WHERE a = 1;
+SELECT LAST_INSERT_ID();
+SELECT * FROM t1;
+DROP TABLE t1;
+SET INSERT_ID = 1;
+}
+
-- echo # Simple test with NULL
eval CREATE TABLE t1 (
c1 INT NOT NULL AUTO_INCREMENT,
@@ -831,5 +855,30 @@ SELECT * FROM t ORDER BY c1 ASC;
DROP TABLE t;
+if (!$skip_update)
+{
+eval CREATE TABLE t1
+(a INT NULL AUTO_INCREMENT,
+ UNIQUE KEY (a))
+ENGINE=$engine
+PARTITION BY KEY(a) PARTITIONS 2;
+SET LAST_INSERT_ID = 999;
+SET INSERT_ID = 0;
+INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+SELECT LAST_INSERT_ID();
+SELECT * FROM t1;
+INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+SELECT LAST_INSERT_ID();
+SELECT * FROM t1;
+UPDATE t1 SET a = 1 WHERE a IS NULL;
+SELECT LAST_INSERT_ID();
+SELECT * FROM t1;
+UPDATE t1 SET a = NULL WHERE a = 1;
+SELECT LAST_INSERT_ID();
+SELECT * FROM t1;
+DROP TABLE t1;
+}
+
+
--echo ##############################################################################
}
=== modified file 'mysql-test/suite/parts/r/partition_auto_increment_blackhole.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_blackhole.result 2010-03-04 17:16:10 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_blackhole.result 2010-11-11 10:34:55 +0000
@@ -120,6 +120,38 @@ t1 CREATE TABLE `t1` (
PRIMARY KEY (`c1`)
) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
DROP TABLE t1;
+CREATE TABLE t1
+(a INT NULL AUTO_INCREMENT,
+UNIQUE KEY (a))
+ENGINE='Blackhole';
+SET LAST_INSERT_ID = 999;
+SET INSERT_ID = 0;
+INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+999
+SELECT * FROM t1;
+a
+INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+999
+SELECT * FROM t1;
+a
+UPDATE t1 SET a = 1 WHERE a IS NULL;
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+999
+SELECT * FROM t1;
+a
+UPDATE t1 SET a = NULL WHERE a = 1;
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+999
+SELECT * FROM t1;
+a
+DROP TABLE t1;
+SET INSERT_ID = 1;
# Simple test with NULL
CREATE TABLE t1 (
c1 INT NOT NULL AUTO_INCREMENT,
=== modified file 'mysql-test/suite/parts/r/partition_auto_increment_innodb.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_innodb.result 2010-03-04 17:16:10 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_innodb.result 2010-11-11 10:34:55 +0000
@@ -136,6 +136,42 @@ t1 CREATE TABLE `t1` (
PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
DROP TABLE t1;
+CREATE TABLE t1
+(a INT NULL AUTO_INCREMENT,
+UNIQUE KEY (a))
+ENGINE='InnoDB';
+SET LAST_INSERT_ID = 999;
+SET INSERT_ID = 0;
+INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+999
+SELECT * FROM t1;
+a
+1
+INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+999
+SELECT * FROM t1;
+a
+0
+UPDATE t1 SET a = 1 WHERE a IS NULL;
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+999
+SELECT * FROM t1;
+a
+0
+UPDATE t1 SET a = NULL WHERE a = 1;
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+999
+SELECT * FROM t1;
+a
+0
+DROP TABLE t1;
+SET INSERT_ID = 1;
# Simple test with NULL
CREATE TABLE t1 (
c1 INT NOT NULL AUTO_INCREMENT,
@@ -1023,4 +1059,40 @@ c1 c2
2 20
127 40
DROP TABLE t;
+CREATE TABLE t1
+(a INT NULL AUTO_INCREMENT,
+UNIQUE KEY (a))
+ENGINE='InnoDB'
+PARTITION BY KEY(a) PARTITIONS 2;
+SET LAST_INSERT_ID = 999;
+SET INSERT_ID = 0;
+INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+999
+SELECT * FROM t1;
+a
+1
+INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+999
+SELECT * FROM t1;
+a
+0
+UPDATE t1 SET a = 1 WHERE a IS NULL;
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+999
+SELECT * FROM t1;
+a
+0
+UPDATE t1 SET a = NULL WHERE a = 1;
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+999
+SELECT * FROM t1;
+a
+0
+DROP 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 2010-03-04 17:16:10 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_memory.result 2010-11-11 10:34:55 +0000
@@ -136,6 +136,42 @@ t1 CREATE TABLE `t1` (
PRIMARY KEY (`c1`)
) ENGINE=MEMORY AUTO_INCREMENT=102 DEFAULT CHARSET=latin1
DROP TABLE t1;
+CREATE TABLE t1
+(a INT NULL AUTO_INCREMENT,
+UNIQUE KEY (a))
+ENGINE='Memory';
+SET LAST_INSERT_ID = 999;
+SET INSERT_ID = 0;
+INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+999
+SELECT * FROM t1;
+a
+1
+INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+999
+SELECT * FROM t1;
+a
+0
+UPDATE t1 SET a = 1 WHERE a IS NULL;
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+999
+SELECT * FROM t1;
+a
+0
+UPDATE t1 SET a = NULL WHERE a = 1;
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+999
+SELECT * FROM t1;
+a
+0
+DROP TABLE t1;
+SET INSERT_ID = 1;
# Simple test with NULL
CREATE TABLE t1 (
c1 INT NOT NULL AUTO_INCREMENT,
@@ -1051,4 +1087,40 @@ c1 c2
2 20
127 40
DROP TABLE t;
+CREATE TABLE t1
+(a INT NULL AUTO_INCREMENT,
+UNIQUE KEY (a))
+ENGINE='Memory'
+PARTITION BY KEY(a) PARTITIONS 2;
+SET LAST_INSERT_ID = 999;
+SET INSERT_ID = 0;
+INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+999
+SELECT * FROM t1;
+a
+1
+INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+999
+SELECT * FROM t1;
+a
+0
+UPDATE t1 SET a = 1 WHERE a IS NULL;
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+999
+SELECT * FROM t1;
+a
+0
+UPDATE t1 SET a = NULL WHERE a = 1;
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+999
+SELECT * FROM t1;
+a
+0
+DROP 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 2010-03-04 17:16:10 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_myisam.result 2010-11-11 10:34:55 +0000
@@ -136,6 +136,42 @@ t1 CREATE TABLE `t1` (
PRIMARY KEY (`c1`)
) ENGINE=MyISAM AUTO_INCREMENT=102 DEFAULT CHARSET=latin1
DROP TABLE t1;
+CREATE TABLE t1
+(a INT NULL AUTO_INCREMENT,
+UNIQUE KEY (a))
+ENGINE='MyISAM';
+SET LAST_INSERT_ID = 999;
+SET INSERT_ID = 0;
+INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+999
+SELECT * FROM t1;
+a
+1
+INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+999
+SELECT * FROM t1;
+a
+0
+UPDATE t1 SET a = 1 WHERE a IS NULL;
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+999
+SELECT * FROM t1;
+a
+0
+UPDATE t1 SET a = NULL WHERE a = 1;
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+999
+SELECT * FROM t1;
+a
+0
+DROP TABLE t1;
+SET INSERT_ID = 1;
# Simple test with NULL
CREATE TABLE t1 (
c1 INT NOT NULL AUTO_INCREMENT,
@@ -1070,4 +1106,40 @@ c1 c2
2 20
127 40
DROP TABLE t;
+CREATE TABLE t1
+(a INT NULL AUTO_INCREMENT,
+UNIQUE KEY (a))
+ENGINE='MyISAM'
+PARTITION BY KEY(a) PARTITIONS 2;
+SET LAST_INSERT_ID = 999;
+SET INSERT_ID = 0;
+INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+999
+SELECT * FROM t1;
+a
+1
+INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL;
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+999
+SELECT * FROM t1;
+a
+0
+UPDATE t1 SET a = 1 WHERE a IS NULL;
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+999
+SELECT * FROM t1;
+a
+0
+UPDATE t1 SET a = NULL WHERE a = 1;
+SELECT LAST_INSERT_ID();
+LAST_INSERT_ID()
+999
+SELECT * FROM t1;
+a
+0
+DROP TABLE t1;
##############################################################################
=== modified file 'sql/ha_partition.cc'
--- a/sql/ha_partition.cc 2010-10-01 13:41:27 +0000
+++ b/sql/ha_partition.cc 2010-11-11 10:34:55 +0000
@@ -3050,7 +3050,9 @@ int ha_partition::write_row(uchar * buf)
my_bitmap_map *old_map;
HA_DATA_PARTITION *ha_data= (HA_DATA_PARTITION*) table_share->ha_data;
THD *thd= ha_thd();
- timestamp_auto_set_type orig_timestamp_type= table->timestamp_field_type;
+ timestamp_auto_set_type saved_timestamp_type= table->timestamp_field_type;
+ ulong saved_sql_mode= thd->variables.sql_mode;
+ bool saved_auto_inc_field_not_null= table->auto_increment_field_not_null;
#ifdef NOT_NEEDED
uchar *rec0= m_rec0;
#endif
@@ -3086,6 +3088,22 @@ int ha_partition::write_row(uchar * buf)
*/
if (error)
goto exit;
+
+ /*
+ Don't allow generation of auto_increment value the partitions handler.
+ If a partitions handler would change the value, then it might not
+ match the partition any longer.
+ This can occur if 'SET INSERT_ID = 0; INSERT (NULL)',
+ So allow this by adding 'MODE_NO_AUTO_VALUE_ON_ZERO' to sql_mode.
+ The partitions handler::next_insert_id must always be 0. Otherwise
+ we need to forward release_auto_increment, or reset it for all
+ partitions.
+ */
+ if (table->next_number_field->val_int() == 0)
+ {
+ table->auto_increment_field_not_null= TRUE;
+ thd->variables.sql_mode|= MODE_NO_AUTO_VALUE_ON_ZERO;
+ }
}
old_map= dbug_tmp_use_all_columns(table, table->read_set);
@@ -3119,7 +3137,9 @@ int ha_partition::write_row(uchar * buf)
set_auto_increment_if_higher(table->next_number_field);
reenable_binlog(thd);
exit:
- table->timestamp_field_type= orig_timestamp_type;
+ thd->variables.sql_mode= saved_sql_mode;
+ table->auto_increment_field_not_null= saved_auto_inc_field_not_null;
+ table->timestamp_field_type= saved_timestamp_type;
DBUG_RETURN(error);
}
@@ -3186,11 +3206,24 @@ int ha_partition::update_row(const uchar
}
else
{
+ Field *saved_next_number_field= table->next_number_field;
+ /*
+ Don't allow generation of auto_increment value for update.
+ table->next_number_field is never set on UPDATE.
+ But is set for INSERT ... ON DUPLICATE KEY UPDATE,
+ and since update_row() does not generate or update an auto_inc value,
+ we cannot have next_number_field set when moving a row
+ to another partition with write_row(), since that could
+ generate/update the auto_inc value.
+ This gives the same behavior for partitioned vs non partitioned tables.
+ */
+ table->next_number_field= NULL;
DBUG_PRINT("info", ("Update from partition %d to partition %d",
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);
reenable_binlog(thd);
+ table->next_number_field= saved_next_number_field;
if (error)
goto exit;
Attachment: [text/bzr-bundle] bzr/mattias.jonsson@oracle.com-20101111103455-hdepxtdkao7yzn8s.bundle
| Thread |
|---|
| • bzr commit into mysql-5.1-bugteam branch (mattias.jonsson:3553) Bug#57890 | Mattias Jonsson | 11 Nov |
