util.checkForServerUpgrade Error

MySQL & MariaDB Percona Server for MySQL 5.7
,
1

Hello,

I’ve got an error when I try to upgrade MySQL 5.7 to MySQL 8.0.

Any idea how can I fix it.

We’re using Debian Bullseye 11.

  1. Schema inconsistencies resulting from file removal or corruption
    Error: Following tables show signs that either table datadir directory or frm
    file was removed/corrupted. Please check server logs, examine datadir to
    detect the issue and fix it before upgrade

I cannot find a deleted frm files.

1 Like
2

What is the MySQL Shell version? Are you running the latest MySQL Shell version? Could you also provide the list of tables reported in this section? Are these system tables or tables from your own database?

1 Like
3

Thats the version.

mysqlsh Ver 8.0.33 for Linux on x86_64 - for MySQL 8.0.33 (MySQL Community Server (GPL))

The tables I suspect are as follows.

SELECT TABLE_SCHEMA, TABLE_NAME  FROM INFORMATION_SCHEMA.TABLES  
WHERE ENGINE='InnoDB'  
AND TABLE_SCHEMA NOT IN ('mysql', 'performance_schema', 'sys') AND 
TABLE_NAME NOT IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE');

# TABLE_SCHEMA, TABLE_NAME
information_schema, COLUMNS
information_schema, EVENTS
information_schema, OPTIMIZER_TRACE
information_schema, PARAMETERS
information_schema, PARTITIONS
information_schema, PLUGINS
information_schema, PROCESSLIST
information_schema, ROUTINES
information_schema, TRIGGERS
information_schema, VIEWS
information_schema, XTRADB_ZIP_DICT

These are also engine is null tables.

SELECT table_schema, table_name 
FROM information_schema.tables 
WHERE engine IS NULL;

# table_schema, table_name
kep, Pake_xxx
kep, doxxxxxxlar
kep, hexxxxxxxmain
kep, kexxxxxxeri
kep, maixxxxxxxxicilar
sys, host_summary
sys, host_summary_by_file_io
sys, host_summary_by_file_io_type
sys, host_summary_by_stages
sys, host_summary_by_statement_latency
sys, host_summary_by_statement_type
sys, innodb_buffer_stats_by_schema
sys, innodb_buffer_stats_by_table
sys, innodb_lock_waits
sys, io_by_thread_by_latency
sys, io_global_by_file_by_bytes
sys, io_global_by_file_by_latency
sys, io_global_by_wait_by_bytes
sys, io_global_by_wait_by_latency
sys, latest_file_io
sys, memory_by_host_by_current_bytes
sys, memory_by_thread_by_current_bytes
sys, memory_by_user_by_current_bytes
sys, memory_global_by_current_bytes
sys, memory_global_total
sys, metrics
sys, processlist
sys, ps_check_lost_instrumentation
sys, schema_auto_increment_columns
sys, schema_index_statistics
sys, schema_object_overview
sys, schema_redundant_indexes
sys, schema_table_lock_waits
sys, schema_table_statistics
sys, schema_table_statistics_with_buffer
sys, schema_tables_with_full_table_scans
sys, schema_unused_indexes
sys, session
sys, session_ssl_status
sys, statement_analysis
sys, statements_with_errors_or_warnings
sys, statements_with_full_table_scans
sys, statements_with_runtimes_in_95th_percentile
sys, statements_with_sorting
sys, statements_with_temp_tables
sys, user_summary
sys, user_summary_by_file_io
sys, user_summary_by_file_io_type
sys, user_summary_by_stages
sys, user_summary_by_statement_latency
sys, user_summary_by_statement_type
sys, version
sys, wait_classes_global_by_avg_latency
sys, wait_classes_global_by_latency
sys, waits_by_host_by_latency
sys, waits_by_user_by_latency
sys, waits_global_by_latency
sys, x$host_summary
sys, x$host_summary_by_file_io
sys, x$host_summary_by_file_io_type
sys, x$host_summary_by_stages
sys, x$host_summary_by_statement_latency
sys, x$host_summary_by_statement_type
sys, x$innodb_buffer_stats_by_schema
sys, x$innodb_buffer_stats_by_table
sys, x$innodb_lock_waits
sys, x$io_by_thread_by_latency
sys, x$io_global_by_file_by_bytes
sys, x$io_global_by_file_by_latency
sys, x$io_global_by_wait_by_bytes
sys, x$io_global_by_wait_by_latency
sys, x$latest_file_io
sys, x$memory_by_host_by_current_bytes
sys, x$memory_by_thread_by_current_bytes
sys, x$memory_by_user_by_current_bytes
sys, x$memory_global_by_current_bytes
sys, x$memory_global_total
sys, x$processlist
sys, x$ps_digest_95th_percentile_by_avg_us
sys, x$ps_digest_avg_latency_distribution
sys, x$ps_schema_table_statistics_io
sys, x$schema_flattened_keys
sys, x$schema_index_statistics
sys, x$schema_table_lock_waits
sys, x$schema_table_statistics
sys, x$schema_table_statistics_with_buffer
sys, x$schema_tables_with_full_table_scans
sys, x$session
sys, x$statement_analysis
sys, x$statements_with_errors_or_warnings
sys, x$statements_with_full_table_scans
sys, x$statements_with_runtimes_in_95th_percentile
sys, x$statements_with_sorting
sys, x$statements_with_temp_tables
sys, x$user_summary
sys, x$user_summary_by_file_io
sys, x$user_summary_by_file_io_type
sys, x$user_summary_by_stages
sys, x$user_summary_by_statement_latency
sys, x$user_summary_by_statement_type
sys, x$wait_classes_global_by_avg_latency
sys, x$wait_classes_global_by_latency
sys, x$waits_by_host_by_latency
sys, x$waits_by_user_by_latency
sys, x$waits_global_by_latency

