1

I run the mysql_upgrade against my mariadb 10.3 and get the following error - see here for details.

ERROR 1813 (HY000) at line 85: Tablespace for table 'mysql.transaction_registry' exists. Please DISCARD the tablespace before IMPORT

When doing the DISCARD it does not work as the table does not exist

MariaDB [mysql]> show tables;

+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| column_stats              |
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| gtid_slave_pos            |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| index_stats               |
| innodb_index_stats        |
| innodb_table_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 |
| user                      |
+---------------------------+
MariaDB [mysql]> ALTER TABLE transaction_registry DISCARD TABLESPACE;
ERROR 1146 (42S02): Table 'mysql.transaction_registry' doesn't exist

How to proceed in such a case?

1 Answer 1

-1

After carefully checking again, I've found transaction_registry.ibd on the file system. So I manually deleted the file after stopping mariadb. Here you can see what I did:

sudo systemctl stop mariadb
sudo rm /var/lib/mysql/mysql/transaction_registry.ibd
sudo systemctl startb mariadb
sudo mysql_upgrade -u root --force -pxxxxx
...
3
  • You can't simply rm a .ibd file -- there is info in ibdata1 that knows about it.
    – Rick James
    Commented Jan 24, 2020 at 23:48
  • 1
    I assume it's generally a bad idea to just delete tables but I'm coming from a bad state: dba.stackexchange.com/q/257699/169281
    – papanito
    Commented Jan 25, 2020 at 7:17
  • Interestingly I've referenced some other posts which also recommend to delete tables files incl. idb. What are then the better options if it's not the correct way? In my case it's not dramatic I don't have production data from a client is only personal stuff
    – papanito
    Commented Jan 25, 2020 at 7:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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