0

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
  • Do a mysqldump with the -d option (structure only) and post that on SQLFiddle or PasteBin. – Vérace Jan 29 '15 at 16:48
  • Now done the dump. SQLFiddle's down unfortunately. – Escher Jan 30 '15 at 16:25
  • What about using PasteBin? Just put the dump up and I'll see can I work with it here. – Vérace Jan 30 '15 at 16:43
  • Sorry, that completely didn't register in my brain (though I read the words). I was so engrossed in all the abstract errors. Thanks for your kind offer; I have now solved the problem and will post a solution. – Escher Jan 31 '15 at 18:53
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.