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.
DB: MySQL 5.0.22 on Ubuntu 6.06 LTS i386.
JDBC: MySQL Connector/J 5.0.4
SQL Developer: 126.96.36.199
(all on same system)
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?
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
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.
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.
Have you looked into the Isolation level ?
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 ?
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.
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.