Created attachment 149749 [details] Test database In version Version: 6.1.5.2 Build ID: 1:6.1.5~rc2-0ubuntu0.18.10.1~lo3 CPU threads: 8; OS: Linux 4.18; UI render: default; VCL: x11; Locale: en-GB (en_GB.UTF-8); Calc: group threaded I have a problem with some queries when using MySQL(JDBC). MySQL(Native) works fine. In the sample, using JDBC, Query NIC4RatesPivot works, but NIC4RatesPivotOld fails, stating that table NIC4Rates does not exist. But it does, as a query in the LibreOffice database. Changing to MySQL(Native) fixes the problem. I don't know how old this issue is, but it affects many of my nested queries. SQL for the underlying tables is attached separately. The database is actually MariaDB 10.3.
Created attachment 149750 [details] SQL to create tables
@tim : which JDBC driver and version are you using against your MariaDB database ?
Apologies, but I'm a little unclear on this. I have libmysql-java installed, version 5.1.45-1. I see there is also a libmariadb-java driver available, version 2.3.0-1, not currently installed. Opening up the Base database information, it says I'm using org.mariadb.jdbc.Driver
@Tim : thanks. So at least according to Base, you are using the mariadb jdbc connector for that particular connection :-) the libmysql-driver 5.1.45-1 package provided by Ubuntu should be installed system wide (and is recognised by the com.mysql.jdbc.Driver string). If you override that with a manually indicated path to the mariadb driver, then that should get picked up instead (assuming that you never pointed to the mysql driver). So, I would look for previous existing bug reports against the mariadb driver.
I need a bit more help with this. I'm not sure exactly what you mean. In my java class paths I have: /usr/share/java/mysql-connector-java.jar /usr/share/java/mariadb-java-client-2.4.0.jar Should I try something different? I don't know what the "com.mysql.jdbc.Driver string" is, or whether you are suggesting I change it.
@Tim : I reconfigured your ODB file to work with the imported SQL from your other attachment, the differences in my setup being : - mysql JDBC connector 8.0.12 - database called NestedQueries - mysql server 5.7.15 - LibreOffice Version: 6.1.5.2 Build ID: 90f8dcf33c87b3705e78202e3df5142b201bd805 Threads CPU : 8; OS : Mac OS X 10.14.3; UI Render : par défaut; Locale : fr-FR (fr_FR.UTF-8); Calc: group threaded All of your queries worked for me without error in this setup when I double-clicked on them from the Queries module. Sorry, no repro with the setup above.
(In reply to tim from comment #5) > I need a bit more help with this. I'm not sure exactly what you mean. > > In my java class paths I have: > > /usr/share/java/mysql-connector-java.jar > /usr/share/java/mariadb-java-client-2.4.0.jar > > Should I try something different? > In my experience, having both of the above confuses LO leading to inconsistent results when using the underlying database. I encountered strange behaviour when I had a similar setup, which is unfortunate, as one of my LAN database servers is mariadb, whereas my localhost server (running on the same machine as LO) is mysql. Try removing the first path entry, applying the changes, closing LO completely, then restarting.
(In reply to tim from comment #5) > > I don't know what the "com.mysql.jdbc.Driver string" is, or whether you are > suggesting I change it. If you use the mysql JDBC connector driver instead of the mariadb jdbc connector, this would be string to enter instead of "org.mariadb.jdbc.Driver"
(In reply to Alex Thurgood from comment #8) > (In reply to tim from comment #5) > > If you use the mysql JDBC connector driver instead of the mariadb jdbc > connector, this would be string to enter instead of "org.mariadb.jdbc.Driver" In LO6.1, this can be set either when you configure a new database connection using a MySQL(JDBC) connection (via the wizard), or else, with the ODB already open, menu Edit > Databases > Properties > Additional Properties tab, and entering the class string com.mysql.jdbc.Driver instead of the one you have. You can then test that the class loads correctly with the test button. Save your ODB file, close it and restart LO for the changes to take effect.
(In reply to Alex Thurgood from comment #9) > > If you use the mysql JDBC connector driver instead of the mariadb jdbc > > connector, this would be string to enter instead of "org.mariadb.jdbc.Driver" > Just to be clear, if you remove the first path entry, then you need to keep the org.mariadb.jdbc.Driver class string. If you remove the second path entry, then you need to switch to the com.mysql.jdbc.Driver class string.
Our latest comments overlapped - I'm resubmitting. You really are an expert - thanks so much. Ignorant but persistent tinkerers like me really appreciate your help. I first tried just using the mariab java connector having removed the mysql one, but keeping the org.mariadb.jdbc.Driver. That failed. I then did the reverse. I went back to just using: /usr/share/java/mysql-connector-java.jar And then edited the database connection to use: com.mysql.jdbc.Driver string And it worked. So (I guess) there is no Base bug, but a mariadb issue. Whether I can get them to look at it may be challenging, but I'll try sometime. Will you change this report's status or shall I?
@Tim let's keep it as unconfirmed for the moment, I've changed the title to be more specific. Ideally, we'd need someone with a similar setup to test. I might be able to give this a try on a different machine or a VM. Alex
The problem could also be Ubuntu-package specific, in which case it wouldn't be our bug...
@Robert, Bernard : just wondering if you could test this yourselves - trying to narrow down the problem. The problem seems to lie in the libmariadb-java JDBC connector. @Robert : I'm guessing that on Suse, this is called something slightly different.
@Julien : if you have the time or the inclination, could you see whether you can confirm ?
Sorry, Alex! I made a mistake when adding myself in the cc liste, my PC is under Windows, not Linux. Bernard
Have tested this one with mariadb-java-client-2.2.3.jar and mariadb-java-client-2.4.0.jar. Queries, which will connect to queries, doesn't work. The JDBC-driver will look for this queries in the MariaDB as tables. Then I tried the same with mysql-connector-java-8.0.11.jar. Queries, which will connect to queries, will work. All tested with OpenSUSE 15 64bit rpm Linux and different LO-Versions, now with LO 6.1.5.2., also with LO 5.1.5.2. Is this a bug of LO (in the special properties for this connector) or is it a bug of the mariadb-java-client?
I really appreciate people looking into the stuff I report. I often think "It's just me", but on the off-chance it might be something more I report most of them after a while. One of the reasons I reported this one was that I couldn't get the native connector to work at all in 6.2 on ubuntu (still a pre-release there) so was worried I might have to use JDBC for all access. And since JDBC was giving me problems with some queries, it mattered. I know now there is a workaround for me using the mysql driver, so can be more confident going to 6.2. Let me know if there's anything you would like me to do.
(In reply to Robert Großkopf from comment #17) > > Is this a bug of LO (in the special properties for this connector) or is it > a bug of the mariadb-java-client? Thanks Robert. You raise a very good question, to which, unfortunately, I have no answer.
I reinstalled Debian on a new pc. There's no more phpmyadmin, just adminer and can't succeed in making it work. However, my main pb is I tested JDBC with classname: com.mysql.jdbc.Driver and org.mariadb.jdbc.Driver both fails when testing Mysql/JDBC connection with test class button. whereas I downloaded mariadb-java-client-2.4.0.jar from https://downloads.mariadb.com/Connectors/java/connector-java-2.4.0/ and added it to the classpath. Any thoughts?
I also installed libreoffice-mysql-connector and have this in my autogen.input: --without-system-mariadb --enable-bundle-mariadb
(In reply to Julien Nabet from comment #20) > ... > > Any thoughts? I had just forgotten to click Apply when I added jar. So I uninstalled libreoffice-mysql-connector and test class works. Sorry for the noise.
On pc Debian x86-64 with master sources updated today, I could reproduce this. Queries are ok except NIC4RatesPivotOld. For last one, I got: SQL Status: 42S02 Error code: 1146 (conn=93) (conn=93) Table 'NestedQueries.NIC4Rates' doesn't exist
I have now upgraded to Version: 6.2.2.2 Build ID: 1:6.2.2-0ubuntu0.18.10.1~lo1 CPU threads: 8; OS: Linux 4.18; UI render: default; VCL: x11; Locale: en-GB (en_GB.UTF-8); UI-Language: en-GB Calc: threaded Mysql(Native) no longer seems to exist, and I have to use the mysql rather than mariadb driver and jar. Should I raise this as a separate issue? Is it just ubuntu, or a general issue? One should be able to test with this test database.
(In reply to tim from comment #24) > Mysql(Native) no longer seems to exist, and I have to use the mysql rather > than mariadb driver and jar. Should I raise this as a separate issue? Is > it just ubuntu, or a general issue? One should be able to test with this > test database. @Tim: where did you upgrade from ? The native connector has to be provided as a separate package under Ubuntu-released versions, which means that if you are using a user-provided PPA as your package source, it might well be that there is no native connctor package (as it has to be built and provided separately and the packagers aren't always up to date on that, or simply can't be bothered). One of my biggest gripes with LO-PPA supplied packages is that they often don't include all the "extras" that come as standard with the TDF official release DEB versions, leading to screwed up user configurations, or dysfunctional LO installations...
I originally installed from the ubuntu ppa, and took this update. The libreoffice-mysql-connector is installed, and the same version as the rest (6.2.2), but when I edit the database the MySQL(Native) option is not there any more.
I have tested under Version: 6.2.3.2 Build ID: 1:6.2.3-0ubuntu0.19.04.1~lo1 CPU threads: 8; OS: Linux 5.0; UI render: default; VCL: x11; Locale: en-GB (en_GB.UTF-8); UI-Language: en-GB Calc: threaded with mariadb 10.3 built for ubuntu 19.04 (disco). With mariadb jdbc (/usr/share/javamariadb-java-client-2.4.0.jar) and using org.mariadb.jdbc.Driver the problem is still present. I still don't seem to have any MySQL(Native) connector as a database connection choice, even if I install the libreoffice-mysql-connector.
Tested this again with MariaDB and mariadb-java-client-2.4.1.jar and mysql-connector-java-5.1.40-bin.jar. Nested queries fail with the mariadb-connector, will work with the mysql-connector.
Don't think this is a bug of JDBC-Connector. The query-GUI should first test if there is a nested query. If not it should send the query directly to the database through the connector. Why is it send directly?
I just tried mariadb-java-client-2.4.2.jar. It still fails, so I have reverted to the mysql version.
Dear tim, To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present with the latest version of LibreOffice from https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the information from Help - About LibreOffice. If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a comment that includes the information from Help - About LibreOffice. Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) If you want to do more to help you can test to see if your issue is a REGRESSION. To do so: 1. Download and install oldest version of LibreOffice (usually 3.3 unless your bug pertains to a feature added after 3.3) from https://downloadarchive.documentfoundation.org/libreoffice/old/ 2. Test your bug 3. Leave a comment with your results. 4a. If the bug was present with 3.3 - set version to 'inherited from OOo'; 4b. If the bug was not present in 3.3 - add 'regression' to keyword Feel free to come ask questions or to say hello in our QA chat: https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug
I have retested this using Version: 7.1.4.2 / LibreOffice Community Build ID: 10(Build:2) CPU threads: 8; OS: Linux 5.11; UI render: default; VCL: gtk3 Locale: en-GB (en_GB.UTF-8); UI: en-GB Ubuntu package version: 1:7.1.4~rc2-0ubuntu0.21.04.1~lo1 Calc: threaded using mariadb-java-client-2.7.3.jar (org.mariadb.jdbc.Driver) and also using mysql-connector-java-5.1.45.jar (com.mysql.jdbc.Driver) Nothing has changed: MYSQL(Native) no longer exists MYSQL(Java) with the mariadb java client fails as before MYSQL(Java) with the mysql java client works as expected. Since I have a workaround I think this could be closed - it's a bug somewhere, but not a problem.
(In reply to tim from comment #32) > > Nothing has changed: > > MYSQL(Native) no longer exists MySQL native is integrated into LO since LO 6.2, isn't it? I could connect through the direct connection here with packages installed from LO. If I try to create a query based on a query it will work with the native connector installed here - but there are other special bugs for this connector …
(In reply to Robert Großkopf from comment #33) > (In reply to tim from comment #32) > > > > Nothing has changed: > > > > MYSQL(Native) no longer exists > > MySQL native is integrated into LO since LO 6.2, isn't it? I could connect > through the direct connection here with packages installed from LO. > > If I try to create a query based on a query it will work with the native > connector installed here - but there are other special bugs for this > connector … Apologies, but I don't understand - what type of database connection is that in the list of Database Types? I don't see any 'Native' connector.
(In reply to tim from comment #34) > Apologies, but I don't understand - what type of database connection is that > in the list of Database Types? I don't see any 'Native' connector. When choosing "MySQL" I see → Connect using ODBC → Connect using JDBC → Connect directly The last is the "native" connector.
(In reply to Robert Großkopf from comment #35) > (In reply to tim from comment #34) > > > Apologies, but I don't understand - what type of database connection is that > > in the list of Database Types? I don't see any 'Native' connector. > > When choosing "MySQL" I see > → Connect using ODBC > → Connect using JDBC > → Connect directly > > The last is the "native" connector. I'm sorry - I seem to be coming from a different place from you. Where are you choosing "MySQL"? I don't have that in the Database Type list - only "MySQL(JDBC)". Am I missing some other feature? I see JDBC on its own, and ODBC on its own.
(In reply to tim from comment #36) > > Where are you choosing "MySQL"? I don't have that in the Database Type list > - only "MySQL(JDBC)". Am I missing some other feature? I see JDBC on its > own, and ODBC on its own. Which packages do you use? I have installed the original packages from LO. You reported the bug for the packages from Ubuntu. So it could be a special "feature" from Ubuntu only to show the JDBC-Connection for MySQL. Let us solve this problem per private mail.
I think this report can be closed. The MySQL JDBC connector works fine - the MariaDB one is to be avoided if one gets query problems, and there's a straightforward workaround. As to the MySQL Native option, it seems that is only available in new odbs. For some reason one cannot change the database type to MySQL(Native) in an existing database, which is why I was puzzled by suggestions that I use it instead. Is that a 'feature' or a 'bug'? If people consider it to be a bug I'll raise a report, otherwise I'll leave it. The main database I use has a lot of macros, forms, queries & reports and to recreate it will take time, so I may not get around to trying it.
(In reply to tim from comment #38) > I think this report can be closed. The MySQL JDBC connector works fine - > the MariaDB one is to be avoided if one gets query problems, and there's a > straightforward workaround. I will set this bug to "WORKSFORME", because it seems to be a special bug of MariaDB-JDBC-connection and could be solved by using MySQL-JDBC-connection.
OK. Thanks for your help.