Bug #14645 restoring large sql file causes syntax errors in correct file

Submitted: 4 Nov 2005 14:46 Modified: 26 Jun 2006 23:04
Reporter: Lukas Smith Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S2 (Serious)
Version:5.0.17-BK, 5.0.15 OS:Linux (Linux, windows xp sp2)
Assigned to: CPU Architecture:Any

[4 Nov 2005 14:46] Lukas Smith
Description:
Using mysql administrator 1.1.4 (or mysql query browser's 1.1.17 script loading capability) I have been unable to load the sakilar2.mysql sample database (http://www.openwin.org/mike/download/sakila2.zip) without getting syntax errors on statements that run through perfectly when send separately.

I am using mysql 5.0.15 with stock options.

How to repeat:
start  mysql administrator
go to restore
open the sakilar2.mysql file contained in http://www.openwin.org/mike/download/sakila2.zip
restore

Suggested fix:
I presume there are issues on the SQL parsing.
[7 Nov 2005 16:00] Valeriy Kravchuk
Thank you for a problem report. I tried to restore your data on a fresh 5.0.15-nt using MySQL Adminsitrator 1.1.4, and got the following error messages:

Warning: Do not know how to handle this statement at line 2300:
/*!50001 DROP VIEW IF EXISTS `customer_list`*/;
Ignoring this statement. Please file a bug-report including the statement if this statement should be recognized.

Warning: Do not know how to handle this statement at line 2301:
/*!50001 CREATE TABLE `customer_list` (
  `ID` int(10) unsigned NOT NULL default '0',
  `name` varchar(91) character set utf8 NOT NULL default '',
  `address` varchar(255) character set utf8 NOT NULL default '',
  `zip code` varchar(20) character set utf8 NOT NULL default '',
  `phone` varchar(20) character set utf8 NOT NULL default '',
  `city` varchar(60) character set utf8 NOT NULL default '',
  `country` varchar(60) character set utf8 NOT NULL default '',
  `notes` varchar(6) character set utf8 NOT NULL default '',
  `SID` int(10) unsigned NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1*/;
Ignoring this statement. Please file a bug-report including the statement if this statement should be recognized.

The MySQL Server returned this Error:
MySQL Error Nr.1064-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 '/
/*!50003 CREATE TRIGGER `ins_film` AFTER INSERT ON `film` FOR EACH ROW BEGIN
 ' at line 1

But when I tried to use the same your "dump" to restore with mysql client, it also did not worked. I've got a server crash:

C:\Documents and Settings\openxs>mysql -uroot -p -P3307 < c:\work\sakilar2.mysql

Enter password: ****
ERROR 2006 (HY000) at line 3348: MySQL server has gone away

It is just the line before those reported in the MySQL Administrator. Please, check if the file you sent really works on your machine.

Later I tried to restore it on 5.0.16-BK on Linux and got some clue on what happened:

[openxs@Fedora 5.0]$ bin/mysql -uroot < /tmp/sakilar2.mysql
ERROR 1153 (08S01) at line 3348: Got a packet bigger than 'max_allowed_packet' bytes
[openxs@Fedora 5.0]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20 to server version: 5.0.16

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

mysql> exit
Bye
[openxs@Fedora 5.0]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21 to server version: 5.0.16

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

mysql> show variables like 'max%';
+----------------------------+------------+
| Variable_name              | Value      |
+----------------------------+------------+
| max_allowed_packet         | 1048576    |
...

So, please, send the results of the same, show varaibles like 'max%', statement from your machine.
[7 Nov 2005 17:05] Lukas Smith
Seems to match your result.

mysql> show variables like 'max%';
+----------------------------+------------+
| Variable_name              | Value      |
+----------------------------+------------+
| max_allowed_packet         | 1048576    |
| max_binlog_cache_size      | 4294967295 |
| max_binlog_size            | 1073741824 |
| max_connect_errors         | 10         |
| max_connections            | 100        |
| max_delayed_threads        | 20         |
| max_error_count            | 64         |
| max_heap_table_size        | 16777216   |
| max_insert_delayed_threads | 20         |
| max_join_size              | 4294967295 |
| max_length_for_sort_data   | 1024       |
| max_relay_log_size         | 0          |
| max_seeks_for_key          | 4294967295 |
| max_sort_length            | 1024       |
| max_tmp_tables             | 32         |
| max_user_connections       | 0          |
| max_write_lock_count       | 4294967295 |
+----------------------------+------------+

I forgot to mention that I also tried the CLI and got the same errors (mysql server gone away).

I am not sure if there are some issues with the /*!50001 conditional stuff though. I seemed to get other errors when I removed those (but left other comments in). Then again it might just have led to parser errors in a different place, or different packets being screwed up.
[8 Nov 2005 11:46] Valeriy Kravchuk
No, I think the real problem is related to the max_allowed_packet=1048576. Let me check. Changed category as it leads to server crash...
[10 Nov 2005 14:52] Valeriy Kravchuk
Please, try to set max_allowed_packet larger than your file (10485760, for example) and repeat restoring. Inform about the results.
[11 Nov 2005 8:47] Lukas Smith
C:\Dokumente und Einstellungen\lsmith\Eigene Dateien\sql\phpconf05>mysql -u root
 --max_allowed_packet=10485760 < sakilar2.mysql
ERROR 1064 (42000) at line 3348: You have an error in your SQL syntax; check the
 manual that corresponds to your MySQL server version for the right syntax to us
e near '/
/*!50003 CREATE TRIGGER `ins_film` AFTER INSERT ON `film` FOR EACH ROW BEGIN I'
at line 1
[13 Nov 2005 14:10] Valeriy Kravchuk
Verified just as you described on 5.0.17-BK (ChangeSet@1.1967, 2005-11-11 13:39:46+04:00, ...) on Linux:

mysql> set global max_allowed_packet=10485760;
Query OK, 0 rows affected (0.03 sec)

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

mysql> exit
Bye
[openxs@Fedora 5.0]$ bin/mysql -uroot --max_allowed_packet=10485760 < /tmp/sakilar2.mysql
ERROR 1064 (42000) at line 3348: 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 '/
/*!50003 CREATE TRIGGER `ins_film` AFTER INSERT ON `film` FOR EACH ROW BEGIN
' at line 1
[openxs@Fedora 5.0]$ ls -l /tmp/sakilar2.mysql
-rwx------    1 openxs   openxs    2215384 Nov  7 18:50 /tmp/sakilar2.mysql
[openxs@Fedora 5.0]$ uname -a
Linux Fedora 2.4.22-1.2115.nptl #1 Wed Oct 29 15:42:51 EST 2003 i686 i686 i386 GNU/Linux
[14 Nov 2005 8:44] Lukas Smith
Just to make sure that there is no confusing about the max allowed paket setting. There is one for the server and one for the client. I am not sure if you can even see the client setting using "show variables". Eitherway I do not see a change in that setting when using the "mysql --max_allowed_paket" option on windows.
[23 Jun 2006 15:00] Valeriy Kravchuk
The real problem was:

DELIMITER //;
/*!50003 SET SESSION SQL_MODE="" */ //
/*!50003 CREATE TRIGGER `ins_film` AFTER INSERT ON `film` FOR EACH ROW BEGIN
    INSERT INTO film_text (film_id, title, description)
        VALUES (new.film_id, new.title, new.description);
  END */ //

That is: wrong delimiter: "//;" produced by older (5.0.13) version of mysqldump. Now mysqldump uses ;; as a delimiter, for example:

LOCK TABLES `location` WRITE;
INSERT INTO `location` VALUES (1,10.1,'Good, positive');
UNLOCK TABLES;
/*!40000 ALTER TABLE `location` ENABLE KEYS */;

/*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/;
DELIMITER ;;
/*!50003 SET SESSION SQL_MODE="" */;;
/*!50003 CREATE */ /*!50017 DEFINER=`root`@`localhost` */ /*!50003 TRIGGER `loca
tion_update_before` BEFORE UPDATE ON `location` FOR EACH ROW BEGIN
CASE
WHEN new.latitude < -180.0 THEN
SET new.comments = 'Bad, too low';
SET new.latitude = old.latitude;
WHEN new.latitude > 180.0 THEN
SET new.comments = 'Bad, too high';
SET new.latitude = old.latitude;
WHEN new.latitude < 0.0 THEN
SET new.comments = 'Good, negative';
WHEN new.latitude = 0.0 THEN
SET new.comments = 'Good, zero';
ELSE
SET new.comments = 'Good, positive';
END CASE;
END */;;

DELIMITER ;
/*!50003 SET SESSION SQL_MODE=@OLD_SQL_MODE */;
...

So, it was mysqldump bug, already fixed.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.056 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.