0

I have seen a strange behavior in MySQL Innodb tables having below structure :

CREATE TABLE `bug` (
`Id` bigint(20) NOT NULL AUTO_INCREMENT,
`num` int(11) DEFAULT NULL,
`src` varchar(16) NOT NULL,
`destination` varchar(30) DEFAULT NULL,
`Text` varchar(2000) DEFAULT NULL,
`status` varchar(50) NOT NULL,
`response` varchar(50) NOT NULL DEFAULT 'PNDNG',
 PRIMARY KEY (`Id`),
KEY `idx_status_num` (`status`,`num`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ;

I have truncated this table :

truncate table bug;

After this , application started inserting records in it , and then i got duplicacy errors on primary key Id which was very strange for me. Moreover , i haven't seen any error in error log file related to this.

After this , i dropped and re-created this table :

drop table bug;

After this , application started inserting records in it and everything went fine .

So what was wrong ? Did Innodb table got corrupted ? If so , then how can I identify if table is corrupted ? As far as i know, it's MyISAM table which usually get corrupt.

Index on this is below :

       mysql> show index from bug\G;
      *************************** 1. row ***************************
      Table: bug
      Non_unique: 0
      Key_name: PRIMARY
      Seq_in_index: 1
      Column_name: Id
      Collation: A
      Cardinality: 0
      Sub_part: NULL
      Packed: NULL
      Null:
      Index_type: BTREE
      Comment:
      Index_comment:
 *************************** 2. row ***************************
      Table: bug
      Non_unique: 1
      Key_name: idx_status_num
      Seq_in_index: 1
      Column_name: status
      Collation: A
      Cardinality: 0
      Sub_part: NULL
      Packed: NULL
      Null:
      Index_type: BTREE
      Comment:
      Index_comment:
     *************************** 3. row ***************************
      Table: bug
      Non_unique: 1
      Key_name: idx_status_num
      Seq_in_index: 2
      Column_name: num
      Collation: A
      Cardinality: 0
      Sub_part: NULL
      Packed: NULL
      Null: YES
     Index_type: BTREE
     Comment:
   Index_comment:
   3 rows in set (0.00 sec)
10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.