Meshlium stopped syncing with external database

Development questions: create new plugins, add new features...
JeffreyArts
Posts: 35
Joined: Thu Sep 01, 2016 4:39 pm

Re: Meshlium stopped syncing with external database

Post by JeffreyArts » Fri Apr 21, 2017 2:12 pm

Hey libelium-dev,

I want to change the timezone in the Meshlium from:

Code: Select all

Fri Apr 21 11:40:01 UTC 2017
To:

Code: Select all

Fri Apr 21 11:40:01 CEST 2017
So the timezone is identical to our other Meshlium. However, the timezone 'CEST', does not seem to be in the list to choose from in the 'Time synchronization' menu.
Only the 'CET' timezone is available. However, when this timezone is chosen, the timezone does not change after the restart. It still says 'UTC' after restart.

Any suggestions?

libelium-dev
Posts: 27967
Joined: Mon Sep 28, 2009 1:06 pm

Re: Meshlium stopped syncing with external database

Post by libelium-dev » Fri Apr 21, 2017 2:24 pm

Hi Robert_HL,

Meshlium do have a backup battery and a hardware RTC. It's very strange that the hour is lost every reboot.

Maybe, the RTC is not up to date, or its battery is dead.

After changing the timezone in System > Time Synchronization in your Manager System, you will see the current date and time in the Meshlium system at the beginning If it is correct, once you change the date and time information, you have to restart your Meshlium immediatelly to apply changes right before doing anything else, then the changes should be applied, if your issue still reamins you can synchronize your Meshlium clock with NTP server from the internet:

Code: Select all

remountrw

Code: Select all

ntpdate pool.ntp.org

Code: Select all

hwclock --systohc

Code: Select all

remountro
You can set a cron job to synchronize your clock periodically.

Regards

JeffreyArts
Posts: 35
Joined: Thu Sep 01, 2016 4:39 pm

Re: Meshlium stopped syncing with external database

Post by JeffreyArts » Fri Apr 21, 2017 3:54 pm

Hey libelium-dev,

Disregard my last question. I thought the issue might have been related to the timezone. But it appears to be related to the timestamp's formatting.

The Meshlium is trying to insert the timestamp like this:

Code: Select all

2017-03-26 01:15:21.0
However, the database is expecting the timestamp to be like this:

Code: Select all

2017-03-26 01:15:21
That's why the following error is thrown:

Code: Select all

2017-04-18 13:24:49.588 - DETAILED INFO: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '2017-03-26 01:14:05.0' for column 'timestamp' at row 1
2017-04-18 13:24:49.613 - Error synchronizing with remote DB.
Any idea why this might be happening?

libelium-dev
Posts: 27967
Joined: Mon Sep 28, 2009 1:06 pm

Re: Meshlium stopped syncing with external database

Post by libelium-dev » Tue Apr 25, 2017 9:29 am

Hi Robert_HL,

Please check that the sensorParser table where you synchronize the data (the one in the remote server, not in the Meshlium) has the following structure:

Code: Select all

+--------------+---------------------+------+-----+-------------------+----------------+
| Field        | Type                | Null | Key | Default           | Extra          |
+--------------+---------------------+------+-----+-------------------+----------------+
| id           | int(11)             | NO   | PRI | NULL              | auto_increment | 
| id_wasp      | varchar(16)         | NO   |     | NULL              |                | 
| id_secret    | varchar(10)         | NO   |     | NULL              |                | 
| frame_type   | int(11)             | YES  |     | NULL              |                | 
| frame_number | int(11)             | YES  |     | NULL              |                | 
| sensor       | varchar(16)         | NO   |     | NULL              |                | 
| value        | varchar(65)         | NO   |     | NULL              |                | 
| timestamp    | timestamp           | NO   | MUL | CURRENT_TIMESTAMP |                | 
| sync         | bigint(12) unsigned | NO   |     | 0                 |                | 
| raw          | varchar(100)        | NO   |     | noraw             |                | 
| parser_type  | tinyint(3)          | NO   |     | 0                 |                | 
+--------------+---------------------+------+-----+-------------------+----------------+

Specially the field timestamp. You can use the mysql command describe

Regards

JeffreyArts
Posts: 35
Joined: Thu Sep 01, 2016 4:39 pm

Re: Meshlium stopped syncing with external database

Post by JeffreyArts » Tue Apr 25, 2017 9:51 am

Hey libelium-dev,

Code: Select all

mysql> describe sensorParser;
+--------------+------------+------+-----+-------------------+----------------+
| Field        | Type       | Null | Key | Default           | Extra          |
+--------------+------------+------+-----+-------------------+----------------+
| id           | int(11)    | NO   | PRI | NULL              | auto_increment |
| id_wasp      | text       | YES  |     | NULL              |                |
| id_secret    | text       | YES  |     | NULL              |                |
| frame_type   | int(11)    | YES  |     | NULL              |                |
| frame_number | int(11)    | YES  |     | NULL              |                |
| sensor       | text       | YES  |     | NULL              |                |
| value        | text       | YES  |     | NULL              |                |
| timestamp    | timestamp  | NO   | MUL | CURRENT_TIMESTAMP |                |
| raw          | text       | YES  |     | NULL              |                |
| parser_type  | tinyint(3) | NO   |     | 0                 |                |
+--------------+------------+------+-----+-------------------+----------------+
10 rows in set (0.02 sec)

JeffreyArts
Posts: 35
Joined: Thu Sep 01, 2016 4:39 pm

Re: Meshlium stopped syncing with external database

Post by JeffreyArts » Tue Apr 25, 2017 10:17 am

Hey libelium-dev,

I truncated the local database of the Meshlium and attempted to synchronise again, it seems to work now. Still no clue, why it failed before? Maybe some data was corrupted?

Anyways, thanks for the help. I'll let you know if there's any update on this.

JeffreyArts
Posts: 35
Joined: Thu Sep 01, 2016 4:39 pm

Re: Meshlium stopped syncing with external database

Post by JeffreyArts » Tue Apr 25, 2017 10:31 am

Hey libelium-dev,

One last additional question: Is there any option to remove the synchronised data from the sensorParser table? I'm afraid the database size is gonna get quite large after a while.
I know this option is available for the wifiScan table, but I can't seem to find the option for the sensorParser.

Thanks.

libelium-dev
Posts: 27967
Joined: Mon Sep 28, 2009 1:06 pm

Re: Meshlium stopped syncing with external database

Post by libelium-dev » Wed Apr 26, 2017 9:47 am

Hi JeffreyArts,

Glad to hear that your problem is solved.

For deleting the synchronized data, use the option "Remove synchronized data", for further information, please check the guide page 75:

http://www.libelium.com/downloads/docum ... ide.pdf#75


Regards

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest