Upload to meteotemplate

All about the standard Meteobridge devices based on mobile routers from TP-Link, D-Link, ASUS

Moderator: Mattk

User avatar
admin
Platinum Boarder
Platinum Boarder
Posts: 5754
Joined: Mon Oct 01, 2007 10:51 pm

Re: Upload to meteotemplate

Post by admin » Sun Apr 07, 2019 1:20 pm

I think it is the harsh SSL encryption enforced by your sever, which results into may rounds of trying until an agreement has been reached.

User avatar
staze
Senior Boarder
Senior Boarder
Posts: 79
Joined: Fri Jan 18, 2019 2:56 am
Contact:

Re: Upload to meteotemplate

Post by staze » Sun Apr 07, 2019 4:16 pm

My meteotemplate connection isn’t using ssl/https...
Davis Vantage Vue, Weather Envoy, Meteobridge Nano SD

User avatar
staze
Senior Boarder
Senior Boarder
Posts: 79
Joined: Fri Jan 18, 2019 2:56 am
Contact:

Re: Upload to meteotemplate

Post by staze » Mon Apr 15, 2019 9:56 pm

it's timing out when it's doing the actual DB insert (given the timestamps and my MT settings being every 5 minutes)

2019-04-15 12:45:44 Error: 2019-04-15 12:45:31 Read error (Operation timed out) in headers. (no more tries)
2019-04-15 12:40:39 Error: 2019-04-15 12:40:25 Read error (Operation timed out) in headers. (no more tries)
2019-04-15 12:35:14 Error: 2019-04-15 12:35:01 Read error (Operation timed out) in headers. (no more tries)
2019-04-15 12:30:16 Error: 2019-04-15 12:30:00 Read error (Operation timed out) in headers. (no more tries)
2019-04-15 12:26:08 Error: 2019-04-15 12:25:53 Read error (Operation timed out) in headers. (no more tries)
2019-04-15 12:20:46 Error: 2019-04-15 12:20:31 Read error (Operation timed out) in headers. (no more tries)
2019-04-15 12:15:42 Error: 2019-04-15 12:15:28 Read error (Operation timed out) in headers. (no more tries)
2019-04-15 12:10:43 Error: 2019-04-15 12:10:28 Read error (Operation timed out) in headers. (no more tries)
2019-04-15 12:05:33 Error: 2019-04-15 12:05:18 Read error (Operation timed out) in headers. (no more tries)
2019-04-15 12:00:29 Error: 2019-04-15 12:00:14 Read error (Operation timed out) in headers. (no more tries)
2019-04-15 11:55:16 Error: 2019-04-15 11:55:02 Read error (Operation timed out) in headers. (no more tries)
2019-04-15 11:50:15 Error: 2019-04-15 11:50:00 Read error (Operation timed out) in headers. (no more tries)
Davis Vantage Vue, Weather Envoy, Meteobridge Nano SD

User avatar
admin
Platinum Boarder
Platinum Boarder
Posts: 5754
Joined: Mon Oct 01, 2007 10:51 pm

Re: Upload to meteotemplate

Post by admin » Thu Apr 18, 2019 12:12 pm

Can you please give me a remote login? Please send credentials to "indo(at)meteobridge.com".

User avatar
staze
Senior Boarder
Senior Boarder
Posts: 79
Joined: Fri Jan 18, 2019 2:56 am
Contact:

Re: Upload to meteotemplate

Post by staze » Fri Apr 19, 2019 12:06 am

sent this morning (local time).

Thanks!
Davis Vantage Vue, Weather Envoy, Meteobridge Nano SD

User avatar
admin
Platinum Boarder
Platinum Boarder
Posts: 5754
Joined: Mon Oct 01, 2007 10:51 pm

Re: Upload to meteotemplate

Post by admin » Sat Apr 20, 2019 2:01 am

Timeout is 13 seconds. Most time the upload just takes a second, but here and there it times out. Looks like the receiving process on the server gets sometimes stalled or massively delayed. I would recommend to add timing information there and to find a correlation between time outs and server operations.

User avatar
staze
Senior Boarder
Senior Boarder
Posts: 79
Joined: Fri Jan 18, 2019 2:56 am
Contact:

Re: Upload to meteotemplate

Post by staze » Sat Apr 20, 2019 2:05 am

That is so weird. It's only on the DB update times (the 5 minute intervals). But an insert should happen pretty quick (it's innodb, so there's no need to lock the table or the whole db).

Weird. alright, will see if I can add some logging to see how long the insert is taking. I'm not entirely convinced MT isn't just not responding (and MB is timing out on that).

Wish MT dev was better able to help. Could be some PHP plugin causing an issue, etc.
Davis Vantage Vue, Weather Envoy, Meteobridge Nano SD

User avatar
admin
Platinum Boarder
Platinum Boarder
Posts: 5754
Joined: Mon Oct 01, 2007 10:51 pm

Re: Upload to meteotemplate

Post by admin » Sat Apr 20, 2019 2:09 am

A dead lock situation where the update process and scheduled database process do block each other would be the best explanation. Do you have a chance to monitor database operations in that regard?

User avatar
staze
Senior Boarder
Senior Boarder
Posts: 79
Joined: Fri Jan 18, 2019 2:56 am
Contact:

