2.16.4 Hung on updating database

Support & Bugs
#1

My database has been hung on updating for 4 1/2 hrs now. I don’t believe it should take this long. I’m not sure where to proceed from here. Any help would be greatly appreciated.

root@piwik httpd]# php /var/www/html/piwik/console core:update

    *** Update ***

    Database Upgrade Required

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

    The following dimensions will be updated: log_visit.visit_entry_idaction_url.

    *** Note: this is a Dry Run ***

    ALTER TABLE `piwik_log_visit` MODIFY COLUMN `visit_entry_idaction_url` INTEGER(11) UNSIGNED NULL  DEFAULT NULL;

    *** End of Dry Run ***

A database upgrade is required. Execute update? (y/N) y

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

    *** Update ***

    Database Upgrade Required

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

    The following dimensions will be updated: log_visit.visit_entry_idaction_url.

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

  Executing ALTER TABLE `piwik_log_visit` MODIFY COLUMN `visit_entry_idaction_url` INTEGER(11) UNSIGNED NULL  DEFAULT NULL...
#2

How big is your log_visit table? it should take more than 1 hour only when the table is very large such as several Gb or more. Is the table INNODB or MyISAM engine?

#3

thx for replying it finally finished… my log_visit table had 17 Million rows in it. Just as an FYI to anyone else that runs into this, it took a total of 6 hrs to complete.

#4

The same here. It is a 12 million table and a 32GB mysql file:

-rw-rw---- 1 mysql mysql 32G ott 5 08:59 ibdata1
(I don’t know why all the data of my database are on a single file, I hope it is correct)

I’ve run the 2.14.5 upgrade and it runned all the night, from 9pm to 8am but never finished (stalled on copyng table).

I find this problem a BIG problem. I don’t even image on high traffic sites how they could even update this table. The change is from signed int(11) to unsigned. Is worth it? can I just upodate the version in UpdateCheck_LatestVersion and ignore this step?

#5

Yes if you are blocked, you can always do this and ignore the update, but it will create some bugs in the future, around tracking Entry Pages, so we don’t recommend it

#6

This problem is going to hit a lot of people. Not updating can hurt only if I will have 2 giga entries, wich will occour in more than 30 year :slight_smile:

Or, as an alternative,can I just dump the table, empty it, run the upgrade, and reload from the dump? Or auto_increment can be a problem?

#7

I’ev manually updated UpdateCheck_LatestVersion to 2.16.5 but it still asks me to update… how this works?

Can someone explain me how to bypass the version checking?

#8

also switching it to 2.16.2 return the same message about updating dimension: log_visit.visit_entry_idaction_url. I’m also updating core/Version.php accordingly.

Seems it still find this query to be executed somewhere.

Also: https://piwik.org/changelog/piwik-2-16-4/

404 not found. So nobody knows why such update is important.

#9

the correct changelog is Piwik 2.16.3 - Analytics Platform - Matomo

currently we haven’t documented how to skip this schema but it would be valuable i guess. feel free to create a request in our tracker: Issues · piwik/piwik · GitHub

#10

Thanks matthieu!

Can you add the “major upgrade query” in the changelog? So people can analize it in advance and take the correct steps to implement it.

Btw, I resolved reverting the codebase to 2.16.2 and writing “2.16.2” in the option table where there were 2.16.5.

#11

Hey mattieu.
Our piwik DB is +34GB …
the query is stuck on “copy to tmp table | ALTER TABLE "piwik_log_visit" MODIFY COLUMN "visit_entry_idaction_url" INTEGER(11) UNSIGNED NULL DEFAULT NULL |” for 16+ hrs…
Is there any way to estimate how long the process might take?