Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.8.5, 12.1.2
-
None
-
None
-
Can result in unexpected behaviour
Description
When an InnoDB table is created without a primary key and later altered to add a primary key on a new AUTO_INCREMENT column, subsequent DDL on that table fails with "Tablespace is missing for a table".
Behavior differs slightly by version:
11.8.5: The ALTER succeeds, but TRUNCATE TABLE fails with error 194 ("Tablespace is missing for a table") and InnoDB reports an inability to rename the .ibd file.
12.1.1: The failure occurs earlier: the ALTER TABLE ... CHANGE id ... AUTO_INCREMENT already fails with error 1025 / errno 194 ("Tablespace is missing for a table").
Creating the same final schema in a single CREATE TABLE statement works fine and TRUNCATE works repeatedly, so the bug appears tied specifically to the "create without PK → ALTER to add PK + AUTO_INCREMENT" path.
Simple set of steps to reproduce:
-- Start clean
|
DROP DATABASE IF EXISTS test_bug; |
CREATE DATABASE test_bug; |
USE test_bug; |
|
|
-- 1. Create table without primary key
|
CREATE TABLE test_table_one ( |
col1 TEXT NULL |
) ENGINE=InnoDB;
|
|
|
-- 2. Add a nullable id column, then make it PK and AUTO_INCREMENT
|
ALTER TABLE test_table_one |
ADD id BIGINT(20) NULL; |
|
|
ALTER TABLE test_table_one |
ADD PRIMARY KEY (id); |
|
|
ALTER TABLE test_table_one |
CHANGE id id BIGINT(20) NOT NULL AUTO_INCREMENT; -- Fails here on 12.1.2 |
|
|
-- 3. TRUNCATE
|
TRUNCATE TABLE test_table_one; -- Failes here on 11.8.5 |
Similarly, the issue does occur if you drop an old key and add a new one:
-- Start clean
|
DROP DATABASE IF EXISTS test_bug; |
CREATE DATABASE test_bug; |
USE test_bug; |
|
|
-- 1. Create table WITH a primary key from the start
|
CREATE TABLE test_table_one ( |
old_id BIGINT(20) NOT NULL AUTO_INCREMENT, |
col1 TEXT NULL, |
PRIMARY KEY (old_id) |
) ENGINE=InnoDB;
|
|
|
-- 2. Add a new nullable column 'id' that will become the new PK
|
ALTER TABLE test_table_one |
ADD COLUMN id BIGINT(20) NULL; |
|
|
-- 3. Remove AUTO_INCREMENT from the old PK so it can stop being a key
|
ALTER TABLE test_table_one |
MODIFY old_id BIGINT(20) NOT NULL; |
|
|
-- 4. Move the primary key to the new column
|
ALTER TABLE test_table_one |
MODIFY id BIGINT(20) NOT NULL, |
DROP PRIMARY KEY, |
ADD PRIMARY KEY (id); |
|
|
-- 5. Make the new PK column AUTO_INCREMENT
|
ALTER TABLE test_table_one |
MODIFY id BIGINT(20) NOT NULL AUTO_INCREMENT; |
|
|
-- 6. TRUNCATE after PK change
|
TRUNCATE TABLE test_table_one; |
|