API Update Issues

[Paul]

After running fine for 6 months on my new WebHost I noticed in the last few days my Meteotemplate DB was not updating every 5 mins as normal. There would often be gaps of 25/30/35 minutes.

I have WD updating every minute.

I first checked the meteotemplatelive.txt and this seemed to be updating but maybe not constantly
Though there is an issue with the values the API is writing, i.e. a snippet

Code: Select all

{"U":1620497296,"T":15.9000000000000003552713678800500929355621337890625,"TMX":15.9000000000000003552713678800500929355621337890625,

Not sure what is going on with the bizarre 'anti-rounding'

However the APIcache and APILog are not updating every minute as you would expect.
Sometimes they won't update for 5 or 10 minutes or more.
Then you might see issues like this:

Code: Select all

Time to update the database, preparing query.

Timestamp first cached record: 2021-05-08 18:42:16

Timestamp last cached record: 2021-05-08 18:44:16

Timestamp last received record: 2021-05-08 19:04:16

Timestamp new database record: 2021-05-08 18:45:00

No new day, currentRain=11.0

Preparing extra sensor query...

The extra database table is updated with the following query:
                INSERT INTO alldataExtra
                (TIN,HIN,UV,SS,DateTime)
                values (23.2,51,0.5,0,'2021-05-08 18:45:00')
           

Cache file deleted.

Saving first new data to cache/apiCache.txt, timestamp=2021-05-08 19:04:16

Generating log file cache/apiLog.txt


ERRORS:

Meteotemplate MySQL Error: Duplicate entry '2021-05-08 18:45:00' for key 'PRIMARY' with query:
            INSERT INTO alldata
            (DateTime,T,Tmax,Tmin,H,P,W,G,B,R,RR,S,D,A)
            values ('2021-05-08 18:45:00',15.9,15.9,15.9,77.0,1001.8,13.8,23.0,275,11.0,0.0,83.3,11.8,12.

Maybe WD isn't sending every minute? so after a bit of digging I found the WD sentcustomhttget file

which is where I saw that WD is trying every minute and found this reported:

Code: Select all

Error receiving data: (12002) The operation timed out

Aha, though i'm not sure if this a connectivity issue, a server response etc.

I'm guessing it must be an issue with my webhost that is stopping the called API PHP code from either running or writing files (though the meteotemplatelive.txt is updating more frequently than the API files which is odd). My connectivity seems fine.

Any ideas?

[hcorrin]

this error 12002 could be caused by windows itself first try rebooting your pc and router to see if it improves then try a search for the error
what response do you get from the test button in meteo template setup in WD

[davidefa]

The 'rounding problem' is present in PHP >= 7.1, it is not a real problem, but quite annoying, if you want to get rid of it add after the first few php lines

Code: Select all

ini_set( 'serialize_precision', -1 );       // so json_encode does not 'change roundness' ( for PHP >= 7.1 )

As you say, the timeout problem may be originated on your 'local' side or on the server side.
You can check your router's log to see if there is any connectivity problem/outage
Or use some software tool to check connectivity over an extended period of time

[Paul]

Pretty sure it is not a general connectivity issue.
I am still uploading clientraw from WD via FTP and that is successful.

The 12002 error is actually occurring on both machines I have here where WD is sending the HTTP GET to update via the API (two different WD on different machines updating 2 different MT on same Web Host).
Both are showing intermittent failures (more fail than succeed).

I note my Router has a syslog explorer on it so 'ill have a play with that tomorrow, then I'll give Wireshark a try but I'm not sure how to interpret it really, particularly if the problem is on the host side.

[Paul]

So I have done some more investigation on this.

First to rule out WD and general connectivity I used davidefa's param.php from another thread.
I pointed WD to this, updating once a minute, fired up WireShark and left it for a couple of hours.

This is the behaviour I observed, WD = Weather Display (or the underlying network stack of Windows), SRV = Web Host Server:

WD -> TCP SYN (Starting network connection)
SRV -> TCP SYN, ACK (acknowledge)
WD -> TCP ACK
WD -> HTTP Get (Sending paramters via URL)
SRV -> TCP ACK
SRV -> HTTP 200 OK (after PHP processed, usually instantly but sometimes up to 3s)
WD -> TCP RST, ACK (closing network connection)

This worked faultlessly for a couple of hours. So we know connectivity is OK and WD sends the request every minute.

So I pointed WD back at api.php

It will work as expected up until the next DB update time, i.e every 5 minutes,
Then the Server often fails to send the HTTP 200 OK response.
Though the meteotemplatelive.txt is always updated so the api.php is at least partially processing everytime.
When there is no response the api log and cache files do not get updated.

It can also get things out of synch, usually if WD did not receive a 200 OK response it will send a Reset (of the last HTTP 'thread') just before it starts again, but sometimes they overlap.

Anyway, something is stopping the php from being processed fully.

Could be processing, could be storage i/o but the obvious contender was the DB as the issue first occurs on update time.

Not aware of much you can do with Web Host mySQL DB as you have no root access but I did see in phpMyAdmin the PROCESSLIST table in the information_schema
So I kept refereshing this at update time and I noticed the following:

db_proc.JPG (88.78 KiB) Viewed 8644 times

Well there is the answer.

The ALTER TABLE stetment is taking a long time and locking the table meaning the INSERT statement won't happen.

I see Jachym had added the logic into the alldata update so that the alter table statement would only occur 00:00 - 00:10 but for me it was attempting to run, not sure why.

I've commented it out for now.

I notice the alldataextra update also has an alter table statement and it doesn't have the time base IF statement with it, I've commented that out too.

Hopefully, that should sort it out.

Next I need to think about the ramifications, especially as I now have a chunk of data I need to import, maybe a topic for a new thread.

[davidefa]

Good finding ( and thanks for sharing ).
I noted that the alter table is executed only in the first ten minutes of the day... but this is for both alldata and alldataExtra tables ( at least in my api.php, see attachment ).
I have the impression that there are some 'variants' in the meteotemplate 18.0 we received...
Still remain the question: why the alter table query is executed outside the set timeframe?!

[kd7eir]

Could you please show exactly what you commented out?