MySQL Bugs: #12975: openoffice returns relations error for an innodb table when connected with jdbc

Bug #12975	openoffice returns relations error for an innodb table when connected with jdbc
Submitted:	4 Sep 2005 3:49	Modified:	17 Nov 2005 15:23
Reporter:	edwin oakes	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	4.1.10a	OS:	Linux (suse 9.2)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
mysql-connector-java-3.1.10, MySQL Connector/ODBC 3.51.11-2, openoffice 2.0beta2

Using OpenOffice I connected using MyODBC and added innodb tables that contain foreign keys to a view. The relationships did not show up. tools/relationships returned a "The database does not support relations" error. I found a bug report # 8100 that said that this was a problem in MySQL Connector/ODBC that would be fixed in MySQL Connector/ODBC 3.51.12! The bug was caused by a slight change in the format returned by the "show table status" query

I loaded mysql-connector-java-3.1.10 and connected OpenOffice to my database using the jdbc connector. I received the same error.

Does mysql-connector-java-3.1.10 also use the "show table status" query so that jdbc has the same problem. I was unable to find any reference in the bug list? 

How to repeat:
Connect an innodb table with forgien keys to OpenOffice. Open tools/relationships and see the error dialog. Or go to tables create view and add tables with foreign keys and referenced table and note the absence of the relationship lines.

Suggested fix:
The fix would be to change mysql-connector-java to correctly handle the new "show table status" format.

Sorry....I have not any problem from java applications with relationships. Probably it is problem of openoffice. Could you please create and attach java application when I will able to reproduce the bug ?

I do not have a java application as such. I was using the jdbc connector to acces an innodb file in openoffice as per the openoffice direction. I tried this after getting a "The database does not support relations" error dialog when use the MyODBC connector. There is a bug #8100 on the problem wit MyODBC. I got the same error when trying the jdbc connector. I assumed that it was the same poblem with the output format of "show table status" . It may well be that openoffice uses "show table status'" to determine while java programs use some other method. The bug #8100 has a discussion on the source of that bug in MyODBC.

I do not have a java application as such. I was using the jdbc connector to acces an innodb file in openoffice as per the openoffice direction. I tried this after getting a "The database does not support relations" error dialog when use the MyODBC connector. There is a bug #8100 on the problem wit MyODBC. I got the same error when trying the jdbc connector. I assumed that it was the same poblem with the output format of "show table status" . It may well be that openoffice uses "show table status'" to determine while java programs use some other method. The bug #8100 has a discussion on the source of that bug in MyODBC.

Could you please provide table definitions ? I'll try to check it on Java application.

I have been off line for several weeks.
 thanks,
Edwin Oakes

