Download it now!
Bug 123999 - time change causes the return of "parameter index out of range... using mysql and J/connector
Summary: time change causes the return of "parameter index out of range... using mysql...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.2.1.2 release
Hardware: All Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:6.3.0 target:6.2.3
Keywords:
Depends on:
Blocks:
 
Reported: 2019-03-11 13:54 UTC by Dan Lewis
Modified: 2019-08-25 07:21 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 Dan Lewis 2019-03-11 13:54:45 UTC
Description:
My database backend is mysql 8.0.15 along with same versions of J/connector and workbook. On the day of a time change, I could not connect to MySQL (a known bug) In the main database window, I opened the Connection dialog (Database > Properties > Advanced ). In the Advanced Properties tab, I changed the name of the database: from "my_database" to my_database?serverTimezone=UTC" as per the bug instructions. I tested the connection: success. I closed the dialog. When I opened a form with a subform, I can not get the data to load. Error message begins with Parameter index out of range. I then unzipped the *.odb file and looked in the context file. I looked for 'db parameter-name-substitution="false"', but it did not exist in the file.

Actual Results:
1) Use a database which contains a form with a main and sub forms using MySQL as the backend and Base as the front end.
2) The time must have changed. (In my case, EST became DST at 2AM on 3/10/2019.)
3) Open the database and connect to MySQL.
4) Open the form containing the subform.

Expected Results:
The error message immediately appeared: The data could not be loaded. Parameter index out of range (1> number of parameters, which is 0)


Reproducible: Always


User Profile Reset: No



Additional Info:
It should have loaded the data into the fields of the form.
Comment 1 Alex Thurgood 2019-03-11 14:23:05 UTC
@Dan : could you check to see whether you've been affected by bug 119786 ?
Comment 2 Alex Thurgood 2019-03-11 14:28:40 UTC
Oh, and also that bug 119850 isn't interfering either...
Comment 3 Alex Thurgood 2019-03-11 15:04:03 UTC
@Dan : actually, looks like this is a duplicate of bug 118112, setting as such.

*** This bug has been marked as a duplicate of bug 118112 ***
Comment 4 Mike Kaganski 2019-03-11 15:15:28 UTC
I believe that's related to https://git.libreoffice.org/core/+/d6fce54c82868b82bd6fa190db6047d69bbb3ecf and https://git.libreoffice.org/core/+/3208fcb3a36d75d6290d9c548430682f153b09db

