Bug 119139 - Mysql timestamp fields not displayed in table data entry mode with mysql jdbc-connector ConnectorJ 8.x
Summary: Mysql timestamp fields not displayed in table data entry mode with mysql jdbc...
Status: UNCONFIRMED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.2.5.2 release
Hardware: All Mac OS X (All)
: high major
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: bibisectRequest, regression
Depends on: 119850
Blocks: Database-Connectivity
  Show dependency treegraph
 
Reported: 2018-08-07 09:23 UTC by Alex Thurgood
Modified: 2019-03-21 09:57 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Screenshot of querying timestamps in LO and terminal (160.40 KB, image/png)
2019-03-19 12:11 UTC, Alex Thurgood
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Alex Thurgood 2018-08-07 09:23:50 UTC
Description:
Mysql timestamp fields are not displayed in table data entry mode after double-clicking on a table.

In comparison, using the native mysql connector extension shows the timestamp fields.

Setting the connection parameter string zeroDateTimeBehavior=convertToNull doesn't have any effect on the display, even after a shutdown and relaunch of LO.

Steps to Reproduce:
1. Create a mysql table containing a NOT NULL timestamp field with DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

2. For example, use the following DDL in the Tools > SQL dialog or via the mysql command line:

create table ts7 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, fname VARCHAR(50), ts7 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

3. Refresh the list of tables in the LO UI
4. Double-click on the table ts7 to open the data entry mode
5. Notice that the timestamp field is not displayed.
6. ANy attempt to enter data in the fname field will generate an error indicating that required data for the missing ts field has not been entered !


Actual Results:
1. Create a mysql table containing a NOT NULL timestamp field with DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

2. For example, use the following DDL in the Tools > SQL dialog or via the mysql command line:

create table ts7 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, fname VARCHAR(50), ts7 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

3. Refresh the list of tables in the LO UI
4. Double-click on the table ts7 to open the data entry mode
5. Notice that the timestamp field is not displayed.
6. ANy attempt to enter data in the fname field will generate an error indicating that required data for the missing ts field has not been entered !


Expected Results:
The timestamp field should be displayed in the UI and allow data entry.


Reproducible: Always


User Profile Reset: No



Additional Info:
Mysql Connector J : 5.1.36
Comment 1 Alex Thurgood 2018-08-07 09:25:52 UTC
Adding the parameter :

zeroDateTimeBehavior=convertToNull

to the connection string in the connection properties has no effect.
Comment 2 Alex Thurgood 2018-08-07 09:29:43 UTC
Further to my initial comment about not being able to enter data without generating an error, this is incorrect, varchar data can be entered and validated.

In the underlying mysql table, the timestamp data is also present.
Comment 3 Alex Thurgood 2018-08-07 09:31:30 UTC
This used to work. I used similarly designed tables for many years with the jdbc-connector and the timestamp fields were displayed in the Table data entry mode.
Comment 4 Alex Thurgood 2018-08-07 11:57:15 UTC
Testing with

Version 3.6.7.2 (Build ID: e183d5b)

1) Create an ODB file linked via the jdbc-connector to a mysqldb having tables with timestamp fields

2) Open the table for data entry

3) Notice that timestamp fields are displayed.
Comment 5 Alex Thurgood 2018-08-07 12:11:17 UTC
Problem not reproducible in 

LO4162

but reproducible in LO Version: 4.2.5.2
Build ID: 61cb170a04bb1f12e77c884eab9192be736ec5f5
Comment 6 Robert Großkopf 2018-08-07 14:07:46 UTC
Couldn't reproduce with MariaDB and connector mariadb-java-client-2.2.3.jar.

Timestamp-field could be created, is shown and values could be saved. If there are rows in the table before the TIMESTAMP here has been created as the moment I created the TIMESTAMP-field.

When opening the same datasource with mysql-connector-java-5.1.40-bin.jar the field isn't shown - together with a DateTime field, which should save the DateTime of an update of the table.

Seems to be a bug of mysql-connector together with MariaDB.
Comment 7 Alex Thurgood 2018-09-19 08:05:34 UTC
Also reproducible on MacOS with:

LO6112
mysql-connector-java-5.1.36-bin.jar
Comment 8 Alex Thurgood 2018-09-19 08:11:24 UTC
Also tested against latest Connector/J driver : 

