Overview
This article provides an overview of using reserved keywords in MySQL 8, the version all DreamHost MySQL servers currently run.
Using reserved words in MySQL 8
MySQL 8 includes a list of new "reserved words" (such as 'GROUPS', 'EMPTY', 'RANK', and 'SYSTEM'). These words can be used as table and column names but require special treatment as identifiers and must be escaped in database queries. See the following link for more information on how to use reserved words in MySQL 8:
Errors importing a database with mysqlimport
DreamHost has implemented some newer features in MySQL 8, which may cause an error to occur when using the command line program mysqlimport:
mysqlimport: Error: 1227 Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation
The mysqlimport command causing the error may look something like this:
[server]$ /usr/bin/mysqlimport --host hostname --user user --password password --local --fields-terminated-by='|' database /path/to/file
It may also use an options file (recommended) instead, which would look like this:
[server]$ /usr/bin/mysqlimport --defaults-extra-file=/path/to/file --local --fields-terminated-by='|' database /path/to/file
Fixing the error
If this error occurs, using the mysql client instead of mysqlimport will allow this feature to work. Here is an example of the above commands.
Keep in mind the table name needs to be specified, where previously mysqlimport determined that from the file name.
[server]$ echo "LOAD DATA LOCAL INFILE '/PATH/TO/FILE' into table tablename fields-terminated-by='|' mysql --host hostname --user username --password password database
[server]$ echo "LOAD DATA LOCAL INFILE '/PATH/TO/FILE' into table tablename fields-terminated-by='|' mysql --defaults-extra-file=/path/to/file
DreamHost's custom modifications for WordPress users
DreamHost has made modifications to its MySQL 8 configuration to provide broader support for most sites and software. The following are default settings in MySQL 8 that have been modified in DreamHost's configuration:
| Setting | Default | DreamHost |
|---|---|---|
| Encryption plugin | caching_sha2_password | mysql_native_password |
| Character set | utf8mb4 | utf8 |