Bug 99978 - MariaDB/MySQL - JDBC: Field not shown in table/query, if set ON UPDATE CURRENT_TIMESTAMP
Summary: MariaDB/MySQL - JDBC: Field not shown in table/query, if set ON UPDATE CURREN...
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
3.3.0 release
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Connectivity
  Show dependency treegraph
 
Reported: 2016-05-21 08:21 UTC by Robert Großkopf
Modified: 2018-05-06 06:08 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
Run the SQL-code to create DB, change the user and have a look at the table (3.71 KB, application/zip)
2016-05-21 08:21 UTC, Robert Großkopf
Details
Query should contain 5 fields - only 4 fields will be shown. (22.89 KB, image/png)
2016-05-21 19:32 UTC, Robert Großkopf
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Großkopf 2016-05-21 08:21:19 UTC
Created attachment 125208 [details]
Run the SQL-code to create DB, change the user and have a look at the table

A special problem with JDBC-connection an MariaDB/MySQL-DB. 

Create a table in MySQL/MariaDB.
Set one datetime-field to DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP.
This field won't be shown if opening the table with Base, if connected to the DB with JDBC. Works right with direct connection.
This field won't be shown in a query also.

Added an example database and the SQL-code to create the database and table. There must be changed the user to connect to the database.

This code will be executed:
CREATE TABLE IF NOT EXISTS `table` (
  `ID` mediumint(7) NOT NULL,
  `Name` varchar(100) DEFAULT NULL,
  `DateTime_Default` datetime DEFAULT CURRENT_TIMESTAMP,
  `DateTime_OnUpdateDefault` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `Field5` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Five fields. Four fields will be shown, when opening the table in LO-Base for input values. Five fields will be shown, when opening the table for editing the structure of the table.
Comment 1 Robert Großkopf 2016-05-21 19:32:32 UTC
Created attachment 125217 [details]
Query should contain 5 fields - only 4 fields will be shown.

Fields with "ON UPDATE CURRENT_TIMESTAMP" aren't shown in queries. You could only see the content of this fields, if you create a view of such a query.
Comment 2 Alex Thurgood 2016-05-30 07:58:31 UTC
Hi Robert,
 
I will test, but am hasarding a guess that this is linked to bug 85190
Comment 3 Lionel Elie Mamane 2016-05-30 08:33:26 UTC
I wouldn't be too surprised if the JDBC driver hides that column by default.

Which version of which JDBC driver are you using?

I see in the changelog of the MySQL one:

09-06-12 - Version 5.1.22
  - Fix for Bug#63800, getVersionColumns() does not return timestamp fields; always empty.
    Added support of ON UPDATE CURRENT_TIMESTAMP for TIMESTAMP and DATETIME fields.


I suppose you are using something more recent than that?
Comment 4 Alex Thurgood 2016-05-30 09:25:36 UTC
Confirming on

Version: 5.1.3.2
Build ID: 644e4637d1d8544fd9f56425bd6cec110e49301b
CPU Threads: 2; OS Version: Mac OS X 10.11.4; UI Render: default; 
Locale: fr-FR (fr.UTF-8)

with mysql JDBC connector 5.1.39
Comment 5 Alex Thurgood 2016-05-30 09:38:32 UTC
I see the same issue with

Version: 4.1.4.2
Build ID: 0a0440ccc0227ad9829de5f46be37cfb6edcf72

and JDBC connector 5.1.34
Comment 6 Alex Thurgood 2016-05-30 09:54:13 UTC
I also see the same problem in 

LibreOffice 3.3.0 
OOO330m19 (Build:6)
tag libreoffice-3.3.0.4

with JDBC connector 5.1.34
Comment 7 Lionel Elie Mamane 2016-05-30 10:28:37 UTC
I'd appreciate if someone could check with another JDBC-using application, such as e.g. sqlline . It would tell us whether the column is hidden by the JDBC driver or if LibreOffice "loses" it.
Comment 8 Alex Thurgood 2017-05-05 07:51:17 UTC
The SQL DDL  typed in from the mysql command line fails to create a table in my installed version of mysql (5.5.25a) on OSX:

`DateTime_Default` datetime DEFAULT CURRENT_TIMESTAMP,
`DateTime_OnUpdateDefault` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
Comment 9 Alex Thurgood 2017-05-05 08:15:10 UTC
OK, so I'm using a 5.7 client to connect to a 5.5 server and of course strict mode is enabled by default in 5.7 client sessions, which excludes zero date values, hences the failure of the create setting - sigh...
Comment 10 QA Administrators 2018-05-06 02:30:10 UTC Comment hidden (obsolete)
Comment 11 Robert Großkopf 2018-05-06 06:08:44 UTC
Tested this one again with
Version: 6.0.3.2
Build-ID: 8f48d515416608e3a835360314dac7e47fd0b821
CPU-Threads: 4; BS: Linux 4.4; UI-Render: Standard; VCL: kde4; 
Gebietsschema: de-DE (de_DE.UTF-8); Calc: group

and MariaDB Connector/J 2.2.3 Stable 2018-03-14

All fields were shown, works as expected.

I will set this one to WORKSFORME.