Re: Upload to meteotemplate

Post by staze » Sat Apr 20, 2019 2:27 am

not that I know of, but I have been meaning to see if I can enable slow queries. That might show what's going on... might.

again, the data gets inserted. and the API log from MT looks fine. either it's just not responding (and MB times out waiting for a response) or for some reason the insert is just taking too long.

So, it JUST did an insert, here's what MT says:

Code: Select all

End time for database update based on timestamp of first cache data: 2019-04-19 17:10:47
Rounded end time for database update: 2019-04-19 17:15:00
Time to update the database, preparing query.
Timestamp first cached record: 2019-04-19 17:10:47
Timestamp last cached record: 2019-04-19 17:14:55
Timestamp last received record: 2019-04-19 17:15:56
Timestamp new database record: 2019-04-19 17:15:00
No new day, currentRain=0.00
The database is updated with the following query: 
            INSERT INTO alldata
            (DateTime,T,Tmax,Tmin,H,P,W,G,B,R,RR,D,A)
            values ('2019-04-19 17:15:00',62.1,62.2,61.9,78.0,29.89,6.0,14.1,314,0.00,0.00,55.1,60.3
        )
Cache file deleted.
Saving first new data to cache/apiCache.txt, timestamp=2019-04-19 17:15:56
Generating log file cache/apiLog.txt
And MB says:

Code: Select all

2019-04-19 17:16:10 Error: 2019-04-19 17:15:56 Read error (Operation timed out) in headers.  (no more tries)
So times align. I need to figure out if I can easily log how long the insert takes. That said... MT is using the date as the primary key, which I don't think is correct for DB design (and InnoDB apparently hates it from googling around). I bet if I converted the DB back to MyISAM it would fix it, but I don't want to do that.

Here's original DB creation from MT:

Code: Select all

CREATE  TABLE  `$dbName`.`alldata` (  `DateTime` datetime NOT  NULL ,
	 `T` decimal( 4, 1  )  DEFAULT  NULL ,
	 `Tmax` decimal( 4, 1  )  DEFAULT  NULL ,
	 `Tmin` decimal( 4, 1  )  DEFAULT  NULL ,
	 `H` decimal( 4, 1  )  DEFAULT  NULL ,
	 `D` decimal( 4, 1  )  DEFAULT  NULL ,
	 `W` decimal( 4, 1  )  DEFAULT  NULL ,
	 `G` decimal( 4, 1  )  DEFAULT  NULL ,
	 `B` decimal( 4, 1  )  DEFAULT  NULL ,
	 `RR` decimal( 7, 3  )  DEFAULT  NULL ,
	 `R` decimal( 7, 3  )  DEFAULT  NULL ,
	 `P` decimal( 7, 3  )  DEFAULT  NULL ,
	 `S` decimal( 5, 1  )  DEFAULT NULL ,
	 `A` decimal( 4, 1  )  DEFAULT NULL ,
	 PRIMARY  KEY (  `DateTime`  ) ,
	 UNIQUE  KEY  `DateTime` (  `DateTime`  ) 
	  ) ENGINE  =  MyISAM  DEFAULT CHARSET  = utf8 COLLATE  = utf8_unicode_ci;
Okay, I'm going to play with server side (DB) and see what I get.
Davis Vantage Vue, Weather Envoy, Meteobridge Nano SD

User avatar
staze
Senior Boarder
Senior Boarder
Posts: 79
Joined: Fri Jan 18, 2019 2:56 am
Contact:

Re: Upload to meteotemplate

Post by staze » Sat Apr 20, 2019 2:59 am

Moved table back to it's own DB (I had consolidated wondering if that was an issue), and converted back to MyISAM. That resolved the issue.

Can I implore you to please, add a true primary key column (ID or something), set it to autoincrement, and then just index on datetime (and set to unique) rather than using it as the primary key. MyISAM is slowly being deprecated. InnoDB does not like arbitrary primary keys.

For reference, the create statement (in my mind) would be:

Code: Select all

CREATE TABLE `alldata` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `DateTime` datetime NOT NULL,
  `T` decimal(4,1) DEFAULT NULL,
  `Tmax` decimal(4,1) DEFAULT NULL,
  `Tmin` decimal(4,1) DEFAULT NULL,
  `H` decimal(4,1) DEFAULT NULL,
  `D` decimal(4,1) DEFAULT NULL,
  `W` decimal(4,1) DEFAULT NULL,
  `G` decimal(4,1) DEFAULT NULL,
  `B` decimal(4,1) DEFAULT NULL,
  `RR` decimal(7,3) DEFAULT NULL,
  `R` decimal(7,3) DEFAULT NULL,
  `P` decimal(7,3) DEFAULT NULL,
  `S` decimal(5,1) DEFAULT NULL,
  `A` decimal(4,1) DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE,
  UNIQUE KEY `DateTime` (`DateTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
That would get along with MyISAM and InnoDB, and should be just as fast if not faster (and certainly faster for inserts for InnoDB). It also should be extremely easy to implement during an upgrade (just alter table to add the column, and set to auto_increment. MySQL will take care of populating the ID starting at 1.
Davis Vantage Vue, Weather Envoy, Meteobridge Nano SD

Post Reply