I am having a nightmare of a time with transactions in my application. I need to wrap updates to may unrelated tables in a single transaction. I am developing in Kylix so I needed to customize the DataSetProvider component to not automatically launch a transaction for every update (thus precluding multi-table updates). Once I finished this, I thought I was past my problem but transaction rollbacks are still not working.
I am using MySQL 3.23.54a-4 and InnoDB tables. When I do a rollback from the mysql client, all works well. I issue: begin; update statements rollback; and all my updates are undone - perfect! However, all updates succeed in my application even when I force a rollback. I captured the SQL calls that Kylix is issuing and they all look perfect. Here they are: MySQL - mysql_init MySQL - mysql_real_connect select * from Accessors_X509 MySQL - mysql_real_query MySQL - mysql_field_count MySQL - mysql_use_result MySQL - mysql_fetch_field SHOW INDEX FROM Accessors_X509 MySQL - mysql_real_query MySQL - mysql_field_count MySQL - mysql_use_result MySQL - mysql_fetch_field MySQL - mysql_fetch_row MySQL - mysql_fetch_lengths MySQL - mysql_fetch_row MySQL - mysql_fetch_lengths MySQL - mysql_fetch_row MySQL - mysql_fetch_row MySQL - mysql_fetch_lengths select * from Accessors_IP MySQL - mysql_real_query MySQL - mysql_field_count MySQL - mysql_use_result MySQL - mysql_fetch_field SHOW INDEX FROM Accessors_IP MySQL - mysql_real_query MySQL - mysql_field_count MySQL - mysql_use_result MySQL - mysql_fetch_field MySQL - mysql_fetch_row MySQL - mysql_fetch_lengths MySQL - mysql_fetch_row MySQL - mysql_free_result select * from Accessors_X509 MySQL - mysql_real_query MySQL - mysql_field_count MySQL - mysql_use_result MySQL - mysql_fetch_field MySQL - mysql_fetch_row MySQL - mysql_fetch_lengths MySQL - mysql_fetch_row MySQL - mysql_fetch_lengths MySQL - mysql_fetch_row MySQL - mysql_fetch_lengths MySQL - mysql_fetch_row MySQL - mysql_fetch_lengths MySQL - mysql_fetch_row MySQL - mysql_fetch_lengths MySQL - mysql_fetch_row MySQL - mysql_free_result SET AUTOCOMMIT=0; MySQL - mysql_real_query MySQL - mysql_field_count MySQL - mysql_affected_rows BEGIN WORK; MySQL - mysql_real_query MySQL - mysql_field_count MySQL - mysql_affected_rows MySQL - mysql_escape_string MySQL - mysql_escape_string MySQL - mysql_escape_string update Accessors_IP set Range = '10.2.39.0-10.2.39.8' where Range = '10.2.39.0-10.2.39.7' and Comment is null and PEP_Name = '0' MySQL - mysql_real_query MySQL - mysql_field_count MySQL - mysql_affected_rows ROLLBACK; MySQL - mysql_real_query MySQL - mysql_field_count MySQL - mysql_affected_rows SET AUTOCOMMIT=1; MySQL - mysql_real_query MySQL - mysql_field_count MySQL - mysql_affected_rows MySQL - mysql_escape_string MySQL - mysql_escape_string MySQL - mysql_escape_string update Accessors_X509 set DN = 'C=*,L=*,O=Nexus,OU=HD,CN=*' where DN = 'C=*,L=*,O=Nexus,OU=*,CN=*' and CA = 'C=US,O=Nexus,OU=Security,CN=MC-CA1\n' and Comment is null MySQL - mysql_real_query MySQL - mysql_field_count MySQL - mysql_affected_rows I've tried it with and without ";" at the end of the commands. I've tried it in lower case and upper case. I've tried it with just "begin" and with "set autocommit = 0". After a week and a half of beating my head against the wall, I'm getting a little dazed! Any help would be greatly appreciated. Thanks - John -- John A. Sullivan III Chief Technology Officer Nexus Management +1 207-985-7880 [EMAIL PROTECTED] --- If you are interested in helping to develop a GPL enterprise class VPN/Firewall/Security device management console, please visit http://iscs.sourceforge.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
