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.
In MySQL, GIPK stands for generated invisible primary key. A GIPK is created whenever we create an InnoDB table without explicitly defining a primary key and when we have GIPKs enabled.
To enable GIPKs, we need to set our sql_generate_invisible_primary_key server system variable to ON. By default this is set to OFF, which means GIPKs are disabled by default.
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.
Continue readingIf 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.
Here are two options for getting a list of character sets that are available in MySQL.
Continue readingIn MySQL, a GIPK is an invisible primary key that was automatically generated by MySQL due to the fact that a primary key wasn’t explicitly defined in the statement that created the table.
Given that it’s an invisible column, the GIPK column isn’t returned when we use a SELECT * or TABLE statement to return the table’s contents.
However, as with any invisible column, we can still select the GIPK by explicitly including it in our SELECT list.
MySQL provides us with the ability to create scheduled events. These are tasks that run according to a specified schedule.
Often referred to simply as events, scheduled events can be created with the CREATE EVENT statement, they can be modified with the ALTER EVENT statement, and they can be removed with the DROP EVENT statement.
In MySQL we can use the SHOW BINLOG EVENTS statement to return a list of events in the binary log. We can specify which binary log to use, or we can let it default to the first one.
The MySQL REPLACE statement allows us to replace data in a table with new data. The new data can be explicitly provided in the statement or it could come from the result of a query. Therefore, we can use the REPLACE statement to effectively “refresh” a table with new/updated data from another table.
If you’re getting an error that reads something like “ERROR 1539 (HY000): Unknown event ‘Event1’” in MySQL when trying to drop an event, it’s probably because the event that you’re trying to drop doesn’t exist.
There are a couple of ways to address this issue. One way is to use the IF EXISTS clause so that dropping a non-existent event doesn’t cause an error. Another way is to check the name of the event that you’re trying to drop – it could be that you’re using the wrong event name.