Solved

JDBC Connection not working (Zomato)

  • 5 December 2022
  • 16 replies
  • 239 views
M
Rank

Hi

I am trying to establishing a connection to jdbc:mysql://mysqldb:3306/zomato   but it is not working.

I tried 2 differents approach:

  • at first, I did not want to use Strigo so I was not limited to the 8 hours. So I run my engines in containers within my local machine. The engines are running fine on the Control Hub and I can create and run some pipelines. However, I would like to use the connection cited in one of the lab with mysqldb and zomato. I am not sure how to “install” the zomato db and reviews table locally? I have a container running mysql but from there i dont know what to.

         If i try to create the connection using my local engine, i get then the below error.

JDBC_00 - Cannot connect to specified database: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

I could see a post with the same error message but it does not give the solution on how to get the zomato db and reviews table installed locally?

  • then I tried to create my engines in Strigo, even if it s not what I would like as an environment. When I then create a connection using the Strigo engine i get the below error message

 

Please note that i uploaded the mysql jar file as external source for all my engines.

 

Can you please help?

thank you

icon

Best answer by Russ Webb 6 December 2022, 11:15

View original

16 replies

Bikram
Rank
Userlevel 5
Badge +1

@Melanie

The same issue has been raised before and it has been resolved with the correct configuration.

 

Kindly refer the discussion in the below link and do the necessary setup correctly to fix your issues

 

 

Thanks & Regards

Bikram_

M
Rank

@Melanie

The same issue has been raised before and it has been resolved with the correct configuration.

 

Kindly refer the discussion in the below link and do the necessary setup correctly to fix your issues

 

 

Thanks & Regards

Bikram_

Hi Bikram

It is not at all the same error msg so does not solve my issue,

Also as mentioned in my previous message, I could see a post with the same error message but it does not give the solution on how to get the zomato db and reviews table installed locally?

R
Rank

Hello Melanie,

I created the following instructions to install the ‘zomato’ and all the other databases:

onto a new mysql container I create on my MAC M1.

  1. Install mysqldb as a container locally on my pc:

% docker pull arm64v8/mysql

 

  1. create a mysql account for root/StreamTra1ningSets! in the mysqldb container on my pc:

% docker run --name mysqldb -e MYSQL_ROOT_PASSWORD=StreamTra1ningSets! -d arm64v8/mysql:latest

 

  1. Export all databases from the strigo db:

ubuntu$ sudo docker exec -it mysqldb bash

bash# mysqldump --all-databases -uroot -p -r /tmp/alldatabases.sql 2>/dev/null

<Password = StreamTra1ningSets!   >

 

root# exit

 

  1. Copy from the strigo container to outside it (so we can transfer it to my pc) :

ubuntu$ sudo docker cp mysqldb:/tmp/alldatabases.sql /tmp

 

  1. ssh the alldatabases.sql to my pc

On my pc create an authorized key:

% ssh-keygen

Copy the whole ssh-rsa entry from .ssh/id_rsa.pub to paste in strigo /home/ubuntu/.ssh/authorized_keys

 

  1. Then on my pc:

% scp ubuntu@<public ip - get from ‘machine info’ on strigo env - cog icon>:/tmp/alldatabases.sql .

e.g.:

% scp ubuntu@52.37.179.143:/tmp/alldatabases.sql .

 

  1. Copy this file to inside mysqldb container on my pc

% docker cp alldatabases.sql mysqldb:/

 

  1. Enter the mysqldb container on my pc:

% docker exec -it mysqldb bash

 

  1. Import database tables into mysql on my pc:

bash# mysql -u root -p  -f < alldatabases.sql

 

-f is used to force the ignorance of :

ERROR 3554 (HY000) at line 609: Access to system table 'mysql.innodb_index_stats' is rejected.

ERROR 3554 (HY000) at line 612: Access to system table 'mysql.innodb_index_stats' is rejected.

ERROR 3554 (HY000) at line 629: Access to system table 'mysql.innodb_index_stats' is rejected.

ERROR 1062 (23000) at line 631: Duplicate entry 'classicmodels-customers-PRIMARY-n_diff_pfx01' for key 'innodb_index_stats.PRIMARY'

ERROR 3554 (HY000) at line 639: Access to system table 'mysql.innodb_table_stats' is rejected.

