MySQL Master-Master replication
1 April, 2014
We are trying to implement Yellowfin clustering on two nodes, with master-master replication for the configuration database, so it can run locally on each node (as the nodes are geographically disparate).
Our first problem seemed to be due to binlog_format=STATEMENT (similar to other posts in this forum), so we switched to MIXED and that error went away.
Now, replication will run successfully for a short time and then break, with error messages like the below in the MySQL error log.
[quote="">[ERROR] Slave SQL: Could not execute Update_rows event on table yellowfinDB.ReportGroup; Can't find record in 'ReportGroup', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000008, end_log_pos 166261, Error_code: 1032
140331 20:53:37 [Warning] Slave: Can't find record in 'ReportGroup' Error_code: 1032[/quote]
Is Yellowfin compatible with MySQL master-master replication for the configuration database? My hunch is that even though STATEMENT level replication won't work because of some READ UNCOMMITTED statements, ROW (or MIXED) level won't work either because of how Yellowfin interacts with the configuration database.
If it is compatible, can you help us with some tips? Thanks!
- Andy
Our first problem seemed to be due to binlog_format=STATEMENT (similar to other posts in this forum), so we switched to MIXED and that error went away.
Now, replication will run successfully for a short time and then break, with error messages like the below in the MySQL error log.
[quote="">[ERROR] Slave SQL: Could not execute Update_rows event on table yellowfinDB.ReportGroup; Can't find record in 'ReportGroup', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000008, end_log_pos 166261, Error_code: 1032
140331 20:53:37 [Warning] Slave: Can't find record in 'ReportGroup' Error_code: 1032[/quote]
Is Yellowfin compatible with MySQL master-master replication for the configuration database? My hunch is that even though STATEMENT level replication won't work because of some READ UNCOMMITTED statements, ROW (or MIXED) level won't work either because of how Yellowfin interacts with the configuration database.
If it is compatible, can you help us with some tips? Thanks!
- Andy
Some additional info that may be helpful in debugging...
- MySQL 5.5
- Per the common best practices for MySQL, we're using a different auto-increment-offset on each node, to avoid primary key collisions
- The data source settings for our Yellowfin configuration database have the isolation level set to READ COMMITTED. Per MySQL documentation this should be fully compatible with ROW level replication, though I find it odd that we still got an error about not allowing STATEMENT-level replication because of a READ UNCOMMITTED
Thanks, Andy
- MySQL 5.5
- Per the common best practices for MySQL, we're using a different auto-increment-offset on each node, to avoid primary key collisions
- The data source settings for our Yellowfin configuration database have the isolation level set to READ COMMITTED. Per MySQL documentation this should be fully compatible with ROW level replication, though I find it odd that we still got an error about not allowing STATEMENT-level replication because of a READ UNCOMMITTED
Thanks, Andy
Hi Andy,
Unfortunately we do not have a MySQL instance with this type of replication in the office, we are unable to test & identify if the issue stems from Yellowfin , or from the replication itself.
Technically we don't support this as it's something that is being handled outside of Yellowfin.
However, I could see how it be useful and would like to investigate further to see if there is something we can do. But this is where we have a problem :(.
Are you able to identify exactly where the issue lies, which would help us discuss what can be done. E.g, is it the same DB statement/query causing the issue.
The Yellowfin transaction isolation level should follow what is setup in the web.xml, and should never change unless manually changing. So not really sure why this error would only occur after a certain period of time.
Sorry for the inconvenience this has caused.
Regards,
David
Unfortunately we do not have a MySQL instance with this type of replication in the office, we are unable to test & identify if the issue stems from Yellowfin , or from the replication itself.
Technically we don't support this as it's something that is being handled outside of Yellowfin.
However, I could see how it be useful and would like to investigate further to see if there is something we can do. But this is where we have a problem :(.
Are you able to identify exactly where the issue lies, which would help us discuss what can be done. E.g, is it the same DB statement/query causing the issue.
The Yellowfin transaction isolation level should follow what is setup in the web.xml, and should never change unless manually changing. So not really sure why this error would only occur after a certain period of time.
Sorry for the inconvenience this has caused.
Regards,
David
Thanks for the reply David. Here is some more information:
First, this is the MySQL Error:
[quote="">
140408 2:46:43 [ERROR] Slave SQL: Could not execute Update_rows event on table yellowfinDB.ReportGroup; Can't find record in 'ReportGroup', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000016, end_log_pos 4352, Error_code: 1032
140408 2:46:43 [Warning] Slave: Can't find record in 'ReportGroup' Error_code: 1032
140408 2:46:43 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000016' position 3734
[/quote]
This is in MIXED mode binary logging... so it uses STATEMENT when it can (that is, the same SQL statement is run on slave), but MySQL switches to ROW when it detects an "unsafe" SQL statement. In this case, the ROW replication seems to be trying to update a row that does not yet exist on the master.
Below is the query:
[quote="">
### UPDATE `yellowfinDB`.`ReportGroup`
### WHERE
### @1=72904 /* INT meta=0 nullable=1 is_null=0 */
### @2='ANALYTIC' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @3=1 /* INT meta=0 nullable=1 is_null=0 */
### @4='ORGANISATION' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @5='UNSECURE' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @6='2014:01:07' /* DATE meta=0 nullable=1 is_null=0 */
### @7='9999:12:31' /* DATE meta=0 nullable=1 is_null=0 */
### @8='OPEN' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @9='Installations Dashboard' /* VARSTRING(768) meta=768 nullable=1 is_null=0 */
### @10='Description' /* MEDIUMBLOB/MEDIUMTEXT meta=3 nullable=1 is_null=0 */
### @11='EN' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @12=NULL /* VARSTRING(120) meta=120 nullable=1 is_null=1 */
### @13=NULL /* VARSTRING(120) meta=3 nullable=1 is_null=1 */
### @14=000020140.210135732 /* DECIMAL(15,0) meta=3840 nullable=1 is_null=0 */
### @15=12001 /* INT meta=0 nullable=1 is_null=0 */
### @16=72898 /* INT meta=0 nullable=1 is_null=0 */
### @17=NULL /* INT meta=768 nullable=1 is_null=1 */
### @18='OFFGRID' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @19='INSTALLATIONSDASHBOARD' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @20='bb1eec54-4019-431a-af3f-2579aafb3925' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @21=NULL /* VARSTRING(120) meta=0 nullable=1 is_null=1 */
### @22=NULL /* VARSTRING(120) meta=120 nullable=1 is_null=1 */
### @23=463 /* INT meta=0 nullable=1 is_null=0 */
### @24=2013-09-03 14:46:17 /* DATETIME meta=0 nullable=1 is_null=0 */
### @25=12001 /* INT meta=0 nullable=1 is_null=0 */
### SET
### @1=72904 /* INT meta=0 nullable=1 is_null=0 */
### @2='ANALYTIC' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @3=1 /* INT meta=0 nullable=1 is_null=0 */
### @4='ORGANISATION' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @5='UNSECURE' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @6='2014:01:07' /* DATE meta=0 nullable=1 is_null=0 */
### @7='9999:12:31' /* DATE meta=0 nullable=1 is_null=0 */
### @8='OPEN' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @9='Installations Dashboard' /* VARSTRING(768) meta=768 nullable=1 is_null=0 */
### @10='Description' /* MEDIUMBLOB/MEDIUMTEXT meta=3 nullable=1 is_null=0 */
### @11='EN' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @12=NULL /* VARSTRING(120) meta=120 nullable=1 is_null=1 */
### @13=NULL /* VARSTRING(120) meta=3 nullable=1 is_null=1 */
### @14=000020140.210135732 /* DECIMAL(15,0) meta=3840 nullable=1 is_null=0 */
### @15=12001 /* INT meta=0 nullable=1 is_null=0 */
### @16=72898 /* INT meta=0 nullable=1 is_null=0 */
### @17=NULL /* INT meta=768 nullable=1 is_null=1 */
### @18='OFFGRID' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @19='INSTALLATIONSDASHBOARD' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @20='bb1eec54-4019-431a-af3f-2579aafb3925' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @21=NULL /* VARSTRING(120) meta=0 nullable=1 is_null=1 */
### @22=NULL /* VARSTRING(120) meta=120 nullable=1 is_null=1 */
### @23=464 /* INT meta=0 nullable=1 is_null=0 */
### @24=2013-09-03 14:46:17 /* DATETIME meta=0 nullable=1 is_null=0 */
### @25=12001 /* INT meta=0 nullable=1 is_null=0 */
# at 4352
#140408 2:06:41 server id 1 end_log_pos 4379 Xid = 1910
[/quote]
I don't see any other inserts or updates to the ReportGroup table near this query.
I looked at the ReportGroup table on both master & slave... they are identical. This is curious, but I can't explain it.
First, this is the MySQL Error:
[quote="">
140408 2:46:43 [ERROR] Slave SQL: Could not execute Update_rows event on table yellowfinDB.ReportGroup; Can't find record in 'ReportGroup', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000016, end_log_pos 4352, Error_code: 1032
140408 2:46:43 [Warning] Slave: Can't find record in 'ReportGroup' Error_code: 1032
140408 2:46:43 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000016' position 3734
[/quote]
This is in MIXED mode binary logging... so it uses STATEMENT when it can (that is, the same SQL statement is run on slave), but MySQL switches to ROW when it detects an "unsafe" SQL statement. In this case, the ROW replication seems to be trying to update a row that does not yet exist on the master.
Below is the query:
[quote="">
### UPDATE `yellowfinDB`.`ReportGroup`
### WHERE
### @1=72904 /* INT meta=0 nullable=1 is_null=0 */
### @2='ANALYTIC' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @3=1 /* INT meta=0 nullable=1 is_null=0 */
### @4='ORGANISATION' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @5='UNSECURE' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @6='2014:01:07' /* DATE meta=0 nullable=1 is_null=0 */
### @7='9999:12:31' /* DATE meta=0 nullable=1 is_null=0 */
### @8='OPEN' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @9='Installations Dashboard' /* VARSTRING(768) meta=768 nullable=1 is_null=0 */
### @10='Description' /* MEDIUMBLOB/MEDIUMTEXT meta=3 nullable=1 is_null=0 */
### @11='EN' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @12=NULL /* VARSTRING(120) meta=120 nullable=1 is_null=1 */
### @13=NULL /* VARSTRING(120) meta=3 nullable=1 is_null=1 */
### @14=000020140.210135732 /* DECIMAL(15,0) meta=3840 nullable=1 is_null=0 */
### @15=12001 /* INT meta=0 nullable=1 is_null=0 */
### @16=72898 /* INT meta=0 nullable=1 is_null=0 */
### @17=NULL /* INT meta=768 nullable=1 is_null=1 */
### @18='OFFGRID' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @19='INSTALLATIONSDASHBOARD' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @20='bb1eec54-4019-431a-af3f-2579aafb3925' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @21=NULL /* VARSTRING(120) meta=0 nullable=1 is_null=1 */
### @22=NULL /* VARSTRING(120) meta=120 nullable=1 is_null=1 */
### @23=463 /* INT meta=0 nullable=1 is_null=0 */
### @24=2013-09-03 14:46:17 /* DATETIME meta=0 nullable=1 is_null=0 */
### @25=12001 /* INT meta=0 nullable=1 is_null=0 */
### SET
### @1=72904 /* INT meta=0 nullable=1 is_null=0 */
### @2='ANALYTIC' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @3=1 /* INT meta=0 nullable=1 is_null=0 */
### @4='ORGANISATION' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @5='UNSECURE' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @6='2014:01:07' /* DATE meta=0 nullable=1 is_null=0 */
### @7='9999:12:31' /* DATE meta=0 nullable=1 is_null=0 */
### @8='OPEN' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @9='Installations Dashboard' /* VARSTRING(768) meta=768 nullable=1 is_null=0 */
### @10='Description' /* MEDIUMBLOB/MEDIUMTEXT meta=3 nullable=1 is_null=0 */
### @11='EN' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @12=NULL /* VARSTRING(120) meta=120 nullable=1 is_null=1 */
### @13=NULL /* VARSTRING(120) meta=3 nullable=1 is_null=1 */
### @14=000020140.210135732 /* DECIMAL(15,0) meta=3840 nullable=1 is_null=0 */
### @15=12001 /* INT meta=0 nullable=1 is_null=0 */
### @16=72898 /* INT meta=0 nullable=1 is_null=0 */
### @17=NULL /* INT meta=768 nullable=1 is_null=1 */
### @18='OFFGRID' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @19='INSTALLATIONSDASHBOARD' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @20='bb1eec54-4019-431a-af3f-2579aafb3925' /* VARSTRING(120) meta=120 nullable=1 is_null=0 */
### @21=NULL /* VARSTRING(120) meta=0 nullable=1 is_null=1 */
### @22=NULL /* VARSTRING(120) meta=120 nullable=1 is_null=1 */
### @23=464 /* INT meta=0 nullable=1 is_null=0 */
### @24=2013-09-03 14:46:17 /* DATETIME meta=0 nullable=1 is_null=0 */
### @25=12001 /* INT meta=0 nullable=1 is_null=0 */
# at 4352
#140408 2:06:41 server id 1 end_log_pos 4379 Xid = 1910
[/quote]
I don't see any other inserts or updates to the ReportGroup table near this query.
I looked at the ReportGroup table on both master & slave... they are identical. This is curious, but I can't explain it.
This turned out to be a MySQL replication issue. When we resolved it, MySql Master-Master replication of the Yellowfin Configuration DB seems to be working well.
Thanks for letting us know!
Please let us know if you run into any other issues.
Regards,
David
Please let us know if you run into any other issues.
Regards,
David
What was the root cause of the MySQL replication? We are facing something similar. Would be great to know how you fixed it.