WINDOWS平台,MYSQL5.1.42A表没有触发器,INSERT INTO A VALUES(10);
这样在二进制日志中MYSQLLOGBIN 打开看,可以看到这个INSERT如果在A中创建一个触发器,修改另外的表,那么再INSERT INTO A VALUES(10),这样二进制打开就看不到这个语句。而是
#101018 18:24:55 server id 1 end_log_pos 3976 Table_map: `cpc`.`terminal` mapped to number 59
#101018 18:24:55 server id 1 end_log_pos 4043 Table_map: `cpc`.`terminal_addtion` mapped to number 115
#101018 18:24:55 server id 1 end_log_pos 4124 Table_map: `cpc`.`terminal_gis` mapped to number 109
#101018 18:24:55 server id 1 end_log_pos 4328 Write_rows: table id 59
#101018 18:24:55 server id 1 end_log_pos 4446 Write_rows: table id 109
#101018 18:24:55 server id 1 end_log_pos 4531 Write_rows: table id 115 flags: STMT_END_FBINLOG '
dyC8TBMBAAAAiAAAAIgPAAAAADsAAAAAAAAAA2NwYwAIdGVybWluYWwAJwMD/v4DAwMDAw8PDwwMA/7+Dw8PDw8PDwMPDw8EBA8PDw8PDwMDAzD+A/4DPAA8ADwA/gP+AzwAPAA8ADwAPAA8ADwAPAA8
AJYABAQ8ADwAPAA8ADwAPAD+r///fw==yC8TBMBAAAAQwAAAMsPAAAAAHMAAAAAAAAAA2NwYwAQdGVybWluYWxfYWRkdGlvbgAJAwMMAwMD
DwMPBBQAFAC+AQ==
根本没有INSERT语句出来。。版主帮我看看啥问题啊??
这样在二进制日志中MYSQLLOGBIN 打开看,可以看到这个INSERT如果在A中创建一个触发器,修改另外的表,那么再INSERT INTO A VALUES(10),这样二进制打开就看不到这个语句。而是
#101018 18:24:55 server id 1 end_log_pos 3976 Table_map: `cpc`.`terminal` mapped to number 59
#101018 18:24:55 server id 1 end_log_pos 4043 Table_map: `cpc`.`terminal_addtion` mapped to number 115
#101018 18:24:55 server id 1 end_log_pos 4124 Table_map: `cpc`.`terminal_gis` mapped to number 109
#101018 18:24:55 server id 1 end_log_pos 4328 Write_rows: table id 59
#101018 18:24:55 server id 1 end_log_pos 4446 Write_rows: table id 109
#101018 18:24:55 server id 1 end_log_pos 4531 Write_rows: table id 115 flags: STMT_END_FBINLOG '
dyC8TBMBAAAAiAAAAIgPAAAAADsAAAAAAAAAA2NwYwAIdGVybWluYWwAJwMD/v4DAwMDAw8PDwwMA/7+Dw8PDw8PDwMPDw8EBA8PDw8PDwMDAzD+A/4DPAA8ADwA/gP+AzwAPAA8ADwAPAA8ADwAPAA8
AJYABAQ8ADwAPAA8ADwAPAD+r///fw==yC8TBMBAAAAQwAAAMsPAAAAAHMAAAAAAAAAA2NwYwAQdGVybWluYWxfYWRkdGlvbgAJAwMMAwMD
DwMPBBQAFAC+AQ==
根本没有INSERT语句出来。。版主帮我看看啥问题啊??
`cpc`.`terminal_addtion` mapped to number 115
`cpc`.`terminal_gisterminal是A表,terminal_addtion和terminal_gis事A表的INSERT触发器同时插入的另外2个表。
#101018 18:27:09 server id 1 end_log_pos 5850 Delete_rows: table id 115
#101018 18:27:09 server id 1 end_log_pos 5968 Delete_rows: table id 109
#101018 18:27:09 server id 1 end_log_pos 6172 Delete_rows: table id 59
#101018 18:27:09 server id 1 end_log_pos 6261 Delete_rows: table id 115
#101018 18:27:09 server id 1 end_log_pos 6379 Delete_rows: table id 109
#101018 18:27:09 server id 1 end_log_pos 6583 Delete_rows: table id 59
#101018 18:27:09 server id 1 end_log_pos 6672 Delete_rows: table id 115
#101018 18:27:09 server id 1 end_log_pos 6790 Delete_rows: table id 109
#101018 18:27:09 server id 1 end_log_pos 6994 Delete_rows: table id 59
#101018 18:27:09 server id 1 end_log_pos 7083 Delete_rows: table id 115
#101018 18:27:09 server id 1 end_log_pos 7201 Delete_rows: table id 109
#101018 18:27:09 server id 1 end_log_pos 7405 Delete_rows: table id 59
#101018 18:27:09 server id 1 end_log_pos 7490 Delete_rows: table id 115
#101018 18:27:09 server id 1 end_log_pos 7608 Delete_rows: table id 109
#101018 18:27:09 server id 1 end_log_pos 7812 Delete_rows: table id 59
#101018 18:27:09 server id 1 end_log_pos 7897 Delete_rows: table id 115
#101018 18:27:09 server id 1 end_log_pos 8015 Delete_rows: table id 109
#101018 18:27:09 server id 1 end_log_pos 8110 Delete_rows: table id 59
#101018 18:27:09 server id 1 end_log_pos 8193 Delete_rows: table id 115
#101018 18:27:09 server id 1 end_log_pos 8311 Delete_rows: table id 109
#101018 18:27:09 server id 1 end_log_pos 8481 Delete_rows: table id 59
#101018 18:27:09 server id 1 end_log_pos 8566 Delete_rows: table id 115
#101018 18:27:09 server id 1 end_log_pos 8684 Delete_rows: table id 109
#101018 18:27:09 server id 1 end_log_pos 8854 Delete_rows: table id 59
#101018 18:27:09 server id 1 end_log_pos 8939 Delete_rows: table id 115
#101018 18:27:09 server id 1 end_log_pos 9057 Delete_rows: table id 109
#101018 18:27:09 server id 1 end_log_pos 9261 Delete_rows: table id 59
#101018 18:27:09 server id 1 end_log_pos 9346 Delete_rows: table id 115
#101018 18:27:09 server id 1 end_log_pos 9464 Delete_rows: table id 109
#101018 18:27:09 server id 1 end_log_pos 9634 Delete_rows: table id 59
#101018 18:27:09 server id 1 end_log_pos 9719 Delete_rows: table id 115
#101018 18:27:09 server id 1 end_log_pos 9837 Delete_rows: table id 109 flags: STMT_END_F
一次删除了5,6条A表的记录,日志里面是这样的
select version();贴出来看看。 或者描述一如何再现你的故障。
mysqlbinlog -vv mysql-bin.0...
WINDOWS平台,MYSQL5.1.42这并不是个错误。
再现的步骤
1:create table a(a int);
2: create table b(a int);
3: CREATE TRIGGER testref after INSERT ON a
FOR EACH ROW BEGIN
INSERT INTO b values(new.a);
END;
4: 打开2进制日志
5:INSERT INTO A VALUES(1);
6: MYSQLBINLOG C:\BINLOG\BIN.000001 -r c:\log.sql
7: 打开LOG.SQL,里面并没有INSERT INTO A VALUES(1);这个命令。但是可以确定的是肯定写日志了。至于写成啥就看出来。如果没有触发器,一切正常,日志里面有INSERT语句。
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.00 sec)mysql>是MIXED的
-v, --verbose Reconstruct SQL statements out of row events. -v -v adds
comments on column data types
感谢楼上。解出来了,能够看到具体的命令咯
### INSERT INTO cpc.terminal
### SET
### @1=4211071
### @2=4198400
### @3=NULL
### @4=NULL
### @5=7200
### @6=NULL
### @7=NULL
### @8=60
### @9=NULL
### @10=NULL
### @11='106552151'
### @12='11631040'
### @13=2009-08-19 09:22:39
### @14=NULL
### @15=1
### @16='0'
### @17='0'
### @18=NULL
### @19=NULL
### @20=NULL
### @21='wapAPn'
### @22='wapuser'
### @23='wap_pw'
### @24=NULL