[tlug] Charset and other problems with MySQL versions

[Shawn <bofh@example.com>]

Hello,

I was recently asked to move some MySQL data from one machine (linux, MySQL
ver. 2.32) to another (FreeBSD, MySQL ver. 4.1.12).

So like a good sysadmin I just copied the data directories over without
checking for any effects of differences between the MySQL versions.
Apparentely there are some. :-)

At his URL
( http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html ) I found:
"The table definition format used in .frm files has changed slightly in 4.1.
MySQL 4.0 versions from 4.0.11 on can read the new .frm format directly, but
older versions cannot. If you need to move tables from 4.1 to a version
earlier than 4.0.11, you should use mysqldump."

I think this is the root of at least one of the problems so I tried to dump
the data out of the 4.1.12 system with:

# mysqldump -l --databases PADB -uroot -p > padb.sql

This gives me:

mysqldump: Got error: 22: Character set '#13' is not a compiled character set
and is not specified in the '/usr/local/share/mysql/charsets/Index.xml' file
when using LOCK TABLES

Googling around for information on that error led to 
http://dev.mysql.com/doc/mysql/en/mysqldump.html, which talks about 
'--default-character-set=<charset>'. I tried specifying the charset, but got
'mysqldump: Character set 'sjis' is not a compiled character set and is not
specified in the '/usr/local/share/mysql/charsets/Index.xml' file'.

There is a section in /usr/local/share/mysql/charsets/Index.xml like this:

<charset name="sjis">
  <family>Japanese</family>
  <description>Shift-JIS Japanese</description>
  <alias>s-jis</alias>
  <alias>shift-jis</alias>
  <alias>x-sjis</alias>
  <collation name="sjis_japanese_ci"    id="13" order="Japanese">
    <flag>primary</flag>
    <flag>compiled</flag>
  </collation>
  <collation name="sjis_bin"    id="88" order="Binary">
    <flag>binary</flag>
    <flag>compiled</flag>
  </collation>
</charset>

So I'm almost stumped... I've looked at
http://dev.mysql.com/doc/mysql/en/problems-with-character-sets.html, but am
still unsure if I need to recompile MySQL 4.1.12 with support for the data charset
or if I can specify it in a config file or on the command line.

Anyone out there with experience in this?
Pointers appreciated.

Thanks,

Shawn