Installing Backdrop 1.12.6 on MySQL 8 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'

tl;dr

As of mysql 8.0.3, "system" is a reserved word.  It can't be used for the name of the table "system" and NO_AUTO_CREATE_USER is no longer a valid sql-code.

I'd think this was a critical issue.

-=-=-=-=-=-

I'm trying to install backdrop 1.12.6 using mysql version 8.0.15 on Windows 7 and I get this error:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1231 Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER' in backdrop_get_installed_schema_version() (line 170 of C:\Dev\apacheD\Apache24\htdocs\backdrop\core\includes\install.inc).

It seems that 'NO_AUTO_CREATE_USER' has been removed from sql-code in MySql 8.

I found a reference to 'NO_AUTO_CREATE_USER' in core/includes/database/mysql/database.inc, starting at line 98:

    $connection_options['init_commands'] += array(
      'sql_mode' => "SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER'",
    );

I tried, naively, to just remove the ",NO_AUTO_CREATE_USER" at the end of the string but that seemed to mess up the parsing of the array into sql (I'm mostly a Java developer, new to PHP) and got this error:

PDOException: 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 'system WHERE type = 'module'' at line 1: SELECT name, schema_version FROM {system} WHERE type = :type; Array ( [:type] => module ) in backdrop_get_installed_schema_version() (line 170 of C:\Dev\apacheD\Apache24\htdocs\backdrop\core\includes\install.inc).

You can see that instead of adding the array values to :type placeholder it actually sends "Array ( [:type] => module )".

I'm thinking that this will block anyone trying to install backdrop with the most recent versions of MySql.  If someone could suggest a way to remove the NO_AUTO_CREATE_USER option without breaking the array parsing it would be very helpful.

-=-=-=-=-=-=-=- Update -=-=-=-=-=-=-

After removing the ",NO_AUTO_CREATE_USER" from the string above and forcing a re-install as opposed to an update, it created the database tables and the config directories and files but ultimately threw another error, seemingly related to mysql 8 syntax incompatibility:

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 'system ( `filename` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'The path of the pr' at line 1

That issue is at a deeper level than my newbie skills can resolve.  I hope someone will try an install against mysql version 8.0.15 and determine what syntax changes are breaking the install.php script.

-=-=-=-=-=-=-=- Update -=-=-=-=-=-=-

Ok , it should have been obvious but until I found the error message pieces "system" and "filename" in core/modules/system/system.install and checked mysql 8.0 reserved words I wasn't sure.  That is in fact the problem, as of mysql 8.0.3 "system" is a reserved word.

https://dev.mysql.com/doc/refman/8.0/en/keywords.html

...

SYSTEM (R); added in 8.0.3 (reserved)