InnoDB: Error: Table “mysql”.“mysql.transaction_registry” not found after upgrade to mariadb 10.3

Question

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

Stop mariadb with sudo systemctl stop mariadb

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

Start mariadb with sudo systemctl start mariadb
Open mysql using mysql -u root -p

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, Commented Jan 19, 2020 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, Commented Jan 19, 2020 at 12:21
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, Commented Jan 19, 2020 at 17:26
transaction_registry is in engine InnoDB; the rest are MyISAM. — Rick James, Commented Jan 24, 2020 at 22:41
Tables were definitely innodb. However how can I restore the databases do my tables are recognize again. — papanito, Commented Jan 25, 2020 at 7:07

danblack · Accepted Answer · 2023-01-07 01:14:48Z

0

Drop tables on the system tables that are reporting errors.

mysql_upgrade --force to recreate them.

Avoid ever deleting any MariaDB files except as documented in official manuals (not random people's blogs), and even then under very careful following of the conditions and insturctions provided.

answered Jan 7, 2023 at 1:14

danblack

8,0092 gold badges12 silver badges28 bronze badges

Add a comment |

Stack Exchange Network

InnoDB: Error: Table “mysql”.“mysql.transaction_registry” not found after upgrade to mariadb 10.3

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
mariadb
linux
mariadb-10.3
debian
or ask your own question.

Linked

Hot Network Questions

InnoDB: Error: Table “mysql”.“mysql.transaction_registry” not found after upgrade to mariadb 10.3

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged mariadblinuxmariadb-10.3debian or ask your own question.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
mariadb
linux
mariadb-10.3
debian
or ask your own question.