Bug #25513 Federated: transaction failures
Submitted: 10 Jan 2007 0:10 Modified: 17 Jul 2007 19:26
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Federated storage engine Severity:S3 (Non-critical)
Version:5.1 OS:Linux (SUSE 10.0 / 64-bit)
Assigned to: Antony Curtis CPU Architecture:Any

[10 Jan 2007 0:10] Peter Gulutzan
Description:
I use a transactional storage engine.
On a remote computer, I create a table.
On a local computer, I create a matching federated table.
On the remote computer, I enter a failing INSERT statement.
On the local computer, I enter a failing INSERT statement.
I see that the statement has not failed completely.

I know, the single statement has become three INSERTs.
But that's not what I asked for, and not documented.

How to repeat:
On a remote computer, say:
        USE test
        SET @@autocommit=0;
        CREATE TABLE t17 (s1 int, UNIQUE (s1)) engine=innodb;
        INSERT INTO t17 VALUES (1),(2),(1);  /* Causes error, OK */
        SELECT * FROM t17;                   /* Returns 0 rows */
        ROLLBACK;

On a local computer, say:
        SET @@autocommit=0;
        USE test
        CREATE TABLE t17 (s1 INT, UNIQUE (s1))
        ENGINE=FEDERATED
        CONNECTION='mysql://Remote@192.168.1.106/test/t17';
        /* Change User and Host to what's appropriate for you. */
        INSERT INTO t17 VALUES (1),(2),(1);
        SELECT * FROM t17;

The final SELECT will return 2 rows.
[10 Jan 2007 2:45] MySQL Verification Team
Thank you for the bug report.

local>        SELECT * FROM t17;
+------+
| s1   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)
[25 Jan 2007 18:07] Brian Aker
The problem is the behavior in the extension to INSERT by MySQL.

Federated treats an extended INSERT as three insert statements, not as a single statement (the same goes for MyISAM, Archive... etc). I suspect we would get a similar behavior difference in NDB.
[27 Apr 2007 22:36] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/25636

ChangeSet@1.2489, 2007-04-27 15:35:51-07:00, acurtis@xiphis.org +4 -0
  Bug#25513
    "Federated: transaction failures"
    Fix case where using bulk inserts, autocommit=1 and the remote storage engine has
    single statement rollback, make the user-perceived behaviour the same where possible.
    (limitation is server memory and max packet size between server and federated server)
[28 Apr 2007 0:15] Brian Aker
Please do not make this non-pluggable :)
THD *thd= current_thd;

+  /* this is just a sanity-cap */
+  initial= (rows > 32767) ? 32767 : (uint) rows;

Why these numbers? Use defines (better if per table configure)
Please document.

Comment:
old_length= insert_string.length() - 2;

An insert with no fields... should take local default values and insert them remotely.

THD memroot will get too big... use a local memroot for handler, or INSERT specific. (better valgrind)

Almost no memory bounds checking... you need to determine how to return errors if memory allocation fails.

(Why not wrap into transactions remotely, if you have to split?)

Please place emit_bulk_insert into private parts of class... aka this does not need to be public!

Add tests for bulk insert. Not enough testing :)
[28 Apr 2007 2:23] Antony Curtis
The handler::ha_thd() method is not yet in the tree instead of current_thd;

As for the magic numbers - no good reason but perhaps I should concider turning off the bulk-update mode if the estimated number of rows was very large.
Again, once WL#2936 hits the tree, this can be easily a tunable variable.

An insert with no fields... That would alter the current behaviour to set locally default values. Perhaps this should be a tunable - that for all/any column for which a value is not specified should take the local default value?

Yes, I can create a MEM_ROOT for the bulk update.

...

If there is a need to split, it would be a solution to set AUTOCOMMIT=0 on remote.

and emit_bulk_insert() is already declared in a private part of the class.

I'll create more tests.
[3 May 2007 18:23] Brian Aker
I would wait for wl 2936 before committing this, there is no reason to do this now, and then hope to remember to do it again correctly afterward (and you are pushing into the engine tree right? So its already there). 

