Problems with Opennebula and MySQL/MariaDB

Community Support
1

Hello, recently, I have done a fresh installation of Opennebula in my university.
versions:
Opennebula v5.11.90
Ubuntu 20.04.2 LTS (in a fresh installation)
Linux: Linux 5.4.0-65-generic #73-Ubuntu SMP Mon Jan 18 17:25:17 UTC 2021 x86_64 x86_64 x86_64 GNU/Linux

Installation was fine, but problems began when I change the database from sqlite to mysql/mariadb.

First of all, I follow the instructions in your documentation: MySQL Setup — OpenNebula 5.11.90 documentation First step is migrate sqlite to SQL. After google it, I get this simple perl from here: Notes, Convert an OpenNebula DB from SQLite to MySQL

With the database converted to a MariaDB, I began to configure my installation. When I add my first node, I got this log message:

Feb 9 14:48:40 oned[2322736]: [Z0][ONE][E]: SQL command was: SELECT host_monitoring.body FROM host_monitoring INNER JOIN host_pool ON hid = oid WHERE (host_monitoring.last_mon_time in (select last_mon_time from host_monitoring as t where t.hid = host_monitoring.hid ORDER by last_mon_time DESC LIMIT 1)) ORDER BY hid, host_monitoring.last_mon_time;, error 1235 : This version of MariaDB doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’

My MariaDB version is: mariadb-server-10.3 - MariaDB database server binaries

So… I migrate my database to Mysql. Before change my opennebula, I test the error query, and then, I got this message:

mysql> SELECT host_monitoring.body FROM host_monitoring INNER JOIN host_pool ON hid = oid WHERE (host_monitoring.last_mon_time in (select last_mon_time from host_monitoring as t where t.hid = host_monitoring.hid ORDER by last_mon_time DESC LIMIT 1)) ORDER BY hid, host_monitoring.last_mon_time;
ERROR 1235 (42000): This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’

My MySQL version is
user@myserver:/home2/users/# /usr/sbin/mysqld --version
/usr/sbin/mysqld Ver 8.0.23-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

And then… I think that opennebula v5.11.90 is not compatible with last MySQL/MariaDB versions. I think it’s very important to notice users about this. I haven’t test PostgresQL.

So… I think that this part of documentation and must be re-checked.

Thanks!

2

Just wondering why you choose v5.11.90?! This is a beta version…
I’d recommend to try the latest stable version OpenNebula 5.12 Documentation — OpenNebula 5.12.7 documentation

BR,
Anton

1 Like
3

Hello Anton,
I prefer the Community version to the Enterprise.
And yes, I know that there is a Community version 5.12.0.3, but … in my 15 yeas as sysadmin I mistrust of XXXX.0 versions. :wink:

Thanks!
Dani

4

so in that case use latest 5.10.*, on the other hand, 5.12.0.3 is just like 5.12.3. They add .0 to the all comunity patches to differentiate it from enterprise version.

1 Like
5

Verifyed,
I update to Opennebula 5.12.0.3 and the problem dissapears. All is fine now!

2 Likes