~ubuntu-branches/ubuntu/trusty/mysql-5.6/trusty

Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.

[connection master]

DROP TABLE IF EXISTS t1;

DROP TABLE IF EXISTS t2;

DROP TABLE IF EXISTS t3;

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

create table t2 (a int auto_increment, primary key (a), b int);

create table t3 (a int auto_increment, primary key (a), name varchar(64) not null, old_a int, old_b int, rand_value double not null);

create trigger t1 before insert on t1 for each row

begin

insert into t3 values (NULL, "t1", new.a, new.b, rand());

end|

create trigger t2 after insert on t2 for each row

begin

insert into t3 values (NULL, "t2", new.a, new.b, rand());

end|

insert into t3 values(100,"log",0,0,0);

SET @@RAND_SEED1=658490765, @@RAND_SEED2=635893186;

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

insert into t2 (b) values(last_insert_id());

insert into t2 values(3,0),(NULL,0);

insert into t2 values(NULL,0),(500,0);

select a,b, truncate(rand_value,4) from t1;

a b truncate(rand_value,4)

1 1 0.4320

2 2 0.3055

select * from t2;

a b

1 2

3 0

4 0

6 0

500 0

select a,name, old_a, old_b, truncate(rand_value,4) from t3;

a name old_a old_b truncate(rand_value,4)

100 log 0 0 0.0000

101 t1 1 1 0.3203

102 t1 0 2 0.5666

103 t2 1 2 0.9164

104 t2 3 0 0.8826

105 t2 4 0 0.6635

106 t2 6 0 0.6699

107 t2 500 0 0.3593

--- On slave --

select a,b, truncate(rand_value,4) from t1;

a b truncate(rand_value,4)

1 1 0.4320

2 2 0.3055

select * from t2;

a b

1 2

3 0

4 0

6 0

500 0

select a,name, old_a, old_b, truncate(rand_value,4) from t3;

a name old_a old_b truncate(rand_value,4)

100 log 0 0 0.0000

101 t1 1 1 0.3203

102 t1 0 2 0.5666

103 t2 1 2 0.9164

104 t2 3 0 0.8826

105 t2 4 0 0.6635

106 t2 6 0 0.6699

107 t2 500 0 0.3593

drop table t1,t2,t3;

select get_lock("bug12480",2);

get_lock("bug12480",2)

create table t1 (a datetime,b datetime, c datetime);

drop function if exists bug12480;

create function bug12480() returns datetime

begin

set @a=get_lock("bug12480",2);

return now();

end|

create trigger t1_first before insert on t1

for each row begin

set @a=get_lock("bug12480",2);

set new.b= now();

set new.c= bug12480();

end

insert into t1 set a = now();

select a=b && a=c from t1;

a=b && a=c

SELECT routine_name, definer

FROM information_schema.routines

WHERE routine_name = 'bug12480';

routine_name definer

bug12480 root@localhost

SELECT trigger_name, definer

FROM information_schema.triggers

WHERE trigger_name = 't1_first';

100

trigger_name definer

101

t1_first root@localhost

102

103

--- On slave --

104

SELECT routine_name, definer

105

FROM information_schema.routines

106

WHERE routine_name = 'bug12480';

107

routine_name definer

108

bug12480 root@localhost

109

SELECT trigger_name, definer

110

FROM information_schema.triggers

111

WHERE trigger_name = 't1_first';

112

trigger_name definer

113

t1_first root@localhost

114

select a=b && a=c from t1;

115

a=b && a=c

116

117

test

118

119

truncate table t1;

120

drop trigger t1_first;

121

insert into t1 values ("2003-03-03","2003-03-03","2003-03-03"),(bug12480(),bug12480(),bug12480()),(now(),now(),now());

122

select a=b && a=c from t1;

123

a=b && a=c

124

125

126

127

drop function bug12480;

128

drop table t1;

129

create table t1 (i int);

130

create table t2 (i int);

131

create trigger tr1 before insert on t1 for each row

132

begin

133

insert into t2 values (1);

134

end|

135

create database other;

136

use other;

137

