Bug #310

Failing MySQL query when creating host_pool table

Added by Martin Kopta over 10 years ago. Updated over 10 years ago.

Status:ClosedStart date:08/09/2010
Priority:HighDue date:
Assignee:-% Done:

0%

Category:Core & SystemEstimated time:0.10 hour
Target version:Release 2.0
Resolution:duplicate Pull request:
Affected Versions:

Description

I checked out latest version of opennebula from git repo (f017f08ce3a05c4d2ade010df7f48f877306311a) and tried to get it work with MySQL. After successful compilation, instalation and one start, opennebula failed to add new host. I found few error messages in the one.log file:

Mon Aug  9 17:49:41 2010 [ONE][E]: SQL command was: SELECT oid, im_mad FROM host_pool WHERE state != 4 ORDER BY last_mon_time ASC LIMIT 10, er
ror 1146 : Table 'opennebula.host_pool' doesn't exist

I checked in MySQL for table host_pool, but it wasn't there. Then I found SQL command for creating host_pool table in src/host/Host.cc

const char * Host::db_bootstrap = "CREATE TABLE IF NOT EXISTS host_pool (" 
    "oid INTEGER PRIMARY KEY,host_name VARCHAR(512), state INTEGER," 
    "im_mad VARCHAR(128),vm_mad VARCHAR(128),tm_mad VARCHAR(128)," 
    "last_mon_time INTEGER, cluster VARCHAR(128), " 
    "UNIQUE(host_name, im_mad, vm_mad, tm_mad) )";

I tried to run this command manualy and I got confusing error about max key length.

mysql> CREATE TABLE IF NOT EXISTS host_pool (oid INTEGER PRIMARY KEY,host_name VARCHAR(512), state INTEGER, 
    -> im_mad VARCHAR(128),vm_mad VARCHAR(128),tm_mad VARCHAR(128), last_mon_time INTEGER, cluster VARCHAR(128),
    -> UNIQUE(host_name, im_mad, vm_mad, tm_mad) );
ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

I removed im_mad, vm_mad, tm_mad from UNIQUE part of the SQL command and changed the size of host_name atribute from 512 to 128.

mysql> 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), UNIQUE(host_name) );;;
Query OK, 0 rows affected (0.00 sec)
mysql> 

After that, query executed fine.

Here is the patch for src/host/Host.cc:

diff --git a/src/host/Host.cc b/src/host/Host.cc
index 3fc1087..819a40b 100644
--- a/src/host/Host.cc
+++ b/src/host/Host.cc
@@ -57,10 +57,10 @@ const char * Host::db_names = "(oid,host_name,state,im_mad,vm_mad," 
                               "tm_mad,last_mon_time, cluster)";

 const char * Host::db_bootstrap = "CREATE TABLE IF NOT EXISTS host_pool (" 
-    "oid INTEGER PRIMARY KEY,host_name VARCHAR(512), state INTEGER," 
+    "oid INTEGER PRIMARY KEY,host_name VARCHAR(128), state INTEGER," 
     "im_mad VARCHAR(128),vm_mad VARCHAR(128),tm_mad VARCHAR(128)," 
     "last_mon_time INTEGER, cluster VARCHAR(128), " 
-    "UNIQUE(host_name, im_mad, vm_mad, tm_mad) )";
+    "UNIQUE(host_name) )";

 /* ------------------------------------------------------------------------ */
 /* ------------------------------------------------------------------------ */

After uninstall + purge DB, recompilation and install - it created table host_pool just fine.


Related issues

Duplicates Bug #308: Host table is not created in ONE 2.0beta Closed 08/06/2010

History

#1 Updated by Martin Kopta over 10 years ago

And I forgot to mention that I use Archlinux (rolling updates) with default MySQL configuration

$ mysql -V
mysql  Ver 14.14 Distrib 5.1.47, for unknown-linux-gnu (x86_64) using readline 6.1
$ pacman -Qi mysql | grep ^Version
Version        : 5.1.47-1

MySQL configuration:

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
[mysqld]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
skip-external-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 16K
myisam_sort_buffer_size = 8M
skip-networking
server-id       = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

#2 Updated by Ruben S. Montero over 10 years ago

  • Status changed from New to Closed
  • Resolution set to duplicate

A patch for this is available in the git repo, currently we are fixing bugs in the one-2.0 branch. Thanks for the feedback!

Cheers

Ruben

Also available in: Atom PDF