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

create event event3 on schedule every 50 + 10 minute starts date_add("20100101", interval 5 minute) ends date_add("20151010", interval 5 day) comment "portokala_comment" DO insert into t_event3 values (unix_timestamp(), rand());

127

let $wait_condition=SELECT count(*)=0 from t_event3;

128

--source include/wait_condition.inc

129

select count(*) from t_event3;

130

drop event event3;

131

drop table t_event3;

132

133

134

set names utf8;

135

136

# SHOW CREATE EVENT test begin

137

138

CREATE EVENT root6 ON SCHEDULE EVERY '10:20' MINUTE_SECOND ON COMPLETION PRESERVE ENABLE COMMENT 'some comment' DO select 1;

139

--replace_regex /STARTS '[^']+'/STARTS '#'/

140

SHOW CREATE EVENT root6;

141

create event root7 on schedule every 2 year do select 1;

142

--replace_regex /STARTS '[^']+'/STARTS '#'/

143

SHOW CREATE EVENT root7;

144

create event root8 on schedule every '2:5' year_month do select 1;

145

--replace_regex /STARTS '[^']+'/STARTS '#'/

146

SHOW CREATE EVENT root8;

147

create event root8_1 on schedule every '2:15' year_month do select 1;

148

--replace_regex /STARTS '[^']+'/STARTS '#'/

149

SHOW CREATE EVENT root8_1;

150

create event root9 on schedule every 2 week ON COMPLETION PRESERVE DISABLE COMMENT 'коментар на кирилица' do select 1;

151

--replace_regex /STARTS '[^']+'/STARTS '#'/

152

SHOW CREATE EVENT root9;

153

create event root10 on schedule every '20:5' day_hour do select 1;

154

--replace_regex /STARTS '[^']+'/STARTS '#'/

155

SHOW CREATE EVENT root10;

156

create event root11 on schedule every '20:25' day_hour do select 1;

157

--replace_regex /STARTS '[^']+'/STARTS '#'/

158

SHOW CREATE EVENT root11;

159

create event root12 on schedule every '20:25' hour_minute do select 1;

160

--replace_regex /STARTS '[^']+'/STARTS '#'/

161

SHOW CREATE EVENT root12;

162

create event root13 on schedule every '25:25' hour_minute do select 1;

163

--replace_regex /STARTS '[^']+'/STARTS '#'/

164

SHOW CREATE EVENT root13;

165

create event root13_1 on schedule every '11:65' hour_minute do select 1;

166

--replace_regex /STARTS '[^']+'/STARTS '#'/

167

SHOW CREATE EVENT root13_1;

168

create event root14 on schedule every '35:35' minute_second do select 1;

169

--replace_regex /STARTS '[^']+'/STARTS '#'/

170

SHOW CREATE EVENT root14;

171

create event root15 on schedule every '35:66' minute_second do select 1;

172

--replace_regex /STARTS '[^']+'/STARTS '#'/

173

SHOW CREATE EVENT root15;

174

create event root16 on schedule every '35:56' day_minute do select 1;

175

--replace_regex /STARTS '[^']+'/STARTS '#'/

176

SHOW CREATE EVENT root16;

177

create event root17 on schedule every '35:12:45' day_minute do select 1;

178

--replace_regex /STARTS '[^']+'/STARTS '#'/

179

SHOW CREATE EVENT root17;

180

create event root17_1 on schedule every '35:25:65' day_minute do select 1;

181

--replace_regex /STARTS '[^']+'/STARTS '#'/

182

SHOW CREATE EVENT root17_1;

183

create event root18 on schedule every '35:12:45' hour_second do select 1;

184

--replace_regex /STARTS '[^']+'/STARTS '#'/

185

SHOW CREATE EVENT root18;

186

create event root19 on schedule every '15:59:85' hour_second do select 1;

187

--replace_regex /STARTS '[^']+'/STARTS '#'/

188

SHOW CREATE EVENT root19;

189

create event root20 on schedule every '50:20:12:45' day_second do select 1;

190

--replace_regex /STARTS '[^']+'/STARTS '#'/

191

SHOW CREATE EVENT root20;

192

set names cp1251;

193

create event ��21 on schedule every '50:23:59:95' day_second COMMENT '�� 1251 ��' do select 1;

194

--replace_regex /STARTS '[^']+'/STARTS '#'/

195

SHOW CREATE EVENT ��21;

196

insert into mysql.event (

197

db,

198

name,

199

body,

200

definer,

201

interval_value,

202

interval_field,

203

originator,

204

character_set_client,

205

collation_connection,

206

db_collation,

207

body_utf8)

208

