Failed migration, key too long - Imposible to install

[EnderStar]

Hi, i can install namelayer. spigot 1.10.2 latest NameLayer-2.9.06 latest Error: http://pastebin.com/7mBpgv60 config succesfully generated i have civmodcore installed (it generated an empty config file thanks!

[ProgrammerDan]

What version of database are you using? I recommend Mariadb 10.0.2 or later.

[EnderStar]

i use Mariabd 10.0.2 Thanks for the fast help! i tryed to generate the tables with an older version (430) and it worked, it generated the 13 tables but when i changed it for the latest version i had the same problem

[ProgrammerDan]

Check this out: https://answers.launchpad.net/maria/+question/241612

Can probably get this working if you run set global innodb_large_prefix=on; and perhaps the other commands recommended in that thread. I cannot attest to this, as I've never had to do so on any install to date, when using mariadb. Hopefully this helps out!

[EnderStar]

Thanks :D i will try but is this a bug in the latest version? i dont understand how this only happens to me

[EnderStar]

After following this tutorial: http://mechanics.flite.com/blog/2014/07/29/using-innodb-large-prefix-to-avoid-error-1071/ i get the same error, maybe the tables dont use ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED so it cant use the large prefix?

[ProgrammerDan]

That would be my assumption as well. Consider implementing the rest of the poster's process.

[EnderStar]

i tryed to do the example but i fail at setting large prefixes on, this is what i did as a test: create database namelayer; use namelayer; set global innodb_file_format = BARRACUDA; set global innodb_large_prefix = ON; and then i try to create the table in the example but i get the error. can you tell me what i am doing wrong? thanks

[EnderStar]

I finally did it, i can set large-prefix=on and it works with the example in my last post but namelayer keeps throwing the same error (probably because it dont have the required row format), i used the 430 build to create the tables because that version works fine, then i changed row format to dynamic in each table and finally i installed the latest version of namelayer but now i have this error: http://pastebin.com/Xd4fuWSJ Can you tell me the name of the required tables and the needed info about them to create them manually? thanks :D

[ProgrammerDan]

I'm confounded.

Please upgrade to Mariadb 10.1.17 or as close to that as you can, drop all the tables related to Namelayer, and begin again.

Trying to manually recover the install is likely a painful exercise.

I just tried creating the "problem" table on that release, and had no problems whatsoever with the keysize, and it was a virgin install, effectively un-modified config on a VPS.

[EnderStar]

what names have you used for the user, database and do you use pasword? and what privileges have you granted for that user?

i cant get it to work D: now i have the same problem

[ProgrammerDan]

For production I always use a custom user and password, and a new database with all permissions granted to that user on that database.

What operating system are you using? What version of Mariadb? What version of Spigot? I'll see if I can match 'em and reproduce.

[EnderStar]

i use mariabd 10.1, spigot 1.10.2-R0.1-SNAPSHOT and NameLayer-2.9.06 i create a database and then GRANT ALL ON namelayer.* TO namelayer_user@localhost IDENTIFIED BY 'pcFas5'; my config: http://pastebin.com/BsZmcDfi civmodcore version: CivModCore-1.5.02 Edit: i know that the password in the command here and the one in the config is different, i use the same password in the server

[ProgrammerDan]

use the same password in the server

Be sure whatever passwords you use are different from what you post here. This is public, and prior posts might reveal the IP -- always be mindful of savages looking for easy exploits :).

In any case:

Alright, I'll do my best to match on a virtual host tonight and let you know if I run into the same, and what resolution might exist.

Oh, one final query, what version of Java are you using?

[EnderStar]

im using java 8 thanks :D

[ProgrammerDan]

OpenJDK or Oracle's official JDK?

[EnderStar]

openjdk

[EnderStar]

hi, have you run into the same?

[ProgrammerDan]

Ran out of time to test last night, will try tonight.

[ProgrammerDan]

I still haven't had a chance to look at this, my apologies. Any changes on your end?

[EnderStar]

No problem and i always get the same error

[ProgrammerDan]

What operating system are you using? We're discussing this issue and we have a theory.

[Maxopoly]

After a bit of discussion we managed to track this down, the error will occur if your default charset is utf8mb4, which uses 4 byte per character compared to utf8 (3 byte) or latin1 (1 byte) which are usually used and with which it wouldnt be too long.

Easy fix is explicitly specifying the charset the table should have, so fixed by https://github.com/Civcraft/NameLayer/commit/f6c82f1155d0780b34e68f37d94eccbb31c7136c

[erocs]

NameLayer isn't specifying the table character sets / collations. I'd bet that the defaults on his install are set to a variant of UTF, which has to utilize the maximum character encoding length when calculating the maximum field length. For a varchar(255) using utf-8 this would be 765 bytes. Since it's varchar, add the additional 2 bytes to contain the string length and that 767 bytes is suspiciously the same as the error in his log.

Walking down the migrations, the faction_member is the first which uses more then a single varchar(255) field as its primary key.

Another issue is that NL isn't specifying the table type and row format. Assuming a default of InnoDB, we find http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html. "The index key prefix length limit is 767 bytes for InnoDB tables that use the REDUNDANT or COMPACT row format. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and the maximum of 3 bytes for each character." Other table formats / options will have different max lengths.

Looking above we see that Barracuda is being used. "The code name for an InnoDB file format that supports the COMPRESSED row format."

innodb_large_prefix is deprecated and I'm unsure about mariadb support so it's not surprising this isn't honored.

[ProgrammerDan]

Yep, we all arrived at the same conclusions.

Fascinating to research, however. Thanks for the additional knowledge drop.

[ProgrammerDan]

@EnderStar To avoid all issues, simply set the encoding of the database.

CREATE DATABASE namelayer CHARACTER SET = latin1

I think Max might be making some changes to simply truncate the length of the index fields, although that might cause trouble with existing installations.