MySQL w32 problem: Unable to ALTER or DROP

#7 - Error on rename of '.\dbname\test.MYI' to '.\dbname\#sql2-7a0-11.MYI'

[vincevincevince]

Error

SQL-query :

ALTER TABLE `test` CHANGE `1` `1` VARCHAR( 1 ) NOT NULL

MySQL said:

#7 - Error on rename of '.\dbname\test.MYI' to '.\dbname\#sql2-7a0-11.MYI' (Errcode: 13)

This is from myAdmin 2.5.3-rc2 on win32 with MySQL 4.0.1-alpha-nt [Client API version 3.23.39] under Apache/2.0.39 (Win32) PHP/4.2.2.

What can I do to fix it? REPAIR fails as well, on all tables in all databases. I can make new tables.

[justageek]

That usually means your indexes are corrupt. There is a force option on the repair. Have you tried that? I'm not sure about the version you are using but on the later versions there is also an option to repair the files from the .frm files. I've had to go that route and it worked where all else failed.

[vincevincevince]

Following repair it says the repair has failed, and then the table becomes inaccessible. If I then restart the mySQL demon the table becomes marked In Use, and I can then successfully DROP it...

So how can I repair the files from the .frm files? I'm using windows so I'm not sure how to use mysql directly - only via PHP or similar...

[justageek]

I'm pretty sure it's a command line switch. Or you can use the control center. Once you choose the repair option there is a check box that shows up saying 'use FRM'. Check that option and all will be fine.

[coopster]

Are you sure it isn't a file permission issue? From the manual [mysql.com]:

A.3.3 Problems with File Permissions

If you have problems with file permissions, for example, if mysql issues the following error message when you create a table:

ERROR: Can't find file: 'path/with/filename.frm' (Errcode: 13)

then the environment variable UMASK might be set incorrectly when mysqld starts up. The default umask value is 0660. You can change this behaviour by starting mysqld_safe as follows:

shell> UMASK=384 # = 600 in octal
shell> export UMASK
shell> /path/to/mysqld_safe &

By default MySQL will create database and RAID directories with permission type 0700. You can modify this behaviour by setting the UMASK_DIR variable. If you set this, new directories are created with the combined UMASK and UMASK_DIR. For example, if you want to give group access to all new directories, you can do:

shell> UMASK_DIR=504 # = 770 in octal
shell> export UMASK_DIR
shell> /path/to/mysqld_safe &

In MySQL Version 3.23.25 and above, MySQL assumes that the value for UMASK and UMASK_DIR is in octal if it starts with a zero.

[justageek]

It could be, but, he's on win32.

[vincevincevince]

yes, my first thoughts were that it was a file permissions issue, then i realised i was on win32 :-p

i thought it was a problem with too many open files for the win32 handlers, so i added --open-files-limit=10 to my my.ini (mysqld-nt)

either way i've decided to reinstall the whole chebang