My production db crashed and I have 5 very large partitioned tables. The active partitions are from the 14th on. The DB is up but 5 tables “do not exist” when trying any commands i.e. DESC tbl1;
InnoDB: Table xyz in the InnoDB data dictionary has tablespace nnnn, but tablespacewith that id or name does not exist.
I can see the row in information_schema.innodb_sys_tables for the orphaned partition.
I cannot do any operations against the tables because MySQL thinks they do not exist.
Is there a way to clean this up?
My prod DB is down at this time…
SteveS0922 apologies but the Forum is not attended to at the weekend and this landed outside European hours.
What’s your current situation? If you could use our professional help then I can put you in touch with someone to discuss how to proceed if you haven’t yet sorted your issue…
I’ll also bring your question to the attention of the server team in case there is something that can help you that would not be specific to your environment and circumstance. Could you let us have copies of your my.cnf and of any database or server error log files you have found, plus tell us what environment you are running in?
For introduction to our professional services teams you can email me directly lorraine.pocklington@percona.com please let me know where in the world you are (including state if you are in the US) and your company name. Thanks!
Hello again SteveS0922
Some notes from our tech team: best practice in this situation would be to restore from a backup + binary logs?
Could you give us some idea of the size of the very large tables that we’re dealing with?
Whatever you try out, you should make sure that they have a backup beforehand.
If you can’t recover using this as guidelines it feels more likely that it’s beyond the scope of the Forum to get the right answer to you.
Here are a couple of blogs and webinars that could be helpful, if you need clarification on any of the points don’t hesitate to come back.
[URL]https://www.percona.com/blog/2017/10/23/mysql-point-in-time-recovery-right-way/[/URL]
[URL]https://learn.percona.com/database-backup-and-recovery-best-practices-focus-mysql-recording[/URL]