MySQL Bugs: #33670: binlog_stm_ctype_cp932 test fails under Falcon

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

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.

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?

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.

Setting to verified since this bug is repeatable with a resonably simple test case. Can you please do a triage?

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

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?

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.

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."

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.

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.

Vlad, Can you determine why the correct error is not reported?

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?

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

Svoj, please have a look if this is Falcon or Replication.

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.

This bug is a duplicate of BUG#33376 as far as I can tell.