| 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: | |
| 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
[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.
