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.
In general, once we create a primary key on a table we leave it there. It’s there to help us maintain data integrity and removing it can put that data integrity at risk.
But sometimes we might have good reason to remove a primary key. Perhaps we need to drop it so that we can create a primary key on a different column or multiple columns. Or perhaps there’s another valid reason to drop the primary key.
Either way, below is an example of dropping the primary key from a column.
Continue readingIf you’re getting an error that reads “ERROR 1229 (HY000): Variable ‘event_scheduler’ is a GLOBAL variable and should be set with SET GLOBAL” in MySQL, it’s probably because you’re trying to set the event_scheduler system variable, but you’re not specifying it as a global variable.
The event_scheduler variable is a global variable and so we must specify it as a global variable.
To fix this issue, specify it as a global variable when setting it’s value.
Continue readingWhen we have a table with a generated invisible primary key (GIPK) in MySQL, we can usually see its definition when we use various SHOW statements such as SHOW CREATE TABLE, SHOW COLUMNS, and SHOW INDEX, as well as when we query information schema tables such as information_schema.columns.
But there is a way of hiding the GIPK from such statements. It all comes down to the show_gipk_in_create_table_and_information_schema variable. Yes, there’s actually a system variable that allows us to hide GIPKs from the output of various SHOW statements and information schema tables.
In MySQL we can use the SHOW BINARY LOGS statement to return a list of binary log files on the server.
In MySQL, GIPK stands for generated invisible primary key. These are created whenever we create an InnoDB table without explicitly defining primary key.
However, MySQL will only create a GIPK if we have enabled GIPKs. More specifically, a GIPK will only be created when our sql_generate_invisible_primary_key server system variable is set to ON. By default this is set to OFF.
In MySQL we can use the SHOW COLLATION statement to return all collations supported by the server. By default it returns all available collations, but we can filter the output down to just those collations that we’re interested in.