UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Mysql Workbench (8.0) Foreign Key Problem, MySQL 8.0.16    
 
   
Zaddicus
post Jul 17 2019, 06:57 AM
Post#1



Posts: 139
Joined: 3-April 19
From: Cardiff


Hi UtterAccess,

This may or may not be something you can help with but I'm having issues creating certain foreign keys for specific tables. A lot of the foreign keys create just fine however there are some tables that I must link together and I just can't do so as I don't even get given the option. When I select the dropdown all it says is "Specify Column..."

I have done some googling before I posted this and the most common clause is inconsistencies in table structure, however I don;t believe this is my issue as:

- both tables use the same chartset/collation.
- both tables are InnoDB,
- both table fields are set to BIGINT(20)
- both table fields are set to NotNull

Note: I know bigint(20) is probably very excessive however this is just more future-proofing and other bigint relationships work perfectly fine (example CompanyID on contacts table)

Anyway here are some screenshots:
(Contacts Table) - ContactID = PK
Attached File  mysqlhell1.PNG ( 52.85K )Number of downloads: 13

(ContactFlags Table) - ContactID = FK
Attached File  mysqlhell2.PNG ( 30.25K )Number of downloads: 8

Relationship window with no options...
Attached File  mysqlhell3.png ( 20.15K )Number of downloads: 9

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
DanielPineault
post Jul 17 2019, 08:01 AM
Post#2


UtterAccess VIP
Posts: 7,240
Joined: 30-June 11



Have you indexed the FK?

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
cheekybuddha
post Jul 17 2019, 08:22 AM
Post#3


UtterAccess Moderator
Posts: 12,603
Joined: 6-December 03
From: Telegraph Hill


Workbench will normally create the index for you if required.

@Castiel - perhaps check ContactID before dropping the Referenced Column list.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
Zaddicus
post Jul 17 2019, 08:40 AM
Post#4



Posts: 139
Joined: 3-April 19
From: Cardiff


Hi both, thanks for the replies

QUOTE
Have you indexed the FK?


Can confirm workbench creates the indexes when I hit apply changes (If there is the option to select a column in the dropdown) - only thing I do is set the index names to be as short as possible because it hates long names (e.g. this would be 'contacts_cflags' rather than tbl_contacts.ContactID_tbl_contactflags.ContactID)

QUOTE
perhaps check ContactID before dropping the Referenced Column list


What should I check in regards to this?

I did execute a stand-alone statement though the query window in workbench
SQL
ALTER TABLE tbl_contactflags
ADD CONSTRAINT FK_ContactID
FOREIGN KEY ( ContactID )
REFERENCES tbl_contacts ( ContactID )


Which then actually applied the FK with no issue HOWEVER, when I then went to test the index by basically deselecting the column in drop down and attempting to reapply through the usual process I got the following error:
Attached File  mysqlkeyerror.PNG ( 4.95K )Number of downloads: 2


Only thing is with this is it doesn't help due to the previously mentioned:
- both tables use the same chartset/collation.
- both tables are InnoDB,
- both table fields are set to BIGINT(20)
- both table fields are set to NotNull

My concern is although I can adapt this statement manually to do all the foreign keys, whether or not they will actually work/apply when attempting to update records or if it will return errors down the line when a user goes to enter data because I'm basically forcing a join that it doesn't like.

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
cheekybuddha
post Jul 17 2019, 08:49 AM
Post#5


UtterAccess Moderator
Posts: 12,603
Joined: 6-December 03
From: Telegraph Hill


In your third image in your original post you have not checked 'Column' (ContactID) at the point you drop down the list to view the referenced column options.

I meant check the checkbox to indicate the column to be used as FK. Then perhaps the referenced column list will populate.

--------------------


Regards,

David Marten
Go to the top of the page
 
Zaddicus
post Jul 17 2019, 09:14 AM
Post#6



Posts: 139
Joined: 3-April 19
From: Cardiff


Oh right, it won't let me check the box because there is no option in the drop down - workbench auto-checks that once a valid option has been chosen.

Did a check vs a different table with a BIGINT primary key and it actually does display as a valid choice, it seems to pick and choose what it likes.

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
cheekybuddha
post Jul 19 2019, 05:52 AM
Post#7


UtterAccess Moderator
Posts: 12,603
Joined: 6-December 03
From: Telegraph Hill


