MYSql Autocommi

[reddeagle]

Hi There,
Have tried the Hibernate forum on this but no avail. I am trying the following.
e.g.
public void saveProduct(Product p) {
getHibernateTemplate().saveOrUpdate(p);
}
Hibernate Mapping File Sample

<class 
    name="Product" 
    table="products"
>
    <meta attribute="class-description" inherit="false">
       @hibernate.class
        table="products"
    </meta>

    <id
        name="id"
        type="java.lang.Integer"
        column="ID"
    >
        <meta attribute="field-description">
           @hibernate.id
            generator-class="native"
            type="java.lang.Integer"
            column="ID"

        </meta>
        <generator class="native" />
    </id>

I get the folllwing error:

Translating SQLException with SQLState 'null' and errorCode '0' and message [Can't call commit when autocommit=true]; SQL was [null] for task [HibernateAccessor]

I set autcommit in MYSql to be false as follows.

set autocommit=0;

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0     |
+--------------+

Any Ideas would be helpful on this one. I can read fine from the datbase its the inserts thats causing me pain.

Thanks in advance.

[Rod Johnson]

How do you configure your connection pool? Pools like Commons DBCP expose autocommit as a property that you can set in the DataSource bean definition in Spring. If you use an application server's pool, autocommit will normally be switched off by default. You should really configure autocommit at connection pool level, not database level.

Rgds
Rod

[reddeagle]

Hi Rod,
Appreciate the prompt reply on this one. its much appreciated.

How do you configure your connection pool? Pools like Commons DBCP expose autocommit as a property that you can set in the DataSource bean definition in Spring.

  <bean id="myDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName"><value>org.gjt.mm.mysql.Driver</value></property>
        <property name="url"><value>jdbc&#58;mysql&#58;//localhost/dbname?relaxAutoCommit=true</value></property>
        <property name="username"><value>user</value></property>
        <property name="password"><value>pass</value></property>
    </bean>

I havnt seen any posts like this out there, which might suggest i am doing something wrong and could possible be the case?

Thanks in advance.
Slán

[Rod Johnson]

Don't use DriverManagerDataSource. Use a connection pool. Switch off autocommit as follows:

<bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
          <property name="driverClassName"><value>org.gjt.mm.mysql.Driver</value></property>
          <property name="url"><value>jdbc&#58;mysql&#58;//localhost/dbname</value></property>
          <property name="username"><value>user</value></property>
          <property name="password"><value>pass</value></property>
         <property name="defaultAutoCommit"><value>false</value></property>
</bean>

Note the defaultAutoCommit property of Commons pool, which sets the default autocommit value for all connections in the pool.

That example uses Apache Commons DBCP, as in Spring samples such as PetStore. You'll need the Commons DBCP JAR on your path. You could equally well use another pool such as C3P0. Refer to its documentation in that case for how to disable autocommit by default.

With connection pooling you will get much better performance as well.

[reddeagle]

That solved it alright. Thanks for this.
The data hasnt appeared in the database table yet, but i am getting a value for the Product.getId() so will have a look seee why its not saving to the database when the Junit test finsishes.



Go raibh mile maith agaibh - (A thousand thanks IRL)

[Rod Johnson]

The data hasnt appeared in the database table yet, but i am getting a value for the Product.getId() so will have a look seee why its not saving to the database when the Junit test finsishes

Are you managing transactions anywhere (e.g. declaratively with TransactionProxyFactoryBean)? Otherwise the effect of no autocommit will be no permanently persisted data at all. At least it will be atomic :-)

[pawlorj]

I ran into the same problem (connections being set to autocommit). I could not figure out how to configure C3P0 to serve connections with autocommit=false. Has anyone successfully done this? I had to use Apache Commons DBCP which works fine (thanks to the post on this thread), but would rather go back to C3P0.