MySQL Bugs: #74694: MySQL fabric - Error after setting server status to SPARE and then SECONDARY

Bug #74694	MySQL fabric - Error after setting server status to SPARE and then SECONDARY
Submitted:	5 Nov 2014 4:17	Modified:	1 Jan 2015 13:11
Reporter:	Mahesh Patil	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Fabric	Severity:	S1 (Critical)
Version:	1.5.2	OS:	Linux (Centos 6.3)
Assigned to:		CPU Architecture:	Any

Description:
After stopping secondary server manually. I changed status to SPARE and then SECONDARY and checked Health shows: 

$ mysqlfabric group health group1

Error 'Operation CREATE USER failed for 'replication'@'%'' on query. Default database: ''. Query: 'CREATE USER 'replication'@'%' IDENTIFIED BY PASSWORD '*D36660B5249B066D7AC5A1A14CECB71D36944CBC''
bf2225d7-58e7-11e4-8bea-0050569e5fd9      

How to repeat:
1. Stop SECONDARY server
2. Check Health 
3. Change server status to SPARE
4. Change server status to SECONDARY
5. Check Health 

Throws SQL error 

'Operation CREATE USER failed for 'replication'@'%'' on query. Default database: ''. Query: 'CREATE USER 'replication'@'%' IDENTIFIED BY PASSWORD '*D36660B5249B066D7AC5A1A14CECB71D36944CBC''
bf2225d7-58e7-11e4-8bea-0050569e5fd9      

Suggested fix:
From my point of view it should not throw any error and Health status should return "Success" result. Isn't it ?

Hi Mahesh,

The error occurs because the servers are out of sync for some reason. It is not clear how you stopped the server, so could you provide information on how you stopped the server?

I have just executed 
Sudo service mysql stop on master(PRIMARY) server.

I was testing whether can I get original setup as expected after PRIMARY goes down
Basically replication coordinates should get change and fabric should know the position when it was broken to get the original state back to functional.

I hope its clear to you , if not I can provide more details tomorrow on this.

Hi Mahesh,

I think I have a guess at what the problem is, but I would like to have some more information. It is likely not related to MySQL Fabric, but rather to how replication works.

What happen is that you have the CREATE USER statement in the binary log on the master, which is replicated to the slave. From the looks of the statement, it seems like it is part of a configuration procedure where you add the user to all servers while having the binary log enabled. Since these statement will be assigned different GTIDs, there is no way for the replication code to distinguish between them.

Could you please add the following to the bug report:

1. An excerpt of the binary logs of both the servers (the original primary and promoted secondary that is now a primary) containing the CREATE USER statement. The important information here is to see if the CREATE USER was assigned a GTID and in that case what GTID.

2. A description of how you create your empty servers before adding them to Fabric. At this point, the binary log should be empty.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".