Opened 5 years ago

Closed 5 years ago

Last modified 4 years ago

#666 closed Bug (fixed)

Database upgrades may take too long to run in web browser

Reported by: frabcus Owned by: vipsoft
Priority: critical Milestone: Piwik 0.4.3
Component: Core Keywords:
Cc: Sensitive: no

Description (last modified by vipsoft)

When upgrading to a new version, the database upgrades take too long to run in the web browser. Mine timed out, and left me with a half upgraded database which I had to manually fix by reading the upgrade files.

I would like one or all of:

  • An option to run the database upgrades at the command line.
  • Progress to show which database migration is currently being run (in the web browser and/or on the command line)
  • It to do each upgrade separately, and record it is done in the database. So if things go wrong, it is easier to carry on from where you left off. Only one migration would need fixing.

Example command from Updates/0.2.27.php:

ALTER TABLE piwik_log_visit ADD visit_goal_converted VARCHAR( 1 ) NOT NULL AFTER visit_total

So far that has taken 3 minutes, I suspect it will take about 6. I had a whole bunch of upgrades to run, which in total will take a couple of hours I suspect. Not a realistic thing to do in one web request.

I've marked this critical, as it caused my piwik installation to be totally broke during a routine upgrade.

Change History (14)

comment:1 Changed 5 years ago by vipsoft (robocoder)

  • Milestone set to DigitalVibes

A command line upgrade is probably the most reasonable path given that:

  • php has a max_execution_time (typical default is 30 for apache; 0 for the cli)
  • apache and iis have max timeouts of 300 (again default)

Going forward, users should be encouraged to stay up-to-date with releases to avoid large database updates.

comment:2 Changed 5 years ago by vipsoft (robocoder)

  • Summary changed from Database upgrades too slow to run in web browser to Database upgrades may take too long to run in web browser

comment:3 Changed 5 years ago by matt (mattab)

in a future version (Maciej is working on it) there will be rather big schema update (adding one field to piwik_log_link_visit_action, adding field in piwik_log_visit)..

This problem will happen again. What should we do to quick fix it?

A "command line" message below the normal "Welcome to Piwik! You need to update [...] If your Piwik database is large or very large and you have a shell access to your Piwik server, you can trigger the update by using the following command line: /usr/bin/php5 /path/to/piwik/index.php updateCorePlugins=1" or something similar?

comment:4 Changed 5 years ago by larsen

+1

comment:5 Changed 5 years ago by matt (mattab)

anybody implementing this ticket should feel evil.

comment:6 Changed 5 years ago by vipsoft (robocoder)

  • Owner set to vipsoft
  • Status changed from new to assigned

Feeling evil...

comment:7 Changed 5 years ago by vipsoft (robocoder)

  • Milestone changed from 2- DigitalVibes to 1 - Piwik 0.4.2

comment:8 Changed 5 years ago by vipsoft (robocoder)

  • Description modified (diff)

comment:9 Changed 5 years ago by matt (mattab)

the command line tool, if this is the direction are going, could maybe read the max execution time from the config, and let the user know "Your max execution time is Xmin. If you have a medium or large Piwik installation, maybe you would like to increase this limit. Look for X in your php.ini."

comment:10 Changed 5 years ago by vipsoft (robocoder)

(In [1319]) refs #666 - record version of incremental updates as they are done

comment:11 Changed 5 years ago by vipsoft (robocoder)

(In [1351]) fixes #894, refs #666 - fix problems and inconsistencies between fresh install and upgrade path

Only the utf8 side-effects of converting of TEXT columns to MEDIUMTEXT columns was unchanged.

comment:12 Changed 5 years ago by vipsoft (robocoder)

  • Resolution set to fixed
  • Sensitive unset
  • Status changed from assigned to closed

(In [1375]) fixes #666 - command line update, e.g., (if a database upgrade is required)

    you@home:/var/www/piwik$ php index.php

(Progress bar deferred to #910.)

comment:13 Changed 5 years ago by domtop

comment:14 Changed 4 years ago by vipsoft (robocoder)

  • Keywords database upgrade removed
Note: See TracTickets for help on using tickets.