insert into test.t1 values (1);

138

use test;

139

drop table t1,t2;

140

drop database other;

141

test case for BUG#13227

142

-------------------

143

144

-------------------

145

drop table if exists t110;

146

drop table if exists t210,t310;

147

create table t110 (f1 int) /* 2 replicate */;

148

insert into t110 values (-5);

149

insert into t110 values (-4);

150

insert into t110 values (-3);

151

insert into t110 values (-2);

152

insert into t110 values (-1);

153

select * from t110;

154

155

-5

156

-4

157

-3

158

-2

159

-1

160

create trigger trg110 before update on t110 /* slave local */

161

for each row

162

begin

163

DECLARE r integer;

164

SELECT f2 INTO r FROM t210 where f1=NEW.f1;

165

INSERT INTO t310 values (r);

166

end|

167

create table t210 (f1 int, f2 int) /* slave local */;

168

create table t310 (f3 int) /* slave local */;

169

insert into t210 values (5, 5*100);

170

insert into t210 values (4, 4*100);

171

insert into t210 values (3, 3*100);

172

insert into t210 values (2, 2*100);

173

insert into t210 values (1, 1*100);

174

select * from t210;

175

f1 f2

176

5 500

177

4 400

178

3 300

179

2 200

180

1 100

181

UPDATE t110 SET f1=5 where f1=-5;

182

SELECT * from t110 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;

183

184

185

-4

186

-3

187

-2

188

-1

189

SELECT * from t310 /* must be f3 5*100 */;

190

191

500

192

UPDATE t110 SET f1=5 where f1=-5;

193

UPDATE t110 SET f1=4 where f1=-4;

194

UPDATE t110 SET f1=3 where f1=-3;

195

UPDATE t110 SET f1=2 where f1=-2;

196

UPDATE t110 SET f1=1 where f1=-1;

197

SELECT * from t110 /* must be f1 5 ... 1 */;

198

199

200

201

202

203

204

SELECT * from t310 /* must be f3 5 * 100 ... 100 */;

205

206

500

207

400

208

300

209

200

210

100

211

drop trigger trg110;

212

drop table t210,t310;

213

drop table t110;

214

-------------------

215

216

-------------------

217

drop table if exists t19;

218

drop table if exists t29,t39;

219

create table t19 (f1 int) /* 2 replicate */;

220

insert into t19 values (-5);

221

insert into t19 values (-4);

222

insert into t19 values (-3);

223

insert into t19 values (-2);

224

insert into t19 values (-1);

225

select * from t19;

226

227

-5

228

-4

229

-3

230

-2

231

-1

232

create trigger trg19 before update on t19 /* slave local */

233

for each row

234

begin

235

DECLARE r integer;

236

SELECT f2 INTO r FROM t29 where f1=NEW.f1;

237

INSERT INTO t39 values (r);

238

end|

239

create table t29 (f1 int, f2 int) /* slave local */;

240

create table t39 (f3 int) /* slave local */;

241

insert into t29 values (5, 5*100);

242

insert into t29 values (4, 4*100);

243

insert into t29 values (3, 3*100);

244

insert into t29 values (2, 2*100);

245

insert into t29 values (1, 1*100);

246

select * from t29;

247

f1 f2

248

5 500

249

4 400

250

3 300

251

2 200

252

1 100

253

UPDATE t19 SET f1=5 where f1=-5;

254

SELECT * from t19 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;

255

256

257

-4

258

-3

259

-2

260

-1

261

SELECT * from t39 /* must be f3 5*100 */;

262

263

500

264

UPDATE t19 SET f1=5 where f1=-5;

265

UPDATE t19 SET f1=4 where f1=-4;

266

UPDATE t19 SET f1=3 where f1=-3;

267

UPDATE t19 SET f1=2 where f1=-2;

268

UPDATE t19 SET f1=1 where f1=-1;

269

SELECT * from t19 /* must be f1 5 ... 1 */;

270

271

272

273

274

275

276

SELECT * from t39 /* must be f3 5 * 100 ... 100 */;

277

278

500

279

400

280

300

281

200

282

100

283

drop trigger trg19;

284

drop table t29,t39;

285

drop table t19;

286

-------------------

287

288

-------------------

289

drop table if exists t18;

290

drop table if exists t28,t38;

291

create table t18 (f1 int) /* 2 replicate */;

292

insert into t18 values (-5);

293

insert into t18 values (-4);

294

insert into t18 values (-3);

295

insert into t18 values (-2);

296

insert into t18 values (-1);

297

select * from t18;

298

299

-5

300

-4

301

-3

302

-2

303

-1

304

create trigger trg18 before update on t18 /* slave local */

305

for each row

306

begin

307

DECLARE r integer;

308

SELECT f2 INTO r FROM t28 where f1=NEW.f1;

309

INSERT INTO t38 values (r);

310

end|

311

create table t28 (f1 int, f2 int) /* slave local */;

312

create table t38 (f3 int) /* slave local */;

313

insert into t28 values (5, 5*100);

314

insert into t28 values (4, 4*100);

315

insert into t28 values (3, 3*100);

316

insert into t28 values (2, 2*100);

317

insert into t28 values (1, 1*100);

318

select * from t28;

319

f1 f2

320

5 500

321

4 400

322

3 300

323

2 200

324

1 100

325

UPDATE t18 SET f1=5 where f1=-5;

326

SELECT * from t18 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;

327

328

329

-4

330

-3

331

-2

332

-1

333

SELECT * from t38 /* must be f3 5*100 */;

334

335

500

336

UPDATE t18 SET f1=5 where f1=-5;

337

UPDATE t18 SET f1=4 where f1=-4;

338

UPDATE t18 SET f1=3 where f1=-3;

339

UPDATE t18 SET f1=2 where f1=-2;

340

UPDATE t18 SET f1=1 where f1=-1;

341

SELECT * from t18 /* must be f1 5 ... 1 */;

342

343

344

345

346

347

348

SELECT * from t38 /* must be f3 5 * 100 ... 100 */;

349

350

500

351

400

352

300

353

200

354

100

355

drop trigger trg18;

356

drop table t28,t38;

357

drop table t18;

358

-------------------

359

360

-------------------

361

drop table if exists t17;

362

drop table if exists t27,t37;

363

create table t17 (f1 int) /* 2 replicate */;

364

insert into t17 values (-5);

365

insert into t17 values (-4);

366

insert into t17 values (-3);

367

insert into t17 values (-2);

368

insert into t17 values (-1);

369

select * from t17;

370

371

-5

372

-4

373

-3

374

-2

375

-1

376

create trigger trg17 before update on t17 /* slave local */

377

for each row

378

begin

379

DECLARE r integer;

380

SELECT f2 INTO r FROM t27 where f1=NEW.f1;

381

INSERT INTO t37 values (r);

382

end|

383

create table t27 (f1 int, f2 int) /* slave local */;

384

create table t37 (f3 int) /* slave local */;

385

insert into t27 values (5, 5*100);

386

insert into t27 values (4, 4*100);

387

insert into t27 values (3, 3*100);

388

insert into t27 values (2, 2*100);

389

insert into t27 values (1, 1*100);

390

select * from t27;

391

f1 f2

392

5 500

393

4 400

394

3 300

395

2 200

396

1 100

397

UPDATE t17 SET f1=5 where f1=-5;

398

SELECT * from t17 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;

399

400

401

-4

402

-3

403

-2

404

-1

405

SELECT * from t37 /* must be f3 5*100 */;

406

407

500

408

UPDATE t17 SET f1=5 where f1=-5;

409

UPDATE t17 SET f1=4 where f1=-4;

410

UPDATE t17 SET f1=3 where f1=-3;

411

UPDATE t17 SET f1=2 where f1=-2;

412

UPDATE t17 SET f1=1 where f1=-1;

413

SELECT * from t17 /* must be f1 5 ... 1 */;

414

415

416

417

418

419

420

SELECT * from t37 /* must be f3 5 * 100 ... 100 */;

421

422

500

423

400

424

300

425

200

426

100

427

drop trigger trg17;

