MySQL Forums
Forum List  »  InnoDB clusters

Re: InnoDB Cluster: Navigating the Cluster
Posted by: Simon Cruise
Date: February 09, 2017 04:23AM

Using mysqlsh to manage the cluster is great until you have an event where the entire cluster shuts down and you need to bring it back online.

My current environment has 3 sandbox mysql instances behind mysql router configured in a cluster called "mycluster" and everything works great when a node is shutdown / killed and when it comes back up it can be easily re-introduced. This is all because via the mysqlsh with an active connection to the current master you have configuration options.

However when everything is shutdown and brought back up the following is seen on all nodes.

mysql-js> dba.getCluster("mycluster");
Dba.getCluster: This function is not available through a session to a standalone instance (RuntimeError)

So in my mind it seemed as though the cluster is torn down in the event of a complete failure and it is up to an administrator to bring it back online by recreating it.

However we see the following :

mysql-js> dba.createCluster("mycluster");
A new InnoDB cluster will be created on instance 'root@192.168.122.62:3310'.

Creating InnoDB cluster 'mycluster' on 'root@192.168.122.62:3310'...
Dba.createCluster: A Cluster with the name 'mycluster' already exists. (ArgumentError)

Okay so maybe we could create a cluster of a different name?


mysql-js> dba.createCluster("mycluster2");
A new InnoDB cluster will be created on instance 'root@192.168.122.62:3310'.

Creating InnoDB cluster 'mycluster2' on 'root@192.168.122.62:3310'...
Dba.createCluster: ERROR: 1 table(s) do not have a Primary Key
ERROR: Error executing the 'start-replicaset' command: The operation could not continue due to the following requirements not being met:
Non-compatible tables found in database. (RuntimeError)

Here are the logs from the above commands on the host that was the primary before I restarted the VM hosting all instances.

