Synchronization of data with the external database does not work with MYSQL 8 and Meshlium 3 (V.3.2.9)

All related to the Kernel, packages system and libraries
Frank007
Posts: 32
Joined: Tue May 14, 2013 3:49 pm

Synchronization of data with the external database does not work with MYSQL 8 and Meshlium 3 (V.3.2.9)

Post by Frank007 » Mon Jun 24, 2019 11:07 am

Hello,
I have a synchronization problem between the Meshlium database and my external database which is a MYSQL 8 server.

The Meshlium connects to the external database but can not synchronize the data.

See the logs of "tail -f syncDB.log":
2019-06-24 10:55:11.735 - Connection to remote data base couldn't be stablished.

2019-06-24 10:55:11.738 - HOST: 10.19.10.12

2019-06-24 10:55:11.739 - PORT: 3306

2019-06-24 10:55:11.739 - DB: MeshliumDB

2019-06-24 10:55:11.739 - Verify that remote database is accessible from internet and the login info is correct.

2019-06-24 10:55:11.74 - Connection parameters to remote database stored in /mnt/lib/cfg/sensorExternalDB

2019-06-24 10:55:11.74 - DETAILED INFO: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.
2019-06-24 10:55:11.749 - DETAILED INFO: java.lang.NullPointerException
2019-06-24 10:55:25.208 - Connection to remote data base couldn't be stablished.

2019-06-24 10:55:25.209 - HOST: 10.19.10.12

2019-06-24 10:55:25.209 - PORT: 3306

2019-06-24 10:55:25.209 - DB: MeshliumDB

2019-06-24 10:55:25.21 - Verify that remote database is accessible from internet and the login info is correct.

2019-06-24 10:55:25.21 - Connection parameters to remote database stored in /mnt/lib/cfg/sensorExternalDB

thank you in advance for your help

2019-06-24 10:55:25.211 - DETAILED INFO: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.
2019-06-24 10:55:25.22 - DETAILED INFO: java.lang.NullPointerException

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

Re: Synchronization of data with the external database does not work with MYSQL 8 and Meshlium 3 (V.3.2.9)

Post by libelium-dev » Tue Jun 25, 2019 9:39 am

Hi,

Could you please try to connect to the external data base through the terminal using the root user? Does it work?

Regards

Frank007
Posts: 32
Joined: Tue May 14, 2013 3:49 pm

Re: Synchronization of data with the external database does not work with MYSQL 8 and Meshlium 3 (V.3.2.9)

Post by Frank007 » Sun Jun 30, 2019 6:23 pm

Hi,
Yes I did this test, the connection from another machine works well as shows the code below.

Code: Select all

root@br-medybat-1:~#
root@br-medybat-1:~# mysql -u root -p -h 10.19.10.12
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24927
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| MeshliumDB         |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0,01 sec)

mysql>

<a href='https://www.casimages.com/i/190630061943554008.png.html' title='Mon image'>Connecting to the database server</a>

<a href='https://www.casimages.com/i/190630061944803653.png.html' title='Mon image'>No Synchronise</a>


What is the origin of the problem?

Thank you?

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

Re: Synchronization of data with the external database does not work with MYSQL 8 and Meshlium 3 (V.3.2.9)

Post by libelium-dev » Mon Jul 01, 2019 9:17 am

Hi,

Is the 10.19.10.12 a Meshlium device? Are you trying to synchronize data from a Meshlium to another Meshlium?

Please try to perform the connection to the database from Meshlium. Please access Meshlum through ssh and try to connect to the 10.19.10.12 database.

Regards

Frank007
Posts: 32
Joined: Tue May 14, 2013 3:49 pm

Re: Synchronization of data with the external database does not work with MYSQL 8 and Meshlium 3 (V.3.2.9)

Post by Frank007 » Mon Jul 01, 2019 4:01 pm

Hi,
I am making a connection from the Meshlium to an external database on Mysql 8.

My Meshlium has the IP address: 10.19.10.20

My external database (Debian 9 and Mysql 8): 10.19.10.12

Here is the connection from the Meshlium to my DB:

Code: Select all

meshlium:~#
meshlium:~# mysql -u roott -p -h 10.19.10.12
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 8.0.16 MySQL Community Server - GPL

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show status like '%abo%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Aborted_clients        | 0     |
| Aborted_connects       | 23    |
| Mysqlx_aborted_clients | 0     |
+------------------------+-------+
3 rows in set (0.01 sec)

mysql> flush hosts;
Query OK, 0 rows affected (0.00 sec)

synchronization still does not work.

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

Re: Synchronization of data with the external database does not work with MYSQL 8 and Meshlium 3 (V.3.2.9)

Post by libelium-dev » Tue Jul 02, 2019 8:30 am

Hi,

You have to configure the database and table in the External Database tab according to your external database (10.19.10.12). According to your screenshots, you are trying to save data from Meshlium to another DB called Meshlium DB and to a table sensorParser. Did you create it in your external DB?

