~drizzle-trunk/drizzle/development

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

--echo -- Here we enable metadata just to check that the collation of the

--echo -- resultset is non-binary for string type. This should be changed

--echo -- after Bug#29394 is implemented.

--enable_metadata

analyze table t1;

--disable_metadata

show index from t1;

--echo -- Here we enable metadata just to check that the collation of the

--echo -- resultset is non-binary for string type. This should be changed

--echo -- after Bug#29394 is implemented.

--enable_metadata

repair table t1;

--disable_metadata

show index from t1;

drop table t1;

# Test of SHOW CREATE

create temporary table t1 (a int not null);

show create table t1;

alter table t1 rename t2;

show create table t2;

drop table t2;

create table t1 (

test_set set( 'val1', 'val2', 'val3' ) not null default '',

100

name char(20) default 'O''Brien' comment 'O''Brien as default',

101

c int not null comment 'int column',

102

`c-b` int comment 'name with a minus',

103

`space 2` int comment 'name with a space'

104

) comment = 'it\'s a table' ;

105

show create table t1;

106

set sql_quote_show_create=0;

107

show create table t1;

108

set sql_quote_show_create=1;

109

show full columns from t1;

110

drop table t1;

111

112

create table t1 (a int not null, unique aa (a));

113

show create table t1;

114

drop table t1;

115

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

116

show create table t1;

117

drop table t1;

118

119

flush tables;

120

show open tables;

121

create table t1(n int);

122

insert into t1 values (1);

123

show open tables;

124

drop table t1;

125

126

create table t1 (a int not null, b VARCHAR(10), INDEX (b) ) AVG_ROW_LENGTH=10 CHECKSUM=1 COMMENT="test" ENGINE=MYISAM MIN_ROWS=10 MAX_ROWS=100 PACK_KEYS=1 DELAY_KEY_WRITE=1 ROW_FORMAT=fixed;

127

show create table t1;

128

alter table t1 MAX_ROWS=200 ROW_FORMAT=dynamic PACK_KEYS=0;

129

show create table t1;

130

ALTER TABLE t1 AVG_ROW_LENGTH=0 CHECKSUM=0 COMMENT="" MIN_ROWS=0 MAX_ROWS=0 PACK_KEYS=DEFAULT DELAY_KEY_WRITE=0 ROW_FORMAT=default;

131

show create table t1;

132

drop table t1;

133

134

create table t1 (a decimal(9,2), b decimal (9,0), e double(9,2), f double(5,0), h float(3,2), i float(3,0));

135

show columns from t1;

136

show full columns from t1;

137

drop table t1;

138

139

140

# Do a create table that tries to cover all types and options

141

142

create table t1 (

143

type_bool bool not null default 0,

144

type_tiny tinyint not null auto_increment primary key,

145

type_short smallint(3),

146

type_mediumint mediumint,

147

type_bigint bigint,

148

type_decimal decimal(5,2),

149

type_numeric numeric(5,2),

150

empty_char char(0),

151

type_char char(2),

152

type_varchar varchar(10),

153

type_timestamp timestamp not null,

154

type_date date not null default '0000-00-00',

155

type_time time not null default '00:00:00',

156

type_datetime datetime not null default '0000-00-00 00:00:00',

157

type_year year,

158

type_enum enum ('red', 'green', 'blue'),

159

type_set enum ('red', 'green', 'blue'),

160

type_tinyblob tinyblob,

161

type_blob blob,

162

type_medium_blob mediumblob,

163

type_long_blob longblob,

164

index(type_short)

165

) AVG_ROW_LENGTH=10 CHECKSUM=1 COMMENT="test" ENGINE=MYISAM MIN_ROWS=10 MAX_ROWS=100 PACK_KEYS=1 DELAY_KEY_WRITE=1 ROW_FORMAT=fixed CHARSET=latin1;

166

167

# Not tested above: RAID_# UNION INSERT_METHOD DATA DIRECTORY INDEX DIRECTORY

168

show create table t1;

169

insert into t1 (type_timestamp) values ("2003-02-07 10:00:01");

170

select * from t1;

171

drop table t1;

172

173

174

# Check metadata

175

176

create table t1 (a int not null);

177

create table t2 select max(a) from t1;

178

show columns from t2;

179

drop table t1,t2;

180

181

# Check auto conversions of types

182

183

create table t1 (c decimal, d double, f float, r real);

184

show columns from t1;

185

drop table t1;

186

187

create table t1 (c decimal(3,3), d double(3,3), f float(3,3));

188

show columns from t1;

189

drop table t1;

190

191

192

# Test for Bug #2593 "SHOW CREATE TABLE doesn't properly double quotes"

193

194

195

CREATE TABLE ```ab``cd``` (i INT);

196

SHOW CREATE TABLE ```ab``cd```;

197

DROP TABLE ```ab``cd```;

198

199

CREATE TABLE ```ab````cd``` (i INT);

200

SHOW CREATE TABLE ```ab````cd```;

201

DROP TABLE ```ab````cd```;

202

203

CREATE TABLE ```a` (i INT);

204

