1

I've upgraded the mariadb to 10.3 packages on my Debian 10, however something went wrong and I ended in the same issue which is mentioned several times

So in my case you can see mysql_upgrade complains of missing tables although they seem to exist

$ sudo mysql_upgrade -u root --force -pxxxxx
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.index_stats                                  OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.roles_mapping                                OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.table_stats                                  OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.transaction_registry
Error    : Table 'mysql.transaction_registry' doesn't exist in engine
status   : Operation failed
mysql.user                                         OK

Repairing tables
mysql.transaction_registry
Error    : Table 'mysql.transaction_registry' doesn't exist in engine
status   : Operation failed
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
Phase 4/7: Running 'mysql_fix_privilege_tables'
ERROR 1813 (HY000) at line 77: Tablespace for table '`mysql`.`innodb_table_stats`' exists. Please DISCARD the tablespace before IMPORT
ERROR 1813 (HY000) at line 81: Tablespace for table '`mysql`.`innodb_index_stats`' exists. Please DISCARD the tablespace before IMPORT
ERROR 1813 (HY000) at line 153: Tablespace for table '`mysql`.`gtid_slave_pos`' exists. Please DISCARD the tablespace before IMPORT
ERROR 1146 (42S02) at line 639: Table 'mysql.innodb_index_stats' doesn't exist
ERROR 1243 (HY000) at line 640: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1146 (42S02) at line 642: Table 'mysql.innodb_table_stats' doesn't exist
ERROR 1243 (HY000) at line 643: Unknown prepared statement handler (stmt) given to EXECUTE
ERROR 1146 (42S02) at line 647: Table 'mysql.innodb_index_stats' doesn't exist
ERROR 1146 (42S02) at line 651: Table 'mysql.innodb_table_stats' doesn't exist
ERROR 1146 (42S02) at line 654: Table 'mysql.innodb_table_stats' doesn't exist
FATAL ERROR: Upgrade failed

However the tables are there

MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| column_stats              |
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| index_stats               |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| roles_mapping             |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| table_stats               |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| transaction_registry      |
| user                      |
+---------------------------+
31 rows in set (0.005 sec)

As non-db expert and althought some of the posts mentioned above are old, I followed them trying to solve the problem

  1. Stop mariadb with sudo systemctl stop mariadb
  2. Delete files
    cd /var/lib
    sudo rm mysql/ibdata1
    sudo rm mysql/ib_logfile0
    sudo rm mysql/ib_logfile1
    sudo rm mysql/mysql/innodb_index_stats.ibd
    sudo rm mysql/mysql/innodb_table_stats.ibd
    sudo rm mysql/mysql/gtid_slave_pos.frm
    sudo rm mysql/mysql/gtid_slave_pos.ibd
    sudo rm mysql/mysql/slave_master_info.frm
    sudo rm mysql/mysql/slave_master_info.ibd 
    sudo rm mysql/mysql/slave_worker_info.frm
    sudo rm mysql/mysql/slave_worker_info.ibd
    sudo rm mysql/mysql/slave_relay_log_info.frm
    sudo rm mysql/mysql/slave_relay_log_info.ibd
    
  3. Start mariadb with sudo systemctl start mariadb
  4. Open mysql using mysql -u root -p
  5. Recreate tables for mariadb 10

    CREATE TABLE `gtid_slave_pos` (
    `domain_id` int(10) unsigned NOT NULL,
    `sub_id` bigint(20) unsigned NOT NULL,
    `server_id` int(10) unsigned NOT NULL,
    `seq_no` bigint(20) unsigned NOT NULL,
    PRIMARY KEY (`domain_id`,`sub_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Replication slave GTID state';
    
    CREATE TABLE `innodb_index_stats` (
    `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
    `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
    `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
    `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
    `stat_value` bigint(20) unsigned NOT NULL,
    `sample_size` bigint(20) unsigned DEFAULT NULL,
    `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
    PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
    
    CREATE TABLE `innodb_table_stats` (
    `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
    `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
    `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `n_rows` bigint(20) unsigned NOT NULL,
    `clustered_index_size` bigint(20) unsigned NOT NULL,
    `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
    PRIMARY KEY (`database_name`,`table_name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
    
    CREATE TABLE `slave_master_info` (
    `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',
    `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log currently being read from the master.',
    `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',
    `Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.',
    `User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',
    `User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',
    `Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',
    `Connect_retry` int(10) unsigned NOT NULL COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',
    `Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',
    `Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',
    `Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',
    `Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',
    `Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',
    `Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',
    `Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',
    `Heartbeat` float NOT NULL,
    `Bind` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Displays which interface is employed when connecting to the MySQL server',
    `Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',
    `Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',
    `Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',
    `Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
    `Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
    `Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',
    PRIMARY KEY (`Host`,`Port`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';
    
    CREATE TABLE `slave_relay_log_info` (
    `Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
    `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
    `Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',
    `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
    `Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',
    `Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
    `Number_of_workers` int(10) unsigned NOT NULL,
    `Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
    PRIMARY KEY (`Id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';
    
    CREATE TABLE `slave_worker_info` (
    `Id` int(10) unsigned NOT NULL,
    `Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
    `Relay_log_pos` bigint(20) unsigned NOT NULL,
    `Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
    `Master_log_pos` bigint(20) unsigned NOT NULL,
    `Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
    `Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL,
    `Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
    `Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL,
    `Checkpoint_seqno` int(10) unsigned NOT NULL,
    `Checkpoint_group_size` int(10) unsigned NOT NULL,
    `Checkpoint_group_bitmap` blob NOT NULL,
    PRIMARY KEY (`Id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';
    

However situation got worse:

$ sudo mysql_upgrade -u root --force -pxxxx
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.gtid_slave_pos                               OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.index_stats                                  OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.roles_mapping                                OK
mysql.servers                                      OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.table_stats                                  OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.transaction_registry
Error    : Table 'mysql.transaction_registry' doesn't exist in engine
status   : Operation failed
mysql.user                                         OK

Repairing tables
mysql.transaction_registry
Error    : Table 'mysql.transaction_registry' doesn't exist in engine
status   : Operation failed
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
Phase 4/7: Running 'mysql_fix_privilege_tables'
Phase 5/7: Fixing table and database names
Phase 6/7: Checking and upgrading tables
Processing databases
information_schema
nc
nc.oc_accounts
Error    : Table 'nc.oc_accounts' doesn't exist in engine
status   : Operation failed
nc.oc_activity
Error    : Table 'nc.oc_activity' doesn't exist in engine
status   : Operation failed
nc.oc_activity_mq
...
Phase 7/7: Running 'FLUSH PRIVILEGES'
OK

What can I do? Not only are there errors with mysql-tables but also all others.

  • I also realize that after starting the db there are no idb but MYD and MYI files in /var/lib/mysql/mysql – papanito Jan 19 at 10:53
  • I came so far that I get the following error ERROR 1813 (HY000) at line 85: Tablespace for table 'mysql.transaction_registry' exists. Please DISCARD the tablespace before IMPORT however there are no respective idb or frm files. How to discard, DROP TABLESPACE transaction_registry does not work – papanito Jan 19 at 12:21
  • Opened a separate entry for this: dba.stackexchange.com/questions/257721/… – papanito Jan 19 at 13:58
  • Ok I could solve that problem but now the upgrade complains on every table Table 'xxx' doesn't exist in engine. how to fix that one? – papanito Jan 19 at 17:26
  • transaction_registry is in engine InnoDB; the rest are MyISAM. – Rick James Jan 24 at 22:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.