search

codeigniter4 / shield

Authentication and Authorization for CodeIgniter 4
https://shield.codeigniter.com
MIT License
351 stars 123 forks source link

Bug: Specified key was too long; max key length is 767 bytes #1102

Closed gridphp closed 3 months ago

gridphp commented 3 months ago

PHP Version

8.3

CodeIgniter4 Version

4.5

Shield Version

codeigniter4/shield (v1.0.3)

Which operating systems have you tested for this bug?

Windows

Which server did you use?

apache

Database

Mysql 8.2

Did you customize Shield?

No

What happened?

After installing shield with composer when i setup using spark, it ended on following error.

Running all new migrations... [CodeIgniter\Database\Exceptions\DatabaseException] Specified key was too long; max key length is 767 bytes at SYSTEMPATH\Database\BaseConnection.php:676

Steps to Reproduce

1) Setup CI4 (i did manual setup) 2) Install Shield with composer (it went fine) composer require codeigniter4/shield 3) Setup Shield with spark php spark shield:setup

After creating the required config files and emails, it show following error. .... Running all new migrations... [CodeIgniter\Database\Exceptions\DatabaseException] Specified key was too long; max key length is 767 bytes at SYSTEMPATH\Database\BaseConnection.php:676

Expected Output

Should work as normal

Anything else?

Full process log ..........

  Run `spark migrate --all` now? [y, n]: y
Running all new migrations...
[CodeIgniter\Database\Exceptions\DatabaseException]
Specified key was too long; max key length is 767 bytes
at SYSTEMPATH\Database\BaseConnection.php:676

  Caused by:
  [CodeIgniter\Database\Exceptions\DatabaseException]
  Specified key was too long; max key length is 767 bytes
  at SYSTEMPATH\Database\MySQLi\Connection.php:313

  Caused by:
  [mysqli_sql_exception]
  Specified key was too long; max key length is 767 bytes
  at SYSTEMPATH\Database\MySQLi\Connection.php:308

