When I tried to upgrade 4.6 to 5.1, I had an issue. The system environment is:

  • Original:
    • Drupal 4.6
    • MySQL 4.1
    • Charset: latin1_swedish_ci
    • Using two-byte character: Yes (I am using Japanese)
  • Upgrade:
    • Drupal 5.1
    • MySQL 4.1
    • Charset: utf8_general_ci
    • Using two-byte character: Yes (I am using Japanese)

My hosting provider has changed MySQL version from 4.0 to 4.1 meanwhile the version of Drupal is going up from 4.6 to 5.1.
As a result, the charset in Recipe table is set on latin1_swedish_ci under MySQL 4.1.

Upgrading MySQL 4.0 to 4.1 is really messy in terms of charset conversion due to the specification change and it is causing some problems especially under a multi-byte character environment.

update.php on Drupal 5.1 is smartly handling this issue because I checked out update.php and found a comment as 'See: http://dev.mysql.com/doc/refman/4.1/en/charset-conversion.html' in line 643.

As long as upgrading Drupal 4.6 to 5.1, there is no problem. But Recipe module has a problem in terms of converting chaset because there is currently no converting solution like 5.1's update.php.

So I had to fix this problem manually, the following is what I did in order to convert the charset on Recipe module to migrate from 4.6/4.7 to 5.1.

ALTER TABLE recipe MODIFY source       BINARY(255),
                   MODIFY yield        BINARY(255),
                   MODIFY notes        BINARY(255),
                   MODIFY instructions BINARY(255);

ALTER TABLE recipe MODIFY source       VARCHAR(255) CHARACTER SET utf8,
                   MODIFY yield        VARCHAR(255) CHARACTER SET utf8,
                   MODIFY notes        VARCHAR(255) CHARACTER SET utf8,
                   MODIFY instructions VARCHAR(255) CHARACTER SET utf8;

ALTER TABLE recipe_ingredients MODIFY ingredient BINARY(255),
                               MODIFY ingredient VARCHAR(255) CHARACTER SET utf8;

I have no idea to include this SQL into the latest recipe module, however I hope someone include and fix this problem.

Comments

yas’s picture

yas
🇯🇵 he/him
Primary language Japanese
Location California 🇺🇸
commented

I found I put a wrong code above. The following is working.

ALTER TABLE recipe MODIFY source       BINARY(255),
                   MODIFY yield        BINARY(255),
                   MODIFY notes        BINARY(65535),
                   MODIFY instructions BINARY(65535);

ALTER TABLE recipe MODIFY source       VARCHAR(255) CHARACTER SET utf8,
                   MODIFY yield        VARCHAR(255) CHARACTER SET utf8,
                   MODIFY notes        TEXT CHARACTER SET utf8,
                   MODIFY instructions TEXT CHARACTER SET utf8;

ALTER TABLE recipe_ingredients MODIFY ingredient BINARY(255);
ALTER TABLE recipe_ingredients MODIFY ingredient VARCHAR(255) CHARACTER SET utf8;

Then,

CREATE TABLE recipe (
         nid int(10) unsigned NOT NULL,
         source varchar(255),
         yield int(2) unsigned NOT NULL,
         instructions text,
         notes text,
         preptime int(10) unsigned DEFAULT '0',
         PRIMARY KEY (nid)
      ) <strong>DEFAULT CHARACTER SET utf8</strong>;

CREATE TABLE recipe_node_ingredient (
         id int unsigned NOT NULL PRIMARY KEY auto_increment,
         nid int(10) unsigned NOT NULL,
         unit_id int(3) unsigned NOT NULL,
         quantity double,
         ingredient_id int(10) unsigned NOT NULL
      ) <strong>DEFAULT CHARACTER SET utf8</strong>;

CREATE TABLE recipe_ingredient (
         id int(10) unsigned NOT NULL PRIMARY KEY auto_increment,
         name varchar(255)
      ) <strong>DEFAULT CHARACTER SET utf8</strong>;

CREATE TABLE recipe_unit (
         id int(3) unsigned NOT NULL PRIMARY KEY auto_increment,
         name varchar(64) NOT NULL default '',
         abbreviation varchar(8) NOT NULL default '',
         metric int(1) unsigned NOT NULL default '0',
         type enum('Mass','Volume','Unit') NOT NULL default 'Mass'
      ) <strong>DEFAULT CHARACTER SET utf8</strong>;
green monkey’s picture

green monkey commented
Status: Active » Closed (fixed)

closed on age - if still active - please resubmit