SOLVED Strict Mode question

[ca2236]

Hello

I had a question when converting from mysql to mariadb. I know newer databases force strict mode by default. However, my question is, do we need to set default values for each of the fields or is this handled by default with the conversion script (since strict mode requires default field values)

 

[cPanelMichael]

Hello @ca2236,

The SQL mode configured at the time of creation is not automatically modified per the following MariaDB document:

SQL_MODE

Globally, upgrading to MariaDB version 10.2 or higher will automatically enable strict mode and remove any existing sql_mode entries in the /etc/my.cnf. This is by-design due how MariaDB implements the change. After the initial upgrade to MariaDB 10.2, you'd need to manually add the following value under the [mysqld] section in the /etc/my.cnf file if you want to revert this change:

sql_mode=NO_ENGINE_SUBSTITUTION

Then, restart MariaDB to apply the changes. Once your system is using MariaDB 10.2, the future minor updates will not require that you manually update the sql_mode value again.

Thank you.

 

[ca2236]

Hi,

Thanks for taking the time to answer my question, I do have a follow up question.

Looking at this page
Understanding SQL compatibility modes in MySQL and MariaDB - makandra dev

and seeing this section here:
=======================================================================
Important SQL mode flags
These SQL modes will probably give you the most headaches:

STRICT_TRANS_TABLES, STRICT_ALL_TABLES
Introduced in MySQL 5.7, these enable Strict SQL mode. In SQL Strict mode MySQL is likely to throw an error if an INSERT or UPDATE has invalid or missing values. Without strict mode, MySQL will try more to not throw an error.

For instance, if a STRING exceeds the length of a field, MySQL will throw an error in strict mode. Without strict mode it would truncate the string to the maximum column size.
=======================================================================

and this page

Fix for Mysql field doesn't have default value


it says if we leave strict mode in place and and do not have default values set, we get an error on insert/update SQL queries. When doing the SQL upgrade to the latest MySQL or MariaDB that enables strict mode by default, are default values set or would we have to set them ourselves? (barring that we leave strict mode enabled and disable it per the previous comment)

 

[cPanelMichael]

When doing the SQL upgrade to the latest MySQL or MariaDB that enables strict mode by default, are default values set or would we have to set them ourselves? (barring that we leave strict mode enabled and disable it per the previous comment)

Hello @ca2236,

Default values are not automatically assigned to the individual database table schemas during the upgrade. You'd need to do this manually on a table-by-table basis.

Thank you.

 

[ca2236]

Thanks! We can resolve this ticket.