Play! & MariaDB - index to large 767 bytes when using InnoDB

Question

please note: play newbie here

System Info:

OS: Arch Linux x64

4.12.3-1-ARCH

Database: MariaDB

mysql Ver 15.1 Distrib 10.1.25-MariaDB, for Linux (x86_64) using readline 5.1

Problem:

I am attemptig to create a database with tables from associated model classes, where the index/id key is to long.

Error:

An evolution has not been applied properly. Please check the problem and resolve it manually before marking it as resolved.

We got the following error: Index column size too large. The maximum column size is 767 bytes. [ERROR:1709, SQLSTATE:HY000], while trying to run this SQL script:

# --- Rev:1,Ups - 7feba1a
create table address (
    address_id                    bigint auto_increment not null,
    unit_number                   varchar(255),
    street_name                   varchar(255),
    community_name                varchar(255),
    is_community                  tinyint(1) default 0,
    constraint pk_address primary key (address_id)
);


create table user (
    user_id                       bigint auto_increment not null,
    name                          varchar(255),
    surname                       varchar(255),
    password                      varchar(255),
    email                         varchar(255),
    cell_number                   varchar(255),
    email_verified                tinyint(1) default 0,
    crsftoken                     varchar(255),
    token                         varchar(255),
    constraint pk_user primary key (user_id)
);

A few models listed below:

Address model

@Entity 
public class Address extends Model {

    @Id
    @Constraints.Required
    @GeneratedValue
    private Long addressId;
    @Constraints.Required
    private String unitNumber;
    @Constraints.Required
    private String streetName;
    private String communityName;
    private Boolean isCommunity;

    public static Finder<String, Address> find = new Finder<String, Address>(Address.class);

User model:

@Entity
public class User extends Model {

    @Id
    @Constraints.MinLength(10)
    @Constraints.MaxLength(10)
    @GeneratedValue
    private Long userId;

    private String name;
    private String surname;

    @Constraints.Required
    private String password;

    @Constraints.Email
    @Constraints.Required
    private String email;

    @Constraints.Required
    @Constraints.Pattern("[0]\\d{2}[- ]{0,1}\\d{3}[- ]{0,1}\\d{4}")
    private String cellNumber;
    private Boolean emailVerified = false;

    private String CRSFToken;

    public static Finder<String, User> find = new Finder<String, User>(User.class);

What I have discovered:

Researching this error, it seems to imply that I should: 1. use the innoDB engine (used by default in MariaDB) 2. set the innodb_large_prefix 3. use the barracuda file format 4. set table (with innoDB engine) row formatting with ROW_FORMAT to DYNAMIC or COMPRESSED

These solutions were suggested on SO:

here
here
here (including setting the collate, but I couldn't find out how to do this in Play!)

and an independent blog:

here (most helpful of all)

More information:

I attempted setting these:

MariaDB [(none)]> CREATE DATABASE eatalotdb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> set GLOBAL storage_engine='InnoDb';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> set GLOBAL innodb_large_prefix = ON;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> set global innodb_file_format = BARRACUDA;
Query OK, 0 rows affected (0.00 sec)

I could not set row format, since no tables existed at the moment, but when tables are created by the Evolutions SQL script, the error below is presented.

Question:

I have ended up at page 3 of Google search results, yet not found a solution. How can I resolve this error?

p.s. As a side question, I am struggling to find information and documentation on Play! Framework, besides the standard documentation provided by Play!, where else can I learn more with examples, understanding, etc?

`innodb_file_per_table` has to be enabled too (although it is enabled by default). You should set the values in your server config, as otherwise, they reset to defaults after restart, and ANY alter table without explicitly specifying the format will reset the format of the altered table to the (maybe) wrong defaults. (I don't know the play! framework, so there might be an option to do it there though) Also: the sql script would not result in an error (it contains no large indexes), so maybe check if you need the problematic index, or need that big varchars (e.g. 255 chars for `unit_number`). — Solarflare, Aug 11 '17 at 10:33

score 0 · Answer 1 · answered Aug 18 '17 at 03:19

See the end of
http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

Reconfigure (if staying with 5.6.3 - 5.7.6 or the equivalent MariaDB) -- 4 things to change: Barracuda + innodb_file_per_table + innodb_large_prefix + dynamic or compressed.

Please provide SHOW CREATE TABLE and SHOW TABLE STATUS to help verify some of those.

score 0 · Accepted Answer · answered Dec 19 '17 at 22:46

Since I was using Archlinux, the only MySQL package available were from MariaDB or percona-server.

Neither of these statisfy the requirements of a standard Ubuntu type MySQL install.

Solution:

Use a remote server installed using the original MySQL or switch to an OS supporting the original MySQL installation

Play! & MariaDB - index to large 767 bytes when using InnoDB

2 Answers2