Background
On some outdated Drupal 7 installations, and after a server has been upgraded to MySQL version 8, some websites might stop working displaying the error below:
Error The website encountered an unexpected error. Please try again later. Error messagePDOException: SQLSTATE[42000]: Syntax error or access violation: 1231 Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER' in lock_may_be_available() (line 167 of /home/user/public_html/includes/lock.inc).
Cause
Some older versions of Drupal are not compatible with MySQL version 8.
To solve this issue, add the following to Drupal’s database initialization array:
'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'", )
The extra configuration has to be added $databases default section in:
/home/user/public_html/sites/default/settings.php
The precise location can be seen in the reference below, but basically add it under 'prefix', or where ever the last section ends.
Why this fix works
The sql_mode directive above contains all the values set by Drupal 7 except NO_AUTO_CREATE_USER
Reference
See post #9 here: