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
--- a/src/host/Host.cc 2011-06-08 15:15:46.000000000 +0200
+++ b/src/host/Host.cc 2011-09-28 15:42:11.000000000 +0200
@@ -56,7 +56,7 @@
"tm_mad,last_mon_time, cluster, template";
const char * Host::db_bootstrap = "CREATE TABLE IF NOT EXISTS host_pool ("
- "oid INTEGER PRIMARY KEY,host_name VARCHAR(256), state INTEGER,"
+ "oid INTEGER PRIMARY KEY,host_name VARCHAR(255), state INTEGER,"
"im_mad VARCHAR(128),vm_mad VARCHAR(128),tm_mad VARCHAR(128),"
"last_mon_time INTEGER, cluster VARCHAR(128), template TEXT, "
"UNIQUE(host_name))";
--- a/src/um/User.cc 2011-06-08 15:15:46.000000000 +0200
+++ b/src/um/User.cc 2011-09-28 15:42:25.000000000 +0200
@@ -53,7 +53,7 @@
const char * User::db_names = "oid,user_name,password,enabled";
const char * User::db_bootstrap = "CREATE TABLE IF NOT EXISTS user_pool ("
- "oid INTEGER PRIMARY KEY, user_name VARCHAR(256), password TEXT,"
+ "oid INTEGER PRIMARY KEY, user_name VARCHAR(255), password TEXT,"
"enabled INTEGER, UNIQUE(user_name))";
/* --------------------------------------------------------------------------
*/
--- a/src/vnm/VirtualNetwork.cc 2011-06-08 15:15:46.000000000 +0200
+++ b/src/vnm/VirtualNetwork.cc 2011-09-28 15:42:39.000000000 +0200
@@ -78,7 +78,7 @@
const char * VirtualNetwork::db_bootstrap = "CREATE TABLE IF NOT EXISTS"
" network_pool ("
- "oid INTEGER PRIMARY KEY, uid INTEGER, name VARCHAR(256), type INTEGER, "
+ "oid INTEGER PRIMARY KEY, uid INTEGER, name VARCHAR(255), type INTEGER, "
"bridge TEXT, public INTEGER, template TEXT, UNIQUE(name))";
/* --------------------------------------------------------------------------
*/
--- a/src/group/Group.cc 2011-09-23 16:56:55.000000000 +0200
+++ b/src/group/Group.cc 2011-09-28 17:27:03.000000000 +0200
@@ -27,7 +27,7 @@
const char * Group::db_names = "oid, name, body";
const char * Group::db_bootstrap = "CREATE TABLE IF NOT EXISTS group_pool ("
- "oid INTEGER PRIMARY KEY, name VARCHAR(256), body TEXT, "
+ "oid INTEGER PRIMARY KEY, name VARCHAR(255), body TEXT, "
"UNIQUE(name))";
/* ************************************************************************ */
--- a/src/host/Host.cc 2011-09-23 16:56:55.000000000 +0200
+++ b/src/host/Host.cc 2011-09-28 17:27:03.000000000 +0200
@@ -61,7 +61,7 @@
const char * Host::db_names = "oid, name, body, state, last_mon_time";
const char * Host::db_bootstrap = "CREATE TABLE IF NOT EXISTS host_pool ("
- "oid INTEGER PRIMARY KEY, name VARCHAR(256), body TEXT, state INTEGER, "
+ "oid INTEGER PRIMARY KEY, name VARCHAR(255), body TEXT, state INTEGER, "
"last_mon_time INTEGER, UNIQUE(name))";
/* ------------------------------------------------------------------------ */
--- a/src/image/Image.cc 2011-09-23 16:56:55.000000000 +0200
+++ b/src/image/Image.cc 2011-09-28 17:27:03.000000000 +0200
@@ -74,7 +74,7 @@
const char * Image::db_names = "oid, name, body, uid, gid, public";
const char * Image::db_bootstrap = "CREATE TABLE IF NOT EXISTS image_pool ("
- "oid INTEGER PRIMARY KEY, name VARCHAR(256), body TEXT, uid INTEGER, "
+ "oid INTEGER PRIMARY KEY, name VARCHAR(255), body TEXT, uid INTEGER, "
"gid INTEGER, public INTEGER, UNIQUE(name,uid) )";
/* ------------------------------------------------------------------------ */
--- a/src/nebula/Nebula.cc 2011-09-23 16:56:55.000000000 +0200
+++ b/src/nebula/Nebula.cc 2011-09-28 17:27:03.000000000 +0200
@@ -643,7 +643,7 @@
oss.str("");
oss << "CREATE TABLE db_versioning (oid INTEGER PRIMARY KEY, "
- "version VARCHAR(256), timestamp INTEGER, comment VARCHAR(256))";
+ "version VARCHAR(255), timestamp INTEGER, comment VARCHAR(256))";
db->exec(oss);
--- a/src/onedb/2.0_to_2.9.80.rb 2011-09-23 16:56:55.000000000 +0200
+++ b/src/onedb/2.0_to_2.9.80.rb 2011-09-28 17:27:04.000000000 +0200
@@ -30,13 +30,13 @@
########################################################################
# 2.2 Schema
- # CREATE TABLE user_pool (oid INTEGER PRIMARY KEY, user_name
VARCHAR(256), password TEXT,enabled INTEGER, UNIQUE(user_name));
+ # CREATE TABLE user_pool (oid INTEGER PRIMARY KEY, user_name
VARCHAR(255), password TEXT,enabled INTEGER, UNIQUE(user_name));
# Move table user_pool
@db.run "ALTER TABLE user_pool RENAME TO old_user_pool;"
# Create new user_pool
- @db.run "CREATE TABLE user_pool (oid INTEGER PRIMARY KEY, name
VARCHAR(256), body TEXT, UNIQUE(name));"
+ @db.run "CREATE TABLE user_pool (oid INTEGER PRIMARY KEY, name
VARCHAR(255), body TEXT, UNIQUE(name));"
user_group_ids = ""
@@ -71,14 +71,14 @@
########################################################################
# 2.2 Schema
- # CREATE TABLE host_pool (oid INTEGER PRIMARY KEY,host_name
VARCHAR(256), state INTEGER,im_mad VARCHAR(128),vm_mad VARCHAR(128),tm_mad
VARCHAR(128),last_mon_time INTEGER, cluster VARCHAR(128), template TEXT,
UNIQUE(host_name));
+ # CREATE TABLE host_pool (oid INTEGER PRIMARY KEY,host_name
VARCHAR(255), state INTEGER,im_mad VARCHAR(128),vm_mad VARCHAR(128),tm_mad
VARCHAR(128),last_mon_time INTEGER, cluster VARCHAR(128), template TEXT,
UNIQUE(host_name));
# CREATE TABLE host_shares(hid INTEGER PRIMARY KEY,disk_usage INTEGER,
mem_usage INTEGER, cpu_usage INTEGER,max_disk INTEGER, max_mem INTEGER,
max_cpu INTEGER,free_disk INTEGER, free_mem INTEGER, free_cpu
INTEGER,used_disk INTEGER, used_mem INTEGER, used_cpu INTEGER,running_vms
INTEGER);
# Move table
@db.run "ALTER TABLE host_pool RENAME TO old_host_pool;"
# Create new table
- @db.run "CREATE TABLE host_pool (oid INTEGER PRIMARY KEY, name
VARCHAR(256), body TEXT, state INTEGER, last_mon_time INTEGER, UNIQUE(name));"
+ @db.run "CREATE TABLE host_pool (oid INTEGER PRIMARY KEY, name
VARCHAR(255), body TEXT, state INTEGER, last_mon_time INTEGER, UNIQUE(name));"
# Read each entry in the old table, and insert into new table
@db.fetch("SELECT * FROM old_host_pool") do |row|
@@ -133,7 +133,7 @@
@db.run "ALTER TABLE image_pool RENAME TO old_image_pool;"
# Create new table
- @db.run "CREATE TABLE image_pool (oid INTEGER PRIMARY KEY, name
VARCHAR(256), body TEXT, uid INTEGER, gid INTEGER, public INTEGER,
UNIQUE(name,uid) );"
+ @db.run "CREATE TABLE image_pool (oid INTEGER PRIMARY KEY, name
VARCHAR(255), body TEXT, uid INTEGER, gid INTEGER, public INTEGER,
UNIQUE(name,uid) );"
# Read each entry in the old table, and insert into new table
@db.fetch("SELECT * FROM old_image_pool") do |row|
@@ -236,7 +236,7 @@
########################################################################
# 2.2 Schema
- # CREATE TABLE network_pool (oid INTEGER PRIMARY KEY, uid INTEGER,
name VARCHAR(256), type INTEGER, bridge TEXT, public INTEGER, template TEXT,
UNIQUE(name));
+ # CREATE TABLE network_pool (oid INTEGER PRIMARY KEY, uid INTEGER,
name VARCHAR(255), type INTEGER, bridge TEXT, public INTEGER, template TEXT,
UNIQUE(name));
# CREATE TABLE leases (oid INTEGER, ip BIGINT, mac_prefix BIGINT,
mac_suffix BIGINT,vid INTEGER, used INTEGER, PRIMARY KEY(oid,ip));
# Move tables
@@ -298,12 +298,12 @@
# New tables in DB version 1
########################################################################
- @db.run "CREATE TABLE db_versioning (oid INTEGER PRIMARY KEY, version
VARCHAR(256), timestamp INTEGER, comment VARCHAR(256));"
- @db.run "CREATE TABLE template_pool (oid INTEGER PRIMARY KEY, name
VARCHAR(256), body TEXT, uid INTEGER, gid INTEGER, public INTEGER);"
+ @db.run "CREATE TABLE db_versioning (oid INTEGER PRIMARY KEY, version
VARCHAR(255), timestamp INTEGER, comment VARCHAR(256));"
+ @db.run "CREATE TABLE template_pool (oid INTEGER PRIMARY KEY, name
VARCHAR(255), body TEXT, uid INTEGER, gid INTEGER, public INTEGER);"
@db.run "CREATE TABLE acl (oid INT PRIMARY KEY, user BIGINT, resource
BIGINT, rights BIGINT);"
# The group pool has two default ones
- @db.run "CREATE TABLE group_pool (oid INTEGER PRIMARY KEY, name
VARCHAR(256), body TEXT, UNIQUE(name));"
+ @db.run "CREATE TABLE group_pool (oid INTEGER PRIMARY KEY, name
VARCHAR(255), body TEXT, UNIQUE(name));"
@db.run "INSERT INTO group_pool
VALUES(0,'oneadmin','<GROUP><ID>0</ID><NAME>oneadmin</NAME><USERS><ID>0</ID></USERS></GROUP>');"
@db.run "INSERT INTO group_pool
VALUES(1,'users','<GROUP><ID>1</ID><NAME>users</NAME><USERS>#{user_group_ids}</USERS></GROUP>');"
--- a/src/onedb/2.9.80_to_2.9.85.rb 2011-09-23 16:56:55.000000000 +0200
+++ b/src/onedb/2.9.80_to_2.9.85.rb 2011-09-28 17:27:04.000000000 +0200
@@ -30,7 +30,7 @@
# now have a <SIZE> element
# Image pool table:
- # CREATE TABLE image_pool (oid INTEGER PRIMARY KEY, name VARCHAR(256),
body TEXT, uid INTEGER, gid INTEGER, public INTEGER, UNIQUE(name,uid) );
+ # CREATE TABLE image_pool (oid INTEGER PRIMARY KEY, name VARCHAR(255),
body TEXT, uid INTEGER, gid INTEGER, public INTEGER, UNIQUE(name,uid) );
@db.fetch("SELECT * FROM image_pool") do |row|
doc = Document.new(row[:body])
--- a/src/pool/test/TestPoolSQL.cc 2011-09-23 16:56:55.000000000 +0200
+++ b/src/pool/test/TestPoolSQL.cc 2011-09-28 17:27:04.000000000 +0200
@@ -35,7 +35,7 @@
const char * TestObjectSQL::db_names = "oid,name,body,uid,number";
const char * TestObjectSQL::db_bootstrap = "CREATE TABLE test_pool ("
- "oid INTEGER PRIMARY KEY, name VARCHAR(256), body TEXT, uid INTEGER, "
+ "oid INTEGER PRIMARY KEY, name VARCHAR(255), body TEXT, uid INTEGER, "
"number INTEGER)";
/* --------------------------------------------------------------------------
*/
--- a/src/um/User.cc 2011-09-23 16:56:55.000000000 +0200
+++ b/src/um/User.cc 2011-09-28 17:27:05.000000000 +0200
@@ -38,7 +38,7 @@
const char * User::db_names = "oid,name,body";
const char * User::db_bootstrap = "CREATE TABLE IF NOT EXISTS user_pool ("
- "oid INTEGER PRIMARY KEY, name VARCHAR(256), body TEXT, UNIQUE(name))";
+ "oid INTEGER PRIMARY KEY, name VARCHAR(255), body TEXT, UNIQUE(name))";
/* --------------------------------------------------------------------------
*/
/* --------------------------------------------------------------------------
*/
--- a/src/vm_template/VMTemplate.cc 2011-09-23 16:56:55.000000000 +0200
+++ b/src/vm_template/VMTemplate.cc 2011-09-28 17:27:06.000000000 +0200
@@ -63,7 +63,7 @@
const char * VMTemplate::db_bootstrap =
"CREATE TABLE IF NOT EXISTS template_pool (oid INTEGER PRIMARY KEY, "
- "name VARCHAR(256), body TEXT, uid INTEGER, gid INTEGER, public INTEGER)";
+ "name VARCHAR(255), body TEXT, uid INTEGER, gid INTEGER, public INTEGER)";
/* ------------------------------------------------------------------------ */
/* ------------------------------------------------------------------------ */
_______________________________________________
Users mailing list
Users@lists.opennebula.org
http://lists.opennebula.org/listinfo.cgi/users-opennebula.org
