LinuxQuestions.org - MySQL table deletion.

Page 1 of 2

Show 50 post(s) from this thread on one page

- Linux - Newbie (https://www.linuxquestions.org/questions/linux-newbie-8/)

- - MySQL table deletion. (https://www.linuxquestions.org/questions/linux-newbie-8/mysql-table-deletion-4175487581/)

jojanmpaul

12-11-2013 03:53 AM

MySQL table deletion.

I have deleted (rm -rf) a bunch of tables from /var/lib/mysql/sites/. Now am not able to execute the same tables in to the sites db and able to execute if the table name got changed. Please help me to solve this issue, I need to use the same table with same table name for execution.

sample of deleted tables are listed below,
site_field.frm
site_field_instance.frm
site_field_instance.MYD
site_field_instance.MYI
site_field.MYD
site_field.MYI
site_circular.frm
site_circular.MYD
site_circular.MYI
site_allery.frm
site_allery.MYD
site_allery.MYI
site_.........
site_.........etc

JJJCR

12-11-2013 03:58 AM

have you tried to stop my sql service? and check whether you can execute again the same table name.

or if it is okay to reboot the server please reboot it, because there could be some process that still points the old data.

jojanmpaul

12-11-2013 06:01 AM

Quote:

Originally Posted by JJJCR (Post 5078856)

I have restarted the service and rebooted the machine too, but the issue is persisting.
The error file shows the below,
Error Code: 1813 - Tablespace for table '`testtable`.`actions`' exists. Please DISCARD the tablespace before IMPORT.

JJJCR

12-11-2013 07:00 AM

Quote:

Originally Posted by jojanmpaul (Post 5078899)

Did you delete the data from MySQL DB?

pan64

12-11-2013 07:01 AM

looks like you need something like this:
alter table <tablename> discard tablespace;

jojanmpaul

12-12-2013 04:24 AM

Quote:

Originally Posted by pan64 (Post 5078930)

looks like you need something like this:
alter table <tablename> discard tablespace;

There are 'n' number of tables, how to alter a group of tables. The db is having other tables too, so have to handle careful.

pan64

12-12-2013 05:05 AM

I would say one by one, you need to collect all the names and create a script to do the work
(next time try to delete tables by drop table instead of removing files)

jojanmpaul

12-12-2013 05:25 AM

Quote:

Originally Posted by pan64 (Post 5079386)

I would say one by one, you need to collect all the names and create a script to do the work
(next time try to delete tables by drop table instead of removing files)

Thank you very much, I manually deleted the files so thought it is possible to trouble shoot from Linux file system itself, without logging in to MySQL db.

pan64

12-12-2013 05:33 AM

no, the problem is that you did not delete the bookings of those tables, just those files. You need to cleanup that booking now.

nishantkharat

12-12-2013 06:58 AM

There is no way to get the data because it was physically removed from the server . The Only way is you can restore the backup and then you can get the data.

jojanmpaul

12-12-2013 10:30 PM

Quote:

Originally Posted by nishantkharat (Post 5079437)

There is no way to get the data because it was physically removed from the server . The Only way is you can restore the backup and then you can get the data.

I am not worried about the data, at least take an effort to read my issue before replying. Its just a kind information for you, will help you in future.

astrogeek

12-12-2013 10:48 PM

*** UPDATE *** (Think before posting...)

pan64 has your answer in post #5,7.

But you will have to login to the MySQL client - this is not a fielsystem issue.

JJJCR

12-12-2013 11:15 PM

Quote:

Originally Posted by astrogeek (Post 5079859)

*** UPDATE *** (Think before posting...)

pan64 has your answer in post #5,7.

But you will have to login to the MySQL client - this is not a filesystem issue.

agree this is not a filesystem issue, I think when the MySQL is run. It is trying to find a file which does not exist anymore on the particular location.

Quote:

I have deleted (rm -rf) a bunch of tables from /var/lib/mysql/sites/

Files were deleted, but not the reference in the database.

If i'm not wrong this is a MySQL issue not relating to Linux OS. :)

jojanmpaul

12-12-2013 11:41 PM

Quote:

Originally Posted by astrogeek (Post 5079859)

If you have removed the files you should be able to use (for example)...

Code:

DROP TABLESPACE site_field ENGINE...

Then you can recreate the tables with a CREATE TABLE statement.

Be sure that you have the table structure or pre-made CREATE TABLE statements or are restoring them from a dump of some sort.

This is a nice tip, but in my case I have tables backup, I want to restore it on the same db. It doesn't allow to restore the same tables because deleted manually from /var/lib/mysql/site/. There is a chance of current user’s session id and the batch id to be present there. But service restart and reboot is not a solution for this. Could you help me to trace which files still block me to restore the tables.

astrogeek

12-12-2013 11:51 PM

Quote:

Originally Posted by jojanmpaul (Post 5079872)

I had thought that through and updated my above post earlier.

But the difficulty for you is that it is not a 'file' that is blocking the recreation of the tables - it is the internal MySQL meta-data. SO there is really not a solution that can be performed from the filesystem.

I think that you will need at minimum to do the following from within mysql client or from a script (for each affected table):

Code:

ALTER TABLE... DISCARD TABLESPACE;



DROP TABLE...



CREATE TABLE (from table spec) OR restore from backup dump.

You can do that without restarting the MySQL server, but not without access to the mysql client, or the ability to run it as a script on the affected server.

All times are GMT -5. The time now is 08:19 PM.

Page 1 of 2

Show 50 post(s) from this thread on one page