Re: JDBC driver: Buggy for DatabaseMetaData.getImported/Exportedkeys ?

[Mark Matthews]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

j.random.programmer wrote:

Hi:

I am using Connector/J 3.0.2 against MySQL
3.23.52-max.

My default table type is set to InnoDB (since I always use innodb).

The following methods are acting strangely:

- DatabaseMetaData.getImportedKeys()
- DatabaseMetaData.getExportedKeys()

Often these methods return partial information and
sometimes no information. Here's a simple test harness
that illustrates the issue.

This was fixed in the version that is going to be 3.0.3 a few weeks ago. The parsing has changed, so that it now uses 'SHOW CREATE TABLE', but it is more complex, so there have been a few kinks to work out.

--- set up test tables [cut-n-paste this] ------------

CREATE TABLE UserStatus (
id TINYINT NOT NULL AUTO_INCREMENT,
type CHAR(50),
PRIMARY KEY (id)
);

CREATE TABLE UserType (
id TINYINT NOT NULL AUTO_INCREMENT,
type CHAR(20) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE Locations (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(30),
department VARCHAR(50),
PRIMARY KEY (id)
);

CREATE TABLE Users (
id INTEGER AUTO_INCREMENT,
locationID INTEGER DEFAULT NULL,
userTypeID TINYINT NOT NULL,
statusID TINYINT NOT NULL,
username VARCHAR(50) NOT NULL UNIQUE,
PRIMARY KEY (id), FOREIGN KEY (statusID)
REFERENCES UserStatus(id), INDEX(statusID) , FOREIGN KEY (locationID)
REFERENCES Locations(id), INDEX(locationID) , FOREIGN KEY (userTypeID)
REFERENCES UserType(id), INDEX(userTypeID) );

CREATE TABLE Groups (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE UsersAndGroups(
userID INTEGER NOT NULL,
groupID INTEGER NOT NULL,
dateAdded TIMESTAMP,
PRIMARY KEY (userID, groupID)
);

---------------- end test tables -------------

Create the above tables in a test database. Make
sure you specify innodb as the default table type.
(otherwise hack the above to add tabletype=innodb
for each table).

Now, after connecting to this database, call
getImportedKeys and getExportedKeys thru your
java code. Specify the "Users" table.

Findings:

1. ImportedKeys
Expected-to-see: statusID, locationID, userTypeID
Actual: statusID

This works correctly with the new code.

2. ExportedKeys
Expected-to-see: UsersAndGroups
Actual: empty

Why would you expect to see this? With the SQL you give above, you don't reference the Users table from the UsersAndGroups table. The only table that has foreign keys is the Users table.

Also, as an aside, it is better to send JDBC bug reports to either [EMAIL PROTECTED] (preferred), or to [EMAIL PROTECTED], because the chance that they will get seen quicker by someone 'in the known' is guaranteed.

-Mark
- -- For technical support contracts, visit https://order.mysql.com/?ref=mmma

__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mark Matthews <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java
/_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA
<___/ www.mysql.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.1.90 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE97LWZtvXNTca6JD8RAvI9AKClvF/YyhMZBFtegAbc0/PFBqUL0gCbB8SO
AM1iOZPlJcv05rYFFcFK7go=
=TZ66
-----END PGP SIGNATURE-----


---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php