select count(*) from information_schema.columns;
count(*)
#
CREATE LOGFILE GROUP lg1
ADD UNDOFILE 'undofile.dat'
INITIAL_SIZE 16M
UNDO_BUFFER_SIZE = 1M
ENGINE=MYISAM;
ERROR HY000: Table storage engine 'MyISAM' does not support the create option 'CREATE/ALTER/DROP LOGFILE GROUP'
ALTER LOGFILE GROUP lg1
ADD UNDOFILE 'undofile02.dat'
INITIAL_SIZE = 4M 
ENGINE=XYZ;
ERROR 42000: Unknown storage engine 'XYZ'
CREATE TABLESPACE ts1
ADD DATAFILE 'datafile.dat'
USE LOGFILE GROUP lg1
INITIAL_SIZE 12M
ENGINE=MYISAM;
ERROR HY000: Table storage engine 'MyISAM' does not support the create option 'CREATE TABLESPACE'
CREATE LOGFILE GROUP lg1
ADD UNDOFILE 'undofile.dat'
INITIAL_SIZE 5K
UNDO_BUFFER_SIZE = 1M
ENGINE=NDB;
ERROR HY000: Failed to create LOGFILE GROUP
SHOW WARNINGS;
Level	Code	Message
Warning	1296	Undofile size rounded down to kernel page size
Warning	1296	Got error 1516 'File too small' from NDB
Warning	1296	Failed to commit NDB schema transaction
Error	1528	Failed to create LOGFILE GROUP
CREATE LOGFILE GROUP lg1
ADD UNDOFILE 'undofile.dat'
INITIAL_SIZE 16M
UNDO_BUFFER_SIZE = 1M
ENGINE=NDB;
ALTER LOGFILE GROUP lg1
ADD UNDOFILE 'undofile02.dat'
INITIAL_SIZE = 4M 
ENGINE=NDB;
CREATE TABLESPACE ts1
ADD DATAFILE 'datafile.dat'
USE LOGFILE GROUP lg1
INITIAL_SIZE 12M
ENGINE NDB;
ALTER TABLESPACE ts1
ADD DATAFILE 'datafile02.dat'
INITIAL_SIZE = 12M;
CREATE TABLE t1
(pk1 INT NOT NULL PRIMARY KEY, b INT NOT NULL, c INT NOT NULL)
TABLESPACE ts1 STORAGE DISK
ENGINE=NDB
PARTITION BY KEY() PARTITIONS 8;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `pk1` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  PRIMARY KEY (`pk1`)
) /*!50100 TABLESPACE `ts1` STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY KEY ()
PARTITIONS 8 */
INSERT INTO t1 VALUES (0, 0, 0);
SELECT * FROM t1;
pk1	b	c
0	0	0
INSERT INTO t1 VALUES 
(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),
(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10),
(11,11,11),(12,12,12),(13,13,13),(14,14,14),(15,15,15),
(16,16,16),(17,17,17),(18,18,18),(19,19,19),(20,20,20),
(21,21,21),(22,22,22),(23,23,23),(24,24,24),(25,25,25),
(26,26,26),(27,27,27),(28,28,28),(29,29,29),(30,30,30),
(31,31,31),(32,32,32),(33,33,33),(34,34,34),(35,35,35),
(36,36,36),(37,37,37),(38,38,38),(39,39,39),(40,40,40),
(41,41,41),(42,42,42),(43,43,43),(44,44,44),(45,45,45),
(46,46,46),(47,47,47),(48,48,48),(49,49,49),(50,50,50),
(51,51,51),(52,52,52),(53,53,53),(54,54,54),(55,55,55),
(56,56,56),(57,57,57),(58,58,58),(59,59,59),(60,60,60),
(61,61,61),(62,62,62),(63,63,63),(64,64,64),(65,65,65),
(66,66,66),(67,67,67),(68,68,68),(69,69,69),(70,70,70),
(71,71,71),(72,72,72),(73,73,73),(74,74,74),(75,75,75),
(76,76,76),(77,77,77),(78,78,78),(79,79,79),(80,80,80),
(81,81,81),(82,82,82),(83,83,83),(84,84,84),(85,85,85),
(86,86,86),(87,87,87),(88,88,88),(89,89,89),(90,90,90),
(91,91,91),(92,92,92),(93,93,93),(94,94,94),(95,95,95),
(96,96,96),(97,97,97),(98,98,98),(99,99,99),(100,100,100),
(101,101,101),(102,102,102),(103,103,103),(104,104,104),(105,105,105),
(106,106,106),(107,107,107),(108,108,108),(109,109,109),(110,110,110),
(111,111,111),(112,112,112),(113,113,113),(114,114,114),(115,115,115),
(116,116,116),(117,117,117),(118,118,118),(119,119,119),(120,120,120),
(121,121,121),(122,122,122),(123,123,123),(124,124,124),(125,125,125),
(126,126,126),(127,127,127),(128,128,128),(129,129,129),(130,130,130),
(131,131,131),(132,132,132),(133,133,133),(134,134,134),(135,135,135),
(136,136,136),(137,137,137),(138,138,138),(139,139,139),(140,140,140),
(141,141,141),(142,142,142),(143,143,143),(144,144,144),(145,145,145),
(146,146,146),(147,147,147),(148,148,148),(149,149,149),(150,150,150),
(151,151,151),(152,152,152),(153,153,153),(154,154,154),(155,155,155),
(156,156,156),(157,157,157),(158,158,158),(159,159,159),(160,160,160),
(161,161,161),(162,162,162),(163,163,163),(164,164,164),(165,165,165),
(166,166,166),(167,167,167),(168,168,168),(169,169,169),(170,170,170),
(171,171,171),(172,172,172),(173,173,173),(174,174,174),(175,175,175),
(176,176,176),(177,177,177),(178,178,178),(179,179,179),(180,180,180),
(181,181,181),(182,182,182),(183,183,183),(184,184,184),(185,185,185),
(186,186,186),(187,187,187),(188,188,188),(189,189,189),(190,190,190),
(191,191,191),(192,192,192),(193,193,193),(194,194,194),(195,195,195),
(196,196,196),(197,197,197),(198,198,198),(199,199,199),(200,200,200),
(201,201,201),(202,202,202),(203,203,203),(204,204,204),(205,205,205),
(206,206,206),(207,207,207),(208,208,208),(209,209,209),(210,210,210),
(211,211,211),(212,212,212),(213,213,213),(214,214,214),(215,215,215),
(216,216,216),(217,217,217),(218,218,218),(219,219,219),(220,220,220),
(221,221,221),(222,222,222),(223,223,223),(224,224,224),(225,225,225),
(226,226,226),(227,227,227),(228,228,228),(229,229,229),(230,230,230),
(231,231,231),(232,232,232),(233,233,233),(234,234,234),(235,235,235),
(236,236,236),(237,237,237),(238,238,238),(239,239,239),(240,240,240),
(241,241,241),(242,242,242),(243,243,243),(244,244,244),(245,245,245),
(246,246,246),(247,247,247),(248,248,248),(249,249,249),(250,250,250),
(251,251,251),(252,252,252),(253,253,253),(254,254,254),(255,255,255),
(256,256,256),(257,257,257),(258,258,258),(259,259,259),(260,260,260),
(261,261,261),(262,262,262),(263,263,263),(264,264,264),(265,265,265),
(266,266,266),(267,267,267),(268,268,268),(269,269,269),(270,270,270),
(271,271,271),(272,272,272),(273,273,273),(274,274,274),(275,275,275),
(276,276,276),(277,277,277),(278,278,278),(279,279,279),(280,280,280),
(281,281,281),(282,282,282),(283,283,283),(284,284,284),(285,285,285),
(286,286,286),(287,287,287),(288,288,288),(289,289,289),(290,290,290),
(291,291,291),(292,292,292),(293,293,293),(294,294,294),(295,295,295),
(296,296,296),(297,297,297),(298,298,298),(299,299,299),(300,300,300),
(301,301,301),(302,302,302),(303,303,303),(304,304,304),(305,305,305),
(306,306,306),(307,307,307),(308,308,308),(309,309,309),(310,310,310),
(311,311,311),(312,312,312),(313,313,313),(314,314,314),(315,315,315),
(316,316,316),(317,317,317),(318,318,318),(319,319,319),(320,320,320),
(321,321,321),(322,322,322),(323,323,323),(324,324,324),(325,325,325),
(326,326,326),(327,327,327),(328,328,328),(329,329,329),(330,330,330),
(331,331,331),(332,332,332),(333,333,333),(334,334,334),(335,335,335),
(336,336,336),(337,337,337),(338,338,338),(339,339,339),(340,340,340),
(341,341,341),(342,342,342),(343,343,343),(344,344,344),(345,345,345),
(346,346,346),(347,347,347),(348,348,348),(349,349,349),(350,350,350),
(351,351,351),(352,352,352),(353,353,353),(354,354,354),(355,355,355),
(356,356,356),(357,357,357),(358,358,358),(359,359,359),(360,360,360),
(361,361,361),(362,362,362),(363,363,363),(364,364,364),(365,365,365),
(366,366,366),(367,367,367),(368,368,368),(369,369,369),(370,370,370),
(371,371,371),(372,372,372),(373,373,373),(374,374,374),(375,375,375),
(376,376,376),(377,377,377),(378,378,378),(379,379,379),(380,380,380),
(381,381,381),(382,382,382),(383,383,383),(384,384,384),(385,385,385),
(386,386,386),(387,387,387),(388,388,388),(389,389,389),(390,390,390),
(391,391,391),(392,392,392),(393,393,393),(394,394,394),(395,395,395),
(396,396,396),(397,397,397),(398,398,398),(399,399,399),(400,400,400),
(401,401,401),(402,402,402),(403,403,403),(404,404,404),(405,405,405),
(406,406,406),(407,407,407),(408,408,408),(409,409,409),(410,410,410),
(411,411,411),(412,412,412),(413,413,413),(414,414,414),(415,415,415),
(416,416,416),(417,417,417),(418,418,418),(419,419,419),(420,420,420),
(421,421,421),(422,422,422),(423,423,423),(424,424,424),(425,425,425),
(426,426,426),(427,427,427),(428,428,428),(429,429,429),(430,430,430),
(431,431,431),(432,432,432),(433,433,433),(434,434,434),(435,435,435),
(436,436,436),(437,437,437),(438,438,438),(439,439,439),(440,440,440),
(441,441,441),(442,442,442),(443,443,443),(444,444,444),(445,445,445),
(446,446,446),(447,447,447),(448,448,448),(449,449,449),(450,450,450),
(451,451,451),(452,452,452),(453,453,453),(454,454,454),(455,455,455),
(456,456,456),(457,457,457),(458,458,458),(459,459,459),(460,460,460),
(461,461,461),(462,462,462),(463,463,463),(464,464,464),(465,465,465),
(466,466,466),(467,467,467),(468,468,468),(469,469,469),(470,470,470),
(471,471,471),(472,472,472),(473,473,473),(474,474,474),(475,475,475),
(476,476,476),(477,477,477),(478,478,478),(479,479,479),(480,480,480),
(481,481,481),(482,482,482),(483,483,483),(484,484,484),(485,485,485),
(486,486,486),(487,487,487),(488,488,488),(489,489,489),(490,490,490),
(491,491,491),(492,492,492),(493,493,493),(494,494,494),(495,495,495),
(496,496,496),(497,497,497),(498,498,498),(499,499,499),(500, 500, 500);
SELECT COUNT(*) FROM t1;
COUNT(*)
501
SET SESSION information_schema_stats_expiry=0;
SELECT table_name,
SUM(table_rows),
SUM(data_length),
MAX(max_data_length),
SUM(data_free)
FROM INFORMATION_SCHEMA.partitions
WHERE table_name='t1'
GROUP BY table_name;
TABLE_NAME	SUM(table_rows)	SUM(data_length)	MAX(max_data_length)	SUM(data_free)
t1	501	262144	1048576	8345820
SELECT table_name, table_rows, data_length, max_data_length, data_free
FROM information_schema.tables
WHERE table_name='t1';
TABLE_NAME	TABLE_ROWS	DATA_LENGTH	MAX_DATA_LENGTH	DATA_FREE
t1	501	262144	8388608	8345820
CREATE LOGFILE GROUP lg2
ADD UNDOFILE 'x.dat'
INITIAL_SIZE 10y
ENGINE = NDB;
ERROR HY000: A size parameter was incorrectly specified, either number or on the form 10M
CREATE LOGFILE GROUP lg2
ADD UNDOFILE 'x.dat'
INITIAL_SIZE 10MB
ENGINE = NDB;
ERROR HY000: A size parameter was incorrectly specified, either number or on the form 10M
CREATE LOGFILE GROUP lg2
ADD UNDOFILE 'x.dat'
INITIAL_SIZE 10 MB
ENGINE = NDB;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MB
ENGINE = NDB' at line 3
CREATE LOGFILE GROUP lg2
ADD UNDOFILE 'x.dat'
INITIAL_SIZE 10 M
ENGINE = NDB;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'M
ENGINE = NDB' at line 3
CREATE LOGFILE GROUP lg2
ADD UNDOFILE 'x.dat'
INITIAL_SIZE 1000000000000K
ENGINE = NDB;
ERROR HY000: The size number was correct but we don't allow the digit part to be more than 2 billion
DROP TABLE t1;
create table t1(
col1 int unsigned not null primary key,
col2 int unsigned not null,
col3 varchar(8176),
col4 int)
tablespace ts1 storage disk
charset = latin1 engine=ndbcluster;
ERROR HY000: Can't create table 't1'
show warnings;
Level	Code	Message
Warning	1296	Got error 851 'Fixed-size column offset exceeded max.Use VARCHAR or COLUMN_FORMAT DYNAMIC for memory-stored columns' from NDB
Warning	1296	Failed to commit NDB schema transaction
Error	1005	Can't create table 't1'
CREATE TABLE t1 (a bigint primary key, b bigint storage disk)
ENGINE ndb
COMMENT="NDB_TABLE=NOLOGGING=1"
TABLESPACE ts1 STORAGE DISK;
ERROR HY000: Table storage engine 'ndbcluster' does not support the create option 'NOLOGGING=1 on table with fields using STORAGE DISK'
ALTER TABLESPACE ts1
DROP DATAFILE 'datafile.dat';
ALTER TABLESPACE ts1
DROP DATAFILE 'datafile02.dat';
DROP TABLESPACE ts1;
#
# Bug #21699 DROP last DATAFILE from TABLESPACE even though there are still tables in it
#
# Create the tablespace
create tablespace ts2
add datafile 'datafile2_1.dat'
use logfile group lg1
initial_size 12M
engine ndb;
# Create tables on the tablespace
CREATE TABLE City (
ID int(11) NOT NULL AUTO_INCREMENT,
Name char(35) NOT NULL,
CountryCode char(3) NOT NULL,
District char(20) NOT NULL,
Population int(11) NOT NULL,
PRIMARY KEY (ID)
) ENGINE=ndbcluster
tablespace ts2
storage disk;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
create table t1 (a int primary key, b int)
engine = ndbcluster tablespace ts2 storage disk;
insert into t1 values (1,1);
# Attempting to drop the datafile should fail now
alter tablespace ts2
drop datafile 'datafile2_1.dat';
ERROR HY000: Failed to alter: DROP DATAFILE FAILED
show warnings;
Level	Code	Message
Warning	1296	Got error 770 'Cant drop file, file is used' from NDB
Warning	1296	Failed to commit NDB schema transaction
Error	1533	Failed to alter: DROP DATAFILE FAILED
drop table t1;
alter tablespace ts2
drop datafile 'datafile2_1.dat';
# Inserting into the table should now fail with error : NoDatafile in tablespace
insert
into City (Name,CountryCode,District,Population)
values ('BeiJing','CN','Beijing',2000);
ERROR HY000: Got error 1602 'No datafile in tablespace' from NDBCLUSTER
# Dropping tablsepace before dropping table should fail
drop tablespace ts2;
ERROR HY000: Tablespace `ts2` is not empty.
# Cleanup
drop table City;
drop tablespace ts2;
###########################
# Test update of mm/dd part
###########################
# Create the tablespaces
CREATE TABLESPACE ts1
ADD DATAFILE 'datafile.dat'
USE LOGFILE GROUP lg1
INITIAL_SIZE 8M
ENGINE NDB;
# Create the tables with mm/dd parts and run tests
CREATE TABLE t1 (a INT PRIMARY KEY, b CHAR(4) NOT NULL, c CHAR(4) NOT NULL, KEY(b)) TABLESPACE ts1 STORAGE DISK ENGINE = NDB;
INSERT INTO t1 VALUES (1,'1','1'), (2,'2','2'), (3,'3','3');
BEGIN;
UPDATE t1 SET b = '2' WHERE a = 1;
SELECT b FROM t1 WHERE a = 1;
b
2
SELECT * FROM t1 WHERE a = 1;
a	b	c
1	2	1
UPDATE t1 SET c = '2' WHERE a = 1;
SELECT b FROM t1 WHERE a = 1;
b
2
SELECT * FROM t1 WHERE a = 1;
a	b	c
1	2	2
UPDATE t1 SET b = '3' WHERE a = 1;
SELECT b FROM t1 WHERE a = 1;
b
3
SELECT * FROM t1 WHERE a = 1;
a	b	c
1	3	2
COMMIT;
SELECT * FROM t1 ORDER BY 1;
a	b	c
1	3	2
2	2	2
3	3	3
BEGIN;
UPDATE t1 SET c = '3' WHERE a = 1;
SELECT b FROM t1 WHERE a = 1;
b
3
SELECT * FROM t1 WHERE a = 1;
a	b	c
1	3	3
UPDATE t1 SET b = '4' WHERE a = 1;
SELECT b FROM t1 WHERE a = 1;
b
4
SELECT * FROM t1 WHERE a = 1;
a	b	c
1	4	3
UPDATE t1 SET c = '4' WHERE a = 1;
SELECT b FROM t1 WHERE a = 1;
b
4
SELECT * FROM t1 WHERE a = 1;
a	b	c
1	4	4
COMMIT;
SELECT * FROM t1 ORDER BY 1;
a	b	c
1	4	4
2	2	2
3	3	3
UPDATE t1 SET b = '5' WHERE a = 1;
SELECT * FROM t1 ORDER BY 1;
a	b	c
1	5	4
2	2	2
3	3	3
UPDATE t1 SET b = '6' WHERE b = '5';
SELECT * FROM t1 ORDER BY 1;
a	b	c
1	6	4
2	2	2
3	3	3
UPDATE t1 SET b = '7'WHERE c = '4';
SELECT * FROM t1 ORDER BY 1;
a	b	c
1	7	4
2	2	2
3	3	3
UPDATE t1 SET c = '5' WHERE a = 1;
SELECT * FROM t1 ORDER BY 1;
a	b	c
1	7	5
2	2	2
3	3	3
UPDATE t1 SET c = '6' WHERE b = '7';
SELECT * FROM t1 ORDER BY 1;
a	b	c
1	7	6
2	2	2
3	3	3
UPDATE t1 SET c = '7' WHERE c = '6';
SELECT * FROM t1 ORDER BY 1;
a	b	c
1	7	7
2	2	2
3	3	3
DROP TABLE t1;
CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(4) NOT NULL, c CHAR(4) NOT NULL, KEY(b)) TABLESPACE ts1 STORAGE DISK ENGINE NDB;
INSERT INTO t1 VALUE (1,'1','1'), (2,'2','2'), (3,'3','3');
BEGIN;
UPDATE t1 SET b = '2' WHERE a = 1;
SELECT b FROM t1 WHERE a = 1;
b
2
SELECT * FROM t1 WHERE a = 1;
a	b	c
1	2	1
UPDATE t1 SET c = '2' WHERE a = 1;
SELECT b FROM t1 WHERE a = 1;
b
2
SELECT * FROM t1 WHERE a = 1;
a	b	c
1	2	2
UPDATE t1 SET b = '3' WHERE a = 1;
SELECT b FROM t1 WHERE a = 1;
b
3
SELECT * FROM t1 WHERE a = 1;
a	b	c
1	3	2
COMMIT;
SELECT * FROM t1 ORDER BY 1;
a	b	c
1	3	2
2	2	2
3	3	3
BEGIN;
UPDATE t1 SET c = '3' WHERE a = 1;
SELECT b FROM t1 WHERE a = 1;
b
3
SELECT * FROM t1 WHERE a = 1;
a	b	c
1	3	3
UPDATE t1 SET b = '4' WHERE a = 1;
SELECT b FROM t1 WHERE a = 1;
b
4
SELECT * FROM t1 WHERE a = 1;
a	b	c
1	4	3
UPDATE t1 SET c = '4' WHERE a = 1;
SELECT b FROM t1 WHERE a = 1;
b
4
SELECT * FROM t1 WHERE a = 1;
a	b	c
1	4	4
COMMIT;
SELECT * FROM t1 ORDER BY 1;
a	b	c
1	4	4
2	2	2
3	3	3
UPDATE t1 SET b = '5' WHERE a = 1;
SELECT * FROM t1 ORDER BY 1;
a	b	c
1	5	4
2	2	2
3	3	3
UPDATE t1 SET b = '6' WHERE b = '5';
SELECT * FROM t1 ORDER BY 1;
a	b	c
1	6	4
2	2	2
3	3	3
UPDATE t1 SET b = '7' WHERE c = '4';
SELECT * FROM t1 ORDER BY 1;
a	b	c
1	7	4
2	2	2
3	3	3
UPDATE t1 SET c = '5' WHERE a = 1;
SELECT * FROM t1 ORDER BY 1;
a	b	c
1	7	5
2	2	2
3	3	3
UPDATE t1 SET c = '6' WHERE b = '7';
SELECT * FROM t1 ORDER BY 1;
a	b	c
1	7	6
2	2	2
3	3	3
UPDATE t1 SET c = '7' WHERE c = '6';
SELECT * FROM t1 ORDER BY 1;
a	b	c
1	7	7
2	2	2
3	3	3
DELETE FROM t1;
INSERT INTO t1 VALUES (3,'1','1');
BEGIN;
UPDATE t1 SET b = b + 2 WHERE A = 3;
DELETE FROM t1 WHERE A = 3;
INSERT INTO t1 VALUES (3,'0','0');
COMMIT;
SELECT * from t1 ORDER BY 1;
a	b	c
3	0	0
DROP TABLE t1;
# Cleanup tablespace
ALTER TABLESPACE ts1
DROP DATAFILE 'datafile.dat';
DROP TABLESPACE ts1;
########################
# Test for blobs...
########################
# Create the tablespace
CREATE TABLESPACE ts1
ADD DATAFILE 'datafile.dat'
USE LOGFILE GROUP lg1
INITIAL_SIZE 12M
ENGINE NDB;
# Create a table with blob column on it
CREATE TABLE t1 (
a INT NOT NULL PRIMARY KEY,
b TEXT NOT NULL
) TABLESPACE ts1 STORAGE DISK ENGINE=NDBCLUSTER;
# Generate the blob values to be inserted
set @x0 = '01234567012345670123456701234567';
set @x0 = concat(@x0,@x0,@x0,@x0,@x0,@x0,@x0,@x0);
set @b1 = 'b1';
set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
set @b1 = concat(@b1,@x0);
set @b2 = 'b2';
set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);
# Run the tests
INSERT INTO t1 VALUES(1,@b1);
INSERT INTO t1 VALUES(2,@b2);
SELECT a,length(b),substr(b,1+2*900,2) FROM t1 WHERE a=1;
a	length(b)	substr(b,1+2*900,2)
1	2256	b1
SELECT a,length(b),substr(b,1+2*9000,2) FROM t1 WHERE a=2;
a	length(b)	substr(b,1+2*9000,2)
2	20000	b2
UPDATE t1 SET b=@b2 WHERE a=1;
UPDATE t1 SET b=@b1 WHERE a=2;
SELECT a,length(b),substr(b,1+2*9000,2) FROM t1 WHERE a=1;
a	length(b)	substr(b,1+2*9000,2)
1	20000	b2
SELECT a,length(b),substr(b,1+2*900,2) FROM t1 WHERE a=2;
a	length(b)	substr(b,1+2*900,2)
2	2256	b1
UPDATE t1 SET b=concat(b,b) WHERE a=1;
UPDATE t1 SET b=concat(b,b) WHERE a=2;
SELECT a,length(b),substr(b,1+4*9000,2) FROM t1 WHERE a=1;
a	length(b)	substr(b,1+4*9000,2)
1	40000	b2
SELECT a,length(b),substr(b,1+4*900,2) FROM t1 WHERE a=2;
a	length(b)	substr(b,1+4*900,2)
2	4512	b1
DELETE FROM t1 WHERE a=1;
DELETE FROM t1 WHERE a=2;
SELECT COUNT(*) FROM t1;
COUNT(*)
0
# Cleanup
DROP TABLE t1;
ALTER TABLESPACE ts1
DROP DATAFILE 'datafile.dat';
DROP TABLESPACE ts1;
#
# Bug#20612 INS-DEL bug in TUP (not pgman bug)
# found via disk data assert but is not pgman or disk data related
#
#Setup - Create tablespaces and table
CREATE TABLESPACE ts1
ADD DATAFILE 'datafile.dat'
USE LOGFILE GROUP lg1
INITIAL_SIZE 8M
ENGINE NDB;
CREATE TABLE t1 (
a int NOT NULL,
b varchar(4000) charset latin1,  -- must use 2 pages undo
PRIMARY KEY using hash (a)
)
TABLESPACE ts1 STORAGE DISK ENGINE=NDBCLUSTER;
# Run the tests
set autocommit = 0;
insert into t1 values(0,'x');
insert into t1 values(1,'x');
insert into t1 values(2,'x');
insert into t1 values(3,'x');
insert into t1 values(4,'x');
insert into t1 values(5,'x');
insert into t1 values(6,'x');
insert into t1 values(7,'x');
insert into t1 values(8,'x');
delete from t1 where a = 0;
commit;
delete from t1;
begin;
insert into t1 values (1, 'x');
select * from t1;
a	b
1	x
rollback;
set autocommit = 1;
# Cleanup
drop table t1;
alter tablespace ts1
drop datafile 'datafile.dat';
drop tablespace ts1;
#############################
# Customer posted order by test case
# Org in ndb_dd_advance.test
#############################
# Setup
create tablespace ts1
add datafile 'datafile.dat'
use logfile group lg1
initial_size 4M
engine ndb;
create table t1 (f1 varchar(50) primary key, f2 text,f3 int) 
TABLESPACE ts1 STORAGE DISK engine=NDB;
# Run the tests
insert into t1 (f1,f2,f3)VALUES("111111","aaaaaa",1);
insert into t1 (f1,f2,f3)VALUES("222222","bbbbbb",2);
select * from t1 order by f1;
f1	f2	f3
111111	aaaaaa	1
222222	bbbbbb	2
select f1,f2 from t1 order by f2;
f1	f2
111111	aaaaaa
222222	bbbbbb
select f2 from t1 order by f2;
f2
aaaaaa
bbbbbb
select f1,f2 from t1 order by f1;
f1	f2
111111	aaaaaa
222222	bbbbbb
# Cleanup
drop table t1;
alter tablespace ts1
drop datafile 'datafile.dat';
drop tablespace ts1;
#
# bug#34118 hash index trigger and do not update any disk attr
#
# Setup - Create tablespace, table and fill it
CREATE TABLESPACE ts1
ADD DATAFILE 'datafile.dat'
USE LOGFILE GROUP lg1
INITIAL_SIZE 4M
ENGINE NDB;
CREATE TABLE t1 (
id int unsigned NOT NULL,
c1 int unsigned NOT NULL,
c2 int DEFAULT NULL,
PRIMARY KEY using hash (id),
UNIQUE KEY c1 using hash (c1))
TABLESPACE ts1 STORAGE DISK ENGINE=ndbcluster;
insert into t1 values(1, 1, 3);
insert into t1 values(2, 2, 3);
# Confirm that the update fails
update t1 set c1 = 1 where id = 2;
ERROR 23000: Can't write; duplicate key in table ''
# Cleanup
drop table t1;
ALTER TABLESPACE ts1
DROP DATAFILE 'datafile.dat';
DROP TABLESPACE ts1;
# Also drop the logfile group lg1 created at the start of this file
DROP LOGFILE GROUP lg1 
ENGINE =NDB;
=== bug#31712 ===
create logfile group lg1
add undofile 'undofile.dat'
initial_size 2M
undo_buffer_size 1M
engine ndb;
create tablespace ts1
add datafile 'datafile.dat'
use logfile group lg1
initial_size 1M
extent_size 2M
engine ndb;
ERROR HY000: Failed to create DATAFILE
show warnings;
Level	Code	Message
Warning	1296	Got error 1516 'File too small' from NDB
Warning	1296	Failed to create datafile 'datafile.dat' in NDB
Error	1528	Failed to create DATAFILE
create tablespace ts1
add datafile 'datafile.dat'
use logfile group lg1
initial_size 2M
extent_size 2M
engine ndb;
alter tablespace ts1
add datafile 'datafile02.dat'
initial_size 1M;
ERROR HY000: Failed to alter: CREATE DATAFILE FAILED
show warnings;
Level	Code	Message
Warning	1296	Got error 1516 'File too small' from NDB
Warning	1296	Failed to create datafile 'datafile02.dat' in NDB
Error	1533	Failed to alter: CREATE DATAFILE FAILED
alter tablespace ts1
drop datafile 'datafile.dat';
drop tablespace ts1;
drop logfile group lg1
engine ndb;
=== bug#31782 ===
create logfile group lg1
add undofile 'undofile.dat'
initial_size 2M
undo_buffer_size 1M
engine ndb;
create tablespace ts1
add datafile 'datafile.dat'
use logfile group lg1
initial_size 2M
extent_size 2M
engine ndb;
select tablespace_name, file_type, file_name
from information_schema.files
where engine = 'ndbcluster' and file_type = 'DATAFILE';
TABLESPACE_NAME	FILE_TYPE	FILE_NAME
ts1	DATAFILE	./datafile.dat
alter tablespace ts1
drop datafile 'datafile.dat';
select tablespace_name, file_type, file_name
from information_schema.files
where engine = 'ndbcluster' and file_type = 'DATAFILE';
TABLESPACE_NAME	FILE_TYPE	FILE_NAME
drop tablespace ts1;
drop logfile group lg1
engine ndb;
=== bug#49709 ===
create logfile group lg1
add undofile 'undofile.dat'
initial_size 3276801
undo_buffer_size 294913
engine ndb;
Warnings:
Warning	1296	Undo buffer size rounded up to kernel page size
Warning	1296	Undofile size rounded down to kernel page size
alter logfile group lg1
add undofile 'undofile02.dat'
initial_size 3309567
engine ndb;
Warnings:
Warning	1296	Undofile size rounded down to kernel page size
create tablespace ts1
add datafile 'datafile.dat'
use logfile group lg1
initial_size 3276801
extent_size 294913
engine ndb;
Warnings:
Warning	1296	Extent size rounded up to kernel page size
Warning	1296	Datafile size rounded down to extent size
alter tablespace ts1
add datafile 'datafile02.dat'
initial_size 3342335;
Warnings:
Warning	1296	Datafile size rounded up to extent size
select file_name, initial_size
from information_schema.files
where file_type = 'UNDO LOG' and file_name like '%dat'
order by file_name;
FILE_NAME	INITIAL_SIZE
./undofile.dat	3276800
./undofile02.dat	3276800
select file_name, initial_size, extent_size, total_extents, free_extents
from information_schema.files
where file_type = 'DATAFILE'
order by file_name;
FILE_NAME	INITIAL_SIZE	EXTENT_SIZE	TOTAL_EXTENTS	FREE_EXTENTS
./datafile.dat	3276800	327680	10	10
./datafile02.dat	3604480	327680	11	11
alter tablespace ts1
drop datafile 'datafile02.dat';
alter tablespace ts1
drop datafile 'datafile.dat';
drop tablespace ts1;
drop logfile group lg1
engine ndb;
set character_set_client=utf8;
Warnings:
Warning	3719	'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
create logfile group lg1
add undofile 'undo1.log'
undo_buffer_size 2M
engine=ndb;
drop logfile group lg1
engine ndb;
CREATE LOGFILE GROUP lg1
ADD UNDOFILE 'undofile.dat'
INITIAL_SIZE 16M
UNDO_BUFFER_SIZE = 128M
ENGINE=NDB;
ERROR HY000: Failed to create LOGFILE GROUP
SHOW WARNINGS;
Level	Code	Message
Warning	1296	Got error 1504 'Out of logbuffer memory(specify smaller undo_buffer_size or increase SharedGlobalMemory)' from NDB
Warning	1296	Failed to commit NDB schema transaction
Error	1528	Failed to create LOGFILE GROUP
SET SESSION information_schema_stats_expiry=default;