Database problems when installing HA 3.3.2 [Solved]
Posted: Fri Mar 17, 2023 9:45 am
I'm in progress of updating HA from 2.0.2 to 3.3.2 (on new HW). Most of the installation works like a charm, but I have some problems getting temperatures into the database, and later to get them presented on the House plan.
Some details about my setup:
Ubuntu 16.04 server
PHP 7.0.8-0ubuntu0.16.04.3 (cli) ( NTS )
mysql Ver 14.14 Distrib 5.7.15, for Linux (x86_64)
Telldus DUO interface.
There was no errors shown during installation.
Telldus core is installed and working properly, lights can be switched on/off from HA
Temperatures are listed properly when running "tdtool -l"
I have tried three different approaches to get the temperatures in to HA:
1 - Using weather parser. The parser is configured as described in the readme, and when running the parser on the command line i get the following feedback:
/usr/bin/php /var/www/ha/parser/parser.php
2 - Using the Telldus sensorevent script, as described by Daniel in the Sticky post. Not sure how to check the progress of this. I have not found any error log, but no data shows up in the sensor table.
3 - Using my old homemade script for HA 2.0.2. This is a crude bash script (I'm an amateur on scripting), see below:
This puts the values into the sensor table, and I'm able to retrieve them in HA and place them on the House plan.
But the values do not show on the House plan, it displays "Null" as the value. If i click the value I get the graph, presenting the actual value. I had a suspicion this could be because of som locale problem ("," vs ".") for the decimals, but it is the same for humidity values (which are only integers).
Also, if I try the "Logs->Measurements" option on the meny, I get the following error message:
Some details about my setup:
Ubuntu 16.04 server
PHP 7.0.8-0ubuntu0.16.04.3 (cli) ( NTS )
mysql Ver 14.14 Distrib 5.7.15, for Linux (x86_64)
Telldus DUO interface.
There was no errors shown during installation.
Telldus core is installed and working properly, lights can be switched on/off from HA
Temperatures are listed properly when running "tdtool -l"
I have tried three different approaches to get the temperatures in to HA:
1 - Using weather parser. The parser is configured as described in the readme, and when running the parser on the command line i get the following feedback:
/usr/bin/php /var/www/ha/parser/parser.php
But no data shows up in the databaseParsing 'telldusduo'
Found: mandolyn-temperaturehumidity-14, value: 21.9
Found: mandolyn-temperaturehumidity-14-humidity, value: 36
Found: mandolyn-temperaturehumidity-12, value: 11.3
Found: mandolyn-temperaturehumidity-12-humidity, value: 72
Found: mandolyn-temperaturehumidity-11, value: 4.8
Found: mandolyn-temperaturehumidity-11-humidity, value: 77
Parsing 'telldusduo' completed
2 - Using the Telldus sensorevent script, as described by Daniel in the Sticky post. Not sure how to check the progress of this. I have not found any error log, but no data shows up in the sensor table.
3 - Using my old homemade script for HA 2.0.2. This is a crude bash script (I'm an amateur on scripting), see below:
Code: Select all
#!/bin/bash
#telltmp.sh
#26.08.2012
ValidID=(11 12 13 14) #set valid temp sensor ID's
LIMIT=300 #threshold time in seconds for last valid reading
MyUSER="user" # USERNAME
MyPASS="pwd" # PASSWORD
MyHOST="localhost" # Hostname
MyDB="homeautomation" # Database name
IDOK=no
#Read latest tellstick status
tdtool -l | while read LINE
do
#Check if the first word in the line is "mandolyn", to check sensor type
if [[ $LINE == mandolyn* ]]
then
#Assign values to variables
y=0
for x in $LINE
do
y=$((y+1))
# echo "> [$x] nr.: $y"
case $y in
[3]*)
ID="$x"
;;
[4]*)
TEMP=$"${x%?}" #remove last character
;;
[5]*)
HUM=${x//[%]/} #remove % from value
;;
[6]*)
DATE="$x"
;;
[7]*)
TIME="$x"
;;
esac
done
#check if ID is valid
for z in "${ValidID[@]}"
do
#echo $z
if [[ $ID == $z ]]
then #ID is valid
IDOK=yes
fi
done
if [[ $IDOK == yes ]]
then
# echo "ID: $ID, Temp: $TEMP, Humidity: $HUM, Timestamp: $DATE $TIME"
CURRTIME=$(date +%s)
COMPTIME=$(date --date="$DATE $TIME" +%s)
diff=$(($CURRTIME - $COMPTIME))
if [ $diff -lt $LIMIT ]; then
echo "INSERT INTO temps(sensor_serial, temp_c, date) VALUES ('$ID','$TEMP','$DATE $TIME');" | mysql -u $MyUSER --password=$MyPASS $MyDB
echo "INSERT INTO temps(sensor_serial, temp_c, date) VALUES ('h$ID','$HUM','$DATE $TIME');" | mysql -u $MyUSER --password=$MyPASS $MyDB
fi
fi
fi
IDOK=no
done
But the values do not show on the House plan, it displays "Null" as the value. If i click the value I get the graph, presenting the actual value. I had a suspicion this could be because of som locale problem ("," vs ".") for the decimals, but it is the same for humidity values (which are only integers).
Also, if I try the "Logs->Measurements" option on the meny, I get the following error message:
Any ideas/ assistance would be greatly appreciatedOops, think we broke something while communicating with the database.
ERROR: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'homeautomation.s.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Query: SELECT s.name, st.unit, ROUND(temp_c, 1) as actual FROM temps t INNER JOIN tempsensors s ON t.sensor_serial = s.serial INNER JOIN sensortypes st ON st.id = s.sensortype WHERE date > DATE_SUB(NOW(), INTERVAL 1800 SECOND) GROUP BY t.sensor_serial ORDER BY date DESC
Params:
Array
(
)
Please correct the problem and try again.
Hint: If you get an error stating that you are missing a table and you have verified that your mysql_settings.php file is correct, delete the mysql_settings.php and put the install folder back and do the installation again.