MySQL Bugs: #8021: Windows error number 87 with multiple tablespaces after mysqld restart

Bug #8021	Windows error number 87 with multiple tablespaces after mysqld restart
Submitted:	19 Jan 2005 21:02	Modified:	15 Feb 2005 21:34
Reporter:	Dean Ellis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	4.1.9	OS:	Windows (Windows)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
When using innodb_file_per_table, changing the default-character-set to utf8 (from latin1 or default) causes error number 87 (invalid paramter, presumably) when reading tables that were created with a different default.

How to repeat:
my.ini:
[mysqld]
innodb_file_per_table
default-character-set=latin1

CREATE TABLE t1 ( a varchar(20) ) ENGINE=InnoDB;
INSERT INTO t1 VALUES ( 'test' );

my.ini:
[mysqld]
innodb_file_per_table
default-character-set=utf8

SELECT * FROM t1;

Suggested fix:
n/a

Hi!

I guess the bug is associated with what character set Windows expects InnoDB to use in the directory path to the .ibd file.

We will compile 4.1.9 on Windows tomorrow and check what is the problem.

Regards,

Heikki

Hi!

I am able to repeat the bug without any default-character-set in my.cnf. Just restarting mysqld is enough.

I guess the bug is due to some change I made when I fixed the critical innodb_file_per_table bug in 4.1. Looks like I introduced another critical bug in the Windows version.

Regards,

Heikki

Hi, I am having the same problem. Will this "bug" be fixed in 4.1.10?

   Best Regards.

Hi!

I think I found the bug. It was caused by the bug fix of the critical bug #7496
http://bugs.mysql.com/bug.php?id=7496.

On Windows, fil_node_open_file() opens the .ibd file in the async I/O mode, but tries to read the space id from the file with a normal file read, not an async read. That is not allowed in Windows.

This bug will be fixed in 4.1.10. Till then, Windows users have to stick to 4.1.8 if they use the innodb_file_per_table option in my.cnf. But beware of CRITICAL BUG #7496 in 4.1 then.

Regards,

Heikki

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/fileio/base/readfile.asp

fil_node_open_file(
/*===============*/
        fil_node_t*     node,   /* in: file node */
        fil_system_t*   system, /* in: tablespace memory cache */
        fil_space_t*    space)  /* in: space */
{
        ib_longlong     size_bytes;
        ulint           size_low;
        ulint           size_high;
        ibool           ret;
        byte*           buf2;
        byte*           page;
        ibool           success;
        ulint           space_id;

#ifdef UNIV_SYNC_DEBUG
        ut_ad(mutex_own(&(system->mutex)));
#endif /* UNIV_SYNC_DEBUG */
        ut_a(node->n_pending == 0);
        ut_a(node->open == FALSE);

        /* printf("Opening file %s\n", node->name); */

        if (space->purpose == FIL_LOG) {
                node->handle = os_file_create(node->name, OS_FILE_OPEN,
                                        OS_FILE_AIO, OS_LOG_FILE, &ret);
        } else if (node->is_raw_disk) {
                node->handle = os_file_create(node->name,
                                        OS_FILE_OPEN_RAW,
                                        OS_FILE_AIO, OS_DATA_FILE, &ret);
        } else {
                node->handle = os_file_create(node->name, OS_FILE_OPEN,
                                        OS_FILE_AIO, OS_DATA_FILE, &ret);
        }

Hi!

Another workaround in 4.1.9 is to set

innodb_flush_method=unbuffered

in my.cnf.

Then InnoDB uses normal sync I/O also on Windows, and the error does not happen.

Regards,

Heikki

Hi!

The bug is now fixed in the 4.0.10 bk tree. But I still have to test on Windows to make sure it works this time.

The

innodb_flush_method=unbuffered

workaround is a good one. Better to run on Windows 4.1.9 than the critically buggy 4.1.8.

--Heikki

Hi!

I have now tested on Windows XP and Linux the patch that I pushed to 4.1.

--Heikki

That's right this workaroud works fine in windows xp :D

Thank you!

Fixed in 4.1.10.

This error was reported by a user recently from a recent MySQL/InnoDB version. Error 87 in his case appeared to be a result of a Veritas snapshot program in combination with a bad SATA driver.