moose moose - 2 months ago 12
MySQL Question

Unexpected cardinality violation (Subquery returns more than 1 row)

Consider the following example:

A MySQL table (

table
) with structure
id
(Primary Key),
value
,
unique_id
exists.

Whenever a user clicks on
Button A
, PHP executes the following query:

SELECT `id` FROM `table` WHERE `unique_id` = x; //where x is a `unique_id`


If the query returns nothing (I.E.
x
does not yet exist in the
unique_id
column), the new row is inserted into
table
(with
x
in the
unique_id
column).




... Continuing with this logic:

A query is executed when viewing
Page A
:

SELECT `id` FROM `table` WHERE `unique_id` = x;


Today I received the following error:

SQLSTATE[21000]: Cardinality violation: 1242 Subquery returns more than 1 row


This is the first time I have experienced this issue (with a table in excess of 20k rows).

Is is possible that if two separate users both clicked on
Button A
at precisely the same moment in time (down to the millisecond), that two rows could be written with duplicate values in the
unique_id
column?

If so, how can I avoid this happening again in future? (Am I taking the wrong approach here?).

Answer

With the approach you are using, the answer is most likely that two records can be created. The first thing to do is to actually create UNIQUE INDEX on your unique_id column. According to your question it does not seem to have one.

That raises another question. Do you really need both an id and unique_id in your table? It may be possible to use only one or the other. If you dropped the unique_id and relied merely on the primary key and converted that to an auto increment field, this problem would not exist.

If you want to continue with your current approach, add the unique index and then do the INSERT first.