Bug #33670 binlog_stm_ctype_cp932 test fails under Falcon
Submitted: 3 Jan 2008 18:54 Modified: 7 Sep 2009 12:09
Reporter: Philip Stoev Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S3 (Non-critical)
Version:6.0.4 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Triage: Triaged: D4 (Minor)

[3 Jan 2008 18:54] Philip Stoev
Description:
The binlog_stm_ctype_cp932 test fails under falcon with a weird message:

mysqltest: In included file "./include/ctype_common.inc": At line 59: query 'insert into t1 values (),()' failed: 1534: Writing one row to the row-based binary log failed

I take this to mean that this may be an issue with replication, hence I file this bug. cp932 is a Japanese code page.

How to repeat:
Please run:

perl mysql-test-run.pl --mysqld=--default-storage-engine=falcon \ binlog_stm_ctype_cp932

Please let me know if further narrowing of the test case is required, and I will do it.
[4 Jan 2008 15:25] Kevin Lewis
Philip, please let us know if you think this is a problem with Falcon or the binlog.  What exactly is different between InnoDB and Falcon with this test?
[4 Jan 2008 15:35] Philip Stoev
This test does not fail with innodb - the insert completes succesfully. I placed it in the Falcon category because bug #30282 shows that Falcon may have problems with non-ascii characters.

Please reassign as you see fit -- I was unable to find more information on this error.
[17 Jan 2008 11:43] Philip Stoev
Setting to verified since this bug is repeatable with a resonably simple test case. Can you please do a triage?
[22 Jan 2008 22:37] Kevin Lewis
After adding --disable_abort_on_error the test shows;

 DROP TABLE t1;
 create table t1 (a set('a') not null);
 insert into t1 values (),();
-Warnings:
-Warning        1364    Field 'a' doesn't have a default value
+ERROR HY000: Writing one row to the row-based binary log failed
[22 Jan 2008 22:38] Kevin Lewis
Vlad,  This is actually a good one for you to dig into. Whay does the write fail to the binlog for Falcon and not other storage engines?
[25 Jan 2008 11:35] Vladislav Vaintroub
Philip, this test is designed to work with engines that support statement based
binlogging. Falcon does not support it yet. I think it is not a bug or do you have any concerns.
[25 Jan 2008 19:42] Philip Stoev
If Falcon does not support statement-based logging, the correct error to be returned is ER_BINLOG_UNSAFE_STATEMENT, English text

"Statement is not safe to log in statement format."
[25 Jan 2008 20:51] Kevin Lewis
Here is an explanation from Ann Harrison that explains why Falcon currently does not support statement based replication.

*** Ann Harrison writes ***
The primary requirement of replication is that the replication process
keep the slave an exact copy of the master.  Row-based replication makes
that easy.  Data changes are replicated from the master to the slave in
the order they occur.

Statement based replication is trickier.   First, the replication
system has to provide a complete state of the master environment
to the slave.  But that's already handled.  The harder problem, and
the one that caused us to say that Falcon could not work with statement
based replication is imperfect isolation.  If transactions are
serializable, the replication process can serialize the transactions
in order by commit and run all all the change (insert/update/delete)
statements each transaction makes in order.  The result on the slave
will be the same as it is on the master.

Unfortunately, Falcon's repeatable read has some update anomalies.
They're interesting (to me) because the root cause is too much
transaction isolation - transactions that should see and avoid
each other don't.

Consider this case - a database for a cheap restaurant:

create table lunch (item varchar(10), price decimal (5,2));
create table dinner (entree varchar(10), price decimal (5,2));

insert into lunch ('hamburg', 3.00);
insert into lunch ('steak', 6.00);
insert into dinner ('hamburg', 5.00);
insert into dinner ('steak', 8.00);

OK, you've got a new manager who says that each dinner must cost
twice as much as the equivalent lunch.  And you've got two people
working on the database.  One reads the lunch menu and increases
the price of dinner as necessary.

update dinner set price =
     select 2 * l.price from lunch l where l.item = d.entree

The other query running at the same time reads the dinner menu
and changes the lunch menu as necessary.

update lunch set price =
     select .5 * d.price from dinner d where d.entree = l.item

In a serializable system, one of those queries would fail.  In Falcon
they both succeed, resulting in these values

    lunch                  dinner

hamburger $2.50        hamburg   $6.00
steak     $4.00        steak    $12.00

That's not a result that you could possibly get by running the
statements in serialized transactions.  Row based replication
would reproduce that result, but statement based replication
would not.

Currently, in repeatable read mode, InnoDB uses select for update
to get values used in insert, update, and (I think) delete statements.
That makes their results serializable enough for statement based
replication but introduces other anomalies, and cuts back on
concurrency.  It's not a solution I would recommend.
[25 Jan 2008 21:35] Philip Stoev
The behavoir of MySQL regarding the various binary log types is described at:

http://dev.mysql.com/doc/refman/6.0/en/binary-log-mixed.html

I think Falcon's behavoir should be completely defined in both code and documentation, that is, there should be a Falcon row in the first table on that page and the errors and warnings given out should be according to the second table.

If this is already the case, then something else is going on and I will continue to examine this bug.
[1 Feb 2008 23:05] Kevin Lewis
Vlad, Can you determine why the correct error is not reported?
[8 Feb 2008 1:18] Kevin Lewis
Lars, could you assign this to a server engineer to determine if Falcon is doing anything wrong here.  Vlad just debugged it and cannot find fault.  We rightfuly do not return HA_BINLOG_STMT_CAPABLE since Falcon does not do statement based logging. 

The error that occurs;
1534: Writing one row to the row-based binary log failed
seems to be the wrong error according to Philips interpretation of;
http://dev.mysql.com/doc/refman/6.0/en/binary-log-mixed.html

If this indeed is the correct error for an engine that is NOT statement-logging capable (SLC), then maybe only the documentation aught to change.  And if so, what should be done with this testcase if it can only run against SLC engines?
[19 Feb 2008 13:35] Philip Stoev
Here is another obscure error:

$ perl mysql-test-run.pl --mysqld=--default-storage-engine=falcon ctype_cp932_binlog_stm

mysqltest: At line 25: query 'SHOW BINLOG EVENTS FROM 369' failed: 1220: Error when executing command SHOW BINLOG EVENTS: Wrong offset or I/O error
[6 Aug 2008 9:30] Lars Thalmann
Svoj, please have a look if this is Falcon or Replication.
[25 Nov 2008 13:50] Sergey Vojtovich
The test case requires statement or mixed binlog format. As Falcon doesn't support statement based binlogging, it chooses to binlog the INSERT statement in row format in mixed mode. For other engines it will likely go using statement mode.

Testing this particular statement with other engines and binlog format row causes the very same failure:
-- source include/have_binlog_format_row.inc
create table t1(a int);
insert into t1 values(),();
ERROR HY000: Writing one row to the row-based binary log failed

Meaning the root issue affects all engines and is outside of Falcon. This bug looks like a duplicate of BUG#33376. Reassigning to Mats as discussed on IRC.
[7 Sep 2009 12:09] Mats Kindahl
This bug is a duplicate of BUG#33376 as far as I can tell.