Bug 128698 - MySQL/MariaDB direct Connection: time-values not written to table
Summary: MySQL/MariaDB direct Connection: time-values not written to table
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.3.3.2 release
Hardware: All All
: medium normal
Assignee: Julien Nabet
URL:
Whiteboard: target:6.5.0 target:6.4.0.1 target:6.3.4
Keywords: bibisectRequest, dataLoss, regression
Depends on:
Blocks:
 
Reported: 2019-11-10 15:00 UTC by Robert Großkopf
Modified: 2019-11-28 07:07 UTC (History)
5 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 Robert Großkopf 2019-11-10 15:00:47 UTC
This one has been reported in https://ask.libreoffice.org/de/question/215884/base-setzt-time-feld-immer-auf-000000/

The buggy behaviour only appears with windows.
Connect to MySQL or MariaDB with direct connection from Windows.
Create a table with a TIME-field.
Try to put values in the tome field through GUI.
The only value, which will appear, is 00:00:00.

Connect to the same database with Linux and direct connection. 
All values will be saved without any problem.

Tested with
Windows 10 Pro and MX Linux Version 19 x64 (VM)
Database: MariaDB 10.4.8 on Windows 10 Pro
LO-Version Windows: 6.3.3.2 (x64)
LO-Version Linux: 6.3.3.2 (x64)
See the link to ask.libreoffice.org/de
Comment 1 Alex Thurgood 2019-11-12 15:06:30 UTC
Reproduced with

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

Not just a Win problem.

I set up table in mysql as follows via the LO GUI:

ID (INT) AUTOINCREMENT NOT NULL
timing TIME NULL


Then I saved the table in LO, and double clicked on it to enter data.

I could enter a first recordset, for example, I entered 17:45:32 successfully.
When I attempted to enter a second recordset, it didn't matter which time I entered, I always get a 00:00:00 display.

If I then connect to the mysql instance via the command line interface, and modify or update the recordset, and refresh the table data in LO, I see the amended data.

If I try to edit an existing record, I once again get a 00:00:00 result which is committed to the database.

==>> regression
Comment 2 Alex Thurgood 2019-11-12 15:08:56 UTC
The fact that 00:00:00 gets committed to the database, which means data is lost when editing an existing entry, indicates that an incorrect string is being passed to the parser to perform the INSERT or UPDATE.
Comment 3 Alex Thurgood 2019-11-12 15:15:40 UTC
Something weird going on.

If I change the name of the time field, by opening up the table for edition, then saving the change, and re-opening the table for data entry, I can now enter any time I like and no longer get the error.
Comment 4 erich 2019-11-13 16:44:52 UTC
Similar Problem as Alex
Using a Form entering 12:45 sends a  Key Error Time 00:00:00 duplicate key

Entering 12:45 in mysql workbench I find 45:00:00 in my dbase Form

