Support » Plugin: Matomo Analytics - Ethical Stats. Powerful Insights. » Error in “Visits over time”

  • Resolved duts2020

    (@duts2020)


    Hi,
    I have Matomo Analytics running on my site and since August 6th the area “Visits over time” has been showing the following error: “WP DB Error: [1173] This table type requires a primary key SQL: CREATE TEMPORARY TABLE wp_matomo_logtmpsegment61cf2c66075809150e7c503f11993813 (idvisit BIGINT(10) UNSIGNED NOT NULL)

    The area “Visits overview” has been showing zeroes in each category since the appearance of the above error.

    I will appreciate your help in resolving this error.

Viewing 15 replies - 1 through 15 (of 15 total)
  • Reply from WordPress’ customer support, “You should let the plugin developer know that all database tables need to be created with a primary key or the plugin won’t work on WordPress.com.”

    Plugin Author Thomas

    (@tsteur)

    Thank you very much for bringing this to our attention @duts2020

    We have prepared a fix which we will include in our next release in https://github.com/matomo-org/wp-matomo/pull/329/files . Any chance you could give this change a try already? Not sure if you can edit files on WordPress.com? If so, you would need to replace the file wp-content/plugins/matomo/app/core/DataAccess/LogAggregator.php with this content: https://raw.githubusercontent.com/matomo-org/wp-matomo/develop/app/core/DataAccess/LogAggregator.php

    Hi tsteur,
    I actually tried what you suggested and completely replaced the above-mentioned file. Unfortunately I am still getting the same error. Do you have any other suggestions?

    Plugin Author Thomas

    (@tsteur)

    Thanks for testing @duts2020 . Very appreciated.

    When does this exactly happen? by any chance it’s maybe not just an old error message in the Matomo System Report?

    Is this happening when you apply a segment for example? Or you simply view a report?

    tsteur,
    I first noticed this error on August 6th. I can see all the information before that date.
    I was simply checking my stats and the area “visits over time” still showing exactly the same error. The area “Visits overview” is showing zeroes in each category. I am still able to see the real time visits. So the issue is isolated to those two areas.
    I applied your fix and saved that file. Then I went to check the end result, but I am still seeing “WP DB Error: [1173] This table type requires a primary key SQL: CREATE TEMPORARY TABLE wp_matomo_logtmpsegment61cf2c66075809150e7c503f11993813 (idvisit BIGINT(10) UNSIGNED NOT NULL)”. Very strange indeed. I can send you screenshots if needed but you would have to provide your Email. Let me know if you need more details.

    Plugin Author Thomas

    (@tsteur)

    Thank you @duts2020

    Can you also try this change maybe? https://github.com/matomo-org/wp-matomo/pull/330/files

    I can generally reproduce the issue and the fix works for me but I’m receiving a slightly different error because I’m using MySQL 8 vs it seems there MariaDB is used. The error should be detected though and things should work. I wonder if otherwise the php file is maybe cached through opcache or something.

    Can you otherwise send me your Matomo system report to wordpress at matomo.org ? Can you confirm otherwise this is on WordPress.com ?

    tsteur,
    I tried to make a change you mentioned and after that change my real time statistics crashed as well. I had to remove the plugin and re-install it again so I could have at least real time statistics. Yes, my site is on wordpress.com just like I said.

    I will not try any other fixes for the time being. Hopefully this issue will be fixed someday.
    My only question: is this issue isolated one?

    Plugin Author Thomas

    (@tsteur)

    @duts2020 it’s possible it crashed if maybe by accident the lines weren’t 100% correctly replaced. You could otherwise again replace the same file with this updated content https://raw.githubusercontent.com/matomo-org/wp-matomo/develop/app/core/DataAccess/LogAggregator.php I totally understand though if you’re not keen on doing it again. I’ll include the fix in the next release and with the most recent changes I’d really expect things to work.

    If after the next update this still happens, it might be only a debug message which can be actually ignored. In this case you could disable these notices like this: https://matomo.org/faq/wordpress/how-do-i-suppress-db-errors-in-matomo-for-wordpress/

    Yes it’s quite an isolated one. Matomo is running on > 100.000 Matomo installations and we’ve heard of this particular error message only once before. Nevertheless I’m keen to get this fixed.

    @tsteur,
    I tried one more time and this time I replaced the whole file. It worked. The error is gone and the graph is finally visible.
    Thank you for your help!

    Plugin Author Thomas

    (@tsteur)

    Awesome, that’s great to hear. Thank you very much for your help.

    tsteur,
    I guess it was too early for me to report that everything is OK. The graph area was fixed but the Matomo is no longer registering any visitors even in real time. All the fields are showing 0 while there are visitors based on Jetpack’s stats. Any suggestions on how to fix this?

    tsteur,
    One part of the problem cleared on itself and the system is now registering visitors in real time. The graph is being present but the area “visits overview” is featuring zeroes in each category which is not right. Not sure why there is an issue with updating that information there.

    Plugin Author Thomas

    (@tsteur)

    Glad one part of the problem cleared on itself @duts2020 .

    Regarding the zeros in each category below the “visits overview”. I’m understanding that you had visits in the selected date range (eg yesterday). The visualisation below the graph shows “zero” for everything. Does the graph itself show data for the selected date?

    It’s possible it’s somehow related to the previous problem and that it won’t happen anymore in the future. We could likely correct this. Is the “Visits overview” now showing maybe data for recent dates like “today” or yesterday’s date assuming they had visits?

    tsteur,
    The data was finally updated in the category “visits overview”. It was probably connected to the initial issue. Right now I can say that all the issues previously reported have cleared.
    Thank you for your help!

    Plugin Author Thomas

    (@tsteur)

    Great to hear and thanks for your help too!

Viewing 15 replies - 1 through 15 (of 15 total)
  • You must be logged in to reply to this topic.