search

matomo-org / matomo

Empowering People Ethically with the leading open source alternative to Google Analytics that gives you full control over your data. Matomo lets you easily collect data from websites & apps and visualise this data and extract insights. Privacy is built-in. Liberating Web Analytics. Star us on Github? +1. And we love Pull Requests!
https://matomo.org/
GNU General Public License v3.0
19.81k stars 2.64k forks source link

Piwik Sequence table does not auto-update after changing DB prefix #9533

Open quba opened 8 years ago

quba commented 8 years ago

Steps to reproduce:

  1. Change DB prefix
  2. Run archiving

In this case, Piwik will create new records in prefix_sequence table and, unfortunately, will start with 0.

There are two suggested solutions: 1) Don't use prefixes in prefix_sequence table. 2) While recreating records in the sequence table, set them to max(idarchive) from the corresponding table.

It's rather a critical issue. Currently the only way is to manually set the sequence records to max(idarchive) for each corresponding archive_numeric table.

tsteur commented 8 years ago

1) Without prefix is not really solution since one could have installed multiple piwiks in one database.

2) When recreating records, do we know the original prefix? Which tool are you using for this? Or how do you change the prefix? Only in the config file?

quba commented 8 years ago

1) What is the issue with this approach? It should still be doable, as the prefix affects only table name, not records inside. And during the archiving process you should have access to the right config file which defines the prefix of archive tables.

2) The original prefix is not important if you recreate records based on current archive tables. Selecting max(idarchive) should be really fast and there's no need to access previous record from the sequence table.

Why do we have to change prefix in some cases? Just to keep all our databases consistent (e.g. when we import existing database to Piwik Cloud). Would be nice to have a command/script to do the job. If there are issues with the sequence table, it may be really confising for the end users, as the archving process doesn't update the reports in this case.

tsteur commented 8 years ago

1) True! Sounds like simplest solution

2) I'm still not sure what you mean by recreate records but 1) sounds like a good solution anyway :)

quba commented 8 years ago

2) To not start with 0 when there's no record for a given archive table in piwik_sequence, but instead check the the corresponding table.

tsteur commented 8 years ago

I got that, what I don't get is how you change the prefix and recreate records. Like how do you do that step by step? Just to understand it better... I presume you basically import the tables into a database and then do a rename on the table names and that's it?

Anyway, still thinking 1) is best solution so doesn't really matter. We can simply save it without the prefix and also update existing records in the sequence table

quba commented 8 years ago

@tsteur yes, our sys-admins were not aware that something may break after changing tables prefix and updating config file. Now the procedure is to update also records in the sequence table (change prefix).

tsteur commented 8 years ago

OK got it. We should remove it, maybe better for Piwik 3.0 to not break anything. If something goes wrong, there could be serious problems

mattab commented 8 years ago

So the solution would be:

Assigning to LTS backlog.