"SHOW COLUMNS" request called by trackers

Support & Bugs
1

Hello,

I’m working with Google Cloud, 3 Virtual Machines as trackers, working with Google Cloud SQL.

I’m encountering an issue which prevent me completely to use Piwik : my trackers are calling the requests "SHOW COLUMNS FROM piwik_log_visit / piwik_log_link_visit_action […]"
I absolutely don’t know why this request is called, but Google Cloud SQL seems to thread this query, and by threading it with the “one_thread_per_connection” (thread_handling parameter in SQL), it grows to the max_connections instantly => Every next requests encounter the “Too many connection” error from MySQL…
Usually, it doesn’t last more than 30 minutes (which is HUGE amount of data lost), but today it’s being executed for more than 4 hours, and it does not finish at all. My database contains about 29G of data, and I’m tracking more than 500k visits per day, so maybe that’s why it takes so long, but it doesn’t explain why this request is called…

I can’t track anything so I’m losing all of my data atm… I searched a lot, even posted on stackoverflow but I got no answer for the moment (http://stackoverflow.com/questions/24631874/too-many-connections-mysql-threads-reaching-max-connections-with-one-thread-pe)

Is there a solution?

Thanks

Stephane

2

I heard about some “lock table” issue. Could it help?

3

Bumping the subject just in case, issue being still live :frowning:

Sometimes the “SHOW COLUMNS FROM piwik_log_conversion/piwik_log_visit” is called multiple times causing my database to be unavailable for a little moment.

4

Hi Stefan,
which version of Piwik do you use?

5

Hello,

I’m using Piwik 2.3.0.

EDIT : Regarding the “SHOW COLUMNS”, I think it’s just called once but the query is so slow (I think I have about 400 millions lines) that it is threaded, and since it is divided by 1000, it reaches the maximum threads available, and when another thread is asked, the 1st one is stopped and it just loops.

The question remains the same : How to avoid those “Show columns” query calls?

6

Moreover, I have a little question related to this, is there any “cache” function or anything that can be used to remember what inserts failed and do it again a few minutes/hours later when database is up, in order not to lose any data?

Thanks a lot for your answers matt and great job at maintaining Piwik.

7

Regarding the issue iwth SHOW COLUMNS and Google cloud, can you please create a issue on github ? then we can discuss with other devs best way to solve this for you and others using google cloud. https://github.com/piwik/piwik/issues

is there any “cache” function or anything that can be used to remember what inserts failed and do it again a few minutes/hours later when database is up, in order not to lose any data?

to make sure you don’t lose any data, the current best way to achieve this is to “disable real time tracking” and then replay the logs. This is a bit technical but is explained in this FAQ http://piwik.org/faq/how-to/faq_17033/

8

Thanks for your answer.

Disabling real time tracking is in fact a great way. Sadly, real-time is one of the main feature I need.
=> Is it possible to have the logs AND still keep real-time? That could make me able to “recover” data lost.

For the other issue with the “Show columns”, I may have a clue after 1 month of investigation : Could the DBStats plugin be related to this query? Maybe in conjonction with the fact I do not acrhive my data?
I disabled it and I hope it will solve my problem, I’ll keep this posted.
If it doesn’t work, I will create an issue.

Thanks for your help.

Stephane