[MDEV-7135] MySQL restarted after failed creating FK Created: 2014-11-19  Updated: 2014-11-22  Due: 2014-12-10  Resolved: 2014-11-22

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.0.14
Fix Version/s: 10.0.15

Type: Bug Priority: Critical
Reporter: Aurélien LEQUOY Assignee: Elena Stepanova
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Blocks
is blocked by MDEV-7091 10.0.15 merge Closed
Duplicate
duplicates MDEV-6922 mysqld signal 11 crash when running m... Closed

 Description    

2014-11-19 09:36:46 7f148ef22700  InnoDB: Assertion failure in thread 139726274307840 in file dict0mem.h line 1165
 
InnoDB: Failing assertion: ret.second
 
InnoDB: We intentionally generate a memory trap.
 
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
 
InnoDB: If you get repeated assertion failures or crashes, even
 
InnoDB: immediately after the mysqld startup, there may be
 
InnoDB: corruption in the InnoDB tablespace. Please refer to
 
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
 
InnoDB: about forcing recovery.
 
141119  9:36:46 [ERROR] mysqld got signal 6 ;
 
This could be because you hit a bug. It is also possible that this binary
 
or one of the libraries it was linked against is corrupt, improperly built,
 
or misconfigured. This error can also be caused by malfunctioning hardware.

query played : 

ALTER TABLE
 
  `PROD_ATTENTE_CONFIGURATION_IMPOSITION`
 
ADD
 
  FOREIGN KEY (`ID_PROD_COMMANDE`) REFERENCES `PRODUCTION`.`PROD_COMMANDES`(`ID_PROD_COMMANDE`) ON DELETE RESTRICT ON UPDATE RESTRICT;
 

Warning: mysqli_query(): MySQL server has gone away in /data/www/photobox/vendor/glial/glial/Glial/Sgbd/Sql/Mysql/Mysql.php on line 91
 
 
 
Warning: mysqli_query(): Error reading result set's header in /data/www/photobox/vendor/glial/glial/Glial/Sgbd/Sql/Mysql/Mysql.php on line 91
 
SQL : ALTER TABLE `PROD_ATTENTE_CONFIGURATION_IMPOSITION` ADD FOREIGN KEY (`ID_PROD_COMMANDE`) REFERENCES `PRODUCTION`.`PROD_COMMANDES`(`ID_PROD_COMMANDE`) ON DELETE RESTRICT ON UPDATE RESTRICT;
 
 
 
MySQL server has gone away



 Comments   
Comment by Aurélien LEQUOY [ 2014-11-19 ]

I didn't got this problem on 2 slaves, and got this problem on slave's history.

Comment by Aurélien LEQUOY [ 2014-11-19 ]

Reproduced on my Master unfortunatly ...

141119 10:41:55 [ERROR] mysqld got signal 11 ;
 
This could be because you hit a bug. It is also possible that this binary
 
or one of the libraries it was linked against is corrupt, improperly built,
 
or misconfigured. This error can also be caused by malfunctioning hardware.
 
 
 
To report this bug, see http://kb.askmonty.org/en/reporting-bugs
 
 
 
We will try our best to scrape up some info that will hopefully help
 
diagnose the problem, but since we have already crashed,
 
something is definitely wrong and this may fail.
 

ALTER TABLE
 
  `PROD_ATTENTE_INTEGRATION`
 
ADD
 
  FOREIGN KEY (`ID_PROD_COMMANDE`) REFERENCES `PRODUCTION`.`PROD_COMMANDES`(`ID_PROD_COMMANDE`) ON DELETE RESTRICT ON UPDATE RESTRICT;
 

Warning: mysqli_query(): MySQL server has gone away in /data/www/photobox/vendor/glial/glial/Glial/Sgbd/Sql/Mysql/Mysql.php on line 91
 
 
 
Warning: mysqli_query(): Error reading result set's header in /data/www/photobox/vendor/glial/glial/Glial/Sgbd/Sql/Mysql/Mysql.php on line 91

both servers & hdd are differents this time so he can't be an hardware issue.

Comment by Elena Stepanova [ 2014-11-19 ]

Hi,

Could you please provide the output of
SHOW CREATE TABLE `PROD_ATTENTE_CONFIGURATION_IMPOSITION`
SHOW CREATE TABLE `PRODUCTION`.`PROD_COMMANDES`

and also attach your cnf file(s)?

Even better you you can upload the data dump of the above tables to our ftp.askmonty.org/private.

If you can't provide a dump, please also add SHOW INDEX IN .. and SHOW TABLE STATUS LIKE '..' for both tables.

Comment by Aurélien LEQUOY [ 2014-11-19 ] 

SHOW CREATE TABLE `PROD_ATTENTE_CONFIGURATION_IMPOSITION`
 
 
 
