~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

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

220

--error 1364

227

221

create table if not exists t1 select 1,2;

228

--error ER_WRONG_VALUE_COUNT_ON_ROW

222

--error 1136

229

223

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

230

--error ER_NO_DEFAULT_FOR_FIELD

224

--error 1364

231

225

create table if not exists t1 select 1;

232

226

select * from t1;

233

227

drop table t1;

246

240

--error ER_DUP_ENTRY

247

241

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

248

242

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;

243

show status like "Opened_tables";

251

244

select * from t1;

252

245

drop table t1;

253

246

256

249

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

257

250

258

251

259

--error ER_WRONG_TABLE_NAME

252

--error 1103

260

253

create table `t1 `(a int);

261

--error ER_WRONG_DB_NAME

254

--error 1102

262

255

create database `db1 `;

263

--error ER_WRONG_COLUMN_NAME

256

--error 1166

264

257

create table t1(`a ` int);

265

258

266

259

268

261

# "Parser permits multiple commas without syntax error"

269

262

270

263

271

--error ER_PARSE_ERROR

264

--error 1064

272

265

create table t1 (a int,);

273

--error ER_PARSE_ERROR

266

--error 1064

274

267

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

275

--error ER_PARSE_ERROR

268

--error 1064

276

269

create table t1 (,b int);

277

270

278

271

281

274

282

275

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

283

276

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

284

--error ER_ROW_IS_REFERENCED

277

--error 1217

285

278

drop table if exists t1,t2;

286

279

drop table if exists t2,t1;

287

280

294

287

create table t2(id int not null);

295

288

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

296

289

create table t3 like t1;

297

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

298

290

show create table t3;

299

291

select * from t3;

300

292

# Disable PS becasue of @@warning_count

303

295

select @@warning_count;

304

296

--enable_ps_protocol

305

297

create temporary table t3 like t2;

306

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

307

298

show create table t3;

308

299

select * from t3;

309

300

drop table t3;

310

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

311

301

show create table t3;

312

302

select * from t3;

313

303

drop table t2, t3;

314

304

create database mysqltest;

315

305

create table mysqltest.t3 like t1;

316

306

create temporary table t3 like mysqltest.t3;

317

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

318

307

show create table t3;

319

308

create table t2 like t3;

320

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

321

309

show create table t2;

322

310

select * from t2;

323

--error ER_TABLE_EXISTS_ERROR

324

311

create table t3 like t1;

325

--error ER_TABLE_EXISTS_ERROR

312

--error 1050

326

313

create table t3 like mysqltest.t3;

327

--error ER_BAD_DB_ERROR

314

--error 1049

328

315

create table non_existing_database.t1 like t1;

329

316

--error ER_NO_SUCH_TABLE

330

317

create table t3 like non_existing_table;

331

--error ER_TABLE_EXISTS_ERROR

318

--error 1050

332

319

create temporary table t3 like t1;

333

320

drop table t1, t2, t3;

321

drop table t3;

334

322

drop database mysqltest;

335

323

336

324

337

325

# Test default table type

338

326

339

SET SESSION storage_engine="MEMORY";

327

SET SESSION storage_engine="heap";

340

328

SELECT @@storage_engine;

341

CREATE TEMPORARY TABLE t1 (a int not null);

329

CREATE TABLE t1 (a int not null);

342

330

show create table t1;

343

331

drop table t1;

344

--error ER_UNKNOWN_STORAGE_ENGINE

332

--error 1286

345

333

SET SESSION storage_engine="gemini";

346

334

SELECT @@storage_engine;

347

CREATE TEMPORARY TABLE t1 (a int not null);

335

CREATE TABLE t1 (a int not null);

348

336

show create table t1;

349

337

SET SESSION storage_engine=default;

350

338

drop table t1;

382

370

drop table t1, t2;

383

371

384

372

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

373

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

374

show create table t2;

389

375

drop table t1,t2;

390

376

413

399

414

400

415

401

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

416

--error ER_DUPLICATED_VALUE_IN_TYPE

402

--error 1291

417

403

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

418

--error ER_DUPLICATED_VALUE_IN_TYPE

404

--error 1291

419

405

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

420

406

drop table t1;

421

407

435

421

436

422

437

423

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

438

#--error ER_WRONG_NAME_FOR_INDEX

424

#--error 1280

439

425

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

440

#--error ER_WRONG_NAME_FOR_INDEX

426

#--error 1280

441

427

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

442

428

443

429

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

444

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

445

430

#show create table t1;

446

431

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

447

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

448

432

#show create table t2;

449

433

450

434

#create table t3 (a int);

451

#--error ER_WRONG_NAME_FOR_INDEX

435

#--error 1280

452

436

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

453

#--error ER_WRONG_NAME_FOR_INDEX