Regards

Frank007
Posts: 32
Joined: Tue May 14, 2013 3:49 pm

Re: Synchronization of data with the external database does not work with MYSQL 8 and Meshlium 3 (V.3.2.9)

Post by Frank007 » Tue Jul 02, 2019 1:54 pm

Hi,
actually, I want to synchronize the data of my meshlium (10.19.10.20) in an external database (10.19.10.12).
My external base is configured with the script that gives the Meshlium:

Code: Select all

Just copy paste:
  CREATE database MeshliumDB;
      
Just copy paste:
CREATE TABLE IF NOT EXISTS `sensorParser` (
  `id` int(11) NOT NULL auto_increment,
  `id_wasp` text character set utf8 collate utf8_unicode_ci,
  `id_secret` text character set utf8 collate utf8_unicode_ci,
  `frame_type` int(11) default NULL,
  `frame_number` int(11) default NULL,
  `sensor` text character set utf8 collate utf8_unicode_ci,
  `value` text character set utf8 collate utf8_unicode_ci,
  `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `raw` text character set utf8 collate utf8_unicode_ci,
  `parser_type` tinyint(3) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

      
Just copy paste:
  GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY 'passw';
I am using version 8 of mysql server, the creation of the user and the exchange rights of the script but I know how to do it.

Frank007
Posts: 32
Joined: Tue May 14, 2013 3:49 pm

Re: Synchronization of data with the external database does not work with MYSQL 8 and Meshlium 3 (V.3.2.9)

Post by Frank007 » Tue Jul 02, 2019 1:57 pm

Show logs Sync

Code: Select all

Tue Jul 2 13:44:42 CEST 2019 - Error occurred during initialization of VM
java.lang.ClassNotFoundException: error in opening JAR file /usr/java/jre1.6.0_25/lib/rt.jar
at java.security.AccessControlContext.(Unknown Source)
at java.security.AccessController.getStackAccessControlContext(Native Method)
at java.security.AccessController.getContext(Unknown Source)
at java.lang.Thread.init(Unknown Source)
at java.lang.Thread.(Unknown Source)
Tue Jul 2 13:45:53 CEST 2019 - Error occurred during initialization of VM
java.lang.ClassNotFoundException: error in opening JAR file /usr/java/jre1.6.0_25/lib/rt.jar
at java.security.AccessControlContext.(Unknown Source)
at java.security.AccessController.getStackAccessControlContext(Native Method)
at java.security.AccessController.getContext(Unknown Source)
at java.lang.Thread.init(Unknown Source)
at java.lang.Thread.(Unknown Source)
Tue Jul 2 13:47:03 CEST 2019 - Error occurred during initialization of VM
java.lang.ClassNotFoundException: error in opening JAR file /usr/java/jre1.6.0_25/lib/rt.jar
at java.security.AccessControlContext.(Unknown Source)
at java.security.AccessController.getStackAccessControlContext(Native Method)
at java.security.AccessController.getContext(Unknown Source)
at java.lang.Thread.init(Unknown Source)
at java.lang.Thread.(Unknown Source)
Tue Jul 2 13:48:13 CEST 2019 - Error occurred during initialization of VM
java.lang.ClassNotFoundException: error in opening JAR file /usr/java/jre1.6.0_25/lib/rt.jar
at java.security.AccessControlContext.(Unknown Source)
at java.security.AccessController.getStackAccessControlContext(Native Method)
at java.security.AccessController.getContext(Unknown Source)
at java.lang.Thread.init(Unknown Source)
at java.lang.Thread.(Unknown Source

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

Re: Synchronization of data with the external database does not work with MYSQL 8 and Meshlium 3 (V.3.2.9)

Post by libelium-dev » Wed Jul 03, 2019 8:19 am

Hi,

Connect to the external DB again from the Meshlium ssh connection and execute the following commands
mysql -u root -p -h 10.19.10.12
show databases;
show tables from MeshliuMDB;
use meshliumDB;
describe sensorParser;
select user,host from mysql.user;
show grants for root;
show grants;

Regards

Frank007
Posts: 32
Joined: Tue May 14, 2013 3:49 pm

Re: Synchronization of data with the external database does not work with MYSQL 8 and Meshlium 3 (V.3.2.9)

Post by Frank007 » Wed Jul 03, 2019 9:56 am

Hi,

Out cmd:

Code: Select all

login as: root
root@10.19.10.20's password:
Linux meshlium 2.6.30-voyage #1 PREEMPT Wed Dec 30 18:39:44 GMT 2009 i586
Meshlium - Linux Kernel 2.6.30-voyage
The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.
Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.

                       MESHLIUM - 3G
                                                      )[            ....
                                                   -$wj[        _swmQQWC
                                                    -4Qm    ._wmQWWWW!'
                                                     -QWL_swmQQWBVY"~.____
                                                     _dQQWTY+vsawwwgmmQWV!
                                    1isas,       _mgmQQQQQmmQWWQQWVY!"-
                                   .s,. -?ha     -9WDWU?9Qz~- -- -
                                   -""?Ya,."h,   <!`_mT!2-?5a,
                                   -Swa. Yg.-Q,  ~ ^`  /`   "$a.
 aac  <aa, aa/                aac  _a,-4c ]k +m               "1
