2.0 to 2.2 database fails

[selphjd]

I've got zabbix on ubuntu 12.04 and when I did the upgrade, it's failing to handle the database upgrade. It was working perfectly before, and I can't find any reference to this error. Any one have ideas?

3026:20131113:090044.440 Starting Zabbix Server. Zabbix 2.2.0 (revision 40163).
3026:20131113:090044.440 ****** Enabled features ******
3026:20131113:090044.440 SNMP monitoring: YES
3026:20131113:090044.440 IPMI monitoring: YES
3026:20131113:090044.440 WEB monitoring: YES
3026:20131113:090044.440 VMware monitoring: YES
3026:20131113:090044.440 Jabber notifications: YES
3026:20131113:090044.440 Ez Texting notifications: YES
3026:20131113:090044.440 ODBC: YES
3026:20131113:090044.440 SSH2 support: YES
3026:20131113:090044.440 IPv6 support: YES
3026:20131113:090044.440 ******************************
3026:20131113:090044.440 using configuration file: /etc/zabbix/zabbix_server.conf
3026:20131113:090044.444 current database version (mandatory/optional): 02010040/02010040
3026:20131113:090044.444 required mandatory version: 02020000
3026:20131113:090044.444 starting automatic database upgrade
3026:20131113:090044.445 [Z3005] query failed: [1060] Duplicate column name 'state' [alter table items add state integer default '0' not null]
3026:20131113:090044.445 database upgrade failed

[selphjd]

so I did a snapshot back to before the upgrade. Then I gdebi'd the upgrade package again and it seemed to be working. Hopefully. I'm not sure because the log is spitting out tons of the messages below. But maybe it's working on it?
13267:20131113:105505.244 [Z3005] query failed: [1062] Duplicate entry '10087-vfs.fs.size[C:,total]' for key 'items_1' [insert into items (itemid,name,key_,hostid,type,value_type,data_type ,delay,delay_flex,history,trends,status,trapper_ho sts,units,multiplier,delta,formula,logtimefmt,valu emapid,params,ipmi_sensor,snmp_community,snmp_oid, port,snmpv3_securityname,snmpv3_securitylevel,snmp v3_authprotocol,snmpv3_authpassphrase,snmpv3_privp rotocol,snmpv3_privpassphrase,authtype,username,pa ssword,publickey,privatekey,description,interfacei d,flags,snmpv3_contextname) values (27217,'Total disk space - $1','vfs.fs.size[C:,total]',10087,0,0,0,30,'',90,365,0,'','B',0,0,'1','',nul l,'','','','','','',0,0,'',0,'',0,'','','','','',3 0194,4,''),(27218,'Total disk space - $1','vfs.fs.size[D:,total]',10087,0,0,0,30,'',90,365,0,'','B',0,0,'1','',nul l,'','','','','','',0,0,'',0,'',0,'','','','','',3 0194,4,''),(27219,'Total disk space - $1','vfs.fs.size[E:,total]',10087,0,0,0,30,'',90,365,0,'','B',0,0,'1','',nul l,'','','','','','',0,0,'',0,'',0,'','','','','',3 0194,4,'');
]
13268:20131113:105505.428 [Z3005] query failed: [1062] Duplicate entry '10078-vfs.fs.size[C:,free]' for key 'items_1' [insert into items (itemid,name,key_,hostid,type,value_type,data_type ,delay,delay_flex,history,trends,status,trapper_ho sts,units,multiplier,delta,formula,logtimefmt,valu emapid,params,ipmi_sensor,snmp_community,snmp_oid, port,snmpv3_securityname,snmpv3_securitylevel,snmp v3_authprotocol,snmpv3_authpassphrase,snmpv3_privp rotocol,snmpv3_privpassphrase,authtype,username,pa ssword,publickey,privatekey,description,interfacei d,flags,snmpv3_contextname) values (27217,'Free disk space - $1','vfs.fs.size[A:,free]',10078,0,0,0,30,'',90,365,0,'','B',0,0,'1','',nul l,'','','','','','',0,0,'',0,'',0,'','','','','',3 0186,4,''),(27218,'Free disk space - $1','vfs.fs.size[C:,free]',10078,0,0,0,30,'',90,365,0,'','B',0,0,'1','',nul l,'','','','','','',0,0,'',0,'',0,'','','','','',3 0186,4,''),(27219,'Free disk space - $1','vfs.fs.size[D:,free]',10078,0,0,0,30,'',90,365,0,'','B',0,0,'1','',nul l,'','','','','','',0,0,'',0,'',0,'','','','','',3 0186,4,''),(27220,'Free disk space - $1','vfs.fs.size[E:,free]',10078,0,0,0,30,'',90,365,0,'','B',0,0,'1','',nul l,'','','','','','',0,0,'',0,'',0,'','','','','',3 0186,4,'');
]

