Api updates doesn't update database. meteotemplateLive.txt is ok

[psper]

Hi all,

I update my meteotemplate data using API.

Since 2022-01-12 My database is not updated but realtime data is. No graphs. Using MySql Workbench, I checked database table alldata is not updated since 2022-01-12.

I checked my database and there are no problems. Same user, same password and external access to it is ok.
Any idea way my database is not updated?
I send meteotemplateLive.txt, cache/apiCache.txt and cacheapiLog.txt

Thanks in advance!

[davidefa]

Hi, can you provide a link to your site?
First of all check your cache/apiLog.txt and wait for the datadase update interval ( every 5 minutes ) near end of the file you should see the database query and the query result ( or error if any ).
Did you ( or your hosting provider ) recently updated mysql or made any changes?
Do you have any error regarding ONLY_FULL_GROUP_BY? A few days ago an user on wxforum, after updating his mysql server, had a problem with his database ( see this post )

api2.png (109.8 KiB) Viewed 6446 times

[psper]

Hi!

My site URL
http://www.7lab.pt/Meteo/indexDesktop.php

My apiLog.txt has a different format from yours.
I attach all my cache/apiLog.txt. Two images.
Can I define another level of logging?


Thanks!

[davidefa]

The format is the same ( I have only removed the blank lines... and a few other changes ).
Every 5 minutes api.php saves data to the database ( you have to wait this 'event' ):
- script does not connect to database
- in the main setup of meteotemplate, check the 4 parameters of database: host address, user, password, database name

api3.png (99.71 KiB) Viewed 6435 times

[psper]

Hi,

Finally it is solved. I start to debut api.php. apiLog.txt is the log.

My mysql DB was upgraded to version8 (automatic upgrade?).

First problem:

Error: Host 'xxx.xxx.xx.xx' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

Solution: I didn't use mysqladmin program. I logged with root and execute SQL "flush hosts;"

Second problem:

I start to receive this error: Error: "MySQL 8.0.1 error “Server sent charset unknown to the client.”

Solution:

Edit C:\ProgramData\MySQL\MySQL Server 8.0\my.ini like this:

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation-server = utf8_unicode_ci
character-set-server = utf8

Now is working Thanks !!!

[davidefa]

Thanks for sharing

[psper]

Hi,
It is not easy to catch the DB errors on apiLog.txt, because que apiLog.txt is deleted and created each time I use API. Since Api only write to DB one time each 5 minutes, and I update my site each minute, the possibility to see DB error is low.
I changed Api.php the DB interval for update for 60:
###############################################################
// Check db update / update db
###############################################################

$dbInterval = 300;

Than I start to have the possibility to see DB Error all the time and it was easier correct the problems.

Best regards

[davidefa]

Yes to see db errors you have to reload apiLog.txt 'a few times'.
Db is updated on the first api.php call after min 00, 05, 10...
If you reduce the db update interval to 1 min you multiply by 5 the records written in the db ( roughly 500K records/year ). This may impact performances.
Another thing you may consider is that if you do this database update interval change 'in the middle of the month' your month averages are altered ( you may find some blocks showing correct averages others incorrect values ).
This is due to the fact that days with different update interval have different 'weight'.
This should not have a 'dramatic' effect ( I mean you can live with it ).
This was noted by an user I helped to import historic data from a different server he was using ( with update interval of 10 mins ), comparing the averages of the two sites.
A 'quick and dirty' fix was to double the imported values ( only for the 'incriminated' month )