Bug 125168 - The database wizard generates buggy PostgreSQL URLs
Summary: The database wizard generates buggy PostgreSQL URLs
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.2.2.2 release
Hardware: x86-64 (AMD64) Linux (All)
: high major
Assignee: Not Assigned
URL:
Whiteboard: target:6.4.0 target:6.3.0.1 target:6.2.6
Keywords: bibisectRequest, regression
: 125221 (view as bug list)
Depends on:
Blocks:
 
Reported: 2019-05-08 06:30 UTC by Sébastien Ducoulombier
Modified: 2019-06-30 08:24 UTC (History)
11 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 Sébastien Ducoulombier 2019-05-08 06:30:05 UTC
Description:
The database Wizard has become unreliable in creating a new ODB file connecting to a PostgreSQL database.

One has only one chance over many to get the right connection URL generated.

Steps to Reproduce:
1. File / New / Database
2. Connect to an existing Database / PostgreSQL, Next
3. Datasource URL: host=servername dbname=basename
4. Type in the user name, check "Password required", press "Test Connection" and type the password

Actual Results:
"A driver is not registered for the URL ~sdbc:postgresql:host=servername dbname=basename"
or 
"A driver is not registered for the URL host=servername dbname=basename"

Expected Results:
"The connection was established successfully"


Reproducible: Sometimes


User Profile Reset: Yes



Additional Info:
This is reproducible on the first few attempts.
One has to restart from the first step (File / New / Database) several times (usually less than 5 times) in order to succeed.

Version: 6.2.3.2
Build ID: aecc05fe267cc68dde00352a451aa867b3b546ac
CPU threads: 4; OS: Linux 4.9; UI render: default; VCL: gtk3; 
Locale: en-US (C); UI-Language: en-US
Calc: threaded

I could not reproduce this bug in version 6.1.5.2.
I could reproduce this bug in version 6.2.2.2.
It might have appeared in 6.2.0 (not tested yet)
Comment 1 Sébastien Ducoulombier 2019-05-09 06:20:04 UTC
I cannot reproduce this bug in version 6.3.0.0.alpha0+
Comment 2 Alex Thurgood 2019-05-09 10:09:27 UTC
@Sébastien:

Any idea whether this is specific to Ubuntu versions, or are you testing with TDF downloads ?
Comment 3 Sébastien Ducoulombier 2019-05-09 10:26:09 UTC
I am testing with AppImage versions released at https://www.libreoffice.org/download/appimage/

I am running them on an up-to-date Debian stable desktop amd64 host.
Comment 4 Sébastien Ducoulombier 2019-05-12 11:53:34 UTC
Windows is affected too :
https://ask.libreoffice.org/en/question/193452/windows-libreoffice-623-postgresql-sdbc-driver-error/
Comment 5 Alex Thurgood 2019-05-14 06:55:05 UTC
I can confirm this in my own master build with

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

It did indeed used to work, as I have an ODB file created with a previous version of LO which does.
Comment 6 Alex Thurgood 2019-05-14 06:59:38 UTC
Methinks it might be related to bug 119850 or bug 119786
Comment 7 Alex Thurgood 2019-05-14 07:03:32 UTC
In my previously made ODB which still continues to function in master, the URL connection string indicated at the bottom of the main base window shows :

host=localhost dbname=ajt

As the port used for my test instance of postgres is the default 5432, I didn't need to enter the port value in the URL string when I set up the database.
Comment 8 Alex Thurgood 2019-05-14 07:10:16 UTC
Hmm, seeing how Drivers.xcu was changed to correct the mysql URL construction, I am wondering whether Mike's recent changes to the various properties of the Drivers.xcu files has perhaps caused a problem here with the postgres URL UI ?
Comment 9 Alex Thurgood 2019-05-14 07:15:19 UTC
@Mike: any chance your recent changes have had an effect here ?
Comment 10 Alex Thurgood 2019-05-14 07:16:39 UTC
By which I mean :

commit f33eb08a2b74e5de033af9b5f5283b220ac58ded

and/or

commit 8402cd6974bf4dc6318bda39298ac1b56f1923f7
Comment 11 Mike Kaganski 2019-05-14 07:25:11 UTC
(In reply to Alex Thurgood from comment #9)
> @Mike: any chance your recent changes have had an effect here ?

Given that I didn't touch connectivity/registry/postgresql/org/openoffice/Office/DataAccess/Drivers.xcu, I doubt it. Rather, I suspect that some similar change would be required after commit 6de679cca6978694bacf5322c9ab8559740f0443 (but that specific commit also didn't change the said file) or some similar one.

