Question

Is there anything special I should know about MySQL for FogBugz?

Answer 1

There are a few things that can trip people up with MySQL. We try to allow for them, but sometimes default settings override. Running the following commands and looking for the desired result should flush out any config issues:

SHOW VARIABLES LIKE '%olla%'; -- want latin1_swedish_ci for all.
SHOW VARIABLES LIKE '%engine%'; -- want a storage engine of MyISAM
SHOW VARIABLES LIKE 'max_allowed_packet'; -- should be 50 megs, or 52428800

If your installation is experiencing problems with schema errors, a solution may be to make sure you're not running with STRICT mode turned on. That mode causes some schema warnings to be interpreted as errors. You may also need this if you're upgrading from FogBugz 6 or earlier. You can check for it thusly:

SELECT @@global.sql_mode;  -- want blank, or at least no STRICT

If you're experiencing character set problems or if you installed MySQL with a default collation that isn't latin1_swedish_ci, you may need to force FogBugz to talk to the server using latin1. To do so, add 'charset=latin1' to the end of your connection string on the FogBugz server.

The lettercase of the table names must also be set correctly for your OS. These are almost always correct with the defaults, which differ across platforms, but if you are moving a MySQL-backed FogBugz installation between platforms (Windows, Unix and Mac), make sure that table names are case insensitive:

SHOW VARIABLES LIKE 'lower_case_table_names'; -- the value should be 1

NOTE! The 'lower_case_table_names' setting will affect all your databases on the MySQL Server. Do not use this setting unless you are ONLY hosting fogbugz on the server or you know what you are doing.

Additionally, when importing and exporting data on a MySQL server, you should have disk space in excess of three times your database dump file size. The logs generated by an import are sometimes twice the size of the dump file itself.

See also this post for a sample my.cnf / my.ini file

Answer 2

Another gotcha:

As of FogBugz 7.1, we require MySQL 5.0.45 or higher due to compatibility issues.

Answer 3

If performance is very slow, you can also check to make sure that your MySQL instance isn't running in debug mode. In unix you can see if the mysqld process was started with the option --debug. In windows, the executable should not have "debug" in the name, e.g. mysql-debug.exe

Answer 4

More on max_allowed_packet:

Note that there is a client and server setting. When sourcing a new database make sure both are set high enough.

• The client setting can be set when invoking the MySQL command prompt by using the option --max_allowed_packet=50M.

• The server setting can be set in the MySQL config file (my.ini on windows and my.cnf in *nix) or, alternatively, set in the MySQL command prompt.

If you edit the config file you'll need to restart MySQL to apply this change.

source: http://stackoverflow.com/questions/93128/mysql-got-a-packet-bigger-than-max-allowed-packet-bytes-

Answer 5

By adding charset=latin1 at the end of connectionstring in application.data file, what variable in mysql reflect that change? is it collation_database ?

Answer 6

is this still up to date? these are my settings:

SHOW VARIABLES LIKE '%olla%';                               
collation_connection    utf8_general_ci 
collation_database      utf8_general_ci 
collation_server        utf8_general_ci 

SHOW VARIABLES LIKE '%engine%';
default_storage_engine     InnoDB 
engine_condition_pushdown  ON     
storage_engine             InnoDB 

SHOW VARIABLES LIKE 'max_allowed_packet';
max_allowed_packet 1048576 

any problem with the settings? I am worried that the change may affect existing databases

thanks henro