No curves in interactiveGraph (and others)

[Oli]

I've been trying for days to figure out how to get the curves to show up in my interactive graph. I only have an empty view there. From the side of my web hoster everything should fit, the PHP version is 7.4.
Has anyone also had this problem?

It affects interactiveGraph, but also the statistics.
Here for example

[davidefa]

You can try the attached script, just expand it in your pages/station directory and call, in your browser, the following address ( it should show the database query and the error returned ):

Code: Select all

https://c-c.lima.zone/template/pages/station/graphAjax1.php?parameter=H&value=all&interval=today&from=2022-06-02%2000:00&to=2022-06-02%2000:00&errors

- what sql server ( and version ) are you using?
- if you use mysql > 5.6 verify that the option ONLY_FULL_GROUP_BY is disabled ( see this or other similar post: viewtopic.php?t=2100#p10183 )

[Oli]

I use MySQL 8.0.25-15 and I will try to disable ONLY_FULL_GROUP with the command
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

But I have an "Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation" problem.
So I have to figure out how to GRANT ACCESS for the user.

your attached script shows:

query: SELECT DateTime, avg(H), min(H), max(H) FROM alldata WHERE DATE(DateTime) = CURDATE() GROUP BY YEAR(DateTime), MONTH(DateTime), DAY(DateTime), HOUR(DateTime), MINUTE(DateTime) ORDER BY DateTime
error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db_422653_2.alldata.DateTime' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, bool given in ..../pages/station/graphAjax1.php on line 148
{"name1":"Luftfeuchtigkeit","name2":"Spannweite"}

[davidefa]

Check in the control panel of your hosting provider if there is any option to configure the database ( or check your hosting provider if they can assist you or change database server, MariaDb is ok for example )

[Oli]

Okay, thank you
I'll do further investigations tomorrow and have already contacted my hosting provider, waiting for answer.
So in the worst case I have to change the provider

[Oli]

Okay, I got the infomration, that in that case I can only adjust the script and run the SET after establishing the MySQL connection (the scripts probably use the same code to connect).
However, I don't know enough about php to know where to put what in which script. The question is whether this is even possible?
(my hoster uses Percona Server, BTW)

[davidefa]

You need to add a line at the end of your config.php script similar to the following one:

Code: Select all

mysqli_query($con,"SET sql_mode='QUERY_RESULT_HERE'");

config2.png (147.34 KiB) Viewed 1127 times

The exact syntax can be derived executing the following query ( in phpmyadmin via your provider control panel ):

Code: Select all

SELECT @@sql_mode

If you are not familiar with phpmyadmin:
1) select your meteotemplate database
2) select the sql tab
3) paste the query and execute it cliccking the go button
4) copy the result of the query ( verify that the result does not contain any trailing dots, if this is the case go to step 5 )
5) check the full text radio button and press the go button again
4a) copy the result of the query
6) from the copied result remove the ',ONLY_FULL_GROUP_BY' and substitute in place of QUERY_RESULT_HERE in the mysqli_query posted above
( if unsure how to do the last step simply post the query result and I'll post the exact syntax of the statement )
7) add the statement near the end of the config.php script as shown above

phpmyadmin2.png (188.07 KiB) Viewed 1127 times

P.S.
First of all, it is a good idea to make a backup copy of your config.php script
No need to say that I can't guarantee that it works ( I mean I tested it in my server and it works... but it works even without it )

[Oli]

Thank you for the very very detailed and understandable instructions.
I started some steps, but after I had another problem, I switched to another provider (netcup), imported the database there, adjusted a few fields in the conifuguration and the site runs on the first try!