| Bug #36661 | binlog problem in mixing ndb tables and innodb tables | ||
|---|---|---|---|
| Submitted: | 12 May 2008 5:48 | Modified: | 17 May 2008 13:23 |
| Reporter: | ws lee | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Row Based Replication ( RBR ) | Severity: | S4 (Feature request) |
| Version: | 5.1.23 rc | OS: | Solaris (5.10) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | mysql clu | ||
[12 May 2008 18:43]
Sveta Smirnova
Thank you for the report. According to http://dev.mysql.com/doc/refman/5.1/en/news-5-1-21.html this is actually not a bug: # MySQL Cluster: If a storage engine has its own logging capability, then any statement using both this engine and some other engine not having its own logging could not be correctly logged, due to the fact that entries from one engine could be logged before entries from the other engine were. This did not generate any error messages when it occurred. Now, if multiple storage engines are used in a statement and at least one of them has its own logging capability, then an error message is generated and the statement is not executed. Note Currently, the only storage engine to have its own logging capability is NDBCLUSTER. (Bug#28722) See also Bug#28722 and http://forge.mysql.com/worklog/task.php?id=3931 But as there is no easy workaround for this problem I reclassify this report to feature request: please implement a way to use replication for multi-engine statements.
[16 May 2008 3:59]
ws lee
Thanks Sveta Smirnova your reply. of coures, i have read already, http://dev.mysql.com/doc/refman/5.1/en/news-5-1-21.html and http://forge.mysql.com/worklog/task.php?id=3931. i have setup binlogging about only db_audit db in my.cnf and db_audit db have all innodb tables with not ndb tables. therefore, this case, Only binlogging of db_audit db not concerned with ndbselflogging. In my case, I don't need below error messsage. 「Statement cannot be written atomically since more than one engine involved and at least one engine self-logging」 Q1. what do you think this? Do you ready to modify source about this part? Q2. I have complied temporarily excepte below part of sql/sql_base.cc /*-------------------------------- if (multi_engine && (flags_some_set & HA_HAS_OWN_BINLOGGING)) { error= ER_BINLOG_LOGGING_IMPOSSIBLE; my_error(error, MYF(0), "Statement cannot be written atomically since more" " than one engine involved and at least one engine" " is self-logging"); } DBUG_PRINT("info", ("error: %d", error)); if (error) { ha_rollback_stmt(thd); return -1; } ----------------------------------*/ this is no problem in mysql operation in my case.(only in db_audit with all innodb talbes binlogging)?
[18 Dec 2008 9:16]
Mats Kindahl
This is a deliberate design (WL#3931) to prevent a crash from causing an inconsistency between master and slave by logging only half a statement. Note that disabling the check run the risk of causing inconsistencies between the log table and the data table, and this is already the case on the master since both storage engines are not XA-aware. It is possible to add an option to relax the checking, which would then include the capabilities checking, and allow the statement to be committed anyway. It is also possible to extend the CREATE TRIGGER syntax to support deferred triggers, which would the allow the checking to be relaxed for writing to the log table.
Description: i have two database, db_main and db_audit db_main is main database. db_audit is log of db_main. tables of db_main is all ndb engine. but, tables of db_audit is all innodb. i have using master-master replication in order to keep same data of db_audit tables.(only binlog-do-db=db_audit) for using trigger in db_main tables, db_main update log is written in db_audit u but, below error. Statement cannot be written atomically since more than one engine involved and at least one engine self-logging but, this error is useless to me. i have changed source /sql/sql_base.cc the below part did commentin. /*-------------------------------- if (multi_engine && (flags_some_set & HA_HAS_OWN_BINLOGGING)) { error= ER_BINLOG_LOGGING_IMPOSSIBLE; my_error(error, MYF(0), "Statement cannot be written atomically since more" " than one engine involved and at least one engine" " is self-logging"); } DBUG_PRINT("info", ("error: %d", error)); if (error) { ha_rollback_stmt(thd); return -1; } ----------------------------------*/ thus, recompile. i have no problem in now. this chang is what problem? i want using binlog my case. P.S) ndb talbes use shockingly much memory. In this reason, log tables must created innodb. and, innodb table must replicated using binlog. How to repeat: ----- STEP1 ----- mysql> user db_main; db_main> create table t1(a int, b name, c date) engine=ndb; db_audit> delimiter // CREATE TRIGGER trg1 AFTER UPDATE ON t1 FOR EACH ROW BEGIN INSERT INTO db_audit.au_t1(inserted_by,inserted_t,a,b,c) VALUES(user(),old.a,old.b,old.c); END; // DELIMITER ; db_main> use db_audit; db_audit> create table au_t1(inserted_by varchar(256),inserted_t datetime, a int, b name, c date) enginer=innodb; ----- STEP2 ----- and, the other server is same command. ----- STEP3 ----- In db_audit, master-master replication setup. in my.cnf of both master and slave server ####################### ... binlog_format=row binlog-do-db=db_audit replicate-do-db=db_audit\ ... ######################## cf)db_main don't use binlog. only db_audit do binglog. ------ STEP4 ------ db_main> update t1 set date=now() where a=1; in case, below error is not showned in my recompile verion. Statement cannot be written atomically since more than one engine involved and at least one engine self-logging to this enable fix this? Suggested fix: in my.cnf binlog-do-db=db_audit <- db_audit have all innodb table. In case consisiting of innodb table is admit binlogging between ndb and innodb.