MySQL/Percona sql strict mode issue

[dev-rke]

Hi,

i am using TYPO3 7.6.24 with ig_ldap_sso_auth 3.3.0.

When i run updating database tables within install tool (from 3.2.2 to 3.3.0), i get the following error:

array(4 items)
   caller => 'TYPO3\CMS\Core\Database\DatabaseConnection::admin_query' (55 chars)
   ERROR => 'BLOB/TEXT column 'tx_igldapssoauth_dn' used in key specification without a k
      ey length' (85 chars)
   lastBuiltQuery => 'ALTER TABLE be_groups ADD KEY tx_igldapssoauth_dn (tx_igldapssoauth_dn);' (72 chars)
   debug_backtrace => '{closure}#109 // TYPO3\CMS\Install\Http\Application->run#108 // TYPO3\CMS\Co
      re\Core\Bootstrap->handleRequest#74 // TYPO3\CMS\Install\Http\RequestHandler
      ->handleRequest#307 // TYPO3\CMS\Install\Controller\ToolController->execute#
      64 // TYPO3\CMS\Install\Controller\ToolController->dispatchAuthenticationAct
      ions#64 // TYPO3\CMS\Install\Controller\Action\AbstractAction->handle#192 //
       TYPO3\CMS\Install\Controller\Action\Tool\UpgradeWizard->executeAction#92 //
       TYPO3\CMS\Install\Controller\Action\Tool\UpgradeWizard->performUpdate#69 //
       TYPO3\CMS\Install\Updates\InitialDatabaseSchemaUpdate->performUpdate#200 //
       TYPO3\CMS\Core\Database\DatabaseConnection->admin_query#159 // TYPO3\CMS\Co
      re\Database\DatabaseConnection->debug#1513' (726 chars)

I fixed this locally by changing ext_tables.sql to

#
# Table structure for table 'be_groups'
#
CREATE TABLE be_groups (
    tx_igldapssoauth_dn varchar(255) DEFAULT '' NOT NULL,

    KEY title (title),
    KEY tx_igldapssoauth_dn (tx_igldapssoauth_dn(64))
);

#
# Table structure for table 'be_users'
#
CREATE TABLE be_users (
    tx_igldapssoauth_dn varchar(255) DEFAULT '' NOT NULL,
    tx_igldapssoauth_id int(11) unsigned DEFAULT '0' NOT NULL,

    KEY tx_igldapssoauth_dn (tx_igldapssoauth_dn(64))
);

#
# Table structure for table 'fe_groups'
#
CREATE TABLE fe_groups (
    tx_igldapssoauth_dn varchar(255) DEFAULT '' NOT NULL,

    KEY title (title),
    KEY tx_igldapssoauth_dn (tx_igldapssoauth_dn(64))
);

#
# Table structure for table 'fe_users'
#
CREATE TABLE fe_users (
    tx_igldapssoauth_dn varchar(255) DEFAULT '' NOT NULL,
    tx_igldapssoauth_id int(11) unsigned DEFAULT '0' NOT NULL,

    KEY tx_igldapssoauth_dn (tx_igldapssoauth_dn(64))
);

I changed KEY tx_igldapssoauth_dn (tx_igldapssoauth_dn) to KEY tx_igldapssoauth_dn (tx_igldapssoauth_dn(64))

I assume that this issue does only pop up when mysql is in strict mode (as it is in percona by default): https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-strict

Might it be possible to release a fix for this? Unfortunately key length is limited to 255 bytes (not chars), so you are unable to index the whole string. See more details e.g. here: https://stackoverflow.com/questions/1827063/mysql-error-key-specification-without-a-key-length

Thank you for this great extension, it is really stable and simplifies us the login for thousands of editors! :-)

[xperseguers]

Just to be complete, another solution would be to enable large_key_prefix:

https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html#innodb-maximums-minimums

- If innodb_large_prefix is enabled (the default), the index key prefix limit is 3072 bytes for
   InnoDB tables that use DYNAMIC or COMPRESSED row format.
- If innodb_large_prefix is disabled, the index key prefix limit is 767 bytes for tables of any
   row format.

[dev-rke]

Hi, thanks for the input, i was not aware of that. But i think it should work with a default configuration as well. ;-)