ERROR 3554 (HY000) at line 642: Access to system table 'mysql.innodb_table_stats' is rejected.

ERROR 3554 (HY000) at line 657: Access to system table 'mysql.innodb_table_stats' is rejected.

ERROR 1062 (23000) at line 659: Duplicate entry 'classicmodels-customers' for key 'innodb_table_stats.PRIMARY'

 

But all gets imported ok.

Show the database tables now on your pc as in the screenshot at the start of my response.

  1. You will have to add this new container to your container network on your pc:

Check the container networks you have. Mine is called ‘cluster’

% docker network ls

NETWORK ID     NAME                         DRIVER    SCOPE

4aba97c1969a   bridge                       bridge    local

9c81c7316f3f   cluster                      bridge    local

2e6126853c16   docker-kafka-setup_default   bridge    local

b90a17b0800e   host                         host      local

48212a84bfa3   none                         null      local

 

Add your new container to the network:

% docker network connect cluster mysqldb

Check the contents of ‘cluster’ with:

% docker network inspect cluster

 

 

Hope this helps. Let me know what steps may not work for you.

Russ.

M
Rank

@Russ Webb  Thank you for your message, it is very detailed and i would never have known how to do all of it 😂

I am currently blocked at step 6 : scp ubuntu@<public ip - get from ‘machine info’ on strigo env - cog icon>:/tmp/alldatabases.sql .

Indeed I run the cmd with the public ip of my strigo env and I get the below error msg:

scp ubuntu@54.71.34.231:/tmp/alldatabases.sql .
The authenticity of host '54.71.34.231 (54.71.34.231)' can't be established.
ECDSA key fingerprint is SHA256:DA5oGdh5kfOtDURWzjP0ziFrtKWpdZ25NFHcugTPD3s.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '54.71.34.231' (ECDSA) to the list of known hosts.
scp: /tmp/alldatabases.sql: No such file or directory

 

Below is what i have in the /tmp folder of Strigo, so no sql file there.

I also searched for a filename containing database but does not think the alldatabase.sql file is there

 

Did the academy change the path of the sql file?  Did you run the above steps recently in your environment?

 

Thank you!

R
Rank

ok, first thing:

check in your strigo mysqldb container in /tmp to see if the alldatabases.sql file is there.

If not, redo step 3

Then redo step 4.

Then outside your container on strigo (‘exit’ the container), check to see if the file is in your /tmp folder on strigo.

This is what step 4 did. Sounds like you need to get step 3 and 4 to work to make sure /tmp/alldatabases.sql exists in your main strigo environment.

Let me know how you get on.

 

M
Rank

@Russ Webb 

  1. there is nothing in the tmp folder on Strigo (My mistake i was doing the steps initally locally on my pc)

 

  1. I redid step 3 and 4 on Strigo and now i can see the sql file in /tmp :)

 

 

  1. So now i resumed from step 5 onwards. Even with the ssh key added, i get a permission denied.

locally i did

 

