Syncing external database with MySQL

Development questions: create new plugins, add new features...
FIU Grad
Posts: 81
Joined: Wed Nov 16, 2016 10:37 pm

Syncing external database with MySQL

Post by FIU Grad » Fri Sep 01, 2017 6:25 pm

Hello,

I am currently trying to sync the external database to MySQL workbench and facing problem.
MySQL workbench is already downloaded and have tried to establish a connection. MeshilumDB is successfully created and connected but getting an error that states " A connection to this database can be established but some MySQL Workbench features may not work properly since the database is not fully compatible with the supported versions of MySQL" ?
More importantly, There is no clear step on how the connection can easily be done from the meshilum.
1. What are the step by step process i need to follow from the meshilum side to set up the synchronization. codes i need to run on the meshilum through SSH?
2. <Press the “Show SQL script” button, copy the SQL code. You can modify user, password, database name and table, as long as you change the setup of the connection to match. > I tried entering this SQL script in the query in MySQL workbench and keeps giving me error. Where exactly do I enter this script?
3. <Enter the connection settings and press “Save” button. You can check the connection now to ensure the settings are correct. > Other than entering this connection setting on MySQL workbench connect to database prompts , where else do i enter these settings ?

look forward to hear from you.

Regards,

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

Re: Syncing external database with MySQL

Post by libelium-dev » Mon Sep 04, 2017 10:20 am

Hi FIU Grad,

Please, provide us your Meshlium Magager System version (go tu Help > about > version) and your MySQL Workbench version.

The script showed in Meshlium Manager System is for create MySQL Schema on a external database, this script create same schema and prepare an external database to use External Database Syncronization. You dont need to connect with MySQL Workbench Meshlium MySQL database to enable this proccess.
  • You must to execute this SQL script on external database. "CREATE database MeshliumDB; .... etc"
  • If SQL scripts executes ok, then you configure external database parameters in "External Database" tab > "connection data".
  • Check connection to ensure your parameters are correct.
  • Enable checkbox: "Store frames in the external data base" and set your time sincronization.
  • Force sync manually pressing "Syncronize now"
  • Wait some minutes and check your data in your External DB pressing "Show data" button.
Best regards,

FIU Grad
Posts: 81
Joined: Wed Nov 16, 2016 10:37 pm

Re: Syncing external database with MySQL

Post by FIU Grad » Tue Sep 05, 2017 7:47 pm

The version is V.3.2.7 and the MySQL version is the latest one which is 5.7,not quite sure if that makes a difference

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

Re: Syncing external database with MySQL

Post by libelium-dev » Wed Sep 06, 2017 9:04 am

Hi FIU Grad,

Please follow the steps in the previous answer and tell us the result.

If any step fails, please, take an snapshot and share it with us.

Regards,

FIU Grad
Posts: 81
Joined: Wed Nov 16, 2016 10:37 pm

Re: Syncing external database with MySQL

Post by FIU Grad » Tue Sep 26, 2017 4:49 am

"C:\Users\Yem\Downloads\MySQL connection warning.PNG"

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

Re: Syncing external database with MySQL

Post by libelium-dev » Tue Sep 26, 2017 9:55 am

Hi FIU Grad,


Sorry but we can not see the image you have posted. Please use a service like https://postimages.org to share images.

Regards

FIU Grad
Posts: 81
Joined: Wed Nov 16, 2016 10:37 pm

Re: Syncing external database with MySQL

Post by FIU Grad » Tue Sep 26, 2017 5:12 pm

In the past week I have been trying to connect the local database to MySQL workbench with no luck. I followed each step that was outlined on the technical manual. The error message shows that there is incompatibility issue. I was not sure where to execute the SQL script. Once I created the database and attempted to connect.
https://postimg.org/image/s8erdst8l/
https://postimg.org/image/xuxc61rlx/
https://postimg.org/image/6jfv9xr0l/
For the synchronization to happen, does my laptop has to always be connected to the router network? How can I access the router network remotely?

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

Re: Syncing external database with MySQL

Post by libelium-dev » Wed Sep 27, 2017 8:46 am

Hi FIU Grad,

We are sorry for your issues trying to connect with Meshlium's DB. The mysql external connection it is well tested, so we think it should be a problem with your computer. Please try this:

1.- Disable firewall in your computer
2.- Update your Mysql Workbench version: https://dev.mysql.com/downloads/workbench/

Please reply with the result.

Regards

FIU Grad
Posts: 81
Joined: Wed Nov 16, 2016 10:37 pm

Re: Syncing external database with MySQL

Post by FIU Grad » Thu Sep 28, 2017 11:41 pm

Hello,

I disabled my firewall and updated the workbench. I successfully established connection, I still get an error message saying compatibility issue with the server but I connected anyway. It is the same problem I was running into; https://postimg.org/image/b7dzz0s5h/
Currently I have managed to make the connection and synchronize data from local database to MySql workbench.
https://postimg.org/image/5qzxido5h/
https://postimg.org/image/pcoeekqkl/
https://postimg.org/image/i6wnc4hhh/ You can see all the data that has been synchronized.
My observation is the main issue comes from the meshilum not being on the university network which was fixed. I am not sure if MySQL workbench needs internet to make connection.
However, I have a couple of questions,
1. the only data that I see for is the 'sensor parser' table and 'sensors'(which are list of sensors), for instance the table for the bluetooth data is empty; https://postimg.org/image/obo5pg9l1/ . is that because I only created list of table for 'sensor parser' based on the sql script ? Why am I not seeing bluetooth, gps, encryption data...

2. Does Meshilum delete old data to make room for the newer ones if it is out of space?

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

Re: Syncing external database with MySQL

Post by libelium-dev » Fri Sep 29, 2017 9:24 am

Hello,

We are glad that the synchronization issue is solved.

1. For synchronizing the Bluetooth table you have to follow the same procedure but in the Bluetooth module. You can find instructions in the manual:

http://www.libelium.com/downloads/docum ... 64%3A17565

2. Option for deleting old data is only available for the Meshlium database, the maintenance of the external database is out of the scope of Meshlium:

http://www.libelium.com/downloads/docum ... 43%3A17560



Regards

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest