Strict Mode question

ca2236

• February 25, 2019 10:21

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

  • February 25, 2019 19:35

  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.

  0
• 

  ca2236

  • March 19, 2019 22:45

  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 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)

  0
• 

  cPanelMichael

  • March 20, 2019 16:33

  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.

  0
• 

  ca2236

  • March 20, 2019 20:52

  Thanks! We can resolve this ticket.

  0

Please sign in to leave a comment.