Consistency among multiple connections

[Sergio]

Hi everybody,

I have the following scenario. Several computers with shared disk in a
LAN. Each of these computer has a MySQL server that serves several
databases. I have several clients that communicate with a Java process
that I have in each computer to answer queries for a given database
(it connects to the MySQL server). I also have a Java process that
updates continuously the databases; this process executes only in the
local computer: it takes advantage from the fact that the disk is
shared in order to update all the databases as quick as possible when
it is needed.

I am observing some problems:

1) It seems to be a consistency problem. Updates that are performed by
the process that updates the databases are not always seen by the
clients.

2) Sometimes, it seems that some tables get corrupted, as clients get
errors relative to the table handler.

Do I need to do something special to ensure the consistency? Does this
scenario make sense to you? I know that I could use different
processes in each computer to perform the updates (not taking
advantage of the shared disk) but then I would have to synchronize
these processes to perform the updates at the same time (the updates
are the result of a simulation, and they must be performed at specific
relative times).

Thanks in advance for any suggestion,

Sergio

[Bill Karwin]

Sergio wrote:

it takes advantage from the fact that the disk is
shared in order to update all the databases as quick as possible when
it is needed.
Well, quick is nice, but if you're occasionally causing corruption, the
time taken to recover the database probably puts a dent in any
performance advantage. ;-)
1) It seems to be a consistency problem. Updates that are performed by
the process that updates the databases are not always seen by the
clients.
Each instance of mysqld keeps a cache containing recent modifications to
the database. When you do a query, mysqld may get part of the result
from disk, and also amend that with its current knowledge of updates
that haven't been written to disk yet. This means that a _second_
instance of mysqld accessing the same database directly doesn't have any
way of knowing what, if any, changes are floating in the cache of the
first instance of mysqld.
2) Sometimes, it seems that some tables get corrupted, as clients get
errors relative to the table handler.
Not surprising, given the above.
Do I need to do something special to ensure the consistency? Does this
scenario make sense to you?

This scenario is, as you have discovered, practically guaranteed to
corrupt your databases on a regular basis. I recommend strongly against
accessing databases from more than one MySQL engine. Two engines that
are not synchronizing their operations are very likely to make
conflicting updates to the files.

You said you're using Java to access MySQL. You must specify the
hostname of the database server in your JDBC URLs, so that you can let
the MySQL daemon on that host do the I/O on all the databases running
locally on that server. Like so:

Connection conn = DriverManager.getConnection(
"jdbc:mysql://mydbserver/test?user=mydbuser&password=xxxxxxxx");

This is the most reliable way (perhaps the *only* way) to run client
applications on multiple machines, and access the databases
simultaneously, without corrupting the database. That's why there is a
server hostname field in the JDBC URL!

Regards,
Bill K.