By ant71 on

My webhost has recently upgraded the MySQL database server from 4.0.x to 4.1.x. I am currently running Drupal 4.7.2 with the old MySQL 4.0 database schema (i.e. charsets are latin). I would like to upgrade the database to the 4.1 schema (i.e. utf-8 charsets) but am not sure what the smoothest way to do it would be.

I am guessing I would;

1. back up all files and the database.
2. drop the old database then run the 4.1 script
3. replace all the druapl files.
4. reinstall all modules
5. import content back into the database

Alternatively, could I just run alter table queries to change the character set of all tables?

Any suggestions greatfully received.

thanks

⋅ Categories: Drupal 4.7.x

Comments

killes@www.drop.org’s picture

killes@www.drop.org commented

I think that both ways should work.
--
Drupal services
My Drupal services

--
Drupal services
My Drupal services

forngren’s picture

forngren commented

How do I do an ALTER TABLE? ALTER TABLE * CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; doesn't work :(

From http://dev.mysql.com/doc/refman/4.1/en/alter-table.html

From MySQL 4.1.2 on, if you want to change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

This is useful, for example, after upgrading from MySQL 4.0.x to 4.1.x. See Section 10.9, “Upgrading Character Sets from MySQL 4.0”.

Warning: The preceding operation converts column values between the character sets. This is not what you want if you have a column in one character set (like latin1) but the stored values actually use some other, incompatible character set (like utf8). In this case, you have to do the following for each such column:

ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;

The reason this works is that there is no conversion when you convert to or from BLOB columns.

If you specify CONVERT TO CHARACTER SET binary, the CHAR, VARCHAR, and TEXT columns are converted to their corresponding binary string types (BINARY, VARBINARY, BLOB). This means that the columns no longer will have a character set and a subsequent CONVERT TO operation will not apply to them.

To change only the default character set for a table, use this statement:

ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;

The word DEFAULT is optional. The default character set is the character set that is used if you do not specify the character set for a new column which you add to a table (for example, with ALTER TABLE ... ADD column).

Warning: From MySQL 4.1.2 and up, ALTER TABLE ... DEFAULT CHARACTER SET and ALTER TABLE ... CHARACTER SET are equivalent and change only the default table character set. In MySQL 4.1 releases before 4.1.2, ALTER TABLE ... DEFAULT CHARACTER SET changes the default character set, but ALTER TABLE ... CHARACTER SET (without DEFAULT) changes the default character set and also converts all columns to the new character set.

Steve Dondley’s picture

Steve Dondley commented

Is there a mysql guru in the house?

--
Get better help from Drupal's forums and read this.

bmargulies’s picture

bmargulies commented

This isn't so hard...

create a file that contains two lines:

show tables
quit

mysql --database=dbname --user=username --password=passwordforit < that_file > alter.sql

Now, edit alter.sql. Put 'alter table ' on the front of every line, and 'character set utf8;' on the back of every line.

Now feed to mysql.

kowalke’s picture

kowalke commented

I tried the manual alter table method mentioned above. However, site text still comes out garbled.

I'm wondering if this because the DB itself still is latin1.