3

I have a Spring Boot based REST application that is deployed on Pivotal Cloud Foundry (PCF) and working fine. But this REST application did not had any DB connection till now. Hence, I decided to install MySQL service on PCF from the marketplace and finally got it bounded to my application successfully.

However, the problem is that I am NOT able to connect to this newly created MySQL instance via CLI or any other GUI.

So far I have followed following links but no luck.

The steps that I performed in order to accomplish this are:

  1. Created service instance for MySQL successfully

  2. Bound the service instance for MySQL to the application successfully (see the cf services command result above)

    # cf services

    name service plan bound apps last operation

    my-mysql-db             cleardb          spark      agrisell-rest-app   create succeeded

  3. Pushed my app successfully and can hit the REST API successfully

    https://agrisell-rest-app.cfapps.io/hello

  4. Enabled ssh on the host app using cf enable-ssh agrisell-rest-app successfully

  5. Created service key using cf create-service-key my-mysql-db my-mysql-db-service-key (the command for fetching service keys after creation is shown below)

    # cf service-keys my-mysql-db

    name my-mysql-db-service-key

  6. Retrieved service key and DB credentials using following command

cf service-key my-mysql-db my-mysql-db-service-key 

{
 "hostname": "us-cdbr-iron-east-01.cleardb.net",
 "jdbcUrl": "jdbc:mysql://us-cdbr-iron-east-01.cleardb.net/ad_b509d7f388732d5?user=hello123\u0026password=world456",
 "name": "ad_b509d7f388732d5",
 "password": "world456",
 "port": "3306",
 "uri": "mysql://b311794a3c31eb:ca7ad71f@us-cdbr-iron-east-01.cleardb.net:3306/ad_b509d7f388732d5?reconnect=true",
 "username": "hello123"
}

  1. Configured SSH tunnel successfully

    cf ssh -L 63306:us-cdbr-iron-east-01.cleardb.net:3306 agrisell-rest-app

  2. Accessing the service instance through CLI

    vcap@33eb3942-10bd-4068-650f-64fc:~$ mysql -u hello123 -h 0 -p -D ad_b509d7f388732d5 -P 63306 bash: mysql: command not found

Based the above error it felt to me as if did not install the mysql plugins correctly but I already have them installed. 

# cf plugins
Listing installed plugins...

plugin   version   command name   command help
mysql    2.0.0     mysql          Connect to a MySQL database service
mysql    2.0.0     mysqldump      Dump a MySQL database

Use 'cf repo-plugins' to list plugins in registered repos available to install.

Second Approach:

Second approach I tried was to connect to the PCF MySQL DB through MySQL Workbench which comes bundled with MySQL server installation on Windows. I use it to connect to my local MySQL DB. I modified the settings to point it to the host, db and changed credentials and tried to connect which did not work either.

Third Approach

Third approach that I tried was installing Pivotal MySQL Web Database Management tool which I from one of the PCF documentation

https://docs.pivotal.io/p-mysql/2-2/use.html#mysqlweb

I installed it on my Windows 10 machine (did not deploy it to PCF) and tried to log in but that also failed.

Second Approach Tried and Failed

