1062 - Duplicate entry '1-1' for key 'PRIMARY'

[Fearghal]

Hi,

Any time my plug-in attempts to insert duplicate values into the database MyBB gives out an error.

I have the insert statement inside a try catch but instead of running the catch MyBB just stops everything and throws out the error.

Does anyone know how I can make it run the catch and continue on without stopping the user?

Thanks

[Darth Apple]

What exactly are you trying to do? Is it throwing the error when you attempt to view some page of your website, or on the plugin install?

Generally, because it's hard for the end user to fix database corruption, it's usually best to just avoid creating queries that have errors at all.

Edit: didn't read the OP well. I assume it's a bug you're running into during plugin use?

[Fearghal]

My plug-in allows users to vote on threads, and has the users ID and the thread ID as primary keys. If the user tries to vote for the same thread twice the database will not allow the entry.

I guess I could run a query to check if they have an existing entry but it would be much easier if I could just use try catch.

[Darth Apple]

My plug-in allows users to vote on threads, and has the users ID and the thread ID as primary keys. If the user tries to vote for the same thread twice the database will not allow the entry.

I guess I could run a query to check if they have an existing entry but it would be much easier if I could just use try catch.

If I were developing it, I'd probably just run another query to check if the user has already voted. The advantage of using your own query is that you have complete control over exactly what the plugin does. If you rely on MySQL errors, any ordinary MySQL error that might have nothing to do with an existing key in the database would flag a "you have already voted for this thread" error.

[Fearghal]

Ok

If I were developing it, I'd probably just run another query to check if the user has already voted. The advantage of using your own query is that you have complete control over exactly what the plugin does. If you rely on MySQL errors, any ordinary MySQL error that might have nothing to do with an existing key in the database would flag a "you have already voted for this thread" error.

Ok I will add an extra check before the entry then

Thanks!

[dragonexpert]

You could just do 4 columns in the table instead.

Suggested structure:
vid INT NOT NULL AUTO INCREMENT PRIMARY KEY
tid INT UNSIGNED NOT NULL DEFAULT 0
uid INT UNSIGNED NOT NULL DEFAULT 0
rating INT UNSIGNED NOT NULL DEFAULT 0

If you want them to be able to use decimal points have the field rating be FLOAT instead.

[Fearghal]

You could just do 4 columns in the table instead.

Suggested structure:
vid INT NOT NULL AUTO INCREMENT PRIMARY KEY
tid INT UNSIGNED NOT NULL DEFAULT 0
uid INT UNSIGNED NOT NULL DEFAULT 0
rating INT UNSIGNED NOT NULL DEFAULT 0

If you want them to be able to use decimal points have the field rating be FLOAT instead.

Hi Dragonexpert,

Would this work even if each user was only allowed to vote once?

[Darth Apple]

If you're using a vote ID as the primary key, MySQL won't prevent the user from voting multiple times on one thread. You will have to code prevention of that into your own code. It will, however, be much more stable and more predictable. I took a class on using Microsoft office databases a while back and was told that using multiple primary keys is a big no in database design.

Something like what dragonexpert suggested is probably about the ideal design for what you're trying to do. To check if a user has already voted for a thread, you'll only need something like this:

		$query = $db->simple_select("votes", "vid", "tid = '$tid' AND uid = '$uid'");
		$result = $db->fetch_field($query, "vid");
		if (!empty($result)) {
				error("you have already voted for this thread. ");
		}

[Tom K.]

The issue you're probably having there is that you've set the user ID and thread ID as a primary composite key. This requires them to be unique, a simple concept of database normalisation.

You should use the 4 columns as dragonexpert said. Then perform logic to check if a user has voted based on whether or not the table already contains a vote for that thread and that specific user. Also, you should really be checking to see if the user has already voted already, instead of letting them submit a useless vote and then informing them afterwards. Maybe you should remove the vote button altogether tat runtime if there is already a vote for that user in that thread?

[Cameron:D]

If you check if they have voted and then insert the vote afterward you open yourself up to a race condition in which a user can vote multiple times (This issue already exists in MyBB's poll system (1)(2) and some like plugins such as g33k Thankyou/Like (3)).
He is not wrong in making the primary key a composite key between uid and tid as a method of preventing duplicate votes (he just needs to add a friendly fallback for this instead of the default MyBB SQL error page, which is what the OP was asking about), the other option here is for him to use the 4 columns and create a UNIQUE constraint on (tid, uid) to precent race conditions.
I'd argue that the primary key is fine - it covers the two columns that you're going to be using most, and often in conjunction.