MySQL Bugs: #10087: myisamchk changes MySQL-Servers behaviour

Bug #10087	myisamchk changes MySQL-Servers behaviour
Submitted:	22 Apr 2005 9:39	Modified:	27 Jul 2005 2:59
Reporter:	[ name withheld ]	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.0.24	OS:	Linux (Linux 2.4.18)
Assigned to:	Lachlan Mulcahy	CPU Architecture:	Any

Description:
When I run the tool myisamchk once as follows: "myisamchk --silent --fast /var/lib/mysql/*/*.MYI" (also occurs when using "myisamchk -r --silent --sort-index -O sort_buffer_size=16M /var/lib/mysql/*/*.MYI" as described in the manual), then after I start the mysqld again, something with my mysql has changed. When I start the mysql-console and run the following queries:

use <database>
CREATE TABLE x1 (x2 DOUBLE);
INSERT INTO x1 VALUES(2.34);

then a SELECT for x1 returns me a row containing a "2" and not as expected a 2.34. When I do the following query

INSERT INTO x1 VALUES('2,34')

a SELECT returns me a 2,34 (right, it shows me a comma not a dot as the decimal separator).

After I reboot the whole system (just restarting mysqld doesn't change anything) the behaviour switched back to the usual behaviour. My first INSERT puts really a 2.34 into the db. I can reproduce this behaviour. I'm sure it has something to do with myisamchk as I run this MySQL-Server for over an year now without problems and two days ago was the first time I used this myisamchk-Tool and after that this weired behaviour came up.

BTW.: Tested this also with a 4.0.24 on a Windows-Box - here I encountered no Problems.
Also tested on another Linux Box (older one with Kernel 2.2 and MySQL 3.23) - also no problems.

How to repeat:
As described above, run myisamchk and let him do it st. with the Index-Files. BTW: A simple myisamchk --help doesn't do anything to the Server, so it seems that myisamchk has to touch the files to produce this error.

Suggested fix:
Don't use myisamchk, if you encounter this behaviour in combination with him or don't start mysqld after myisamchk finished but reboot your system, if possible.

You say that running myisamchk over all tables in your datadir is linked to the behaviour that you describe. However, as you may know, myisamchk works directly on the files themselves and not via the server.

If you are doing the CREATE TABLE test as you describe after you run this then the myisamchk will have never touched the newly created table. This seems strange indeed -- also, the fact that a system reboot is required suggests this may be something in the OS layer, rather than a MySQL software problem.

Can you let me know what Linux Distribution this is manifesting on? eg. Redhat/Debian, what version specifically, etc?

the Distribution on that this error occurs is Debian Woody 3.0R1

I have tried and failed to reproduce this problem on a fresh install of Debian 3.0r1 on an x86 machine -- though I suspect your installation significantly differs from a standard install given that you report using Linux kernel version 2.4.18 and 2.2.20 is what comes with 3.0r1.

I followed these steps:

1. Install and setup MySQL 4.0.24 using the following pc-linux-i686 tarball as per instructions in the INSTALL-BINARY
2. Start mysql_safe
3. Run following queries:
USE test;
CREATE TABLE preIsamchk (field DOUBLE);
INSERT INTO preIsamchk VALUES (2.34);
SELECT field FROM preIsamchk;
# The expected 2.34 is returned

INSERT INTO preIsamchk VALUES ('2,32');
SELECT field FROM preIsamchk;
# The original row is returned plus a new row with a value of '2' -- as expected.

4. Shutdown the MySQL Server
5. Run the myisamchk command:
myisamchk --silent --fast /usr/local/mysql/data/*/*.MYI
6. Restart mysqld_safe
7. Run following queries:
USE test;
CREATE TABLE postIsamchk (field DOUBLE);
INSERT INTO postIsamchk VALUES (2.34);
SELECT field FROM postIsamchk;
# The expected 2.34 is returned

INSERT INTO postIsamchk VALUES ('2,32');
SELECT field FROM postIsamchk;
# The original row is returned plus a new row with a value of '2' -- as expected.

SELECT field FROM preIsamchk;
# The expected rows with values '2' and '2.34' are returned as expected.

If you have any further information or ideas on how to repeat this, please update this bug and set the status back to open. For now, I am unable to repeat the problem you describe.

Thank you for your interest in MySQL!