Description:
Changing a disk based cluster table with explicit "STORAGE DISK" columns to a memory based table doesn't work as it doesn't change the column storage attributes.
This is ok, the error message produced could be way better though as
right now it refers to the name of the temporary copy table, not the
actual table in the ALTER statement, and that the error was due to
a column with STORAGE DISK attribute not being possible in a STORAGE
MEMORY table isn't necessarily obvious right away either (depending
on size of the table definition and the user doing the ALTER being
or not being the same that created the table in the first place)
How to repeat:
> CREATE LOGFILE GROUP lg_1
ADD UNDOFILE 'undo_1.dat'
INITIAL_SIZE 16M
UNDO_BUFFER_SIZE 2M
ENGINE NDB;
> CREATE TABLESPACE ts_1
ADD DATAFILE 'data_1.dat'
USE LOGFILE GROUP lg_1
INITIAL_SIZE 32M
ENGINE NDB;
> CREATE TABLE t1 (
id int PRIMARY KEY,
f1 int(11) STORAGE DISK,
f2 int(11) STORAGE MEMORY
) TABLESPACE ts_1 STORAGE DISK ENGINE=ndbcluster;
> ALTER TABLE t1 STORAGE MEMORY;
ERROR 1005 (HY000): Can't create table 'test.#sql-4064_2' (errno: 140)
> SHOW WARNINGS;
+-------+------+----------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------+
| Error | 1296 | Got error 755 'Invalid tablespace' from NDB |
| Error | 1005 | Can't create table 'test.#sql-4064_2' (errno: 140) |
+-------+------+----------------------------------------------------+
Suggested fix:
Check for storage type conflicts before actually creating the temporary copy table?
© 2025, Oracle Corporation and/or its affiliates
Description: Changing a disk based cluster table with explicit "STORAGE DISK" columns to a memory based table doesn't work as it doesn't change the column storage attributes. This is ok, the error message produced could be way better though as right now it refers to the name of the temporary copy table, not the actual table in the ALTER statement, and that the error was due to a column with STORAGE DISK attribute not being possible in a STORAGE MEMORY table isn't necessarily obvious right away either (depending on size of the table definition and the user doing the ALTER being or not being the same that created the table in the first place) How to repeat: > CREATE LOGFILE GROUP lg_1 ADD UNDOFILE 'undo_1.dat' INITIAL_SIZE 16M UNDO_BUFFER_SIZE 2M ENGINE NDB; > CREATE TABLESPACE ts_1 ADD DATAFILE 'data_1.dat' USE LOGFILE GROUP lg_1 INITIAL_SIZE 32M ENGINE NDB; > CREATE TABLE t1 ( id int PRIMARY KEY, f1 int(11) STORAGE DISK, f2 int(11) STORAGE MEMORY ) TABLESPACE ts_1 STORAGE DISK ENGINE=ndbcluster; > ALTER TABLE t1 STORAGE MEMORY; ERROR 1005 (HY000): Can't create table 'test.#sql-4064_2' (errno: 140) > SHOW WARNINGS; +-------+------+----------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------+ | Error | 1296 | Got error 755 'Invalid tablespace' from NDB | | Error | 1005 | Can't create table 'test.#sql-4064_2' (errno: 140) | +-------+------+----------------------------------------------------+ Suggested fix: Check for storage type conflicts before actually creating the temporary copy table?