Unexpected error connecting vtiger with mysql

Question

I am currently getting this error in vtiger installation wizard (step 4).

MySQL Server should be configured with:  
sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

I am using shared PHP 7.2 MySQL 5.7 hosting and adminer says that sql_mode is set to

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

---

Here comes the question
Is this behaviour really related to sql_mode setting? Do I have to contact my hosting provider or there is there an override?

Answer 1

I think you should pay attention to the port Mysql is using, if you are using Wampserver, By default the new versions install MariaDB first, and MySQL second so MariaDB get the port 3306 and Mysql get the an other one (most of the time 3307).

Vtiger will always try to connect to a database to the port 3306 (unless you change it in the code). So you can change the sql-mode in "/bin/mysqlx.x.x/my.cfg" as you want, and see it changed in the MySQL console, but Vtiger won't be trying to connect to it, but to 3306.

I spent way more time on this issue than it deserved, mostly because I felt that changing the "sql-mode" in the .cfg file to change an "sql_mode" variable was weird and was the issues, idk why MySQL made it like that but that's not user friendly at all.

Answer 2

For Ubuntu - Run command:

sudo nano /etc/mysql/my.cnf

Add the following part to the bottom:

[mysqld]

sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Run command to restart MySQL Service:

sudo service mysql restart

You will also need to change Database Collation to utf8_general_ci in phpmyadmin.

Done !

Answer 3

Connecting to a new mariadb 10 database solved the problem.

Changing MySQL version to <5.7 as stated in comments should therefore work as well.