HeidiSQL does not detect the engine of a table

[expired user #11126]'s profile image [expired user #11126] posted 6 years ago in General Permalink

Hello,

in my database the storage engine for all tables is InnoDB.

mysql> SHOW VARIABLES LIKE '%storage%';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| default_storage_engine     | InnoDB |
| default_tmp_storage_engine | InnoDB |
| storage_engine             | InnoDB |
+----------------------------+--------+
3 rows in set (0.04 sec)

mysql> SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA='database';
+--------+
| ENGINE |
+--------+
| InnoDB |
| InnoDB |
| InnoDB |
| InnoDB |
| InnoDB |
| InnoDB |
| InnoDB |
| InnoDB |
| InnoDB |
| InnoDB |
| InnoDB |
| InnoDB |
| InnoDB |
| InnoDB |
+--------+
14 rows in set (0.02 sec)

But when I open a table in HeidiSQL, "<Server default>" is selected under Options -> Engine. And I can't create foreign keys (that's my main problem). When I change the engine to InnoDB, I can create foreign keys. But then an alter statement is created:

ALTER TABLE `ratingscale` ENGINE=InnoDB;

When I save this statement, <Server default> is selected and foreign keys are disabled again.

So what is the problem with this "server default engine"?

kalvaro's profile image kalvaro posted 6 years ago Permalink

HeidiSQL runs a query like this:

SHOW CREATE TABLE `database`.`ratingscale`;

It's possible that the server does not include the ENGINE=InnoDB part in the CREATE TABLE statement. If that's the case, Anse will probably need more details in order to diagnose the issue (at least the exact server version) and determine if it's widespread enough to merit a workaround (often, weird things happen in really old MySQL versions).

P.S. I recall now that old versions would use TYPE=InnoDB instead of ENGINE=InnoDB

[expired user #11126]'s profile image [expired user #11126] posted 6 years ago Permalink
HeidiSQL Version 9.4.0.5191 (64 Bit)
Server version: 5.6.33-0ubuntu0.14.04.1 (Ubuntu)
SHOW CREATE TABLE `database`.`ratingscale`;
| Table       | Create Table
| ratingscale | CREATE TABLE "ratingscale" (
  "name" varchar(100) DEFAULT NULL,
  "intersteps" int(11) DEFAULT NULL,
  "reverse" tinyint(4) DEFAULT NULL,
  "id" varchar(50) NOT NULL,
  "project" varchar(50) NOT NULL,
  PRIMARY KEY ("id"),
  KEY "RATINGSCALE_PROJECT" ("project"),
  CONSTRAINT "RATINGSCALE_PROJECT" FOREIGN KEY ("project") REFERENCES "project" ("id")
) |

So yes, the create table statement doesn't include ENGINE=InnoDB. Do you know in which Mysql-Version it is included?

What else do you need?

kalvaro's profile image kalvaro posted 6 years ago Permalink

Thank you! I've been able to reproduce it. This happens with you enable the ANSI SQL mode:

set @@sql_mode = 'ANSI';

I presume such mode intentionally removes MySQL-only stuff such us the engine.

Unfortunately, I'm not part of the HeidiSQL team and I can't really help fix it. I can only offer a workaround, which is setting a different mode in your HeidiSQL sessions. I have a custom init file in most of my stored sessions:

Startup script

... where I configure MySQL to my specific needs:

SET @@SESSION.sql_mode='TRADITIONAL,NO_AUTO_VALUE_ON_ZERO';
[expired user #11126]'s profile image [expired user #11126] posted 6 years ago Permalink

Thanks for the tip. I am using ANSI because I like to keep my code compatible to other database systems. And backticks are just evil ;-)

It is a bit strange - it is working when I use the following:

SET @@SESSION.sql_mode='REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE';

But with this it does not work:

SET @@SESSION.sql_mode='ANSI';

According to the documentation (I am not allowed to post links...), both should be equivalent.

Please login to leave a reply, or register at first.