2017-02-09 10:17:46: Info: Joining '192.168.122.62:3310' to group using account root@192.168.122.62:3310
2017-02-09 10:17:46: Info: DBA: mysqlprovision: Executing /usr/bin/mysqlprovision start-replicaset --instance=root@192.168.122.62:3310 --replication-user=mysql_innodb_cluster_r1725026912@'%' --skip-ssl --stdin --log-format=json -xV 1.0
2017-02-09 10:17:47: Error: DBA: mysqlprovision exited with error code (1) : {"type": "INFO", "msg": "", "time": "2017-02-09 10:17:46 AM"}{"type": "STEP", "msg": "Running start command on '192.168.122.62@3310'.", "time": "2017-02-09 10:17:46 AM"}{"type": "STEP", "msg": "Checking Group Replication prerequisites.", "time": "2017-02-09 10:17:46 AM"}{"type": "INFO", "msg": "* Comparing options compatibility with Group Replication... PASS", "time": "2017-02-09 10:17:46 AM"}{"type": "INFO", "msg": "Server configuration is compliant with the requirements.", "time": "2017-02-09 10:17:46 AM"}{"type": "INFO", "msg": "* Checking server version... PASS", "time": "2017-02-09 10:17:46 AM"}{"type": "INFO", "msg": "Server is 5.7.17", "time": "2017-02-09 10:17:46 AM"}{"type": "INFO", "msg": "", "time": "2017-02-09 10:17:46 AM"}{"type": "INFO", "msg": "* Checking that server_id is unique... PASS", "time": "2017-02-09 10:17:46 AM"}{"type": "INFO", "msg": "The server_id is valid.", "time": "2017-02-09 10:17:46 AM"}{"type": "INFO", "msg": "", "time": "2017-02-09 10:17:46 AM"}{"type": "INFO", "msg": "", "time": "2017-02-09 10:17:46 AM"}{"type": "INFO", "msg": "The user root@'192.168.122.62' does not exists on '192.168.122.62@3310' and requires to be created.", "time": "2017-02-09 10:17:46 AM"}{"type": "INFO", "msg": "* Checking user privileges... PASS", "time": "2017-02-09 10:17:46 AM"}{"type": "INFO", "msg": "* Checking compliance of existing tables... FAIL", "time": "2017-02-09 10:17:47 AM"}{"type": "ERROR", "msg": "1 table(s) do not have a Primary Key", "time": "2017-02-09 10:17:47 AM"}{"type": "INFO", "msg": " VERSION_4_1_0_0.user_partner_portfolio", "time": "2017-02-09 10:17:47 AM"}{"type": "INFO", "msg": "", "time": "2017-02-09 10:17:47 AM"}{"type": "INFO", "msg": "Group Replication requires tables to use InnoDB and have a PRIMARY key. Tables that do not follow these requirements will be readable but not updateable when used with Group Replication. If your applications make updates (INSERT, UPDATE or DELETE) to these tables, ensure they use the InnoDB storage engine and have a PRIMARY KEY.", "time": "2017-02-09 10:17:47 AM"}{"type": "INFO", "msg": "You can retry this command with the --allow-non-compatible-tables option if you'd like to enable Group Replication ignoring this warning.", "time": "2017-02-09 10:17:47 AM"}{"type": "INFO", "msg": "", "time": "2017-02-09 10:17:47 AM"}{"type": "INFO", "msg": "", "time": "2017-02-09 10:17:47 AM"}{"type": "ERROR", "msg": "Error executing the 'start-replicaset' command: The operation could not continue due to the following requirements not being met:
Non-compatible tables found in database.", "time": "2017-02-09 10:17:47 AM"}
2017-02-09 10:17:47: Error: ERROR: 1 table(s) do not have a Primary Key
ERROR: Error executing the 'start-replicaset' command: The operation could not continue due to the following requirements not being met:
Non-compatible tables found in database.
2017-02-09 10:17:47: Error: Dba.createCluster: ERROR: 1 table(s) do not have a Primary Key
ERROR: Error executing the 'start-replicaset' command: The operation could not continue due to the following requirements not being met:
Non-compatible tables found in database.

Now there is a wonderfully named method which is not yet implemented, so my fear currently is that we need to get into removing all evidence of mycluster from the mysql instance before recreating.

mysql-js> dba.rebootClusterFromCompleteOutage('mycluster')
Function not available yet. (LogicError)

I went on to check for any variable set to the value mycluster on the mysqld nodes but there is nothing there.

mysql> pager grep cluster PAGER set to 'grep cluster'
mysql> show variables;
| general_log_file | /home/scruise/mysql-sandboxes/3310/sandboxdata/innodb-cluster.log |
| hostname | innodb-cluster |
| log_bin_basename | /home/scruise/mysql-sandboxes/3310/sandboxdata/innodb-cluster-bin |
| log_bin_index | /home/scruise/mysql-sandboxes/3310/sandboxdata/innodb-cluster-bin.index |
| relay_log_basename | /home/scruise/mysql-sandboxes/3310/sandboxdata/innodb-cluster-relay-bin |
| relay_log_index | /home/scruise/mysql-sandboxes/3310/sandboxdata/innodb-cluster-relay-bin.index |
| slow_query_log_file | /home/scruise/mysql-sandboxes/3310/sandboxdata/innodb-cluster-slow.log |
555 rows in set (0.01 sec)

Does anybody know how it is intended to recover from this situation?

It is also worth pointing out that mysqlrouter also does not continue to work so an application would be forced to change its datasource to a single node.


scruise@innodb-cluster:~$ mysql -uroot -ppassword -h192.168.122.62 -P6446
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to remote MySQL server for client '0.0.0.0:6446'

Options: ReplyQuote


Subject
Views
Written By
Posted
2423
December 27, 2016 11:32PM
Re: InnoDB Cluster: Navigating the Cluster
11701
February 09, 2017 04:23AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.