Database Failure When Creating Cases

I’m running SuiteCRM v7.1.4 and some of my users have reported to me that whenever they create cases they are unable to go back and view them. So I decided to check this out and it turns out that even though the activity streams shows that a case was created, it wasn’t in fact so. While attempting to save a test case I got the following error:

Database failure. Please refer to sugarcrm.log for details.

So I got curious and decided to checkout the log and I found the following error message as a result of my attempt to create the test case:

Mon Feb  9 14:19:44 2015 [3081][f0001cb5-be06-aa26-1ce0-537f88383579][FATAL] Error inserting into table: cases: Query Failed: INSERT INTO cases (id,name,date_entered,date_modified,modified_user_id,created_by,description,deleted,assigned_user_id,type,status,priority,resolution,account_id,state)
					VALUES ('7004ee6c-a6a5-304a-64f7-54d9082d18ae','Test Case','2015-02-09 19:19:43','2015-02-09 19:19:43','f0001cb5-be06-aa26-1ce0-537f88383579','f0001cb5-be06-aa26-1ce0-537f88383579','Testing',0,'f0001cb5-be06-aa26-1ce0-537f88383579','Billing_Issue','Open_New','P1','','8b42c284-41b3-13ec-10a7-54d3c738f0cd','Open'): MySQL error 1062: Duplicate entry '0' for key 'casesnumk'

I also noticed that the later part of the error (MySQL error 1062: Duplicate entry ‘0’ for key ‘casesnumk’) was consistent in all the other INSERT statements that were a result of my users’ previous attempts at creating cases. So, how can I resolve this problem? It appears SuiteCRM is trying to use the same key when creating new cases - or am I reading this wrong?

Your help would be greatly appreciated, thanks.

Hi kagbasi,

have a look at this

it appears they had the same problem and it was to do with the case number which auto increments, there is a link which may solve the problem.



Thanks for the assist. I checked out the article and followed the suggestions to use mysqlcheck to repair the cases table. Unfortunately, upon attempting to create a test case I’m getting the same error in the sugarcrm.log. Strangely though I was expecting to see these errors in mysql’s error log but nothing there, so it appears the queries aren’t making it to the DB - or are they?

Anyway, here’s the error I’m getting in SuiteCRM:

Database failure. Please refer to sugarcrm.log for details.

And here’s the error in the sugarcrm.log file:

Tue Feb 10 09:26:01 2015 [4411][1][FATAL] Error inserting into table: cases: Query Failed: INSERT INTO cases (id,name,date_entered,date_modified,modified_user_id,created_by,description,deleted,assigned_user_id,type,status,priority,resolution,account_id,state)
					VALUES ('f2ce7455-7e64-dcdc-42e9-54da14fa385d','Test Case','2015-02-10 14:25:59','2015-02-10 14:25:59','1','1','This is a test case',0,'1','Billing_Issue','Open_New','P1','','118280','Open'): MySQL error 1062: Duplicate entry '0' for key 'casesnumk'

Using MySQL Workbench I checked out the cases table and found that the datatype for the case_number field is set to INT(11) with the Not Null and Unique Index properties enabled. It’s not set to autoincrement - could this be the problem?

I think I may have solved the issue after reading this post. It involved making the case_number field autoincrement. I am now able to create cases, but I’m not sure if this is upgrade safe. Could somebody on the SuiteCRM development team take a look at the application code that creates the cases table to ensure it is setting the autoincrement flag for the case_number field?

Thanks Ian for pointing me in the right direction.

1 Like

@kagbasi that worked perfectly!