Updates not visible to client

[Martin Woolley]

Hi, could someone explain the following please?

I have a Java application (app A) which "polls" a MySQL database table
for records with a certain column set to 0. It runs continuously,
utilising the same commection repeatedly.

The other day, I had a support call raised, whereby there were clearly
suitable records in the database, but my application was not selecting
them.

I wrote a standalone test app (app B) to read the same table and list
the records with the column=0. Sure enough, this app listed records as
requested.

So, app A retrieved zero records whilst app B retrieved many. Both
apps *are* using the same select statement.

I think it might be something to do with the rules governing when
updates become visible to clients.

The application that writes records to the database is not mine so I
don't know all the details. It's a php webapp that seems pretty simple
and just does plain inserts into the table with no attempt to do
anything non-default wrt locking afaik.

Any useful comments or clues to help explain this most gratefully
received.

Regards

Martin

[Aggro]

Martin Woolley wrote:

I think it might be something to do with the rules governing when
updates become visible to clients.

There are no such rules. The error is propably in the app A, which
either fails to do the queries, or fails to show the results to user.

[Martin Woolley]

Aggro <sp**********@yahoo.com> wrote in message news:<l1***************@read3.inet.fi>...

There are no such rules. The error is propably in the app A, which
either fails to do the queries, or fails to show the results to user.

There most certainly are such rules. They relate to the way that locks
are applied and associated updates made visible. See
http://dev.mysql.com/doc/mysql/en/an...nsactions.html for some
comment on this.

App A runs 24x7 and has exhibited no issues in this area at all until
this one incident occured. If there's an issue with this application
then it is something subtle. All it does is

1. Get connection
2. Repeat
2.1 select * from table where condition limit n
2.2 sleep 5 seconds
2.3 Repeat this loop forever

And like I said, this one time, App A executing the select in 2.1
retrieved zero records whilst App B executing the same select
retrieved some records.

What I haven't found out yet is what storage model the MySQL database
is using.

Anyway, the issue may have nothing to do with locks either, I just
wanted to explore this with any real experts out there (ie people who
at least are aware of such issues).

[Aggro]

Martin Woolley wrote:

There most certainly are such rules. They relate to the way that locks
are applied and associated updates made visible. See
http://dev.mysql.com/doc/mysql/en/an...nsactions.html for some
comment on this.
Yes I know that, but you explained that the PHP app doen't use any of
those. I ment that there are no hidden rules that would cause the
behaviour you explained.
1. Get connection
2. Repeat
2.1 select * from table where condition limit n
2.2 sleep 5 seconds
2.3 Repeat this loop forever

You never reset the connection? What if there is a network failure and
client gets disconnected? Or there might be some mystic connection
buffer that gets full over the time.

Did the application get the new data after it was restarted?

[Martin Woolley]

Aggro <sp**********@yahoo.com> wrote in message news:<D3***************@read3.inet.fi>...

Martin Woolley wrote:

There most certainly are such rules. They relate to the way that locks
are applied and associated updates made visible. See
http://dev.mysql.com/doc/mysql/en/an...nsactions.html for some
comment on this.

Yes I know that, but you explained that the PHP app doen't use any of
those. I ment that there are no hidden rules that would cause the
behaviour you explained.

1. Get connection
2. Repeat
2.1 select * from table where condition limit n
2.2 sleep 5 seconds
2.3 Repeat this loop forever

You never reset the connection? What if there is a network failure and
client gets disconnected? Or there might be some mystic connection
buffer that gets full over the time.

Did the application get the new data after it was restarted?

Thanks for clarifying.

If there's network failure then the application takes care of
reconnecting itself. This actually happens quite frequently (at least
once a day) for reasons I won't go into, and the recovery code seems
quite reliable and robust. As it's a java app it's reliant on the
MySQL JDBC driver to report such issues. I did note yesterday that the
driver has an auto-reconnect parameter I could set, but at present I
do not and attempt to handle all such issues in the application.

The application did get the new data after it was restarted. So it
does sound like maybe the old connection was invalidated in some way.
What do you think? I don't understand how it could become
"invalidated" without this resulting in an Exception being thrown by
the JDBC driver.

[Aggro]

Martin Woolley wrote:

The application did get the new data after it was restarted. So it
does sound like maybe the old connection was invalidated in some way.
What do you think?

I would first test how the application handles situation where network
cable in unplugged and then reattached after at least one failed query.

If the application doesn't handle that situation I would make a guess
that the problem is there. If it handles that situation I might try to
test other possible scenarios, like database server shutdown -> start
situation.

If the application handles all that then I could only wonder what the
reason was. And then I would try to add some debug data to the
application to find out more of the cause, next time the connection fails.

[Martin Woolley]

Aggro <sp**********@yahoo.com> wrote in message news:<7T************@read3.inet.fi>...

Martin Woolley wrote:

The application did get the new data after it was restarted. So it
does sound like maybe the old connection was invalidated in some way.
What do you think?

I would first test how the application handles situation where network
cable in unplugged and then reattached after at least one failed query.

If the application doesn't handle that situation I would make a guess
that the problem is there. If it handles that situation I might try to
test other possible scenarios, like database server shutdown -> start
situation.

If the application handles all that then I could only wonder what the
reason was. And then I would try to add some debug data to the
application to find out more of the cause, next time the connection fails.

Thanks for these suggestions. I've tested these scenarios
previously.... but I wonder if there's some specific combination of
conditions in these situations... which I have not tested. OK, so no
magic solution.... looks like the only way to solve this is to work
hard to try and recreate the issue in my test environment.

Regards

Martin

[Martin Woolley]

ma****@woolleynet.com (Martin Woolley) wrote in message news:<e7**************************@posting.google. com>...

Aggro <sp**********@yahoo.com> wrote in message news:<7T************@read3.inet.fi>...

Martin Woolley wrote:

The application did get the new data after it was restarted. So it
does sound like maybe the old connection was invalidated in some way.
What do you think?

I would first test how the application handles situation where network
cable in unplugged and then reattached after at least one failed query.

If the application doesn't handle that situation I would make a guess
that the problem is there. If it handles that situation I might try to
test other possible scenarios, like database server shutdown -> start
situation.

If the application handles all that then I could only wonder what the
reason was. And then I would try to add some debug data to the
application to find out more of the cause, next time the connection fails.

Thanks for these suggestions. I've tested these scenarios
previously.... but I wonder if there's some specific combination of
conditions in these situations... which I have not tested. OK, so no
magic solution.... looks like the only way to solve this is to work
hard to try and recreate the issue in my test environment.

Regards

Martin

I don't quite have proof but.... the incident occurred again, this
time with some extra debug in App A. The debug suggests that the
database read loop is blocking somewhere. My guess therefore (to be
confimed by even more debug now that I know where to put it) is that
it's sitting there waiting for the SQL execution to return... or
something like this. If the issue is that the connection has become
"stale" then I don't understand why I'm not getting a SQLException
from the JDBC driver.
http://dev.mysql.com/doc/connector/j...html#id3029128
describes what's meant to happen.

Any thoughts on why blocking might occur?

Martin