14 Replies Latest reply: Jan 29, 2007 3:28 AM by 485301 RSS

    MySQL InnoDB tables

    228275
      This one is probably easy to resolve, but I am stumped.
      I've been using SQL Developer for a few days against MySQL tables that use MyISAM engine with no problems. Today I created a MySQL table using InnoDB engine but SQL Developer seems to be doing something odd -- it is getting data consistent with when the query was first issued.

      For example, if I create an innodb table called MyTestTbl, and insert 1 row.
      Then launch SQL Developer and query MyTestTbl, it will show the one row.
      But then on another connection I go in and insert another row, SQL Developer will still only show one row.

      If I issue a "commit" in SQL Developer", and then query MyTestTbl again, it will then show the second row.

      So I don't know if this is a configuration setting I need to change, or if this is a problem is in SQL Developer.

      Versions:
      DB: MySQL 5.0.22 on Ubuntu 6.06 LTS i386.
      JDBC: MySQL Connector/J 5.0.4
      SQL Developer: 1.1.0.23
      (all on same system)

      Any suggestions?
        • 1. Re: MySQL InnoDB tables
          Jim Smith
          I don't know much about InnoDB, but didn't it provide MySQL with a multiversion concurrency model similar to oracle's?

          If that is the case, then what you are seeing is the correct behaviour.
          • 2. Re: MySQL InnoDB tables
            484267
            I can confirm that InnoDB copied Oracle's consistency model, so you should always get the results as of the beginning of the query's execution. That is by design.
            • 3. Re: MySQL InnoDB tables
              228275
              the results as of the beginning of the query's execution
              But the results that I am seeing are as-of the last commit. So unless I issue a commit at the beginning of each query, my results are stale.
              • 4. Re: MySQL InnoDB tables
                Jim Smith
                You shouldn't have to issue a commit in the 'viewing' session. The changes should be visible in the viewing session as soon as the updating session commits.

                It may be that InnoDB hasn't implemented the model in the same way, or that the jdbc driver does something strange or sqldeveloper does something strange.

                Can you reproduce it using 2 sessions in a native mysql client?
                • 5. Re: MySQL InnoDB tables
                  228275
                  Can you reproduce it using 2 sessions in a native mysql client?
                  No, only Oracle SQL Developer is behaving like this.

                  Here are the steps I perform:
                  #1.) Launch Mysql client

                  mysql> create schema MySchema;
                  Query OK, 1 row affected (0.00 sec)

                  mysql> create table MySchema.MyTable (col1 int) engine=InnoDB;
                  Query OK, 0 rows affected (0.04 sec)


                  #2.) Launch Oracle SQL Developer, connect as root to localhost.
                  Username: root
                  Hostname: localhost

                  Enter SQL Statement:
                  select count(*) from MySchema.MyTable;
                  [F9 to Execute Statement]
                  Results:
                  0

                  #3.) Alt-Tab back the MySQL client:
                  mysql> insert into MySchema.MyTable values (1);
                  Query OK, 1 row affected (0.01 sec)

                  mysql> commit;
                  Query OK, 0 rows affected (0.00 sec)

                  mysql> select count(*) from MySchema.MyTable;
                  ----------
                  | count(*) |
                  ----------
                  | 1 |
                  ----------
                  1 row in set (0.00 sec)

                  mysql> exit;
                  Bye

                  #4.) Alt-Tab back to Oracle SQL Developer
                  Enter SQL Statement:
                  select count(*) from MySchema.MyTable;
                  [F9 to Execute Statement]
                  Results:
                  0

                  Enter SQL Statement:
                  commit;
                  [F9 to Execute Statement]

                  Enter SQL Statement:
                  select count(*) from MySchema.MyTable;
                  [F9 to Execute Statement]
                  Results:
                  1
                  • 6. Re: MySQL InnoDB tables
                    Jim Smith
                    It would appear to be a bug in SQL Developer (or possibly the JDBC driver).

                    One of the development team will have to pick this up.
                    • 7. Re: MySQL InnoDB tables
                      228275
                      I don't have paid support on Metalink. Is there any other way for me to file a TAR / bug report for SQL Developer?
                      • 8. Re: MySQL InnoDB tables
                        484267
                        Without a support contract, you need to use the forums. Unfortunately with the misfire that was 1.1, I would imagine that they are quite busy fixing bugs and may not get to this thread. You could also try posting on the data migration forum as they were the team that created the integration with MySQL.

                        Database and Application Migrations
                        • 9. Re: MySQL InnoDB tables
                          228275
                          I wanted to eliminate the chance that the problem was unique to me running MySQL and Oracle SQL Developer both on Linux, so I installed both under Windows and got the same results, that is -- need to enter commit before each query against InnoDB tables to ensure are getting a new result set.
                          • 10. Re: MySQL InnoDB tables
                            484267
                            Do you see similar behavior in other jdbc tools when connecting to innodb? (Squirrel may be a good candidate to test with)

                            Eric
                            • 11. Re: MySQL InnoDB tables
                              228275
                              Thanks for the suggestion. I installed SQuirreL SQL client on Windows connected to MySQL (localhost) and queries in SQuirrel against InnoDB tables return current results. So that solidifies the argument that SQL Developer is where the problem lies.

                              BTW, SQL Developer's user interface is a heck of a lot slicker than SQuirreL's ..., in my opinion. I sure hope Oracle can go the last few yards that are needed and get this to work.
                              • 12. Re: MySQL InnoDB tables
                                448576
                                Have you looked into the Isolation level ?
                                http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-isolation.htm

                                From the description, the default for MySQL InnoDB is "REPEATABLE READ" under which...
                                "All consistent reads within the same transaction read the same snapshot established by the first read. This convention means that if you issue several plain SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other."

                                Also, are the other clients doing autocommits after each select ?
                                • 13. Re: MySQL InnoDB tables
                                  228275
                                  Gary, thank you for pointing out the problem. All I needed to do was turn on "auto commit" in SQL Developer.
                                  Tools -> Preferences
                                  Click + next to Database,
                                  Select Worksheet Parameters
                                  Click checkbox to mark "Autocommit in SQL Worksheet"
                                  Close SQL Developer and re-launch.
                                  • 14. Re: MySQL InnoDB tables
                                    485301
                                    Hi,
                                    The page mentioned in Gary's post above also has some interesting comments by users on the effect of MySQL's default Transaction level.

                                    Basically, what you are seeing is standard MySQL behaviour when auto commit has not be set on.

                                    I've logged this as a SQL Developer bug on this to allow better control of autocommit.

                                    Regards,
                                    Dermot.