Created attachment 148080 [details] Seen with Tools > Relationships I use MySQL 8.0.13 as my back end and connect Base to it using MySQL(JDBC) connector 8.0.13. I create two tables which have a primary-foreign key pair. If the primary key contains any caps, then a fictitious relationship is created. Example: primary key name: Qt_id and foreign key name: Qt_id. The relationship created is between qt_id and Qt_id (primary and foreign key, resp.) I have attached three screenshots. The first one shows the tables in Tools > Relationships. The second one shows the fields which are related. The third one shows the primary-foreign key pair relationship in MySQL Workbench. (It is also version 8.0.13.)
Created attachment 148082 [details] view of opened Relations dialog showing fields that are related
Created attachment 148083 [details] Workbench showing the relationship
Guessing that the code responsible for this is to be found in RelationControl.cxx ?
From the description, it seems that a string comparison is made between the key strings, and a constraint that the two strings can not be identical is enforced. Quite why, I don't know.
(In reply to Alex Thurgood from comment #4) > From the description, it seems that a string comparison is made between the > key strings, and a constraint that the two strings can not be identical is > enforced. Quite why, I don't know. The description of the buggy behaviour, not RelationControl.cxx.
I can't understand the workbench screenshot. I'm unsure if - you create the tables and "primary-foreign key pair" with the LibreOffice UI, and the problem you report is that LibreOffice creates a relationship *additionally*, which then also shows in workbench and an SQL dump of the database. - you create the tables and "primary-foreign key pair" with another tool, but LibreOffice merely *shows* (not creates) a relationship that doesn't exist. In both cases, can you please give exact instructions to create the tables? (Click by click if done with the LibreOffice UI, and SQL script if done by another tool).
(In reply to Lionel Elie Mamane from comment #6) > I can't understand the workbench screenshot. > > I'm unsure if > - you create the tables and "primary-foreign key pair" with the LibreOffice > UI, and the problem you report is that LibreOffice creates a relationship > *additionally*, which then also shows in workbench and an SQL dump of the > database. > - you create the tables and "primary-foreign key pair" with another tool, > but LibreOffice merely *shows* (not creates) a relationship that doesn't > exist. > > In both cases, can you please give exact instructions to create the tables? > (Click by click if done with the LibreOffice UI, and SQL script if done by > another tool). The tables were created in 2012 by the latest LibreOffice. A database document file was created to connect Base (the front end) and MySQL (the back end). The tables were originally part of embedded database files. Calc was opened followed by the Data Source window. Each table was copied to its individual empty spreadsheet. Then using the Table Copy Dialogue, the tables were copied into the MySQL database file. the primary-foreign key pairs were then created in Tools > Relations including the use of the Relations dialogue. Since then I have also installed MySQL Workbench. Using this, I have made backups of the data through the years as well as later made adjustments to the tables in the MySQL file.
There is a solution to this, but it requires Workbench or the command line in MySQL itself. 1) Open the table with the foreign key in edit mode. Under Foreign keys, uncheck the box that links the primary and foreign keys. 2) Delete the foreign key. 3) Click the Apply button. 4) OK the sql required to do this. 5) Open the other table in edit mode. 6) Double click the primary key permit you to change the spelling. 7) Change all CAPITALS to lower case. 8) Click the Apply button. 9) OK the sql required to do this. 10) Create the foreign key restraint. 11) Click the Apply button, and then OK the sql required to do this. Close Workbench. 12) Close the database in Base and reopen. (This should correct the situation. Comment: Perhaps somewhere in documentation either help and/or user guides something should be written to emphasize the fact that capital letters should not be used in the name of primary keys used in foreign key restraints.
Hello Dan Lewis, A new major release of LibreOffice is available since this bug was reported. Could you please try to reproduce it with the latest version of LibreOffice from https://www.libreoffice.org/download/libreoffice-fresh/ ? I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' if the bug is still present in the latest version.
(In reply to Xisco Faulí from comment #9) > Hello Dan Lewis, > A new major release of LibreOffice is available since this bug was reported. > Could you please try to reproduce it with the latest version of LibreOffice > from https://www.libreoffice.org/download/libreoffice-fresh/ ? > I have set the bug's status to 'NEEDINFO'. Please change it back to > 'UNCONFIRMED' if the bug is still present in the latest version. I tested this problem using LO 6.2.5.2 and 6.3.0. For this, MySQL Client, Connector/J, and Server are all now updated to 8.0.17 which was recently updated. 1) First using LO 6.2.5.2: I can create a table with its primary key containing a capital letter along with a second table with both primary and foreign key containing a capital letter. Then I created a relationship using Tools > Relationships for these tables (sample1 and sample2), saved, and then closed this dialog. When I had the problem, reopening this dialog is what produced the problem. But this time, reopening the Relationships dialog did not make any changes: the relationship was exactly what it should be. Problem solved. Important point: I deleted the primary-foreign key relationship first and then the tables themselves before opening LO 6.3.0. 2) Now using LO 6.3.0 (prelease): following the same steps to create two related tables in which both primary and foreign keys contain a capital letter. It is impossible to go any farther. When I opened Tool > Relationships dialog, I got a display of all of the presently defined relationships. To create a relationship, I clicked the Add Table button. What I got was a list of some schema and some actual tables. But there were not any way of selecting a specific table from a given given schema! Thus, there is no way to define the relationship between two tables using the Add Table button. So, in essence, Tools > Relationships has a very serious problem. This is likely a new bug: the Add Tables list does not contain any dropdown lists. 3) Before I made the deletions mentioned in 1) above, I connected to MySQL using LO 6.3.0 which means I had access to the related tables. So when I opened Tools > Relationships, there was the two tables sample1 and sample2 shown with the proper relationship 1:n just as it should. Conclusion: As long as a person is using LO 6.2.5.2 (possibly earlier), using a capital letter in either of the primary or foreign key will not cause a problem. (I have not tried 6.2.5.1 yet.)
[Automated Action] NeedInfo-To-Unconfirmed
(In reply to Dan Lewis from comment #10) > 2) Now using LO 6.3.0 (prelease): following the same steps to create two > related tables in which both primary and foreign keys contain a capital > letter. It is impossible to go any farther. When I opened Tool > > Relationships dialog, I got a display of all of the presently defined > relationships. To create a relationship, I clicked the Add Table button. > What I got was a list of some schema and some actual tables. But there were > not any way of selecting a specific table from a given given schema! Thus, > there is no way to define the relationship between two tables using the Add > Table button. So, in essence, Tools > Relationships has a very serious > problem. This is likely a new bug: the Add Tables list does not contain any > dropdown lists. @Dan, please open a separate report for this behaviour.
Given Dan's reported finding in comment 10, point (1), closing as WFM