Resolving the SQL Mode Conflict Between MySQL 8 and Laravel Migrations
When upgrading your development environment, specifically moving from older MySQL versions to 8.0, you often encounter compatibility issues that weren't present in previous iterations. A common scenario involves cloning an existing project from GitHub, installing dependencies like MySQL and Sequel Pro, and then attempting to run database migrations. If the target server is running MySQL 8.0.11 while your application logic assumes an older behavior, you will hit a specific syntax error during the migration process.
The error message typically looks like this:
SQLSTATE[42000]: Syntax error or access violation: 1231 Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'
This occurs because MySQL 8.0 deprecated several legacy features and changed default behaviors that Laravel, particularly versions like 5.6.12, still expect by default. Specifically, the NO_AUTO_CREATE_USER flag was removed in MySQL 8.0 as part of security hardening. It is no longer a valid option for the sql_mode variable. When your application attempts to execute a query that implicitly sets this modeâoften when creating tables or handling migrationsâit throws an exception because the database server rejects the invalid value.
The stack trace points to a query against information_schema.tables, which suggests that Laravel is checking table existence or structure before proceeding with the migration logic. The root cause isn't the query itself, but the underlying session configuration where MySQL tries to enforce a mode that no longer exists.
To resolve this, you need to adjust the SQL mode on your MySQL server to exclude the deprecated flags while keeping other security features active. You can achieve this by updating the sql_mode variable in your MySQL configuration file (my.cnf or my.ini) or by altering it directly within your session.
If you are using a local development setup, the quickest fix is to run the following command in your terminal before starting Laravel:
mysql -e "SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'NO_AUTO_CREATE_USER','')); SET SESSION sql_mode=@@session.sql_mode;"
However, for a permanent solution, you should edit your MySQL configuration file. Locate the sql_mode directive and ensure it does not include NO_AUTO_CREATE_USER. In MySQL 8.0+, the recommended default mode usually includes STRICT_TRANS_TABLES, ONLY_FULL_GROUP_BY, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION, and potentially NO_BACKSLASH_ESCAPES.
Alternatively, you can configure this directly within your Laravel application by modifying the connection settings. While changing global MySQL settings is often preferred for consistency across tools like Sequel Pro or phpMyAdmin, adding a migration to enforce the correct mode upon first boot ensures that even if the server default changes, your app remains compatible. You can create a custom migration file at database/migrations/xxxx_xx_xx_set_sql_mode.php with the following content:
use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;
class SetSqlMode extends Migration
{
public function up()
{
$currentMode = DB::selectOne("SELECT @@GLOBAL.sql_mode");
if ($currentMode && strpos($currentMode, 'NO_AUTO_CREATE_USER') !== false) {
$newMode = str_replace('NO_AUTO_CREATE_USER', '', $currentMode);
DB::statement("SET SESSION sql_mode = '$newMode'");
}
}
public function down()
{
// Revert to original if needed, though usually not required for this fix
}
}
Note that depending on your Laravel version and migration runner capabilities, you might need to use DB::statement directly in a service provider or artisan command instead of a standard migration if the schema validation interferes with the execution order.
Another approach is to update your config/database.php file. While this primarily affects connection parameters, understanding that MySQL 8.0 does not support legacy flags helps prevent future configuration errors. If you are deploying to production, ensure your CI/CD pipeline or server setup explicitly sets the sql_mode before application initialization to avoid runtime exceptions during deployment.
Finally, consider upgrading your Laravel version if possible. Laravel 5.6 is quite old and may have hardcoded assumptions about MySQL features that have since been deprecated. Moving to a newer LTS version ensures better compatibility with modern MySQL defaults and reduces the need for manual sql_mode patching.