Tunable would be good for the behavior, but I believe the behavior should be local vs remote... I'd call this a current bug.
[29 May 2007 20:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/27620

ChangeSet@1.2516, 2007-05-29 13:27:22-07:00, antony@ppcg5.local +4 -0
  Bug#25513
    "Federated transaction failure"
    When the user performs a bulk insert, perform a bulk insert at the
    storage engine to the remote server so that when operating on a
    table which us transactional, we do not get partially complete inserts.
    However, if the size of the query exceeds the maximum packet size
    which the remote server would accept, attempt to mark a savepoint and
    use that to make the bulk-insert statement 'atomic'.
    New system variables:
      federated-min-bulk-insert
        The minimum number of rows which is required to trigger bulk inserts.
        (When mysqld does not know how many rows will be inserted, this is
        ignored, except for when it is zero, then bulk-insert is disabled.
      federated-max-bulk-insert
        The maximum number of rows which will bulk-insert will be used.
        (When zero, it is unlimited)
    Note - Bulk-inserts will consume memory as it stores in memory
           all rows which will be inserted and would only act upon them 
           when complete.
  TODO:
    make Federated properly transaction aware.
[30 May 2007 20:14] Brian Aker
Put a comment on for the -2:
old_length= insert_string.length() - 2;

You need a test for both a transactional engine, and a non-transactional engine. 

You need to document the behavior of the code as it now works.
[30 May 2007 23:43] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/27744

ChangeSet@1.2516, 2007-05-30 16:43:01-07:00, antony@ppcg5.local +4 -0
  Bug#25513
    "Federated transaction failure"
    When the user performs a bulk insert, perform a bulk insert at the
    storage engine to the remote server so that when operating on a
    table which us transactional, we do not get partially complete inserts.
    However, if the size of the query exceeds the maximum packet size
    which the remote server would accept, attempt to mark a savepoint and
    use that to make the bulk-insert statement 'atomic'.
    New system variables:
      federated-min-bulk-insert
        The minimum number of rows which is required to trigger bulk inserts.
        (When mysqld does not know how many rows will be inserted, this is
        ignored, except for when it is zero, then bulk-insert is disabled.
      federated-max-bulk-insert
        The maximum number of rows which will bulk-insert will be used.
        (When zero, it is unlimited)
    Note - Bulk-inserts will consume memory as it stores in memory
           all rows which will be inserted and would only act upon them 
           when complete.
  TODO:
    make Federated properly transaction aware.
[4 Jun 2007 9:50] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/28034

ChangeSet@1.2520, 2007-06-04 02:49:38-07:00, acurtis@xiphis.org +7 -0
  Bug#25513
    "Federated transaction failure"
  
  Experimental and fully functional patch for review/comments.
  
  What this patch provides:
    * Uses only 1 federated connection per server per concurrent transaction.
      As a result, will require significantly fewer network connections.
      Network connections are bound to a transaction until commit/rollback
    * Implements much better/more complete transactions support.
    * Implements primitive per-server connecton pool.
      connection reuse is strictly LIFO so old idle connections are permitted
      to time out and reconnect later when required.
    * Supports single-statement rollback.
    * Support for savepoints.
  
  Notes:
  1) transaction support still requires that the remote server's storage engines have
  a working transaction implementation complete with savepoint support.
  2) XA 2-phase commit is not yet implemented but will be trivial to add.
  3) Trivial to extend in order to support other transports than mysql client protocol.
  4) bulk-inserts can be implemented later as a performance improvement.
  5) no limits on connection pool size - a tunable server variable may be added.
  6) at shutdown, not all memory is freed. This is trivial to resolve.
  7) Some indents have tab characters.
  
  Things to address later:
  1) We need to use the connection pool when CREATE TABLE is performed for instances
  where the new federated table is using a server for which a connection already exists.
  2) We need to resolve situation where CREATE TABLE requires a new network connection...
  Do we stall the entire server when this occurs, pick a small timeout or change LOCK_open
  3) Old federated code builds SQL inconsistantly - inconsistant quoting of identifiers 
  may open possibilities for SQL injection attack. This should be investigated.
[8 Jun 2007 4:08] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/28357

ChangeSet@1.2548, 2007-06-07 19:21:23-07:00, acurtis@xiphis.org +4 -0
  Bug#25513
    "Federated Transactions Failure"
    Implement bulk-insert to relieve common situation where a multi-row insert
    into a transactional table would result in an inconsistant commit.
    By performing a multi-row insert into the remote server preserves the user's
    intent and offers a performance boost when inserting a large number of small rows.
[8 Jun 2007 22:19] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/28445

ChangeSet@1.2548, 2007-06-08 15:19:28-07:00, acurtis@xiphis.org +6 -0
  Bug#25513
    "Federated Transactions Failure"
    Bug occurs when AUTOCOMMIT=TRUE and where the user performs an operation
    which inserts more than one row into the federated table and the federated
    table references a remote table stored within a transactional storage
    engine. When the insert operation for any one row in the statement fails
    due to constraint violation, the federated engine is unable to perform
    statement rollback and so the remote table contains a partial commit.
    This bug was fixed by implementing  bulk-insert handling into the
    federated storage engine. This will relieve the bug for most common
    situations by enabling the generation of a multi-row insert into the
    remote table and thus permitting the remote table to perform statement
    rollback when neccessary.
    The multi-row insert is limited to the maximum packet size between servers
    and should the size overflow, more than one insert statement will be sent
    and this bug will reappear.
    The bulk-insert handling will offer a significant performance boost when
    inserting a large number of small rows.
    A new session variable 'federated_bulk_insert' permits disabling the new feature.
  
  Implemented support for: INSERT IGNORE/REPLACE/UPDATE IGNORE
  New comments in Doxygen format.
[11 Jun 2007 15:30] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/28505

ChangeSet@1.2548, 2007-06-11 08:29:42-07:00, acurtis@xiphis.org +6 -0
  Bug#25513
    "Federated Transactions Failure"
    Bug occurs when AUTOCOMMIT=TRUE and where the user performs an operation
    which inserts more than one row into the federated table and the federated
    table references a remote table stored within a transactional storage
    engine. When the insert operation for any one row in the statement fails
    due to constraint violation, the federated engine is unable to perform
    statement rollback and so the remote table contains a partial commit.
    This bug was fixed by implementing  bulk-insert handling into the
    federated storage engine. This will relieve the bug for most common
    situations by enabling the generation of a multi-row insert into the
    remote table and thus permitting the remote table to perform statement
    rollback when neccessary.
    The multi-row insert is limited to the maximum packet size between servers
    and should the size overflow, more than one insert statement will be sent
    and this bug will reappear.
    The bulk-insert handling will offer a significant performance boost when
    inserting a large number of small rows.
    A new session variable 'federated_bulk_insert' permits disabling the new feature.
[11 Jun 2007 15:52] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/28509

ChangeSet@1.2548, 2007-06-11 08:52:10-07:00, acurtis@xiphis.org +6 -0
  Bug#25513
    "Federated Transactions Failure"
    Bug occurs when AUTOCOMMIT=TRUE and where the user performs an operation
    which inserts more than one row into the federated table and the federated
    table references a remote table stored within a transactional storage
    engine. When the insert operation for any one row in the statement fails
    due to constraint violation, the federated engine is unable to perform
    statement rollback and so the remote table contains a partial commit.
    This bug was fixed by implementing  bulk-insert handling into the
    federated storage engine. This will relieve the bug for most common
    situations by enabling the generation of a multi-row insert into the
    remote table and thus permitting the remote table to perform statement
    rollback when neccessary.
    The multi-row insert is limited to the maximum packet size between servers
    and should the size overflow, more than one insert statement will be sent
    and this bug will reappear.
    The bulk-insert handling will offer a significant performance boost when
    inserting a large number of small rows.
    A new session variable 'federated_bulk_insert' permits disabling the new feature.
[20 Jun 2007 6:50] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/29151

ChangeSet@1.2494, 2007-06-19 23:50:17-07:00, antony@ppcg5.local +7 -0
  Bug#25513
    "Federared Transactions Failure"
    Bug occurs when the user performs an operation which inserts more than 
    one row into the federated table and the federated table references a 
    remote table stored within a transactional storage engine. When the insert 
    operation for any one row in the statement fails due to constraint 
    violation, the federated engine is unable to perform statement rollback 
    and so the remote table contains a partial commit. The user would expect
    a statement to perform the same so a statement rollback is expected.
    This bug was fixed by implementing  bulk-insert handling into the
    federated storage engine. This will relieve the bug for most common
    situations by enabling the generation of a multi-row insert into the
    remote table and thus permitting the remote table to perform statement
    rollback when neccessary.
    The multi-row insert is limited to the maximum packet size between servers
    and should the size overflow, more than one insert statement will be sent
    and this bug will reappear.
    The bulk-insert handling will offer a significant performance boost when
    inserting a large number of small rows.
  This patch builds on Bug29019 and Bug25511
[20 Jun 2007 23:54] Brian Aker
Please fix the naming of have_auto_increment and make it self explanatory.
[21 Jun 2007 21:05] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/29344

ChangeSet@1.2494, 2007-06-21 14:04:35-07:00, antony@ppcg5.local +5 -0
  Bug#25513
    "Federared Transactions Failure"
    Bug occurs when the user performs an operation which inserts more than 
    one row into the federated table and the federated table references a 
    remote table stored within a transactional storage engine. When the
    insert operation for any one row in the statement fails due to 
    constraint violation, the federated engine is unable to perform 
    statement rollback and so the remote table contains a partial commit. 
    The user would expect a statement to perform the same so a statement 
    rollback is expected.
    This bug was fixed by implementing  bulk-insert handling into the
    federated storage engine. This will relieve the bug for most common
    situations by enabling the generation of a multi-row insert into the
    remote table and thus permitting the remote table to perform 
    statement rollback when neccessary.
    The multi-row insert is limited to the maximum packet size between 
    servers and should the size overflow, more than one insert statement 
    will be sent and this bug will reappear.
    The bulk-insert handling will offer a significant performance boost 
    when inserting a large number of small rows.
  This patch builds on Bug29019 and Bug25511
[22 Jun 2007 22:45] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/29439