| tblClub | CREATE TABLE `tblClub` (
  `id` int(11) NOT NULL auto_increment,
  `ClubName` char(50) NOT NULL default '',
  `Region` char(10) default NULL,
  `Address` char(50) default NULL,
  `City` char(25) NOT NULL default '',
  `State` char(10) NOT NULL default '',
  `Zip` char(10) default NULL,
  `Password` char(25) default NULL,
  `VolCoord` char(100) default NULL,
  `Email` char(50) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idxClub1` (`ClubName`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

tblPeople | CREATE TABLE `tblPeople` (
  `id` int(11) NOT NULL auto_increment,
  `FirstName` char(25) NOT NULL default '',
  `LastName` char(25) NOT NULL default '',
  `AVA_Num` char(10) default NULL,
  `ClubID` int(11) NOT NULL default '0',
  `Gender` enum('Female','Male') NOT NULL default 'Female',
  `Medal` enum('None','Trot','Bronze','Silver','Gold') NOT NULL default 'None',
  `Rating` enum('U','A','B','C') NOT NULL default 'U',
  `USAE_Num` char(10) default NULL,
  `Vaulter` enum('No','Yes') NOT NULL default 'No',
  `Lounger` enum('No','Yes') NOT NULL default 'No',
  `Coach` enum('No','Yes') NOT NULL default 'No',
  `Trainer` enum('No','Yes') NOT NULL default 'No',
  `HorseOwner` enum('No','Yes') NOT NULL default 'No',
  `US_citizen` enum('Yes','No') default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idxPeople2` (`FirstName`,`LastName`,`ClubID`),
  KEY `ClubID` (`ClubID`),
  CONSTRAINT `tblPeople_ibfk_1` FOREIGN KEY (`ClubID`) REFERENCES `tblClub` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

 tblHorse | CREATE TABLE `tblHorse` (
  `id` int(11) NOT NULL auto_increment,
  `ClubID` int(11) NOT NULL default '1',
  `AVA_Num` char(10) default NULL,
  `HorseName` char(30) default NULL,
  `Sex` enum('Mare','Gelding','Stallion') NOT NULL default 'Mare',
  `Age` int(11) default NULL,
  `Color` char(20) default NULL,
  `Height` char(10) default NULL,
  `TrainerID` int(11) default '1',
  `OwnerID` int(11) default '1',
  `USAE_Num` char(10) default NULL,
  `Breed` char(25) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idxHorse1` (`HorseName`,`ClubID`),
  KEY `ClubID` (`ClubID`),
  KEY `OwnerID` (`OwnerID`),
  KEY `TrainerID` (`TrainerID`),
  CONSTRAINT `tblHorse_ibfk_1` FOREIGN KEY (`ClubID`) REFERENCES `tblClub` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tblHorse_ibfk_2` FOREIGN KEY (`OwnerID`) REFERENCES `tblPeople` (`id`),
  CONSTRAINT `tblHorse_ibfk_3` FOREIGN KEY (`TrainerID`) REFERENCES `tblPeople` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

I created test case on Java and the test case returns all columns of "show table status" correctly. I attached my test case and result file. Could you please run the test case on your computer ?

Test case

Attachment: MySQL.java (text/java), 1.12 KiB.

Result file

Attachment: result.txt (text/plain), 1.16 KiB.

OpenOffice complains about not supporting relations because we return "false" for DatabaseMetaData.supportsIntegrityEnhancementFacility().

To return "true" for that method MySQL would also have to support check constraints (it doesn't currently). Therefore it's not an "isolated" test to determine if a database supports foreign keys. We can add a feature to return "true" for this, but it should be filed as a bug against OpenOffice if all they're looking for is support for foreign keys.

I would be very interested to know when you might be able to facilitate the relations option in OpenOffice 2.0.

Fixed for 3.1.12/5.0.0, see nightly builds after 00:00 GMT Nov 18 at http://downloads.mysql.com/snapshots.php#connector-j

You need to add "overrideSupportsIntegrityEnhancementFacility=true" to your JDBC URL configuration parameters, as the SQL standard states that the IEF also covers CHECK CONSTRAINT, which MySQL doesn't have yet, which makes returning "true" for this method by default wrong.

Thanks for the bug report.

Thanks for the quick response. I will try it next week.

I have tried it with success.

For anyone who might wish to folow this path, here are the details:-

I downloaded mysql-connector-java-5.0-nightly-20051121.zip.

Having unzipped I placed mysql-connector-java-5.0-nightly-20051121-bin.jar into the jre which was selected in OpenOffice.
In my case this was C:\Program Files\Java\jre1.5.0_05\lib\ext.

When establishing the database in OpenOffice follow the 'Connect to and existing database' and choose JDBC rather than MySQL.

Set the dataSource url to something like:-
jdbc:mysql://localhost:3306/MySQL?overrideSupportsIntegrityEnhancementFacility=true

and the driver to 
com.mysql.jdbc.Driver

This should allow OpenOffice to open a relations window.  But the rest is a little disappointing.  If you add 2 tables to the relations window any existing foreign key constraint is not automatically shown.  There is a danger of creating duplicate foreign key constraints.

So close!

David

Has any status moved forward on this issue?  

As of the date of this writing, OO 2.0.2 appears to maintain that it cannot manage relationships with MySql as MySql does not have integral row tracking.

And yet, they also admit that as long as unique indexes exist in all related tables and/or and explicit "where" clause marries the rows of interest, the data can be adequately parsed concurrently with the given constraints.

Has MySql determined this to be an issue for OO and it remains as such, or has anyone succeeded in a work-around for this.  The lack of query update capability and relationships in OO 2.0n is a frustrating issue driving users to create code integrated updates which invites developers to weaken constraints to cope.

Thanks for any updates or suggestions.

Mike

It appears this problem continues without any attention from MySql.  The original bug was that MyODBC did not provide relationships information with OpenOffice.  As of OO 2.2 on WinXP home in a P4 machine, this concern remains.

The workaround suggested was the use of the Java J connector, requiring the user to install and use Java.  While the inclusion of a startup command appears to allow the user to reference the relationship data between OO and MySql, tables built inside of OO versus being built by the MySql prompt interface, do not operate the same.

Worse, there are limitations that appear to not resolve accurately along the join conventions as typical queries in OO which should allow inserts of new data to simple table join assemblies do not.  This later issues limits OO from being able to create properly functional inserts in form/subform assemblies and severely cripples the ability for complex data entry of new data via existing relationships with MySql.

The problem continues to be ignored by MySql by first suggesting the user should move from ODBC to MyJDBC or the MySql J connector to solve the lacking in the MyODBC driver, which then leaves the user with only limited functionality at which point MySql devel community comes back and complains it is a limitation of the Java community's implementation of the type IV J connector.

Since MySql does not provide a forms interface, the Open Source alternatives appear to land squarely on Open Office as the only relatively full Office Suit with any true target for universal interoperability.  However, as long as the MySql community continues to divert the lack of a fully functional driver for Open Office to implement to, it can hardly be said that Open Office or Java for that matter have anything to do with the underlying problem of MySql ignoring this huge need.