[one-users] Opennebula 2.2.1 Failed to create database tables

[Max Hennig]

Seems I've found the reasons / a workaround for me. The problem is that
767 / 3 (bytes for utf8 in mysql) is 255,66 and as the fields in the
table creation sql are specified to be 256 chars long it fails trying to
add an index on them. So my solution is to create those tables manually
while changing the field length to a propper value:

CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS network_pool (
  oid INTEGER PRIMARY KEY,
  uid INTEGER,
  name VARCHAR(255),
  type INTEGER,
  bridge TEXT,
  public INTEGER,
  template TEXT,
  UNIQUE(name)
);

CREATE TABLE IF NOT EXISTS user_pool (
  oid INTEGER PRIMARY KEY,
  user_name VARCHAR(255),
  password TEXT,
  enabled INTEGER,
  UNIQUE(user_name)
);

Regards,

Robert

Am 26.08.11 02:31, schrieb Max Hennig:
> Hello,
>
> I have installed Opennebula 2.2.1 from source with MySQL support. On the
> first start, while trying to create the corresponding database tables,
> it goes wrong. oned.log shows:
>
> Fri Aug 26 02:24:39 2011 [ONE][I]: Bootstraping OpenNebula database.
> Fri Aug 26 02:24:39 2011 [ONE][E]: SQL command was: CREATE TABLE IF NOT
> EXISTS 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)), error 1071 : Specified key was too long; max key
> length is 767 bytes
> Fri Aug 26 02:24:39 2011 [ONE][E]: SQL command was: CREATE TABLE IF NOT
> EXISTS network_pool (oid INTEGER PRIMARY KEY, uid INTEGER, name
> VARCHAR(256), type INTEGER, bridge TEXT, public INTEGER, template TEXT,
> UNIQUE(name)), error 1071 : Specified key was too long; max key length
> is 767 bytes
> Fri Aug 26 02:24:39 2011 [ONE][E]: SQL command was: CREATE TABLE IF NOT
> EXISTS user_pool (oid INTEGER PRIMARY KEY, user_name VARCHAR(256),
> password TEXT,enabled INTEGER, UNIQUE(user_name)), error 1071 :
> Specified key was too long; max key length is 767 bytes
> Fri Aug 26 02:24:39 2011 [ONE][E]: SQL command was: SELECT MAX(oid) FROM
> host_pool, error 1146 : Table 'opennebula.host_pool' doesn't exist
> Fri Aug 26 02:24:39 2011 [ONE][E]: SQL command was: SELECT MAX(oid) FROM
> network_pool, error 1146 : Table 'opennebula.network_pool' doesn't exist
> Fri Aug 26 02:24:39 2011 [ONE][E]: SQL command was: SELECT MAX(oid) FROM
> user_pool, error 1146 : Table 'opennebula.user_pool' doesn't exist
> Fri Aug 26 02:24:39 2011 [ONE][E]: SQL command was: SELECT oid,user_name
> FROM user_pool, error 1146 : Table 'opennebula.user_pool' doesn't exist
> Fri Aug 26 02:24:39 2011 [ONE][E]: SQL command was: INSERT INTO
> user_pool (oid,user_name,password,enabled) VALUES
> (0,'oneadmin','26fb5ccdf40100814ce73175d3df62f0c4cbb819',1), error 1146
> : Table 'opennebula.user_pool' doesn't exist
> Fri Aug 26 02:24:39 2011 [ONE][E]:
>
> It looks like opennebula tries to create the table using UTF-8 charset
> (where one char could be representet by up to 6 bytes) together with
> innodb tables (standard table engine in newer MySQL servers, having the
> restriction to 767 bytes while MyIsam tables seem to be limited to 1000
> bytes).
>
> As far as I've seen there was a bug connected to this in earlier
> versions (http://dev.opennebula.org/issues/308) and it seems this had
> returned. Is there a workaround for this?
>
> Regards,
>
> Robert