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

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

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

5 Ways to Find a Table’s Primary Key in MySQL

Generally speaking, most tables we create in MySQL should have a primary key. A primary key is one or more columns that have been configured as the unique identifier for the table.

We usually create the primary key when creating the table, but we can also add a primary key to an existing table (assuming it doesn’t already have one).

Occasionally it might not be immediately apparent which column is the primary key for a given table. Or it might not be apparent whether or not the table has a composite primary key. Fortunately it’s easy enough to find out.

Below are five ways to get the primary key column/s from an existing table in MySQL.

Continue reading

A Quick Overview of the ‘show_gipk_in_create_table_and_information_schema’ System Variable in MySQL

Depending on our configuration, if we create a table without a primary key column, MySQL will automatically create one behind the scenes. This is called a generated invisible primary key (GIPK).

We can normally use statements such as SHOW CREATE TABLE, SHOW COLUMNS, SHOW INDEX or even check the information schema to see if a table has a GIPK.

But this depends on the setting of our show_gipk_in_create_table_and_information_schema system variable.

This variable can be set to ON or OFF. When set to ON, we will see GIPKs in the output of the aforementioned statements. When set to OFF, we won’t see any GIPKs.

Continue reading