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

In rare cases the same idarchive includes data from 2 different archives of different segments (edge case) #14379

Closed
mattab opened this issue Apr 28, 2019 · 4 comments
Labels
Bug For errors / faults / flaws / inconsistencies etc. not-in-changelog For issues or pull requests that should not be included in our release changelog on matomo.org.

Comments

@mattab
Copy link
Member

mattab commented Apr 28, 2019

This issue occurs very rarely, but has been reported/seen multiple times already.

On the evolution graphs some days have 0 visits, but have pageviews:

Creating this bug report to document the workaround.

Example

Screenshot from 2019-04-29 09-04-01


> select count(distinct idarchive) as count from archive_numeric_2019_01;
+-------+
| count |
+-------+
| 93763 |
+-------+
1 row in set (0.23 sec)


select count(*) as count, GROUP_CONCAT(name), idarchive, date1, date2 as names from archive_numeric_2019_01 where name like 'done%' group by idarchive having count > 1;
+-------+----------------------------------------------------------------------------------------------------------+-----------+------------+------------+
| count | GROUP_CONCAT(name)                                                                                       | idarchive | date1      | names      |
+-------+----------------------------------------------------------------------------------------------------------+-----------+------------+------------+
|     2 | done65a4192105c20356743cb73ffb841a68.UserCountry,doned545a7ce53f05bf0d5fbc29074676061.VisitsSummary      |       134 | 2019-01-07 | 2019-01-07 |
|     2 | done4a766c89fa870f1df683b9ed3833ae4a,doned545a7ce53f05bf0d5fbc29074676061.Actions                        |       135 | 2019-01-08 | 2019-01-08 |
|     2 | done1f27eaa8663a6eabfa2d19a79aa36387.VisitsSummary,donedad43d8c8fb4710f6bcba3388733b6c9                  |       908 | 2019-01-01 | 2019-01-01 |
|     2 | done1f27eaa8663a6eabfa2d19a79aa36387.Goals,donedad43d8c8fb4710f6bcba3388733b6c9                          |       909 | 2019-01-01 | 2019-01-01 |
|     2 | done1f27eaa8663a6eabfa2d19a79aa36387.VisitsSummary,donea163d7d5c703543e558ed10fbc862f20.Goals            |      4036 | 2019-01-02 | 2019-01-02 |
|     2 | done1f27eaa8663a6eabfa2d19a79aa36387.Goals,donea163d7d5c703543e558ed10fbc862f20.Goals                    |      4037 | 2019-01-02 | 2019-01-02 |
|     2 | done06e00bbad0fb1c701da685477ab2b183.VisitsSummary,done552fdc0cf9522a971170b55914f47989.VisitsSummary    |      4050 | 2019-01-03 | 2019-01-03 |
|     2 | done06e00bbad0fb1c701da685477ab2b183.Contents,done552fdc0cf9522a971170b55914f47989.Goals                 |      4051 | 2019-01-03 | 2019-01-03 |
|     2 | done3691a82d36972a5ad807f8e156642c34.VisitsSummary,done552fdc0cf9522a971170b55914f47989.Goals            |      4054 | 2019-01-03 | 2019-01-03 |
|     2 | done3691a82d36972a5ad807f8e156642c34.Contents,donea163d7d5c703543e558ed10fbc862f20.Goals                 |      4055 | 2019-01-03 | 2019-01-03 |
|     2 | done50bbd9a26834d00a041254b94283620f.VisitsSummary,donef0e6ea3df087c58b6591e1459544aa51.VisitsSummary    |      4058 | 2019-01-03 | 2019-01-03 |
|     2 | done50bbd9a26834d00a041254b94283620f.Contents,donef0e6ea3df087c58b6591e1459544aa51.VisitsSummary         |      4059 | 2019-01-03 | 2019-01-03 |
|     2 | done50bbd9a26834d00a041254b94283620f.VisitsSummary,done676f41195e3f952697c4c05d19a35bc6.VisitsSummary    |      4202 | 2019-01-03 | 2019-01-03 |
|     2 | done50bbd9a26834d00a041254b94283620f.Contents,done676f41195e3f952697c4c05d19a35bc6.Goals                 |      4203 | 2019-01-03 | 2019-01-03 |
|     2 | done06e00bbad0fb1c701da685477ab2b183.VisitsSummary,donef0e6ea3df087c58b6591e1459544aa51.Goals            |      4204 | 2019-01-03 | 2019-01-03 |
|     2 | done06e00bbad0fb1c701da685477ab2b183.Contents,donef0e6ea3df087c58b6591e1459544aa51.Goals                 |      4205 | 2019-01-03 | 2019-01-03 |
[........]
[ > 8k rows displayed here and cut out]
[........]
|     2 | done2ab49b4d5224aa24d23fdf8a5fe32768.Goals,done8201a92efc58729046e0bd68b20adf4d.Goals                    |    133614 | 2019-01-01 | 2019-12-31 |
+-------+----------------------------------------------------------------------------------------------------------+-----------+------------+------------+
8569 rows in set (0.16 sec)

Workaround

To fix this issue:

  1. Set Matomo into maintenance mode
  2. executed the following SQL query for each archive_numeric table:
drop table tmp_archive_idarchives;
create temporary table tmp_archive_idarchives select distinct idarchive from archive_numeric_2019_04 where name like 'done%' group by idarchive having count(*) > 1;
delete from archive_numeric_2019_04 WHERE idarchive IN (select idarchive from tmp_archive_idarchives);

drop table tmp_archive_idarchives;
create temporary table tmp_archive_idarchives select distinct idarchive from archive_numeric_2019_03 where name like 'done%' group by idarchive having count(*) > 1;
delete from archive_numeric_2019_03 WHERE idarchive IN (select idarchive from tmp_archive_idarchives);

