SQL error upgrading from 1.7.1 to 1.8.4

Support & Bugs
1

I am trying to upgrade from 1.7.1 to 1.8.4…

I tried using php /app/piwik/index.php – “module=CoreUpdater” to upgrade but after 21 hours stuck on the copy into piwik_log_action_duplicates I canceled it… I attempted to run the queries manually and I get a syntax error on the same line :

INSERT INTO piwik_log_action_duplicates ( SELECT action.idaction AS before, canonical.idaction AS after FROM ( SELECT name, hash, MIN(idaction) AS idaction FROM piwik_log_action AS action_canonical_base WHERE type = 1 AND url_prefix IS NOT NULL GROUP BY name, hash # only grouping by name would be case-insensitive HAVING COUNT(idaction) > 1 ) AS canonical LEFT JOIN piwik_log_action AS action ON (action.type = 1 AND canonical.hash = action.hash) # use index (type, hash) AND canonical.name = action.name AND canonical.idaction != action.idaction );

any ideas???

thanks

2

I am pretty sure the problem is with the # in the sql… I copied it right from the “Click here to view and copy the list of SQL queries that will get executed”… does anyone have what this should be?

I have users bugging me… would love to get this resolved quickly…

thanks again

3

Sorry about that, we shouldn’t have put a comment in the SQL queries as it gets inlined in the UI… Can you try to remove the 2 comments from your SQL, as per the fix in: http://dev.piwik.org/trac/changeset/7017

4

thanks… works like a charm now…

if only I could speed it up… our db is 20gb… and this has been running 4 hours now… wondering if maybe purging some data might speed things up…

5

This update works on the log table. So, definitely purging “old visitor log” information will speed up this update.