LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices


Reply
  Search this Thread
Old 12-11-2013, 04:53 AM   #1
jojanmpaul
Member
 
Registered: Sep 2012
Location: Bangalore
Posts: 80

Rep: Reputation: Disabled
Thumbs up 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
 
Old 12-11-2013, 04:58 AM   #2
JJJCR
Senior Member
 
Registered: Apr 2010
Posts: 1,102

Rep: Reputation: 181Reputation: 181
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.
 
Old 12-11-2013, 07:01 AM   #3
jojanmpaul
Member
 
Registered: Sep 2012
Location: Bangalore
Posts: 80

Original Poster
Rep: Reputation: Disabled
Quote:
Originally Posted by JJJCR View Post
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.
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.
 
Old 12-11-2013, 08:00 AM   #4
JJJCR
Senior Member
 
Registered: Apr 2010
Posts: 1,102

Rep: Reputation: 181Reputation: 181
Question

Quote:
Originally Posted by jojanmpaul View Post
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.
Did you delete the data from MySQL DB?
 
Old 12-11-2013, 08:01 AM   #5
pan64
LQ Guru
 
Registered: Mar 2012
Location: Hungary
Distribution: debian i686 (solaris)
Posts: 8,119

Rep: Reputation: 2269Reputation: 2269Reputation: 2269Reputation: 2269Reputation: 2269Reputation: 2269Reputation: 2269Reputation: 2269Reputation: 2269Reputation: 2269Reputation: 2269
looks like you need something like this:
alter table <tablename> discard tablespace;
 
Old 12-12-2013, 05:24 AM   #6
jojanmpaul
Member
 
Registered: Sep 2012
Location: Bangalore
Posts: 80

Original Poster
Rep: Reputation: Disabled
Quote:
Originally Posted by pan64 View Post
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.
 
Old 12-12-2013, 06:05 AM   #7
pan64
LQ Guru
 
Registered: Mar 2012
Location: Hungary
Distribution: debian i686 (solaris)
Posts: 8,119

Rep: Reputation: 2269Reputation: 2269Reputation: 2269Reputation: 2269Reputation: 2269Reputation: 2269Reputation: 2269Reputation: 2269Reputation: 2269Reputation: 2269Reputation: 2269
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)
 
Old 12-12-2013, 06:25 AM   #8
jojanmpaul
Member
 
Registered: Sep 2012
Location: Bangalore
Posts: 80

Original Poster
Rep: Reputation: Disabled
Quote:
Originally Posted by pan64 View Post
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.
 
Old 12-12-2013, 06:33 AM   #9
pan64
LQ Guru
 
Registered: Mar 2012
Location: Hungary
Distribution: debian i686 (solaris)
Posts: 8,119

Rep: Reputation: 2269Reputation: 2269Reputation: 2269Reputation: 2269Reputation: 2269Reputation: 2269Reputation: 2269Reputation: 2269Reputation: 2269Reputation: 2269Reputation: 2269
no, the problem is that you did not delete the bookings of those tables, just those files. You need to cleanup that booking now.
 
Old 12-12-2013, 07:58 AM   #10
nishantkharat
LQ Newbie
 
Registered: Jun 2013
Posts: 3

Rep: Reputation: Disabled
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.
 
Old 12-12-2013, 11:30 PM   #11
jojanmpaul
Member
 
Registered: Sep 2012
Location: Bangalore
Posts: 80

Original Poster
Rep: Reputation: Disabled
Quote:
Originally Posted by nishantkharat View Post
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.
 
Old 12-12-2013, 11:48 PM   #12
astrogeek
Moderator
 
Registered: Oct 2008
Distribution: Slackware [64]-X.{0|1|2|37|-current} ::12<=X<=14, FreeBSD_10{.0|.1|.2}
Posts: 3,880
Blog Entries: 1

Rep: Reputation: 1998Reputation: 1998Reputation: 1998Reputation: 1998Reputation: 1998Reputation: 1998Reputation: 1998Reputation: 1998Reputation: 1998Reputation: 1998Reputation: 1998
*** 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.

Last edited by astrogeek; 12-13-2013 at 12:11 AM.
 
Old 12-13-2013, 12:15 AM   #13
JJJCR
Senior Member
 
Registered: Apr 2010
Posts: 1,102

Rep: Reputation: 181Reputation: 181
Lightbulb

Quote:
Originally Posted by astrogeek View Post
*** 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.

Last edited by JJJCR; 12-13-2013 at 12:23 AM. Reason: edit
 
Old 12-13-2013, 12:41 AM   #14
jojanmpaul
Member
 
Registered: Sep 2012
Location: Bangalore
Posts: 80

Original Poster
Rep: Reputation: Disabled
Quote:
Originally Posted by astrogeek View Post
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.
 
Old 12-13-2013, 12:51 AM   #15
astrogeek
Moderator
 
Registered: Oct 2008
Distribution: Slackware [64]-X.{0|1|2|37|-current} ::12<=X<=14, FreeBSD_10{.0|.1|.2}
Posts: 3,880
Blog Entries: 1

Rep: Reputation: 1998Reputation: 1998Reputation: 1998Reputation: 1998Reputation: 1998Reputation: 1998Reputation: 1998Reputation: 1998Reputation: 1998Reputation: 1998Reputation: 1998
Quote:
Originally Posted by jojanmpaul View Post
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.
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.

Last edited by astrogeek; 12-13-2013 at 12:54 AM.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
mysql 'Table 'mysql.user' doesn't exist' Joe of Loath Linux - Server 13 08-05-2010 07:54 AM
Mysql Replication Deletion cbtshare Linux - Server 1 07-22-2010 04:00 AM
mysql users on Fedora 10 some may need deletion? which ones should I delete? asif2k Linux - Newbie 2 03-10-2009 11:50 PM
MySQL non-realtime table-by-table mirroring Passive Linux - Software 1 01-20-2004 01:11 PM
How to import MS ACCESS Table including OLE filed into the MySQL Table ? myunicom Linux - General 1 11-28-2003 12:30 PM


All times are GMT -5. The time now is 06:27 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration