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.
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 reading