Error on slave database: Duplicate entry

[DomeDan]

Have you thought about this issue?:
http://www.vicidial.org/VICIDIALmantis/view.php?id=619
I would change to the master database and then back to slave to prevent this,
or is there any other way you would do it?
if not then I can write a patch to solve this.

[mflorell]

Good catch!

We are planning on adding admin logging to all reports, so this is something we are going to have to address on all report scripts in the near future.

[williamconley]

store a UUID function in the database and use it to generate the key for anything that will happen in the admin logs. need a diff?

[DomeDan]

Got an other report with this issue: "Search Leads Logs"

same problem as in issue http://www.vicidial.org/VICIDIALmantis/view.php?id=619

* setup slave database
* use slave database for reports
* run a "Search Leads Logs" that uses slave + some other report that uses master later
then it uses the slave database for every query in admin_search_lead.php including "INSERT INTO vicidial_admin_log ...."
and when the master database does an other insert into vicidial_admin_log and the replication process copy it over to the slave then the slave complains when the private key id already exists:

Error 'Duplicate entry '23107' for key 'PRIMARY'' on query. Default database: 'asterisk'. Query: 'INSERT INTO vicidial_admin_log set event_date='2014-08-29 10:06:42', user='1234', ip_address='1.1.1.1', event_section='LEADS', event_type='SEARCH', record_id='616387', event_code='ADMIN SEARCH LEAD', event_sql="SELECT lead_id,entry_date,modify_date,status,user,vendor_lead_code,source_id,list_id,gmt_offset_now,called_since_last_reset,phone_code,phone_number,title,first_name,middle_initial,last_name,address1,address2,address3,city,state,province,postal_code,country_code,gender,date_of_birth,alt_phone,email,security_phrase,comments,called_count,last_local_call_time,rank,owner from vicidial_list where first_name=\\'eddie\\' and last_name=\\'meduza\\' order by last_local_call_time desc limit 1000|", event_notes='''

and the slave stops and all reports using slave seams to get stuck in time

[williamconley]

Temporary solution: Disable logging of admin searches if you're linked to the slave db.

Or force placement of that log entry into the master even though you're connected to the slave.

[DomeDan]

Just change the system setting to not use slave database for reports is the first thing to do.

I've made a patch to fix the issue in admin_search_lead.php http://www.vicidial.org/VICIDIALmantis/view.php?id=790
what I did was to add code to change to master before doing a INSERT or UPDATE like it was done in lead_report_export.php http://www.vicidial.org/VICIDIALmantis/view.php?id=619

[geoff3dmg]

Thanks for the patch Domdan, I've had this happen occasionally here. I'd like to point out that it's also a good idea to set slave database servers to read only.

https://mariadb.com/blog/looking-slave- ... kip-errors

[DomeDan]

oh, didnt know about the read only option, it makes perfectly sense to have that option.
though it had to be read/write to be able to run the replication.
Thanks geoff3dmg!