~drizzle-trunk/drizzle/development

create table `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` (aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa int);

--error ER_TOO_LONG_IDENT

--error 1059

create table a (`aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` int);

# Some wrong defaults, so these creates should fail too (Bug #5902)

--error ER_INVALID_DEFAULT

--error 1067

create table t1 (a datetime default now());

--error ER_INVALID_ON_UPDATE

--error 1294

create table t1 (a datetime on update now());

--error ER_INVALID_DEFAULT

--error 1067

create table t1 (a int default 100 auto_increment);

# TODO: Should this really fail? What's wrong with default 1000 ???

#--error ER_INVALID_DEFAULT

#--error 1067

#create table t1 (a int default 1000);

--error ER_INVALID_DEFAULT

--error 1067

create table t1 (a varchar(5) default 'abcdef');

create table t1 (a varchar(5) default 'abcde');

insert into t1 values();

select * from t1;

--error ER_INVALID_DEFAULT

--error 1067

alter table t1 alter column a set default 'abcdef';

drop table t1;

drop table mysqltest.test2$;

100

drop database mysqltest;

101

102

--error ER_WRONG_TABLE_NAME

--error 1103

103

create table `` (a int);

104

--error ER_WRONG_TABLE_NAME

--error 1103

105

100

drop table if exists ``;

106

--error ER_WRONG_COLUMN_NAME

101

--error 1166

107

102

create table t1 (`` int);

108

--error ER_WRONG_NAME_FOR_INDEX

103

--error 1280

109

104

create table t1 (i int, index `` (i));

110

105

111

106

128

123

describe t1;

129

124

describe t2;

130

125

drop table t2;

131

create table t2 select now() as a , curdate() as c , 1+1 as d , 1.0 + 1 as e , 33333333333333333 + 3 as f;

126

create table t2 select now() as a , curtime() as b, curdate() as c , 1+1 as d , 1.0 + 1 as e , 33333333333333333 + 3 as f;

132

127

describe t2;

133

128

drop table t2;

134

create table t2 select CAST("2001-12-29" AS DATE) as d, CAST("2001-12-29 20:45:11" AS DATETIME) as dt;

129

create table t2 select CAST("2001-12-29" AS DATE) as d, CAST("20:45:11" AS TIME) as t, CAST("2001-12-29 20:45:11" AS DATETIME) as dt;

135

130

describe t2;

136

131

drop table t1,t2;

137

132

144

139

describe t1;

145

140

describe t2;

146

141

drop table if exists t2;

147

--error ER_DUP_FIELDNAME

142

--error 1060

148

143

create table t2 (a int, a float) select * from t1;

149

144

drop table if exists t2;

150

--error ER_DUP_FIELDNAME

145

--error 1060

151

146

create table t2 (a int) select a as b, a+1 as b from t1;

152

147

drop table if exists t2;

153

--error ER_DUP_FIELDNAME

148

--error 1060

154

149

create table t2 (b int) select a as b, a+1 as b from t1;

155

150

drop table if exists t1,t2;

156

151

162

157

INSERT INTO t1 values (1),(2),(1);

163

158

--error ER_DUP_ENTRY

164

159

CREATE TABLE t2 (primary key(a)) SELECT * FROM t1;

165

--error ER_NO_SUCH_TABLE

160

--error 1146

166

161

SELECT * from t2;

167

162

DROP TABLE t1;

168

163

DROP TABLE IF EXISTS t2;

172

167

173

168

174

169

create table t1 (a int not null, b int, primary key(a), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b));

175

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

176

170

show create table t1;

177

171

drop table t1;

178

172

create table t1 select if(1,'1','0'), month("2002-08-02");

184

178

185

179

# Test default table type

186

180

187

SET SESSION storage_engine="MEMORY";

181

SET SESSION storage_engine="heap";

188

182

SELECT @@storage_engine;

189

CREATE TEMPORARY TABLE t1 (a int not null);

183

CREATE TABLE t1 (a int not null);

190

184

show create table t1;

191

185

drop table t1;

192

--error ER_UNKNOWN_STORAGE_ENGINE

186

--error 1286

193

187

SET SESSION storage_engine="gemini";

194

188

SELECT @@storage_engine;

195

CREATE TEMPORARY TABLE t1 (a int not null);

189

CREATE TABLE t1 (a int not null);

196

190

show create table t1;

