Bug 118112 - Parameter index out of range (1 > number of parameters, which is 0). (see comment 39 for manual fix)
Summary: Parameter index out of range (1 > number of parameters, which is 0). (see com...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.1.0.0.beta1+
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:6.2.0 target:6.1.0.1 target:6....
Keywords: dataLoss
: 120091 121997 124427 128554 132377 (view as bug list)
Depends on:
Blocks:
 
Reported: 2018-06-11 14:10 UTC by Alex Kempshall
Modified: 2020-05-21 06:21 UTC (History)
14 users (show)

See Also:
Crash report or crash signature:


Attachments
git bisect log (2.56 KB, text/x-log)
2018-06-11 14:16 UTC, Alex Kempshall
Details
Base application and mysql data (23.78 KB, application/gzip)
2018-06-11 14:52 UTC, Alex Kempshall
Details
Sql to create test database (1.77 KB, application/sql)
2019-07-01 14:13 UTC, tim
Details
odb file returning error when form opened (9.68 KB, application/vnd.oasis.opendocument.database)
2019-07-01 14:14 UTC, tim
Details
odb file with macro having been run - no error (10.86 KB, application/vnd.oasis.opendocument.database)
2019-07-01 14:15 UTC, tim
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Alex Kempshall 2018-06-11 14:10:12 UTC
When opening a form, in LibreOffice Base, I get the following error box displayed

The data content could not be loaded.
SQL Status: S1009

Parameter index out of range (1 > number of parameters, which is 0).


The form has worked on all versions of LibreOffice up to and including 6.0.4

I'm using

Slackware 14.2
Mariadb 10.0.35
mysql-connector-java-5.1.39
Comment 1 Alex Kempshall 2018-06-11 14:16:00 UTC
Created attachment 142656 [details]
git bisect log

Attached is a git bisect log. The first bad commit is described as -
 a29d97e6ddab8ec002ba9827bd5fc874117712e0 is the first bad commit
commit a29d97e6ddab8ec002ba9827bd5fc874117712e0
Author: Lionel Elie Mamane <lionel@mamane.lu>
Date:   Tue Jan 2 22:49:31 2018 +0100

    tdf#104986 move named parameters substitution into generic layer
    
    Previously, drivers were responsible for making the substitution themselves.
    In practice they all (Firebird, ODBC and JDBC) used the LibreOffice SQL
    parser to parse the SQL statement and do the substitution.
    
    This had a few negative consequences:
     * The substitition was applied to _all_ SQL commands, including
       queries having the "execute SQL directly" bit set. Which means
       that the SQL was _not_ sent to the DBMS exactly as typed by
       the user. Even if there was no substitution to be made, since
       the SQL command was always round-tripped through the parser,
       thus "normalising" it (which is what led to tdf#104986).
     * "execute SQL directly" queries "magically" behaved slightly
       differently depending on whether the LibreOffice SQL parser
       succeeded in parsing them or not.
    
    Change-Id: Ieedc643f33467435a139e2565a01e6f398af041f
    Reviewed-on: https://gerrit.libreoffice.org/47283
    Tested-by: Jenkins <ci@libreoffice.org>
    Reviewed-by: Lionel Elie Mamane <lionel@mamane.lu>

:040000 040000 21498e671d4de80e991c7b51dec9934941e385a2 9ad610a6a942238862876f30a23e287fdbbda9b4 M      connectivity
:040000 040000 e9abefeaa3966bf120917e674b181db122593c34 7e986739f2e48cc68966a085f241f46fd2ec80ae M      include
Comment 2 Xisco Faulí 2018-06-11 14:20:03 UTC
Hello Alex,
Could you please attach the document you're using to reproduce this problem ?
Comment 3 Alex Kempshall 2018-06-11 14:52:12 UTC
Created attachment 142660 [details]
Base application and mysql data

tar gz file with database document and sql data for mariadb/mysql

Steps to recreate problem in LibreOffice 6.1

1.
untar the attached btuc.mysql.tar.gz

2.
Create the btuc database using the attached mysqldump file

3.
Open attached btuc_mysql.odb file with libreoffice

4.
Register btuc database in LibreOffice

5.
Open the form FMBTUC_DATA_ENTRY

6.
Error Box is displayed.
Comment 4 Alex Kempshall 2018-06-11 14:55:06 UTC
I've created an equivalent application using the embedded hsqldb database. The application behaves as expected - no error box.
Comment 5 Alex Kempshall 2018-06-13 15:46:40 UTC
The commit that caused this regression was to fix bug tdf#104986.

In that bug report there's a discussion

(In reply to Lionel Elie Mamane from comment #18)
> There is probably a much more robust way to do it... One only has to grossly
> lex the SQL, just keeping track of whether on is inside a '-delimited string
> or a "-delimited identifier. And outside of that, replace any word that
> starts with ":" with "?".


In GDB of tdf#118112 I see this 

Thread 7 "UNO AffineBridg" hit Breakpoint 1, connectivity::java_sql_Connection::prepareStatement (this=0x35f5350, sql="SELECT * FROM `btuc`.`tblBTUC_details` WHERE ( `btuc`.`tblBTUC_details`.`Contact_ID` = :link_from_Contact_ID )")
    at /home/alex/master/connectivity/source/drivers/jdbc/JConnection.cxx:474
474         java_sql_PreparedStatement* pStatement = new java_sql_PreparedStatement( t.pEnv, *this, sql );


Does this mean that  :link_from_Contact_ID  is being changed to ? and causing the bug that I'm seeing.
Comment 6 Lionel Elie Mamane 2018-06-13 16:16:05 UTC
Either:
1) The data source of the form is configured as "execute directly: yes" / "analyse SQL command: no", and then, well, it was kinda based on a bug that it worked before.
Or:
2) Looks like on the contrary :link_from_Contact_ID is _not_ being replaced by "?" anymore, and that's your problem. Assuming my guess is correct, then the reason is probably that ParameterNameSubstitution is not set to "true", and the solution is to set ParameterNameSubstitution to "true". It needs to be set in existing files manually / by macro (sorry....), and to set it in new files, connectivity/registry/mysql/org/openoffice/Office/DataAccess/Drivers.xcu needs the same change as

https://gerrit.libreoffice.org/#/c/47283/5/connectivity/registry/firebird/org/openoffice/Office/DataAccess/Drivers.xcu

Julien, you want to take this?
Comment 7 Julien Nabet 2018-06-13 20:20:48 UTC
I submitted this patch to gerrit: https://gerrit.libreoffice.org/#/c/55771/1
but it doesn't seem to work.
See comment in gerrit.

Anyway, I could reproduce this, let's put this to NEW.
Comment 8 Lionel Elie Mamane 2018-06-14 05:27:26 UTC
(In reply to Lionel Elie Mamane from comment #6)

> the reason is probably that ParameterNameSubstitution is not set to "true",
> and the solution is to set ParameterNameSubstitution to "true". It needs to
> be set in existing files manually / by macro (sorry....),

Refer to
https://bugs.documentfoundation.org/show_bug.cgi?id=117053#c11
https://bugs.documentfoundation.org/show_bug.cgi?id=117053#c15
for how to do that by editing the files in the odb manually or by macro, respectively.
Comment 9 Commit Notification 2018-06-14 07:49:23 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=c179e8a7ea273a48713192c3ba7dfea313149cd8

tdf#118112: Use ParameterNameSubstitution for Mysql

It will be available in 6.2.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 10 Alex Kempshall 2018-06-14 09:49:51 UTC
Just tested this.

Can see the patch in my git log.


> commit c179e8a7ea273a48713192c3ba7dfea313149cd8
> Author: Julien Nabet <serval2412@yahoo.fr>
> Date:   Wed Jun 13 22:16:44 2018 +0200
> 
>     tdf#118112: Use ParameterNameSubstitution for Mysql
>     
>     Change-Id: Ic48aee378d0a89466c49de85baa96a3bea127d2f
>     Reviewed-on: https://gerrit.libreoffice.org/55771
>     Tested-by: Jenkins <ci@libreoffice.org>
>     Reviewed-by: Julien Nabet <serval2412@yahoo.fr>

Have recompiled.

Still have the same problem. Is there anything else I'm supposed to have done?
Comment 11 Lionel Elie Mamane 2018-06-14 09:51:50 UTC
(In reply to Alex Kempshall from comment #10)
> Just tested this.
>
> Have recompiled.
> 
> Still have the same problem. Is there anything else I'm supposed to have
> done?

See comment 8
Comment 12 Alex Kempshall 2018-06-14 10:18:54 UTC
Thanks for pointing me in the right direction.

Now works as expected.
Comment 13 Commit Notification 2018-06-14 16:26:12 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-6-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=90d77c7d18050437d47ba967e4c4ad7e523ced0e&h=libreoffice-6-1

tdf#118112: Use ParameterNameSubstitution for Mysql

It will be available in 6.1.0.1.

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

Affected users are encouraged to test the fix and report feedback.
Comment 14 Julien Nabet 2018-06-14 18:21:48 UTC
(In reply to Lionel Elie Mamane from comment #8)
> ...
> Refer to
> https://bugs.documentfoundation.org/show_bug.cgi?id=117053#c11
> https://bugs.documentfoundation.org/show_bug.cgi?id=117053#c15
> for how to do that by editing the files in the odb manually or by macro,
> respectively.
Just for information, I tested both methods (even if first one has already been confirmed), I confirm it works.
Comment 15 Julien Nabet 2018-06-15 08:20:44 UTC
Lionel: about your comment https://gerrit.libreoffice.org/#/c/55771/, "force ParameterNameSubstitution to true", I checked YDriver.cxx and there's already the property at true (see https://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/mysql/YDriver.cxx#211)

I didn't test but it seems that default value of "ParameterNameSubstitution" is in 
https://opengrok.libreoffice.org/xref/core/dbaccess/source/core/dataaccess/ModelImpl.cxx#992
Thought it might help for tdf#117589 put in see also if the by default value is true.
Comment 16 Lionel Elie Mamane 2018-06-15 11:36:34 UTC
(In reply to Julien Nabet from comment #15)
> Lionel: about your comment https://gerrit.libreoffice.org/#/c/55771/, "force
> ParameterNameSubstitution to true", I checked YDriver.cxx and there's
> already the property at true (see
> https://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/mysql/
> YDriver.cxx#211)

Hmm... Maybe the bug then is that if ParamterNameSubstitution is _already_ in info, it is added a second time, and only the first value takes effect. In that case, IMHO it needs to be *changed* in info, not appended to it. Can you check if my guess is correct?

> I didn't test but it seems that default value of "ParameterNameSubstitution"
> is in 
> https://opengrok.libreoffice.org/xref/core/dbaccess/source/core/dataaccess/
> ModelImpl.cxx#992
> Thought it might help for tdf#117589 put in see also if the by default value
> is true.

No, that's a LibreOffice-wide default for all drivers, I wouldn't touch it for the needs of a few drivers.
Comment 17 Commit Notification 2018-06-18 21:20:13 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-6-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=4de33eb2b5a802e8eda36c5806913efeead68441&h=libreoffice-6-0

tdf#118112: Use ParameterNameSubstitution for Mysql

It will be available in 6.0.6.

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

Affected users are encouraged to test the fix and report feedback.
Comment 18 Alex Kempshall 2018-06-20 06:51:03 UTC
Is my understanding correct in that this patch -

1.
will stop the problem in new applications;

2.
applications built before this patch will have to be fixed by manually applying one of the work arounds.

If the above is true can we make a comment in the change log and/or release notes to that effect and also provide a macro in LibreOffice Macros & Dialogs that can be manually run to fix the problem in old applications.
Comment 19 Xisco Faulí 2018-07-23 12:00:11 UTC
A polite ping to Julien Nabet:
Is this bug fixed? if so, could you please close it as RESOLVED FIXED ? Otherwise, Could you please explain what's missing?
Thanks
Comment 20 Julien Nabet 2018-07-23 12:25:32 UTC
Xisco: I must recognize I don't know.
We may say it's fixed because the brand new files won't have the pb + we can fix this with a macro.
We may say it's not fixed yet (or partly fixed) because we'd like "force ParameterNameSubstitution to true" and I don't know how to do it.
Comment 21 Alex Kempshall 2018-07-23 12:28:56 UTC
I manually applied one of the work arounds and it fixed the problem for me.

I've not built an application from scratch to see whether the problem never occurs.
Comment 22 Robert Großkopf 2018-08-20 05:58:43 UTC
Just read this in the users-mailinglist:
OLD SETTING:<db:driver-settings db:system-driver-settings="" db:base-dn=""
db:parameter-name-substitution="false"/>
NEW SETTING:<db:driver-settings db:system-driver-settings="" db:base-dn=""/>

You have to change the content of content.xml inside the *.odb-file for *.odb-files, which are created before 6.1. So we couldn't say the bug has been solved.

http://document-foundation-mail-archive.969070.n3.nabble.com/LibreOffice-Base-now-does-not-apply-named-parameter-substitution-after-upgrade-from-LO-6-0-to-LO-6-1s-td4246734.html
Comment 23 david 2018-08-26 08:49:59 UTC
While waiting for a better way to convert the .odb file, can someone please post the steps to extract and edit the content.xml file manually?  Thanks!
Comment 24 Robert Großkopf 2018-08-26 17:17:50 UTC
(In reply to david from comment #23)
> While waiting for a better way to convert the .odb file, can someone please
> post the steps to extract and edit the content.xml file manually?  Thanks!

Try the following with a copy of your *.odb-file:

Open the *.odb-file with a packing-program ("Zip", here on Linux it's called "Ark").
content.xml should be moved outside the *.odb-file (Unzip only this file).
Take a simple text-editor (I use Kate in Linux ...) and open content.xml.
Search for 
db:parameter-name-substitution="false"
and delete this.
Save the new content.xml
Delete old content.xml in the *.odb-file with the packing-program.
Move the new content.xml into the *.odb-file.

Hope this works for you.

Regards

Robert
Comment 25 david 2018-08-27 13:47:35 UTC
Thanks Robert!  Its working now.  : )
Comment 26 tim 2018-09-14 09:06:15 UTC
I reported this against #119569, which may have been the wrong bug, so I'm repeating here.

I am getting much the same error on Windows version 6.1.0.3 and 

ubuntu Version: 6.1.1.2
Build ID: 1:6.1.1~rc2-0ubuntu0.18.04.1~lo3
CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: gtk2; 
Locale: en-GB (en_GB.UTF-8); Calc: group threaded

ubuntu version 6.0.5.2 is OK on my system.

I'm using base with mysql/mariadb sql.  When I open a form with a subform I get (at least) one of these for each subform:

"The data content could not be loaded.

Parameter index out of range (1 > number of parameters, which is 0)."

And the detail: "SQL Status: S1009

Parameter index out of range (1 > number of parameters, which is 0)."

I have checked that it is the queries used by the subforms that are giving the errors (not the subform code itself), but opening the query and supplying parameters.  On 6.0.5.2 it works.

This is critical for me.  I use Base a lot, and all my applications makes extensive use of subforms.  I have had to revert back to 6.0.5.2 on my main system.
Comment 27 tim 2018-09-14 09:33:44 UTC
Having read through more of the comments above I can confirm that deleting  

db:parameter-name-substitution="false"

does work for me, so it's no longer 'critical' for me. 

Editing content.xml turned out to be slightly tricky because on my system using xml editors such as kate or geany, it's all one line, with no xml structure.

That fix could be a lot of work for a lot of users of existing databases.  If this is the only fix I suggest the ability to do this from within Base itself is added to the database edit menu.
Comment 28 Lionel Elie Mamane 2018-09-14 09:48:14 UTC
(In reply to tim from comment #27)
> Editing content.xml turned out to be slightly tricky because on my system
> using xml editors such as kate or geany, it's all one line, with no xml
> structure.

Using the macro could be considered easier. See 
https://bugs.documentfoundation.org/show_bug.cgi?id=117053#c15
Comment 29 tim 2018-09-14 10:00:42 UTC
I think there may be users for which some of this could be difficult.  I still think some for of easy to understand migration tool for pre 6.1 odbs should be provided.  

Many users may not find it easy to find stuff in bugzilla (I struggle at times), let alone find and execute a macro or edit an odb.
Comment 30 Lionel Elie Mamane 2018-09-14 13:01:58 UTC
(In reply to tim from comment #29)
> I think there may be users for which some of this could be difficult.  I
> still think some for of easy to understand migration tool for pre 6.1 odbs
> should be provided.  

Yes. No one disputes your statement, nor said anything to the contrary. Possibly,  the settings the LibreOffice *knows* are the correct one should just overwrite the ones in the file...
Comment 31 tim 2018-09-14 13:06:54 UTC
(In reply to Lionel Elie Mamane from comment #30)
> (In reply to tim from comment #29)
> > I think there may be users for which some of this could be difficult.  I
> > still think some for of easy to understand migration tool for pre 6.1 odbs
> > should be provided.  
> 
> Yes. No one disputes your statement, nor said anything to the contrary.
> Possibly,  the settings the LibreOffice *knows* are the correct one should
> just overwrite the ones in the file...
Yes, apologies if I was misunderstanding the drift of the suggestions and commments.
Comment 32 tim 2018-09-14 20:31:24 UTC
I just noticed that using MYSQL(Native) avoids this problem.  I was using MYSQL(JDBC) when I came across it.
Comment 33 Dan Lewis 2018-12-06 22:50:41 UTC
(In reply to tim from comment #32)
> I just noticed that using MYSQL(Native) avoids this problem.  I was using
> MYSQL(JDBC) when I came across it.

I have recently installed MySQL and MySQL workbook. I use LibreOffice 6.0.6.2 and 6.1.4.1 downloaded from LibreOffice website. I also have two Java connectors for MySQL (8.0.13 and 5.1.23) which I downloaded from the MySQL website in the last couple of days. Both of them work with LO 6.0.6.2 but give the same error message with 6.1.4.1.
Comment 34 Alex Thurgood 2018-12-10 09:10:39 UTC
*** Bug 121997 has been marked as a duplicate of this bug. ***
Comment 35 Dan Lewis 2019-01-03 03:54:17 UTC
(In reply to Dan Lewis from comment #33)
> (In reply to tim from comment #32)
> > I just noticed that using MYSQL(Native) avoids this problem.  I was using
> > MYSQL(JDBC) when I came across it.
> 
> I have recently installed MySQL and MySQL workbook. I use LibreOffice
> 6.0.6.2 and 6.1.4.1 downloaded from LibreOffice website. I also have two
> Java connectors for MySQL (8.0.13 and 5.1.23) which I downloaded from the
> MySQL website in the last couple of days. Both of them work with LO 6.0.6.2
> but give the same error message with 6.1.4.1.

I have recently downloaded and installed LO 6.2.0.1 RC1. When using the native MySQL connector, I still get this error message. I also get it when using both JDBC connectors (8.0.13, 5.1.23).

Both of them work properly for LO 6.0.6.3 and 6.0.7.3.
Comment 36 erich 2019-02-18 13:55:46 UTC
Same effect with 6.2 and Mariadb  10.1.37 on debian 9.6
following Dan Lewis falling back to 6.0.7
made my day Thanks !!
Comment 37 Roland 2019-02-25 00:38:52 UTC
It's the same with LO 6.1 and 6.2 and Mysql 5.7  on Linux Mint 18.2.
I also have this after upgrading LO. My subforms don't show anything anymore and I get the same error message, using the native MySQL connector or JDBC connectors (8.0.13, 5.1.23). I tried to find a solution that I could understand but no success.
It all worked properly up to 6.0.7.3.
So the only solution was (thnx Dan Lewis) falling back to 6.0.7.3  :-(
It's an important database with many forms, so no more upgrading until an easy to understand solution is available.  Would be really nice if a migration tool of some kind for pre 6.1 odbs could be provided.
Comment 38 Alex Kempshall 2019-02-25 09:19:50 UTC
In comment 18 I observed that this patch -

1.
will stop the problem in new applications;

2.
applications built before this patch will have to be fixed by manually applying one of the workarounds.

In comment 21, July 2018, I reported that I applied one of the workarounds to an existing application. Not had the problem since. The LO version I was using then was 6.0.4.

I'm now on LO 6.2.0
Comment 39 Julien Nabet 2019-02-25 09:37:12 UTC
Dan, Erich, Roland, Alex:

Just for the remind, as Lionel indicated in the comment 28, he created a macro to convert odb files created before this patch.

See https://bugs.documentfoundation.org/show_bug.cgi?id=117053#c15

Of course, make a backup of your file before applying it.

The parameter management was buggy before. LO code has been fixed but the ODB files created before the fix need a manual fix, it's what the Lionel's macro does.
Comment 40 Alex Thurgood 2019-03-11 15:04:03 UTC
*** Bug 123999 has been marked as a duplicate of this bug. ***
Comment 41 tim 2019-03-27 09:20:45 UTC
On upgrading from 6.5 to 6.2.2 this problem re-occurred for me on an old odb that was working fine.  Running the macro fixed it.

I have had to revert to using jdbc since the mysql(native) connector is not appearing for my mariadb database - I don't know if these things are related.
Comment 42 tim 2019-03-27 09:22:27 UTC
Sorry (I wish I could edit posts!) - for 6.5 read 6.1.5.
Comment 43 tim 2019-03-27 10:05:41 UTC
And note that it seems a time change can trigger the same problem, although here in the UK we haven't changed the clocks yet in 2019 (and maybe never will after 2021...).

See https://bugs.documentfoundation.org/show_bug.cgi?id=123999
Comment 44 Drew Jensen 2019-03-29 14:30:10 UTC
*** Bug 124427 has been marked as a duplicate of this bug. ***
Comment 45 Gerhard Weydt 2019-05-15 19:50:37 UTC
I still encounter this problem, although with a different message starting with "Could not set parameter at position 1 (values was..." with Libo 6.2.2.2 (Windows 64 bit) with MariaDB and the JDBC connector (a native connector unfortunately still does not seem to be available); erich reported nearly the same in comment 36.
The macro provided (see comment 28) works fine for me, now that I know, but this could be a problem for new users, indeed it could be a show-stopper.
Comment 46 Julien Nabet 2019-05-15 19:54:35 UTC
(In reply to Gerhard Weydt from comment #45)
> ...
> The macro provided (see comment 28) works fine for me, now that I know, but
> this could be a problem for new users, indeed it could be a show-stopper.

I noticed the change was in release notes (see https://wiki.documentfoundation.org/ReleaseNotes/6.1) but not the way to fix it with the macro.
Comment 47 erich 2019-05-17 09:05:16 UTC
Just let you know:
The problem still exists with 6.3 beta using an 
existing (older)  .odb

So Lionels macro treatment becomes a MUST
using existing .odb's  in future ???

as Julien says in comment-46 what about
all the other users who dont find the way
into this forum ?

Erich
Comment 48 Alex Kempshall 2019-05-17 09:47:53 UTC
I raised this bug report initially.

As the problem has been fixed going forward, users creating new applications shouldn't encounter the problem. I've not tested this assumption, just accept what the developers have said.

If users are still using old applications that haven't yet encountered the problem they must be using an old version of LibreOffice. Those should be a diminishing number.

If a user encounters the problem, they have either inherited an application or written it themselves, in the latter case I would have thought they are reasonably expert and would find themselves to this fix.
Comment 49 Xisco Faulí 2019-05-21 10:53:48 UTC
This issue was incorrectly moved to UNCONFIRMED
Comment 50 tim 2019-06-28 15:40:01 UTC
I created a completely new mariadb database today using

Version: 6.2.2.2
Build ID: 1:6.2.2-0ubuntu2
CPU threads: 8; OS: Linux 5.0; UI render: default; VCL: x11; 
Locale: en-GB (en_GB.UTF-8); UI-Language: en-GB
Calc: threaded

I still get the parameter error.

What am I doing wrong?
Comment 51 tim 2019-06-28 19:23:53 UTC
I am using mysql-connector-java.jar, and com.mysql.jdbc.Driver. (The mariadb ones still give me other problems, so I am avoiding them for now).

This is completely new odb file, so I did not expect to get this problem again.  I know how to fix it, but surely it should not be happening?
Comment 52 Julien Nabet 2019-06-30 08:16:33 UTC
(In reply to tim from comment #51)
> ...
> This is completely new odb file, so I did not expect to get this problem
> again.  I know how to fix it, but surely it should not be happening?

On connectivity/registry, there are 2 subdirs related to mysql:
- mysqlc
- mysql_jdbc

Both have "ParameterNameSubstitution" blocks in "Properties" superblock with "true" value.
Re reading "connectivity/registry/README", I noted:
"
Property: actual setting with its underlying type and its default value

Feature: a boolean that says "let the user change that setting in the Advanced Settings UI"
"
So it shouldn't be necessary to add "ParameterNameSubstitution" in "Features" parts.

Did you rename your LO directory profile before creating your brand new odb file?
Comment 53 tim 2019-06-30 09:27:35 UTC
(In reply to Julien Nabet from comment #52)
> (In reply to tim from comment #51)
> > ...
> > This is completely new odb file, so I did not expect to get this problem
> > again.  I know how to fix it, but surely it should not be happening?
> 
> On connectivity/registry, there are 2 subdirs related to mysql:
> - mysqlc
> - mysql_jdbc
> 
> Both have "ParameterNameSubstitution" blocks in "Properties" superblock with
> "true" value.
> Re reading "connectivity/registry/README", I noted:
> "
> Property: actual setting with its underlying type and its default value
> 
> Feature: a boolean that says "let the user change that setting in the
> Advanced Settings UI"
> "
> So it shouldn't be necessary to add "ParameterNameSubstitution" in
> "Features" parts.
> 
> Did you rename your LO directory profile before creating your brand new odb
> file?
You've slightly lost me there.  Are you saying I need to start a completely new LO user profile?  If so, others are also going to encounter this problem every time they create new databases.
Comment 54 Julien Nabet 2019-06-30 09:30:02 UTC
(In reply to tim from comment #53)
> ...
> > Did you rename your LO directory profile before creating your brand new odb
> > file?
> You've slightly lost me there.  Are you saying I need to start a completely
> new LO user profile?  If so, others are also going to encounter this problem
> every time they create new databases.

I'm not sure here but can you give it a try just to know if it helps?
Comment 55 tim 2019-06-30 14:54:42 UTC
(In reply to Julien Nabet from comment #54)
> (In reply to tim from comment #53)
> > ...
> > > Did you rename your LO directory profile before creating your brand new odb
> > > file?
> > You've slightly lost me there.  Are you saying I need to start a completely
> > new LO user profile?  If so, others are also going to encounter this problem
> > every time they create new databases.
> 
> I'm not sure here but can you give it a try just to know if it helps?
I renamed my old profile, so a new one was created (LO 6.2.2.2).

I created a new base document, using mysql jdbc (and Driver) to a test mariadb database.

I created a new form (and subform).

I got the same parameter error.
Comment 56 Julien Nabet 2019-07-01 08:29:24 UTC
(In reply to tim from comment #55)
> ...
> I renamed my old profile, so a new one was created (LO 6.2.2.2).
> 
> I created a new base document, using mysql jdbc (and Driver) to a test
> mariadb database.
> 
> I created a new form (and subform).
> 
> I got the same parameter error.

Did you try to apply macro even if it shouldn't be necessary? Indeed, I expect you still reproduce the problem even when apply macro.

Also, would it be possible you attach the odb file with a script to create a minimum database to reproduce this?
Comment 57 tim 2019-07-01 14:13:22 UTC
(In reply to Julien Nabet from comment #56)
> (In reply to tim from comment #55)
> > ...
> > I renamed my old profile, so a new one was created (LO 6.2.2.2).
> > 
> > I created a new base document, using mysql jdbc (and Driver) to a test
> > mariadb database.
> > 
> > I created a new form (and subform).
> > 
> > I got the same parameter error.
> 
> Did you try to apply macro even if it shouldn't be necessary? Indeed, I
> expect you still reproduce the problem even when apply macro.
> 
> Also, would it be possible you attach the odb file with a script to create a
> minimum database to reproduce this?
I will shortly attach:

Test.sql is the sql for my (empty) test database

TestNew4.odb is a test odb with no macro having been run, giving the error when the only form is open

TestNew5.odb is the same, but the macro is included and has been run, and no error is detected when opening the only form.
Comment 58 tim 2019-07-01 14:13:57 UTC
Created attachment 152485 [details]
Sql to create test database
Comment 59 tim 2019-07-01 14:14:46 UTC
Created attachment 152486 [details]
odb file returning error when form opened
Comment 60 tim 2019-07-01 14:15:30 UTC
Created attachment 152487 [details]
odb file with macro having been run - no error
Comment 61 Lionel Elie Mamane 2019-07-01 14:46:28 UTC
Since running the macro solves the problem, it really looks like the bug is back, at file creation time.
Comment 62 Julien Nabet 2019-07-01 15:23:14 UTC
Thank you Tim for your feedback.

I don't see any location in LO code which could explain this and you indicate your LO profile is new.
I must recognize I'm stuck.
Comment 63 tim 2019-07-01 16:24:40 UTC
(In reply to Julien Nabet from comment #62)
> Thank you Tim for your feedback.
> 
> I don't see any location in LO code which could explain this and you
> indicate your LO profile is new.
> I must recognize I'm stuck.
I should clarify that after the test yesterday I restored my LO profile (too many settings I don't want to lose).

TestNew4.dob is as it was yesterday, created with a new profile.  

Today I copied it to TestNew5.odb, added the macro and ran it, but that was under my old profile.  If I new to redo TestNew5.odb with a new profile, let me know.


All on:
Version: 6.2.2.2
Build ID: 1:6.2.2-0ubuntu2
CPU threads: 8; OS: Linux 5.0; UI render: default; VCL: x11; 
Locale: en-GB (en_GB.UTF-8); UI-Language: en-GB
Calc: threaded
Comment 64 Julien Nabet 2020-03-31 20:32:23 UTC
On pc Debian x86-64 with master sources updated today, I gave a new try.
First I wanted to recreate the bug from scratch so I created a brand odb file.
On MariaDBServer, I got a simple table with ID (Integer primary key), Test (Varchar) containing just 2 values: 1, "Test1", 2 "Test2"


With Mysql direct and Mysql JDBC, here what I did:
- create a query:
SELECT "Test" FROM "Table1" WHERE "ID" = :id

- create a form from query with by default options.

I unzip the 2 odb files generated from this process, but found no "ParameterNameSubstitution" at all.

Waht step did I miss so this param is present on the content.xml?
Comment 65 tim 2020-04-02 16:48:08 UTC
(In reply to Julien Nabet from comment #64)
> On pc Debian x86-64 with master sources updated today, I gave a new try.
> First I wanted to recreate the bug from scratch so I created a brand odb
> file.
> On MariaDBServer, I got a simple table with ID (Integer primary key), Test
> (Varchar) containing just 2 values: 1, "Test1", 2 "Test2"
> 
> 
> With Mysql direct and Mysql JDBC, here what I did:
> - create a query:
> SELECT "Test" FROM "Table1" WHERE "ID" = :id
> 
> - create a form from query with by default options.
> 
> I unzip the 2 odb files generated from this process, but found no
> "ParameterNameSubstitution" at all.
> 
> Waht step did I miss so this param is present on the content.xml?
Sorry, I can't help with that. It's way beyond my knowledge.
Comment 66 Julien Nabet 2020-04-24 14:11:02 UTC
*** Bug 132377 has been marked as a duplicate of this bug. ***
Comment 67 Julien Nabet 2020-04-29 10:23:22 UTC
The reporter of tdf#132377 indicates that he doesn't reproduce the problem with 6.4.3, a brand new LO profile and a brand new odb file, see https://bugs.documentfoundation.org/show_bug.cgi?id=132377#c4

For those who encountered this problem, could you:
- uninstall completely LO
- rename your LO profile
- install LO 6.4.3
- test again from a brand new odb file
?
Comment 68 tim 2020-04-29 15:42:03 UTC
(In reply to Julien Nabet from comment #67)
> The reporter of tdf#132377 indicates that he doesn't reproduce the problem
> with 6.4.3, a brand new LO profile and a brand new odb file, see
> https://bugs.documentfoundation.org/show_bug.cgi?id=132377#c4
> 
> For those who encountered this problem, could you:
> - uninstall completely LO
> - rename your LO profile
> - install LO 6.4.3
> - test again from a brand new odb file
> ?
I purged my LO, renamed my profile.  Installed LO 6.4.3.

The old TestNew4.odb still returns the parameter error.

A brand new odb (still using JDBC) to the same old mariadb test database does not return the error.

It's been a while since I created any new forms, and the form design wizard doesn't seem to exist.  Whether that's an ubuntu version issue or more general I don't yet know.  I copied the old form from TestNew4.odb to the new file, since I couldn't remember how to set it up the same way as before.

My LO version is:

Version: 6.4.3.2
Build ID: 1:6.4.3-0ubuntu0.19.10.1
CPU threads: 8; OS: Linux 5.3; UI render: default; VCL: gtk3; 
Locale: en-GB (en_GB.UTF-8); UI-Language: en-GB
Calc: threaded
Comment 69 Julien Nabet 2020-04-29 16:16:31 UTC
(In reply to tim from comment #68)
> ...
> I purged my LO, renamed my profile.  Installed LO 6.4.3.
> 
> The old TestNew4.odb still returns the parameter error.
It's expected since the fix will work only for the odb files generated from LO version which is indeed fixed and LO 6.4.3 should be ok.
For old odb, you must use the macro proposed by Lionel here:
https://bugs.documentfoundation.org/show_bug.cgi?id=117053#c15

> 
> A brand new odb (still using JDBC) to the same old mariadb test database
> does not return the error.
It's normal since the pb isn't the MariaDB or Mysql database. The new odb is generated from a version of LO which is fixed.

> 
> It's been a while since I created any new forms, and the form design wizard
> doesn't seem to exist.  Whether that's an ubuntu version issue or more
> general I don't yet know.  I copied the old form from TestNew4.odb to the
> new file, since I couldn't remember how to set it up the same way as before.
> 
or as I said, just run the macro quoted on your odb file.

In brief, if you don't reproduce the problem with a brand new odb file, it shows the bug is indeed fixed.

With old odb files, just run the macro once and it'll fix the odb.
Comment 70 tim 2020-04-29 16:29:57 UTC
(In reply to Julien Nabet from comment #69)
> (In reply to tim from comment #68)
> > ...
> > I purged my LO, renamed my profile.  Installed LO 6.4.3.
> > 
> > The old TestNew4.odb still returns the parameter error.
> It's expected since the fix will work only for the odb files generated from
> LO version which is indeed fixed and LO 6.4.3 should be ok.
> For old odb, you must use the macro proposed by Lionel here:
> https://bugs.documentfoundation.org/show_bug.cgi?id=117053#c15
> 
> > 
> > A brand new odb (still using JDBC) to the same old mariadb test database
> > does not return the error.
> It's normal since the pb isn't the MariaDB or Mysql database. The new odb is
> generated from a version of LO which is fixed.
> 
> > 
> > It's been a while since I created any new forms, and the form design wizard
> > doesn't seem to exist.  Whether that's an ubuntu version issue or more
> > general I don't yet know.  I copied the old form from TestNew4.odb to the
> > new file, since I couldn't remember how to set it up the same way as before.
> > 
> or as I said, just run the macro quoted on your odb file.
> 
> In brief, if you don't reproduce the problem with a brand new odb file, it
> shows the bug is indeed fixed.
> 
> With old odb files, just run the macro once and it'll fix the odb.
Julian, 

Yes, it's as expected.  I was just running the test as you requested to confirm that it works as you expect.

Having run the macro on my old databases quite a long time ago, all still works normally.
Comment 71 Julien Nabet 2020-04-29 17:26:25 UTC
Thank you for your feedback.
Let's put this to FIXED then.
Comment 72 tim 2020-04-29 19:07:50 UTC
(In reply to Julien Nabet from comment #71)
> Thank you for your feedback.
> Let's put this to FIXED then.
Good.  I just hope that any with older databases that haven't yet moved to newer versions of LO manage to find the macro.
Comment 73 Julien Nabet 2020-05-15 17:15:17 UTC
*** Bug 128554 has been marked as a duplicate of this bug. ***
Comment 74 Jean-Paul 2020-05-21 06:21:04 UTC
*** Bug 120091 has been marked as a duplicate of this bug. ***