CiviCRM Community Forums (archive)

Twice now I've had a mysql slave stops because of duplicate key errors.  The tables involved are:

main_crm_db.civicrm_uf_match
main_cms_db.users
main_cms_db.users_roles

where main_crm_db is the CiviCRM db and main_cms_db is the Drupal database.

Specifically, the errors are:

      Last_Error: Error 'Duplicate entry '11233' for key 1' on query. Default database: 'main_crm_db'. Query: 'INSERT INTO civicrm_uf_match(uf_id,contact_id,domain_id,email) values(xxx)'

...which I then try to correct manually, only to have....

                 Last_Error: Error 'Duplicate entry '10928' for key 1' on query. Default database: 'main_cms_db'. Query: 'INSERT INTO users(uid,name,pass,mail,created,status,language) values(xxx)'

...which I then try to correct manually, only to have....

                 Last_Error: Error 'Duplicate entry '10928-9' for key 1' on query. Default database: 'main_cms_db'. Query: 'INSERT INTO users_roles(uid,rid) values(xxx)'

...which I then try to correct manually.  After this cycle, another cycle starts, involving similar statements and the same tables in the same order.


I thought it might be this, because civicrm_uf_match is InnoDB and users and users_roles is MyISAM:
"Replication and CASCADE.   Cascading actions for InnoDB tables on the master are replicated to the slave only  if both the master's and slave' versions of the tables sharing the foreign key relation use InnoDB. For example, suppose you have started replication, and then create two tables on the master using the following CREATE TABLE  statements:"
-- http://dev.mysql.com/doc/refman/5.0/en/innodb-and-mysql-replication.html

But I'm not seing a chain of foreign keys between the 3:

mysql> select CONSTRAINT_NAME, TABLE_SCHEMA, TABLE_NAME, REFERENCED_TABLE_NAME from KEY_COLUMN_USAGE where table_name in ('civicrm_uf_match', 'users', 'users_roles') or REFERENCED_TABLE_NAME in ('civicrm_uf_match', 'users', 'users_roles') ;

+-------------------------+--------------+------------------+-----------------------+
| CONSTRAINT_NAME         | TABLE_SCHEMA | TABLE_NAME       | REFERENCED_TABLE_NAME |
+-------------------------+--------------+------------------+-----------------------+
| PRIMARY                 | main_cms_db  | users            | NULL                  |
| name                    | main_cms_db  | users            | NULL                  |
| PRIMARY                 | main_cms_db  | users_roles      | NULL                  |
| PRIMARY                 | main_cms_db  | users_roles      | NULL                  |
| PRIMARY                 | main_crm_db  | civicrm_uf_match | NULL                  |
| UI_contact              | main_crm_db  | civicrm_uf_match | NULL                  |
| UI_uf_domain_id         | main_crm_db  | civicrm_uf_match | NULL                  |
| UI_uf_domain_id         | main_crm_db  | civicrm_uf_match | NULL                  |
| civicrm_uf_match_ibfk_1 | main_crm_db  | civicrm_uf_match | civicrm_contact       |
| civicrm_uf_match_ibfk_2 | main_crm_db  | civicrm_uf_match | civicrm_domain        |
+-------------------------+--------------+------------------+-----------------------+

I don't have experience with MySQL replication - so others may have better insight here. However, it migth help us understand why the query is failing if you provide the "values" that it's trying to insert.

Also, I'm not aware of any PHP script that is part of the CiviCRM distribution which would attempt to insert records to the civicrm_uf_match table. Are you running custom PHP scripts via cron?

NOTE: The civicrm_uf_match table is a join table between your CMS user table ('users' in Drupal) and the CiviCRM contact table (civicrm_contact). The uf_id is an implicit foreign key to users.uid. It does look like the queries are trying to insert a record for a user that already exists in those tables - but not sure why.

It's clear that the replication is trying to insert records which already exist in the slave copy (for user 10928). However, I don't know why this is happening. Since this seems more like a general replication issue - your best bet might be to consult with someone who is knowledgeable about replication issues.

I Just wanted to follow up on this as there seemed to be no clear resolution here.
I got the same error and not quite sure what caused it.
To resolve the issue, I simply went into the table civicrm_uf_match and deleted the entry that the system was complaining about. The user then logged in and a new record for that user was created in the table with the correct links.
Hope this helps someone else out.
Sincerely,
Ryan