DD,MySQL:Too big column length for column LONGID

I am getting the message below when trying to setup DAS. I am using EasyDeploy 6.0 with DataDeploy module installed/enabled. I am trying to setup DAS from the OD/DD Admin UI: DAS->DAS Configuration.


...
[Wed Aug 04 16:29:34 EDT 2004] Begin executing deltagen
Area VPath:/default/main/Internet/WORKAREA/Internet
Setting up TDbSchemaSynchronizer.
Validating cached connection...
Cached connection OK
Connected to [<db_host>:3306/<dbinstance>]
Creating identifier mapping table: IWOV_IDMAPS
CREATE TABLE IWOV_IDMAPS (TYPE INT NOT NULL,  SHORTID VARCHAR(30) NOT NULL,  LONGID VARCHAR(1024) NOT NULL) 
ERRORQLException: Invalid argument value,  message from server: "Too big column length for column 'LONGID' (max = 255). Use BLOB instead"
ERROR:Vendor Error code: 1074
ERRORQL state: S1009
ERROR:TIdentifierMappingTable constructor failed
Error occured in TDbAgent constructor
...

It seems that MySQL does not allow length of more than 255 for VARCHAR type, and DD failes to create the IWOV_IDMAPS, IWTRACKER tables.

Does ED6.0 with DD module work with MySQL in DAS mode ?
Can the maxlength of VARCHAR be changed in MySQL ?
Can we change DD to used different column type/length for IWOV_IDMAPS and IWTRACKER tables ?

Any other ideas ?

ENV:
TS6.1, ED6.0, DD 6 module for ED6.0 on Windows2003
MySQL 3.23.58 on Linux.

Find more posts tagged with

Comments

iwovGraduate

This turned out to be a matter of little RTFM: DD Admin Guide.
Resolved.

sort_crosstab.rptdesign

Adam Stoller

What was it that you found in your RTFM'ing that resolved your problem?

--fish
Senior Consultant, Quotient Inc.
http://www.quotient-inc.com

iwovGraduate

You can use create_tables_override.xml file to override the default size. Example:


<sql-statements-override>
 <create>
  <table name="iwtracker">
   <sql-statement>
    CREATE TABLE IWTRACKER
    (
    NAME VARCHAR(255) NOT NULL,
    BASE VARCHAR(255) NOT NULL,
    BRANCH VARCHAR(255) NOT NULL,
    UPDATETIME VARCHAR(255) NOT NULL
    )
   </sql-statement>
  </table>
  <table name="iwdeltracker">
   <sql-statement>
    CREATE TABLE IWDELTRACKER
    (
    PATH VARCHAR(255) NOT NULL,
    AREA VARCHAR(255) NOT NULL,
    KEYCOLNAME VARCHAR(255) NOT NULL,
    KEYCOLVALUE VARCHAR(255) NOT NULL
    )
   </sql-statement>
  </table>
  <table name="iwov_idmaps">
   <sql-statement>
    CREATE TABLE IWOV_IDMAPS
    (
    TYPE INT NOT NULL,
    SHORTID VARCHAR(255) NOT NULL,
    LONGID VARCHAR(255) NOT NULL
    )
   </sql-statement>
  </table>
  <table name="iwdephistory">
   <sql-statement>
    CREATE TABLE IWDEPHISTORY
    (
    CONFIGNAME VARCHAR(255) NOT NULL,
    AREA VARCHAR(255) NOT NULL,
    DEPLOYMENTNAME VARCHAR(255) NOT NULL,
    FILENAME VARCHAR(255) NOT NULL,
    ACTIONCODE INT NOT NULL
    )
   </sql-statement>
  </table>
 </create>
</sql-statements-override>

Adam Stoller

Maybe I'm missing something here - but how does this get around the limitation on the size of VARCHAR? or does it just explicitly truncate the desired size to the permissable size so that you don't get the error?

--fish
Senior Consultant, Quotient Inc.
http://www.quotient-inc.com

iwovGraduate

As I understand, it does not get around the limitation. It simply creates columns with VARCHAR(255).
e.g.: the LONGID column default is VARCHAR(1024), with this config, DD will create the LONGID column as VARCHAR(255).

