Trying to upgrade from 1.9.4 to 1.9.9 running into problems with mysql.

[MTWiley]

Trying to upgrade from Zabbix 1.9.4 to 1.9.9 and I get errors that the host inventory table is missing if I try to start the server without upgrading the DB, that's to be expected, I understand there were some changes made to the DB and a new table was added. However when I try to upgrade the table after restoring from a snapshot, I get the following messages

[]# ./upgrade -D zabbix -p
 WARNING: backup your database before performing upgrade

 This is an UNSUPPORTED Zabbix upgrade script from 1.8 to 2.0 for MySQL
 It does the following things:
  1. Updates indexes that might require changes;
  2. Patches the database from 1.8 schema to 2.0 schema;
  3. Adds 'Disabled' and 'Debug' usergroup if any missing;
  4. Checks for hosts not belonging to any group and adds them to one if any found.

 Usage: pass required MySQL parameters to this script (like database, user, password etc).

Continue ? (y/n) y
Dropping indexes that might need re-creation...
Enter password:
 ... dropping of indexes took 0:00:08
Creating possibly missing indexes...
creating of indexes took 0:00:00
Patching the database
Enter password:
ERROR 1005 (HY000) at line 6: Can't create table './zabbix/#sql-852_9b.frm' (errno: 121)
Failed to patch Zabbix database. Restore from backup

I've looked online to try to find a bit more information where people were having similar issues, and my understanding is that it's some type of foreign key constraint error. I'm not that familiar with the upgrade process on Zabbix or mysql in general, but from what I was able to gather by running the SHOW INNODB in mysql it appears that it's confirmed as a foreign key constraint issue. I'm wondering how do I fix this? I'm sure it's possible, but I'm just not clear on how to upgrade the database from 1.9.4 to 1.9.9?

[]# mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 161
Server version: 5.0.95 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show innodb status
    -> ;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Status                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
=====================================
120301 14:39:53 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 20 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 428, signal count 426
Mutex spin waits 0, rounds 3647, OS waits 38
RW-shared spins 709, OS waits 360; RW-excl spins 36, OS waits 27
------------------------
LATEST FOREIGN KEY ERROR
------------------------
120301 14:39:06 Error in foreign key constraint creation for table `zabbix/#sql-852_9b`.
A foreign key constraint of name `zabbix/c_acknowledges_1`
already exists. (Note that internally InnoDB adds 'databasename/'
in front of the user-defined constraint name).
Note that InnoDB's FOREIGN KEY system tables store
constraint names as case-insensitive, with the
MySQL standard latin1_swedish_ci collation. If you
create tables or databases whose names differ only in
the character case, then collisions in constraint
names can occur. Workaround: name your constraints
explicitly with unique names.
------------
TRANSACTIONS
------------
Trx id counter 0 942154509
Purge done for trx's n:o < 0 942154447 undo n:o < 0 0
History list length 26
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 2130, OS thread id 1184090432
MySQL thread id 161, query id 79000 localhost root
show innodb status
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
12824 OS file reads, 10842 OS file writes, 3953 OS fsyncs
11.90 reads/s, 16384 avg bytes/read, 0.35 writes/s, 0.25 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 119, seg size 121,
860 inserts, 860 merged recs, 802 merges
Hash table size 17393, used cells 5615, node heap has 11 buffer(s)
64.90 hash searches/s, 102.49 non-hash searches/s
---
LOG
---
Log sequence number 144 332273535
Log flushed up to   144 332273535
Last checkpoint at  144 332273535
0 pending log writes, 0 pending chkp writes
3336 log i/o's done, 0.15 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 22987626; in additional pool allocated 1048320
Buffer pool size   512
Free buffers       0
Database pages     501
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 13037, created 125, written 8205
11.90 reads/s, 0.00 creates/s, 0.20 writes/s
Buffer pool hit rate 977 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 2130, id 1181960512, state: waiting for server activity
Number of rows inserted 2341, updated 1283, deleted 16420, read 1499779
0.00 inserts/s, 0.05 updates/s, 0.00 deletes/s, 384.83 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Is there a script somewhere else in the source that's meant for this upgrade? The one I was using was:
zabbix-1.9.9/upgrades/dbpatches/2.0/mysql/upgrade but it seems to be meant for upgrading from 1.8 to 2.0.

[ahowell]

Have a read of http://www.zabbix.org/wiki/How_to/trunk_db_patches