Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

"The selected table engine (<Server default>) does not support foreign keys", even though it does. #375

Closed
tlm-2501 opened this issue Oct 24, 2018 · 7 comments

Comments

Projects
None yet
3 participants
@tlm-2501
Copy link

commented Oct 24, 2018

Steps to reproduce this issue

  1. Create a new database (if necessary) on a server with InnoDB being the default storage engine.
  2. Create a new table.
  3. Head to the "Foreign keys" tab.

Current behavior

An error message "The selected table engine (<Server default>) does not support foreign keys" appears.

Expected behavior

The error should not appear. I should be allowed to create my foreign keys.

Possible solution

Foreign key support is only present in InnoDB. It's possible to check the Support column of the SHOW ENGINES output to see if the default engine is already InnoDB, and if it is, allow foreign key creation. InnoDB has been the default in both MySQL and MariaDB for quite a while now.

Environment

  • HeidiSQL version: 9.5.0.5196
  • Database system and version: 10.1.23-MariaDB-9+deb9u1
  • Operating system: Client - Windows 10 1803, server - Debian Stretch
@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

commented Oct 24, 2018

The problem is just that the previous SHOW CREATE TABLE xyz seems to return ... ENGINE <Server default>, while HeidiSQL expects "InnoDB". Never saw that in the create code of a table, but it's surely fixable.

Could you please post the result of SHOW CREATE TABLE yourtable and of SHOW ENGINES here?

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

commented Oct 24, 2018

I assume you looked at the "Foreign keys" tab before you even saved that table? In that case we have that "Server default" in the engine dropdown.

I think a good solution to remove that "Server default", auto-select whatever engine is the default one, and show that to the user.

@tlm-2501

This comment has been minimized.

Copy link
Author

commented Oct 24, 2018

Yes, this is on an entirely new table -- I was trying to design it fully before saving. Here is the output of the commands you requested:
image
It seems to return Engine=InnoDB as one would expect it to for existing tables.

@ansgarbecker ansgarbecker added this to the v10.1 milestone Jan 3, 2019

@PowerGamer1

This comment has been minimized.

Copy link

commented May 2, 2019

When sql_mode in MySQL is set to ANSI the output of SHOW CREATE TABLE changes and no longer includes ENGINE specification for the table. Under such conditions latest version of HeidiSQL displays the "The selected table engine () does not support foreign keys" error message in the "Foreign keys" tab for an already existing table.

The older version of HeidiSQL (v. 9.4.0.5125) worked just fine under ANSI sql_mode and displayed the foreign keys correctly without any error messages.

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

commented May 28, 2019

I just removed that falsy detection of foreign key support in the table engine. Leaving it in as long as the table is being designed would have been solvable. But in ANSI mode I have no chance to detect it. Even the SHOW ENGINES result does not help here.

If you find a better working approach then please shout.

@ansgarbecker

This comment has been minimized.

Copy link
Collaborator

commented May 28, 2019

Forgot to mention, what this means for the next nightly build: now the foreign key list always stays enabled, even for engines which don't support them, e.g. MyISAM, and even CSV.

@PowerGamer1

This comment has been minimized.

Copy link

commented May 28, 2019

In MySQL you can find out what engine a table uses by querying INFORMATION_SCHEMA.TABLES. But in my opinion it is perfectly fine if foreign keys tab stays enabled always.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.