Author: Ian

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

Posted on by Ian

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.

Continue reading →

How to Select a GIPK in MySQL

Posted on by Ian

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

Continue reading →

Fix “Unknown event” Error in MySQL when Trying to Drop an Event

Posted on by Ian

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.

Continue reading →

How to Drop a Primary Key in MySQL

Posted on by Ian

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 →

Fix “Variable ‘event_scheduler’ is a GLOBAL variable and should be set with SET GLOBAL” in MySQL

Posted on by Ian

If 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 reading →

How to Hide a GIPK from the SHOW CREATE TABLE Statement (and SHOW COLUMNS and SHOW INDEX) in MySQL

Posted on by Ian

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

Continue reading →