Did you get a solution to this?

If not, can you post the results of running:
CODE
SHOW CREATE TABLE tbl_contacts;
SHOW CREATE TABLE tbl_contactflags;

We can see if we can replicate the issue.

d

--------------------


Regards,

David Marten
Go to the top of the page
 
Zaddicus
post Jul 19 2019, 06:28 AM
Post#8



Posts: 139
Joined: 3-April 19
From: Cardiff


Hi again,

still having the issue, here are the create table statements - as you can see there is an FK between contacts and contactflags however I basically forced that with the SQL statement in my previous comment and looking at the below I'm not sure if it's even been indexed correctly as a result
tbl_contacts
CODE
'tbl_contacts', 'CREATE TABLE `tbl_contacts` (\n  `ContactID` bigint(20) NOT NULL AUTO_INCREMENT,\n  `Email` varchar(255) NOT NULL,\n  `FirstName` varchar(255) NOT NULL,\n  `MiddleNames` varchar(255) DEFAULT NULL,\n  `LastName` varchar(255) NOT NULL,\n  `Position` varchar(255) NOT NULL,\n  `CompanyID` bigint(20) NOT NULL,\n  `LocationID` int(11) NOT NULL COMMENT \'Contact location\',\n  `OfficeNumber` varchar(255) DEFAULT NULL,\n  `MobileNumber` varchar(255) DEFAULT NULL,\n  `TwitterHandle` varchar(255) DEFAULT NULL,\n  `LinkedIn` varchar(255) DEFAULT NULL,\n  `PAName` varchar(255) DEFAULT NULL,\n  `PAEmail` varchar(255) DEFAULT NULL,\n  `PAPhone` varchar(255) DEFAULT NULL,\n  `Source` varchar(255) DEFAULT NULL,\n  `Suppress` tinyint(1) DEFAULT NULL,\n  `TempSupp` tinyint(1) DEFAULT NULL,\n  `FailedVerification` varchar(255) DEFAULT NULL,\n  `Notes` varchar(255) DEFAULT NULL,\n  `ImportDate` datetime DEFAULT NULL,\n  PRIMARY KEY (`ContactID`),\n  UNIQUE KEY `Email_UNIQUE` (`Email`),\n  UNIQUE KEY `ContactID_UNIQUE` (`ContactID`),\n  KEY `contacts_companies_idx` (`CompanyID`) /*!80000 INVISIBLE */,\n  KEY `contacts_location_idx` (`LocationID`),\n  CONSTRAINT `contacts_companies` FOREIGN KEY (`CompanyID`) REFERENCES `tbl_lst_companies` (`CompanyID`),\n  CONSTRAINT `contacts_location` FOREIGN KEY (`LocationID`) REFERENCES `tbl_lst_location` (`LocationID`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci'


