List:Falcon Storage Engine« Previous MessageNext Message »
From:John Embretsen Date:October 3 2008 1:30pm
Subject:Func. test for ONLINE ADD COLUMN
View as plain text  
Chris, others,

Attached is a first take at a functional regression test for the new
falcon feature ALTER ONLINE TABLE [...] ADD COLUMN (Worklog #4048).

Test name: falcon_online_add_column

(result file not included)

I am handing it over to you at this point because I think it is at a
stage where the developer is better suited to make the final adjustments
in the test.

Others, feel free to review and tweak (I will be on vacation for some time).

This feature is not yet enabled due to Bug#39445: "Update fails
following online add column" (and possibly other issues, I'm not sure).
There is a subtest in this test file which should be able to detect that
bug (see echo comments).

Also, which restrictions or limitations will be in place for this
feature before 6.0 beta or GA is not 100% clear at this point, so the
test will obviously need some adjustments before it is run in Pushbuild
once the feature is enabled.

If I enable ONLINE ADD COLUMN and run the test with the current code
line it fails since it does not handle the fact that adding not-null and
primary key columns is not supported. (There could be other issues as well).

Good luck ;)


Regards,


-- 
John


--source include/have_falcon.inc

#
# Test for ONLINE ADD COLUMN.
#
# ONLINE ADD/DROP was added to Falcon as part of
# Worklog 4048 - "Falcon: On-line add attribute, Falcon handler part"
# and is implicit for those statements where ONLINE functionality is supported 
# by the storage engine (and the server).
# 
# This test is supposed to be testing that:
#
#    a) ONLINE ADD COLUMN succeeds where expected.
#    b) Statements using ONLINE keyword are rejected with an appropriate error 
#       message if the storage engine does not support doing this online.
#
# Includes a subtest for Bug#39445 - Falcon: Update fails following online add column
#
# ##
# ## TODO: Is it possible to use --enable-info (# of affected rows) to verify 
# ##       implicit ONLINE ADD COLUMN? (see falcon_online_index.test)
# ##
# ## In other words, test that:
# ## c) ONLINE mode is used implicitly for some selected 
# ##    ALTER TABLE ... ADD COLUMN statements where neither ONLINE nor OFFLINE 
# ##    is specified.
# ##
#
#
# ----------------------------------------------------- #
# --- Initialisation                                --- #
# ----------------------------------------------------- #

let $engine = 'Falcon';
eval SET @@storage_engine = $engine;

--echo ####### Tests for ALTER ONLINE ADD COLUMN #######

--disable_warnings
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
DROP TABLE IF EXISTS t4;
DROP VIEW IF EXISTS v1;
--enable_warnings

# A simple table with one column
CREATE TABLE t1 (a INT);
SHOW CREATE TABLE t1;

# Populate t1 with some test data
INSERT INTO t1 VALUES (1), (2), (3), (4);

# A table with a primary key and a second non-key column
CREATE TABLE t2 (a_pk INT NOT NULL DEFAULT 0 PRIMARY KEY, b INT);

# Populate t2 with some test data.
# Column b has non-unique values.
INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (4, 3);

# A table with no initial rows
CREATE TABLE t3 (a INT);

# A table with one single row (allows adding UNIQUE, PRIMARY KEY columns)
CREATE TABLE t4 (a INT);
INSERT INTO t4 VALUES (1);

# A simple view defined on t1
CREATE VIEW test.v1 AS SELECT * FROM t1;

# ----------------------------------------------------- #
# --- Test                                          --- #
# ----------------------------------------------------- #

--echo ###------ negative tests ------###
--echo
--echo #-------- alter a non-existing table --------#
--error ER_NO_SUCH_TABLE
ALTER ONLINE TABLE notExists ADD COLUMN c1 INT;

--echo #-------- alter table on a view should fail --------#
--error ER_WRONG_OBJECT
ALTER ONLINE TABLE v1 ADD COLUMN b int;

--echo #-------- add a column that already exists --------#
--error ER_DUP_FIELDNAME
ALTER ONLINE TABLE t1 ADD COLUMN a INT;

--echo #-------- add a primary key column to a table which already has one --------#
--error ER_MULTIPLE_PRI_KEY
ALTER ONLINE TABLE t2 ADD COLUMN c_pk INT NOT NULL DEFAULT 0 PRIMARY KEY;

--echo #-------- add a not null unique column to table with multiple rows --------#
# Not supported ONLINE.
# Otherwise this would cause unique constraint conflict with default values in 
# new column.
# If offline: --error ER_DUP_ENTRY
--error ER_NOT_SUPPORTED_YET
ALTER ONLINE TABLE t2 ADD COLUMN c INT UNIQUE NOT NULL DEFAULT 0;


