Details
-
Bug
-
Resolution: Won't Fix
-
Major
-
None
-
4.5.3
-
MySQL Server version: 5.5.31-0ubuntu0.12.04.1 (Ubuntu)
XWiki-4-5-3, Tomcat7
Description
The back-up script generated by MySQL is not restored in a fresh MySQL database. All tables are created but one (xwikidates).
Steps to reproduce the issue:
mysqldump --add-drop-database xwiki > bup.sql
(move to another clone machine)
mysql < bup.sql
Error message:
ERROR 1005 (HY000) at line 384: Can't create table 'xwiki.xwikidates' (errno: 150) MySQL log (SHOW ENGINE INNODB STATUS): ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 130501 21:06:57 Error in foreign key constraint of table xwiki/xwikidates: FOREIGN KEY (`XWS_ID`, `XWS_NAME`) REFERENCES `xwikiproperties` (`XWP_ID`, `XWP_NAME`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin: Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html for correct foreign key definition.
Extract from the SQL file (the constraint is the issue, and removing it does succeed in creating the table):
CREATE TABLE `xwikidates` ( `XWS_ID` bigint(20) NOT NULL, `XWS_NAME` varchar(255) COLLATE utf8_bin NOT NULL, `XWS_VALUE` datetime DEFAULT NULL, PRIMARY KEY (`XWS_ID`,`XWS_NAME`), KEY `FKDEAEAB5D3433FD87` (`XWS_ID`,`XWS_NAME`), KEY `XWDATE_NAME` (`XWS_NAME`), KEY `XWDATE_VALUE` (`XWS_VALUE`), CONSTRAINT `FKDEAEAB5D3433FD87` FOREIGN KEY (`XWS_ID`, `XWS_NAME`) REFERENCES `xwikiproperties` (`XWP_ID`, `XWP_NAME`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; /*!40101 SET character_set_client = @saved_cs_client */;