1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
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
|
