Issue installing with MySQL

Post Reply
gregor
Posts: 2
Joined: 27 Sep 2013, 19:16

Hi,

I'm installing Essential Project 4 on MySQL 5.6 and having problems with setting this up on the DB. I've gone through the installation instructions and all is well until I get to the "convert project to format" stage. Clearly the DB connection is working as the table gets created in my schema, however no data is added and I get the error message below:

1. Errors at copying knowledgebase jdbc:mysql://localhost/essential
Exception class com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException. Message: Specified key was too long; max key length is 767 bytes

See console and log for more details


An extract from the log does not shed any more light for me
2013.09.27 20:30:17.953 BST INFO: Creating database indicies: -- DefaultDatabaseFrameDb.createIndices()
2013.09.27 20:30:18.875 BST WARNING: Failed to create table on database MySQL with command 'CREATE TABLE essentialRepository1 (frame VARBINARY(310) NOT NULL, frame_type SMALLINT NOT NULL, slot VARBINARY(310) NOT NULL, facet VARBINARY(310) NOT NULL, is_template BIT NOT NULL, value_index INT NOT NULL, value_type SMALLINT NOT NULL, short_value VARCHAR(310) COLLATE utf8_general_ci, long_value MEDIUMTEXT) ENGINE = INNODB DEFAULT CHARACTER SET 'utf8'' :Specified key was too long; max key length is 767 bytes -- DefaultDatabaseFrameDb.createTable()
2013.09.27 20:30:18.937 BST WARNING: Errors at copying knowledgebase jdbc:mysql://localhost/essential -- com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes



Has anyone hit this before? Have I missed something in my config somewhere?

Thanks
User avatar
neil.walsh
Posts: 444
Joined: 16 Feb 2009, 13:45
Contact:

Hi,

Thanks for the log details.

Can you tell me a bit more about your setup?

We have heard reports of some people having difficulty with MySQL6 (though I understand it's worked fine for others). You may wish to try giving MySQL v5.5 a go.

Which versions of Windows, Protege, MySQL are you using?
Which JDBC driver are you using?

Let us know and we'll investigate.

Cheers

Neil
gregor
Posts: 2
Joined: 27 Sep 2013, 19:16

Hi,

Thanks for the response, sorry I've not updated sooner as I didn't get a chance until now to look at it.
I was using Windows XP, Protege 3.4.8, MySQL 6 and JDBC driver 5.1.26. I changed to use MySQL V5.5 as per your advice, this did the trick and worked no problem. Up and running :D , thanks again!

Cheers

Gregor
User avatar
jonathan.carter
Posts: 1087
Joined: 04 Feb 2009, 15:44

Following up on this request, we have found the answer to making Protege work with MySQL 5.6+

What is the issue?
It turns out that the problem is to do with the length (in bytes) of the short_value column. By default, this is set to VARCHAR(310) for MySQL databases. The answer is to set this to 255 (the limit for UTF-8 text columns when they are used in indexes). The beauty of Protege is that we can do this without having to make any code changes - just by setting two properties in the Protege.properties file.
  • Go to File -> Preferences and go to the ‘protege.properties’
  • Create a new property called: Database.type.varchar.maxsize.com.mysql.jdbc.Driver and set the value to 255
  • Create another new property called: Database.typename.short.value.type.com.mysql.jdbc.Driver and set that value to VARCHAR(255)
Messages Image(477548483).png
Now, when you perform the Convert to Protege Database, the short_value column will be adjusted and the project will save in to a MySQL 5.6+ table

Jonathan
You do not have the required permissions to view the files attached to this post.
Essential Project Team
seldont
Posts: 11
Joined: 14 Jun 2016, 10:58

Had the same problem with mariadb - Getting : 'Specified key is too long'.

Alternate solution is to create the database using latin1 rather than utf8
i.e.

create database if not exists <dbname> character set = 'latin1';
select * from information_schema.schemata;
Post Reply