Database using 'latin1' .MYD's with 'utf8' .frm's
Posted by: Kit DeKat
Date: May 17, 2007 02:22PM

I am having corruption and data truncation from a recent migration from latin1 to utf8. It appears as if the database is parsing the MYD files in UTF8 while the files are really in LATIN1 format.

The SHOW CREATE TABLE statement is displaying all columns with 1/3 of the byte size they actually have (255->85, 100->33, & 32->11) and the DEFAULT CHARSET=utf8 -- even though they were copied from a DEFAULT CHARSET=latin1 database.

I set the default character set in the /etc/my.cnf like so:

[client]
default-character-set=utf8
[mysqld]
default-character-set=utf8

The SHOW VARIABLES LIKE '%char%' returns:

+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

The SHOW CREATE TABLE returns: (truncated)

CREATE TABLE `table_name` (
`user_id` mediumint(8) NOT NULL default '0',
`user_active` tinyint(1) default '1',
`username` varchar(8) NOT NULL default '',
`user_password` varchar(10) NOT NULL default '',
`user_lastvisit` int(11) NOT NULL default '0',
`user_dateformat` varchar(4) NOT NULL default 'd M Y H:i',
`user_website` varchar(33) default NULL,
`user_from` varchar(33) default NULL,
`user_interests` varchar(85) default NULL,
`user_newpasswd` varchar(10) default NULL,
PRIMARY KEY (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8


What do I need to do to get my data back?
- a special command like ALTER TABLE to "reconvert" from LATIN1 to UTF8?
- find a way to export the data, recreate the table in proper UTF8, and load data?
- setup multiple mysql services and dump/load between them?
- create a quantum computer that already has every possible permutation of the above and more just to select what I need?

Options: ReplyQuote


Subject
Views
Written By
Posted
Database using 'latin1' .MYD's with 'utf8' .frm's
2354
May 17, 2007 02:22PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.