0

I ran into a peculiar situation with MySql 8.0.18. I created some tables without specifying "Engine=InnoDB". One of them contains foreign keys to the others, and I specified a foreign key constraint with On Delete Cascade. I deleted a value from a parent table expecting the corresponding rows to be deleted from the child table, but they weren't.

I then dropped the tables and recreated them specifying "Engine=InnoDB" for each of them. I repopulated them and ran the same delete query. This time the child table rows were deleted as expected.

My question is why I need to specify "Engine=InnoDB". The MySql 8.0 documentation says "In MySQL 8.0, InnoDB is the default MySQL storage engine. Unless you have configured a different default storage engine, issuing a CREATE TABLE statement without an ENGINE clause creates an InnoDB table." But apparently I need to specify it explicitly. Is this a bug in MySql or am I missing something?

CODE SAMPLE

# Create tables

DROP TABLE IF EXISTS GROUPISSUE ;

DROP TABLE IF EXISTS ACTIONGROUP ;

DROP TABLE IF EXISTS ISSUE ;


CREATE TABLE ACTIONGROUP (
Name   CHAR(100) NOT NULL ,
Description   VARCHAR(255) ,
PRIMARY KEY (Name)
) Engine=InnoDB ;


CREATE TABLE ISSUE (
Name   CHAR(100) NOT NULL ,
Description   VARCHAR(255) ,
PRIMARY KEY (Name)
) Engine=InnoDB ;


CREATE TABLE GROUPISSUE (
GroupName   CHAR(100) ,
IssueName   CHAR(100) ,
PRIMARY KEY (GroupName, IssueName) ,
CONSTRAINT GroupName_fk
FOREIGN KEY (GroupName) REFERENCES ActionGroup (Name)
    ON DELETE CASCADE
    ON UPDATE CASCADE ,
CONSTRAINT IssueName_fk
FOREIGN KEY (IssueName) REFERENCES Issue (Name) 
    ON DELETE CASCADE
    ON UPDATE CASCADE
) Engine=InnoDB ;


# Populate tables

INSERT INTO Issue (Name, Description)
VALUES ('Ageism',''),
('Air','air quality'),
('Elections',''),
('Other',''),
('Water','') ,
('Climate change','')
;

INSERT INTO ActionGroup (Name, Description) VALUES 
('Org1', 'The first organization') ,
('Org2', 'The second organization')
;


INSERT INTO GroupIssue (GroupName, IssueName) VALUES
 ('Org1','Air') ,
 ('Org1','Elections')
;

INSERT INTO GroupIssue (GroupName, IssueName) VALUES
 ('Org2','Air') ,
 ('Org2','Elections') ,
 ('Org2','Other')
;


# Verify table contents

Select * from actiongroup ;
Select * from issue ;
Select * from groupissue ;


# Delete from child table and check results

delete from actiongroup where name = 'Org1' ;
Select * from actiongroup ;
Select * from issue ;
Select * from groupissue ;
Bill Meacham
  • 1
  • Check your MySQL configuration file. Also you can do `SHOW CREATE TABLE` to see exactly *how* a given table was created. – LSerni Feb 07 '21 at 23:11
  • Well, if you need to specify innodb when you create a table to get innodb engine, then it is not the default one for your mysql instnce. – Shadow Feb 07 '21 at 23:44
  • The configuration variable that sets the default storage engine is `default_storage_engine`. You can see the current value: `SELECT @@default_storage_engine;`. It might be set to something other than the default value of `innodb`. Edit your `my.cnf` file. Look for multiple lines that set that option, in case there's extra lines, the last one takes priority. – Bill Karwin Feb 08 '21 at 00:00

0 Answers0