Question

How do you go about migrating a FogBugz database from one MySQL instance to another?

(see this post for moving the FogBugz site)

Answer 1

NB: Before you go through any of this, you should make sure there are no FogBugz-unfriendly settings in your MySQL instance and that there's adequate disk space (3x the dump file size) on the MySQL machine.

This guide is intended for people who want to move a MySQL FogBugz database over to a new MySQL installation.

If you are upgrading from a previous version of FogBugz at the same time as moving your database, please install the upgraded FogBugz code against the old database if this is applicable or feasible, log in once so FogBugz can do the upgrade, and then proceed to moving your database.

1. Make sure that you're running the latest version of FogBugz before starting. You must be running at least FogBugz version 3.0.15.
2. On the old MySQL server, run the following at the command-line:
   
   mysqldump fogbugz > createfb.sql
   
   (This assumes your database name is "fogbugz")
   
3. Put the new file creatfb.sql somewhere accessible from the new server
4. Install FogBugz on the new server and let it create a new MySQL database. Make sure it's working (even add a test case). If you're using the same FogBugz server and only moving the database, just get the new MySQL instance up and running.  Remember the FogBugz user and password that you set for the database.
5. Once it's working, log into mysql on the new server as root.  The instructions assume your FogBugz database is called 'fogbugz'.  If not, replace 'fogbugz' here with the name of your database.
   
   mysql> drop database fogbugz;
   mysql> create database fogbugz;
   mysql> use fogbugz;
   mysql> source /path/to/createfb.sql
   mysql> grant all on fogbugz.* to fogbugzuser identified by 'fogbugzpassword';
   mysql> grant all on fogbugz.* to fogbugzuser@'localhost' identified by 'fogbugzpassword';
   
   Note the ' around the 'localhost' part.  You can replace localhost by the server name if your FogBugz server is on a different machine. All values in italics should be replaced with valid values for your environment.
   
6. Then go back into FogBugz through your web browser. If you moved FogBugz, you will have to enter your order info to install licenses again.

If you changed platforms (switched between Windows and Unix/Linux), you will need to clear the passwords for your users and have them set them again. The hashing algorithm is different for the two platforms. You can do this with the following sql query:

UPDATE Person SET sPassword = ''

A platform may also require that you enable the "lowercase_table_names" setting in your my.ini or my.cnf file.

Answer 2

You may also need to add --max_allowed_packet option to mysqldump, it seems to need it, like so:

mysqldump --max_allowed_packet=50M fogbugz > createfb.sql

I kept getting this error before I used the option above:

mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table Attachment at row: 756

Answer 3

Note: you might need to increase the value of your MySQL max_allowed_packets variable before restoring the database from the createfb.sql file.

Find your my.ini file (the configuration file for MySQL). It's normally here:

C:\Program Files\MySQL\MySQL Server 5.1

Search your MySQL my.ini file's [mysqld] seciton, for this:

max_allowed_packet

If you find it, make sure it says:

max_allowed_packet=50M

If you don't find that line, add it.

Stop and start the MySQL server. From the MySQL command line, verify your change by typing:

show variables like '%max_allowed%';