[one-users] Bugreport / Patch for MySQL with InnoDB (instead of MyISAM)

[Fabian Wenk]

Hello

According to the posting "Re: [one-users] Opennebula 2.2.1 Failed 
to create database tables" [1] from Max Hennig, I prepared the 
attached patches (for 2.2.x and 2.9.90), which solve to problem 
with the first start of oned when the database will be initialized.

   [1] 
http://lists.opennebula.org/pipermail/users-opennebula.org/2011-August/006260.html

As far as I had the problem with the first start of oned, it 
could not create the tables, when in my.cnf the setting 
"default_storage_engine = InnoDB" is present. After removing it 
(and restarting MySQL), it was working, as MySQL then is using 
the default MyISAM storage engine. But there are reasons for 
using the InnoDB storage engine as default in MySQL. So it would 
be helpful to OpenNebula if this is also working.

The attached patches only change all the "VARCHAR(256)" to 
"VARCHAR(255)". I did test the patch with OpenNebula 2.2.1 (MySQL 
with InnoDB) and it is working fine so far. But I guess this 
should also work with 2.9.90. It would probably help if somebody 
could test this with 2.9.90 and then do this changes in the 
source repository before the next RC or final build for 3.0.

I do not know, if it is a good idea or not to have the upgrade 
script also do this modifications on an already running MySQL 
database. To do this, the three 'alter table ... VARCHAR(255);' 
commands from below would be needed (for an existing 2.2.1 
database). But reducing the field length could cause some 
problems if it is filled to the limit. I even do not know, if 
oned or the one* commands do check the field length before 
entering data into the database. If yes, then this should also be 
adjusted there in the source code.

To convert an already running MySQL opennebula database from 
MyISAM to InnoDB, I did the following steps (with OpenNebula 
2.2.1). It is probably a good idea to stop OpenNebula during this 
modifications. Then first create a backup with:
mysqldump -u root -p opennebula > opennebula.mysql

And then convert the tables with the mysql client:
mysql -u root -p
mysql> use opennebula
mysql> alter table host_pool modify host_name VARCHAR(255);
mysql> alter table network_pool modify name VARCHAR(255);
mysql> alter table user_pool modify user_name VARCHAR(255);
mysql> alter table cluster_pool ENGINE=InnoDB;
mysql> alter table history ENGINE=InnoDB;
mysql> alter table host_pool ENGINE=InnoDB;
mysql> alter table host_shares ENGINE=InnoDB;
mysql> alter table image_pool ENGINE=InnoDB;
mysql> alter table leases ENGINE=InnoDB;
mysql> alter table network_pool ENGINE=InnoDB;
mysql> alter table user_pool ENGINE=InnoDB;
mysql> alter table vm_pool ENGINE=InnoDB;

To check the current properties of a table the following MySQL 
command can be used:
mysql> show create table <table_name>;


bye
Fabian
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: 2.2-mysql-db.patch
URL: <http://lists.opennebula.org/pipermail/users-opennebula.org/attachments/20110928/14ec0a6b/attachment-0001.txt>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: 2.9.90-mysql-db.patch
URL: <http://lists.opennebula.org/pipermail/users-opennebula.org/attachments/20110928/14ec0a6b/attachment-0001.asc>