Database problems when installing HA 3.3.2 [Solved]

http://karpero.mine.nu/ha

Moderators: tom_rosenback, Daniel, Telldus

Post Reply
tomolav
Posts: 7
Joined: Thu Jan 18, 2018 11:08 am

Database problems when installing HA 3.3.2 [Solved]

Post by tomolav » Thu Jan 18, 2018 11:08 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
Parsing '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
But no data shows up in the database
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
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:
Oops, 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.
Any ideas/ assistance would be greatly appreciated :)
Last edited by tomolav on Wed Oct 19, 2016 7:21 pm, edited 1 time in total.
/Br. Tom Olav

Daniel
Posts: 315
Joined: Thu Jan 18, 2018 11:08 am

Re: Database problems when installing HA 3.3.2

Post by Daniel » Thu Jan 18, 2018 11:08 am

Well, I'd recommend using the sensorevent script method, so I'll try to help you get that one going. Firstly, do you get any /tmp/tdsensor.log and if so, what's in it? If not, could you try moving the echo call in the script up two rows (so it gets before the if) and see if that renders anything in the log?

I checked the sql error you get on the measurements page. Seems like the default setting for sql_mode was changed in 5.7.5, giving that error. Probably we should fix our sql query, but for the time being I think you could try adding the following line to your /etc/mysql/my.cnf to get the old behaviour back:

SET sql_mode = ''

/Daniel
Senaste info och release om/av HomeAutomation hittas här http://karpero.mine.nu/ha
Latest info and release of HomeAutomation can be found here http://karpero.mine.nu/ha

tomolav
Posts: 7
Joined: Thu Jan 18, 2018 11:08 am

Re: Database problems when installing HA 3.3.2

Post by tomolav » Thu Jan 18, 2018 11:08 am

Thank's for the prompt reply :)

No, I do not get any /tmp/tdsensor.log at all, even after moving the echo before the if. My white-listing section is :

Code: Select all

declare -A whitelist=([mandolyn-temperaturehumidity-11]=1
                    [mandolyn-temperaturehumidity-11-humidity]=1
                    [mandolyn-temperaturehumidity-12]=1
                    [mandolyn-temperaturehumidity-12-humidity]=1
                    [mandolyn-temperaturehumidity-13]=1)
                    [mandolyn-temperaturehumidity-13-humidity]=1
                    [mandolyn-temperaturehumidity-14]=1)
                    [mandolyn-temperaturehumidity-14-humidity]=1
This is built up the same way as the parser.
the script file is called "ha.sh" and located in /usr/local/share/telldus/scripts/sensorevent.


When adding SET sql_mode = '' to /etc/mysql/my.cnf (on line 22) , mysql will not start properly. Running "service mysql status" I get the following output:
Oct 09 22:03:09 webserver systemd[1]: Starting MySQL Community Server...
Oct 09 22:03:10 webserver mysqld[1109]: mysqld: [ERROR] Found option without preceding group in config file /etc/mysql/my.cnf at line 22!
Oct 09 22:03:10 webserver mysqld[1109]: mysqld: [ERROR] Fatal error in defaults handling. Program aborted!
Oct 09 22:03:10 webserver systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
I had to change it back in order to get the site operational again.
/Br. Tom Olav

Daniel
Posts: 315
Joined: Thu Jan 18, 2018 11:08 am

Re: Database problems when installing HA 3.3.2

Post by Daniel » Thu Jan 18, 2018 11:08 am

tomolav wrote:Thank's for the prompt reply :)

No, I do not get any /tmp/tdsensor.log at all, even after moving the echo before the if. My white-listing section is :

Code: Select all

declare -A whitelist=([mandolyn-temperaturehumidity-11]=1
                    [mandolyn-temperaturehumidity-11-humidity]=1
                    [mandolyn-temperaturehumidity-12]=1
                    [mandolyn-temperaturehumidity-12-humidity]=1
                    [mandolyn-temperaturehumidity-13]=1)
                    [mandolyn-temperaturehumidity-13-humidity]=1
                    [mandolyn-temperaturehumidity-14]=1)
                    [mandolyn-temperaturehumidity-14-humidity]=1
