| 1 |
-- source include/have_ndb.inc |
| 2 |
|
| 3 |
# |
| 4 |
# Bug#30322 Server crashes on selecting from i_s.columns when cluster is running -regression |
| 5 |
# |
| 6 |
--replace_column 1 # |
| 7 |
select count(*) from information_schema.columns; |
| 8 |
|
| 9 |
# |
| 10 |
# some negative tests |
| 11 |
# - (which assumes that MyISAM does not support |
| 12 |
# tablespace or logfile group) |
| 13 |
# |
| 14 |
|
| 15 |
--error ER_ILLEGAL_HA_CREATE_OPTION |
| 16 |
CREATE LOGFILE GROUP lg1 |
| 17 |
ADD UNDOFILE 'undofile.dat' |
| 18 |
INITIAL_SIZE 16M |
| 19 |
UNDO_BUFFER_SIZE = 1M |
| 20 |
ENGINE=MYISAM; |
| 21 |
|
| 22 |
--error ER_UNKNOWN_STORAGE_ENGINE |
| 23 |
ALTER LOGFILE GROUP lg1 |
| 24 |
ADD UNDOFILE 'undofile02.dat' |
| 25 |
INITIAL_SIZE = 4M |
| 26 |
ENGINE=XYZ; |
| 27 |
|
| 28 |
--error ER_ILLEGAL_HA_CREATE_OPTION |
| 29 |
CREATE TABLESPACE ts1 |
| 30 |
ADD DATAFILE 'datafile.dat' |
| 31 |
USE LOGFILE GROUP lg1 |
| 32 |
INITIAL_SIZE 12M |
| 33 |
ENGINE=MYISAM; |
| 34 |
|
| 35 |
# |
| 36 |
# bug#29574 |
| 37 |
# |
| 38 |
--error ER_CREATE_FILEGROUP_FAILED |
| 39 |
CREATE LOGFILE GROUP lg1 |
| 40 |
ADD UNDOFILE 'undofile.dat' |
| 41 |
INITIAL_SIZE 5K |
| 42 |
UNDO_BUFFER_SIZE = 1M |
| 43 |
ENGINE=NDB; |
| 44 |
|
| 45 |
SHOW WARNINGS; |
| 46 |
|
| 47 |
################################## |
| 48 |
# Basic test of disk tables for NDB |
| 49 |
# Start by creating a logfile group |
| 50 |
################################## |
| 51 |
|
| 52 |
# Note : This logfile group is used by |
| 53 |
# most of the tests in this file |
| 54 |
CREATE LOGFILE GROUP lg1 |
| 55 |
ADD UNDOFILE 'undofile.dat' |
| 56 |
INITIAL_SIZE 16M |
| 57 |
UNDO_BUFFER_SIZE = 1M |
| 58 |
ENGINE=NDB; |
| 59 |
|
| 60 |
ALTER LOGFILE GROUP lg1 |
| 61 |
ADD UNDOFILE 'undofile02.dat' |
| 62 |
INITIAL_SIZE = 4M |
| 63 |
ENGINE=NDB; |
| 64 |
|
| 65 |
################################################### |
| 66 |
# Create a tablespace connected to the logfile group |
| 67 |
################################################### |
| 68 |
|
| 69 |
CREATE TABLESPACE ts1 |
| 70 |
ADD DATAFILE 'datafile.dat' |
| 71 |
USE LOGFILE GROUP lg1 |
| 72 |
INITIAL_SIZE 12M |
| 73 |
ENGINE NDB; |
| 74 |
|
| 75 |
ALTER TABLESPACE ts1 |
| 76 |
ADD DATAFILE 'datafile02.dat' |
| 77 |
INITIAL_SIZE = 12M; |
| 78 |
|
| 79 |
######################################## |
| 80 |
# Create a table using this tablespace |
| 81 |
# Specify number of partitions in order to make |
| 82 |
# output from INFORMATION_SCHEMA.PARTITIONS |
| 83 |
# independent of ndbd/ndbmtd and ThreadConfig. |
| 84 |
######################################## |
| 85 |
|
| 86 |
CREATE TABLE t1 |
| 87 |
(pk1 INT NOT NULL PRIMARY KEY, b INT NOT NULL, c INT NOT NULL) |
| 88 |
TABLESPACE ts1 STORAGE DISK |
| 89 |
ENGINE=NDB |
| 90 |
PARTITION BY KEY() PARTITIONS 8; |
| 91 |
|
| 92 |
SHOW CREATE TABLE t1; |
| 93 |
|
| 94 |
INSERT INTO t1 VALUES (0, 0, 0); |
| 95 |
SELECT * FROM t1; |
| 96 |
|
| 97 |
INSERT INTO t1 VALUES |
| 98 |
(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5), |
| 99 |
(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10), |
| 100 |
(11,11,11),(12,12,12),(13,13,13),(14,14,14),(15,15,15), |
| 101 |
(16,16,16),(17,17,17),(18,18,18),(19,19,19),(20,20,20), |
| 102 |
(21,21,21),(22,22,22),(23,23,23),(24,24,24),(25,25,25), |
| 103 |
(26,26,26),(27,27,27),(28,28,28),(29,29,29),(30,30,30), |
| 104 |
(31,31,31),(32,32,32),(33,33,33),(34,34,34),(35,35,35), |
| 105 |
(36,36,36),(37,37,37),(38,38,38),(39,39,39),(40,40,40), |
| 106 |
(41,41,41),(42,42,42),(43,43,43),(44,44,44),(45,45,45), |
| 107 |
(46,46,46),(47,47,47),(48,48,48),(49,49,49),(50,50,50), |
| 108 |
(51,51,51),(52,52,52),(53,53,53),(54,54,54),(55,55,55), |
| 109 |
(56,56,56),(57,57,57),(58,58,58),(59,59,59),(60,60,60), |
| 110 |
(61,61,61),(62,62,62),(63,63,63),(64,64,64),(65,65,65), |
| 111 |
(66,66,66),(67,67,67),(68,68,68),(69,69,69),(70,70,70), |
| 112 |
(71,71,71),(72,72,72),(73,73,73),(74,74,74),(75,75,75), |
| 113 |
(76,76,76),(77,77,77),(78,78,78),(79,79,79),(80,80,80), |
| 114 |
(81,81,81),(82,82,82),(83,83,83),(84,84,84),(85,85,85), |
| 115 |
(86,86,86),(87,87,87),(88,88,88),(89,89,89),(90,90,90), |
| 116 |
(91,91,91),(92,92,92),(93,93,93),(94,94,94),(95,95,95), |
| 117 |
(96,96,96),(97,97,97),(98,98,98),(99,99,99),(100,100,100), |
| 118 |
(101,101,101),(102,102,102),(103,103,103),(104,104,104),(105,105,105), |
| 119 |
(106,106,106),(107,107,107),(108,108,108),(109,109,109),(110,110,110), |
| 120 |
(111,111,111),(112,112,112),(113,113,113),(114,114,114),(115,115,115), |
| 121 |
(116,116,116),(117,117,117),(118,118,118),(119,119,119),(120,120,120), |
| 122 |
(121,121,121),(122,122,122),(123,123,123),(124,124,124),(125,125,125), |
| 123 |
(126,126,126),(127,127,127),(128,128,128),(129,129,129),(130,130,130), |
| 124 |
(131,131,131),(132,132,132),(133,133,133),(134,134,134),(135,135,135), |
| 125 |
(136,136,136),(137,137,137),(138,138,138),(139,139,139),(140,140,140), |
| 126 |
(141,141,141),(142,142,142),(143,143,143),(144,144,144),(145,145,145), |
| 127 |
(146,146,146),(147,147,147),(148,148,148),(149,149,149),(150,150,150), |
| 128 |
(151,151,151),(152,152,152),(153,153,153),(154,154,154),(155,155,155), |
| 129 |
(156,156,156),(157,157,157),(158,158,158),(159,159,159),(160,160,160), |
| 130 |
(161,161,161),(162,162,162),(163,163,163),(164,164,164),(165,165,165), |
| 131 |
(166,166,166),(167,167,167),(168,168,168),(169,169,169),(170,170,170), |
| 132 |
(171,171,171),(172,172,172),(173,173,173),(174,174,174),(175,175,175), |
| 133 |
(176,176,176),(177,177,177),(178,178,178),(179,179,179),(180,180,180), |
| 134 |
(181,181,181),(182,182,182),(183,183,183),(184,184,184),(185,185,185), |
| 135 |
(186,186,186),(187,187,187),(188,188,188),(189,189,189),(190,190,190), |
| 136 |
(191,191,191),(192,192,192),(193,193,193),(194,194,194),(195,195,195), |
| 137 |
(196,196,196),(197,197,197),(198,198,198),(199,199,199),(200,200,200), |
| 138 |
(201,201,201),(202,202,202),(203,203,203),(204,204,204),(205,205,205), |
| 139 |
(206,206,206),(207,207,207),(208,208,208),(209,209,209),(210,210,210), |
| 140 |
(211,211,211),(212,212,212),(213,213,213),(214,214,214),(215,215,215), |
| 141 |
(216,216,216),(217,217,217),(218,218,218),(219,219,219),(220,220,220), |
| 142 |
(221,221,221),(222,222,222),(223,223,223),(224,224,224),(225,225,225), |
| 143 |
(226,226,226),(227,227,227),(228,228,228),(229,229,229),(230,230,230), |
| 144 |
(231,231,231),(232,232,232),(233,233,233),(234,234,234),(235,235,235), |
| 145 |
(236,236,236),(237,237,237),(238,238,238),(239,239,239),(240,240,240), |
| 146 |
(241,241,241),(242,242,242),(243,243,243),(244,244,244),(245,245,245), |
| 147 |
(246,246,246),(247,247,247),(248,248,248),(249,249,249),(250,250,250), |
| 148 |
(251,251,251),(252,252,252),(253,253,253),(254,254,254),(255,255,255), |
| 149 |
(256,256,256),(257,257,257),(258,258,258),(259,259,259),(260,260,260), |
| 150 |
(261,261,261),(262,262,262),(263,263,263),(264,264,264),(265,265,265), |
| 151 |
(266,266,266),(267,267,267),(268,268,268),(269,269,269),(270,270,270), |
| 152 |
(271,271,271),(272,272,272),(273,273,273),(274,274,274),(275,275,275), |
| 153 |
(276,276,276),(277,277,277),(278,278,278),(279,279,279),(280,280,280), |
| 154 |
(281,281,281),(282,282,282),(283,283,283),(284,284,284),(285,285,285), |
| 155 |
(286,286,286),(287,287,287),(288,288,288),(289,289,289),(290,290,290), |
| 156 |
(291,291,291),(292,292,292),(293,293,293),(294,294,294),(295,295,295), |
| 157 |
(296,296,296),(297,297,297),(298,298,298),(299,299,299),(300,300,300), |
| 158 |
(301,301,301),(302,302,302),(303,303,303),(304,304,304),(305,305,305), |
| 159 |
(306,306,306),(307,307,307),(308,308,308),(309,309,309),(310,310,310), |
| 160 |
(311,311,311),(312,312,312),(313,313,313),(314,314,314),(315,315,315), |
| 161 |
(316,316,316),(317,317,317),(318,318,318),(319,319,319),(320,320,320), |
| 162 |
(321,321,321),(322,322,322),(323,323,323),(324,324,324),(325,325,325), |
| 163 |
(326,326,326),(327,327,327),(328,328,328),(329,329,329),(330,330,330), |
| 164 |
(331,331,331),(332,332,332),(333,333,333),(334,334,334),(335,335,335), |
| 165 |
(336,336,336),(337,337,337),(338,338,338),(339,339,339),(340,340,340), |
| 166 |
(341,341,341),(342,342,342),(343,343,343),(344,344,344),(345,345,345), |
| 167 |
(346,346,346),(347,347,347),(348,348,348),(349,349,349),(350,350,350), |
| 168 |
(351,351,351),(352,352,352),(353,353,353),(354,354,354),(355,355,355), |
| 169 |
(356,356,356),(357,357,357),(358,358,358),(359,359,359),(360,360,360), |
| 170 |
(361,361,361),(362,362,362),(363,363,363),(364,364,364),(365,365,365), |
| 171 |
(366,366,366),(367,367,367),(368,368,368),(369,369,369),(370,370,370), |
| 172 |
(371,371,371),(372,372,372),(373,373,373),(374,374,374),(375,375,375), |
| 173 |
(376,376,376),(377,377,377),(378,378,378),(379,379,379),(380,380,380), |
| 174 |
(381,381,381),(382,382,382),(383,383,383),(384,384,384),(385,385,385), |
| 175 |
(386,386,386),(387,387,387),(388,388,388),(389,389,389),(390,390,390), |
| 176 |
(391,391,391),(392,392,392),(393,393,393),(394,394,394),(395,395,395), |
| 177 |
(396,396,396),(397,397,397),(398,398,398),(399,399,399),(400,400,400), |
| 178 |
(401,401,401),(402,402,402),(403,403,403),(404,404,404),(405,405,405), |
| 179 |
(406,406,406),(407,407,407),(408,408,408),(409,409,409),(410,410,410), |
| 180 |
(411,411,411),(412,412,412),(413,413,413),(414,414,414),(415,415,415), |
| 181 |
(416,416,416),(417,417,417),(418,418,418),(419,419,419),(420,420,420), |
| 182 |
(421,421,421),(422,422,422),(423,423,423),(424,424,424),(425,425,425), |
| 183 |
(426,426,426),(427,427,427),(428,428,428),(429,429,429),(430,430,430), |
| 184 |
(431,431,431),(432,432,432),(433,433,433),(434,434,434),(435,435,435), |
| 185 |
(436,436,436),(437,437,437),(438,438,438),(439,439,439),(440,440,440), |
| 186 |
(441,441,441),(442,442,442),(443,443,443),(444,444,444),(445,445,445), |
| 187 |
(446,446,446),(447,447,447),(448,448,448),(449,449,449),(450,450,450), |
| 188 |
(451,451,451),(452,452,452),(453,453,453),(454,454,454),(455,455,455), |
| 189 |
(456,456,456),(457,457,457),(458,458,458),(459,459,459),(460,460,460), |
| 190 |
(461,461,461),(462,462,462),(463,463,463),(464,464,464),(465,465,465), |
| 191 |
(466,466,466),(467,467,467),(468,468,468),(469,469,469),(470,470,470), |
| 192 |
(471,471,471),(472,472,472),(473,473,473),(474,474,474),(475,475,475), |
| 193 |
(476,476,476),(477,477,477),(478,478,478),(479,479,479),(480,480,480), |
| 194 |
(481,481,481),(482,482,482),(483,483,483),(484,484,484),(485,485,485), |
| 195 |
(486,486,486),(487,487,487),(488,488,488),(489,489,489),(490,490,490), |
| 196 |
(491,491,491),(492,492,492),(493,493,493),(494,494,494),(495,495,495), |
| 197 |
(496,496,496),(497,497,497),(498,498,498),(499,499,499),(500, 500, 500); |
| 198 |
|
| 199 |
SELECT COUNT(*) FROM t1; |
| 200 |
|
| 201 |
######################################## |
| 202 |
# WL#5568: Check disk space usage. |
| 203 |
######################################## |
| 204 |
|
| 205 |
# Turn off caching for information_schema, otherwise values |
| 206 |
# will be cached and only updated after ANALYZE TABLE |
| 207 |
SET SESSION information_schema_stats_expiry=0; |
| 208 |
|
| 209 |
SELECT table_name, |
| 210 |
SUM(table_rows), |
| 211 |
SUM(data_length), |
| 212 |
MAX(max_data_length), |
| 213 |
SUM(data_free) |
| 214 |
FROM INFORMATION_SCHEMA.partitions |
| 215 |
WHERE table_name='t1' |
| 216 |
GROUP BY table_name; |
| 217 |
|
| 218 |
SELECT table_name, table_rows, data_length, max_data_length, data_free |
| 219 |
FROM information_schema.tables |
| 220 |
WHERE table_name='t1'; |
| 221 |
|
| 222 |
#################################### |
| 223 |
# Test error cases with size numbers |
| 224 |
#################################### |
| 225 |
--error ER_WRONG_SIZE_NUMBER |
| 226 |
CREATE LOGFILE GROUP lg2 |
| 227 |
ADD UNDOFILE 'x.dat' |
| 228 |
INITIAL_SIZE 10y |
| 229 |
ENGINE = NDB; |
| 230 |
|
| 231 |
--error ER_WRONG_SIZE_NUMBER |
| 232 |
CREATE LOGFILE GROUP lg2 |
| 233 |
ADD UNDOFILE 'x.dat' |
| 234 |
INITIAL_SIZE 10MB |
| 235 |
ENGINE = NDB; |
| 236 |
|
| 237 |
--error ER_PARSE_ERROR |
| 238 |
CREATE LOGFILE GROUP lg2 |
| 239 |
ADD UNDOFILE 'x.dat' |
| 240 |
INITIAL_SIZE 10 MB |
| 241 |
ENGINE = NDB; |
| 242 |
|
| 243 |
--error ER_PARSE_ERROR |
| 244 |
CREATE LOGFILE GROUP lg2 |
| 245 |
ADD UNDOFILE 'x.dat' |
| 246 |
INITIAL_SIZE 10 M |
| 247 |
ENGINE = NDB; |
| 248 |
|
| 249 |
--error ER_SIZE_OVERFLOW_ERROR |
| 250 |
CREATE LOGFILE GROUP lg2 |
| 251 |
ADD UNDOFILE 'x.dat' |
| 252 |
INITIAL_SIZE 1000000000000K |
| 253 |
ENGINE = NDB; |
| 254 |
|
| 255 |
DROP TABLE t1; |
| 256 |
|
| 257 |
# Create a table with a disk column having offset > max offset |
| 258 |
|
| 259 |
--error ER_CANT_CREATE_TABLE |
| 260 |
create table t1( |
| 261 |
col1 int unsigned not null primary key, |
| 262 |
col2 int unsigned not null, |
| 263 |
col3 varchar(8176), |
| 264 |
col4 int) |
| 265 |
tablespace ts1 storage disk |
| 266 |
charset = latin1 engine=ndbcluster; |
| 267 |
show warnings; |
| 268 |
|
| 269 |
#Verify that it isn't possible to create no logging table with disk fields |
| 270 |
--error ER_ILLEGAL_HA_CREATE_OPTION |
| 271 |
CREATE TABLE t1 (a bigint primary key, b bigint storage disk) |
| 272 |
ENGINE ndb |
| 273 |
COMMENT="NDB_TABLE=NOLOGGING=1" |
| 274 |
TABLESPACE ts1 STORAGE DISK; |
| 275 |
|
| 276 |
# |
| 277 |
# Drop tablespace and datafiles |
| 278 |
# |
| 279 |
ALTER TABLESPACE ts1 |
| 280 |
DROP DATAFILE 'datafile.dat'; |
| 281 |
ALTER TABLESPACE ts1 |
| 282 |
DROP DATAFILE 'datafile02.dat'; |
| 283 |
DROP TABLESPACE ts1; |
| 284 |
|
| 285 |
|
| 286 |
--echo # |
| 287 |
--echo # Bug #21699 DROP last DATAFILE from TABLESPACE even though there are still tables in it |
| 288 |
--echo # |
| 289 |
|
| 290 |
--echo # Create the tablespace |
| 291 |
create tablespace ts2 |
| 292 |
add datafile 'datafile2_1.dat' |
| 293 |
use logfile group lg1 |
| 294 |
initial_size 12M |
| 295 |
engine ndb; |
| 296 |
|
| 297 |
--echo # Create tables on the tablespace |
| 298 |
CREATE TABLE City ( |
| 299 |
ID int(11) NOT NULL AUTO_INCREMENT, |
| 300 |
Name char(35) NOT NULL, |
| 301 |
CountryCode char(3) NOT NULL, |
| 302 |
District char(20) NOT NULL, |
| 303 |
Population int(11) NOT NULL, |
| 304 |
PRIMARY KEY (ID) |
| 305 |
) ENGINE=ndbcluster |
| 306 |
tablespace ts2 |
| 307 |
storage disk; |
| 308 |
|
| 309 |
create table t1 (a int primary key, b int) |
| 310 |
engine = ndbcluster tablespace ts2 storage disk; |
| 311 |
insert into t1 values (1,1); |
| 312 |
|
| 313 |
--echo # Attempting to drop the datafile should fail now |
| 314 |
--error ER_ALTER_FILEGROUP_FAILED |
| 315 |
alter tablespace ts2 |
| 316 |
drop datafile 'datafile2_1.dat'; |
| 317 |
|
| 318 |
show warnings; |
| 319 |
|
| 320 |
drop table t1; |
| 321 |
|
| 322 |
alter tablespace ts2 |
| 323 |
drop datafile 'datafile2_1.dat'; |
| 324 |
|
| 325 |
--echo # Inserting into the table should now fail with error : NoDatafile in tablespace |
| 326 |
--error ER_GET_ERRMSG |
| 327 |
insert |
| 328 |
into City (Name,CountryCode,District,Population) |
| 329 |
values ('BeiJing','CN','Beijing',2000); |
| 330 |
|
| 331 |
--echo # Dropping tablsepace before dropping table should fail |
| 332 |
--error ER_TABLESPACE_IS_NOT_EMPTY |
| 333 |
drop tablespace ts2; |
| 334 |
|
| 335 |
--echo # Cleanup |
| 336 |
drop table City; |
| 337 |
|
| 338 |
drop tablespace ts2; |
| 339 |
|
| 340 |
--echo ########################### |
| 341 |
--echo # Test update of mm/dd part |
| 342 |
--echo ########################### |
| 343 |
|
| 344 |
--echo # Create the tablespaces |
| 345 |
CREATE TABLESPACE ts1 |
| 346 |
ADD DATAFILE 'datafile.dat' |
| 347 |
USE LOGFILE GROUP lg1 |
| 348 |
INITIAL_SIZE 8M |
| 349 |
ENGINE NDB; |
| 350 |
|
| 351 |
--echo # Create the tables with mm/dd parts and run tests |
| 352 |
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; |
| 353 |
INSERT INTO t1 VALUES (1,'1','1'), (2,'2','2'), (3,'3','3'); |
| 354 |
BEGIN; |
| 355 |
UPDATE t1 SET b = '2' WHERE a = 1; |
| 356 |
SELECT b FROM t1 WHERE a = 1; |
| 357 |
SELECT * FROM t1 WHERE a = 1; |
| 358 |
UPDATE t1 SET c = '2' WHERE a = 1; |
| 359 |
SELECT b FROM t1 WHERE a = 1; |
| 360 |
SELECT * FROM t1 WHERE a = 1; |
| 361 |
UPDATE t1 SET b = '3' WHERE a = 1; |
| 362 |
SELECT b FROM t1 WHERE a = 1; |
| 363 |
SELECT * FROM t1 WHERE a = 1; |
| 364 |
COMMIT; |
| 365 |
SELECT * FROM t1 ORDER BY 1; |
| 366 |
BEGIN; |
| 367 |
UPDATE t1 SET c = '3' WHERE a = 1; |
| 368 |
SELECT b FROM t1 WHERE a = 1; |
| 369 |
SELECT * FROM t1 WHERE a = 1; |
| 370 |
UPDATE t1 SET b = '4' WHERE a = 1; |
| 371 |
SELECT b FROM t1 WHERE a = 1; |
| 372 |
SELECT * FROM t1 WHERE a = 1; |
| 373 |
UPDATE t1 SET c = '4' WHERE a = 1; |
| 374 |
SELECT b FROM t1 WHERE a = 1; |
| 375 |
SELECT * FROM t1 WHERE a = 1; |
| 376 |
COMMIT; |
| 377 |
SELECT * FROM t1 ORDER BY 1; |
| 378 |
UPDATE t1 SET b = '5' WHERE a = 1; |
| 379 |
SELECT * FROM t1 ORDER BY 1; |
| 380 |
UPDATE t1 SET b = '6' WHERE b = '5'; |
| 381 |
SELECT * FROM t1 ORDER BY 1; |
| 382 |
UPDATE t1 SET b = '7'WHERE c = '4'; |
| 383 |
SELECT * FROM t1 ORDER BY 1; |
| 384 |
UPDATE t1 SET c = '5' WHERE a = 1; |
| 385 |
SELECT * FROM t1 ORDER BY 1; |
| 386 |
UPDATE t1 SET c = '6' WHERE b = '7'; |
| 387 |
SELECT * FROM t1 ORDER BY 1; |
| 388 |
UPDATE t1 SET c = '7' WHERE c = '6'; |
| 389 |
SELECT * FROM t1 ORDER BY 1; |
| 390 |
DROP TABLE t1; |
| 391 |
|
| 392 |
##### |
| 393 |
|
| 394 |
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; |
| 395 |
INSERT INTO t1 VALUE (1,'1','1'), (2,'2','2'), (3,'3','3'); |
| 396 |
BEGIN; |
| 397 |
UPDATE t1 SET b = '2' WHERE a = 1; |
| 398 |
SELECT b FROM t1 WHERE a = 1; |
| 399 |
SELECT * FROM t1 WHERE a = 1; |
| 400 |
UPDATE t1 SET c = '2' WHERE a = 1; |
| 401 |
SELECT b FROM t1 WHERE a = 1; |
| 402 |
SELECT * FROM t1 WHERE a = 1; |
| 403 |
UPDATE t1 SET b = '3' WHERE a = 1; |
| 404 |
SELECT b FROM t1 WHERE a = 1; |
| 405 |
SELECT * FROM t1 WHERE a = 1; |
| 406 |
COMMIT; |
| 407 |
SELECT * FROM t1 ORDER BY 1; |
| 408 |
BEGIN; |
| 409 |
UPDATE t1 SET c = '3' WHERE a = 1; |
| 410 |
SELECT b FROM t1 WHERE a = 1; |
| 411 |
SELECT * FROM t1 WHERE a = 1; |
| 412 |
UPDATE t1 SET b = '4' WHERE a = 1; |
| 413 |
SELECT b FROM t1 WHERE a = 1; |
| 414 |
SELECT * FROM t1 WHERE a = 1; |
| 415 |
UPDATE t1 SET c = '4' WHERE a = 1; |
| 416 |
SELECT b FROM t1 WHERE a = 1; |
| 417 |
SELECT * FROM t1 WHERE a = 1; |
| 418 |
COMMIT; |
| 419 |
SELECT * FROM t1 ORDER BY 1; |
| 420 |
UPDATE t1 SET b = '5' WHERE a = 1; |
| 421 |
SELECT * FROM t1 ORDER BY 1; |
| 422 |
UPDATE t1 SET b = '6' WHERE b = '5'; |
| 423 |
SELECT * FROM t1 ORDER BY 1; |
| 424 |
UPDATE t1 SET b = '7' WHERE c = '4'; |
| 425 |
SELECT * FROM t1 ORDER BY 1; |
| 426 |
UPDATE t1 SET c = '5' WHERE a = 1; |
| 427 |
SELECT * FROM t1 ORDER BY 1; |
| 428 |
UPDATE t1 SET c = '6' WHERE b = '7'; |
| 429 |
SELECT * FROM t1 ORDER BY 1; |
| 430 |
UPDATE t1 SET c = '7' WHERE c = '6'; |
| 431 |
SELECT * FROM t1 ORDER BY 1; |
| 432 |
|
| 433 |
DELETE FROM t1; |
| 434 |
INSERT INTO t1 VALUES (3,'1','1'); |
| 435 |
BEGIN; |
| 436 |
UPDATE t1 SET b = b + 2 WHERE A = 3; |
| 437 |
DELETE FROM t1 WHERE A = 3; |
| 438 |
INSERT INTO t1 VALUES (3,'0','0'); |
| 439 |
COMMIT; |
| 440 |
SELECT * from t1 ORDER BY 1; |
| 441 |
|
| 442 |
DROP TABLE t1; |
| 443 |
|
| 444 |
--echo # Cleanup tablespace |
| 445 |
ALTER TABLESPACE ts1 |
| 446 |
DROP DATAFILE 'datafile.dat'; |
| 447 |
DROP TABLESPACE ts1; |
| 448 |
|
| 449 |
--echo ######################## |
| 450 |
--echo # Test for blobs... |
| 451 |
--echo ######################## |
| 452 |
|
| 453 |
--echo # Create the tablespace |
| 454 |
CREATE TABLESPACE ts1 |
| 455 |
ADD DATAFILE 'datafile.dat' |
| 456 |
USE LOGFILE GROUP lg1 |
| 457 |
INITIAL_SIZE 12M |
| 458 |
ENGINE NDB; |
| 459 |
|
| 460 |
--echo # Create a table with blob column on it |
| 461 |
CREATE TABLE t1 ( |
| 462 |
a INT NOT NULL PRIMARY KEY, |
| 463 |
b TEXT NOT NULL |
| 464 |
) TABLESPACE ts1 STORAGE DISK ENGINE=NDBCLUSTER; |
| 465 |
|
| 466 |
--echo # Generate the blob values to be inserted |
| 467 |
# b1 length 2000+256 (blob part aligned) |
| 468 |
######################################### |
| 469 |
set @x0 = '01234567012345670123456701234567'; |
| 470 |
set @x0 = concat(@x0,@x0,@x0,@x0,@x0,@x0,@x0,@x0); |
| 471 |
set @b1 = 'b1'; |
| 472 |
set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1); |
| 473 |
set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1); |
| 474 |
set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1); |
| 475 |
set @b1 = concat(@b1,@x0); |
| 476 |
# b2 length 20000 |
| 477 |
########################################## |
| 478 |
set @b2 = 'b2'; |
| 479 |
set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2); |
| 480 |
set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2); |
| 481 |
set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2); |
| 482 |
set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2); |
| 483 |
|
| 484 |
--echo # Run the tests |
| 485 |
INSERT INTO t1 VALUES(1,@b1); |
| 486 |
INSERT INTO t1 VALUES(2,@b2); |
| 487 |
SELECT a,length(b),substr(b,1+2*900,2) FROM t1 WHERE a=1; |
| 488 |
SELECT a,length(b),substr(b,1+2*9000,2) FROM t1 WHERE a=2; |
| 489 |
UPDATE t1 SET b=@b2 WHERE a=1; |
| 490 |
UPDATE t1 SET b=@b1 WHERE a=2; |
| 491 |
SELECT a,length(b),substr(b,1+2*9000,2) FROM t1 WHERE a=1; |
| 492 |
SELECT a,length(b),substr(b,1+2*900,2) FROM t1 WHERE a=2; |
| 493 |
UPDATE t1 SET b=concat(b,b) WHERE a=1; |
| 494 |
UPDATE t1 SET b=concat(b,b) WHERE a=2; |
| 495 |
SELECT a,length(b),substr(b,1+4*9000,2) FROM t1 WHERE a=1; |
| 496 |
SELECT a,length(b),substr(b,1+4*900,2) FROM t1 WHERE a=2; |
| 497 |
DELETE FROM t1 WHERE a=1; |
| 498 |
DELETE FROM t1 WHERE a=2; |
| 499 |
SELECT COUNT(*) FROM t1; |
| 500 |
|
| 501 |
--echo # Cleanup |
| 502 |
DROP TABLE t1; |
| 503 |
ALTER TABLESPACE ts1 |
| 504 |
DROP DATAFILE 'datafile.dat'; |
| 505 |
DROP TABLESPACE ts1; |
| 506 |
|
| 507 |
--echo # |
| 508 |
--echo # Bug#20612 INS-DEL bug in TUP (not pgman bug) |
| 509 |
--echo # found via disk data assert but is not pgman or disk data related |
| 510 |
--echo # |
| 511 |
|
| 512 |
--echo #Setup - Create tablespaces and table |
| 513 |
CREATE TABLESPACE ts1 |
| 514 |
ADD DATAFILE 'datafile.dat' |
| 515 |
USE LOGFILE GROUP lg1 |
| 516 |
INITIAL_SIZE 8M |
| 517 |
ENGINE NDB; |
| 518 |
|
| 519 |
CREATE TABLE t1 ( |
| 520 |
a int NOT NULL, |
| 521 |
b varchar(4000) charset latin1, -- must use 2 pages undo |
| 522 |
PRIMARY KEY using hash (a) |
| 523 |
) |
| 524 |
TABLESPACE ts1 STORAGE DISK ENGINE=NDBCLUSTER; |
| 525 |
|
| 526 |
--echo # Run the tests |
| 527 |
set autocommit = 0; |
| 528 |
insert into t1 values(0,'x'); |
| 529 |
insert into t1 values(1,'x'); |
| 530 |
insert into t1 values(2,'x'); |
| 531 |
insert into t1 values(3,'x'); |
| 532 |
insert into t1 values(4,'x'); |
| 533 |
insert into t1 values(5,'x'); |
| 534 |
insert into t1 values(6,'x'); |
| 535 |
insert into t1 values(7,'x'); |
| 536 |
insert into t1 values(8,'x'); |
| 537 |
delete from t1 where a = 0; |
| 538 |
commit; |
| 539 |
|
| 540 |
delete from t1; |
| 541 |
begin; |
| 542 |
insert into t1 values (1, 'x'); |
| 543 |
select * from t1; |
| 544 |
rollback; |
| 545 |
|
| 546 |
set autocommit = 1; |
| 547 |
|
| 548 |
--echo # Cleanup |
| 549 |
drop table t1; |
| 550 |
alter tablespace ts1 |
| 551 |
drop datafile 'datafile.dat'; |
| 552 |
drop tablespace ts1; |
| 553 |
|
| 554 |
--echo ############################# |
| 555 |
--echo # Customer posted order by test case |
| 556 |
--echo # Org in ndb_dd_advance.test |
| 557 |
--echo ############################# |
| 558 |
|
| 559 |
--echo # Setup |
| 560 |
create tablespace ts1 |
| 561 |
add datafile 'datafile.dat' |
| 562 |
use logfile group lg1 |
| 563 |
initial_size 4M |
| 564 |
engine ndb; |
| 565 |
create table t1 (f1 varchar(50) primary key, f2 text,f3 int) |
| 566 |
TABLESPACE ts1 STORAGE DISK engine=NDB; |
| 567 |
--echo # Run the tests |
| 568 |
insert into t1 (f1,f2,f3)VALUES("111111","aaaaaa",1); |
| 569 |
insert into t1 (f1,f2,f3)VALUES("222222","bbbbbb",2); |
| 570 |
select * from t1 order by f1; |
| 571 |
select f1,f2 from t1 order by f2; |
| 572 |
select f2 from t1 order by f2; |
| 573 |
select f1,f2 from t1 order by f1; |
| 574 |
--echo # Cleanup |
| 575 |
drop table t1; |
| 576 |
alter tablespace ts1 |
| 577 |
drop datafile 'datafile.dat'; |
| 578 |
drop tablespace ts1; |
| 579 |
|
| 580 |
--echo # |
| 581 |
--echo # bug#34118 hash index trigger and do not update any disk attr |
| 582 |
--echo # |
| 583 |
|
| 584 |
--echo # Setup - Create tablespace, table and fill it |
| 585 |
CREATE TABLESPACE ts1 |
| 586 |
ADD DATAFILE 'datafile.dat' |
| 587 |
USE LOGFILE GROUP lg1 |
| 588 |
INITIAL_SIZE 4M |
| 589 |
ENGINE NDB; |
| 590 |
CREATE TABLE t1 ( |
| 591 |
id int unsigned NOT NULL, |
| 592 |
c1 int unsigned NOT NULL, |
| 593 |
c2 int DEFAULT NULL, |
| 594 |
PRIMARY KEY using hash (id), |
| 595 |
UNIQUE KEY c1 using hash (c1)) |
| 596 |
TABLESPACE ts1 STORAGE DISK ENGINE=ndbcluster; |
| 597 |
insert into t1 values(1, 1, 3); |
| 598 |
insert into t1 values(2, 2, 3); |
| 599 |
|
| 600 |
--echo # Confirm that the update fails |
| 601 |
--error ER_DUP_ENTRY |
| 602 |
update t1 set c1 = 1 where id = 2; |
| 603 |
|
| 604 |
--echo # Cleanup |
| 605 |
drop table t1; |
| 606 |
|
| 607 |
ALTER TABLESPACE ts1 |
| 608 |
DROP DATAFILE 'datafile.dat'; |
| 609 |
DROP TABLESPACE ts1; |
| 610 |
|
| 611 |
--echo # Also drop the logfile group lg1 created at the start of this file |
| 612 |
DROP LOGFILE GROUP lg1 |
| 613 |
ENGINE =NDB; |
| 614 |
|
| 615 |
# bug#31712 - datafile smaller than extent |
| 616 |
--echo === bug#31712 === |
| 617 |
|
| 618 |
create logfile group lg1 |
| 619 |
add undofile 'undofile.dat' |
| 620 |
initial_size 2M |
| 621 |
undo_buffer_size 1M |
| 622 |
engine ndb; |
| 623 |
|
| 624 |
--error ER_CREATE_FILEGROUP_FAILED |
| 625 |
create tablespace ts1 |
| 626 |
add datafile 'datafile.dat' |
| 627 |
use logfile group lg1 |
| 628 |
initial_size 1M |
| 629 |
extent_size 2M |
| 630 |
engine ndb; |
| 631 |
show warnings; |
| 632 |
|
| 633 |
create tablespace ts1 |
| 634 |
add datafile 'datafile.dat' |
| 635 |
use logfile group lg1 |
| 636 |
initial_size 2M |
| 637 |
extent_size 2M |
| 638 |
engine ndb; |
| 639 |
|
| 640 |
--error ER_ALTER_FILEGROUP_FAILED |
| 641 |
alter tablespace ts1 |
| 642 |
add datafile 'datafile02.dat' |
| 643 |
initial_size 1M; |
| 644 |
show warnings; |
| 645 |
|
| 646 |
alter tablespace ts1 |
| 647 |
drop datafile 'datafile.dat'; |
| 648 |
|
| 649 |
drop tablespace ts1; |
| 650 |
|
| 651 |
drop logfile group lg1 |
| 652 |
engine ndb; |
| 653 |
|
| 654 |
# bug#31782 - tablespace with no files |
| 655 |
--echo === bug#31782 === |
| 656 |
|
| 657 |
create logfile group lg1 |
| 658 |
add undofile 'undofile.dat' |
| 659 |
initial_size 2M |
| 660 |
undo_buffer_size 1M |
| 661 |
engine ndb; |
| 662 |
|
| 663 |
create tablespace ts1 |
| 664 |
add datafile 'datafile.dat' |
| 665 |
use logfile group lg1 |
| 666 |
initial_size 2M |
| 667 |
extent_size 2M |
| 668 |
engine ndb; |
| 669 |
|
| 670 |
select tablespace_name, file_type, file_name |
| 671 |
from information_schema.files |
| 672 |
where engine = 'ndbcluster' and file_type = 'DATAFILE'; |
| 673 |
|
| 674 |
alter tablespace ts1 |
| 675 |
drop datafile 'datafile.dat'; |
| 676 |
|
| 677 |
select tablespace_name, file_type, file_name |
| 678 |
from information_schema.files |
| 679 |
where engine = 'ndbcluster' and file_type = 'DATAFILE'; |
| 680 |
|
| 681 |
drop tablespace ts1; |
| 682 |
|
| 683 |
drop logfile group lg1 |
| 684 |
engine ndb; |
| 685 |
|
| 686 |
# bug#49709 - I_S discrepancies due to DICT vs LGMAN/TSMAN |
| 687 |
--echo === bug#49709 === |
| 688 |
|
| 689 |
# undo buffer size rounded UP to page size (32k) |
| 690 |
# 294913 = 9 * 32k + 1 => 10 * 32k = 327680 |
| 691 |
# undofile size rounded DOWN to page size (32k) |
| 692 |
# 3276801 = 100 * 32k + 1 => 100 * 32k = 3276800 |
| 693 |
create logfile group lg1 |
| 694 |
add undofile 'undofile.dat' |
| 695 |
initial_size 3276801 |
| 696 |
undo_buffer_size 294913 |
| 697 |
engine ndb; |
| 698 |
|
| 699 |
# undofile size rounded DOWN to page size (32k) |
| 700 |
# 3309567 = 101 * 32k - 1 => 100 * 32k = 3276800 |
| 701 |
alter logfile group lg1 |
| 702 |
add undofile 'undofile02.dat' |
| 703 |
initial_size 3309567 |
| 704 |
engine ndb; |
| 705 |
|
| 706 |
# extent size rounded UP to page size (32k) |
| 707 |
# 294913 = 9 * 32k + 1 => 10 * 32k = 327680 |
| 708 |
# datafile size rounded DOWN to page size and UP to extent size |
| 709 |
# 3276801 = 100 * 32k + 1 => 100 * 32k == 10 * EXT = 3276800 |
| 710 |
create tablespace ts1 |
| 711 |
add datafile 'datafile.dat' |
| 712 |
use logfile group lg1 |
| 713 |
initial_size 3276801 |
| 714 |
extent_size 294913 |
| 715 |
engine ndb; |
| 716 |
|
| 717 |
# datafile size rounded DOWN to page size and UP to extent size |
| 718 |
# 3342335 = 102 * 32k - 1 => 110 * 32k == 11 * EXT = 3604480 |
| 719 |
alter tablespace ts1 |
| 720 |
add datafile 'datafile02.dat' |
| 721 |
initial_size 3342335; |
| 722 |
|
| 723 |
# check undofiles |
| 724 |
select file_name, initial_size |
| 725 |
from information_schema.files |
| 726 |
where file_type = 'UNDO LOG' and file_name like '%dat' |
| 727 |
order by file_name; |
| 728 |
|
| 729 |
# check datafiles |
| 730 |
select file_name, initial_size, extent_size, total_extents, free_extents |
| 731 |
from information_schema.files |
| 732 |
where file_type = 'DATAFILE' |
| 733 |
order by file_name; |
| 734 |
|
| 735 |
alter tablespace ts1 |
| 736 |
drop datafile 'datafile02.dat'; |
| 737 |
|
| 738 |
alter tablespace ts1 |
| 739 |
drop datafile 'datafile.dat'; |
| 740 |
|
| 741 |
drop tablespace ts1; |
| 742 |
|
| 743 |
drop logfile group lg1 |
| 744 |
engine ndb; |
| 745 |
|
| 746 |
# Bug 13116514: parser does not accept suffix 'M', etc for sizes |
| 747 |
set character_set_client=utf8; |
| 748 |
create logfile group lg1 |
| 749 |
add undofile 'undo1.log' |
| 750 |
undo_buffer_size 2M |
| 751 |
engine=ndb; |
| 752 |
|
| 753 |
drop logfile group lg1 |
| 754 |
engine ndb; |
| 755 |
|
| 756 |
#End 5.1 test case |
| 757 |
|
| 758 |
# |
| 759 |
# BUG#11762867 |
| 760 |
# - create logfile group with too large undo_buffer_size |
| 761 |
# in order to show the improved ndb error message indicating |
| 762 |
# "too large undo_buffer_size or too small GlobalSharedMemory" |
| 763 |
# |
| 764 |
--error ER_CREATE_FILEGROUP_FAILED |
| 765 |
CREATE LOGFILE GROUP lg1 |
| 766 |
ADD UNDOFILE 'undofile.dat' |
| 767 |
INITIAL_SIZE 16M |
| 768 |
UNDO_BUFFER_SIZE = 2G |
| 769 |
ENGINE=NDB; |
| 770 |
SHOW WARNINGS; |
| 771 |
SET SESSION information_schema_stats_expiry=default; |