moose moose - 1 year ago 147
MySQL Question

Unexpected cardinality violation (Subquery returns more than 1 row)

Consider the following example:

A MySQL table (

) with structure
(Primary Key),

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.
does not yet exist in the
column), the new row is inserted into
in the

... 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

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

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download