--echo ###------ positive tests ------###
--echo

--echo #-------- add a nullable, no-key column --------#

# Add to table with existing records
ALTER ONLINE TABLE t1 ADD COLUMN b INT;
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES (9, 9);
SELECT * FROM t1;

# Add to table with no records
ALTER ONLINE TABLE t3 ADD COLUMN b INT;
SHOW CREATE TABLE t3;
INSERT INTO t3 VALUES (1, 1);
SELECT * FROM t3;

# Cleanup of the above changes:
DELETE FROM t1 WHERE a = 9;
ALTER TABLE t1 DROP COLUMN b;
SELECT * FROM t1;
DELETE FROM t3;
ALTER TABLE t3 DROP COLUMN b;
SELECT * FROM t3;


--echo #-------- add a non-nullable column --------#

# Add to table with existing records
ALTER ONLINE TABLE t1 ADD COLUMN b INT NOT NULL;
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES (9, 9);
SELECT * FROM t1;

# Add to table with no records
ALTER ONLINE TABLE t3 ADD COLUMN b INT NOT NULL;
SHOW CREATE TABLE t3;
INSERT INTO t3 VALUES (1, 1);
SELECT * FROM t3;

# Cleanup of the above changes:
ALTER TABLE t1 DROP COLUMN b;
SELECT * FROM t1;
ALTER TABLE t3 DROP COLUMN b;
DELETE FROM t3;
SELECT * FROM t3;


--echo #-------- add a primary key column --------#

# Add to table with one existing record
# DEFAULT 0 automatically added to new column for all existing rows.
ALTER ONLINE TABLE t4 ADD COLUMN b INT DEFAULT 0 PRIMARY KEY;
SHOW CREATE TABLE t4;
INSERT INTO t4 VALUES (88, 99);
# Check that primary key is in effect.
--error ER_DUP_ENTRY
INSERT INTO t4 VALUES (99, 99);
SELECT * FROM t4;

# Add to table with no records.
ALTER ONLINE TABLE t3 ADD COLUMN b INT PRIMARY KEY;
SHOW CREATE TABLE t3;
INSERT INTO t3 VALUES (0, 1);
# Check that primary key is in effect.
--error ER_DUP_ENTRY
INSERT INTO t3 VALUES (1, 1);
SELECT * FROM t3;

# Cleanup of the above changes:
ALTER TABLE t4 DROP COLUMN b;
DELETE FROM t4 WHERE a > 1;
SELECT * FROM t4;
ALTER TABLE t3 DROP COLUMN b;
DELETE FROM t3;
SELECT * FROM t3;


--echo #-------- add a unique column --------#

# Add to table with single existing record.
ALTER ONLINE TABLE t4 ADD COLUMN b VARCHAR (20) NOT NULL UNIQUE DEFAULT 'UNDEFINED'; 
SHOW CREATE TABLE t4;
SELECT * FROM t4;
INSERT INTO t4 VALUES (99, 'Supposed 2 be unique');
# Check that unique key is in effect.
--error ER_DUP_ENTRY
INSERT INTO t4 (a) VALUES (99);
--error ER_DUP_ENTRY
INSERT INTO t4 VALUES (99, 'Supposed 2 be unique');
INSERT INTO t4 VALUES (99, 'Entirely unique');
SELECT * FROM t4;

# Add to table with no records
ALTER ONLINE TABLE t3 ADD COLUMN b VARCHAR (20) NOT NULL UNIQUE DEFAULT 'UNDEFINED'; 
SHOW CREATE TABLE t3;
INSERT INTO t3 VALUES (1, 'Supposed 2 be unique');
# Insert another unique row (gets the default value)
INSERT INTO t3 (a) VALUES (1);
# Check that unique key is in effect.
--error ER_DUP_ENTRY
INSERT INTO t3 VALUES (1, 'Supposed 2 be unique');
SELECT * FROM t3;

# Cleanup of the above changes:
ALTER TABLE t4 DROP COLUMN b;
DELETE FROM t4 WHERE a = 99;
SELECT * FROM t4;
ALTER TABLE t3 DROP COLUMN b;
DELETE FROM t3;
SELECT * FROM t3;


--echo #-------- add an AUTO_INCREMENT column --------#

# Add to table with existing records
ALTER ONLINE TABLE t1 ADD COLUMN b INT NOT NULL AUTO_INCREMENT KEY;
SHOW CREATE TABLE t1;
INSERT INTO t1 (a) VALUES (99);
INSERT INTO t1 (a) VALUES (88);
INSERT INTO t1 (a) VALUES (88);
SELECT * FROM t1;

