How to Disable MySQL Strict Mode

MySQL’s, and MariaDB’s, strict mode controls how invalid or missing values in data changing queries are handled; this includes INSERT, UPDATE, and CREATE TABLE statements. With MySQL strict mode enabled, which is the default state, invalid or missing data may cause warnings or errors when attempting to process the query.

When strict mode is disabled the same query would have its invalid, or missing, values adjusted and would produce a simple warning. This may seem like the preferred result, however with strict mode disabled certain actions may cause unexpected results; for instance, when the value being inserted exceeds the maximum character limit it will be truncated to fit the limit.

There are various reasons why MySQL’s strict mode may need to be disabled, however the most common is when a server is running WHMCS — this is a requirement of that tool.

Pre-Flight Check

  • These instructions are intended specifically for disabling MySQL strict mode on a managed Liquid Web server with cPanel.
  • The server should be running either MySQL 5.6/5.7 or MariaDB 10.x
  • Command line and root level access via SSH will be necessary to follow this tutorial.

Step #1: Make Backups, Always!

Whenever modifying files on a server it’s always best practice to take some form of a backup beforehand. This ensures you have a way to revert changes if something goes awry; it’s also beneficial because it helps track when and what changes were made.

While logged into SSH with the root user, do the following:

cp -a /usr/my.cnf{,.strict.bak}
cp -a /etc/my.cnf{,.strict.bak}

The above command uses ‘BASH brace expansion’ in order to make a backup copy of the file in its original directory.

Step #2: Disable MySQL Strict Mode

Depending on the server and the current configurations you may need to edit one, or both, of the following files on the server. Generally, the relevant configuration lines are only in one of them, however, it could be in either one without causing issues; so generally it’s best to check both.

To edit the files, you will open the file with your favorite command line editor. In this example, we use ‘vim’.

vim /usr/my.cnf
vim /etc/my.cnf

In vim, you can press “a” or “i” to enter text insertion mode; pressing the escape key (Esc) on your keyboard returns you to command mode. For a refresher on editing files with vim, see our New User Tutorial: Overview of the Vim Text Editor.

Within each file above you will be looking for a line with the following content:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

If you find a line similar to the above that is setting the `sql_mode` variable then you will need to replace it with the following line to disable MySQL strict mode.

sql_mode=""

Once this adjustment has been made, or you’ve confirmed the file does not need to be adjusted you will then save and close the file.

Step #3: Restart the MySQL Service

Finally, to make these changes effective you will need to restart the MySQL service as it will only read the configuration files when it initially loads up. In order to force MySQL to use the new configuration files you will do the following:

For CentOS 7 servers:
systemctl restart mysql

For CentOS 6 and prior:
/etc/init.d/mysql restart

After issuing this command on the server the MySQL service will be restarted and will load the changes made. If all the directions were followed and completed, then MySQL strict mode should now be disabled.

To verify that the process was completed properly you can run the following:

mysql -e "SELECT @@sql_mode;"

The output may look similar to the following:

+--------------------------------------------+
| @@sql_mode
+--------------------------------------------+
| NO_AUTO_CREATE_USER
+--------------------------------------------+

If you have any questions or are not comfortable making these changes yourself, please feel free to contact Heroic Support®.

How to Check the Version of cPanel / WHM

Pre-Flight Check

  • These instructions are intended specifically for checking your version of cPanel or WHM via the command line or the WHM dashboard.
  • I’ll be working from a Liquid Web Managed CentOS 7 server, and I’ll be logged in as root.

Method #1: Checking the Version of cPanel / WHM by the ‘cpanel’ Command

Using the cPanel command:

/usr/local/cpanel/cpanel -V

11.50.0 (build 27)

Continue reading “How to Check the Version of cPanel / WHM”

How to Remove (Delete) a User on Ubuntu 15.04

Users via Command Line 101: Basic User Interaction
I. How to Add a User and Grant Root Privileges on Ubuntu 15.04
II. How to Remove (Delete) a User on Ubuntu 15.04

Pre-Flight Check

  • These instructions are intended specifically for removing a user on Ubuntu 15.04.
  • I’ll be working from a Liquid Web Self Managed Ubuntu 15.04 server, and I’ll be logged in as root.