which changed the default for ParameterNameSubstitution to true.
Comment 5 Dan Lewis 2019-03-11 16:03:32 UTC
(In reply to Alex Thurgood from comment #1)
> @Dan : could you check to see whether you've been affected by bug 119786 ?

Yes, I believe that is part of the problem which can be solved. But, this does not solve the problem of the error message concerning "parameter index out of range...)
Comment 6 Dan Lewis 2019-03-11 16:25:23 UTC
(In reply to Alex Thurgood from comment #3)
> @Dan : actually, looks like this is a duplicate of bug 118112, setting as
> such.
> 
> *** This bug has been marked as a duplicate of bug 118112 ***

Sorry, but this is NOT a duplicate of bug 118112. I had a problem with bug 118112 more than a month ago. That is when I removed: db parameter-name-substitution="false. I have even edited context.xml when this problem occurred "yesterday. db parameter" only exists in one place in this file. However, "db parameter name" does not exist. So that is not the problem.
Comment 7 Dan Lewis 2019-03-11 16:32:46 UTC
(In reply to Mike Kaganski from comment #4)
> I believe that's related to
> https://git.libreoffice.org/core/+/d6fce54c82868b82bd6fa190db6047d69bbb3ecf
> and
> https://git.libreoffice.org/core/+/3208fcb3a36d75d6290d9c548430682f153b09db
> 
> which changed the default for ParameterNameSubstitution to true.

I have created a new form to access the same data using 6.2.1.2. Then I edited the context.xml file replacing
   parameter-name-substitution="false" to
   parameter-name-substitution="true".
When opening the form, I get the error message again about the parameter index being out of range.
Comment 8 Julien Nabet 2019-03-11 18:33:42 UTC
Dan: would it be possible you create a minimum testcase with embedded hsqldb so it could be easily and quickly reproduceable?
Comment 9 Dan Lewis 2019-03-15 14:03:51 UTC
(In reply to Dan Lewis from comment #6)
> (In reply to Alex Thurgood from comment #3)
> > @Dan : actually, looks like this is a duplicate of bug 118112, setting as
> > such.
> > 
> > *** This bug has been marked as a duplicate of bug 118112 ***
> 
> Sorry, but this is NOT a duplicate of bug 118112. I had a problem with bug
> 118112 more than a month ago. That is when I removed: db
> parameter-name-substitution="false. I have even edited context.xml when this
> problem occurred "yesterday. db parameter" only exists in one place in this
> file. However, "db parameter name" does not exist. So that is not the
> problem.

More investigation on my part: When I finally edited the context.xml file, I again found 'db: parameter-name-sustiution="false" '. Deleting it solved the problem this time.

However, it had to have been added again sometime between Saturday afternoon when I entered data into a form with a subform and Sunday morning when the problem resurfaced. The only thing I did in that time was to add the time zone to the name of my database because of an bug in the MySQL J/connector (8.0.15) when the time went from EST to DST.

I also have discovered that creating a new form results in   db:parameter-name-substituion="false" being added to the context.xml file if it is not present. So is this really a duplication of bug #118112?
Comment 10 Xisco Faulí 2019-03-21 09:44:39 UTC
(In reply to Julien Nabet from comment #8)
> Dan: would it be possible you create a minimum testcase with embedded hsqldb
> so it could be easily and quickly reproduceable?

@Dan, would you mind providing a minimal testcase ?
Comment 11 Dan Lewis 2019-03-22 15:18:51 UTC
(In reply to Xisco Faulí from comment #10)
> (In reply to Julien Nabet from comment #8)
> > Dan: would it be possible you create a minimum testcase with embedded hsqldb
> > so it could be easily and quickly reproduceable?
> 
> @Dan, would you mind providing a minimal testcase ?

The quick answer is no; that is not possible at all. I am using Base as the front end for MySQL. This does not an embedded database. All of the table structure and data are contained on the MySQL server, not within the Base document file. I use MySQL server 8.0.15 and MySQL J/connector 8.0.15 to connect to the MySQL server.
Comment 12 Dan Lewis 2019-03-22 17:38:17 UTC
I have finally discovered what happened; I just do not know why. This is the steps I used to create and temporarily solve it.
1) Use Base to connect to the MySQL server using the J/connector (MySQL). My versions of these two are 8.0.15.
2) Open a form with a subform and add some data. No problem yet.
3) Display the lists of forms contained in the database.
4) Right click below this list.
5) Select Database > Connection Type
6) In the Data Source Properties window, click Next.
7) In the Database Properties window, modify the database name.
   In my case, it was Budget?serverTimezone=EST. I changed this to 
   Budget?serverTimezone=EDT.
8) When testing the connection, the error message stated EDT is not a recognized time zone, so I changed it back. This time testing the connection worked.
9) Click finished and save the database document file.
10) Open the form with the subform. Immediately, the error message appears about the parameter being out of range.
11) Close the database file.
12 Edit the context.xml file and remove db;parameter-name-substitution="false".
13) Connect the database to MySQl again. When the form with the subform is opened, it does so nomally. Data can be added, deleted, or modified.

Conclusion: When changing the connection type including the time zone, you are also causing Base to again add db:parameter-name-substitution="false".
Comment 13 Julien Nabet 2019-03-25 22:42:58 UTC
On pc Debian x86-64 with master sources updated today, I could reproduce this with a more simple process:
- create brand new odb  file
- choose Mysql then JDBC
- then click by default option + no need to register database

not sure it's useful but I was trying to reproduce the last Dan's comment from a brand new file
- Select Database > Connection Type
- In the Data Source Properties window, click Next.
- In the Database Properties window, modify the database name.
  add "serverTimezone=EST" without quotes and save

- Click finished and save the database document file
- close LO

Indeed, there's db:parameter-name-substitution="false" added
With this patch, I don't reproduce this:
diff --git a/dbaccess/source/core/dataaccess/ModelImpl.cxx b/dbaccess/source/core/dataaccess/ModelImpl.cxx
index 00463902b340..bcf15cbbbf22 100644
--- a/dbaccess/source/core/dataaccess/ModelImpl.cxx
+++ b/dbaccess/source/core/dataaccess/ModelImpl.cxx
@@ -985,7 +985,7 @@ const AsciiPropertyValue* ODatabaseModelImpl::getDefaultDataSourceSettings()
         AsciiPropertyValue( "LocalSocket",                makeAny( OUString() ) ),
         AsciiPropertyValue( "NamedPipe",                  makeAny( OUString() ) ),
         // misc known driver settings
-        AsciiPropertyValue( "ParameterNameSubstitution",  makeAny( false ) ),
+        AsciiPropertyValue( "ParameterNameSubstitution",  makeAny( true ) ),
         AsciiPropertyValue( "AddIndexAppendix",           makeAny( true ) ),
         AsciiPropertyValue( "IgnoreDriverPrivileges",     makeAny( true ) ),
         AsciiPropertyValue( "ImplicitCatalogRestriction", ::cppu::UnoType< OUString >::get() ),

Lionel: any thoughts about this patch? Does it seem the right lead or would you prefer I provide a bt from this location to understand the full mechanism?
Comment 14 Commit Notification 2019-03-27 07:42:05 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/3149ffea4c7285ceebe990640cbfb74d2e10e7e0%5E%21

tdf#123999: ParameterNameSubstitution in mysqljdbc