This is built up the same way as the parser.
the script file is called "ha.sh" and located in /usr/local/share/telldus/scripts/sensorevent.
Doh. I just realized that I forgot to mention in my sticky that the telldus service needs to be restarted after adding new scripts. Could that be the problem?
tomolav wrote: When adding SET sql_mode = '' to /etc/mysql/my.cnf (on line 22) , mysql will not start properly. Running "service mysql status" I get the following output:
Oct 09 22:03:09 webserver systemd[1]: Starting MySQL Community Server...
Oct 09 22:03:10 webserver mysqld[1109]: mysqld: [ERROR] Found option without preceding group in config file /etc/mysql/my.cnf at line 22!
Oct 09 22:03:10 webserver mysqld[1109]: mysqld: [ERROR] Fatal error in defaults handling. Program aborted!
Oct 09 22:03:10 webserver systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
I had to change it back in order to get the site operational again.
Ok. I didn't try that one myself, I just copied the tip from a stackoverflow thread that Google gave me :wink:

Could you try this solution instead: Open the file sensors/sensors.php and go to line 66 (the line containing the problematic query) and replace the query with the following:

Code: Select all

SELECT s.name, st.unit, MAX(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, s.name, st.unit ORDER BY date DESC;
(I'm sorry, but I can't try this myself, so I'm not sure if it'll work. My own server currently refuses to show that page at all for other reasons)

/Daniel
Senaste info och release om/av HomeAutomation hittas här http://karpero.mine.nu/ha
Latest info and release of HomeAutomation can be found here http://karpero.mine.nu/ha

tomolav
Posts: 7
Joined: Thu Jan 18, 2018 11:08 am

Re: Database problems when installing HA 3.3.2

Post by tomolav » Thu Jan 18, 2018 11:08 am

The server have been restarted several times while testing, but still no log files :)

I tried changing the query on line 66, and the behavior changed, but still an erro message. Now it says:
The test.ttt page isn’t working

test.ttt is currently unable to handle this request.
HTTP ERROR 500
I discovered another bug while playing with scripts and devices today. When setting an abs. dimmer, the action is executed (e.g. set dimlevel to 50%). But the status on the House plan stays at 1%, no matter what value the dimmer is set to. The level is presented correctly in the infobox log.

There seems to something wrong with the entire setup (tdtool not running scripts, ha not reading the mysql database properly). I'll try to start from scratch again some day soon, to see if that changes the behavior.
/Br. Tom Olav

Daniel
Posts: 315
Joined: Thu Jan 18, 2018 11:08 am

Re: Database problems when installing HA 3.3.2

Post by Daniel » Thu Jan 18, 2018 11:08 am

tomolav wrote:The server have been restarted several times while testing, but still no log files :)
This is really weird. Did you make the script executable? (chmod a+x)
tomolav wrote:I tried changing the query on line 66, and the behavior changed, but still an erro message. Now it says:
The test.ttt page isn’t working

test.ttt is currently unable to handle this request.
HTTP ERROR 500
Que? I've never heard of a page named test.ttt before. You sure this isn't something you've played with yourself?
tomolav wrote:I discovered another bug while playing with scripts and devices today. When setting an abs. dimmer, the action is executed (e.g. set dimlevel to 50%). But the status on the House plan stays at 1%, no matter what value the dimmer is set to. The level is presented correctly in the infobox log.
Sounds weird too. You mean you pull the lever in the house plan to 50% and it jumps back to 1%?

/Daniel
Senaste info och release om/av HomeAutomation hittas här http://karpero.mine.nu/ha
Latest info and release of HomeAutomation can be found here http://karpero.mine.nu/ha

tomolav
Posts: 7
Joined: Thu Jan 18, 2018 11:08 am

Re: Database problems when installing HA 3.3.2

Post by tomolav » Thu Jan 18, 2018 11:08 am

This is really weird. Did you make the script executable? (chmod a+x)
Yes, the script and directories are all chmod 755 - Still no luck.
Que? I've never heard of a page named test.ttt before. You sure this isn't something you've played with yourself?
haha :oops: "test.tt" is just a replacement for mydomain name, I just replaced it before posting. Sorry for not pointing that out.
Sounds weird too. You mean you pull the lever in the house plan to 50% and it jumps back to 1%?
Correct - It seems like the House plan page does not read data from the database properly. The temperatures and humidity is presented as "null", but works fine when presented in the graph. And the dimlevel is also ok in the logs.

As mentioned, I will try to do a new install from scratch when I get the time. maybe I should try with ubuntu 12.04 first, and then 16.04 to se if there is any difference. I can also let you SSH into my server and play around if you are interested.
/Br. Tom Olav

tom_rosenback
Posts: 774
Joined: Thu Jan 18, 2018 11:08 am
Contact:

Re: Database problems when installing HA 3.3.2

Post by tom_rosenback » Thu Jan 18, 2018 11:08 am

tomolav wrote:
This is really weird. Did you make the script executable? (chmod a+x)
Yes, the script and directories are all chmod 755 - Still no luck.
Que? I've never heard of a page named test.ttt before. You sure this isn't something you've played with yourself?
haha :oops: "test.tt" is just a replacement for mydomain name, I just replaced it before posting. Sorry for not pointing that out.
Sounds weird too. You mean you pull the lever in the house plan to 50% and it jumps back to 1%?
Correct - It seems like the House plan page does not read data from the database properly. The temperatures and humidity is presented as "null", but works fine when presented in the graph. And the dimlevel is also ok in the logs.

As mentioned, I will try to do a new install from scratch when I get the time. maybe I should try with ubuntu 12.04 first, and then 16.04 to se if there is any difference. I can also let you SSH into my server and play around if you are interested.
Can you give us access to your installation? Need a frontend user, send crendentials per email.
//Tom

Senaste info och release om/av HomeAutomation hittas här http://karpero.mine.nu/ha
Latest info and release of HomeAutomation can be found here http://karpero.mine.nu/ha

tomolav
Posts: 7
Joined: Thu Jan 18, 2018 11:08 am

Re: Database problems when installing HA 3.3.2

Post by tomolav » Thu Jan 18, 2018 11:08 am

With some help from the developers, all issues have been solved:
1: Temperatures not showing on the House plan - I had to rewrite my own script to use the ha API to input sensor data. One big change since HA 2.02 that I used before is that the House plan now get the sensor value from the "last_reading" column in the "tempsensors" table, not from the "temps" table. I had to replace the "localhost" reference in the API string to "127.0.0.1" since wget did not resolve localhost.
2: "Logs->Measurements" option on the menu didi not work, MySQL gave error message - This was due to MySQL 5.7.5+ changed the way GROUP BY behaved in order to be SQL99 compliant. More description about the issues can be found at the following link https://docs.oracle.com/cd/E17952_01/my ... dling.html. I disabled this by adding

Code: Select all

[mysqld]  
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
to the end of the /etc/mysql/my.cnf file.
3: Absolute dimmer did not show correct value on the House plan, the slider vent back to 1 %. This was solved by the developers, new version of tdtool plugin (1.4.2) released.
/Br. Tom Olav

tom_rosenback
Posts: 774
Joined: Thu Jan 18, 2018 11:08 am
Contact:

Re: Database problems when installing HA 3.3.2

Post by tom_rosenback » Thu Jan 18, 2018 11:08 am

tomolav wrote:With some help from the developers, all issues have been solved:
1: Temperatures not showing on the House plan - I had to rewrite my own script to use the ha API to input sensor data. One big change since HA 2.02 that I used before is that the House plan now get the sensor value from the "last_reading" column in the "tempsensors" table, not from the "temps" table. I had to replace the "localhost" reference in the API string to "127.0.0.1" since wget did not resolve localhost.
2: "Logs->Measurements" option on the menu didi not work, MySQL gave error message - This was due to MySQL 5.7.5+ changed the way GROUP BY behaved in order to be SQL99 compliant. More description about the issues can be found at the following link https://docs.oracle.com/cd/E17952_01/my ... dling.html. I disabled this by adding

Code: Select all

[mysqld]  
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
to the end of the /etc/mysql/my.cnf file.
3: Absolute dimmer did not show correct value on the House plan, the slider vent back to 1 %. This was solved by the developers, new version of tdtool plugin (1.4.2) released.
Thank you for the extensive report!
//Tom

Senaste info och release om/av HomeAutomation hittas här http://karpero.mine.nu/ha
Latest info and release of HomeAutomation can be found here http://karpero.mine.nu/ha

Post Reply