I think this is similar to http://drupal.org/node/586952

I open an existing ticket. upload one text file to it. The file got uploaded. But I get the user warning like

user warning: Duplicate entry '3' for key 'PRIMARY' query: INSERT INTO support_ticket (nid, message_id, state, priority, client, assigned) VALUES(3, '', 2, 3, 3, 8) in C:\xampplite\htdocs\drupal\modules\support\support.module on line 649.

Screenshot attached.

OS : windows XP
PHP Version : 5.3.1
Drupal version : 6.15

CommentFileSizeAuthor
#5 support-removing_duplicate_entry_errors.patch8.46 KBrsevero
#2 support_ticket.pdf548.49 KBbarathee
stissue.PNG86 KBbarathee

Comments

barathee’s picture

barathee commented

I am getting the same issue, while closing a ticket (ticket id 1).

user warning: Duplicate entry '1' for key 'PRIMARY' query: INSERT INTO support_ticket (nid, message_id, state, priority, client, assigned) VALUES(1, '', 4, 2, 1, 7) in C:\xampplite\htdocs\drupal\modules\support\support.module on line 649.

barathee’s picture

barathee commented
StatusFileSize
new support_ticket.pdf548.49 KB

Adding the table data of support_ticket

jeremy’s picture

jeremy commented
Status: Active » Postponed (maintainer needs more info)

I'm at a loss. This should not happen, and I'm unable to come up with a theory as to what might cause this.

What type of database are you using? (ie, MySQL, PostgreSQL)

Does this affect all tickets, or just one specific ticket?

What version of the support module are you using?

Is there anything non-standard about your installation?

rsevero’s picture

rsevero commented
Status: Postponed (maintainer needs more info) » Active

In MySQL mysql_affected_rows (which is called by db_affected_rows) returns the number of lines actually changed not the number of lines selected by the WHERE clause.

You would have to specify the CLIENT_FOUND_ROWS flag to mysql_real_connect() when connecting to mysqld to make MySQL return the number of lines selected by the WHERE clause.

I believe the portable way to deal with these situations is to execute a SELECT first and based on the result of the SELECT, choose to do an UPDATE or an INSERT.

You have 4 places where you execute an INSERT if the previous UPDATE produces a db_affected_rows() = 0. In all these places this same problem might appear.

I can prepare a patch if there is interest but I would prefer to be sure about the question on #973358: What is the difference between a user not having an entry related to a ticket in {support_assigned} and having active = 0? so I would have to deal with the active column of the support_assigned table only once.

rsevero’s picture

rsevero commented
Status: Active » Needs review
StatusFileSize
new support-removing_duplicate_entry_errors.patch8.46 KB

The change that #973358: What is the difference between a user not having an entry related to a ticket in {support_assigned} and having active = 0? will probably produce can wait.

For now here is a patch that solve "Duplicate entry" errors in all possible 4 places.

webmestre’s picture

webmestre commented
Assigned: Unassigned » webmestre

Patching the module is a bit complicate. Not working with the "Patch manager" module.
Wouldn't it be easyer to release a new patched version ?

rsevero’s picture

rsevero commented
Assigned: webmestre » Unassigned

@webmestre: Certainly. That's the reason for the patch available here.

You can help by applying the patch and testing it. If everything seems to be ok, change the "status" from "needs review" to "reviewed & tested by the community".

Please don't change the "assigned" field to your name.

webmestre’s picture

webmestre commented

@rsevero SORRY for changing assignement.

Now, it works.

1- completely remove the previous version
2- install the new release
3- patch

NB : when trying to get old support exchanges, the error message comes back. No problem for new messages.

rsevero’s picture

rsevero commented
Status: Needs review » Reviewed & tested by the community

Changing the status based on webmestre report on #8.

span’s picture

span commented

Applied and tested, seems to work fine for me.

bdragon’s picture

bdragon commented
Status: Reviewed & tested by the community » Needs work

Here's why it happens:
On mysql, db_affected_rows() is the number of rows that were actually changed. Therefore, if you are trying to update something to the same value, you will get 0 back because there were no actual changes.

Core works around this in cases where it is possible to update something to the same value (i.e. stuff that isn't doing x = x + 1 or similar tricks in the UPDATE query) by using the @ error control operator to mask errors completely in the INSERT query.

(Also, doing count queries to detect the existence of a row is incorrect. This can really get you in trouble when using replication for example.)

bdragon’s picture

bdragon commented
Status: Needs work » Closed (duplicate)

Marking as duplicate of #677504: duplicate entry error inserting into support_assigned.