Adding the parameter :

zeroDateTimeBehavior=convertToNull

to the connection string in the connection properties has no effect.

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.

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.

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.

Problem not reproducible in 

LO4162

but reproducible in LO Version: 4.2.5.2
Build ID: 61cb170a04bb1f12e77c884eab9192be736ec5f5

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.

Also reproducible on MacOS with:

LO6112
mysql-connector-java-5.1.36-bin.jar

Also tested against latest Connector/J driver : 

mysql-connector-java-8.0.12-bin.jar

(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.

Tamas: since it concerns Mysql, thought you might be interested in this one.

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

(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.

Like tdf#119786, investigation won't be easy because of tdf#119850.

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

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.

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

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 ?

Reading up on stackexchange, I changed the driver string as suggested in the warning message, but it made no difference.

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?

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.

@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

(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

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

Created attachment 150098 [details]
Screenshot of querying timestamps in LO and terminal

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.

Works for me too with

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

Closing

Sorry, commented on wrong bug report, arghh - reopened

Still not working with:

Version: 6.3.2.2
Build ID: 98b30e735bda24bc04ab42594c85f7fd8be07b9c
Threads CPU : 4; OS : Mac OS X 10.15; UI Render : par défaut; VCL: osx; 
Locale : fr-FR (fr_FR.UTF-8); Langue IHM : fr-FR
Calc: threaded

and Mysql Connector/J 8.0.18

Any table that contains a timestamp field defined as :

timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

does not display the timestamp field column or data held therein in the LO UI in table data edit mode.

https://www.razorsql.com/articles/mysql_jdbc_connect.html summarizes well the situation about JDBC with MariaDB/Mysql.
We got:
com.mysql.jdbc.Driver: the legacy class used for Mysql
com.mysql.cj.jdbc.Driver: for recent Mysql
org.mariadb.jdbc.Driver: for MariaDB

Only first one is used in LO:
https://opengrok.libreoffice.org/search?project=core&full=%22com.mysql.jdbc.Driver%22&defs=&refs=&path=&hist=&type=&xrd=&si=full&si=full

So :
1) We got "mysql_jdbc" and "jdbc" drivers (see https://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/), why not just keeping jdbc and dump mysql_jdbc to simplify?
Idem for ODBC part.
Selecting Mysql (or MariaDB if 1) is done), should just bring direct connection

If disagree, why don't we do the same for Postgresql for example?

(When a bug talks about Mysql with JDBC, how to know if Mysql_jdbc has been used or just JDBC?)

2) We use Mariadb package 2.0.0 (see https://opengrok.libreoffice.org/xref/core/download.lst?r=d400009e), what about renaming Mysql to MariaDB to prepare the 
fact the 2 DBs will have more and more difference with the time?
Of course, we may also propose both.

> why not just keeping jdbc and dump mysql_jdbc to simplify?

I suspect that would require a rework of the UI (and the help) as well though. Currently, the DB creation wizard (and also the mailmerge wizard, off the top of my head, and possibly the Define Addressbook dialog) gives you a specific dialog if you choose MySQL as the intended database at the first step. If we just had JDBC at that first step, then we would have to add specific entries in the first menu for the alternative MySQL connection possibilities. Personally, I'm not opposed to this, but we need to be consistent IMO.  

> If disagree, why don't we do the same for Postgresql for example?

If we do decide to go for just one list, then yes, bearing in mind the UI rework required.

 
> (When a bug talks about Mysql with JDBC, how to know if Mysql_jdbc has been
> used or just JDBC?)

Currently, as far as I know, the only visual clue is in the bottom of the Base window, where it specifies the type of connection being used.

 
> 2) We use Mariadb package 2.0.0 (see
> https://opengrok.libreoffice.org/xref/core/download.lst?r=d400009e), what
> about renaming Mysql to MariaDB to prepare the 
> fact the 2 DBs will have more and more difference with the time?
> Of course, we may also propose both.


We would need both entries to be complete, especially if they are diverging.


Whilst I agree pretty much with your suggestions, I don't really understand how that will solve the timestamp issue ?

(In reply to Alex Thurgood from comment #30)
> > why not just keeping jdbc and dump mysql_jdbc to simplify?
>...
> Whilst I agree pretty much with your suggestions, I don't really understand
> how that will solve the timestamp issue ?

You're right, it doesn't help indeed and certainly it wasn't the right place to talk about this but I thought we should begin to take into account the fork between both databases, perhaps bugs may be more difficult with the time to reproduce if we don't have the same DBs.
(for example, on my personal desktop, I got Linux with a MariaDB Server + use MariaDB jar)

(In reply to Julien Nabet from comment #31)


> You're right, it doesn't help indeed and certainly it wasn't the right place
> to talk about this but I thought we should begin to take into account the
> fork between both databases, perhaps bugs may be more difficult with the
> time to reproduce if we don't have the same DBs.
> (for example, on my personal desktop, I got Linux with a MariaDB Server +
> use MariaDB jar)

+1

Tested with mysql-connector-java-8.0.29.jar and connection string
mysql://localhost/test?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

Existing timestamp field will not be shown in the table. It won't be shown, because it is defined as
NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Other timestamp and datetime fields will be shown with this connector and MariaDB on OpenSUSE 15.3 64bit rpm Linux.
Same behavior with mysql-connector-java-5.1.33.jar and connection without special connection string.
Won't happen with JDBC-MariaDB-connector.

Now I open the query editor for creating a query, choose the table and the timestamp-field will be shown. Executing a query
SELECT * FROM `test`.`Tabelle1`
and the field has gone.
Executing
SELECT `Tabelle1`.*, `Tabelle1`.`DirectTime` FROM `test`.`Tabelle1` `Tabelle1`
and the field appears.

Executing 
SELECT * FROM `test`.`Tabelle1`
through tools → SQL and the field appears.

This all seems to be a special GUI problem of LO Base.

Bug still present in 

Version: 24.2.4.2 (AARCH64) / LibreOffice Community
Build ID: 51a6219feb6075d9a4c46691dcfe0cd9c4fff3c2
CPU threads: 8; OS: macOS 14.5; UI render: Skia/Raster; VCL: osx
Locale: fr-FR (fr_FR.UTF-8); UI: fr-FR
Calc: threaded

see attached screenshot

Created attachment 195161 [details]
Screenshot of table definition UI, grid view of table - no timestamp field displayed

Tested with :

Mysql Connector J version 8.4.0

(In reply to Robert Großkopf from comment #34)

> 
> Now I open the query editor for creating a query, choose the table and the
> timestamp-field will be shown. Executing a query

Yes.


> SELECT * FROM `test`.`Tabelle1`
> and the field has gone.

Yes.

> Executing
> SELECT `Tabelle1`.*, `Tabelle1`.`DirectTime` FROM `test`.`Tabelle1`
> `Tabelle1`
> and the field appears.

Yes.


> 
> Executing 
> SELECT * FROM `test`.`Tabelle1`
> through tools → SQL and the field appears.

Yes.

> 
> This all seems to be a special GUI problem of LO Base.

Yes, it looks like it is.