Mysql Maria DB fields don't have defualt value

[briand]

as far as I gather this is the cause: STRICT_TRANS_TABLES

sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

i wish to remove STRICT_TRANS_TABLES

I’ve searched and the info tells me to modify the my.cnf file (or one of the cponfig files).
however. I seem unable to find the correct config file or the data in mariadb in control panel. ( I see the above parameter but can;t change it)

MariaDB version 10.3.32
Operating system Ubuntu Linux 20.04.3
Webmin version 1.984

thanks for any pointers
Brian

[briand]

Peter mentioned I should do more checking and set the parameters, hence the reason for the mechanisms.(as I had ported from mysql to maraidb)

indeed, but I’m happy with the way it was ;o)
I would like to remove STRICT_TRANS_TABLES so will check further

I did manage to remove STRICT_TRANS_TABLES by getting the right password for the system (typing command in) but not sure its yet saved on a reboot (I tried GLOBAL)

[Ilia]

Hi,

Have a look at Webmin / Servers ⇾ MySQL Database Server: Edit Config Files page and edit /etc/mysql/mysql.conf.d/mysqld.cnf file.

As the default for MySQL on Ubuntu 20.04 includes STRICT_TRANS_TABLES for sql_mode:

… all you should do is to copy defaults and removing wished option, and adding that line of directives to mysqld.cnf file, under [mysqld] section and restarting MySQL aftetwards:

sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

This will preserve defaults minus mentioned option you wished to remove, and that will survive restarts/reboots.

[briand]

Thanks for your input

I’ll check it shortly

[briand]

Hi Ilia,
thanks for your help on that, it clarified what I needed to do.

I updated this file:
/etc/mysql/mariadb.conf.d/50-server.cnf

wityh this:
[mariadb-10.3]

sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

restarted MariaDB and it has saved the details.

thank you