27

I've just installed MySQL 8.0.11, transfered my app's database into it and changed the laravel database settings to use the new one. Now everytime I try to login I get the following error:

ERROR 1231 (42000):
Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'

I tried to set NO_AUTO_CREATE_USER manually:

set global sql_mode="..., NO_AUTO_CREATE_USER, ...";

But I get the same error. How could I solve the problem and run laravel 5.5 with MySQL 8.0.11?

manifestor
  • 1,352
  • 6
  • 19
  • 34

7 Answers7

56

your laravel connexion (config / database.php) should be such that:

'mysql' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', 'localhost'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'charset' => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
        'modes'  => [
            'ONLY_FULL_GROUP_BY',
            'STRICT_TRANS_TABLES',
            'NO_ZERO_IN_DATE',
            'NO_ZERO_DATE',
            'ERROR_FOR_DIVISION_BY_ZERO',
            'NO_ENGINE_SUBSTITUTION',
            ],
    ],
eValdezate
  • 699
  • 1
  • 4
  • 4
15

Add the following on each of your MySQL connections: 

'modes' => [
     'ONLY_FULL_GROUP_BY',
     'STRICT_TRANS_TABLES',
     'NO_ZERO_IN_DATE',
     'NO_ZERO_DATE',
     'ERROR_FOR_DIVISION_BY_ZERO',
     'NO_ENGINE_SUBSTITUTION',
],
Ivanka Todorova
  • 9,964
  • 16
  • 66
  • 103
nasirkhan
  • 9,948
  • 5
  • 27
  • 33
12

The next release of Laravel 5.5 will add support for MySQL 8.0: https://github.com/laravel/framework/pull/24038

UPDATE: Laravel 5.5.41 has been released.

Jonas Staudenmeir
  • 24,815
  • 6
  • 63
  • 109
3

MySQL 8.0 does not support NO_AUTO_CREATE_USER

Documentation: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-11.html

KAGG Design
  • 1,945
  • 8
  • 14
2

I'm using MySQL 8.0.18 on Windows Wampserver and adding the below to database.php for Laravel

'modes'  => [
        'ONLY_FULL_GROUP_BY',
        'STRICT_TRANS_TABLES',
        'NO_ZERO_IN_DATE',
        'NO_ZERO_DATE',
        'ERROR_FOR_DIVISION_BY_ZERO',
        'NO_ENGINE_SUBSTITUTION',
        ],
Kennedy Kairu Kariuki
  • 99
  • 1
  • 4
1

Since MySQL 8.0 at this Moment(as today) does not support NO_AUTO_CREATE_USER you should find and replace in your backup: "NO_AUTO_CREATE_USER," by Space

this way Backup from 5.7 will restore ok in 8.0

Pemtium
  • 11
  • 1
1

How to fix laravel NO_AUTO_CREATE_USER code for database.php

mode => [
'ONLY_FULL_GROUP_BY',
'STRICT_TRANS_TABLES',
'NO_ZERO_IN_DATE',
'NO_ZERO_DATE',
'ERROR_FOR_DIVISION_BY_ZERO',
'NO_ENGINE_SUBSTITUTION',
]
iAmGroot
  • 950
  • 1
  • 6
  • 22
user14585530
  • 11
  • 1