Connecting Lumen app to Azure Database for MySQL Server
Core components of GemeindeOnline hub are Lumen framework and MySQL database, both versions 8.x as of today. We operate them in Azure cloud, so when I was creating Azure Database for MySQL server
resource in Azure Portal I ensured that v8.0 was deployed:
However, I was surprised to see MySQL 5.6.47.0 as a version number in Sequel Ace — a GUI client that I used locally to browse Azure MySQL Database:
To find the truth, I connected to Azure MySQL Database from the command line:
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 65256 Server version: 5.6.47.0 Source distribution mysql> SELECT VERSION(); +-----------+ | VERSION() | +-----------+ | 8.0.15 | +-----------+ 1 row in set (0.05 sec) mysql>
Two observations from the console output you see above:
- MySQL server version exposed to the client when connection is established can differ from actual MySQL version obtained with
SELECT VERSION()
command, with latter to be a real version number; - Azure uses some sort of a proxy to access MySQL servers that can easily confuse a client connecting to server [1, 2].
So, what could go wrong?
After I successfully created a database, I attempted to run a migration, but it failed:
zahhar@zahhar> php artisan migrate In Connection.php line 678: SQLSTATE[42000] [1227] Access denied; you need (at least one of) the SUPERor SYSTEM_VARIABLES_ADMIN privilege(s) for this operation (SQL: SHOW FULL TABLES WHERE table_type = 'BASE TABLE') In Connector.php line 70: SQLSTATE[42000] [1227] Access denied; you need (at least one of) the SUPERor SYSTEM_VARIABLES_ADMIN privilege(s) for this operation In MySqlConnector.php line 170: SQLSTATE[42000]: Syntax error or access violation: 1231 Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'
Troubleshooting this stack trace I figured out, that Lumen (and Laravel as its bigger brother) attempts to connect to the database in 'strict' mode [3]. It relies on PHP's PDO::getAttribute(PDO::ATTR_SERVER_VERSION)
to detect underlying mySQL version, and sets appropriate 'strict' mode flags that are different for mySQL versions 5 and 8.
One of the flags that Laravel sets when connecting in 'strict' mode is NO_AUTO_CREATE_USER
SQL mode that was introduced in mySQL 5.7.7 [4], but has been removed in MySQL 8.0 [5].
So when Laravel gets confused by ATTR_SERVER_VERSION
that makes framework think it got connected to mySQL v5 instead of v8, it attempts to set this unsupported flag [6]. MySQL server reacts on it with Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'
error message, but it is not very obvious from logs, especially when you read only the top of it:
Making it work
If all you want is just fully functional setup both in your local environment and in Azure, you should change your mySQL connection settings as follows:
- either set
'strict' => false
— that tell Lumen not to set any mode flags at all; - or add
'version' => env('DB_VERSION')
and set in your .env file real mySQL version you have, that overrides autodetected version and sets appropriate headers for 'strict' mode.
As a result your config/database.php
[7] may look like this:
'mysql' => [ 'driver' => 'mysql', 'url' => env('DATABASE_URL'), 'host' => env('DB_HOST', '127.0.0.1'), 'port' => env('DB_PORT', '3306'), 'database' => env('DB_DATABASE'), 'username' => env('DB_USERNAME', 'root'), 'password' => env('DB_PASSWORD', ''), 'unix_socket' => env('DB_SOCKET', ''), 'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci', 'prefix' => '', 'prefix_indexes' => true, 'strict' => true, //change this to 'false' 'engine' => null, 'version' => env('DB_VERSION'), //or add DB_VERSION=8.0.15 to .env 'timezone' => env('DB_TIMEZONE', date('P')), 'sslmode' => env('DB_SSLMODE', 'prefer'), 'options' => extension_loaded('pdo_mysql') ? array_filter([PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),]) : [], ]
Now you have configuration that allows you to connect both to your local environment, and to Azure Database for mySQL server
.
Happy hacking!
Notes
- https://github.com/laravel/framework/issues/32681
- https://github.com/laravel/framework/pull/32708
- https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sql-mode-strict
- https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
- https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-11.html
- https://github.com/laravel/framework/blob/28e0f61bba1cbc727383d183b7116eb723de7660/src/Illuminate/Database/Connectors/MySqlConnector.php
- https://github.com/laravel/laravel/blob/8.x/config/database.php