Then I copy the whole content of my local file /home/melm/.ssh/id_rsa.pub and copy it into strigo. You can see it highlighted in red

 

  1. Finally i run on my pc the  below cmd which is denied due to permissions :(

 

R
Rank

are you issuing the scp from your home directory (such that you have permissions to put files in there)? or from just /home. ?

ensure you can create a file in the folder you’re issuing the scp command by typing:

 

touch myFile

to see if you get a permissions problem.

 

send me the output of:

ls -ld .

 

M
Rank

@Russ Webb  we are def getting there haha. I gave permissions to my folder and indeed that was the issue.

I believe now we have a more problematic issue, the zomato db does not seem to exist … so i wont be able to do the labs for the certifications. See below all the dbs included . It looks very differently to yours :( any idea how i can get the zomato db?

 

R
Rank

if you are “Melanie Monnereau” then you are currently registered for a legacy strigo environment (i.e. 3.x).

Which is where these databases are found.

You need to be on the 4.x strigo env. for the zomato db etc.

I have registered you on a 4.x environment.

 

 

 

 

M
Rank

@Russ Webb  thanks, how do i get onto the 4.x environment? I connected again to Strigo but i can see the sql file is still containing the 3.x databases. ty

R
Rank

try:

https://app.strigo.io/training/ondemand/6163cb4c02fff307ab568620

 

 

M
Rank

@Russ Webb  great thank you! I now have the zomato db locally in my container :)

I m on step 10 where you ask to check the container networks.

Personally i do not see any additional network on my side

 

So I did not execute the step 10 and 11.

In any case I tried adding the connection to Control Hub, selecting my local engine, and it is still failing with the same message.

 

M
Rank

PS: engines and sqldb containers are all on the network called bridge

R
Rank

Great!

So your Data collector container needs to be able to talk to your mysqldb container (and other containers.)

I created my own cluster called ‘cluster’ and added my containers to it.

Here are the contents of my cluster:

docker network inspect cluster

[

    {

        "Name": "cluster",

        "Id": "9c81c7316f3f11ef467dc757f2cf1a8168d40f0a449aba3af86ca715fc3a9b83",

        "Created": "2022-08-01T16:44:37.392142799Z",

        "Scope": "local",

        "Driver": "bridge",

        "EnableIPv6": false,

        "IPAM": {

            "Driver": "default",

            "Options": null,

            "Config": [

                {

                    "Subnet": "172.18.0.0/16",

                    "Gateway": "172.18.0.1"

                }

            ]

        },

        "Internal": false,

        "Attachable": false,

        "Ingress": false,

        "ConfigFrom": {

            "Network": ""

        },

        "ConfigOnly": false,

        "Containers": {

            "7ff251e44a067e82db073d94094c7a4f839558cdea2b8eea964fa17209d3b601": {

                "Name": "beautiful_hofstadter",

                "EndpointID": "9f90c22dff40d611752a1f51a25235f96df7da53cf190dc90404b96e4ba606d1",

                "MacAddress": "02:42:ac:12:00:04",

                "IPv4Address": "172.18.0.4/16",

                "IPv6Address": ""

            },

            "abf01d9a6a489300b001d32178dc159450e8afcf8179649b230936770d563ef3": {

                "Name": "wizardly_jepsen",

                "EndpointID": "2af6515a7628dada2ac52c6361e65b7b3d3f5588fa001e0d2305cb5abf81f36f",

                "MacAddress": "02:42:ac:12:00:03",

                "IPv4Address": "172.18.0.3/16",

                "IPv6Address": ""

            },

            "c94ce5e2c24d376cb83d47e1ba8d5804940ae753d72cace11cce9956bdc9319a": {

                "Name": "mysqldb",

                "EndpointID": "fa296fa36d3199dec75fdeeef1e4cbaa9991922dd0cf9e3fbe037fbd75354aff",

                "MacAddress": "02:42:ac:12:00:02",

                "IPv4Address": "172.18.0.2/16",

                "IPv6Address": ""

            }

        },

        "Options": {},

        "Labels": {}

    }

]

 

 

so “beautiful_hofstadter” (begins 7ff)  is my data collector.

 

Try the following on your pc:

docker network create melanie

docker network connect melanie mysqldb

docker network connect melanie <first-3-chars-of-the-containerid-of-your-datacollector>

docker network inspect melanie

 

This should add two containers, your sdc and your mysqldb to the cluster called ‘melanie’.

 

M
Rank

@Russ Webb  my 2 sdc containers and my sqldb container was all in the network bridge so I thought it would be the same than creating another network.

But I did it anyway as you mentioned, now they are all 3 in another network and my SQL connection works! I don’t understand what is different between the bridge network and the melanie network since when I inpected the brigde one, i could see the 3 of them. But in any case, now it finally works yeahhh!!

Thank you very much for all your help and responsiveness!

 

R
Rank

No probs.

See this link:

https://docs.docker.com/network/bridge/#:~:text=In%20terms%20of%20Docker%2C%20a,connected%20to%20that%20bridge%20network.

in particular:

Differences between user-defined bridges and the default bridge

  • User-defined bridges provide automatic DNS resolution between containers.

    Containers on the default bridge network can only access each other by IP addresses, unless you use the --link option, which is considered legacy. On a user-defined bridge network, containers can resolve each other by name or alias.

 

so I reckon I’d have to use the ip address 172.18.0.2 rather than mysqldb (in your JDBC connection) although I’ve not tried that.

Reply


Terms & Conditions