Bug #115033 Running util.debug.collectDiagnostics w/ schemaStats:true get MySQL Error 1242
Submitted: 16 May 9:12 Modified: 17 May 20:47
Reporter: Keith Hollman Email Updates:
Status: Verified Impact on me:
None 
Category:Shell Dump & Load Severity:S3 (Non-critical)
Version:8.0.37 OS:Red Hat (4.18.0-513.24.1.el8_9.x86_64)
Assigned to: CPU Architecture:x86
Tags: mysqlsh, schemaStats, util.debug.collectDiagnostics

[16 May 9:12] Keith Hollman
Description:
mysqlsh 8.0.36
mysqld 8.0.36

Running either of the following:
mysqlsh --login-path=icadmin -hdbgsc-d-rtdb-01 -- util debug collectDiagnostics "/tmp/diag2" --allMembers=false --schemaStats=true --slowQueries=false

util.debug.collectDiagnostics("/tmp/diag3", {schemaStats:true})

I get the following:

Collecting diagnostics information from mysql://icadmin@dbgsc-d-rtdb-01:3306...
InnoDB Cluster detected
Dumping mysql_innodb_cluster_metadata schema...
Copying shell log file...
Collecting Schema Information and Statistics
 - Gathering schema tables without a PK...
 - Gathering schema routine size...
 - Gathering schema table count...
 - Gathering schema unused indexes...
 - Gathering schema object overview...
 - Gathering schema top biggest tables...
ERROR: While executing "select t.table_schema, t.table_name, t.row_format, t.table_rows, t.avg_row_length, t.data_length, t.max_data_length, t.index_length, t.table_collation,
        json_objectagg(idx.index_name, json_object('columns', idx.col, 'type', idx.index_type, 'cardinality', idx.cardinality)) indexes,
        group_concat((select concat(c.column_name, ':', c.column_type)
          from information_schema.columns c
          where c.table_schema = t.table_schema and c.table_name = t.table_name and c.column_type in ('blob'))) blobs
    from information_schema.tables t
    join (select s.table_schema, s.table_name, s.index_name, s.index_type, s.cardinality, json_arrayagg(concat(c.column_name, ':', c.column_type)) col
          from information_schema.statistics s left join information_schema.columns c on s.table_schema=c.table_schema and s.table_name=c.table_name and s.column_name=c.column_name
          group by s.table_schema, s.table_name, s.index_name, s.index_type, s.cardinality
          order by s.table_schema, s.table_name, s.index_name, s.index_type, s.cardinality) idx
    on idx.table_schema=t.table_schema and idx.table_name = t.table_name
    where t.table_type = 'BASE TABLE' and t.table_schema not in ('mysql', 'information_schema', 'performance_schema')
    group by t.table_schema, t.table_name, t.engine, t.row_format, t.table_rows, t.avg_row_length, t.data_length, t.max_data_length, t.index_length, t.table_collation
    order by t.data_length desc limit 20"

MySQL Error (1242): ClassicResult.fetch_one: Subquery returns more than 1 row

An error occurred during data collection. Partial output deleted.
debug.collectDiagnostics: ClassicResult.fetch_one: Subquery returns more than 1 row (MySQL Error 1242)

The subquery:
select s.table_schema, s.table_name, s.index_name, s.index_type, s.cardinality, json_arrayagg(concat(c.column_name, ':', c.column_type)) col
from information_schema.statistics s left join information_schema.columns c on s.table_schema=c.table_schema and s.table_name=c.table_name and s.column_name=c.column_name
group by s.table_schema, s.table_name, s.index_name, s.index_type, s.cardinality
order by s.table_schema, s.table_name, s.index_name, s.index_type, s.cardinality

is giving more than 1 row per "col":
select count(*) from information_schema.statistics s
 left join information_schema.columns c on s.table_schema=c.table_schema
 and s.table_name=c.table_name
 and s.column_name=c.column_name
group by s.table_schema, s.table_name, s.index_name, s.index_type, s.cardinality
order by s.table_schema, s.table_name, s.index_name, s.index_type, s.cardinality;

..
...
|   1 |
|   1 |
|   1 |
|   1 |
|   2 |
|   1 |
|   1 |
|   1 |
|   1 |
+-----+
99756 rows in set (4.0589 sec)

select count(*) from information_schema.statistics s
 left join information_schema.columns c on s.table_schema=c.table_schema
 and s.table_name=c.table_name
 and s.column_name=c.column_name 
group by s.table_schema, s.table_name, s.index_name, s.index_type, s.cardinality 
having count(*) >1
order by s.table_schema, s.table_name, s.index_name, s.index_type, s.cardinality ;

..
...
|        4 |
|        2 |
|        2 |
|        3 |
|        2 |
|        2 |
|        2 |
|        2 |
|        2 |
|        2 |
|        2 |
|        4 |
|        2 |
|        2 |
|        2 |
|        2 |
|        2 |
|        2 |
|        2 |
+----------+
12020 rows in set (6.1436 sec)

How to repeat:
Using 8.0.36 for both mysqlsh & mysqld run:
Connected to either Router or mysqld gives the same result.

mysqlsh --login-path=icadmin -hhostname -- util debug collectDiagnostics "/tmp/diag2" --schemaStats=true
or
util.debug.collectDiagnostics("/tmp/diag3", {schemaStats:true})

In comparison, running:
util.debug.collectDiagnostics("/tmp/diag3", {allMembers:true, schemaStats:false, slowQueries:true})

works fine.

Suggested fix:
Correct the subquery to allow for more than 1 row, please.
[16 May 9:19] Keith Hollman
Also tested mysqlsh 8.0.37 and reproduces:
[root@dbgsc-d-db-01 tmp]# mysqlsh --login-path=icadmin -hdbgsc-d-db-01 -- util debug collectDiagnostics "/tmp/diag4" --schemaStats=true
Collecting diagnostics information from mysql://icadmin@dbgsc-d-db-01:3306...
InnoDB Cluster detected
Dumping mysql_innodb_cluster_metadata schema...
Copying shell log file...
Collecting Schema Information and Statistics
 - Gathering schema tables without a PK...
 - Gathering schema routine size...
 - Gathering schema table count...
 - Gathering schema unused indexes...
 - Gathering schema object overview...
 - Gathering schema top biggest tables...
ERROR: While executing "select t.table_schema, t.table_name, t.row_format, t.table_rows, t.avg_row_length, t.data_length, t.max_data_length, t.index_length, t.table_collation,
        json_objectagg(idx.index_name, json_object('columns', idx.col, 'type', idx.index_type, 'cardinality', idx.cardinality)) indexes,
        group_concat((select concat(c.column_name, ':', c.column_type)
          from information_schema.columns c
          where c.table_schema = t.table_schema and c.table_name = t.table_name and c.column_type in ('blob'))) blobs
    from information_schema.tables t
    join (select s.table_schema, s.table_name, s.index_name, s.index_type, s.cardinality, json_arrayagg(concat(c.column_name, ':', c.column_type)) col
          from information_schema.statistics s left join information_schema.columns c on s.table_schema=c.table_schema and s.table_name=c.table_name and s.column_name=c.column_name
          group by s.table_schema, s.table_name, s.index_name, s.index_type, s.cardinality
          order by s.table_schema, s.table_name, s.index_name, s.index_type, s.cardinality) idx
    on idx.table_schema=t.table_schema and idx.table_name = t.table_name
    where t.table_type = 'BASE TABLE' and t.table_schema not in ('mysql', 'information_schema', 'performance_schema')
    group by t.table_schema, t.table_name, t.engine, t.row_format, t.table_rows, t.avg_row_length, t.data_length, t.max_data_length, t.index_length, t.table_collation
    order by t.data_length desc limit 20": MySQL Error (1242): ClassicResult.fetch_one: Subquery returns more than 1 row

An error occurred during data collection. Partial output deleted.
ERROR: ClassicResult.fetch_one: Subquery returns more than 1 row

[root@dbgsc-d-db-01 tmp]# mysqlsh --version
mysqlsh   Ver 8.0.37 for Linux on x86_64 - for MySQL 8.0.37 (MySQL Community Server (GPL))
[17 May 20:47] MySQL Verification Team
Hi,

Thank you for the report.