Page 1 of 2

sql

Posted: Fri 27 Jan 2017 4:11 pm
by dazza1223
hi ive just setup SQL to use with top10.php

i get this in MXdiags


017-01-27 02:08:01.550 Error encountered during MySQL operation.
2017-01-27 02:08:01.550 Connection must be opened.
2017-01-27 02:08:03.550 Error encountered during MySQL operation.
2017-01-27 02:08:03.550 Connection must be opened.
2017-01-27 02:08:05.557 Error encountered during MySQL operation.
2017-01-27 02:08:05.558 Connection must be opened.
2017-01-27 02:08:07.568 Error encountered during MySQL operation.
2017-01-27 02:08:07.568 Connection must be opened.
2017-01-27 02:08:09.567 Error encountered during MySQL operation.
2017-01-27 02:08:09.568 Connection must be opened.
2017-01-27 02:08:11.577 Error encountered during MySQL operation.
2017-01-27 02:08:11.578 Connection must be opened.
2017-01-27 02:08:13.595 Error encountered during MySQL operation.
2017-01-27 02:08:13.595 Connection must be opened.
2017-01-27 02:08:15.578 Error encountered during MySQL operation.
2017-01-27 02:08:15.578 Connection must be opened.
2017-01-27 02:08:17.578 Error encountered during MySQL operation.
2017-01-27 02:08:17.579 Connection must be opened.
2017-01-27 02:08:19.579 Error encountered during MySQL operation.
2017-01-27 02:08:19.579 Connection must be opened.
2017-01-27 02:08:21.578 Error encountered during MySQL operation.
2017-01-27 02:08:21.579 Connection must be opened.
2017-01-27 02:08:23.578 Error encountered during MySQL operation.
2017-01-27 02:08:23.579 Connection must be opened.
2017-01-27 02:08:25.588 Error encountered during MySQL operation.
2017-01-27 02:08:25.588 Connection must be opened.
2017-01-27 02:08:27.597 Error encountered during MySQL operation.
2017-01-27 02:08:27.598 Connection must be opened.
2017-01-27 02:08:29.599 Error encountered during MySQL operation.
2017-01-27 02:08:29.600 Connection must be opened.
2017-01-27 02:08:31.598 Error encountered during MySQL operation.
2017-01-27 02:08:31.598 Connection must be opened.
2017-01-27 02:08:33.621 Error encountered during MySQL operation.
2017-01-27 02:08:33.622 Connection must be opened.
2017-01-27 02:08:35.598 Error encountered during MySQL operation.
2017-01-27 02:08:35.598 Connection must be opened.
2017-01-27 02:08:37.598 Error encountered during MySQL operation.
2017-01-27 02:08:37.598 Connection must be opened.
2017-01-27 02:08:39.601 Error encountered during MySQL operation.
2017-01-27 02:08:39.602 Connection must be opened.
2017-01-27 02:08:41.598 Error encountered during MySQL operation.
2017-01-27 02:08:41.598 Connection must be opened.
2017-01-27 02:08:43.738 Error encountered during MySQL operation.
2017-01-27 02:08:43.738 Connection must be opened.
2017-01-27 02:08:45.608 Error encountered during MySQL operation.
2017-01-27 02:08:45.608 Connection must be opened.
2017-01-27 02:08:47.608 Error encountered during MySQL operation.
2017-01-27 02:08:47.609 Connection must be opened.
2017-01-27 02:08:49.608 Error encountered during MySQL operation.
2017-01-27 02:08:49.608 Connection must be opened.
2017-01-27 02:08:51.608 Error encountered during MySQL operation.
2017-01-27 02:08:51.608 Connection must be opened.
2017-01-27 02:08:53.629 Error encountered during MySQL operation.
2017-01-27 02:08:53.629 Connection must be opened.
2017-01-27 02:08:55.609 Error encountered during MySQL operation.
2017-01-27 02:08:55.609 Connection must be opened.
2017-01-27 02:08:57.618 Error encountered during MySQL operation.
2017-01-27 02:08:57.618 Connection must be opened.
2017-01-27 02:08:59.617 Error encountered during MySQL operation.

