Bug 120091 - Some LO Base views with conversions or casting on datetime fields causing error message "the data content could not be loaded" with MySQL Connector/J 8 (not with with MySQL Connector/J 5)
Summary: Some LO Base views with conversions or casting on datetime fields causing err...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
5.4.7.2 release
Hardware: All All
: high major
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: bibisectRequest, regression
Depends on:
Blocks: Database-Connectivity
  Show dependency treegraph
 
Reported: 2018-09-23 16:10 UTC by jnplg
Modified: 2018-12-08 16:26 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
zip with 4 files to reproduce the problem (233.86 KB, application/zip)
2018-09-23 16:22 UTC, jnplg
Details

Note You need to log in before you can comment on or make changes to this bug.
Description jnplg 2018-09-23 16:10:31 UTC
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
Comment 1 jnplg 2018-09-23 16:22:50 UTC
Created attachment 145128 [details]
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
Comment 2 Alex Thurgood 2018-09-24 08:29:05 UTC
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) ?
Comment 3 Alex Thurgood 2018-09-24 08:37:52 UTC
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
Comment 4 Alex Thurgood 2018-09-24 08:51:11 UTC
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))
Comment 5 Alex Thurgood 2018-09-24 08:56:19 UTC
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`))))
Comment 6 Alex Thurgood 2018-09-24 09:05:42 UTC
Testing with Connector/J 5.1.47 shows all the required views containing calculated /conditional values and tables containing datetime fields.
Comment 7 Alex Thurgood 2018-09-24 09:07:56 UTC
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.
Comment 8 Alex Thurgood 2018-09-24 10:29:35 UTC
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
Comment 9 Julien Nabet 2018-09-24 16:57:54 UTC
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.
Comment 10 Julien Nabet 2018-10-24 19:55:19 UTC
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.
Comment 11 jnplg 2018-11-10 15:04:01 UTC
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 !!!