Continue reading “How to Remove (Delete) a User on Ubuntu 15.04”

7 Extremely Useful Linux Commands for Beginners

#1: ls : What’s in this directory?

The command ls stands for list directory contents. And, cleverly, it will do just that: list a directory’s contents! Using it with -F will give a list of the directories contents, and denote items that are other directories with a trailing /.

ls -F

On my server returns:

allthethings.txt important.doc Indominus/ Misc/ probs.xls Red Wings/ Spreadsheets/ Work/

In the above case, allthethings.txt, garbage.file, important.doc, and probs.xls are files, and Indominus, Misc, Red Wings, Spreadsheets, and Work, each with the trailing /, are directories!

There are many other options, or switches, such as -F that can be used with ls for improved results. For example:

ls -lFa

Returns:

dr-xr-x---. 10 root root 4096 Apr 17 12:01 .
drwxr-xr-x. 19 root root 4096 Apr 14 12:45 ..
-rw-r--r-- 1 root root 0 Apr 17 12:00 allthethings.txt
-rw------- 1 root root 483 Apr 14 12:45 .bash_history
-rw-r--r--. 1 root root 18 Dec 28 2013 .bash_logout
-rw-r--r--. 1 root root 176 Dec 28 2013 .bash_profile
-rw-r--r--. 1 root root 361 Jan 1 01:24 .bashrc
drwxr-xr-x 3 root root 4096 Jan 1 01:25 .cache/
drwxr-xr-x 3 root root 4096 Jan 1 01:25 .config/
-rw-r--r--. 1 root root 100 Dec 28 2013 .cshrc
-rw-r--r-- 1 root root 0 Apr 17 12:01 garbage.file
-rw-r--r-- 1 root root 0 Apr 17 11:58 important.doc
drwxr-xr-x 2 root root 4096 Apr 17 11:59 Indominus/
drwxr-xr-x 2 root root 4096 Apr 17 11:57 Misc/
-rw------- 1 root root 42 Apr 14 12:44 .my.cnf
-rw-r--r-- 1 root root 0 Apr 17 12:00 probs.xls
drwxr-xr-x 2 root root 4096 Apr 17 11:57 Red Wings/
-rw------- 1 root root 1024 Jan 1 01:22 .rnd
drwxr-xr-x 2 root root 4096 Apr 17 11:56 Spreadsheets/
drw------- 2 root root 4096 Apr 14 12:42 .ssh/
-rw-r--r--. 1 root root 129 Dec 28 2013 .tcshrc
drwxr-xr-x 2 root root 4096 Apr 17 11:57 Work/

In the above case two switches are added: -l and -a. The -l uses the long listing format, and the -a switch lists all of the files, including hidden files.

Each column contains an important bit of information:

Column | Information | Example

  • 1 | Permissions | drwxr-xr-x
  • 2 | # of Hard Links | 2
  • 3 | User That Owns File or Directory | root
  • 4 | Group for File or Directory | root
  • 5 | File Size | 4096
  • 6 | Timestamp | Apr 17 11:59
  • 7 | Filename | Indominus/

Continue reading “7 Extremely Useful Linux Commands for Beginners”

How to Check the MySQL Version

Pre-Flight Check
  • These instructions are intended for checking the MySQL version on your server.
  • I’ll be working from a Liquid Web Core Managed CentOS 7 server, and I’ll be logged in as root.
Note: Are you looking to upgrade MySQL on a Liquid Web Core Managed or Fully Managed server? Then contact our Heroic Support® team! And if you’re looking for ways to improve the performance of your VPS, check out our blog on Optimizing Your VPS!

Continue reading “How to Check the MySQL Version”

How to Remove (Delete) a User on Ubuntu 14.04 LTS

Users via Command Line 101: Basic User Interaction
I. How to Add a User and Grant Root Privileges on Ubuntu 14.04 LTS
II. How to Remove (Delete) a User on Ubuntu 14.04 LTS

Pre-Flight Check

  • These instructions are intended specifically for removing a user on Ubuntu 14.04 LTS.
  • I’ll be working from a Liquid Web Core Managed Ubuntu 14.04 LTS server, and I’ll be logged in as root.

Continue reading “How to Remove (Delete) a User on Ubuntu 14.04 LTS”