values (

209

database(),

210

"root22",

211

"select 1",

212

user(),

213

100,

214

"SECOND_MICROSECOND",

215

216

'utf8',

217

'utf8_general_ci',

218

'utf8_general_ci',

219

'select 1');

220

--error ER_NOT_SUPPORTED_YET

221

show create event root22;

222

--error ER_NOT_SUPPORTED_YET

223

SHOW EVENTS;

224

drop event root22;

225

--error ER_EVENT_INTERVAL_NOT_POSITIVE_OR_TOO_BIG

226

create event root23 on schedule every -100 year do select 1;

227

--error ER_EVENT_INTERVAL_NOT_POSITIVE_OR_TOO_BIG

228

create event root23 on schedule every 222222222222222222222 year do select 1;

229

drop event root6;

230

drop event root7;

231

drop event root8;

232

drop event root8_1;

233

drop event root9;

234

drop event root10;

235

drop event root11;

236

drop event root12;

237

drop event root13;

238

drop event root13_1;

239

drop event root14;

240

drop event root15;

241

drop event root16;

242

drop event root17;

243

drop event root17_1;

244

drop event root18;

245

drop event root19;

246

drop event root20;

247

drop event ��21;

248

249

set names latin1;

250

251

# SHOW CREATE EVENT test end

252

253

254

255

# mysql.event intact checking

256

# Check that the server does not crash if

257

# one has destroyed or tampered with the event table.

258

# Please see see for events_restart_phase* tests to

259

# see the server behavior at start up with bad mysql.event

260

# table.

261

262

263

--echo Create a test event. Only event metadata is relevant,

264

--echo the actual schedule and body are not.

265

266

CREATE EVENT intact_check ON SCHEDULE EVERY 10 HOUR DO SELECT "nothing";

267

--replace_column 8 # 9 #

268

SHOW EVENTS;

269

270

--echo Try to alter mysql.event: the server should fail to load

271

--echo event information after mysql.event was tampered with.

272

--echo

273

--echo First, let's add a column to the end and make sure everything

274

--echo works as before

275

--echo

276

ALTER TABLE mysql.event ADD dummy INT;

277

--replace_column 8 # 9 #

278

SHOW EVENTS;

279

SELECT event_name FROM INFORMATION_SCHEMA.events;

280

--replace_regex /STARTS '[^']+'/STARTS '#'/

281

SHOW CREATE EVENT intact_check;

282

--error ER_EVENT_DOES_NOT_EXIST

283

DROP EVENT no_such_event;

284

CREATE EVENT intact_check_1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5;

285

ALTER EVENT intact_check_1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8;

286

ALTER EVENT intact_check_1 RENAME TO intact_check_2;

287

--error ER_EVENT_DOES_NOT_EXIST

288

DROP EVENT intact_check_1;

289

DROP EVENT intact_check_2;

290

DROP EVENT intact_check;

291

DROP DATABASE IF EXISTS mysqltest_no_such_database;

292

CREATE DATABASE mysqltest_db2;

293

DROP DATABASE mysqltest_db2;

294

SELECT @@event_scheduler;

295

SHOW VARIABLES LIKE 'event_scheduler';

296

SET GLOBAL event_scheduler=OFF;

297

# Clean up

298

ALTER TABLE mysql.event DROP dummy;

299

CREATE EVENT intact_check ON SCHEDULE EVERY 10 HOUR DO SELECT "nothing";

300

--echo

301

--echo Now let's add a column to the first position: the server

302

--echo expects to see event schema name there

303

--echo

304

ALTER TABLE mysql.event ADD dummy INT FIRST;

305

--error ER_CANNOT_LOAD_FROM_TABLE

306

SHOW EVENTS;

307

--error ER_CANNOT_LOAD_FROM_TABLE

308

SELECT event_name FROM INFORMATION_SCHEMA.events;

309

--error ER_EVENT_DOES_NOT_EXIST

310

SHOW CREATE EVENT intact_check;

311

--error ER_EVENT_DOES_NOT_EXIST

312

DROP EVENT no_such_event;

313

--error ER_EVENT_STORE_FAILED

314

CREATE EVENT intact_check_1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5;

315

--error ER_EVENT_DOES_NOT_EXIST

316

ALTER EVENT intact_check_1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8;

317

--error ER_EVENT_DOES_NOT_EXIST

318

ALTER EVENT intact_check_1 RENAME TO intact_check_2;

319

--error ER_EVENT_DOES_NOT_EXIST

320

DROP EVENT intact_check_1;

321

--error ER_EVENT_DOES_NOT_EXIST

322

DROP EVENT intact_check_2;

323

--error ER_EVENT_DOES_NOT_EXIST

324

DROP EVENT intact_check;

325

# Should work OK

