3

If you take the hello-slick-3.0 typesafe activator template and try to use it with MySQL rather than H2, creating the COFFEES table results in the following MySQL JDCB driver exception:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: BLOB/TEXT column 'COF_NAME' used in key specification without a key length

This apparently is due to COF_NAME primary key field's using a SQL TEXT column, instead of say, a VARCHAR column, and consequently running into INNODB's limit of 768 bytes for keys. Is there anything that can be done here other than stop using Slicks DDL, and switching to explicit MySQL schema creation?

eswenson
  • 745
  • 1
  • 9
  • 25

3 Answers3

4

With Slick 3.2, use O.Length instead of O.Sqltype: 

def name = column[String]("COF_NAME", O.PrimaryKey, O.Length(100))
kostja
  • 60,521
  • 48
  • 179
  • 224
1

The error happens because MySQL can index only the first N chars of a BLOB or TEXT column. So The error mainly happen when there is a field/column type of TEXT or BLOB or those belongs to TEXT or BLOB types such as TINYBLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, MEDIUMTEXT, and LONGTEXT that you try to make as primary key or index.(for more info MySQL error: key specification without a key length)

You just need to remove primary key from COF_NAME column:

     def name: Rep[String] = column[String]("COF_NAME")

Or you can specify datatype using DBType(dbType: String):

 def name: Rep[String] = column[String]("COF_NAME", O.PrimaryKey,O.DBType("varchar(100)"))
Community
  • 1
  • 1
Sky
  • 2,509
  • 1
  • 19
  • 28
  • Thanks. The O.DBType("varchar(100)") was what I needed. I would think (even thought this is a dummy sample application) that one would want COF_NAME to be a primary key. However, since Slick is doing the DDL creation, and the Scala type is String, I would think that it should be taught (in the MySQL slick driver) to NOT use TEXT with O.PrimaryKey, but rather to use VARCHAR, since it should *know* that MySQL has this limitation of key length. – eswenson Apr 11 '15 at 21:36
  • 1
    @user1769997 Good point. But it is little bit tough for slick to decide the size of varchar used for Schema creation.So this flexible for user , he can decide what size he want to use for schema creation. – Sky Apr 13 '15 at 16:50
  • Thanks. You're right. I guess I can't think of anything Slick could do that would be right in all cases. Better to force the user to specify a DB type. – eswenson Apr 13 '15 at 20:56
1

Since O.DBType is deprecated, it would be more convenient to use O.SqlType:

 def name: Rep[String] = column[String]("COF_NAME", O.PrimaryKey,O.Sqltype("varchar(100)"))
fcat
  • 1,251
  • 8
  • 18