~mdcallag/+junk/5.1-map : revision 2471

scripts/mysql_system_tables_fix.sql

389

char(64) collate utf8_bin DEFAULT '' NOT NULL;

390

391

392

# Create missing log tables (5.1)

393

394

395

delimiter //

396

CREATE PROCEDURE create_log_tables()

397

BEGIN

398

DECLARE is_csv_enabled int DEFAULT 0;

399

SELECT @@have_csv = 'YES' INTO is_csv_enabled;

400

IF (is_csv_enabled) THEN

401

CREATE TABLE IF NOT EXISTS general_log (

402

event_time TIMESTAMP NOT NULL,

403

user_host MEDIUMTEXT,

404

thread_id INTEGER,

405

server_id INTEGER,

406

command_type VARCHAR(64),

407

argument MEDIUMTEXT

408

) engine=CSV CHARACTER SET utf8 comment='General log';

409

CREATE TABLE IF NOT EXISTS slow_log (

410

start_time TIMESTAMP NOT NULL,

411

user_host MEDIUMTEXT NOT NULL,

412

query_time TIME NOT NULL,

413

lock_time TIME NOT NULL,

414

rows_sent INTEGER NOT NULL,

415

rows_examined INTEGER NOT NULL,

416

db VARCHAR(512),

417

last_insert_id INTEGER,

418

insert_id INTEGER,

419

server_id INTEGER,

420

sql_text MEDIUMTEXT NOT NULL

421

) engine=CSV CHARACTER SET utf8 comment='Slow log';

422

END IF;

423

END//

424

delimiter ;

425

CALL create_log_tables();

426

DROP PROCEDURE create_log_tables;

427

428

# EVENT table

429

430

431

432

CREATE TABLE event (

433

db char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '',

434

name char(64) CHARACTER SET utf8 NOT NULL default '',

435

body longblob NOT NULL,

436

definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '',

437

execute_at DATETIME default NULL,

438

interval_value int(11) default NULL,

439

interval_field ENUM('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK',

440

'SECOND','MICROSECOND', 'YEAR_MONTH','DAY_HOUR',

441

'DAY_MINUTE','DAY_SECOND',

442

'HOUR_MINUTE','HOUR_SECOND',

443

'MINUTE_SECOND','DAY_MICROSECOND',

444

'HOUR_MICROSECOND','MINUTE_MICROSECOND',

445

'SECOND_MICROSECOND') default NULL,

446

created TIMESTAMP NOT NULL,

447

modified TIMESTAMP NOT NULL,

448

last_executed DATETIME default NULL,

449

starts DATETIME default NULL,

450

ends DATETIME default NULL,

451

status ENUM('ENABLED','DISABLED') NOT NULL default 'ENABLED',

452

on_completion ENUM('DROP','PRESERVE') NOT NULL default 'DROP',

453

sql_mode set(

454

'REAL_AS_FLOAT',

455

'PIPES_AS_CONCAT',

456

'ANSI_QUOTES',

457

'IGNORE_SPACE',

458

'NOT_USED',

459

'ONLY_FULL_GROUP_BY',

460

'NO_UNSIGNED_SUBTRACTION',

461

'NO_DIR_IN_CREATE',

462

'POSTGRESQL',

463

'ORACLE',

464

'MSSQL',

465

'DB2',

466

'MAXDB',

467

'NO_KEY_OPTIONS',

468

'NO_TABLE_OPTIONS',

469

'NO_FIELD_OPTIONS',

470

'MYSQL323',

471

'MYSQL40',

472

'ANSI',

473

'NO_AUTO_VALUE_ON_ZERO',

474

'NO_BACKSLASH_ESCAPES',

475

'STRICT_TRANS_TABLES',

476

'STRICT_ALL_TABLES',

477

'NO_ZERO_IN_DATE',

478

'NO_ZERO_DATE',

479

'INVALID_DATES',

480

'ERROR_FOR_DIVISION_BY_ZERO',

481

'TRADITIONAL',

482

'NO_AUTO_CREATE_USER',

483

'HIGH_NOT_PRECEDENCE'

484

) DEFAULT '' NOT NULL,

485

comment char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default '',

486

PRIMARY KEY (db,name)

487

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT 'Events';

488

489

490

491

392

# EVENT privilege

492

393

493

494

394

SET @hadEventPriv := 0;

495

395

SELECT @hadEventPriv :=1 FROM user WHERE Event_priv LIKE '%';

496

396

497

397

ALTER TABLE user add Event_priv enum('N','Y') character set utf8 DEFAULT 'N' NOT NULL AFTER Create_user_priv;

498

398

ALTER TABLE user MODIFY Event_priv enum('N','Y') character set utf8 DEFAULT 'N' NOT NULL AFTER Create_user_priv;

499

399

400

UPDATE user SET Event_priv=Super_priv WHERE @hadEventPriv = 0;

401

500

402

ALTER TABLE db add Event_priv enum('N','Y') character set utf8 DEFAULT 'N' NOT NULL;

501

403

ALTER TABLE db MODIFY Event_priv enum('N','Y') character set utf8 DEFAULT 'N' NOT NULL;

502

404

405

406

# EVENT table

407

503

408

ALTER TABLE event DROP PRIMARY KEY;

504

409

ALTER TABLE event ADD PRIMARY KEY(db, name);

505

410

ALTER TABLE event ADD sql_mode

534

439

'NO_AUTO_CREATE_USER',

535

440

'HIGH_NOT_PRECEDENCE'

536

441

) DEFAULT '' NOT NULL AFTER on_completion;

537

538

UPDATE user SET Event_priv=Super_priv WHERE @hadEventPriv = 0;

539

442

ALTER TABLE event MODIFY name char(64) CHARACTER SET utf8 NOT NULL default '';

443

ALTER TABLE event ADD COLUMN originator INT(10) NOT NULL;

444

ALTER TABLE event MODIFY COLUMN status ENUM('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL default 'ENABLED';

445

446

ALTER TABLE event ADD COLUMN time_zone char(64) CHARACTER SET latin1

447

NOT NULL DEFAULT 'SYSTEM' AFTER originator;

540

448

541

449

542

450

# TRIGGER privilege

558

466

559

467

UPDATE user SET Trigger_priv=Super_priv WHERE @hadTriggerPriv = 0;

560

468

561

CREATE TABLE IF NOT EXISTS ndb_binlog_index (Position BIGINT UNSIGNED NOT NULL, File VARCHAR(255) NOT NULL, epoch BIGINT UNSIGNED NOT NULL, inserts BIGINT UNSIGNED NOT NULL, updates BIGINT UNSIGNED NOT NULL, deletes BIGINT UNSIGNED NOT NULL, schemaops BIGINT UNSIGNED NOT NULL, PRIMARY KEY(epoch)) ENGINE=MYISAM;

562

563

469

# Activate the new, possible modified privilege tables

564

470

# This should not be needed, but gives us some extra testing that the above

565

471

# changes was correct

Older »