data mysteriously "shuffles" in MySQL db table

ppowell

I have a very strange case where one entire row's set of records literally "shuffled"... e.g., "phone" became "email", "email" became "address2", "address2" became "url", "url" became "resume", etc.

It has never occurred before or since, only 1 record was affected. There was no outside interference and the data was apparently never touched by anyone (according to external and MySQL logs). Ideas?

MySQL 4.0.17 DB within Linux Red Hat Enterprise Level 4

Thanx
Phil

MarkR

This won't ever happen due to MySQL. I suspect it was caused by a bug in either your application or some tools you were using.

You should try to reproduce it of course. Perhaps you're using some dodgy buggy tool (phpmyadmin?) which has done a bogus UPDATE command.

Mark

Sxooter

Did you happen to add a new column in a differen position? Just guessing. There are certain DDL commands that cause MySQL to rewrite the entire table, and there might some sneaky little race condition that bit you.

ppowell

'Fraid not. The record got scrambled apparently well after the column additions; in fact it was apparently fine until 2 days ago, the last timestamp for columnar additions was a month ago..

And there was no other messed up row in the entire table and no other tables were affected at all.

MarkR: I can't reproduce it, even while using phpMyAdmin and my own applications concurrently, the data was fine.

Phil

Sxooter

What table handler for this table, btw? Not that I've got any theories, but any little bit helps.

ppowell

All of the tables are MyISAM tables, if that helps any

Sxooter

Well, isam are the types I would expect something like to happen in if it happened during adding / moving a column.

isam tables are copied in place when you add a column or change the position of one, so I was thinking maybe one got copied while a row was held in memory and after the copy the buffers got flushed and wrote out the row in the wrong order.

It's an interesting case. Any idea what DID happen right around that time? Got a timeline for when the table was known good, known bad, and everything relavent that happened in between?

I wonder if it's possible to make a test case and flog the database with it until it cracks, if the problem is in MySQL.

ppowell

In other words, during the time that I was adding/altering this column, a row could have been added to the db table and that row could have, in theory, have been shuffled as a result?

The MySQL logs have no timeline as to when this occurred (nothing in the logs to indicate anything of the sort)

But that's a very interesting theory, unfortunately I am not sure how to test that myself at this time. Suggestions? (This would have to be tested in some dev environment and only I have access to it)

Phil

Sxooter

I'd think you'd have to set up some kind of test bed where you did the two things at the same, or about the same time. Like setup a stream that's inserting rows into the table, then have another client that adds a column in the middle of the column order and see if you can reproduce it. since it's a possible race condition, you'd have to likely do it several times to trigger it, I'd think. I'm not saying it IS mysql's fault, btw, just that given the way it works, it's a possibility.

Note that updates could just as likely cause the same kind of failure too.

MarkR

I think it's extremely unlikely that it's MySQL's fault.

The MyISAM handler uses table-level locking, that for all its criticism, is extremely safe. An ALTER TABLE or similar would have to obtain at least a write-lock for the table before doing anything. This would effectively block any other threads from accessing the table until the ALTER was complete. An ALTER TABLE on a large table could take a long time - so those threads might even time out waiting for the lock.

It is of course possible that on your SPECIFIC INSTALLATION of MySQL, the locking is horribly broken. This will only happen if your DBA blatantly ignored the things that the MySQL manual says to do to ensure that locking works right (It's pretty difficult to set up such a scenario, but not impossible).

This might include running more than one instance of MySQL against the same tables in the same network filesystem on different machines (i.e. relying on network locking).

Mark

Sxooter

I'm not saying the problem IS in MySQL, just that it's a possibility. All software has bugs (even PostgreSQL has had a few that did things like this) and if MySQL does have a bug that does this, a test case that can recreate it would be most useful.

About how big was this table, btw? a few rows, a few hundred, a few thousand, a few million???

ppowell

It has about 4,000 rows accounted for at this time. I wish I could come up with a test case for this but I'm totally incapable of reproducing the error on this one

Phil

ppowell

MarkR wrote:
I think it's extremely unlikely that it's MySQL's fault.

The MyISAM handler uses table-level locking, that for all its criticism, is extremely safe. An ALTER TABLE or similar would have to obtain at least a write-lock for the table before doing anything. This would effectively block any other threads from accessing the table until the ALTER was complete. An ALTER TABLE on a large table could take a long time - so those threads might even time out waiting for the lock.

It is of course possible that on your SPECIFIC INSTALLATION of MySQL, the locking is horribly broken. This will only happen if your DBA blatantly ignored the things that the MySQL manual says to do to ensure that locking works right (It's pretty difficult to set up such a scenario, but not impossible).

This might include running more than one instance of MySQL against the same tables in the same network filesystem on different machines (i.e. relying on network locking).

Mark

I hate to tell you this, but you're assuming we HAVE a DBA, which we do not.

Phil

Sxooter

MarkR wrote:
It is of course possible that on your SPECIFIC INSTALLATION of MySQL, the locking is horribly broken. This will only happen if your DBA blatantly ignored the things that the MySQL manual says to do to ensure that locking works right (It's pretty difficult to set up such a scenario, but not impossible).

This might include running more than one instance of MySQL against the same tables in the same network filesystem on different machines (i.e. relying on network locking).

Mark

I was wondeing what kind of interlocking MySQL has to prevent you from running two instances at the same time. PostgreSQL uses a postgresql.pid file, which works but can make bringing the database up automagically at boot after some kinds of crash manual intervention prone.

Plus, if you ran two copies of MySQL against the same data store, I'd expect more than one row slightly shuffled. I'd expect LOTS of rows shuffled, duped, deleted, etc...

ppowell

Sxooter wrote:
I was wondeing what kind of interlocking MySQL has to prevent you from running two instances at the same time. PostgreSQL uses a postgresql.pid file, which works but can make bringing the database up automagically at boot after some kinds of crash manual intervention prone.

Plus, if you ran two copies of MySQL against the same data store, I'd expect more than one row slightly shuffled. I'd expect LOTS of rows shuffled, duped, deleted, etc...

Nope, that's not the case here. ONE copy of MySQL, one machine, I even do INSERT /! LOW_PRIORITY / with new application entries per instance so they do some low-level form of "queueing". And like I said (and Mark said), it's not "lots of rows", it's just ONE ROW, and that's it. And in fact has not happened before or since (this occurred a couple of weeks ago)

Phil

MarkR

Sxooter wrote:
I was wondeing what kind of interlocking MySQL has to prevent you from running two instances at the same time.

I've no idea- but it works.

Plus, if you ran two copies of MySQL against the same data store, I'd expect more than one row slightly shuffled. I'd expect LOTS of rows shuffled, duped, deleted, etc...

Actually believe it or not, with some table handlers, this is a supported configuration. Provided you don't use --skip-external-locking, then MySQL SHOULD correctly use the OS file locking to prevent concurrent access to tables.

It may break a few things though.

But it works with embedded mysql too.

Although I think this has very little (i.e. probably nothing) to do with the OP's problem.

I've found MySQL to be very robust - although earlier versions did have some peculiar behaviour when running out of disc space (nothing that lost any data though).

Mark

ppowell

I should mention that this is MySQL 4.0.17 for Linux Red Hat Enterprise Level 4

So it's very recent.

Phil