VMware vCenter for Linux MySQL support research

The vCenter Server for Linux technical preview release supports only Oracle's database. Support for MySQL requires schema modifications, which a VMware expert demonstrates in this tip.

Edward Haletky

When VMware released the vCenter Server for Linux technical preview, the release notes stated that it supported only Oracle's database. Not to be swayed, I decided to try database schema modifications to see whether I could get MySQL database to work with vCenter for Linux. I soon discovered that in this case, the lack of support for the database is a true limitation, not a formality of a technical release.

That said, there is hope on the horizon. Since my original blog post summarizing my findings (see vCenter for Linux technical preview disappointing), I talked extensively with VMware engineers about vCenter for Linux, and the following discussion shows my research for MySQL database (and part two discusses research for PostgreSQL). When it is finally released, I expect a GNU/Linux database to be available -- at least in an experimental mode.

Designed to determine the requirements that would enable vCenter for Linux to connect to MySQL Open Database Connectivity (ODBC) as well as how to port the database schema, stored procedures, and views currently used so that they can be used within MySQL, this research shows that while further work is required, it's possible for VMware engineers to enable MySQL support to vCenter for Linux.

MySQL: Partial success
Since the vCenter for Linux appliance runs CentOS 5, I went to the CentOS 5 website and downloaded the following RPMS: libtool-ltdl-1.5.22-6.1.i386.rpm, mysql-5.0.45-7.el5.i386.rpm, mysql-connector-odbc-3.51.12-2.2.i386.rpm, unixODBC-2.2.11-7.1.i386.rpm, and perl-DBI-1.52-1.fc6.i386.rpm. After transferring them to the VMware vCenter on Linux VM, I installed them using:

 

rpm -ivh libtool-ltdl-1.5.22-6.1.i386.rpm mysql-5.0.45-7.el5.i386.rpm mysql-connector-odbc-3.51.12-2.2.i386.rpm unixODBC-2.2.11-7.1.i386.rpm perl-DBI-1.52-1.fc6.i386.rpm

I also downloaded and transferred the Oracle ODBC and Basic instantclient ZIP files to the system, which are required to get Oracle to install within vCenter on Linux. During the reboot of vCenter for Linux, it asks you to configure the Oracle ODBC package, which I proceeded to do even though I was attaching to a MySQL server. At one point after entering the DB name, host name, username and password the setup will fail and ask whether I want to continue. I continued regardless of the failure, because this process helps to create the necessary files that we will edit below. First, however, we need to add the MySQL connector information into the /etc/odbcinst.ini file. If you're following along, add these lines:

 

 
[MySQL]
Description = MySQL 3.51 ODBC Driver
Driver = /usr/lib/libmyodbc3.so
Setup = /usr/lib/libmyodbc3S.so
FileUsage = 1
CPTimeout =
CPReuse =
UsageCount = 2

To change from using Oracle to MySQL, including some MySQL specific changes, we need to modify the /etc/odbc.ini file. Make sure this file has permissions of "0400," as it could contain a password. First change the following line to reference MySQL instead of Oracle as such:

 

Driver       = MySQL

Next add these lines adjusting for your MySQL database server by substituting IPOfMySQLServer with the actual IP of your MySQL database server. You can use any database name. VC seems convenient to me. Substitute your database username with MySQLUserName, and provide your own password.

SERVER = IPOfMySQLServer
PORT = 3306
DATABASE = VC
USER = MySQLUserName
PASSWORD = Password

Install using the following code:

odbcinst -i -d -f /etc/odbcinst.ini
odbcinst -i -s -l -f /etc/odbc.ini

Test the connection using the following command using the appropriate username and password instead of MySQLUserName and password variables.

 

odbcinst -s –q
isql -v "VMware VirtualCenter" MySQLUserName Password

But more work is necessary; it attempts to connect but fails. This is most likely because tables are missing. So I decided to try and get the tables imported by hand into MySQL. This this requires making a new SQL schema file.

After conducting a few tests, I realized that the stored procedures within the database view schema elements would not work properly. So I contacted my colleague Oliver Reeh (oliver_reeh on the VMTN Communities Forum) for assistance. We decided to upgrade the MySQL server to version 5.1, which has better support for stored procedures. The tests worked at this level of the database. I then transferred the MySQL-client-community-5.1.31-0.rhel5.i386.rpm and MySQL-shared-community-5.1.31-0.rhel5.i386.rpm files to the vCenter for Linux virtual machine and proceeded to upgrade MySQL. To do so, use the following commands:

 

