Apache CloudStack and MySQL 5.7

SQL Mode

Starting with MySQL 5.7 the default SQL mode is far more strict then it was before.

It now includes ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION.

This can cause problems for applications which need other SQL modes. Apache CloudStack is one of these applications.

The best thing would be to modify the SQL queries executed by CloudStack, but that’s not that easy.

Changing the mode

Luckily the SQL mode can be changed in either the my.conf or as a session variable.

In the my.cnf one can add:

[mysqld]
sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

Or modify the /etc/cloudstack/management/db.properties file to include this line:

db.cloud.url.params=prepStmtCacheSize=517&cachePrepStmts=true&sessionVariables=sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

You should now be able to run a CloudStack management server on MySQL 5.7!

Future

In the future CloudStack should only be using SQL queries which comply with the new more strict SQL mode. In the meantine a issue and Pull Request have been created to track this situation.

SQL connection error after upgrade to CloudStack 4.3.0

I just upgraded a small cluster of mine from CloudStack 4.2.1 to 4.3.0 and after installing the packages on my Ubuntu system the management server wouldn’t start due to a SQL error:

2014-03-25 20:52:13,643 INFO  [c.c.u.d.T.Transaction] (main:null) Is Data Base High Availiability enabled? Ans : false
2014-03-25 20:52:13,736 ERROR [c.c.u.d.Merovingian2] (main:null) Unable to get a new db connection
java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/cloud?autoReconnect=true&prepStmtCacheSize=517&cachePrepStmts=true
	at java.sql.DriverManager.getConnection(DriverManager.java:635)
	at java.sql.DriverManager.getConnection(DriverManager.java:195)

I quickly remembered a licensing issue around JDBC which delayed 4.3.0 and I was right. The management server was missing the right JAR/package for the SQL connection.

A quick apt-get install fixed it:

$ sudo apt-get install libmysql-java

This should have been a dependency of the ‘cloudstack-management’ package, but that somehow slipped through. I already applied a patch in the master branch and I’ll make sure it gets into 4.3.1 and 4.4.0.

So if you are running Ubuntu and are upgrading to CloudStack 4.3.0 and run into this issue, simply install the package and it’s fixed.

CloudStack: The given command does not exist or it is not available for user

So I was working on CloudStack today and I build new packages from the 4.2 branch to test some new things for the Ceph integration.

After installing the new packages and restarting my management server I wasn’t able to log on anymore. This is what I got:

The given command does not exist or it is not available for user

It took me quite some time to figure out what was going on, but after turning on MySQL logging it turned out that I was missing a column in a database. This setup is a dev setup where I build packages on a daily basis and perform a lot of database changes manually.

The problem was that my database was out of sync with what the code expected it to be. When you go from version A to B the management server will upgrade the database accordingly, but I went from version B to B, which did have some database changes, but weren’t taken care of by the DatabaseUpgradeChecker, which makes perfectly sense since this is a dev server.

So should you encounter this message at some point, turn on MySQL query logging and see the queries it tries to do. You’ll probably see that one of them is failing.

This causes the whole management server not to start properly.