How to “Unhide” a GIPK in MySQL

In MySQL, GIPKs are invisible by definition. GIPK stands for generated invisible primary key, and it’s basically an invisible column automatically created by MySQL with a primary key constraint.

However, just because GIPKs are automatically created invisible, it doesn’t mean that we can’t “unhide” them – or make them visible.

We can make a GIPK visible just as we would make any other invisible column visible – use the ALTER TABLE statement to set it to VISIBLE.

Continue reading

Create a Generated Invisible Primary Key (GIPK) in MySQL

MySQL 8.0.30 introduced generated invisible primary keys (GIPKs), which are primary keys that are automatically created whenever we create a table without explicitly defining a primary key.

GIPKs only work with the InnoDB storage engine, and they only work when we have GIPKs enabled.

In this article, I check whether or not GIPKs are enabled on my system, I then enable GIPKs, and finally I create a table with a GIPK.

Continue reading

Remove the Comment from an Event in MySQL

When we create a scheduled event in MySQL we have the option of using the COMMENT clause to add a comment to the event. Comments can be a handy addition that help explain what the event does, why it was created, etc.

So it’s probably quite rare that we would want to remove the comment from an event. But in the event that we do (pun intended!), we can simply update the event with a blank comment.

Continue reading

Move a Scheduled Event to Another Database in MySQL

In MySQL scheduled events are tasks that run according to a given schedule. After creating a scheduled event, we can modify it using the ALTER EVENT statement. This statement allows us to change the event’s definition, change its schedule, enable/disable it, rename it, and more. It also allows us to move the event to another database.

To move an event to another database, we use the ALTER EVENT statement with the RENAME clause, prefixing the event name with the database name (using dot notation).

Continue reading

3 Ways to Check an Event’s Status in MySQL

When we create a scheduled event in MySQL it’s enabled by default. However, we do have the option of creating it in disabled status. We can also go back later and change an event’s status from enabled to disabled.

Given this fact, we may sometimes find ourselves wondering whether an event is currently enabled or disabled.

Below are three ways to go about checking an event for its enabled/disabled status.

Continue reading

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

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.

Continue reading