ChangeSet@1.2494, 2007-06-22 15:44:18-07:00, antony@ppcg5.local +5 -0
  Bug#25513
    "Federared Transactions Failure"
    Bug occurs when the user performs an operation which inserts more than 
    one row into the federated table and the federated table references a 
    remote table stored within a transactional storage engine. When the
    insert operation for any one row in the statement fails due to 
    constraint violation, the federated engine is unable to perform 
    statement rollback and so the remote table contains a partial commit. 
    The user would expect a statement to perform the same so a statement 
    rollback is expected.
    This bug was fixed by implementing  bulk-insert handling into the
    federated storage engine. This will relieve the bug for most common
    situations by enabling the generation of a multi-row insert into the
    remote table and thus permitting the remote table to perform 
    statement rollback when neccessary.
    The multi-row insert is limited to the maximum packet size between 
    servers and should the size overflow, more than one insert statement 
    will be sent and this bug will reappear. Multi-row insert is disabled
    when an "INSERT...ON DUPLICATE KEY UPDATE" is being performed.
    The bulk-insert handling will offer a significant performance boost 
    when inserting a large number of small rows.
  This patch builds on Bug29019 and Bug25511
[28 Jun 2007 22:18] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/29911

ChangeSet@1.2506, 2007-06-28 15:17:35-07:00, antony@ppcg5.local +5 -0
  Bug#25513
    "Federared Transactions Failure"
    Bug occurs when the user performs an operation which inserts more than 
    one row into the federated table and the federated table references a 
    remote table stored within a transactional storage engine. When the
    insert operation for any one row in the statement fails due to 
    constraint violation, the federated engine is unable to perform 
    statement rollback and so the remote table contains a partial commit. 
    The user would expect a statement to perform the same so a statement 
    rollback is expected.
    This bug was fixed by implementing  bulk-insert handling into the
    federated storage engine. This will relieve the bug for most common
    situations by enabling the generation of a multi-row insert into the
    remote table and thus permitting the remote table to perform 
    statement rollback when neccessary.
    The multi-row insert is limited to the maximum packet size between 
    servers and should the size overflow, more than one insert statement 
    will be sent and this bug will reappear. Multi-row insert is disabled
    when an "INSERT...ON DUPLICATE KEY UPDATE" is being performed.
    The bulk-insert handling will offer a significant performance boost 
    when inserting a large number of small rows.
  This patch builds on Bug29019 and Bug25511
[28 Jun 2007 23:03] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/29917

ChangeSet@1.2506, 2007-06-28 16:03:01-07:00, antony@ppcg5.local +5 -0
  Bug#25513
    "Federared Transactions Failure"
    Bug occurs when the user performs an operation which inserts more than 
    one row into the federated table and the federated table references a 
    remote table stored within a transactional storage engine. When the
    insert operation for any one row in the statement fails due to 
    constraint violation, the federated engine is unable to perform 
    statement rollback and so the remote table contains a partial commit. 
    The user would expect a statement to perform the same so a statement 
    rollback is expected.
    This bug was fixed by implementing  bulk-insert handling into the
    federated storage engine. This will relieve the bug for most common
    situations by enabling the generation of a multi-row insert into the
    remote table and thus permitting the remote table to perform 
    statement rollback when neccessary.
    The multi-row insert is limited to the maximum packet size between 
    servers and should the size overflow, more than one insert statement 
    will be sent and this bug will reappear. Multi-row insert is disabled
    when an "INSERT...ON DUPLICATE KEY UPDATE" is being performed.
    The bulk-insert handling will offer a significant performance boost 
    when inserting a large number of small rows.
  This patch builds on Bug29019 and Bug25511
[2 Jul 2007 16:13] Antony Curtis
queued to engines tree
[7 Jul 2007 16:33] Bugs System
Pushed into 5.1.21-beta
[7 Jul 2007 16:35] Bugs System
Pushed into 5.0.46
[17 Jul 2007 19:26] Paul DuBois
Noted in 5.0.46, 5.1.21 changelogs.

With autocommit disabled, for a multiple-row insert into a
FEDERATED table that refers to a remote transactional table,
if the insert failed for a row due to constraint failure,
the remote table would contain a partial commit (the rows
preceding the failed one) instead of rolling back the statement
completely.  This occurred because the rows were treated as
individual inserts.

Now FEDERATED performs bulk-insert handling such that multiple
rows are sent to the remote table in a batch. This enables the
remote table to perform statement rollback properly should an
error occur. This capability has the following limitations:

* The size of the insert cannot exceed the maximum packet size
  between servers.  If the insert exceeds this size, it is broken
  into multiple packets and the rollback problem can occur.

* Bulk-insert handling does not occur for INSERT ... ON DUPLICATE
  KEY UPDATE.

Also updated the FEDERATED storage engine section.