drop table tmp_archive_idarchives;
create temporary table tmp_archive_idarchives select distinct idarchive from archive_numeric_2019_02 where name like 'done%' group by idarchive having count(*) > 1;
delete from archive_numeric_2019_02 WHERE idarchive IN (select idarchive from tmp_archive_idarchives);

drop table tmp_archive_idarchives;
create temporary table tmp_archive_idarchives select distinct idarchive from archive_numeric_2019_01 where name like 'done%' group by idarchive having count(*) > 1;
delete from archive_numeric_2019_01 WHERE idarchive IN (select idarchive from tmp_archive_idarchives);

drop table tmp_archive_idarchives;
create temporary table tmp_archive_idarchives select distinct idarchive from archive_numeric_2018_12 where name like 'done%' group by idarchive having count(*) > 1;
delete from archive_numeric_2018_12 WHERE idarchive IN (select idarchive from tmp_archive_idarchives);

drop table tmp_archive_idarchives;
create temporary table tmp_archive_idarchives select distinct idarchive from archive_numeric_2018_11 where name like 'done%' group by idarchive having count(*) > 1;
delete from archive_numeric_2018_11 WHERE idarchive IN (select idarchive from tmp_archive_idarchives);


drop table tmp_archive_idarchives;
create temporary table tmp_archive_idarchives select distinct idarchive from archive_numeric_2018_10 where name like 'done%' group by idarchive having count(*) > 1;
delete from archive_numeric_2018_10 WHERE idarchive IN (select idarchive from tmp_archive_idarchives);


drop table tmp_archive_idarchives;
create temporary table tmp_archive_idarchives select distinct idarchive from archive_numeric_2018_09 where name like 'done%' group by idarchive having count(*) > 1;
delete from archive_numeric_2018_09 WHERE idarchive IN (select idarchive from tmp_archive_idarchives);

Reasons

Cause & root bug is still unknown... looking at the done flags, it looks like 2 totally different archives processed at different times yet having the same idarchive:

select count(*) as count, GROUP_CONCAT(name), group_concat(ts_archived), idarchive, group_concat(date1), group_concat(date2) from archive_numeric_2018_11 where name like 'done%' group by idarchive having count > 1 limit 10;
+-------+-------------------------------------------------------------------------------------------------------+-----------------------------------------+-----------+-----------------------+-----------------------+
| count | GROUP_CONCAT(name)                                                                                    | group_concat(ts_archived)               | idarchive | group_concat(date1)   | group_concat(date2)   |
+-------+-------------------------------------------------------------------------------------------------------+-----------------------------------------+-----------+-----------------------+-----------------------+
|     2 | done,done83140f5b45bc7dcddb3f9991625fa0e0.VisitsSummary                                               | 2019-04-28 21:29:24,2019-03-14 19:56:18 |     55778 | 2018-11-01,2018-11-07 | 2018-11-01,2018-11-07 |
|     2 | done83140f5b45bc7dcddb3f9991625fa0e0.VisitsSummary,done90a5a511e1974bca37613b6daec137ba.VisitsSummary | 2019-03-14 19:56:19,2019-04-28 21:29:24 |     55779 | 2018-11-08,2018-11-01 | 2018-11-08,2018-11-01 |
|     2 | done83140f5b45bc7dcddb3f9991625fa0e0.VisitsSummary,done90a5a511e1974bca37613b6daec137ba.Goals         | 2019-03-14 19:56:20,2019-04-28 21:29:24 |     55780 | 2018-11-09,2018-11-01 | 2018-11-09,2018-11-01 |
|     2 | done3dc29dcf40518a31c228516ecaf3bdca.VisitsSummary,donefea44bece172bc9696ae57c26888bf8a.VisitsSummary | 2019-03-14 19:56:21,2019-04-28 21:29:24 |     55781 | 2018-11-21,2018-11-01 | 2018-11-21,2018-11-01 |
|     2 | done83140f5b45bc7dcddb3f9991625fa0e0.VisitsSummary,donefea44bece172bc9696ae57c26888bf8a.Goals         | 2019-03-14 19:56:21,2019-04-28 21:29:24 |     55782 | 2018-11-10,2018-11-01 | 2018-11-10,2018-11-01 |
|     2 | done83140f5b45bc7dcddb3f9991625fa0e0.VisitsSummary,donec276dccc46ea23c1bffdd046127f41d6.UserCountry   | 2019-03-14 19:56:22,2019-04-28 21:29:24 |     55783 | 2018-11-11,2018-11-01 | 2018-11-11,2018-11-01 |
|     2 | done,done3dc29dcf40518a31c228516ecaf3bdca.VisitsSummary                                               | 2019-04-28 21:29:24,2019-03-14 19:56:22 |     55784 | 2018-11-02,2018-11-22 | 2018-11-02,2018-11-22 |
|     2 | done83140f5b45bc7dcddb3f9991625fa0e0.VisitsSummary,done90a5a511e1974bca37613b6daec137ba.VisitsSummary | 2019-03-14 19:56:31,2019-04-28 21:29:24 |     55785 | 2018-11-12,2018-11-02 | 2018-11-18,2018-11-02 |
|     2 | done83140f5b45bc7dcddb3f9991625fa0e0.VisitsSummary,done90a5a511e1974bca37613b6daec137ba.Goals         | 2019-03-14 19:56:23,2019-04-28 21:29:24 |     55786 | 2018-11-12,2018-11-02 | 2018-11-12,2018-11-02 |
|     2 | done3dc29dcf40518a31c228516ecaf3bdca.VisitsSummary,donefea44bece172bc9696ae57c26888bf8a.VisitsSummary | 2019-03-14 19:56:23,2019-04-28 21:29:24 |     55787 | 2018-11-23,2018-11-02 | 2018-11-23,2018-11-02 |
+-------+-------------------------------------------------------------------------------------------------------+-----------------------------------------+-----------+-----------------------+-----------------------+
10 rows in set (0.05 sec)
@mattab mattab added the Bug For errors / faults / flaws / inconsistencies etc. label Apr 28, 2019
@xpader
Copy link

