Removing MySQL Orphan Tables in Google's Managed MySQL

Question

I have a number of orphan tables that I am unable to remove in Google managed MySQL instance.

https://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html describes how to find them using the query: SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%';

and returns the following results

TABLE_ID, NAME, FLAG, N_COLS, SPACE, FILE_FORMAT, ROW_FORMAT, ZIP_PAGE_SIZE, SPACE_TYPE
'10790334', 's/#sql-ib10651620-992396264', '33', '10', '15069', 'Barracuda', 'Dynamic', '0', 'Single'
'11386494', 's/#sql-ib11176578-758128583', '33', '10', '15865', 'Barracuda', 'Dynamic', '0', 'Single'
'11715795', 's/#sql-ib11550885-968245244', '33', '56', '16593', 'Barracuda', 'Dynamic', '0', 'Single'
'11884112', 's/#sql-ib11854996-1155811138', '33', '63', '16936', 'Barracuda', 'Dynamic', '0', 'Single'
'12648506', 's/#sql-ib11883996-1143057998', '33', '31', '18549', 'Barracuda', 'Dynamic', '0', 'Single'
'12877658', 's/#sql-ib11884055-1196159549', '33', '34', '19856', 'Barracuda', 'Dynamic', '0', 'Single'
'15159365', 's/#sql-ib15000139-4079941189', '33', '58', '24560', 'Barracuda', 'Dynamic', '0', 'Single'

I am unable to select any of these tables, for example:

use s; SELECT * FROM `#mysql50##sql-ib10651620-992396264` limit 10;

Following https://mariadb.com/resources/blog/get-rid-of-orphaned-innodb-temporary-tables-the-right-way/, I tried creating a table with the same name.

I figured out which tables these orphans came from with help from the query

SELECT * FROM information_schema.innodb_sys_tables JOIN information_schema.innodb_sys_columns USING (TABLE_ID) WHERE information_schema.innodb_sys_tables.NAME LIKE '%#sql%'

I then copied the create table statement for the related table and edited the table name.

CREATE TABLE `s`.`#sql-ib10651620-992396264` ( `ID` int(11) NOT NULL AUTO_INCREMENT, ...) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and then tried to drop it

drop table `s`.`#mysql50##sql-ib10651620-992396264`;

but it could not find the table. After dropping the created table with

drop table `#sql-ib10651620-992396264`;

the orphan table still persists.

To my knowledge, with a managed MySQL instance, I do not have access to the machine that MySQL is running on and therefore cannot remove the associated files from the command line. Logging into the database from the command line and executing a shell command, executes a command on the client's host system, not the system that MySQL is hosted on.

Not a huge issue, but it would be nice to reclaim this disk space.

Answer 1

Your theory is correct about your cloudSQL since it is a Google managed service.

If you would like to have full access over your mysql database, you can use it through a Compute Engine instance in which you will have super user privileges.

Nevertheless, about your current issue please fill a bug here which is under a private component and you can share if there is a need, sensitive information.