Installer fails with HTTP 500 on large MySQL 8 restore due to FK order, strict SQL mode, and localhost socket handling

[mxmsmnv]

Summary

While restoring a ProcessWire site package through installer.php, the browser installer spun for several minutes and then returned HTTP 500 during the database import step.

The package contained a large SQL dump:

• SQL zip: about 26 MB
• extracted SQL: about 243 MB
• dump footer reported numTables: 157, numInserts: 949617
• local MySQL: 8.0.44
• local PHP CLI: 8.5.7

I was able to complete the restore manually from the command line, but only after changing the import conditions.

What failed

The browser installer timed out/returned 500 during SQL import. When importing the same dump manually, MySQL exposed the underlying restore issues:

1. Foreign key creation order failed:

ERROR 1824 (HY000) at line 13419: Failed to open the referenced table 'embedr_types'

This was fixed by wrapping the restore in:

SET FOREIGN_KEY_CHECKS=0;
-- dump
SET FOREIGN_KEY_CHECKS=1;

2. MySQL strict mode failed on an enum value:

ERROR 1265 (01000) at line 197154: Data truncated for column 'name' at row 1

The failing row was an empty string for an enum field, e.g. field_social_media.name = ''. This was fixed for restore by running the import with non-strict session mode:

SET SESSION sql_mode='';

3. The restored site/config.php used:

$config->dbHost = 'localhost';

On this local stack, PHP/PDO interpreted localhost as a Unix socket and failed because the socket path did not match MAMP's MySQL socket. Changing it to TCP fixed the site after import:

$config->dbHost = '127.0.0.1';

Expected behavior

The installer should either:

• complete the restore successfully for large MySQL 8 dumps, or
• surface the actual MySQL/PDO error instead of ending as a generic HTTP 500.

Suggested improvements

• During restore, consider temporarily disabling foreign key checks for the import session.
• Consider making strict SQL mode handling configurable, or detect/report enum/data truncation errors clearly.
• Improve long-running restore error reporting so browser users see the MySQL error instead of a generic 500.
• Consider warning users that localhost may use a socket and 127.0.0.1 may be required on local MAMP-style stacks.

Manual workaround that succeeded

(
  printf 'SET SESSION sql_mode="";\nSET FOREIGN_KEY_CHECKS=0;\n'
  cat duplicator-temp/example.sql
  printf '\nSET FOREIGN_KEY_CHECKS=1;\n'
) | mysql --default-character-set=utf8mb4 --max_allowed_packet=256M database_name

After that, the restored ProcessWire site returned HTTP 200 locally.