@mattab opened this Issue on April 28th 2019 Member

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:

```sql
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)
@xpader commented on May 17th 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 commented on May 17th 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)
Powered by GitHub Issue Mirror