加载数据库文件时出现SQL语法错误(可能是版本错误?) [英] SQL Syntax Error when Loading Database File (Possible Version Error?)

问题描述

我为此花了大约五个小时，并对几个同事进行了质询，所以我认为可能是时候寻求帮助了.

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

我有一个中等大小的(.1GB).sql转储文件，我正在尝试将其加载到数据库中.因此，在大多数编辑器中进行检查有些困难.

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.

该文件显然是由mysql自动生成的，因此我发现语法错误感到有些惊讶.文件顶部指出...

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)

我尝试用两个不同版本的MySql加载该文件，我拥有的版本(5.5.38)和使用该文件创建的版本(5.5.31).具体问题显然发生在以下行中……

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; */;;

具体错误提到了

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

我知道这些注释类型允许不同版本的mysql考虑不同的命令.因为我的版本和创建版本都超过了这些注释所指定的版本，所以我删除了注释符号(我认为它一直在考虑这些注释)，并出现了此错误...

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?

推荐答案

为什么这个问题有些过时，我想与那些使用较大的转储文件时遇到类似问题的人分享我的发现

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.

为了允许通过大型转储文件进行更新，必须将MySQL配置为处理较大的数据包大小.这些是更新文件的以下步骤:

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. 导航到您的MySQL配置文件. 此SO问题是找到它的绝佳参考.
2. 找到max_allowed_packet选项.如果不存在，请创建它.您应该根据可能使用的任何转储文件设置大小以反映您的需求，即max_allowed_packet=500M将最大数据包大小设置为500MB. 此SO问题可以帮助提供更多的解释和参考，以获取更多信息.
3. 重新启动MySQL.如果您已根据需要适当设置了最大数据包大小，则此错误将不再引起您的麻烦.如果错误仍然存​​在并且已正确设置大小，则可能需要探索其他配置选项. 此页面提供了一些极好的信息，可帮助您进一步探索.

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.

这篇关于加载数据库文件时出现SQL语法错误(可能是版本错误?)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！