Hello MySQL users,
I have a weired issue using the MySQL C API and InnoDB tables.
An application polls a database every 30 seconds. When the application
starts everything seems to be fine.
During the running of the application i change some rows, but the
application itself doesnt see the changes at all.
I tracked down the problem and saw that its only happening if I set
autocommit=0.
If I run a second mysql shell I can see all changes immediately. Just the
application itself doesnt.
Here are the important parts of the code without any exception catching.
Just wanted to show the steps I did for querying the database.
conn = mysql_init(NULL);
mysql_real_connect(
conn,
hostname,
username,
password,
dbname,
0,
NULL,
0)
== NULL);
mysql_query(conn,"SET AUTOCOMMIT=0");
... so far for the preparing of the connection. now for the interresting
part. the following query runs in an loop :
mysql_query(conn,"SELECT
a.id,k.fix_SenderCompID,k.fix_TargetCompID,k.fix_version FROM aorder AS
a,kontrahenten AS k WHERE status='active' AND fix_status='queued_send' AND
k.id=a.kontrahent")
result = mysql_store_result(conn);
while ( ( row = mysql_fetch_row(result) ) != NULL ) {
// do something with the data
}
mysql_free_result(result);
sleep(30);
okay, when running this application does not see the changes to the
database - for what reason ever.
If I drop the SET AUTOCOMMIT=0 it works fine.
When inserting 'mysql_query(conn,"COMMIT;") ' after the mysql_store_result
everything seems to work fine, too.
But theres only a select statement - nothing that changes data at all. why
doesnt the application see the changes without the commit ?
I wrote a small test application without any other code, just this simple
query - the same result.
Can anyone explain this thing to me ? Why is there a COMMIT needed after a
select ?
When stopping the application and restarting it immediately the changes are
visible, too.
I dont get any further - hope one of you guys can help.
Thanks in advance,
Heri
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