428

drop table t27,t37;

429

drop table t17;

430

-------------------

431

432

-------------------

433

drop table if exists t16;

434

drop table if exists t26,t36;

435

create table t16 (f1 int) /* 2 replicate */;

436

insert into t16 values (-5);

437

insert into t16 values (-4);

438

insert into t16 values (-3);

439

insert into t16 values (-2);

440

insert into t16 values (-1);

441

select * from t16;

442

443

-5

444

-4

445

-3

446

-2

447

-1

448

create trigger trg16 before update on t16 /* slave local */

449

for each row

450

begin

451

DECLARE r integer;

452

SELECT f2 INTO r FROM t26 where f1=NEW.f1;

453

INSERT INTO t36 values (r);

454

end|

455

create table t26 (f1 int, f2 int) /* slave local */;

456

create table t36 (f3 int) /* slave local */;

457

insert into t26 values (5, 5*100);

458

insert into t26 values (4, 4*100);

459

insert into t26 values (3, 3*100);

460

insert into t26 values (2, 2*100);

461

insert into t26 values (1, 1*100);

462

select * from t26;

463

f1 f2

464

5 500

465

4 400

466

3 300

467

2 200

468

1 100

469

UPDATE t16 SET f1=5 where f1=-5;

470

SELECT * from t16 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;

471

472

473

-4

474

-3

475

-2

476

-1

477

SELECT * from t36 /* must be f3 5*100 */;

478

479

500

480

UPDATE t16 SET f1=5 where f1=-5;

481

UPDATE t16 SET f1=4 where f1=-4;

482

UPDATE t16 SET f1=3 where f1=-3;

483

UPDATE t16 SET f1=2 where f1=-2;

484

UPDATE t16 SET f1=1 where f1=-1;

485

SELECT * from t16 /* must be f1 5 ... 1 */;

486

487

488

489

490

491

492

SELECT * from t36 /* must be f3 5 * 100 ... 100 */;

493

494

500

495

400

496

300

497

200

498

100

499

drop trigger trg16;

500

drop table t26,t36;

501

drop table t16;

502

-------------------

503

504

-------------------

505

drop table if exists t15;

506

drop table if exists t25,t35;

507

create table t15 (f1 int) /* 2 replicate */;

508

insert into t15 values (-5);

509

insert into t15 values (-4);

510

insert into t15 values (-3);

511

insert into t15 values (-2);

512

insert into t15 values (-1);

513

select * from t15;

514

515

-5

516

-4

517

-3

518

-2

519

-1

520

create trigger trg15 before update on t15 /* slave local */

521

for each row

522

begin

523

DECLARE r integer;

524

SELECT f2 INTO r FROM t25 where f1=NEW.f1;

525

INSERT INTO t35 values (r);

526

end|

527

create table t25 (f1 int, f2 int) /* slave local */;

528

create table t35 (f3 int) /* slave local */;

529

insert into t25 values (5, 5*100);

530

insert into t25 values (4, 4*100);

531

insert into t25 values (3, 3*100);

532

insert into t25 values (2, 2*100);

533

insert into t25 values (1, 1*100);

534

select * from t25;

535

f1 f2

536

5 500

537

4 400

538

3 300

539

2 200

540

1 100

541

UPDATE t15 SET f1=5 where f1=-5;

542

SELECT * from t15 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;

543

544

545

-4

546

-3

547

-2

548

-1

549

SELECT * from t35 /* must be f3 5*100 */;

550

551

500

552

UPDATE t15 SET f1=5 where f1=-5;

553

UPDATE t15 SET f1=4 where f1=-4;

554

UPDATE t15 SET f1=3 where f1=-3;

555

UPDATE t15 SET f1=2 where f1=-2;

556

UPDATE t15 SET f1=1 where f1=-1;

557

SELECT * from t15 /* must be f1 5 ... 1 */;

558

559

560

561

562

563

564

SELECT * from t35 /* must be f3 5 * 100 ... 100 */;

565

566

500

567

400

568

300

569

200

570

100

571

drop trigger trg15;

572

drop table t25,t35;

