Fixed length BINARY with index results in SQL error since v4.7.0

[Phally]

Since migrations version 4.7.0 when trying to create a BINARY(20) field with an index on it, MySQL will fail as the migrations will create the field as a TINYBLOB now instead of BINARY or VARBINARY. When using BLOB indexes require lengths to tell MySQL how much of the BLOB it should index. There is a way to do it in MySQL, but that can't be done with migrations either (requires a custom query). Edit: which can be done with $this->index('hash')->setLimit(), but that should be baked then probably to prevent the error.

I read there were some changes in 4.7.0 regarding this. Is this behavior intentional and if so, what is the best way to do this then?

Prior to 4.7.0 it worked as expected. Also bake will still create the migration properly as a binary type.

I did quickly try the builtin backend and specifying a custom type using TypeFactory::map(), but it didn't seem to pick that up. I still got the same result at least.

Migration code (baked from a BINARY(20) column):

$this->table('files')
    ->addColumn('hash', 'binary', [
        'after' => 'hash',
        'default' => null,
        'length' => 20,
        'null' => true,
    ])
    ->addIndex(
        $this->index('hash')
            ->setName('hash')
            ->setType('unique')
    )
    ->update();

Error:

SQLSTATE[42000]: Syntax error or access violation: 1170 BLOB/TEXT column 'hash' used in key specification without a key length
Query: ALTER TABLE `files` ADD  UNIQUE KEY `hash` (`hash`);

[markstory]

This issue is caused by migrations adopting cakephp's database schema abstraction layer, and the translation layer in migrations which currently fits length attributes for blob types into tiny/small/normal/long.

which can be done with $this->index('hash')->setLimit(), but that should be baked then probably to prevent the error.

I don't think bake will be able to do that today. When baking new migrations for existing tables the option parser doesn't have a way for index lengths to be defined, and when generating a snapshot from existing schema, the index doesn't have a length defined, so it won't be part of the reflection state that bake uses to create new migration files.

We can fix this issue in the translation layer between migrations and cakephp/database as arbitrary lengths are supported for binary column types.

[dereuromark]

@markstory You beat me to it ;)

[Phally]

That was quick! Ah gotcha, yeah fair enough. The fix works well.

Slight difference in behavior as it now creates a VARBINARY. Before it created a BINARY, but that's obviously no problem.

Thank you!

[dereuromark]

@Phally Good point. This might require a core change - cakephp/cakephp#19207