~ubuntu-branches/ubuntu/precise/mysql-5.1/precise

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MB

179

ENGINE = NDB' at line 3

180

CREATE LOGFILE GROUP lg2

181

ADD UNDOFILE 'x.dat'

182

INITIAL_SIZE 10 M

183

ENGINE = NDB;

184

ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'M

185

ENGINE = NDB' at line 3

186

CREATE LOGFILE GROUP lg2

187

ADD UNDOFILE 'x.dat'

188

INITIAL_SIZE 1000000000000K

189

ENGINE = NDB;

190

ERROR HY000: The size number was correct but we don't allow the digit part to be more than 2 billion

191

DROP TABLE t1;

192

create tablespace ts2

193

add datafile 'datafile2_1.dat'

194

use logfile group lg1

195

initial_size 12M

196

engine ndb;

197

CREATE TABLE City (

198

ID int(11) NOT NULL AUTO_INCREMENT,

199

Name char(35) NOT NULL,

200

CountryCode char(3) NOT NULL,

201

District char(20) NOT NULL,

202

Population int(11) NOT NULL,

203

PRIMARY KEY (ID)

204

) ENGINE=ndbcluster

205

tablespace ts2

206

storage disk;

207

alter tablespace ts2

208

drop datafile 'datafile2_1.dat'

209

engine ndb;

210

insert

211

into City (Name,CountryCode,District,Population)

212

values ('BeiJing','CN','Beijing',2000);

213

ERROR HY000: Got error 1602 'No datafile in tablespace' from NDBCLUSTER

214

drop tablespace ts2

215

engine ndb;

216

ERROR HY000: Failed to drop TABLESPACE

217

drop table City;

218

drop tablespace ts2

219

engine ndb;

220

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;

221

INSERT INTO t1 VALUES (1,'1','1'), (2,'2','2'), (3,'3','3');

222

BEGIN;

223

UPDATE t1 SET b = '2' WHERE a = 1;

224

SELECT b FROM t1 WHERE a = 1;

225

226

227

SELECT * FROM t1 WHERE a = 1;

228

a b c

229

1 2 1

230

UPDATE t1 SET c = '2' WHERE a = 1;

231

SELECT b FROM t1 WHERE a = 1;

232

233

234

SELECT * FROM t1 WHERE a = 1;

235

a b c

236

1 2 2

237

UPDATE t1 SET b = '3' WHERE a = 1;

238

SELECT b FROM t1 WHERE a = 1;

239

240

241

SELECT * FROM t1 WHERE a = 1;

242

a b c

243

1 3 2

244

COMMIT;

245

SELECT * FROM t1 ORDER BY 1;

246

a b c

247

1 3 2

248

2 2 2

249

3 3 3

250

BEGIN;

251

UPDATE t1 SET c = '3' WHERE a = 1;

252

SELECT b FROM t1 WHERE a = 1;

253

254

255

SELECT * FROM t1 WHERE a = 1;

256

a b c

257

1 3 3

258

UPDATE t1 SET b = '4' WHERE a = 1;

259

SELECT b FROM t1 WHERE a = 1;

260

261

262

SELECT * FROM t1 WHERE a = 1;

263

a b c

264

1 4 3

265

UPDATE t1 SET c = '4' WHERE a = 1;

266

SELECT b FROM t1 WHERE a = 1;

267

268

269

SELECT * FROM t1 WHERE a = 1;

270

a b c

271

1 4 4

272

COMMIT;

273

SELECT * FROM t1 ORDER BY 1;

274

a b c

275

1 4 4

276

2 2 2

277

3 3 3

278

UPDATE t1 SET b = '5' WHERE a = 1;

279

SELECT * FROM t1 ORDER BY 1;

280

a b c

281

1 5 4

282

2 2 2

283

3 3 3

284

UPDATE t1 SET b = '6' WHERE b = '5';

285

SELECT * FROM t1 ORDER BY 1;

286

a b c

287

1 6 4

288

2 2 2

289

3 3 3

290

UPDATE t1 SET b = '7'WHERE c = '4';

291

SELECT * FROM t1 ORDER BY 1;

292

a b c

293

1 7 4

294

2 2 2

295

3 3 3

296

UPDATE t1 SET c = '5' WHERE a = 1;

297

SELECT * FROM t1 ORDER BY 1;

298

a b c

299

1 7 5

300

2 2 2

301

3 3 3

302

UPDATE t1 SET c = '6' WHERE b = '7';

303

SELECT * FROM t1 ORDER BY 1;