573

drop table t15;

574

-------------------

575

576

-------------------

577

drop table if exists t14;

578

drop table if exists t24,t34;

579

create table t14 (f1 int) /* 2 replicate */;

580

insert into t14 values (-5);

581

insert into t14 values (-4);

582

insert into t14 values (-3);

583

insert into t14 values (-2);

584

insert into t14 values (-1);

585

select * from t14;

586

587

-5

588

-4

589

-3

590

-2

591

-1

592

create trigger trg14 before update on t14 /* slave local */

593

for each row

594

begin

595

DECLARE r integer;

596

SELECT f2 INTO r FROM t24 where f1=NEW.f1;

597

INSERT INTO t34 values (r);

598

end|

599

create table t24 (f1 int, f2 int) /* slave local */;

600

create table t34 (f3 int) /* slave local */;

601

insert into t24 values (5, 5*100);

602

insert into t24 values (4, 4*100);

603

insert into t24 values (3, 3*100);

604

insert into t24 values (2, 2*100);

605

insert into t24 values (1, 1*100);

606

select * from t24;

607

f1 f2

608

5 500

609

4 400

610

3 300

611

2 200

612

1 100

613

UPDATE t14 SET f1=5 where f1=-5;

614

SELECT * from t14 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;

615

616

617

-4

618

-3

619

-2

620

-1

621

SELECT * from t34 /* must be f3 5*100 */;

622

623

500

624

UPDATE t14 SET f1=5 where f1=-5;

625

UPDATE t14 SET f1=4 where f1=-4;

626

UPDATE t14 SET f1=3 where f1=-3;

627

UPDATE t14 SET f1=2 where f1=-2;

628

UPDATE t14 SET f1=1 where f1=-1;

629

SELECT * from t14 /* must be f1 5 ... 1 */;

630

631

632

633

634

635

636

SELECT * from t34 /* must be f3 5 * 100 ... 100 */;

637

638

500

639

400

640

300

641

200

642

100

643

drop trigger trg14;

644

drop table t24,t34;

645

drop table t14;

646

-------------------

647

648

-------------------

649

drop table if exists t13;

650

drop table if exists t23,t33;

651

create table t13 (f1 int) /* 2 replicate */;

652

insert into t13 values (-5);

653

insert into t13 values (-4);

654

insert into t13 values (-3);

655

insert into t13 values (-2);

656

insert into t13 values (-1);

657

select * from t13;

658

659

-5

660

-4

661

-3

662

-2

663

-1

664

create trigger trg13 before update on t13 /* slave local */

665

for each row

666

begin

667

DECLARE r integer;

668

SELECT f2 INTO r FROM t23 where f1=NEW.f1;

669

INSERT INTO t33 values (r);

670

end|

671

create table t23 (f1 int, f2 int) /* slave local */;

672

create table t33 (f3 int) /* slave local */;

673

insert into t23 values (5, 5*100);

674

insert into t23 values (4, 4*100);

675

insert into t23 values (3, 3*100);

676

insert into t23 values (2, 2*100);

677

insert into t23 values (1, 1*100);

678

select * from t23;

679

f1 f2

680

5 500

681

4 400

682

3 300

683

2 200

684

1 100

685

UPDATE t13 SET f1=5 where f1=-5;

686

SELECT * from t13 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;

687

688

689

-4

690

-3

691

-2

692

-1

693

SELECT * from t33 /* must be f3 5*100 */;

694

695

500

696

UPDATE t13 SET f1=5 where f1=-5;

697

UPDATE t13 SET f1=4 where f1=-4;

698

UPDATE t13 SET f1=3 where f1=-3;

699

UPDATE t13 SET f1=2 where f1=-2;

700

UPDATE t13 SET f1=1 where f1=-1;

701

SELECT * from t13 /* must be f1 5 ... 1 */;

702

703

704

705

706

707

708

SELECT * from t33 /* must be f3 5 * 100 ... 100 */;

709

710

500

711

400

712

300

713

200

714

100

715

drop trigger trg13;

716

drop table t23,t33;

717

drop table t13;