197

191

SET SESSION storage_engine=default;

198

192

drop table t1;

203

197

204

198

create table t1 ( k1 varchar(2), k2 int, primary key(k1,k2));

205

199

insert into t1 values ("a", 1), ("b", 2);

206

--error ER_BAD_NULL_ERROR

200

--error 1048

207

201

insert into t1 values ("c", NULL);

208

--error ER_BAD_NULL_ERROR

202

--error 1048

209

203

insert into t1 values (NULL, 3);

210

--error ER_BAD_NULL_ERROR

204

--error 1048

211

205

insert into t1 values (NULL, NULL);

212

206

drop table t1;

213

207

223

217

224

218

225

219

create table t1 select 1,2,3;

226

--error ER_NO_DEFAULT_FOR_FIELD

227

220

create table if not exists t1 select 1,2;

228

--error ER_WRONG_VALUE_COUNT_ON_ROW

229

221

create table if not exists t1 select 1,2,3,4;

230

--error ER_NO_DEFAULT_FOR_FIELD

231

222

create table if not exists t1 select 1;

232

223

select * from t1;

233

224

drop table t1;

246

237

--error ER_DUP_ENTRY

247

238

create table if not exists t1 select 3 as 'a',3 as 'b';

248

239

show warnings;

249

--replace_column 3 # 4 # 5 #

250

select * from DATA_DICTIONARY.TABLE_DEFINITION_CACHE WHERE TABLE_COUNT > 1 ORDER BY TABLE_SCHEMA, TABLE_NAME;

240

show status like "Opened_tables";

251

241

select * from t1;

252

242

drop table t1;

253

243

256

246

# "Table truncated when creating another table name with Spaces"

257

247

258

248

259

--error ER_WRONG_TABLE_NAME

249

--error 1103

260

250

create table `t1 `(a int);

261

--error ER_WRONG_DB_NAME

251

--error 1102

262

252

create database `db1 `;

263

--error ER_WRONG_COLUMN_NAME

253

--error 1166

264

254

create table t1(`a ` int);

265

255

266

256

268

258

# "Parser permits multiple commas without syntax error"

269

259

270

260

271

--error ER_PARSE_ERROR

261

--error 1064

272

262

create table t1 (a int,);

273

--error ER_PARSE_ERROR

263

--error 1064

274

264

create table t1 (a int,,b int);

275

--error ER_PARSE_ERROR

265

--error 1064

276

266

create table t1 (,b int);

277

267

278

268

281

271

282

272

create table t1 (a int, key(a));

283

273

create table t2 (b int, foreign key(b) references t1(a), key(b));

284

--error ER_ROW_IS_REFERENCED

274

--error 1217

285

275

drop table if exists t1,t2;

286

276

drop table if exists t2,t1;

287

277

294

284

create table t2(id int not null);

295

285

insert into t2 values(10),(20);

296

286

create table t3 like t1;

297

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

298

287

show create table t3;

299

288

select * from t3;

300

289

# Disable PS becasue of @@warning_count

303

292

select @@warning_count;

304

293

--enable_ps_protocol

305

294

create temporary table t3 like t2;

306

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

307

295

show create table t3;

308

296

select * from t3;

309

297

drop table t3;

310

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

311

298

show create table t3;

312

299

select * from t3;

313

300

drop table t2, t3;

314

301

create database mysqltest;

315

302

create table mysqltest.t3 like t1;

316

303

create temporary table t3 like mysqltest.t3;

317

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

318

304

show create table t3;

319

305

create table t2 like t3;

320

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

321

306

show create table t2;

322

307

select * from t2;

323

--error ER_TABLE_EXISTS_ERROR

324

308

create table t3 like t1;

325

--error ER_TABLE_EXISTS_ERROR

309

--error 1050

326

310

create table t3 like mysqltest.t3;

327

--error ER_BAD_DB_ERROR

311

--error 1049

328

312

create table non_existing_database.t1 like t1;

329

313

--error ER_NO_SUCH_TABLE

330

314

create table t3 like non_existing_table;

331

--error ER_TABLE_EXISTS_ERROR

315

--error 1050

332

316

create temporary table t3 like t1;

333

317

drop table t1, t2, t3;

318

drop table t3;

334

319

drop database mysqltest;

335

320

336

321

337

322

# Test default table type

338

323

339