304

a b c

305

1 7 6

306

2 2 2

307

3 3 3

308

UPDATE t1 SET c = '7' WHERE c = '6';

309

SELECT * FROM t1 ORDER BY 1;

310

a b c

311

1 7 7

312

2 2 2

313

3 3 3

314

DROP TABLE t1;

315

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;

316

INSERT INTO t1 VALUE (1,'1','1'), (2,'2','2'), (3,'3','3');

317

BEGIN;

318

UPDATE t1 SET b = '2' WHERE a = 1;

319

SELECT b FROM t1 WHERE a = 1;

320

321

322

SELECT * FROM t1 WHERE a = 1;

323

a b c

324

1 2 1

325

UPDATE t1 SET c = '2' WHERE a = 1;

326

SELECT b FROM t1 WHERE a = 1;

327

328

329

SELECT * FROM t1 WHERE a = 1;

330

a b c

331

1 2 2

332

UPDATE t1 SET b = '3' WHERE a = 1;

333

SELECT b FROM t1 WHERE a = 1;

334

335

336

SELECT * FROM t1 WHERE a = 1;

337

a b c

338

1 3 2

339

COMMIT;

340

SELECT * FROM t1 ORDER BY 1;

341

a b c

342

1 3 2

343

2 2 2

344

3 3 3

345

BEGIN;

346

UPDATE t1 SET c = '3' WHERE a = 1;

347

SELECT b FROM t1 WHERE a = 1;

348

349

350

SELECT * FROM t1 WHERE a = 1;

351

a b c

352

1 3 3

353

UPDATE t1 SET b = '4' WHERE a = 1;

354

SELECT b FROM t1 WHERE a = 1;

355

356

357

SELECT * FROM t1 WHERE a = 1;

358

a b c

359

1 4 3

360

UPDATE t1 SET c = '4' WHERE a = 1;

361

SELECT b FROM t1 WHERE a = 1;

362

363

364

SELECT * FROM t1 WHERE a = 1;

365

a b c

366

1 4 4

367

COMMIT;

368

SELECT * FROM t1 ORDER BY 1;

369

a b c

370

1 4 4

371

2 2 2

372

3 3 3

373

UPDATE t1 SET b = '5' WHERE a = 1;

374

SELECT * FROM t1 ORDER BY 1;

375

a b c

376

1 5 4

377

2 2 2

378

3 3 3

379

UPDATE t1 SET b = '6' WHERE b = '5';

380

SELECT * FROM t1 ORDER BY 1;

381

a b c

382

1 6 4

383

2 2 2

384

3 3 3

385

UPDATE t1 SET b = '7' WHERE c = '4';

386

SELECT * FROM t1 ORDER BY 1;

387

a b c

388

1 7 4

389

2 2 2

390

3 3 3

391

UPDATE t1 SET c = '5' WHERE a = 1;

392

SELECT * FROM t1 ORDER BY 1;

393

a b c

394

1 7 5

395

2 2 2

396

3 3 3

397

UPDATE t1 SET c = '6' WHERE b = '7';

398

SELECT * FROM t1 ORDER BY 1;

399

a b c

400

1 7 6

401

2 2 2

402

3 3 3

403

UPDATE t1 SET c = '7' WHERE c = '6';

404

SELECT * FROM t1 ORDER BY 1;

405

a b c

406

1 7 7

407

2 2 2

408

3 3 3

409

DELETE FROM t1;

410

INSERT INTO t1 VALUES (3,'1','1');

411

BEGIN;

412

UPDATE t1 SET b = b + 2 WHERE A = 3;

413

DELETE FROM t1 WHERE A = 3;

414

INSERT INTO t1 VALUES (3,'0','0');

415

COMMIT;

416

SELECT * from t1 ORDER BY 1;

417

a b c

418

3 0 0

419

DROP TABLE t1;

420

CREATE TABLE t1 (

421

a INT NOT NULL PRIMARY KEY,

422

b TEXT NOT NULL

423

) TABLESPACE ts1 STORAGE DISK ENGINE=NDBCLUSTER;

424

set @x0 = '01234567012345670123456701234567';

425

set @x0 = concat(@x0,@x0,@x0,@x0,@x0,@x0,@x0,@x0);

426

set @b1 = 'b1';

427

set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);

428

set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);

429

set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);

430

set @b1 = concat(@b1,@x0);

431

set @b2 = 'b2';

432

set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);

433

set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);

434

set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);

435