Based on your response Arun, I followed all the instructions mentioned in the Readme here (https://github.com/pivotal-cf/PivotalMySQLWeb) but it does not allow me to login to the application with DB URL and credentials. The first login with credentials admin/cfmysqlweb works but the login page where it requires DB credentials and password it send me back to the first login screen with a message "Bad Credentials". Screenshot below.

Deployed the PivotalMysqlWeb on PCF and bound it to DB instance

enter image description here

Login Page that shows up now after basic authentication is successful using admin/cfmysqlweb credentials

enter image description here

DB credentials entered above and on submit I am taken back to the first login page (Back to square one)

enter image description here

Nital
  • 5,784
  • 26
  • 103
  • 195
  • If you're using ClearDb, you don't need any of this. ClearDb provides public IPs through which you can access your service from anywhere on the Internet. Thus you can `mysql` or MySQL Workbench directly from your local PC to interact with your DB. – Daniel Mikusa Jan 23 '19 at 18:35
  • 1
    If you're using a different MySQL provider, then your mileage will vary. Different provides handle access differently. On PWS, if you're using Pivotal managed MySQL, this only allows access from inside your app containers. That's where you need to use `cf ssh` and set up a tunnel or push an app like Pivotal MySQL Web or PHPMyAdmin to manage your DB. – Daniel Mikusa Jan 23 '19 at 18:36
  • Yes, I was able to connect to ClearDb through MySQL Workbench. But then my application was not able to connect to it even after specifying the hostname, port and credentials correctly. Moreover the ClearDb database was also an older version MySQL 5.x. – Nital Jan 24 '19 at 01:41

3 Answers3

2

You cannot connect with any of the Marketplace service with the tools that you have in your PC / Laptop (like what you do with MySQL App)

The reason is that any Marketplace service within PCF can be running anywhere and you are just provided an instance of it .

For security purpose, your Marketplace service (that you ordered .. that you paid) is just exposed only to that particular PCF Foundation ..

If you still want to connect to MySQL and have a look at your database, I recommend you to use PivotalMySQLWeb tool . You would have to upload this App in the same PCF-space and bind it to your MySQL DB Instance that you would have created in that space. You can view lot of instructions in their README file

Arun
  • 3,440
  • 11
  • 60
  • 108
  • Thanks for the explanation Arun. Any idea on how to connect using CLI through SSH? – Nital Jan 21 '19 at 16:01
  • You have to install CF CLI tool in your System and can login to PCF ... After logging in you can SSH in to the apps running there using cf ssh command – Arun Jan 21 '19 at 18:05
  • I already have that installed and in the above-mentioned steps I already can SSH into the system. The only problem is that I cannot access MySQL through MySQL CLI client, which I have already mentioned. – Nital Jan 21 '19 at 20:39
  • You cannot do that using a CLI tool, but can use Pivotal's Web UI tool. I updated my answer. Please have a look at it – Arun Jan 22 '19 at 06:25
  • I deployed the `PivotalMySQLWeb` tool on `PCF` and bound it to the DB instance as mentioned in the Readme file. But now the login fails. I have updated the original post with my findings under the section "Second Approach Tried and Failed" and also provided screenshots of each step. Followed their documentation throughout but to me it looks like there are lot of bugs in `PCF`. Neither their `CLI` connectivity for MySQL works nor their GUI tool works. – Nital Jan 23 '19 at 02:50
  • The username and password mentioned in their README file is “admin” and the password is “cfmysqlweb” . You can also customise this . Please read the README fully ... I have used this tool multiple times and found this the best – Arun Jan 23 '19 at 02:56
  • I have used the default admin/cfmysqlweb which works. But the problem is with the screen that shows up after that. I entered DB username and password and the jdbc url there. That's when it takes me back to the first page with "Bad credentials" message. – Nital Jan 23 '19 at 03:10
  • Ok... So it works .. If you find this solution fits for your issue, mark this as the correct Answer – Arun Jan 23 '19 at 03:12
  • No, it doesn't Arun. The whole point of this question was to connect to `DB` either through `PCF CLI` or through a `GUI` tool such as `PivotalMySQLWeb`. And none of it has worked till date. Just because I was able to login into the `GUI` does not provide me authentication/connectivity to `DB`. – Nital Jan 23 '19 at 03:17
  • The moment you bind your PivotalMySqlWeb tool to MySQl instance, it will automatically connect to the DB and will show you the details... You just need to login with default credentials- admin / cfmysqlweb and you don’t need to give the DB credentials – Arun Jan 23 '19 at 03:19
  • Yes, but for some reason that is not working for me. I have checked thrice my manifest.yml and also checked if my GUI is bounded to my DB both in the UI and through CLI and I did not find any discrepancies. I am constantly been asked to provide DB credentials after basic authentication and as soon as I enter DB credentials and JDBC URL it fails with "Bad Credentials" message. Even my Spring Boot application is not able to connect to this DB. Not sure what to do now. Let me try creating a brand new instance of DB and retry. – Nital Jan 23 '19 at 03:31
  • Created new MySQL instance from scratch and bound it to PivotalMySqlWeb tool but didn't work. Some good news now. Was able to connect to MySQL server through MySQL Workbench tool from my Windows machine. – Nital Jan 23 '19 at 03:57
  • Thanks for the help Arun. For some reason the provided solutions didn't work for me. But thanks so much for your guidance and support. – Nital Jan 23 '19 at 04:02
  • 1
    @Nital I can confirm this application works like a charm. All you have to do is: 1) to change the `manifest.yml` file replacing the `pas-mysql` database name for your mysql service name. 2) push this app to your space. PCF will automatically bind the database. 3) Access the app and log in using **admin/cfmysqlweb**. Thank you Arun very much indeed. Upvoted both question and answer. – dbaltor Apr 17 '21 at 19:46
0

I installed MySQL Workbench and created a new connection. Then filled in the details from the service details on PCF (the ones you posted)

{
 "hostname": "us-cdbr-iron-east-01.cleardb.net",
 "jdbcUrl": "jdbc:mysql://us-cdbr-iron-east-01.cleardb.net/ad_b509d7f388732d5?user=hello123\u0026password=world456",
 "name": "ad_b509d7f388732d5",
 "password": "world456",
 "port": "3306",
 "uri": "mysql://b311794a3c31eb:ca7ad71f@us-cdbr-iron-east-01.cleardb.net:3306/ad_b509d7f388732d5?reconnect=true",
 "username": "hello123"
}

So in your case:

host: us-cdbr-iron-east-01.cleardb.net

Username: hello123

Password: world456

default Schema: ad_b509d7f388732d5 Port: 3306

Interesting to note, the default schema need to match the "name" attribute. I put a custom name and couldn't connect. Not super sure why, but what can you do.

Community
  • 1
  • 1
user170800
  • 1
0

In case anyone wants to connect aws-rds-mysql (instantiated from Pivotal Cloud Foundry) from IntelliJ, here are the steps:

Once you have created ssh tunnel, you can access the AWS-RDS database from your localhost on port 63306, follow the instructions in order to connect DB with DB Navigator plugin (intelliJ )

  • Install DB Navigator plugin in intelliJ (Community Edition), if you have't installed already -> https://plugins.jetbrains.com/plugin/1800-database-navigator
  • Connect to AWS RDS in intelliJ -> notice the connection is made via localhost on port 63306
    • Go to DB Navigator plugin and click on custom under new connection.
    • Enter the URL as: jdbc:mysql://:@localhost:63306/<database_name>

Steps are also available at GitHub

Arsalan Khan
  • 9
  • 2