SET SESSION storage_engine="MEMORY";

324

SET SESSION storage_engine="heap";

340

325

SELECT @@storage_engine;

341

CREATE TEMPORARY TABLE t1 (a int not null);

326

CREATE TABLE t1 (a int not null);

342

327

show create table t1;

343

328

drop table t1;

344

--error ER_UNKNOWN_STORAGE_ENGINE

329

--error 1286

345

330

SET SESSION storage_engine="gemini";

346

331

SELECT @@storage_engine;

347

CREATE TEMPORARY TABLE t1 (a int not null);

332

CREATE TABLE t1 (a int not null);

348

333

show create table t1;

349

334

SET SESSION storage_engine=default;

350

335

drop table t1;

353

338

# Test types of data for create select with functions

354

339

355

340

356

create table t1(a int,b int,c int,d date,e char,f datetime,h blob);

341

create table t1(a int,b int,c int,d date,e char,f datetime,g time,h blob);

357

342

insert into t1(a)values(1);

358

insert into t1(a,b,c,d,e,f,h)

359

values(2,-2,2,'1825-12-14','a','2003-01-01 03:02:01','binary data');

343

insert into t1(a,b,c,d,e,f,g,h)

344

values(2,-2,2,'1825-12-14','a','2003-1-1 3:2:1','4:3:2','binary data');

360

345

select * from t1;

361

346

select a,

362

347

ifnull(b,-7) as b,

364

349

ifnull(d,cast('2000-01-01' as date)) as d,

365

350

ifnull(e,cast('b' as char)) as e,

366

351

ifnull(f,cast('2000-01-01' as datetime)) as f,

367

ifnull(h,cast('yet another binary data' as binary)) as h

352

ifnull(g,cast('5:4:3' as time)) as g,

353

ifnull(h,cast('yet another binary data' as binary)) as h,

354

addtime(cast('1:0:0' as time),cast('1:0:0' as time)) as dd

368

355

from t1;

369

356

370

357

create table t2

375

362

ifnull(d,cast('2000-01-01' as date)) as d,

376

363

ifnull(e,cast('b' as char)) as e,

377

364

ifnull(f,cast('2000-01-01' as datetime)) as f,

378

ifnull(h,cast('yet another binary data' as binary)) as h

365

ifnull(g,cast('5:4:3' as time)) as g,

366

ifnull(h,cast('yet another binary data' as binary)) as h,

367

addtime(cast('1:0:0' as time),cast('1:0:0' as time)) as dd

379

368

from t1;

380

369

explain t2;

381

370

select * from t2;

382

371

drop table t1, t2;

383

372

384

373

create table t1 (a int, b int, d int, e bigint, f float(3,2), g double(4,3), h decimal(5,4), j date, k timestamp, l datetime, m enum('a','b'), o char(10));

385

SHOW CREATE TABLE t1;

386

374

create table t2 select ifnull(a,a), ifnull(b,b), ifnull(d,d), ifnull(e,e), ifnull(f,f), ifnull(g,g), ifnull(h,h), ifnull(j,j), ifnull(k,k), ifnull(l,l), ifnull(m,m), ifnull(o,o) from t1;

387

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

388

375

show create table t2;

389

376

drop table t1,t2;

390

377

413

400

414

401

415

402

create table t1(cenum enum('a'));

416

--error ER_DUPLICATED_VALUE_IN_TYPE

403

--error 1291

417

404

create table t2(cenum enum('a','a'));

418

--error ER_DUPLICATED_VALUE_IN_TYPE

405

--error 1291

419

406

create table t3(cenum enum('a','A','a','c','c'));

420

407

drop table t1;

421

408

435

422

436

423

437

424

## TODO: Is this really a bug? It works in Drizzle. Should it?

438

#--error ER_WRONG_NAME_FOR_INDEX

425

#--error 1280

439

426

#create table t1 (a int, index `primary` (a));

440

#--error ER_WRONG_NAME_FOR_INDEX

427

#--error 1280

441

428

#create table t1 (a int, index `PRIMARY` (a));

442

429

443

430

#create table t1 (`primary` int, index(`primary`));

444

#--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

445

431

#show create table t1;

446

432

#create table t2 (`PRIMARY` int, index(`PRIMARY`));

447

#--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

448

433

#show create table t2;

449

434

450

435

#create table t3 (a int);

451

