Bug #63446 | Update queries on Federated tables do not update text columns | ||
---|---|---|---|
Submitted: | 27 Nov 2011 17:00 | Modified: | 12 Dec 2011 19:57 |
Reporter: | Eric Ouellette | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Federated storage engine | Severity: | S1 (Critical) |
Version: | 5.5.18, 5.6.5-m8 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | federated, text, UPDATE |
[27 Nov 2011 17:00]
Eric Ouellette
[12 Dec 2011 19:57]
Sveta Smirnova
Thank you for the report. Verified as described.
[7 Dec 2018 0:00]
helmut Kleinhans
Happens in version 8 as well.
[10 Dec 2018 14:47]
helmut Kleinhans
The problem only arises if the updated text is not null and shorter than the text in the column. EXAMPLE ====== CREATE DATABASE ft; USE ft; CREATE TABLE ft ( id int(10) unsigned NOT NULL AUTO_INCREMENT, name varchar(64) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '', description text COLLATE utf8mb4_general_ci, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; CREATE TABLE fed_ft ( id int(10) unsigned NOT NULL AUTO_INCREMENT, name varchar(64) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '', description text COLLATE utf8mb4_general_ci, PRIMARY KEY (id) ) ENGINE=FEDERATED DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci CONNECTION='mysql://xxx:xxx@127.0.0.1/ft/ft'; INSERT INTO fed_ft SET id=1, name='bla', description='foo bar baz'; SELECT * FROM fed_ft; +----+------+-------------+ | id | name | description | +----+------+-------------+ | 1 | bla | foo bar baz | +----+------+-------------+ 1 row in set (0.01 sec) SELECT * FROM ft; +----+------+-------------+ | id | name | description | +----+------+-------------+ | 1 | bla | foo bar baz | +----+------+-------------+ 1 row in set (0.00 sec) UPDATE fed_ft SET description="BAZ" WHERE id=1; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 SELECT * FROM fed_ft; +----+------+-------------+ | id | name | description | +----+------+-------------+ | 1 | bla | foo bar baz | +----+------+-------------+ 1 row in set (0.02 sec) UPDATE fed_ft SET description="baz baz baz baz" WHERE id=1; Query OK, 1 row affected (0.11 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM fed_ft; +----+------+-----------------+ | id | name | description | +----+------+-----------------+ | 1 | bla | baz baz baz baz | +----+------+-----------------+ 1 row in set (0.03 sec) END EXAMPLE =========== The federated engine rebuilds the UPDATE statement to the following: FAILING CASE: UPDATE fed_ft SET description="BAZ" WHERE id=1 is rewritten to: UPDATE ft SET id = 1, name = 'bla', description = 'BAZ' WHERE id = 1 AND name = 'bla' AND description = 'BAZ bar baz' The new value fits within the current value and is overwritten. I.e. "description = 'BAZ bar baz'" instead of the correct "description = 'foo bar baz'" Since the clobbert text is used in the where clause the statement has no effect. SUCCESS CASE UPDATE fed_ft SET description="baz baz baz baz" WHERE id=1; is rewritten to: UPDATE ft SET id = 1, name = 'bla', description = 'baz baz baz baz' WHERE id = 1 AND name = 'bla' AND description = 'foo bar baz' Since the new value for description is longer than the current description a new value is allocated and the old value remains correct. NOTE: The data is clobbert sometime before the statement is built. 'old_data' is already wrong when 'int ha_federated::update_row(const uchar *old_data, uchar *)' is called (mysql-8.0.13/storage/federated/ha_federated.cc:1953). NOTE2: If binlog-format is 'MIXED' or 'STATEMENT' then the statement is not rebuilt to include all columns in the where clause. However, it still failes if the text column is mentioned in the original UPDATE statement: SELECT * FROM fed_ft; +----+------+-------------+ | id | name | description | +----+------+-------------+ | 1 | bla | foo bar baz | +----+------+-------------+ 1 row in set (0.03 sec) UPDATE fed_ft SET description="BAZ" WHERE description="foo bar baz"; Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0 The UPDATE statement is rebuilt to: UPDATE ft SET description = 'BAZ' WHERE id = 1 AND description = 'BAZ bar baz'