I think this boils down to "Am I doomed to upgrading mysql hoping the problem goes away?". I've seen a few of the same issues around the internet but with no guidance on a fix other than "Did you turn it off and on again?"
I've install Perl's DBI::mysql, but most of the tests fail because there's a character set problem:
t/10connect.t ........................ Character set 'latin1' is not a compiled character set and is not specified in the '/usr/local/share/mysql/charsets/Index.xml' file
I wrote a script to investigate further:
use DBI;
use DBD::mysql;
print "Perl $^V\n";
print "DBI ", DBI->VERSION, "\n";
print "DBD::mysql ", DBD::mysql->VERSION, "\n";
$args{host} //= 'localhost';
$args{port} //= 3306;
$args{user} //= 'root';
my $dsn = "dbi:mysql:db=$args{database};host=$args{host};port=$args{port}";
my $db = DBI->connect( $dsn, $args{user}, $ARGV[0] );
The output has the same error from the tests:
Perl v5.18.0
DBI 1.631
DBD::mysql 4.027
Character set 'latin1' is not a compiled character set and is not specified in the '/usr/local/share/mysql/charsets/Index.xml' file
DBI connect('db=;host=localhost;port=3306','root',...) failed: Can't initialize character set latin1 (path: /usr/local/share/mysql/charsets/) at test.pl line 14.
I thought turning on UTF-8 in the connect
would bypass the Latin-1 problem, but it's the same problem:
Perl v5.18.0
DBI 1.631
DBD::mysql 4.027
Character set 'utf8' is not a compiled character set and is not specified in the '/usr/local/share/mysql/charsets/Index.xml' file
DBI connect('db=;host=localhost;port=3306','root',...) failed: Can't initialize character set utf8 (path: /usr/local/share/mysql/charsets/) at test.pl line 14.
I think this is related to the MySQL bug Bug #59243:Character set 'latin1' is not a compiled character set and is not specified, but MySQL begs off responsibility for this and says it's a FreeBSD problem (and that's where I'm working!). This might be more appropriate for ServerFault if that's true.
This is MySQL:
$ mysql --version
mysql Ver 14.14 Distrib 5.5.8, for FreeBSD8.0 (amd64) using EditLine wrapper
The MySQL docs (10.5 Character Set Configuration) say that I have to specify this set in Index.xml, and that's what the error message says, but Latin-1 is already in that file (and the file is at the path in the error messages):
<charset name="latin1">
<family>Western</family>
<description>cp1252 West European</description>
<alias>csisolatin1</alias>
<alias>iso-8859-1</alias>
<alias>iso-ir-100</alias>
<alias>iso_8859-1</alias>
<alias>iso_8859-1:1987</alias>
They suggest getting a new Index.xml (although they don't say from where), so I copy the one I have on OS X (where all of this isn't a problem). That file was updated in 2012 whereas the one I had on FreeBSD was last updated in 2003. That didn't change the error message.
There's a FreeBSD bug ports/153588: Character set 'latin1' is not a compiled character set and is not specified, but it dies without any user guidance. I'd hate to take down everything to reinstall a FreeBSD port if this is a programming issue.
For what it's worth, this is only a problem because I'm installing on this on a different machine since my MySQL slave where I normally work is having all sorts of weird replication issues. I hopefully thought going right to the master (without creating a new user with the grants I need from where I am) would be easier than rebuilding the slave. Yeah, how's that working for me? :)