Under debian 9
Comment 5 Robert Großkopf 2019-11-13 18:16:22 UTC
(In reply to erich from comment #4)
> Similar Problem as Alex
> Using a Form entering 12:45 sends a  Key Error Time 00:00:00 duplicate key
> 
> Entering 12:45 in mysql workbench I find 45:00:00 in my dbase Form
> 
> Under debian 9

Which version of LO do you use?
I have tried all here to reproduce it with LO 6.3.3.2 rpm-packages. Couldn't reproduce it with my OpenSUSE Linux 64bit system. And you get the error with Debian 9 ....
Comment 6 erich 2019-11-14 09:01:42 UTC
Thanks Robert 

I solved (MY) problem (getting up early this morning)

Using Version 6.1.
testing falling back till 5.7.  but ........

The Problem is the mysql Native driver 1.0.1

Going back to JDBC driver its fine again you even get
an error message complaining about the TIME values
I succeeded to enter with the native diver.:

------------------------------------------------------
Die Dateninhalte konnten nicht aktualisiert werden.

The value '30:00:00' is an invalid TIME value. (I entered 12:30)
JDBC Time objects represent a wall-clock time
and not a duration as MySQL treats them .....
------------------------------------------------------

LO 5.7 refuses to load Mysql-native 1.0.2 under Debian 9.0
('auf diesem System nicht lauffähig)

So back to JDBC and forget about nativeDriver(for some time)

But it would be nice to work with a native driver - wouldn't it

NEVER change drivers !!!! that was the lesson I just learned
Comment 7 Robert Großkopf 2019-11-14 15:03:13 UTC
(In reply to erich from comment #6)
> Thanks Robert 
> 
> I solved (MY) problem (getting up early this morning)
> 
> Using Version 6.1.
> testing falling back till 5.7.  but ........
> 
> The Problem is the mysql Native driver 1.0.1

The native driver is included in LO since LO 6.2. So you don't have to use any other native driver. Seems you had installed an extension.

This bug doesn't appear with 64bit-rpm-systems with LO 6.3.3.2 with this included native driver.

Did you really assigned yourself to fix this bug? If not please reset it to new.
Comment 8 erich 2019-11-14 15:58:31 UTC
(In reply to Robert Großkopf from comment #7)
> (In reply to erich from comment #6)

> any other native driver. Seems you had installed an extension.
> 

Yes, I did install an extension because it looked and worked no nicely (the installation) and NativeDriver sounds sooo  good.

> Did you really assigned yourself to fix this bug? If not please reset it to
> new.

For me it is no longer a bug I can work. Sorry  I forgot to reset the Status

6.4. seems to be close then I'll have a test again with NativeDriver
Comment 9 Xisco Faulí 2019-11-14 16:07:19 UTC
Wait a second... what is going on here?
Could anyone explain how to reproduce this issue and under which circumstances it's happening ? As far as I can read, not happening on win, not happening on 64 bits, not happening with native drivers ? Please, clarify
Comment 10 Julien Nabet 2019-11-14 16:18:47 UTC
On Win10 with master sources updated today, here what I did:
- connect to a Mysql existing DB with Mysql direct
- create test table with:
  - ID integer primary key
  - testtime time

When opening the table and trying to fill a line, I tried several time formats, I always get:
22:59:59

I tried :
- 12:20:20
- 12:20

I only noticed this log on console:
warn:connectivity.mysqlc:47988:33492:connectivity/source/drivers/mysqlc/mysqlc_databasemetadata.cxx:1035: method not implemented


=> In brief, I could reproduce this.
Comment 11 erich 2019-11-14 16:57:39 UTC
(In reply to Xisco Faulí from comment #9)
> Wait a second... what is going on here?
> Could anyone explain how to reproduce this issue and under which
> circumstances it's happening ? As far as I can read, not happening on win,
> not happening on 64 bits, not happening with native drivers ? Please, clarify

O.K. don't want to bore anybody with my situation but so it was when I gave up testing:

Debian 9 64bit  L.O. 5.7. finally (before 6.1).
came across NativeMysql driver and loaded it via Extensionmanager and connected
Testing and existing program found 23:59 or 30:00:00 in my time fields.
This happened using a Form and also using table directly
Even happened using MySQLWorkbench my times where changed from 12:30 into
30:00:00 or mostly 23:59. and even 00:00:00 what brought another error since
the timefield is part of my Primarykey
Somehow I could enter a few records and then
went back to MySQL JDBC driver, loading the form I got an error -see comment#6

Then I deleted all the records entered under NativeDriver and the world
was o.k. again

Thinking is was NativeDriver 1.0.1 causing the problem I looked for another one 1.0.2
trying to install it (1.0.2) via extension manager got an error
' this .oxt application can not be run in this environment'

so for me:  bye NativeDriver, see you in august of another time

MySQL and JDBC are (so it seems to me)  using different time-formats
and JDBC driver handles this better than NativeDriver 1.0.1 ....etc.

Maybe LO 6.4. has it all fixed
Comment 12 Xisco Faulí 2019-11-14 17:00:56 UTC
Hi erich,
Please, do no change the status, even if you can't reproduce it, it doesn't mean it's fixed for everyone
Comment 13 Robert Großkopf 2019-11-14 18:01:41 UTC
This bug has been reported as a Windows-bug.
Seems Alex could reproduce it on Mac (comment 1).

It couldn't be reproduced with Linux LO 6.3.3.2 and the internal native driver, which is included since LO 6.2.0
Comment 14 Julien Nabet 2019-11-21 21:12:49 UTC
I gave a new try and this time have 00:00:00 each time.

Trying to debug this, I only can tell that everything seems ok in "OPreparedStatement::setTime"
(see https://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/mysqlc/mysqlc_preparedstatement.cxx?r=913d34ec#264)
I tried to compare the mechanism with setDate which works, I don't find the root cause.
Comment 15 Julien Nabet 2019-11-21 21:19:48 UTC
I don't even know where I can see the request which will be executed:
161	    int nFail = mysql_stmt_execute(m_pStmt);
(gdb) p m_pStmt
$27 = (MYSQL_STMT *) 0x555558d06190
(gdb) p *m_pStmt
$28 = {mem_root = {free = 0x555558d01c90, used = 0x0, pre_alloc = 0x0, min_malloc = 32, block_size = 2008, block_num = 0, first_block_usage = 0, error_handler = 0x0}, mysql = 0x555557ac7100, stmt_id = 34, 
  flags = 0, state = MYSQL_STMT_PREPARED, fields = 0x0, field_count = 0, param_count = 2, send_types_to_server = 1 '\001', params = 0x555558d01ca8, bind = 0x0, result = {rows = 0, fields = 0, data = 0x0, 
    alloc = {free = 0x0, used = 0x0, pre_alloc = 0x0, min_malloc = 32, block_size = 4056, block_num = 0, first_block_usage = 0, error_handler = 0x0}}, result_cursor = 0x0, bind_result_done = 0 '\000', 
  bind_param_done = 1 '\001', upsert_status = {warning_count = 0, server_status = 0, affected_rows = 18446744073709551615, last_insert_id = 0}, last_errno = 0, last_error = '\000' <repeats 512 times>, 
  sqlstate = "00000", update_max_length = 0 '\000', prefetch_rows = 1, list = {prev = 0x0, next = 0x555558a63920, data = 0x555558d06190}, cursor_exists = 0 '\000', extension = 0x5555586f2410, 
  fetch_row_func = 0x0, execute_count = 0, default_rset_handler = 0x0, m = 0x0}

I'm stuck.
Comment 16 Julien Nabet 2019-11-26 15:50:02 UTC
This patch seems to fix it, at least I don't reproduce the pb with it:
diff --git a/connectivity/source/drivers/mysqlc/mysqlc_preparedstatement.cxx b/connectivity/source/drivers/mysqlc/mysqlc_preparedstatement.cxx
index 902a1d93edb0..7cdd19667f30 100644
--- a/connectivity/source/drivers/mysqlc/mysqlc_preparedstatement.cxx
+++ b/connectivity/source/drivers/mysqlc/mysqlc_preparedstatement.cxx
@@ -267,7 +267,7 @@ void SAL_CALL OPreparedStatement::setTime(sal_Int32 parameter, const Time& aVal)
     checkDisposed(OPreparedStatement::rBHelper.bDisposed);
     checkParameterIndex(parameter);

-    MYSQL_TIME my_time;
+    MYSQL_TIME my_time = {};

     my_time.hour = aVal.Hours;
     my_time.minute = aVal.Minutes;

I'll submit it when I get back home.

If someone wants to submit it before, don't hesitate!
Comment 17 Commit Notification 2019-11-26 21:22:13 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/97ad402d58b52a1f2636905137c21298531c2935

tdf#128698: MySQL/MariaDB direct Connection: time-values not written to table

It will be available in 6.5.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 18 Julien Nabet 2019-11-26 22:40:48 UTC
Backports submitted to review:
for 6.4: https://gerrit.libreoffice.org/#/c/83840/
for 6.3: https://gerrit.libreoffice.org/#/c/83841/
Comment 19 Commit Notification 2019-11-27 04:28:27 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-6-4":

https://git.libreoffice.org/core/commit/ef80d47c38a7adeae3b4122ecfae8fd99773bff7

tdf#128698: MySQL/MariaDB direct Connection: time-values not written to table

It will be available in 6.4.0.1.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 20 Commit Notification 2019-11-27 09:58:31 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-6-3":

https://git.libreoffice.org/core/commit/a973e58a8e3c848cb7d9f397e688e2a9a6ec8f6e

tdf#128698: MySQL/MariaDB direct Connection: time-values not written to table

It will be available in 6.3.5.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 21 Commit Notification 2019-11-27 12:16:32 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-6-3-4":

https://git.libreoffice.org/core/commit/1c9816d9adf27dda1e00db57cf91817574dcc93c

tdf#128698: MySQL/MariaDB direct Connection: time-values not written to table

It will be available in 6.3.4.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 22 Alex Thurgood 2019-11-28 07:06:00 UTC
Verified with

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

Thanks Julien !