samedi 31 janvier 2015

Dropped connections, corrupted schema ... is MySQL sick?


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




Aucun commentaire:

Enregistrer un commentaire