~drizzle-trunk/drizzle/development

# The two bugs below (BUG#25507 and BUG#26116) existed only in

# statement-based binlogging; we test that now they are fixed;

# we also test that mixed and row-based binlogging work too,

# for completeness.

connection master;

--disable_warnings

CREATE SCHEMA IF NOT EXISTS mysqlslap;

USE mysqlslap;

--enable_warnings

select @@global.binlog_format;

#

# BUG#25507 "multi-row insert delayed + auto increment causes

# duplicate key entries on slave";

# happened only in statement-based binlogging.

#

CREATE TABLE t1 (id INT primary key auto_increment, name VARCHAR(64));

let $query = "INSERT DELAYED INTO t1 VALUES (null, 'Dr. No'), (null, 'From Russia With Love'), (null, 'Goldfinger'), (null, 'Thunderball'), (null, 'You Only Live Twice')";

--exec $DRIZZLE_SLAP --silent --concurrency=5 --iterations=200 --query=$query --delimiter=";"

FLUSH TABLE t1; # another way to be sure INSERT DELAYED has inserted

SELECT COUNT(*) FROM t1;

# when bug existed slave failed below ("duplicate key" error at random INSERT)

sync_slave_with_master;

use mysqlslap;

SELECT COUNT(*) FROM t1;

#

# BUG#26116 "If multi-row INSERT DELAYED has errors,

# statement-based binlogging breaks";

# happened only in statement-based binlogging.

#

connection master;

truncate table t1;

# first scenario: duplicate on first row

insert delayed into t1 values(10, "my name");

if ($binlog_format_statement)

{

  # statement below will be converted to non-delayed INSERT and so

  # will stop at first error, guaranteeing replication.

  --error ER_DUP_ENTRY

  insert delayed into t1 values(10, "is Bond"), (20, "James Bond");

}

if (!$binlog_format_statement)

{

  insert delayed into t1 values(10, "is Bond"), (20, "James Bond");

}

flush table t1; # to wait for INSERT DELAYED to be done

select * from t1;

sync_slave_with_master;

# when bug existed in statement-based binlogging, t1 on slave had

# different content from on master

select * from t1;

# second scenario: duplicate on second row

connection master;

delete from t1 where id!=10;

if ($binlog_format_statement)

{

  # statement below will be converted to non-delayed INSERT and so

  # will be binlogged with its ER_DUP_ENTRY error code, guaranteeing

  # replication (slave will hit the same error code and so be fine).

  --error ER_DUP_ENTRY

  insert delayed into t1 values(20, "is Bond"), (10, "James Bond");

}

if (!$binlog_format_statement)

{

  insert delayed into t1 values(20, "is Bond"), (10, "James Bond");

}

flush table t1; # to wait for INSERT DELAYED to be done

select * from t1;

sync_slave_with_master;

# when bug existed in statement-based binlogging, query was binlogged

# with error_code=0 so slave stopped

select * from t1;

# clean up

connection master;

USE test;

DROP SCHEMA mysqlslap;

sync_slave_with_master;

use test;

connection master;

#

# Bug #29571: INSERT DELAYED IGNORE written to binary log on the master but

# on the slave

#

if  (`SELECT @@global.binlog_format != 'ROW'`)

{

  #flush the logs before the test

  connection slave;

  FLUSH LOGS;

  connection master;

  FLUSH LOGS;

}

CREATE TABLE t1(a int, UNIQUE(a));

INSERT DELAYED IGNORE INTO t1 VALUES(1);

INSERT DELAYED IGNORE INTO t1 VALUES(1);

flush table t1; # to wait for INSERT DELAYED to be done

if  (`SELECT @@global.binlog_format != 'ROW'`)

{

  #must show two INSERT DELAYED

  --replace_column 1 x 2 x 3 x 4 x 5 x

  show binlog events in 'master-bin.000002' LIMIT 2,2;

}

select * from t1;

sync_slave_with_master;

echo On slave;

if  (`SELECT @@global.binlog_format != 'ROW'`)

{

  #must show two INSERT DELAYED

  --replace_column 1 x 2 x 3 x 4 x 5 x

  show binlog events in 'slave-bin.000002' LIMIT 2,2;

}

select * from t1;

# clean up

connection master;

drop table t1;

sync_slave_with_master;

if  (`SELECT @@global.binlog_format != 'ROW'`)

{

  #flush the logs after the test

  FLUSH LOGS;

  connection master;

  FLUSH LOGS;

}

connection master;

--echo End of 5.0 tests