search

dominik-th / matomo-plugin-LoginOIDC

external authentication services for matomo
https://plugins.matomo.org/LoginOIDC/
GNU General Public License v3.0
42 stars 30 forks source link

"loginoidc_provider" table was not created during plugin installation with MySQL in version 8 #31

Closed dejwsz closed 3 years ago

dejwsz commented 3 years ago

I used MySQL in version 8 as matomo database and in that case the "loginoidc_provider" table couldn't be created well. I did it by myself with this SQL:

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 ) CHARACTER SET utf8;

The original command from plugin ended with such error: ERROR 3780 (HY000): Referencing column 'user' and referenced column 'login' in foreign key constraint 'matomo_loginoidc_provider_ibfk_1' are incompatible.

dominik-th commented 3 years ago

Are you using MyISAM tables? https://matomo.org/faq/troubleshooting/faq_25610/

dejwsz commented 3 years ago

InnoDB - it is Percona XtraDB Cluster deployed in Openshift with mysql compatible pxc db image "percona-xtradb-cluster:8.0.20-11.3"

jakeh999 commented 3 years ago

I also have this issue with MariaDB 10.4 with InnoDB type, PHP 7.4, and Matomo 4. Running your query before install but with changing the character encoding to utf8mb4, the default for Matomo 4, did the trick for me. 

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
) CHARACTER SET utf8mb4;
C-Duv commented 3 years ago

I have the same issue when installing (for the first time) the plugin on Matomo v4.1 (which was recently updated from v3):

I get the "An error occurred" error page:

There was a problem installing the plugin LoginOIDC:

SQLSTATE[HY000]: General error: 1005 Can't create table matomo.matomo_loginoidc_provider (errno: 150 "Foreign key constraint is incorrectly formed")

If you want to hide this message you must remove the following line under the [Plugins] entry in your 'config/config.ini.php' file to disable this plugin. Plugins[] = LoginOIDC

If this plugin has already been installed, you must add the following line under the [PluginsInstalled] entry in your 'config/config.ini.php' file: PluginsInstalled[] = LoginOIDC

Plugin was indeed installed (listed on the module=CorePluginsAdmin&action=plugins page) but not "installed".

I solved it by:

dominik-th commented 3 years ago

I see two tasks which have to be done:

  1. Register the table from LoginOIDC in Matomo so the convert-to-utf8mb command also converts our table (#35)

New PHP events

Added new event Db.getTablesInstalled, plugins should use to register the tables they create.

https://github.com/matomo-org/matomo/blob/4.x-dev/CHANGELOG.md

  1. Use the DbHelper class to create tables so Matomo (hopefully) handles the charset issues for us https://developer.matomo.org/api-reference/Piwik/DbHelper#createtable This is also the way official Matomo plugins perform the table creation: https://github.com/matomo-org/tag-manager/blob/1f674678b461ce562bd33b0f3cf57230ac879d8b/Dao/ContainerReleaseDao.php#L21-L31
tekhnee commented 3 years ago

In my case, the error differs somewhat from the one reported by @C-Duv:

SQLSTATE[HY000]: General error: 3780 Referencing column 'user' and referenced column 'login' in foreign key constraint 'piwik_loginoidc_provider_ibfk_1' are incompatible.

@C-Duv's steps didn't help — which might suggest a problem beyond character encoding?

(Linking to #37, where this was first reported in greater detail.)

dominik-th commented 3 years ago

Should be fixed in the next version: https://github.com/dominik-th/matomo-plugin-LoginOIDC/commit/ed137deb617287532e9d5f84b8c20e6f226101cf