is there somthink im missing

Re: sql

Posted: Fri 27 Jan 2017 4:17 pm
by steve
Looks like Cumulus was unable to connect to your SQL server - are there no error messages about this near the start of the diags file? Have you configured your SQL server details correctly in Cumulus? Does your SQL server allow remote connections? Some don't, and with those that do, sometimes you have to explicitly allow the remote connection. Your hosting provider should have this information.

Re: sql

Posted: Fri 27 Jan 2017 4:48 pm
by dazza1223
i will send you the file mate and i use sql on my raspberry pi 3 and the webserver the same pi

and i look in mysql and i can see the database be uploaded

and the setting in the Cumulus i uese host name 127.0.0.1


and port is 3306

but it must be ok as i se the database?

Re: sql

Posted: Fri 27 Jan 2017 5:05 pm
by steve
Looks like the updates into the 'Monthly' table are OK and the errors are from another update. Some kind of configuration error somewhere, perhaps. Perhaps you haven't created the other tables?

Re: sql

Posted: Fri 27 Jan 2017 5:13 pm
by dazza1223
o

um i just look and this what been uploaded

daydata

Monthly

Realtime

maybe ony let it upload daydata or Monthly ?

Re: sql

Posted: Fri 27 Jan 2017 7:36 pm
by steve
From the frequency of the error messages, it must be the connection for the realtime update which is failing. Unfortunately, I can't tell you why. Perhaps your SQL server logs can give you more information.

Re: sql

Posted: Fri 27 Jan 2017 9:23 pm
by dazza1223
ok mate i will check it out and i am still geting them web tag erros

Re: sql

Posted: Fri 27 Jan 2017 9:38 pm
by steve
I noticed that, and I believe I fixed the bug that could cause that, in 3043, so I have no idea why that's happening.

Re: sql

Posted: Fri 27 Jan 2017 9:48 pm
by dazza1223
o i saw that u fix a bug was it realtimegauges.txt?

Re: sql

Posted: Fri 27 Jan 2017 9:54 pm
by steve
The bug was that processing of the realtimegauges.txt file caused problems with the processing of other files.

You seem to be uploading lots of files at a the realtime interval. And using a very short realtime interval = 1 or 2 seconds? I'm not sure why you're doing that for files that don't change that frequently, but I suspect that the uploads are taking longer than the time you are allowing and hence one set of uploads is clashing with the next one (although in theory Cumulus shouldn't start the next upload if the previous one hasn't finished).

Re: sql

Posted: Fri 27 Jan 2017 9:58 pm
by steve
This might also be the cause of the SQL errors (too small a realtime interval for what you are trying to do in that interval).

Re: sql

Posted: Fri 27 Jan 2017 9:58 pm
by dazza1223
so i got Extra web files to /home/pi/usb/davis/web/realtimegaugesT.txt /home/pi/usb/website/realtimegauges.txt so i dont do it like that ?


and so do i uptick real time then mate?

and the realtime interval = 1 or 2 what shud it be?

Re: sql

Posted: Fri 27 Jan 2017 10:01 pm
by steve
If you just want to copy the realtimegauges.txt file locally, you will need to use Extra Files as you are doing (and select realtime for that one), and not tick the new option to do it automatically. You should untick the realtime setting for most if not all of your other files, it's pointless processing all-time records, for example, at 2-second intervals.

Re: sql

Posted: Fri 27 Jan 2017 10:04 pm
by dazza1223
and how meny did u say to set and the realtime interval ?

Re: sql

Posted: Fri 27 Jan 2017 10:08 pm
by steve
Once you've stopped trying to do so much at the realtime interval, 2 seconds might be a reasonoable setting (given that you have a Davis which updates wind speeds at 2 to 3 second intervals). if you still have problems you'll need to try increasing the interval.