It will be available in 6.3.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 15 Julien Nabet 2019-03-27 07:42:52 UTC
Cherry-pick on 6.2 waiting for review here:
https://gerrit.libreoffice.org/#/c/69786/
Comment 16 Christian Lohmaier 2019-03-27 11:58:27 UTC
adding comments lost during infra-outage:
https://lists.freedesktop.org/archives/libreoffice-bugs/2019-March/176623.html
--- Comment #14 from Lionel Elie Mamane ---
If I understand well, all this is with the "MySQL (JDBC)" connection type, not
the JDBC connection type.

(In reply to Julien Nabet from comment #13)
> Lionel: any thoughts about this patch? Does it seem the right lead or would
> you prefer I provide a bt from this location to understand the full
> mechanism?

This looks like a LibreOffice-wide default. The right fix should probably be in
the MySQL-JDBC specific defaults, in
connectivity/registry/mysql_jdbc/org/openoffice/Office/DataAccess/Drivers.xcu

In the "<node oor:name="sdbc:mysql:jdbc:*" oor:op="replace">" Properties part,
it should give the correct value for ParameterNameSubstitution, that is "true".
It seems it doesn't.

As usual, this will solve it only for new files...

https://lists.freedesktop.org/archives/libreoffice-bugs/2019-March/176624.html
--- Comment #15 from Julien Nabet ---
(In reply to Lionel Elie Mamane from comment #14)
> ...
> This looks like a LibreOffice-wide default. The right fix should probably be
> in the MySQL-JDBC specific defaults, in
> connectivity/registry/mysql_jdbc/org/openoffice/Office/DataAccess/Drivers.xcu
> ...
I thought about this after having commented but I just wonder in which case
this option should be put to false.

Anyway, I'll be able to propose a patch after my day time job.

https://lists.freedesktop.org/archives/libreoffice-bugs/2019-March/176625.html
--- Comment #16 from Lionel Elie Mamane ---
(In reply to Julien Nabet from comment #15)

> I thought about this after having commented but I just wonder in which case
> this option should be put to false.

Databases (or drivers) that support the :named_parameter syntax, as opposed to
only the unnamed parameter syntax with "?".

https://lists.freedesktop.org/archives/libreoffice-bugs/2019-March/176626.html
--- Comment #17 from Mike Kaganski ---
(In reply to Lionel Elie Mamane from comment #14)
> This looks like a LibreOffice-wide default. The right fix should probably be
> in the MySQL-JDBC specific defaults, in
> connectivity/registry/mysql_jdbc/org/openoffice/Office/DataAccess/Drivers.xcu
> 
> In the "<node oor:name="sdbc:mysql:jdbc:*" oor:op="replace">" Properties
> part, it should give the correct value for ParameterNameSubstitution, that
> is "true". It seems it doesn't.
> 
> As usual, this will solve it only for new files...

Please note that we have some issues, so that different defaults are used at
different levels of execution, which makes strange results when a
driver-specific default does not correspond to global defaults, like in bug
121092. I'm afraid bug 124022 should be addressed first.

https://lists.freedesktop.org/archives/libreoffice-bugs/2019-March/176628.html
--- Comment #18 from Julien Nabet ---
(In reply to Mike Kaganski from comment #17)
> ...
> Please note that we have some issues, so that different defaults are used at
> different levels of execution, which makes strange results when a
> driver-specific default does not correspond to global defaults, like in bug
> 121092. I'm afraid bug 124022 should be addressed first.

Let's see the coming patch on
connectivity/registry/mysql_jdbc/org/openoffice/Office/DataAccess/Drivers.xcu
like quick bandaid fix.

tdf#124022 would be useful indeed but seems more a redesign/refactoring which
needs someone wanting to spend some time on internal mechanism.
Comment 17 Commit Notification 2019-03-27 13:14:53 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-6-2":

https://git.libreoffice.org/core/+/15f4f8b7bfd666d69381696fe288da24302d2ecd%5E%21

tdf#123999: ParameterNameSubstitution in mysqljdbc

It will be available in 6.2.3.

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 Dan Lewis 2019-04-01 12:37:00 UTC
This morning, I tested LibreOfficeDev_6.2.4.0.0_Linux_x86-64_deb for this bug. After installing this in parallel, I copied the config files for LO 6.2.2.2 to 6.2.4.0.0 config folder so that it would have the same settings. Opening the database file I use to connect to the MySQL 8.0.15 server, I changed the time zone setting from EST to UTC. Both of these are recognizable time zone. Then I opened a form with subform. No "parameter out of range" error message. The patch works! I also edited the content.xml file for this database file searching for "db:parameter-name-substitution". There was not one. Thanks to everyone who worked on this bug!! Someone else can close this bug as resolved if you think it should be.
Comment 19 Dan Lewis 2019-04-01 12:38:42 UTC
The daily build I used was March 31, 2019
Comment 20 Julien Nabet 2019-04-01 12:51:24 UTC
Thank you Dan for your feedback.
Let's put this one to VERIFIED then.