mysql.initial.sql throws errors on MariaDB 10.3.39

[nextgen-networks]

Aloha!

Found your great successor of ident_switch a couple of minutes ago and was eager to test it.

Sadly the composer install throws an error in the last step:

Updated local config at /var/www/vhosts/CUSTOMER/DOMAIN/config/config.inc.php
Creating package config file
Running database initialization script for identy_switch
Creating database schema... ERROR: [1005] Can't create table `roundcubemail`.`identy_switch` (errno: 121 "Duplicate key on write or update") (SQL Query: CREATE TABLE IF NOT EXISTS `identy_switch`(
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT(10) UNSIGNED NOT NULL,
    `iid` INT(10) UNSIGNED NOT NULL,
    `label` VARCHAR(32),
    `flags` INT NOT NULL DEFAULT 0,
    `imap_user` VARCHAR(64),
    `imap_pwd` VARCHAR(64),
    `imap_host` VARCHAR(64),
    `imap_port` SMALLINT DEFAULT 0,
    `imap_delim` CHAR(1),
    `newmail_check` SMALLINT DEFAULT 300,
    `notify_timeout` SMALLINT DEFAULT 10,
    `smtp_host` VARCHAR(64),
    `smtp_port` SMALLINT DEFAULT 0,
    `drafts` VARCHAR(64) DEFAULT '',
    `sent` VARCHAR(64) DEFAULT '',
    `junk` VARCHAR(64) DEFAULT '',
    `trash` VARCHAR(64) DEFAULT '',
    UNIQUE KEY `user_id_label`(`user_id`, `label`),
    CONSTRAINT `fk_user_id` FOREIGN KEY(`user_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_identity_id` FOREIGN KEY(`iid`) REFERENCES `identities`(`identity_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`id`),
    INDEX `IX_identy_switch_user_id`(`user_id`),
    INDEX `IX_identy_switch_iid`(`iid`)
))
[FAILED]
ERROR: [1005] Can't create table `roundcubemail`.`identy_switch` (errno: 121 "Duplicate key on write or update")

OS: Ubuntu 20.04.6 LTS DB-Server: 10.3.39-MariaDB-0ubuntu0.20.04.2 - Ubuntu 20.04 DB-Server collation: UTF-8 Unicode (utf8mb4) DB-Server table collation of "roundcubemail": utf8_general_ci

shady workaround: modify SQL statement

CREATE TABLE IF NOT EXISTS `identy_switch`(
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT(10) UNSIGNED NOT NULL,
    `iid` INT(10) UNSIGNED NOT NULL,
    `label` VARCHAR(32),
    `flags` INT NOT NULL DEFAULT 0,
    `imap_user` VARCHAR(64),
    `imap_pwd` VARCHAR(64),
    `imap_host` VARCHAR(64),
    `imap_port` SMALLINT DEFAULT 0,
    `imap_delim` CHAR(1),
    `newmail_check` SMALLINT DEFAULT 300,
    `notify_timeout` SMALLINT DEFAULT 10,
    `smtp_host` VARCHAR(64),
    `smtp_port` SMALLINT DEFAULT 0,
    `drafts` VARCHAR(64) DEFAULT '',
    `sent` VARCHAR(64) DEFAULT '',
    `junk` VARCHAR(64) DEFAULT '',
    `trash` VARCHAR(64) DEFAULT '',
    UNIQUE KEY `user_id_label`(`user_id`, `label`),
    PRIMARY KEY(`id`),
    INDEX `IX_identy_switch_user_id`(`user_id`),
    INDEX `IX_identy_switch_iid`(`iid`)
);

[toteph42]

Hi, thank you for your input. Will check it / integrate into bundle when I'm back from my journey...

[CodeShakingSheep]

I'm running into the same issue when installing the plugin (when wanting to migrate from ident_switch plugin). It's because of the constraint names which are equal to the constraint names of the ident_switch table. I could successfully create the table like this (without dropping the constraints, just renaming them).

CREATE TABLE IF NOT EXISTS `identy_switch`(
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT(10) UNSIGNED NOT NULL,
    `iid` INT(10) UNSIGNED NOT NULL,
    `label` VARCHAR(32),
    `flags` INT NOT NULL DEFAULT 0,
    `imap_user` VARCHAR(64),
    `imap_pwd` VARCHAR(64),
    `imap_host` VARCHAR(64),
    `imap_port` SMALLINT DEFAULT 0,
    `imap_delim` CHAR(1),
    `newmail_check` SMALLINT DEFAULT 300,
    `notify_timeout` SMALLINT DEFAULT 10,
    `smtp_host` VARCHAR(64),
    `smtp_port` SMALLINT DEFAULT 0,
    `drafts` VARCHAR(64) DEFAULT '',
    `sent` VARCHAR(64) DEFAULT '',
    `junk` VARCHAR(64) DEFAULT '',
    `trash` VARCHAR(64) DEFAULT '',
    UNIQUE KEY `user_id_label`(`user_id`, `label`),
    CONSTRAINT `fk_user_id2` FOREIGN KEY(`user_id`) REFERENCES `users`(`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_identity_id2` FOREIGN KEY(`iid`) REFERENCES `identities`(`identity_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`id`),
    INDEX `IX_identy_switch_user_id`(`user_id`),
    INDEX `IX_identy_switch_iid`(`iid`)
)