MySQL Bugs: #101446: dba.createReplicaSet does not specify storage engine for CREATE TABLE

Bug #101446	dba.createReplicaSet does not specify storage engine for CREATE TABLE
Submitted:	3 Nov 2020 21:55	Modified:	8 Mar 2021 14:19
Reporter:	Sveta Smirnova (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Shell AdminAPI InnoDB Cluster / ReplicaSet	Severity:	S3 (Non-critical)
Version:	8.0.22	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
dba.createReplicaSet does not specify storage engine for CREATE TABLE which may lead to errors like "Dba.createReplicaSet: Specified key was too long; max key length is 1000 bytes (RuntimeError)" if default storage engine is not InnoDB

How to repeat:
1. Start 3 servers inside MTR:

./mtr --start --mysqld=--master-info-repository=table --mysqld=--relay-log-info-repository=table --suite=rpl rpl_row_img_eng_min --mysqld=--enforce-gtid-consistency --mysqld=--gtid_mode=ON --mysqld=--log-slave-updates --mysqld=--binlog-format=row --big-test &

Default storage engine for MTR is MyISAM even in version 8.0

2. Configure ReplicaSet instances:

MySQL 127.0.0.1:13000 ssl JS > dba.configureReplicaSetInstance('root@127.0.0.1:13000', {clusterAdmin: "'repl'@'%'"})
Please provide the password for 'root@127.0.0.1:13000':
Configuring local MySQL instance listening at port 13000 for use in an InnoDB ReplicaSet...

This instance reports its own address as Delly-7390:13000
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
Password for new account: ********
Confirm password: ********

The instance 'Delly-7390:13000' is valid to be used in an InnoDB ReplicaSet.
Cluster admin user 'repl'@'%' created.
The instance 'Delly-7390:13000' is already ready to be used in an InnoDB ReplicaSet.
MySQL 127.0.0.1:13000 ssl JS > dba.configureReplicaSetInstance('root@127.0.0.1:13002', {clusterAdmin: "'repl'@'%'"})
Please provide the password for 'root@127.0.0.1:13002':
Configuring local MySQL instance listening at port 13002 for use in an InnoDB ReplicaSet...

This instance reports its own address as Delly-7390:13002
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
Password for new account: ********
Confirm password: ********

The instance 'Delly-7390:13002' is valid to be used in an InnoDB ReplicaSet.
Cluster admin user 'repl'@'%' created.
The instance 'Delly-7390:13002' is already ready to be used in an InnoDB ReplicaSet.
MySQL 127.0.0.1:13000 ssl JS > dba.configureReplicaSetInstance('root@127.0.0.1:13004', {clusterAdmin: "'repl'@'%'"})
Please provide the password for 'root@127.0.0.1:13004':
Configuring local MySQL instance listening at port 13004 for use in an InnoDB ReplicaSet...

This instance reports its own address as Delly-7390:13004
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
Password for new account: ********
Confirm password: ********

The instance 'Delly-7390:13004' is valid to be used in an InnoDB ReplicaSet.
Cluster admin user 'repl'@'%' created.
The instance 'Delly-7390:13004' is already ready to be used in an InnoDB ReplicaSet.

So far, so good.

3. Create a ReplicaSet:

MySQL 127.0.0.1:13000 ssl JS > var rs = dba.createReplicaSet("bug")
A new replicaset with instance 'Delly-7390:13000' will be created.

* Checking MySQL instance at Delly-7390:13000

This instance reports its own address as Delly-7390:13000
Delly-7390:13000: Instance configuration is suitable.

* Updating metadata...
ERROR: Failed to update the metadata. Please fix the issue and drop the metadata using dba.dropMetadataSchema() before retrying to execute the operation.
Dba.createReplicaSet: Specified key was too long; max key length is 1000 bytes (RuntimeError)

====
After checking general query log I found that error happened when MySQL Shell tried to create table routers.

Suggested fix:
Add ENGINE clause for CREATE TABLE statements.

Hello Sveta,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh

-- 8.0.22

bin/mysqlsh --log-level=debug3
MySQL Shell 8.0.22

Copyright (c) 2016, 2020, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
MySQL JS > \c root@localhost:13000
Creating a session to 'root@localhost:13000'
Please provide the password for 'root@localhost:13000':
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 9
Server version: 8.0.22 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
MySQL localhost:13000 ssl JS >
MySQL localhost:13000 ssl JS > dba.configureReplicaSetInstance('root@127.0.0.1:13000', {clusterAdmin: "'repl'@'%'"})
Please provide the password for 'root@127.0.0.1:13000':
Configuring local MySQL instance listening at port 13000 for use in an InnoDB ReplicaSet...

This instance reports its own address as support-cluster01:13000
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
Password for new account:
Confirm password:

The instance 'support-cluster01:13000' is valid to be used in an InnoDB ReplicaSet.
Cluster admin user 'repl'@'%' created.
The instance 'support-cluster01:13000' is already ready to be used in an InnoDB ReplicaSet.
MySQL localhost:13000 ssl JS >
MySQL localhost:13000 ssl JS > dba.configureReplicaSetInstance('root@127.0.0.1:13002', {clusterAdmin: "'repl'@'%'"})
Please provide the password for 'root@127.0.0.1:13002':
Configuring local MySQL instance listening at port 13002 for use in an InnoDB ReplicaSet...

This instance reports its own address as support-cluster01:13002
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
Password for new account:
Confirm password:

The instance 'support-cluster01:13002' is valid to be used in an InnoDB ReplicaSet.
Cluster admin user 'repl'@'%' created.
The instance 'support-cluster01:13002' is already ready to be used in an InnoDB ReplicaSet.
MySQL localhost:13000 ssl JS >
MySQL localhost:13000 ssl JS > dba.configureReplicaSetInstance('root@127.0.0.1:13004', {clusterAdmin: "'repl'@'%'"})
Please provide the password for 'root@127.0.0.1:13004':
Configuring local MySQL instance listening at port 13004 for use in an InnoDB ReplicaSet...

This instance reports its own address as support-cluster01:13004
Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed.
Password for new account:
Confirm password:

The instance 'support-cluster01:13004' is valid to be used in an InnoDB ReplicaSet.
Cluster admin user 'repl'@'%' created.
The instance 'support-cluster01:13004' is already ready to be used in an InnoDB ReplicaSet.
MySQL localhost:13000 ssl JS >
MySQL localhost:13000 ssl JS >
MySQL localhost:13000 ssl JS > var rs = dba.createReplicaSet("bug")
A new replicaset with instance 'support-cluster01:13000' will be created.

* Checking MySQL instance at support-cluster01:13000

This instance reports its own address as support-cluster01:13000
support-cluster01:13000: Instance configuration is suitable.

- Excerpt from mysqlsh log

2020-11-04 05:45:05: Debug: Metadata operations will use support-cluster01:13000
2020-11-04 05:45:05: Debug: * Checking MySQL instance at support-cluster01:13000
2020-11-04 05:45:05: Debug: Target has report_host=NULL
2020-11-04 05:45:05: Debug: Target has hostname=support-cluster01
2020-11-04 05:45:05: Debug: This instance reports its own address as support-cluster01:13000
2020-11-04 05:45:05: Info: Validating InnoDB page size of instance 'support-cluster01:13000'.
2020-11-04 05:45:05: Info: Checking if performance_schema is enabled on instance 'support-cluster01:13000'.
2020-11-04 05:45:05: Info: Validating configuration of support-cluster01:13000 (mycnf = )
2020-11-04 05:45:05: Debug: Checking if 'server_id' is compatible.
2020-11-04 05:45:05: Debug: OK: 'server_id' value '1' is compatible.
2020-11-04 05:45:05: Debug: Checking if 'log_bin' is compatible.
2020-11-04 05:45:05: Debug: OK: 'log_bin' value 'ON' is compatible.
2020-11-04 05:45:05: Debug: Checking if 'binlog_format' is compatible with InnoDB Cluster.
2020-11-04 05:45:05: Debug: OK: 'binlog_format' value 'ROW' is compatible.
2020-11-04 05:45:05: Debug: Checking if 'log_slave_updates' is compatible with InnoDB Cluster.
2020-11-04 05:45:05: Debug: OK: 'log_slave_updates' value 'ON' is compatible.
2020-11-04 05:45:05: Debug: Checking if 'enforce_gtid_consistency' is compatible with InnoDB Cluster.
2020-11-04 05:45:05: Debug: OK: 'enforce_gtid_consistency' value 'ON' is compatible.
2020-11-04 05:45:05: Debug: Checking if 'gtid_mode' is compatible with InnoDB Cluster.
2020-11-04 05:45:05: Debug: OK: 'gtid_mode' value 'ON' is compatible.
2020-11-04 05:45:05: Debug: Checking if 'master_info_repository' is compatible with InnoDB Cluster.
2020-11-04 05:45:05: Debug: OK: 'master_info_repository' value 'TABLE' is compatible.
2020-11-04 05:45:05: Debug: Checking if 'relay_log_info_repository' is compatible with InnoDB Cluster.
2020-11-04 05:45:05: Debug: OK: 'relay_log_info_repository' value 'TABLE' is compatible.
2020-11-04 05:45:05: Debug: Checking if 'report_port' is compatible with InnoDB Cluster.
2020-11-04 05:45:05: Debug: OK: 'report_port' value '13000' is compatible.
2020-11-04 05:45:05: Debug: Check command returned: {"status": "ok"}
2020-11-04 05:45:05: Debug: support-cluster01:13000: Instance configuration is suitable.
2020-11-04 05:45:05: Error: Error while querying for group_replication info: Unknown system variable 'group_replication_group_name'
2020-11-04 05:45:05: Info: Unfencing PRIMARY support-cluster01:13000
2020-11-04 05:45:05: Info: Dropping account mysql_innodb_rs_1@% at support-cluster01:13000
2020-11-04 05:45:05: Info: Creating replication user mysql_innodb_rs_1@% with random password at support-cluster01:13000
2020-11-04 05:45:05: Debug: * Updating metadata...
2020-11-04 05:45:05: Info: Deploying metadata schema in support-cluster01:13000...
2020-11-04 05:45:06: Error: Failed to update the metadata. Please fix the issue and drop the metadata using dba.dropMetadataSchema() before retrying to execute the operation.
2020-11-04 05:45:06: Debug: Releasing locks for 'AdminAPI_instance' on support-cluster01:13000.
2020-11-04 05:45:06: Debug: Dba.createReplicaSet: Specified key was too long; max key length is 1000 bytes (RuntimeError)

Thank you for your feedback, this has been fixed in upcoming versions and the following was added to the 8.0.24 change log:

When you issue dba.createCluster() and dba.createReplica(), tables are created to store the metadata. If the default storage engine was not InnoDB, these operations could fail. Now, metadata creation operations always use the InnoDB storage engine.