| Bug #29638 | syntax error when inserting large hex strings with sufficient max_allowed_packet | ||
|---|---|---|---|
| Submitted: | 9 Jul 2007 6:41 | Modified: | 31 Jul 2007 6:55 |
| Reporter: | Sean Pringle | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0, 5.1 | OS: | Any |
| Assigned to: | Ramil Kalimullin | CPU Architecture: | Any |
[9 Jul 2007 7:24]
Sean Pringle
Setting max_allowed_packet for the MySQL client also and piping allows the import to work: mysql -u root --max_allowed_packet=1073741824 test < t1_dump.sql Still don't know why normally escaped blobs of any size and much larger INSERT statements apparently work without this step.
[9 Jul 2007 10:09]
Sveta Smirnova
Thank you for the report. I can not repeat described behavior nor with current 5.0 and 5.1 development sources, neither with 5.0.41. Although bug is repeatable with version 5.0.38. So i close this report as "Can't repeat" Please upgrade to current version.
[9 Jul 2007 12:29]
Sean Pringle
Thanks Sveta. I must be missing something here at my end; here are results from MySQL 5.0.44 Enterprise. I will test BK sources myself tomorrow.
Linux mythago 2.6.20-15-generic #2 SMP Sun Apr 15 07:36:31 UTC 2007 i686 GNU/Linux
mysql-enterprise-gpl-5.0.44-linux-i686-glibc23
1. Create data:
mysql> select version();
+-----------------------+
| version() |
+-----------------------+
| 5.0.44-enterprise-gpl |
+-----------------------+
1 row in set (0.00 sec)
mysql> show variables like 'max_allowed_packet';
+--------------------+------------+
| Variable_name | Value |
+--------------------+------------+
| max_allowed_packet | 1073740800 |
+--------------------+------------+
1 row in set (0.00 sec)
mysql> CREATE TABLE `t1` (`f1` longblob) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO t1 VALUES (REPEAT("\0",(1024*1024*16)));
Query OK, 1 row affected (0.55 sec)
2. Dump to sql file using --hex-blob:
shell> mysqldump -V
mysqldump Ver 10.11 Distrib 5.0.44, for pc-linux-gnu (i686)
shell> mysqldump --hex-blob --add-drop-table -u root test > test.sql
3. Reload data dump into same instance and session of MySQL:
mysql> source test.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '00000000000000000000000000000000000000000000000000000000000000000000000000000000' at line 2
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Dump data without --hex-blob:
shell> mysqldump --add-drop-table -u root test > test.sql
4. Reload new sql file into same insatnce and session of MySQL:
mysql> INSERT INTO t1 VALUES (REPEAT("\0",(1024*1024*16)));
Query OK, 1 row affected (0.56 sec)
mysql> source test.sql
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.17 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (1.25 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> select length(f1) from t1;
+------------+
| length(f1) |
+------------+
| 16777217 |
+------------+
1 row in set (0.03 sec)
5. Retry --hex-blob dump/load piping to MySQL cli (error):
shell> mysql -u root test <test.sql
ERROR 1064 (42000) at line 33: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '00A00000000000000000000000000000000000000000000000000000000000000000000000000000' at line 2
shell>
6. Retry no --hex-blob dump/load piping to MySQL cli (no error):
shell> mysql -u root test <test.sql
shell>
7. Retry --hex-blob dump specifying max_allowed_packet manually and piping (no error):
shell> mysql -u root --max_allowed_packet=1073740800 test <test.sql
shell>
[9 Jul 2007 12:37]
Sean Pringle
Sorry, cut and paste mistake above; Step 4 started with:
mysql> INSERT INTO t1 VALUES (REPEAT("\0",(1024*1024*16)));
Query OK, 1 row affected (0.56 sec)
This INSERT was meant to be above the previously pasted mysqldump command:
shell> mysqldump --add-drop-table -u root test > test.sql
Retested to confirm same results.
[9 Jul 2007 12:48]
Sean Pringle
double checking 5.0.44
Attachment: testcase_5.0.44.txt (text/plain), 3.22 KiB.
[10 Jul 2007 9:44]
Sveta Smirnova
Sorry: missed max_allowed_packet influence.
Verified as described using BK sources:
$bin/mysql -uroot --socket=/tmp/mysql_ssmirnova.sock test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.46-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE `t1` (`f1` longblob) ENGINE=MyISAM;
ERROR 1050 (42S01): Table 't1' already exists
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE `t1` (`f1` longblob) ENGINE=MyISAM;
Query OK, 0 rows affected (0.11 sec)
mysql> INSERT INTO t1 VALUES (REPEAT("\0",(1024*1024*16)));
Query OK, 1 row affected (0.46 sec)
mysql> select length(f1) from t1;
+------------+
| length(f1) |
+------------+
| 16777216 |
+------------+
1 row in set (0.04 sec)
mysql> \q
Bye
$bin/mysqldump -uroot --socket=/tmp/mysql_ssmirnova.sock --hex-blob --add-drop-table test t1>bug29638_hex.sql
$bin/mysqldump -uroot --socket=/tmp/mysql_ssmirnova.sock --add-drop-table test t1>bug29638_normal.sql
$bin/mysql -uroot --socket=/tmp/mysql_ssmirnova.sock test <bug29638_hex.sql
ERROR 1064 (42000) at line 33: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '00000000000000000000000000000000000000000000000000000000000000000000000000000000' at line 2
$bin/mysql -uroot --socket=/tmp/mysql_ssmirnova.sock test <bug29638_normal.sql
$bin/mysql -uroot --socket=/tmp/mysql_ssmirnova.sock test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.46-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select length(f1) from t1;
+------------+
| length(f1) |
+------------+
| 16777217 |
+------------+
1 row in set (0.04 sec)
mysql> \q
Bye
[31 Jul 2007 6:55]
Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php max_allowed_packet for client is independent from same setting on server. See also http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html
Description: Inserting a LONGBLOB as a hex string larger than 32M results in a syntax error on both 5.0 and 5.1. ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0000 0000000000000000000000000000000000000000000000000000000000000000000000000000' This affects INSERT statements created using "mysqldump --hex-blob" even with max_allowed_packet=1G on subsequent import. Dumping the same data without --hex-blob has no problems. It appears that the maximim hex string length is equal to: 32M - length of remainder of insert statement. How to repeat: Set max_allowed_packet=1G for both mysqld and mysqldump mysql> USE test; mysql> CREATE TABLE `t1` (`f1` longblob) ENGINE=MyISAM; mysql> INSERT INTO t1 VALUES (REPEAT("\0",(1024*1024*16))); shell> mysqldump --hex-blob --add-drop-table test t1 >t1_dump.sql mysql> source t1_dump.sql ... ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0000 0000000000000000000000000000000000000000000000000000000000000000000000000000' at line 2 ... * Note - REPEAT syntax is not valid in 5.1, but importing the 5.0 generated sql dump file in 5.1 demonstrates the issue. Suggested fix: Considering error message, appears to be a parser problem? Dealing with single fields greater then 32M is not apparent elsewhere. a) If hex strings are length limited in any way besides max_allowed_packet, this should be documented (couldn't see where it was). mysqldump --hex-blob needs to emphasize this. The 'syntax error' message is not helpful. b) If hex strings should not be length limited except by max_allowed_packet, then something is amiss...