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.
@Dan : could you check to see whether you've been affected by bug 119786 ?
Oh, and also that bug 119850 isn't interfering either...
@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 ***
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.
(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...)
(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.
(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.
Dan: would it be possible you create a minimum testcase with embedded hsqldb so it could be easily and quickly reproduceable?
(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?
(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 ?
(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.
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".
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?
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.
Cherry-pick on 6.2 waiting for review here: https://gerrit.libreoffice.org/#/c/69786/
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.
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.
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.
The daily build I used was March 31, 2019
Thank you Dan for your feedback. Let's put this one to VERIFIED then.