Migrating Mantis from mysql 4.0 to 5.0

Goal: migrate the Mysql 4.0 mantis database to Mysql 5.0

Backup the database

The wrong way:

mysqldump --opt --password=xxxx mantisbt > mantisbt.sql

This is a bad way because Mysql 4.0 stores the database in latin-1 encoding and if you later convert the .sql file to utf-8 the blob’s will also get converted (destroyed) and they will not be correctly imported later.

The good way:

mysqldump --opt --password=xxxx --allow-keywords --flush-logs --hex-blob --max_allowed_packet=16M --quote-names mantisbt > mantisbt.sql

Most important is the –hex-blog switch

Also save you database files so you can restore everything later:

quickpkg dev-db/mysql #gentoo specific<br></br>cp -r /var/lib/mysql /var/lib/mysql-4.0<br></br>cp -r /etc/mysql/my.cnf /etc/mysql/my.cnf.4.0<br></br>

Upgrade the database

rm -rf /var/lib/mysql /var/log/mysql<br></br>emerge -upv dev-db/mysql<br></br>emerge -uv dev-db/mysql<br></br>emerge --config dev-db/mysql<br></br>

Import the mantis database

iconv -f latin1 -t UTF-8 mantisbt.sql > mantisbt-utf8.sql<br></br>mysql -u root --password=xxxx mantisbt < mantisbt-utf8.sql<br></br>

If you try to import the dump without first converting the encoding you will loose all strings which contains non-ascii chars such as é, è, à, etc. The strings will be trimmed at the first special char.

Mantis configuration

Now the database was ok but the special characters where not showing on. The first try was to use the info provided on the forum and I modified the database_api.php file.

50c50,52<br></br><               } <br></br>---<br></br>>               } else {<br></br>>                       $g_db->Execute("SET NAMES UTF8");<br></br>>               }<br></br>73c75,79<br></br><               }<br></br>---<br></br>>               }else{<br></br>>                       //Added by Stonez to add UTF8 encoding to MySQL 2005-12-26<br></br>>                       $g_db->Execute("SET NAMES UTF8");<br></br>>                       } <br></br>> <br></br>

This however did not changed anything as this is the default in mysql 5 and the problem was not here.
Instead the problem resided in the way the mantis page encoding. It sufficed to change the files lang/strings_english.txt and the others by replacing

$s_charset = "windows-1252";

with:

$s_charset = "UTF-8";

Comments:

Len - Jun 0, 2008

It was not about setting a ENV VAR but rather modifying the lang/strings_english.txt so there is not a problem with spaces. A far as the –hex-blog option I don’t know about 4.3 in my case it was 4.0.* and you can find the option here: http://dev.mysql.com/doc/refman/4.1/en/mysqldump.html

quote:
--hex-blob

Dump binary columns using hexadecimal notation (for example, ‘abc’ becomes 0x616263). The affected data types are BINARY, VARBINARY, and BLOB in MySQL 4.1 and up, and CHAR BINARY, VARCHAR BINARY, and BLOB in MySQL 4.0. This option was added in MySQL 4.0.23 and 4.1.8.
end quote

---

Rob Vaughn - Jun 0, 2008

First, setting the s_charset ENV VAR is a good idea but you can’t use spaces around the equal sign. That said, MySQL 4.3.x doesn’t support –hex-blob for mysqldump so this is only useful, maybe, for MySQL 4.4.x but I looked into that and it doesn’t seem to work there either. So this might have worked on someone’s system somewhere but in general this is not how to migrate from MySQL 4 to 5.

---