What is SQL mode & Why is ONLY_FULL_GROUP_BY SQL mode ? Print

Introduction

SQL mode is a configuration feature in MySQL and MariaDB that determines how the database handles SQL syntax, data validation, and query execution. Different SQL modes allow you to control database behavior, ensuring compatibility or enforcing strict standards for queries.

SQL Modes Overview

SQL mode can be set at three levels:

1. Server Level: Configured globally for the database server.
2. Session Level: Applies to a specific session.
3. Query Level: Enabled or disabled for individual queries.

SQL modes are specified using the sql_mode variable and can be adjusted dynamically or via configuration files.

Examples of SQL Modes:

• STRICT_TRANS_TABLES: Enforces strict data validation for transactions.
• ONLY_FULL_GROUP_BY: Requires all columns in SELECT not wrapped in an aggregate function to be included in the GROUP BY clause.
• NO_ZERO_DATE: Prevents the use of 0000-00-00 as a valid date.
• ANSI_QUOTES: Treats double quotes as string delimiters.

What is ONLY_FULL_GROUP_BY SQL Mode?

The ONLY_FULL_GROUP_BY mode enforces strict rules for grouping queries. It ensures that:

1. All non-aggregated columns in the SELECT list are included in the GROUP BY clause.
2. Queries return deterministic results by grouping logically.

Why is ONLY_FULL_GROUP_BY Disabled by Default in cPanel?

In shared hosting environments like cPanel:

1. Many legacy scripts and applications use non-standard SQL syntax incompatible with ONLY_FULL_GROUP_BY.
2. Disabling it ensures broader compatibility, minimizing errors for older or less strict applications.

How to Enable ONLY_FULL_GROUP_BY SQL Mode?

If you manage your own server (e.g., VPS or Dedicated Hosting), you can enable ONLY_FULL_GROUP_BY in MySQL/MariaDB.

Modify MySQL/MariaDB Configuration File:

1. Open the MySQL configuration file (my.cnf or my.ini).
2. Add or modify the sql_mode entry :

   sql_mode = "STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY"
   ​

3. Restart the MySQL/MariaDB service:

   sudo service mysql restart
   ​

Enable via SQL Query:
Run this query to enable the mode dynamically:

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY';

Verify Current SQL Modes:
Check the active SQL modes using:

SELECT @@GLOBAL.sql_mode;

Pros and Cons of ONLY_FULL_GROUP_BY

Pros

1. Data Integrity: Ensures grouped queries return logically consistent results.
2. Error Prevention: Avoids ambiguous queries that can produce unexpected outputs.
3. Optimization: Helps MySQL optimize queries more effectively.

Cons

1. Compatibility Issues: May break older applications or scripts relying on permissive behavior.
2. Increased Development Effort: Requires developers to rewrite non-compliant queries.
3. Not Always Necessary: In some cases, permissive behavior is preferred for performance.

Default SQL Mode in cPanel

The default SQL mode in cPanel varies but typically includes:

• NO_ENGINE_SUBSTITUTION: Prevents automatic engine substitution.
• STRICT_TRANS_TABLES: Ensures strict validation for transactional engines.
• NO_ZERO_IN_DATE and NO_ZERO_DATE: Prevent invalid date values.
• ERROR_FOR_DIVISION_BY_ZERO: Generates errors for division by zero.

Example default configuration:

sql_mode = "NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO"

How to Enable Multiple SQL Modes

You can enable multiple SQL modes simultaneously by listing them in a comma-separated format. For example:

SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY,ANSI_QUOTES';

Or in the configuration file:

sql_mode = "STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY,ANSI_QUOTES"

Should You Enable ONLY_FULL_GROUP_BY?

The decision depends on your needs:

• Enable ONLY_FULL_GROUP_BY if:

  • You want strict validation for data integrity.
  • Your applications and queries comply with strict SQL standards.

• Do not enable ONLY_FULL_GROUP_BY if:

  • You rely on older applications or scripts with non-standard SQL syntax.
  • Compatibility is more important than strict query validation.

Recommendation: Test your applications thoroughly in a staging environment before enabling ONLY_FULL_GROUP_BY in production.

Conclusion

ONLY_FULL_GROUP_BY ensures strict SQL query validation and prevents unexpected results, enhancing database integrity. However, enabling it in cPanel shared hosting environments may cause compatibility issues. If you manage your own VPS or dedicated server, you can enable and combine SQL modes to fit your specific requirements. Always test your queries and applications to ensure compatibility when making such changes.