Database Failure When Creating Cases

SuiteCRM Forum - English Language SuiteCRM General Discussion
1

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.

2

Hi kagbasi,

have a look at this
http://forums.sugarcrm.com/f3/cases-can-not-saved-v-6-1-2-a-69309/

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.

Ian,

3

Ian,

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?

4

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
5

@kagbasi that worked perfectly!