MySQL Bugs: #28418: MySql failing to communicate relationship data via ODBC or JRE IV J connector

Bug #28418	MySql failing to communicate relationship data via ODBC or JRE IV J connector
Submitted:	14 May 2007 15:37	Modified:	22 Jun 2007 19:53
Reporter:	Mike Wilson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5n	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Marked serious as the issue continues to be ignored.

See the following... http://bugs.mysql.com/bug.php?id=12975&thanks=3&notify=71

The problems of interfacing with Open Office on either ODBC or Java J connector reliably, appear to continue to go un-addressed by MySql, by having moved the issue from ODBC functionality onto suggesting the J connector alternative, which does not work properly with Open Office.

Relationship information and hence the reliable use of joins and queries dependent upon reliable relationship mapping continue to limit MySql's usefulness with Open Office, making several critical aspects of database design and implementation either unavailable or unreliable between MySql and OpenOffice.

How to repeat:
Install either MyODBC 3.1 or Current Version 5 of JRE J connector into Windows. Design database in MySql and establish either identical or related PK/FK fields and indexes with simple relationship as 1:n.

Open OO 2.2, seek, test create connection to MySql Database, register as typical in OO, then open and access the Relationships dialog. Sometimes a relationship will be shown, sometimes not.

With MyOdbc, Relationships do not establish at all. (the real problem)

With J connector as a workaround. Create a relationship in OO by dragging / dropping PK/FK and see the rubberband join established. Save, close and reopen database within OpenOffice and find the joins are not present, but are present inside of MySql. Creation of yet another relationship will create a redundant relationship inside MySql.

If one is able to establish and maintain a relationship, create a simply query in OpenOffice with an equijoin or right/left outer join and notice that the OO query will not allow the addition of new information to that complex, simple join. Form/Subform and ListBox relationships therefore will not function properly as well, nor can referential integrity be established or maintained reliably.

Suggested fix:
Correct the ODBC deficiencies or create a driver for OpenOffice that works properly and reliably.

Hi Mike and thanks for your report. I won't complain about duplicate post as it seems very important to resolve some of the questions you're rising here. We, at MySQL, are committed to provide support for any platform / 3rd party SW with our connectors. I am very sad that it doesn't appear that way to our users. Here are some of the explanations for problems you're facing:
1)
 - Fact: You're using MySQL server < 5.0.x with InnoDB storage engine. There's no other way to obtain metadata but as shown bellow.
 - Consequence: Restrictions of InnoDB Engine is that it has only 80 symbols for comment. MyODBC driver executes "show table status" query and reads the FK information from that field, ie. InnoDB free: 10240 kB; (`fk1`) REFER `dbtest/par`(`id`) ON UPDATE CASCADE;. Thus, when key names are long, they may exceed 80 characters limit and the result of SQLForeignKeys() can be unpredictable. We can not fix this behavior as InnoDB is not MySQL product.
 - Remedy: Please upgrade to MySQL server 5.x and use c/ODBC v5 as it reads related info from I__S database. Also, consider using other transactional storage engines.
 - Conclusion: From MySQL part, we did a lot. We created entirely new server / storage engine(s) to overcome this problem. More on this in Bug#8100 (and elsewhere in BugsDB).

2)
 - Fact: Each platform has it's own driver managers with bugs of their own.
 - Consequence: Due to several bugs in unixODBC (and the very slow response time) we decided to focus on iODBC, if you take Linux as example of this.
 - Remedy: Not available yet although we checked numerous options...

3)
 - Fact: 3rd party SW has it's bugs too. Check on Mark's post in original report:
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.

Now, don't take me wrong, I am not looking for excuses, just trying to inform you of facts, and facts are:
 - Due to limitations of InnoDB engine, retrieval of FK information from MySQL server is buggy. It has nothing to do with connectors.
 - We are striving to make our connectors as good as possible and will continue our work in that direction.
 - OO is in no way neglected by MySQL. There are some plans but I'm really not at liberty to discuss them here.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Reply to Tonci Grgin from May 21:

Thank you Tonci for your comments.

I was admittedly harsh in this particular comment, chiefly because the problem has continued for so long without address and while MySql admittedly has a reliable upper hand in the quality of code releases over OO, OO is picking up speed and their base product is approaching usable, if they would stop issuing Alpha to release state.

I think I stated I am "Current Version 5 of JRE J connector" and have been 5.0.22 server for some time and will add fairly that the .Net connector and ODBC are serving well, compared to this particular issue, which should have been marked as a "J connector issue, if you would please re-classify it.  I held up in doing so as you may care to move the entire thread, to be proper.

I can't see the references to other SW as relevant to why this continues without remedial efforts, unless MySql isn't concerned about the growing usability of OO with PostGres and others beside the fact that OO Database will soon reach usability in the the near future.  Practically speaking, it's a matter of market share if not a continuing "Ache" to not have a solution where for some reason, other third party Admin systems ARE finding successful solutions to the J conn interface with OO.

Out of fairness, I will go back and verify the ODBC connector some more and see how it fairs over the J conn, as this may provide some gains for those who are looking for an alternative connection away from J/Conn into OO as a front end.  I will be glad to drop back here and relate what benefits were found, as I have recently had some other ODBC and ADO interaction in OO that went considerably better, than the J conn provides overall, even in other 3rd party products.

Perhaps a note in the J/conn download area regarding testing ODBC or ADO as alternatives to J/conn could help guide others away from the same frustrations, if either proves to offer higher reliability.  Admittedly, the surrounding nature of J/conn itself seems to be the real underlying problem I think, though that is an unfounded suggestion.

My biggest concern really is the potential for a shift in Open user paradigm if OO does not hold up well with MySql and were to begin edging market aggressively with another product(s), as they don't seem to be growing toward MySql, evidenced by an absence of MySql in their forums as it is and a counter statement in their bug list regarding J/Conn and MySql.

Thanks again,

Mike

Hi Mike and thanks for your valuable observations.

We are deeply aware of this problem and working on solution. In fact, we have a driver for OO.org but it works with libmysql therefore Sun doesn't accept it. However, we will work in Q3 on native implementation.

The report is in "Analyzing" again as I want to hear from c/J team leader too.

> Perhaps a note in the J/conn download area regarding testing ODBC or ADO as alternatives
> to J/conn could help guide others away from the same frustrations, if either poves to
> offer higher reliability.  Admittedly, the surrounding nature of J/conn itself seems to be
> the real underlying problem I think, though that is an unfounded suggestion.

If you look at bug counts vs. number of downloads, the JDBC driver is generally considered more mature and reliable than either ODBC or ADO.Net.

Reliability asside, the issue you're running into is that the MySQL _server_ just plain doesn't provide all of the metadata to return foreign key information reliably, at least not until MySQL-5.1. Prior to 5.0 it's not even available in tabular form and has to be parsed out of "SHOW CREATE TABLE", and in 5.0, some of it is in tabular form, but is missing key information (such as the constraint actions). Only in MySQL-5.1 is the INFORMATION_SCHEMA complete enough to provide all of the pieces to return this metadata correctly, and the changes to support this availability are in SVN for the JDBC driver.

The fact that OOo can't extract relationship information from MySQL reliabily has nothing to do with the implementation of our JDBC, ODBC or .Net drivers. It wouldn't change if you used a "native" OOo driver either. The information just isn't there until MySQL-5.1.

In addition, as stated before, one portion of this "bug" exists because whoever maintains the OOo database code has constrained the meaning of JDBC's DBMD.supportsIntegrityEnhancementFacility() to mean that a database supports foreign keys. This simply isn't true. It means foreign keys as well as check constraints. Our JDBC driver, in good faith, out of the box can _not_ return "true" for this method, because we don't support check contraints. (I actually have never run into any other software that uses this method). That is why you have to pass extra information in the URL to tell us that you want the driver to "lie" about the capabilities the database has.