SHOW CREATE TABLE ```a`;

205

DROP TABLE ```a`;

206

207

CREATE TABLE `a.1` (i INT);

208

SHOW CREATE TABLE `a.1`;

209

DROP TABLE `a.1`;

210

211

212

213

# Test for bug #2719 "Heap tables status shows wrong or missing data."

214

215

216

select @@max_heap_table_size;

217

218

CREATE TABLE t1 (

219

a int(11) default NULL,

220

KEY a USING BTREE (a)

221

) ENGINE=HEAP;

222

223

CREATE TABLE t2 (

224

b int(11) default NULL,

225

index(b)

226

) ENGINE=HEAP;

227

228

CREATE TABLE t3 (

229

a int(11) default NULL,

230

b int(11) default NULL,

231

KEY a USING BTREE (a),

232

index(b)

233

) ENGINE=HEAP;

234

235

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

236

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

237

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

238

--replace_column 6 # 7 # 8 # 9 #

239

show table status;

240

insert into t1 values (3),(4);

241

insert into t2 values (3),(4);

242

insert into t3 values (3,3),(4,4);

243

--replace_column 6 # 7 # 8 # 9 #

244

show table status;

245

insert into t1 values (5);

246

insert into t2 values (5);

247

insert into t3 values (5,5);

248

--replace_column 6 # 7 # 8 # 9 #

249

show table status;

250

delete from t1 where a=3;

251

delete from t2 where b=3;

252

delete from t3 where a=3;

253

--replace_column 6 # 7 # 8 # 9 # 10 #

254

show table status;

255

truncate table t1;

256

truncate table t2;

257

truncate table t3;

258

--replace_column 6 # 7 # 8 # 9 #

259

show table status;

260

insert into t1 values (5);

261

insert into t2 values (5);

262

insert into t3 values (5,5);

263

--replace_column 6 # 7 # 8 # 9 #

264

show table status;

265

delete from t1 where a=5;

266

delete from t2 where b=5;

267

delete from t3 where a=5;

268

--replace_column 6 # 7 # 8 # 9 # 10 #

269

show table status;

270

271

drop table t1, t2, t3;

272

273

# Test that USING <keytype> is always shown in SHOW CREATE TABLE when it was

274

# specified during table creation, but not otherwise. (Bug #7235)

275

CREATE TABLE t1 (i int, KEY (i)) ENGINE=MEMORY;

276

SHOW CREATE TABLE t1;

277

DROP TABLE t1;

278

CREATE TABLE t1 (i int, KEY USING HASH (i)) ENGINE=MEMORY;

279

SHOW CREATE TABLE t1;

280

DROP TABLE t1;

281

CREATE TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MEMORY;

282

SHOW CREATE TABLE t1;

283

DROP TABLE t1;

284

CREATE TABLE t1 (i int, KEY (i)) ENGINE=MyISAM;

285

SHOW CREATE TABLE t1;

286

DROP TABLE t1;

287

CREATE TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MyISAM;

288

SHOW CREATE TABLE t1;

289

DROP TABLE t1;

290

# Test that when an index is created with the default key algorithm and

291

# altered to another storage engine, it gets the default key algorithm

292

# for that storage engine, but when it is specified, the specified type is

293

# preserved.

294

CREATE TABLE t1 (i int, KEY (i)) ENGINE=MyISAM;

295

SHOW CREATE TABLE t1;

296

ALTER TABLE t1 ENGINE=MEMORY;

297

SHOW CREATE TABLE t1;

298

DROP TABLE t1;

299

CREATE TABLE t1 (i int, KEY USING BTREE (i)) ENGINE=MyISAM;

300

SHOW CREATE TABLE t1;

301

ALTER TABLE t1 ENGINE=MEMORY;

302

SHOW CREATE TABLE t1;

303

DROP TABLE t1;

304

305

# Test for BUG#9439 "Reporting wrong datatype for sub_part on show index"

306

CREATE TABLE t1(

307

field1 text NOT NULL,

308

PRIMARY KEY(field1(1000))

309

);

310

--enable_metadata

311

show index from t1;

312

--disable_metadata

313

drop table t1;

314

315

# Test for BUG#11635: mysqldump exports TYPE instead of USING for HASH

316

create table t1 (

317

c1 int NOT NULL,

318

c2 int NOT NULL,

319

PRIMARY KEY USING HASH (c1),

320

INDEX USING BTREE(c2)

321

);

322

SHOW CREATE TABLE t1;

323

DROP TABLE t1;

324

325

# Test for BUG#93: 4.1 protocl crash on corupted frm and SHOW TABLE STATUS

326

327

flush tables;

328

329

# Create a junk frm file on disk

330

system echo "this is a junk file for test" >> $MYSQLTEST_VARDIR/master-data/test/t1.frm ;

331

--replace_column 6 # 7 # 8 # 9 #

332

SHOW TABLE STATUS like 't1';

333

--error 1033

334

show create table t1;

335

drop table if exists t1;

336

--error 1,0

337

--remove_file $MYSQLTEST_VARDIR/master-data/test/t1.frm

