Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
10.1.11
-
The operating system is Linux 2.6.32-573.8.1.el6.x86_64 x86_64.
Description
One of my tables is losing the primary key every time I make an update on that particular table.
Describe zizi_card_household
gives me this result after the update.
+-------------------+--------------+------+-----+---------+----------------+
|
| Field | Type | Null | Key | Default | Extra |
|
+-------------------+--------------+------+-----+---------+----------------+
|
| id | int(11) | NO | | NULL | auto_increment |
|
| householdnumber | varchar(45) | NO | | | |
|
| cardnumber | varchar(45) | YES | MUL | NULL | |
|
| startdate | datetime | YES | | NULL | |
|
| enddate | datetime | YES | | NULL | |
|
| assignedby | int(11) | YES | | NULL | |
|
| assigneddate | datetime | YES | | NULL | |
|
Where it should normally be
+-------------------+--------------+------+-----+---------+----------------+
|
| Field | Type | Null | Key | Default | Extra |
|
+-------------------+--------------+------+-----+---------+----------------+
|
| id | int(11) | NO | PRI | NULL | auto_increment |
|
| householdnumber | varchar(45) | NO | MUL | | |
|
| cardnumber | varchar(45) | YES | MUL | NULL | |
|
| startdate | datetime | YES | | NULL | |
|
| enddate | datetime | YES | | NULL | |
|
| assignedby | int(11) | YES | | NULL | |
|
SHOW CREATE TABLE will give this statement which shows a primary key was defined. It's possible that entry is updated whenever the table is altered so it shows the table as it was last altered or created.
CREATE TABLE `zizi_card_household` (
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
`householdnumber` varchar(45) NOT NULL DEFAULT '',
|
`cardnumber` varchar(45) DEFAULT NULL,
|
`startdate` datetime DEFAULT NULL,
|
`enddate` datetime DEFAULT NULL,
|
`assignedby` int(11) DEFAULT NULL,
|
`assigneddate` datetime DEFAULT NULL,
|
`deassignedby` int(11) DEFAULT NULL,
|
.
|
.
|
.
|
.
|
`modifiedby` int(11) DEFAULT NULL,
|
`reprintstatus` tinyint(4) DEFAULT NULL,
|
`printeddate` datetime DEFAULT NULL,
|
`printedby` int(11) DEFAULT NULL,
|
PRIMARY KEY (`id`),
|
KEY `idx_householdnumber` (`householdnumber`),
|
KEY `idx_cardnumber` (`cardnumber`),
|
KEY `idx_deassignedby` (`deassignedby`),
|
.
|
.
|
.
|
.
|
KEY `idx_reprintstatus` (`reprintstatus`)
|
) ENGINE=InnoDB AUTO_INCREMENT=860137 DEFAULT CHARSET=latin1
|
Attempting to add a primary key
ALTER TABLE `labour`.`zizi_card_household`
|
CHANGE COLUMN `id` `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
|
at this point gives me
ERROR 1068 (42000): Multiple primary key defined
|
Repair table
repair table zizi_card_household;
|
Gives me this but it does solve the problem. The primary key returns but will get ruined the next time the table is updated.
+----------------------------+--------+----------+---------------------------------------------------------+
|
| Table | Op | Msg_type | Msg_text |
|
+----------------------------+--------+----------+---------------------------------------------------------+
|
| labour.zizi_card_household | repair | note | The storage engine for the table doesn't support repair |
|
+----------------------------+--------+----------+---------------------------------------------------------+
|
The table and other tables have a trigger that updates a summary table. It deletes related rows and reconstructs them. It does something like
FOR EACH ROW
|
 |
begin
|
delete from zizi_card_summary where householdnumber=new.householdnumber;
|
insert into zizi_card_summary(
|
cardnumber,cardhouseholdid,householdnumber,startdate,enddate,assignedby
|
.
|
.
|
.
|
)
|
select ch.cardnumber,ch.id,b.householdnumber,
|
ch.startdate,ch.enddate,ch.assignedby,
|
.
|
.
|
.
|
.
|
where b.householdnumber=new.householdnumber and b.beneficiary_type=0;
|
end
|
All other tables work just fine