148 lines
3.8 KiB
Plaintext
148 lines
3.8 KiB
Plaintext
--source include/have_ndb.inc
|
|
|
|
# Terminology:
|
|
# Explicit disk column : A column that has 'storage disk' definition.
|
|
# Implicit disk column : A column that has default storage and thus inherits
|
|
# its storage from the table having 'storage disk' definition.
|
|
|
|
#
|
|
# This test tests the following cases :
|
|
# Moving column data between disk and memory will be performed
|
|
# with copy algorithm
|
|
# Create a table and an index on a column having explicit and implicit disk
|
|
# columns
|
|
|
|
# Create logfile group
|
|
CREATE LOGFILE GROUP lg1
|
|
ADD UNDOFILE 'lg1_undofile.dat'
|
|
INITIAL_SIZE 1M
|
|
UNDO_BUFFER_SIZE = 1M
|
|
ENGINE=ndb;
|
|
|
|
# Create tablespace using the logfile group
|
|
CREATE TABLESPACE ts1
|
|
ADD DATAFILE 'ts1_datafile.dat'
|
|
USE LOGFILE GROUP lg1
|
|
INITIAL_SIZE 10M
|
|
ENGINE ndb;
|
|
|
|
|
|
## Move columns between memory and disk storage and
|
|
## verify whether the copy algorithm is used.
|
|
|
|
CREATE TABLE t5 (a int PRIMARY KEY, t5b int, t5c int)
|
|
ENGINE ndb;
|
|
INSERT INTO t5 VALUES (1, 1, 1);
|
|
|
|
# Moving implicit memory columns to disk will be
|
|
# an automatic copy alter (no need to specify alg=copy)
|
|
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
|
|
ALTER TABLE t5 ALGORITHM=inplace, STORAGE disk TABLESPACE ts1;
|
|
ALTER TABLE t5 STORAGE DISK TABLESPACE ts1;
|
|
|
|
#Check if the disk data is on disk
|
|
--error ER_ALTER_FILEGROUP_FAILED
|
|
ALTER TABLESPACE ts1
|
|
DROP DATAFILE 'ts1_datafile.dat';
|
|
SHOW WARNINGS;
|
|
|
|
# Moving implicit disk columns back to memory will be
|
|
# an automatic copy alter
|
|
ALTER TABLE t5 STORAGE memory;
|
|
|
|
#Check if the disk data has been moved from disk
|
|
ALTER TABLESPACE ts1
|
|
DROP DATAFILE 'ts1_datafile.dat';
|
|
SELECT * FROM t5;
|
|
|
|
DROP TABLE t5;
|
|
DROP TABLESPACE ts1;
|
|
|
|
CREATE TABLESPACE ts1
|
|
ADD DATAFILE 'ts1_datafile.dat'
|
|
USE LOGFILE GROUP lg1
|
|
INITIAL_SIZE 10M
|
|
ENGINE ndb;
|
|
|
|
# Move a memory column to disk
|
|
CREATE TABLE t5 (a int PRIMARY KEY, t5b int, t5c int)
|
|
ENGINE ndb;
|
|
INSERT INTO t5 VALUES (1, 1, 1);
|
|
|
|
ALTER TABLE t5 CHANGE t5b t5b int STORAGE disk, TABLESPACE ts1;
|
|
|
|
#Check if the disk data has been moved to disk
|
|
--error ER_ALTER_FILEGROUP_FAILED
|
|
ALTER TABLESPACE ts1
|
|
DROP DATAFILE 'ts1_datafile.dat';
|
|
SHOW WARNINGS;
|
|
|
|
# Move an explicit disk column to memory
|
|
ALTER TABLE t5 CHANGE t5b t5b int STORAGE memory;
|
|
#Check if the disk data has been moved to disk
|
|
ALTER TABLESPACE ts1
|
|
DROP DATAFILE 'ts1_datafile.dat';
|
|
SELECT * FROM t5;
|
|
|
|
DROP TABLE t5;
|
|
|
|
DROP TABLESPACE ts1;
|
|
|
|
CREATE TABLESPACE ts1
|
|
ADD DATAFILE 'ts1_datafile.dat'
|
|
USE LOGFILE GROUP lg1
|
|
INITIAL_SIZE 10M
|
|
ENGINE ndb;
|
|
|
|
|
|
## Creating an index on an explicit disk column
|
|
|
|
# Creating an index on an explicit disk column in 'create table',
|
|
# 'create index' or 'alter table add index' will fail.
|
|
|
|
# Tinyblob/text will be stored in memory irrespective of
|
|
# the storage type specified in the create/alter query
|
|
# and cannot create an index on them.
|
|
|
|
--error ER_ILLEGAL_HA_CREATE_OPTION
|
|
CREATE TABLE t5 (a int PRIMARY KEY, t5b tinyblob,
|
|
t5c tinytext, t5d int unique key STORAGE disk)
|
|
TABLESPACE ts1 ENGINE ndb;
|
|
SHOW WARNINGS;
|
|
|
|
CREATE TABLE t5 (a int PRIMARY KEY, t5b tinyblob,
|
|
t5c tinytext, t5d int STORAGE disk)
|
|
TABLESPACE ts1 ENGINE ndb;
|
|
|
|
--error ER_ILLEGAL_HA_CREATE_OPTION
|
|
CREATE INDEX 5d_i ON t5(t5d);
|
|
|
|
--error ER_ILLEGAL_HA_CREATE_OPTION
|
|
ALTER TABLE t5 ADD INDEX 5d_i (t5d);
|
|
|
|
DROP TABLE t5;
|
|
|
|
# Creating an index on an implicit disk column will pass.
|
|
# The implicit disk column will be silently converted
|
|
# to a memory column by Ndb unless the column is
|
|
# explicitly defined as a disk column. as tested above.
|
|
|
|
CREATE TABLE t5 (a int PRIMARY KEY, t5b tinyblob,
|
|
t5c tinytext, t5d int)
|
|
STORAGE disk TABLESPACE ts1 ENGINE ndb;
|
|
|
|
CREATE INDEX 5d_i ON t5(t5d);
|
|
|
|
DROP INDEX 5d_i on t5;
|
|
|
|
ALTER TABLE t5 ADD INDEX 5d_i(t5d);
|
|
|
|
# Clean up
|
|
DROP TABLE t5;
|
|
ALTER TABLESPACE ts1
|
|
DROP DATAFILE 'ts1_datafile.dat';
|
|
DROP TABLESPACE ts1;
|
|
DROP LOGFILE GROUP lg1 ENGINE=ndb;
|
|
|
|
# end test ndb_storage.
|