MySQL – Dropped Connections and Corrupted Schema Issues

foreign keyinnodbMySQLmysql-workbenchschema

Lot's of problems and I wonder if it's all tracable to some root cause. I'm trying to load a simple schema related to product marketing. The symptoms are:

Not being able to forward engineer said schema mysql -u root -p dbeq < dbeq.sql due to loosing connection with the server. 11kb file, 13 tables, each table has at most 13 columns, and the sql file is the output of MySQLWorkbench, so pretty much gauranteed to be syntax error-free. It does SET foreign_key_checks=0 at the start, but I've checked the file table by table; it creates them all in the correct order so that no mismatched foreign key dependencies occur.
Frequent **ERROR 2013 (HY000) at line 47: Lost connection to MySQL server during query type messages, even when pasting CREATE TABLE statements at the mysql shell (which are 50ms queries at maximum). Max packet is 16Mb and write timeout is one minute, so those can't be the constraints.
The database becomes corrupted by the last table each time (can't create table because it exists, can't drop it because it doesn't).
MySQL workbench can't forward engineer directly to the database either; the connection to the MySQL server gets dropped in under a second.

Do I have something wrong with MySQL? I'm going to try creating the schema on a different machine at home (Unfortunately SQLFiddle is down so I haven't been able to test the schema there).

Output of mysqlcheck -u root -p dbeq (noting that it took 3 goes to create this many tables)

dbeq.AB_test_questions                             OK
dbeq.AB_test_responses_v0                          OK
dbeq.ABs                                           OK
dbeq.business_units                                OK
dbeq.customers                                     OK
dbeq.normalization_survey_customers
Error    : Table 'dbeq.normalization_survey_customers' doesn't exist
status   : Operation failed
dbeq.normalization_survey_questions                OK
dbeq.product_test_questions                        OK
dbeq.product_test_responses_v0                     OK
dbeq.products                                      OK
dbeq.project_managers                              OK
dbeq.survey_hashtable                              OK
dbeq.surveys                                       OK

Output of mysqldump -d dbeq

-- MySQL dump 10.13  Distrib 5.5.41, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: dbeq
-- ------------------------------------------------------
-- Server version   5.5.41-0ubuntu0.14.04.1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
mysqldump: Got error: 1146: Table 'dbeq.normalization_survey_customers' doesn't exist when using LOCK TABLES

Best Answer

Someone fetch me a big spoon, I've got a huge slice of humble pie to get stuck into.

I have no formal database training, but as a developer I have had to interact with databases (mainly via PHP). I've never been the person to create the schema. In debugging this, I was having a really hard time, reading many many SE posts (thanks Rolando, I've lost track of how many of your posts I've read this last week) and the MySQL manual. The trouble is that the errors were all so dang abstract - lost connection error, table doesn't exist error, etc.

So I just tried to run the schema on a machine that had never hosted that database. The server wouldn't take it, and so I knew it couldn't have been all the crazy silly things I had done with the data dictionary. SQLfiddle didn't like it either, and it was something to do with foreign keys because it balked at the tables with them (as did the server).

Turns out that it was just by luck that most of my FKs were uniquely named! I did not know you had to do that. All of the tutorials and lessons I had read never had a complicated-enough example that it became an issue. So after a quick lesson I changed the keys and presto everything works!

SQL's simple syntax and experience with other abstract "tables" of data (spreadsheets, R/Matlab) fools developers into thinking DBs are simple. Doing an enterprise-size schema by youself ought to be compulsory at least once in every developer's life. You guys deserve serious respect.

Related Solutions

Mysql – Trying to use MySQL Workbench with TCP/IP over SSH – failed to connect

I stumbled upon this question when I myself had encountered this error. I could finally figure out the configuration.