#--error ER_WRONG_NAME_FOR_INDEX

436

#--error 1280

452

437

#alter table t3 add index `primary` (a);

453

#--error ER_WRONG_NAME_FOR_INDEX

438

#--error 1280

454

439

#alter table t3 add index `PRIMARY` (a);

455

440

456

441

#create table t4 (`primary` int);

457

442

#alter table t4 add index(`primary`);

458

#--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

459

443

#show create table t4;

460

444

#create table t5 (`PRIMARY` int);

461

445

#alter table t5 add index(`PRIMARY`);

462

#--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

463

446

#show create table t5;

464

447

465

448

#drop table t1, t2, t3, t4, t5;

486

469

# an improper fix is present.

487

470

488

471

create table t1 (a int);

489

--error ER_UPDATE_TABLE_USED

472

## TODO: Should this statement fail?

473

#--error 1093

490

474

create table t1 select * from t1;

491

475

## TODO: Huh? --error ER_WRONG_OBJECT

492

476

#create table t2 union = (t1) select * from t1;

497

481

498

482

# Bug#10413: Invalid column name is not rejected

499

483

500

--error ER_WRONG_TABLE_NAME

484

--error 1103

501

485

create table t1(column.name int);

502

--error ER_WRONG_TABLE_NAME

486

--error 1103

503

487

create table t1(test.column.name int);

504

--error ER_WRONG_DB_NAME

488

--error 1102

505

489

create table t1(xyz.t1.name int);

506

490

create table t1(t1.name int);

507

491

create table t2(test.t2.name int);

552

536

primary key (a)

553

537

) select 'test' as a ;

554

538

#--warning 1364

555

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

556

539

show create table t1;

557

540

drop table t1;

558

541

570

553

a varchar(12) collate utf8_bin not null,

571

554

b int not null, primary key (a)

572

555

) select a, 1 as b from t2 ;

573

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

574

556

show create table t1;

575

557

drop table t1;

576

558

577

--error ER_NO_DEFAULT_FOR_FIELD

559

--error 1364

578

560

create table t1 (

579

561

a varchar(12) collate utf8_bin not null,

580

562

b int not null, primary key (a)

584

566

a varchar(12) collate utf8_bin not null,

585

567

b int null, primary key (a)

586

568

) select a, 1 as c from t2 ;

587

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

588

569

show create table t1;

589

570

drop table t1;

590

571

592

573

a varchar(12) collate utf8_bin not null,

593

574

b int not null, primary key (a)

594

575

) select 'a' as a , 1 as b from t2 ;

595

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

596

576

show create table t1;

597

577

drop table t1;

598

578

600

580

a varchar(12) collate utf8_bin,

601

581

b int not null, primary key (a)

602

582

) select 'a' as a , 1 as b from t2 ;

603

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

604

583

show create table t1;

605

584

drop table t1, t2;

606

585

643

622

644

623

create table t2 ( a int default 3, b int default 3)

645

624

select a1,a2 from t1;

646

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

647

625

show create table t2;

648

626

649

627

drop table t1, t2;

650

628

651

629

652

630

631

# Bug #14155: Maximum value of MAX_ROWS handled incorrectly on 64-bit

632

# platforms

633

634

create table t1 (i int) engine=myisam max_rows=100000000000;

635

show create table t1;

636

alter table t1 max_rows=100;

637

show create table t1;

638

alter table t1 max_rows=100000000000;

639

show create table t1;

640

drop table t1;

641

642

643

653

644

# Tests for errors happening at various stages of CREATE TABLES ... SELECT

654

645

655

646

# (Also checks that it behaves atomically in the sense that in case

714

705

715

706

716

707

708

# CREATE TABLE ... SELECT and LOCK TABLES

709

710

# There is little sense in using CREATE TABLE ... SELECT under

711

# LOCK TABLES as it mostly does not work. At least we check that

712

# the server doesn't crash, hang and produces sensible errors.

713

# Includes test for bug #20662 "Infinite loop in CREATE TABLE

714

# IF NOT EXISTS ... SELECT with locked tables".

715

create table t1 (i int);

716

insert into t1 values (1), (2);

717

lock tables t1 read;

718

--error ER_TABLE_NOT_LOCKED

719

create table t2 select * from t1;

720

--error ER_TABLE_NOT_LOCKED

721

create table if not exists t2 select * from t1;

722

unlock tables;

