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