MySQL Forums
Forum List  »  Data Recovery

Deleted a table manually and MySQL Server isn't running anymore
Posted by: Othman K
Date: March 31, 2023 05:40AM

I had a bug in my code which kept writing data to a table for gigabytes, tried ```DELETE FROM TABLENAME``` but it was taking forever, so I went to the Data directory and manually deleted *TABLENAME.ibd*

Now if I start MySQL server, I get this error:

2023-03-31T09:48:13.913658Z 1 [Warning] [MY-013541] [InnoDB] Doublewrite page 523 for {space: 17339, page_no:861324} could not be restored. File name unknown for tablespace ID 17339
2023-03-31T09:48:13.915373Z 1 [ERROR] [MY-012179] [InnoDB] Could not find any file associated with the tablespace ID: 17339
2023-03-31T09:48:13.916478Z 1 [ERROR] [MY-012964] [InnoDB] Use --innodb-directories to find the tablespace files. If that fails then use --innodb-force-recovery=1 to ignore this and to permanently lose all changes to the missing tablespace(s)
2023-03-31T09:48:14.019872Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2023-03-31T09:48:14.041689Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2023-03-31T09:48:14.052947Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2023-03-31T09:48:14.058936Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-03-31T09:48:14.091527Z 0 [System] [MY-010910] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: Shutdown complete (mysqld 8.0.28) MySQL Community Server - GPL.

Then I added: *innodb-force-recovery=1* to *my.ini*, it's getting stuck in "starting" and the log file shows this error:

2023-03-31T10:01:43.226457Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
mysqld: Tablespace is missing for table db/tablename.

Created the table on a PC and copied it to the same folder, but that didn't work either cause I think the tablespace ID doesn't match the one I deleted.

- So, is there a solution? Or is it possible to create the same table on a different PC but change the tablespace ID to 17339?
- Removed *innodb-force-recovery=1* my *my.ini*, now the log doesn't show any error, but the service is stuck on Starting! Neither changing to Running nor Stopping/Stopped.
- Tried this tool: https://mysql.recoverytoolbox.com/ , but when I put the folder: ```C:\ProgramData\MySQL\MySQL Server 8.0\Data``` it shows an error: ```No database found.```
I'm browsing the folder and all the databases are there!
- Is there a way to ignore the entire schema which contains the missing table when starting the server?
- Starting with *innodb-force-recovery=1* gives this error in the log:

> 2023-03-31T11:18:54.572788Z 1 [ERROR] [MY-011971] [InnoDB] Tablespace
> 'innodb_undo_001' Page [page id: space=4294967279, page number=108805]
> log sequence number 905282159231 is in the future! Current system log
> sequence number 674565298710. 2023-03-31T11:18:54.574925Z 1 [ERROR]
> [MY-011972] [InnoDB] Your database may be corrupt or you may have
> copied the InnoDB tablespace but not the InnoDB log files. Please
> refer to
> http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
> for information about forcing recovery.

Help! I'm desperate and I don't know what to do anymore

Options: ReplyQuote


Subject
Views
Written By
Posted
Deleted a table manually and MySQL Server isn't running anymore
361
March 31, 2023 05:40AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.