Please help! Update to Piwik 1.8 broke my Installation


#1

I have been using Piwik for 2 years now without problems.
The update to 1.8 (installed with the web interface) broke my installation.

I get several error-messages on my Dashboard.

This is a screenshot how it looks like…

Please! How can i fix it? Is my analytic data lost?


(Matthieu Aubry) #2

Dont worry no data is lost. can you please copy the error message here?


#3

Hi,

I can’t paste it completely, because i can’t get to the end of the lines. They are hidden behind the layers. Couldn’t find it in the HTML source either.

Seems to be a database login problem, but I was able to use Piwik normally before the update and haven’t changed users in the database.

This is what I could get:

SQLSTATE[42000]: Syntax error or access violation: 1044 Access denied for user ‘piwik’@‘localhost’ to database ‘piwik’

Go to Piwik
Login
Backtrace:

#0 /var/www/piwik/piwik/core/PluginsFunctions/Sql.php(51): Zend_Db_Adapter_Pdo_Abstract->exec(‘LOCK TABLES piw…’)
#1 /var/www/piwik/piwik/core/PluginsFunctions/Sql.php(205): Piwik_Sql::exec(‘LOCK TABLES piw…’)
#2 /var/www/piwik/piwik/core/PluginsFunctions/Sql.php(359): Piwik_Sql::lockTables(‘piwik_archive_n…’, ‘piwik_archive_n…’)
#3 /var/www/piwik/piwik/core/ArchiveProcessing.php(688): Piwik_LockTables(‘piwik_archive_n…’, ‘piwik_archive_n…’)
#4 /var/www/piwik/piwik/core/ArchiveProcessing.php(533): Piwik_ArchiveProcessing->loadNextIdarchive()
#5 /var/www/piwik/piwik/core/ArchiveProcessing.php(472): Piwik_ArchiveProcessing->initCompute()
#6 /var/www/piwik/piwik/core/Archive/Single.php(226): Piwik_ArchiveProcessing->launchArchiving()
#7 /var/www/piwik/piwik/core/Archive/Array/IndexedByDate.php(90): Piwik_Archive_Single->prepareArchive()
#8 /var/www/piwik/piwik/plugins/VisitsSummary/API.php(88): Piwik_Archive_Array_IndexedByDate->getDataTableFromNumeric(Array)
#9 [internal function]: Piwik_VisitsSummary_API->get(‘2’, ‘day’, ‘2012-05-02,2012…’, false, ‘nb_visits’)
#10 /var/www/piwik/piwik/core/API/Proxy.php(190): call_user_func_array(Array, Array)
#11 /var/www/piwik/piwik/plugins/API/API.php(986): Piwik_API_Proxy->call(‘Piwik_VisitsSum…’, ‘get’, Array)
#12 [internal function]: Piwik_API_API->get(‘2’, ‘day’, ‘2012-05-02,2012…’, false, ‘nb_visits’)
#13 /var/www/piwik/piwik/core/API/Proxy.php(190): call_user_func_array(Array, Array)
#14 /var/www/piwik/piwik/core/API/Request.php(128): Piwik_API_Proxy->call(‘Piwik_API_API’, ‘get’, Array)
#15 /var/www/piwik/piwik/core/ViewDataTable.php(421): Piwik_API_Request->process()
#16 /var/www/piwik/piwik/core/ViewDataTable/GenerateGraphData/ChartEvolution.php(109): Piwik_ViewDataTable->loadDataTableFromAPI()
#17 /var/www/piwik/piwik/core/ViewDataTable/GenerateGraphData.php(181): Piwik_ViewDataTable_GenerateGraphData_ChartEvolution->loadDataTableFromAPI()
#18 /var/www/piwik/piwik/core/Controller.php(247): Piwik_ViewDataTable_GenerateGraphData->main()
#19 /var/www/piwik/piwik/plugins/VisitsSummary/Controller.php(78): Piwik_Controller->getLastUnitGraphAcrossPlugins(‘VisitsSummary’, ‘getEvolutionGra…’, Array, Array, ‘Dies ist eine ?..’)
#20 [internal function]: Piwik_VisitsSummary_Controller->getEvolutionGraph()
#21 /var/www/piwik/piwik/core/FrontController.php(138): call_user_func_array(Array, Array)
#22 /var/www/piwik/piwik/core/FrontController.php(159): Piwik_FrontController->dispatch(‘VisitsSummary’, ‘getEvolutionGra…’, Array)
#23 /var/www/piwik/piwik/core/ViewDataTable/GenerateGraphHTML.php(170): Piwik_FrontController->fetchDispatch(‘VisitsSummary’, ‘getEvolutionGra…’, Array)
#24 /var/www/piwik/piwik/core/ViewDataTable/GenerateGraphHTML.php(125): Piwik_ViewDataTable_GenerateGraphHTML->getGraphData()
#25 /var/www/piwik/piwik/core/ViewDataTable/GenerateGraphHTML.php(109): Piwik_ViewDataTable_GenerateGraphHTML->buildView()
#26 /var/www/piwik/piwik/core/Controller.php(247): Piwik_ViewDataTable_GenerateGraphHTML->main()
#27 /var/www/piwik/piwik/plugins/VisitsSummary/Controller.php(78): Piwik_Controller->getLastUnitGraphAcrossPlugins(‘VisitsSummary’, ‘getEvolutionGra…’, Array, Array, ‘Dies ist eine ?..’)
#28 /var/www/piwik/piwik/plugins/VisitsSummary/Controller.php(23): Piwik_VisitsSummary_Controller->getEvolutionGraph(true, Array)
#29 [internal function]: Piwik_VisitsSummary_Controller->index()
#30 /var/www/piwik/piwik/core/FrontController.php(138): call_user_func_array(Array, Array)
#31 /var/www/piwik/piwik/index.php(53): Piwik_FrontController->dispatch()
#32 {main}


