Bug #8504 Transaction in mysql-connector-java-3.1.6

• Submitted: 14 Feb 2005 18:40
• Modified: 14 Feb 2005 19:33
• Reporter: Adnan Ahmed
• Status: Can't repeat
• Category: Connector / J
• Severity: S1 (Critical)
• Version: 3.1.6
• OS: Microsoft Windows (windows 2000 professional)
• CPU Architecture: Any

[14 Feb 2005 18:40] Adnan Ahmed

Description:
Transaction is not working for this version of Mysql Connector/J with mysql-4.1.7-win. 

i tried with a very simple scenario like this: 

        try {

            Class.forName("com.mysql.jdbc.Driver");
           conn = DriverManager.getConnection("jdbc:mysql://localhost/test?user=root&password=admin");
          String query = "Select ID, UnitPrice from Stock where ItemName=? and Quantity=?";
            String updateQuery = "Update Stock Set UnitPrice=? Where ID=?";

            PreparedStatement queryStmt = conn.prepareStatement(query);
            PreparedStatement updateStmt = conn.prepareStatement(updateQuery);

            conn.setAutoCommit(false);
            queryStmt.setString(1,"M.U");
            queryStmt.setInt(2,325);
            ResultSet queryResultSet = queryStmt.executeQuery();
            System.out.print("ID:\t\t");
            System.out.print("OldPrice:\t\t");
            System.out.println("NewPrice:");

            while(queryResultSet.next()){
                int ID = queryResultSet.getInt("ID");
                int  oldPrice = queryResultSet.getInt("UnitPrice");
                int newPrice = oldPrice + (int) (oldPrice * 0.1f);
                updateStmt.setInt(1,newPrice);
                updateStmt.setInt(2,ID);
                updateStmt.executeUpdate();
                System.out.print(ID+"\t\t\t\t");
                System.out.print(oldPrice+"\t\t\t");
                System.out.println(newPrice);
            }
              conn.rollback();
              conn.setAutoCommit(true);

        } catch (ClassNotFoundException e) {
            System.out.println("Could not load driver");
        } catch (SQLException e) {
            System.out.println("\n---------SQL Exception Caught----------\n");
            e.printStackTrace();
            while (e != null) {
                System.out.println("Message:    " + e.getMessage());
                System.out.println("SQL State:  " + e.getSQLState());
                System.out.println("Error Code: " + e.getErrorCode());
                e = e.getNextException();
            }
        } finally {
            System.out.println("Finally block");
            if (conn != null) {
                conn = null;
                System.gc();
            }
        }
    }

The above code always update the values in the table. However, i m rolling back my transaction. it should not change values in data base. Please chk it and tell either i m doing some thing wrong or this is any issue of the driver

How to repeat:
I m providing the code of my issue

        try {

            Class.forName("com.mysql.jdbc.Driver");
           conn = DriverManager.getConnection("jdbc:mysql://localhost/test?user=root&password=admin");
          String query = "Select ID, UnitPrice from Stock where ItemName=? and Quantity=?";
            String updateQuery = "Update Stock Set UnitPrice=? Where ID=?";

            PreparedStatement queryStmt = conn.prepareStatement(query);
            PreparedStatement updateStmt = conn.prepareStatement(updateQuery);

            conn.setAutoCommit(false);
            queryStmt.setString(1,"M.U");
            queryStmt.setInt(2,325);
            ResultSet queryResultSet = queryStmt.executeQuery();
            System.out.print("ID:\t\t");
            System.out.print("OldPrice:\t\t");
            System.out.println("NewPrice:");

            while(queryResultSet.next()){
                int ID = queryResultSet.getInt("ID");
                int  oldPrice = queryResultSet.getInt("UnitPrice");
                int newPrice = oldPrice + (int) (oldPrice * 0.1f);
                updateStmt.setInt(1,newPrice);
                updateStmt.setInt(2,ID);
                updateStmt.executeUpdate();
                System.out.print(ID+"\t\t\t\t");
                System.out.print(oldPrice+"\t\t\t");
                System.out.println(newPrice);
            }
              conn.rollback();
              conn.setAutoCommit(true);

        } catch (ClassNotFoundException e) {
            System.out.println("Could not load driver");
        } catch (SQLException e) {
            System.out.println("\n---------SQL Exception Caught----------\n");
            e.printStackTrace();
            while (e != null) {
                System.out.println("Message:    " + e.getMessage());
                System.out.println("SQL State:  " + e.getSQLState());
                System.out.println("Error Code: " + e.getErrorCode());
                e = e.getNextException();
            }
        } finally {
            System.out.println("Finally block");
            if (conn != null) {
                conn = null;
                System.gc();
            }
        }
    }

[14 Feb 2005 19:33] Mark Matthews

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

[14 Feb 2005 19:35] Mark Matthews

Adnan,

We'd have to see the DDL you used to create your table(s). The default storage engine in MySQL is MyISAM, which doesn't support transactions, if you want to use transactions, you have to create your tables with the 'InnoDB' storage engine type (or alter them):

ALTER TABLE foo ENGINE INNODB;

[15 Feb 2005 4:27] Adnan Ahmed

Thanks Mark Matthews for your response. Yes i was trying to apply transaction with the type of MyISAM. Its working fine with InnoDB.