Disable ONLY_FULL_GROUP_BY
MysqlMysql Problem Overview
I accidentally enabled ONLY_FULL_GROUP_BY mode like this:
SET sql_mode = 'ONLY_FULL_GROUP_BY';
How do I disable it?
Mysql Solutions
Solution 1 - Mysql
Solution 1: Remove ONLY_FULL_GROUP_BY from mysql console
mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
you can read more here
Solution 2: Remove ONLY_FULL_GROUP_BY from phpmyadmin
Solution 2 - Mysql
Update:
To keep your current mysql settings and disable ONLY_FULL_GROUP_BY
I suggest to visit your phpmyadmin or whatever client you are using and type:
SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','') copy_me
next copy result to your my.ini
file.
mint: sudo nano /etc/mysql/my.cnf
ubuntu 16 and up: sudo nano /etc/mysql/my.cnf
ubuntu 14-16: /etc/mysql/mysql.conf.d/mysqld.cnf
copy_me
result can contain a long text which might be trimmed by default. Make sure you copy whole text!
Caution! old answer:
If you want to disable permanently error "Expression #N of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db.table.COL' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by" do those steps:
-
sudo nano /etc/mysql/my.cnf
-
Add this to the end of the file
[mysqld] sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
-
sudo service mysql restart
to restart MySQL
This will disable ONLY_FULL_GROUP_BY
for ALL users
Solution 3 - Mysql
Be careful using
SET sql_mode = ''
This actually clears all the modes currently enabled. If you don't want to mess with other settings, you'll want to do a
SELECT @@sql_mode
first, to get a comma-separated list of the modes enabled, then SET it to this list without the ONLY_FULL_GROUP_BY
option.
Solution 4 - Mysql
Give this a try:
SET sql_mode = ''
Community Note: As pointed out in the answers below, this actually clears all the SQL modes currently enabled. That may not necessarily be what you want.
Solution 5 - Mysql
mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> exit;
Solution 6 - Mysql
Adding only one mode to sql_mode without removing existing ones:
SET sql_mode=(SELECT CONCAT(@@sql_mode,',<mode_to_add>'));
Removing only a specific mode from sql_mode without removing others:
SET sql_mode=(SELECT REPLACE(@@sql_mode,'<mode_to_remove>',''));
In your case, if you want to remove only ONLY_FULL_GROUP_BY
mode, then use below command:
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));
Reference: http://johnemb.blogspot.com/2014/09/adding-or-removing-individual-sql-modes.html
Solution 7 - Mysql
Thanks to @cwhisperer. I had the same issue with Doctrine in a Symfony app. I just added the option to my config.yml:
doctrine:
dbal:
driver: pdo_mysql
options:
# PDO::MYSQL_ATTR_INIT_COMMAND
1002: "SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))"
This worked fine for me.
Solution 8 - Mysql
I have noticed that @Eyo Okon Eyo solution works as long as MySQL server is not restarted, then defaults settings are restored. Here is a permanent solution that worked for me:
To remove particular SQL mode (in this case ONLY_FULL_GROUP_BY), find the current SQL mode:
SELECT @@GLOBAL.sql_mode;
copy the result and remove from it what you don't need (ONLY_FULL_GROUP_BY)
e.g.:
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
to
STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
create and open this file:
/etc/mysql/conf.d/disable_strict_mode.cnf
and write and past into it your new SQL mode:
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
restart MySQL:
sudo service mysql restart
Or you can use ANY_VALUE()
to suppress ONLY_FULL_GROUP_BY value rejection, you can read more about it here
Solution 9 - Mysql
On:
- Ubuntu 14.04
- mysql Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using EditLine wrapper
Do:
$ sudo nano /etc/mysql/conf.d/mysql.cnf
Copy and paste:
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
To the bottom of the file
$ sudo service mysql restart
Solution 10 - Mysql
The MySQL documentation also specifies the following methods:
- Set
sql-mode="<modes>"
in an option file such as my.cnf (Unix operating systems) or my.ini (Windows). - To set the SQL mode at server startup via the command line, use the
--sql-mode="<modes>"
option.
*Where <modes>
is a list of different modes separated by commas.
To clear the SQL mode explicitly, set it to an empty string using --sql-mode=""
on the command line, or sql-mode=""
in an option file.
I added the sql-mode=""
option to /etc/my.cnf
and it worked.
This SO solution discusses ways to find out which my.cnf file is being used by MySQL.
Don't forget to restart MySQL after making changes.
Solution 11 - Mysql
sql_mode
Add or Remove modes to MySQL 5.7.9 or later
To add or remove a mode from sql_mode
, you can use list_add
and list_drop
functions.
To remove a mode from the current SESSION.sql_mode
, you can use one of the following:
SET SESSION sql_mode = sys.list_drop(@@SESSION.sql_mode, 'ONLY_FULL_GROUP_BY');
SET sql_mode = sys.list_drop(@@sql_mode, 'ONLY_FULL_GROUP_BY');
SET @@sql_mode = sys.list_drop(@@sql_mode, 'ONLY_FULL_GROUP_BY');
To remove a mode from the GLOBAL.sql_mode
that persists for the current runtime operation, until the service is restarted.
SET GLOBAL sql_mode = sys.list_drop(@@GLOBAL.sql_mode, 'ONLY_FULL_GROUP_BY');
MySQL 5.7.8 or prior
Since the sql_mode
value is a CSV string of modes, you would need to ensure that the string does not contain residual commas, which can be accomplished by using TRIM(BOTH ',' FROM ...)
.
To remove a mode from the sql_mode
variable, you would want to use REPLACE()
along with TRIM()
to ensure any residual commas are removed.
SET SESSION sql_mode = TRIM(BOTH ',' FROM REPLACE(@@SESSION.sql_mode, 'ONLY_FULL_GROUP_BY', ''));
SET GLOBAL sql_mode = TRIM(BOTH ',' FROM REPLACE(@@GLOBAL.sql_mode, 'ONLY_FULL_GROUP_BY', ''));
To add a mode to the sql_mode
variable, you would want to use CONCAT_WS(',', ...)
, to ensure a comma is appended with the current modes and TRIM()
to ensure any residual commas are removed.
SET SESSION sql_mode = TRIM(BOTH ',' FROM CONCAT_WS(',', 'ONLY_FULL_GROUP_BY', @@SESSION.sql_mode));
SET GLOBAL sql_mode = TRIM(BOTH ',' FROM CONCAT_WS(',', 'ONLY_FULL_GROUP_BY', @@GLOBAL.sql_mode));
> NOTE: Changing the GLOBAL
variable does not propagate to the SESSION
variable, until a new connection is established.
>
> The GLOBAL
variable will persist until the running service is restarted.
>
> The SESSION
variable will persist for the current connection, until the connection is closed and a new connection is established.
GLOBAL.sql_mode
Revert to Since SET sql_mode = 'ONLY_FULL_GROUP_BY';
was executed without the GLOBAL
modifier, the change only affected the current SESSION
state value, which also pertains to @@sql_mode
. To remove it and revert to the global default on server restart value, you would want to use the value from @@GLOBAL.sql_mode
. [sic]
> The current SESSION
value is only valid for the current connection.
> Reconnecting to the server will revert the value back to the GLOBAL
> value.
To revert the current session state value to the current global value, you can use one of the following:
SET SESSION sql_mode = @@GLOBAL.sql_mode;
SET @@sql_mode = @@GLOBAL.sql_mode;
SET sql_mode = @@GLOBAL.sql_mode;
Change SESSION.sql_mode
value to ONLY_FULL_GROUP_BY
SET sql_mode = 'ONLY_FULL_GROUP_BY';
SELECT @@sql_mode, @@GLOBAL.sql_mode;
+--------------------+----------------------------------------------+
| @@sql_mode | @@GLOBAL.sql_mode |
+--------------------+----------------------------------------------+
| ONLY_FULL_GROUP_BY | NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION |
+--------------------+----------------------------------------------+
Revert the SESSION.sql_mode
value to the GLOBAL.sql_mode
value
SET sql_mode = @@GLOBAL.sql_mode;
SELECT @@sql_mode, @@GLOBAL.sql_mode;
+----------------------------------------------+----------------------------------------------+
| @@sql_mode | @@GLOBAL.sql_mode |
+----------------------------------------------+----------------------------------------------+
| NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION | NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------+----------------------------------------------+
sql_mode
using the option file
Server Restart Persistent > To set the SQL mode
> at server startup, use the --sql-mode="modes"
option on the command
> line, or sql-mode="modes"
in an option file such as my.cnf (Unix
> operating systems) or my.ini (Windows). [sic]
Please see your version of MySQL to determine the supported and default modes.
MySQL >= 5.7.5, <= 5.7.6 default
[mysqld]
sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
Please see the Option File Syntax for more information.
> The syntax for specifying options in an option file is similar to
> command-line syntax. However, in an option file, you omit the leading
> two dashes from the option name and you specify only one option per
> line. For example, --quick
and --host=localhost
on the command line
> should be specified as quick
and host=localhost
on separate lines in
> an option file. To specify an option of the form --loose-opt_name
in
> an option file, write it as loose-opt_name
.
> The value optionally can be enclosed within single quotation marks or
> double quotation marks, which is useful if the value contains a #
> comment character.
sql_mode
values
Default Since the MySQL documentation per-version values have been removed, I have added them here for your reference.
MySQL >= 8.0.11 8.0.5 - 8.0.10 Skipped
ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ZERO_IN_DATE NO_ZERO_DATE ERROR_FOR_DIVISION_BY_ZERO NO_ENGINE_SUBSTITUTION
MySQL >= 5.7.8, <= 8.0.4
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
MySQL 5.7.7
ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION
MySQL >= 5.7.5, <= 5.7.6
ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ENGINE_SUBSTITUTION
MySQL >= 5.6.6, <= 5.7.4
NO_ENGINE_SUBSTITUTION
MySQL <= 5.6.5
''
Solution 12 - Mysql
If you are using WAMP. Left click on the WAMP icon then goto MySQL -> MySQL settings -> sql-mode and then select sql-mode->user mode
Solution 13 - Mysql
To disable ONLY_FULL_GROUP_BY with the help of the following query.
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
And to enable the ONLY_FULL_GROUP_BY use following query.
SET sql_mode = 'ONLY_FULL_GROUP_BY';
Solution 14 - Mysql
On MySQL 5.7 and Ubuntu 16.04, edit the file mysql.cnf.
$ sudo nano /etc/mysql/conf.d/mysql.cnf
Include the sql_mode like the following and save the file.
[mysql]
sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Observe that, in my case, I removed the mode STRICT_TRANS_TABLES and the ONLY_FULL_GROUP_BY.
Doing this, it will save the mode configuration permanently. Differently if you just update the @@sql_mode through MySQL, because it will reset on machine/service restart.
After that, to the modified configuration take in action, restart the mysql service:
$ sudo service mysql restart
Try to access the mysql:
$ mysql -u user_name -p
If you are able to login and access MySQL console, it is ok. Great!
BUT, if like me, you face the error "unknown variable sql_mode", which indicates that sql_mode is an option for mysqld, you will have to go back, edit the file mysql.cnf again and change the [mysql]
to [mysqld]
. Restart the MySQL service and do a last test trying to login on MySQL console. Here it is!
Solution 15 - Mysql
This is what I performed to fix on Mysql workbench:
Before I got the current value with the below command
SELECT @@sql_mode
later I removed the ONLY_FULL_GROUP_BY key from the list and I pasted the below command
SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
Solution 16 - Mysql
For Mac OS Mojave (10.14) Open terminal
$ sudo mkdir /usr/local/mysql-5.7.24-macos10.14-x86_64/etc
$ cd /usr/local/mysql-5.7.24-macos10.14-x86_64/etc
$ sudo nano my.cnf
Paste following:
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Shortkeys to Save & Exit nano: Ctrl+x
and y
and Enter
Note: You might need to update mysql-5.7.24-macos10.14-x86_64
in these commands, just check the correct folder name you got within /usr/local/
Hope it will help someone!
Solution 17 - Mysql
On my sql (version 5.7.11 running on Mac OS X) this work for me on mysql shell client:
SET
@@GLOBAL.sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
According to MySQL 5.6 Documentation, sql_mode is default is
blank string in MySQL 5.6.5 and back NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES in 5.6.6 +
Solution 18 - Mysql
If you are using MySQL 8.0.11 so, you need to remove the ’NO_AUTO_CREATE_USER‘ from sql-mode.
Add following line in file /etc/mysql/my.cnf
and [mysqld] header
[mysqld]
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Solution 19 - Mysql
-
Check default value of sql_mode:
SELECT @@sql_mode;
-
Remove ONLY_FULL_GROUP_BY from console by executing below query:
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
- Also remove it from your specific Database
use database_name;
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
-
Restart your web app server
-
Access that page that was causing this issue. It would work now.
OR
-
Add following in your my.cnf file
sql_mode="TRADITIONAL"
Note: if you are using mac my.cnf might be available here /usr/local/etc/my.cnf
Or try this link https://dev.mysql.com/doc/refman/8.0/en/option-files.html
-
Restart MySQL server
sudo /usr/local/bin/mysql.server restart
OR
brew services restart mysql
Solution 20 - Mysql
I'm using doctrine and I have added the driverOptions in my doctrine.local.php :
return array(
'doctrine' => array(
'connection' => array(
'orm_default' => array(
'driverClass' => 'Doctrine\DBAL\Driver\PDOMySql\Driver',
'params' => array(
'host' => 'localhost',
'port' => '3306',
'user' => 'myusr',
'password' => 'mypwd',
'dbname' => 'mydb',
'driverOptions' => array(
PDO::MYSQL_ATTR_INIT_COMMAND => "SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))"
),
),
),
),
));
In phpmyadmin the user needs SUPER activated in the privileges.
Solution 21 - Mysql
This worked for me:
SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Solution 22 - Mysql
Add the line
sql-mode=""
in my.ini file as a permanent fix.
Solution 23 - Mysql
Im working with mysql and registered with root user, the solution that work for me is the following:
> mysql > SET SESSION sql_mode=(SELECT > REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Solution 24 - Mysql
To whom is running a VPS/Server with cPanel/WHM, you can do the following to permanently disable ONLY_FULL_GROUP_BY
You need root access (either on a VPS or a dedicated server)
-
Enter WHM as root and run phpMyAdmin
-
Click on Variables, look for
sql_mode
, click on 'Edit' and copy the entire line inside that textbox
e.g. copy this:
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
3. Connect to you server via SFTP - SSH (root) and download the file /etc/my.cnf
- Open with a text editor
my.cnf
file on your local PC and paste into it (under[mysqld]
section) the entire line you copied at step (2) but removeONLY_FULL_GROUP_BY,
e.g. paste this:
# disabling ONLY_FULL_GROUP_BY
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
5. Save the my.cnf
file and upload it back into /etc/
- Enter WHM and go to "WHM > Restart Services > SQL Server (MySQL)" and restart the service
Solution 25 - Mysql
As of MySQL 5.7.x, the default sql mode includes ONLY_FULL_GROUP_BY. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default).
ONLY_FULL_GROUP_BY: Non-deterministic grouping queries will be rejected
For more details check the documentation of sql_mode
You can follow either of the below methods to modify the sql_mode
Method 1:
Check default value of sql_mode:
SELECT @@sql_mode
Remove ONLY_FULL_GROUP_BY
from console by executing below query:
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
Method 2:
Access phpmyadmin for editing your sql_mode
Solution 26 - Mysql
This is a permanent solution for MySql 5.7+ on Ubuntu 14+:
$ sudo bash -c "echo -e \"\nsql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION\" >> /etc/mysql/mysql.conf.d/mysqld.cnf"
$ sudo service mysql restart
# Check if login attempt throws any errors
$ mysql -u[user] -p # replace [user] with your own user name
If you are able to login without errors - you should be all set now.
Solution 27 - Mysql
For MySql 8 you can try this one. (not tested on 5.7. Hope it also works there)
First open this file
sudo vi /etc/mysql/my.cnf
and paste below code at the end of above file
[mysqld]
sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
Then restart mysql by running this sudo service mysql restart
Solution 28 - Mysql
in case anyone using Drupal 8 face this issue with mysql 8, I fixed that by overriding the default configuration by adding this piece of code.
Source: https://www.drupal.org/project/drupal/issues/2856270
The option, just in case:
'init_commands' => array(
'sql_mode' => "SET sql_mode =''"
)
Solution 29 - Mysql
You can disable it using the config file my.cnf
:
$ mysql --verbose --help | grep my.cnf
So in macOS 10.12, it's at usr/local/etc/my.cnf
. You can edit sql_mode
here:
# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
Solution 30 - Mysql
Here is my solution changing the Mysql configuration through the phpmyadmin dashboard:
In order to fix "this is incompatible with sql_mode=only_full_group_by": Open phpmyadmin and goto Home Page and select 'Variables' submenu. Scroll down to find sql mode. Edit sql mode and remove 'ONLY_FULL_GROUP_BY' Save it.
Solution 31 - Mysql
The best is to try to use ONLY_FULL_GROUP_BY on new projects while remaining backward compatible on existing sites and databases. For this I opted for a modification of the SQL Mode when loading mysqli, respectively PDO in my classes.
For mysqli :
mysqli_query($this->link,"SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))");
For PDO class :
PDO::MYSQL_ATTR_INIT_COMMAND => "SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))"
Example with PDO init :
try {
$dns = 'mysql:host=localhost;dbname=' . $prod_db;
$user = _DBUSER_;
$pass = _DBPASS_;
$options = array(
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8",
PDO::MYSQL_ATTR_INIT_COMMAND => "SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))"
);
$db = new PDO($dns, $user, $pass, $options);
}
catch(Exception $e) {
mail("[email protected]","Database Error ",$dns.",".$user);
echo "Unable ot connect to mySQL : ", $e->getMessage();
die();
}
Thus, the new classes created on the same server will work with new standard of databases.
Solution 32 - Mysql
What about "optimizer hints" from MySQL 8.x ?
for example:
SELECT /*+ SET_VAR(sql_mode='STRICT_TRANS_TABLES') */
... rest of query
more information: https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-set-var
Solution 33 - Mysql
with MySQL version 5.7.28 check by using
SELECT @@sql_mode;
and update with
SET @@sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
Solution 34 - Mysql
One important thing to note, if you have ANSI on sql_mode:
> Equivalent to REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, and (as of MySQL 5.7.5) ONLY_FULL_GROUP_BY.
See https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_ansi
Solution 35 - Mysql
In case someone else facing same issue as me. On Ubuntu 16.04, the only persistent solution it worked for me:
Edit /lib/systemd/system/mysql.service and set it to:
[Unit]
Description=MySQL Community Server
After=network.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
PermissionsStartOnly=true
ExecStartPre=/usr/share/mysql/mysql-systemd-start pre
# Normally, we'd simply use:
# ExecStart=/usr/sbin/mysqld
ExecStart=/usr/sbin/mysqld --sql-mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
ExecStartPost=/usr/share/mysql/mysql-systemd-start post
TimeoutSec=600
Restart=on-failure
RuntimeDirectory=mysqld
RuntimeDirectoryMode=755`