#4

Did you try to connect manually your mysql database

#mysql -u piwik -p XXXX

Which piwik version you were using before upgrade ?


#5

Exact same problem here.

SQLSTATE[42000]: Syntax error or access violation: 1044 Access denied for user ‘piwik’@‘localhost’ to database ‘piwik_dei’

  • Server is Mac OS X Server 10.6.8
  • Previous Piwik version was 1.7.1
  • “mysql -u piwik -p piwik_dei” successfully connects (after entering the password).
  • Verified that “password” is correct in config/config.ini.php

Update:
The Visitor Log still works and it appears to be logging new visits. But every other module gives the above error.


#6

When I try to view the statistics about today I got the same error. I did a plain upgrade from the previous release (it was 1.7.something) to 1.8.

Atm I can see old statistics only.

I didn’t change user privileges or anything else.

Server is Debian+Apache2+PHP5+MySQL (nothing was changed in the last few months).

How to reproduce this bug:
Log in -> Don’t upgrade -> Choose today from datepicker -> Wait for statistics to appear -> Upgrade -> Bug

It seems it doesn’t trigger if you don’t choose today from datepicker (anything related to new month statistics “piwik_archive_blob_2012_06” or “piwik_archive_numeric_2012_06”? it’s 1st of June), but maybe I’m wrong…

Thank you for your patience… :slight_smile:

Error:
SQLSTATE[42000]: Syntax error or access violation: 1044 Access denied for user ‘statistics’@’—’ to database ‘statistics’

Backtrace:
#0 /home/siti/www.—.com/—.com/htdocs/statistics/core/PluginsFunctions/Sql.php(51): Zend_Db_Adapter_Pdo_Abstract->exec(‘LOCK TABLES piw…’)
#1 /home/siti/www.—.com/—.com/htdocs/statistics/core/PluginsFunctions/Sql.php(205): Piwik_Sql::exec(‘LOCK TABLES piw…’)
#2 /home/siti/www.—.com/—.com/htdocs/statistics/core/PluginsFunctions/Sql.php(359): Piwik_Sql::lockTables(‘piwik_archive_n…’, ‘piwik_archive_n…’)
#3 /home/siti/www.—.com/—.com/htdocs/statistics/core/ArchiveProcessing.php(688): Piwik_LockTables(‘piwik_archive_n…’, ‘piwik_archive_n…’)
#4 /home/siti/www.—.com/—.com/htdocs/statistics/core/ArchiveProcessing.php(533): Piwik_ArchiveProcessing->loadNextIdarchive()
#5 /home/siti/www.—.com/—.com/htdocs/statistics/core/ArchiveProcessing/Period.php(230): Piwik_ArchiveProcessing->initCompute()
#6 /home/siti/www.—.com/—.com/htdocs/statistics/core/ArchiveProcessing.php(472): Piwik_ArchiveProcessing_Period->initCompute()
#7 /home/siti/www.—.com/—.com/htdocs/statistics/core/Archive/Single.php(226): Piwik_ArchiveProcessing->launchArchiving()
#8 /home/siti/www.—.com/—.com/htdocs/statistics/core/Archive/Single.php(256): Piwik_Archive_Single->prepareArchive()
#9 /home/siti/www.—.com/—.com/htdocs/statistics/core/Archive/Single.php(446): Piwik_Archive_Single->get(‘UserCountry_dis…’, ‘numeric’)
#10 /home/siti/www.—.com/—.com/htdocs/statistics/core/Archive/Single.php(489): Piwik_Archive_Single->getNumeric(‘UserCountry_dis…’)
#11 /home/siti/www.—.com/—.com/htdocs/statistics/plugins/UserCountry/API.php(68): Piwik_Archive_Single->getDataTableFromNumeric(‘UserCountry_dis…’)
#12 [internal function]: Piwik_UserCountry_API->getNumberOfDistinctCountries(‘1’, ‘range’, ‘2012-01-22,2012…’, false)
#13 /home/siti/www.—.com/—.com/htdocs/statistics/core/API/Proxy.php(190): call_user_func_array(Array, Array)
#14 /home/siti/www.—.com/—.com/htdocs/statistics/core/API/Request.php(128): Piwik_API_Proxy->call(‘Piwik_UserCount…’, ‘getNumberOfDist…’, Array)
#15 /home/siti/www.—.com/—.com/htdocs/statistics/core/Controller.php(347): Piwik_API_Request->process()
#16 /home/siti/www.—.com/—.com/htdocs/statistics/plugins/UserCountry/Controller.php(67): Piwik_Controller->getNumericValue(‘UserCountry.get…’)
#17 /home/siti/www.—.com/—.com/htdocs/statistics/plugins/UserCountry/Controller.php(24): Piwik_UserCountry_Controller->getNumberOfDistinctCountries(true)
#18 [internal function]: Piwik_UserCountry_Controller->index()
#19 /home/siti/www.—.com/—.com/htdocs/statistics/core/FrontController.php(138): call_user_func_array(Array, Array)
#20 /home/siti/www.—.com/—.com/htdocs/statistics/index.php(53): Piwik_FrontController->dispatch()
#21 {main}

