Troubleshooting MySQL databases

If your MySQL database is not correctly configured for Ghost then you may run into some issues.

The solutions given are for self-hosted Ghost developers who are using the supported install method with ghost-cli. If you’re having problems with an unsupported custom install, check out the forum.

Error ECONNREFUSED

If you’re seeing an ECONNREFUSED error which refers to port 3306, Ghost wasn’t able to connect to your MySQL server and you need to check if your server is running via the command line.

To fix this issue:

1. Ensure the server is running with sudo service mysql start
2. Test that the server is now running by typing mysql in the command line and checking the response
3. If this error occurred after using ghost install, once resolved re-run the setup phase using ghost setup

Error ER_BAD_FIELD_ERROR

You may see this error if your database has ANSI_QUOTES mode enabled, which is not supported. To fix this issue, disable ANSI_QUOTES mode in your MySQL config. Combination modes such as ANSI which include ANSI_QUOTES should also be disabled.

Error ER_WRONG_FIELD_WITH_GROUP

To fix this issue:

1. Open your my.cnf using sudo find / -name my.cnf
2. Remove only_full_group_by from mysql_mode

Error ER_TOO_BIG_ROWSIZE

The row_format of a table determines how it is stored, and this introduces limits on the size of the data. Older versions of MySQL and MariaDB used a different default row_format of COMPACT or REDUNDANT, which have stricter row size limits. The default on the latest version is DYNAMIC, which is what we support.

In MariaDB 10.1 and before, and in MySQL 5.6 and before, the COMPACT row format was the default row format. - https://mariadb.com/kb/en/troubleshooting-row-size-too-large-errors-with-innodb/

To confirm if your database is affected:

1. Run show table status; whilst attached to the database in MySQL
2. If Row_format says COMPACT or REDUNDANT, this table needs updating to DYNAMIC.

To fix this issue MySQL should be updated to the latest version and all tables used by Ghost should be converted to the DYNAMIC format:

1. Ensure you have a recent backup of your database
2. Update MySQL to the latest supported version (so the default is DYNAMIC moving forwards)
3. For each table, run ALTER TABLE <table> ROW_FORMAT=DYNAMIC;
4. show table status; should report all tables are DYNAMIC and the problem should be solved

Error ER_CANT_CREATE_TABLE with “Foreign key constraint is incorrectly formed”

MySQL is unable to create tables with foreign keys referencing tables using a different collation. This usually occurs after upgrading to MySQL 8 from an earlier version:

The default collation for utf8mb4 differs between MySQL 5.7 and 8.0 (utf8mb4_general_ci for 5.7, utf8mb4_0900_ai_ci for 8.0). - https://dev.mysql.com/doc/refman/8.0/en/charset-connection.html

To fix this issue, ensure that all tables use the same collation as the default connection collation. You can find this by checking the value for show variables like '%collation_connection%';.