MySQL connector issue

[Joseph L. Casale]

I have some code that I am testing on Windows without c extensions which
runs on a RHEL server with c extensions. In a simplified test case as follows:

connection = mysql.connector.connect(...)
cursor = connection.cursor(cursor_class=MySQLCursorDict)
while True:
cursor.execute('SELECT foo,biz FROM bar WHERE baz IS NULL)
rows = cursor.fetchall()
print(rows)

cursor.execute('UPDATE bar SET baz=42 WHERE baz IS NULL')
connection.commit()

sleep(.5)

This works on Windows, the select query consistently returns new results as they
appear in the database when generated by other applications.

However on the RHEL server, the initial select only produces a result on the first
iteration and then as new results are written to the database, the select does not
find results?

What might be the issue?

Thanks,
jlc

[Peter Otten]

Perhaps you simplified too much, but changes between the select and the
update could be lost. I think you need at least three states:

1 mark rows where baz is null (by setting baz to some value other than NULL
or 42, 24, say: set baz = 24 where baz is NULL)
2 show marked rows (select ... where baz = 24)
3 mark rows as seen (set baz = 42 where baz = 24)

[Joseph L. Casale]

> Perhaps you simplified too much, but changes between the select and the update could be lost. I think you need at least three states:
>
> 1 mark rows where baz is null (by setting baz to some value other than NULL or 42, 24, say: set baz = 24 where baz is NULL)
> 2 show marked rows (select ... where baz = 24)
> 3 mark rows as seen (set baz = 42 where baz = 24)

Hi Peter,
The UPDATE happens only if rows were found, the sleep is much longer as it
is expected that another application adds rows satisfying the SELECT.

It really is that simple which is why I am baffled. Given the throughput is so
low, if I close the cursor and connection at the end of loop and instantiate them
both at the start of the loop, it works as expected but that's obviously not optimal.

Thanks,
jlc

[Chris Angelico]

On Mon, Oct 24, 2016 at 3:46 AM, Joseph L. Casale
<jca...@activenetwerx.com> wrote:
> It really is that simple which is why I am baffled. Given the throughput is so
> low, if I close the cursor and connection at the end of loop and instantiate them
> both at the start of the loop, it works as expected but that's obviously not optimal.

Interesting. Generally, I allocate cursors exactly at the same time as
I open transactions; not sure if this works with the mysql connector,
but with psycopg2 (PostgreSQL), my code looks like this:

with conn, conn.cursor() as cur:
cur.execute(...)
... = cur.fetchall()

The 'with' block guarantees that (a) the cursor will be closed and all
resources freed, and (b) the transaction will be committed or rolled
back (on exception, roll back, otherwise commit), at the unindent.
It's a nice, clean way to operate. Never had problems with it.

ChrisA

[Joseph L. Casale]

> Interesting. Generally, I allocate cursors exactly at the same time as I open transactions;
> not sure if this works with the mysql connector, but with psycopg2 (PostgreSQL), my code looks like this:
>
> with conn, conn.cursor() as cur:
> cur.execute(...)
> ... = cur.fetchall()
>
> The 'with' block guarantees that (a) the cursor will be closed and all resources freed, and (b) the
> transaction will be committed or rolled back (on exception, roll back, otherwise commit), at the unindent.
> It's a nice, clean way to operate. Never had problems with it.

Ditto, however the official Oracle python module lacks context managers.

Regardless, using try/finally and creating and closing cursors for both the query and the update
still did not help. The connection must be reset in order for the query to see new results.
That sounds ridiculous, certainly I overlooking something in a connection parameter possibly?

Thanks,
jlc