Matomo: Updating from 2.16 to 3.0.0b1 fails: piwik_site_setting doesn't exist

[dertuxmalwieder]

I've been running Piwik since version 1.something, there never was a major problem even in beta versions. However, trying to update to 3.0.0b1 broke everything:

/var/www/virtual/myusername/html/piwik/core/Updates/3.0.0-b1.php: Error trying to execute the migration 'ALTER TABLE piwik_site_setting ADD COLUMN plugin_name VARCHAR(60) NOT NULL AFTER idsite;'. The error was: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'myusername_piwik.piwik_site_setting' doesn't exist

Now what should I do?

[mattab]

The table should have been created during 2.14.0 upgrade few months ago.

To fix this issue you can create the table manually by running this SQL query

CREATE TABLE piwik_site_setting (
       idsite INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT,
     `plugin_name` VARCHAR(60) NOT NULL,
     `setting_name` VARCHAR(255) NOT NULL,
     `setting_value` LONGTEXT NOT NULL,
     INDEX(idsite, plugin_name)
) ENGINE=Innodb DEFAULT CHARSET=utf8

[hpvd]

maybe one could integrate some kind of check if everything is fine before update?
Maybe there are some other things brought by other version updates...
I'm a little afraid that one may have miss any update and with this not everything is in place...

[hpvd]

gave it a try because we didn't miss the 2.14 and oops... got the same:

Critical Error during the update process:
/var/www/clients/client1/web7/web/core/Updates/3.0.0-b1.php: Error trying to execute the migration 'ALTER TABLE piwik_site_setting ADD COLUMN plugin_name VARCHAR(60) NOT NULL AFTER idsite;'. The error was: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'c1piwik.piwik_site_setting' doesn't exist

[dertuxmalwieder]

At least manually creating the table fixes the problem for me. But it can't be so critical then anyway, right?

[tsteur]

Thanks for the report. I'm working on a fix in https://github.com/piwik/piwik/pull/10708

This was actually related due to a "problem" in Piwik 2.14.0-b2 when someone had updated to 2.14.0-b2 or 2.14.0-b3 but not when someone updated to 2.14.0-b4 so hopefully does not affect too many people. We will fix that problem. The update script that added this table in 2.14 was assigned to "b2" but it was only present in "b4" and therefore may have not been executed. As we were not using that feature yet, it never appeared as a problem

[dertuxmalwieder]

Thanks!

[hpvd]

manually creating the table fixes the problem for us too.

[GreenReaper]

This also breaks plugin uninstallation, which reports:
Mysqli prepare error: Table 'piwik.piwik_site_setting' doesn't exist

Note that this is _after_ I updated to 3.0beta1 via the web interface. It succeeded and claims that there are no more core updates to do when I try to run it manually from the command line; hopefully this is correct.

[jookk]

Mine instance isnt working 3 days. Tried to create table manually, but no success.

[mattab]

@jookk execute the following SQL query in your piwik database:

CREATE TABLE piwik_site_setting (
                          idsite INTEGER(10) UNSIGNED NOT NULL,
                          `plugin_name` VARCHAR(60) NOT NULL,
                          `setting_name` VARCHAR(255) NOT NULL,
                          `setting_value` LONGTEXT NOT NULL,
                     `user_login` VARCHAR(100) NOT NULL DEFAULT '',
                              INDEX(idsite, plugin_name)
                            ) ENGINE=$engine DEFAULT CHARSET=utf8

if your Piwik does not use table prefixes, then adjust your table name appropriately, eg. change to site_setting instead of piwik_site_setting

if you still have some problem let us know the error message

[mattab]

Will be fixed with #10708 for anyone who upgrades directly to beta2. if you upgraded to beta 1 and have this error, then the solution is to run one SQL query, see: https://github.com/piwik/piwik/issues/10703#issuecomment-257184867

[Redsmith24]

I also created the table manually. After this, piwik came out with another error message:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'user_login' in 'where clause'

I have no idea, where this column is missing. Can you help me please.

[sergeevabc]

Upgrading from 2.1x to 3.0.
Could you be so kind to provide SQL to deal with databasename.piwik_plugin_setting doesn't exist?

[Redsmith24]

I executed the SQL query from mattab and the error message disapeared. The new error message is this.

Why and where should there be a column 'user_login' und why it is'nt? So what can I do?

[mattab]

@Redsmith24 add a field user_login' => "VARCHAR(100) NOT NULL DEFAULT to the table plugin_setting

[mattab]

Could you be so kind to provide SQL to deal with

@sergeevabc https://github.com/piwik/piwik/issues/10703#issuecomment-257184867

[sergeevabc]

@mattab, it does not work, I execute your SQL passage

CREATE TABLE piwik_plugin_setting (
idsite INTEGER(10) UNSIGNED NOT NULL,
`plugin_name` VARCHAR(60) NOT NULL,
`setting_name` VARCHAR(255) NOT NULL,
`setting_value` LONGTEXT NOT NULL,
`user_login` VARCHAR(100) NOT NULL DEFAULT,
INDEX(idsite, plugin_name)
) ENGINE=$engine DEFAULT CHARSET=utf8

and the output is

#1064 - You have an error in your SQL syntax;
check the manual that corresponds to your MySQL version for the right syntax to use near
' INDEX(idsite, plugin_name) ' at line 6

Someone on Piwik’s forum offered a workaround that works

CREATE TABLE piwik_plugin_setting (
plugin_name VARCHAR(60) NOT NULL,
setting_name VARCHAR(255) NOT NULL,
setting_value LONGTEXT NOT NULL,
user_login VARCHAR(100) NOT NULL DEFAULT '',
INDEX(plugin_name, user_login)
) ENGINE=Innodb DEFAULT CHARSET=utf8

Yet it does not solve the subsequent problem which is

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'plugin_name' in 'where clause'

It seems additional column should be added like this

ALTER TABLE piwik_site_setting
ADD plugin_name VARCHAR(60) NOT NULL;

Then Piwik finishes updating, however throws the following warning

You are not allowed to change the value of the setting
"enable_plugin_update_communication" in plugin "CoreUpdater"

Question about its meaning is already published on the forum, but nobody cares to answer.

[mattab]

@sergeevabc Ok let's continue discussion there: https://forum.piwik.org/t/warning-during-upgrade-to-3-0-0/22296

[sergeevabc]

@mattab, I neither started that topic, nor have credentials to answer there, but could answer here:
so far no issues pop up after mentioned warning, but you never know. In sum, upgrade to 3.0 was painful.

[Redsmith24]

@mattab thks, this shot the errors ;-)

[frlan]

While attempt to upgrade from 2.8.3 to 3.0.1 I got the same error

[FlorentCoppint]

Same error upgrading from 2.13.1 to 3.0.1.

[bruno28300]

Hi to all
I just had the same errors upgrading from 2.12.1 to 3.0.4 and I used the following steps :

1
CREATE TABLE piwik_plugin_setting (
plugin_name VARCHAR(60) NOT NULL,
setting_name VARCHAR(255) NOT NULL,
setting_value LONGTEXT NOT NULL,
user_login VARCHAR(100) NOT NULL DEFAULT '',
INDEX(plugin_name, user_login)
) ENGINE=Innodb DEFAULT CHARSET=utf8

2
CREATE TABLE piwik_site_setting (
plugin_name VARCHAR(60) NOT NULL,
setting_name VARCHAR(255) NOT NULL,
setting_value LONGTEXT NOT NULL,
user_login VARCHAR(100) NOT NULL DEFAULT '',
INDEX(plugin_name, user_login)
) ENGINE=Innodb DEFAULT CHARSET=utf8

3
ALTER TABLE piwik_site_setting
ADD plugin_name VARCHAR(60) NOT NULL;

And it works well
Hope this will help many of you
Have a good day

Bruno