Category Archives: MYSQL

MYSQL import fails with “ERROR 2006 (HY000) at line 44: MySQL server has gone away”

MYSQL

Recently I was attempting to import a large (136mb) SQL backup for a new client.

I first created a database and db user in PHPMYADMIN (most current version).

Then using my Windows workstation, I launched MySQL Workbench 6.1 (my favorite tool for importing/exporting) and tried to do a Server–>Data Import.

Pointed it to the 136mb file and first had a problem:

17:45:03 Restoring Z:\whateverdatabase.sql Running: mysql.exe --defaults-file="c:\users\aaa\appdata\local\temp\tmp8pbfwe.cnf"  --host=192.168.x.x --user=root --port=3306 --default-character-set=utf8 --comments --database=btb-old < "Z:\\whateverdatabase.sql" ERROR 2006 (HY000) at line 44: MySQL server has gone away
Operation failed with exitcode 1
17:45:04 Import of Z:\whateverdatabase.sql has finished with 1 errors

Want an easy solution for this?  It turns out there is a limit in the MySQL database for the size of imports…

Run this SQL statement against the MySQL database:

SET GLOBAL max_allowed_packet=250x1024x1024;

In my case, I picked a size (250mb) (above) that is larger than my SQL import.

Execute that script.

Re-run the import.  Works perfectly!

17:47:42 Restoring Z:\whateverdatabase.sql
Running: mysql.exe --defaults-file="c:\users\aaa\appdata\local\temp\tmpwpesfe.cnf"  --host=192.168.x.x --user=root --port=3306 --default-character-set=utf8 --comments --database=btb-old < "Z:\\whateverdatabase.sql"
17:50:18 Import of Z:\whateverdatabase.sql has finished

 

 

 

One comment

How to migrate WordPress 3.X from one domain to another

IIS Tips & Tricks, MYSQL, WordPress Tips

I recently came across this situation on a client site where they wanted to change their domain from something like www.whatever.com to www.whatever-new-site.com and their site runs on WordPress 3.91. This is also applicable if you develop a site on a testing url like “testing.whateverdomain.com” and you want to change the site to run off “www.whateverdomain.com” when it goes live. After a lot of research and trial & error, here is the easiest approach to handle this.

  1. go to plugins and download and install Velvet Blues Update URLs 3.2.2
  2. go to TOOLS -> Update URLs
  3. enter the OLD DOMAIN and the NEW DOMAIN
  4. check all the checkboxes that apply (I have always checked all 6 checkboxes and never had any issues)
  5. click on UPDATE URLs NOW
  6. go to settings -> general and update the domain info in there.  Save the changes.
  7. At this point you need to adjust your web server settings to begin serving and responding to the site on the new domain name.
  8. You can now access the site from the new domain name and all should be fine!

 

Leave a comment

Quick and easy method to upgrade MySQL Community Edition – Windows Server

MYSQL, Windows Server

I recently crossed my fingers and went through the upgrade process to upgrade MySQL from 5.6.14 to 5.6.17 on my Windows server.  (also works for 5.5.x to current)

 

NOTE – UPDATED 5/27/2016:  If you are using the MYSQL Installer – Community (if that is in your Start -> program list, please use that to upgrade you instance.  That is much simpler than my process below.  The process below should now be considered a LEGACY process.

 

Don’t forget to run

mysql_upgrade -u root -p

to allow MySQL to upgrade all the tables to the new standard.

 

Through trial and error, I have found the easiest process to do this.

  1. stop the existing service via services
  2. command prompt (as administrator)
  3. change to the MySQL directory, BIN, and then type in mysqld –remove  (to remove the service)
    1. If the above command does not work, try using SC DELETE MYSQL    (put the exact service name in place of MYSQL).  That should do the trick.
    2. go into the service manage and refresh and ensure the service is removed.
  4. go to the control panel and uninstall everything to do with MySQL (presuming this is on a dedicated MySQL box)
  5. make sure your data is moved to a directory on the root like “MYSQLDATA”
  6. delete every folder under programdata and program files pertaining to MySQL
  7. reboot the machine
  8. log into machine
  9. make sure .NET Framework 4.0 is installed
  10. download and run the MySQL Community 5.6.17 (or later) x32 MSI full installer
  11. do a fresh install
  12. Server type:  Server  (for production deployments)
  13. point things to the correct directories (e.g. your existing data directory, etc.)
  14. use the same root password as before (on the old install)
  15. add any extra admin users
  16. complete the install
  17. make sure the service is complete
  18. That’s it!

hope that helps someone else out there.  I took the couple of hours to sort this all out for you!

2 comments ,

How to migrate MYSQL to a new Windows server

MYSQL, Windows Server

Here is the easiest process that I know of for migrating (and upgrading) from one version of MYSQL running on Windows server, to a new Windows server.

  1. Setup the new Windows server, give it a fixed IP address (we’ll change that later to be at the same IP as the old server).
  2. Run all Windows updates
  3. Install IIS 7.5 (in our example, we were on W2K8 R2 Standard) and take all defaults.
  4. Go to Google and search for MICROSOFT PLATFORM INSTALLER, go to that Microsoft site, and run the installer.  The current version as of this post was 4.0RC
  5. in the MPI, search for PHP and install PHP.  Allow it to take any extras it chooses, hit next and install those items.
  6. Now that PHP is installed, launch the IIS 7.5 manager, stop the default site and setup a new website that we’ll use to park PHPMYADMIN on.
  7. Go download the latest version of PHPMYADMIN.
  8. extract it to your website directory into the /phpmyadmin/ directory.
  9. you should now be able to access the PHPMYADMIN site via http://localhost/phpmyadmin/
  10. Download and install the latest MSI installer download (of the x64 version if you have an x64 server)
  11. Install MYSQL
  12. Run the configuration wizard and complete the standard setup.  In our case we used a dedicated MYSQL server, set a new password for the admin account and pretty much took all defaults.
  13. At this pount we just need to migrate the data over from the old server to the new server.
  14. Make sure you have MYSQL Workbench installed to do this (under windows).  Current version is 5.2.30 CE.
  15. Under “Server Administration” you need to setup one connection for the old (current server) and one for the new one.
  16. Connect to the old server.
  17. Click Data Export.
  18. Export out ALL databases to one SQL file.  This may take some time depending on the speed of your internet connection.
  19. when this is complete, go check the file to make sure it is (relatively) large depending on the number of databases that you exported out.  MAKE SURE you export out the MYSQL database as well as all databases.
  20. Connect to the new server.
  21. Do a DATA IMPORT/RESTORE and load the SQL file you just exported
  22. Start the import.  Again, this may take some time to complete.
  23. When complete, the new machine is now a clone of the old machine.
  24. Shut down the old server.
  25. Change the IP on the new server to the IP of the old server.  Reboot (just to be sure).
  26. Once the new server is rebooted and it is at the “old IP” any of your sites that connect to MYSQL should now be working once again just as if they were connecting to the old server.

I hope this helps anyone who is in need of migrating/upgrading their Windows-based MYSQL install.

 

One comment , , ,