path: root/mysql-test/extra/binlog_tests/binlog.test

#
# misc binlogging tests that do not require a slave running
#

-- source include/have_log_bin.inc
-- source include/not_embedded.inc
-- source include/have_innodb.inc
-- source include/have_debug.inc

--disable_warnings
drop table if exists t1, t2;
--enable_warnings
reset master;

create table t1 (a int) engine=innodb;
create table t2 (a int) engine=innodb;
begin;
insert t1 values (5);
commit;
begin;
insert t2 values (5);
commit;
# first COMMIT must be Query_log_event, second - Xid_log_event
source include/show_binlog_events.inc;
drop table t1,t2;

#
# binlog rotation after one big transaction
#
reset master;
let $1=100;

create table t1 (n int) engine=innodb;
begin;
--disable_query_log
while ($1)
{
 eval insert into t1 values($1 + 4);
 dec $1;
}
--enable_query_log
commit;
drop table t1;
--replace_column 2 # 5 #
--replace_regex /table_id: [0-9]+/table_id: #/ /\/\* xid=.* \*\//\/* xid= *\//
show binlog events in 'master-bin.000001' from 106;
--replace_column 2 # 5 #
--replace_regex /table_id: [0-9]+/table_id: #/ /\/\* xid=.* \*\//\/* xid= *\//
show binlog events in 'master-bin.000002' from 106;


#
# Bug#22540 - Incorrect value in column End_log_pos of
# SHOW BINLOG EVENTS using InnoDB
#

# the following tests will show that certain queries now return
# absolute offsets (from binlog start, rather than relative to
# the beginning of the current transaction).  under what
# conditions it should be allowed / is sensible to put the
# slider into the middle of a transaction is not our concern
# here; we just guarantee that if and when it's done, the
# user has valid offsets to use.  if the setter function still
# wants to throw a "positioning into middle of transaction"
# warning, that's its prerogative and handled elsewhere.

set @ac = @@autocommit;

# first show this to work for SHOW BINLOG EVENTS

set autocommit= 0;
reset master;
create table t1(n int) engine=innodb;
begin;
insert into t1 values (1);
insert into t1 values (2);
insert into t1 values (3);
commit;
drop table t1;
--replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/ /Server ver: [^,]*,/Server version,/
show binlog events from 0;

# now show that nothing breaks if we need to read from the cache more
# than once, resulting in split event-headers

set @bcs = @@binlog_cache_size;
set global binlog_cache_size=4096;
reset master;

create table t1 (a int) engine=innodb;

let $1=400;
disable_query_log;
begin;
while ($1)
{
 eval insert into t1 values( $1 );
 dec $1;
}
commit;
enable_query_log;

--replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/ /Server ver: [^,]*,/Server version,/
show binlog events from 0;

drop table t1;

set global binlog_cache_size=@bcs;
set session autocommit = @ac;

#
# Bug#33798: prepared statements improperly handle large unsigned ints
#
--disable_warnings
drop table if exists t1;
--enable_warnings
reset master;
create table t1 (a bigint unsigned, b bigint(20) unsigned);
prepare stmt from "insert into t1 values (?,?)";
set @a= 9999999999999999;
set @b= 14632475938453979136;
execute stmt using @a, @b;
deallocate prepare stmt;
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;

#
# Bug#35383: binlog playback and replication breaks due to 
# name_const substitution
#
DELIMITER //;
CREATE PROCEDURE p1(IN v1 INT)
BEGIN
  CREATE TABLE t1 SELECT v1;
  DROP TABLE t1;
END//
CREATE PROCEDURE p2()
BEGIN
  DECLARE v1 INT;
  CREATE TABLE t1 SELECT v1+1;
  DROP TABLE t1;
END//
CREATE PROCEDURE p3(IN v1 INT)
BEGIN
  CREATE TABLE t1 SELECT 1 FROM DUAL WHERE v1!=0;
  DROP TABLE t1;
END//
CREATE PROCEDURE p4(IN v1 INT)
BEGIN
  DECLARE v2 INT;
  CREATE TABLE t1 SELECT 1, v1, v2;
  DROP TABLE t1;
  CREATE TABLE t1 SELECT 1, v1+1, v2;
  DROP TABLE t1;
END//
DELIMITER ;//

CALL p1(1);
CALL p2();
CALL p3(0);
CALL p4(0);
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP PROCEDURE p3;
DROP PROCEDURE p4;

--echo End of 5.0 tests

# Test of a too big SET INSERT_ID: see if the truncated value goes
# into binlog (right), or the too big value (wrong); we look at the
# binlog further down with SHOW BINLOG EVENTS.
reset master;
create table t1 (id tinyint auto_increment primary key);
set insert_id=128;
insert into t1 values(null);
select * from t1;
drop table t1;

# bug#22027 
create table t1 (a int);
create table if not exists t2 select * from t1;

# bug#22762
create temporary table tt1 (a int);
create table if not exists t3 like tt1;

# BUG#25091 (A DELETE statement to mysql database is not logged with
# ROW mode format): Checking that some basic operations on tables in
# the mysql database is replicated even when the current database is
# 'mysql'.

--disable_warnings
USE mysql;
INSERT INTO user SET host='localhost', user='@#@', password=password('Just a test');
UPDATE user SET password=password('Another password') WHERE host='localhost' AND user='@#@';
DELETE FROM user WHERE host='localhost' AND user='@#@';
--enable_warnings

use test;
source include/show_binlog_events.inc;
drop table t1,t2,t3,tt1;

-- source extra/binlog_tests/binlog_insert_delayed.test

#Bug #26079 max_binlog_size + innodb = not make new binlog and hang server
# server should not hang, binlog must rotate in the end
reset master;
--disable_warnings
drop table if exists t3;
--enable_warnings
create table t3 (a int(11) NOT NULL AUTO_INCREMENT, b text, PRIMARY KEY (a) ) engine=innodb;
show master status;
let $it=4;
while ($it)
{
insert into t3(b) values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
dec $it;
}
show master status /* must show new binlog index after rotating */;
drop table t3;

--echo #
--echo # Bug #45998: database crashes when running "create as select"
--echo #
CREATE DATABASE test1;
USE test1;
DROP DATABASE test1;
CREATE TABLE test.t1(a int);
INSERT INTO test.t1 VALUES (1), (2);
CREATE TABLE test.t2 SELECT * FROM test.t1;
USE test;
DROP TABLES t1, t2;