[MDEV-9558] `describe table` broken Created: 2016-02-15  Updated: 2016-03-15  Resolved: 2016-03-15

Status: Closed
Project: MariaDB Server
Component/s: Admin statements, Data Manipulation - Update
Affects Version/s: 10.1.11
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Timothy Wachiuri Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: need_feedback
Environment:

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

 Comments   
Comment by Elena Stepanova [ 2016-02-15 ]

wachiuri,

Are there any warnings/errors in the error log?

Are you able to experiment with the schema?
For example, can you reproduce it with a new (but identical) table - 

CREATE TABLE tst LIKE zizi_card_household;
 
INSERT INTO tst SELECT * FROM zizi_card_household;
 
# also create a trigger of the same structure on the new table

Then run updates on the new table and see if they cause the same effect. If you observe the same problem, please dump the schema and upload the dump to our ftp.askmonty.org/private if possible.

If you can't reproduce the problem on the new table, but can share the data, please upload the binary datadir (not the dump, but files itself) to the same location.

In either case please also paste or attach your cnf file(s) and specify which packages you are using.

Comment by Elena Stepanova [ 2016-03-15 ]

Please comment to re-open if you have more information on the issue.

Generated at Thu Feb 08 07:35:35 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.