*I had to replace my client’s domain name and IP with “—”.


#7

My previous Version was 1.7.1
Debian Squeeze

mysql --version
mysql Ver 14.14 Distrib 5.1.61, for debian-linux-gnu (x86_64) using readline 6.1

Strange thing is, I can’t login to mysql from console. Password seems to really have changed

mysql -u piwik -p
Enter password:
ERROR 1045 (28000): Access denied for user ‘piwik’@‘localhost’ (using password: YES)

Edit:

After setting the paswword for the db again, I can login from console, but Piwik still fails to connect.


#8

I just ran into the same problem, but in fact everything we need to know to fix this is right there in the first line of the error message:

#0 /var/www/piwik/piwik/core/PluginsFunctions/Sql.php(51): Zend_Db_Adapter_Pdo_Abstract->exec(‘LOCK TABLES piw…’)

So, for whatever reason, Piwik tries to LOCK the a table here.
Since it only had grants for “SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER” on my database, it wasn’t allwowed to do so and probably isn’t as well in your installtion.

What you can do about it is just granting your piwik user the “LOCK TABLES” privilege in MySQL and hopefully you should be fine just like me.
But as locking isn’t a standard privlege I’d give the database user of a webapp, I think this should be documented at least in the release notes or anywhere else.


(Matthieu Aubry) #9

TO all who experience this issue, can you please check that your Mysql USER has the LOCK privilege ?

we now require the LOCK privilege as of Piwik 1.8 so maybe you will need to add it specifically to the user!


(Matthieu Aubry) #10

Thanks for your feedback I added a note about LOCk in the changelog: Changelog Archive - Analytics Platform - Matomo

and updated the Requirements page: Piwik Requirements - Analytics Platform - Matomo


#11

I granted All Privileges to my piwik user, including LOCK.
Piwik still can’t authenticate to it’s DB.


(Matthieu Aubry) #12

what is the error and full backtrace please?


#13

I can’t login anymore.
Piwik shows the installer now, instead of my installation.

Error is

SQLSTATE[28000] [1045] Access denied for user ‘piwik’@‘localhost’ (using password: YES)


#14

This fixed it for my “piwik_dei” table and “piwik” user:

grant lock tables on piwik_dei.* to ‘piwik’@‘localhost’;

Note the syntax: not lock or ‘lock tables’, but lock tables.


#15

I had the same problem. I think the upgrader or the script itself should give a more useful error during or after installation. “Hey, big change: add the LOCK privilege to your Piwik SQL user to continue!”


#16

Thank you all, everything is fine now! :slight_smile:


#17

[quote=matt]
TO all who experience this issue, can you please check that your Mysql USER has the LOCK privilege ?

we now require the LOCK privilege as of Piwik 1.8 so maybe you will need to add it specifically to the user![/quote]

While i’am upgrading from piwik 1.7.1 this solution was helpful for me!

thank you very much


#18

Hi,
Thank you very much for the solution.
I just added this additional privilege to the user Pikwi to the database: “LOCK TABLES”.
Everything works again and again correctly.
Thank you so much for this information Mr NFG.
Jo


#19

[quote=matt]
Thanks for your feedback I added a note about LOCk in the changelog: Changelog Archive - Analytics Platform - Matomo

and updated the Requirements page: http://piwik.org/docs/requirements/[/quote]

FYI, The word “lock” is mentioned nowhere in the changelog.


#20

Adding that additional “LOCK TABLE” privilege to the Piwik-user in the database does NOT make things work for me …

Everything remains messed up as described by wonder75 in the start of this thread.

How to resolve this ?