Backtrace:
  1    SYSTEMPATH\Database\MySQLi\Connection.php:308
       mysqli()->query('CREATE TABLE `auth_identities` (
        `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
        `user_id` int(11) UNSIGNED NOT NULL,
        `type` varchar(255) NOT NULL,
        `name` varchar(255) NULL,
        `secret` varchar(255) NOT NULL,
        `secret2` varchar(255) NULL,
        `expires` datetime NULL,
        `extra` text NULL,
        `force_reset` tinyint(1) NOT NULL DEFAULT 0,
        `last_used_at` datetime NULL,
        `created_at` datetime NULL,
        `updated_at` datetime NULL,
        CONSTRAINT `pk_auth_identities` PRIMARY KEY(`id`),
        CONSTRAINT `auth_identities_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
        UNIQUE KEY `type_secret` (`type`, `secret`),
        KEY `user_id` (`user_id`)
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci', 0)

  2    SYSTEMPATH\Database\BaseConnection.php:722
       CodeIgniter\Database\MySQLi\Connection()->execute('CREATE TABLE `auth_identities` (
        `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
        `user_id` int(11) UNSIGNED NOT NULL,
        `type` varchar(255) NOT NULL,
        `name` varchar(255) NULL,
        `secret` varchar(255) NOT NULL,
        `secret2` varchar(255) NULL,
        `expires` datetime NULL,
        `extra` text NULL,
        `force_reset` tinyint(1) NOT NULL DEFAULT 0,
        `last_used_at` datetime NULL,
        `created_at` datetime NULL,
        `updated_at` datetime NULL,
        CONSTRAINT `pk_auth_identities` PRIMARY KEY(`id`),
        CONSTRAINT `auth_identities_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
        UNIQUE KEY `type_secret` (`type`, `secret`),
        KEY `user_id` (`user_id`)
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci')

  3    SYSTEMPATH\Database\BaseConnection.php:636
       CodeIgniter\Database\BaseConnection()->simpleQuery('CREATE TABLE `auth_identities` (
        `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
        `user_id` int(11) UNSIGNED NOT NULL,
        `type` varchar(255) NOT NULL,
        `name` varchar(255) NULL,
        `secret` varchar(255) NOT NULL,
        `secret2` varchar(255) NULL,
        `expires` datetime NULL,
        `extra` text NULL,
        `force_reset` tinyint(1) NOT NULL DEFAULT 0,
        `last_used_at` datetime NULL,
        `created_at` datetime NULL,
        `updated_at` datetime NULL,
        CONSTRAINT `pk_auth_identities` PRIMARY KEY(`id`),
        CONSTRAINT `auth_identities_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
        UNIQUE KEY `type_secret` (`type`, `secret`),
        KEY `user_id` (`user_id`)
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci')

  4    SYSTEMPATH\Database\Forge.php:570
       CodeIgniter\Database\BaseConnection()->query('CREATE TABLE `auth_identities` (
        `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
        `user_id` int(11) UNSIGNED NOT NULL,
        `type` varchar(255) NOT NULL,
        `name` varchar(255) NULL,
        `secret` varchar(255) NOT NULL,
        `secret2` varchar(255) NULL,
        `expires` datetime NULL,
        `extra` text NULL,
        `force_reset` tinyint(1) NOT NULL DEFAULT 0,
        `last_used_at` datetime NULL,
        `created_at` datetime NULL,
        `updated_at` datetime NULL,
        CONSTRAINT `pk_auth_identities` PRIMARY KEY(`id`),
        CONSTRAINT `auth_identities_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
        UNIQUE KEY `type_secret` (`type`, `secret`),
        KEY `user_id` (`user_id`)
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci')

  5    VENDORPATH\codeigniter4\shield\src\Database\Migrations\2020-12-28-223112_create_auth_tables.php:187
       CodeIgniter\Database\Forge()->createTable('auth_identities', false, [...])

  6    VENDORPATH\codeigniter4\shield\src\Database\Migrations\2020-12-28-223112_create_auth_tables.php:84
       CodeIgniter\Shield\Database\Migrations\CreateAuthTables()->createTable('auth_identities')

  7    SYSTEMPATH\Database\MigrationRunner.php:867
       CodeIgniter\Shield\Database\Migrations\CreateAuthTables()->up()

  8    SYSTEMPATH\Database\MigrationRunner.php:183
       CodeIgniter\Database\MigrationRunner()->migrate('up', Object(stdClass))

  9    SYSTEMPATH\Commands\Database\Migrate.php:85
       CodeIgniter\Database\MigrationRunner()->latest(null)

 10    VENDORPATH\codeigniter4\shield\src\Commands\Setup.php:420
       CodeIgniter\Commands\Database\Migrate()->run([...])

 11    VENDORPATH\codeigniter4\shield\src\Commands\Setup.php:97
       CodeIgniter\Shield\Commands\Setup()->runMigrations()

 12    VENDORPATH\codeigniter4\shield\src\Commands\Setup.php:82
       CodeIgniter\Shield\Commands\Setup()->publishConfig()

 13    SYSTEMPATH\CLI\Commands.php:70
       CodeIgniter\Shield\Commands\Setup()->run([])

 14    SYSTEMPATH\CLI\Console.php:48
       CodeIgniter\CLI\Commands()->run('shield:setup', [])

 15    SYSTEMPATH\Boot.php:338
       CodeIgniter\CLI\Console()->run()

 16    SYSTEMPATH\Boot.php:104
       CodeIgniter\Boot::runCommand(Object(CodeIgniter\CLI\Console))

 17    ROOTPATH\spark:84
       CodeIgniter\Boot::bootSpark(Object(Config\Paths))
kenjis commented 3 months ago

At least, I cannot reproduce with MySQL 8.0.36 and CI 4.5.1 and Shield 1.0.3.

datamweb commented 3 months ago

I could not reproduce this issue either.

P:\C4.5.1
php spark migrate --all

CodeIgniter v4.5.1 Command Line Tool - Server Time: 2024-04-18 03:33:54 UTC+00:00

Running all new migrations...
        Running: (CodeIgniter\Shield) 2020-12-28-223112_CodeIgniter\Shield\Database\Migrations\CreateAuthTables
        Running: (CodeIgniter\Settings) 2021-07-04-041948_CodeIgniter\Settings\Database\Migrations\CreateSettingsTable
        Running: (CodeIgniter\Settings) 2021-11-14-143905_CodeIgniter\Settings\Database\Migrations\AddContextColumn
Migrations complete.

P:\C4.5.1
mysql -V
mysql  Ver 8.2.0 for Win64 on x86_64 (MySQL Community Server - GPL)

P:\C4.5.1
php -v
PHP 8.3.0 (cli) (built: Nov 21 2023 17:48:31) (ZTS Visual C++ 2019 x64)
Copyright (c) The PHP Group
Zend Engine v4.3.0, Copyright (c) Zend Technologies

Try replacing the following code in file vendor\codeigniter4\shield\src\Database\Migrations\2020-12-28-223112_create_auth_tables.php.

<?php

declare(strict_types=1);

/**
 * This file is part of CodeIgniter Shield.
 *
 * (c) CodeIgniter Foundation <admin@codeigniter.com>
 *
 * For the full copyright and license information, please view
 * the LICENSE file that was distributed with this source code.
 */

namespace CodeIgniter\Shield\Database\Migrations;

use CodeIgniter\Database\Forge;
use CodeIgniter\Database\Migration;
use CodeIgniter\Shield\Config\Auth;

class CreateAuthTables extends Migration
{
    /**
     * Auth Table names
     */
    private array $tables;

    private array $attributes;

    public function __construct(?Forge $forge = null)
    {
        /** @var Auth $authConfig */
        $authConfig = config('Auth');

        if ($authConfig->DBGroup !== null) {
            $this->DBGroup = $authConfig->DBGroup;
        }

        parent::__construct($forge);

        $this->tables     = $authConfig->tables;
        $this->attributes = ($this->db->getPlatform() === 'MySQLi') ? ['ENGINE' => 'InnoDB'] : [];
    }

    public function up(): void
    {
        // Users Table
        $this->forge->addField([
            'id'             => ['type' => 'int', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'username'       => ['type' => 'varchar', 'constraint' => 30, 'null' => true],
            'status'         => ['type' => 'varchar', 'constraint' => 191, 'null' => true],
            'status_message' => ['type' => 'varchar', 'constraint' => 191, 'null' => true],
            'active'         => ['type' => 'tinyint', 'constraint' => 1, 'null' => 0, 'default' => 0],
            'last_active'    => ['type' => 'datetime', 'null' => true],
            'created_at'     => ['type' => 'datetime', 'null' => true],
            'updated_at'     => ['type' => 'datetime', 'null' => true],
            'deleted_at'     => ['type' => 'datetime', 'null' => true],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addUniqueKey('username');
        $this->createTable($this->tables['users']);

        /*
         * Auth Identities Table
         * Used for storage of passwords, access tokens, social login identities, etc.
         */
        $this->forge->addField([
            'id'           => ['type' => 'int', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'user_id'      => ['type' => 'int', 'constraint' => 11, 'unsigned' => true],
            'type'         => ['type' => 'varchar', 'constraint' => 191],
            'name'         => ['type' => 'varchar', 'constraint' => 191, 'null' => true],
            'secret'       => ['type' => 'varchar', 'constraint' => 191],
            'secret2'      => ['type' => 'varchar', 'constraint' => 191, 'null' => true],
            'expires'      => ['type' => 'datetime', 'null' => true],
            'extra'        => ['type' => 'text', 'null' => true],
            'force_reset'  => ['type' => 'tinyint', 'constraint' => 1, 'default' => 0],
            'last_used_at' => ['type' => 'datetime', 'null' => true],
            'created_at'   => ['type' => 'datetime', 'null' => true],
            'updated_at'   => ['type' => 'datetime', 'null' => true],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addUniqueKey(['type', 'secret']);
        $this->forge->addKey('user_id');
        $this->forge->addForeignKey('user_id', $this->tables['users'], 'id', '', 'CASCADE');
        $this->createTable($this->tables['identities']);

        /**
         * Auth Login Attempts Table
         * Records login attempts. A login means users think it is a login.
         * To login, users do action(s) like posting a form.
         */
        $this->forge->addField([
            'id'         => ['type' => 'int', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'ip_address' => ['type' => 'varchar', 'constraint' => 191],
            'user_agent' => ['type' => 'varchar', 'constraint' => 191, 'null' => true],
            'id_type'    => ['type' => 'varchar', 'constraint' => 191],
            'identifier' => ['type' => 'varchar', 'constraint' => 191],
            'user_id'    => ['type' => 'int', 'constraint' => 11, 'unsigned' => true, 'null' => true], // Only for successful logins
            'date'       => ['type' => 'datetime'],
            'success'    => ['type' => 'tinyint', 'constraint' => 1],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addKey(['id_type', 'identifier']);
        $this->forge->addKey('user_id');
        // NOTE: Do NOT delete the user_id or identifier when the user is deleted for security audits
        $this->createTable($this->tables['logins']);

        /*
         * Auth Token Login Attempts Table
         * Records Bearer Token type login attempts.
         */
        $this->forge->addField([
            'id'         => ['type' => 'int', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'ip_address' => ['type' => 'varchar', 'constraint' => 191],
            'user_agent' => ['type' => 'varchar', 'constraint' => 191, 'null' => true],
            'id_type'    => ['type' => 'varchar', 'constraint' => 191],
            'identifier' => ['type' => 'varchar', 'constraint' => 191],
            'user_id'    => ['type' => 'int', 'constraint' => 11, 'unsigned' => true, 'null' => true], // Only for successful logins
            'date'       => ['type' => 'datetime'],
            'success'    => ['type' => 'tinyint', 'constraint' => 1],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addKey(['id_type', 'identifier']);
        $this->forge->addKey('user_id');
        // NOTE: Do NOT delete the user_id or identifier when the user is deleted for security audits
        $this->createTable($this->tables['token_logins']);

        /*
         * Auth Remember Tokens (remember-me) Table
         * @see https://paragonie.com/blog/2015/04/secure-authentication-php-with-long-term-persistence
         */
        $this->forge->addField([
            'id'              => ['type' => 'int', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'selector'        => ['type' => 'varchar', 'constraint' => 191],
            'hashedValidator' => ['type' => 'varchar', 'constraint' => 191],
            'user_id'         => ['type' => 'int', 'constraint' => 11, 'unsigned' => true],
            'expires'         => ['type' => 'datetime'],
            'created_at'      => ['type' => 'datetime', 'null' => false],
            'updated_at'      => ['type' => 'datetime', 'null' => false],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addUniqueKey('selector');
        $this->forge->addForeignKey('user_id', $this->tables['users'], 'id', '', 'CASCADE');
        $this->createTable($this->tables['remember_tokens']);

        // Groups Users Table
        $this->forge->addField([
            'id'         => ['type' => 'int', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'user_id'    => ['type' => 'int', 'constraint' => 11, 'unsigned' => true],
            'group'      => ['type' => 'varchar', 'constraint' => 191, 'null' => false],
            'created_at' => ['type' => 'datetime', 'null' => false],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addForeignKey('user_id', $this->tables['users'], 'id', '', 'CASCADE');
        $this->createTable($this->tables['groups_users']);

        // Users Permissions Table
        $this->forge->addField([
            'id'         => ['type' => 'int', 'constraint' => 11, 'unsigned' => true, 'auto_increment' => true],
            'user_id'    => ['type' => 'int', 'constraint' => 11, 'unsigned' => true],
            'permission' => ['type' => 'varchar', 'constraint' => 191, 'null' => false],
            'created_at' => ['type' => 'datetime', 'null' => false],
        ]);
        $this->forge->addPrimaryKey('id');
        $this->forge->addForeignKey('user_id', $this->tables['users'], 'id', '', 'CASCADE');
        $this->createTable($this->tables['permissions_users']);
    }

    // --------------------------------------------------------------------

    public function down(): void
    {
        $this->db->disableForeignKeyChecks();

        $this->forge->dropTable($this->tables['logins'], true);
        $this->forge->dropTable($this->tables['token_logins'], true);
        $this->forge->dropTable($this->tables['remember_tokens'], true);
        $this->forge->dropTable($this->tables['identities'], true);
        $this->forge->dropTable($this->tables['groups_users'], true);
        $this->forge->dropTable($this->tables['permissions_users'], true);
        $this->forge->dropTable($this->tables['users'], true);

        $this->db->enableForeignKeyChecks();
    }

    private function createTable(string $tableName): void
    {
        $this->forge->createTable($tableName, false, $this->attributes);
    }
}
gridphp commented 3 months ago

New migration script worked smooth. Most likely composer build need update as well, it shows 'constraint' => 255.

I diff both files and following is the difference for varchar field. 'constraint' => 255 changed to 'constraint' => 191

Thank you both.

>php spark migrate --all

CodeIgniter v4.5.1 Command Line Tool - Server Time: 2024-04-18 03:58:00 UTC+00:00

Running all new migrations...
        Running: (CodeIgniter\Shield) 2020-12-28-223112_CodeIgniter\Shield\Database\Migrations\CreateAuthTables
        Running: (CodeIgniter\Settings) 2021-07-04-041948_CodeIgniter\Settings\Database\Migrations\CreateSettingsTable
        Running: (CodeIgniter\Settings) 2021-11-14-143905_CodeIgniter\Settings\Database\Migrations\AddContextColumn
Migrations complete.
kenjis commented 3 months ago

@gridphp This is your InnoDB Row Format issue. Run show table status; to check.

The index key prefix length limit is 3072 bytes for InnoDB tables that use DYNAMIC or COMPRESSED row format.

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 191 characters on a TEXT or VARCHAR column, assuming a utf8mb4 character set and the maximum of 4 bytes for each character.

https://dev.mysql.com/doc/refman/8.0/en/innodb-limits.html

datamweb commented 3 months ago

CI 4.5 uses utf8mb4. See https://github.com/codeigniter4/CodeIgniter4/pull/7920

For me Run show table status;

Screenshot 2024-04-18 085047

gridphp commented 3 months ago

Thanks, I understand better now.