Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Delete old visitor logs uses idvisit instead of the date #7180

Closed
kiwipro opened this issue Feb 11, 2015 · 5 comments
Closed

Delete old visitor logs uses idvisit instead of the date #7180

kiwipro opened this issue Feb 11, 2015 · 5 comments
Assignees
@diosmosis
Labels
Bug For errors / faults / flaws / inconsistencies etc.
Milestone
2.14.0

Comments

@kiwipro
Copy link

kiwipro commented Feb 11, 2015

We configure Piwik (2.9.1) to regularly delete old visitor logs, setting to '42' days the field 'Delete logs older than' from 'Delete old visitor logs and reports' section.

Piwik saves the new configuration, recognizes the visit with oldest date (2014-12-30) and counts the records to be deleted by running the following queries:

  • SELECT COUNT(*) FROM piwik_log_link_visit_action WHERE idvisit <= '5328886'
  • SELECT COUNT(*) FROM piwik_log_visit WHERE idvisit <= '5328886'

The visit with idvisit='5328886' from our 'piwik_log*' tables is:
"157204844 38 ÇLÔ/JB" 2014-12-30 23:52:59 5328886 1198925 ..."
"5328886 38 ÇLÔ/JB" 23:52:59 1 1 0 0 0 2014-12-30 23:52:59 2014-12-30 23:53:45 ..."

If we click on 'Purge DB now', Piwik runs the following:

  • DELETE FROM piwik_log_link_visit_action WHERE idvisit <= '5328886' ORDER BY idvisit ASC LIMIT 100000

Our piwik_log* tables contain a lot of visits with idvisit lower than '5328886', but with more recents dates, i.e. visit with idvisit '5328876' has '2015-02-05' as date:
"157164279 30 î«7]¤b³k 2015-02-05 13:56:05 5328786 ..."
"5328786 30 î«7]¤b³k 13:56:05 1 1 0 0 0 2015-02-05 13:56:05 2015-02-05 13:56:22 ..."

It seems Piwik will not delete the visits older than 42 days, but it will delete visits based on idvisit <= '5328886'.
@kiwipro kiwipro changed the title Delete old visitor logs using idvist rather than the date Delete old visitor logs using idvisit rather than the date Feb 11, 2015
@mattab mattab added the Bug For errors / faults / flaws / inconsistencies etc. label Feb 12, 2015
@mattab mattab added this to the Short term milestone Feb 12, 2015
@mattab
Copy link
Member

mattab commented Feb 12, 2015

Hi @kiwipro thanks for the report.

if Piwik does not deleted based on date, that's a bug that could lead to data loss, and we should investigate.

@kiwipro
Copy link
Author

kiwipro commented Mar 6, 2015

Hi mattab

any news about ?

We have stopped 'Purge DB' functionality from several weeks, and this affects our storage as piwik db is growing day by day.

We would like to manually delete oldest visits from db using sql queries.

Pls, could you suggest us the correct query to delete visits from piwik_log_link_visit_action and piwik_log_visit piwik tables selecting records by date ??

As example, to delete all the visits of 2015 January 15th from both piwik_log_link_visit_action and piwik_log_visit table, what is the correct query to run via mysql client or mysqladmin ??

Awaiting your prompt reply, I thank you in advance for your support.
Ciao
kiwipro

@kiwipro kiwipro closed this as completed Mar 6, 2015
@kiwipro kiwipro reopened this Mar 6, 2015
@kiwipro
Copy link
Author

kiwipro commented Mar 10, 2015

Hi there,

any news ?? any suggestion ??

Awaiting your input, is there anyone that can help me to build the right query in order to delete old visits from piwik db ??

Thanks in advance.
kiwipro

@mattab mattab changed the title Delete old visitor logs using idvisit rather than the date Delete old visitor logs uses idvisit instead of the date Apr 7, 2015
@mattab
Copy link
Member

mattab commented Apr 7, 2015

Since this could lead to data loss, increasing priority

refs #6785

@mattab mattab modified the milestones: Piwik 2.14.0, Short term Apr 7, 2015
@mattab mattab mentioned this issue Apr 22, 2015
Closed
@diosmosis diosmosis self-assigned this May 4, 2015
diosmosis pushed a commit that referenced this issue May 7, 2015
Refs #7180, create initial (not working) logs:delete command.
9f5a800
diosmosis pushed a commit that referenced this issue May 12, 2015
Refs #7180, create initial (not working) logs:delete command.
ae1f771
diosmosis pushed a commit that referenced this issue May 13, 2015
Refs #7180, create initial (not working) logs:delete command.
d2a41c0
@diosmosis diosmosis mentioned this issue May 14, 2015
Merged
4 tasks
diosmosis pushed a commit that referenced this issue May 15, 2015
Refs #7180, add test that checks logs are correctly purged even if vi…
07749b0 
…sits are tracked in the past.
diosmosis pushed a commit that referenced this issue May 27, 2015
Refs #7180, create initial (not working) logs:delete command.
fb4adae
diosmosis pushed a commit that referenced this issue May 27, 2015
Refs #7180, add test that checks logs are correctly purged even if vi…
ee15fae 
…sits are tracked in the past.
diosmosis pushed a commit that referenced this issue Jun 11, 2015
Refs #7180, create initial (not working) logs:delete command.
0ae985f
diosmosis pushed a commit that referenced this issue Jun 11, 2015
Refs #7180, add test that checks logs are correctly purged even if vi…
794f056 
…sits are tracked in the past.
mattab pushed a commit that referenced this issue Jun 16, 2015
Merge pull request #7887 from piwik/6785_log_delete_command
36529f5 
Fixes #6785, #7180 add log delete command and make log deletion delete only based on time and not idvisit.
@mattab
Copy link
Member

mattab commented Jun 16, 2015

Dear @kiwipro

thanks again for reporting this issue. We have fixed it and it's available in Piwik 2.14.0-b3

Could you please test this beta version and confirm that the issue is fixed?

@mattab mattab closed this as completed Jun 16, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@diosmosis diosmosis

Labels
Bug For errors / faults / flaws / inconsistencies etc.
Projects
None yet
Milestone
2.14.0
Development

No branches or pull requests
3 participants
@diosmosis @mattab @kiwipro