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:
None 
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
Description:
Updating Text columns on Federated tables doesn't update the data on the remote database. Updating the value from non null to null or from null to non null will work.

How to repeat:
-- Run on Remote server;
GRANT SELECT, INSERT, UPDATE on test.* TO 'testUser'@'%' IDENTIFIED BY 'testPassword';

CREATE TABLE test.tblFederated (
  txtColumn TEXT
) ENGINE=InnoDB;

-- Run on Federated Server;
CREATE TABLE test.tblFederated (
  txtColumn TEXT
) ENGINE=Federated DEFAULT CHARSET=latin1 CONNECTION='mysql://testUser:testPassword@192.168.1.100:3306/test/tblFederated';

INSERT INTO test.tblFederated (txtColumn) VALUES ('INSERT');

SELECT * FROM test.tblFederated;

-- Run on Remote server;
SELECT * FROM test.tblFederated;

-- Run on Federated Server;
UPDATE test.tblFederated SET txtColumn = "2";

SELECT * FROM test.tblFederated;

-- Run on Remote Server;
SELECT * FROM test.tblFederated;

-- Run on Federated Server;
UPDATE test.tblFederated SET txtColumn = NULL;

SELECT * FROM test.tblFederated;

-- Run on Remote Server;
SELECT * FROM test.tblFederated;
[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'