718

-------------------

719

720

-------------------

721

drop table if exists t12;

722

drop table if exists t22,t32;

723

create table t12 (f1 int) /* 2 replicate */;

724

insert into t12 values (-5);

725

insert into t12 values (-4);

726

insert into t12 values (-3);

727

insert into t12 values (-2);

728

insert into t12 values (-1);

729

select * from t12;

730

731

-5

732

-4

733

-3

734

-2

735

-1

736

create trigger trg12 before update on t12 /* slave local */

737

for each row

738

begin

739

DECLARE r integer;

740

SELECT f2 INTO r FROM t22 where f1=NEW.f1;

741

INSERT INTO t32 values (r);

742

end|

743

create table t22 (f1 int, f2 int) /* slave local */;

744

create table t32 (f3 int) /* slave local */;

745

insert into t22 values (5, 5*100);

746

insert into t22 values (4, 4*100);

747

insert into t22 values (3, 3*100);

748

insert into t22 values (2, 2*100);

749

insert into t22 values (1, 1*100);

750

select * from t22;

751

f1 f2

752

5 500

753

4 400

754

3 300

755

2 200

756

1 100

757

UPDATE t12 SET f1=5 where f1=-5;

758

SELECT * from t12 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;

759

760

761

-4

762

-3

763

-2

764

-1

765

SELECT * from t32 /* must be f3 5*100 */;

766

767

500

768

UPDATE t12 SET f1=5 where f1=-5;

769

UPDATE t12 SET f1=4 where f1=-4;

770

UPDATE t12 SET f1=3 where f1=-3;

771

UPDATE t12 SET f1=2 where f1=-2;

772

UPDATE t12 SET f1=1 where f1=-1;

773

SELECT * from t12 /* must be f1 5 ... 1 */;

774

775

776

777

778

779

780

SELECT * from t32 /* must be f3 5 * 100 ... 100 */;

781

782

500

783

400

784

300

785

200

786

100

787

drop trigger trg12;

788

drop table t22,t32;

789

drop table t12;

790

-------------------

791

792

-------------------

793

drop table if exists t11;

794

drop table if exists t21,t31;

795

create table t11 (f1 int) /* 2 replicate */;

796

insert into t11 values (-5);

797

insert into t11 values (-4);

798

insert into t11 values (-3);

799

insert into t11 values (-2);

800

insert into t11 values (-1);

801

select * from t11;

802

803

-5

804

-4

805

-3

806

-2

807

-1

808

create trigger trg11 before update on t11 /* slave local */

809

for each row

810

begin

811

DECLARE r integer;

812

SELECT f2 INTO r FROM t21 where f1=NEW.f1;

813

INSERT INTO t31 values (r);

814

end|

815

create table t21 (f1 int, f2 int) /* slave local */;

816

create table t31 (f3 int) /* slave local */;

817

insert into t21 values (5, 5*100);

818

insert into t21 values (4, 4*100);

819

insert into t21 values (3, 3*100);

820

insert into t21 values (2, 2*100);

821

insert into t21 values (1, 1*100);

822

select * from t21;

823

f1 f2

824

5 500

825

4 400

826

3 300

827

2 200

828

1 100

829

UPDATE t11 SET f1=5 where f1=-5;

830

SELECT * from t11 /* must be f1 5, 1 - 5 2 - 5 ... -1 */;

831

832

833

-4

834

-3

835

-2

836

-1

837

SELECT * from t31 /* must be f3 5*100 */;

838

839

500

840

UPDATE t11 SET f1=5 where f1=-5;

841

UPDATE t11 SET f1=4 where f1=-4;

842

UPDATE t11 SET f1=3 where f1=-3;

843

UPDATE t11 SET f1=2 where f1=-2;

844

UPDATE t11 SET f1=1 where f1=-1;

845

SELECT * from t11 /* must be f1 5 ... 1 */;

846

847

848

849

850

851

852

SELECT * from t31 /* must be f3 5 * 100 ... 100 */;

853

854

500

855

400

856

300

857

200

858

100

859

drop trigger trg11;

860