437

#--error 1280

454

438

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

455

439

456

440

#create table t4 (`primary` int);

457

441

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

458

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

459

442

#show create table t4;

460

443

#create table t5 (`PRIMARY` int);

461

444

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

462

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

463

445

#show create table t5;

464

446

465

447

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

486

468

# an improper fix is present.

487

469

488

470

create table t1 (a int);

489

--error ER_UPDATE_TABLE_USED

471

--error 1093

490

472

create table t1 select * from t1;

491

473

## TODO: Huh? --error ER_WRONG_OBJECT

492

474

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

497

479

498

480

# Bug#10413: Invalid column name is not rejected

499

481

500

--error ER_WRONG_TABLE_NAME

482

--error 1103

501

483

create table t1(column.name int);

502

--error ER_WRONG_TABLE_NAME

484

--error 1103

503

485

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

504

--error ER_WRONG_DB_NAME

486

--error 1102

505

487

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

506

488

create table t1(t1.name int);

507

489

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

552

534

primary key (a)

553

535

) select 'test' as a ;

554

536

#--warning 1364

555

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

556

537

show create table t1;

557

538

drop table t1;

558

539

570

551

a varchar(12) collate utf8_bin not null,

571

552

b int not null, primary key (a)

572

553

) select a, 1 as b from t2 ;

573

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

574

554

show create table t1;

575

555

drop table t1;

576

556

577

--error ER_NO_DEFAULT_FOR_FIELD

557

--error 1364

578

558

create table t1 (

579

559

a varchar(12) collate utf8_bin not null,

580

560

b int not null, primary key (a)

584

564

a varchar(12) collate utf8_bin not null,

585

565

b int null, primary key (a)

586

566

) select a, 1 as c from t2 ;

587

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

588

567

show create table t1;

589

568

drop table t1;

590

569

592

571

a varchar(12) collate utf8_bin not null,

593

572

b int not null, primary key (a)

594

573

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

595

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

596

574

show create table t1;

597

575

drop table t1;

598

576

600

578

a varchar(12) collate utf8_bin,

601

579

b int not null, primary key (a)

602

580

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

603

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

604

581

show create table t1;

605

582

drop table t1, t2;

606

583

643

620

644

621

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

645

622

select a1,a2 from t1;

646

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

647

623

show create table t2;

648

624

649

625

drop table t1, t2;

650

626

651

627

652

628

629

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

630

# platforms

631

632

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

633

show create table t1;

634

alter table t1 max_rows=100;

635

show create table t1;

636

alter table t1 max_rows=100000000000;

637

show create table t1;

638

drop table t1;

639

640

641

653

642

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

654

643

655

644

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

714

703

715

704

716

705

706

# CREATE TABLE ... SELECT and LOCK TABLES

707

708

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

709

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

710

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

711

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

712

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

713

create table t1 (i int);

714

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

715

lock tables t1 read;

716

--error ER_TABLE_NOT_LOCKED

717

create table t2 select * from t1;

718

--error ER_TABLE_NOT_LOCKED

719

create table if not exists t2 select * from t1;

720

unlock tables;

721

create table t2 (j int);

722

lock tables t1 read;

723

--error ER_TABLE_NOT_LOCKED

724

create table t2 select * from t1;

725

# This should not be ever allowed as it will undermine

726

# lock-all-at-once approach

727

--error ER_TABLE_NOT_LOCKED

728

create table if not exists t2 select * from t1;

729

unlock tables;

730

lock table t1 read, t2 read;

731

--error ER_TABLE_NOT_LOCKED_FOR_WRITE

732

create table t2 select * from t1;

733

--error ER_TABLE_NOT_LOCKED_FOR_WRITE

734

create table if not exists t2 select * from t1;

735

unlock tables;

736

lock table t1 read, t2 write;

737

--error ER_TABLE_EXISTS_ERROR

738

create table t2 select * from t1;

739

# This is the only case which really works.

740

create table if not exists t2 select * from t1;

741

select * from t1;

742

unlock tables;

743

drop table t2;

744

745

# OTOH CREATE TEMPORARY TABLE ... SELECT should work

746

# well under LOCK TABLES.

747

lock tables t1 read;

748

create temporary table t2 select * from t1;

749

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

750

select * from t2;

751

unlock tables;

752

drop table t1, t2;

753

754

755

717

756

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

718

757

719

758

create table t1 (upgrade int);

869

908

);

870

909

871

910

# Check that the table is not corrupted

872

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

873

911

show create table t1;

874

912

flush tables;

875

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

876

913

show create table t1;

877

914

878

915

# Repeat test using ALTER to add indexes

1018

1055

