Case number always 0 (zero)

[BigWil]

Installed 6.1.1 and had this issue. Upgraded to 6.1.2 and still have this issue.

Tue Mar 1 11:20:01 2011 [44061][1][FATAL] Query Failed: INSERT into cases set id='a352b58d-5ea7-eba1-273d-4d6d46f9048f', name='MySQL troubles', date_entered='2011-03-01 19:20:01', date_modified='2011-03-01 19:20:01', modified_user_id='1', created_by='1', description='Having serious problems with mysql. Please help.\r\n\r\nSamuel', deleted='0', assigned_user_id='1', status='New', priority='P1': MySQL error 1062: Duplicate entry '0' for key 2

Seems that when the inbound group email account imports an email and tries to convert it to a case that the case number is always zero. This causes the duplicate entry '0' for key two error. The scheduler shows In Progress and doesn't try re-running because of this.

Anybody know of a quick fix for this or shall i create a bug report? Considering that this has existed for at least two versions now I'm assuming the bug report wouldn't be resolved anytime real soon.

I noticed a similar report on a different post and the resolution there was to do a repair on the database and run the admin fix database function. That hasn't worked at all.

BigWil

[andopes]

You have to run mysql check in order to repair the cases table if it is corrupted.
Then go to Admin -> Repair -> Quick Repair and Rebuild to rebuild the extensions and database.
Try again.

Cheers

[BigWil]

It was actually your resolution I attempted earlier. Already did a check and a repair on the table. Then went to Admin > Repair > Quick and did that too. This was not effective.

I think I have it resolved though. I suspect that somewhere in a previous version maybe 6.1.1 could be earlier the table creation for the cases failed to create an auto increment field for the case number. When i cracked it open to have a look I noticed no auto_increment and a default set for 0 (zero). I turned that column into an autoincrement and cleared the default. I have been sending emails to and from in testing and it seems to be working fine. Just to make sure I had a look at the relationships and those too seem to be fine.

So I think the answer to this bug is....

 ALTER TABLE `cases` CHANGE `case_number` `case_number` INT( 11 ) NOT NULL AUTO_INCREMENT 
  

I'm also noticing that phpmyadmin is complaining about this. "The following indexes appear to be equal and one of them should be removed: casesnumk, case_number" And sure enough these are both set on the same column. One is a UNIQUE and one an INDEX. I think the UNIQUE should suffice but that's just my opinion.

Thanks for the suggestion though and taking the time to read. Shall I mark this as resolved or do you have any further feedback for me?

BigWil

[andopes]

In my opinion it is resolved. I have no further feedback
Congrats.

Regards

[BigWil]

Thanks that's appreciated.

BigWil