1SET @old_binlog_format= @@global.binlog_format; 2INSTALL PLUGIN example SONAME 'ha_example'; 3################################################################################ 4# Verifies if ER_BINLOG_STMT_MODE_AND_ROW_ENGINE happens by setting the binlog 5# format to STATEMENT and the transaction isolation level to READ COMMITTED as 6# such changes force Innodb to accept changes in the row format. 7# 8# When CREATE TABLE, ALTER TABLE, CREATE INDEX and CREATE TRIGGER are executed 9# any error should be triggered. 10# 11# In contrast, CREATE TABLE ... SELECT should trigger the following error: 12# ER_BINLOG_STMT_MODE_AND_ROW_ENGINE. 13################################################################################ 14SET binlog_format = STATEMENT; 15SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; 16CREATE TABLE t_row (a VARCHAR(100)) ENGINE = InnoDB; 17ALTER TABLE t_row ADD COLUMN b INT; 18CREATE TRIGGER trig_row BEFORE INSERT ON t_row FOR EACH ROW INSERT INTO t_stmt VALUES (1); 19CREATE INDEX i ON t_row(a); 20CREATE TABLE t_row_new ENGINE = InnoDB SELECT * FROM t_row; 21ERROR HY000: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. 22DROP TABLE t_row; 23 24 25################################################################################ 26# Verifies if ER_BINLOG_ROW_MODE_AND_STMT_ENGINE happens by setting the binlog 27# format to ROW and using a engine, i.e. EXAMPLE, that only supports STATEMENT. 28# 29# When CREATE TABLE, ALTER TABLE, CREATE INDEX and CREATE TRIGGER are executed 30# the error ER_BINLOG_ROW_MODE_AND_STMT_ENGINE is not triggered. Note that other 31# errors are triggered due to restrictions in the engine. 32# 33# In contrast, CREATE TABLE ... SELECT should trigger the following error: 34# ER_BINLOG_ROW_MODE_AND_STMT_ENGINE. 35################################################################################ 36SET binlog_format = ROW; 37CREATE TABLE t_stmt (a VARCHAR(100)) ENGINE = EXAMPLE; 38ALTER TABLE t_stmt ADD COLUMN b INT; 39CREATE TRIGGER trig_stmt BEFORE INSERT ON t_stmt FOR EACH ROW INSERT INTO t_stmt VALUES (1); 40CREATE INDEX i ON t_stmt(a); 41ERROR 42000: Too many key parts specified; max 0 parts allowed 42CREATE TABLE t_stmt_new ENGINE = EXAMPLE SELECT * FROM t_stmt; 43ERROR HY000: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = ROW and at least one table uses a storage engine limited to statement-based logging 44DROP TABLE t_stmt; 45 46 47################################################################################ 48# CLEAN UP # 49################################################################################ 50flush tables; 51UNINSTALL PLUGIN example; 52SET @@global.binlog_format = @old_binlog_format; 53SET @@session.binlog_format = @old_binlog_format; 54