Setting autocommit to 0 on a select statement does not an explanation for this at all. You do not need to have a commit on a select statement as it is not changing the db in any way. The insert is happening before for the select so the commit you need is for the insert not the select. If the insert has not been committed then the select will not see it. So it is your insert statement that requires the autocommit not the select that follows it.
This is from the MySQL documentation:
<SNIP>
In InnoDB, all user activity occurs inside a transaction. If the autocommit mode is enabled, each SQL statement forms a single transaction on its own. MySQL always starts a new connection with autocommit enabled.
If the autocommit mode is switched off with SET AUTOCOMMIT = 0, then we can consider that a user always has a transaction open. A SQL COMMIT or ROLLBACK statement ends the current transaction and a new one starts. Both statements release all InnoDB locks that were set during the current transaction. A COMMIT means that the changes made in the current transaction are made permanent and become visible to other users. A ROLLBACK statement, on the other hand, cancels all modifications made by the current transaction.
If the connection has autocommit enabled, the user can still perform a multiple-statement transaction by starting it with an explicit START TRANSACTION or BEGIN statement and ending it with COMMIT or ROLLBACK.
</SNIP>
It looks like the insert is probably not doing the commit. The select does not need to. The oddity here is that a commit from a select in a totally different connection should have no bearing on an insert statement in a different application and connection.
Phil
On 24/03/2005, at 12:38 PM, John McGowan wrote:
> Jason Schulz wrote:
>> What happens when you use a third tool to query the data - does the data
>> returned to it match Witango or the MySQL CC?
>>
>> It's not clear from what you've written, but are you sure that MySQL CC is
>> committing its transactions?
>
> Yes, It's commiting the transactions... I just verified from a trace of the
> JDBC driver that Witango is setting autocommit = 0� Which is a perfectly
> logical explanation for the behaviour I'm seeing.
>
> We'll see what Witango has to say...
>
> /John
________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
