MySQL Bugs: #33969: Updating a text field via a left join

Bug #33969	Updating a text field via a left join
Submitted:	21 Jan 2008 23:28	Modified:	6 Mar 2010 20:05
Reporter:	Ryan Brothers	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.1.22-rc-community	OS:	Other (UBUNTU (64Bit), FreeBSD (64bit), RedHat)
Assigned to:	Alexey Kopytov	CPU Architecture:	Any

Description:
If I run a query to update a text field in a left join to more than 65,535 characters, MySQL does not throw an error or warning.

How to repeat:
SET @@session.sql_mode = 'TRADITIONAL';

DROP TABLE IF EXISTS test1;
DROP TABLE IF EXISTS test2;

CREATE TABLE test1 (
 id int not null
);

CREATE TABLE test2 (
 id int not null,
 description text not null
);

INSERT INTO test1 VALUES (1);
INSERT INTO test2 VALUES (1, '');

UPDATE test2 SET description = REPEAT('1', 70000) WHERE id = 1;
-- Error: ERROR 1406 (22001): Data too long for column 'description' at row 1

UPDATE test1
  LEFT JOIN test2 USING (id)
  SET test2.description = REPEAT('1', 70000)
  WHERE test1.id = 1;
-- No errors or warnings logged

Thank you for a problem report. For some reason I can not repeat the behaviour described:

C:\Program Files\MySQL\MySQL Server 5.0>bin\mysql -uroot -proot test -P3310
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.22-rc-community-debug MySQL Community Server - Debug (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SET @@session.sql_mode = 'TRADITIONAL';
Query OK, 0 rows affected (0.05 sec)

mysql> DROP TABLE IF EXISTS test1;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> DROP TABLE IF EXISTS test2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE test1 (
    ->  id int not null
    -> );
Query OK, 0 rows affected (0.19 sec)

mysql> CREATE TABLE test2 (
    ->  id int not null,
    ->  description text not null
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO test1 VALUES (1);
Query OK, 1 row affected (0.09 sec)

mysql> INSERT INTO test2 VALUES (1, '');
Query OK, 1 row affected (0.03 sec)

mysql> UPDATE test2 SET description = REPEAT('1', 70000) WHERE id = 1;
ERROR 1406 (22001): Data too long for column 'description' at row 1
mysql> UPDATE test1
    ->   LEFT JOIN test2 USING (id)
    ->   SET test2.description = REPEAT('1', 70000)
    ->   WHERE test1.id = 1;
ERROR 1406 (22001): Data too long for column 'description' at row 1
mysql> select version();
+---------------------------+
| version()                 |
+---------------------------+
| 5.1.22-rc-community-debug |
+---------------------------+
1 row in set (0.00 sec)

mysql> select @@sql_mode\G
*************************** 1. row ***************************
@@sql_mode: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,E
RROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER
1 row in set (0.00 sec)

Can you identify what is different in your case?

Thanks for your help.  My @@sql_mode matches yours.  Could the character set be the reason for the difference in behavior?  My table was using latin1 since I didn't specify a character set in the create table statement.

I am running on CentOS 5 though I was also able to replicate this on a machine running CentOS 3 with MySQL 5.0.45.

Also, I noticed that if I select the length of the description field after my last UPDATE I only get 4464:

mysql> SELECT LENGTH(description) FROM test2;
+---------------------+
| LENGTH(description) |
+---------------------+
|                4464 |
+---------------------+
1 row in set (0.00 sec)

It's not the character set.
I'll get the same result as Valeriy before, by using Latin1.

How did you install MySQL.

Did you use the packages/source from our download pages?

Thanks for your help.  Yes, I am running the RPM's available for Red Hat 5 from http://dev.mysql.com/downloads/mysql/5.1.html:

MySQL-client-community-5.1.22-0.rhel5
MySQL-devel-community-5.1.22-0.rhel5
MySQL-server-community-5.1.22-0.rhel5
MySQL-shared-compat-5.1.22-0.rhel5

As a test, I just tried installing MySQL 5.1 for Windows and the 2nd UPDATE statement does cause an error as expected, so it seems that this problem is only occurring on Linux.  Were you running on Linux in your test?

I can repeat this on FreeBSD with:

mysql> select version()\G
*************************** 1. row ***************************
version(): 5.1.22-rc-debug

mysql> CREATE TABLE test1 (id int not null);
mysql> CREATE TABLE test2 (id int not null, description text not null);
mysql> INSERT INTO test1 VALUES (1);
mysql> iNSERT INTO test2 VALUES (1, '');

mysql> UPDATE test2 SET description = REPEAT('1', 70000) WHERE id = 1;
ERROR 1406 (22001): Data too long for column 'description' at row 1

mysql> UPDATE test1 LEFT JOIN test2 USING (id) SET test2.description = REPEAT('1', 70000) WHERE test1.id = 1;
Query OK, 1 row affected (0.41 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select length(description) from test2\G
*************************** 1. row ***************************
length(description): 4464

Also repeatable on FreeBSD 64bit with:

mysql> select version()\G
*************************** 1. row ***************************
version(): 5.1.24-rc-debug

I'll try to get a deeper look.

I also can repeat this on Ubuntu amd64.

I forgot, of course test mode was TRADITIONAL during all my tests:

mysql> show variables like 'sql_mode'\G
*************************** 1. row ***************************
Variable_name: sql_mode
        Value: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER

Maybe this is 64bit only.

Thanks - I am running on all 32-bit machines, so it appears to affect both 32-bit and 64-bit.

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/45862

ChangeSet@1.2562, 2008-04-23 09:52:53+04:00, kaa@kaamos.(none) +4 -0
  Fix for bug #33969: Updating a text field via a left join
  
  When creating a temporary TEXT/BLOB field from an Item in
  Item::make_string_field(), the field's type was unconditionally set
  to the one corresponding to the maximum length (i.e. LONGTEXT/
  LONGBLOB). This resulted in problems when exactly the same TEXT/BLOB
  is type required in cases like CREATE ... SELECT or creating internal
  temporary tables for joins.
  
  Fixed by calling a different constructor for Field_blob so that an
  appropriate type is used depending on the Item's max_length value.

Pushed into 6.0.6-alpha

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/91222

2898 Alexey Kopytov	2009-11-21
      Backport of the fix for bug #33969: Updating a text field via a
      left join 
      
      When creating a temporary TEXT/BLOB field from an Item in
      Item::make_string_field(), the field's type was unconditionally
      set to the one corresponding to the maximum length (i.e.
      LONGTEXT/ LONGBLOB). This resulted in problems when exactly the
      same TEXT/BLOB is type required in cases like CREATE ... SELECT
      or creating internal temporary tables for joins. 
      
      Fixed by calling a different constructor for Field_blob so that
      an appropriate type is used depending on the Item's max_length
      value.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091211055901-yp18b3c7xuhl87rf) (version source revid:alik@sun.com-20091211055401-43rjwq7gjed6ds83) (merge vers: 6.0.14-alpha) (pib:13)

Pushed into 5.6.0-beta (revid:alik@sun.com-20091211055628-ltr7fero363uev7r) (version source revid:alik@sun.com-20091211055453-717czhtezc74u8db) (merge vers: 5.6.0-beta) (pib:13)

Noted in 5.6.0, 6.0.6 changelogs.

Creation of a temporary BLOB or TEXT column could create a column 
with the wrong maximum length.

Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20091211201717-03qf8ckwiw0np80p) (merge vers: 5.6.0-beta) (pib:16)

Moved 5.6.0 changelog entry to 5.5.3.