xpader commented May 17, 2019

The same things:

mysql> SELECT COUNT(*) AS `count`,idarchive,name,date1,date2,period FROM `matomo_archive_numeric_2019_05` WHERE idsite=1  GROUP BY name,idsite,date1,date2,period HAVING `count`>1;
+-------+-----------+----------------------------------------------------+------------+------------+--------+
| count | idarchive | name                                               | date1      | date2      | period |
+-------+-----------+----------------------------------------------------+------------+------------+--------+
|    18 |     51164 | Actions_nb_downloads                               | 2019-05-01 | 2019-05-31 |      3 |
|     2 |     28538 | Actions_nb_downloads                               | 2019-05-05 | 2019-05-05 |      1 |
|    18 |     51164 | Actions_nb_hits_with_time_generation               | 2019-05-01 | 2019-05-31 |      3 |
|    24 |     51080 | Actions_nb_hits_with_time_generation               | 2019-05-13 | 2019-05-19 |      2 |
|    37 |     46046 | Actions_nb_hits_with_time_generation               | 2019-05-16 | 2019-05-16 |      1 |
|    27 |     51075 | Actions_nb_hits_with_time_generation               | 2019-05-17 | 2019-05-17 |      1 |
|    18 |     51164 | Actions_nb_keywords                                | 2019-05-01 | 2019-05-31 |      3 |
|     2 |     28538 | Actions_nb_keywords                                | 2019-05-05 | 2019-05-05 |      1 |
|    18 |     51164 | Actions_nb_outlinks                                | 2019-05-01 | 2019-05-31 |      3 |
|    24 |     51080 | Actions_nb_outlinks                                | 2019-05-13 | 2019-05-19 |      2 |
|    18 |     51164 | Actions_nb_pageviews                               | 2019-05-01 | 2019-05-31 |      3 |
|     2 |     28538 | Actions_nb_pageviews                               | 2019-05-05 | 2019-05-05 |      1 |
|    24 |     51080 | Actions_nb_pageviews                               | 2019-05-13 | 2019-05-19 |      2 |
|    37 |     46046 | Actions_nb_pageviews                               | 2019-05-16 | 2019-05-16 |      1 |
|    27 |     51075 | Actions_nb_pageviews                               | 2019-05-17 | 2019-05-17 |      1 |
|    18 |     51164 | Actions_nb_searches                                | 2019-05-01 | 2019-05-31 |      3 |
|     2 |     28538 | Actions_nb_searches                                | 2019-05-05 | 2019-05-05 |      1 |
|    18 |     51164 | Actions_nb_uniq_downloads                          | 2019-05-01 | 2019-05-31 |      3 |
|     2 |     28538 | Actions_nb_uniq_downloads                          | 2019-05-05 | 2019-05-05 |      1 |
|    18 |     51164 | Actions_nb_uniq_outlinks                           | 2019-05-01 | 2019-05-31 |      3 |
|    24 |     51080 | Actions_nb_uniq_outlinks                           | 2019-05-13 | 2019-05-19 |      2 |
|    18 |     51164 | Actions_nb_uniq_pageviews                          | 2019-05-01 | 2019-05-31 |      3 |
|     2 |     28538 | Actions_nb_uniq_pageviews                          | 2019-05-05 | 2019-05-05 |      1 |
|    24 |     51080 | Actions_nb_uniq_pageviews                          | 2019-05-13 | 2019-05-19 |      2 |
|    37 |     46046 | Actions_nb_uniq_pageviews                          | 2019-05-16 | 2019-05-16 |      1 |
|    27 |     51075 | Actions_nb_uniq_pageviews                          | 2019-05-17 | 2019-05-17 |      1 |
|    18 |     51164 | Actions_sum_time_generation                        | 2019-05-01 | 2019-05-31 |      3 |
|    24 |     51080 | Actions_sum_time_generation                        | 2019-05-13 | 2019-05-19 |      2 |
|    37 |     46046 | Actions_sum_time_generation                        | 2019-05-16 | 2019-05-16 |      1 |
|    27 |     51075 | Actions_sum_time_generation                        | 2019-05-17 | 2019-05-17 |      1 |
|     2 |     28801 | bounce_count                                       | 2019-05-01 | 2019-05-01 |      1 |
|   108 |     51164 | bounce_count                                       | 2019-05-01 | 2019-05-31 |      3 |
|     3 |     28806 | bounce_count                                       | 2019-05-02 | 2019-05-02 |      1 |
|     3 |     28811 | bounce_count                                       | 2019-05-03 | 2019-05-03 |      1 |
|     3 |     28826 | bounce_count                                       | 2019-05-04 | 2019-05-04 |      1 |
|     4 |     28403 | bounce_count                                       | 2019-05-05 | 2019-05-05 |      1 |
|     3 |     27849 | bounce_count                                       | 2019-05-06 | 2019-05-06 |      1 |
|     3 |     27848 | bounce_count                                       | 2019-05-06 | 2019-05-12 |      2 |
|     3 |     27321 | bounce_count                                       | 2019-05-07 | 2019-05-07 |      1 |
|     3 |     26689 | bounce_count                                       | 2019-05-08 | 2019-05-08 |      1 |
|     3 |     26400 | bounce_count                                       | 2019-05-09 | 2019-05-09 |      1 |
|     3 |     25036 | bounce_count                                       | 2019-05-10 | 2019-05-10 |      1 |
|     3 |     24970 | bounce_count                                       | 2019-05-11 | 2019-05-11 |      1 |
|     3 |     24430 | bounce_count                                       | 2019-05-12 | 2019-05-12 |      1 |
|     3 |     32284 | bounce_count                                       | 2019-05-13 | 2019-05-13 |      1 |
|   207 |     51080 | bounce_count                                       | 2019-05-13 | 2019-05-19 |      2 |
|     3 |     38912 | bounce_count                                       | 2019-05-14 | 2019-05-14 |      1 |
|     3 |     44605 | bounce_count                                       | 2019-05-15 | 2019-05-15 |      1 |
|   396 |     46046 | bounce_count                                       | 2019-05-16 | 2019-05-16 |      1 |
|   184 |     51075 | bounce_count                                       | 2019-05-17 | 2019-05-17 |      1 |
|    18 |     51164 | done                                               | 2019-05-01 | 2019-05-31 |      3 |
|     2 |     28538 | done                                               | 2019-05-05 | 2019-05-05 |      1 |
|    24 |     51080 | done                                               | 2019-05-13 | 2019-05-19 |      2 |
|    37 |     46046 | done                                               | 2019-05-16 | 2019-05-16 |      1 |
|    27 |     51075 | done                                               | 2019-05-17 | 2019-05-17 |      1 |
|    59 |     46141 | done                                               | 2019-05-18 | 2019-05-18 |      1 |
|    17 |     51222 | done                                               | 2019-05-19 | 2019-05-19 |      1 |
|    12 |     51274 | done1f27eaa8663a6eabfa2d19a79aa36387.Goals         | 2019-05-01 | 2019-05-31 |      3 |
|     2 |     28415 | done1f27eaa8663a6eabfa2d19a79aa36387.Goals         | 2019-05-04 | 2019-05-04 |      1 |
|     2 |     26742 | done1f27eaa8663a6eabfa2d19a79aa36387.Goals         | 2019-05-07 | 2019-05-07 |      1 |
|     3 |     24990 | done1f27eaa8663a6eabfa2d19a79aa36387.Goals         | 2019-05-09 | 2019-05-09 |      1 |
|    30 |     51120 | done1f27eaa8663a6eabfa2d19a79aa36387.Goals         | 2019-05-13 | 2019-05-19 |      2 |
|    72 |     46065 | done1f27eaa8663a6eabfa2d19a79aa36387.Goals         | 2019-05-16 | 2019-05-16 |      1 |
|    54 |     51094 | done1f27eaa8663a6eabfa2d19a79aa36387.Goals         | 2019-05-17 | 2019-05-17 |      1 |
|    12 |     51273 | done1f27eaa8663a6eabfa2d19a79aa36387.VisitsSummary | 2019-05-01 | 2019-05-31 |      3 |
|    30 |     51118 | done1f27eaa8663a6eabfa2d19a79aa36387.VisitsSummary | 2019-05-13 | 2019-05-19 |      2 |
|    73 |     46064 | done1f27eaa8663a6eabfa2d19a79aa36387.VisitsSummary | 2019-05-16 | 2019-05-16 |      1 |
|    55 |     51093 | done1f27eaa8663a6eabfa2d19a79aa36387.VisitsSummary | 2019-05-17 | 2019-05-17 |      1 |
|    84 |     46190 | done1f27eaa8663a6eabfa2d19a79aa36387.VisitsSummary | 2019-05-18 | 2019-05-18 |      1 |
|    27 |     51186 | done1f27eaa8663a6eabfa2d19a79aa36387.VisitsSummary | 2019-05-19 | 2019-05-19 |      1 |
|    15 |     51302 | done5aa0e3c4a2c285b80bcc0cd6e8a62f3e.Goals         | 2019-05-01 | 2019-05-31 |      3 |
|     2 |     28503 | done5aa0e3c4a2c285b80bcc0cd6e8a62f3e.Goals         | 2019-05-02 | 2019-05-02 |      1 |
|     4 |     28508 | done5aa0e3c4a2c285b80bcc0cd6e8a62f3e.Goals         | 2019-05-03 | 2019-05-03 |      1 |
|     4 |     28292 | done5aa0e3c4a2c285b80bcc0cd6e8a62f3e.Goals         | 2019-05-04 | 2019-05-04 |      1 |
|     2 |     26768 | done5aa0e3c4a2c285b80bcc0cd6e8a62f3e.Goals         | 2019-05-07 | 2019-05-07 |      1 |
|     2 |     25005 | done5aa0e3c4a2c285b80bcc0cd6e8a62f3e.Goals         | 2019-05-09 | 2019-05-09 |      1 |
|    48 |     51134 | done5aa0e3c4a2c285b80bcc0cd6e8a62f3e.Goals         | 2019-05-13 | 2019-05-19 |      2 |
|    97 |     46075 | done5aa0e3c4a2c285b80bcc0cd6e8a62f3e.Goals         | 2019-05-16 | 2019-05-16 |      1 |
|    73 |     51104 | done5aa0e3c4a2c285b80bcc0cd6e8a62f3e.Goals         | 2019-05-17 | 2019-05-17 |      1 |
|    18 |     51301 | done5aa0e3c4a2c285b80bcc0cd6e8a62f3e.VisitsSummary | 2019-05-01 | 2019-05-31 |      3 |
|    48 |     51132 | done5aa0e3c4a2c285b80bcc0cd6e8a62f3e.VisitsSummary | 2019-05-13 | 2019-05-19 |      2 |
|    99 |     46074 | done5aa0e3c4a2c285b80bcc0cd6e8a62f3e.VisitsSummary | 2019-05-16 | 2019-05-16 |      1 |
|    73 |     51103 | done5aa0e3c4a2c285b80bcc0cd6e8a62f3e.VisitsSummary | 2019-05-17 | 2019-05-17 |      1 |
|   139 |     46214 | done5aa0e3c4a2c285b80bcc0cd6e8a62f3e.VisitsSummary | 2019-05-18 | 2019-05-18 |      1 |
|    45 |     51210 | done5aa0e3c4a2c285b80bcc0cd6e8a62f3e.VisitsSummary | 2019-05-19 | 2019-05-19 |      1 |
|    27 |     51170 | done90a5a511e1974bca37613b6daec137ba.Goals         | 2019-05-01 | 2019-05-31 |      3 |
|    53 |     51090 | done90a5a511e1974bca37613b6daec137ba.Goals         | 2019-05-13 | 2019-05-19 |      2 |
|   109 |     46048 | done90a5a511e1974bca37613b6daec137ba.Goals         | 2019-05-16 | 2019-05-16 |      1 |
|    82 |     51077 | done90a5a511e1974bca37613b6daec137ba.Goals         | 2019-05-17 | 2019-05-17 |      1 |
|   142 |     46154 | done90a5a511e1974bca37613b6daec137ba.Goals         | 2019-05-18 | 2019-05-18 |      1 |
|    44 |     51183 | done90a5a511e1974bca37613b6daec137ba.Goals         | 2019-05-19 | 2019-05-19 |      1 |
|    33 |     51165 | done90a5a511e1974bca37613b6daec137ba.VisitsSummary | 2019-05-01 | 2019-05-31 |      3 |
|    80 |     51087 | done90a5a511e1974bca37613b6daec137ba.VisitsSummary | 2019-05-13 | 2019-05-19 |      2 |
|   217 |     46047 | done90a5a511e1974bca37613b6daec137ba.VisitsSummary | 2019-05-16 | 2019-05-16 |      1 |
|   157 |     51076 | done90a5a511e1974bca37613b6daec137ba.VisitsSummary | 2019-05-17 | 2019-05-17 |      1 |
|   360 |     46145 | done90a5a511e1974bca37613b6daec137ba.VisitsSummary | 2019-05-18 | 2019-05-18 |      1 |
|   110 |     51169 | done90a5a511e1974bca37613b6daec137ba.VisitsSummary | 2019-05-19 | 2019-05-19 |      1 |
|    45 |     51193 | donefea44bece172bc9696ae57c26888bf8a.Goals         | 2019-05-01 | 2019-05-31 |      3 |
|     2 |     28445 | donefea44bece172bc9696ae57c26888bf8a.Goals         | 2019-05-05 | 2019-05-05 |      1 |
|     2 |     25011 | donefea44bece172bc9696ae57c26888bf8a.Goals         | 2019-05-11 | 2019-05-11 |      1 |
|   119 |     51100 | donefea44bece172bc9696ae57c26888bf8a.Goals         | 2019-05-13 | 2019-05-19 |      2 |
|   230 |     46050 | donefea44bece172bc9696ae57c26888bf8a.Goals         | 2019-05-16 | 2019-05-16 |      1 |
|   171 |     51079 | donefea44bece172bc9696ae57c26888bf8a.Goals         | 2019-05-17 | 2019-05-17 |      1 |
|   196 |     46168 | donefea44bece172bc9696ae57c26888bf8a.Goals         | 2019-05-18 | 2019-05-18 |      1 |
|    62 |     51207 | donefea44bece172bc9696ae57c26888bf8a.Goals         | 2019-05-19 | 2019-05-19 |      1 |
|    57 |     51188 | donefea44bece172bc9696ae57c26888bf8a.VisitsSummary | 2019-05-01 | 2019-05-31 |      3 |
|   103 |     51097 | donefea44bece172bc9696ae57c26888bf8a.VisitsSummary | 2019-05-13 | 2019-05-19 |      2 |
|   285 |     46049 | donefea44bece172bc9696ae57c26888bf8a.VisitsSummary | 2019-05-16 | 2019-05-16 |      1 |
|   190 |     51078 | donefea44bece172bc9696ae57c26888bf8a.VisitsSummary | 2019-05-17 | 2019-05-17 |      1 |
|   477 |     46158 | donefea44bece172bc9696ae57c26888bf8a.VisitsSummary | 2019-05-18 | 2019-05-18 |      1 |
|   145 |     51192 | donefea44bece172bc9696ae57c26888bf8a.VisitsSummary | 2019-05-19 | 2019-05-19 |      1 |
|     2 |     28801 | max_actions                                        | 2019-05-01 | 2019-05-01 |      1 |
|   108 |     51164 | max_actions                                        | 2019-05-01 | 2019-05-31 |      3 |
|     3 |     28806 | max_actions                                        | 2019-05-02 | 2019-05-02 |      1 |
|     3 |     28811 | max_actions                                        | 2019-05-03 | 2019-05-03 |      1 |
|     3 |     28826 | max_actions                                        | 2019-05-04 | 2019-05-04 |      1 |
|     4 |     28403 | max_actions                                        | 2019-05-05 | 2019-05-05 |      1 |
|     3 |     27849 | max_actions                                        | 2019-05-06 | 2019-05-06 |      1 |
|     3 |     27848 | max_actions                                        | 2019-05-06 | 2019-05-12 |      2 |
|     3 |     27321 | max_actions                                        | 2019-05-07 | 2019-05-07 |      1 |
|     3 |     26689 | max_actions                                        | 2019-05-08 | 2019-05-08 |      1 |
|     3 |     26400 | max_actions                                        | 2019-05-09 | 2019-05-09 |      1 |
|     3 |     25036 | max_actions                                        | 2019-05-10 | 2019-05-10 |      1 |
|     3 |     24970 | max_actions                                        | 2019-05-11 | 2019-05-11 |      1 |
|     3 |     24430 | max_actions                                        | 2019-05-12 | 2019-05-12 |      1 |
|     3 |     32284 | max_actions                                        | 2019-05-13 | 2019-05-13 |      1 |
|   207 |     51080 | max_actions                                        | 2019-05-13 | 2019-05-19 |      2 |
|     3 |     38912 | max_actions                                        | 2019-05-14 | 2019-05-14 |      1 |
|     3 |     44605 | max_actions                                        | 2019-05-15 | 2019-05-15 |      1 |
|   539 |     46046 | max_actions                                        | 2019-05-16 | 2019-05-16 |      1 |
|   374 |     51075 | max_actions                                        | 2019-05-17 | 2019-05-17 |      1 |
|     2 |     28801 | nb_actions                                         | 2019-05-01 | 2019-05-01 |      1 |
|   108 |     51164 | nb_actions                                         | 2019-05-01 | 2019-05-31 |      3 |
|     3 |     28806 | nb_actions                                         | 2019-05-02 | 2019-05-02 |      1 |
|     3 |     28811 | nb_actions                                         | 2019-05-03 | 2019-05-03 |      1 |
|     3 |     28826 | nb_actions                                         | 2019-05-04 | 2019-05-04 |      1 |
|     4 |     28403 | nb_actions                                         | 2019-05-05 | 2019-05-05 |      1 |
|     3 |     27849 | nb_actions                                         | 2019-05-06 | 2019-05-06 |      1 |
|     3 |     27848 | nb_actions                                         | 2019-05-06 | 2019-05-12 |      2 |
|     3 |     27321 | nb_actions                                         | 2019-05-07 | 2019-05-07 |      1 |
|     3 |     26689 | nb_actions                                         | 2019-05-08 | 2019-05-08 |      1 |
|     3 |     26400 | nb_actions                                         | 2019-05-09 | 2019-05-09 |      1 |
|     3 |     25036 | nb_actions                                         | 2019-05-10 | 2019-05-10 |      1 |
|     3 |     24970 | nb_actions                                         | 2019-05-11 | 2019-05-11 |      1 |
|     3 |     24430 | nb_actions                                         | 2019-05-12 | 2019-05-12 |      1 |
|     3 |     32284 | nb_actions                                         | 2019-05-13 | 2019-05-13 |      1 |
|   207 |     51080 | nb_actions                                         | 2019-05-13 | 2019-05-19 |      2 |
|     3 |     38912 | nb_actions                                         | 2019-05-14 | 2019-05-14 |      1 |
|     3 |     44605 | nb_actions                                         | 2019-05-15 | 2019-05-15 |      1 |
|   539 |     46046 | nb_actions                                         | 2019-05-16 | 2019-05-16 |      1 |
|   374 |     51075 | nb_actions                                         | 2019-05-17 | 2019-05-17 |      1 |
|     2 |     28801 | nb_uniq_visitors                                   | 2019-05-01 | 2019-05-01 |      1 |
|   108 |     51164 | nb_uniq_visitors                                   | 2019-05-01 | 2019-05-31 |      3 |
|     3 |     28806 | nb_uniq_visitors                                   | 2019-05-02 | 2019-05-02 |      1 |
|     3 |     28811 | nb_uniq_visitors                                   | 2019-05-03 | 2019-05-03 |      1 |
|     3 |     28826 | nb_uniq_visitors                                   | 2019-05-04 | 2019-05-04 |      1 |
|     4 |     28403 | nb_uniq_visitors                                   | 2019-05-05 | 2019-05-05 |      1 |
|     3 |     27849 | nb_uniq_visitors                                   | 2019-05-06 | 2019-05-06 |      1 |
|     3 |     27848 | nb_uniq_visitors                                   | 2019-05-06 | 2019-05-12 |      2 |
|     3 |     27321 | nb_uniq_visitors                                   | 2019-05-07 | 2019-05-07 |      1 |
|     3 |     26689 | nb_uniq_visitors                                   | 2019-05-08 | 2019-05-08 |      1 |
|     3 |     26400 | nb_uniq_visitors                                   | 2019-05-09 | 2019-05-09 |      1 |
|     3 |     25036 | nb_uniq_visitors                                   | 2019-05-10 | 2019-05-10 |      1 |
|     3 |     24970 | nb_uniq_visitors                                   | 2019-05-11 | 2019-05-11 |      1 |
|     3 |     24430 | nb_uniq_visitors                                   | 2019-05-12 | 2019-05-12 |      1 |
|     3 |     32284 | nb_uniq_visitors                                   | 2019-05-13 | 2019-05-13 |      1 |
|   207 |     51080 | nb_uniq_visitors                                   | 2019-05-13 | 2019-05-19 |      2 |
|     3 |     38912 | nb_uniq_visitors                                   | 2019-05-14 | 2019-05-14 |      1 |
|     3 |     44605 | nb_uniq_visitors                                   | 2019-05-15 | 2019-05-15 |      1 |
|   539 |     46046 | nb_uniq_visitors                                   | 2019-05-16 | 2019-05-16 |      1 |
|   374 |     51075 | nb_uniq_visitors                                   | 2019-05-17 | 2019-05-17 |      1 |
|     2 |     28801 | nb_visits                                          | 2019-05-01 | 2019-05-01 |      1 |
|   108 |     51164 | nb_visits                                          | 2019-05-01 | 2019-05-31 |      3 |
|     3 |     28806 | nb_visits                                          | 2019-05-02 | 2019-05-02 |      1 |
|     3 |     28811 | nb_visits                                          | 2019-05-03 | 2019-05-03 |      1 |
|     3 |     28826 | nb_visits                                          | 2019-05-04 | 2019-05-04 |      1 |
|     4 |     28403 | nb_visits                                          | 2019-05-05 | 2019-05-05 |      1 |
|     3 |     27849 | nb_visits                                          | 2019-05-06 | 2019-05-06 |      1 |
|     3 |     27848 | nb_visits                                          | 2019-05-06 | 2019-05-12 |      2 |
|     3 |     27321 | nb_visits                                          | 2019-05-07 | 2019-05-07 |      1 |
|     3 |     26689 | nb_visits                                          | 2019-05-08 | 2019-05-08 |      1 |
|     3 |     26400 | nb_visits                                          | 2019-05-09 | 2019-05-09 |      1 |
|     3 |     25036 | nb_visits                                          | 2019-05-10 | 2019-05-10 |      1 |
|     3 |     24970 | nb_visits                                          | 2019-05-11 | 2019-05-11 |      1 |
|     3 |     24430 | nb_visits                                          | 2019-05-12 | 2019-05-12 |      1 |
|     3 |     32284 | nb_visits                                          | 2019-05-13 | 2019-05-13 |      1 |
|   207 |     51080 | nb_visits                                          | 2019-05-13 | 2019-05-19 |      2 |
|     3 |     38912 | nb_visits                                          | 2019-05-14 | 2019-05-14 |      1 |
|     3 |     44605 | nb_visits                                          | 2019-05-15 | 2019-05-15 |      1 |
|   539 |     46046 | nb_visits                                          | 2019-05-16 | 2019-05-16 |      1 |
|   374 |     51075 | nb_visits                                          | 2019-05-17 | 2019-05-17 |      1 |
|    18 |     51164 | Referrers_distinctCampaigns                        | 2019-05-01 | 2019-05-31 |      3 |
|     2 |     28538 | Referrers_distinctCampaigns                        | 2019-05-05 | 2019-05-05 |      1 |
|    18 |     51164 | Referrers_distinctKeywords                         | 2019-05-01 | 2019-05-31 |      3 |
|    18 |     51164 | Referrers_distinctSearchEngines                    | 2019-05-01 | 2019-05-31 |      3 |
|    18 |     51164 | Referrers_distinctWebsites                         | 2019-05-01 | 2019-05-31 |      3 |
|     2 |     28538 | Referrers_distinctWebsites                         | 2019-05-05 | 2019-05-05 |      1 |
|    24 |     51080 | Referrers_distinctWebsites                         | 2019-05-13 | 2019-05-19 |      2 |
|    18 |     51164 | Referrers_distinctWebsitesUrls                     | 2019-05-01 | 2019-05-31 |      3 |
|     2 |     28538 | Referrers_distinctWebsitesUrls                     | 2019-05-05 | 2019-05-05 |      1 |
|    24 |     51080 | Referrers_distinctWebsitesUrls                     | 2019-05-13 | 2019-05-19 |      2 |
|     2 |     28801 | sum_visit_length                                   | 2019-05-01 | 2019-05-01 |      1 |
|   108 |     51164 | sum_visit_length                                   | 2019-05-01 | 2019-05-31 |      3 |
|     2 |     28806 | sum_visit_length                                   | 2019-05-02 | 2019-05-02 |      1 |
|     3 |     28811 | sum_visit_length                                   | 2019-05-03 | 2019-05-03 |      1 |
|     3 |     28826 | sum_visit_length                                   | 2019-05-04 | 2019-05-04 |      1 |
|     4 |     28403 | sum_visit_length                                   | 2019-05-05 | 2019-05-05 |      1 |
|     3 |     27849 | sum_visit_length                                   | 2019-05-06 | 2019-05-06 |      1 |
|     3 |     27848 | sum_visit_length                                   | 2019-05-06 | 2019-05-12 |      2 |
|     3 |     27321 | sum_visit_length                                   | 2019-05-07 | 2019-05-07 |      1 |
|     3 |     26689 | sum_visit_length                                   | 2019-05-08 | 2019-05-08 |      1 |
|     3 |     26400 | sum_visit_length                                   | 2019-05-09 | 2019-05-09 |      1 |
|     3 |     25036 | sum_visit_length                                   | 2019-05-10 | 2019-05-10 |      1 |
|     3 |     24970 | sum_visit_length                                   | 2019-05-11 | 2019-05-11 |      1 |
|     3 |     24430 | sum_visit_length                                   | 2019-05-12 | 2019-05-12 |      1 |
|     3 |     32284 | sum_visit_length                                   | 2019-05-13 | 2019-05-13 |      1 |
|   207 |     51080 | sum_visit_length                                   | 2019-05-13 | 2019-05-19 |      2 |
|     3 |     38912 | sum_visit_length                                   | 2019-05-14 | 2019-05-14 |      1 |
|     3 |     44605 | sum_visit_length                                   | 2019-05-15 | 2019-05-15 |      1 |
|   539 |     46046 | sum_visit_length                                   | 2019-05-16 | 2019-05-16 |      1 |
|   374 |     51075 | sum_visit_length                                   | 2019-05-17 | 2019-05-17 |      1 |
|    18 |     51164 | UserCountry_distinctCountries                      | 2019-05-01 | 2019-05-31 |      3 |
|     2 |     28538 | UserCountry_distinctCountries                      | 2019-05-05 | 2019-05-05 |      1 |
|    24 |     51080 | UserCountry_distinctCountries                      | 2019-05-13 | 2019-05-19 |      2 |
|    37 |     46046 | UserCountry_distinctCountries                      | 2019-05-16 | 2019-05-16 |      1 |
|    27 |     51075 | UserCountry_distinctCountries                      | 2019-05-17 | 2019-05-17 |      1 |
+-------+-----------+----------------------------------------------------+------------+------------+--------+
226 rows in set (0.08 sec)

