bunnytail wrote:
In short, I have a database data directory, 2 Mysql daemon are running
parallel over it through different port and sock.
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.