How to Prevent a MySQL Event from Disappearing Once it’s Completed

by

By default, scheduled events in MySQL are dropped from the system once they expire. That means that if the event’s schedule has completed, then the event is dropped.

But we can override this behaviour with the ON COMPLETION clause. Specifically we can specify ON COMPLETION PRESERVE to keep the event in the system after it has expired. By default, events have ON COMPLETION NOT PRESERVE added to their definition, which means they are dropped as soon as they expire. Using ON COMPLETION PRESERVE changes this so that they aren’t dropped when they expire.

Read more

2 Ways to Return Disabled Events in MySQL

by

There are several ways to list out all scheduled events in MySQL. But sometimes we might want to limit the results to just those that are set to a given status. Sometimes we might only want to see a list of disabled events.

Below are two options we can use to return all disabled events.

Read more

Using REPLACE to Insert New Rows in MySQL

by

In MySQL the REPLACE statement is normally used to replace existing rows in a table. But it’s not limited to just replacing existing rows. There are times when REPLACE will also insert new rows. It all depends on the incoming data, as well as how the table is structured in relation to the incoming data.

Read more

How to Change the Comment on a Scheduled Event in MySQL

by

Suppose you have an existing MySQL event that has a comment. But the comment isn’t as helpful as you’d like it to be, and so you’d like to change it to something else.

This can be done easily with the ALTER EVENT statement along with the COMMENT clause. When we include the COMMENT clause in the ALTER EVENT statement, it replaces whatever comment was in the event with the new comment. If the event didn’t have a comment, then a new comment appears.

Read more

Add a Comment to a Scheduled Event in MySQL

by

MySQL provides us with the ability to create scheduled events – tasks that run according to a specified schedule. Scheduled events allow for the ability to add comments within the event. We can do this with the COMMENT clause.

We can include the comment when creating the event, or we can add one to an existing event.

Read more

Add a Composite Primary Key to an Existing Table in MySQL

by

In MySQL, adding a composite primary key to an existing table is similar to adding a singular primary key. We define the primary key with the ALTER TABLE statement.

However, when adding a composite primary key we need to use the ADD PRIMARY KEY clause. We can’t define it at the column level. With singular primary keys on the other hand, we have the choice of using the ADD PRIMARY KEY clause or applying it at the column level.

Read more

How Scheduled Events Deal with sql_mode in MySQL

by

When we create a scheduled event in MySQL, the current sql_mode is stored with the event. That causes the event to use the sql_mode that was in effect at the time the event was created. Same with altering an event.

Therefore, if we change the sql_mode after the event was created or altered, it won’t have any impact on the event. In other words, we can safely change our system’s sql_mode without worrying about whether it’s going to mess up any existing scheduled events.

Read more

3 Ways to Check the Event Scheduler Status in MySQL

by

In MySQL we can create events that run on a specified schedule. But events will only run if the Event Scheduler is on. If the Event Scheduler is off or disabled, then such events won’t run as scheduled.

Therefore if we create any events on the server, it’s a good idea to check whether the Event Scheduler is on or not.

Here are three options for doing this using SQL.

Read more

Disable Quotes from Table and Column Names When Using SHOW CREATE TABLE in MySQL

by

If you’re using MySQL’s SHOW CREATE TABLE statement, but you don’t want quotes to be included in the output, this is easily addressed.

By default, SHOW CREATE TABLE (and SHOW CREATE DATABASE) includes quotes around table and column names, etc. When I say “by default”, I mean assuming you haven’t yet changed anything to disable these quotes.

Fortunately there’s an easy way to disable these quotes. MySQL provides us with the sql_quote_show_create system variable. This variable can be set to 1 or 0, which enables and disables such quotes respectively.

Read more