MySQL Error 1062 on creation of record

[monsta]

Hi,

We are currently running a pilot of Sugar CRM where two users have moved to Sugar from Goldmine CRM.

I wrote a few PHP scripts that copy data between sugar and goldmine and all is working fine. Except all of a sudden we're now getting the MySQL Error 1062 when creating new records in most tables.

I understand (from other posts) is that the problem is down to the unique id's I have generated within my PHP script (even though I have used Sugar's create_guid() function) and I need to reset the auto increment counter on the MySQL database.

How do I go about this? Also, my understanding was that the id's are not auto increment?

Thanks in advance,

Justin.

[Angel]

I am not quite clear on what it is you've done, but that error message would seem to point to the fact that there are duplicate GUID values in some of the records.

The auto increment is only applicable to certain tables, for example, Cases. Not knowing which actions are causing the error, it is difficult to state whether or not this is part of the problem.

One quick way that you can determine if you have duplicate GUID values in most tables is to run the following queries:

SELECT count(*) FROM contacts

SELECT count(distinct(id)) FROM contacts

Both of those queries should return the same number. If they don't, you have duplicates.

[monsta]

The problem has occured when checking emails, creating a call or meeting. We haven't used cases so the auto_increment thing doesn't apply then.

The number of rows and the number unique ids are the same on these tables. The strange thing is that now it's working again...?!

Here is an example query, since this is an email I have removed most of the body and change both destination and sender email address.

Error message was ::MySQL error 1062: Duplicate entry '' for key 2

INSERT into emails set id='e42f3638-2e4e-7f21-0359-46bc3fa45840', date_entered='2007-08-10 10:36:31', date_modified='2007-08-10 10:36:31', assigned_user_id='487c7b82-0f08-ee53-d5fe-45c34af51f91', modified_user_id='487c7b82-0f08-ee53-d5fe-45c34af51f91', created_by='487c7b82-0f08-ee53-d5fe-45c34af51f91', name='Airmagnet', date_start='2007-08-10', time_start='10:36:00', parent_type='Accounts', parent_id=null, description='Dear Kevin', description_html=null, from_addr='smackinnon@ourdomain.co.uk', from_name='Sheila Mackinnon', to_addrs='Kevin Watkins ;', cc_addrs=null, bcc_addrs=null, to_addrs_ids='b3c490ca-ba46-aade-1738-46bb4291ac9e; ', to_addrs_names='Kevin Watkins; ', to_addrs_emails='kevin.watkins@theirdomain.co.uk; ', cc_addrs_ids=null, cc_addrs_names=null, cc_addrs_emails=null, bcc_addrs_ids=null, bcc_addrs_names=null, bcc_addrs_emails=null, type='out', status='sent', deleted='0'

[Angel]

I ran this query on a test database and didn't get any errors. Are you able to execute it through phpMyAdmin or other MySQL admin tool?

[monsta]

When the error occured, I ran it in phpMyAdmin and got the same error. But in some cases if I tried it again a few minutes later it would work...?!

I'm most confused by this.

I ran this query on a test database and didn't get any errors. Are you able to execute it through phpMyAdmin or other MySQL admin tool?

[monsta]

OK this gets more confusing. We had an error sending an email, I ran the query in phpMyAdmin and it failed

I then took a phone call and tried the query again afterwards and it worked fine?!?!

SQL query:

INSERT INTO emails
SET id = 'd444992b-4c83-84ad-3d84-46c181e60c29',
date_entered = '2007-08-14 10:17:36',
date_modified = '2007-08-14 10:17:36',
assigned_user_id = '1',
modified_user_id = '1',
created_by = '1',
name = 'Test',
date_start = '2007-08-14',
time_start = '10:17:00',
parent_type = 'Contacts',
parent_id = '9ccd3d77-958c-2d65-e926-46bb42b5e4cf',
description = 'Test',
description_html = NULL ,
from_addr = 'sugar@gss.co.uk',
from_name = 'Sugar Administrator',
to_addrs = 'Mr. Justin Wanstall ',
cc_addrs = NULL ,
bcc_addrs = NULL ,
to_addrs_ids = '9ccd3d77-958c-2d65-e926-46bb42b5e4cf',
to_addrs_names = 'Mr. Justin Wanstall',
to_addrs_emails = 'justin@wanstall.co.uk',
cc_addrs_ids = NULL ,
cc_addrs_names = NULL ,
cc_addrs_emails = NULL ,
bcc_addrs_ids = NULL ,
bcc_addrs_names = NULL ,
bcc_addrs_emails = NULL ,
TYPE = 'out',
STATUS = 'sent',
deleted = '0'

MySQL said:

#1062 - Duplicate entry '' for key 2

[Angel]

Not sure what to tell you.

I can run that query multiple times on my system and all it complains about is the ID value, which makes sense. If I change the ID value in between executions, it works fine.

BTW, the error would be on Key1 if it was an ID problem on your end too.

All I can theorize is that there is something in the emails table that is causing the problem. The error seems to indicate a problem with duplicate values for Key2, the name field if not mistaken, but I can enter it multiple times without problem on my end and I do not know of it needing to be a unique value.

Can you post the CREATE TABLE script for your emails table (including indexes)? Perhaps something in the structure is causing the problem.

[monsta]

Well I think I have solved the problem and I have to put my hands up and take the blame.

Basically I had setup an extra column for a record id relating to Goldmine's database. I set this as unique. While this was OK most of the time, if there was a new sugar record (i.e. with blank GM RECID) and that record hadn't been sync'd back to Goldmine (and therefore a RECID was added to Sugar's db) before another one was created, the error came up as there were two blanks.

This explains why I could run the same query minutes later and it would work, the RECID had been added by that point.

I'm feeling slightly red faced now but this is a steep learning curve for me!

Cheers.

Can you post the CREATE TABLE script for your emails table (including indexes)? Perhaps something in the structure is causing the problem.

[nehcobocaj]

Hi thanks guys for such good devotion on the developers part of the sugarcrm. the problem is related to the one mentioned above but i must say that am not a very good admin user of the Sugarcrm. I get the same problem when i check mails. contacts or cases can anyone please help me be as genius as he is??

this is what comes on one of the cases and many more for emails and etc


Query Failed:INSERT into tracker ( user_id, module_name, item_id, item_summary,date_modified) values ('7b9edb91-5ded-cc3f-d4a2-4448e86a14a4', 'Cases', '304ad1c2-7900-3a63-574b-46bff8d71ef1', 'case testing on callender','2007-09-28 12:37:05')::MySQL error 1062: Duplicate entry '64431' for key 1

[nehcobocaj]

Hey guys guys guys, i got the solutions myself but incase you tried to search and help out thanks anyway.
ok,
Just as Angel had suggested to make sure of the duplicate values by typing the select sql querry. I found out that there was no duplication of values.
so as i was bussy riping hair out of my head,
i read the Error again and reallised that the 64431 is either an overload of the database or something related to it (lol i hoped so)
or when it said"" Insert into tracker""" i reallised the problem lay in the tracker table and straight away i was rumbling on it.
__________
phpMyAdmin------>root------->sugardatabase------------>Tracker table----------->clicked the small icon of structure---->click the OPTIMISE TABLE-----------> check it again. cool

oh sorry windows server--4.1blah blah database