remove init_connect part error handling for dual-master fail-over case

[hickey]

Hi Guys,

init_connect is useful for access audit, but for dual-master(M1-M2)
case which M1 is read/write but M2 is read_only, once fail-over
happened, the paint comes.

For failover case, M1 is readonly and M2 is read/write, but M1's
init_connect is not cleaned (if M1 is crashed and restarted, it's
cleared).
That case really happended for some scenarios, such as M1's workload
is suddendly increased but not crashed, for the the sake of online
production's stability, master-slave switch is taken.

After failover, M1 is readonly but init_connect is still exist. For
all connections, they could still connect successfully but failed to
do any command.
Let's have a look for the example:

#1 create accesslog
CREATE TABLE `accesslog` (
`id` int(11) NOT NULL,
`check_time` datetime DEFAULT NULL,
`localname` varchar(60) DEFAULT NULL,
`matchname` varchar(60) DEFAULT NULL,
`default_database` varchar(60) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk
#2 set init_connect
set global init_connect = 'insert into test.accesslog
values(connection_id(),now(),user(),current_user(),database());'
#3 grant non-super user
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'ac'@'%' IDENTIFIED BY
PASSWORD 'ac';
#4 login with ac twice, and check the result
root@test 04:35:13>select * from accesslog;
+----+---------------------+-----------------+-----------
+------------------+
| id | check_time | localname | matchname |
default_database |
+----+---------------------+-----------------+-----------
+------------------+
| 4 | 2012-05-07 16:34:46 | a...@10.232.xx.yy | ac@% |
mysql |
| 6 | 2012-05-07 16:34:56 | a...@10.232.xx.yy | ac@% |
test |
+----+---------------------+-----------------+-----------
+------------------+
2 rows in set (0.00 sec)
#5 set read_only=1
set global read_only=1;
#6 login with ac and do select 1
mysql [localhost] {ac} ((none)) > select 1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 11139
Current database: *** NONE ***
ERROR 2013 (HY000): Lost connection to MySQL server during query


The solution is easy, just remove the line below in
prepare_new_connection_state of sql/sql_connect.cc :
thd->killed= THD::KILL_CONNECTION;
As a result, the connection is still 'usable', not abort any more.

Any body suffered the paint as we are feeling? And what's yours
solution?
Wish any comments.

regards,
hickey

[Hui Liu]

Hi Guys,

init_connect is useful for access audit, but for dual-master(M1-M2) case which M1 is read/write but M2 is read_only, once fail-over happened, the paint comes.

For failover case, M1 is readonly and M2 is read/write, but M1's init_connect is not cleaned (if M1 is crashed and restarted, it's cleared).

That case really happended for some scenarios, such as M1's workload is suddendly increased but not crashed, for the the sake of online production's stability, master-slave switch is taken.

After failover, M1 is readonly but init_connect is still exist. For all connections, they could still connect successfully but failed to do any command.

Let's have a look for the example:

#1 create accesslog
CREATE TABLE `accesslog` (
  `id` int(11) NOT NULL,
  `check_time` datetime DEFAULT NULL,
  `localname` varchar(60) DEFAULT NULL,
  `matchname` varchar(60) DEFAULT NULL,
  `default_database` varchar(60) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk  

#2 set init_connect
set global init_connect = 'insert into test.accesslog values(connection_id(),now(),user(),current_user(),database());'
#3 grant non-super user
 GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'ac'@'%' IDENTIFIED BY PASSWORD 'ac';
 
#4 login with ac twice, and check the result
root@test 04:35:13>select * from accesslog;
+----+---------------------+-----------------+-----------+------------------+
| id | check_time          | localname       | matchname | default_database |
+----+---------------------+-----------------+-----------+------------------+

|  4 | 2012-05-07 16:34:46 | a...@10.232.31.44 | ac@%      | mysql            |
|  6 | 2012-05-07 16:34:56 | a...@10.232.31.44 | ac@%      | test             |
+----+---------------------+-----------------+-----------+------------------+

2 rows in set (0.00 sec)
#5 set read_only=1
set global read_only=1;
#6 login with ac and do select 1
mysql [localhost] {ac} ((none)) > select 1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    11139
Current database: *** NONE ***
ERROR 2013 (HY000): Lost connection to MySQL server during query