add key a064_long_123456789_123456789_123456789_123456789_123456789_1234 (

1019

1056

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

1020

1057

1021

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

1022

1058

show create table t1;

1023

1059

flush tables;

1024

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

1025

1060

show create table t1;

1026

1061

1027

1062

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

1028

1063

# to be rewritten to hit the limit

1029

1064

1030

1065

# Ensure limit is really 64 keys

1031

--error ER_TOO_MANY_KEYS

1066

--error 1069

1032

1067

alter table t1 add key

1033

1068

a065_long_123456789_123456789_123456789_123456789_123456789_1234 (

1034

1069

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

1042

1077

c16 int, c17 int);

1043

1078

1044

1079

# Get error for max key parts

1045

--error ER_TOO_MANY_KEY_PARTS

1080

--error 1070

1046

1081

alter table t1 add key i1 (

1047

1082

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

1048

1083

1049

1084

# Get error for max key-name length

1050

--error ER_TOO_LONG_IDENT

1085

--error 1059

1051

1086

alter table t1 add key

1052

1087

a001_long_123456789_123456789_123456789_123456789_123456789_12345 (c1);

1053

1088

1054

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

1055

1089

show create table t1;

1056

1090

1057

1091

drop table t1;

1101

1135

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

1102

1136

# precedence

1103

1137

1104

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

1105

#SHOW INDEX FROM t1;

1138

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

1139

SHOW INDEX FROM t1;

1106

1140

DROP TABLE t1;

1107

1141

1108

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

1109

#SHOW INDEX FROM t1;

1142

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

1143

SHOW INDEX FROM t1;

1110

1144

DROP TABLE t1;

1111

1145

1112

1146

1152

1186

# Test incorrect database names

1153

1187

1154

1188

1155

--error ER_WRONG_DB_NAME

1189

--error 1102

1156

1190

CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1157

--error ER_WRONG_DB_NAME

1191

--error 1102

1158

1192

DROP DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1159

1193

1160

1194

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

1161

# --error ER_BAD_DB_ERROR

1195

# --error 1049

1162

1196

# RENAME DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa TO a;

1163

# --error ER_WRONG_DB_NAME

1197

# --error 1102

1164

1198

# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1165

1199

# create database mysqltest;

1166

# --error ER_WRONG_DB_NAME

1200

# --error 1102

1167

1201

# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1168

1202

# drop database mysqltest;

1169

1203

1170

--error ER_WRONG_DB_NAME

1204

--error 1102

1171

1205

USE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1172

#--error ER_WRONG_DB_NAME

1206

--error 1102

1173

1207

SHOW CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;

1174

1208

1175

1176

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

1177

1209

1210

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

1211

1178

1212

1179

1213

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

1180

1214

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

1181

1215

select database();

1182

1216

use test;

1183

1217

1184

select SCHEMA_NAME from data_dictionary.schemas

1218

select SCHEMA_NAME from information_schema.schemata

1185

1219

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

1186

1220

1187

1221

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

1195

1229

# database, table, field, key

1196

1230

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

1197

1231

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/

1232

select TABLE_NAME from information_schema.tables where

1233

table_schema='test';

1234

1235

select COLUMN_NAME from information_schema.columns where

1236

table_schema='test';

1237

1238

select INDEX_NAME from information_schema.statistics where

1239

table_schema='test';

1240

1208

1241

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

1209

1242

1210

1243

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

1211

1244

1212

1245

1213

1246

1214

1247

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

1215

1248

1216

--error ER_CANT_CREATE_TABLE

1217

create table t1 like data_dictionary.processlist;

1218

create table t1 like data_dictionary.processlist engine=innodb;

1249

create table t1 like information_schema.processlist;

1219

1250

show create table t1;

1220

1251

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;

1252

create temporary table t1 like information_schema.processlist;

1224

1253

show create table t1;

1225

1254

drop table t1;

1226

1255

1248

1277

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

1249

1278

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

1250

1279

c6 VARCHAR(255))

1251

COLLATE=utf8_bin;

1280

COLLATE utf8_bin;

1252

1281

1253

1282

--echo

1254

1283

1255

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

1256

1284

SHOW CREATE TABLE t1;

1257

1285

1258

1286

--echo

1261

1289

1262

1290

--echo

1263

1291

1264

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

1265

1292

SHOW CREATE TABLE t2;

1266

1293

1267

1294

--echo

1292

1319

--echo

1293

1320

1294

1321

--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');

1322

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

1299

1323

drop table t2;

1300

1324

1301

1325

--echo

1315

1339

--echo

1316

1340

1317

1341

CREATE TABLE t3(c1 DATETIME NOT NULL);

1318

--error ER_INVALID_DATETIME_VALUE # Bad datetime

1342

--error 1686 # Bad datetime

1319

1343

INSERT INTO t3 VALUES (0);

1320

1344

1321

1345

--echo

Older »