723

create table t2 (j int);

724

lock tables t1 read;

725

--error ER_TABLE_NOT_LOCKED

726

create table t2 select * from t1;

727

# This should not be ever allowed as it will undermine

728

# lock-all-at-once approach

729

--error ER_TABLE_NOT_LOCKED

730

create table if not exists t2 select * from t1;

731

unlock tables;

732

lock table t1 read, t2 read;

733

--error ER_TABLE_NOT_LOCKED_FOR_WRITE

734

create table t2 select * from t1;

735

--error ER_TABLE_NOT_LOCKED_FOR_WRITE

736

create table if not exists t2 select * from t1;

737

unlock tables;

738

lock table t1 read, t2 write;

739

--error ER_TABLE_EXISTS_ERROR

740

create table t2 select * from t1;

741

# This is the only case which really works.

742

create table if not exists t2 select * from t1;

743

select * from t1;

744

unlock tables;

745

drop table t2;

746

747

# OTOH CREATE TEMPORARY TABLE ... SELECT should work

748

# well under LOCK TABLES.

749

lock tables t1 read;

750

create temporary table t2 select * from t1;

751

create temporary table if not exists t2 select * from t1;

752

select * from t2;

753

unlock tables;

754

drop table t1, t2;

755

756

757

717

758

# Bug#21772: can not name a column 'upgrade' when create a table

718

759

719

760

create table t1 (upgrade int);

869

910

);

870

911

871

912

# Check that the table is not corrupted

872

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

873

913

show create table t1;

874

914

flush tables;

875

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

876

915

show create table t1;

877

916

878

917

# Repeat test using ALTER to add indexes

1018

1057

add key a064_long_123456789_123456789_123456789_123456789_123456789_1234 (

1019

1058

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16);

1020

1059

1021

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

1022

1060

show create table t1;

1023

1061

flush tables;

1024

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

1025

1062

show create table t1;

1026

1063

1027

1064

# Test the server limits; if any of these pass, all above tests need

1028

1065

# to be rewritten to hit the limit

1029

1066

1030

1067

# Ensure limit is really 64 keys

1031

--error ER_TOO_MANY_KEYS

1068

--error 1069

1032

1069

alter table t1 add key

1033

1070

a065_long_123456789_123456789_123456789_123456789_123456789_1234 (

1034

1071

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16);

1042

1079

c16 int, c17 int);

1043

1080

1044

1081

# Get error for max key parts

1045

--error ER_TOO_MANY_KEY_PARTS

1082

--error 1070

1046

1083

alter table t1 add key i1 (

1047

1084

c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16, c17);

1048

1085

1049

1086

# Get error for max key-name length

1050

--error ER_TOO_LONG_IDENT

1087

--error 1059

1051

1088

alter table t1 add key

1052

1089

a001_long_123456789_123456789_123456789_123456789_123456789_12345 (c1);

1053

1090

1054

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

1055

1091

show create table t1;

1056

1092

1057

1093

drop table t1;

1101

1137

# Show that in case of multiple index type definitions, the last one takes

1102

1138

# precedence

1103

1139

1104

CREATE TEMPORARY TABLE t1(c1 VARCHAR(33), KEY USING BTREE (c1) USING HASH) ENGINE=MEMORY;

1105

#SHOW INDEX FROM t1;

1140

CREATE TABLE t1(c1 VARCHAR(33), KEY USING BTREE (c1) USING HASH) ENGINE=MEMORY;

1141

SHOW INDEX FROM t1;

1106

1142

DROP TABLE t1;

1107

1143

1108

CREATE TEMPORARY TABLE t1(c1 VARCHAR(33), KEY USING HASH (c1) USING BTREE) ENGINE=MEMORY;

1109

#SHOW INDEX FROM t1;

1144

CREATE TABLE t1(c1 VARCHAR(33), KEY USING HASH (c1) USING BTREE) ENGINE=MEMORY;

1145

SHOW INDEX FROM t1;

1110

1146

DROP TABLE t1;

1111

1147

1112

1148

1152

1188

# Test incorrect database names

1153

1189

1154

1190

1155

--error ER_WRONG_DB_NAME

1191

--error 1102

1156

1192

CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1157

--error ER_WRONG_DB_NAME

1193

--error 1102

1158

1194

DROP DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1159

1195

1160

1196

# TODO: enable these tests when RENAME DATABASE is implemented.

1161

# --error ER_BAD_DB_ERROR

1197

# --error 1049

1162

1198

# RENAME DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa TO a;

1163

# --error ER_WRONG_DB_NAME

1199

# --error 1102

1164

1200

# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1165

1201

# create database mysqltest;

1166

# --error ER_WRONG_DB_NAME

1202

# --error 1102

1167

1203

# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1168

1204

# drop database mysqltest;

1169

1205

1170

--error ER_WRONG_DB_NAME

1206

--error 1102

1171

1207

USE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1172

#--error ER_WRONG_DB_NAME

1208

--error 1102

1173

1209

SHOW CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1174

1210

1175

1176

## Bug#21432 Database/Table name limited to 64 bytes, not chars, problems with multi-byte

1177

1211

1212

# Bug#21432 Database/Table name limited to 64 bytes, not chars, problems with multi-byte

1213

1178

1214

1179

1215

create database имя_базы_в_кодировке_утф8_длиной_больше_чем_45;

1180

1216

use имя_базы_в_кодировке_утф8_длиной_больше_чем_45;

1181

1217

select database();

1182

1218

use test;

1183

1219

1184

select SCHEMA_NAME from data_dictionary.schemas

1220

select SCHEMA_NAME from information_schema.schemata

1185

1221

where schema_name='имя_базы_в_кодировке_утф8_длиной_больше_чем_45';

1186

1222

1187

1223

drop database имя_базы_в_кодировке_утф8_длиной_больше_чем_45;

1195

1231

# database, table, field, key

1196

1232

select * from имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48;

1197

1233

1198

select TABLE_NAME from data_dictionary.tables where

1199

table_schema='test';

1200

1201

select COLUMN_NAME from data_dictionary.columns where

1202

table_schema='test';

1203

1204

select INDEX_NAME from data_dictionary.indexes where

1205

table_schema='test';

1206

1207

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

1234

select TABLE_NAME from information_schema.tables where

1235

table_schema='test';

1236

1237

select COLUMN_NAME from information_schema.columns where

1238

table_schema='test';

1239

1240

select INDEX_NAME from information_schema.statistics where

1241

table_schema='test';

1242

1208

1243

show create table имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48;

1209

1244

1210

1245

drop table имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48;

1211

1246

1212

1247

1213

1248

1214

1249

# Bug#25629 CREATE TABLE LIKE does not work with INFORMATION_SCHEMA

1215

1250

1216

--error ER_CANT_CREATE_TABLE

1217

create table t1 like data_dictionary.processlist;

1218

create table t1 like data_dictionary.processlist engine=innodb;

1251

create table t1 like information_schema.processlist;

1219

1252

show create table t1;

1220

1253

drop table t1;

1221

--error ER_CANT_CREATE_TABLE

1222

create temporary table t1 like data_dictionary.processlist;

1223

create temporary table t1 like data_dictionary.processlist engine=myisam;

1254

create temporary table t1 like information_schema.processlist;

1224

1255

show create table t1;

1225

1256

drop table t1;

1226

1257

1248

1279

c4 VARCHAR(255) NOT NULL DEFAULT 'a',

1249

1280

c5 VARCHAR(255) COLLATE utf8_unicode_ci NULL DEFAULT 'b',

1250

1281

c6 VARCHAR(255))

1251

COLLATE=utf8_bin;

1282

COLLATE utf8_bin;

1252

1283

1253

1284

--echo

1254

1285

1255

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

1256

1286

SHOW CREATE TABLE t1;

1257

1287

1258

1288

--echo

1261

1291

1262

1292

--echo

1263

1293

1264

--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/

1265

1294

SHOW CREATE TABLE t2;

1266

1295

1267

1296

--echo

1292

1321

--echo

1293

1322

1294

1323

--echo

1295

CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP NULL);

1296

drop table t2;

1297

1298

CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP DEFAULT '1982-01-29');

1324

CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP DEFAULT 0);

1299

1325

drop table t2;

1300

1326

1301

1327

--echo

1315

1341

--echo

1316

1342

1317

1343

CREATE TABLE t3(c1 DATETIME NOT NULL);

1318

--error ER_INVALID_DATETIME_VALUE # Bad datetime

1319

1344

INSERT INTO t3 VALUES (0);

1320

1345

1321

1346

--echo

Older »