MySQL Bugs: #63446: Update queries on Federated tables do not update text columns

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

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;

Thank you for the report.

Verified as described.

Happens in version 8 as well.

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'