.QWk  ]VV( QQf   .      .     QQk  )YT`-C.-? -Y  .
.QWk       WQmymmgc  <wgmggc. QQk       wgz  = gygmgwagmmgc
.QWk  jQQ[ WQQQQQQW;jWQQ  QQL QQk  ]WQ[ dQk  ) QF~"WWW(~)QQ[
.QWk  jQQ[ QQQ  QQQ(mWQ9VVVVT QQk  ]WQ[ mQk  = Q;  jWW  :QQ[
 WWm,,jQQ[ QQQQQWQW']WWa,_aa. $Qm,,]WQ[ dQm,sj Q(  jQW  :QW[
 -TTT(]YT' TTTYUH?^  ~TTB8T!` -TYT[)YT( -?9WTT T'  ]TY  -TY(

                      www.libelium.com

Last login: Wed Jul  3 09:44:05 2019 from 10.19.4.38
meshlium:~# mysql -u root -p -h 10.19.10.12
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.16 MySQL Community Server - GPL

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| MeshliumDB         |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> show tables from MeshliuMDB;
ERROR 1049 (42000): Unknown database 'MeshliuMDB'
mysql> show tables from MeshliuDB;
ERROR 1049 (42000): Unknown database 'MeshliuDB'
mysql> show tables from MeshliumDB;
+----------------------+
| Tables_in_MeshliumDB |
+----------------------+
| sensorParser         |
+----------------------+
1 row in set (0.01 sec)

mysql> use meshliumDB;
ERROR 1049 (42000): Unknown database 'meshliumDB'
mysql> use MeshliumDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
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   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED                                                                                                              |
| raw          | text       | YES  |     | NULL              |                                                                                                                                |
| parser_type  | tinyint(3) | NO   |     | 0                 |                                                                                                                                |
+--------------+------------+------+-----+-------------------+------------------                                                                                                             -+
10 rows in set (0.01 sec)

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| roott            | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)

mysql> show grants for root;
+-------------------------------------------------------------------------------                                                                                                             --------------------------------------------------------------------------------                                                                                                             --------------------------------------------------------------------------------                                                                                                             --------------------------------------------------------------------------------                                                                                                             --------------------------------------------------------------------------------                                                                                                             -------+
| Grants for root@%                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+-------------------------------------------------------------------------------                                                                                                             --------------------------------------------------------------------------------                                                                                                             --------------------------------------------------------------------------------                                                                                                             --------------------------------------------------------------------------------                                                                                                             --------------------------------------------------------------------------------                                                                                                             -------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS,                                                                                                              FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES,                                                                                                              LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW                                                                                                              VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESP                                                                                                             ACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%`                                                                                                                                                    |
| GRANT APPLICATION_PASSWORD_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_A                                                                                                             DMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VA                                                                                                             RIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,R                                                                                                             OLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_US                                                                                                             ER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `roo                                                                                                             t`@`%` |
+-------------------------------------------------------------------------------                                                                                                             --------------------------------------------------------------------------------                                                                                                             --------------------------------------------------------------------------------                                                                                                             --------------------------------------------------------------------------------                                                                                                             --------------------------------------------------------------------------------                                                                                                             -------+
2 rows in set (0.00 sec)

mysql> show grants;
+-------------------------------------------------------------------------------                                                                                                             --------------------------------------------------------------------------------                                                                                                             --------------------------------------------------------------------------------                                                                                                             --------------------------------------------------------------------------------                                                                                                             --------------------------------------------------------------------------------                                                                                                             -------+
| Grants for root@%                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+-------------------------------------------------------------------------------                                                                                                             --------------------------------------------------------------------------------                                                                                                             --------------------------------------------------------------------------------                                                                                                             --------------------------------------------------------------------------------                                                                                                             --------------------------------------------------------------------------------                                                                                                             -------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS,                                                                                                              FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES,                                                                                                              LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW                                                                                                              VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESP                                                                                                             ACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%`                                                                                                                                                    |
| GRANT APPLICATION_PASSWORD_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_A                                                                                                             DMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VA                                                                                                             RIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,R                                                                                                             OLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_US                                                                                                             ER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `roo                                                                                                             t`@`%` |
+-------------------------------------------------------------------------------                                                                                                             --------------------------------------------------------------------------------                                                                                                             --------------------------------------------------------------------------------                                                                                                             --------------------------------------------------------------------------------                                                                                                             --------------------------------------------------------------------------------                                                                                                             -------+
2 rows in set (0.01 sec)

mysql>

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest