Hi,
I'm trying to migrate an old system to a newer - while doing that I
have to move the database. The old database i UTF-8
encoded, and the new one is Latin-1.
To move the data I'm doing something similar to:
INSERT INTO new_db.table (new_db.field) SELECT
CONVERT(old_db.field USING latin1) FROM old_db.table;
When doing a
SELECT CHARSET(field) FROM new_db.table;
It returns "latin1", so everthings seems to be A-Okay - but since
I'm posting this message you could prolly guess that it didn't -
and right, it failed...
Using SSH to the mysql-server, and using `mysql` it always
returns the data as UTF-8 (when setting the encoding to
anything else it trashes the special-chars). I don't know if this is
normal - I might be, so I continued my work on the migration.
I then reloaded the webpage (yes, it's for a PHP driven CMS),
and no - the problem still existed. All data was returned as
UTF-8 (when running a utf8_decode() in PHP on the db-output
the output was corrected and the speciel-chars showed up.
But - this isn't normal. Normally you can just enter the data as
latin1, and display the output on the webpage (assuming that
content-type has a charset og iso-8859-1). And this works
everywhere else on the website.
SO, my question is: How the heck do you actually transfer
data from the utf8-table to a latin1-table and properly
re-encoding the text so that the latin1-table wouldn't contain
raw utf8-data (because that my friends - sucks)
Below are som excerps from SHOW VARIABLES - tell if
your wan't more info... just included a few of the lines:
+---------------------------------+---------------------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------------------+
| back_log | 50 |
| basedir | /pack/mysql-4.1.13/ |
| binlog_cache_size | 32768 |
| bulk_insert_buffer_size | 8388608 |
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| version | 4.1.13-log |
+---------------------------------+---------------------------------------------------+
Regards
Morten Fangel // fangel
