Bug 127343 - When creating a query in design view using MySQL, the Add Tables dialog only displays tables from, and allows interaction with, the first alphabetically sorted schema
Summary: When creating a query in design view using MySQL, the Add Tables dialog only ...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.3.1.1 rc
Hardware: All All
: high major
Assignee: Not Assigned
URL:
Whiteboard: target:6.3.3 target:6.4.0 target:6.3.2
Keywords: bibisectRequest, regression
: 127358 (view as bug list)
Depends on:
Blocks:
 
Reported: 2019-09-04 16:26 UTC by Dan Lewis
Modified: 2019-09-18 21:21 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
Screen show for LO 6.3 (20.48 KB, image/png)
2019-09-04 16:27 UTC, Dan Lewis
Details
Screen shot for LO 6.2.6.1 (14.38 KB, image/png)
2019-09-04 16:28 UTC, Dan Lewis
Details
screenshot for 20190917 daily build (14.01 KB, image/png)
2019-09-18 21:21 UTC, Dan Lewis
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Dan Lewis 2019-09-04 16:26:11 UTC
Description:
When the Add Tables or Query dialog, I get a list of my schemas in LO 6.3 but none of the schemas has a drop down list of its tables. (I have seen one of the schema with its tables listed directly below it rather than being part of a drop down list.

Steps to Reproduce:
1.Open the MySQL database using Connector/J using LO 6.3.
2.Click Create Query in Design View
3. The query dialog opens and then the Add Table or Query dialog opens.


Actual Results:
I have several schemas in my database, and there are all listed in this dialog. There is no drop down list for the tables of these schemas. This is true for 6.3.0.1, 6.3.0.2, 6.3.0.4, and now 6.3.1.2. (See attached screen shot labeled AddTableOrQuery_6.3.)

Expected Results:
The schemas listed each have a drop down list of their tables as is done when using LO 6.2.6.1. See the screen shot labeled AddTableOrQuery_6.2.


Reproducible: Always


User Profile Reset: No



Additional Info:
I opened the same database document file by LO 6.2.6.1 and 6.3.1.2 in succession to get the screen shots.
Comment 1 Dan Lewis 2019-09-04 16:27:36 UTC
Created attachment 153877 [details]
Screen show for LO 6.3
Comment 2 Dan Lewis 2019-09-04 16:28:44 UTC
Created attachment 153878 [details]
Screen shot for LO 6.2.6.1
Comment 3 Xisco Faulí 2019-09-05 09:31:33 UTC
*** Bug 127358 has been marked as a duplicate of this bug. ***
Comment 4 Xisco Faulí 2019-09-05 09:41:37 UTC
I don't know how to test it but the dialog was weld in https://cgit.freedesktop.org/libreoffice/core/commit/?id=c5edb541337054412fb1d5037de6c50397ab053f

@Caolán, I thought you might be interested in this issue!!

@Dan Lewis, Does it only happen with MySQL ?
Comment 5 Alex Thurgood 2019-09-05 10:25:38 UTC
@Xisco : with an embedded hsqldb, only the tables are shown, not the schema.

Historically, the fact that all of the schema are shown with a mysql JDBC connection is an error, as the connection setup requires a schema to be selected before connection. The fact that the specific choice of schema was not used to filter out the others in the displays of tables, queries, views, etc is/was a bug. This is why mutliple schema were displayed with a tree structure to allow the user to select any given table from any of the available schema. The issues of only displaying the pre-defined schema was addressed by the introduction of a working filter for the schema, allowing a user to exclude from view the other schema (menu Tools > Table Filter), and required a refresh of the tables, or in some circumstances, a reconnection to the database (closing/re-opening ODB file).

This doesn't happen with embedded hsqldb because it is, to all intents and purposes, single schema, the hsqldb system information schema is hidden from the user within LO and only accessible via SQL.
Comment 6 Alex Thurgood 2019-09-05 10:38:40 UTC
Confirming that tree view of schema, with tables listed underneath, appears in LO6252, using the native, direct mysql connector.

Confirming the display of schema and tables as reported by Dan in LO6304.
Only the first alphabetically sorted schema displays any tables in my test, the other schema being displayed, but no operable action on them is possible, i.e. clicking on them does not unroll the tree.

This is a regression over the behaviour in LO62, especially seeing as the first alphabetically sorted schema may not even be the one defined in the connection properties.
Comment 7 Alex Thurgood 2019-09-05 10:47:41 UTC
Clearly, it shouldn't just display the first schema in the list, unless this is the one that was chosen in the connection setup.

The additional difficulty is that mysql (and other db engines) allows multiple-schema, or cross-schema queries, so you still need to be able to see all of those and interact with them via the Add Table/Query dialog.
Comment 8 Caolán McNamara 2019-09-10 21:54:28 UTC
Here's a public mysql database for testing purposes: https://rfam.readthedocs.io/en/latest/database.html

https://gerrit.libreoffice.org/#/c/78807/ might be the fix for the problem described here
Comment 9 Alex Thurgood 2019-09-11 07:41:59 UTC
(In reply to Caolán McNamara from comment #8)
> Here's a public mysql database for testing purposes:
> https://rfam.readthedocs.io/en/latest/database.html
> 
> https://gerrit.libreoffice.org/#/c/78807/ might be the fix for the problem
> described here

Hi Caolán,

Has this fix made it into 

Version: 6.4.0.0.alpha0+
Build ID: f0c832acb53326ccc9a8c1a47401fbc9e1081feb
CPU threads: 8; OS: Mac OS X 10.14.6; UI render: GL; VCL: osx; 
TinderBox: MacOSX-x86_64@49-TDF, Branch:master, Time: 2019-09-11_05:45:47
Locale: fr-FR (fr_FR.UTF-8); UI-Language: en-US
Calc: threaded

because I still see the same problem there.
Comment 10 Caolán McNamara 2019-09-11 13:05:37 UTC
No, its still unmerged in gerrit as https://gerrit.libreoffice.org/#/c/78807/
Comment 11 Xisco Faulí 2019-09-11 14:57:57 UTC
(In reply to Caolán McNamara from comment #10)
> No, its still unmerged in gerrit as https://gerrit.libreoffice.org/#/c/78807/

it fixes the issue. Verified locally
Comment 12 Commit Notification 2019-09-11 14:59:30 UTC
Caolán McNamara committed a patch related to this issue.
It has been pushed to "libreoffice-6-3":

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

Resolves: tdf#127343 show expanders in Add Tables or Query dialog

It will be available in 6.3.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 13 Commit Notification 2019-09-11 14:59:39 UTC
Caolán McNamara committed a patch related to this issue.
It has been pushed to "master":

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

Resolves: tdf#127343 show expanders in Add Tables or Query dialog

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 14 Caolán McNamara 2019-09-11 16:20:36 UTC
Thanks for the merge
Comment 15 Alex Thurgood 2019-09-13 08:06:03 UTC
Perfect, thanks Caolán

Verified in 

Version: 6.4.0.0.alpha0+
Build ID: 005c43025ac6121074616fed7628d7acd0a68101
CPU threads: 4; OS: Mac OS X 10.14.6; UI render: default; VCL: osx; 
Locale: fr-FR (fr_FR.UTF-8); UI-Language: en-US
Calc: threaded
Comment 16 Commit Notification 2019-09-17 12:41:51 UTC
Caolán McNamara committed a patch related to this issue.
It has been pushed to "libreoffice-6-3-2":

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

Resolves: tdf#127343 show expanders in Add Tables or Query dialog

It will be available in 6.3.2.

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 17 Dan Lewis 2019-09-18 21:21:10 UTC
Created attachment 154272 [details]
screenshot for 20190917 daily build

This screenshot from yesterday's DEB daily build says it all: problem solved. Thank you to everyone who had a part in solving this problem!