ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kris Jenkins <krisajenk...@yahoo.co.uk>
Subject Re: can't insert into InnoDB tables
Date Sun, 05 Dec 2004 21:45:57 GMT
Robin Johnson wrote:
> Hi, I'm new to SQL Maps and I have been successful at getting it to do 
> what I wanted until I hit a rather ugly hitch.  One of my insert 
> statements appeared to be working fine, but nothing was getting inserted 
> into the database.  At first I thought there was something wrong with 
> the way I was supplying the parameters, but I trimmed the statement down 
> until it was nothing more than the following:
> 
> <insert id="submitAttachment" parameterClass="java.util.Map">
>        insert into RESPONSE_ATTACHMENTS
>        values(NULL,'2','2','30')
> </insert>
> 
> The console log showed this, as if everything was working normally:
> 
> <DEBUG 21:48:11,444 SimpleDataSource:563> Checked out connection 8441896 
> from pool.
> <DEBUG 21:48:11,447 Connection:42> {conn-100087} Connection
> <DEBUG 21:48:11,449 PreparedStatement:48> {pstm-100088} 
> PreparedStatement: insert into RESPONSE_ATTACHMENTS 
> values(NULL,'2','2','30')
> <DEBUG 21:48:11,546 PreparedStatement:49> {pstm-100088} Parameters: []
> <DEBUG 21:48:11,548 PreparedStatement:50> {pstm-100088} Types: []
> <DEBUG 21:48:11,709 SimpleDataSource:527> Returned connection 8441896 to 
> pool.
> 
> ...but still, no row was being inserted into RESPONSE_ATTACHMENTS.  
> After much hair pulling, I discovered what the problem was.  I'm using 
> MySQL 4.0.21, sometimes using MyISAM tables (where full text search is 
> needed) and the rest of the time using InnoDB tables.  All my insert 
> statements that inserted data into MyISAM tables worked, but 
> RESPONSE_ATTACHMENTS was an InnoDB table.  I converted it to a MyISAM 
> table and submitAttachment immediately started working.  Another piece 
> of my application which uses JDBC code to insert rows (via 
> SqlMapSession.getCurrentConnection().prepareStatement()) into a 
> different InnoDB table also works.
> Am I missing something here?   I searched the mailing list archives for 
> other people with the same problem, but found nothing.  It seems like a 
> pretty glaring problem for no one else to have noticed, so I'm guessing 
> it has something to do with my configuration.
> I'm using iBATIS 2.0.7, a JDBC transaction manager and a simple 
> datasource with only the JDBC.Driver, JDBC.ConnectionURL, JDBC.Username, 
> and JDBC.Password parameters set.
> 
> Any insights would be greatly appreciated.
> 
> Thanks,
> Robin
> 

Hey Robin,

Your problem is to do with transactions, I believe.  MyISAM tables don't 
support transactions (a pox on them and all their kind), but InnoDB 
tables do.  When you insert into a MyISAM table, the data is just 
written.  With an InnoDB table you have to either commit or rollback the 
change.  I think that the MySQL driver will rollback by default, hence 
your problem.  The easiest solution is to try this as a SimpleDataSource 
property:

    <property name="JDBC.DefaultAutoCommit" value="true" />

But if the distinction between transactional and non-transactional table 
types doesn't mean much to you, take a look at this:

   http://dev.mysql.com/books/mysqlpress/mysql-tutorial/ch10.html

HTH,
Kris


Mime
View raw message