Restored my MySQL, and now i cannot create cases

[gmullen]

I recently moved my Sugar from a linux machine onto a windows server 2003 machine. I am using IIS 6, MySQL 5, and PHP 5.1.6. Both machine are using the same MySQL and PHP versions. I migrated my MySQL database onto the new machine using MySQL Migration Toolkit, and installed sugar with little problem (Was surprised how smoothly it went). I can log into my sugar just fine, and all my old data appears to be there. I had to recreate a few of the custom dropdowns I made etc, however, the problem I now have is when i try to create a case there is no longer a default case number. there is simply nothing there for case number, and i receive this error:

Query Failed:INSERT into cases set id='7659d1ca-fa7f-a547-3fc5-4575c9ba245f', date_entered='2006-12-05 19:32:12', date_modified='2006-12-05 19:32:12', modified_user_id='59d21aa4-dffe-ea85-12df-44ce3fa1d477', assigned_user_id='59d21aa4-dffe-ea85-12df-44ce3fa1d477', created_by='59d21aa4-dffe-ea85-12df-44ce3fa1d477', deleted='0', name='test', account_id='864228ac-2dac-85cc-194a-44e1d5d8c6b6', status='New', priority='P2', description=null, resolution=null::MySQL error 1364: Field 'case_number' doesn't have a default value

How do i resolve this problem?

[kpit]

I recently moved my Sugar from a linux machine onto a windows server 2003 machine. I am using IIS 6, MySQL 5, and PHP 5.1.6. Both machine are using the same MySQL and PHP versions. I migrated my MySQL database onto the new machine using MySQL Migration Toolkit, and installed sugar with little problem (Was surprised how smoothly it went). I can log into my sugar just fine, and all my old data appears to be there. I had to recreate a few of the custom dropdowns I made etc, however, the problem I now have is when i try to create a case there is no longer a default case number. there is simply nothing there for case number, and i receive this error:

Query Failed:INSERT into cases set id='7659d1ca-fa7f-a547-3fc5-4575c9ba245f', date_entered='2006-12-05 19:32:12', date_modified='2006-12-05 19:32:12', modified_user_id='59d21aa4-dffe-ea85-12df-44ce3fa1d477', assigned_user_id='59d21aa4-dffe-ea85-12df-44ce3fa1d477', created_by='59d21aa4-dffe-ea85-12df-44ce3fa1d477', deleted='0', name='test', account_id='864228ac-2dac-85cc-194a-44e1d5d8c6b6', status='New', priority='P2', description=null, resolution=null::MySQL error 1364: Field 'case_number' doesn't have a default value

How do i resolve this problem?

Check the MySQL user has proper permissions for the SugarCRM database. The SugarCRM user in MySQL needs to have the following permissions:

SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, CREATE VIEW, SHOW VIEW

The views are not nessary at this time but you may see this as a requirment in later versions of SugarCRM. This should be done for each host defined including localhost for the sugarcrm user.

The case_number field is not defined but required what does it show in your entry field for the case number?

Also try rebuilding indexes and relationships in the admin panel.

[Angel]

It sounds like the auto_increment property for the case_number didn't carry over in the transfer.

Verify that this is the case. If you need to correct it, the following SQL syntax should correct it

ALTER TABLE cases CHANGE case_number case_number int(11) NOT NULL auto_increment;

EDIT: Apparently this can also come up if you are using STRICT MODE on the MySQL server.

[gmullen]

Thanks for your help Angel, that line of code you gave me fixed the problem.....