MySQL Bugs: #75188: MySQLFabric doesn't work properly with Application User that has SUPER privilege

Bug #75188	MySQLFabric doesn't work properly with Application User that has SUPER privilege
Submitted:	12 Dec 2014 2:47	Modified:	15 Dec 2014 13:18
Reporter:	Shinya Sugiyama	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Fabric	Severity:	S3 (Non-critical)
Version:	1.5.3	OS:	Linux (3.10.0-123.el7.x86_64)
Assigned to:		CPU Architecture:	Any
Tags:	mysqlfabric

Description:
If I add a slave, total number of data is same among all data node.
It is fine; however, ......
When I promote one of slave in the group, total number of data became
different.

[ Initial Condition ]
-bash-4.2$ mysqlfabric group lookup_servers global
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode
weight
------------------------------------ --------------- --------- ----------
------
07ac6b6c-80da-11e4-9069-08002766cefe 127.0.0.1:63301   PRIMARY READ_WRITE    
1.0
085c0d07-80da-11e4-9069-08002766cefe 127.0.0.1:63302 SECONDARY  READ_ONLY    
1.0
08e1a534-80da-11e4-9069-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    
1.0
097e5bc1-80da-11e4-9069-08002766cefe 127.0.0.1:63304 SECONDARY  READ_ONLY    
1.0

[ Data is same - Loop Script (insert / select is running behind count(*)]
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63301 -se"select count(*) from
test.employees"
@ Warning: Using a password on the command line interface can be insecure.
count(*)
622
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63302 -se"select count(*) from
test.employees"
@ Warning: Using a password on the command line interface can be insecure.
count(*)
622
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63303 -se"select count(*) from
test.employees"
@ Warning: Using a password on the command line interface can be insecure.
count(*)
622
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63304 -se"select count(*) from
test.employees"
@ Warning: Using a password on the command line interface can be insecure.
count(*)
622

[ If I promote one of slave in this group...  ]
-bash-4.2$ mysqlfabric group promote global
--slave_id=097e5bc1-80da-11e4-9069-08002766cefe
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                                uuid finished success result
------------------------------------ -------- ------- ------
34da8ac5-546b-4ea5-941a-46d9c7b35dba        1       1      1

state success          when                                                  
description
----- ------- -------------
-------------------------------------------------------------
    3       2 1418264078.33 Triggered by <mysql.fabric.events.Event object at
0x179a810>.
    4       2 1418264078.33                      Executing action
(_define_ha_operation).
    5       2 1418264078.37                       Executed action
(_define_ha_operation).
    3       2 1418264078.33 Triggered by <mysql.fabric.events.Event object at
0x1914bd0>.
    4       2 1418264078.37                   Executing action
(_check_candidate_switch).
    5       2 1418264078.43                    Executed action
(_check_candidate_switch).
    3       2 1418264078.38 Triggered by <mysql.fabric.events.Event object at
0x1914c10>.
    4       2 1418264078.43                       Executing action
(_block_write_switch).
    5       2 1418264078.45                        Executed action
(_block_write_switch).
    3       2 1418264078.44 Triggered by <mysql.fabric.events.Event object at
0x1914c50>.
    4       2 1418264078.45                       Executing action
(_wait_slaves_switch).
    5       2 1418264078.49                        Executed action
(_wait_slaves_switch).
    3       2 1418264078.49 Triggered by <mysql.fabric.events.Event object at
0x1914c90>.
    4       2 1418264078.49                      Executing action
(_change_to_candidate).
    5       2 1418264078.79                       Executed action
(_change_to_candidate).

-bash-4.2$ mysqlfabric group lookup_servers global
Fabric UUID:  5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

                         server_uuid         address    status       mode
weight
------------------------------------ --------------- --------- ----------
------
07ac6b6c-80da-11e4-9069-08002766cefe 127.0.0.1:63301 SECONDARY  READ_ONLY    
1.0
085c0d07-80da-11e4-9069-08002766cefe 127.0.0.1:63302 SECONDARY  READ_ONLY    
1.0
08e1a534-80da-11e4-9069-08002766cefe 127.0.0.1:63303 SECONDARY  READ_ONLY    
1.0
097e5bc1-80da-11e4-9069-08002766cefe 127.0.0.1:63304   PRIMARY READ_WRITE    
1.0

-bash-4.2$

[Total number of data count became different even I stop the loop script. ]
Original Master has more data, compare to other node.
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63301 -se"select count(*) from
test.employees"
@ Warning: Using a password on the command line interface can be insecure.
count(*)
1200  <---- It seems original Master database has more data.
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63302 -se"select count(*) from
test.employees"
@ Warning: Using a password on the command line interface can be insecure.
count(*)
1168
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63303 -se"select count(*) from
test.employees"
@ Warning: Using a password on the command line interface can be insecure.
count(*)
1168
-bash-4.2$ mysql -uroot -proot -h127.0.0.1 -P63304 -se"select count(*) from
test.employees"
@ Warning: Using a password on the command line interface can be insecure.
count(*)
1167
-bash-4.2$ 

How to repeat:
1) Run Loop Script for Inser & Select Data to Database.
2) mysqlfabric group promote global
--slave_id=097e5bc1-80da-11e4-9069-08002766cefe
3) Original Master Database contain more data.
   Other node contain same data.

Suggested fix:
Total Number of data should be same even we execute promote command.
mysqlfabric group promote global
--slave_id=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Promote is working fine with Application User without SUPER grants.

Hi Shinya,

Thank you for the bug report. As noted, the problem occurs because you are using a user that have SUPER privileges.

As part of the fail-over and switch-over procedure, the master is set to read-only using the @@READ_ONLY server variable. Unfortunately, this do not prevent users with SUPER privileges from writing, which is causing your problem.

When connecting to a server through a Fabric-aware connector, users that do not have SUPER privileges should be used.

I'm keeping the bug open, but reducing the severity and priority, since it is a usability issue and we need to ensure that it works also if a user with SUPER privileges are used.

Posted by developer:
 
Changing title of bug to better reflect problem description.

Mats-san,

Thank you for your support.
I got it.

Also thank you for your advise, I also change title for this report.

Old:     WHEN SUBMIT MYSQLFABRIC GROUP PROMOTE,TOTAL NUMBER OF DATA BECAME DIFFERENT. 
Changed: MySQLFabric doesn't work properly with Application User that has SUPER privilege.

Best Regard

Status updated to 'Won't fix' (Fabric is now covered under Oracle Lifetime Sustaining Support)