338

339

340

# BUG 12183 - SHOW OPEN TABLES behavior doesn't match grammar

341

# First we close all open tables with FLUSH tables and then we open some.

342

343

344

--echo

345

--echo # Bug#12183: SHOW OPEN TABLES behavior doesn't match grammar.

346

--echo

347

348

# NOTE: SHOW OPEN TABLES does not sort result list by database or table names.

349

# Tables are listed in the order they were opened. We can not use the system

350

# database (mysql) for the test here, because we have no control over the order

351

# of opening tables in it. Consequently, we can not use 'SHOW OPEN TABLES'.

352

353

--disable_warnings

354

DROP DATABASE IF EXISTS mysqltest1;

355

--enable_warnings

356

357

CREATE DATABASE mysqltest1;

358

use mysqltest1;

359

360

--echo

361

362

CREATE TABLE t1(a INT);

363

CREATE TABLE t2(a INT);

364

365

--echo

366

367

--disable_ps_protocol

368

FLUSH TABLES;

369

370

--echo

371

372

SELECT 1 FROM t1;

373

SELECT 1 FROM t2;

374

375

--echo

376

377

SHOW OPEN TABLES FROM mysqltest1;

378

379

--echo

380

381

SHOW OPEN TABLES FROM mysqltest1 LIKE 'z%';

382

383

--echo

384

385

SHOW OPEN TABLES FROM mysqltest1 LIKE 't1%';

386

387

--echo

388

389

SHOW OPEN TABLES FROM mysqltest1 LIKE '%1%';

390

391

--echo

392

393

FLUSH TABLES;

394

--enable_ps_protocol

395

396

--echo

397

398

DROP DATABASE mysqltest1;

399

use test;

400

401

--echo

402

403

404

# BUG #12591 (SHOW TABLES FROM dbname produces wrong error message)

405

406

--error 1049

407

SHOW TABLES FROM non_existing_database;

408

409

--echo End of 4.1 tests

410

411

412

# Check that SHOW TABLES and SHOW COLUMNS give a error if there is no

413

# referenced database and table respectively.

414

415

--error ER_BAD_DB_ERROR

416

SHOW TABLES FROM no_such_database;

417

--error ER_NO_SUCH_TABLE

418

SHOW COLUMNS FROM no_such_table;

419

420

421

422

# Bug #28808: log_queries_not_using_indexes variable dynamic change is ignored

423

424

flush status;

425

show variables like "log_queries_not_using_indexes";

426

select 1 from information_schema.tables limit 1;

427

show status like 'slow_queries';

428

set global log_queries_not_using_indexes=OFF;

429

show variables like "log_queries_not_using_indexes";

430

select 1 from information_schema.tables limit 1;

431

show status like 'slow_queries';

432

set global log_queries_not_using_indexes=ON;

433

show variables like "log_queries_not_using_indexes";

434

select 1 from information_schema.tables limit 1;

435

show status like 'slow_queries';

436

437

438

# Bug #30088: Can't disable myisam-recover by a value of ""

439

440

show variables like 'myisam_recover_options';

441

442

--echo End of 5.0 tests

443

444

--disable_result_log

445

SHOW AUTHORS;

446

--enable_result_log

447

448

449

450

# Ensure that show plugin code is tested

451

452

453

--disable_result_log

454

show plugins;

455

--enable_result_log

456

457

458

# Bug #19874: SHOW COLUMNS and SHOW KEYS handle identifiers containing

459

# \ incorrectly

460

461

create database `mysqlttest\1`;

462

create table `mysqlttest\1`.`a\b` (a int);

463

show tables from `mysqlttest\1`;

464

show fields from `mysqlttest\1`.`a\b`;

465

show columns from `a\b` from `mysqlttest\1`;

466

show keys from `mysqlttest\1`.`a\b`;

467

drop table `mysqlttest\1`.`a\b`;

468

drop database `mysqlttest\1`;

469

470

471

# Bug#24392: SHOW ENGINE MUTEX STATUS is a synonym for SHOW INNODB STATUS

472

473

474

--error ER_UNKNOWN_STORAGE_ENGINE

475

show engine foobar status;

476

--error ER_UNKNOWN_STORAGE_ENGINE

477

show engine foobar logs;

478

--error ER_UNKNOWN_STORAGE_ENGINE

479

show engine foobar mutex;

480

481

--error ER_UNKNOWN_STORAGE_ENGINE

482

show engine mutex status;

483

484

show engine csv status;

485

show engine csv logs;

486

show engine csv mutex;

487

488

# Bug#25081 SHOW FULL TABLES on table with latin chars in name fails

489

490

set names utf8;

491

--disable_warnings

492

drop table if exists `été`;

493

--enable_warnings

494

create table `été` (field1 int);

495

show full tables;

496

drop table `été`;

497

set names latin1;

498

499

500

# Bug#26402 Server crashes with old-style named table

501

502

--error ER_NO_SUCH_TABLE,ER_FILE_NOT_FOUND

503

show columns from `#mysql50#????????`;

504

505

--echo End of 5.1 tests

Older »