include/master-slave.inc
[connection master]
call mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.');
connection master;
create table tm (b int auto_increment, a int, primary key (a,b)) engine= myisam;
create table ti (b int auto_increment, a int, primary key (a,b)) engine= innodb;
ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key
create table ti (b int auto_increment, a int, primary key (b,a)) engine= innodb;
set @@binlog_format=statement;
*** autoincrement field is not the first in PK warning must be there: ***
insert into tm set b=null, a=1;
Warnings:
Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT into autoincrement field which is not the first part in the composed primary key is unsafe
show warnings;
Level	Code	Message
Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT into autoincrement field which is not the first part in the composed primary key is unsafe
*** no warning when autoincrement is the first in PK
insert into ti set b=null, a=1;
show warnings;
Level	Code	Message
create function multi_part_pk_with_autoinc (arg int)
returns int
begin
insert into tm set b=null, a=arg;
return arg;
end//
select multi_part_pk_with_autoinc (3);
multi_part_pk_with_autoinc (3)
3
Warnings:
Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly
Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT into autoincrement field which is not the first part in the composed primary key is unsafe
*** autoincrement field is not the first in PK warning must be there: ***
show warnings;
Level	Code	Message
Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly
Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT into autoincrement field which is not the first part in the composed primary key is unsafe
set @@binlog_format=mixed;
insert into tm set b=null, a=2;
connection slave;
connection master;
drop table tm, ti;
drop function multi_part_pk_with_autoinc;
connection slave;
include/rpl_end.inc