BLOB/TEXT column 'id' used in key specification without a key length

[darkghosthunter]

BLOB/TEXT column 'id' used in key specification without a key length

[hiszpek]

When I tried to use $table->string('id', 255);, when registering a device I had an error about an incorrect value being inserted into column 'id'. What I did was leave it as tinyblob, remove the $table->primary(['id', 'user_id']); fragment, and add a raw statement after creating the table:

DB::statement('ALTER TABLE web_authn_credentials ADD PRIMARY KEY (id(255), user_id)');

Notice the added (255) after id.

Using MySQL 5.7.29

[DarkGhostHunter]

https://github.com/DarkGhostHunter/Larapass/releases/tag/v3.0.0

[DarkGhostHunter]

As this StackOverflow answer, is a problem on MySQL end.

The solution to the problem is to remove the TEXT or BLOB column from the index or unique constraint or set another field as primary key. If you can't do that, and wanting to place a limit on the TEXT or BLOB column, try to use VARCHAR type and place a limit of length on it. By default, VARCHAR is limited to a maximum of 255 characters and its limit must be specified implicitly within a bracket right after its declaration, i.e VARCHAR(200) will limit it to 200 characters long only.

[Kehet]

If MySQL isn't supported, there should be mention about it in readme.

[DarkGhostHunter]

Better, I can detect the driver, and if it's MySQL, change the column type to varchar(255).

It will add it next version. In the meantime, you should do the same.

[nguyenhiepvan]

You can change it to bigincreasementInteger, and add index to user_id

[DarkGhostHunter]

You can change it to bigincreasementInteger, and add index to user_id

An user can have more than 1 credentials. Doing that breaks it.

What the client returns as credential_id is essentially anything. Since it's encoded in BASE64, we can just use binary. Doing an "hey, since MySQL shits its pants on indexing binary keys, I will use a varchar(255) instead" it's not bad but there is a problem when trying to query for a binary string into a text string.

Now, the idea behind a composite primary key is that queries use the credential id and the user_id to identify each row.

I have pushed a fix on dev-master. I don't have access to an MySQL instance, so if anybody can test, I can push it to the next version.

[joshrowland]

I updated the 2020_04_02_000000_create_web_authn_tables.php file to your new update today and used the php artisan migrate command. It gave this error.
SQLSTATE[42000]: Syntax error or access violation: 1170 BLOB/TEXT column 'id' used in key specification without a key length (SQL: alter table web_authn_credentialsadd primary keyweb_authn_credentials_id_user_id_primary(id, user_id))
and
PDOException::("SQLSTATE[42000]: Syntax error or access violation: 1170 BLOB/TEXT column 'id' used in key specification without a key length")
I'm not familiar with tinyblob to know what it needs.

[DarkGhostHunter]

As MySQL says:

• For indexes on BLOB and TEXT columns, you must specify an index prefix length.

It's the index prefix length. How we do that with Laravel? The docs say to use the static call:

Schema::defaultStringLength(255):

I think 255 should suffice, based on the assumption that no authenticator would push a larger than 255 characters string.

Can someone test this? Again, I'm unable to test on MySQL, at least on the short term.

[felipe-machado]

I was able to make it work using

$table->string('id', 255);

As MySQL says:

• For indexes on BLOB and TEXT columns, you must specify an index prefix length.

It's the index prefix length. How we do that with Laravel? The docs say to use the static call:

Schema::defaultStringLength(255):

I think 255 should suffice, based on the assumption that no authenticator would push a larger than 255 characters string.

Can someone test this? Again, I'm unable to test on MySQL, at least on the short term.

I tried setting the defaultStringLength to 255 but the same error persisted.

[nguyenhiepvan]

$table->string('id', 255);

it's work, @DarkGhostHunter i've tried your update in mysql but it got error, because of MySQL assumes 3 bytes per utf8 character. 255 characters is the maximum index size you can specify per column, because 256x3=768, which breaks the 767 byte limit., you should add length of column

[DarkGhostHunter]

Okay, I've put an alpha for v3.0.

Check it out for any problems. I'll make a full project with it to test it thoroughly.

[DarkGhostHunter]

Fixed, id column now is string VARCHAR. Breaking change. But works on any database.