we use the Mysql Native connector for Windows extension to make the connection to the MySQL database Like is still working in a general where clause but not when used in a JOIN clause atable is a table in a MYSQL database atable.field is a field in the atable JOIN atable on atable.field like "text%" gives a error where atable.field like "text%" gives no error Steps to reproduce: 1. .... 2. .... 3. .... Current behavior: Expected behavior: Operating System: Windows XP Version: 4.1.0.1 rc Last worked in: 4.0.4.2 release
Lionel - looks quite serious, you mind poking at it?
Let me guess... One of your "JOIN" tables has a TIME or TIMESTAMP (DATETIME) column, and the native connector you use is compiled for LibreOffice 4.0. There is a backwards-binary-incompatible change in LibreOffice 4.1 for time-related datatypes. We may do another one between 4.1.0.rc1 and 4.1.0.final, or not, not completely decided yet. We plan that this will be the last one and the LibreOffice 4 ABI will stabilise once 4.1.0 is released. The MySQL native connector needs a recompile for LibreOffice 4.1. Hopefully we can do that in time for the release.
I have datetime fields in MySql tables which are being misinterpreted in Base 4.1.0.1 on ubuntu 12.04 64 bit. All the pre-existing dates appear to be 01/01/1800. I am using Mysql native connector. Using JDBC I get other errors on datetime fields (it says the datetime is 0 and invalid). Is this the same problem?
Correction - with JDBC it works fine. I got an error using JDBC because a record had previously been saved using mysql connector creating an invalid datetime. JDBC quite properly didn't like it when I tried to read it.
(In reply to comment #3) > I have datetime fields in MySql tables which are being misinterpreted in > Base 4.1.0.1 on ubuntu 12.04 64 bit. All the pre-existing dates appear to > be 01/01/1800. > I am using Mysql native connector. Are you using an Ubuntu build (PPA?) or a TDF build? If you are using an Ubuntu build, make sure you use the MySQL native connector from the same Ubuntu source/version. Please show me: COLUMNS=300 dpkg -l libreoffice COLUMNS=300 dpkg -l libreoffice-mysql-connector If you are using a TDF build, please explain which MySQL native connector you use. Compiled yourself against 4.1 sources? The ones on http://extensions.libreoffice.org/ are *not* compatible with LibreOffice 4.1. It is written on their download page.
>> Lionel Indead nearly all our tables have a timestamp (to know when and who has updated) i most also admid that LIKE in a join is not a good practice and can been avoided. Anyhow i hope Jésus finished sucesfull his Thesis and can make a recompile for 4.1
Short version: I see an error, maybe the reported error or maybe not, without going near any time-related column. Workaround suggested. Long version ... I am setting bug status NEW, but this is merely a guess, as Fernand does not tell us exactly what error he saw. If his error is the error that I see, then the bug summary should be changed to not reference MySQL. So, for specificity, let me say that in a quite different environment, I issued SQL statement select * from Table1 a join Table2 b on a.number like '0%' and LibreOffice presented a dialog Syntax error in SQL statement with <More> revealing SQL Status:HY000, Error code: 1000, and further descriptions ... Syntax error in SQL statement SELECT * FROM "Table1" "a" JOIN "Table2" "b" ON LIKE '0%' Syntax error in SQL statement Note that the second of these further descriptions omits the variable before the LIKE operator. For comparison, `isql` accepts and processes my sql statement correctly, and LibreOffice accepts and processes correctly the statement select * from Table1 a join Table2 b on a.number = b.number where a.number like '0%' This suggests a workaround for the error. My .odb file uses an ODBC connection to PostgreSQL, and my Libreoffice (which no longer has the crash reported in bug 65619 "segfault querying spreadsheet with LIKE criterion" and bug 65653 "EDITING: Base crashes with queries with keyword LIKE in GUI-Mode") is master commit 60e2096, pulled 2013-06-18, configured with --enable-option-checking=fatal --enable-dbgutil --enable-crashdump --without-system-postgresql --without-myspell-dicts --without-help --with-extra-buildid --without-doxygen built and executing ubunty-natty (11.04) 32-bit. HTH, Terry.
(In reply to comment #7) > Short version: I see an error, maybe the reported error or maybe not, > without going near any time-related column. Terrence, in your scenario, does it work in LibreOffice 4.0.x (or earlier)?
I find the bug ABSENT in the following versions, all of them Linux 32-bit: (*) 4.0.3.1 Linux 32-bit (build id a67943c) (*) 3.6.4.3 Linux 32-bit (build id2ef5aff) (*) 3.3.4 (OOO330m19 (Build:401), tag libreoffice-3.3.3.1, Ubuntu package 1:3.3.4-0ubuntu1.4) Alas, I cannot try my daily bibisect (which is awfuly old, anyway) because my 64-bit Linux lacks ODBC. FWIW, the option "execute SQL statement directly" is another fine workaround for the problem.
On the assumption that Fernand is reporting the same error that I see, I am changing ... - summary, because - I see the problem with ODBC to PostgreSQL - LIKE works in the WHERE clause - platform All, because I see the problem on Linux - priority way down, because there is an easy workaround Of course, if Fernand is reporting a different error, then this is wrong.
OK, this looks like it is unrelated to MySQL connector indeed. This could be related to http://cgit.freedesktop.org/libreoffice/core/commit/?id=16c9ce4877def18ca4578171a96615a632d08092 I'll try to investigate tommorrow.
(In reply to comment #5) > (In reply to comment #3) > > I have datetime fields in MySql tables which are being misinterpreted in > > Base 4.1.0.1 on ubuntu 12.04 64 bit. All the pre-existing dates appear to > > be 01/01/1800. > > > I am using Mysql native connector. > > Are you using an Ubuntu build (PPA?) or a TDF build? > > If you are using an Ubuntu build, make sure you use the MySQL native > connector from the same Ubuntu source/version. Please show me: > > COLUMNS=300 dpkg -l libreoffice > COLUMNS=300 dpkg -l libreoffice-mysql-connector > > > If you are using a TDF build, please explain which MySQL native connector > you use. Compiled yourself against 4.1 sources? The ones on > http://extensions.libreoffice.org/ are *not* compatible with LibreOffice > 4.1. It is written on their download page. LO: ii libreoffice 1:4.1.0~rc1-buildfix1-0ubuntu1~precise1~ppa5 office productivity suite (metapackage) and: un libreoffice-mysql-connector <none> (no description available) I was not aware that the connector was not compatible. I'll try the version from the PPA.
I tried using the connector version downloaded from the ppa, which seems to be 1.0.2 and it's got the same problem as 1.0.3. I'll stick with jdbc for now (which works ok, just slower) Will this be fixed in the full release version of 4.1?
Just to be clear, using the ppa to install the connector I now get: ii libreoffice-mysql-connector 1.0.2+LibO4.1.0~rc1-buildfix1-0ubuntu1~precise1~ppa5 MariaDB/MySQL Connector extension for LibreOffice Date/time fields are still mangled.
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=ede7f6ae5fa9fd7a466066de74941a09117cc9a0 fdo#66130 correct "skip column name" condition for LIKE predicate The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
(In reply to comment #14) > Just to be clear, using the ppa to install the connector I now get: > ii libreoffice-mysql-connector > 1.0.2+LibO4.1.0~rc1-buildfix1-0ubuntu1~precise1~ppa5 MariaDB/MySQL Connector > extension for LibreOffice > Date/time fields are still mangled. OK, this is a different bug than this one. 1) I fixed several issues around mangling of time and datetime (timestamp) fields in the last 2-3 days. If you could retest when the PPA is updated after today, it would be nice. Or restes with something from http://dev-builds.libreoffice.org/daily/libreoffice-4-1/ that is generated after today. 2) If you still get problems and/or you get problems with dates, please file a separate bug and put me in CC.
Fix for 4.1 submitted for review on https://gerrit.libreoffice.org/4606
(In reply to comment #16) > (In reply to comment #14) > > Just to be clear, using the ppa to install the connector I now get: > > > ii libreoffice-mysql-connector > > 1.0.2+LibO4.1.0~rc1-buildfix1-0ubuntu1~precise1~ppa5 MariaDB/MySQL Connector > > extension for LibreOffice > > > Date/time fields are still mangled. > > OK, this is a different bug than this one. > > 1) I fixed several issues around mangling of time and datetime (timestamp) > fields in the last 2-3 days. If you could retest when the PPA is updated > after today, it would be nice. Or restes with something from > http://dev-builds.libreoffice.org/daily/libreoffice-4-1/ > that is generated after today. > > 2) If you still get problems and/or you get problems with dates, > please file a separate bug and put me in CC. If this is a connector problem what do I need to re-install? The connector or LO or both? I have been struggling slightly having made the jump to 4.1 on my main system. Ubuntu kept wanting to re-install a 3.n version, and I couldn't find a good way of having 3 and 4 on my system, so I added the pre-release PPA for 4.1. However, that doesn't seem to include the GB localisation, and seems to use an old mysql connector. Is there a recommended way of having both a final release and a pre-release (or beta) fully installed on ubuntu? I have failed to find it.
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "libreoffice-4-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=ca0bbec3c9ce2defee96743cf63227e46cd01c11&h=libreoffice-4-1 fdo#66130 correct "skip column name" condition for LIKE predicate It will be available in LibreOffice 4.1. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
(In reply to comment #16) > (In reply to comment #14) > > Just to be clear, using the ppa to install the connector I now get: > > > ii libreoffice-mysql-connector > > 1.0.2+LibO4.1.0~rc1-buildfix1-0ubuntu1~precise1~ppa5 MariaDB/MySQL Connector > > extension for LibreOffice > > > Date/time fields are still mangled. > > OK, this is a different bug than this one. > > 1) I fixed several issues around mangling of time and datetime (timestamp) > fields in the last 2-3 days. If you could retest when the PPA is updated > after today, it would be nice. Or restes with something from > http://dev-builds.libreoffice.org/daily/libreoffice-4-1/ > that is generated after today. > > 2) If you still get problems and/or you get problems with dates, > please file a separate bug and put me in CC. Having taken extra care about which native connector I am using I can now confirm that the mysql native connector shipped with the ubuntu pre-release PPA works OK with date/times, but the extension downloaded from the website (tools, extensions ...), which states it is version 1.0.3, does not. I had to ensure that I had completely removed the 1.0.3 version before using synaptic to re-install the connector from the PPA (recorded as 1.0.2), and it seems I had previously not done so correctly. With apologies for wasting people's time.
With master commit a08f579 fetched 2013-06-28 18:30 UTC, following the steps from comment 7, I see that the bug is fixed. Fernand: If the problem is fixed for you and you are reporting the same problem, can you please change the bug status to VERIFIED? Thanks, Terry.