版本 5.0.45
os linux当前应用在java代码 执行一个带外键的create表语句。发现有时候成功,有时候不成功 ,总是抱错误(所外键创建不成功)
(程序需要每次启动创建这个表 退出程序drop这个表)
现在我把这个建表语句直接执行,他是有时候成功,有时候不成功。
后来把这个语句搞成2部分,一部分是建立表语句,另外一部分是sql的创建外键语句(3条)
结果第1部分是成功的 ,但是第2部分总是有时候成功,有时候不成功
CREATE TABLE `Event
(
`id` bigint(20) unsigned NOT NULL,
`name` varchar(255) default NULL,
`disciplineId` bigint(20) unsigned NOT NULL,
`locationId` bigint(20) unsigned default NULL,
`tournamentId` bigint(20) unsigned default NULL,
PRIMARY KEY (`id`),
KEY `idx_Event_locationId_0` (`locationId`),
KEY `idx_Event_disciplineId_0` (`disciplineId`),
KEY `idx_Event_tournamentId_0` (`tournamentId`),
KEY `idx
_Event_startDate_0` (`startDate`),
CONSTRAINT `fk_Ev_disciplineId_0` FOREIGN KEY (`disciplineId`) REFERENCES `a` (`id`),
CONSTRAINT `fk_Ev_locationId_0` FOREIGN KEY (`locationId`) REFERENCES `a` (`id`),
CONSTRAINT `fk_Ev_tournamentId_0` FOREIGN KEY (`tournamentId`) REFERENCES `Event` (`id`) ON DELETE CASCADE
) eNGINE=InnoDB DEFAULT CHARSET=utf8;
我将他搞成CREATE TABLE `Event
(
`id` bigint(20) unsigned NOT NULL,
`name` varchar(255) default NULL,
`disciplineId` bigint(20) unsigned NOT NULL,
`locationId` bigint(20) unsigned default NULL,
`tournamentId` bigint(20) unsigned default NULL,
PRIMARY KEY (`id`),
KEY `idx_Event_locationId_0` (`locationId`),
KEY `idx_Event_disciplineId_0` (`disciplineId`),
KEY `idx_Event_tournamentId_0` (`tournamentId`),
KEY `idx_Event_startDate_0` (`startDate)
) eNGINE=InnoDB DEFAULT CHARSET=utf8;
alter table Event_02 ADD constraint fk_Ev_disciplineId_0 FOREIGN KEY(disciplineId) REFERENCES Discipline(id);
alter table Event_02 ADD constraint fk_Ev_locationId_0 FOREIGN KEY(locationId) REFERENCES Location(id);
alter table Event_02 ADD CONSTRAINT fk_Ev_tournamentId_0 FOREIGN KEY(tournamentId) REFERENCES Event(id) ON DELETE CASCADE;结果总是有时候成功,有时候不成功?
是否外键不稳定(mysql5.0。45)? 如何解决?
os linux当前应用在java代码 执行一个带外键的create表语句。发现有时候成功,有时候不成功 ,总是抱错误(所外键创建不成功)
(程序需要每次启动创建这个表 退出程序drop这个表)
现在我把这个建表语句直接执行,他是有时候成功,有时候不成功。
后来把这个语句搞成2部分,一部分是建立表语句,另外一部分是sql的创建外键语句(3条)
结果第1部分是成功的 ,但是第2部分总是有时候成功,有时候不成功
CREATE TABLE `Event
(
`id` bigint(20) unsigned NOT NULL,
`name` varchar(255) default NULL,
`disciplineId` bigint(20) unsigned NOT NULL,
`locationId` bigint(20) unsigned default NULL,
`tournamentId` bigint(20) unsigned default NULL,
PRIMARY KEY (`id`),
KEY `idx_Event_locationId_0` (`locationId`),
KEY `idx_Event_disciplineId_0` (`disciplineId`),
KEY `idx_Event_tournamentId_0` (`tournamentId`),
KEY `idx
_Event_startDate_0` (`startDate`),
CONSTRAINT `fk_Ev_disciplineId_0` FOREIGN KEY (`disciplineId`) REFERENCES `a` (`id`),
CONSTRAINT `fk_Ev_locationId_0` FOREIGN KEY (`locationId`) REFERENCES `a` (`id`),
CONSTRAINT `fk_Ev_tournamentId_0` FOREIGN KEY (`tournamentId`) REFERENCES `Event` (`id`) ON DELETE CASCADE
) eNGINE=InnoDB DEFAULT CHARSET=utf8;
我将他搞成CREATE TABLE `Event
(
`id` bigint(20) unsigned NOT NULL,
`name` varchar(255) default NULL,
`disciplineId` bigint(20) unsigned NOT NULL,
`locationId` bigint(20) unsigned default NULL,
`tournamentId` bigint(20) unsigned default NULL,
PRIMARY KEY (`id`),
KEY `idx_Event_locationId_0` (`locationId`),
KEY `idx_Event_disciplineId_0` (`disciplineId`),
KEY `idx_Event_tournamentId_0` (`tournamentId`),
KEY `idx_Event_startDate_0` (`startDate)
) eNGINE=InnoDB DEFAULT CHARSET=utf8;
alter table Event_02 ADD constraint fk_Ev_disciplineId_0 FOREIGN KEY(disciplineId) REFERENCES Discipline(id);
alter table Event_02 ADD constraint fk_Ev_locationId_0 FOREIGN KEY(locationId) REFERENCES Location(id);
alter table Event_02 ADD CONSTRAINT fk_Ev_tournamentId_0 FOREIGN KEY(tournamentId) REFERENCES Event(id) ON DELETE CASCADE;结果总是有时候成功,有时候不成功?
是否外键不稳定(mysql5.0。45)? 如何解决?
(
`id` bigint(20) unsigned NOT NULL,
`name` varchar(255) default NULL,
`disciplineId` bigint(20) unsigned NOT NULL,
`locationId` bigint(20) unsigned default NULL,
`tournamentId` bigint(20) unsigned default NULL,
PRIMARY KEY (`id`),
KEY `idx_Event_locationId_0` (`locationId`),
KEY `idx_Event_disciplineId_0` (`disciplineId`),
KEY `idx_Event_tournamentId_0` (`tournamentId`),
KEY `idx_Event_startDate_0` (`startDate)
) eNGINE=InnoDB DEFAULT CHARSET=utf8;
alter table Event_02 ADD constraint fk_Ev_disciplineId_0 FOREIGN KEY(disciplineId) REFERENCES Discipline(id);
alter table Event_02 ADD constraint fk_Ev_locationId_0 FOREIGN KEY(locationId) REFERENCES Location(id);
alter table Event_02 ADD CONSTRAINT fk_Ev_tournamentId_0 FOREIGN KEY(tournamentId) REFERENCES Event(id) ON DELETE CASCADE; 另外,5.0的外键没有BUG或其它来源报告外建创建不稳定。
建议当你出错的时候,检查一下mysql的错误日志,把错误信息提供一下,这样比较容易分析问题所在。
还给我看看 2个环境这个代码跑的结果。(2个环境确实这个表 被 java程序跑成功
非常困惑 (这个表基本每天drop 每天新建的那种) )
而且开发人他的java程序跑1年多都没有发生问题的 哦。
但是我在本机机器(版本都一样 )无论如何编译都不过的 。难道java里面创建mysql可以强制执行 跳过错误 ???
建议你查看一下错误日志,否则大家在这儿猜也猜不出什么。