Bug #101446 dba.createReplicaSet does not specify storage engine for CREATE TABLE
Submitted: 3 Nov 2020 21:55 Modified: 8 Mar 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

[3 Nov 2020 21:55] Sveta Smirnova
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.
[4 Nov 2020 5:48] MySQL Verification Team
Hello Sveta,

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

regards,
Umesh
[4 Nov 2020 5:49] MySQL Verification Team
-- 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.

* 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)
[4 Nov 2020 5:51] MySQL Verification Team
- 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)
[8 Mar 14:19] David Moss
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.