You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Robin Johnson <ro...@syndicom.com> on 2004/12/05 06:38:44 UTC
can't insert into InnoDB tables
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
Re: can't insert into InnoDB tables
Posted by Robin Johnson <ro...@syndicom.com>.
Thanks Kris,
You were right- it was a transaction-related problem.
Setting JDBC.DefaultAutoCommit to true didn't help, but I looked at my
code again and discovered that it didn't call
SqlMapClient.commitTransaction() before endTransaction(). Adding a
commitTransaction() call fixed my problem.
*sigh*, I'm learning....
Thanks again,
Robin
Kris Jenkins wrote:
> 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
>
>
Re: can't insert into InnoDB tables
Posted by Kris Jenkins <kr...@yahoo.co.uk>.
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