Improve Database (MySQL) Imports
Writing this as a web-hosting provider/system administrator -- one of the influx of support tickets lately comes from people attempting to migrate very large databases from their current providers to us.
The obvious workflow for them is to first create the database, assign a user and then jump to phpMyAdmin to import the SQL dump.
This has a few issues: for obvious reasons, we restrict the POST size to sane values -- we are currently at 256MiB and we have had requests for 1GiB.
Now, phpMyAdmin does allow you to import a compressed sql dump, but once again, there are some glaring UI/UX issues with the product itself that will result into support ticket created by our end-users:
- Non-Intuitive requirements - as per the upload form:
A compressed file's name must end in .[format].[compression]. Example: .sql.zip
Absolutely no client to date has noticed that restriction, because the UI is very poor in phpMyAdmin in that regard, and they keep trying to import file.zip instead of file.sql.zip.
This in turn creates another issue: cPanel's PHP-FPM thread will just "hang" when someone attempts to do this. To the user, it just stopped working and they can no longer access phpMyAdmin, sometimes for hours at a time.
- It's easy to screw it up. Even when uploading a properly named file (file.sql.zip), this zip archive only has to contain the single .sql dump file. If you zip a file from macOS, you will get the dreaded __MACOSX directory in the ZIP. This in turn, breaks phpMyAdmin... without any feedback to the user (presuming it's the same issue as above - fpm thread hangs).
- phpMyAdmin does not offer sufficient info on import failures. We have seen people trying to import dumps that were created with a specific database name (ie: it tried to create database/use non-existing-database), we have seen people trying to import dumps with stored procedures and/or views that could not be imported by the limited rights users etc.
- THERE IS NO FEEDBACK TO THE USER. Literally none. If you drag&drop the to-be-imported file in the phpMyAdmin page, you will get a feedback for the upload. Once the upload gets to 100%... there is nothing.
I can see it server-side that MySQL is processing the import (SHOW PROCESSLIST), but the user does not see absolutely ANYTHING in the UI.
I have imported a 3GiB database (gzipped to about 150MiB) consisting of a few million rows, and I just sat there for 10minutes+ looking at a screen that kept saying 100% progress on the upload. No spinner, no indication there was something going on. I wasn't even sure if I can navigate away from the page...
How to improve? A dedicated MySQL Import Wizard!
- No 3rd party tools dependency (hopping to phpMyAdmin is just a terrible experience)
- Notify the user of POST restrictions (max upload size), and suggest compressing it if the file is over the allowed size (zip, gzip, bzip, xz etc.)
- When the archive uploaded contains multiple files, allow the user to pick a file to import (to cover __MACOSX cases, and/or other common mistakes)
- Allow import of files located in in the users' $HOME. This is a big one, because sometimes the databases are just massive and it would be just so much easier if they can upload the file first and then import it.
- Provide better feedback on import failures. Notify if the dump is attempting to switch database (USE <database>) or trying to do some other actions that would not be permitted by non-privileged users.