Description:
MySQLD nodes should automatically re-connect when a cluster is shut down and then restarted. (keeping the MySQLD node alive during shutdown/restart).
I have found that this automatic reconnect only works when no table is created, or no data is added, making it pretty useless - you have to shutdown the sql node anyway after you modify anything.
How to repeat:
Bring up the management and data nodes (--initial if you must). Start the SQL Node. Connect with the mysql client (but don't do anything just yet). SHOW indicates all is working fine:
ndb_mgm> show
Connected to Management Server at: 127.0.0.1:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @127.0.0.1 (Version: 5.1.14, Nodegroup: 0, Master)
id=3 @127.0.0.1 (Version: 5.1.14, Nodegroup: 0)
[ndb_mgmd(MGM)] 2 node(s)
id=1 @127.0.0.1 (Version: 5.1.14)
id=9 @127.0.0.1 (Version: 5.1.14)
[mysqld(API)] 3 node(s)
id=4 @127.0.0.1 (Version: 5.1.14)
id=5 (not connected, accepting connect from any host)
id=6 (not connected, accepting connect from any host)
Create a table:
use test;
create table t(
id int unsigned auto_increment primary key
, name char(41)
)engine=ndb;
Then, shut down the cluster using the SHUTDOWN command in the management client. The management node and data nodes shutdown. (keep mysqld and mysql client running). Restart the management node and data nodes (of course, without --initial). A SHOW proves the SQL node does *not* reconnect successfully:
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @127.0.0.1 (Version: 5.1.14, Nodegroup: 0, Master)
id=3 @127.0.0.1 (Version: 5.1.14, Nodegroup: 0)
[ndb_mgmd(MGM)] 2 node(s)
id=1 @127.0.0.1 (Version: 5.1.14)
id=9 @127.0.0.1 (Version: 5.1.14)
[mysqld(API)] 3 node(s)
id=4 (not connected, accepting connect from 127.0.0.1)
id=5 (not connected, accepting connect from any host)
id=6 (not connected, accepting connect from any host)
Shutdown and restart the sql node. It connects, and SHOW gives the original output again (node 4 connected).
Shut down the cluster again using the SHUTDOWN command in the management client. The management node and data nodes shutdown. (keep mysqld and mysql client running) Restart the management node and data nodes (of course, without --initial). A SHOW proves the SQL node now does reconnect successfully (we have the original output again).
Now, in the management client, insert a row into the table we just created:
insert into t(name) values (sha(rand()));
The insert succeeds. Now, Shutdown the cluster again, but keep the SQL node and command client running. Bring the cluster back up, and notice that yet again, the SQL node does *not* reconnect automatically:
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2 @127.0.0.1 (Version: 5.1.14, Nodegroup: 0, Master)
id=3 @127.0.0.1 (Version: 5.1.14, Nodegroup: 0)
[ndb_mgmd(MGM)] 2 node(s)
id=1 @127.0.0.1 (Version: 5.1.14)
id=9 @127.0.0.1 (Version: 5.1.14)
[mysqld(API)] 3 node(s)
id=4 (not connected, accepting connect from 127.0.0.1)
id=5 (not connected, accepting connect from any host)
id=6 (not connected, accepting connect from any host)
So,
Suggested fix:
Well, make the SQL node reconnect regardless.
Description: MySQLD nodes should automatically re-connect when a cluster is shut down and then restarted. (keeping the MySQLD node alive during shutdown/restart). I have found that this automatic reconnect only works when no table is created, or no data is added, making it pretty useless - you have to shutdown the sql node anyway after you modify anything. How to repeat: Bring up the management and data nodes (--initial if you must). Start the SQL Node. Connect with the mysql client (but don't do anything just yet). SHOW indicates all is working fine: ndb_mgm> show Connected to Management Server at: 127.0.0.1:1186 Cluster Configuration --------------------- [ndbd(NDB)] 2 node(s) id=2 @127.0.0.1 (Version: 5.1.14, Nodegroup: 0, Master) id=3 @127.0.0.1 (Version: 5.1.14, Nodegroup: 0) [ndb_mgmd(MGM)] 2 node(s) id=1 @127.0.0.1 (Version: 5.1.14) id=9 @127.0.0.1 (Version: 5.1.14) [mysqld(API)] 3 node(s) id=4 @127.0.0.1 (Version: 5.1.14) id=5 (not connected, accepting connect from any host) id=6 (not connected, accepting connect from any host) Create a table: use test; create table t( id int unsigned auto_increment primary key , name char(41) )engine=ndb; Then, shut down the cluster using the SHUTDOWN command in the management client. The management node and data nodes shutdown. (keep mysqld and mysql client running). Restart the management node and data nodes (of course, without --initial). A SHOW proves the SQL node does *not* reconnect successfully: Connected to Management Server at: localhost:1186 Cluster Configuration --------------------- [ndbd(NDB)] 2 node(s) id=2 @127.0.0.1 (Version: 5.1.14, Nodegroup: 0, Master) id=3 @127.0.0.1 (Version: 5.1.14, Nodegroup: 0) [ndb_mgmd(MGM)] 2 node(s) id=1 @127.0.0.1 (Version: 5.1.14) id=9 @127.0.0.1 (Version: 5.1.14) [mysqld(API)] 3 node(s) id=4 (not connected, accepting connect from 127.0.0.1) id=5 (not connected, accepting connect from any host) id=6 (not connected, accepting connect from any host) Shutdown and restart the sql node. It connects, and SHOW gives the original output again (node 4 connected). Shut down the cluster again using the SHUTDOWN command in the management client. The management node and data nodes shutdown. (keep mysqld and mysql client running) Restart the management node and data nodes (of course, without --initial). A SHOW proves the SQL node now does reconnect successfully (we have the original output again). Now, in the management client, insert a row into the table we just created: insert into t(name) values (sha(rand())); The insert succeeds. Now, Shutdown the cluster again, but keep the SQL node and command client running. Bring the cluster back up, and notice that yet again, the SQL node does *not* reconnect automatically: Cluster Configuration --------------------- [ndbd(NDB)] 2 node(s) id=2 @127.0.0.1 (Version: 5.1.14, Nodegroup: 0, Master) id=3 @127.0.0.1 (Version: 5.1.14, Nodegroup: 0) [ndb_mgmd(MGM)] 2 node(s) id=1 @127.0.0.1 (Version: 5.1.14) id=9 @127.0.0.1 (Version: 5.1.14) [mysqld(API)] 3 node(s) id=4 (not connected, accepting connect from 127.0.0.1) id=5 (not connected, accepting connect from any host) id=6 (not connected, accepting connect from any host) So, Suggested fix: Well, make the SQL node reconnect regardless.