[Libreoffice-bugs] [Bug 120091] New: After upgrading MySQL Connector/J from 5.1.46 to 8.0.11 or 8.0.12, getting error msg "the data could not be loaded"

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=120091

Bug ID: 120091
Summary: After upgrading MySQL Connector/J from 5.1.46 to
8.0.11 or 8.0.12, getting error msg "the data could
not be loaded"
Product: LibreOffice
Version: 5.4.7.2 release
Hardware: All
OS: All
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Base
Assignee: libreoffice-***@lists.freedesktop.org
Reporter: ***@outlook.com

Description:
My LO Base form works correctly with MySQL Connector/J 5.1.46 (and earlier).
I am using MySQL 5.7.23 server.
If I change the connector to the new version of Connector/J (8.0.11 or 8.0.12),
then, when opening the form in Base, I immediately get error msg "the data
could not be loaded".
All my other forms are working correctly with both connector versions.

Steps to Reproduce:
1. import the attached file (Dump20180923) into MySQL. It creates the database
(or schema) testcj8 and loads tables and views.
2. open the attached LO Base file (TestCJ8 on localhost).
3. open the LO Base form (Form-per-Dag).
4. reply "root" as username and password.
5. you get error message "the data could not be loaded"

Actual Results:
5. you get error message "the data could not be loaded" (see attached
screenshot testcj8NOTOK)

Expected Results:
6. go to "Tools", "Options...", "LibreOffice", "advanced", "Class path...",
"Add Archive..." and select "C:\Program Files (x86)\MySQL\Connector J
5.1\mysql-connector-java-5.1.46" instead of "C:\Program Files
(x86)\MySQL\Connector J 8.0\mysql-connector-java-8.0.12"
7. result : the 2nd grid table is correctly filled. see attached screenshot
(testcj8OK)


Reproducible: Always


User Profile Reset: Yes



Additional Info:
Version: 6.0.6.2 (x64)
Build ID: 0c292870b25a325b5ed35f6b45599d2ea4458e77
CPU threads: 4; OS: Windows 10.0; UI render: default;
Locale: nl-BE (en_US); Calc: group

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=120091

--- Comment #1 from ***@outlook.com ---
Created attachment 145128
--> https://bugs.documentfoundation.org/attachment.cgi?id=145128&action=edit
zip with 4 files to reproduce the problem

zipped file with 4 files to reproduce the problem :

1. Dump20180923 : the file to be imported into MySQL (with mysqldump)
2. TestCJ8 on localhost : the LO Base file
3. testcj8NOTOK : screenshot with Connector/J 8.0.12
4. testcj8OK : screenshot with Connector/J 5.1.46

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=120091

--- Comment #2 from Alex Thurgood <***@tuta.io> ---
I can connect to several of my own remote mysql instance databases via the
8.0.12 Connector/J on MacOS, and LibreOffice

Version: 6.2.0.0.alpha0+
Build ID: 0b06762ff19a804d3b86167ae3012811662412f1
CPU threads: 4; OS: Mac OS X 10.13.6; UI render: default;
Locale: fr-FR (fr_FR.UTF-8); Calc: threaded


so the problem could be due to:

- some specific configuration of your database ;
- a duplication of connector/J JAR files added to the classpath in LibreOffice,
i.e. you have two different Connector/J jar files added via the classpath
configurator dialog (Tools > Options > Advanced > Add Jar archive) ?

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=120091

Alex Thurgood <***@tuta.io> changed:

What |Removed |Added
----------------------------------------------------------------------------
Status|UNCONFIRMED |NEW
Ever confirmed|0 |1

--- Comment #3 from Alex Thurgood <***@tuta.io> ---
I can however reproduce the error message

"The data content could not be loaded"

with tables or views that contain a datetime definition, such as the one below:

