MySQL tables "in use"

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
MarK (CZ)
Forum Contributor
Posts: 239
Joined: Tue Apr 13, 2004 12:51 am
Location: Prague (CZ) / Vienna (A)
Contact:

MySQL tables "in use"

Post by MarK (CZ) »

Hi, I have problem with my MySQL database. After reinstall, I thought it's gonna be enough to move just the contents of "mysql/data" to the new installation. However, some tables doesn't work anymore and phpmyadmin says just "in use" or that it doesn't exist.. Is there any way to fix it?? Please! Thanks!
ianhull
Forum Contributor
Posts: 310
Joined: Tue Jun 14, 2005 10:04 am
Location: Hull England UK

Post by ianhull »

Have you changed the version?
User avatar
MarK (CZ)
Forum Contributor
Posts: 239
Joined: Tue Apr 13, 2004 12:51 am
Location: Prague (CZ) / Vienna (A)
Contact:

Post by MarK (CZ) »

Probably. If the bundled manual version corresponds with the mysql version, then it was from 5.0.19 to 5.0.25.
While reinstalling, I usually download the latest version.

I need those DBs back :cry: :cry: :cry:
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post by AKA Panama Jack »

Make sure the data directory, all sub directories and files have the SAME user and group permission as the mysql program. It sounds like you probably make a copy and your copy took on the permissions of the user that made the copy instead of retaining the olf permissions. So when mysql tries to access the databases it can't as it doesn't have permission to access those files or directories.
User avatar
MarK (CZ)
Forum Contributor
Posts: 239
Joined: Tue Apr 13, 2004 12:51 am
Location: Prague (CZ) / Vienna (A)
Contact:

Post by MarK (CZ) »

I've reinstalled Windows but done all the actions on the same account - same username, same password (if you mean that).
It seems that only MyISAM tables are still working.
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post by AKA Panama Jack »

I bet you forgot to copy the ibdata files and ib_log files. That is where the actual data for INNODB tables is stored. The directories for the INNODB tables just contain the FRM files and don't contain any of the actual data.

So if you didn't copy those ib files you have lost all of the data.
User avatar
MarK (CZ)
Forum Contributor
Posts: 239
Joined: Tue Apr 13, 2004 12:51 am
Location: Prague (CZ) / Vienna (A)
Contact:

Post by MarK (CZ) »

I thought I did. Anyway, I've copied them again from backup and seems hopefully to wok. Thanks!
Could you please tell me more (or point to somewhere) about the mysql storage system? Are the MyISAM tables stored in the db folder and InnoDB in the IB files? Why the difference? Thanks!
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Are the MyISAM tables stored in the db folder and InnoDB in the IB files?

exactly
Why the difference?
Because they are quite different =).
Could you please tell me more (or point to somewhere) about the mysql storage system?
http://dev.mysql.com/doc/refman/5.0/en/ ... gines.html
Post Reply