Bug 119135 - Definition of field type as TIMESTAMP via UI for mysql table is systematically modified to DATETIME.
Summary: Definition of field type as TIMESTAMP via UI for mysql table is systematicall...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Connectivity
  Show dependency treegraph
 
Reported: 2018-08-07 01:28 UTC by Hugo de la O
Modified: 2020-03-31 18:49 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Hugo de la O 2018-08-07 01:28:27 UTC
Description:
Every time I modify a table, using BASE over Mysql, the data type of a TIMESTAMP field is modified to DATETIME. No matter how many times I have tried, when I modify the table it changes back to DATETIME.

Steps to Reproduce:
1. Use as data engine MYSQL 5.7.23
2. Open BASE
3. Create a table configuring a given field as TIMESTAMP.
4. Close the table. 
5. Open to edit the same table.
6. Modify it and save it.
7. The TIMESTAMP has changed to DATETIME

Actual Results:
1. Use as data engine MYSQL 5.7.23
2. Open BASE and create a database
3. Create a table configuring a given field as TIMESTAMP.
4. Close the table. 
5. Open to edit the same table.
6. Modify it and save it.
7. The TIMESTAMP has changed to DATETIME

Expected Results:
The field changes from TIMESTAMP to DATETIME and TIMESTAMP doesn't works.


Reproducible: Always


User Profile Reset: Yes


OpenGL enabled: Yes

Additional Info:
Remain the configuration of TIMESTAMP unless I want to modify it.
Comment 1 Alex Thurgood 2018-08-07 07:41:12 UTC
Confirming with

Version: 6.0.5.2
Build ID: 54c8cbb85f300ac59db32fe8a675ff7683cd5a16
Threads CPU : 8; OS : Mac OS X 10.13.6; UI Render : par défaut; 
Locale : fr-FR (fr_FR.UTF-8); Calc: group
Comment 2 Alex Thurgood 2018-08-07 07:42:22 UTC
I'm pretty sure that this is a regression, but will need to check.
Comment 3 Alex Thurgood 2018-08-07 07:43:35 UTC
My tests were made with the native mysql connector extension (MacOS).
Comment 4 Alex Thurgood 2018-08-07 07:47:11 UTC
Reproduced also with
Version: 5.1.6.2
Build ID: 07ac168c60a517dba0f0d7bc7540f5afa45f0909
Threads CPU : 8; Version de l'OS :Mac OS X 10.13.6; UI Render : par défaut; 
Locale : fr-FR (fr_FR.UTF-8); Calc: group
Comment 5 Alex Thurgood 2018-08-07 07:51:34 UTC
Hmm, also reproduced with

Version: 4.1.6.2
Build ID: 40ff705089295be5be0aae9b15123f687c05b0a

maybe this bug has been around since the beginning of LO
Comment 6 Alex Thurgood 2018-08-07 07:56:17 UTC
Also reproduced with

Version 3.6.7.2 (Build ID: e183d5b)
Comment 7 Alex Thurgood 2018-08-07 07:57:18 UTC
OK, so am leaning more towards the "we've always had this problem" kind of bug...
Comment 8 Alex Thurgood 2018-08-07 08:01:24 UTC
Reproduced with OOo321m18 (build9502)

Setting as InheritedfromOOo
Comment 9 Julien Nabet 2018-08-07 08:08:09 UTC
Tamás: noticing https://gerrit.libreoffice.org/#/c/55960/, thought you might be interested in this one.
Comment 10 Alex Thurgood 2018-08-07 08:10:18 UTC
Reproduced also with 

mysql-connector-java-5.1.36

so it isn't just limited to the native mysql connector.
Comment 11 Robert Großkopf 2018-08-07 08:23:10 UTC
Couldn't test it with native connector - isn't working here.

Tested with MariaDB and mariadb-java-client-2.2.3.jar. Created a new filed for a TIMESTAMP. Saved the table, closed the database-file, reopened the database-file: The TIMESTAMP hasn't been gone.

So I couldn't confirm this behavior for OpenSUSE 15, 64bit rpm Linux, MaraiaDB and Javaconnector for this MariaDB.
Comment 12 Alex Thurgood 2018-08-07 08:28:03 UTC
Hmm, inspection of the table definition created on the mysql server reveals that the timestamp field is created with the following attributes:

TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

but the UI doesn't reflect this.
Comment 13 Alex Thurgood 2018-08-07 08:52:37 UTC
This has other side-effects with the jdbc-connector that cause the timestamp field to not even be displayed in the table data edit mode (double-click on table), even with the zeroDateTimeBehaviour=convertToNull flag added to the connection string.
Comment 14 QA Administrators 2019-08-19 07:00:12 UTC Comment hidden (obsolete)
Comment 15 Julien Nabet 2020-03-31 18:26:43 UTC
On pc Debian x86-64 with master sources updated today + native mysqlc + 5.5.5-10.3.22-MariaDB-1 I could reproduce this.
I wonder if it could be due to the fact that in JDBC, there's only TIMESTAMP (see https://docs.oracle.com/cd/A97337_01/ias102_otn/buslog.102/bc4j/bc_abcdatatypes.htm) so not present in https://opengrok.libreoffice.org/xref/core/offapi/com/sun/star/sdbc/DataType.idl?r=19c4d8d2
Perhaps should we use "OTHER"?
79     /** indicates that the SQL type is database-specific and
80              gets mapped to an object that can be accessed via
81              the method com::sun::star::sdbc::XRow::getObject().
82      */
83     const long OTHER        = 1111;


DATETIME and TIMESTAMP are quite similar, according to Mysql website:
(https://dev.mysql.com/doc/refman/8.0/en/datetime.html)
 The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD hh:mm:ss' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
Comment 16 Julien Nabet 2020-03-31 18:49:50 UTC
I began to give a try but stuck since I get "OBJECT" in the end.

Then when git grep the code, I noticed this:
offapi/type_reference/offapi.idl:10204:     ::com::sun::star::util::DateTime getTimestamp() raises (::com::sun::star::sdbc::SQLException);

So the 2 notions timestamp and datetime seem quite mixed.
=> uncc myself.