I just understood what you mean, the information written under the error is as follows;


  kep.#sql-ib197-3868964134 - present in INFORMATION_SCHEMA's INNODB_SYS_TABLES
    table but missing from TABLES table
4

Please share the list of temp files available inside the kep data directory.
ls -lrth /data/directory/kep/ | grep -i "sql-ib"

Also, share the below output :

select * from information_schema.innodb_sys_tablespaces where name like '%sql-ib%';
select * from information_schema.INNODB_SYS_TABLES where NAME like '%sql-ib%';

Here, if this is a leftover table, then you will need to drop it before upgrading the database to 8.0.

5

Those are VIEWs, thus they have no engine.

6

You’re right.
Thanks, you felt me stupid every time :slight_smile:

This is same as the error output:

rootdb04:/DB/mysql# ls -lrth /DB/mysql/kep/ | grep -i “sql-ib”
-rw-r----- 1 mysql mysql 52M Mar 30 2024 #sql-ib197-3868964134.ibd

# SPACE, NAME, FLAG, FILE_FORMAT, ROW_FORMAT, PAGE_SIZE, ZIP_PAGE_SIZE, SPACE_TYPE, FS_BLOCK_SIZE, FILE_SIZE, ALLOCATED_SIZE
595, kep/#sql-ib197-3868964134, 33, Barracuda, Dynamic, 16384, 0, Single, 4096, 54525952, 54525952

# TABLE_ID, NAME, FLAG, N_COLS, SPACE, FILE_FORMAT, ROW_FORMAT, ZIP_PAGE_SIZE, SPACE_TYPE
636, kep/#sql-ib197-3868964134, 33, 24, 595, Barracuda, Dynamic, 0, Single
7

@aycelen,
I would mv the #sql file to /tmp/, then restart mysql 5.7, then run the upgrade checker again. ‘#sql’ files are temporary files from broken ALTER TABLE in the past.

8

Hi @matthewb,

I tried this method but it didn’t work.

I move to #sql file to temp and restart mysql, this file is gone now.
I can’t find in the sytem.

After restart a new #sql file in like

#sql-1db9_2584d28.frm

Still got an same error.

select * from information_schema.innodb_sys_tablespaces where name like ‘%sql-ib%’;
select * from information_schema.INNODB_SYS_TABLES where NAME like ‘%sql-ib%’;

These query’s output is same.

9

In this case, you could try to drop the orphaned table using DROP TABLE, but before this, ensure this temp file is not currently being used. Get rid of orphaned InnoDB temporary tables, the right way | MariaDB