Dear Heri, > 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.
Does the second shell actually perform those changes? In this case, I assume it's got something to do with the isolation level / consistent read in InnoDB tables. "shell1" sees all its changes immediately, "shell2" (the application) has just a snapshot of the data at the time it performs the select. This should however only be true for the select the first time the application loops. But if it uses the same conn = mysql_init(NULL); (I am not a C programmer), it will keep the same isolation level, and thus, the same snapshot. Hope it helps, -- Stefan Hinz <[EMAIL PROTECTED]> CEO / Geschäftsleitung iConnect GmbH <http://iConnect.de> Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 ----- Original Message ----- From: "H. Steuer" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, December 02, 2002 10:29 PM Subject: C API problems with InnoDB > 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 > --------------------------------------------------------------------- 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
