Hi,
Thank you for reviewing my tests!
My comments are below yours. (the result differences are explained last.)
Regards
Mattias
Guilhem Bichot wrote:
> Hello,
>
> I marked my comments with GB.
>
> On Thu, Mar 06, 2008 at 10:36:51AM +0100, mattiasj@stripped wrote:
>> Below is the list of changes that have just been committed into a local
>> 5.1 repository of mattiasj. When mattiasj does a push these changes
>> will be propagated to the main repository and, within 24 hours after the
>> push, to the public repository.
>> For information on how to access the public repository
>> see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
>>
>> ChangeSet@stripped, 2008-03-06 10:36:45+01:00, mattiasj@stripped +5 -0
>> Bug#33479: auto_increment failures in partitioning
>>
>> This is the updated 5.1+ tests
>>
>> mysql-test/suite/parts/inc/partition_auto_increment.inc@stripped, 2008-03-06
> 10:36:43+01:00, mattiasj@stripped +468 -0
>> Bug#33479: auto_increment failures in partitioning
>>
>> Test source file for testing auto_increment
>>
>> mysql-test/suite/parts/inc/partition_auto_increment.inc@stripped, 2008-03-06
> 10:36:43+01:00, mattiasj@stripped +0 -0
>>
>> mysql-test/suite/parts/r/partition_auto_increment_innodb.result@stripped, 2008-03-06
> 10:36:43+01:00, mattiasj@stripped +736 -0
>> Bug#33479: auto_increment failures in partitioning
>>
>> test result
>>
>> mysql-test/suite/parts/r/partition_auto_increment_innodb.result@stripped, 2008-03-06
> 10:36:43+01:00, mattiasj@stripped +0 -0
>>
>> mysql-test/suite/parts/r/partition_auto_increment_myisam.result@stripped, 2008-03-06
> 10:36:43+01:00, mattiasj@stripped +783 -0
>> Bug#33479: auto_increment failures in partitioning
>>
>> test result
>>
>> mysql-test/suite/parts/r/partition_auto_increment_myisam.result@stripped, 2008-03-06
> 10:36:43+01:00, mattiasj@stripped +0 -0
>>
>> mysql-test/suite/parts/t/partition_auto_increment_innodb.test@stripped, 2008-03-06
> 10:36:43+01:00, mattiasj@stripped +35 -0
>> Bug#33479: auto_increment failures in partitioning
>>
>> test file
>>
>> mysql-test/suite/parts/t/partition_auto_increment_innodb.test@stripped, 2008-03-06
> 10:36:43+01:00, mattiasj@stripped +0 -0
>>
>> mysql-test/suite/parts/t/partition_auto_increment_myisam.test@stripped, 2008-03-06
> 10:36:44+01:00, mattiasj@stripped +34 -0
>> Bug#33479: auto_increment failures in partitioning
>>
>> test file
>>
>> mysql-test/suite/parts/t/partition_auto_increment_myisam.test@stripped, 2008-03-06
> 10:36:44+01:00, mattiasj@stripped +0 -0
>>
>> diff -Nrup a/mysql-test/suite/parts/inc/partition_auto_increment.inc
> b/mysql-test/suite/parts/inc/partition_auto_increment.inc
>> --- /dev/null Wed Dec 31 16:00:00 196900
>> +++ b/mysql-test/suite/parts/inc/partition_auto_increment.inc 2008-03-06 10:36:43
> +01:00
>> @@ -0,0 +1,468 @@
>> +# inc/partition_auto_increment.inc
>> +#
>> +# auto_increment test
>> +# used variables: $engine
>> +#
>> +
>> +-- disable_warnings
>> +DROP TABLE IF EXISTS t1;
>> +-- enable_warnings
>> +
>> +-- echo # test without partitioning for reference
>> +eval CREATE TABLE t1 (
>> + c1 INT NOT NULL AUTO_INCREMENT,
>> + PRIMARY KEY (c1))
>> +ENGINE=$engine;
>> +SHOW CREATE TABLE t1;
>> +SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
>> +AND TABLE_NAME='t1';
>> +INSERT INTO t1 VALUES (2);
>> +INSERT INTO t1 VALUES (4);
>> +INSERT INTO t1 VALUES (NULL);
>> +SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
>> +AND TABLE_NAME='t1';
>> +SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
>> +AND TABLE_NAME='t1';
>> +INSERT INTO t1 VALUES (0);
>> +-- error ER_DUP_KEY, ER_DUP_ENTRY
>> +INSERT INTO t1 VALUES (5), (16);
>> +INSERT INTO t1 VALUES (17);
>> +INSERT INTO t1 VALUES (19), (NULL);
>> +INSERT INTO t1 VALUES (NULL), (10), (NULL);
>> +INSERT INTO t1 VALUES (NULL);
>> +SET INSERT_ID = 30;
>> +INSERT INTO t1 VALUES (NULL);
>> +SELECT * FROM t1 ORDER BY c1;
>> +DROP TABLE t1;
>> +eval CREATE TABLE t1 (
>> + c1 INT NOT NULL AUTO_INCREMENT,
>> + PRIMARY KEY (c1))
>> +ENGINE=$engine;
>> +SHOW CREATE TABLE t1;
>> +FLUSH TABLE;
>> +SHOW CREATE TABLE t1;
>> +INSERT INTO t1 VALUES (4);
>> +FLUSH TABLE;
>> +SHOW CREATE TABLE t1;
>> +INSERT INTO t1 VALUES (NULL);
>> +FLUSH TABLE;
>> +SHOW CREATE TABLE t1;
>> +DELETE FROM t1;
>> +INSERT INTO t1 VALUES (NULL);
>> +SHOW CREATE TABLE t1;
>> +SELECT * FROM t1 ORDER BY c1;
>> +TRUNCATE TABLE t1;
>> +INSERT INTO t1 VALUES (NULL);
>> +SHOW CREATE TABLE t1;
>> +SELECT * FROM t1 ORDER BY c1;
>> +DROP TABLE t1;
>> +
>> +-- echo # Simple test with NULL
>> +eval CREATE TABLE t1 (
>> + c1 INT NOT NULL AUTO_INCREMENT,
>> + PRIMARY KEY (c1))
>> +ENGINE=$engine
>> +PARTITION BY HASH(c1)
>> +PARTITIONS 2;
>> +INSERT INTO t1 VALUES (NULL);
>> +SHOW CREATE TABLE t1;
>> +SELECT * FROM t1;
>> +DROP TABLE t1;
>> +
>> +-- echo # Test with sql_mode and first insert as 0
>> +eval CREATE TABLE t1 (
>> + c1 INT,
>> + c2 INT NOT NULL AUTO_INCREMENT,
>> + PRIMARY KEY (c2))
>> +ENGINE=$engine
>> +PARTITION BY HASH(c2)
>> +PARTITIONS 2;
>> +INSERT INTO t1 VALUES (1, NULL);
>> +-- error ER_DUP_KEY, ER_DUP_ENTRY
>> +INSERT INTO t1 VALUES (1, 1), (99, 99);
>> +INSERT INTO t1 VALUES (1, NULL);
>> +let $old_sql_mode = `select @@session.sql_mode`;
>> +SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
>> +INSERT INTO t1 VALUES (1, 0);
>> +SELECT * FROM t1 ORDER BY c1, c2;
>> +DROP TABLE t1;
>> +eval CREATE TABLE t1 (
>> + c1 INT,
>> + c2 INT NOT NULL AUTO_INCREMENT,
>> + PRIMARY KEY (c2))
>> +ENGINE=$engine
>> +PARTITION BY HASH(c2)
>> +PARTITIONS 2;
>> +INSERT INTO t1 VALUES (1, 0);
>> +INSERT INTO t1 VALUES (1, 1), (1, NULL);
>> +INSERT INTO t1 VALUES (2, NULL), (4, 7);
>> +INSERT INTO t1 VALUES (1, NULL);
>> +SELECT * FROM t1 ORDER BY c1, c2;
>> +eval SET @@session.sql_mode = '$old_sql_mode';
>> +DROP TABLE t1;
>> +
>> +
>> +-- echo # Simple test with NULL, 0 and explicit values both incr. and desc.
>> +eval CREATE TABLE t1 (
>> + c1 INT NOT NULL AUTO_INCREMENT,
>> + PRIMARY KEY (c1))
>> +ENGINE=$engine
>> +PARTITION BY HASH(c1)
>> +PARTITIONS 2;
>> +INSERT INTO t1 VALUES (2), (4), (NULL);
>> +INSERT INTO t1 VALUES (0);
>> +-- error ER_DUP_KEY, ER_DUP_ENTRY
>> +INSERT INTO t1 VALUES (5), (16);
>> +INSERT INTO t1 VALUES (17), (19), (NULL);
>> +INSERT INTO t1 VALUES (NULL), (10), (NULL);
>> +INSERT INTO t1 VALUES (NULL), (9);
>> +INSERT INTO t1 VALUES (59), (55);
>> +INSERT INTO t1 VALUES (NULL), (90);
>> +INSERT INTO t1 VALUES (NULL);
>> +SELECT * FROM t1 ORDER BY c1;
>> +DROP TABLE t1;
>> +
>> +-- echo # Test with auto_increment_increment and auto_increment_offset.
>> +eval CREATE TABLE t1 (
>> + c1 INT NOT NULL AUTO_INCREMENT,
>> + PRIMARY KEY (c1))
>> +ENGINE=$engine
>> +PARTITION BY HASH(c1)
>> +PARTITIONS 2;
>> +let $old_increment = `SELECT @@session.auto_increment_increment`;
>> +let $old_offset = `SELECT @@session.auto_increment_offset`;
>> +SET @@session.auto_increment_increment = 10;
>> +SET @@session.auto_increment_offset = 5;
>> +INSERT INTO t1 VALUES (1);
>> +INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
>> +SET @@session.auto_increment_increment = 5;
>> +SET @@session.auto_increment_offset = 3;
>> +INSERT INTO t1 VALUES (NULL);
>> +let $new_val = `SELECT LAST_INSERT_ID()`;
>> +eval INSERT INTO t1 VALUES ($new_val + 1);
>> +INSERT INTO t1 VALUES (NULL);
>> +let $new_val = `SELECT LAST_INSERT_ID()`;
>> +eval INSERT INTO t1 VALUES ($new_val + 2);
>> +INSERT INTO t1 VALUES (NULL);
>> +let $new_val = `SELECT LAST_INSERT_ID()`;
>> +eval INSERT INTO t1 VALUES ($new_val + 3);
>> +INSERT INTO t1 VALUES (NULL);
>> +let $new_val = `SELECT LAST_INSERT_ID()`;
>> +eval INSERT INTO t1 VALUES ($new_val + 4);
>> +INSERT INTO t1 VALUES (NULL);
>> +let $new_val = `SELECT LAST_INSERT_ID()`;
>> +eval INSERT INTO t1 VALUES ($new_val + 5);
>> +INSERT INTO t1 VALUES (NULL);
>> +let $new_val = `SELECT LAST_INSERT_ID()`;
>> +eval INSERT INTO t1 VALUES ($new_val + 6);
>> +INSERT INTO t1 VALUES (NULL);
>> +eval SET @@session.auto_increment_increment = $old_increment;
>> +eval SET @@session.auto_increment_offset = $old_offset;
>> +SELECT * FROM t1 ORDER BY c1;
>> +DROP TABLE t1;
>> +
>> +
>> +-- echo # Test reported auto_increment value
>> +eval CREATE TABLE t1 (
>> + c1 INT NOT NULL AUTO_INCREMENT,
>> + PRIMARY KEY (c1))
>> +ENGINE=$engine
>> +PARTITION BY HASH (c1)
>> +PARTITIONS 2;
>> +SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
>> +AND TABLE_NAME='t1';
>> +INSERT INTO t1 VALUES (2);
>> +SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
>> +AND TABLE_NAME='t1';
>> +INSERT INTO t1 VALUES (4);
>> +INSERT INTO t1 VALUES (NULL);
>> +SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
>> +AND TABLE_NAME='t1';
>> +INSERT INTO t1 VALUES (NULL);
>> +INSERT INTO t1 VALUES (17);
>> +INSERT INTO t1 VALUES (19);
>> +INSERT INTO t1 VALUES (NULL);
>> +INSERT INTO t1 VALUES (NULL);
>> +SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
>> +AND TABLE_NAME='t1';
>> +SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
>> +AND TABLE_NAME='t1';
>> +INSERT INTO t1 VALUES (10);
>> +SELECT * FROM t1 ORDER BY c1;
>> +INSERT INTO t1 VALUES (NULL);
>> +SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
>> +AND TABLE_NAME='t1';
>> +INSERT INTO t1 VALUES (NULL);
>> +INSERT INTO t1 VALUES (15);
>> +INSERT INTO t1 VALUES (NULL);
>> +SELECT * FROM t1 ORDER BY c1;
>> +INSERT INTO t1 VALUES (NULL);
>> +DELETE FROM t1;
>> +INSERT INTO t1 VALUES (NULL);
>> +SHOW CREATE TABLE t1;
>> +SELECT * FROM t1 ORDER BY c1;
>> +TRUNCATE TABLE t1;
>> +INSERT INTO t1 VALUES (NULL);
>> +SHOW CREATE TABLE t1;
>> +SELECT * FROM t1 ORDER BY c1;
>> +DROP TABLE t1;
>> +
>> +-- echo # Test with two threads
>> +connect(con1, localhost, root,,);
>> +connection default;
>> +-- echo # con default
>> +eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
>> + ENGINE = $engine
>> + PARTITION BY HASH(c1)
>> + PARTITIONS 2;
>> +INSERT INTO t1 (c1) VALUES (2);
>> +INSERT INTO t1 (c1) VALUES (4);
>> +connection con1;
>> +-- echo # con1
>> +INSERT INTO t1 (c1) VALUES (NULL);
>> +INSERT INTO t1 (c1) VALUES (10);
>> +connection default;
>> +-- echo # con default
>> +INSERT INTO t1 (c1) VALUES (NULL);
>> +INSERT INTO t1 (c1) VALUES (NULL);
>> +INSERT INTO t1 (c1) VALUES (19);
>> +INSERT INTO t1 (c1) VALUES (21);
>> +-- echo # con1
>> +connection con1;
>> +INSERT INTO t1 (c1) VALUES (NULL);
>> +connection default;
>> +-- echo # con default
>> +INSERT INTO t1 (c1) VALUES (16);
>> +-- echo # con1
>> +connection con1;
>> +INSERT INTO t1 (c1) VALUES (NULL);
>> +disconnect con1;
>> +connection default;
>> +-- echo # con default
>> +INSERT INTO t1 (c1) VALUES (NULL);
>> +SELECT * FROM t1 ORDER BY c1;
>> +DROP TABLE t1;
>> +
>> +-- echo # Test with two threads + start transaction NO PARTITIONING
>> +connect(con1, localhost, root,,);
>> +connection default;
>> +-- echo # con default
>> +eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
>> + ENGINE = $engine;
>> +START TRANSACTION;
>> +INSERT INTO t1 (c1) VALUES (2);
>> +INSERT INTO t1 (c1) VALUES (4);
>> +connection con1;
>> +-- echo # con1
>> +START TRANSACTION;
>> +INSERT INTO t1 (c1) VALUES (NULL);
>> +INSERT INTO t1 (c1) VALUES (10);
>> +connection default;
>> +-- echo # con default
>> +INSERT INTO t1 (c1) VALUES (NULL);
>> +INSERT INTO t1 (c1) VALUES (NULL);
>> +INSERT INTO t1 (c1) VALUES (19);
>> +INSERT INTO t1 (c1) VALUES (21);
>> +-- echo # con1
>> +connection con1;
>> +INSERT INTO t1 (c1) VALUES (NULL);
>> +connection default;
>> +-- echo # con default
>> +INSERT INTO t1 (c1) VALUES (16);
>> +-- echo # con1
>> +connection con1;
>> +INSERT INTO t1 (c1) VALUES (NULL);
>> +SELECT * FROM t1 ORDER BY c1;
>> +COMMIT;
>> +SELECT * FROM t1 ORDER BY c1;
>> +disconnect con1;
>> +connection default;
>> +-- echo # con default
>> +INSERT INTO t1 (c1) VALUES (NULL);
>> +SELECT * FROM t1 ORDER BY c1;
>> +COMMIT;
>> +SELECT * FROM t1 ORDER BY c1;
>> +DROP TABLE t1;
>> +
>> +-- echo # Test with two threads + start transaction
>> +connect(con1, localhost, root,,);
>> +connection default;
>> +-- echo # con default
>> +eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
>> + ENGINE = $engine
>> + PARTITION BY HASH(c1)
>> + PARTITIONS 2;
>> +START TRANSACTION;
>> +INSERT INTO t1 (c1) VALUES (2);
>> +INSERT INTO t1 (c1) VALUES (4);
>> +connection con1;
>> +-- echo # con1
>> +START TRANSACTION;
>> +INSERT INTO t1 (c1) VALUES (NULL), (10);
>> +connection default;
>> +-- echo # con default
>> +INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19);
>> +INSERT INTO t1 (c1) VALUES (21);
>> +-- echo # con1
>> +connection con1;
>> +INSERT INTO t1 (c1) VALUES (NULL);
>> +connection default;
>> +-- echo # con default
>> +INSERT INTO t1 (c1) VALUES (16);
>> +-- echo # con1
>> +connection con1;
>> +INSERT INTO t1 (c1) VALUES (NULL);
>> +SELECT * FROM t1 ORDER BY c1;
>> +COMMIT;
>> +SELECT * FROM t1 ORDER BY c1;
>> +disconnect con1;
>> +connection default;
>> +-- echo # con default
>> +INSERT INTO t1 (c1) VALUES (NULL);
>> +SELECT * FROM t1 ORDER BY c1;
>> +COMMIT;
>> +SELECT * FROM t1 ORDER BY c1;
>> +DROP TABLE t1;
>> +
>> +-- echo # Test with another column after
>> +eval CREATE TABLE t1 (
>> +c1 INT NOT NULL AUTO_INCREMENT,
>> +c2 INT,
>> +PRIMARY KEY (c1,c2))
>> +ENGINE = $engine
>> +PARTITION BY HASH(c2)
>> +PARTITIONS 2;
>> +INSERT INTO t1 VALUES (1, 0);
>> +-- error 0, ER_DUP_KEY
>
> GB why can the statement either succeed or fail with dup key?
>
MJ: some missed cleanups from sql_mode changes, I'll fix it.
>> +INSERT INTO t1 VALUES (1, 1);
>> +INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (NULL, 3);
>> +INSERT INTO t1 VALUES (NULL, 3);
>> +INSERT INTO t1 VALUES (2, 0), (NULL, 2);
>> +-- error 0, ER_DUP_KEY
>> +INSERT INTO t1 VALUES (2, 2);
>> +INSERT INTO t1 VALUES (2, 22);
>> +INSERT INTO t1 VALUES (NULL, 2);
>> +SELECT * FROM t1 ORDER BY c1,c2;
>> +DROP TABLE t1;
>> +
>> +-- echo # Test with another column before
>> +eval CREATE TABLE t1 (
>> +c1 INT,
>> +c2 INT NOT NULL AUTO_INCREMENT,
>> +PRIMARY KEY (c2))
>> +ENGINE = $engine
>> +PARTITION BY HASH(c2)
>> +PARTITIONS 2;
>> +INSERT INTO t1 VALUES (1, 0);
>> +-- error 0, ER_DUP_KEY
>
> GB why can the statement either succeed or fail with dup key?
>
MJ: some missed cleanups from sql_mode changes, I'll fix it.
>> +INSERT INTO t1 VALUES (1, 1);
>> +INSERT INTO t1 VALUES (1, NULL);
>> +INSERT INTO t1 VALUES (2, NULL), (3, 11), (3, NULL), (2, 0);
>> +INSERT INTO t1 VALUES (2, NULL);
>> +-- error 0, ER_DUP_KEY
>
> GB why can the statement either succeed or fail with dup key?
>
MJ: some missed cleanups from sql_mode changes, I'll fix it.
>> +INSERT INTO t1 VALUES (2, 2);
>> +INSERT INTO t1 VALUES (2, 22);
>> +INSERT INTO t1 VALUES (2, NULL);
>> +SELECT * FROM t1 ORDER BY c1,c2;
>> +DROP TABLE t1;
>> +
>> +-- echo # Test with auto_increment on secondary column in multi-column-index
>> +-- disable_abort_on_error
>> +eval CREATE TABLE t1 (
>> +c1 INT,
>> +c2 INT NOT NULL AUTO_INCREMENT,
>> +PRIMARY KEY (c1,c2))
>> +ENGINE = $engine
>> +PARTITION BY HASH(c2)
>> +PARTITIONS 2;
>> +-- enable_abort_on_error
>> +-- disable_query_log
>> +eval SET @my_errno= $mysql_errno ;
>> +let $run = `SELECT @my_errno = 0`;
>> +# ER_WRONG_AUTO_KEY is 1075
>> +let $ER_WRONG_AUTO_KEY= 1075;
>> +if (`SELECT @my_errno NOT IN (0,$ER_WRONG_AUTO_KEY)`)
>> +{
>> + -- echo # Unknown error code, exits
>> + exit;
>> +}
>> +-- enable_query_log
>> +if ($run)
>> +{
>> +INSERT INTO t1 VALUES (1, 0);
>> +-- error 0, ER_DUP_KEY
>
> GB why can the statement either succeed or fail with dup key?
>
MJ: some missed cleanups from sql_mode changes, I'll fix it.
>> +INSERT INTO t1 VALUES (1, 1);
>> +INSERT INTO t1 VALUES (1, NULL);
>> +INSERT INTO t1 VALUES (2, NULL);
>> +INSERT INTO t1 VALUES (3, NULL);
>> +INSERT INTO t1 VALUES (3, NULL), (2, 0), (2, NULL);
>> +-- error 0, ER_DUP_KEY
>
> GB why can the statement either succeed or fail with dup key?
>
MJ: some missed cleanups from sql_mode changes, I'll fix it.
>> +INSERT INTO t1 VALUES (2, 2);
>> +INSERT INTO t1 VALUES (2, 22), (2, NULL);
>> +SELECT * FROM t1 ORDER BY c1,c2;
>> +DROP TABLE t1;
>> +}
>> +
>> +-- echo # Test AUTO_INCREMENT in CREATE
>> +eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
>> + ENGINE = $engine
>> + AUTO_INCREMENT = 15
>> + PARTITION BY HASH(c1)
>> + PARTITIONS 2;
>> +SHOW CREATE TABLE t1;
>> +INSERT INTO t1 (c1) VALUES (4);
>> +SHOW CREATE TABLE t1;
>> +INSERT INTO t1 (c1) VALUES (0);
>> +SHOW CREATE TABLE t1;
>> +INSERT INTO t1 (c1) VALUES (NULL);
>> +SHOW CREATE TABLE t1;
>> +SELECT * FROM t1 ORDER BY c1;
>> +
>> +-- echo # Test sql_mode 'NO_AUTO_VALUE_ON_ZERO'
>> +let $old_sql_mode = `select @@session.sql_mode`;
>> +SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
>> +INSERT INTO t1 (c1) VALUES (300);
>> +SHOW CREATE TABLE t1;
>> +INSERT INTO t1 (c1) VALUES (0);
>> +SHOW CREATE TABLE t1;
>> +INSERT INTO t1 (c1) VALUES (NULL);
>> +SHOW CREATE TABLE t1;
>> +SELECT * FROM t1 ORDER BY c1;
>> +eval SET @@session.sql_mode = '$old_sql_mode';
>> +DROP TABLE t1;
>> +
>> +-- echo # Test SET INSERT_ID
>> +eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
>> + ENGINE = $engine
>> + PARTITION BY HASH(c1)
>> + PARTITIONS 2;
>> +SHOW CREATE TABLE t1;
>> +INSERT INTO t1 (c1) VALUES (NULL);
>> +SHOW CREATE TABLE t1;
>> +SELECT * FROM t1;
>> +SET INSERT_ID = 23;
>> +SHOW CREATE TABLE t1;
>> +INSERT INTO t1 (c1) VALUES (NULL);
>> +SHOW CREATE TABLE t1;
>> +SELECT * FROM t1 ORDER BY c1;
>> +DROP TABLE t1;
>> +
>> +-- echo # Testing with FLUSH TABLE
>> +eval CREATE TABLE t1 (
>> + c1 INT NOT NULL AUTO_INCREMENT,
>> + PRIMARY KEY (c1))
>> + ENGINE=$engine
>> + PARTITION BY HASH(c1)
>> + PARTITIONS 2;
>> +SHOW CREATE TABLE t1;
>> +FLUSH TABLE;
>> +SHOW CREATE TABLE t1;
>> +INSERT INTO t1 VALUES (4);
>> +FLUSH TABLE;
>> +SHOW CREATE TABLE t1;
>> +INSERT INTO t1 VALUES (NULL);
>> +FLUSH TABLE;
>> +SHOW CREATE TABLE t1;
>> +SELECT * FROM t1 ORDER BY c1;
>> +DROP TABLE t1;
>> +
>> diff -Nrup a/mysql-test/suite/parts/r/partition_auto_increment_innodb.result
> b/mysql-test/suite/parts/r/partition_auto_increment_innodb.result
>> --- /dev/null Wed Dec 31 16:00:00 196900
>> +++ b/mysql-test/suite/parts/r/partition_auto_increment_innodb.result 2008-03-06
> 10:36:43 +01:00
>
> GB I did a diff between
> partition_auto_increment_myisam.result
> and
> partition_auto_increment_innodb.result
>
> and this showed possibly abnormal differences (among some normal
> ones). Could you please do the diff and explain the cause of each
> significant difference?
>
> Thanks.
>
I have check the diffs between MyISAM, InnoDB and Falcon, and all diffs
are related to the differences in the engines. (but there are less
differences when using partitioning, i.e. InnoDB's gaps are no longer
existent)
First diff (row 47-49) is between nonpartitioned InnoDB vs MyISAM, and
that diff is because of InnoDB:s behavior of leaving gaps, documented in:
http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html
The second diff (row 341) is because of the truncate bug in myisam
(bug#3511)
The third diff (row 409) is because of InnoDB's transaction handling and
MyISAM's lack of it.
The fourth diff (row 586) is because of only MyISAM supports
auto_increment on a secondary part of an index. (I wrote the .inc file
to handle both cases)
And there are also some differences between InnoDB and Falcon regarding
to the transactional behavior.
--
Mattias Jonsson, Software Engineer
MySQL AB, www.mysql.com
Are you MySQL certified? www.mysql.com/certification
