MySQL and the invisible problem…
The other day I bumped into a very strange issue with MySQL on Windows. Just a quick warning, what follows is pretty geeky…
mysqldump as I normally would on a Linux box:
C:\mysqldump -u username -p database_name > output.sql
I thought it was all ok. So I went to restore it on another server (another Windows server).
C:\mysql -u root -p < output.sql
It just sat there for hours… doing nothing. I checked in after about 24 hours and found that it had returned with something like:
ERROR 2006 (HY000) at line 1: MySQL server has gone away
Now, there’s a few results about this on Google etc and they mention increasing the
max_allowed_packet setting in
my.cnf. There’s some information that this setting is actually smaller by default than the setting used for
However, what I found was something far more subtle…
When using Powershell (I also used the standard CMD) I hadn’t realised that it uses different encoding for the redirection operator (
>). It in fact was trying to use
UTF-16 LE not
UTF-8 like mysql was expecting.
The first clue was the size of the backup which, compared to some slightly older backups, was about twice the size. When I opened the backup file and found the encoding it was indeed set to
After switching and re-saving the backup it worked and the restore took under 10 minutes.
So, what’s the best way of handling this?
Use the Powershell’s
C:\mysqldump -u user -p database | out-file -Encoding utf8 C:\output.sql
Then on restore use:
C:\cat C:\output.sql | mysql -u user -p database
You can also set 2 options in your
my.cnf which might help things along:
[mysqld] max_allowed_packet=64M innodb_flush_log_at_trx_commit=0
Don’t forget to switch back
innodb_flush_log_at_trx_commit when you’re done (normally set to 1).