| Bug #115033 | Running util.debug.collectDiagnostics w/ schemaStats:true get MySQL Error 1242 | ||
|---|---|---|---|
| Submitted: | 16 May 9:12 | Modified: | 19 Jul 14:43 |
| Reporter: | Keith Hollman | Email Updates: | |
| Status: | Closed | Impact on me: | |
| 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: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.
[19 Jul 14:43]
Edward Gilmore
Posted by developer: Added the following note to the MySQL Shell 8.4.3 and 9.1.0 release notes: Running the diagnostic utility util.debug.collectDiagnostics with the parameter schemaStats:true returned MySQL Error 1242 if a table had more than one column of type BLOB.
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.