[zalex_ua]

the attempt to create duplicated column name 'state' could say that there were some previous upgrade steps/actions.
We don't know what you did previously ...

If you could describe how to reproduce the error (I don't believe in that ) then we could continue discussion.


Next errors - you could try to truncate an "ids" table (it's safe) to fix the problem.
Again, usually such errors say about manual intervention in the database, which is not recommenced.

[HarryKalahan]

[Z3005] query failed: [1060] Duplicate column name 'state'

Hello,

I'm having the same error message:
3010:20140926:115228.342 ODBC: YES
3010:20140926:115228.342 SSH2 support: YES
3010:20140926:115228.342 IPv6 support: YES
3010:20140926:115228.342 ******************************
3010:20140926:115228.342 using configuration file: /etc/zabbix/zabbix_server.conf
3010:20140926:115228.349 current database version (mandatory/optional): 02010040/02010040
3010:20140926:115228.349 required mandatory version: 02020000
3010:20140926:115228.349 starting automatic database upgrade
3010:20140926:115228.352 [Z3005] query failed: [1060] Duplicate column name 'state' [alter table items add state integer defaul ull]
3010:20140926:115228.352 database upgrade failed

Did you find a solution?

I've upgraded the version from mysql5.5 to mysql5.6 before upgrading zabbix version from zabbix2.0.6 to 2.2.2. The command of mysql_upgrade is ok.

Any help would be appreciated.

Thank you very much!

Regards,

HarryKalahan

[HarryKalahan]

[Z3005] query failed: [1060] Duplicate column name 'state'

Hello: I've just found the solution:

You have to drop the 'state' field from items table:

I recommend you to make a database backup before the process:

1.-Also make a backup of the content of items table:
mysql -u root -p

>use zabbix;
>SELECT * INTO OUTFILE '/var/backups/bd_zabbix/items.txt' FROM `zabbix`.`items`;

2.-Delete the field 'state' from the items table. I did it from phpmyadmin:
>alter table items drop state;

3.-Start Zabbix Server again:
/etc/init.d/zabbix-server start

4.-Check zabbix.log and you will see how the upgrade process is completed:

64495:20140927:211337.086 ******************************
64495:20140927:211337.086 using configuration file: /usr/local/etc/zabbix_server.conf
64495:20140927:211337.093 current database version (mandatory/optional): 02010040/02010040
64495:20140927:211337.093 required mandatory version: 02020000
64495:20140927:211337.093 starting automatic database upgrade
64495:20140927:211338.768 completed 0% of database upgrade
64495:20140927:211339.032 completed 1% of database upgrade
64495:20140927:211339.108 completed 2% of database upgrade
64495:20140927:211339.265 completed 3% of database upgrade
64495:20140927:211339.363 completed 4% of database upgrade
64495:20140927:211339.375 completed 5% of database upgrade
...
64495:20140927:211409.936 completed 97% of database upgrade
64495:20140927:211409.936 completed 98% of database upgrade
64495:20140927:211409.937 completed 99% of database upgrade
64495:20140927:211409.938 completed 100% of database upgrade
64495:20140927:211409.938 database upgrade fully completed
64513:20140927:211409.996 server #1 started [configuration syncer #1]
64514:20140927:211409.997 server #2 started [db watchdog #1]
64515:20140927:211410.001 server #3 started [poller #1]

I hope this helps!

HarryKalahan.

[zalex_ua]

The "state" column can store these values:
0 - normal
3 - not supported by agent

This is not "configuration" type values but sort of "run time" ones.
So it's indeed relatively safe to manually drop and let server recreate this column.

Just question is - why this column was existing before? ...

[HarryKalahan]

[Z3005] query failed: [1060] Duplicate column name 'state'

The colum 'state' was existing because I'd just migrated from mysql server 5.5 to mysql5.6 and from zabbix2.0.6 to 2.2.2. I had to migrate from a production enviroment, so that colum probably existed before, or it appeared after upgrading mysql, but I'm not sure.