Can not activate plugin, Foreign key constraint is incorrectly formed

[staeglis]

I have the same issue as described there:
#31 (comment)
#1

I've checked the tables adn tried the mentioned workarounds. All using innodb with utf8mb4. What else could cause the error?

Best,
Stefan

[staeglis]

I was able to create the table with this SQL command:

CREATE TABLE matomo_loginoidc_provider ( user VARCHAR( 100 ) NOT NULL, provider_user VARCHAR( 255 ) NOT NULL, provider VARCHAR( 255 ) NOT NULL, date_connected TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY ( provider_user, provider ), UNIQUE KEY user_provider ( user, provider ), FOREIGN KEY ( user ) REFERENCES matomo_user ( login ) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

[HatBeardZero]

I was able to create the table with this SQL command:

CREATE TABLE matomo_loginoidc_provider ( user VARCHAR( 100 ) NOT NULL, provider_user VARCHAR( 255 ) NOT NULL, provider VARCHAR( 255 ) NOT NULL, date_connected TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY ( provider_user, provider ), UNIQUE KEY user_provider ( user, provider ), FOREIGN KEY ( user ) REFERENCES matomo_user ( login ) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I am trying to get this going on mysql 8 and matomo 5.x, and i get this when i try to run that.

Referencing column 'user' and referenced column 'login' in foreign key constraint 'matomo_loginoidc_provider_ibfk_1' are incompatible.

any thoughts where i can look?

[nbaud]

For me the trick was to modify the charset very precisely to what it is in the database with utf8mb3_general_ci
Without that the above query would not work.

For instance, check your database with

SHOW FULL COLUMNS FROM piwik_user;
(in my case)
Then adapt your query according to what you see. It needs to match perfectly otherwise you will get that weird misleading error message.

This went fine and the plugin got activated after passing the following query:

MariaDB [matomo]> CREATE TABLE piwik_loginoidc_provider (
    ->     user VARCHAR(100) NOT NULL,
    ->     provider_user VARCHAR(255) NOT NULL,
    ->     provider VARCHAR(255) NOT NULL,
    ->     date_connected TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
    ->     PRIMARY KEY (provider_user, provider),
    ->     UNIQUE KEY user_provider (user, provider),
    ->     FOREIGN KEY (user) REFERENCES piwik_user (login) ON DELETE CASCADE
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;