# Add to table with no records
ALTER ONLINE TABLE t3 ADD COLUMN b INT NOT NULL AUTO_INCREMENT KEY;
SHOW CREATE TABLE t3;
INSERT INTO t3 (a) VALUES (99);
INSERT INTO t3 (a) VALUES (88);
INSERT INTO t3 (a) VALUES (88);
SELECT * FROM t3;

# Cleanup of the above changes:
ALTER TABLE t1 DROP COLUMN b;
SELECT * FROM t1;
ALTER TABLE t3 DROP COLUMN b;
DELETE FROM t3;
SELECT * FROM t3;

--echo #-------- prepared statement seeing added column? --------#
# Prepared statements created before an add column should see the added column.
PREPARE p1 FROM "SELECT * FROM t1";
EXECUTE p1;
ALTER ONLINE TABLE t1 ADD COLUMN b int;
EXECUTE p1;

--echo #-------- static view cannot see added column --------#
# If a view is defined as SELECT * on a table, new columns added to the table 
# later do not become part of the view.
SELECT * FROM v1;

# Cleanup after the previous statements:
DROP PREPARE p1;
ALTER TABLE t1 DROP COLUMN b;

--echo #-------- add multiple columns --------#
ALTER ONLINE TABLE t1 ADD COLUMN (b INT), ADD COLUMN (c INT);
DESCRIBE t1;
INSERT INTO t1 VALUES (11, 0, 100), (12, 0, 200);
SELECT * FROM t1;
DELETE FROM t1 WHERE c > 99;
ALTER ONLINE TABLE t1 ADD COLUMN (d VARCHAR(20)), ADD COLUMN (e DATE);
DESCRIBE t1;
INSERT INTO t1 VALUES (11, 0, 300, 'TestMe', '2008-10-01');
SELECT * FROM t1;
DELETE FROM t1 WHERE c > 99;

# Cleanup after previous statements:
ALTER TABLE t1 
  DROP COLUMN b, 
  DROP COLUMN c,
  DROP COLUMN d,
  DROP COLUMN e;
SELECT * FROM t1;
DESCRIBE t1;


--echo #-------- combined online add column and index --------#

ALTER ONLINE TABLE t1 
  ADD COLUMN (b INT), 
  ADD COLUMN (c INT), 
  ADD INDEX (a);

# Note: Doing the following causes server crash (2008-10-01)
# (adding index to column added in same statement) on some builds,
# regardless of engine (needs more investigation?):
#
# ALTER ONLINE TABLE t1 
#  ADD COLUMN (b INT), 
#  ADD COLUMN (c INT), 
#  ADD INDEX (a),
#  ADD INDEX (c);

DESCRIBE t1;
INSERT INTO t1 VALUES (11, 0, 100), (12, 0, 200);
SELECT * FROM t1;
DELETE FROM t1 WHERE c > 99;

--echo #-------- add multiple columns to table with index --------#
# Add multiple columns to table with index
ALTER ONLINE TABLE t1 ADD COLUMN (d VARCHAR(20)), ADD COLUMN (e DATE);
DESCRIBE t1;
INSERT INTO t1 VALUES (11, 0, 300, 'TestMe', '2008-10-01');
SELECT * FROM t1 WHERE c > 55;
DELETE FROM t1 WHERE c > 99;

# Cleanup after previous statements:
ALTER TABLE t1 
  DROP COLUMN b, 
  DROP COLUMN c,
  DROP COLUMN d,
  DROP COLUMN e,
  DROP INDEX a;
SELECT * FROM t1;
DESCRIBE t1;

--echo #-------- bug#39445: Update fails following online add column --------#
# Issue is also mentioned in Worklog#4048 (07-jul 2008).
# Error message: ERROR 1032 (HY000): Can't find record in 't2'
# To reproduce:
# Table must include an indexed column,
# ONLINE ADD COLUMN must be enabled,
# must be a multiple-column ADD.
# Key column need not be included in update query.

ALTER ONLINE TABLE t2 ADD COLUMN (c int), ADD COLUMN (d int);
SELECT * FROM t2;
UPDATE t2 SET c = b;

# Cleanup
ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;


# ----------------------------------------------------- #
# --- Check                                         --- #
# ----------------------------------------------------- #

SELECT * FROM t1;
SELECT * FROM t2;
SELECT * FROM t3;
SELECT * FROM t4;

# ----------------------------------------------------- #
# --- Final cleanup                                 --- #
# ----------------------------------------------------- #

DROP VIEW v1;
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
DROP TABLE t4;

Thread
Func. test for ONLINE ADD COLUMNJohn Embretsen3 Oct