MYD and MYI file missing

HI

i have a small innodB database on linux SErver. and due to some stupid mistake , I had to rebuild the OS and i forgot to take backup, after speaking with Vps provider, they restore data from the tape drive, but since its a INNODB database few tables has missing its index and MYD file..

from mysql , its can see the table, but when i try to select data, its say:

mysql> show tables;
+----------------------+
| Tables_in_database|
+----------------------+
| admins               |
| ausers               |
| installation_history |
| model                |
| stock_avail          |
| users                |
+----------------------+
6 rows in set (0.00 sec)

mysql> select * from users;
ERROR 1146 (42S02): Table 'database.users' doesn't exist


as you can see from ls -al output, some table has missing MYD and MYI , only one table model has eveyrthing, but rest missing

-rw-rw---- 1 mysql mysql 8632 Nov  5  2007 admins.frm
-rw-rw---- 1 mysql mysql 8632 Nov  5  2007 ausers.frm
-rw-rw---- 1 mysql mysql   65 Nov  5  2007 db.opt
-rw-rw---- 1 mysql mysql 8732 Nov 14  2007 installation_history.frm
-rw-rw---- 1 mysql mysql 8610 Nov  5  2007 model.frm
-rw-rw---- 1 mysql mysql  220 Feb  8 17:26 model.MYD
-rw-rw---- 1 mysql mysql 1024 Feb  9 03:00 model.MYI
-rw-rw---- 1 mysql mysql 8726 Nov 26  2007 stock_avail.frm
-rw-rw---- 1 mysql mysql 8744 Nov  5  2007 users.frm

is there any way to repair this tables or get data from the tables ??

really appreciate your. thanks
LVL 29
fosiul01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Muhammad KhanManager, ITCommented:
0
fosiul01Author Commented:
but problem is, i dont have MYD and MYI

i just only have FRM file

and I have a felling i am in deep problem ...

0
woolmilkporcCommented:
Hi,
please check this -
http://www.mysqlperformanceblog.com/2008/12/17/recovering-create-table-statement-from-frm-file/
Basically, you need to create the xxxx.MYD and xxxx.MYI files using "touch" and "chown" to then issue
repair TABLE xxxx USE_FRM;
wmp
 
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

fosiul01Author Commented:
Good morning WMP

if this works then you are a life saver!!!

let me try this

0
fosiul01Author Commented:
this giving this error

mysql> repair TABLE  admins USE_FRM;
+--------+--------+----------+----------------------------------------+
| Table  | Op     | Msg_type | Msg_text                               |
+--------+--------+----------+----------------------------------------+
| admins | repair | error    | Failed reparing incompatible .FRM file |
+--------+--------+----------+----------------------------------------+





i tryed to upgrade database by using

 mysqlcheck --database swapout --check-upgrade --auto-repair


swapout.admins
Error    : Table 'swapout.admins' doesn't exist
error    : Corrupt

Repairing tables
swapout.admins
Error    : Table 'swapout.admins' doesn't exist
error    : Corrupt

.. is there any way to download mysql 4 ???

this database  was create by mysql 4
0
woolmilkporcCommented:
Well, OK,
you didn't mention clearly that you upgraded the mysql server.
This is the official statement from mysql -
Do not use USE_FRM if your table was created by a different version of the MySQL server
than the one you are currently running.

You will actually have to revert to mysql 4. Hopefully you don't have any data created by mysql 5/6 in your DB!
I couldn't find a download site for mysql 4 though. Don't you have your Ver.4 packages at hand any more?
wmp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fosiul01Author Commented:
I did not configure this server , the previous IT guy who left, he configured, yesterday , i rebuild this VPS yesterday. due to lack of documentation from previous IT guy, i did not know that this database was running on this server, I thought it was running under another host.

after rebuild then user stated to complain!! but i am stuck

then the vps provider they provided me this data from their tape but most of the file was missing!!!!

dont know how ...

i will have a look to archives see, if i can find anywhere old version of database


0
fosiul01Author Commented:
thanks for the link
0
fosiul01Author Commented:
tryed to install mysql 4 on a centos 5 server, did nto work

