| Bug #14007 | Updating primary key of NDB table fails | ||
|---|---|---|---|
| Submitted: | 13 Oct 2005 15:09 | Modified: | 23 Feb 2006 9:06 |
| Reporter: | Scott Tully | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S2 (Serious) |
| Version: | 4.1.14 | OS: | Linux (Linux) |
| Assigned to: | Pekka Nousiainen | CPU Architecture: | Any |
[13 Oct 2005 21:59]
Hartmut Holzgraefe
your "how to repeat" script does not work (missing quotes in the insert statements, failing REPLACE statements, no WHERE conditions in the REPLACE statements), please add a working script showing the problem ...
[13 Oct 2005 22:18]
Scott Tully
It was missing a quote around a datetime value... but REPLACE does not use a WHERE clause, it keys on the primary and/or unique keys. http://dev.mysql.com/doc/refman/4.1/en/replace.html ============================================================= -- -------------------------------------------------------- -- -- Table structure for table `members` -- CREATE TABLE `members` ( `master_id` int(11) NOT NULL auto_increment, `zone_id` int(11) NOT NULL default '0', `user_id` varchar(128) NOT NULL default '', `mac` varchar(32) NOT NULL default '', `pass` varchar(32) NOT NULL default '', `name` varchar(255) default NULL, `url` varchar(255) default NULL, `description` text, `created` datetime NOT NULL default '0000-00-00 00:00:00', `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `account_status` int(3) NOT NULL default '0', `validation_token` varchar(32) NOT NULL default '', `status` enum('A','P') default NULL, `temp_time` int(10) NOT NULL default '0', `temp_trans` int(10) NOT NULL default '0', PRIMARY KEY (`master_id`,`user_id`), KEY `zone_id` (`zone_id`), KEY `user_id` (`user_id`), KEY `status` (`status`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1; -- -- Dumping data for table `members` -- INSERT INTO `members` (`master_id`, `zone_id`, `user_id`, `mac`, `pass`, `name`, `url`, `description`, `created`, `modified`, `account_status`, `validation_token`, `status`, `temp_time`, `temp_trans`) VALUES (199, '160', 'john@john.com', '00:0E:9B:D6:DD:F4', 'c92f72de28de64b8c169c5df471d8789', 'John', NULL, NULL, '2005-09-01 01:44:23', '2005-09-01 07:01:02', '1', 'ad6bf82438a49a09b3870ec10b1e3638', NULL, 0, '0'); -- -------------------------------------------------------- -- -- Table structure for table `zone_user` -- CREATE TABLE `zone_user` ( `user_id` varchar(128) NOT NULL default '', `zone_id` int(11) NOT NULL default '0', `master_id` int(11) NOT NULL default '0', `class` enum('5','4','3','2') default NULL, `expire` enum('Y','N','O') NOT NULL default 'N', `clocked` enum('N','Y') NOT NULL default 'N', `length` bigint(21) unsigned default NULL, `bandwidth` bigint(21) unsigned default NULL, `ambiguous` enum('N','Y') NOT NULL default 'N', `dwm_band` enum('%j','%u','%c') default NULL, `dwm_length` enum('%j','%u','%c','%Y') default NULL, `dwm_expire` enum('%j','%u','%c') default NULL, `expire_start` date NOT NULL default '0000-00-00', `expire_stop` date NOT NULL default '0000-00-00', PRIMARY KEY (`user_id`,`zone_id`), KEY `class` (`class`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1; -- -- Dumping data for table `zone_user` -- INSERT INTO `zone_user` (`user_id`, `zone_id`, `master_id`, `class`, `expire`, `clocked`, `length`, `bandwidth`, `ambiguous`, `dwm_band`, `dwm_length`, `dwm_expire`, `expire_start`, `expire_stop`) VALUES ('john@john.com', '160', '199', '5', 'N', N, NULL, NULL, '', NULL, NULL, NULL, '0000-00-00', '0000-00-00'); -- -------------------------------------------------------- REPLACE INTO members SET user_id = 'John@john.com', pass = MD5('sfsfsfsf'), account_status = '1', status = 'P', created = CONVERT_TZ(NOW(),'SYSTEM','US/Mountain'), master_id = '199', zone_id = '160'; REPLACE INTO zone_user SET class = '4', master_id = '199', zone_id = '160', expire_start = DATE(CONVERT_TZ(NOW(),'SYSTEM','US/Mountain')), expire_stop = DATE(CONVERT_TZ(NOW(),'SYSTEM','US/Mountain')), expire = 'Y', user_id = 'John@john.com';
[14 Oct 2005 9:51]
Hartmut Holzgraefe
verified after the following modifications:
- added missing quotes around one 'N' in second insert statement
- changed date expressions involving time zone modifications
to just NOW() in the REPLACE statements
-- --------------------------------------------------------
--
-- Table structure for table `members`
--
CREATE TABLE `members` (
`master_id` int(11) NOT NULL auto_increment,
`zone_id` int(11) NOT NULL default '0',
`user_id` varchar(128) NOT NULL default '',
`mac` varchar(32) NOT NULL default '',
`pass` varchar(32) NOT NULL default '',
`name` varchar(255) default NULL,
`url` varchar(255) default NULL,
`description` text,
`created` datetime NOT NULL default '0000-00-00 00:00:00',
`modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
`account_status` int(3) NOT NULL default '0',
`validation_token` varchar(32) NOT NULL default '',
`status` enum('A','P') default NULL,
`temp_time` int(10) NOT NULL default '0',
`temp_trans` int(10) NOT NULL default '0',
PRIMARY KEY (`master_id`,`user_id`),
KEY `zone_id` (`zone_id`),
KEY `user_id` (`user_id`),
KEY `status` (`status`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
--
-- Dumping data for table `members`
--
INSERT INTO `members` (`master_id`, `zone_id`, `user_id`, `mac`, `pass`,
`name`,
`url`, `description`, `created`, `modified`, `account_status`,
`validation_token`, `status`, `temp_time`, `temp_trans`) VALUES (199, '160',
'john@john.com', '00:0E:9B:D6:DD:F4', 'c92f72de28de64b8c169c5df471d8789',
'John', NULL, NULL, '2005-09-01 01:44:23', '2005-09-01 07:01:02', '1',
'ad6bf82438a49a09b3870ec10b1e3638', NULL, 0, '0');
-- --------------------------------------------------------
--
-- Table structure for table `zone_user`
--
CREATE TABLE `zone_user` (
`user_id` varchar(128) NOT NULL default '',
`zone_id` int(11) NOT NULL default '0',
`master_id` int(11) NOT NULL default '0',
`class` enum('5','4','3','2') default NULL,
`expire` enum('Y','N','O') NOT NULL default 'N',
`clocked` enum('N','Y') NOT NULL default 'N',
`length` bigint(21) unsigned default NULL,
`bandwidth` bigint(21) unsigned default NULL,
`ambiguous` enum('N','Y') NOT NULL default 'N',
`dwm_band` enum('%j','%u','%c') default NULL,
`dwm_length` enum('%j','%u','%c','%Y') default NULL,
`dwm_expire` enum('%j','%u','%c') default NULL,
`expire_start` date NOT NULL default '0000-00-00',
`expire_stop` date NOT NULL default '0000-00-00',
PRIMARY KEY (`user_id`,`zone_id`),
KEY `class` (`class`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
--
-- Dumping data for table `zone_user`
--
INSERT INTO `zone_user` (`user_id`, `zone_id`, `master_id`, `class`, `expire`,
`clocked`, `length`, `bandwidth`, `ambiguous`, `dwm_band`, `dwm_length`,
`dwm_expire`, `expire_start`, `expire_stop`) VALUES ('john@john.com', '160',
'199', '5', 'N', 'N', NULL, NULL, '', NULL, NULL, NULL, '0000-00-00',
'0000-00-00');
-- --------------------------------------------------------
REPLACE INTO members SET
user_id = 'John@john.com',
pass = MD5('sfsfsfsf'),
account_status = '1',
status = 'P',
created = NOW(),
master_id = '199',
zone_id = '160';
REPLACE INTO zone_user SET
class = '4',
master_id = '199',
zone_id = '160',
expire_start = NOW(),
expire_stop =NOW(),
expire = 'Y',
user_id = 'John@john.com';
[15 Nov 2005 15:25]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/32273
[16 Nov 2005 10:52]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/32310
[16 Nov 2005 12:26]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/32313
[20 Nov 2005 10:15]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/32440
[14 Feb 2006 8:32]
Jon Stephens
Need to know what version(s) fix was pushed to, also need description of bug and resolution. Thanks.
[20 Feb 2006 14:45]
Pekka Nousiainen
4.1.16
5.0.17
5.1.3
Using 'replace' to update primary key failed with error 897.
"Update pk" means to update it to another equivalent value.
This works now. Simplest example:
create table t1 (
a char(10) primary key
) engine=ndbcluster default charset=latin1;
insert into t1 values ('aaabb');
replace into t1 set a = 'AAABB';
replace into t1 set a = 'aAaBb';
replace into t1 set a = 'aaabb';
[23 Feb 2006 9:06]
Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Additional info:
Changed description as this doesn't seem to require use of the NDB internal API, the bug manifests at the SQL level.
Documented fix in 4.1.16/5.0.17/5.1.3 changelogs.
Closed.
(Thanks, Pekka!)
Description: I think REPLACE is failing on the composite primary key of user_id and master_id when there is a CaSE difference. Since John != john, the ndb engine is trying to create a new record, but it can't becuase John == john when CaSE does not matter. There must be a bug in the cluster engine with CasE. [Thu Oct 13 10:13:53 EDT 2005]WARNING: ExecSqlUpdate: REPLACE INTO members SET user_id = 'John@john.com', pass = MD5('sfsfsfsfs'), account_status = '1', status = 'P', created = CONVERT_TZ(NOW(),'SYSTEM','US/Mountain'), master_id = '199', zone_id = '160' [Thu Oct 13 10:13:53 EDT 2005]ERROR: ExecSqlUpdate: Got error 4350 'Transaction already aborted' from ndbcluster [Thu Oct 13 10:13:53 EDT 2005]WARNING: ExecSqlUpdate: REPLACE INTO zone_user SET class = '4', master_id = '199', zone_id = '160', expire_start = DATE(CONVERT_TZ(NOW(),'SYSTEM','US/Mountain')), expire_stop = DATE(CONVERT_TZ(NOW(),'SYSTEM','US/Mountain')), expire = 'Y', user_id = 'John@john.com' [Thu Oct 13 10:13:53 EDT 2005]ERROR: ExecSqlUpdate: Got error 897 'Update attempt of primary key via ndbcluster internal api (if this occurs via the MySQL server it is a bug, please report)' from ndbcluster How to repeat: -- -------------------------------------------------------- -- -- Table structure for table `members` -- CREATE TABLE `members` ( `master_id` int(11) NOT NULL auto_increment, `zone_id` int(11) NOT NULL default '0', `user_id` varchar(128) NOT NULL default '', `mac` varchar(32) NOT NULL default '', `pass` varchar(32) NOT NULL default '', `name` varchar(255) default NULL, `url` varchar(255) default NULL, `description` text, `created` datetime NOT NULL default '0000-00-00 00:00:00', `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `account_status` int(3) NOT NULL default '0', `validation_token` varchar(32) NOT NULL default '', `status` enum('A','P') default NULL, `temp_time` int(10) NOT NULL default '0', `temp_trans` int(10) NOT NULL default '0', PRIMARY KEY (`master_id`,`user_id`), KEY `zone_id` (`zone_id`), KEY `user_id` (`user_id`), KEY `status` (`status`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1; -- -- Dumping data for table `members` -- INSERT INTO `members` (`master_id`, `zone_id`, `user_id`, `mac`, `pass`, `name`, `url`, `description`, `created`, `modified`, `account_status`, `validation_token`, `status`, `temp_time`, `temp_trans`) VALUES (199, '160', 'john@john.com', '00:0E:9B:D6:DD:F4', 'c92f72de28de64b8c169c5df471d8789', 'John', NULL, NULL, '2005-09-01 01:44:23', 2005-09-01 07:01:02, '1', 'ad6bf82438a49a09b3870ec10b1e3638', NULL, 0, '0'); -- -------------------------------------------------------- -- -- Table structure for table `zone_user` -- CREATE TABLE `zone_user` ( `user_id` varchar(128) NOT NULL default '', `zone_id` int(11) NOT NULL default '0', `master_id` int(11) NOT NULL default '0', `class` enum('5','4','3','2') default NULL, `expire` enum('Y','N','O') NOT NULL default 'N', `clocked` enum('N','Y') NOT NULL default 'N', `length` bigint(21) unsigned default NULL, `bandwidth` bigint(21) unsigned default NULL, `ambiguous` enum('N','Y') NOT NULL default 'N', `dwm_band` enum('%j','%u','%c') default NULL, `dwm_length` enum('%j','%u','%c','%Y') default NULL, `dwm_expire` enum('%j','%u','%c') default NULL, `expire_start` date NOT NULL default '0000-00-00', `expire_stop` date NOT NULL default '0000-00-00', PRIMARY KEY (`user_id`,`zone_id`), KEY `class` (`class`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1; -- -- Dumping data for table `zone_user` -- INSERT INTO `zone_user` (`user_id`, `zone_id`, `master_id`, `class`, `expire`, `clocked`, `length`, `bandwidth`, `ambiguous`, `dwm_band`, `dwm_length`, `dwm_expire`, `expire_start`, `expire_stop`) VALUES ('john@john.com', '160', '199', '5', 'N', N, NULL, NULL, '', NULL, NULL, NULL, '0000-00-00', '0000-00-00'); -- -------------------------------------------------------- REPLACE INTO members SET user_id = 'John@john.com', pass = MD5('sfsfsfsf'), account_status = '1', status = 'P', created = CONVERT_TZ(NOW(),'SYSTEM','US/Mountain'), master_id = '199', zone_id = '160'; REPLACE INTO zone_user SET class = '4', master_id = '199', zone_id = '160', expire_start = DATE(CONVERT_TZ(NOW(),'SYSTEM','US/Mountain')), expire_stop = DATE(CONVERT_TZ(NOW(),'SYSTEM','US/Mountain')), expire = 'Y', user_id = 'John@john.com';