mysql - mariadb 在更新时丢失主键

Question

每次我对某个特定表进行更新时，我的一个表都会丢失主键。

Describe zizi_card_household

更新后给我这个结果。

+-------------------+--------------+------+-----+---------+----------------+
| 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    |                |

通常应该在的位置

+-------------------+--------------+------+-----+---------+----------------+
| 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

将给出此语句，显示已定义主键。每当表发生更改时，条目都可能会更新，因此它会显示上次更改或创建的表。

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 

尝试添加主键

ALTER TABLE `labour`.`zizi_card_household` 
CHANGE COLUMN `id` `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY

此时给了我

ERROR 1068 (42000): Multiple primary key defined

维修表

repair table zizi_card_household;

给了我这个，但它确实解决了问题。主键返回，但下次更新表时会被破坏。

+----------------------------+--------+----------+---------------------------------------------------------+
| Table                      | Op     | Msg_type | Msg_text                                                |
+----------------------------+--------+----------+---------------------------------------------------------+
| labour.zizi_card_household | repair | note     | The storage engine for the table doesn't support repair |
+----------------------------+--------+----------+---------------------------------------------------------+

该表和其他表都有一个更新汇总表的触发器。它删除相关行并重建它们。它的作用类似于

 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

所有其他表都工作正常。谢谢。已经玩这个一周了。该应用程序是 Zend 框架 1.12。

数据库版本是10.1.11-MariaDB-log。操作系统是Linux 2.6.32-573.8.1.el6.x86_64 x86_64。

Answer 1

我可以确认 Timothy 所描述的错误和解决方法:删除触发器在记录更新后保留主键。错误解决进度为 https://jira.mariadb.org/browse/MDEV-9558