tbl_contactflags
CODE
CREATE TABLE `tbl_contactflags` (
   `ConFID` bigint(20) NOT NULL AUTO_INCREMENT,
   `FlagID` int(11) DEFAULT NULL,
   `ContactID` bigint(20) DEFAULT NULL,
   PRIMARY KEY (`ConFID`),
   UNIQUE KEY `ConFID_UNIQUE` (`ConFID`),
   KEY `contactflags_flags_idx` (`FlagID`),
   KEY `contactflags_contacts` (`ContactID`),
   CONSTRAINT `contactflags_contacts` FOREIGN KEY (`ContactID`) REFERENCES `tbl_contacts` (`ContactID`),
   CONSTRAINT `contactflags_flags` FOREIGN KEY (`FlagID`) REFERENCES `tbl_lst_flags` (`FlagID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
cheekybuddha
post Jul 19 2019, 09:02 AM
Post#9


UtterAccess Moderator
Posts: 12,603
Joined: 6-December 03
From: Telegraph Hill


Hmm...

I can't reproduce - it works OK for me.

What version of MySQL server are you running, and what version of Workbench
Attached File(s)
Attached File  20190719150233_zaddicus_mysqlwb.png ( 22.96K )Number of downloads: 4
 

--------------------


Regards,

David Marten
Go to the top of the page
 
Zaddicus
post Jul 19 2019, 09:49 AM
Post#10



Posts: 139
Joined: 3-April 19
From: Cardiff


It is a very odd error becuase it only seems to impact certain tables/foreign keys.

I'm using Workbench Version 8.0.16 build 14498383 CE
Server version: 8.0.16 MySQL Community Server - GPL

CODE
mysql> SHOW VARIABLES LIKE "%version%";
+--------------------------+------------------------------+
| Variable_name            | Value                        |
+--------------------------+------------------------------+
| immediate_server_version | 999999                       |
| innodb_version           | 8.0.16                       |
| original_server_version  | 999999                       |
| protocol_version         | 10                           |
| slave_type_conversions   |                              |
| tls_version              | TLSv1,TLSv1.1,TLSv1.2        |
| version                  | 8.0.16                       |
| version_comment          | MySQL Community Server - GPL |
| version_compile_machine  | x86_64                       |
| version_compile_os       | Win64                        |
| version_compile_zlib     | 1.2.11                       |
+--------------------------+------------------------------+


Only thing I could think of is it's configured to MySQL Server 5.5 legacy authentication due to visual studio 2019 being released but no updates on the connectors so it won't connect to workbench with the new authentication method until they get an update - but then that's only the authentication method and shouldn't have any impact on the indexes/foreign keys

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
dman
post Dec 16 2019, 05:03 AM
Post#11



Posts: 3
Joined: 16-December 19



hey,
The same exact issue where with MySql 8.0.15
Workbench 8.0.18.
Some work, some won't, now sure the reason.
Noting that I did upgrade from MySQL 5.7 to 8 and later from 8.0.x to 8.0.15.
Also, this issue happened today when I created a new table and needed to do foreign relation to an older table (which was created in the past on the older version of MySql (5.7).
Also, note that I did have duplicate Scheme (older a bit and almost identical with less data) on the same Database and it DID WORK!
Tried a few times including dropping the newly created table and recreating it, the issue is constant.
It seems that setting the Foreign key manually (Query) works. This is the current only solution for me,
Did you find a solution for this annoying bug/issue?
This post has been edited by dman: Dec 16 2019, 05:15 AM
Go to the top of the page
 
cheekybuddha
post Dec 16 2019, 09:12 AM
Post#12


UtterAccess Moderator
Posts: 12,603
Joined: 6-December 03
From: Telegraph Hill


welcome2UA.gif

Check the index and foreign key names - foreign key names must be unique across the database.

If possible please post CREATE TABLE statements for each table.

--------------------


Regards,

David Marten
Go to the top of the page
 
dman
post Dec 16 2019, 09:44 AM
Post#13



Posts: 3
Joined: 16-December 19



Thanks for your reply.

Do you mean the Index/Foreign key actual textual text name?

How can you explain that I can make the foreign key with a Query with the exact same name that won't work with MySql Workbench, seems to me like a workbench bug? btw, after doing the query, I can see the foreign key in workbench and update it normally.
At this time i can't post the CREATE TABLE statements :\
Go to the top of the page
 
cheekybuddha
post Dec 17 2019, 07:08 AM
Post#14


UtterAccess Moderator
Posts: 12,603
Joined: 6-December 03
From: Telegraph Hill


>> seems to me like a workbench <<

Quite likely, but without seeing the actual table definitions/ queries involved it's hard to say.

When you try and create the FK through the interface it will show you the SQL it will use before executing. Copy that and compare what it's producing compared with your manual query.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
dman
post Dec 17 2019, 07:13 AM
Post#15



Posts: 3
Joined: 16-December 19



in my case, it won't allow me to select (as a checkbox with column names) the Column I want to do the foreign key for.
When I try to click on the 'Column' to flag a field, it won't check it but shows empty selection popup saying 'Specify Column'.
I could never reach the query SQL window for that case.
This post has been edited by dman: Dec 17 2019, 07:14 AM
Go to the top of the page
 
cheekybuddha
post Dec 17 2019, 07:18 AM
Post#16


UtterAccess Moderator
Posts: 12,603
Joined: 6-December 03
From: Telegraph Hill


Ah, OK.

It must be having some problem populating its list of available tables/fields to offer.

If you are able to create a couple of test tables that display this [mis-]behaviour then you can post the definitions and we can tray and re-create what you see.

Otherwise it's difficult to offer any assistance. shrug.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    28th March 2020 - 04:26 PM