MySQL Bugs: #45236: large blob inserts from mysqldump fail, possible memory issue ?

Bug #45236	large blob inserts from mysqldump fail, possible memory issue ?
Submitted:	1 Jun 2009 8:46	Modified:	17 Jul 2009 3:33
Reporter:	Chris Anders	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S1 (Critical)
Version:	5.0.81 & 5.1.34	OS:	FreeBSD (6.3)
Assigned to:	Alexey Kopytov	CPU Architecture:	Any
Tags:	regression

Description:
After a fatal crash on our mysql server i tried to import a mysqldump which was taken the night before only to find it failed with:

"ERROR 2005 (HY000) at line 560: Unknown MySQL server host '13:16:02,988' (1)"

viewing line 560 out of my mysqldump showed that it was trying to insert 10meg of TEXT data into a row of type longblob (a text based log file that had been inserted as an attachment for Bugzilla).

Since we recently upgraded to 5.1.34 in production i figured it must be an issue with the current build so on one of our dev environments I installed our previous prod version 5.0.81 and tried to run the backup again only to hit the same error.

I then went back to version 5.0.51a using the same mysqldump for it to now succeed.

Thinking that this was somewhat odd I then used the 5.0.51a client to insert the backup into another dev environment running 5.0.81 which now worked!

After hours of fighting i have come to the conclusion there must be a memory buffer issue with large text files being inserted into longblobs in the current code stream.

The odd thing to add to this... If i change the data being inserted by adding a char or removing one, the insert works with the latest clients. It also works if i change the id to be less digits ie to 1.

to summarise:
- mysqldump from 5.1.24 (even tried with previous dumps from older versions)
- reimport fails with 5.1.24
- reimport fails with 5.0.81
- reimport works with 5.0.51a
- reimport works with 5.0.81 when using 5.0.51a client!
- shrink the data or id contents works with all clients

Note this is not a once off issue... if i remove line 560 and run the backup again i hit another insert of the same type that will cause the import to fail.

How to repeat:
create the following schema

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `bugzilla` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `bugzilla`;

--
-- Table structure for table `attach_data`
--

