Hi,
My db has many orphaned tables, and because of it I can’t make replication set. They can be deleted but thrown warning every time mysql is restarted. Is there any proper way to delete them from the system?
Tables are like:
*#sql-ib90314-3498075423.ibd*
What I tried:
When I’m trying to delete it from db it shows:
ERROR 1051 (42S02): Unknown table ‘isklep.#sql-ib105365-4143814347’
When trying create it says:
ERROR 1062 (23000): Duplicate entry 'isklep/#sql-ib105365-4143814347' for key 'tablespaces.name'
Can only create temp table and delete it but file is still there.
Tried it mysql5 way also (not working):
mysql> drop table `#mysql50##sql-ib105365-4143814347`;
ERROR 1051 (42S02): Unknown table 'isklep.#mysql50##sql-ib105365-4143814347'
Also i tried to delete the whole database, it was executed but files and dir stays in datadir, after delete it manually and restart ive got the same errors:
2022-01-25T11:00:57.416617Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 64719, name 'isklep/#sql-ib90314-3498075423', file './isklep/#sql-ib90314-3498075423.ibd' is missing!
2022-01-25T11:00:57.420561Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 72439, name 'isklep/#sql-ib105365-4143814347', file './isklep/#sql-ib105365-4143814347.ibd' is missing!
2022-01-25T11:00:57.423657Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 72649, name 'isklep/#sql-ib105930-1133666009', file './isklep/#sql-ib105930-1133666009.ibd' is missing!
Anyone has any idea how to deal with this problem? Thanks!
1 Like
Hello @Pawel_Babilas,
Tables that start with #sql- are temporary tables and should normally be cleaned up by MySQL and/or the filesystem. Are you manually creating tables with these names? It may be that you are somehow confusing InnoDB because of this. If that is not the case, then I would DROP the database, stop mysql, erase the directory manually, start mysql back up, and simply ignore the ‘WARNING’ messages. They are not errors. Restart mysql again and see if the messages continue to show. You may need to do a full restore from a logical dump to completely fix the issue. A physical backup would not work as the dictionary information would be copied and restored as-is.
1 Like
Thanks for the reply, this error is most likely happening because my database was stored in nfs datadir so packet loss and other network issues may have occurred.
So I delete the files on the copy of my database and in addition to the warnings I also get the error:
2022-01-26T09:06:43.506771Z 1 [ERROR] [MY-012214] [InnoDB] A file-per-table tablespace cannot be located in the datadir. Cannot open file '/var/lib/mysql/#sql-ib90314-3498075423.ibd'.
2022-01-26T09:06:43.507099Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 64719, name 'isklep/#sql-ib90314-3498075423', file './isklep/#sql-ib90314-3498075423.ibd' is missing!
2022-01-26T09:06:43.510526Z 1 [ERROR] [MY-012214] [InnoDB] A file-per-table tablespace cannot be located in the datadir. Cannot open file '/var/lib/mysql/#sql-ib105365-4143814347.ibd'.
2022-01-26T09:06:43.510806Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 72439, name 'isklep/#sql-ib105365-4143814347', file './isklep/#sql-ib105365-4143814347.ibd' is missing!
2022-01-26T09:06:43.512763Z 1 [ERROR] [MY-012214] [InnoDB] A file-per-table tablespace cannot be located in the datadir. Cannot open file '/var/lib/mysql/#sql-ib105930-1133666009.ibd'.
2022-01-26T09:06:43.513004Z 1 [Warning] [MY-012351] [InnoDB] Tablespace 72649, name 'isklep/#sql-ib105930-1133666009', file './isklep/#sql-ib105930-1133666009.ibd' is missing!
DB is still working, is error something I should be worried?
1 Like