Re: [tlug] Charset and other problems with MySQL versions

[Edward Wright <edw@example.com>]

Hi,

On Tue, Jul 19, 2005 at 04:29:19PM +0900, Shawn wrote:
> 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. :-)

You DO still have the old database, right? Why not try mysqldump of the
data from there, and use that file to create the new database. Check the
mysqldump man page for options... there are quite a few....  'tho if you
are lucky you may not need them. I have had to edit the output of
mysqldump occasionally to get it to import.

> 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

I have seen a similar error message, but ignoring it hasn't caused me any
problems :) YMMV
> 
> 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'.

If you are using a precompiled binary from MySQL it should have been compiled
with complex character sets, which should cover your Japanese encodings ok.
If not, then you need to compile that support in.
> 
> 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.

All I can really recommend here is RTFM, and then again and then again until
you start to figure out what they're talking about. Note there are two relevant
sections: 5.9 and 10 (in the online docs). 

Another gotcha that drove me nuts (maybe you've already passed it) was that
password hashing changed with 4.1.x. Relevant section of the docs is
5.6.9. Password Hashing in MySQL 4.1

Good Luck,
Ed