drop table t21,t31;

861

drop table t11;

862

STOP SLAVE;

863

FLUSH LOGS;

864

include/rpl_stop_server.inc [server_number=1]

865

include/rpl_start_server.inc [server_number=1]

866

--> Master binlog: Server ver: 5.0.16-debug-log, Binlog ver: 4

867

RESET SLAVE;

868

START SLAVE;

869

SELECT MASTER_POS_WAIT('master-bin.000001', 513) >= 0;

870

MASTER_POS_WAIT('master-bin.000001', 513) >= 0

871

872

SHOW TABLES LIKE 't_';

873

Tables_in_test (t_)

874

875

876

SHOW TRIGGERS;

877

Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation

878

trg1 INSERT t1 INSERT INTO t2 VALUES(CURRENT_USER()) AFTER NULL latin1 latin1_swedish_ci latin1_swedish_ci

879

SELECT * FROM t1;

880

881

882

SELECT * FROM t2;

883

884

885

INSERT INTO t1 VALUES(2);

886

SELECT * FROM t1;

887

888

889

890

SELECT * FROM t2;

891

892

893

root@localhost

894

DROP TRIGGER trg1;

895

DROP TABLE t1;

896

DROP TABLE t2;

897

STOP SLAVE;

898

RESET SLAVE;

899

SHOW TABLES LIKE 't_';

900

Tables_in_test (t_)

901

SHOW TRIGGERS;

902

Trigger Event Table Statement Timing Created sql_mode Definer character_set_client collation_connection Database Collation

903

RESET MASTER;

904

START SLAVE;

905

include/rpl_reset.inc

906

907

---> Test for BUG#20438

908

909

---> Preparing environment...

910

---> connection: master

911

DROP TABLE IF EXISTS t1;

912

DROP TABLE IF EXISTS t2;

913

914

---> Synchronizing slave with master...

915

916

---> connection: master

917

918

---> Creating objects...

919

CREATE TABLE t1(c INT);

920

CREATE TABLE t2(c INT);

921

/*!50003 CREATE TRIGGER t1_bi BEFORE INSERT ON t1

922

FOR EACH ROW

923

INSERT INTO t2 VALUES(NEW.c * 10) */;

924

925

---> Inserting value...

926

INSERT INTO t1 VALUES(1);

927

928

---> Checking on master...

929

SELECT * FROM t1;

930

931

932

SELECT * FROM t2;

933

934

935

936

---> Synchronizing slave with master...

937

---> connection: master

938

939

---> Checking on slave...

940

SELECT * FROM t1;

941

942

943

SELECT * FROM t2;

944

945

946

947

---> connection: master

948

949

---> Cleaning up...

950

DROP TABLE t1;

951

DROP TABLE t2;

952

drop table if exists t1;

953

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

954

drop trigger not_a_trigger;

955

ERROR HY000: Trigger does not exist

956

drop trigger if exists not_a_trigger;

957

Warnings:

958

Note 1360 Trigger does not exist

959

create trigger t1_bi before insert on t1

960

for each row set NEW.b := "In trigger t1_bi";

961

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

962

drop trigger if exists t1_bi;

963

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

964

drop trigger if exists t1_bi;

965

Warnings:

966

Note 1360 Trigger does not exist

967

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

968

select * from t1;

969

a b

970

1 In trigger t1_bi

971

2 b

972

3 c

973

select * from t1;

974

a b

975

1 In trigger t1_bi

976

2 b

977

3 c

978

drop table t1;

979

include/rpl_reset.inc

980

create table t1 ( f int ) engine = innodb;

981

create table log ( r int ) engine = myisam;

982

create trigger tr

983

after insert on t1

984

for each row insert into log values ( new.f );

985

set autocommit = 0;

986

insert into t1 values ( 1 );

987

rollback;

988

Warnings:

989

Warning 1196 Some non-transactional changed tables couldn't be rolled back

990

include/diff_tables.inc [master:t1, slave:t1]

991

include/diff_tables.inc [master:log, slave:log]

992

drop table t1, log;

993

include/rpl_end.inc

Older »