Inserted record doesn't seem to be commited

[bunnytail]

greeting,


In short, I have a database data directory, 2 Mysql daemon are running
parallel over it through different port and sock. Now I found a problem
that for daemon "A" its read and write are performing well, but for
daemon "B", its read operation is fine, but when I issue "insert"
statement with "B", the new record is cache:
1, from daemon B, I can see the record with "select" statement.
2, no warning/error messge after the insert operation.
3, from daemon A, I can't see the record! Data is not writen to the disk.
4, after daemon B is stop and close, record gone and next time if B will
come up again, record is not there of cause.


I've checked the unix level read/write access...all seem fine.

Why the record is not commited to the disk?
How to solve, or trace?


regards,
sheng

[Bill Karwin]

Re: Inserted record doesn't seem to be commited

bunnytail wrote:[color=blue]
> In short, I have a database data directory, 2 Mysql daemon are running
> parallel over it through different port and sock.[/color]

Don't run two instances of MySQL that use the same data directory. You
will certainly corrupt your databases!

The database process keeps some data in its memory, and writes it to
disk when it can do so efficiently. This is the point of caching. But
it means that the data on disk is not necessarily the current view of
the database while the MySQL process is running.

When you inserted a record with daemon "B", it either conflicted with a
file lock and failed without checking for error (because it assumes it
is the only one writing to a database file), or else daemon "A"
immediately overwrote the change that "B" made. This might have been
coincedental; you shouldn't rely on it.

In any case, I'm surprised it worked as well as it did. It's not
unlikely that the database would be rendered unusable by both daemons
when you try operations like you did.

If you need two MySQL instances for load-balancing or failover, they
*MUST* each use their own data directory. Use MySQL Replication if you
need to keep databases in sync.

Regards,
Bill K.