Exploring IBM Bluemix – Part 3: MySQL Databases

In my previous article I have shown you how to deploy your first PHP application, a simple ‘Hello World’ page, on IBM Bluemix. For your PHP application to be more useful, this tutorial will show you how to use MySQL databases in your Bluemix PHP project.

Adding MySQL as a Bluemix service

In IBM Bluemix, a service is defined as anything that helps you add more functionalities to an existing application. These include databases, push notification for mobile devices, big data support, or even security features. With this in mind, you will need to first create a new service in order to use MySQL – select your space on the left menu of your Bluemix dashboard and click ‘Add a service or API‘:

Scroll to the Data Management section, you can see that many types of databases such as MySQL, MongoDB, postgresql are supported. For now, select mysql that can be easily used with PHP:

The next screen allows you to add the service, as well as select a suitable plan. Take note that the free plan gives you 10 concurrent connections with 10MB of storage on a shared server with 1MB of memory, which is just barely enough for testing:

Enter a name for the service and bind it to an existing application. You may leave a service unbound now and bind the application later – but the credentials to connect to the database will not be available until after binding, so you may as well bind it to your application now. When done, click Create to add the MySQL service.

Retrieving the database credentials

Now click on the service name on the top left menu of your IBM dashboard, you’ll be presented with the service description:

To my surprise, there is nothing on this page – no database explorer, no status panels, not even the credentials to access the database – just a link to open the IBM MySQL documentation. The service settings menu also does not contain anything much useful:

So where are the database credentials? At least the IBM documentation is cleared on this – they are in the VCAP_SERVICES environment variable that can be found in the Start Coding section of the application that is bound to the service:

Here you can find the database server address (host and port fields), login account (username and password fields) as well as the name (name field). Interestingly, the port is 3307, and not the MySQL default port 3306, perhaps for security reasons.

You can also retrieve this information using the following Cloud Foundry command line:

cf env "project_name"

The output will look like the following

"VCAP_SERVICES": {
"mysql-5.5": [
{
"credentials": {
"host": "75.126.23.246",
"hostname": "75.126.23.246",
....
}

If the service is not yet bound to an application, you can also do that now using the following commands:

cf bind-service "project_name" mysql-fs
cf restage "project_name"

where mysql_fs is the name of the service. Take note that it is a must to restage the project after service binding, otherwise the changes will not take effect.

Connecting to the database

So what’s next, can you just use a MySQL client such as MySQL Workbench to connect to this database and upload all the data? Unfortunately if that is your expectation, you are in for a big disappointment here. The created MySQL database can only be connected to from within the application that’s bound to the MySQL service and external connections will be rejected. The only way to manipulate data on this database is to deploy a web-based database explorer tool to a new Bluemix application and bind it to the database service. The best candidate for this would be phpMyAdmin.

First you need to download an existing phpMyAdmin buildpacks for Bluemix such as this. Modify the manifest.yml file to fit your application details, while keeping the link to the buildpack:

applications:
- name: phpmyadmin
  memory: 128M 
  instances: 1
  host: phpmyadmin
  domain: cfapps.io
  path: .
  buildpack: https://github.com/cloudfoundry/php-buildpack.git#8c13f5434b2f31ca960d2bca9153df1978258535
  services:
  - mysql-db

Take note that the git repository does not contain the phpMyAdmin source code, just a script in .extensions/phpmyadmin which will download the source code and perform the necessary modifications to make it work with IBM Bluemix once the project is uploaded.

When you are done, commit the changes and deploy your project. Visit the application home page and you should see the phpMyAdmin login screen:

Log in using your database credentials and you should be able to navigate the MySQL database associated with the service. The database name is usually a random hexadecimal string:

The above screen highlights the first limitation of our MySQL database – we have no privileges to create databases (or to drop existing databases). Each service can only have a single associated MySQL database. In my testing, I also have some difficulties in executing queries containing stored procedures (Alter routine command denied to user error is displayed), but that itself deserves another article on its own. For now, we will move on and try to do something with our MySQL database.

On a side note, once phpMyAdmin is deployed to your Bluemix PHP application, any extra PHP pages added to the same application, even those with just a simple echo or phpinfo() statement, will fail to render and just show a blank page. Without any error log for debugging, I am unable to figure out the reason for the failure. You should therefore use a separate application for phpMyAdmin to avoid this issues.

Testing the database

For testing purposes, we will be using Northwind for MySQL, downloadable from here. This is a MySQL port of the original Northwind database that usually comes with Microsoft products such as Microsoft Access. If you are adventurous enough, you can also try to port the AdventureWorks and pubs databases, available from MSDN, to MySQL and import it to Bluemix!

This is the result of my first attempt to import the NorthWind SQL script:

Why did the SQL execution fail at the first line? Although not immediately obvious, this is due to the presence of Unicode byte order mark, or BOM at the first byte of the SQL script file, which usually does not matter for other MySQL clients. In our case, we will need to remove it for the import to work with phpMyAdmin. Open the file in Notepad++, select Encoding > Encode in UT8 without BOM and save the script file. Also due to database permissions issues, you will need to remove the first part of the script which attempts to drop and recreate the database, as well as the last part of the script which attempts to alter some stored procedures, before the script would run without issues,

This is the result of a successful import – you can see the sample tables:

And these are some records from the Customers table, as retrieved using PHP:

Success! Finally we are able to use MySQL from our Bluemix PHP application.

Afterthoughts

Although we did manage to integrate PHP with MySQL databases under Bluemix, I must say the process is non-trivial and perhaps more complicated than necessary. The lack of a web-based interface to explore the database and the inability to connect to the database from a more familiar MySQL client such as MySQL Workbench makes it a must to go through the deployment process just to install phpMyAdmin in order to make full use of the database. There is also no support for periodic database backups, which are widely available from other hosting providers.  As such, the restrictions such as database privileges, storage capacity and concurrent connection limits would make me feel hesitant to use Bluemix MySQL service for anything other than testing.

In the next few articles, I will explore the possibility of using virtual machines on Bluemix, allowing me to install my own MySQL server and use it with my PHP application without having to worry about setting up phpMyAdmin and other related issues.