PBXT replication failure with simple transactions

[Philip Stoev]

The following test causes PBXT replication to fail:
--source include/master-slave.inc
--disable_abort_on_error
--disable_warnings
--disable_query_log
--disable_result_log

CREATE TABLE `table10_pbxt_int_autoinc` ( `col_int_key` int, pk integer auto_increment, `col_int` int, /*Indices*/ key (`col_int_key` ), primary key (pk)) ENGINE=pbxt;
INSERT /*! IGNORE */ INTO table10_pbxt_int_autoinc VALUES (100, NULL, 100) , (100, NULL, 100) , (100, NULL, 100) , (100, NULL, 100) , (100, NULL, 100) , (100, NULL, 100) , (100, NULL, 100) , (100, NULL, 100) , (100, NULL, 100) , (100, NULL, 100);
SET AUTOCOMMIT=OFF;
DELETE FROM `table10_pbxt_int_autoinc` WHERE 1 = 1 LIMIT 6;
UPDATE `table10_pbxt_int_autoinc` SET `pk` = 2 WHERE `col_int_key` > 3 LIMIT 6;
UPDATE `table10_pbxt_int_autoinc` SET `col_int_key` = 2 WHERE `pk` > 0 LIMIT 2;
set autocommit=1;

The error is

Last_SQL_Error Could not execute Update_rows event on table test.table10_pbxt_int_autoinc; Can't find record in 'table10_pbxt_int_autoinc', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log master-bin.000001, end_log_pos 1083

[Philip Stoev (pstoev-askmonty)]

Bug is also reproducible without LIMIT in the queries and with COMMIT instead of autocommit=1 . The table has a primary key.

[Kristian Nielsen (knielsen)]

The problem can be repeated with a simpler test case that does not involve replication:

--source include/have_pbxt.inc
CREATE TABLE t1 (a INT, b INTEGER AUTO_INCREMENT PRIMARY KEY, c INT) ENGINE=pbxt;
INSERT INTO t1 VALUES (100, NULL, 100), (100, NULL, 100);
BEGIN;
--error 1062
UPDATE t1 SET b = 20 WHERE a > 3 LIMIT 6;
SELECT * FROM t1 ORDER BY b;
COMMIT;
SELECT * FROM t1 ORDER BY b;
DROP TABLE t1;
DROP DATABASE pbxt;

The failing update is not rolled back as it should be; it leaves the primary key of the first row changed from 2 to 20.

[Kristian Nielsen (knielsen)]

This may be a limitation of PBXT with transactions where some statements fail.

From the PBXT documentation, http://www.primebase.org/documentation/#diffs :

8.2 No Statement Level Sub-transactions

PBXT does not support statement level sub-transactions. This means that if an error occurs, PBXT will rollback the entire transaction, not just the current statement.

The only exception to this is the duplicate key error. PBXT undoes the INSERT or UPDATE statement when an duplicate key error occurs. The user is then free to decide whether to continue, or rollback the transaction.

Note that this can cause problems for replication, and means that INSERT with multiple rows should not be used when replicating PBXT tables. For example:

use test;
drop table if exists t;
create table t ( i int not null, unique index i (i) ) engine = PBXT;
set autocommit = 0;
insert into t values (1),(2),(2);
# error 1062 (duplicate key) occurs
commit;

select * from t;
i
1
2

In the above example, the INSERT statement is not completely rolled back. MySQL replication, however, will not replicate any part of the statement because it assumes that a transactional storage engine supports statement level sub-transactions.

In the original test case, the first UPDATE statement fails, and is therefore not replicated, however it is not completely rolled back despite what the pbxt documentation says.

[Philip Stoev (pstoev-askmonty)]

Thanks for the pointer, will try to construct a test case that is error-free
and does not use multi-row insert.

Philip Stoev

[Kristian Nielsen (knielsen)]

Actually, thinking more, I do believe that this is a bug in PBXT.

I don't know if PBXT should be able to roll back only the statement that fails with duplicate key error, as the documentation states. But if not, it should do like NDB, which also is not able to roll back single statements that fail. In this case, the transaction should be marked as failed, and any further operations in the transaction should cause an error (including commit, but excluding rollback of course).

This will force the transaction to roll back in its entirety, and replication will not fail.

Philip, a possible work-around might be if you can in your tests detect failing statements, and in such cases roll back the entire transactions. This might be useful for any engine that lacks the ability to roll back single statements.

[Paul McCullagh (paul-mccullagh)]

Kristian, your insights into the problem are spot on.

The documentation is incorrect. The "statement undo", is only done correctly in the special case that the statement only affects one row. At least, this is all that is done in the current implementation.

As you say, PBXT should return an error in all other cases, and then continue to return an error until the end of the transaction.