Description
Laravel Version
11.0.7
PHP Version
8.3.3
Database Driver & Version
MySQL 8.0+
Description
Since merging #50044, it's no longer possible to specify the transaction isolation level for a (MySQL) connection. The code now bundles all the "SET" statements, but that causes wrong syntax:
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci', SESSION sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Which causes the following exception:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci', SESSION sql_mode='ONLY_FULL_GROU' at line 1
The transaction isolation level must apparently be set in a separate statement to actually work.
Steps To Reproduce
Configure a (MySQL) database with isolation level, like:
'replica' => [
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'database' => env('DB_DATABASE', 'forge'),
'driver' => 'mysql',
'engine' => 'innodb',
'host' => env('DB_REPLICA_HOST', env('DB_HOST', '127.0.0.1')),
// This currently does not work
'isolation_level' => 'READ UNCOMMITTED',
'password' => env('DB_PASSWORD', ''),
'port' => env('DB_PORT', '3306'),
'prefix' => '',
'prefix_indexes' => true,
'sslmode' => env('DB_SSLMODE', 'require'),
'strict' => true,
'unix_socket' => env('DB_SOCKET', ''),
'username' => env('DB_USERNAME', 'forge'),
],
and run any command using that connection, like User::connection('replica')->all().