Level 75
Jun 7, 2020
16
Level 15
How to solve the MySQL 5.7.30 "sql_mode=only_full_group_by" problem
I did a fresh install of Homestead which includes MySQL 5.7.30. I brought across all my Laravel files from my Linux system which also runs MySQL 5.7.30.
For some reason bizaar reason when I run queries on the Homestead system I get the problem:
Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'contraxiq.orders.quote_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Ok - so inside the Homestead version I set the config/database mysql strictsettings to "false". I have also tried turning off the error with no effect.
mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
I have dozens of queries with groupby. As an example, here is one which generates the error:
public function open_orders() {
return DB::table('orders')
->join('customers', 'customers.id', '=', 'orders.customer_id')
->select('orders.reference', 'quote_name','start_date', 'company_name', 'customers.firstname1', 'customers.lastname1', 'customers.city', 'orders.total', 'orders.created_at')
->where('orders.status', '=', 'open')
->groupBy('orders.reference')
->orderBy('orders.created_at')
->get();
}
Wat do I do to fox this problem ? I do not want to rewrite all the dozens of queries as suggested by this post: https://gabi.dev/2016/03/03/group-by-are-you-sure-you-know-it/
Many Thanks !!
Level 15
@jlrdw Brilliant! It works! But here is the bigger question, how can I set this globally? Adding this statement to all my queries is not too bad, however, it seems a little unneccessary.
I have already set config.database.mysql.strict = false to no effect.
Additionally I tried some advice on SO, to set mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); also to no effect.
Level 33
That's strange @vincej
Changing the database config should work since you're resorting to the same mechanism when you're changing the config on the fly.
Could you paste the contents of your config/database.php file and let us know which version of Laravel are you using?
Level 75
@vincej in config\database
Just change
'strict' => true,
To false.
But your queries should be written so you don't have to do this. See the mysql manual on full group by.
Level 15
@jlrdw Like I said, I have alerady changed the connection to false.
@guybrush_threepwood I'm using L7.11.0
<?php
use Illuminate\Support\Str;
return [
/*
|--------------------------------------------------------------------------
| Default Database Connection Name
|--------------------------------------------------------------------------
|
| Here you may specify which of the database connections below you wish
| to use as your default connection for all database work. Of course
| you may use many connections at once using the Database library.
|
*/
'default' => env('DB_CONNECTION', 'mysql'),
/*
|--------------------------------------------------------------------------
| Database Connections
|--------------------------------------------------------------------------
|
| Here are each of the database connections setup for your application.
| Of course, examples of configuring each database platform that is
| supported by Laravel is shown below to make development simple.
|
|
| All database work in Laravel is done through the PHP PDO facilities
| so make sure you have the driver for your particular database of
| choice installed on your machine before you begin development.
|
*/
'connections' => [
'sqlite' => [
'driver' => 'sqlite',
'url' => env('DATABASE_URL'),
'database' => env('DB_DATABASE', database_path('database.sqlite')),
'prefix' => '',
'foreign_key_constraints' => env('DB_FOREIGN_KEYS', true),
],
'mysql' => [
'driver' => 'mysql',
'url' => env('127.0.0.1'),
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '33060'),
'database' => env('DB_DATABASE', ''),
'username' => env('DB_USERNAME', ''),
'password' => env('DB_PASSWORD', ''),
'unix_socket' => env('DB_SOCKET', ''),
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
'prefix_indexes' => true,
'strict' => false,
'engine' => null,
'options' => extension_loaded('pdo_mysql') ? array_filter([
PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
]) : [],
],
'pgsql' => [
'driver' => 'pgsql',
'url' => env('DATABASE_URL'),
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '5432'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'prefix' => '',
'prefix_indexes' => true,
'schema' => 'public',
'sslmode' => 'prefer',
],
'sqlsrv' => [
'driver' => 'sqlsrv',
'url' => env('DATABASE_URL'),
'host' => env('DB_HOST', 'localhost'),
'port' => env('DB_PORT', '1433'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'prefix' => '',
'prefix_indexes' => true,
],
],
/*
|--------------------------------------------------------------------------
| Migration Repository Table
|--------------------------------------------------------------------------
|
| This table keeps track of all the migrations that have already run for
| your application. Using this information, we can determine which of
| the migrations on disk haven't actually been run in the database.
|
*/
'migrations' => 'migrations',
/*
|--------------------------------------------------------------------------
| Redis Databases
|--------------------------------------------------------------------------
|
| Redis is an open source, fast, and advanced key-value store that also
| provides a richer body of commands than a typical key-value system
| such as APC or Memcached. Laravel makes it easy to dig right in.
|
*/
'redis' => [
'client' => env('REDIS_CLIENT', 'phpredis'),
'options' => [
'cluster' => env('REDIS_CLUSTER', 'redis'),
'prefix' => env('REDIS_PREFIX', Str::slug(env('APP_NAME', 'laravel'), '_').'_database_'),
],
'default' => [
'url' => env('REDIS_URL'),
'host' => env('REDIS_HOST', '127.0.0.1'),
'password' => env('REDIS_PASSWORD', null),
'port' => env('REDIS_PORT', '6379'),
'database' => env('REDIS_DB', '0'),
],
'cache' => [
'url' => env('REDIS_URL'),
'host' => env('REDIS_HOST', '127.0.0.1'),
'password' => env('REDIS_PASSWORD', null),
'port' => env('REDIS_PORT', '6379'),
'database' => env('REDIS_CACHE_DB', '1'),
],
],
];
Level 75
See https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html You should leave it true and re-work some queries. But that's up to you.
When you change it in config, you need to run a config:clear also.
Or is it clear:config, well you know what I mean.
Level 15
@jlrdw Thanks for that. What is curious to me is that my Linux dev machine has the same MySQL version, although it is rinning L6.x.
The article I quoted above required a whole sale massive rewrite, This is very undesirable. Moderate rewrite is not a problem.
Level 33
The only problem I see is that your mysql.url is set incorrectly:
'url' => env('127.0.0.1'),
Should be:
'url' => env('DATABASE_URL'),
From the Laravel documentation:
For convenience, Laravel supports these URLs as an alternative to configuring your database with multiple configuration options.
I believe you shouldn't set that variable unless you want to use a URL for database connection/configuration (which is not the framework's default setting for MySQL).
Level 15
@guybrush_threepwood Ok, however, I am using Homestead for this deployment. I'm not too sure what url I should use for HOmestead.
Level 33
Don't set a URL, leave it like this:
'url' => env('DATABASE_URL'),
And do not set DATABASE_URL in your .env file either.
You only need to set the following options to be able to connect to your MySQL database (url is not needed):
DB_CONNECTION
DB_HOST
DB_PORT
DB_DATABASE
DB_USERNAME
DB_PASSWORD
Level 15
@guybrush_threepwood Ok, did as you suggested. As works as before. Still with the MySQL error. Is this supposed to help with setting the only_fully_group_by problem?
Level 33
Sorry to hear that. I believe it should.
You could try setting the SQL modes manually from the database config (list every one of your existing settings except ONLY_FULL_GROUP_BY):
'mysql' => [
'modes' => [
'STRICT_ALL_TABLES',
'ERROR_FOR_DIVISION_BY_ZERO',
'NO_ZERO_DATE',
'NO_ZERO_IN_DATE',
'NO_AUTO_CREATE_USER',
],
],
Try running SELECT @@sql_mode; to check what are the default settings of your installation.
Level 15
mysql> SELECT @@sql_mode; +------------------------------------------------------------------------------------------------------------------------+ | @@sql_mode | +------------------------------------------------------------------------------------------------------------------------+ | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
Level 33
Selecting custom modes will override "strict" mode.
These are the default modes Laravel sets on strict mode:
MySQL 8:
sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
Other MySQL versions:
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'
Try setting it to that according to your version (and exclude ONLY_FULL_GROUP_BY).
Level 15
IT WORKED!!! SOLVED!!! MANY THANKS !!!
1 like
Level 33
Great! You're welcome :)
1 like
Please or to participate in this conversation.