1

I've burned about five hours on this, and questioned several co-workers, so I figure it might be time to ask for help.

I have a modest sized (~1GB) .sql dump file I'm trying to load into a database. Consequently, it's somewhat difficult to examine in most editors.

This file was clearly automatically generated by mysql, so I'm a bit surprised to be finding a syntax error. The top of the file states...

MySQL dump 10.13  Distrib 5.5.31, for debian-linux-gnu (x86_64)

I've tried to load this file with two different versions of MySql, the version I have (5.5.38) and the version that the file was created with (5.5.31). The specific issue apparently occurs on the following line...

/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `thing` BEFORE INSERT ON `crawler_project` FOR EACH ROW delete from django_session; */;;

And the specific error mentions that

ERROR 1064 (42000) at line 884: 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 '*/' at line 1

I understand that these types of comments allow different versions of mysql to consider different commands. Because my version and the creation version both exceed the versions specified by these comments, I removed the comment notation (it's considering these all anyway, I figured) and got this error...

ERROR 2006 (HY000) at line 884: MySQL server has gone away

It was at this point that I felt like I should stop editing an automatically generated file. Anyone have any thoughts for me?

Jack Hessel
  • 103
  • 1
  • 13

1 Answers1

2

Why this question is somewhat dated, I figured I would share my finding for those of you who are having similar problems with using dump files that are somewhat large in size.

In order to allow for updates via large dump files, MySQL must be configured to handle larger packet sizes. These are the following steps to update the file:

  1. Navigate to your MySQL config file. This SO question is an excellent reference for finding it.
  2. Find the max_allowed_packet option. If it does not exist, create it. You should set the size to reflect your needs based on whatever dump files you may be using, i.e. max_allowed_packet=500M sets the max packet size to 500MB. This SO question can help provide greater explanation and references for more information.
  3. Restart MySQL. If you've set the max packet size appropriately for your needs, then you should no longer have troubles with this error. If your errors continue and you've set the size appropriately, you may need to explore additional configuration options. This page has some excellent information to help you explore this further.
Community
  • 1
  • 1
cbrendanprice
  • 420
  • 4
  • 9