Description of problem: Upgrading mysql-connector-java from -3.1.12-6.fc10.x86_64 (fedora F10 repo) to -5.1.8-1.fc10.x86_64 (F10 updates) "breaks" OpenOffice Base queries and forms. Version-Release number of selected component (if applicable): 1:mysql-connector-java-5.1.8-1.fc10.x86_64 openoffice.org-base-3.0.1-15.6.fc10.x86_64 mysql-server-5.0.84-1.fc10.x86_64 How reproducible: yum update mysql-connector-java Steps to Reproduce: 1. Take and existing (pre-update) OpenOffice Base MySQL (JDBC) database with a few queries and forms that use "SELECT ... COUNT(..) AS ..." etc. 2. Verify the queries work as expected. 3. Update to 1:mysql-connector-java-5.1.8-1.fc10.x86_64 4. Run the queries. Actual results: Count fields do not appear correctly. The data in those fields are replaced with content from other fields. Field/column headings change randomly. Expected results: Should work at least as well as before the update. Additional info: Sample info available if required but the data set is large, from MythTV plus auxiliary data for tracking series recordings. Operation restored for now with `yum downgrade 1:mysql-connector-java-3.1.12-6.fc10.x86_64`.
CC'ing ooffice maintainer Dave, could you try the same with F11 please?
Sorry, no access to F11 at the moment - bandwidth and workload are too tight. An upgrade would probably take me about a week.
Could you recheck your usage against the instructions at http://wiki.services.openoffice.org/wiki/Connect_MySQL_and_Base? In particular the section: For the field MySQL JDBC driver class: enter the class name for the JDBC driver you installed. You can find the correct class name in the documentation that came with the driver. MySQL java connector archive version 5.1.6 contains connector-j.pdf document with description in subdirectory docs. The class name is com.mysql.jdbc.Driver for version 5.1.6. Click on Test Class just to make sure that you have correctly set up the access to the connector as detailed above. If the connector has been set up correctly, you will get a message that says The JDBC driver was loaded correctly. Now click on Next. Do you see the text "The JDBC driver was loaded correctly" in those settings there?
Checked through the documentation - all looks OK. Checked functionality under mysql-connector-java-3.1.12-6.fc10.x86_64, all OK. Updated to mysql-connector-java-5.1.8-1.fc10.x86_64 Log out, reboot, log in (just to be sure). Checked my existing OpenOffice Base -> MySQL/JDBC connection: The JDBC driver was loaded successfully. The connection was established successfully. Queries are messed up - column names wrong, content of COUNT() column copied from other columns, just as before. Decided to check what happens with a newly created OpenOffice Base database. Created a new database, MySQL connection, JDBC driver, localhost, 3306, com.mysql.jdbc.Driver. Connection tests OK. Created a copy of a failing query from scratch in Design View and copied the old, failing query (copy and paste the SQL) from the old db to the new db. The newly created query appeared to work while the problems persist with the copied version. Looked like I was on to something. The problems return as soon as column aliases are included - I could break the new query in exactly the same way as the old query, and in new ways (aliases ignored). Back to the documentation: Section 2.3.2. "Upgrading to MySQL Connector/J 5.1.x", in the connector-j.pdf, notes a change of behaviour in relation to table and column aliases - The behaviour is now "compliant", but the old, "non-compliant" behaviour can be reinstated through the useOldAliasMetadataBehavior option which defaults to False in 5.1. It was True in all previous versions. I cannot see any way to set that from OpenOffice Base. I think this is the most likely cause of the problems I am seeing. Back to mysql-connector-java-3.1.12-6.fc10.x86_64 for now.
From http://qa.openoffice.org/issues/show_bug.cgi?id=97096 "I have a problem which I suspect has the same root cause as this one. There was a tightening up of the mysql-connector behaviour as decribed here: http://bugs.mysql.com/bug.php?id=40256 In my case the result of a query including aliases malfunctioned, producing wrong data in the wrong columns. The work-around was to append: ?useOldAliasMetadataBehavior=true to the name of the database in the 'Database Properties' dialog." Does that workaround work for you too ?
YES!!! Many thanks, Caolan! Changed database name from "original" to "original?useOldAliasMetadataBehavior=true", updated to mysql-connector-java-5.1.8-1.fc10.x86_64, queries now function as they "should"/did. This work-around definitely "works for me". Happy for this bug to be closed or marked as resolved. Any implications for OpenOffice?
Great, glad to hear we can keep the updated version for F10.
Well, from an OOo perspective this is "closed upstream as 97096" so we should try and get this fixed in OOo. Though databases aren't my forte so hopefully upstream fs/oj have a better idea about this. As an aside, from googling around I don't think though that OOo is the only app which is having problems with this and I'd generally be wary of major version updates of libraries and apps in released Fedora's, but that's up to each maintainer to call :-)
(In reply to comment #8) > As an aside, from googling around I don't think though that OOo is the only app > which is having problems with this and I'd generally be wary of major version > updates of libraries and apps in released Fedora's, but that's up to each > maintainer to call :-) Yes and in general I agree of course. The fact is, however, that in this case I had tons of both user and maintainer (to be able to update dependent packages) requests to do this (I took the package over some months ago). I had the updated package sitting in -testing for quite a time but still don't feel good about that. Before I decided to do this, I googled around of mysql web and they're claiming backward compatibility for the connector, so I hope there won't be more negative surprises. (btw, thank you for your help Caolan.)