Database Replication, Perfect, Until....

[Nefariousparity]

Hi, I have a clustered setup as you can read in my signature that works fine. I decided recently to try out database replication to have a hot backup. ( I know about changing ip's in case of failure. ) I also wanted replication for when my server is hamming it down at 800+ channels running a big report does not hurt anything.

So I setup database replication, and have no issues, go to system settings tell all reports to use slave DB. Realtime Works, Export calls works like a charm and fast etc...

However, I noticed this morning when I went to reports --> Admin Utilities --> Reset Campaign Lists

Upon doing so, all the sudden I noticed all my agents waiting for calls for a long time. I was like? WTH?

So I went back into system settings, and told Vicidial to not use slave server. Once again real time report working fine, among all other reports.

I logged into slave db, logged into mysql run this command

mysql>SHOW SLAVE STATUS\G;

And this is the error it proudced.

Error 'Duplicate entry '113176' for key 1' on query. Default database: 'asterisk'. Query: 'INSERT INTO vicidial_admin_log set event_date='2015-01-09 08:21:40', user='admin', ip_address='73.99.154.223', event_section='CAMPAIGNS', event_type='RESET', record_id='1001', event_code='ADMIN RESET CAMPAIGN LISTS', event_sql="select list_id from vicidial_lists where campaign_id=\'1001\' and active=\'Y\' order by list_id asc|", event_notes='''

Here is the whole output of that command:

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.230
Master_User: replicate
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 146656667
Relay_Log_File: slave-relay-bin.000004
Relay_Log_Pos: 1060897629
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Error 'Duplicate entry '113176' for key 1' on query. Default database: 'asterisk'. Query: 'INSERT INTO vicidial_admin_log set event_date='2015-01-09 08:21:40', user='admin', ip_address='74.100.156.223', event_section='CAMPAIGNS', event_type='RESET', record_id='1001', event_code='ADMIN RESET CAMPAIGN LISTS', event_sql="select list_id from vicidial_lists where campaign_id=\'1001\' and active=\'Y\' order by list_id asc|", event_notes='''
Skip_Counter: 0
Exec_Master_Log_Pos: 1060897492
Relay_Log_Space: 1220399405
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
1 row in set (0.00 sec)

ERROR:
No query specified


Thanks in advance!!
Kind Regards

-Nef

[okli]

Apparently a bug, however, as a quick workaround until it's fixed, you could set the slave DB read-only, which would also help in case there are any other admin pages which attempt to write to the slave.

FYI:
http://www.vicidial.org/VICIDIALmantis/view.php?id=790
viewtopic.php?f=3&t=26231

[Nefariousparity]

okli! Thank you thank you for the response!

This is the second bug I have ever discovered with Vici. Oh wow looking at link, I guess I am not the first! Thank you so much Okli!!

A few questions about Read Only.

1)Would that mean that the slave would not get any new information from master?
(Like I would have to extract and re upload master database daily?)
2)How does one make Slave Read Only?

Thank you!!

[okli]

1) Nope, replication works as before:
http://dev.mysql.com/doc/refman/5.5/en/ ... _read_only

This variable is off by default. When it is enabled, the server permits no updates except from users that have the SUPER privilege or (on a slave server) from updates performed by slave threads. In replication setups, it can be useful to enable read_only on slave servers to ensure that slaves accept updates only from the master server and not from clients.

You should read all the notes.

In addition, you might want to make sure there are no other SUPER privileged users, than root:

Code: Select all

UPDATE mysql.user SET super_priv='N' WHERE user<>'root';
FLUSH PRIVILEGES;

2) Permanently:
my.cnf

Code: Select all

[mysqld]
...
...
read-only=1
...

and restart the slave mysql stopping replication beforehand. Hopefully when MySQL/MariaDB starts, and you turn replication on, it will continue where you stopped it. Otherwise, you might need to redo it.

For the current session, until mysql is restarted:
http://dev.mysql.com/doc/refman/5.1/en/ ... -only.html

Code: Select all

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SET GLOBAL read_only = ON;

[Nefariousparity]

Wow Okli, above and beyond. You went above and beyond. Thank you I will make changes when we are not in production.

[Nefariousparity]

Worked like a charm setting it as read only.