Announcement Announcement Module
Collapse
No announcement yet.
MYSql Autocommi Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MYSql Autocommi

    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
    Code:
    <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:

    Code:
    Translating SQLException with SQLState 'null' and errorCode '0' and message &#91;Can't call commit when autocommit=true&#93;; SQL was &#91;null&#93; for task &#91;HibernateAccessor&#93;
    I set autcommit in MYSql to be false as follows.


    Code:
    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.

  • #2
    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

    Comment


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

      Originally posted by 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.
      Code:
        <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

      Comment


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

        Code:
        <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.

        Comment


        • #5
          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)

          Comment


          • #6
            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 :-)

            Comment


            • #7
              Turning off autocommit for C3P0

              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.

              Comment

              Working...
              X