MySQL connector issue

[Peter Otten]

Joseph L. Casale wrote:

> 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?

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)