rpm -e --nodeps mysql mysql-connector-odbc
rpm -ivh MySQL-client-community-5.1.31-0.rhel5.i386.rpm MySQL-shared-community-5.1.31-0.rhel5.i386.rpm mysql-connector-odbc-setup-5.1.5-0.i386.rpm mysql-connector-odbc-5.1.5-0.i386.rpm

Now we have to modify the ODBC files again -- specifically the /etc/odbcinst.ini file, remove the old MySQL lines and add the following as we update the driver:

 

 
[MySQL]
Description = MySQL 5.1.5 ODBC Driver
Driver = /usr/lib/libmyodbc5.so
Setup = /usr/lib/libmyodbc5S.so
FileUsage = 1
CPTimeout =
CPReuse =
UsageCount = 2

Install by typing:

odbcinst -i -d -f /etc/odbcinst.ini
odbcinst -i -s -l -f /etc/odbc.ini

Then test the connection using the following command:

odbcinst -s –q
isql -v "VMware VirtualCenter" MySQLUserName Password

Converting Microsoft SQL commands to MySQL commands
Next, I worked through the SQL configuration code and made the following changes. We start with the Microsoft SQL defined tables as MySQL tables are closest to Microsoft SQL tables. Start with the file /usr/lib/vmware-vpx/VCDB_mssql.sql . I created a subsequent VCDB_mysql.sql file.

The conversions are as follows, however these changes will force vCenter to support only UTF-8 strings. You can easily support UTF-16, but there is a limit within MySQL on the size of indexes of 1,000 bytes, and some of the nvarchar field types extend past that limit.

 

NTEXT to type TEXT or to type BLOB (if you want to support UTF-16) NVARCHAR to type VARCHAR
nvarchar to type varchar

ACCESSIBLE and INTERVAL are considered keywords within MySQL so they need to be escaped by making the following conversions:

 

INTERNAL to `INTERVAL`
ACCESSIBLE to `ACCESSIBLE`

Last, some of the same functions in Microsoft SQL Server and MySQL do not do the same thing -- specifically the isnull function. In MySQL this has one argument and is not equivalent to the isnull function of Microsoft SQL or Oracle's nvl function. That change was as follows:

 

isnull change to ifnull
ISNULL changed to IFNULL

In addition, the CAST function takes different arguments within MySQL than in Microsoft SQL. I had to change the CAST to convert a number into type SIGNED and not type BIGINT, as BIGINT is not supported within the CAST function within MySQL. In addition, len is not a valid MySQL function, so we also had to edit the last two database view statements to use LENGTH and not LEN.

The last gotcha was a case-sensitivity issue. Everything on Linux is case sensitive, including database names within MySQL, so the last change converts cases.

 

vpx_sequence to VPX_SEQUENCE
vpx_object_type to VPX_OBJECT_TYPE
vpx_lock to VPX_LOCK
vpx_version to VPX_VERSION

Submit the new schema to the MySQL database server using the following. Note you would use the IP address of your MySQL server instead of IPOfMySQLServer, your MySQL username instead of MySQLUserName, and the command will ask for the password.

 

mysql -p -h IPOfMySQLServer -u MySQLUserName < VCDB_mysql.sql

Now all that is left is to fix some stored procedures for STATS, undo and delete of the databases.

But even after all these updates, MySQL fails because the vpxd server does not recognize MySQL as a valid database. But with some Linux-specific tools, I discovered that PostgreSQL is a valid database and tried that next.

Open source database support is a must for a GNU/Linux based vCenter Server.

Research takeaways
So what has this research taught us? MySQL can handle the database creation as well as the port of the stored procedures, but you are limited to UTF-8 characters within all string variables (which would impact Asian vCenter users). But if a few changes were made to the underlying table structure UTF-16 (multibyte Unicode) characters could be supported with MySQL. Currently, though, vCenter for Linux does not accept using MySQL; it will flat out deny its use. Clearly there is still some work to do, but the hard part of finding proper versions of databases and how to hook ODBC up to the databases and the port of the schemas and views is now complete.

Note: I would like to thank Oliver Reeh for his help in solving stored procedure issues within MySQL.

 

Edward L. Haletky is the author of VMware ESX Server in the Enterprise: Planning and Securing Virtualization Servers. He recently left Hewlett-Packard Co., where he worked on the virtualization, Linux and high-performance computing teams. Haletky owns AstroArch Consulting Inc. and is a champion and moderator for the VMware Communities Forums.


 

This was first published in March 2009

Dig deeper on VMware how-tos

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchServerVirtualization

SearchVirtualDesktop

SearchDataCenter

SearchCloudComputing

Close