Hi,

So, I've built a new database using UTF8_general_cli as the default character set (which, according to others on this forum, is the default character set that Drupal expects). But if I try to setup the FREELINKING database using the code:

CREATE TABLE freelinking (
  phrase VARCHAR(200) NOT NULL,
  target VARCHAR(200) NOT NULL,

  PRIMARY KEY(phrase, target)
);

MySQL complains that:

#1071 - Specified key was too long; max key length is 1024 bytes

My question is this: can I get round this problem by setting up the database like this:

CREATE TABLE freelinking (
  fid INT(11) NOT NULL auto_increment,
  phrase VARCHAR(200) NOT NULL,
  target VARCHAR(200) NOT NULL,

  PRIMARY KEY(fid)
);

Many thanks,
Jack

Comments

eafarris’s picture

eafarris commented
Assigned: Unassigned » eafarris

This is not a problem with UTF8, but rather the version of MySQL that you're using. Older versions don't allow for keys as long as the freelinking.mysql file wants. What you've done is an acceptable workaround, though with a large site you may find performance drop, as fid isn't used in the code anywhere.

Thanks for the report. Leaving this active until it's patched properly.

dan_aka_jack’s picture

dan_aka_jack commented

Great, thanks for your reply.

I'm using MySQL version 4.1.16 running on Windows XP Pro SP2. As I understand it, 4.1.16 is the most recent version of the 4.1 tree.

Maybe it's a problem with running MySQL on WinXP?

Other version numbers of the software that I'm running:

Apache 2.0.55
PHP 5.1.1
MySQL 4.1.16

Thanks,
Jack

dan_aka_jack’s picture

dan_aka_jack commented

Two other bits of info:

I'm running PHP as an Apache module and I'm accessing MySQL via the mysqli system.

dan_aka_jack’s picture

dan_aka_jack commented

(Sorry for all these posts!)

I should explain why I think it's a UTF8 issue:

The first time I installed Drupal-4.7-CVS on my new server, I setup the database useing the default characer set (latin1_swedish_ci). I received no errors when I installed FreeLinking on this system. But on the exact same server setup, the Freelinking database setup fails when the database is set to the UTF8 character set. So, to recap: Freelinking installs fine on my server using the default "latin1_swedish_ci" character set but fails when I switch to the UTF8 character set.

mgifford’s picture

mgifford
he/him
Primary language English
commented

In looking into this problem I found this link:
http://bugs.mysql.com/bug.php?id=6604

CREATE TABLE freelinking (
phrase VARCHAR(200) NOT NULL,
target VARCHAR(200) NOT NULL,
PRIMARY KEY(phrase(50), target(50))
);

This should be faster and avoid problems with the unicode character size (mind you I haven't tried this yet).

Mike

eafarris’s picture

eafarris commented

excellent. Yes, it is a size problem, and it seems to be fixed with MySQL 5.0.15, which is on my test machine.

When I finally branch the module for 4.7, it will use a hash as the primary key, which should resolve this.

dan_aka_jack’s picture

dan_aka_jack commented

Great, thanks for your swift replies and fixes.

eafarris’s picture

eafarris commented
Status: Active » Fixed

The current CVS version uses a hash instead of the longer text fields as the primary key. This version needs some more testing before I can branch it for 4.7, please verify and close.

dan_aka_jack’s picture

dan_aka_jack commented
Title: Freelinking database doesn't work with UTF8??? » Freelinking database doesn't work with UTF8?

Thanks. I'm afraid I won't get a chance to verify this. Please could others verify and close? Many thanks,
Jack

Anonymous’s picture

(not verified) commented
Status: Fixed » Closed (fixed)