Mysql server goes away after ten queries

Question

I have a sql script, generated by MySQL Workbench, which creates my database / tables etc. It is of the form

-- MySQL Script generated by MySQL Workbench
-- 01/29/15 11:35:28
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema dbname
-- -----------------------------------------------------
-- Some description

-- -----------------------------------------------------
-- Schema dbname
--
-- Some description
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `dbname` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `dbname` ;

-- -----------------------------------------------------
-- Table `dbname`.`countries`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `dbname`.`countries` (
  `name` VARCHAR(40) NOT NULL,
  PRIMARY KEY (`name`))
ENGINE = InnoDB;
SHOW WARNINGS;

...

And so on, creating a number of tables and then inserting a few values.

However, if I try to run this, either by

mysql -u root --password=xxx < schema.sql

or

source /path/to/schema.sql;

in the mysql shell, I get the following

Query OK, 0 rows affected (0.00 sec)

...

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.01 sec)

Empty set (0.00 sec)

...

Query OK, 0 rows affected (0.01 sec)

Empty set (0.00 sec)

ERROR 2013 (HY000): Lost connection to MySQL server during query
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)
ERROR:
Can't connect to the server

No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)
ERROR:
Can't connect to the server

...

There are 10 successful queries, and the rest all fail. Running again gives the same (but with warnings, since some tables already exist now).

How do I stop this error 2013 and create all of my tables?

Update

I checked the log files, and found a series of logs of this form in /var/log/mysql/error.log

150130 11:55:32 [ERROR] Cannot find or open table playwhoo/joinRequests from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
how you can resolve the problem.

However, the only help on the net I can find is when this error occurs due to manually moving the files (not what I did, although I did take a backup before this problem started).

Check this answer http://stackoverflow.com/a/13544586/3922692 also this http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_net_write_timeout — ehwas, Jan 30 '15 at 09:36
The linked answer doesn't help, and surely the connection is via a socket (as one of the error lines says) and thus `net_write_timeout` won't do anything? — rspencer, Jan 30 '15 at 10:00

score 0 · Answer 1 · edited May 23 '17 at 11:49

Finally solved it after taking apart my schema.sql to see why it failed at that point.

Turns out I was not naming any of my constraints. That is, they all looked like this:

CONSTRAINT ``
  FOREIGN KEY (`user`)
  REFERENCES `dbname`.`users` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION

MySQL took exception to this, and generated a error 121. This was evident when entering the commands in the MySQL command line one by one. However, when done in a batch, the whole thing just fell over and gave the cryptic MySQL server has gone away.

I am still interested to know (in comments below), if there is a valid reason for the lack of propagation of the correct error. Why does it not say that there was an error 121 instead of failing to connect (as it does)?

Mysql server goes away after ten queries

1 Answers1