whmcs install - how to turn off mysql strict mode

Submitted by adamjedgar on Sun, 05/14/2017 - 05:27 Pro Licensee

WHMCS - The Complete Client Management, Billing & Support Solution New Installation

Oops! There's a problem Strict SQL Mode Detected

Please disable strict SQL mode before continuing.

  1. is this also why the whmcs script doesnt work for me? (i get an error)
  2. How do i turn it off so i can manually install and run whmcs?

I can see where it is when i check out the webmin/servers/mysql database server/mysql system variables module below...

"sql_mode 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"

Status: 
Active

Comments

Submitted by andreychek on Sun, 05/14/2017 - 08:36

Howdy -- hmm, what is the output of this command:

grep sql-mode /etc/mysql/my.cnf

Submitted by adamjedgar on Sun, 05/14/2017 - 16:55 Pro Licensee

grep sql-mode /etc/mysql/my.cnf

produces nothing...just goes back to a command prompt. I cannot vi edit it either from within virtualmin command prompt the screen just goes blank after the i hit enter (gotta go into google cloud compute shell instead).

Submitted by andreychek on Mon, 05/15/2017 - 08:24

Okay, doing some more Googling, it does look like you should be able to undo those settings by changing the MySQL variables.

Before doing any of this, I'd recommend making a backup of your MySQL databases.

You may be able to do these stepd from within Webmin, though it also looks like you can do so with the following steps:

Access MySQL on the command line as root with: mysql -u root -p
In MySQL, run: use mysql;
Unset the variable with this command: SET @@global.sql_mode="";

After running the above, see if you're then able to install WHMCS.

Submitted by adamjedgar on Mon, 05/15/2017 - 16:03 Pro Licensee

Should i be changing the global setting on a webhosting server?

I really only want to change it for the whmcs subdomain virtualmin virtual server. My understanding is that any updates to mysql will overwrite this as its now a standard default on mysql to use strict mode?

If above is true, i wonder if whmcs need to change their software to work with it?

Submitted by andreychek on Mon, 05/15/2017 - 22:25

It doesn't appear that strict mode can be changed per user or per database. It can be changed globally, or per-session (and per-session appears to require support of the application to set that).

Which WHMCS version is it that you're trying to install there? And which MySQL version do you have there?

Doing a little more research, it's possible that error is specific to trying to install WHMCS 6.x on MySQL 5.7.

However, WHMCS is no longer supporting WHMCS 6.x anyhow, that reached it's end of life in March according to this here:

http://docs.whmcs.com/Long_Term_Support

Is using WHMCS 7.x an option?

If not, you could try setting the option:

sql-mode=""

In the mysqld section of your my.cnf, that should unset that particular setting in MySQL in a way that works across restarts.

Submitted by adamjedgar on Fri, 05/19/2017 - 01:54 Pro Licensee

running whmcs 7 and latest mysql on virtualmin.

Submitted by masterg0g0 on Thu, 01/04/2018 - 11:21 Pro Licensee

hello, i have the same problem.. since my ubuntu 16lts has mysql 5.7.. WHMCS throws this message to disable this mode... what do you recomend i do ?

Submitted by andreychek on Thu, 01/04/2018 - 12:48

Try the "sql-mode" suggestion made in Comment #5 above and see if that helps.

Submitted by masterg0g0 on Fri, 01/05/2018 - 01:20 Pro Licensee

got it thanks..