List:General Discussion« Previous MessageNext Message »
From:Rodrigo Ferreira Date:March 2 2011 3:44pm
Subject:Re: Two Identical Values on Primary Key Column
View as plain text  
Hi Johan,

It seems InnoDB doesn't support disable/enable keys.


mysql> alter table faqsessions enable keys;
Query OK, 0 rows affected, 1 warning (0.14 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------------------+
| Level | Code |
Message                                                        
|
+-------+------+-----------------------------------------------------------------+
| Note  | 1031 | Table storage engine for 'faqsessions' doesn't have this option |
+-------+------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table faqsessions disable keys;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------------------+
| Level | Code |
Message                                                        
|
+-------+------+-----------------------------------------------------------------+
| Note  | 1031 | Table storage engine for 'faqsessions' doesn't have this option |
+-------+------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

--- On Wed, 3/2/11, Johan De Meersman <vegivamp@stripped> wrote:

From: Johan De Meersman <vegivamp@stripped>
Subject: Re: Two Identical Values on Primary Key Column
To: "Rodrigo Ferreira" <rodrigof_silva@stripped>
Cc: mysql@stripped
Date: Wednesday, March 2, 2011, 11:21 AM

#yiv704254679 p {margin:0;}Is it possible that someone did an alter table disable keys at
some point, maybe for a bulk load, and forgot to re-enable them ?

From: "Rodrigo Ferreira" <rodrigof_silva@stripped>
To: mysql@stripped
Sent: Wednesday, 2 March, 2011 3:04:31 PM
Subject: Two Identical Values on Primary Key Column

Hi all,

I have just experienced a strange problem with mysql production database. The table
faqsessions have a primary key on column `Code` and the above select return 2 rows!

mysql>
mysql>
mysql> show create table faqsessions;
+-------------+-------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------------------+

+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

  `Code` int(11) unsigned NOT NULL auto_increment,
  `sid` int(11) NOT NULL,
  `ip` text NOT NULL,
  `time` int(11) NOT NULL,
  PRIMARY KEY  (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=1567573 DEFAULT CHARSET=latin1 |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql&g
 t;
mysql> select * from faqsessions where time in (1268650281, 1268650260);
+--------+--------+--------------+------------+

+--------+--------+--------------+------------+

+--------+--------+--------------+------------+
2 rows in set (1.49 sec)

mysql>
mysql>
mysql> select * from faqsessions where Code = 611179;
+--------+--------+--------------+------------+

+--------+--------+--------------+------------+

+--------+--------+--------------+------------+
1 row in set (0.00 sec)

Any idea?

Rodrigo Ferreira
CMDBA, CMDEV



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel



      
Thread
Two Identical Values on Primary Key ColumnRodrigo Ferreira2 Mar
  • Re: Two Identical Values on Primary Key ColumnJohan De Meersman2 Mar
    • Re: Two Identical Values on Primary Key ColumnRodrigo Ferreira2 Mar