mysql-connector-java-8.0.12-bin.jar
Comment 9 Alex Thurgood 2018-09-19 08:11:52 UTC
(In reply to Alex Thurgood from comment #8)
> Also tested against latest Connector/J driver : 
> 
> mysql-connector-java-8.0.12-bin.jar

With the same result, i.e. no timestamp fields are displayed.
Comment 10 Julien Nabet 2018-09-19 08:15:41 UTC
Tamas: since it concerns Mysql, thought you might be interested in this one.
Comment 11 Alex Thurgood 2018-09-19 08:24:11 UTC
Actually, those last 2 remarks are not entirely accurate as I had failed to remove the previous classpath setting to 5.1.36 (and LO appears to search sequentially in the list of added jars...).

Now with the above mentioned latest Connector/J version as the only added connector.jar, no tables will load at all because of the following error message:

"The server time zone value CEST is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimeZone configuration property) to use a more specific time zone value if you want to utilize time zone support."

As a result of this, no further progress can be made.

This problem is currently also known under bug 119786
Comment 12 Alex Thurgood 2018-09-19 09:02:11 UTC
(In reply to Alex Thurgood from comment #11)

Using a workaround in the connection string of ?serverTimezone=UTC at least allows me to load the tables.

Nonetheless, the timestamp fields are still not visible.
Comment 13 Julien Nabet 2018-09-19 09:05:10 UTC
Like tdf#119786, investigation won't be easy because of tdf#119850.
Comment 14 Alex Thurgood 2018-09-26 09:29:21 UTC
Still reproducible with:

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

and Connector/J 8.0.12

with the following options added to connection string:

?zeroDateTimeBehavior=convert_To_Null&serverTimezone=UTC
Comment 15 Alex Thurgood 2019-01-21 15:35:16 UTC
Indeed, tested this on another Macmini with Mojave, LO6132 and Mysql JDBC Connector 8.0.12 and the problem is the same.

Reverting back to ConnectorJ 5.1.47 allows the timestamp fields to be displayed.

The problem is that the 8.x series is current, whereas the 5.1.47 isn't.
Comment 16 Alex Thurgood 2019-01-21 15:52:13 UTC
Still reproducible with

Version: 6.3.0.0.alpha0+
Build ID: 810e5008e75e54241e056de2129886689d7c21eb
CPU threads: 4; OS: Mac OS X 10.14.2; UI render: default; VCL: osx; 
Locale: fr-FR (fr_FR.UTF-8); UI-Language: en-US
Calc: threaded

and ConnectorJ 8.0.14
Comment 17 Alex Thurgood 2019-01-21 16:00:59 UTC
LLDB output when connecting to a mysql instance with ConnectorJ 8.0.14 :


Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.

I have no idea what that means. It sounds like LO is being accused of incorrectly loading the driver class and that it should be loading it in another way ?
Comment 18 Alex Thurgood 2019-01-21 16:14:50 UTC
Reading up on stackexchange, I changed the driver string as suggested in the warning message, but it made no difference.
Comment 19 Julien Nabet 2019-01-22 21:37:35 UTC
Lionel: about the classname searching about "com.mysql.jdbc.Driver" on Opengrok, gives "https://opengrok.libreoffice.org/search?project=core&q=%22com.mysql.jdbc.Driver%22&defs=&refs=&path=&hist=&type=&si=q

Should we just replace com.mysql.jdbc.Driver by com.mysql.cj.jdbc.Driver (see https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-api-changes.html)?
If yes, what about those who have an older version of the connector?
Also, should it be in a separated bugtracker?
Comment 20 Julien Nabet 2019-03-17 09:34:08 UTC
Just for the record, on pc Debian x86-64 with master sources updated today + Mariadb server 10.3.13 + Mariadb connector 2.4.0, I don't reproduce this.
Comment 21 Xisco Faulí 2019-03-18 20:17:46 UTC
@Alex Thurgood,
Could you please try to reproduce it with a master build from http://dev-builds.libreoffice.org/daily/master/ ?
You can install it alongside the standard version.
I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' if the bug is still present in the master build
Comment 22 Alex Thurgood 2019-03-19 08:34:56 UTC
(In reply to Xisco Faulí from comment #21)
> @Alex Thurgood,
> Could you please try to reproduce it with a master build from
> http://dev-builds.libreoffice.org/daily/master/ ?

There is no current master available, the last was from March 13th, 2019
Comment 23 Alex Thurgood 2019-03-19 12:10:35 UTC
Problem still present in 

Version: 6.3.0.0.alpha0+
Build ID: bea44e5c1b982d05314b72452b94fe288071a496
CPU threads: 4; OS: Mac OS X 10.14.2; UI render: default; VCL: osx; 
TinderBox: MacOSX-x86_64@49-TDF, Branch:master, Time: 2019-03-13_05:31:30
Locale: fr-FR (fr_FR.UTF-8); UI-Language: en-US
Calc: threaded

with mysql-connector-java-8.0.15
and mysql server 8.0.13

See enclosed screenshot
Comment 24 Alex Thurgood 2019-03-19 12:11:30 UTC
Created attachment 150098 [details]
Screenshot of querying timestamps in LO and terminal
Comment 25 Alex Thurgood 2019-03-21 08:19:22 UTC
I encounter the very same problem with a freshly created ODB file pointing to a mysql instance with :

LO6073 (1:6.07-0ubuntu0.18.04.2

and libmysql-java 5.1.45-1 (without any additional parameters)

in
ElementaryOS 5.0 (Juno) based on Ubuntu 18.04.2 LTS with Gtk+3.22.30

If I use the mysql client library I see all of the records including the timestamp data.

In LO, if I enter the table definition edit mode, LO indicates that the field in question is a DATETIME field and not a timestamp field, whereas it is clearly a timestamp field when using DESCRIBE TABLE from the mysql command line.