That wasted too much data space.

@xpader
Copy link

xpader commented May 17, 2019

Alot of same data has multi rows.

mysql> SELECT * FROM `matomo_archive_numeric_2019_05` WHERE `idsite`=1 AND `name`='Actions_nb_pageviews' AND `date1`='2019-05-05' AND `date2`='2019-05-05' AND period=1;
+-----------+----------------------+--------+------------+------------+--------+---------------------+--------+
| idarchive | name                 | idsite | date1      | date2      | period | ts_archived         | value  |
+-----------+----------------------+--------+------------+------------+--------+---------------------+--------+
|     28538 | Actions_nb_pageviews |      1 | 2019-05-05 | 2019-05-05 |      1 | 2019-05-13 01:24:38 | 623730 |
|     28637 | Actions_nb_pageviews |      1 | 2019-05-05 | 2019-05-05 |      1 | 2019-05-13 01:29:57 | 623730 |
+-----------+----------------------+--------+------------+------------+--------+---------------------+--------+
2 rows in set (0.01 sec)

mysql> SELECT * from `matomo_archive_numeric_2019_05` WHERE idsite=1 AND `name`='Actions_nb_downloads' AND `date1`='2019-05-01' AND `date2`='2019-05-31' AND `period`=3;
+-----------+----------------------+--------+------------+------------+--------+---------------------+-------+
| idarchive | name                 | idsite | date1      | date2      | period | ts_archived         | value |
+-----------+----------------------+--------+------------+------------+--------+---------------------+-------+
|     51164 | Actions_nb_downloads |      1 | 2019-05-01 | 2019-05-31 |      3 | 2019-05-17 00:16:11 |  2457 |
|     51214 | Actions_nb_downloads |      1 | 2019-05-01 | 2019-05-31 |      3 | 2019-05-17 00:17:59 |  2457 |
|     51325 | Actions_nb_downloads |      1 | 2019-05-01 | 2019-05-31 |      3 | 2019-05-17 00:20:32 |  2457 |
|     51409 | Actions_nb_downloads |      1 | 2019-05-01 | 2019-05-31 |      3 | 2019-05-17 00:22:06 |  2457 |
|     51567 | Actions_nb_downloads |      1 | 2019-05-01 | 2019-05-31 |      3 | 2019-05-17 00:25:13 |  2457 |
|     51771 | Actions_nb_downloads |      1 | 2019-05-01 | 2019-05-31 |      3 | 2019-05-17 00:29:26 |  2457 |
|     52015 | Actions_nb_downloads |      1 | 2019-05-01 | 2019-05-31 |      3 | 2019-05-17 02:08:58 |  2457 |
|     52100 | Actions_nb_downloads |      1 | 2019-05-01 | 2019-05-31 |      3 | 2019-05-17 02:10:49 |  2457 |
|     52211 | Actions_nb_downloads |      1 | 2019-05-01 | 2019-05-31 |      3 | 2019-05-17 02:13:14 |  2457 |
|     52295 | Actions_nb_downloads |      1 | 2019-05-01 | 2019-05-31 |      3 | 2019-05-17 02:14:51 |  2457 |
|     52457 | Actions_nb_downloads |      1 | 2019-05-01 | 2019-05-31 |      3 | 2019-05-17 02:18:19 |  2457 |
|     52661 | Actions_nb_downloads |      1 | 2019-05-01 | 2019-05-31 |      3 | 2019-05-17 02:22:37 |  2457 |
|     52914 | Actions_nb_downloads |      1 | 2019-05-01 | 2019-05-31 |      3 | 2019-05-17 04:22:33 |  2457 |
|     53003 | Actions_nb_downloads |      1 | 2019-05-01 | 2019-05-31 |      3 | 2019-05-17 04:27:34 |  2457 |
|     53114 | Actions_nb_downloads |      1 | 2019-05-01 | 2019-05-31 |      3 | 2019-05-17 04:33:32 |  2457 |
|     53198 | Actions_nb_downloads |      1 | 2019-05-01 | 2019-05-31 |      3 | 2019-05-17 04:37:22 |  2457 |
|     53354 | Actions_nb_downloads |      1 | 2019-05-01 | 2019-05-31 |      3 | 2019-05-17 04:42:58 |  2457 |
|     53538 | Actions_nb_downloads |      1 | 2019-05-01 | 2019-05-31 |      3 | 2019-05-17 04:50:24 |  2457 |
+-----------+----------------------+--------+------------+------------+--------+---------------------+-------+
18 rows in set (0.00 sec)

@mattab
Copy link
Member Author

mattab commented Mar 12, 2020

It hasn't been an issue recently for us.

Is anyone still experiencing this issue?

@mattab
Copy link
Member Author

mattab commented Dec 10, 2023

Thank you for the report 👍 To likely fix the issue, could you upgrade to the latest Matomo version?
see https://matomo.org/docs/update/

if you still experience this issue afterwards, please re-open this and post a comment. Thanks!

@mattab mattab closed this as not planned Won't fix, can't repro, duplicate, stale Dec 10, 2023
@sgiehl sgiehl added the not-in-changelog For issues or pull requests that should not be included in our release changelog on matomo.org. label Dec 11, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Bug For errors / faults / flaws / inconsistencies etc. not-in-changelog For issues or pull requests that should not be included in our release changelog on matomo.org.
Projects
None yet
Development

No branches or pull requests

3 participants