LinuxQuestions.org
Help answer threads with 0 replies.
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-13-2013, 12:38 AM   #16
jojanmpaul
Member
 
Registered: Sep 2012
Location: Bangalore
Posts: 80

Original Poster
Rep: Reputation: Disabled

Quote:
Originally Posted by astrogeek View Post
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.
The internal MySQL meta-data is a single file or multiple files?. In Linux every thing is a file right, so we should be able to trace it, if it is a single file could be able to delete meta-data belongs to the deleted tables right?, am a small fry in Linux. Please don't mind my silly questions.
 
Old 12-13-2013, 12:47 AM   #17
pan64
LQ Guru
 
Registered: Mar 2012
Location: Hungary
Distribution: debian/ubuntu/suse ...
Posts: 9,449

Rep: Reputation: 2776Reputation: 2776Reputation: 2776Reputation: 2776Reputation: 2776Reputation: 2776Reputation: 2776Reputation: 2776Reputation: 2776Reputation: 2776Reputation: 2776
do not try to edit those data "by hand". You need to use mysql client to successfully modify them (otherwise you would need to know perfectly the internal data structures)
 
Old 12-13-2013, 12:49 AM   #18
astrogeek
Moderator
 
Registered: Oct 2008
Distribution: Slackware [64]-X.{0|1|2|37|-current} ::12<=X<=14, FreeBSD_10{.0|.1|.2}
Posts: 4,411
Blog Entries: 6

Rep: Reputation: 2372Reputation: 2372Reputation: 2372Reputation: 2372Reputation: 2372Reputation: 2372Reputation: 2372Reputation: 2372Reputation: 2372Reputation: 2372Reputation: 2372
Quote:
Originally Posted by jojanmpaul View Post
The internal MySQL meta-data is a single file or multiple files?. In Linux every thing is a file right, so we should be able to trace it, if it is a single file could be able to delete meta-data belongs to the deleted tables right?, am a small fry in Linux. Please don't mind my silly questions.
MySQL meta-data is spread across multiple "tables" which are stored in multiple files. You REALLY do not want to delete those files or you will wreck the mysql server, users and permissions, tables and indexes unrelated to your current problem - etc.

The problem exists because the meta-data is out of sync with the filesystem due to the files being deleted. Deleting more files will only make the problems worse and probably non-recoverable.

At this time the only things affected are related to the deleted tables, and the solution is to restore the tables AND bring the meta-data back into alignment with the actual table data storage files.

I can only repeat what I said earlier - there is no filesystem solution for this, you absolutely must let MySQL sort it out for you, and the way to do that is to tell MySQL to discard the incorrect tablespace, drop the tables (which will clear ALL the meta-data assoicated with them), then re-create the tables, which will also recreate valid meta-data for them.

May I ask why it seems necessary to do it without access to the mysql client? You must have had privileged access to delete the files? Just curious.
 
Old 12-13-2013, 02:46 AM   #19
jojanmpaul
Member
 
Registered: Sep 2012
Location: Bangalore
Posts: 80

Original Poster
Rep: Reputation: Disabled
Quote:
Originally Posted by astrogeek View Post
MySQL meta-data is spread across multiple "tables" which are stored in multiple files. You REALLY do not want to delete those files or you will wreck the mysql server, users and permissions, tables and indexes unrelated to your current problem - etc.

The problem exists because the meta-data is out of sync with the filesystem due to the files being deleted. Deleting more files will only make the problems worse and probably non-recoverable.

At this time the only things affected are related to the deleted tables, and the solution is to restore the tables AND bring the meta-data back into alignment with the actual table data storage files.

I can only repeat what I said earlier - there is no filesystem solution for this, you absolutely must let MySQL sort it out for you, and the way to do that is to tell MySQL to discard the incorrect tablespace, drop the tables (which will clear ALL the meta-data assoicated with them), then re-create the tables, which will also recreate valid meta-data for them.

May I ask why it seems necessary to do it without access to the mysql client? You must have had privileged access to delete the files? Just curious.
Last few days I was so curious to know about this, you given me the exact answer which clear my doubts. Thank you very much. Also I wonder about MySQL has such a security over the Linux file system.
 
  


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 06:54 AM
Mysql Replication Deletion cbtshare Linux - Server 1 07-22-2010 03:00 AM
mysql users on Fedora 10 some may need deletion? which ones should I delete? asif2k Linux - Newbie 2 03-10-2009 10:50 PM
MySQL non-realtime table-by-table mirroring Passive Linux - Software 1 01-20-2004 12:11 PM
How to import MS ACCESS Table including OLE filed into the MySQL Table ? myunicom Linux - General 1 11-28-2003 11:30 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie

All times are GMT -5. The time now is 10:56 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