[teiid-issues] [JBoss JIRA] (TEIID-4337) External materialization status table's PK too long for MySQL InnoDB

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-4337?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13273078#comment-13273078 ] 

Steven Hawkins commented on TEIID-4337:
---------------------------------------

It will be difficult to address this in general without using generated values for vdb/version and schema/name combinations.  Since that would be a major effort, it seems best to just recommend using shorter column lengths for MySQL InnoDB - which should be fine in most circumstances as most names will be significantly shorter.  If that doesn't work, then another db would have to be used.  Anyone opposed to just documenting this?

> External materialization status table's PK too long for MySQL InnoDB
> --------------------------------------------------------------------
>
>                 Key: TEIID-4337
>                 URL: https://issues.jboss.org/browse/TEIID-4337
>             Project: Teiid
>          Issue Type: Bug
>          Components: Query Engine
>    Affects Versions: 8.12.5
>            Reporter: Jan Stastny
>            Assignee: Steven Hawkins
>
> When using MySQL with InnoDB storage engine user can't create the status table in it. As stated in [MySQL docs|http://dev.mysql.com/doc/refman/5.5/en/create-index.html ], the limit for PK lengths in InnoDB and MyISAM storage engines differ, for InnoDB being 767 bytes.
> There is needed varchar(50)+varchar(50)+int+varchar(256) to store the key in Teiid. This should be fine when using latin1 charset in the database, but will not work with utf-8 as there is a single character saved in 1-4 bytes.
> Create table query:
> {code:sql}
> create table dv_matviews_statustable (VDBName varchar(50) not null, VDBVersion integer not null, SchemaName varchar(50) not null, Name varchar(256) not null, TargetSchemaName varchar(50), TargetName varchar(256), Valid bit not null, LoadState varchar(25) not null, Cardinality bigint, Updated datetime not null, LoadNumber bigint not null, primary key (VDBName, VDBVersion, SchemaName, Name))
> {code}
> Error message:
> {code:plain}
> Error: Specified key was too long; max key length is 767 bytes
> {code}



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)