326

DROP DATABASE IF EXISTS mysqltest_no_such_database;

327

CREATE DATABASE mysqltest_db2;

328

DROP DATABASE mysqltest_db2;

329

SELECT @@event_scheduler;

330

SHOW VARIABLES LIKE 'event_scheduler';

331

SET GLOBAL event_scheduler=OFF;

332

--echo Clean up

333

ALTER TABLE mysql.event DROP dummy;

334

DELETE FROM mysql.event;

335

CREATE EVENT intact_check ON SCHEDULE EVERY 10 HOUR DO SELECT "nothing";

336

--echo Back up the table, further changes are not reversible

337

CREATE TABLE event_like LIKE mysql.event;

338

INSERT INTO event_like SELECT * FROM mysql.event;

339

--echo

340

--echo Drop some columns and try more checks.

341

--echo

342

--echo

343

ALTER TABLE mysql.event DROP comment, DROP starts;

344

--error ER_CANNOT_LOAD_FROM_TABLE

345

SHOW EVENTS;

346

--error ER_CANNOT_LOAD_FROM_TABLE

347

SELECT event_name FROM INFORMATION_SCHEMA.EVENTS;

348

--error ER_CANNOT_LOAD_FROM_TABLE

349

SHOW CREATE EVENT intact_check;

350

--error ER_EVENT_DOES_NOT_EXIST

351

DROP EVENT no_such_event;

352

--error ER_COL_COUNT_DOESNT_MATCH_CORRUPTED

353

CREATE EVENT intact_check_1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5;

354

--error ER_EVENT_DOES_NOT_EXIST

355

ALTER EVENT intact_check_1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8;

356

--error ER_EVENT_DOES_NOT_EXIST

357

ALTER EVENT intact_check_1 RENAME TO intact_check_2;

358

--error ER_EVENT_DOES_NOT_EXIST

359

DROP EVENT intact_check_1;

360

--error ER_EVENT_DOES_NOT_EXIST

361

DROP EVENT intact_check_2;

362

# Should succeed

363

DROP EVENT intact_check;

364

DROP DATABASE IF EXISTS mysqltest_no_such_database;

365

CREATE DATABASE mysqltest_db2;

366

DROP DATABASE mysqltest_db2;

367

SELECT @@event_scheduler;

368

SHOW VARIABLES LIKE 'event_scheduler';

369

SET GLOBAL event_scheduler=OFF;

370

--echo

371

--echo Now drop the table, and test again

372

--echo

373

--echo

374

DROP TABLE mysql.event;

375

--error ER_NO_SUCH_TABLE

376

SHOW EVENTS;

377

--error ER_NO_SUCH_TABLE

378

SELECT event_name FROM INFORMATION_SCHEMA.events;

379

--error ER_NO_SUCH_TABLE

380

SHOW CREATE EVENT intact_check;

381

--error ER_NO_SUCH_TABLE

382

DROP EVENT no_such_event;

383

--error ER_NO_SUCH_TABLE

384

CREATE EVENT intact_check_1 ON SCHEDULE EVERY 5 HOUR DO SELECT 5;

385

--error ER_NO_SUCH_TABLE

386

ALTER EVENT intact_check_1 ON SCHEDULE EVERY 8 HOUR DO SELECT 8;

387

--error ER_NO_SUCH_TABLE

388

ALTER EVENT intact_check_1 RENAME TO intact_check_2;

389

--error ER_NO_SUCH_TABLE

390

DROP EVENT intact_check_1;

391

--error ER_NO_SUCH_TABLE

392

DROP EVENT intact_check_2;

393

--error ER_NO_SUCH_TABLE

394

DROP EVENT intact_check;

395

DROP DATABASE IF EXISTS mysqltest_no_such_database;

396

CREATE DATABASE mysqltest_db2;

397

DROP DATABASE mysqltest_db2;

398

--echo OK, there is an unnecessary warning about the non-existent table

399

--echo but it's not easy to fix and no one complained about it.

400

--echo A similar warning is printed if mysql.proc is missing.

401

SHOW WARNINGS;

402

SELECT @@event_scheduler;

403

SHOW VARIABLES LIKE 'event_scheduler';

404

SET GLOBAL event_scheduler=OFF;

405

--echo Restore the original table.

406

CREATE TABLE mysql.event like event_like;

407

DROP TABLE event_like;

408

--replace_column 8 # 9 #

409

SHOW EVENTS;

410

411

# End of tests

412

413

414

let $wait_condition=

415

select count(*) = 0 from information_schema.processlist

416

where db='events_test' and command = 'Connect' and user=current_user();

417

--source include/wait_condition.inc

418

419

drop database events_test;

Older »