ATG 2006.3 with MySQL 5.x on Linux

[qmnonic]

Has anyone had any luck installing ATG 2006.3 on MySQL 5.x?  Currently, my environment is Linux on intel (actually, OSX 1.4.7).  I'm having trouble running the ATG_HOME/DAS/sql/install/mysql/das_ddl.sql script, it fails with the following error:

ERROR 1071 (42000) at line 284: Specified key was too long; max key length is
1000 bytes

My characterset seems correct - latin1 as follows:

| character_set_client     | latin1                                                              |
| character_set_connection | latin1                                                              |
| character_set_database   | latin1                                                              |
| character_set_filesystem | binary                                                              |
| character_set_results    | latin1                                                              |
| character_set_server     | latin1                                                              |
| character_set_system     | utf8

I'm wondering if the character_set_system should be latin1 aswell, but I can't be sure.

I can reduce the key sizes, but this might adversely affect ATG.

Thoughts, comments, updates?

[qmnonic]

As a temporary workaround, I updated das_ddl.sql as follows:

----
create table if_integ_data (
        item_id varchar(40)     not null,
--      descriptor      nvarchar(255)   not null,
        descriptor      varchar(255)    not null,
--      repository      nvarchar(255)   not null,
        repository      varchar(255)    not null,
        state   integer not null,
        last_import     datetime        null,
        version integer not null
,constraint if_int_data_p primary key (item_id,descriptor,repository));

commit;

----

This reduces the overall key length (varchar is 1 byte, nvarchar is 2) so that it fits into mysql's limit of 1000 bytes per key.  I opened a support ticket with ATG about this and will hopefully hear back soon.

[qmnonic]

Long term solution - ensure MySQL is using INNODB instead of MYISAM.

For example, this will work:
create table if_integ_data (
        item_id varchar(40)     not null,
        descriptor      nvarchar(255)   not null,
        repository      nvarchar(255)   not null,
        state   integer not null,
        last_import     datetime        null,
        version integer not null
,constraint if_int_data_p primary key (item_id,descriptor,repository)) ENGINE = INNODB

Whereas this will cause the error -
"ERROR 1071 (42000) at line 284: Specified key was too long; max key length is
1000 bytes"
create table if_integ_data (
        item_id varchar(40)     not null,
        descriptor      nvarchar(255)   not null,
        repository      nvarchar(255)   not null,
        state   integer not null,
        last_import     datetime        null,
        version integer not null
,constraint if_int_data_p primary key (item_id,descriptor,repository)) ENGINE = MYISAM



Awesome.  Thanks to Enrique Matta at ATG Support for that!

[thebigjc]

Eek - your MySQL should be setup to use INNODB by default. You should also be using UTF8 for everything when possible. If you load Latin1 data into a UTF8 table, and any of the characters are invalid UTF8 codepoints (i.e. almost anything over 127 that isn't encoded properly for UTF8) will give you that same 'too long' error, even if the data isn't too long.

[qmnonic]

What's funny about that is the ATG dev's were using latin1.  I started with utf8 and simplified as the errors appeared.