[google-cloud-sql-discuss] Cloud MySQL Orphan Tables

I have a number of orphan tables that I am unable to remove.

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 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.

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

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/9b9b654f-d7ac-44de-8cf4-f0441a2952fd%40googlegroups.com.