Thank you Mark,

My intention regarding ODBC was that I'm personally having better luck in some of he OO interfaces using ODBC in Windows as opposed to J/conn and I am continuing to watch MySql 5.1 favorably as it evolves and looking forward to it.  

Some of the semantics surrounding the "out of the box" J/Conn situation appear to have been a manner by which others learned you can spoof the connection string to "fake" some of the facilities through the J/conn driver, which invites disaster in the long run.  It's been a partial workaround that allows OO to display a terse representation of the relationship model but also creates errors whenever full integrity and cascades are deployed, exactly as you describe for the limitations in MySql 5.0.

It's clear to me from your explanation, that some of the third party applications that are finding ways to marry J/conn to MySql 5.0 reliably, are most likely as you say, parsing out the reported outputs from MySql to "derive" continuity.  That would explain a great deal of the differences between those who can and cannot maintain RI, relationship ERDs and DDE data.

If I'm understanding you correctly, as 5.1 progresses, this should improve considerably, especially that there is an update in SVN to equip 5.1 with J/conn, which is good news for those of us working this angle, still.

Your explanation is greatly appreciated and I'm sure likely redundant with other bug references that I either didn't find or overlooked how they applied to the problem at hand, so I am again most appreciative of your second effort to explain it.  I continue to work in avenues that will equip OO with MySql and while perhaps OO is less inclined to worry about it, given their work in an open driver, most of us don't have time or inclination to code drivers for lack of thorough knowledge of both platforms alike and hoping to save time relying on working drivers.

I will mark this thread closed at this point and update the benefits that are found as I get into 5.1 and make additional efforts as the pending OO action query capabilities emerge, at which point this should become a nearly moot issue or be more easily explained for any remaining limitations where we can post some working examples in the two forums.  Hopefully others will take note and MySql users will find these capabilities welcome in OO.

Mike

Thank you Mark,

My intention regarding ODBC was that I'm personally having better luck in some of he OO interfaces using ODBC in Windows as opposed to J/conn and I am continuing to watch MySql 5.1 favorably as it evolves and looking forward to it.  

Some of the semantics surrounding the "out of the box" J/Conn situation appear to have been a manner by which others learned you can spoof the connection string to "fake" some of the facilities through the J/conn driver, which invites disaster in the long run.  It's been a partial workaround that allows OO to display a terse representation of the relationship model but also creates errors whenever full integrity and cascades are deployed, exactly as you describe for the limitations in MySql 5.0.

It's clear to me from your explanation, that some of the third party applications that are finding ways to marry J/conn to MySql 5.0 reliably, are most likely as you say, parsing out the reported outputs from MySql to "derive" continuity.  That would explain a great deal of the differences between those who can and cannot maintain RI, relationship ERDs and DDE data.

If I'm understanding you correctly, as 5.1 progresses, this should improve considerably, especially that there is an update in SVN to equip 5.1 with J/conn, which is good news for those of us working this angle, still.

Your explanation is greatly appreciated and I'm sure likely redundant with other bug references that I either didn't find or overlooked how they applied to the problem at hand, so I am again most appreciative of your second effort to explain it.  I continue to work in avenues that will equip OO with MySql and while perhaps OO is less inclined to worry about it, given their work in an open driver, most of us don't have time or inclination to code drivers for lack of thorough knowledge of both platforms alike and hoping to save time relying on working drivers.

I will mark this thread closed at this point and update the benefits that are found as I get into 5.1 and make additional efforts as the pending OO action query capabilities emerge, at which point this should become a nearly moot issue or be more easily explained for any remaining limitations where we can post some working examples in the two forums.  Hopefully others will take note and MySql users will find these capabilities welcome in OO.

Mike