But of course, I might be wrong in both points - unfortunately I'm not a DB expert.
Comment 12 Alex Thurgood 2019-05-14 07:31:26 UTC
*** Bug 125221 has been marked as a duplicate of this bug. ***
Comment 13 Alex Thurgood 2019-05-14 07:40:26 UTC
(In reply to Mike Kaganski from comment #11)
> (In reply to Alex Thurgood from comment #9)
> > @Mike: any chance your recent changes have had an effect here ?
> 
> Given that I didn't touch
> connectivity/registry/postgresql/org/openoffice/Office/DataAccess/Drivers.
> xcu, I doubt it. Rather, I suspect that some similar change would be
> required after commit 6de679cca6978694bacf5322c9ab8559740f0443 (but that
> specific commit also didn't change the said file) or some similar one.
> 
> But of course, I might be wrong in both points - unfortunately I'm not a DB
> expert.

Ah yes, I see, sorry for the noise.
Comment 14 Julien Nabet 2019-05-14 12:39:28 UTC
On Win10 with master sources updated today, I could reproduce this.

I noticed these logs on console:
warn:connectivity.postgresql:41956:35924:connectivity/source/drivers/postgresql/pq_connection.cxx:474: sdbc-postgresql: unknown argument 'BooleanComparisonMode' having value: <Any: (long) 2>
warn:connectivity.postgresql:41956:35924:connectivity/source/drivers/postgresql/pq_connection.cxx:474: sdbc-postgresql: unknown argument 'EnableOuterJoinEscape' having value: <Any: (boolean) 0>
warn:connectivity.postgresql:41956:35924:connectivity/source/drivers/postgresql/pq_connection.cxx:474: sdbc-postgresql: unknown argument 'EscapeDateTime' having value: <Any: (boolean) 0>
Comment 15 Julien Nabet 2019-05-14 13:14:59 UTC
Sorry, here are the results of my tests with different urls:
1) host=localhost:5432 dbname=dvdrental
=>
Statut SQL: could not translate host name "localhost:5432" to address: Unknown host

Code d'erreur: 1

Couldn't establish database connection to 'sdbc:postgresql:host=localhost:5432 dbname=dvdrental'
could not translate host name "localhost:5432" to address: Unknown host


2) host=localhost dbname=dvdrental
=>
Connection OK

In both cases, I got console logs indicated in my previous post.

I used Postgresql v11.3
Comment 16 Julien Nabet 2019-05-14 13:42:35 UTC
Alex: did you notice any specific console logs?
Comment 17 Drew Jensen 2019-05-23 16:51:02 UTC
Here is what I think is happening:

If the Base wizard can not parse the connection string entered
For instance this: host=localhost:5432 dbname=lo
Base will display an error, complaining about the string.

In LO 6.1 and first few 6.2.x the code behind the Newdatabase Wizard did not alter the string entered, while the wizard is open.

In LO6.2 and 6.3 currently the wizard code will truncate the string to null, always or often, after the first error.

You can see it with these steps

1 - start new database wizard, type postgresql Next tab
2 - enter a connection normally, but add the :5432 to the end of the host name, next tab
3 - enter username and click test connection. You will get the error about the connection string failing to parse.

4 previous tab. Back to the connection string text box tab. The string may still be displayed, or if the string was clobbered the text box is empty.

5 next tab. To the user name, test connection, tab.

6 Click Test connection

result: error now that it can not load the driver!

6 close the error box and previous tab. 

result: The connection string, in the text box, is null "".

** could of skipped the test connection step, the connection string is clobbered it seems when the tab is changed in step 5. 

expected: It never went null, no matter how often you switched tabs.

Ran bibsect against a 6.2 repo, the best I could get was a range of commits for when the anomaly starts:

The first bad commit could be any of:
ffef5911b61a3d1ef7093c7fe8b30ed617df5176
0c4c968c7cfc8c44a60bf1047e3d3a2bf961ff67

The final step(0) of the bibisect session LO crashes right as the database type of postgresql is selected. I answered that as "bibisect skip" rather than bad, which I suppose explains the range.
Comment 18 libreoffice 2019-05-27 08:23:15 UTC
I can confirm this in Version 6.2.4.2

my last update in Archlinux from 6.2.3 made that the database connection  with postgresql is unusable.

I have a Database in Libreoffice base which makes the connection to my postgresDB. The queries in base gives the columns for a pivot-Table in Calc. 

My existing base document is able to show me the Columns. When I update my pivot table it is empty.
Comment 19 Julien Nabet 2019-05-27 08:32:16 UTC
please don't put a newer version since the field "Version" must correspond to "earliest affected"
Comment 20 Alex Thurgood 2019-05-27 11:59:34 UTC
(In reply to Julien Nabet from comment #16)
> Alex: did you notice any specific console logs?

Hi Julien, unfortunately not.
Comment 21 Mike Kaganski 2019-05-27 12:03:51 UTC
(In reply to libreoffice from comment #18)

Also setting this to Linux-only was apparently wrong, given confirmations in c#5 and c#14.

The bibisect result in c#17 points to some commit hashes unknown to core.git - are those bibisect commits instead of source commits?
Comment 22 Drew Jensen 2019-05-27 12:17:57 UTC
(In reply to Mike Kaganski from comment #21)
> (In reply to libreoffice from comment #18)
> 
> Also setting this to Linux-only was apparently wrong, given confirmations in
> c#5 and c#14.
> 
> The bibisect result in c#17 points to some commit hashes unknown to core.git
> - are those bibisect commits instead of source commits?

IDK - it was verbatim (copy/paste) form the terminal.

However - updated the repo again and am running it again for this issue - if still a crash at the last step will not enter 'skip' but answer 'bad' and see if that gives something useful.
Comment 23 Mike Kaganski 2019-05-27 13:36:34 UTC
(In reply to Drew Jensen from comment #22)
> However - updated the repo again and am running it again for this issue - if
> still a crash at the last step will not enter 'skip' but answer 'bad' and
> see if that gives something useful.

Please just issue

> git log -n 1 ffef5911b61a3d1ef7093c7fe8b30ed617df5176
> git log -n 1 0c4c968c7cfc8c44a60bf1047e3d3a2bf961ff67

in your bibisect repo, and post here. And also, please always mention which bibisect repo you used - that way, anyone could find that information.
Comment 24 Drew Jensen 2019-05-27 14:01:17 UTC
Using Ubuntu 18.04.1 (64 bit) and bibisect repo for LO 6.2

1st - still have a crash on the last step(0) and reported that to bibsect as a bad outcome.

With that it returned a single commit:
https://cgit.freedesktop.org/libreoffice/core/commit/?id=a8c1835911450211493f3cddc10c3ccc1455c43a

Titled: drop unused stuff
Author: Caolán McNamara

Files touched: 
dbaccess/source/ui/dlg/ConnectionPageSetup.cxx
dbaccess/source/ui/dlg/ConnectionPageSetup.hxx	
dbaccess/source/ui/dlg/dbwizsetup.cxx

Adding developer to CC
Comment 25 Caolán McNamara 2019-06-23 15:05:32 UTC
I think the problem is that there is a hidden label containing the prefix "sdbc:postgresql" and the auto mnemonic processing is adding the mnemonic ~ to the string giving ~sdbc:postgresql and we're taking the label contents raw leading to a bizarre outcome.
Comment 26 Commit Notification 2019-06-23 17:41:02 UTC
Caolán McNamara committed a patch related to this issue.
It has been pushed to "master":

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

Resolves: tdf#125168 label mnemonic appearing in database url

It will be available in 6.4.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 27 Commit Notification 2019-06-23 17:42:28 UTC
Caolán McNamara committed a patch related to this issue.
It has been pushed to "libreoffice-6-3":

https://git.libreoffice.org/core/+/600b92914d655ed4354debc8dfd7227de5d41cd1%5E%21

Resolves: tdf#125168 label mnemonic appearing in database url

It will be available in 6.3.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 28 Caolán McNamara 2019-06-23 18:47:11 UTC
I believe that fixes it, backport to 6-2 also in gerrit
Comment 29 Commit Notification 2019-06-25 07:24:05 UTC
Caolán McNamara committed a patch related to this issue.
It has been pushed to "libreoffice-6-2":

https://git.libreoffice.org/core/+/1c6bac6e003e9f280a29cc83974bdfb3d06b24a9%5E%21

Resolves: tdf#125168 label mnemonic appearing in database url

It will be available in 6.2.6.

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 30 Xisco Faulí 2019-06-25 15:08:20 UTC
@Alex Thurgood, @Drew Jensen, @Julien Nabet, Could you please verify this commit submitted by Caolán fixes the issue ? I would like to backport it to 6.2.5 if it's correct...
Comment 31 Drew Jensen 2019-06-27 14:46:39 UTC
using Ubuntu 18.04.01 and LO Version: 6.3.0.0.beta2+
Build ID: e17e30dceb110e780a7e7e89c2ede854d4bc38a7
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; 

The problem does seem to be gone.
Comment 32 Julien Nabet 2019-06-30 08:24:46 UTC
Following Drew's comment, let's put this one to VERIFIED.