yii2 recommended lengths in user table

Question

First of all I'm creating a yii2 advanced application, I'm NOT asking how to solve the 1071 error, Im asking how to twerk the migration script to better adjust it to my hosting database limitations

When uploading my site db from my localhost to a production server (shared hosting) i had the following error:

1071 - Specified key was too long; max key length is 767 bytes

So i had to rearrange my migration script with the following parameters:

$this->createTable('{{%user}}', [
            'id' => $this->primaryKey(),
            'username' => $this->string(60)->notNull()->unique(),
            'auth_key' => $this->string(32)->notNull(),
            'password_hash' => $this->string(70)->notNull(),
            'password_reset_token' => $this->string(70)->unique(),
            'email' => $this->string(60)->notNull()->unique(),

            'status' => $this->smallInteger()->notNull()->defaultValue(10),
            'created_at' => $this->integer()->notNull(),
            'updated_at' => $this->integer()->notNull(),
        ], $tableOptions);

But i had to guess the length of the auth_key, password_hash and password_reset_token fields

Are there recommended lengths for this fields and what are they?

Regards...

Possible duplicate of [#1071 - Specified key was too long; max key length is 767 bytes](https://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes) — Bizley, Feb 06 '18 at 06:47
This is more of an index problem (I've marked the duplicated question). I've got these fields with 255 size and it's fine. — Bizley, Feb 06 '18 at 06:49
Its not a duplicate, its more like an explanation because i had already solved the mysql error, i just want to optimize the index values for a yii2 advanced application — Chico3001, Feb 08 '18 at 03:55

score 0 · Answer 1 · answered Feb 06 '18 at 07:06

For hashed field depend of hashin algoritm for SHA-1 (One used by mysql) value is always 160 bit long, Depending of type of dat you use CHAR or BINARY then for type with 4 bit per character athey need 160/4 = 40 characters. But for 8 bit per character, you need a 160/8 = 20 character long field. If you use the VARCHAR this require an additional byte for the length of the fixed-length field. then

MD5 generates a 128-bit hash value. You can use CHAR(32) or BINARY(16) or VARCHAR(33)

SHA-1 generates a 160-bit hash value. You can use CHAR(40) or BINARY(20) or VARCHAR(41)

SHA-224 generates a 224-bit hash value. You can use CHAR(56) or BINARY(28) or VARCHAR(57)

SHA-256 generates a 256-bit hash value. You can use CHAR(64) or BINARY(32) or or VARCHAR(65)

But normally is used SHA-1

depend of your need .. and the related content you manage .. if also for these field you using hash then these have the same lenght otherwise depend of your data content length... — ScaisEdge, Feb 08 '18 at 07:18

yii2 recommended lengths in user table

1071 - Specified key was too long; max key length is 767 bytes

1 Answers1