Using transaction isolation level breaks the whole database connection

[mwikberg-virta]

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().

[driesvints]

Thanks! seems you sent in a PR so let's see how it goes.