SQLite’s json_valid() Now Accepts an Argument that Defines What “Valid” Means

SQLite’s json_valid() function allows us to check whether a JSON string is well formed or not.

Prior to SQLite 3.45.0 the json_valid() function only accepts one argument – the value to check. However, from SQLite 3.45.0 (released on 15 January 2024), we can now provide an optional second argument to define what valid – or “well formed” – means.

Continue reading

How Scheduled Events Deal with sql_mode in MySQL

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.

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

Understanding MySQL’s ‘sql_generate_invisible_primary_key’ System Variable

One of the more recent additions to MySQL’s list of system variables is the sql_generate_invisible_primary_key variable. This variable was introduced in MySQL 8.0.30 along with the introduction of generated invisible primary keys (GIPKs).

The purpose of this variable is to allow us to specify whether or not the system will generate a GIPK when we omit a primary key from a table’s definition when creating the table.

By default the sql_generate_invisible_primary_key is set to OFF, which basically means that GIPKs are disabled by default. But we can change this to ON in order to enable GIPKs.

Continue reading