Upgrade zabbix to 1.8 database problem

[patrick]

I have a problem upgrading databse from 1.6 to 1.8
I use 1.8 upgrade shell tool.
That's what I get :

# sh upgrade zabbix
Dropping indexes that might need re-creation
Converting database to UTF-8
Patching the database
ERROR 1062 (23000) at line 44: Duplicate entry '0-12--10.194.102.180-0' for key 2
Failed to patch Zabbix database. Restore from backup


What could be the problem?
Thanks
Patrick

[richlv]

interesting. how many records does the following query return on zabbix database ?

select * from dservices where ip='10.194.102.180';

edit : oh, and try running this one on the db as well - what does it return ?

alter table dservices drop index dservices_1;

[untergeek]

This is very similar to what I had to do.

I had to truncate the dservices table altogether. I also truncated dhosts (since I'd have a bunch of incorrectly "discovered" duplicates).

I then dropped and recreated the DHOSTS_1, DSERVICES_1, and DSERVICES_2 indexes.

Sure, you'll wind up doing a rediscover on all of your stuff, but it's otherwise painless (unless you have some funky add/remove stuff going on in your discovery actions).

[patrick]

On zabbix 1.6.7 database (before upgarde) :
mysql> select * from dservices where ip='10.194.102.180';
ERROR 1054 (42S22): Unknown column 'ip' in 'where clause'
mysql> alter table dservices drop index dservices_1;
ERROR 1091 (42000): Can't DROP 'dservices_1'; check that column/key exists
mysql> quit
Bye


After trying to upgrade with "sh upgrade zabbix" :
mysql> select * from dservices where ip='10.194.102.180';
+------------+---------+------+------+-------+------+--------+------------+----------+----------+----------------+
| dserviceid | dhostid | type | key_ | value | port | status | lastup | lastdown | dcheckid | ip |
+------------+---------+------+------+-------+------+--------+------------+----------+----------+----------------+
| 94 | 94 | 12 | | | 0 | 0 | 1233150112 | 0 | 0 | 10.194.102.180 |
| 107 | 107 | 12 | | | 0 | 0 | 1256927857 | 0 | 0 | 10.194.102.180 |
+------------+---------+------+------+-------+------+--------+------------+----------+----------+----------------+
2 rows in set (0.00 sec)

mysql> alter table dservices drop index dservices_1;
ERROR 1091 (42000): Can't DROP 'dservices_1'; check that column/key exists
mysql>

[richlv]

ah, right, ip column is added to that table during upgrade.

well, this might break something, but it should at least allow to continue with patching...

before upgrade :

delete from dservices where dserviceid='107';

maybe. maybe it will break everything