MySQL Bugs: #53902: Connection lost trying to insert large text into longtext column

Bug #53902	Connection lost trying to insert large text into longtext column
Submitted:	21 May 2010 20:17	Modified:	24 May 2010 6:34
Reporter:	Farid Zidan (Candidate Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.01.06.00	OS:	Windows (XP SP3)
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
Trying to insert large 24 MB-long text into longtext column. I get this error:

SQL Error. Native Code: 2013, SQLState: 08S01, Return Code: -1
[MySQL][ODBC 5.1 Driver][mysqld-5.1.44-community]Lost connection to MySQL server during query
SQL Error. Native Code: 2006, SQLState: HY000, Return Code: -1
[MySQL][ODBC 5.1 Driver]MySQL server has gone away
SQL Error. Native Code: 2006, SQLState: HY000, Return Code: -1
[MySQL][ODBC 5.1 Driver]MySQL server has gone away

How to repeat:
create table test_longtext (col1 integer, 
col2 longtext)

and then use bind parameters to insert 24 MB text into col3

I am attempting to move data from Firebird to MySQL using my app (data pump). The data pump works with no error moving this large row from Firebird to MS SQL Server, Oracle, DB2, Derby, PostgreSQL, SQLAnyere, SQLite, etc. I only get this error with MySQL.

I can provide the sample Firebird row and instructions on how to reproduce the error using my app CompareData, if needed.

Thank you for the problem report. Please, send my.cnf/my.ini file from your MySQL server. Alternatively, send the results of:

show global variables like '%pack%';

As you do not set max_allowed_packet value explicitly in my.ini, default is used:

mysql> show variables like '%pack%';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.00 sec)

So, there is no way to insert data longer than 1M. Please, check if you can insert data shorter than 1M in size successfully. 

Alternatively, you can try to execute:

set global max_allowed_packet=128*1024*1024;

on server, then reconnect your application and try again. 

Check http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_max_allowed_pac... for the details.

I added:
max_allowed_packet=1073741824

to my.ini and now the insert goes through with no error. Thanks.

My concern with this issue is that a special configuration parameter needs to be added to allow client to insert large text values. I have already defined the columns as longtext when I created the table so I already told the server I want to store very large values in that column when I created the table. Also instead of the server terminating the client connection where the parameter is not large enough it would be more helpful to issue an informative error message and just fail the DML/insert so user can take corrective action. Just my two cents.