to much dependency, its not worth of giving time....

as this database had some static entry, and customer services decided to entry those again!!!!



0
arnoldCommented:
I believe you have/had a system with an older version of mysql, transfer these files to it and see whether the data can be accessed.

Get centos 4.x or centos 3.x I think one of those has mysql version 4.x
Centos 4.x LiveCD might be what you want/need. i.e. you could use mysqldump to output the data from the database into a text file to be imported.
Any Centos mirror will have these.
http://mirrors.serveraxis.net/centos/4.8/os/i386/CentOS/RPMS/
0
fosiul01Author Commented:
hmm at night i will install centos4 in vmware and will give it a try...


0
arnoldCommented:
The other option is to install centos5.0 and perform the upgrade from 4.x to 5.0 and then perform the remaining upgrade from the now 5.0 to 5.x
0
fosiul01Author Commented:
The other option is to install centos5.0 and perform the upgrade from  4.x to 5.0 and then perform the remaining upgrade from the now 5.0 to  5.x

this does not work

i can guess why

beause MYD and MYI missing.. so at first have to create those file by using

repair TABLE xxxx USE_FRM;

then have to upgrade

but without creating file, i think upgrade failing as its not finding any table or datata



0
arnoldCommented:
the problem is multi-fold.
you have model table that has all of them. with all other tables different.
If this was created with version 3 of mysql, centos 3 has that version.
Make sure mysqld is not running and then upload the users.frm as a zipped/compressed file.
The problem is that I do not think you want to share the list of users so I think that closes that option.
0
fosiul01Author Commented:
The problem is that I do not think you want to share the list of users  so I think that closes that option.  : I did not understand what you meant ??

as i said , in my question

initially the problem was :
mysql> show tables;
+----------------------+
|  Tables_in_database|
+----------------------+
| admins                |
| ausers               |
| installation_history |
| model                 |
| stock_avail          |
| users                |
+----------------------+
6  rows in set (0.00 sec)

mysql> select * from users;
ERROR  1146 (42S02): Table 'database.users' doesn't exist

then as WMP said : create users.MYD and user.MYI

after that when i try to run

mysql> repair TABLE  admins USE_FRM;
+--------+--------+----------+----------------------------------------+
|  Table  | Op     | Msg_type | Msg_text                               |
+--------+--------+----------+----------------------------------------+
|  admins | repair | error    | Failed reparing incompatible .FRM file |
+--------+--------+----------+----------------------------------------+

its now say, Failed to repair, which i saw before as a compatibility issue


0
arnoldCommented:
I was wondering whether you would post the users.frm file such that we cout try different things to correct it. The issue is that there is data in the frm file which you might not want to share.
In particular it might include usernames, passwords, and perhaps contact info.
This leaves the experimentation to you.
I.e. while mysqld is not running.  Copy the files to a different folder i.e. new_database outside the mysql hierarchy.
You can then try using mysqlcheck.
Or get the older version of mysqldump there is an offline mysql backup directive that may help or use one of the older binaries to mysqldump from the old or use the upgrade path using the mysqlcheck from version 4.1, 5.0 and then from 5.1 to get the database to the current mysql version.

In your post, you did not include the touched files.  Double check that they are mysql owned.
0
fosiul01Author Commented:
"I was wondering whether you would post the users.frm file such that we  cout try different things to correct it. The issue is that there is data  in the frm file which you might not want to share."

if i would of able to view data then my problem is solved!!!

i have lost all data from that database. dont have any entry.

so if i am unable to fix the problem , will have to entry again from Hard copy... got a great lesson


the permission was Ok,

I will try with centos4 + mysql server 4 , see how its goes, i am sure it will work


0
gtkfreakCommented:
The only option I can think of is that you have an opportunity to know how a MySQL database backup should be taken. You would be better off not taking backup of the database directly. Use mysqldump to do a good backup. You could also try other methods of doing so, but this is the safest I have come across. FRM only stores data dictionary information, if I am not mistaken.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.