I host on a cloud provider, which gives me some flexibility regarding testing database changes and disruptive Piwik changes.
I recently looked into upgrading our instance of Piwik and found that the DB upgrade process takes about 6.5 hours to do, which is acceptable for me in that I can do this over night, but it is quite lengthy and it does fail halfway through requring me to manually delete a table before it will continue.
I then looked into whether we could turn off ‘trigger from the browser’ and set up auto archiving as we have somewhere just under 1000 tracked websites; I can’t load All Websites in Piwik 1.1.1 as it hits the PHP script timeout limit, and it is suggested to turn this off and run the auto archiver from cron. This script takes about a week to run the first time, and it does fail too, so I wondering if I can get the processing done and then import the tables into the old DB once I have updated it?
It says that the piwik_report table already exists. Prior to starting the upgrade of the database this table doens’t actually exist in the database. If I rename the table, or delete it, and rerun the upgrader it completes after about another two hours.
*** Update ***
Database Upgrade Required
Your Piwik database is out-of-date, and must be upgraded before you can continue.
Piwik database will be upgraded from version 1.1.1 to the new version 2.0.3.
The database upgrade process may take a while, so please be patient.
[X] Critical Error during the update process:
* /home/user/www/piwik/core/Updates/1.8.3-b1.php:
Error trying to execute the query 'CREATE TABLE `piwik_report` (
`idreport` INT(11) NOT NULL AUTO_INCREMENT,
`idsite` INTEGER(11) NOT NULL,
`login` VARCHAR(100) NOT NULL,
`description` VARCHAR(255) NOT NULL,
`period` VARCHAR(10) NOT NULL,
`type` VARCHAR(10) NOT NULL,
`format` VARCHAR(10) NOT NULL,
`reports` TEXT NOT NULL,
`parameters` TEXT NULL,
`ts_created` TIMESTAMP NULL,
`ts_last_sent` TIMESTAMP NULL,
`deleted` tinyint(4) NOT NULL default 0,
PRIMARY KEY (`idreport`)
) DEFAULT CHARSET=utf8'.
The error was: SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'piwik_report' already exists
The above is the core error message. It should help explain the cause, but if you require further help please:
* Check the [ Piwik FAQ ] which explains most common errors during update.
* Ask your system administrator - they may be able to help you with the error which is most likely related to your server or MySQL setup.
If you are an advanced user and encounter an error in the database upgrade:
* identify and correct the source of the problem (e.g., memory_limit or max_execution_time)
* execute the remaining queries in the update that failed
* manually update the `option` table in your Piwik database, setting the value of version_core to the version of the failed update
* re-run the updater (through the browser or command-line) to continue with the remaining updates
* report the problem (and solution) so that Piwik can be improved
I’m trying, however, I’m also trying to keep the amount of disruption to a minimum. I will be upgrading directly to 2.0.3 or whatever version is in the latest.zip at the time.
I performed the upgrade this weekend just gone. Unfortunately, after getting it to fail at the same point repeatedly, I decided to optimise the DB tables before I ran the upgrade to try to speed things up.
Big mistake It broke the upgrade at a different point and I ended up running each of the SQL statements manually. Not fun when you’ve got 13m rows in some of the tables and some of the queries take 45 mins
It broke the upgrade at a different point and I ended up running each of the SQL statements manually. Not fun when you’ve got 13m rows in some of the tables and some of the queries take 45 mins 