Database upgrade taking many hours

Support & Bugs
1

Update 6/17/2015
The archive blob table for 2015_01 is larger than I anticipate! I see the reason why the SQL query to update will take a large amount of time! Is anyone has similar udpate that is also taking a long time to run the query?

piwik_archive_blob_2014_11 InnoDB 10 Compact 194317 1980 384925696 0 65044480 340787200
piwik_archive_blob_2014_12 InnoDB 10 Compact 335202 1798 602947584 0 88096768 340787200
piwik_archive_blob_2015_01 InnoDB 10 Compact 37932494 1148 43568381952 0 2976923648 340787200
piwik_archive_blob_2015_02 InnoDB 10 Compact 5696233 1199 6829998080 0 565559296 340787200
piwik_archive_blob_2015_03 InnoDB 10 Compact 7787010 1109 8639954944 0 717619200 340787200
piwik_archive_blob_2015_04 InnoDB 10 Compact 8040723 959 7715225600 0 640073728 340787200
piwik_archive_blob_2015_05 InnoDB 10 Compact 4648127 1669 7762313216 0 659030016 340787200
piwik_archive_blob_2015_06 InnoDB 10 Compact 4168206 749 3123478528 0 277315584 340787200
piwik_archive_numeric_2005_01 InnoDB 10 Compact 0 0 16384 0 32768 340787200

Reently have to update the Piwik version from 2.8.2 to 2.13.1

Proceed with the update instruciton by using command line:
http://piwik.org/docs/update/#database-upgrade-for-high-traffic-piwik-servers

Now that it is still running for over 20 hours… Is the update go stuck, and how can I check the status of the update progress? Need some advise. Thanks!

[size=medium]A database upgrade is required. Execute update? (y/N) y

Starting the database upgrade process now. This may take a while, so please be p
atient.

*** Update ***

Database Upgrade Required

Your Piwik database is out-of-date, and must be upgraded before you can cont

inue.

Piwik database will be upgraded from version 2.10.0-b4 to the new version 2.

13.1.

The database upgrade process may take a while, so please be patient.

Executing # ATTENTION: This update script will execute some more SQL queries t
han that below as it is necessary to rebuilt some archives #… Done. [1 / 201]
Executing UPDATE piwik_report SET reports = REPLACE(reports, ‘UserSettings_get
BrowserVersion’, ‘DevicesDetection_getBrowserVersions’)… Done. [2 / 201]
Executing UPDATE piwik_report SET reports = REPLACE(reports, ‘UserSettings_get
Browser’, ‘DevicesDetection_getBrowsers’)… Done. [3 / 201]
Executing UPDATE piwik_report SET reports = REPLACE(reports, ‘UserSettings_get
OSFamily’, ‘DevicesDetection_getOsFamilies’)… Done. [4 / 201]
Executing UPDATE piwik_report SET reports = REPLACE(reports, ‘UserSettings_get
OS’, ‘DevicesDetection_getOsVersions’)… Done. [5 / 201]
Executing UPDATE piwik_report SET reports = REPLACE(reports, ‘UserSettings_get
MobileVsDesktop’, ‘DevicesDetection_getType’)… Done. [6 / 201]
Executing UPDATE piwik_report SET reports = REPLACE(reports, ‘UserSettings_get
BrowserType’, ‘DevicesDetection_getBrowserEngines’)… Done. [7 / 201]
Executing UPDATE piwik_report SET reports = REPLACE(reports, ‘UserSettings_get
WideScreen’, ‘UserSettings_getScreenType’)… Done. [8 / 201][/size]

2

Update post - 06/24/2015

The udpate of Piwik from 2.8.2 to 2.13.1 is completed and successful. Here are the issues that I experienced:
[ol]
[li] MySQL database timedout
[/li][/ol]
From the start, the Piwik update took over 14 hours to complete with no issue. However, I have found that the MySQL default configurations will need to be modified because of the large database file size 50GB.

The main error or issue that I had was the SQL Query Timed out - Error code: 2013 and 2006. This prevented me from running the query to udpate the tables for the update. I follow the instruction of set the properties on MySQL from searching - http://stackoverflow.com/questions/10563619/error-code-2013-lost-connection-to-mysql-server-during-query

1 Like