DROP TABLE IF EXISTS `attach_data`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `attach_data` (
  `id` mediumint(9) NOT NULL,
  `thedata` longblob NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 MAX_ROWS=100000000 AVG_ROW_LENGTH=10000000000;
/*!40101 SET character_set_client = @saved_cs_client */;

try to insert the following data (needs to be attached)

Suggested fix:
I have no idea what the problem is however this looks to be a memory buffer issue in the client application.

sample insert of large text data into row type longblob

Attachment: bung_data.sql.bz2 (application/octet-stream, text), 211.15 KiB.

The sample attached file has had all the A-z chars replaced to remove sensitive information.

Thank you for the report.

Have you specified large enough max_allowed_packet when doing dump/restore?

sorry i failed to mention that on each install i set on the server's my.cnf the following:

[mysqld]
max_allowed_packet=128M

and on import i run:
mysql --max_allowed_packet=128M < backup.sql

It is not very clear if and when max_allowed_packet can be defined for session by a client.

http://bugs.mysql.com/bug.php?id=32223  (now 568  days old!).

If i don't specify on the client the max_allowed_packet size my past successful imports would fail with the error message of received packet larger then max_allowed_packet (not word for word error but close enough). 

That suggests to me that you can on the client... ?

I wish I could give a clear reply.  This issue is an annoyance for us and our users/customers (of which many use shared hosting where they cannot access server configuration).  It would really be nice it it was possible to write a "SET max_allowed_packet .." statement on top of the file itself.

But I do not know what status is and what plans are or if there is any activity at all. I am waiting and I am not a MySQL person!

Wiht too low max_allowed_packet setting I normally get 'mysql server has gone away'.  The error 'got a packet too large' I have not seen since MySQL 3.23/4.0 I think! Also this is not very good. 'got a packet too large' is a much more precise error than 'mysql server has gone away'.  But for the last many years I have only seen 'gone away' - what means that the server simply drops the connection to the client.

I dont believe this to be a max_allowed_packet issue however it is evident that something has changed from the earlier mysql client version of 5.0.51a to the current client which you can easily reproduce using my attached file.

whats amazes me is that it will insert using the current client if the id is smaller or the data is smaller by 1 char.

Thank you for the feedback.

Verified as described.

Really 1 char affects this. I'll upload dump for reproduce.

problem dump

Attachment: bug45236_corrupted.sql.bz2 (application/octet-stream, text), 119.65 KiB.

OK dump

Attachment: bug45236_OK.sql.bz2 (application/octet-stream, text), 119.66 KiB.

Had the same issue with importing dumps produced by 5.1.23 into 5.1.34, both on Linux. Seems that it is truncating query on an INSERT of a large string into a longtext column.

Technically, this bug is not a regression. Steps to reproduce it on the 5.0.51a client:

mysql --max_allowed_packet=1M -uroot test <../bug45236_corrupted.sql

I.e. you just need to explicitly set max_allowed_packet to 1M. Note that the client-side max_allowed_packet applies only to the buffer reserved for receiving data from the server, data sent by the client can be as long as the *server-side* max_allowed_packet allows.

The reason why it is reproducible with default max_allowed_packet on 5.0.81, but not on 5.0.51a is the patch for bug #41486. 

5.0.51a uses max_allowed_packet to determine the size of an internal readline buffer which does not actually make any sense for the reason described above. The patch for bug #41486 changes this behavior so that this internal buffer is always 1M, so this bug is triggered even with the default max_allowed_packet.

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

2764 Alexey Kopytov	2009-06-10
      Bug #45236: large blob inserts from mysqldump fail, possible 
                  memory issue ? 
       
      The mysql command line client could misinterpret some character 
      sequences as commands under some circumstances. 
       
      The upper limit for internal readline buffer was raised to 1 GB 
      (the same as for server's max_allowed_packet) so that any input 
      line is processed by add_line() as a whole rather than in 
      chunks.
     @ client/mysql.cc
        The upper limit for internal readline buffer was raised to 1 GB 
        (the same as for server's max_allowed_packet) so that any input 
        line is processed by add_line() as a whole rather than in 
        chunks.
     @ mysql-test/r/mysql-bug45236.result
        Added a test case for bug #45236.
     @ mysql-test/t/mysql-bug45236.test
        Added a test case for bug #45236.

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

2776 Alexey Kopytov	2009-06-17
      Disabled embedded server for the test case for bug #45236.

Pushed into 5.0.84 (revid:joro@sun.com-20090707074938-ksah1ibn0vs92cem) (version source revid:alexey.kopytov@sun.com-20090617161048-cyvlf7dsrxc04prf) (merge vers: 5.0.84) (pib:11)

Pushed into 5.1.37 (revid:joro@sun.com-20090708131116-kyz8iotbum8w9yic) (version source revid:alexey.kopytov@sun.com-20090617161250-78jztppb2h0tj0tc) (merge vers: 5.1.36) (pib:11)

Pushed into 5.0.84 (revid:joro@sun.com-20090707074938-ksah1ibn0vs92cem) (version source revid:alexey.kopytov@sun.com-20090617161048-cyvlf7dsrxc04prf) (merge vers: 5.0.84) (pib:11)

Pushed into 5.1.37 (revid:joro@sun.com-20090708131116-kyz8iotbum8w9yic) (version source revid:alexey.kopytov@sun.com-20090617161250-78jztppb2h0tj0tc) (merge vers: 5.1.36) (pib:11)

Pushed into 5.4.4-alpha (revid:anozdrin@bk-internal.mysql.com-20090710111017-bnh2cau84ug1hvei) (version source revid:kristofer.pettersson@sun.com-20090617180427-zxqutdhvhzdhoa8s) (merge vers: 5.4.4-alpha) (pib:11)

Hello,

I am facing the the same problem with Ubuntu 9.04 and mysql 5.4.1 beta. Is there a work around ? please share ..

Noted in 5.1.37, 5.4.4 changelogs.

The mysql client could misinterpret some character sequences as
commands under some circumstances.

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.

Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)

Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)

Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)

Ways in which this bug may manifest itself:

-------
mysql> source /filename.sql
...
...
...
No connection. Trying to reconnect...
ERROR 1102 (42000): Incorrect database name '????<garbled chars>????'
ERROR:
Can't connect to the server
-------

-------
mysql> source /filename.sql
...
...
...
No connection. Trying to reconnect...
ERROR 2005 (HY000): Unknown MySQL server host '????<garbled chars>????' (1)
ERROR:
Can't connect to the server
-------

-------
mysql> source /u01/filename.sql
...
...
...
No connection. Trying to reconnect... uo
<garbled chars>Y000): Unknown MySQL server host '????<garbled chars>????' (1)
ERROR:
Can't connect to the server

mysql>
1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c1;2c .... (continues for some time)
-------

The 5.4 fix has been pushed to 5.4.2.