CREATE TABLE `PROD_ATTENTE_CONFIGURATION_IMPOSITION` (
 
 `ID_PROD_ATTENTE_CONFIGURATION_IMPOSITION` bigint(20) NOT NULL AUTO_INCREMENT,
 
 `COMMENTAIRE` varchar(255) DEFAULT NULL,
 
 `COMPOSANT` varchar(16) DEFAULT NULL,
 
 `DATE_PASSAGE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 
 `GROUPE_LOTIFICATION` varchar(50) NOT NULL,
 
 `ID_STATUT` bigint(20) NOT NULL,
 
 `ID_PROD_ITEM` int(10) unsigned DEFAULT NULL,
 
 `ORIGINE` varchar(32) NOT NULL,
 
 `VERSION` int(10) unsigned NOT NULL DEFAULT '1',
 
 `SERVEUR_TRAITEMENT` varchar(255) DEFAULT NULL,
 
 `DATE_CREATION` datetime DEFAULT NULL,
 
 `DUREE_TRAITEMENT_MILLIS` bigint(20) DEFAULT NULL,
 
 `ID_PROD_COMMANDE` int(11) DEFAULT NULL,
 
 `TYPE_IMPOSITION` varchar(32) DEFAULT NULL,
 
 PRIMARY KEY (`ID_PROD_ATTENTE_CONFIGURATION_IMPOSITION`),
 
 KEY `FK_ID_PROD_ITEM_PACIM` (`ID_PROD_ITEM`),
 
 KEY `FK_ID_STATUT_PACIM` (`ID_STATUT`),
 
 KEY `FK_ID_PROD_COMMANDE_PACI` (`ID_PROD_COMMANDE`)
 
) ENGINE=InnoDB AUTO_INCREMENT=30569 DEFAULT CHARSET=utf8
 

SHOW CREATE TABLE `PRODUCTION`.`PROD_COMMANDES`
 
 
 
CREATE TABLE `PROD_COMMANDES` (
 
 `ID_PROD_COMMANDE` int(11) NOT NULL AUTO_INCREMENT,
 
 `ID_COMMANDE` int(11) unsigned DEFAULT '0',
 
 `ID_COMMANDE_PARTENAIRE` varchar(50) DEFAULT NULL,
 
 `ID_CLIENT` int(11) NOT NULL DEFAULT '0',
 
 `ID_LANGUE` char(2) NOT NULL,
 
 `ID_PAYS` char(2) NOT NULL,
 
 `NOM` varchar(50) NOT NULL,
 
 `PRENOM` varchar(50) NOT NULL,
 
 `EMAIL` varchar(150) NOT NULL,
 
 `TELEPHONE` varchar(32) NOT NULL,
 
 `DIGICODE` varchar(32) NOT NULL,
 
 `ETAT` char(2) DEFAULT '0',
 
 `DATE_PRISE` datetime DEFAULT '0000-00-00 00:00:00',
 
 `DATE_PRISE_PROD` datetime DEFAULT '0000-00-00 00:00:00',
 
 `DATE_RELANCE` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 
 `DATE_ENVOI` datetime DEFAULT '0000-00-00 00:00:00',
 
 `PRESTATAIRE` varchar(16) DEFAULT NULL,
 
 `ID_SITE` int(3) unsigned DEFAULT '0',
 
 `CHANNEL_ID` int(11) DEFAULT NULL,
 
 `DATE_PASSAGE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 
 `DATE_ESTIMATION` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 
 `DATE_ESTIMATION_BABEL` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 
 `RECONSILIATION_TYPE` varchar(2) NOT NULL,
 
 `PRODUCTION_TEAM` int(11) NOT NULL,
 
 `DELAI_PRODUCTION_EN_HEURE` int(11) DEFAULT NULL,
 
 `BRANDNAME` varchar(20) NOT NULL DEFAULT '',
 
 PRIMARY KEY (`ID_PROD_COMMANDE`),
 
 KEY `ID_PROD_COMMANDE` (`ETAT`,`ID_PROD_COMMANDE`),
 
 KEY `ID_SITE` (`ID_SITE`,`ETAT`),
 
 KEY `ID_COMMANDE` (`ID_COMMANDE`)
 
) ENGINE=InnoDB AUTO_INCREMENT=12424082 DEFAULT CHARSET=utf8

Comment by Aurélien LEQUOY [ 2014-11-19 ]

again a new one :

Requête SQL :
ALTER TABLE `PROD_ATTENTE_CONFIGURATION_IMPOSITION` DROP FOREIGN KEY `PROD_ATTENTE_CONFIGURATION_IMPOSITION_ibfk_1`;
MySQL a répondu: Documentation

#2006 - MySQL server has gone away

Comment by Elena Stepanova [ 2014-11-19 ]

Please provide the cnf file(s) and also indicate your environment (system, architecture, where the binaries came from).

Comment by Elena Stepanova [ 2014-11-20 ]

It is likely to be the same bug as described in MDEV-6922.
MDEV-6922 is supposed to be fixed by the merge of InnoDB 5.6.21 into 10.0.15; although in fact the problem disappeared from the tree already, with this revision:

revno: 4464
 
revision-id: jplindst@mariadb.org-20141027145816-cuwsbq6d5yber97u
 
parent: jplindst@mariadb.org-20141027090317-e30s66to4mjj4jig
 
committer: Jan Lindström <jplindst@mariadb.org>
 
branch nick: 10.0
 
timestamp: Mon 2014-10-27 16:58:16 +0200
 
message:
 
  MDEV-6759: innodb valgrind failures
 
  
  Fix failure seen on dict_foreign_remove_partial().

If we have a repeatable test case from your crash, we can check that either the bugfix above, or the merge of InnoDB 5.6.21 fix it too. Otherwise, you can try 10.0.15 release which should be out any day now.

Comment by Aurélien LEQUOY [ 2014-11-20 ]

it's sound happear when we make a series of FK, I got the same problem when I droped a series of FK.

Comment by Elena Stepanova [ 2014-11-22 ]

In all likelihood, the problem was fixed MDEV-6922 / MDEV-6759. 10.0.15 release will be out soon; or, if you want to try it right now, I can give you a link to sources/binaries/packages. In any case, please comment to re-open if you still experience the problem after the upgrade.

Generated at Thu Feb 08 07:17:17 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.