This gets around the problem of creating the tables itself. I don't see how it gets around which the data exceeds 255 - which is quite possible with DAS schema. e.g. if the value in the LONGID column needs to be more than 255.

For the timebeing, I have given up on using DAS with MySQL. Using DD in standalone mode instead - which requires only IWDELTRACKER table.

iwovGraduate

Update:

The override of create statement works, except an error in CREATE INDEX statement:


ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD| Over-riding CREATE statement specified for IWDELTRACKER.
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD| CREATE TABLE IWDELTRACKER
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD|   (
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD|       PATH VARCHAR(255) NOT NULL,
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD|       AREA VARCHAR(255) NOT NULL,
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD|       KEYCOLNAME VARCHAR(255) NOT NULL,
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD|       KEYCOLVALUE VARCHAR(255) NOT NULL
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD|   )
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD| CREATE INDEX iwdelt_idx ON IWDELTRACKER( PATH , AREA )
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD| 
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD| *******************************************************
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD| SQLException occured in TDeleteTracker
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD| Exception Message: Invalid argument value,  message from server: "Specified key was too long. Max key length is 500"
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD| Vendor Error Code: 1071
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD| SQL state: S1009
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD| *******************************************************
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD| 
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD| *******STACK TRACE*************
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD| java.sql.SQLException: Invalid argument value,  message from server: "Specified key was too long. Max key length is 500"
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD|  at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1977)
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD|  at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1163)
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD|  at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1272)
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD|  at com.mysql.jdbc.Connection.execSQL(Connection.java:2236)
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD|  at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1741)
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD|  at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1588)
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD|  at com.interwoven.dd100.dd.TDeleteTracker.CreateDeleteTracker(TDeleteTracker.java:94)
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD|  at com.interwoven.dd100.dd.TDeleteTracker.<init>(TDeleteTracker.java:43)
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD|  at com.interwoven.dd100.dd.TDbSchemaAgent.<init>(TDbSchemaAgent.java:109)
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD|  at com.interwoven.dd100.dd.TConsumerManager.CheckDbSchemaPresent(TConsumerManager.java:513)
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD|  at com.interwoven.dd100.dd.TConsumerManager.SetupConsumer(TConsumerManager.java:586)
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD|  at com.interwoven.dd100.dd.TConsumerManager.SetupConsumersForOneDeployment(TConsumerManager.java:191)
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD|  at com.interwoven.dd100.dd.TConsumerManager.SetupConsumers(TConsumerManager.java:133)
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD|  at com.interwoven.dd100.dd.TAgentClient.Go(TAgentClient.java:97)
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD|  at com.interwoven.dd100.dd.IWDataDeploy.Go(IWDataDeploy.java:520)
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD|  at com.interwoven.dd100.dd.TWorkItem.DoIt(TWorkItem.java:165)
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD|  at com.interwoven.dd100.dd.TLonelyWorker.run(TLonelyWorker.java:49)
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD|  at java.lang.Thread.run(Thread.java:479)
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD| ERROR:Unable to create Delete Tracker.
ENG: 2004-08-05 21:04:58 EDT GMT-04:00 DD| Error occured in TDbSchemaAgent

Even though the message above indicates "Unable to create Delete Tracker." The IWDELTRACKER table is created.

Any ideas ?

Migrateduser

I found your post encouraging because I'm investigating if MySQL can be used with the DD module. Upon looking at the DTD (which should be the gold standard, right?), it looks like "NO". But it seems that you're successfully getting them to talk.

Is there anything out of the ordinary that is necessary to configure this with MySQL versus, say, Oracle? After all, it's not in the DTD (although, I can't say I'm surprised).

Dave

Environments:
(1,2) TS6.5, TS6.5SP1 on W2K3
(3) Vignette V7 Portal on Solaris 9

Johnny

Looks like I've found this problem as well with OD 6.1.1

Has anyone worked around this problem?

Bill Klish

We still ran into this.

Johnny

It's amazing how long some bugs hang around for.

fbousqu

Same problem here too