CREATE TABLE `AC` (
`bid` int(10) NOT NULL AUTO_INCREMENT,
`Ac` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`Cur` varchar(3) COLLATE utf8_unicode_ci NOT NULL,
`dt` datetime DEFAULT NULL,
PRIMARY KEY (`bid`),
KEY `cur` (`Cur`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=120091

Alex Thurgood <***@tuta.io> changed:

What |Removed |Added
----------------------------------------------------------------------------
Summary|After upgrading MySQL |Datetime fields causing
|Connector/J from 5.1.46 to |error message "the data
|8.0.11 or 8.0.12, getting |content could not be
|error msg "the data could |loaded" with MySQL
|not be loaded" |Connector/J 8.0.11 or
| |8.0.12

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=120091

Alex Thurgood <***@tuta.io> changed:

What |Removed |Added
----------------------------------------------------------------------------
Keywords| |regression
Severity|normal |major
Priority|medium |high

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=120091

--- Comment #4 from Alex Thurgood <***@tuta.io> ---
I also see this same error message with server side views containing calculated
dates such as this one :

CREATE VIEW `natpha_ddl` AS
select `oldfmpro`.`File_Num` AS `file_num`,
`oldfmpro`.`Owner1` AS `Owner1`,
`oldfmpro`.`Filing_Num` AS `filing_num`,
`oldfmpro`.`Filing_Date1` AS `filing_date1`,
`oldfmpro`.`Prio_FF_Date1` AS `Prio_FF_Date1`,
if(((`oldfmpro`.`Prio_FF_Date1` + interval 30 month) is not null),
(`oldfmpro`.`Prio_FF_Date1` + interval 30 month),
(`oldfmpro`.`Filing_Date1` + interval 30 month)) AS `NATPHA` from `oldfmpro`
where ((`oldfmpro`.`Filing_Num` is not null)
and (`oldfmpro`.`Ctry_Short` = 'WO')
and (`oldfmpro`.`IngJur` like '%ajt%')
and (`oldfmpro`.`Filing_Date1` >= (curdate() + interval -(30) month)))
order by
if(((`oldfmpro`.`Prio_FF_Date1` + interval 30 month) is not null),
(`oldfmpro`.`Prio_FF_Date1` + interval 30 month),
(`oldfmpro`.`Filing_Date1` + interval 30 month))

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=120091

--- Comment #5 from Alex Thurgood <***@tuta.io> ---
Another example, this view also produces the same error message :

CREATE VIEW `classifier_list` AS
select `classifier`.`ID` AS `classifier_ID`,`classifier`.`matter_ID` AS
`matter_ID`,`classifier_type`.`type` AS `type`,
if(`classifier`.`value_ID`,
`classifier_value`.`value`,
`classifier`.`value`) AS `value`
from (`classifier` left join (`classifier_type` join `classifier_value`)
on(((`classifier`.`type_code` = `classifier_type`.`code`)
and (`classifier`.`value_ID` = `classifier_value`.`ID`))))

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=120091

Alex Thurgood <***@tuta.io> changed:

What |Removed |Added
----------------------------------------------------------------------------
Summary|Datetime fields causing |Some fields causing error
|error message "the data |message "the data content
|content could not be |could not be loaded" with
|loaded" with MySQL |MySQL Connector/J 8.0.11 or
|Connector/J 8.0.11 or |8.0.12 in tables and views
|8.0.12 |

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=120091

--- Comment #6 from Alex Thurgood <***@tuta.io> ---
Testing with Connector/J 5.1.47 shows all the required views containing
calculated /conditional values and tables containing datetime fields.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=120091

--- Comment #7 from Alex Thurgood <***@tuta.io> ---
This could be a problem with the Connector/J and not LibreOffice, in which case
this report will be closed as NOTOURBUG.

Ideally, the connector would need to be tested in a non-LO environment to see
whether the same or similar behaviour is observed.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=120091

--- Comment #8 from Alex Thurgood <***@tuta.io> ---
Testing with RazorSQL 8.0.8 and the Connector/J 8.0.12 jar, I don't experience
any of the problems that I see with LibreOffice

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=120091

--- Comment #9 from Julien Nabet <***@yahoo.fr> ---
Alex: sorry I can't do anything for the moment because my local build has
failed since 4 days. It's gtk3 related but even Caolán doesn't have any hint.
He transferred the pb to Pranav.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=120091

Xisco Faulí <***@libreoffice.org> changed:

What |Removed |Added
----------------------------------------------------------------------------
CC| |***@libreoffice.org
Keywords| |bibisectRequest

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=120091

Julien Nabet <***@yahoo.fr> changed:

What |Removed |Added
----------------------------------------------------------------------------
CC|***@yahoo.fr |

--- Comment #10 from Julien Nabet <***@yahoo.fr> ---
I created the table from comment 3, then tried Mysql connection with JDBC mode,
no pb to open the table.
Can't help here, uncc myself.

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=120091

***@outlook.com changed:

What |Removed |Added
----------------------------------------------------------------------------
Summary|Some fields causing error |Some LO Base views with
|message "the data content |conversions or casting on
|could not be loaded" with |datetime fields causing
|MySQL Connector/J 8.0.11 or |error message "the data
|8.0.12 in tables and views |content could not be
| |loaded" with MySQL
| |Connector/J 8 (not with
| |with MySQL Connector/J 5)

--- Comment #11 from ***@outlook.com ---
I did some additional research on this problem : it appears when trying to
access a view which contains a CAST as DATE (or TIME) function on a DATETIME
(or TIMESTAMP) column.

Here is a short sample :

1. create a table with a datetime column :
CREATE TABLE `dt` (`datetime1` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`datetime1`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci;

2. insert a value :
INSERT INTO `testcj8`.`dt` (`datetime1`) VALUES ('2018-11-11 11:11:11');

3. create a view on this table, with a "cast as date" function on the datetime
column :
CREATE VIEW `view-dt` AS select `dt`.`datetime1` AS `datetime1`,
cast(`dt`.`datetime1` as date) AS `date1`, cast(`dt`.`datetime1` as time) AS
`time1` from `dt`;

4. access this view : SELECT * FROM `view-dt`;

- If LO base is connected to MySQL with MySQL Connector/J 5, there is no
problem.
- If LO base is connected to MySQL with MySQL Connector/J 8, there is a
problem : msg "the data could not be loaded".

Please note that the select statement used to create the view can be
successfully executed with both connectors !!!

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=120091

Roman Kuznetsov <***@mail.ru> changed:

What |Removed |Added
----------------------------------------------------------------------------
Blocks| |104790


Referenced Bugs:

https://bugs.documentfoundation.org/show_bug.cgi?id=104790
[Bug 104790] [META] Database connectivity interfaces (ODBC, JDBC..) and
external databases

[b***@bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=120091

Korrawit Pruegsanusak <***@gmail.com> changed:

What |Removed |Added
----------------------------------------------------------------------------
Attachment #145128|application/octet-stream |application/zip
mime type| |