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:Microsoft Windows (Windows)
Assigned to: Heikki Tuuri

[19 Jan 2005 21:02] Dean Ellis
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
[19 Jan 2005 22:43] Heikki Tuuri
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
[19 Jan 2005 23:22] Heikki Tuuri
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
[20 Jan 2005 1:40] Jairsinho Ramirez Ruiz
Hi, I am having the same problem. Will this "bug" be fixed in 4.1.10?

   Best Regards.
[20 Jan 2005 7:10] Heikki Tuuri
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);
        }
[20 Jan 2005 7:23] Heikki Tuuri
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
[21 Jan 2005 17:31] Heikki Tuuri
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
[26 Jan 2005 17:54] Heikki Tuuri
Hi!

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

--Heikki
[2 Feb 2005 5:59] Jairsinho Ramirez Ruiz
That's right this workaroud works fine in windows xp :D

Thank you!
[15 Feb 2005 21:34] Heikki Tuuri
Fixed in 4.1.10.
[28 Feb 2007 16:23] Heikki Tuuri
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.