MySQL Character Set and Collation

[postman]

I am importing databases from MySQL 3.2 to MySQL 5. When I create a database using Plesk it creates it as utf8 (Character Set) and utf8_general_ci(Collation) but when I create a database using PhpMyAdmin the database is created as latin1 (Character Set) and latin1_swedish_ci(Collation). The trouble comes when I import sql files into the utf8 database. I get an error "Specified key was too long; max key length is 1000 bytes".
I am able to solve this by converting the utf8_general_ci to latin1_swedish_ci and then doing the import again.

ALTER DATABASE `myDB` CHARACTER SET latin1 COLLATE latin1_swedish_ci;

However, I would like Plesk to default to creating the databases as latin1_swedish_ci but I don't know where to do this? Can anyone help with that?
Thanks in advance,
postman

 

[Hedgren]

Hi: the default MySQL character should be set as 'latin1'. Should also be the same in /etc/my.cnf. There are some relevant notes in http://kb.swsoft.com/en/2112, /Regards.

 

[Hedgren]

Also try out commenting (or removing) the option '--compatible=mysql323' from all lines where it is found in the file: /usr/local/psa/PMM/agents/shared/Db/MysqlShellBackend.pm
and re-run the doman migration in Migration Manager from Plesk control panel. There are high chances this prob has been fixed in Plesk 8.3's migration manager (PMM).

 

[M:ke]

Dear users.

Default MySQL character set is on both server the same: latin1. Im using Plesk 8.3.0.
But the PMM creates new databases as utf-8, not latin1. I got the same max key length message:

ERROR 1071 (42000) at line 295: Specified key was too long; max key length is 1000 bytes

When I create the database manually (by using the mysql-client) all goes fine.

Edit: I grepped utf8 in the PMM agents:

localhost:/usr/local/psa/PMM/agents/shared # grep -R "utf8" .
./Db/MysqlDbiBackend.pm:# 'name', 'user', 'password'[, 'host'][, 'socket'][, 'port'][, 'utf8names']
./Db/MysqlDbiBackend.pm: $self->{utf8names} = $params{utf8names} if defined $params{utf8names};
./Db/MysqlDbiBackend.pm: if ($self->{utf8names} && Db::MysqlUtils::doesSupportCharacterSets($self)) {
./Db/MysqlShellBackend.pm:# 'name', 'user', 'password'[, 'host'][, 'socket'][, 'port'][, 'preload_dirs'][, 'utf8names']
./Db/MysqlShellBackend.pm: $self->{utf8names} = $params{utf8names} if defined $params{utf8names};
./Db/MysqlShellBackend.pm: if ($self->{utf8names} && Db::MysqlUtils::doesSupportCharacterSets($self)) {
./Db/MysqlShellBackend.pm: $dumpoptions .= " --default-character-set=utf8 --set-charset" if $self->_mysql41OrNewer();
./Db/MysqlUtils.pm: return "SET character_set_results = 'utf8', character_set_server = 'utf8', character_set_connection = 'utf8', character_set_client = 'utf8'";
./Db/MysqlUtils.pm: return "SET NAMES utf8";
./agent.include.pl:sub utf8ToIdn {
./agent.include.pl: my ($utf8domain) = @_;
./agent.include.pl: return $utf8domain;
./agent.include.pl: if (open(IC, "echo '$utf8domain' | " . AgentConfig::idnconvBin() . " -in 'UTF-8' |")) {
./agent.include.pl: return $utf8domain;

Seems to be hardcoded. Or anavailable to set up in Plesk configuration frontend. I try to fix my problem by changing the $dumpoptions-variable.

Edit: No, I try nothing yet. Because this "problem" still exist when I create a database in the Plesk frontend. Database is UTF8. See:

Server version: 5.0.26
Protocol version: 10
Server characterset: latin1
Db characterset: utf8
Client characterset: latin1
Conn. characterset: latin1

 

[M:ke]

Also try out commenting (or removing) the option '--compatible=mysql323'

Does not work.
I need a solution, cause I cant migrate all databases by myself.

 

[benji]

Also try out commenting (or removing) the option '--compatible=mysql323' from all lines where it is found in the file: /usr/local/psa/PMM/agents/shared/Db/MysqlShellBackend.pm
and re-run the doman migration in Migration Manager from Plesk control panel. There are high chances this prob has been fixed in Plesk 8.3's migration manager (PMM).

I simply removed the <<<--default-charset=utf8>>> parameter, on that file (/usr/local/psa/PMM/agents/shared/Db/MysqlShellBackend.pm), and now migration manager is working just perfect. Hope that helps anyone.