set @b2 = concat(@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2,@b2);

436

INSERT INTO t1 VALUES(1,@b1);

437

INSERT INTO t1 VALUES(2,@b2);

438

SELECT a,length(b),substr(b,1+2*900,2) FROM t1 WHERE a=1;

439

a length(b) substr(b,1+2*900,2)

440

1 2256 b1

441

SELECT a,length(b),substr(b,1+2*9000,2) FROM t1 WHERE a=2;

442

a length(b) substr(b,1+2*9000,2)

443

2 20000 b2

444

UPDATE t1 SET b=@b2 WHERE a=1;

445

UPDATE t1 SET b=@b1 WHERE a=2;

446

SELECT a,length(b),substr(b,1+2*9000,2) FROM t1 WHERE a=1;

447

a length(b) substr(b,1+2*9000,2)

448

1 20000 b2

449

SELECT a,length(b),substr(b,1+2*900,2) FROM t1 WHERE a=2;

450

a length(b) substr(b,1+2*900,2)

451

2 2256 b1

452

UPDATE t1 SET b=concat(b,b) WHERE a=1;

453

UPDATE t1 SET b=concat(b,b) WHERE a=2;

454

SELECT a,length(b),substr(b,1+4*9000,2) FROM t1 WHERE a=1;

455

a length(b) substr(b,1+4*9000,2)

456

1 40000 b2

457

SELECT a,length(b),substr(b,1+4*900,2) FROM t1 WHERE a=2;

458

a length(b) substr(b,1+4*900,2)

459

2 4512 b1

460

DELETE FROM t1 WHERE a=1;

461

DELETE FROM t1 WHERE a=2;

462

SELECT COUNT(*) FROM t1;

463

COUNT(*)

464

465

DROP TABLE t1;

466

CREATE TABLE t1 (

467

a int NOT NULL,

468

b varchar(4000), -- must use 2 pages undo

469

PRIMARY KEY using hash (a)

470

)

471

TABLESPACE ts1 STORAGE DISK ENGINE=NDBCLUSTER;

472

set autocommit = 0;

473

insert into t1 values(0,'x');

474

insert into t1 values(1,'x');

475

insert into t1 values(2,'x');

476

insert into t1 values(3,'x');

477

insert into t1 values(4,'x');

478

insert into t1 values(5,'x');

479

insert into t1 values(6,'x');

480

insert into t1 values(7,'x');

481

insert into t1 values(8,'x');

482

delete from t1 where a = 0;

483

commit;

484

delete from t1;

485

begin;

486

insert into t1 values (1, 'x');

487

select * from t1;

488

a b

489

1 x

490

rollback;

491

set autocommit = 1;

492

drop table t1;

493

create table test.t1 (f1 varchar(50) primary key, f2 text,f3 int)

494

TABLESPACE ts1 STORAGE DISK engine=NDB;

495

insert into test.t1 (f1,f2,f3)VALUES("111111","aaaaaa",1);

496

insert into test.t1 (f1,f2,f3)VALUES("222222","bbbbbb",2);

497

select * from test.t1 order by f1;

498

f1 f2 f3

499

111111 aaaaaa 1

500

222222 bbbbbb 2

501

select f1,f2 from test.t1 order by f2;

502

f1 f2

503

111111 aaaaaa

504

222222 bbbbbb

505

select f2 from test.t1 order by f2;

506

507

aaaaaa

508

bbbbbb

509

select f1,f2 from test.t1 order by f1;

510

f1 f2

511

111111 aaaaaa

512

222222 bbbbbb

513

drop table test.t1;

514

CREATE TABLE t1 (

515

id int unsigned NOT NULL,

516

c1 int unsigned NOT NULL,

517

c2 int DEFAULT NULL,

518

PRIMARY KEY using hash (id),

519

UNIQUE KEY c1 using hash (c1))

520

TABLESPACE ts1 STORAGE DISK ENGINE=ndbcluster;

521

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

522

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

523

update t1 set c1 = 1 where id = 2;

524

ERROR 23000: Duplicate entry '1' for key 'c1'

525

drop table t1;

526

ALTER TABLESPACE ts1

527

DROP DATAFILE 'datafile.dat'

528

ENGINE = NDB;

529

ALTER TABLESPACE ts1

530

DROP DATAFILE 'datafile02.dat'

531

ENGINE = NDB;

532

DROP TABLESPACE ts1

533

ENGINE = NDB;

534

DROP LOGFILE GROUP lg1

535

ENGINE =NDB;

Older »