MySQL Bugs: #75539: max_allowed_packet error destroys original data

Bug #75539	max_allowed_packet error destroys original data
Submitted:	17 Jan 2015 20:55	Modified:	12 May 2015 16:57
Reporter:	Oli Sennhauser	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.6.21	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	max_allowed_packet

Description:
Similar to bug #7745 (http://bugs.mysql.com/bug.php?id=7745) we found that updating a field which exceeds max_allowed_package size destroyed old data.

How to repeat:
CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `data` longtext,
  `ts` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB
;
insert into test values (NULL, 'Angang', NULL);

show global variables like 'max_all%';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+

3 times:
update test set data=concat(data, repeat('bla', 100000));

mysql> select id, left(data, 30), ts, length(data) from test;
+----+--------------------------------+------+--------------+
| id | left(data, 30)                 | ts   | length(data) |
+----+--------------------------------+------+--------------+
|  1 | Angangblablablablablablablabla | NULL |       900006 |
+----+--------------------------------+------+--------------+

mysql> update test set data=concat(data, repeat('bla', 100000));
Rows matched: 1  Changed: 1  Warnings: 1

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------+
| Level   | Code | Message                                                                     |
+---------+------+-----------------------------------------------------------------------------+
| Warning | 1301 | Result of concat() was larger than max_allowed_packet (1048576) - truncated |
+---------+------+-----------------------------------------------------------------------------+

mysql> select id, left(data, 30), ts, length(data) from test;
+----+----------------+------+--------------+
| id | left(data, 30) | ts   | length(data) |
+----+----------------+------+--------------+
|  1 | NULL           | NULL |         NULL |
+----+----------------+------+--------------+

Suggested fix:
* Bad fix but MySQL style: truncate to max_allowed_packet size (1M) and store data in field data.

* Correct and proper way: Through error and not a warning. And do a rollback and not overwrite value with NULL.

Thanks Oli.  Verified. Even sql_mode='strict_all_tables' does not help here!

Testcase
------
set sql_mode='strict_all_tables';
select version(),@@global.max_allowed_packet, @@sql_mode;
drop table if exists `t`;
create table `t` (`a` longtext charset latin1) engine=innodb;
insert into `t` set a=repeat('a',@@global.max_allowed_packet);
show warnings;
update `t` set `a`=concat(`a`, 'aa');
show warnings;
select * from `t`;
-----

mysql> set sql_mode='strict_all_tables';
Query OK, 0 rows affected (0.00 sec)

mysql> select version(),@@global.max_allowed_packet, @@sql_mode;
+-----------------+-----------------------------+-------------------+
| version()       | @@global.max_allowed_packet | @@sql_mode        |
+-----------------+-----------------------------+-------------------+
| 5.7.6-m16-debug |                     4194304 | STRICT_ALL_TABLES |
+-----------------+-----------------------------+-------------------+
1 row in set (0.00 sec)

mysql> drop table if exists `t`;
Query OK, 0 rows affected (0.10 sec)

mysql> create table `t` (`a` longtext charset latin1) engine=innodb;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into `t` set a=repeat('a',@@global.max_allowed_packet);
Query OK, 1 row affected (4.23 sec)

mysql> show warnings;
Empty set (0.00 sec)

mysql> update `t` set `a`=concat(`a`, 'aa');
Query OK, 1 row affected, 1 warning (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------+
| Level   | Code | Message                                                                     |
+---------+------+-----------------------------------------------------------------------------+
| Warning | 1301 | Result of concat() was larger than max_allowed_packet (4194304) - truncated |
+---------+------+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from `t`;
+------+
| a    |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

Also happens with 5.7.5

Posted by developer:
 
After discussion with BPS, it was felt that this problem should be fixed only from 5.7+.

Noted in 5.7.8, 5.8.0 changelogs.

Operations on a string exceeding max_allowed_packet bytes could
return NULL and incorrectly replace an existing value in UPDATE
statements with NULL rather than failing.