I didn't touch anything in /etc/mysql/my.cnf which already has bind_address = 127.0.0.1. So only localhost can connect.
I use OpenSSH server. So in its config file /etc/ssh/sshd_config I changed from no to yes the param responsible for TCP forwarding, thus AllowTcpForwarding yes.
Finally I have the following entered in MySQL WorkBench.
- SSH Hostname: 192.168.0.8:22 (my SSH server listens to port 22)
- SSH Username: sshuser
- SSH Key File: *C:\Users\windowsuser\.ssh\id_rsa* (should be private key, even though it says public)
- MySQL Hostname: 127.0.0.1 (this should not be changed, since MySQL server by default is bound to localhost only which I didn't change)
- MySQL Server Port: 3306 (also default)
- Username: root

The only remaining thing for you is to configure correctly your SSH server to work with keys rather than passwords. Hope this will help someone.

MySQL Load Data Infile Slow Query Logging – How to Resolve

The query under process ID 180233 looks like it is in distress.

Here is the query itself

SELECT COUNT(DISTINCT A.`campaignid`)  INTO _c
FROM `ox_campaigns` A 
INNER JOIN `selfserving_users` B ON B.`user_id` = A.`uid`
INNER JOIN `v3_cam_date` C ON C.`campaignid` = A.`campaignid`
WHERE A.`revenue_type` = 5 AND A.`deleted` = 0
AND A.`expire` = DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY) 
AND A.`isExpired` = 0
AND IF( NAME_CONST('_permitid',3) = -1, 1=1,
IF( NAME_CONST('_permitid',3) = 0, A.`uid` IN
(SELECT C.`user_id` FROM `selfserving_users` C
WHERE C.`groupid` =  NAME_CONST('_groupid',12) ) ,
A.`uid` =  NAME_CONST('userid',388)));

The scary part about the query is the self-reference

You have selfserving_users acting in a self serving manner against itself.

Sometimes, the MySQL Query Optimizer will play a bait-and-switch, smoke-and-mirrors games with data, especially with a self reference, in order to formulate the best EXPLAIN plan possible. While mysql is very capable of completing sub-SELECTs, it can be still be expensive.

However, this is just a symptom that manifested because of Process ID 97. What is really the issue here?

LOAD DATA INFILE against an InnoDB table could make mysqld a little punch drunk. I don't believe (or at least I don't exercise full confidence) you can encapsulate it as a normal transaction although this was addressed back in MySQL 5.0.

Just picture it:

You are hammering the InnoDB Buffer
Some memory swapping may be going on
Possible full table locking issues that are affecting data pages outside the v3_zone_date table (such was with the selfserving_users table)

There may be a way to throttle the LOAD DATA INFILE process on an InnoDB table. I cannot give you a solid answer on this one, but try this link from Baron Schwartz.

UPDATE 2012-02-22 12:00 EST

There is open bug report in MySQL 5.5.7 called Deadlock when DDL under LOCK TABLES WRITE, READ + PREPARE. At the bottom of the report, a person complained about a block problem cause by the explicit LOCK TABLES.

Launching a COMMIT on locked rows in a table would hang because of trying to unraveling MVCC data assocaited with the locked rows. Based on the InnoDB Status you have shown, there would exist 6933 row locks on the table you are importing. I know that in Oracle, when introducing new rows to a table, MVCC is still generated because the previous version of the newly inserted row is a nonexistent row. The same must be occurring for InnoDB.

UPDATE 2012-02-22 12:42 EDT

In your question you stated the following about your .NET process

LOCK TABLES;
SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
LOAD DATA;
COMMIT;
UNLOCK TABLES;
SET autocommit=1;
SET unique_checks=1;

All of these events are running within the same DB Session. This is also happening within one DB Connection. Thus, this is not a deadlock in the traditional sense. It is just a case of blocking your COMMIT within a given DB Connection/Session because the tables were locked within the same DB Connection/Session.

UPDATE 2012-02-23 19:00 EDT

I would change the sequence to be this:

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
LOCK TABLES;
LOAD DATA;
UNLOCK TABLES;
COMMIT;
SET autocommit=1;
SET unique_checks=1;
SET foreign_key_checks=1;

Please remember, a COMMIT cannot proceed if you have the tables locked in serial fashion. Therefore, UNLOCK TABLES must precede COMMIT.