Two Identical Values on Primary Key Column

--0-761965968-1299074671=:53659
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Hi all,

I have just experienced a strange problem with mysql production database. T=
he table faqsessions have a primary key on column `Code` and the above sele=
ct return 2 rows!

mysql>
mysql>
mysql> show create table faqsessions;
+-------------+--------------------------------------------- ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
--------------------------------+
| Table       | Create Table          =A0=
                         =A0=
                         =A0=
                         =A0=
                         =A0=
                         =A0=
                         =A0=
                         =A0=
                         =A0=
                =A0 |
+-------------+--------------------------------------------- ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
--------------------------------+
| faqsessions | CREATE TABLE `faqsessions` (
=A0 `Code` int(11) unsigned NOT NULL auto_increment,
=A0 `sid` int(11) NOT NULL,
=A0 `ip` text NOT NULL,
=A0 `time` int(11) NOT NULL,
=A0 PRIMARY KEY=A0 (`Code`)
) ENGINE=3DInnoDB AUTO_INCREMENT=3D1567573 DEFAULT CHARSET=3Dlatin1 |
+-------------+--------------------------------------------- ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
--------------------------------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> select * from faqsessions where time in (1268650281, 1268650260);
+--------+--------+--------------+------------+
| Code   | sid  =A0 | ip           | time  =
     |
+--------+--------+--------------+------------+
| 611179 | 312713 | 66.249.68.87 | 1268650281 |
| 611179 | 312713 | 66.249.68.89 | 1268650260 |
+--------+--------+--------------+------------+
2 rows in set (1.49 sec)

mysql>
mysql>
mysql> select * from faqsessions where Code =3D 611179;
+--------+--------+--------------+------------+
| Code   | sid  =A0 | ip           | time  =
     |
+--------+--------+--------------+------------+
| 611179 | 312713 | 66.249.68.87 | 1268650281 |
+--------+--------+--------------+------------+
1 row in set (0.00 sec)

Any idea?

Rodrigo Ferreira
CMDBA, CMDEV

=0A
--0-761965968-1299074671=:53659--

Re: Two Identical Values on Primary Key Column

--=_346b0516-8e9e-45da-961e-e7160cb3ddb3
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

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 ?

----- Original Message -----

> From: "Rodrigo Ferreira"
> To: mysql@lists.mysql.com
> 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>
> 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

--=_346b0516-8e9e-45da-961e-e7160cb3ddb3--

Re: Two Identical Values on Primary Key Column

--0-1451338623-1299080684=:3849
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

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                =A0=
                         =A0=
               |
+-------+------+-------------------------------------------- ---------------=
------+
| Note=A0 | 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                =A0=
                         =A0=
               |
+-------+------+-------------------------------------------- ---------------=
------+
| Note=A0 | 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 wrote:

From: Johan De Meersman
Subject: Re: Two Identical Values on Primary Key Column
To: "Rodrigo Ferreira"
Cc: mysql@lists.mysql.com
Date: Wednesday, March 2, 2011, 11:21 AM

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

From: "Rodrigo Ferreira"
To: mysql@lists.mysql.com
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. T=
he table faqsessions have a primary key on column `Code` and the above sele=
ct return 2 rows!

mysql>
mysql>
mysql> show create table faqsessions;
+-------------+--------------------------------------------- ---------------=
-------------------------------------------=0A ----------------------------=
------------------------------------------------------------ ---------------=
------------------------------------+

+-------------+--------------------------------------------- ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
--------------------------------+

=A0 `Code` int(11) unsigned NOT NULL auto_increment,
=A0 `sid` int(11) NOT NULL,
=A0 `ip` text NOT NULL,
=A0 `time` int(11) NOT NULL,
=A0 PRIMARY KEY=A0 (`Code`)
) ENGINE=3DInnoDB AUTO_INCREMENT=3D1567573 DEFAULT CHARSET=3Dlatin1 |
+-------------+--------------------------------------------- ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
--------------------------------+
1 row in set (0.00 sec)

mysql>
mysql&g=0A 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 =3D 611179;
+--------+--------+--------------+------------+

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

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

Any idea?

Rodrigo Ferreira
CMDBA, CMDEV



--=20
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel
=0A
--0-1451338623-1299080684=:3849--