How to Prevent Creation of Non-PK Tables in MySQL InnoDB Cluster Environment?

I have tried to document step by step How to Prevent Creation of Non-PK Tables in MySQL InnoDB Cluster Environment. I hope it will be useful for those who need it!

High Level Steps

1. General Information
1.1. Which Parameter Prevents the Creation of Tables Without a Primary Key in a MySQL InnoDB Cluster Environment?
1.2. Can the sql_require_primary_key Parameter Be Changed Dynamically Without Restarting the DB Service?
2. Test Operations
2.1. sql_require_primary_key=OFF & Non-PK Table Can Be Created
2.1.1. Check sql_require_primary_key Parameter
2.1.2. Try to Create Table Without PK
2.2. sql_require_primary_key=ON & Non-PK Table Can Not Be Created
2.2.1. Add sql_require_primary_key Parameter to MySQL Server Conf File
2.2.2. Restart & Check MySQL Service
2.2.3. Check Primary Node in the Cluster
2.2.4. Change Primary Node (If Needed)
2.2.5. Try to Create Table Without PK

1. General Information

1.1. Which Parameter Prevents the Creation of Tables Without a Primary Key in a MySQL InnoDB Cluster Environment?

In a MySQL InnoDB Cluster environment, the creation of tables without a Primary Key (PK) can be prevented using the sql_require_primary_key parameter.

1.1.1. Explanation
When sql_require_primary_key=ON is set, any CREATE TABLE or ALTER TABLE statement that attempts to define an InnoDB table without a primary key will result in an error.

1.1.2. Usage
This setting is recommended especially in replication environments and high availability architectures such as InnoDB Cluster, to maintain data consistency. A missing primary key in an InnoDB table can lead to issues related to replication, clustering, and performance.

[mysqld]
sql_require_primary_key = ON

1.1.3. Notes

• This parameter applies only to InnoDB tables.
• Its default value is OFF.
• It is supported from MySQL 8.0.13 onwards.

1.1.4. Checking the Value

SHOW VARIABLES LIKE 'sql_require_primary_key';

1.2. Can the sql_require_primary_key Parameter Be Changed Dynamically Without Restarting the DB Service?

No, sql_require_primary_key is not a dynamic system variable, it cannot be changed without restarting the database service.

1.2.1. Details
You can check the current value using:

SHOW VARIABLES LIKE 'sql_require_primary_key';

However, if you try to run:

SET GLOBAL sql_require_primary_key = ON;

You will receive an error, because this variable:

• Can only be configured via the configuration file (my.cnf)
• Cannot be modified using SET GLOBAL/SESSION commands.

2. Test Operations

2.1. sql_require_primary_key=OFF & Non-PK Table Can Be Created

2.1.1. Check sql_require_primary_key Parameter

SHOW VARIABLES LIKE 'sql_require_primary_key';

2.1.2. Try to Create Table Without PK

Since the sql_require_primary_key parameter is OFF, a table without a PK is allowed to be created as expected.

mysqlsh --uri root@localhost
\sql
show databases;
use testdb;

CREATE TABLE customers (
    customer_id INT,
    name VARCHAR(100),
    email VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

2.2. sql_require_primary_key=ON & Non-PK Table Can Not Be Created

2.2.1. Add sql_require_primary_key Parameter to MySQL Server Conf File
Add sql_require_primary_key parameter on all servers in the cluster.

vi /etc/my.cnf.d/mysql-server.cnf

# Prevent creation of InnoDB tables without a primary key (recommended for replication and HA).
sql_require_primary_key = ON

# Check sql_require_primary_key Parameter
more /etc/my.cnf.d/mysql-server.cnf | grep sql_require_primary_key

2.2.2. Restart & Check MySQL Service
Restart all servers in the cluster to take effect.

systemctl restart mysqld.service

systemctl status mysqld.service

2.2.3. Check Primary Node in the Cluster

mysqlsh --uri root@localhost
\c innodbcluster@rocky95-mysql01:3306
var v_cluster= dba.getCluster('INNODB_CLS')
v_cluster.status()

2.2.4. Change Primary Node (If Needed)

v_cluster.setPrimaryInstance('innodbcluster@rocky95-mysql01')
v_cluster.status()

2.2.5. Try to Create Table Without PK
Since the sql_require_primary_key parameter is ON, a table without a PK is not allowed to be created as expected.

mysqlsh --uri root@localhost
\sql
show databases;
use testdb;

CREATE TABLE customers (
    customer_id INT,
    name VARCHAR(100),
    email VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);