Download it now!
Bug 122437 - MySQL native connector: Creating foreign key creates a relationship to the foreign key itself
Summary: MySQL native connector: Creating foreign key creates a relationship to the fo...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.2.0.1 rc
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:6.3.0 target:6.2.1
Keywords:
Depends on:
Blocks:
 
Reported: 2019-01-03 02:45 UTC by Dan Lewis
Modified: 2019-02-08 07:54 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
It's the relationship created by the native MySQL connector (9.38 KB, image/png)
2019-01-03 02:45 UTC, Dan Lewis
Details
Actual snapshot using native MySQL connector: Tools>Relationships (36.97 KB, image/png)
2019-01-03 02:55 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-01-03 02:45:19 UTC
Created attachment 147959 [details]
It's the relationship created by the native MySQL connector

I am using the new built in native MySQL Connector. When creating a new relationship between two tables, rather than relating the foreign key of one table to the primary key of the other; a relationship is created between the foreign key and itself! This is seen in Tools > Relationship in the main database window where every primary-foreign relationship shows this.

Example: "tableA"."primary key" should be related to "tableB"."foreign key"
Instead:
          "tableB"."foreign key: is related to "tableB"."foreign key"

 The problems seems to be in the new MySQL connector. When connecting to the same database using either MySQL(JDBC) [mysql-connector-java-5.1.45, mysql-connector-java-8.0.13], Tools > Relationships shows the proper primary-foreign key pairs.

The database was created in the following way: 
1) Using the database wizard, I selected "Connect to an existing database and selected "MySQL from the drop down menu.
2) Selected "Connect directly"
3) I entered the database name and listed localhost as the server.
4) I entered my user name and checked "password required"
5) Selected "Yes, register the database for me." I checked "Open the database for editing."
Comment 1 Dan Lewis 2019-01-03 02:52:04 UTC
Comment on attachment 147959 [details]
It's the relationship created by the native MySQL connector

The title of the snapshot is wrong. It was taken using mysql-connector-java-5.1.45 for the same database.
Comment 2 Dan Lewis 2019-01-03 02:55:49 UTC
Created attachment 147960 [details]
Actual snapshot using native MySQL connector: Tools>Relationships
Comment 3 Robert Großkopf 2019-01-03 14:25:38 UTC
Tried to test this one but when opening a database by the native MySQL-connector of LO 6.2.0.1rc all tables (of every shown database) were write protected. When opening through JDBC I could add data ...
Comment 4 Alex Thurgood 2019-01-06 14:53:10 UTC
Dan :

You don't mention how you created the relations.

1) Via the mysql commqnd line or another DBMS tool ;
2) Via drag and drop using the LO relations editor
3) Via the Relations menu dialog (Add relation button).

I'm assuming via the LO GUI but it would be nice to have those details fro reproducibility.
Comment 5 Dan Lewis 2019-01-06 22:09:03 UTC
(In reply to Alex Thurgood from comment #4)
> Dan :
> 
> You don't mention how you created the relations.
> 
> 1) Via the mysql commqnd line or another DBMS tool ;
> 2) Via drag and drop using the LO relations editor
> 3) Via the Relations menu dialog (Add relation button).
> 
> I'm assuming via the LO GUI but it would be nice to have those details fro
> reproducibility.

    The relationships were created in a new database back in 2014 or earlier using whatever LibreOffice was current at the time. I have been using this database ever since upgrading LO as new versions became available. If my memory is correct, I was still using the MySQL connector extension last spring that I had created back in 2013 to connect to MySQL.
    I should also mention that at the time I filed this bug, I was using MySQL 5.7. along with MySQL Workbook 8.0.13. The relationships were working in Workbook then. However, after making sure I had a connection between Workbook and Base and had saved these relationships, I opened a Base database using the native MySQL connector. That is when I saw that the relationships were wrong.
    I have since upgraded MySQL to version 8.0.13 and also have the MySQL(JDBC) 8.0.13 driver which I can use with LO 6.2.0.1 to access MySQL with apparently no problems. (The native MySQL connector will no longer connect to the MySQL database. A different problem, I know.)
Comment 6 Commit Notification 2019-01-07 09:03:24 UTC
Tamas Bunth committed a patch related to this issue.
It has been pushed to "master":

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

tdf#122437 mysqlc: fix foreign key name query

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 7 Commit Notification 2019-01-12 11:48:09 UTC
Tamas Bunth committed a patch related to this issue.
It has been pushed to "master":

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

tdf#122437 mysqlc: consider "types" parameter in..

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 8 Commit Notification 2019-01-12 16:25:28 UTC
Tamas Bunth committed a patch related to this issue.
It has been pushed to "master":

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

tdf#122437 mysqlc: Fix foreign key references

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 9 Xisco Faulí 2019-02-04 10:16:54 UTC
Hi Tamas Bunth,
Do you think the fixes for this issue should be cherry-picked to 6-2 brach ?
Comment 10 Tamas Bunth 2019-02-04 20:27:37 UTC
(In reply to Xisco Faulí from comment #9)
> Hi Tamas Bunth,
> Do you think the fixes for this issue should be cherry-picked to 6-2 brach ?

Yes
Comment 11 Xisco Faulí 2019-02-05 14:41:41 UTC
(In reply to Tamas Bunth from comment #10)
> (In reply to Xisco Faulí from comment #9)
> > Hi Tamas Bunth,
> > Do you think the fixes for this issue should be cherry-picked to 6-2 brach ?
> 
> Yes

Patch in gerrit: https://gerrit.libreoffice.org/#/c/67416/
Comment 12 Commit Notification 2019-02-08 07:54:32 UTC
Tamas Bunth committed a patch related to this issue.
It has been pushed to "libreoffice-6-2":

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

tdf#122437: Combine commits for libreoffice-6-2

It will be available in 6.2.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.