2770 Georgi Kodinov 2008-10-07
fixed test suite failures in 5.1-bugteam
modified:
mysql-test/r/trigger-trans.result
mysql-test/suite/rpl/r/rpl_temporary.result
mysql-test/suite/rpl/t/rpl_temporary.test
mysql-test/t/trigger-trans.test
2769 Georgi Kodinov 2008-10-07 [merge]
merged 5.0-bugteam -> 5.1-bugteam
modified:
mysql-test/suite/rpl/t/disabled.def
2768 Georgi Kodinov 2008-10-07 [merge]
merged 5.1-5.1.29-rc -> 5.1-bugteam
added:
mysql-test/extra/rpl_tests/rpl_blackhole.test
mysql-test/suite/parts/inc/partition_auto_increment.inc
mysql-test/suite/parts/r/partition_auto_increment_archive.result
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
mysql-test/suite/parts/t/partition_auto_increment_blackhole.test
mysql-test/suite/parts/t/partition_auto_increment_innodb.test
mysql-test/suite/parts/t/partition_auto_increment_memory.test
mysql-test/suite/parts/t/partition_auto_increment_myisam.test
mysql-test/suite/parts/t/partition_auto_increment_ndb.test
mysql-test/suite/rpl/r/rpl_blackhole.result
mysql-test/suite/rpl/t/rpl_blackhole.test
modified:
mysql-test/extra/binlog_tests/binlog.test
mysql-test/extra/binlog_tests/blackhole.test
mysql-test/include/mix1.inc
mysql-test/lib/mtr_report.pl
mysql-test/r/ctype_cp932_binlog_stm.result
mysql-test/r/func_group.result
mysql-test/r/innodb-semi-consistent.result
mysql-test/r/innodb.result
mysql-test/r/innodb_mysql.result
mysql-test/r/log_basic.result
mysql-test/r/log_bin_trust_routine_creators_basic.result
mysql-test/r/log_state.result
mysql-test/r/partition.result
mysql-test/r/trigger-trans.result
mysql-test/r/tx_isolation_func.result
mysql-test/r/warnings.result
mysql-test/suite/binlog/r/binlog_multi_engine.result
mysql-test/suite/binlog/r/binlog_row_binlog.result
mysql-test/suite/binlog/r/binlog_stm_binlog.result
mysql-test/suite/binlog/r/binlog_stm_blackhole.result
mysql-test/suite/binlog/t/binlog_multi_engine.test
mysql-test/suite/ndb/t/disabled.def
mysql-test/suite/rpl/r/rpl_sp.result
mysql-test/t/func_group.test
mysql-test/t/innodb-semi-consistent.test
mysql-test/t/innodb.test
mysql-test/t/log_basic.test
mysql-test/t/log_state.test
mysql-test/t/partition.test
mysql-test/t/trigger-trans.test
mysql-test/t/tx_isolation_func.test
sql/ha_partition.cc
sql/ha_partition.h
sql/handler.cc
sql/handler.h
sql/item.cc
sql/item.h
sql/log.cc
sql/log_event.cc
sql/mysqld.cc
sql/set_var.cc
sql/sp_head.cc
sql/sql_class.h
sql/sql_partition.cc
sql/sql_select.cc
sql/table.h
storage/blackhole/ha_blackhole.cc
storage/blackhole/ha_blackhole.h
support-files/my-huge.cnf.sh
support-files/my-innodb-heavy-4G.cnf.sh
support-files/my-large.cnf.sh
support-files/my-medium.cnf.sh
support-files/my-small.cnf.sh
2767 Chad MILLER 2008-10-06 [merge]
Merge fix of autoconf substitution of string containing single quotes.
modified:
configure.in
=== modified file 'mysql-test/extra/binlog_tests/binlog.test'
--- a/mysql-test/extra/binlog_tests/binlog.test 2008-02-08 12:47:25 +0000
+++ b/mysql-test/extra/binlog_tests/binlog.test 2008-10-02 05:56:07 +0000
@@ -125,6 +125,45 @@ drop table t1;
--replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/ /Server ver: [^,]*,/Server version,/
show binlog events from 0;
+
+#
+# Bug #39182: Binary log producing incompatible character set query from
+# stored procedure.
+#
+reset master;
+CREATE DATABASE bug39182 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
+USE bug39182;
+CREATE TABLE t1 (a VARCHAR(255) COLLATE utf8_unicode_ci)
+ DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+
+DELIMITER //;
+
+CREATE PROCEDURE p1()
+BEGIN
+ DECLARE s1 VARCHAR(255);
+ SET s1= "test";
+ CREATE TEMPORARY TABLE tmp1
+ SELECT * FROM t1 WHERE a LIKE CONCAT("%", s1, "%");
+ SELECT
+ COLLATION(NAME_CONST('s1', _utf8'test')) c1,
+ COLLATION(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) c2,
+ COLLATION(s1) c3,
+ COERCIBILITY(NAME_CONST('s1', _utf8'test')) d1,
+ COERCIBILITY(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) d2,
+ COERCIBILITY(s1) d3;
+ DROP TEMPORARY TABLE tmp1;
+END//
+
+DELIMITER ;//
+
+CALL p1();
+source include/show_binlog_events.inc;
+
+DROP PROCEDURE p1;
+DROP TABLE t1;
+DROP DATABASE bug39182;
+USE test;
+
--echo End of 5.0 tests
# Test of a too big SET INSERT_ID: see if the truncated value goes
=== modified file 'mysql-test/extra/binlog_tests/blackhole.test'
--- a/mysql-test/extra/binlog_tests/blackhole.test 2008-03-29 13:00:12 +0000
+++ b/mysql-test/extra/binlog_tests/blackhole.test 2008-10-02 09:02:38 +0000
@@ -139,15 +139,6 @@ drop table t1,t2,t3;
# table
#
CREATE TABLE t1(a INT) ENGINE=BLACKHOLE;
-# NOTE: After exchanging open_ltable() by open_and_lock_tables() in
-# handle_delayed_insert() to fix problems with MERGE tables (Bug#26379),
-# problems with INSERT DELAYED and BLACKHOLE popped up. open_ltable()
-# does not check if the binlogging capabilities of the statement and the
-# table match. So the below used to succeed. But since INSERT DELAYED
-# switches to row-based logging in mixed-mode and BLACKHOLE cannot do
-# row-based logging, it could not really work. Until this problem is
-# correctly fixed, we have that error here.
---error ER_BINLOG_LOGGING_IMPOSSIBLE
INSERT DELAYED INTO t1 VALUES(1);
DROP TABLE t1;
=== added file 'mysql-test/extra/rpl_tests/rpl_blackhole.test'
--- a/mysql-test/extra/rpl_tests/rpl_blackhole.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/extra/rpl_tests/rpl_blackhole.test 2008-10-02 19:13:15 +0000
@@ -0,0 +1,32 @@
+# Check replication of one statement assuming that the engine on the
+# slave is a blackhole engine.
+
+# Input:
+# $statement Statement to evaluate, it is assumed to change t1
+
+# 1. Evaluate statement on master, it is assumed to change t1
+# 2. Wait for statement to be processed on slave
+# 3. SELECT from table t1 to see what was written
+# 4. Compare position on slave before executing statement and after
+# executing statement. If difference is >0, then something was
+# written to the binary log on the slave.
+
+connection slave;
+let $before = query_get_value("SHOW MASTER STATUS", Position, 1);
+
+--echo [on master]
+connection master;
+eval $statement;
+
+--echo [on slave]
+sync_slave_with_master;
+--echo # Expect 0
+SELECT COUNT(*) FROM t1;
+let $after = query_get_value("SHOW MASTER STATUS", Position, 1);
+let $something_written = `select $after - $before != 0`;
+if ($something_written) {
+ --echo >>> Something was written to binary log <<<
+}
+if (!$something_written) {
+ --echo >>> Nothing was written to binary log <<<
+}
=== modified file 'mysql-test/include/mix1.inc'
--- a/mysql-test/include/mix1.inc 2008-07-25 15:12:47 +0000
+++ b/mysql-test/include/mix1.inc 2008-10-03 12:24:19 +0000
@@ -1256,6 +1256,7 @@ connect (con1,localhost,root,,);
connect (con2,localhost,root,,);
SET SESSION AUTOCOMMIT = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
+set binlog_format=mixed;
--echo # Switch to connection con1
connection con1;
=== modified file 'mysql-test/lib/mtr_report.pl'
--- a/mysql-test/lib/mtr_report.pl 2008-08-12 10:26:23 +0000
+++ b/mysql-test/lib/mtr_report.pl 2008-10-02 07:46:14 +0000
@@ -312,7 +312,7 @@ sub mtr_report_stats ($) {
/Slave: According to the master's version/ or
/Slave: Column [0-9]* type mismatch/ or
/Slave: Error .* doesn't exist/ or
- /Slave: Error .*Deadlock found/ or
+ /Slave: Deadlock found/ or
/Slave: Error .*Unknown table/ or
/Slave: Error in Write_rows event: / or
/Slave: Field .* of table .* has no default value/ or
=== modified file 'mysql-test/r/ctype_cp932_binlog_stm.result'
--- a/mysql-test/r/ctype_cp932_binlog_stm.result 2008-05-16 15:35:15 +0000
+++ b/mysql-test/r/ctype_cp932_binlog_stm.result 2008-10-02 05:56:07 +0000
@@ -40,9 +40,9 @@ IN ind DECIMAL(10,2))
BEGIN
INSERT INTO t4 VALUES (ins1, ins2, ind);
END
-master-bin.000001 784 Query 1 992 use `test`; INSERT INTO t4 VALUES ( NAME_CONST('ins1',_latin1 0x466F6F2773206120426172), NAME_CONST('ins2',_cp932 0xED40ED41ED42), NAME_CONST('ind',47.93))
-master-bin.000001 992 Query 1 1081 use `test`; DROP PROCEDURE bug18293
-master-bin.000001 1081 Query 1 1160 use `test`; DROP TABLE t4
+master-bin.000001 784 Query 1 1048 use `test`; INSERT INTO t4 VALUES ( NAME_CONST('ins1',_latin1 0x466F6F2773206120426172 COLLATE 'latin1_swedish_ci'), NAME_CONST('ins2',_cp932 0xED40ED41ED42 COLLATE 'cp932_japanese_ci'), NAME_CONST('ind',47.93))
+master-bin.000001 1048 Query 1 1137 use `test`; DROP PROCEDURE bug18293
+master-bin.000001 1137 Query 1 1216 use `test`; DROP TABLE t4
End of 5.0 tests
SHOW BINLOG EVENTS FROM 364;
ERROR HY000: Error when executing command SHOW BINLOG EVENTS: Wrong offset or I/O error
=== modified file 'mysql-test/r/func_group.result'
--- a/mysql-test/r/func_group.result 2008-03-19 11:25:36 +0000
+++ b/mysql-test/r/func_group.result 2008-10-02 14:44:49 +0000
@@ -1416,4 +1416,41 @@ SELECT AVG(a), CAST(AVG(a) AS DECIMAL) F
AVG(a) CAST(AVG(a) AS DECIMAL)
15 15
DROP TABLE t1;
+CREATE TABLE derived1 (a bigint(21));
+INSERT INTO derived1 VALUES (2);
+CREATE TABLE D (
+pk int(11) NOT NULL AUTO_INCREMENT,
+int_nokey int(11) DEFAULT NULL,
+int_key int(11) DEFAULT NULL,
+filler blob,
+PRIMARY KEY (pk),
+KEY int_key (int_key)
+);
+INSERT INTO D VALUES
+(39,40,4,repeat(' X', 42)),
+(43,56,4,repeat(' X', 42)),
+(47,12,4,repeat(' X', 42)),
+(71,28,4,repeat(' X', 42)),
+(76,54,4,repeat(' X', 42)),
+(83,45,4,repeat(' X', 42)),
+(105,53,12,NULL);
+SELECT
+(SELECT COUNT( int_nokey )
+FROM derived1 AS X
+WHERE
+X.int_nokey < 61
+GROUP BY pk
+LIMIT 1)
+FROM D AS X
+WHERE X.int_key < 13
+GROUP BY int_nokey LIMIT 1;
+(SELECT COUNT( int_nokey )
+FROM derived1 AS X
+WHERE
+X.int_nokey < 61
+GROUP BY pk
+LIMIT 1)
+1
+DROP TABLE derived1;
+DROP TABLE D;
End of 5.0 tests
=== modified file 'mysql-test/r/innodb-semi-consistent.result'
--- a/mysql-test/r/innodb-semi-consistent.result 2007-11-27 08:25:45 +0000
+++ b/mysql-test/r/innodb-semi-consistent.result 2008-10-03 12:24:19 +0000
@@ -1,4 +1,5 @@
drop table if exists t1;
+set binlog_format=mixed;
set session transaction isolation level read committed;
create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
insert into t1 values (1),(2),(3),(4),(5),(6),(7);
@@ -6,6 +7,7 @@ set autocommit=0;
select * from t1 where a=3 lock in share mode;
a
3
+set binlog_format=mixed;
set session transaction isolation level read committed;
set autocommit=0;
update t1 set a=10 where a=5;
=== modified file 'mysql-test/r/innodb.result'
--- a/mysql-test/r/innodb.result 2008-08-20 22:18:33 +0000
+++ b/mysql-test/r/innodb.result 2008-10-03 12:24:19 +0000
@@ -1024,6 +1024,7 @@ id code name
4 2 Erik
5 3 Sasha
COMMIT;
+SET binlog_format='MIXED';
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt');
@@ -2961,9 +2962,11 @@ drop table t1,t2;
create table t1(a int not null, b int, primary key(a)) engine=innodb;
insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);
commit;
+SET binlog_format='MIXED';
set autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
update t1 set b = 5 where b = 1;
+SET binlog_format='MIXED';
set autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
select * from t1 where a = 7 and b = 3 for update;
@@ -3002,6 +3005,7 @@ d e
3 1
8 6
12 1
+SET binlog_format='MIXED';
set autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
insert into t1 select * from t2;
@@ -3032,30 +3036,39 @@ a b
3 1
8 6
12 1
+SET binlog_format='MIXED';
set autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
insert into t1 select * from t2;
+SET binlog_format='MIXED';
set autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
update t3 set b = (select b from t2 where a = d);
+SET binlog_format='MIXED';
set autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
create table t4(a int not null, b int, primary key(a)) engine=innodb select * from t2;
+SET binlog_format='MIXED';
set autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
insert into t5 (select * from t2 lock in share mode);
+SET binlog_format='MIXED';
set autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
update t6 set e = (select b from t2 where a = d lock in share mode);
+SET binlog_format='MIXED';
set autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
create table t7(a int not null, b int, primary key(a)) engine=innodb select * from t2 lock in share mode;
+SET binlog_format='MIXED';
set autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
insert into t8 (select * from t2 for update);
+SET binlog_format='MIXED';
set autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
update t9 set e = (select b from t2 where a = d for update);
+SET binlog_format='MIXED';
set autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
create table t10(a int not null, b int, primary key(a)) engine=innodb select * from t2 for update;
@@ -3202,6 +3215,7 @@ id
-10
1
DROP TABLE t1;
+SET binlog_format='MIXED';
SET TX_ISOLATION='read-committed';
SET AUTOCOMMIT=0;
DROP TABLE IF EXISTS t1, t2;
@@ -3212,6 +3226,7 @@ CREATE TABLE t1 ( a int ) ENGINE=InnoDB;
CREATE TABLE t2 LIKE t1;
SELECT * FROM t2;
a
+SET binlog_format='MIXED';
SET TX_ISOLATION='read-committed';
SET AUTOCOMMIT=0;
INSERT INTO t1 VALUES (1);
@@ -3219,10 +3234,12 @@ COMMIT;
SELECT * FROM t1 WHERE a=1;
a
1
+SET binlog_format='MIXED';
SET TX_ISOLATION='read-committed';
SET AUTOCOMMIT=0;
SELECT * FROM t2;
a
+SET binlog_format='MIXED';
SET TX_ISOLATION='read-committed';
SET AUTOCOMMIT=0;
INSERT INTO t1 VALUES (2);
=== modified file 'mysql-test/r/innodb_mysql.result'
--- a/mysql-test/r/innodb_mysql.result 2008-08-11 18:02:03 +0000
+++ b/mysql-test/r/innodb_mysql.result 2008-10-03 12:24:19 +0000
@@ -1512,6 +1512,7 @@ ok
drop table t1;
SET SESSION AUTOCOMMIT = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
+set binlog_format=mixed;
# Switch to connection con1
CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(256))
ENGINE = InnoDB;
=== modified file 'mysql-test/r/log_basic.result'
--- a/mysql-test/r/log_basic.result 2008-04-10 13:14:28 +0000
+++ b/mysql-test/r/log_basic.result 2008-10-06 09:29:42 +0000
@@ -5,8 +5,8 @@ INIT_VALUE
SELECT @@log AS INIT_VALUE;
INIT_VALUE
1
-SET @@global.log = ON;
-SET global log = 0;
+SET @@global.general_log = ON;
+SET global general_log = 0;
'Bug# 34832: log is a system but it is not accessible using SET @@global.log;'
'SET GLOBAL log; and SELECT @@global.log. SHOW VARIABLES shows the value of log.'
'#--------------------FN_DYNVARS_062_02-------------------------#'
=== modified file 'mysql-test/r/log_bin_trust_routine_creators_basic.result'
--- a/mysql-test/r/log_bin_trust_routine_creators_basic.result 2008-04-10 13:14:28 +0000
+++ b/mysql-test/r/log_bin_trust_routine_creators_basic.result 2008-10-06 14:22:07 +0000
@@ -5,17 +5,17 @@ SELECT @start_global_value;
'#--------------------FN_DYNVARS_064_01-------------------------#'
SET @@global.log_bin_trust_routine_creators = TRUE;
Warnings:
-Warning 1287 The syntax 'log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use 'log_bin_trust_function_creators' instead
+Warning 1287 The syntax '@@log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use '@@log_bin_trust_function_creators' instead
SET @@global.log_bin_trust_routine_creators = DEFAULT;
Warnings:
-Warning 1287 The syntax 'log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use 'log_bin_trust_function_creators' instead
+Warning 1287 The syntax '@@log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use '@@log_bin_trust_function_creators' instead
SELECT @@global.log_bin_trust_routine_creators;
@@global.log_bin_trust_routine_creators
0
'#--------------------FN_DYNVARS_064_02-------------------------#'
SET @@global.log_bin_trust_routine_creators = DEFAULT;
Warnings:
-Warning 1287 The syntax 'log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use 'log_bin_trust_function_creators' instead
+Warning 1287 The syntax '@@log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use '@@log_bin_trust_function_creators' instead
SELECT @@global.log_bin_trust_routine_creators = 'FALSE';
@@global.log_bin_trust_routine_creators = 'FALSE'
1
@@ -24,37 +24,37 @@ Warning 1292 Truncated incorrect DOUBLE
'#--------------------FN_DYNVARS_064_03-------------------------#'
SET @@global.log_bin_trust_routine_creators = ON;
Warnings:
-Warning 1287 The syntax 'log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use 'log_bin_trust_function_creators' instead
+Warning 1287 The syntax '@@log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use '@@log_bin_trust_function_creators' instead
SELECT @@global.log_bin_trust_routine_creators;
@@global.log_bin_trust_routine_creators
1
SET @@global.log_bin_trust_routine_creators = OFF;
Warnings:
-Warning 1287 The syntax 'log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use 'log_bin_trust_function_creators' instead
+Warning 1287 The syntax '@@log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use '@@log_bin_trust_function_creators' instead
SELECT @@global.log_bin_trust_routine_creators;
@@global.log_bin_trust_routine_creators
0
SET @@global.log_bin_trust_routine_creators = 0;
Warnings:
-Warning 1287 The syntax 'log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use 'log_bin_trust_function_creators' instead
+Warning 1287 The syntax '@@log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use '@@log_bin_trust_function_creators' instead
SELECT @@global.log_bin_trust_routine_creators;
@@global.log_bin_trust_routine_creators
0
SET @@global.log_bin_trust_routine_creators = 1;
Warnings:
-Warning 1287 The syntax 'log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use 'log_bin_trust_function_creators' instead
+Warning 1287 The syntax '@@log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use '@@log_bin_trust_function_creators' instead
SELECT @@global.log_bin_trust_routine_creators;
@@global.log_bin_trust_routine_creators
1
SET @@global.log_bin_trust_routine_creators = TRUE;
Warnings:
-Warning 1287 The syntax 'log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use 'log_bin_trust_function_creators' instead
+Warning 1287 The syntax '@@log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use '@@log_bin_trust_function_creators' instead
SELECT @@global.log_bin_trust_routine_creators;
@@global.log_bin_trust_routine_creators
1
SET @@global.log_bin_trust_routine_creators = FALSE;
Warnings:
-Warning 1287 The syntax 'log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use 'log_bin_trust_function_creators' instead
+Warning 1287 The syntax '@@log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use '@@log_bin_trust_function_creators' instead
SELECT @@global.log_bin_trust_routine_creators;
@@global.log_bin_trust_routine_creators
0
@@ -70,7 +70,7 @@ SET @@global.log_bin_trust_routine_creat
ERROR 42000: Variable 'log_bin_trust_routine_creators' can't be set to the value of 'OFFF'
SET @@global.log_bin_trust_routine_creators = OF;
Warnings:
-Warning 1287 The syntax 'log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use 'log_bin_trust_function_creators' instead
+Warning 1287 The syntax '@@log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use '@@log_bin_trust_function_creators' instead
SELECT @@global.log_bin_trust_routine_creators;
@@global.log_bin_trust_routine_creators
0
@@ -96,14 +96,14 @@ ERROR HY000: Variable 'log_bin_trust_rou
'#---------------------FN_DYNVARS_064_07----------------------#'
SET @@global.log_bin_trust_routine_creators = TRUE;
Warnings:
-Warning 1287 The syntax 'log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use 'log_bin_trust_function_creators' instead
+Warning 1287 The syntax '@@log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use '@@log_bin_trust_function_creators' instead
SELECT @@log_bin_trust_routine_creators = @@global.log_bin_trust_routine_creators;
@@log_bin_trust_routine_creators = @@global.log_bin_trust_routine_creators
1
'#---------------------FN_DYNVARS_064_08----------------------#'
SET @@global.log_bin_trust_routine_creators = TRUE;
Warnings:
-Warning 1287 The syntax 'log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use 'log_bin_trust_function_creators' instead
+Warning 1287 The syntax '@@log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use '@@log_bin_trust_function_creators' instead
SELECT @@log_bin_trust_routine_creators;
@@log_bin_trust_routine_creators
1
@@ -115,7 +115,7 @@ SELECT log_bin_trust_routine_creators =
ERROR 42S22: Unknown column 'log_bin_trust_routine_creators' in 'field list'
SET @@global.log_bin_trust_routine_creators = @start_global_value;
Warnings:
-Warning 1287 The syntax 'log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use 'log_bin_trust_function_creators' instead
+Warning 1287 The syntax '@@log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use '@@log_bin_trust_function_creators' instead
SELECT @@global.log_bin_trust_routine_creators;
@@global.log_bin_trust_routine_creators
1
=== modified file 'mysql-test/r/log_state.result'
--- a/mysql-test/r/log_state.result 2008-03-28 18:46:18 +0000
+++ b/mysql-test/r/log_state.result 2008-10-06 09:29:42 +0000
@@ -187,6 +187,8 @@ SELECT @@general_log, @@log;
@@general_log @@log
1 1
SET GLOBAL log = 0;
+Warnings:
+Warning 1287 The syntax '@@log' is deprecated and will be removed in MySQL 7.0. Please use '@@general_log' instead
SHOW VARIABLES LIKE 'general_log';
Variable_name Value
general_log OFF
@@ -216,6 +218,8 @@ SELECT @@slow_query_log, @@log_slow_quer
@@slow_query_log @@log_slow_queries
0 0
SET GLOBAL log_slow_queries = 0;
+Warnings:
+Warning 1287 The syntax '@@log_slow_queries' is deprecated and will be removed in MySQL 7.0. Please use '@@slow_query_log' instead
SHOW VARIABLES LIKE 'slow_query_log';
Variable_name Value
slow_query_log OFF
@@ -270,4 +274,28 @@ SET GLOBAL general_log_file = @general_l
SET GLOBAL slow_query_log_file = @slow_query_log_file_saved;
# -- End of Bug#32748.
+deprecated:
+SET GLOBAL log = 0;
+Warnings:
+Warning 1287 The syntax '@@log' is deprecated and will be removed in MySQL 7.0. Please use '@@general_log' instead
+SET GLOBAL log_slow_queries = 0;
+Warnings:
+Warning 1287 The syntax '@@log_slow_queries' is deprecated and will be removed in MySQL 7.0. Please use '@@slow_query_log' instead
+SET GLOBAL log = DEFAULT;
+Warnings:
+Warning 1287 The syntax '@@log' is deprecated and will be removed in MySQL 7.0. Please use '@@general_log' instead
+SET GLOBAL log_slow_queries = DEFAULT;
+Warnings:
+Warning 1287 The syntax '@@log_slow_queries' is deprecated and will be removed in MySQL 7.0. Please use '@@slow_query_log' instead
+not deprecated:
+SELECT @@global.general_log_file INTO @my_glf;
+SELECT @@global.slow_query_log_file INTO @my_sqlf;
+SET GLOBAL general_log = 0;
+SET GLOBAL slow_query_log = 0;
+SET GLOBAL general_log_file = 'WL4403_G.log';
+SET GLOBAL slow_query_log_file = 'WL4403_SQ.log';
+SET GLOBAL general_log_file = @my_glf;
+SET GLOBAL slow_query_log_file = @my_sqlf;
+SET GLOBAL general_log = DEFAULT;
+SET GLOBAL slow_query_log = DEFAULT;
End of 5.1 tests
=== modified file 'mysql-test/r/partition.result'
--- a/mysql-test/r/partition.result 2008-10-06 08:43:46 +0000
+++ b/mysql-test/r/partition.result 2008-10-07 15:19:32 +0000
@@ -1658,4 +1658,74 @@ c1
NULL
DROP TABLE t1;
SET SQL_MODE= @orig_sql_mode;
+create table t1 (s1 int) partition by hash(s1) partitions 2;
+create index i on t1 (s1);
+insert into t1 values (1);
+insert into t1 select s1 from t1;
+insert into t1 select s1 from t1;
+insert into t1 select s1 from t1 order by s1 desc;
+select * from t1;
+s1
+1
+1
+1
+1
+1
+1
+1
+1
+drop table t1;
+create table t1 (s1 int) partition by range(s1)
+(partition pa1 values less than (10),
+partition pa2 values less than MAXVALUE);
+create index i on t1 (s1);
+insert into t1 values (1);
+insert into t1 select s1 from t1;
+insert into t1 select s1 from t1;
+insert into t1 select s1 from t1 order by s1 desc;
+select * from t1;
+s1
+1
+1
+1
+1
+1
+1
+1
+1
+drop table t1;
+create table t1 (s1 int) partition by range(s1)
+(partition pa1 values less than (10),
+partition pa2 values less than MAXVALUE);
+create index i on t1 (s1);
+insert into t1 values (20);
+insert into t1 select s1 from t1;
+insert into t1 select s1 from t1;
+insert into t1 select s1 from t1 order by s1 desc;
+select * from t1;
+s1
+20
+20
+20
+20
+20
+20
+20
+20
+drop table t1;
+create table t1 (s1 int) partition by range(s1)
+(partition pa1 values less than (10),
+partition pa2 values less than MAXVALUE);
+create index i on t1 (s1);
+insert into t1 values (1), (2), (3), (4), (5), (6), (7), (8);
+insert into t1 select s1 from t1;
+insert into t1 select s1 from t1;
+insert into t1 select s1 from t1;
+insert into t1 select s1 from t1;
+insert into t1 select s1 from t1 order by s1 desc;
+insert into t1 select s1 from t1 where s1=3;
+select count(*) from t1;
+count(*)
+288
+drop table t1;
End of 5.1 tests
=== modified file 'mysql-test/r/trigger-trans.result'
--- a/mysql-test/r/trigger-trans.result 2008-08-11 18:02:03 +0000
+++ b/mysql-test/r/trigger-trans.result 2008-10-07 16:54:12 +0000
@@ -161,3 +161,32 @@ SELECT @a, @b;
1 1
DROP TABLE t2, t1;
End of 5.0 tests
+BUG#31612
+Trigger fired multiple times leads to gaps in auto_increment sequence
+create table t1 (a int, val char(1)) engine=InnoDB;
+create table t2 (b int auto_increment primary key,
+val char(1)) engine=InnoDB;
+create trigger t1_after_insert after
+insert on t1 for each row insert into t2 set val=NEW.val;
+insert into t1 values ( 123, 'a'), ( 123, 'b'), ( 123, 'c'),
+(123, 'd'), (123, 'e'), (123, 'f'), (123, 'g');
+insert into t1 values ( 654, 'a'), ( 654, 'b'), ( 654, 'c'),
+(654, 'd'), (654, 'e'), (654, 'f'), (654, 'g');
+select * from t2 order by b;
+b val
+1 a
+2 b
+3 c
+4 d
+5 e
+6 f
+7 g
+8 a
+9 b
+10 c
+11 d
+12 e
+13 f
+14 g
+drop trigger t1_after_insert;
+drop table t1,t2;
=== modified file 'mysql-test/r/tx_isolation_func.result'
--- a/mysql-test/r/tx_isolation_func.result 2008-04-10 13:14:28 +0000
+++ b/mysql-test/r/tx_isolation_func.result 2008-10-03 12:24:19 +0000
@@ -21,8 +21,10 @@ INSERT INTO t1 VALUES(24, 24);
'#----------------------------FN_DYNVARS_184_01--------------------------------------#'
** Connection con0 **
SET SESSION tx_isolation = 'READ-UNCOMMITTED';
+set binlog_format=mixed;
** Connection con1 **
SET SESSION tx_isolation = 'READ-UNCOMMITTED';
+set binlog_format=mixed;
** Connection con0 **
START TRANSACTION;
SELECT * FROM t1 WHERE a IN (2,4,6,8) FOR UPDATE;
=== modified file 'mysql-test/r/warnings.result'
--- a/mysql-test/r/warnings.result 2008-05-13 12:01:02 +0000
+++ b/mysql-test/r/warnings.result 2008-10-06 14:22:07 +0000
@@ -167,7 +167,7 @@ max_error_count 10
drop table t1;
set table_type=MYISAM;
Warnings:
-Warning 1287 The syntax 'table_type' is deprecated and will be removed in MySQL 5.2. Please use 'storage_engine' instead
+Warning 1287 The syntax '@@table_type' is deprecated and will be removed in MySQL 5.2. Please use '@@storage_engine' instead
create table t1 (a int);
insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
update t1 set a='abc';
=== modified file 'mysql-test/suite/binlog/r/binlog_multi_engine.result'
--- a/mysql-test/suite/binlog/r/binlog_multi_engine.result 2008-03-28 12:16:41 +0000
+++ b/mysql-test/suite/binlog/r/binlog_multi_engine.result 2008-10-02 09:02:38 +0000
@@ -43,8 +43,6 @@ INSERT INTO t1n VALUES (1,1), (1,2), (2,
UPDATE t1m, t1b SET m = 2, b = 3 WHERE n = c;
UPDATE t1m, t1n SET m = 2, e = 3 WHERE n = f;
ERROR HY000: Binary logging not possible. Message: Statement cannot be written atomically since more than one engine involved and at least one engine is self-logging
-UPDATE t1n, t1b SET e = 2, b = 3 WHERE f = c;
-ERROR HY000: Binary logging not possible. Message: Statement cannot be written atomically since more than one engine involved and at least one engine is self-logging
TRUNCATE t1m;
TRUNCATE t1b;
TRUNCATE t1n;
@@ -68,20 +66,21 @@ RESET MASTER;
SET SESSION BINLOG_FORMAT=ROW;
INSERT INTO t1m VALUES (1,1), (1,2), (2,1), (2,2);
INSERT INTO t1b VALUES (1,1), (1,2), (2,1), (2,2);
-ERROR HY000: Binary logging not possible. Message: Row-based format required for this statement, but not allowed by this combination of engines
INSERT INTO t1n VALUES (1,1), (1,2), (2,1), (2,2);
-UPDATE t1m, t1b SET m = 2, b = 3 WHERE n = c;
-ERROR HY000: Binary logging not possible. Message: Row-based format required for this statement, but not allowed by this combination of engines
UPDATE t1m, t1n SET m = 2, e = 3 WHERE n = f;
ERROR HY000: Binary logging not possible. Message: Statement cannot be written atomically since more than one engine involved and at least one engine is self-logging
UPDATE t1n, t1b SET e = 2, b = 3 WHERE f = c;
-ERROR HY000: Binary logging not possible. Message: Row-based format required for this statement, but not allowed by this combination of engines
+ERROR HY000: Binary logging not possible. Message: Statement cannot be written atomically since more than one engine involved and at least one engine is self-logging
show binlog events from <binlog_start>;
Log_name Pos Event_type Server_id End_log_pos Info
master-bin.000001 # Query # # use `test`; BEGIN
master-bin.000001 # Table_map # # table_id: # (test.t1m)
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
master-bin.000001 # Query # # use `test`; COMMIT
+master-bin.000001 # Query # # use `test`; BEGIN
+master-bin.000001 # Table_map # # table_id: # (test.t1b)
+master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
+master-bin.000001 # Query # # use `test`; COMMIT
master-bin.000001 # Query # # BEGIN
master-bin.000001 # Table_map # # table_id: # (test.t1n)
master-bin.000001 # Table_map # # table_id: # (mysql.ndb_apply_status)
=== modified file 'mysql-test/suite/binlog/r/binlog_row_binlog.result'
--- a/mysql-test/suite/binlog/r/binlog_row_binlog.result 2008-07-29 15:36:13 +0000
+++ b/mysql-test/suite/binlog/r/binlog_row_binlog.result 2008-10-02 05:56:07 +0000
@@ -1090,6 +1090,53 @@ master-bin.000001 295 Table_map 1 337 ta
master-bin.000001 337 Write_rows 1 383 table_id: # flags: STMT_END_F
master-bin.000001 383 Query 1 452 use `test`; COMMIT
master-bin.000001 452 Query 1 528 use `test`; drop table t1
+reset master;
+CREATE DATABASE bug39182 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
+USE bug39182;
+CREATE TABLE t1 (a VARCHAR(255) COLLATE utf8_unicode_ci)
+DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE s1 VARCHAR(255);
+SET s1= "test";
+CREATE TEMPORARY TABLE tmp1
+SELECT * FROM t1 WHERE a LIKE CONCAT("%", s1, "%");
+SELECT
+COLLATION(NAME_CONST('s1', _utf8'test')) c1,
+COLLATION(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) c2,
+COLLATION(s1) c3,
+COERCIBILITY(NAME_CONST('s1', _utf8'test')) d1,
+COERCIBILITY(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) d2,
+COERCIBILITY(s1) d3;
+DROP TEMPORARY TABLE tmp1;
+END//
+CALL p1();
+c1 c2 c3 d1 d2 d3
+utf8_general_ci utf8_unicode_ci utf8_unicode_ci 2 2 2
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # CREATE DATABASE bug39182 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
+master-bin.000001 # Query # # use `bug39182`; CREATE TABLE t1 (a VARCHAR(255) COLLATE utf8_unicode_ci)
+DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
+master-bin.000001 # Query # # use `bug39182`; CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
+BEGIN
+DECLARE s1 VARCHAR(255);
+SET s1= "test";
+CREATE TEMPORARY TABLE tmp1
+SELECT * FROM t1 WHERE a LIKE CONCAT("%", s1, "%");
+SELECT
+COLLATION(NAME_CONST('s1', _utf8'test')) c1,
+COLLATION(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) c2,
+COLLATION(s1) c3,
+COERCIBILITY(NAME_CONST('s1', _utf8'test')) d1,
+COERCIBILITY(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) d2,
+COERCIBILITY(s1) d3;
+DROP TEMPORARY TABLE tmp1;
+END
+DROP PROCEDURE p1;
+DROP TABLE t1;
+DROP DATABASE bug39182;
+USE test;
End of 5.0 tests
reset master;
create table t1 (id tinyint auto_increment primary key);
=== modified file 'mysql-test/suite/binlog/r/binlog_stm_binlog.result'
--- a/mysql-test/suite/binlog/r/binlog_stm_binlog.result 2008-07-23 16:56:39 +0000
+++ b/mysql-test/suite/binlog/r/binlog_stm_binlog.result 2008-10-02 05:56:07 +0000
@@ -594,6 +594,56 @@ master-bin.000001 4 Format_desc 1 106 Se
master-bin.000001 106 Query 1 227 use `test`; create table t1 (a bigint unsigned, b bigint(20) unsigned)
master-bin.000001 227 Query 1 351 use `test`; insert into t1 values (9999999999999999,14632475938453979136)
master-bin.000001 351 Query 1 427 use `test`; drop table t1
+reset master;
+CREATE DATABASE bug39182 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
+USE bug39182;
+CREATE TABLE t1 (a VARCHAR(255) COLLATE utf8_unicode_ci)
+DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE s1 VARCHAR(255);
+SET s1= "test";
+CREATE TEMPORARY TABLE tmp1
+SELECT * FROM t1 WHERE a LIKE CONCAT("%", s1, "%");
+SELECT
+COLLATION(NAME_CONST('s1', _utf8'test')) c1,
+COLLATION(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) c2,
+COLLATION(s1) c3,
+COERCIBILITY(NAME_CONST('s1', _utf8'test')) d1,
+COERCIBILITY(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) d2,
+COERCIBILITY(s1) d3;
+DROP TEMPORARY TABLE tmp1;
+END//
+CALL p1();
+c1 c2 c3 d1 d2 d3
+utf8_general_ci utf8_unicode_ci utf8_unicode_ci 2 2 2
+show binlog events from <binlog_start>;
+Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # CREATE DATABASE bug39182 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci
+master-bin.000001 # Query # # use `bug39182`; CREATE TABLE t1 (a VARCHAR(255) COLLATE utf8_unicode_ci)
+DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
+master-bin.000001 # Query # # use `bug39182`; CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
+BEGIN
+DECLARE s1 VARCHAR(255);
+SET s1= "test";
+CREATE TEMPORARY TABLE tmp1
+SELECT * FROM t1 WHERE a LIKE CONCAT("%", s1, "%");
+SELECT
+COLLATION(NAME_CONST('s1', _utf8'test')) c1,
+COLLATION(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) c2,
+COLLATION(s1) c3,
+COERCIBILITY(NAME_CONST('s1', _utf8'test')) d1,
+COERCIBILITY(NAME_CONST('s1', _utf8'test' COLLATE utf8_unicode_ci)) d2,
+COERCIBILITY(s1) d3;
+DROP TEMPORARY TABLE tmp1;
+END
+master-bin.000001 # Query # # use `bug39182`; CREATE TEMPORARY TABLE tmp1
+SELECT * FROM t1 WHERE a LIKE CONCAT("%", NAME_CONST('s1',_utf8'test' COLLATE 'utf8_unicode_ci'), "%")
+master-bin.000001 # Query # # use `bug39182`; DROP TEMPORARY TABLE tmp1
+DROP PROCEDURE p1;
+DROP TABLE t1;
+DROP DATABASE bug39182;
+USE test;
End of 5.0 tests
reset master;
create table t1 (id tinyint auto_increment primary key);
=== modified file 'mysql-test/suite/binlog/r/binlog_stm_blackhole.result'
--- a/mysql-test/suite/binlog/r/binlog_stm_blackhole.result 2008-03-29 22:54:08 +0000
+++ b/mysql-test/suite/binlog/r/binlog_stm_blackhole.result 2008-10-02 09:02:38 +0000
@@ -141,7 +141,6 @@ master-bin.000001 # Query # # use `test`
drop table t1,t2,t3;
CREATE TABLE t1(a INT) ENGINE=BLACKHOLE;
INSERT DELAYED INTO t1 VALUES(1);
-ERROR HY000: Binary logging not possible. Message: Row-based format required for this statement, but not allowed by this combination of engines
DROP TABLE t1;
CREATE TABLE t1(a INT, b INT) ENGINE=BLACKHOLE;
DELETE FROM t1 WHERE a=10;
=== modified file 'mysql-test/suite/binlog/t/binlog_multi_engine.test'
--- a/mysql-test/suite/binlog/t/binlog_multi_engine.test 2008-02-28 21:50:15 +0000
+++ b/mysql-test/suite/binlog/t/binlog_multi_engine.test 2008-10-02 09:02:38 +0000
@@ -69,9 +69,6 @@ UPDATE t1m, t1n SET m = 2, e = 3 WHERE n
#UPDATE t1m, t1n SET m = 2, e = 3 WHERE n = f;
-error ER_BINLOG_LOGGING_IMPOSSIBLE;
-UPDATE t1n, t1b SET e = 2, b = 3 WHERE f = c;
-
TRUNCATE t1m;
TRUNCATE t1b;
TRUNCATE t1n;
@@ -83,13 +80,11 @@ RESET MASTER;
SET SESSION BINLOG_FORMAT=ROW;
INSERT INTO t1m VALUES (1,1), (1,2), (2,1), (2,2);
-error ER_BINLOG_LOGGING_IMPOSSIBLE;
+
INSERT INTO t1b VALUES (1,1), (1,2), (2,1), (2,2);
INSERT INTO t1n VALUES (1,1), (1,2), (2,1), (2,2);
error ER_BINLOG_LOGGING_IMPOSSIBLE;
-UPDATE t1m, t1b SET m = 2, b = 3 WHERE n = c;
-error ER_BINLOG_LOGGING_IMPOSSIBLE;
UPDATE t1m, t1n SET m = 2, e = 3 WHERE n = f;
# Not possible to test this since NDB writes its own binlog, which
=== modified file 'mysql-test/suite/ndb/t/disabled.def'
--- a/mysql-test/suite/ndb/t/disabled.def 2008-07-25 10:32:05 +0000
+++ b/mysql-test/suite/ndb/t/disabled.def 2008-10-03 10:10:09 +0000
@@ -12,6 +12,5 @@
partition_03ndb : BUG#16385 2006-03-24 mikael Partitions: crash when updating a range partitioned NDB table
ndb_partition_error2 : HF is not sure if the test can work as internded on all the platforms
-ndb_index_ordered : Bug#38370 The test ndb.ndb_index_ordered fails with the community features on
# the below testcase have been reworked to avoid the bug, test contains comment, keep bug open
=== added file 'mysql-test/suite/parts/inc/partition_auto_increment.inc'
--- a/mysql-test/suite/parts/inc/partition_auto_increment.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/inc/partition_auto_increment.inc 2008-09-08 13:30:01 +0000
@@ -0,0 +1,600 @@
+# 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 0, ER_DUP_KEY, ER_DUP_ENTRY
+INSERT INTO t1 VALUES (5), (16);
+if (!$mysql_errno)
+{
+ echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
+}
+INSERT INTO t1 VALUES (17);
+INSERT INTO t1 VALUES (19), (NULL);
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (NULL), (10), (NULL);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive) mysql_errno: $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 (
+ 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;
+if (!$skip_delete)
+{
+DELETE FROM t1;
+}
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+SELECT * FROM t1 ORDER BY c1;
+if (!$skip_truncate)
+{
+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 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (1, 1), (99, 99);
+if (!$mysql_errno)
+{
+ echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
+}
+INSERT INTO t1 VALUES (1, NULL);
+let $old_sql_mode = `select @@session.sql_mode`;
+SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (1, 0);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+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;
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (1, 0);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+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 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (5), (16);
+if (!$mysql_errno)
+{
+ echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
+}
+INSERT INTO t1 VALUES (17), (19), (NULL);
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (NULL), (10), (NULL);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (NULL), (9);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (59), (55);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive) mysql_errno: $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;
+
+-- 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';
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (10);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+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);
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (15);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+INSERT INTO t1 VALUES (NULL);
+if (!$skip_delete)
+{
+DELETE FROM t1;
+}
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+SELECT * FROM t1 ORDER BY c1;
+if (!$skip_truncate)
+{
+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
+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);
+connect(con1, localhost, root,,);
+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
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 (c1) VALUES (16);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+-- 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
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 (c1) VALUES (16);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+-- 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
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 (c1) VALUES (16);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+-- 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;
+
+if (!$only_ai_pk)
+{
+-- 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);
+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);
+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, ER_DUP_ENTRY
+INSERT INTO t1 VALUES (1, 1);
+if (!$mysql_errno)
+{
+ echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
+}
+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, ER_DUP_ENTRY
+INSERT INTO t1 VALUES (2, 2);
+if (!$mysql_errno)
+{
+ echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
+}
+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, ER_DUP_ENTRY
+INSERT INTO t1 VALUES (1, 1);
+if (!$mysql_errno)
+{
+ echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
+}
+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
+INSERT INTO t1 VALUES (2, 2);
+if (!$mysql_errno)
+{
+echo # ERROR (only OK if Blackhole/NDB) should give ER_DUP_KEY or ER_DUP_ENTRY;
+}
+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;
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 (c1) VALUES (4);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+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;
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 (c1) VALUES (0);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+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;
+
=== added file 'mysql-test/suite/parts/r/partition_auto_increment_archive.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_archive.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_archive.result 2008-09-08 13:30:01 +0000
@@ -0,0 +1,747 @@
+DROP TABLE IF EXISTS t1;
+# test without partitioning for reference
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Archive';
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+1
+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';
+AUTO_INCREMENT
+6
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+6
+INSERT INTO t1 VALUES (0);
+INSERT INTO t1 VALUES (5), (16);
+INSERT INTO t1 VALUES (17);
+INSERT INTO t1 VALUES (19), (NULL);
+INSERT INTO t1 VALUES (NULL), (10), (NULL);
+# ERROR (only OK if Archive) mysql_errno: 1022
+INSERT INTO t1 VALUES (NULL);
+SET INSERT_ID = 30;
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+17
+19
+20
+21
+22
+30
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Archive';
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (4);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (NULL);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c1;
+c1
+4
+5
+6
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=8 DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c1;
+c1
+4
+5
+6
+7
+DROP TABLE t1;
+# Simple test with NULL
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Archive'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1;
+c1
+1
+DROP TABLE t1;
+# Test with sql_mode and first insert as 0
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE='Archive'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (1, 1), (99, 99);
+INSERT INTO t1 VALUES (1, NULL);
+SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+INSERT INTO t1 VALUES (1, 0);
+# ERROR (only OK if Archive) mysql_errno: 1022
+SELECT * FROM t1 ORDER BY c1, c2;
+c1 c2
+1 1
+1 2
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE='Archive'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+# ERROR (only OK if Archive) mysql_errno: 1022
+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;
+c1 c2
+1 1
+1 2
+1 8
+2 3
+4 7
+SET @@session.sql_mode = '';
+DROP TABLE t1;
+# Simple test with NULL, 0 and explicit values both incr. and desc.
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Archive'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (2), (4), (NULL);
+INSERT INTO t1 VALUES (0);
+INSERT INTO t1 VALUES (5), (16);
+INSERT INTO t1 VALUES (17), (19), (NULL);
+INSERT INTO t1 VALUES (NULL), (10), (NULL);
+# ERROR (only OK if Archive) mysql_errno: 1022
+INSERT INTO t1 VALUES (NULL), (9);
+# ERROR (only OK if Archive) mysql_errno: 1022
+INSERT INTO t1 VALUES (59), (55);
+# ERROR (only OK if Archive) mysql_errno: 1022
+INSERT INTO t1 VALUES (NULL), (90);
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+17
+19
+20
+21
+22
+59
+60
+90
+91
+DROP TABLE t1;
+# Test with auto_increment_increment and auto_increment_offset.
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Archive'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+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);
+INSERT INTO t1 VALUES (33 + 1);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (38 + 2);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (43 + 3);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (48 + 4);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (53 + 5);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (63 + 6);
+INSERT INTO t1 VALUES (NULL);
+SET @@session.auto_increment_increment = 1;
+SET @@session.auto_increment_offset = 1;
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+5
+15
+25
+33
+34
+38
+40
+43
+46
+48
+52
+53
+58
+63
+69
+73
+DROP TABLE t1;
+# Test reported auto_increment value
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Archive'
+PARTITION BY HASH (c1)
+PARTITIONS 2;
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+1
+INSERT INTO t1 VALUES (2);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+3
+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';
+AUTO_INCREMENT
+6
+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';
+AUTO_INCREMENT
+22
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+22
+INSERT INTO t1 VALUES (10);
+# ERROR (only OK if Archive) mysql_errno: 1022
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+17
+19
+20
+21
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+23
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (15);
+# ERROR (only OK if Archive) mysql_errno: 1022
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+17
+19
+20
+21
+22
+23
+24
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=27 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+17
+19
+20
+21
+22
+23
+24
+25
+26
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=28 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+17
+19
+20
+21
+22
+23
+24
+25
+26
+27
+DROP TABLE t1;
+# Test with two threads
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Archive'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (10);
+# 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);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# ERROR (only OK if Archive) mysql_errno: 1022
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with two threads + start transaction NO PARTITIONING
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Archive';
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (10);
+# 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);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# ERROR (only OK if Archive) mysql_errno: 1022
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+19
+21
+22
+23
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+19
+21
+22
+23
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+19
+21
+22
+23
+24
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with two threads + start transaction
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Archive'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (NULL), (10);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19);
+INSERT INTO t1 (c1) VALUES (21);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# ERROR (only OK if Archive) mysql_errno: 1022
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+19
+21
+22
+23
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+19
+21
+22
+23
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+19
+21
+22
+23
+24
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with another column before
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE = 'Archive'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+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);
+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;
+c1 c2
+1 1
+1 2
+2 3
+2 13
+2 14
+2 22
+2 23
+3 11
+3 12
+DROP TABLE t1;
+# Test with auto_increment on secondary column in multi-column-index
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1,c2))
+ENGINE = 'Archive'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key
+# Test AUTO_INCREMENT in CREATE
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Archive'
+AUTO_INCREMENT = 15
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (4);
+# ERROR (only OK if Archive) mysql_errno: 1022
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (0);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=16 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+15
+16
+# Test sql_mode 'NO_AUTO_VALUE_ON_ZERO'
+SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+INSERT INTO t1 (c1) VALUES (300);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (0);
+# ERROR (only OK if Archive) mysql_errno: 1022
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=302 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+15
+16
+300
+301
+SET @@session.sql_mode = '';
+DROP TABLE t1;
+# Test SET INSERT_ID
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Archive'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1;
+c1
+1
+SET INSERT_ID = 23;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=24 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+23
+DROP TABLE t1;
+# Testing with FLUSH TABLE
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Archive'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 VALUES (4);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 VALUES (NULL);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+4
+5
+DROP TABLE t1;
=== added file 'mysql-test/suite/parts/r/partition_auto_increment_blackhole.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_blackhole.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_blackhole.result 2008-09-08 13:30:01 +0000
@@ -0,0 +1,570 @@
+DROP TABLE IF EXISTS t1;
+# test without partitioning for reference
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Blackhole';
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+1
+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';
+AUTO_INCREMENT
+1
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+1
+INSERT INTO t1 VALUES (0);
+INSERT INTO t1 VALUES (5), (16);
+# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
+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);
+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;
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Blackhole';
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (4);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (NULL);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
+DELETE FROM t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c1;
+c1
+TRUNCATE TABLE t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c1;
+c1
+DROP TABLE t1;
+# Simple test with NULL
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Blackhole'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1;
+c1
+DROP TABLE t1;
+# Test with sql_mode and first insert as 0
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE='Blackhole'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (1, 1), (99, 99);
+# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
+INSERT INTO t1 VALUES (1, NULL);
+SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+INSERT INTO t1 VALUES (1, 0);
+SELECT * FROM t1 ORDER BY c1, c2;
+c1 c2
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE='Blackhole'
+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;
+c1 c2
+SET @@session.sql_mode = '';
+DROP TABLE t1;
+# Simple test with NULL, 0 and explicit values both incr. and desc.
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Blackhole'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (2), (4), (NULL);
+INSERT INTO t1 VALUES (0);
+INSERT INTO t1 VALUES (5), (16);
+# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
+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);
+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;
+# Test with auto_increment_increment and auto_increment_offset.
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Blackhole'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+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);
+INSERT INTO t1 VALUES (33 + 1);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (38 + 2);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (43 + 3);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (48 + 4);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (53 + 5);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (63 + 6);
+INSERT INTO t1 VALUES (NULL);
+SET @@session.auto_increment_increment = 1;
+SET @@session.auto_increment_offset = 1;
+SELECT * FROM t1 ORDER BY c1;
+c1
+DROP TABLE t1;
+# Test reported auto_increment value
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Blackhole'
+PARTITION BY HASH (c1)
+PARTITIONS 2;
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+1
+INSERT INTO t1 VALUES (2);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+3
+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';
+AUTO_INCREMENT
+6
+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';
+AUTO_INCREMENT
+22
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+22
+INSERT INTO t1 VALUES (10);
+SELECT * FROM t1 ORDER BY c1;
+c1
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+23
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (15);
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+INSERT INTO t1 VALUES (NULL);
+DELETE FROM t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE AUTO_INCREMENT=27 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+TRUNCATE TABLE t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+DROP TABLE t1;
+# Test with two threads
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Blackhole'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (10);
+# 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);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+DROP TABLE t1;
+# Test with two threads + start transaction NO PARTITIONING
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Blackhole';
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (10);
+# 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);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+DROP TABLE t1;
+# Test with two threads + start transaction
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Blackhole'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (NULL), (10);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19);
+INSERT INTO t1 (c1) VALUES (21);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+DROP TABLE t1;
+# Test with another column after
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+c2 INT,
+PRIMARY KEY (c1,c2))
+ENGINE = 'Blackhole'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+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);
+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;
+c1 c2
+DROP TABLE t1;
+# Test with another column before
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE = 'Blackhole'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+INSERT INTO t1 VALUES (1, 1);
+# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (2, NULL), (3, 11), (3, NULL), (2, 0);
+INSERT INTO t1 VALUES (2, NULL);
+INSERT INTO t1 VALUES (2, 2);
+# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
+INSERT INTO t1 VALUES (2, 22);
+INSERT INTO t1 VALUES (2, NULL);
+SELECT * FROM t1 ORDER BY c1,c2;
+c1 c2
+DROP TABLE t1;
+# Test with auto_increment on secondary column in multi-column-index
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1,c2))
+ENGINE = 'Blackhole'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+INSERT INTO t1 VALUES (1, 1);
+# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
+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);
+INSERT INTO t1 VALUES (2, 2);
+# ERROR (only OK if Blackhole/NDB) should give ER_DUP_KEY or ER_DUP_ENTRY
+INSERT INTO t1 VALUES (2, 22), (2, NULL);
+SELECT * FROM t1 ORDER BY c1,c2;
+c1 c2
+DROP TABLE t1;
+# Test AUTO_INCREMENT in CREATE
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Blackhole'
+AUTO_INCREMENT = 15
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (4);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (0);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+# Test sql_mode 'NO_AUTO_VALUE_ON_ZERO'
+SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+INSERT INTO t1 (c1) VALUES (300);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (0);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE AUTO_INCREMENT=302 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+SET @@session.sql_mode = '';
+DROP TABLE t1;
+# Test SET INSERT_ID
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Blackhole'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1;
+c1
+SET INSERT_ID = 23;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE AUTO_INCREMENT=24 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+DROP TABLE t1;
+# Testing with FLUSH TABLE
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Blackhole'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 VALUES (4);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 VALUES (NULL);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+DROP TABLE t1;
=== added file 'mysql-test/suite/parts/r/partition_auto_increment_innodb.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_innodb.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_innodb.result 2008-09-08 13:30:01 +0000
@@ -0,0 +1,747 @@
+DROP TABLE IF EXISTS t1;
+# test without partitioning for reference
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='InnoDB';
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+1
+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';
+AUTO_INCREMENT
+6
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+6
+INSERT INTO t1 VALUES (0);
+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);
+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
+4
+5
+6
+10
+20
+22
+23
+25
+30
+31
+32
+50
+51
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='InnoDB';
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (4);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (NULL);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
+DELETE FROM t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c1;
+c1
+6
+TRUNCATE TABLE t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+DROP TABLE t1;
+# Simple test with NULL
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='InnoDB'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1;
+c1
+1
+DROP TABLE t1;
+# Test with sql_mode and first insert as 0
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE='InnoDB'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (1, 1), (99, 99);
+INSERT INTO t1 VALUES (1, NULL);
+SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+INSERT INTO t1 VALUES (1, 0);
+SELECT * FROM t1 ORDER BY c1, c2;
+c1 c2
+1 0
+1 1
+1 2
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE='InnoDB'
+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;
+c1 c2
+1 0
+1 1
+1 2
+1 8
+2 3
+4 7
+SET @@session.sql_mode = '';
+DROP TABLE t1;
+# Simple test with NULL, 0 and explicit values both incr. and desc.
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='InnoDB'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (2), (4), (NULL);
+INSERT INTO t1 VALUES (0);
+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);
+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
+4
+5
+6
+9
+10
+20
+21
+22
+23
+55
+59
+60
+90
+91
+150
+151
+152
+153
+DROP TABLE t1;
+# Test with auto_increment_increment and auto_increment_offset.
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='InnoDB'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+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);
+INSERT INTO t1 VALUES (33 + 1);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (38 + 2);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (43 + 3);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (48 + 4);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (53 + 5);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (63 + 6);
+INSERT INTO t1 VALUES (NULL);
+SET @@session.auto_increment_increment = 1;
+SET @@session.auto_increment_offset = 1;
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+5
+15
+25
+33
+34
+38
+40
+43
+46
+48
+52
+53
+58
+63
+69
+73
+DROP TABLE t1;
+# Test reported auto_increment value
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='InnoDB'
+PARTITION BY HASH (c1)
+PARTITIONS 2;
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+1
+INSERT INTO t1 VALUES (2);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+3
+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';
+AUTO_INCREMENT
+6
+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';
+AUTO_INCREMENT
+22
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+22
+INSERT INTO t1 VALUES (10);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+10
+17
+19
+20
+21
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+23
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (15);
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+10
+15
+17
+19
+20
+21
+22
+23
+24
+INSERT INTO t1 VALUES (NULL);
+DELETE FROM t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+26
+TRUNCATE TABLE t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+DROP TABLE t1;
+# Test with two threads
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'InnoDB'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (10);
+# 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);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with two threads + start transaction NO PARTITIONING
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'InnoDB';
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (10);
+# 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);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+5
+10
+22
+23
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+5
+10
+22
+23
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with two threads + start transaction
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'InnoDB'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (NULL), (10);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19);
+INSERT INTO t1 (c1) VALUES (21);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+5
+10
+22
+23
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+5
+10
+22
+23
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with another column after
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+c2 INT,
+PRIMARY KEY (c1,c2))
+ENGINE = 'InnoDB'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+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);
+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;
+c1 c2
+1 0
+1 1
+2 0
+2 1
+2 2
+2 22
+3 2
+4 3
+5 3
+6 2
+7 2
+DROP TABLE t1;
+# Test with another column before
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE = 'InnoDB'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+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);
+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;
+c1 c2
+1 1
+1 2
+2 3
+2 13
+2 14
+2 22
+2 23
+3 11
+3 12
+DROP TABLE t1;
+# Test with auto_increment on secondary column in multi-column-index
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1,c2))
+ENGINE = 'InnoDB'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key
+# Test AUTO_INCREMENT in CREATE
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'InnoDB'
+AUTO_INCREMENT = 15
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (4);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (0);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+4
+15
+16
+# Test sql_mode 'NO_AUTO_VALUE_ON_ZERO'
+SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+INSERT INTO t1 (c1) VALUES (300);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (0);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=302 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+0
+4
+15
+16
+300
+301
+SET @@session.sql_mode = '';
+DROP TABLE t1;
+# Test SET INSERT_ID
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'InnoDB'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1;
+c1
+1
+SET INSERT_ID = 23;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+23
+DROP TABLE t1;
+# Testing with FLUSH TABLE
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='InnoDB'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 VALUES (4);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 VALUES (NULL);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+4
+5
+DROP TABLE t1;
=== added file 'mysql-test/suite/parts/r/partition_auto_increment_memory.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_memory.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_memory.result 2008-09-08 13:30:01 +0000
@@ -0,0 +1,775 @@
+DROP TABLE IF EXISTS t1;
+# test without partitioning for reference
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Memory';
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY DEFAULT CHARSET=latin1
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+1
+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';
+AUTO_INCREMENT
+6
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+6
+INSERT INTO t1 VALUES (0);
+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);
+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
+4
+5
+6
+10
+20
+21
+22
+23
+30
+50
+51
+52
+53
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Memory';
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY DEFAULT CHARSET=latin1
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (4);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (NULL);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
+DELETE FROM t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c1;
+c1
+6
+TRUNCATE TABLE t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+DROP TABLE t1;
+# Simple test with NULL
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Memory'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1;
+c1
+1
+DROP TABLE t1;
+# Test with sql_mode and first insert as 0
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE='Memory'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (1, 1), (99, 99);
+INSERT INTO t1 VALUES (1, NULL);
+SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+INSERT INTO t1 VALUES (1, 0);
+SELECT * FROM t1 ORDER BY c1, c2;
+c1 c2
+1 0
+1 1
+1 2
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE='Memory'
+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;
+c1 c2
+1 0
+1 1
+1 2
+1 8
+2 3
+4 7
+SET @@session.sql_mode = '';
+DROP TABLE t1;
+# Simple test with NULL, 0 and explicit values both incr. and desc.
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Memory'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (2), (4), (NULL);
+INSERT INTO t1 VALUES (0);
+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);
+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
+4
+5
+6
+9
+10
+20
+21
+22
+23
+55
+59
+60
+90
+91
+150
+151
+152
+153
+DROP TABLE t1;
+# Test with auto_increment_increment and auto_increment_offset.
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Memory'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+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);
+INSERT INTO t1 VALUES (33 + 1);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (38 + 2);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (43 + 3);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (48 + 4);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (53 + 5);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (63 + 6);
+INSERT INTO t1 VALUES (NULL);
+SET @@session.auto_increment_increment = 1;
+SET @@session.auto_increment_offset = 1;
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+5
+15
+25
+33
+34
+38
+40
+43
+46
+48
+52
+53
+58
+63
+69
+73
+DROP TABLE t1;
+# Test reported auto_increment value
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Memory'
+PARTITION BY HASH (c1)
+PARTITIONS 2;
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+1
+INSERT INTO t1 VALUES (2);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+3
+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';
+AUTO_INCREMENT
+6
+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';
+AUTO_INCREMENT
+22
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+22
+INSERT INTO t1 VALUES (10);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+10
+17
+19
+20
+21
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+23
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (15);
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+10
+15
+17
+19
+20
+21
+22
+23
+24
+INSERT INTO t1 VALUES (NULL);
+DELETE FROM t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=27 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+26
+TRUNCATE TABLE t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=28 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+27
+DROP TABLE t1;
+# Test with two threads
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Memory'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (10);
+# 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);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with two threads + start transaction NO PARTITIONING
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Memory';
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (10);
+# 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);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with two threads + start transaction
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Memory'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (NULL), (10);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19);
+INSERT INTO t1 (c1) VALUES (21);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with another column after
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+c2 INT,
+PRIMARY KEY (c1,c2))
+ENGINE = 'Memory'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+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);
+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;
+c1 c2
+1 0
+1 1
+2 0
+2 1
+2 2
+2 22
+3 2
+4 3
+5 3
+6 2
+7 2
+DROP TABLE t1;
+# Test with another column before
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE = 'Memory'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+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);
+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;
+c1 c2
+1 1
+1 2
+2 3
+2 13
+2 14
+2 22
+2 23
+3 11
+3 12
+DROP TABLE t1;
+# Test with auto_increment on secondary column in multi-column-index
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1,c2))
+ENGINE = 'Memory'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key
+# Test AUTO_INCREMENT in CREATE
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Memory'
+AUTO_INCREMENT = 15
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (4);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (0);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=16 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+4
+15
+16
+# Test sql_mode 'NO_AUTO_VALUE_ON_ZERO'
+SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+INSERT INTO t1 (c1) VALUES (300);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (0);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=302 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+0
+4
+15
+16
+300
+301
+SET @@session.sql_mode = '';
+DROP TABLE t1;
+# Test SET INSERT_ID
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Memory'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1;
+c1
+1
+SET INSERT_ID = 23;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=24 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+23
+DROP TABLE t1;
+# Testing with FLUSH TABLE
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Memory'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 VALUES (4);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 VALUES (NULL);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+4
+5
+DROP TABLE t1;
=== added file 'mysql-test/suite/parts/r/partition_auto_increment_myisam.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_myisam.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_myisam.result 2008-09-08 13:30:01 +0000
@@ -0,0 +1,794 @@
+DROP TABLE IF EXISTS t1;
+# test without partitioning for reference
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='MyISAM';
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+1
+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';
+AUTO_INCREMENT
+6
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+6
+INSERT INTO t1 VALUES (0);
+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);
+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
+4
+5
+6
+10
+20
+21
+22
+23
+30
+50
+51
+52
+53
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='MyISAM';
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (4);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (NULL);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
+DELETE FROM t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c1;
+c1
+6
+TRUNCATE TABLE t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+DROP TABLE t1;
+# Simple test with NULL
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='MyISAM'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1;
+c1
+1
+DROP TABLE t1;
+# Test with sql_mode and first insert as 0
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE='MyISAM'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (1, 1), (99, 99);
+INSERT INTO t1 VALUES (1, NULL);
+SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+INSERT INTO t1 VALUES (1, 0);
+SELECT * FROM t1 ORDER BY c1, c2;
+c1 c2
+1 0
+1 1
+1 2
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE='MyISAM'
+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;
+c1 c2
+1 0
+1 1
+1 2
+1 8
+2 3
+4 7
+SET @@session.sql_mode = '';
+DROP TABLE t1;
+# Simple test with NULL, 0 and explicit values both incr. and desc.
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='MyISAM'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (2), (4), (NULL);
+INSERT INTO t1 VALUES (0);
+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);
+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
+4
+5
+6
+9
+10
+20
+21
+22
+23
+55
+59
+60
+90
+91
+150
+151
+152
+153
+DROP TABLE t1;
+# Test with auto_increment_increment and auto_increment_offset.
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='MyISAM'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+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);
+INSERT INTO t1 VALUES (33 + 1);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (38 + 2);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (43 + 3);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (48 + 4);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (53 + 5);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (63 + 6);
+INSERT INTO t1 VALUES (NULL);
+SET @@session.auto_increment_increment = 1;
+SET @@session.auto_increment_offset = 1;
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+5
+15
+25
+33
+34
+38
+40
+43
+46
+48
+52
+53
+58
+63
+69
+73
+DROP TABLE t1;
+# Test reported auto_increment value
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='MyISAM'
+PARTITION BY HASH (c1)
+PARTITIONS 2;
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+1
+INSERT INTO t1 VALUES (2);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+3
+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';
+AUTO_INCREMENT
+6
+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';
+AUTO_INCREMENT
+22
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+22
+INSERT INTO t1 VALUES (10);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+10
+17
+19
+20
+21
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+23
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (15);
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+10
+15
+17
+19
+20
+21
+22
+23
+24
+INSERT INTO t1 VALUES (NULL);
+DELETE FROM t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+26
+TRUNCATE TABLE t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=28 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+27
+DROP TABLE t1;
+# Test with two threads
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'MyISAM'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (10);
+# 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);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with two threads + start transaction NO PARTITIONING
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'MyISAM';
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (10);
+# 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);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with two threads + start transaction
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'MyISAM'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (NULL), (10);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19);
+INSERT INTO t1 (c1) VALUES (21);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with another column after
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+c2 INT,
+PRIMARY KEY (c1,c2))
+ENGINE = 'MyISAM'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+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);
+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;
+c1 c2
+1 0
+1 1
+2 0
+2 1
+2 2
+2 22
+3 2
+4 3
+5 3
+6 2
+7 2
+DROP TABLE t1;
+# Test with another column before
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE = 'MyISAM'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+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);
+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;
+c1 c2
+1 1
+1 2
+2 3
+2 13
+2 14
+2 22
+2 23
+3 11
+3 12
+DROP TABLE t1;
+# Test with auto_increment on secondary column in multi-column-index
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1,c2))
+ENGINE = 'MyISAM'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+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);
+INSERT INTO t1 VALUES (2, 2);
+INSERT INTO t1 VALUES (2, 22), (2, NULL);
+SELECT * FROM t1 ORDER BY c1,c2;
+c1 c2
+1 1
+1 2
+2 1
+2 2
+2 3
+2 22
+2 23
+3 1
+3 2
+DROP TABLE t1;
+# Test AUTO_INCREMENT in CREATE
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'MyISAM'
+AUTO_INCREMENT = 15
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (4);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (0);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+4
+15
+16
+# Test sql_mode 'NO_AUTO_VALUE_ON_ZERO'
+SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+INSERT INTO t1 (c1) VALUES (300);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (0);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=302 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+0
+4
+15
+16
+300
+301
+SET @@session.sql_mode = '';
+DROP TABLE t1;
+# Test SET INSERT_ID
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'MyISAM'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1;
+c1
+1
+SET INSERT_ID = 23;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=24 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+23
+DROP TABLE t1;
+# Testing with FLUSH TABLE
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='MyISAM'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 VALUES (4);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 VALUES (NULL);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+4
+5
+DROP TABLE t1;
=== added file 'mysql-test/suite/parts/r/partition_auto_increment_ndb.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_ndb.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_ndb.result 2008-09-08 13:30:01 +0000
@@ -0,0 +1,769 @@
+SET new=on;
+DROP TABLE IF EXISTS t1;
+# test without partitioning for reference
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='NDB';
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+1
+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';
+AUTO_INCREMENT
+6
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+6
+INSERT INTO t1 VALUES (0);
+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);
+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
+4
+5
+6
+10
+20
+21
+22
+23
+30
+50
+51
+52
+53
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='NDB';
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (4);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (NULL);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1
+DELETE FROM t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c1;
+c1
+6
+TRUNCATE TABLE t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+DROP TABLE t1;
+# Simple test with NULL
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='NDB'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1;
+c1
+1
+DROP TABLE t1;
+# Test with sql_mode and first insert as 0
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE='NDB'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (1, 1), (99, 99);
+INSERT INTO t1 VALUES (1, NULL);
+SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+INSERT INTO t1 VALUES (1, 0);
+SELECT * FROM t1 ORDER BY c1, c2;
+c1 c2
+1 0
+1 1
+1 100
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE='NDB'
+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;
+c1 c2
+1 0
+1 1
+1 2
+1 8
+2 3
+4 7
+SET @@session.sql_mode = '';
+DROP TABLE t1;
+# Simple test with NULL, 0 and explicit values both incr. and desc.
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='NDB'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (2), (4), (NULL);
+INSERT INTO t1 VALUES (0);
+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);
+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
+4
+5
+6
+9
+10
+20
+21
+22
+23
+55
+59
+60
+90
+91
+150
+151
+152
+153
+DROP TABLE t1;
+# Test with auto_increment_increment and auto_increment_offset.
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='NDB'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+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);
+INSERT INTO t1 VALUES (33 + 1);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (38 + 2);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (43 + 3);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (48 + 4);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (53 + 5);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (63 + 6);
+INSERT INTO t1 VALUES (NULL);
+SET @@session.auto_increment_increment = 1;
+SET @@session.auto_increment_offset = 1;
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+5
+15
+25
+33
+34
+38
+40
+43
+46
+48
+52
+53
+58
+63
+69
+73
+DROP TABLE t1;
+# Test reported auto_increment value
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='NDB'
+PARTITION BY HASH (c1)
+PARTITIONS 2;
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+1
+INSERT INTO t1 VALUES (2);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+3
+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';
+AUTO_INCREMENT
+6
+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';
+AUTO_INCREMENT
+22
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+22
+INSERT INTO t1 VALUES (10);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+10
+17
+19
+20
+21
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+23
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (15);
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+10
+15
+17
+19
+20
+21
+22
+23
+24
+INSERT INTO t1 VALUES (NULL);
+DELETE FROM t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+26
+TRUNCATE TABLE t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+DROP TABLE t1;
+# Test with two threads
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'NDB'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (10);
+# 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);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with two threads + start transaction NO PARTITIONING
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'NDB';
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (10);
+# 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);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+5
+10
+22
+23
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+5
+10
+22
+23
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with two threads + start transaction
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'NDB'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (NULL), (10);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19);
+INSERT INTO t1 (c1) VALUES (21);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+5
+10
+22
+23
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+5
+10
+22
+23
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with another column after
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+c2 INT,
+PRIMARY KEY (c1,c2))
+ENGINE = 'NDB'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+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);
+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;
+c1 c2
+1 0
+1 1
+2 0
+2 1
+2 2
+2 22
+3 2
+4 3
+5 3
+6 2
+7 2
+DROP TABLE t1;
+# Test with another column before
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE = 'NDB'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+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);
+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;
+c1 c2
+1 1
+1 2
+2 3
+2 13
+2 14
+2 22
+2 23
+3 11
+3 12
+DROP TABLE t1;
+# Test with auto_increment on secondary column in multi-column-index
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1,c2))
+ENGINE = 'NDB'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+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);
+INSERT INTO t1 VALUES (2, 2);
+# ERROR (only OK if Blackhole/NDB) should give ER_DUP_KEY or ER_DUP_ENTRY
+INSERT INTO t1 VALUES (2, 22), (2, NULL);
+SELECT * FROM t1 ORDER BY c1,c2;
+c1 c2
+1 1
+1 2
+2 2
+2 3
+2 6
+2 7
+2 22
+2 23
+3 4
+3 5
+DROP TABLE t1;
+# Test AUTO_INCREMENT in CREATE
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'NDB'
+AUTO_INCREMENT = 15
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (4);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (0);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+4
+15
+16
+# Test sql_mode 'NO_AUTO_VALUE_ON_ZERO'
+SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+INSERT INTO t1 (c1) VALUES (300);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (0);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+0
+4
+15
+16
+300
+301
+SET @@session.sql_mode = '';
+DROP TABLE t1;
+# Test SET INSERT_ID
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'NDB'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1;
+c1
+1
+SET INSERT_ID = 23;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+23
+DROP TABLE t1;
+# Testing with FLUSH TABLE
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='NDB'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 VALUES (4);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 VALUES (NULL);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+4
+5
+DROP TABLE t1;
=== added file 'mysql-test/suite/parts/t/partition_auto_increment_archive.test'
--- a/mysql-test/suite/parts/t/partition_auto_increment_archive.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_auto_increment_archive.test 2008-09-08 13:30:01 +0000
@@ -0,0 +1,40 @@
+################################################################################
+# t/partition_auto_increment_archive.test #
+# #
+# Purpose: #
+# Tests around auto increment column #
+# Archive branch #
+# #
+#------------------------------------------------------------------------------#
+# Original Author: MattiasJ #
+# Original Date: 2008-09-02 #
+# Change Author: #
+# Change Date: #
+# Change: #
+################################################################################
+
+#
+# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE !
+# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN
+# THE SOURCED FILES ONLY.
+#
+
+# The server must support partitioning.
+--source include/have_partition.inc
+
+#------------------------------------------------------------------------------#
+# Engine specific settings and requirements
+--source include/have_archive.inc
+# 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
+let $engine= 'Archive';
+
+#------------------------------------------------------------------------------#
+# Execute the tests to be applied to all storage engines
+--source suite/parts/inc/partition_auto_increment.inc
+
=== added file 'mysql-test/suite/parts/t/partition_auto_increment_blackhole.test'
--- a/mysql-test/suite/parts/t/partition_auto_increment_blackhole.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_auto_increment_blackhole.test 2008-09-08 13:30:01 +0000
@@ -0,0 +1,35 @@
+################################################################################
+# t/partition_auto_increment_blackhole.test #
+# #
+# Purpose: #
+# Tests around auto increment column #
+# Blackhole branch #
+# #
+#------------------------------------------------------------------------------#
+# Original Author: MattiasJ #
+# Original Date: 2008-09-02 #
+# Change Author: #
+# Change Date: #
+# Change: #
+################################################################################
+
+#
+# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE !
+# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN
+# THE SOURCED FILES ONLY.
+#
+
+# The server must support partitioning.
+--source include/have_partition.inc
+
+#------------------------------------------------------------------------------#
+# Engine specific settings and requirements
+--source include/have_blackhole.inc
+
+##### Storage engine to be tested
+let $engine= 'Blackhole';
+
+#------------------------------------------------------------------------------#
+# Execute the tests to be applied to all storage engines
+--source suite/parts/inc/partition_auto_increment.inc
+
=== added file 'mysql-test/suite/parts/t/partition_auto_increment_innodb.test'
--- a/mysql-test/suite/parts/t/partition_auto_increment_innodb.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_auto_increment_innodb.test 2008-09-08 13:30:01 +0000
@@ -0,0 +1,35 @@
+################################################################################
+# t/partition_auto_increment_innodb.test #
+# #
+# Purpose: #
+# Tests around auto increment column #
+# InnoDB branch #
+# #
+#------------------------------------------------------------------------------#
+# Original Author: MattiasJ #
+# Original Date: 2008-02-12 #
+# Change Author: #
+# Change Date: #
+# Change: #
+################################################################################
+
+#
+# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE !
+# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN
+# THE SOURCED FILES ONLY.
+#
+
+# The server must support partitioning.
+--source include/have_partition.inc
+
+#------------------------------------------------------------------------------#
+# Engine specific settings and requirements
+
+##### Storage engine to be tested
+let $engine= 'InnoDB';
+--source include/have_innodb.inc
+
+#------------------------------------------------------------------------------#
+# Execute the tests to be applied to all storage engines
+--source suite/parts/inc/partition_auto_increment.inc
+
=== added file 'mysql-test/suite/parts/t/partition_auto_increment_memory.test'
--- a/mysql-test/suite/parts/t/partition_auto_increment_memory.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_auto_increment_memory.test 2008-09-08 13:30:01 +0000
@@ -0,0 +1,34 @@
+################################################################################
+# t/partition_auto_increment_memory.test #
+# #
+# Purpose: #
+# Tests around auto increment column #
+# Memory branch #
+# #
+#------------------------------------------------------------------------------#
+# Original Author: MattiasJ #
+# Original Date: 2008-02-12 #
+# Change Author: #
+# Change Date: #
+# Change: #
+################################################################################
+
+#
+# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE !
+# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN
+# THE SOURCED FILES ONLY.
+#
+
+# The server must support partitioning.
+--source include/have_partition.inc
+
+#------------------------------------------------------------------------------#
+# Engine specific settings and requirements
+
+##### Storage engine to be tested
+let $engine= 'Memory';
+
+#------------------------------------------------------------------------------#
+# Execute the tests to be applied to all storage engines
+--source suite/parts/inc/partition_auto_increment.inc
+
=== added file 'mysql-test/suite/parts/t/partition_auto_increment_myisam.test'
--- a/mysql-test/suite/parts/t/partition_auto_increment_myisam.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_auto_increment_myisam.test 2008-09-08 13:30:01 +0000
@@ -0,0 +1,34 @@
+################################################################################
+# t/partition_auto_increment_myisam.test #
+# #
+# Purpose: #
+# Tests around auto increment column #
+# MyISAM branch #
+# #
+#------------------------------------------------------------------------------#
+# Original Author: MattiasJ #
+# Original Date: 2008-02-12 #
+# Change Author: #
+# Change Date: #
+# Change: #
+################################################################################
+
+#
+# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE !
+# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN
+# THE SOURCED FILES ONLY.
+#
+
+# The server must support partitioning.
+--source include/have_partition.inc
+
+#------------------------------------------------------------------------------#
+# Engine specific settings and requirements
+
+##### Storage engine to be tested
+let $engine= 'MyISAM';
+
+#------------------------------------------------------------------------------#
+# Execute the tests to be applied to all storage engines
+--source suite/parts/inc/partition_auto_increment.inc
+
=== added file 'mysql-test/suite/parts/t/partition_auto_increment_ndb.test'
--- a/mysql-test/suite/parts/t/partition_auto_increment_ndb.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_auto_increment_ndb.test 2008-09-08 13:30:01 +0000
@@ -0,0 +1,41 @@
+################################################################################
+# t/partition_auto_increment_ndb.test #
+# #
+# Purpose: #
+# Tests around auto increment column #
+# NDB branch #
+# #
+# Note: NDB behavior for auto_increment on secondary column in #
+# multi-column-index is NOT like MyISAM, instead it uses the same #
+# behavior as if it was the primary column. #
+#------------------------------------------------------------------------------#
+# Original Author: MattiasJ #
+# Original Date: 2008-09-02 #
+# Change Author: #
+# Change Date: #
+# Change: #
+################################################################################
+
+#
+# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE !
+# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN
+# THE SOURCED FILES ONLY.
+#
+
+# The server must support partitioning.
+--source include/have_partition.inc
+
+#------------------------------------------------------------------------------#
+# Engine specific settings and requirements
+--source include/have_ndb.inc
+
+##### Storage engine to be tested
+let $engine= 'NDB';
+connection default;
+#enable hash partitioning
+SET new=on;
+
+#------------------------------------------------------------------------------#
+# Execute the tests to be applied to all storage engines
+--source suite/parts/inc/partition_auto_increment.inc
+
=== added file 'mysql-test/suite/rpl/r/rpl_blackhole.result'
--- a/mysql-test/suite/rpl/r/rpl_blackhole.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/r/rpl_blackhole.result 2008-10-02 09:02:38 +0000
@@ -0,0 +1,100 @@
+stop slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+reset master;
+reset slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+start slave;
+CREATE TABLE t1 (a INT, b INT, c INT);
+CREATE TABLE t2 (a INT, b INT, c INT);
+ALTER TABLE t1 ENGINE=BLACKHOLE;
+INSERT INTO t2 VALUES (1,9,1), (2,9,2), (3,9,3), (4,9,4);
+[on master]
+INSERT INTO t1 VALUES (1,1,1),(2,1,2),(3,1,3),(4,1,4);
+[on slave]
+# Expect 0
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+0
+>>> Something was written to binary log <<<
+[on master]
+UPDATE t1 SET c = 2*c WHERE a % 2 = 0 AND b = 1;
+[on slave]
+# Expect 0
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+0
+>>> Something was written to binary log <<<
+[on master]
+DELETE FROM t1 WHERE a % 2 = 0 AND b = 1;
+[on slave]
+# Expect 0
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+0
+>>> Something was written to binary log <<<
+[on master]
+INSERT INTO t1 SELECT * FROM t2;
+[on slave]
+# Expect 0
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+0
+>>> Something was written to binary log <<<
+[on master]
+INSERT INTO t2 SELECT * FROM t1;
+[on slave]
+# Expect 0
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+0
+>>> Something was written to binary log <<<
+ALTER TABLE t1 ADD PRIMARY KEY pk_t1 (a,b);
+[on master]
+INSERT INTO t1 VALUES (1,2,1),(2,2,2),(3,2,3),(4,2,4);
+[on slave]
+# Expect 0
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+0
+>>> Something was written to binary log <<<
+[on master]
+UPDATE t1 SET c = 2*c WHERE a % 2 = 0 AND b = 2;
+[on slave]
+# Expect 0
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+0
+>>> Something was written to binary log <<<
+[on master]
+DELETE FROM t1 WHERE a % 2 = 0 AND b = 2;
+[on slave]
+# Expect 0
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+0
+>>> Something was written to binary log <<<
+ALTER TABLE t1 DROP PRIMARY KEY, ADD KEY key_t1 (a);
+[on master]
+INSERT INTO t1 VALUES (1,3,1),(2,3,2),(3,3,3),(4,3,4);
+[on slave]
+# Expect 0
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+0
+>>> Something was written to binary log <<<
+[on master]
+UPDATE t1 SET c = 2*c WHERE a % 2 = 0 AND b = 3;
+[on slave]
+# Expect 0
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+0
+>>> Something was written to binary log <<<
+[on master]
+DELETE FROM t1 WHERE a % 2 = 0 AND b = 3;
+[on slave]
+# Expect 0
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+0
+>>> Something was written to binary log <<<
=== modified file 'mysql-test/suite/rpl/r/rpl_sp.result'
--- a/mysql-test/suite/rpl/r/rpl_sp.result 2008-05-16 15:38:13 +0000
+++ b/mysql-test/suite/rpl/r/rpl_sp.result 2008-10-06 14:22:07 +0000
@@ -193,7 +193,7 @@ end|
ERROR HY000: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
set global log_bin_trust_routine_creators=1;
Warnings:
-Warning 1287 The syntax 'log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use 'log_bin_trust_function_creators' instead
+Warning 1287 The syntax '@@log_bin_trust_routine_creators' is deprecated and will be removed in MySQL 5.2. Please use '@@log_bin_trust_function_creators' instead
set global log_bin_trust_function_creators=0;
set global log_bin_trust_function_creators=1;
set global log_bin_trust_function_creators=1;
@@ -526,7 +526,7 @@ master-bin.000001 # Query 1 # use `test`
master-bin.000001 # Query 1 # use `test`; CREATE TABLE t1(col VARCHAR(10))
master-bin.000001 # Query 1 # use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`(arg VARCHAR(10))
INSERT INTO t1 VALUES(arg)
-master-bin.000001 # Query 1 # use `test`; INSERT INTO t1 VALUES( NAME_CONST('arg',_latin1'test'))
+master-bin.000001 # Query 1 # use `test`; INSERT INTO t1 VALUES( NAME_CONST('arg',_latin1'test' COLLATE 'latin1_swedish_ci'))
master-bin.000001 # Query 1 # use `test`; DROP PROCEDURE p1
master-bin.000001 # Query 1 # use `test`; CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
SET @a = 1
@@ -869,7 +869,7 @@ CREATE DEFINER=`root`@`localhost` PROCED
INSERT INTO t1 VALUES(arg)
/*!*/;
SET TIMESTAMP=t/*!*/;
-INSERT INTO t1 VALUES( NAME_CONST('arg',_latin1'test'))
+INSERT INTO t1 VALUES( NAME_CONST('arg',_latin1'test' COLLATE 'latin1_swedish_ci'))
/*!*/;
SET TIMESTAMP=t/*!*/;
DROP PROCEDURE p1
=== modified file 'mysql-test/suite/rpl/r/rpl_temporary.result'
--- a/mysql-test/suite/rpl/r/rpl_temporary.result 2008-07-18 08:20:55 +0000
+++ b/mysql-test/suite/rpl/r/rpl_temporary.result 2008-10-07 16:54:12 +0000
@@ -24,6 +24,9 @@ drop table if exists t1,t2;
create table t1(f int);
create table t2(f int);
insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+10
create temporary table t3(f int);
insert into t3 select * from t1 where f<6;
create temporary table t3(f int);
=== modified file 'mysql-test/suite/rpl/t/disabled.def'
--- a/mysql-test/suite/rpl/t/disabled.def 2008-10-01 14:02:04 +0000
+++ b/mysql-test/suite/rpl/t/disabled.def 2008-10-07 15:32:58 +0000
@@ -12,5 +12,4 @@
rpl_redirect : Failure is sporadic and and the test is superfluous (mats)
rpl_innodb_bug28430 : Failure on Solaris Bug #36793
-#rpl_temporary : BUG#38269 2008-07-21 Sven valgrind error in pushbuild
rpl_flushlog_loop : BUG#37733 2008-07-23 Sven disabled in 5.1-bugteam. the bug has been fixed in 5.1-rpl: please re-enable when that gets pushed to main
=== added file 'mysql-test/suite/rpl/t/rpl_blackhole.test'
--- a/mysql-test/suite/rpl/t/rpl_blackhole.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/rpl/t/rpl_blackhole.test 2008-10-03 10:52:01 +0000
@@ -0,0 +1,80 @@
+# PURPOSE. Test that blackhole works with replication in all three
+# modes: STATEMENT, MIXED, and ROW.
+#
+# METHOD. We start by creating a table on the master and then change
+# the engine to use blackhole on the slave.
+#
+# After insert/update/delete of one or more rows, the test the
+# proceeds to check that replication is running after replicating an
+# change, that the blackhole engine does not contain anything (which
+# is just a check that the correct engine is used), and that something
+# is written to the binary log.
+#
+# Whe check INSERT, UPDATE, and DELETE statement for tables with no
+# key (forcing a range search on the slave), primary keys (using a
+# primary key lookup), and index/key with multiple matches (forcing an
+# index search).
+
+source include/master-slave.inc;
+source include/have_blackhole.inc;
+
+# We start with no primary key
+CREATE TABLE t1 (a INT, b INT, c INT);
+CREATE TABLE t2 (a INT, b INT, c INT);
+
+sync_slave_with_master;
+ALTER TABLE t1 ENGINE=BLACKHOLE;
+
+connection master;
+INSERT INTO t2 VALUES (1,9,1), (2,9,2), (3,9,3), (4,9,4);
+sync_slave_with_master;
+
+# Test insert, no primary key
+let $statement = INSERT INTO t1 VALUES (1,1,1),(2,1,2),(3,1,3),(4,1,4);
+source extra/rpl_tests/rpl_blackhole.test;
+
+# Test update, no primary key
+let $statement = UPDATE t1 SET c = 2*c WHERE a % 2 = 0 AND b = 1;
+source extra/rpl_tests/rpl_blackhole.test;
+
+# Test delete, no primary key
+let $statement = DELETE FROM t1 WHERE a % 2 = 0 AND b = 1;
+source extra/rpl_tests/rpl_blackhole.test;
+
+# Test INSERT-SELECT into Blackhole, no primary key
+let $statement = INSERT INTO t1 SELECT * FROM t2;
+source extra/rpl_tests/rpl_blackhole.test;
+
+# Test INSERT-SELECT from Blackhole, no primary key
+let $statement = INSERT INTO t2 SELECT * FROM t1;
+source extra/rpl_tests/rpl_blackhole.test;
+
+connection master;
+ALTER TABLE t1 ADD PRIMARY KEY pk_t1 (a,b);
+
+# Test insert, primary key
+let $statement = INSERT INTO t1 VALUES (1,2,1),(2,2,2),(3,2,3),(4,2,4);
+source extra/rpl_tests/rpl_blackhole.test;
+
+# Test update, primary key
+let $statement = UPDATE t1 SET c = 2*c WHERE a % 2 = 0 AND b = 2;
+source extra/rpl_tests/rpl_blackhole.test;
+
+# Test delete, primary key
+let $statement = DELETE FROM t1 WHERE a % 2 = 0 AND b = 2;
+source extra/rpl_tests/rpl_blackhole.test;
+
+connection master;
+ALTER TABLE t1 DROP PRIMARY KEY, ADD KEY key_t1 (a);
+
+# Test insert, key
+let $statement = INSERT INTO t1 VALUES (1,3,1),(2,3,2),(3,3,3),(4,3,4);
+source extra/rpl_tests/rpl_blackhole.test;
+
+# Test update, key
+let $statement = UPDATE t1 SET c = 2*c WHERE a % 2 = 0 AND b = 3;
+source extra/rpl_tests/rpl_blackhole.test;
+
+# Test delete, key
+let $statement = DELETE FROM t1 WHERE a % 2 = 0 AND b = 3;
+source extra/rpl_tests/rpl_blackhole.test;
=== modified file 'mysql-test/suite/rpl/t/rpl_temporary.test'
--- a/mysql-test/suite/rpl/t/rpl_temporary.test 2008-07-18 08:20:55 +0000
+++ b/mysql-test/suite/rpl/t/rpl_temporary.test 2008-10-07 16:54:12 +0000
@@ -56,6 +56,8 @@ drop table if exists t1,t2;
create table t1(f int);
create table t2(f int);
insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
+# Auxiliary select (We want that all rows are in the table)
+SELECT COUNT(*) FROM t1;
connection con1;
create temporary table t3(f int);
=== modified file 'mysql-test/t/func_group.test'
--- a/mysql-test/t/func_group.test 2008-03-19 11:25:36 +0000
+++ b/mysql-test/t/func_group.test 2008-10-02 14:44:49 +0000
@@ -933,5 +933,45 @@ SELECT AVG(a), CAST(AVG(a) AS DECIMAL) F
DROP TABLE t1;
+#
+# Bug #37348: Crash in or immediately after JOIN::make_sum_func_list
+#
+
+CREATE TABLE derived1 (a bigint(21));
+INSERT INTO derived1 VALUES (2);
+
+
+CREATE TABLE D (
+ pk int(11) NOT NULL AUTO_INCREMENT,
+ int_nokey int(11) DEFAULT NULL,
+ int_key int(11) DEFAULT NULL,
+ filler blob,
+ PRIMARY KEY (pk),
+ KEY int_key (int_key)
+);
+
+INSERT INTO D VALUES
+ (39,40,4,repeat(' X', 42)),
+ (43,56,4,repeat(' X', 42)),
+ (47,12,4,repeat(' X', 42)),
+ (71,28,4,repeat(' X', 42)),
+ (76,54,4,repeat(' X', 42)),
+ (83,45,4,repeat(' X', 42)),
+ (105,53,12,NULL);
+
+SELECT
+ (SELECT COUNT( int_nokey )
+ FROM derived1 AS X
+ WHERE
+ X.int_nokey < 61
+ GROUP BY pk
+ LIMIT 1)
+FROM D AS X
+WHERE X.int_key < 13
+GROUP BY int_nokey LIMIT 1;
+
+DROP TABLE derived1;
+DROP TABLE D;
+
###
--echo End of 5.0 tests
=== modified file 'mysql-test/t/innodb-semi-consistent.test'
--- a/mysql-test/t/innodb-semi-consistent.test 2007-11-27 08:25:45 +0000
+++ b/mysql-test/t/innodb-semi-consistent.test 2008-10-03 12:24:19 +0000
@@ -10,6 +10,7 @@ drop table if exists t1;
connect (a,localhost,root,,);
connect (b,localhost,root,,);
connection a;
+set binlog_format=mixed;
set session transaction isolation level read committed;
create table t1(a int not null) engine=innodb DEFAULT CHARSET=latin1;
insert into t1 values (1),(2),(3),(4),(5),(6),(7);
@@ -17,6 +18,7 @@ set autocommit=0;
# this should lock the entire table
select * from t1 where a=3 lock in share mode;
connection b;
+set binlog_format=mixed;
set session transaction isolation level read committed;
set autocommit=0;
-- error ER_LOCK_WAIT_TIMEOUT
=== modified file 'mysql-test/t/innodb.test'
--- a/mysql-test/t/innodb.test 2008-08-20 22:18:33 +0000
+++ b/mysql-test/t/innodb.test 2008-10-03 12:24:19 +0000
@@ -701,6 +701,7 @@ insert into t1 (code, name) values (2, '
select id, code, name from t1 order by id;
COMMIT;
+SET binlog_format='MIXED';
BEGIN;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
insert into t1 (code, name) values (3, 'Jeremy'), (4, 'Matt');
@@ -2000,10 +2001,12 @@ connection a;
create table t1(a int not null, b int, primary key(a)) engine=innodb;
insert into t1 values(1,1),(2,2),(3,1),(4,2),(5,1),(6,2),(7,3);
commit;
+SET binlog_format='MIXED';
set autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
update t1 set b = 5 where b = 1;
connection b;
+SET binlog_format='MIXED';
set autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
#
@@ -2071,6 +2074,7 @@ commit;
set autocommit = 0;
select * from t2 for update;
connection b;
+SET binlog_format='MIXED';
set autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
insert into t1 select * from t2;
@@ -2127,46 +2131,55 @@ commit;
set autocommit = 0;
select * from t2 for update;
connection b;
+SET binlog_format='MIXED';
set autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
--send
insert into t1 select * from t2;
connection c;
+SET binlog_format='MIXED';
set autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
--send
update t3 set b = (select b from t2 where a = d);
connection d;
+SET binlog_format='MIXED';
set autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
--send
create table t4(a int not null, b int, primary key(a)) engine=innodb select * from t2;
connection e;
+SET binlog_format='MIXED';
set autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
--send
insert into t5 (select * from t2 lock in share mode);
connection f;
+SET binlog_format='MIXED';
set autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
--send
update t6 set e = (select b from t2 where a = d lock in share mode);
connection g;
+SET binlog_format='MIXED';
set autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
--send
create table t7(a int not null, b int, primary key(a)) engine=innodb select * from t2 lock in share mode;
connection h;
+SET binlog_format='MIXED';
set autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
--send
insert into t8 (select * from t2 for update);
connection i;
+SET binlog_format='MIXED';
set autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
--send
update t9 set e = (select b from t2 where a = d for update);
connection j;
+SET binlog_format='MIXED';
set autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
--send
@@ -2380,6 +2393,7 @@ DROP TABLE t1;
CONNECT (c1,localhost,root,,);
CONNECT (c2,localhost,root,,);
CONNECTION c1;
+SET binlog_format='MIXED';
SET TX_ISOLATION='read-committed';
SET AUTOCOMMIT=0;
DROP TABLE IF EXISTS t1, t2;
@@ -2387,6 +2401,7 @@ CREATE TABLE t1 ( a int ) ENGINE=InnoDB;
CREATE TABLE t2 LIKE t1;
SELECT * FROM t2;
CONNECTION c2;
+SET binlog_format='MIXED';
SET TX_ISOLATION='read-committed';
SET AUTOCOMMIT=0;
INSERT INTO t1 VALUES (1);
@@ -2398,10 +2413,12 @@ DISCONNECT c2;
CONNECT (c1,localhost,root,,);
CONNECT (c2,localhost,root,,);
CONNECTION c1;
+SET binlog_format='MIXED';
SET TX_ISOLATION='read-committed';
SET AUTOCOMMIT=0;
SELECT * FROM t2;
CONNECTION c2;
+SET binlog_format='MIXED';
SET TX_ISOLATION='read-committed';
SET AUTOCOMMIT=0;
INSERT INTO t1 VALUES (2);
=== modified file 'mysql-test/t/log_basic.test'
--- a/mysql-test/t/log_basic.test 2008-04-10 13:14:28 +0000
+++ b/mysql-test/t/log_basic.test 2008-10-06 09:29:42 +0000
@@ -38,9 +38,9 @@ SELECT @@global.log AS INIT_VALUE;
SELECT @@log AS INIT_VALUE;
-SET @@global.log = ON;
+SET @@global.general_log = ON;
-SET global log = 0;
+SET global general_log = 0;
--echo 'Bug# 34832: log is a system but it is not accessible using SET @@global.log;'
--echo 'SET GLOBAL log; and SELECT @@global.log. SHOW VARIABLES shows the value of log.'
=== modified file 'mysql-test/t/log_state.test'
--- a/mysql-test/t/log_state.test 2008-03-28 18:46:18 +0000
+++ b/mysql-test/t/log_state.test 2008-10-06 09:29:42 +0000
@@ -259,6 +259,32 @@ SET GLOBAL slow_query_log_file = @slow_q
###########################################################################
+
+
+## WL#4403 - deprecate @log and @slow_log_queries variables
+
+## these are all deprecated -- show for command-line as well!
+--echo deprecated:
+SET GLOBAL log = 0;
+SET GLOBAL log_slow_queries = 0;
+SET GLOBAL log = DEFAULT;
+SET GLOBAL log_slow_queries = DEFAULT;
+
+## these are NOT deprecated
+--echo not deprecated:
+SELECT @@global.general_log_file INTO @my_glf;
+SELECT @@global.slow_query_log_file INTO @my_sqlf;
+SET GLOBAL general_log = 0;
+SET GLOBAL slow_query_log = 0;
+SET GLOBAL general_log_file = 'WL4403_G.log';
+SET GLOBAL slow_query_log_file = 'WL4403_SQ.log';
+SET GLOBAL general_log_file = @my_glf;
+SET GLOBAL slow_query_log_file = @my_sqlf;
+SET GLOBAL general_log = DEFAULT;
+SET GLOBAL slow_query_log = DEFAULT;
+
+
+
--echo End of 5.1 tests
--enable_ps_protocol
=== modified file 'mysql-test/t/partition.test'
--- a/mysql-test/t/partition.test 2008-10-06 08:43:46 +0000
+++ b/mysql-test/t/partition.test 2008-10-07 15:19:32 +0000
@@ -1816,4 +1816,53 @@ SET SQL_MODE= @orig_sql_mode;
+#
+# Bug #38005 Partitions: error with insert select
+#
+
+create table t1 (s1 int) partition by hash(s1) partitions 2;
+create index i on t1 (s1);
+insert into t1 values (1);
+insert into t1 select s1 from t1;
+insert into t1 select s1 from t1;
+insert into t1 select s1 from t1 order by s1 desc;
+select * from t1;
+drop table t1;
+
+create table t1 (s1 int) partition by range(s1)
+ (partition pa1 values less than (10),
+ partition pa2 values less than MAXVALUE);
+create index i on t1 (s1);
+insert into t1 values (1);
+insert into t1 select s1 from t1;
+insert into t1 select s1 from t1;
+insert into t1 select s1 from t1 order by s1 desc;
+select * from t1;
+drop table t1;
+
+create table t1 (s1 int) partition by range(s1)
+ (partition pa1 values less than (10),
+ partition pa2 values less than MAXVALUE);
+create index i on t1 (s1);
+insert into t1 values (20);
+insert into t1 select s1 from t1;
+insert into t1 select s1 from t1;
+insert into t1 select s1 from t1 order by s1 desc;
+select * from t1;
+drop table t1;
+
+create table t1 (s1 int) partition by range(s1)
+ (partition pa1 values less than (10),
+ partition pa2 values less than MAXVALUE);
+create index i on t1 (s1);
+insert into t1 values (1), (2), (3), (4), (5), (6), (7), (8);
+insert into t1 select s1 from t1;
+insert into t1 select s1 from t1;
+insert into t1 select s1 from t1;
+insert into t1 select s1 from t1;
+insert into t1 select s1 from t1 order by s1 desc;
+insert into t1 select s1 from t1 where s1=3;
+select count(*) from t1;
+drop table t1;
+
--echo End of 5.1 tests
=== modified file 'mysql-test/t/trigger-trans.test'
--- a/mysql-test/t/trigger-trans.test 2008-03-12 13:13:33 +0000
+++ b/mysql-test/t/trigger-trans.test 2008-10-07 16:54:12 +0000
@@ -162,3 +162,18 @@ DROP TABLE t2, t1;
--echo End of 5.0 tests
+
+--echo BUG#31612
+--echo Trigger fired multiple times leads to gaps in auto_increment sequence
+create table t1 (a int, val char(1)) engine=InnoDB;
+create table t2 (b int auto_increment primary key,
+ val char(1)) engine=InnoDB;
+create trigger t1_after_insert after
+ insert on t1 for each row insert into t2 set val=NEW.val;
+insert into t1 values ( 123, 'a'), ( 123, 'b'), ( 123, 'c'),
+ (123, 'd'), (123, 'e'), (123, 'f'), (123, 'g');
+insert into t1 values ( 654, 'a'), ( 654, 'b'), ( 654, 'c'),
+ (654, 'd'), (654, 'e'), (654, 'f'), (654, 'g');
+select * from t2 order by b;
+drop trigger t1_after_insert;
+drop table t1,t2;
=== modified file 'mysql-test/t/tx_isolation_func.test'
--- a/mysql-test/t/tx_isolation_func.test 2008-04-14 15:21:18 +0000
+++ b/mysql-test/t/tx_isolation_func.test 2008-10-03 12:24:19 +0000
@@ -75,10 +75,12 @@ INSERT INTO t1 VALUES(24, 24);
--echo ** Connection con0 **
connection con0;
SET SESSION tx_isolation = 'READ-UNCOMMITTED';
+set binlog_format=mixed;
--echo ** Connection con1 **
connection con1;
SET SESSION tx_isolation = 'READ-UNCOMMITTED';
+set binlog_format=mixed;
#
# Testing WHERE on keys using IN clause
=== modified file 'sql/ha_partition.cc'
--- a/sql/ha_partition.cc 2008-09-18 19:49:34 +0000
+++ b/sql/ha_partition.cc 2008-10-06 13:14:20 +0000
@@ -160,7 +160,8 @@ const uint ha_partition::NO_CURRENT_PART
ha_partition::ha_partition(handlerton *hton, TABLE_SHARE *share)
:handler(hton, share), m_part_info(NULL), m_create_handler(FALSE),
- m_is_sub_partitioned(0), is_clone(FALSE)
+ m_is_sub_partitioned(0), is_clone(FALSE), auto_increment_lock(FALSE),
+ auto_increment_safe_stmt_log_lock(FALSE)
{
DBUG_ENTER("ha_partition::ha_partition(table)");
init_handler_variables();
@@ -182,7 +183,8 @@ ha_partition::ha_partition(handlerton *h
ha_partition::ha_partition(handlerton *hton, partition_info *part_info)
:handler(hton, NULL), m_part_info(part_info),
m_create_handler(TRUE),
- m_is_sub_partitioned(m_part_info->is_sub_partitioned()), is_clone(FALSE)
+ m_is_sub_partitioned(m_part_info->is_sub_partitioned()), is_clone(FALSE),
+ auto_increment_lock(FALSE), auto_increment_safe_stmt_log_lock(FALSE)
{
DBUG_ENTER("ha_partition::ha_partition(part_info)");
init_handler_variables();
@@ -1248,7 +1250,7 @@ int ha_partition::prepare_new_partition(
assumes that external_lock() is last call that may fail here.
Otherwise see description for cleanup_new_partition().
*/
- if ((error= file->ha_external_lock(current_thd, m_lock_type)))
+ if ((error= file->ha_external_lock(ha_thd(), m_lock_type)))
goto error;
DBUG_RETURN(0);
@@ -1336,8 +1338,8 @@ void ha_partition::cleanup_new_partition
int ha_partition::change_partitions(HA_CREATE_INFO *create_info,
const char *path,
- ulonglong *copied,
- ulonglong *deleted,
+ ulonglong * const copied,
+ ulonglong * const deleted,
const uchar *pack_frm_data
__attribute__((unused)),
size_t pack_frm_len
@@ -1354,7 +1356,7 @@ int ha_partition::change_partitions(HA_C
int error= 1;
bool first;
uint temp_partitions= m_part_info->temp_partitions.elements;
- THD *thd= current_thd;
+ THD *thd= ha_thd();
DBUG_ENTER("ha_partition::change_partitions");
/*
@@ -1628,7 +1630,8 @@ int ha_partition::change_partitions(HA_C
partitions.
*/
-int ha_partition::copy_partitions(ulonglong *copied, ulonglong *deleted)
+int ha_partition::copy_partitions(ulonglong * const copied,
+ ulonglong * const deleted)
{
uint reorg_part= 0;
int result= 0;
@@ -1674,13 +1677,13 @@ int ha_partition::copy_partitions(ulongl
table since it doesn't fit into any partition any longer due to
changed partitioning ranges or list values.
*/
- deleted++;
+ (*deleted)++;
}
else
{
THD *thd= ha_thd();
/* Copy record to new handler */
- copied++;
+ (*copied)++;
tmp_disable_binlog(thd); /* Do not replicate the low-level changes. */
result= m_new_file[new_part]->ha_write_row(m_rec0);
reenable_binlog(thd);
@@ -1714,6 +1717,14 @@ error:
void ha_partition::update_create_info(HA_CREATE_INFO *create_info)
{
+ /*
+ Fix for bug#38751, some engines needs info-calls in ALTER.
+ Archive need this since it flushes in ::info.
+ HA_STATUS_AUTO is optimized so it will not always be forwarded
+ to all partitions, but HA_STATUS_VARIABLE will.
+ */
+ info(HA_STATUS_VARIABLE);
+
info(HA_STATUS_AUTO);
if (!(create_info->used_fields & HA_CREATE_USED_AUTO))
@@ -1804,7 +1815,7 @@ uint ha_partition::del_ren_cre_table(con
handler **file, **abort_file;
DBUG_ENTER("del_ren_cre_table()");
- if (get_from_handler_file(from, current_thd->mem_root))
+ if (get_from_handler_file(from, ha_thd()->mem_root))
DBUG_RETURN(TRUE);
DBUG_ASSERT(m_file_buffer);
DBUG_PRINT("enter", ("from: (%s) to: (%s)", from, to));
@@ -1931,7 +1942,7 @@ int ha_partition::set_up_table_before_cr
{
int error= 0;
const char *partition_name;
- THD *thd= current_thd;
+ THD *thd= ha_thd();
DBUG_ENTER("set_up_table_before_create");
if (!part_elem)
@@ -2327,7 +2338,7 @@ bool ha_partition::get_from_handler_file
tot_partition_words= (m_tot_parts + 3) / 4;
engine_array= (handlerton **) my_alloca(m_tot_parts * sizeof(handlerton*));
for (i= 0; i < m_tot_parts; i++)
- engine_array[i]= ha_resolve_by_legacy_type(current_thd,
+ engine_array[i]= ha_resolve_by_legacy_type(ha_thd(),
(enum legacy_db_type)
*(uchar *) ((file_buffer) + 12 + i));
address_tot_name_len= file_buffer + 12 + 4 * tot_partition_words;
@@ -2398,8 +2409,10 @@ int ha_partition::open(const char *name,
uint alloc_len;
handler **file;
char name_buff[FN_REFLEN];
+ bool is_not_tmp_table= (table_share->tmp_table == NO_TMP_TABLE);
DBUG_ENTER("ha_partition::open");
+ DBUG_ASSERT(table->s == table_share);
ref_length= 0;
m_mode= mode;
m_open_test_lock= test_if_locked;
@@ -2408,9 +2421,9 @@ int ha_partition::open(const char *name,
DBUG_RETURN(1);
m_start_key.length= 0;
m_rec0= table->record[0];
- m_rec_length= table->s->reclength;
+ m_rec_length= table_share->reclength;
alloc_len= m_tot_parts * (m_rec_length + PARTITION_BYTES_IN_POS);
- alloc_len+= table->s->max_key_length;
+ alloc_len+= table_share->max_key_length;
if (!m_ordered_rec_buffer)
{
if (!(m_ordered_rec_buffer= (uchar*)my_malloc(alloc_len, MYF(MY_WME))))
@@ -2483,6 +2496,30 @@ int ha_partition::open(const char *name,
goto err_handler;
/*
+ Use table_share->ha_data to share auto_increment_value among all handlers
+ for the same table.
+ */
+ if (is_not_tmp_table)
+ pthread_mutex_lock(&table_share->mutex);
+ if (!table_share->ha_data)
+ {
+ HA_DATA_PARTITION *ha_data;
+ /* currently only needed for auto_increment */
+ table_share->ha_data= ha_data= (HA_DATA_PARTITION*)
+ alloc_root(&table_share->mem_root,
+ sizeof(HA_DATA_PARTITION));
+ if (!ha_data)
+ {
+ if (is_not_tmp_table)
+ pthread_mutex_unlock(&table_share->mutex);
+ goto err_handler;
+ }
+ DBUG_PRINT("info", ("table_share->ha_data 0x%p", ha_data));
+ bzero(ha_data, sizeof(HA_DATA_PARTITION));
+ }
+ if (is_not_tmp_table)
+ pthread_mutex_unlock(&table_share->mutex);
+ /*
Some handlers update statistics as part of the open call. This will in
some cases corrupt the statistics of the partition handler and thus
to ensure we have correct statistics we call info from open after
@@ -2539,6 +2576,7 @@ int ha_partition::close(void)
handler **file;
DBUG_ENTER("ha_partition::close");
+ DBUG_ASSERT(table->s == table_share);
delete_queue(&m_queue);
if (!is_clone)
bitmap_free(&(m_part_info->used_partitions));
@@ -2607,6 +2645,7 @@ int ha_partition::external_lock(THD *thd
handler **file;
DBUG_ENTER("ha_partition::external_lock");
+ DBUG_ASSERT(!auto_increment_lock && !auto_increment_safe_stmt_log_lock);
file= m_file;
m_lock_type= lock_type;
@@ -2825,8 +2864,9 @@ int ha_partition::write_row(uchar * buf)
uint32 part_id;
int error;
longlong func_value;
- bool autoincrement_lock= FALSE;
+ bool have_auto_increment= table->next_number_field && buf == table->record[0];
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;
#ifdef NOT_NEEDED
@@ -2844,28 +2884,16 @@ int ha_partition::write_row(uchar * buf)
If we have an auto_increment column and we are writing a changed row
or a new row, then update the auto_increment value in the record.
*/
- if (table->next_number_field && buf == table->record[0])
+ if (have_auto_increment)
{
- /*
- Some engines (InnoDB for example) can change autoincrement
- counter only after 'table->write_row' operation.
- So if another thread gets inside the ha_partition::write_row
- before it is complete, it gets same auto_increment value,
- which means DUP_KEY error (bug #27405)
- Here we separate the access using table_share->mutex, and
- use autoincrement_lock variable to avoid unnecessary locks.
- Probably not an ideal solution.
- */
- if (table_share->tmp_table == NO_TMP_TABLE)
+ if (!ha_data->auto_inc_initialized &&
+ !table->s->next_number_keypart)
{
/*
- Bug#30878 crash when alter table from non partitioned table
- to partitioned.
- Checking if tmp table then there is no need to lock,
- and the table_share->mutex may not be initialised.
+ If auto_increment in table_share is not initialized, start by
+ initializing it.
*/
- autoincrement_lock= TRUE;
- pthread_mutex_lock(&table_share->mutex);
+ info(HA_STATUS_AUTO);
}
error= update_auto_increment();
@@ -2903,11 +2931,11 @@ int ha_partition::write_row(uchar * buf)
DBUG_PRINT("info", ("Insert in partition %d", part_id));
tmp_disable_binlog(thd); /* Do not replicate the low-level changes. */
error= m_file[part_id]->ha_write_row(buf);
+ if (have_auto_increment && !table->s->next_number_keypart)
+ set_auto_increment_if_higher(table->next_number_field->val_int());
reenable_binlog(thd);
exit:
table->timestamp_field_type= orig_timestamp_type;
- if (autoincrement_lock)
- pthread_mutex_unlock(&table_share->mutex);
DBUG_RETURN(error);
}
@@ -2931,13 +2959,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
@@ -2969,17 +2990,23 @@ int ha_partition::update_row(const uchar
goto exit;
}
- /*
- TODO:
- set_internal_auto_increment=
- max(set_internal_auto_increment, new_data->auto_increment)
- */
m_last_part= new_part_id;
if (new_part_id == old_part_id)
{
DBUG_PRINT("info", ("Update in partition %d", new_part_id));
tmp_disable_binlog(thd); /* Do not replicate the low-level changes. */
error= m_file[new_part_id]->ha_update_row(old_data, new_data);
+ /*
+ if updating an auto_increment column, update
+ 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->found_next_number_field && new_data == table->record[0] &&
+ !table->s->next_number_keypart)
+ set_auto_increment_if_higher(table->found_next_number_field->val_int());
reenable_binlog(thd);
goto exit;
}
@@ -2989,6 +3016,9 @@ 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->found_next_number_field && new_data == table->record[0] &&
+ !table->s->next_number_keypart)
+ set_auto_increment_if_higher(table->found_next_number_field->val_int());
reenable_binlog(thd);
if (error)
goto exit;
@@ -3084,8 +3114,17 @@ int ha_partition::delete_all_rows()
{
int error;
handler **file;
+ THD *thd= ha_thd();
DBUG_ENTER("ha_partition::delete_all_rows");
+ if (thd->lex->sql_command == SQLCOM_TRUNCATE)
+ {
+ HA_DATA_PARTITION *ha_data= (HA_DATA_PARTITION*) table_share->ha_data;
+ lock_auto_increment();
+ ha_data->next_auto_inc_val= 0;
+ ha_data->auto_inc_initialized= FALSE;
+ unlock_auto_increment();
+ }
file= m_file;
do
{
@@ -4251,6 +4290,17 @@ int ha_partition::handle_unordered_scan_
break;
case partition_index_first:
DBUG_PRINT("info", ("index_first on partition %d", i));
+ /* MyISAM engine can fail if we call index_first() when indexes disabled */
+ /* that happens if the table is empty. */
+ /* Here we use file->stats.records instead of file->records() because */
+ /* file->records() is supposed to return an EXACT count, and it can be */
+ /* possibly slow. We don't need an exact number, an approximate one- from*/
+ /* the last ::info() call - is sufficient. */
+ if (file->stats.records == 0)
+ {
+ error= HA_ERR_END_OF_FILE;
+ break;
+ }
error= file->index_first(buf);
break;
case partition_index_first_unordered:
@@ -4338,10 +4388,32 @@ int ha_partition::handle_ordered_index_s
m_start_key.flag);
break;
case partition_index_first:
+ /* MyISAM engine can fail if we call index_first() when indexes disabled */
+ /* that happens if the table is empty. */
+ /* Here we use file->stats.records instead of file->records() because */
+ /* file->records() is supposed to return an EXACT count, and it can be */
+ /* possibly slow. We don't need an exact number, an approximate one- from*/
+ /* the last ::info() call - is sufficient. */
+ if (file->stats.records == 0)
+ {
+ error= HA_ERR_END_OF_FILE;
+ break;
+ }
error= file->index_first(rec_buf_ptr);
reverse_order= FALSE;
break;
case partition_index_last:
+ /* MyISAM engine can fail if we call index_last() when indexes disabled */
+ /* that happens if the table is empty. */
+ /* Here we use file->stats.records instead of file->records() because */
+ /* file->records() is supposed to return an EXACT count, and it can be */
+ /* possibly slow. We don't need an exact number, an approximate one- from*/
+ /* the last ::info() call - is sufficient. */
+ if (file->stats.records == 0)
+ {
+ error= HA_ERR_END_OF_FILE;
+ break;
+ }
error= file->index_last(rec_buf_ptr);
reverse_order= TRUE;
break;
@@ -4596,21 +4668,54 @@ int ha_partition::handle_ordered_prev(uc
int ha_partition::info(uint flag)
{
- handler *file, **file_array;
- DBUG_ENTER("ha_partition:info");
+ DBUG_ENTER("ha_partition::info");
if (flag & HA_STATUS_AUTO)
{
- ulonglong auto_increment_value= 0;
+ bool auto_inc_is_first_in_idx= (table_share->next_number_keypart == 0);
+ HA_DATA_PARTITION *ha_data= (HA_DATA_PARTITION*) table_share->ha_data;
DBUG_PRINT("info", ("HA_STATUS_AUTO"));
- file_array= m_file;
- do
+ if (!table->found_next_number_field)
+ stats.auto_increment_value= 0;
+ else if (ha_data->auto_inc_initialized)
+ {
+ lock_auto_increment();
+ stats.auto_increment_value= ha_data->next_auto_inc_val;
+ unlock_auto_increment();
+ }
+ else
{
- file= *file_array;
- file->info(HA_STATUS_AUTO);
- set_if_bigger(auto_increment_value, file->stats.auto_increment_value);
- } while (*(++file_array));
- stats.auto_increment_value= auto_increment_value;
+ lock_auto_increment();
+ /* to avoid two concurrent initializations, check again when locked */
+ if (ha_data->auto_inc_initialized)
+ stats.auto_increment_value= ha_data->next_auto_inc_val;
+ else
+ {
+ handler *file, **file_array;
+ ulonglong auto_increment_value= 0;
+ file_array= m_file;
+ DBUG_PRINT("info",
+ ("checking all partitions for auto_increment_value"));
+ do
+ {
+ file= *file_array;
+ file->info(HA_STATUS_AUTO);
+ set_if_bigger(auto_increment_value,
+ file->stats.auto_increment_value);
+ } while (*(++file_array));
+
+ DBUG_ASSERT(auto_increment_value);
+ stats.auto_increment_value= auto_increment_value;
+ if (auto_inc_is_first_in_idx)
+ {
+ set_if_bigger(ha_data->next_auto_inc_val, auto_increment_value);
+ ha_data->auto_inc_initialized= TRUE;
+ DBUG_PRINT("info", ("initializing next_auto_inc_val to %lu",
+ (ulong) ha_data->next_auto_inc_val));
+ }
+ }
+ unlock_auto_increment();
+ }
}
if (flag & HA_STATUS_VARIABLE)
{
@@ -4634,6 +4739,7 @@ int ha_partition::info(uint flag)
check_time: Time of last check (only applicable to MyISAM)
We report last time of all underlying handlers
*/
+ handler *file, **file_array;
stats.records= 0;
stats.deleted= 0;
stats.data_file_length= 0;
@@ -4715,6 +4821,7 @@ int ha_partition::info(uint flag)
So we calculate these constants by using the variables on the first
handler.
*/
+ handler *file;
file= m_file[0];
file->info(HA_STATUS_CONST);
@@ -4736,6 +4843,7 @@ int ha_partition::info(uint flag)
}
if (flag & HA_STATUS_TIME)
{
+ handler *file, **file_array;
DBUG_PRINT("info", ("info: HA_STATUS_TIME"));
/*
This flag is used to set the latest update time of the table.
@@ -5796,19 +5904,33 @@ int ha_partition::cmp_ref(const uchar *r
MODULE auto increment
****************************************************************************/
-void ha_partition::restore_auto_increment(ulonglong)
-{
- DBUG_ENTER("ha_partition::restore_auto_increment");
- DBUG_VOID_RETURN;
+int ha_partition::reset_auto_increment(ulonglong value)
+{
+ handler **file= m_file;
+ int res;
+ HA_DATA_PARTITION *ha_data= (HA_DATA_PARTITION*) table_share->ha_data;
+ DBUG_ENTER("ha_partition::reset_auto_increment");
+ lock_auto_increment();
+ ha_data->auto_inc_initialized= FALSE;
+ ha_data->next_auto_inc_val= 0;
+ do
+ {
+ if ((res= (*file)->ha_reset_auto_increment(value)) != 0)
+ break;
+ } while (*(++file));
+ unlock_auto_increment();
+ DBUG_RETURN(res);
}
-/*
+/**
This method is called by update_auto_increment which in turn is called
- by the individual handlers as part of write_row. We will always let
- the first handler keep track of the auto increment value for all
- partitions.
+ by the individual handlers as part of write_row. We use the
+ table_share->ha_data->next_auto_inc_val, or search all
+ partitions for the highest auto_increment_value if not initialized or
+ if auto_increment field is a secondary part of a key, we must search
+ every partition when holding a mutex to be sure of correctness.
*/
void ha_partition::get_auto_increment(ulonglong offset, ulonglong increment,
@@ -5816,59 +5938,88 @@ void ha_partition::get_auto_increment(ul
ulonglong *first_value,
ulonglong *nb_reserved_values)
{
- ulonglong first_value_part, last_value_part, nb_reserved_values_part,
- last_value= ~ (ulonglong) 0;
- handler **pos, **end;
- bool retry= TRUE;
DBUG_ENTER("ha_partition::get_auto_increment");
-
-again:
- for (pos=m_file, end= m_file+ m_tot_parts; pos != end ; pos++)
+ DBUG_PRINT("info", ("offset: %lu inc: %lu desired_values: %lu "
+ "first_value: %lu", (ulong) offset, (ulong) increment,
+ (ulong) nb_desired_values, (ulong) *first_value));
+ DBUG_ASSERT(increment && nb_desired_values);
+ *first_value= 0;
+ if (table->s->next_number_keypart)
{
- first_value_part= *first_value;
- (*pos)->get_auto_increment(offset, increment, nb_desired_values,
- &first_value_part, &nb_reserved_values_part);
- if (first_value_part == ~(ulonglong)(0)) // error in one partition
- {
- *first_value= first_value_part;
- sql_print_error("Partition failed to reserve auto_increment value");
- DBUG_VOID_RETURN;
- }
/*
- Partition has reserved an interval. Intersect it with the intervals
- already reserved for the previous partitions.
+ next_number_keypart is != 0 if the auto_increment column is a secondary
+ column in the index (it is allowed in MyISAM)
*/
- last_value_part= (nb_reserved_values_part == ULONGLONG_MAX) ?
- ULONGLONG_MAX : (first_value_part + nb_reserved_values_part * increment);
- set_if_bigger(*first_value, first_value_part);
- set_if_smaller(last_value, last_value_part);
+ DBUG_PRINT("info", ("next_number_keypart != 0"));
+ ulonglong nb_reserved_values_part;
+ ulonglong first_value_part, max_first_value;
+ handler **file= m_file;
+ first_value_part= max_first_value= *first_value;
+ /* Must lock and find highest value among all partitions. */
+ lock_auto_increment();
+ do
+ {
+ /* Only nb_desired_values = 1 makes sense */
+ (*file)->get_auto_increment(offset, increment, 1,
+ &first_value_part, &nb_reserved_values_part);
+ if (first_value_part == ~(ulonglong)(0)) // error in one partition
+ {
+ *first_value= first_value_part;
+ /* log that the error was between table/partition handler */
+ sql_print_error("Partition failed to reserve auto_increment value");
+ unlock_auto_increment();
+ DBUG_VOID_RETURN;
+ }
+ DBUG_PRINT("info", ("first_value_part: %lu", (ulong) first_value_part));
+ set_if_bigger(max_first_value, first_value_part);
+ } while (*(++file));
+ *first_value= max_first_value;
+ *nb_reserved_values= 1;
+ unlock_auto_increment();
}
- if (last_value < *first_value) /* empty intersection, error */
+ else
{
+ THD *thd= ha_thd();
+ HA_DATA_PARTITION *ha_data= (HA_DATA_PARTITION*) table_share->ha_data;
/*
- When we have an empty intersection, it means that one or more
- partitions may have a significantly different autoinc next value.
- We should not fail here - it just means that we should try to
- find a new reservation making use of the current *first_value
- wbich should now be compatible with all partitions.
+ This is initialized in the beginning of the first write_row call.
*/
- if (retry)
- {
- retry= FALSE;
- last_value= ~ (ulonglong) 0;
- release_auto_increment();
- goto again;
- }
+ DBUG_ASSERT(ha_data->auto_inc_initialized);
/*
- We should not get here.
+ Get a lock for handling the auto_increment in table_share->ha_data
+ for avoiding two concurrent statements getting the same number.
+ */
+
+ lock_auto_increment();
+
+ /*
+ In a multi-row insert statement like INSERT SELECT and LOAD DATA
+ where the number of candidate rows to insert is not known in advance
+ we must hold a lock/mutex for the whole statement if we have statement
+ based replication. Because the statement-based binary log contains
+ only the first generated value used by the statement, and slaves assumes
+ all other generated values used by this statement were consecutive to
+ this first one, we must exclusively lock the generator until the statement
+ is done.
*/
- sql_print_error("Failed to calculate auto_increment value for partition");
-
- *first_value= ~(ulonglong)(0);
+ if (!auto_increment_safe_stmt_log_lock &&
+ thd->lex->sql_command != SQLCOM_INSERT &&
+ mysql_bin_log.is_open() &&
+ !thd->current_stmt_binlog_row_based &&
+ (thd->options & OPTION_BIN_LOG))
+ {
+ DBUG_PRINT("info", ("locking auto_increment_safe_stmt_log_lock"));
+ auto_increment_safe_stmt_log_lock= TRUE;
+ }
+
+ /* this gets corrected (for offset/increment) in update_auto_increment */
+ *first_value= ha_data->next_auto_inc_val;
+ ha_data->next_auto_inc_val+= nb_desired_values * increment;
+
+ unlock_auto_increment();
+ DBUG_PRINT("info", ("*first_value: %lu", (ulong) *first_value));
+ *nb_reserved_values= nb_desired_values;
}
- if (increment) // If not check for values
- *nb_reserved_values= (last_value == ULONGLONG_MAX) ?
- ULONGLONG_MAX : ((last_value - *first_value) / increment);
DBUG_VOID_RETURN;
}
@@ -5876,9 +6027,31 @@ void ha_partition::release_auto_incremen
{
DBUG_ENTER("ha_partition::release_auto_increment");
- for (uint i= 0; i < m_tot_parts; i++)
+ if (table->s->next_number_keypart)
+ {
+ for (uint i= 0; i < m_tot_parts; i++)
+ m_file[i]->ha_release_auto_increment();
+ }
+ else if (next_insert_id)
{
- m_file[i]->ha_release_auto_increment();
+ HA_DATA_PARTITION *ha_data= (HA_DATA_PARTITION*) table_share->ha_data;
+ ulonglong next_auto_inc_val;
+ lock_auto_increment();
+ next_auto_inc_val= ha_data->next_auto_inc_val;
+ if (next_insert_id < next_auto_inc_val &&
+ auto_inc_interval_for_cur_row.maximum() >= next_auto_inc_val)
+ ha_data->next_auto_inc_val= next_insert_id;
+ DBUG_PRINT("info", ("ha_data->next_auto_inc_val: %lu",
+ (ulong) ha_data->next_auto_inc_val));
+
+ /* Unlock the multi row statement lock taken in get_auto_increment */
+ if (auto_increment_safe_stmt_log_lock)
+ {
+ auto_increment_safe_stmt_log_lock= FALSE;
+ DBUG_PRINT("info", ("unlocking auto_increment_safe_stmt_log_lock"));
+ }
+
+ unlock_auto_increment();
}
DBUG_VOID_RETURN;
}
=== modified file 'sql/ha_partition.h'
--- a/sql/ha_partition.h 2008-09-18 19:49:34 +0000
+++ b/sql/ha_partition.h 2008-10-01 10:14:55 +0000
@@ -37,6 +37,15 @@ typedef struct st_partition_share
} PARTITION_SHARE;
#endif
+/**
+ Partition specific ha_data struct.
+ @todo: move all partition specific data from TABLE_SHARE here.
+*/
+typedef struct st_ha_data_partition
+{
+ ulonglong next_auto_inc_val; /**< first non reserved value */
+ bool auto_inc_initialized;
+} HA_DATA_PARTITION;
#define PARTITION_BYTES_IN_POS 2
class ha_partition :public handler
@@ -140,6 +149,12 @@ private:
"own" the m_part_info structure.
*/
bool is_clone;
+ bool auto_increment_lock; /**< lock reading/updating auto_inc */
+ /**
+ Flag to keep the auto_increment lock through out the statement.
+ This to ensure it will work with statement based replication.
+ */
+ bool auto_increment_safe_stmt_log_lock;
public:
handler *clone(MEM_ROOT *mem_root);
virtual void set_part_info(partition_info *part_info)
@@ -196,8 +211,8 @@ public:
virtual char *update_table_comment(const char *comment);
virtual int change_partitions(HA_CREATE_INFO *create_info,
const char *path,
- ulonglong *copied,
- ulonglong *deleted,
+ ulonglong * const copied,
+ ulonglong * const deleted,
const uchar *pack_frm_data,
size_t pack_frm_len);
virtual int drop_partitions(const char *path);
@@ -211,7 +226,7 @@ public:
virtual void change_table_ptr(TABLE *table_arg, TABLE_SHARE *share);
private:
int prepare_for_rename();
- int copy_partitions(ulonglong *copied, ulonglong *deleted);
+ int copy_partitions(ulonglong * const copied, ulonglong * const deleted);
void cleanup_new_partition(uint part_count);
int prepare_new_partition(TABLE *table, HA_CREATE_INFO *create_info,
handler *file, const char *part_name,
@@ -826,12 +841,51 @@ public:
auto_increment_column_changed
-------------------------------------------------------------------------
*/
- virtual void restore_auto_increment(ulonglong prev_insert_id);
virtual void get_auto_increment(ulonglong offset, ulonglong increment,
ulonglong nb_desired_values,
ulonglong *first_value,
ulonglong *nb_reserved_values);
virtual void release_auto_increment();
+private:
+ virtual int reset_auto_increment(ulonglong value);
+ virtual void lock_auto_increment()
+ {
+ /* lock already taken */
+ if (auto_increment_safe_stmt_log_lock)
+ return;
+ DBUG_ASSERT(table_share->ha_data && !auto_increment_lock);
+ if(table_share->tmp_table == NO_TMP_TABLE)
+ {
+ auto_increment_lock= TRUE;
+ pthread_mutex_lock(&table_share->mutex);
+ }
+ }
+ virtual void unlock_auto_increment()
+ {
+ DBUG_ASSERT(table_share->ha_data);
+ /*
+ If auto_increment_safe_stmt_log_lock is true, we have to keep the lock.
+ It will be set to false and thus unlocked at the end of the statement by
+ ha_partition::release_auto_increment.
+ */
+ if(auto_increment_lock && !auto_increment_safe_stmt_log_lock)
+ {
+ pthread_mutex_unlock(&table_share->mutex);
+ auto_increment_lock= FALSE;
+ }
+ }
+ virtual void set_auto_increment_if_higher(const ulonglong nr)
+ {
+ HA_DATA_PARTITION *ha_data= (HA_DATA_PARTITION*) table_share->ha_data;
+ lock_auto_increment();
+ /* must check when the mutex is taken */
+ if (nr >= ha_data->next_auto_inc_val)
+ ha_data->next_auto_inc_val= nr + 1;
+ ha_data->auto_inc_initialized= TRUE;
+ unlock_auto_increment();
+ }
+
+public:
/*
-------------------------------------------------------------------------
=== modified file 'sql/handler.cc'
--- a/sql/handler.cc 2008-10-01 10:21:15 +0000
+++ b/sql/handler.cc 2008-10-07 15:19:32 +0000
@@ -2202,7 +2202,12 @@ prev_insert_id(ulonglong nr, struct syst
- In both cases, the reserved intervals are remembered in
thd->auto_inc_intervals_in_cur_stmt_for_binlog if statement-based
binlogging; the last reserved interval is remembered in
- auto_inc_interval_for_cur_row.
+ auto_inc_interval_for_cur_row. The number of reserved intervals is
+ remembered in auto_inc_intervals_count. It differs from the number of
+ elements in thd->auto_inc_intervals_in_cur_stmt_for_binlog() because the
+ latter list is cumulative over all statements forming one binlog event
+ (when stored functions and triggers are used), and collapses two
+ contiguous intervals in one (see its append() method).
The idea is that generated auto_increment values are predictable and
independent of the column values in the table. This is needed to be
@@ -2286,8 +2291,6 @@ int handler::update_auto_increment()
handler::estimation_rows_to_insert was set by
handler::ha_start_bulk_insert(); if 0 it means "unknown".
*/
- uint nb_already_reserved_intervals=
- thd->auto_inc_intervals_in_cur_stmt_for_binlog.nb_elements();
ulonglong nb_desired_values;
/*
If an estimation was given to the engine:
@@ -2299,17 +2302,17 @@ int handler::update_auto_increment()
start, starting from AUTO_INC_DEFAULT_NB_ROWS.
Don't go beyond a max to not reserve "way too much" (because
reservation means potentially losing unused values).
+ Note that in prelocked mode no estimation is given.
*/
- if (nb_already_reserved_intervals == 0 &&
- (estimation_rows_to_insert > 0))
+ if ((auto_inc_intervals_count == 0) && (estimation_rows_to_insert > 0))
nb_desired_values= estimation_rows_to_insert;
else /* go with the increasing defaults */
{
/* avoid overflow in formula, with this if() */
- if (nb_already_reserved_intervals <= AUTO_INC_DEFAULT_NB_MAX_BITS)
+ if (auto_inc_intervals_count <= AUTO_INC_DEFAULT_NB_MAX_BITS)
{
- nb_desired_values= AUTO_INC_DEFAULT_NB_ROWS *
- (1 << nb_already_reserved_intervals);
+ nb_desired_values= AUTO_INC_DEFAULT_NB_ROWS *
+ (1 << auto_inc_intervals_count);
set_if_smaller(nb_desired_values, AUTO_INC_DEFAULT_NB_MAX);
}
else
@@ -2322,7 +2325,7 @@ int handler::update_auto_increment()
&nb_reserved_values);
if (nr == ~(ulonglong) 0)
DBUG_RETURN(HA_ERR_AUTOINC_READ_FAILED); // Mark failure
-
+
/*
That rounding below should not be needed when all engines actually
respect offset and increment in get_auto_increment(). But they don't
@@ -2333,7 +2336,7 @@ int handler::update_auto_increment()
*/
nr= compute_next_insert_id(nr-1, variables);
}
-
+
if (table->s->next_number_keypart == 0)
{
/* We must defer the appending until "nr" has been possibly truncated */
@@ -2377,8 +2380,9 @@ int handler::update_auto_increment()
{
auto_inc_interval_for_cur_row.replace(nr, nb_reserved_values,
variables->auto_increment_increment);
+ auto_inc_intervals_count++;
/* Row-based replication does not need to store intervals in binlog */
- if (!thd->current_stmt_binlog_row_based)
+ if (mysql_bin_log.is_open() && !thd->current_stmt_binlog_row_based)
thd->auto_inc_intervals_in_cur_stmt_for_binlog.append(auto_inc_interval_for_cur_row.minimum(),
auto_inc_interval_for_cur_row.values(),
variables->auto_increment_increment);
@@ -2498,6 +2502,7 @@ void handler::ha_release_auto_increment(
release_auto_increment();
insert_id_for_cur_row= 0;
auto_inc_interval_for_cur_row.replace(0, 0, 0);
+ auto_inc_intervals_count= 0;
if (next_insert_id > 0)
{
next_insert_id= 0;
=== modified file 'sql/handler.h'
--- a/sql/handler.h 2008-08-11 18:02:03 +0000
+++ b/sql/handler.h 2008-10-06 14:06:59 +0000
@@ -1129,6 +1129,13 @@ public:
inserter.
*/
Discrete_interval auto_inc_interval_for_cur_row;
+ /**
+ Number of reserved auto-increment intervals. Serves as a heuristic
+ when we have no estimation of how many records the statement will insert:
+ the more intervals we have reserved, the bigger the next one. Reset in
+ handler::ha_release_auto_increment().
+ */
+ uint auto_inc_intervals_count;
handler(handlerton *ht_arg, TABLE_SHARE *share_arg)
:table_share(share_arg), table(0),
@@ -1137,7 +1144,8 @@ public:
ref_length(sizeof(my_off_t)),
ft_handler(0), inited(NONE),
locked(FALSE), implicit_emptied(0),
- pushed_cond(0), next_insert_id(0), insert_id_for_cur_row(0)
+ pushed_cond(0), next_insert_id(0), insert_id_for_cur_row(0),
+ auto_inc_intervals_count(0)
{}
virtual ~handler(void)
{
@@ -1241,8 +1249,8 @@ public:
int ha_change_partitions(HA_CREATE_INFO *create_info,
const char *path,
- ulonglong *copied,
- ulonglong *deleted,
+ ulonglong * const copied,
+ ulonglong * const deleted,
const uchar *pack_frm_data,
size_t pack_frm_len);
int ha_drop_partitions(const char *path);
@@ -1859,7 +1867,8 @@ private:
This is called to delete all rows in a table
If the handler don't support this, then this function will
return HA_ERR_WRONG_COMMAND and MySQL will delete the rows one
- by one.
+ by one. It should reset auto_increment if
+ thd->lex->sql_command == SQLCOM_TRUNCATE.
*/
virtual int delete_all_rows()
{ return (my_errno=HA_ERR_WRONG_COMMAND); }
@@ -1898,8 +1907,8 @@ private:
virtual int change_partitions(HA_CREATE_INFO *create_info,
const char *path,
- ulonglong *copied,
- ulonglong *deleted,
+ ulonglong * const copied,
+ ulonglong * const deleted,
const uchar *pack_frm_data,
size_t pack_frm_len)
{ return HA_ERR_WRONG_COMMAND; }
=== modified file 'sql/item.cc'
--- a/sql/item.cc 2008-09-19 12:34:37 +0000
+++ b/sql/item.cc 2008-10-02 14:44:49 +0000
@@ -1248,10 +1248,12 @@ Item_name_const::Item_name_const(Item *n
if (!(valid_args= name_item->basic_const_item() &&
(value_item->basic_const_item() ||
((value_item->type() == FUNC_ITEM) &&
- (((Item_func *) value_item)->functype() ==
- Item_func::NEG_FUNC) &&
+ ((((Item_func *) value_item)->functype() ==
+ Item_func::COLLATE_FUNC) ||
+ ((((Item_func *) value_item)->functype() ==
+ Item_func::NEG_FUNC) &&
(((Item_func *) value_item)->key_item()->type() !=
- FUNC_ITEM)))))
+ FUNC_ITEM)))))))
my_error(ER_WRONG_ARGUMENTS, MYF(0), "NAME_CONST");
Item::maybe_null= TRUE;
}
@@ -1336,6 +1338,7 @@ public:
else
Item_ident::print(str, query_type);
}
+ virtual Ref_Type ref_type() { return AGGREGATE_REF; }
};
=== modified file 'sql/item.h'
--- a/sql/item.h 2008-08-15 20:42:29 +0000
+++ b/sql/item.h 2008-10-02 14:44:49 +0000
@@ -2126,7 +2126,7 @@ class Item_ref :public Item_ident
protected:
void set_properties();
public:
- enum Ref_Type { REF, DIRECT_REF, VIEW_REF, OUTER_REF };
+ enum Ref_Type { REF, DIRECT_REF, VIEW_REF, OUTER_REF, AGGREGATE_REF };
Field *result_field; /* Save result here */
Item **ref;
Item_ref(Name_resolution_context *context_arg,
=== modified file 'sql/log.cc'
--- a/sql/log.cc 2008-09-03 20:04:07 +0000
+++ b/sql/log.cc 2008-10-06 14:06:59 +0000
@@ -4011,11 +4011,6 @@ bool MYSQL_BIN_LOG::write(Log_event *eve
DBUG_PRINT("info",("number of auto_inc intervals: %u",
thd->auto_inc_intervals_in_cur_stmt_for_binlog.
nb_elements()));
- /*
- If the auto_increment was second in a table's index (possible with
- MyISAM or BDB) (table->next_number_keypart != 0), such event is
- in fact not necessary. We could avoid logging it.
- */
Intvar_log_event e(thd, (uchar) INSERT_ID_EVENT,
thd->auto_inc_intervals_in_cur_stmt_for_binlog.
minimum());
=== modified file 'sql/log_event.cc'
--- a/sql/log_event.cc 2008-09-26 09:39:47 +0000
+++ b/sql/log_event.cc 2008-10-07 13:21:17 +0000
@@ -3350,6 +3350,17 @@ int Start_log_event_v3::do_apply_event(R
close_temporary_tables(thd);
cleanup_load_tmpdir();
}
+ else
+ {
+ /*
+ Set all temporary tables thread references to the current thread
+ as they may point to the "old" SQL slave thread in case of its
+ restart.
+ */
+ TABLE *table;
+ for (table= thd->temporary_tables; table; table= table->next)
+ table->in_use= thd;
+ }
break;
/*
@@ -8613,10 +8624,10 @@ int Rows_log_event::find_row(const Relay
the necessary bits on the bytes and don't set the filler bits
correctly.
*/
- my_ptrdiff_t const pos=
- table->s->null_bytes > 0 ? table->s->null_bytes - 1 : 0;
- table->record[0][pos]= 0xFF;
-
+ if (table->s->null_bytes > 0)
+ table->record[0][table->s->null_bytes - 1]|=
+ 256U - (1U << table->s->last_null_bit_pos);
+
if ((error= table->file->index_read_map(table->record[0], m_key,
HA_WHOLE_KEY,
HA_READ_KEY_EXACT)))
=== modified file 'sql/mysqld.cc'
--- a/sql/mysqld.cc 2008-09-29 13:53:40 +0000
+++ b/sql/mysqld.cc 2008-10-07 09:50:57 +0000
@@ -3384,7 +3384,7 @@ static int init_common_variables(const c
if (opt_slow_log && opt_slow_logname && !(log_output_options & LOG_FILE)
&& !(log_output_options & LOG_NONE))
sql_print_warning("Although a path was specified for the "
- "--log-slow-queries option, log tables are used. "
+ "--log_slow_queries option, log tables are used. "
"To enable logging to files use the --log-output=file option.");
s= opt_logname ? opt_logname : make_default_log_name(buff, ".log");
@@ -3753,23 +3753,25 @@ with --log-bin instead.");
unireg_abort(1);
}
if (!opt_bin_log)
- if (opt_binlog_format_id != BINLOG_FORMAT_UNSPEC)
{
- sql_print_error("You need to use --log-bin to make "
- "--binlog-format work.");
- unireg_abort(1);
- }
+ if (opt_binlog_format_id != BINLOG_FORMAT_UNSPEC)
+ {
+ sql_print_error("You need to use --log-bin to make "
+ "--binlog-format work.");
+ unireg_abort(1);
+ }
else
- {
- global_system_variables.binlog_format= BINLOG_FORMAT_MIXED;
+ {
+ global_system_variables.binlog_format= BINLOG_FORMAT_STMT;
}
+ }
else
if (opt_binlog_format_id == BINLOG_FORMAT_UNSPEC)
- global_system_variables.binlog_format= BINLOG_FORMAT_MIXED;
+ global_system_variables.binlog_format= BINLOG_FORMAT_STMT;
else
{
DBUG_ASSERT(global_system_variables.binlog_format != BINLOG_FORMAT_UNSPEC);
- }
+ }
/* Check that we have not let the format to unspecified at this point */
DBUG_ASSERT((uint)global_system_variables.binlog_format <=
@@ -5530,7 +5532,9 @@ enum options_mysqld
OPT_MIN_EXAMINED_ROW_LIMIT,
OPT_LOG_SLOW_SLAVE_STATEMENTS,
OPT_OLD_MODE,
- OPT_SLAVE_EXEC_MODE
+ OPT_SLAVE_EXEC_MODE,
+ OPT_GENERAL_LOG_FILE,
+ OPT_SLOW_QUERY_LOG_FILE
};
@@ -5727,7 +5731,7 @@ struct my_option my_long_options[] =
"Set up signals usable for debugging",
(uchar**) &opt_debugging, (uchar**) &opt_debugging,
0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
- {"general-log", OPT_GENERAL_LOG,
+ {"general_log", OPT_GENERAL_LOG,
"Enable|disable general log", (uchar**) &opt_log,
(uchar**) &opt_log, 0, GET_BOOL, OPT_ARG, 0, 0, 0, 0, 0, 0},
#ifdef HAVE_LARGE_PAGES
@@ -5763,8 +5767,12 @@ Disable with --skip-large-pages.",
(uchar**) &opt_local_infile,
(uchar**) &opt_local_infile, 0, GET_BOOL, OPT_ARG,
1, 0, 0, 0, 0, 0},
- {"log", 'l', "Log connections and queries to file.", (uchar**) &opt_logname,
+ {"log", 'l', "Log connections and queries to file (deprecated option, use "
+ "--general_log/--general_log_file instead).", (uchar**) &opt_logname,
(uchar**) &opt_logname, 0, GET_STR, OPT_ARG, 0, 0, 0, 0, 0, 0},
+ {"general_log_file", OPT_GENERAL_LOG_FILE,
+ "Log connections and queries to given file.", (uchar**) &opt_logname,
+ (uchar**) &opt_logname, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
{"log-bin", OPT_BIN_LOG,
"Log update queries in binary format. Optional (but strongly recommended "
"to avoid replication problems if server's hostname changes) argument "
@@ -5838,10 +5846,17 @@ Disable with --skip-large-pages.",
(uchar**) &opt_log_slow_slave_statements,
(uchar**) &opt_log_slow_slave_statements,
0, GET_BOOL, NO_ARG, 0, 0, 0, 0, 0, 0},
- {"log-slow-queries", OPT_SLOW_QUERY_LOG,
- "Log slow queries to a table or log file. Defaults logging to table mysql.slow_log or hostname-slow.log if --log-output=file is used. Must be enabled to activate other slow log options.",
+ {"log_slow_queries", OPT_SLOW_QUERY_LOG,
+ "Log slow queries to a table or log file. Defaults logging to table "
+ "mysql.slow_log or hostname-slow.log if --log-output=file is used. "
+ "Must be enabled to activate other slow log options. "
+ "(deprecated option, use --slow_query_log/--slow_query_log_file instead)",
(uchar**) &opt_slow_logname, (uchar**) &opt_slow_logname, 0, GET_STR, OPT_ARG,
0, 0, 0, 0, 0, 0},
+ {"slow_query_log_file", OPT_SLOW_QUERY_LOG_FILE,
+ "Log slow queries to given log file. Defaults logging to hostname-slow.log. Must be enabled to activate other slow log options.",
+ (uchar**) &opt_slow_logname, (uchar**) &opt_slow_logname, 0, GET_STR,
+ REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
{"log-tc", OPT_LOG_TC,
"Path to transaction coordinator log (used for transactions that affect "
"more than one storage engine, when binary log is disabled)",
@@ -6223,7 +6238,7 @@ Can't be set to 1 if --log-slave-updates
{"skip-symlink", OPT_SKIP_SYMLINKS, "Don't allow symlinking of tables. Deprecated option. Use --skip-symbolic-links instead.",
0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0},
{"skip-thread-priority", OPT_SKIP_PRIOR,
- "Don't give threads different priorities.", 0, 0, 0, GET_NO_ARG, NO_ARG,
+ "Don't give threads different priorities. Deprecated option.", 0, 0, 0, GET_NO_ARG, NO_ARG,
DEFAULT_SKIP_THREAD_PRIORITY, 0, 0, 0, 0, 0},
#ifdef HAVE_REPLICATION
{"slave-load-tmpdir", OPT_SLAVE_LOAD_TMPDIR,
@@ -7535,13 +7550,13 @@ static void mysql_init_variables(void)
have_community_features = SHOW_OPTION_YES;
#else
have_community_features = SHOW_OPTION_NO;
+#endif
global_system_variables.ndb_index_stat_enable=FALSE;
max_system_variables.ndb_index_stat_enable=TRUE;
global_system_variables.ndb_index_stat_cache_entries=32;
max_system_variables.ndb_index_stat_cache_entries=~0L;
global_system_variables.ndb_index_stat_update_freq=20;
max_system_variables.ndb_index_stat_update_freq=~0L;
-#endif
#ifdef HAVE_OPENSSL
have_ssl=SHOW_OPTION_YES;
#else
@@ -7649,6 +7664,9 @@ mysqld_get_one_option(int optid,
default_collation_name= 0;
break;
case 'l':
+ WARN_DEPRECATED(NULL, "7.0", "--log", "'--general_log'/'--general_log_file'");
+ /* FALL-THROUGH */
+ case OPT_GENERAL_LOG_FILE:
opt_log=1;
break;
case 'h':
@@ -7818,6 +7836,9 @@ mysqld_get_one_option(int optid,
}
#endif /* HAVE_REPLICATION */
case (int) OPT_SLOW_QUERY_LOG:
+ WARN_DEPRECATED(NULL, "7.0", "--log_slow_queries", "'--slow_query_log'/'--slow_query_log_file'");
+ /* FALL-THROUGH */
+ case (int) OPT_SLOW_QUERY_LOG_FILE:
opt_slow_log= 1;
break;
#ifdef WITH_CSV_STORAGE_ENGINE
@@ -7865,6 +7886,9 @@ mysqld_get_one_option(int optid,
break;
case (int) OPT_SKIP_PRIOR:
opt_specialflag|= SPECIAL_NO_PRIOR;
+ sql_print_warning("The --skip-thread-priority startup option is deprecated "
+ "and will be removed in MySQL 7.0. MySQL 6.0 and up do not "
+ "give threads different priorities.");
break;
case (int) OPT_SKIP_LOCK:
opt_external_locking=0;
@@ -8207,7 +8231,7 @@ static void get_options(int *argc,char *
if ((opt_log_slow_admin_statements || opt_log_queries_not_using_indexes ||
opt_log_slow_slave_statements) &&
!opt_slow_log)
- sql_print_warning("options --log-slow-admin-statements, --log-queries-not-using-indexes and --log-slow-slave-statements have no effect if --log-slow-queries is not set");
+ sql_print_warning("options --log-slow-admin-statements, --log-queries-not-using-indexes and --log-slow-slave-statements have no effect if --log_slow_queries is not set");
#if defined(HAVE_BROKEN_REALPATH)
my_use_symdir=0;
=== modified file 'sql/set_var.cc'
--- a/sql/set_var.cc 2008-09-09 10:19:31 +0000
+++ b/sql/set_var.cc 2008-10-06 09:29:42 +0000
@@ -2391,6 +2391,12 @@ end:
bool sys_var_log_state::update(THD *thd, set_var *var)
{
bool res;
+
+ if (this == &sys_var_log)
+ WARN_DEPRECATED(thd, "7.0", "@@log", "'@@general_log'");
+ else if (this == &sys_var_log_slow)
+ WARN_DEPRECATED(thd, "7.0", "@@log_slow_queries", "'@@slow_query_log'");
+
pthread_mutex_lock(&LOCK_global_system_variables);
if (!var->save_result.ulong_value)
{
@@ -2405,6 +2411,11 @@ bool sys_var_log_state::update(THD *thd,
void sys_var_log_state::set_default(THD *thd, enum_var_type type)
{
+ if (this == &sys_var_log)
+ WARN_DEPRECATED(thd, "7.0", "@@log", "'@@general_log'");
+ else if (this == &sys_var_log_slow)
+ WARN_DEPRECATED(thd, "7.0", "@@log_slow_queries", "'@@slow_query_log'");
+
pthread_mutex_lock(&LOCK_global_system_variables);
logger.deactivate_log_handler(thd, log_type);
pthread_mutex_unlock(&LOCK_global_system_variables);
@@ -3711,7 +3722,7 @@ bool sys_var_thd_storage_engine::update(
void sys_var_thd_table_type::warn_deprecated(THD *thd)
{
- WARN_DEPRECATED(thd, "5.2", "table_type", "'storage_engine'");
+ WARN_DEPRECATED(thd, "5.2", "@@table_type", "'@@storage_engine'");
}
void sys_var_thd_table_type::set_default(THD *thd, enum_var_type type)
@@ -3973,8 +3984,8 @@ bool process_key_caches(process_key_cach
void sys_var_trust_routine_creators::warn_deprecated(THD *thd)
{
- WARN_DEPRECATED(thd, "5.2", "log_bin_trust_routine_creators",
- "'log_bin_trust_function_creators'");
+ WARN_DEPRECATED(thd, "5.2", "@@log_bin_trust_routine_creators",
+ "'@@log_bin_trust_function_creators'");
}
void sys_var_trust_routine_creators::set_default(THD *thd, enum_var_type type)
=== modified file 'sql/sp_head.cc'
--- a/sql/sp_head.cc 2008-09-30 05:41:30 +0000
+++ b/sql/sp_head.cc 2008-10-07 15:32:58 +0000
@@ -126,6 +126,9 @@ sp_get_item_value(THD *thd, Item *item,
if (cs->escape_with_backslash_is_dangerous)
buf.append(' ');
append_query_string(cs, result, &buf);
+ buf.append(" COLLATE '");
+ buf.append(item->collation.collation->name);
+ buf.append('\'');
str->copy(buf);
return str;
=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h 2008-09-17 12:54:50 +0000
+++ b/sql/sql_class.h 2008-10-06 14:06:59 +0000
@@ -1524,6 +1524,9 @@ public:
then the latter INSERT will insert no rows
(first_successful_insert_id_in_cur_stmt == 0), but storing "INSERT_ID=3"
in the binlog is still needed; the list's minimum will contain 3.
+ This variable is cumulative: if several statements are written to binlog
+ as one (stored functions or triggers are used) this list is the
+ concatenation of all intervals reserved by all statements.
*/
Discrete_intervals_list auto_inc_intervals_in_cur_stmt_for_binlog;
/* Used by replication and SET INSERT_ID */
=== modified file 'sql/sql_partition.cc'
--- a/sql/sql_partition.cc 2008-10-06 06:05:34 +0000
+++ b/sql/sql_partition.cc 2008-10-07 15:19:32 +0000
@@ -2999,6 +2999,8 @@ int get_partition_id_range_sub_hash(part
longlong local_func_value;
int error;
DBUG_ENTER("get_partition_id_range_sub_hash");
+ LINT_INIT(loc_part_id);
+ LINT_INIT(sub_part_id);
if (unlikely((error= get_partition_id_range(part_info, &loc_part_id,
func_value))))
@@ -3026,6 +3028,8 @@ int get_partition_id_range_sub_linear_ha
longlong local_func_value;
int error;
DBUG_ENTER("get_partition_id_range_sub_linear_hash");
+ LINT_INIT(loc_part_id);
+ LINT_INIT(sub_part_id);
if (unlikely((error= get_partition_id_range(part_info, &loc_part_id,
func_value))))
@@ -3055,6 +3059,7 @@ int get_partition_id_range_sub_key(parti
longlong local_func_value;
int error;
DBUG_ENTER("get_partition_id_range_sub_key");
+ LINT_INIT(loc_part_id);
if (unlikely((error= get_partition_id_range(part_info, &loc_part_id,
func_value))))
@@ -3078,6 +3083,7 @@ int get_partition_id_range_sub_linear_ke
longlong local_func_value;
int error;
DBUG_ENTER("get_partition_id_range_sub_linear_key");
+ LINT_INIT(loc_part_id);
if (unlikely((error= get_partition_id_range(part_info, &loc_part_id,
func_value))))
@@ -3102,6 +3108,7 @@ int get_partition_id_list_sub_hash(parti
longlong local_func_value;
int error;
DBUG_ENTER("get_partition_id_list_sub_hash");
+ LINT_INIT(sub_part_id);
if (unlikely((error= get_partition_id_list(part_info, &loc_part_id,
func_value))))
@@ -3129,6 +3136,7 @@ int get_partition_id_list_sub_linear_has
longlong local_func_value;
int error;
DBUG_ENTER("get_partition_id_list_sub_linear_hash");
+ LINT_INIT(sub_part_id);
if (unlikely((error= get_partition_id_list(part_info, &loc_part_id,
func_value))))
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2008-08-28 09:54:50 +0000
+++ b/sql/sql_select.cc 2008-10-02 14:44:49 +0000
@@ -14804,6 +14804,7 @@ setup_copy_fields(THD *thd, TMP_TABLE_PA
Item *pos;
List_iterator_fast<Item> li(all_fields);
Copy_field *copy= NULL;
+ IF_DBUG(Copy_field *copy_start);
res_selected_fields.empty();
res_all_fields.empty();
List_iterator_fast<Item> itr(res_all_fields);
@@ -14816,12 +14817,19 @@ setup_copy_fields(THD *thd, TMP_TABLE_PA
goto err2;
param->copy_funcs.empty();
+ IF_DBUG(copy_start= copy);
for (i= 0; (pos= li++); i++)
{
Field *field;
uchar *tmp;
Item *real_pos= pos->real_item();
- if (real_pos->type() == Item::FIELD_ITEM)
+ /*
+ Aggregate functions can be substituted for fields (by e.g. temp tables).
+ We need to filter those substituted fields out.
+ */
+ if (real_pos->type() == Item::FIELD_ITEM &&
+ !(real_pos != pos &&
+ ((Item_ref *)pos)->ref_type() == Item_ref::AGGREGATE_REF))
{
Item_field *item;
if (!(item= new Item_field(thd, ((Item_field*) real_pos))))
@@ -14868,6 +14876,7 @@ setup_copy_fields(THD *thd, TMP_TABLE_PA
goto err;
if (copy)
{
+ DBUG_ASSERT (param->field_count > (uint) (copy - copy_start));
copy->set(tmp, item->result_field);
item->result_field->move_field(copy->to_ptr,copy->to_null_ptr,1);
#ifdef HAVE_purify
=== modified file 'sql/table.h'
--- a/sql/table.h 2008-09-03 14:45:40 +0000
+++ b/sql/table.h 2008-10-01 10:14:55 +0000
@@ -410,6 +410,7 @@ typedef struct st_table_share
int cached_row_logging_check;
#ifdef WITH_PARTITION_STORAGE_ENGINE
+ /** @todo: Move into *ha_data for partitioning */
bool auto_partitioned;
const char *partition_info;
uint partition_info_len;
@@ -419,6 +420,9 @@ typedef struct st_table_share
handlerton *default_part_db_type;
#endif
+ /** place to store storage engine specific data */
+ void *ha_data;
+
/*
Set share's table cache key and update its db and table name appropriately.
=== modified file 'storage/blackhole/ha_blackhole.cc'
--- a/storage/blackhole/ha_blackhole.cc 2008-03-29 13:00:12 +0000
+++ b/storage/blackhole/ha_blackhole.cc 2008-10-02 09:02:38 +0000
@@ -18,6 +18,7 @@
#pragma implementation // gcc: Class implementation
#endif
+#define MYSQL_SERVER 1
#include "mysql_priv.h"
#include "ha_blackhole.h"
@@ -100,6 +101,24 @@ int ha_blackhole::write_row(uchar * buf)
DBUG_RETURN(table->next_number_field ? update_auto_increment() : 0);
}
+int ha_blackhole::update_row(const uchar *old_data, uchar *new_data)
+{
+ DBUG_ENTER("ha_blackhole::update_row");
+ THD *thd= ha_thd();
+ if (thd->system_thread == SYSTEM_THREAD_SLAVE_SQL && thd->query == NULL)
+ DBUG_RETURN(0);
+ DBUG_RETURN(HA_ERR_WRONG_COMMAND);
+}
+
+int ha_blackhole::delete_row(const uchar *buf)
+{
+ DBUG_ENTER("ha_blackhole::delete_row");
+ THD *thd= ha_thd();
+ if (thd->system_thread == SYSTEM_THREAD_SLAVE_SQL && thd->query == NULL)
+ DBUG_RETURN(0);
+ DBUG_RETURN(HA_ERR_WRONG_COMMAND);
+}
+
int ha_blackhole::rnd_init(bool scan)
{
DBUG_ENTER("ha_blackhole::rnd_init");
@@ -110,6 +129,9 @@ int ha_blackhole::rnd_init(bool scan)
int ha_blackhole::rnd_next(uchar *buf)
{
DBUG_ENTER("ha_blackhole::rnd_next");
+ THD *thd= ha_thd();
+ if (thd->system_thread == SYSTEM_THREAD_SLAVE_SQL && thd->query == NULL)
+ DBUG_RETURN(0);
DBUG_RETURN(HA_ERR_END_OF_FILE);
}
@@ -189,6 +211,9 @@ int ha_blackhole::index_read_map(uchar *
enum ha_rkey_function find_flag)
{
DBUG_ENTER("ha_blackhole::index_read");
+ THD *thd= ha_thd();
+ if (thd->system_thread == SYSTEM_THREAD_SLAVE_SQL && thd->query == NULL)
+ DBUG_RETURN(0);
DBUG_RETURN(HA_ERR_END_OF_FILE);
}
@@ -198,6 +223,9 @@ int ha_blackhole::index_read_idx_map(uch
enum ha_rkey_function find_flag)
{
DBUG_ENTER("ha_blackhole::index_read_idx");
+ THD *thd= ha_thd();
+ if (thd->system_thread == SYSTEM_THREAD_SLAVE_SQL && thd->query == NULL)
+ DBUG_RETURN(0);
DBUG_RETURN(HA_ERR_END_OF_FILE);
}
@@ -206,6 +234,9 @@ int ha_blackhole::index_read_last_map(uc
key_part_map keypart_map)
{
DBUG_ENTER("ha_blackhole::index_read_last");
+ THD *thd= ha_thd();
+ if (thd->system_thread == SYSTEM_THREAD_SLAVE_SQL && thd->query == NULL)
+ DBUG_RETURN(0);
DBUG_RETURN(HA_ERR_END_OF_FILE);
}
=== modified file 'storage/blackhole/ha_blackhole.h'
--- a/storage/blackhole/ha_blackhole.h 2007-11-16 10:49:59 +0000
+++ b/storage/blackhole/ha_blackhole.h 2008-10-02 09:02:38 +0000
@@ -53,7 +53,7 @@ public:
ulonglong table_flags() const
{
return(HA_NULL_IN_KEY | HA_CAN_FULLTEXT | HA_CAN_SQL_HANDLER |
- HA_BINLOG_STMT_CAPABLE |
+ HA_BINLOG_STMT_CAPABLE | HA_BINLOG_ROW_CAPABLE |
HA_CAN_INDEX_BLOBS | HA_AUTO_PART_KEY |
HA_FILE_BASED | HA_CAN_GEOMETRY | HA_CAN_INSERT_DELAYED);
}
@@ -72,7 +72,6 @@ public:
uint max_supported_key_part_length() const { return BLACKHOLE_MAX_KEY_LENGTH; }
int open(const char *name, int mode, uint test_if_locked);
int close(void);
- int write_row(uchar * buf);
int rnd_init(bool scan);
int rnd_next(uchar *buf);
int rnd_pos(uchar * buf, uchar *pos);
@@ -94,4 +93,8 @@ public:
THR_LOCK_DATA **store_lock(THD *thd,
THR_LOCK_DATA **to,
enum thr_lock_type lock_type);
+private:
+ virtual int write_row(uchar *buf);
+ virtual int update_row(const uchar *old_data, uchar *new_data);
+ virtual int delete_row(const uchar *buf);
};
=== modified file 'support-files/my-huge.cnf.sh'
--- a/support-files/my-huge.cnf.sh 2008-07-19 08:31:33 +0000
+++ b/support-files/my-huge.cnf.sh 2008-10-03 12:24:19 +0000
@@ -112,6 +112,9 @@ server-id = 1
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin
+#
+# binary logging format - mixed recommended
+#binlog_format=mixed
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
=== modified file 'support-files/my-innodb-heavy-4G.cnf.sh'
--- a/support-files/my-innodb-heavy-4G.cnf.sh 2008-07-09 22:13:42 +0000
+++ b/support-files/my-innodb-heavy-4G.cnf.sh 2008-10-03 12:24:19 +0000
@@ -191,6 +191,9 @@ tmp_table_size = 64M
# the ability to do point in time recovery from your latest backup.
log-bin=mysql-bin
+# binary logging format - mixed recommended
+binlog_format=mixed
+
# If you're using replication with chained slaves (A->B->C), you need to
# enable this option on server B. It enables logging of updates done by
# the slave thread into the slave's binary log.
=== modified file 'support-files/my-large.cnf.sh'
--- a/support-files/my-large.cnf.sh 2008-07-19 08:31:33 +0000
+++ b/support-files/my-large.cnf.sh 2008-10-03 12:24:19 +0000
@@ -53,6 +53,9 @@ skip-federated
# binary logging is required for replication
log-bin=mysql-bin
+# binary logging format - mixed recommended
+binlog_format=mixed
+
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
=== modified file 'support-files/my-medium.cnf.sh'
--- a/support-files/my-medium.cnf.sh 2008-07-19 08:31:33 +0000
+++ b/support-files/my-medium.cnf.sh 2008-10-03 12:24:19 +0000
@@ -51,6 +51,9 @@ skip-federated
# binary logging is required for replication
log-bin=mysql-bin
+# binary logging format - mixed recommended
+binlog_format=mixed
+
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
=== modified file 'support-files/my-small.cnf.sh'
--- a/support-files/my-small.cnf.sh 2008-07-09 22:13:42 +0000
+++ b/support-files/my-small.cnf.sh 2008-10-03 12:24:19 +0000
@@ -48,6 +48,9 @@ server-id = 1
# Uncomment the following if you want to log updates
#log-bin=mysql-bin
+# binary logging format - mixed recommended
+#binlog_format=mixed
+
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = @localstatedir@/
#innodb_data_file_path = ibdata1:10M:autoextend
| Thread |
|---|
| • bzr push into mysql-5.1 branch (kgeorge:2767 to 2770) | Georgi Kodinov | 7 Oct |
